-- The following 3 database server used for primary, standby and catalog database and backup taken from standby database
-- TESTDB1: Primary Database
-- TESTBD2: Standby Database
-- CATDB : Catalog Database
-- Create Catalog User
CREATE USER rman
IDENTIFIED BY rman123
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE RMANCAT
QUOTA UNLIMITED ON RMANCAT;
-- Grant RECOVERY_CALATOL_OWNER to rman
GRANT RECOVERY_CATALOG_OWNER TO rman;
-- Create catalog database
[oracle@catdb ~]$ rman CATALOG rman/rman123
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 19 23:56:41 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
-- Added in both primary and standby database in tnsnames.ora file
CATDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = catdb.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CATDB)
)
)
-- Add in catalog database tnsnames.ora file
TESTDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb1.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb1)
)
)
TESTDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb2)
)
)
-- register primary database and configure standby connect identifier
-- In Primary database (testdb1)
[oracle@testdb1 ~]$ rman target / catalog rman/rman123@CATDB
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 20 09:36:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB1 (DBID=693279980)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
1 TESTDB1 693279980 PRIMARY TESTDB1
RMAN> CONFIGURE DB_UNIQUE_NAME 'testdb2' CONNECT IDENTIFIER 'testdb2';
new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'testdb2' CONNECT IDENTIFIER 'testdb2';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
1 TESTDB1 693279980 PRIMARY TESTDB1
1 TESTDB1 693279980 STANDBY TESTDB2
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RESYNC CATALOG FROM DB_UNIQUE_NAME
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> create global script backup_Inc0_archive_logs_compressed
{
configure controlfile autobackup on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/dbasharedvol/backup_tests/%d/%Y%M%D_%d_ctrl_%F.backup';
allocate channel c1 DEVICE TYPE DISK MAXPIECESIZE = 10G;
allocate channel c2 DEVICE TYPE DISK MAXPIECESIZE = 10G;
allocate channel c3 DEVICE TYPE DISK MAXPIECESIZE = 10G;
allocate channel c4 DEVICE TYPE DISK MAXPIECESIZE = 10G;
backup spfile format "/mnt/dbasharedvol/backup_tests/%d/%Y%M%D_%d_spfile_%s_%p.bkp";
backup as compressed backupset incremental level 0 database format "/mnt/dbasharedvol/backup_tests/%d/%Y%M%D_%d_inc0_db_compressed_%s_%p.bkp"
plus archivelog
delete all input
format "/mnt/dbasharedvol/backup_tests/%d/%Y%M%D_%d_inc0_arc_compressed_%s_%p.bkp";
delete noprompt obsolete device type disk;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
created global script sbackup_Inc0_archive_logs_compressed
-- testdb2 (Standby)
[oracle@testdb2 ~]$ rman target / catalog rman/rman123@CATDB
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 20 09:33:31 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB1 (DBID=693279980, not open)
connected to recovery catalog database
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
1 TESTDB1 693279980 PRIMARY TESTDB1
1 TESTDB1 693279980 STANDBY TESTDB2
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored
-- Test
-- Try to delete archivelogs from standby before backup and log file applied in all standby
-- Testdb2
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_52_941163822.dbf thread=1 sequence=52
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_53_941163822.dbf thread=1 sequence=53
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_54_941163822.dbf thread=1 sequence=54
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_55_941163822.dbf thread=1 sequence=55
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_56_941163822.dbf thread=1 sequence=56
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/holding/testdb2/standby_logs/1_57_941163822.dbf thread=1 sequence=57
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb2/standby_logs/1_58_941163822.dbf thread=1 sequence=58
List of Archived Log Copies for database with db_unique_name TESTDB2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
711 1 51 A 20-APR-17
Name: /holding/testdb2/standby_logs/1_51_941163822.dbf
deleted archived log
archived log file name=/holding/testdb2/standby_logs/1_51_941163822.dbf RECID=34 STAMP=941814623
Deleted 1 objects
-- Take backup from testdb2
[oracle@testdb2 ~]$ rman target / catalog rman/rman123@CATDB script backup_Inc0_archive_logs_compressed log /tmp/baclup_"RUNTIME".log
[oracle@testdb2 ~]$ rman target / catalog rman/rman123@CATDB
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 20 15:13:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB1 (DBID=693279980, not open)
connected to recovery catalog database
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb2/standby_logs/1_58_941163822.dbf thread=1 sequence=58
-- After performing a log swichover in primary database the log file applied in standby and delted
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb2/standby_logs/1_59_941163822.dbf thread=1 sequence=59
List of Archived Log Copies for database with db_unique_name TESTDB2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
779 1 58 A 20-APR-17
Name: /holding/testdb2/standby_logs/1_58_941163822.dbf
deleted archived log
archived log file name=/holding/testdb2/standby_logs/1_58_941163822.dbf RECID=41 STAMP=941814670
Deleted 1 objects
-- Try to delete archivelogs from primary before it applied to standby
-- testdb1
[oracle@testdb1 archive_logs]$ rman target / catalog rman/rman123@CATDB
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 20 15:18:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB1 (DBID=693279980)
connected to recovery catalog database
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb1/archive_logs/1_62_941163822.dbf thread=1 sequence=62
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb1/archive_logs/1_63_941163822.dbf thread=1 sequence=63
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb1/archive_logs/1_64_941163822.dbf thread=1 sequence=64
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=/holding/testdb1/archive_logs/1_65_941163822.dbf thread=1 sequence=65