A quick overview of creating a Standby from an active database, copying over the network.
(words in italics above are added after this post was published)
1. Create the parameter file initSTDBYDB.ora with additional parameters
change or add DB_UNIQUE_NAME to be STDBYDB
change the location of control files
add fal_server to be the lookup name for the Primary (e.g. ORCLCDB)
add log_archive_dest_2 to specify the Primary Service and DB_UNIQUE_NAME (note : If you are using "log_archive_dest", you can't use "log_archive_dest_2" to co-exist. A default DB_RECOVERY_FILE_DEST location is preferable)
add db_file_name_convert and log_file_name_convert to map file names to new directories (if they are to be different or, for example, if creating the Standby on the same server !!) --- ensure that you have the new directories (or ASM DiskGroups) available on the Standby with the right permissions (including directories for PDBs and the PDBSEED) !
change any other hardcoded directory names (e.g. for adump)
(words in italics above are added after this post was published)
1. Create the parameter file initSTDBYDB.ora with additional parameters
change or add DB_UNIQUE_NAME to be STDBYDB
change the location of control files
add fal_server to be the lookup name for the Primary (e.g. ORCLCDB)
add log_archive_dest_2 to specify the Primary Service and DB_UNIQUE_NAME (note : If you are using "log_archive_dest", you can't use "log_archive_dest_2" to co-exist. A default DB_RECOVERY_FILE_DEST location is preferable)
add db_file_name_convert and log_file_name_convert to map file names to new directories (if they are to be different or, for example, if creating the Standby on the same server !!) --- ensure that you have the new directories (or ASM DiskGroups) available on the Standby with the right permissions (including directories for PDBs and the PDBSEED) !
change any other hardcoded directory names (e.g. for adump)
Also, I recommend setting log_archive_config='DG_CONFIG(<primary>,<standby>)' at both databases
2. Create a listener.ora and/or a new listener with a static SID_NAME entry for the Standby DB
3. Add an entry for the Standby in the Primary tnsnames.ora and for the Primary in the Standby tnsnames.ora
4. Add at least one Standby Redo Log file to the Primary Database
5. Ensure that you have the password for the SYS account (or will you be using SYSDG ?) on the Primary and copy the Password file to the Stadnby
6. Start the Standby listener
7. STARTUP NOMOUNT the Standby Instance (remember to have the ORACLE_SID set !!)
8. Start rman on the Primary with :
and thus the execution will be as :
Note : For simplicity, I didn't use the SPFILE specification in the DUPLICATE command to create and update an SPFILE at the Standby. I am using a simple initSTDBYDB.ora pfile
In the next blog post, I will be covering how to begin (and then monitor) shipping of redo from the Primary to the Standby.
2. Create a listener.ora and/or a new listener with a static SID_NAME entry for the Standby DB
3. Add an entry for the Standby in the Primary tnsnames.ora and for the Primary in the Standby tnsnames.ora
4. Add at least one Standby Redo Log file to the Primary Database
5. Ensure that you have the password for the SYS account (or will you be using SYSDG ?) on the Primary and copy the Password file to the Stadnby
6. Start the Standby listener
7. STARTUP NOMOUNT the Standby Instance (remember to have the ORACLE_SID set !!)
8. Start rman on the Primary with :
rman target sys/manager auxiliary sys/manager@STDBYDBand then issue the command
duplicate target database for standby from active database dorecover;
and thus the execution will be as :
oracle19c>rman target sys/manager auxiliary sys/manager@STDBYDB Recovery Manager: Release 19.0.0.0.0 - Production on Sun Feb 23 23:38:59 2020 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 auxiliary database: ORCLCDB (not mounted) RMAN> duplicate target database for standby from active database dorecover; Starting Duplicate Db at 23-FEB-20 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=21 device type=DISK current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDBYDB' ; } executing Memory Script Starting backup at 23-FEB-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=274 device type=DISK Finished backup at 23-FEB-20 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/opt/oracle/oradata/STDBYDB/control01.ctl'; restore clone primary controlfile to '/opt/oracle/oradata/STDBYDB/control02.ctl' from '/opt/oracle/oradata/STDBYDB/control01.ctl'; } executing Memory Script Starting backup at 23-FEB-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f tag=TAG20200223T233924 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 23-FEB-20 Starting restore at 23-FEB-20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 23-FEB-20 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/opt/oracle/oradata/STDBYDB/temp01.dbf"; set newname for tempfile 2 to "/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf"; set newname for tempfile 3 to "/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/opt/oracle/oradata/STDBYDB/system01.dbf"; set newname for datafile 3 to "/opt/oracle/oradata/STDBYDB/sysaux01.dbf"; set newname for datafile 4 to "/opt/oracle/oradata/STDBYDB/undotbs01.dbf"; set newname for datafile 5 to "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf"; set newname for datafile 6 to "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf"; set newname for datafile 7 to "/opt/oracle/oradata/STDBYDB/users01.dbf"; set newname for datafile 8 to "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf"; set newname for datafile 9 to "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf"; set newname for datafile 10 to "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf"; set newname for datafile 11 to "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf"; set newname for datafile 12 to "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/opt/oracle/oradata/STDBYDB/system01.dbf" datafile 3 auxiliary format "/opt/oracle/oradata/STDBYDB/sysaux01.dbf" datafile 4 auxiliary format "/opt/oracle/oradata/STDBYDB/undotbs01.dbf" datafile 5 auxiliary format "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf" datafile 6 auxiliary format "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf" datafile 7 auxiliary format "/opt/oracle/oradata/STDBYDB/users01.dbf" datafile 8 auxiliary format "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf" datafile 9 auxiliary format "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf" datafile 10 auxiliary format "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf" datafile 11 auxiliary format "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf" datafile 12 auxiliary format "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /opt/oracle/oradata/STDBYDB/temp01.dbf in control file renamed tempfile 2 to /opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf in control file renamed tempfile 3 to /opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 23-FEB-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf output file name=/opt/oracle/oradata/STDBYDB/system01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf output file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf output file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf output file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf output file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf output file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf output file name=/opt/oracle/oradata/STDBYDB/users01.dbf tag=TAG20200223T233939 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-FEB-20 sql statement: alter system archive log current current log archived contents of Memory Script: { backup as copy reuse archivelog like "/opt/oracle/archivelog/ORCLCDB/1_41_1007421686.dbf" auxiliary format "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf" archivelog like "/opt/oracle/archivelog/ORCLCDB/1_42_1007421686.dbf" auxiliary format "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf" ; catalog clone archivelog "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf"; catalog clone archivelog "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf"; switch clone datafile all; } executing Memory Script Starting backup at 23-FEB-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=41 RECID=9 STAMP=1033170130 output file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=42 RECID=10 STAMP=1033170130 output file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 23-FEB-20 cataloged archived log archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133 cataloged archived log archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133 datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/users01.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf datafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf datafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf contents of Memory Script: { set until scn 4658614; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 23-FEB-20 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 41 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf archived log for thread 1 with sequence 42 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf thread=1 sequence=41 archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf thread=1 sequence=42 media recovery complete, elapsed time: 00:00:01 Finished recover at 23-FEB-20 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script released channel: ORA_DISK_1 released channel: ORA_AUX_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=274 device type=DISK deleted archived log archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133 deleted archived log archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133 Deleted 2 objects Finished Duplicate Db at 23-FEB-20 RMAN>
Note : For simplicity, I didn't use the SPFILE specification in the DUPLICATE command to create and update an SPFILE at the Standby. I am using a simple initSTDBYDB.ora pfile
In the next blog post, I will be covering how to begin (and then monitor) shipping of redo from the Primary to the Standby.