Primary Database Name: PO
Primary DB Unique Name: PO
Primary Database Version: 19.0.0.0
Standby Database Name: PO
Standby DB Unique Name: DR
Operating System: Windows 10 Pro 64 Bit
Note: Creating Physical Standby database using RMAN DUPLICATE ...FROM ACTIVE DATABASE command does not require to shut down the primary database. Also, it does not require any backup. This is the feature of Oracle 11g where it duplicates to standby database without any backup.
Here, I am creating Physical Standby database on same server where my primary is located. So, the below details will be common for both Primary and Standby DBs.
- tnsnames.ora
- listener.ora
- ORACLE_HOME binaries
Step 1: Create separate directories for DR database. Check existing database directories like data files, control files, redo log files, archived log files, etc. Double check the directories that you added in your pfile and duplicate command.
SQL> set lines 300 pages 3000 SQL> col name for a12 SQL> col FORCE_LOGGING for a12 SQL> col DB_UNIQUE_NAME for a12 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,force_logging,log_mode from v$database; NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE PROTECTION_MODE FORCE_LOGGIN LOG_MODE ----- ------------ ----------- ------------- ------------------ ------------ ---------- PO PO READ WRITE PRIMARY MAXIMUM PROTECTION YES ARCHIVELOG SQL> set lines 300 pages 3000 SQL> col name for a56 SQL> select name from v$datafile; NAME ----------------------------------------------- D:\RUPESH\APP\RGHUBADE\ORADATA\PO\SYSTEM01.DBF D:\RUPESH\APP\RGHUBADE\ORADATA\PO\DATA01.DBF D:\RUPESH\APP\RGHUBADE\ORADATA\PO\SYSAUX01.DBF D:\RUPESH\APP\RGHUBADE\ORADATA\PO\UNDOTBS02.DBF SQL> select name from v$tempfile; NAME -------------------------------------------- D:\RUPESH\APP\RGHUBADE\ORADATA\PO\TEMP01.DBF D:\RUPESH\APP\RGHUBADE\ORADATA\PO\TEMP02.DBF SQL> select name from v$controlfile; NAME ----------------------------------------------- D:\RUPESH\APP\RGHUBADE\ORADATA\PO\CONTROL01.CTL D:\RUPESH\APP\RGHUBADE\ORADATA\PO\CONTROL02.CTL SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination D:\RUPESH\app\rghubade\oradata\PO\archive Oldest online log sequence 691 Next log sequence to archive 693 Current log sequence 693 SQL> show parameter log_archive_dest_1 NAME TYPE VALUE --------------------- ------- ------------------------------ log_archive_dest_1 string LOCATION=D:\RUPESH\app\rghubad e\oradata\PO\archive If you have not enabled force_logging mode in the database then you can enable it by below command: SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING ----------------- YES Below is the pfile "INITDR.ORA" for duplicate standby database to be created. *._readable_standby_sync_timeout=60 *.audit_file_dest='D:\RUPESH\Setups\DR\adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='D:\RUPESH\Setups\DR\control01.ctl','D:\RUPESH\Setups\DR\control02.ctl' *.db_block_size=8192 *.db_keep_cache_size=218103808 *.db_name='PO' *.db_unique_name='DR' *.diagnostic_dest='D:\RUPESH\app\rghubade' *.log_archive_dest_1='LOCATION=D:\RUPESH\Setups\DR\archive' *.log_archive_format='%t_%s_%r.arc' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_limit=3221225472 *.pga_aggregate_target=1073741824 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=4831m *.undo_tablespace='UNDOTBS_NEW' *.standby_file_management='AUTO' *.log_archive_config='dg_config=(PO,DR)' *.db_create_file_dest='D:\RUPESH\Setups\DR' *.DB_CREATE_ONLINE_LOG_DEST_1='D:\RUPESH\Setups\DR' |
Step 2: Create password file for duplicate database "DR" or you can copy existing database password file and rename it as PWDDR.ora.
Step 3: Configure tnsnames.ora and listener.ora file for both Primary and Standby(DR) database.
Note: Please add your actual IP adress in below network files instead of *.*.*.*.
The contents of tnsnames.ora PO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1530)) ) (CONNECT_DATA = (SERVICE_NAME = PO) ) ) DR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1531)) ) (CONNECT_DATA = (SERVICE_NAME = DR) ) ) The contents of listener.ora LISTENER_PO = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2)) (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1530)) ) ) SID_LIST_LISTENER_PO = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PO) (ORACLE_HOME = D:\RUPESH\Setups\WINDOWS.X64_193000_db_home) (SID_NAME = PO) ) ) LISTENER_DR = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1531)) ) ) SID_LIST_LISTENER_DR = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DR) (ORACLE_HOME = D:\RUPESH\Setups\WINDOWS.X64_193000_db_home) (SID_NAME = DR) ) ) |
Step 4: Make below parameter changes in your Primary database.
SQL> alter system set log_archive_dest_2='service=DR LGWR ASYNC valid_for=(all_logfiles,all_roles) db_unique_name=DR'; SQL> alter system set log_archive_config='dg_config=(PO,DR)'; SQL> show parameter remote_login_passwordfile NAME TYPE VALUE -------------------------- ------- ----------- remote_login_passwordfile string EXCLUSIVE SQL> show parameter log_archive_dest_1 NAME TYPE VALUE -------------------- ------ ------------------------------ log_archive_dest_1 string LOCATION=D:\RUPESH\app\rghubad e\oradata\PO\archive NAME TYPE VALUE -------------------- ------ ------------------------------ log_archive_dest_2 string service=DR LGWR ASYNC va lid_for=(all_logfiles,all_role s) db_unique_name=DR NAME TYPE VALUE -------------------- ------ ------------------------------ log_archive_config string dg_config=(PO,DR) |
Step 5: Create service for Standby(DR) database as below:
oradim -NEW -SID DR -syspwd sys123 -STARTMODE auto -PFILE D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITDR.ORA Ensure below services are running in services.msc - OracleServiceDR - OracleOraDB19Home1TNSListenerLISTENER_PO - OracleOraDB19Home1TNSListenerLISTENER_DR C:\windows\system32>tnsping po TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 30-MAR-2023 21:38:48 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1530))) (CONNECT_DATA = (SERVICE_NAME = PO))) OK (0 msec) C:\windows\system32>tnsping dr TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 25-MAR-2023 23:18:44 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = DR))) OK (0 msec) C:\windows\system32> set ORACLE_SID=DR C:\windows\system32>set o ORACLE_SID=DR OS=Windows_NT C:\windows\system32>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 25 23:32:29 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITDR.ORA'; ORACLE instance started. Total System Global Area 5066718192 bytes Fixed Size 9038832 bytes Variable Size 889192448 bytes Database Buffers 4160749568 bytes Redo Buffers 7737344 bytes Verify sqlplus connectivity for both Primary and Standby(DR) database. C:\windows\system32>sqlplus sys/sys123@PO as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 25 23:36:20 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- PO READ WRITE C:\windows\system32>sqlplus sys/sys123@DR as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 25 23:36:27 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> select name,open_mode from v$database; select name,open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted C:\windows\system32>rman target sys/sys123@PO auxiliary sys/sys123@DR Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 25 23:44:58 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved. connected to target database: PO (DBID=2972374659) connected to auxiliary database: PO (not mounted) RMAN> run { 2> allocate channel d1 type disk; 3> allocate channel d2 type disk; 4> allocate auxiliary channel a1 type DISK; 5> allocate auxiliary channel a2 type DISK; 6> duplicate target database for standby from active database nofilenamecheck dorecover; 7> } using target database control file instead of recovery catalog allocated channel: d1 channel d1: SID=777 device type=DISK allocated channel: d2 channel d2: SID=967 device type=DISK allocated channel: a1 channel a1: SID=196 device type=DISK allocated channel: a2 channel a2: SID=387 device type=DISK Starting Duplicate Db at 25-MAR-23 current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format 'D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\DATABASE\PWDdr.ORA' ; } executing Memory Script Starting backup at 25-MAR-23 Finished backup at 25-MAR-23 duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { restore clone from service 'PO' standby controlfile; } executing Memory Script Starting restore at 25-MAR-23 channel a1: starting datafile backup set restore channel a1: using network backup set from service PO channel a1: restoring control file channel a1: restore complete, elapsed time: 00:00:01 output file name=D:\RUPESH\SETUPS\DR\CONTROL01.CTL output file name=D:\RUPESH\SETUPS\DR\CONTROL02.CTL Finished restore at 25-MAR-23 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 clone tempfile 1 to new; set newname for clone tempfile 2 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 5 to new; restore from nonsparse from service 'PO' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_TEMP_%U_.TMP in control file renamed tempfile 2 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_TEMP2_%U_.TMP in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 25-MAR-23 channel a1: starting datafile backup set restore channel a1: using network backup set from service PO channel a1: specifying datafile(s) to restore from backup set channel a1: restoring datafile 00001 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_SYSTEM_%U_.DBF channel a2: starting datafile backup set restore channel a2: using network backup set from service PO channel a2: specifying datafile(s) to restore from backup set channel a2: restoring datafile 00002 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_TEST_%U_.DBF channel a2: restore complete, elapsed time: 00:00:15 channel a2: starting datafile backup set restore channel a2: using network backup set from service PO channel a2: specifying datafile(s) to restore from backup set channel a2: restoring datafile 00003 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_SYSAUX_%U_.DBF channel a1: restore complete, elapsed time: 00:00:19 channel a1: starting datafile backup set restore channel a1: using network backup set from service PO channel a1: specifying datafile(s) to restore from backup set channel a1: restoring datafile 00005 to D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_UNDOTBS__%U_.DBF channel a1: restore complete, elapsed time: 00:00:07 channel a2: restore complete, elapsed time: 00:00:28 Finished restore at 25-MAR-23 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'PO' archivelog from scn 9518522; switch clone datafile all; } executing Memory Script Starting restore at 25-MAR-23 channel a1: starting archived log restore to default destination channel a1: using network backup set from service PO channel a1: restoring archived log archived log thread=1 sequence=239 channel a2: starting archived log restore to default destination channel a2: using network backup set from service PO channel a2: restoring archived log archived log thread=1 sequence=240 channel a1: restore complete, elapsed time: 00:00:00 channel a2: restore complete, elapsed time: 00:00:01 Finished restore at 25-MAR-23 datafile 1 switched to datafile copy input datafile copy RECID=5 STAMP=1132443964 file name=D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_SYSTEM_L1YGKQ3B_.DBF datafile 2 switched to datafile copy input datafile copy RECID=6 STAMP=1132443965 file name=D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_TEST_L1YGKQ99_.DBF datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=1132443965 file name=D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_SYSAUX_L1YGL6FC_.DBF datafile 5 switched to datafile copy input datafile copy RECID=8 STAMP=1132443965 file name=D:\RUPESH\SETUPS\DR\DR\DATAFILE\O1_MF_UNDOTBS__L1YGL9O2_.DBF contents of Memory Script: { set until scn 9518705; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 25-MAR-23 starting media recovery archived log for thread 1 with sequence 239 is already on disk as file D:\RUPESH\SETUPS\DR\ARCHIVE\1_239_1084451655.ARC archived log for thread 1 with sequence 240 is already on disk as file D:\RUPESH\SETUPS\DR\ARCHIVE\1_240_1084451655.ARC archived log file name=D:\RUPESH\SETUPS\DR\ARCHIVE\1_239_1084451655.ARC thread=1 sequence=239 archived log file name=D:\RUPESH\SETUPS\DR\ARCHIVE\1_240_1084451655.ARC thread=1 sequence=240 media recovery complete, elapsed time: 00:00:01 Finished recover at 25-MAR-23 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script deleted archived log archived log file name=D:\RUPESH\SETUPS\DR\ARCHIVE\1_239_1084451655.ARC RECID=1 STAMP=1132443963 Deleted 1 objects deleted archived log archived log file name=D:\RUPESH\SETUPS\DR\ARCHIVE\1_240_1084451655.ARC RECID=2 STAMP=1132443963 Deleted 1 objects Finished Duplicate Db at 25-MAR-23 released channel: d1 released channel: d2 released channel: a1 released channel: a2 RMAN> exit Recovery Manager complete. C:\windows\system32> SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PO MOUNTED PHYSICAL STANDBY Primary: SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- ----------- ------------- PO READ WRITE PRIMARY SQL> select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- --------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 32 rows selected. Below FAL parameters are optional which are not mandatory. I have not set these parameters. SQL> show parameter fal NAME TYPE VALUE -------------- ----------- ------ fal_client string fal_server string SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 241 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> SQL> / System altered. SQL> / System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 247 Standby: SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- ----------- ---------------- PO MOUNTED PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 240 SQL> / MAX(SEQUENCE#) -------------- 240 SQL> / MAX(SEQUENCE#) -------------- 240 SQL> / MAX(SEQUENCE#) -------------- 240 SQL> / MAX(SEQUENCE#) -------------- 240 SQL> select process,status from v$managed_standby; PROCESS STATUS --------- ------------ DGRD ALLOCATED ARCH CONNECTED DGRD ALLOCATED ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED RFS IDLE RFS IDLE RFS IDLE 9 rows selected. SQL> recover managed standby database disconnect from session; Media recovery complete. SQL> select process,status from v$managed_standby; PROCESS STATUS --------- ------------ DGRD ALLOCATED ARCH CONNECTED DGRD ALLOCATED ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED RFS IDLE RFS IDLE RFS IDLE MRP0 WAIT_FOR_LOG SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 247 |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Thank you for your comment !