A quick demo of an RMAN Catalog shared by both Primary and Standby databases
For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby
At the Primary :
oracle19c>echo $ORACLE_SID ORCLCDB oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- ORCLCDB READ WRITE SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string ORCLCDB SQL> SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle19c>rman target / catalog rmanschema/rmanschema@rmancat Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> quit Recovery Manager complete. oracle19c>
Now, at the Standby
oracle19c>echo $ORACLE_SID STDBYDB oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- ORCLCDB MOUNTED SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string STDBYDB SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle19c>rman target / catalog rmanschema/rmanschema@rmancat Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057, not open) connected to recovery catalog database RMAN> register database; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of register command at 04/21/2021 22:26:19 RMAN-01005: Mounted control file type must be CURRENT to register the database RMAN> quit Recovery Manager complete. oracle19c>
(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)
It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).
So, I now have two different database backups of the same NAME and DBID registered in one catalog.
So, how do I run RMAN Backups and have them saved in the Catalog ?
On the Primary, I run a FULL Backup.
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057) connected to recovery catalog database RMAN> backup as compressed backupset database; Starting backup at 21-APR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=280 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 21-APR-21 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf channel ORA_DISK_2: starting piece 1 at 21-APR-21 channel ORA_DISK_2: finished piece 1 at 21-APR-21 piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE ... ... ... Starting Control File and SPFILE Autobackup at 21-APR-21 piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 21-APR-21 RMAN> quit Recovery Manager complete. oracle19c>
Then, I run a separate backup at the Standby :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057, not open) connected to recovery catalog database RMAN> backup as compressed backupset database; Starting backup at 21-APR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=253 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=249 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 21-APR-21 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf channel ORA_DISK_2: starting piece 1 at 21-APR-21 channel ORA_DISK_2: finished piece 1 at 21-APR-21 piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE ... ... ... Starting Control File and SPFILE Autobackup at 21-APR-21 piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 21-APR-21 RMAN> quit Recovery Manager complete. oracle19c>
So, I now have two different database backups of the same NAME and DBID registered in one catalog.
How do I distinguish them ? From the RMAN command lime query I can verify in this manner (querying only for datafile 1 for the purpose of this demo)
At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database
RMAN> list backup of datafile 1 completed after "sysdate-1";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245 Full 231.82M DISK 00:00:38 21-APR-21
BP Key: 1255 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223134
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
List of Datafiles in backup set 1245
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11072202 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/system01.dbf
RMAN> quit
Recovery Manager complete.
oracle19c>
And at the Standby :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database
RMAN> list backup of datafile 1 completed after "sysdate-1";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300 Full 241.08M DISK 00:00:40 21-APR-21
BP Key: 1308 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223703
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
List of Datafiles in backup set 1300
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11061162 21-APR-21 11072277 NO /opt/oracle/oradata/STDBYDB/system01.dbf
RMAN> quit
Recovery Manager complete.
oracle19c>
RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).
But the listing does show that
the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
while the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.
You might also notice that the Checkpoint SCNs are different.
Can I run an SQL query in the RMAN Catalog schema itself ?
Here we can set that RC_DATABASE has only 1 record for the actual database. But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.
oracle19c>sqlplus rmanschema/rmanschema@rmancat
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select db_key, dbid, name from rc_database;
DB_KEY DBID NAME
---------- ---------- --------
1 2778483057 ORCLCDB
SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;
SITE_KEY DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
3 1 PRIMARY ORCLCDB
804 1 STANDBY STDBYDB
SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 bs.bs_key, bs.backup_type
3 from rc_site site, rc_backup_set bs
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and bs.start_time > sysdate-1
7* and bs.bs_key in (1245,1300)
SQL> /
SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY B
---------- ------- ---------------- ---------- -
3 PRIMARY ORCLCDB 1245 D
804 STANDBY STDBYDB 1300 D
SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 df.bs_key, df.file#, df.checkpoint_change#
3 from rc_site site, rc_backup_set bs, rc_backup_datafile df
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and site.db_key=df.db_key
7 and bs.bs_key=df.bs_key
8 and bs.start_time > sysdate-1
9 and bs.bs_key in (1245,1300)
10* and df.file#=1
SQL> /
SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
3 PRIMARY ORCLCDB 1245 1 11072202
804 STANDBY STDBYDB 1300 1 11061162
Here we can set that RC_DATABASE has only 1 record for the actual database. But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.
BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN. Today's backup of Datafile#1 at the Primary was in Backup Set 1245 while that of the same datafile at the Standby was in Backup Set 1300. (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.
There are many more RC_% Tables and Views in the RMAN Schema that you can query.
1 comment:
Very nice article. Very helpful. The tips to identify the primary and standby datase from rc_site were helpful.
Post a Comment