How to build ACTIVE STANDBY DATABASE using RMAN DUPLICATE command in ORACLE DATABASE 12c.
Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 12cR1 active standby setup using RMAN duplicate method posted here.
Some key points before proceeding with the ACTIVE STANDBY setup.
- Primary database should be in archivelog mode.
- Initialization parameter “db_name” should be same on both primary and standby database.
- Initialization parameter “db_unique_name” should be different on primary and standby databases.
Primary Database : APACPROD(DBA13)
Standby Database : APACSTAND(DBA12)
1.Checking Primary database name & locations.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[oracle@dba13 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 4 18:53:19 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ---------------------------------------- -------------------- ---------------- APACPROD READ WRITE PRIMARY SYS>>select name from v$controlfile; NAME ---------------------------------------- /u01/oradata/apacprod/control.ctl SYS>>select instance_name from v$instance; Inst-Name ---------- apacprod |
2.We need to configure Oracle Net Services from both DBA12 and DBA13 servers.
At Primary Side : configure listener.ora
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[oracle@dba13 ~]$ vi listener.ora apacprod = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.13 )(PORT = 9988)) ) ) ) SID_LIST_apacprod= (SID_LIST = (SID_DESC = (SID_NAME = apacprod) (ORACLE_HOME = /oraeng/app/oracle/product/12.1.0) ) ) |
Start the listener.
|
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 |
[oracle@dba13 ~]$ lsnrctl start apacprod LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2018 18:59:24 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /oraeng/app/oracle/product/12.1.0/bin/tnslsnr: please wait.. TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /home/oracle/listener.ora Log messages written to /oraeng/app/oracle/diag/tnslsnr/dba13/apacprod/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.13)(PORT=9988))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.13)(PORT=9988))) STATUS of the LISTENER ------------------------ Alias apacprod Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 04-JUN-2018 18:59:25 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/listener.ora Listener Log File /oraeng/app/oracle/diag/tnslsnr/dba13/apacprod/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.13)(PORT=9988))) Services Summary... Service "apacprod" has 1 instance(s). Instance "apacprod", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
At Standby side : Configure tnsnames.ora and check whether its pings to primary listener or not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@dba12 ~]$ vi tnsnames.ora to_apacprod = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13 )(PORT = 9988)) ) (CONNECT_DATA = (SID = apacprod) ) ) [oracle@dba12 ~]$ tnsping to_apacprod TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2018 19:02:09 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 9988))) (CONNECT_DATA = (SID = apacprod))) OK (70 msec) |
Export new SID then configure and start the listener at same side.
|
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 33 34 35 36 37 38 39 40 41 42 43 44 |
[oracle@dba12 ~]$ export ORACLE_SID=apacstand [oracle@dba12 ~]$ vi listener.ora apacstand= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.12 )(PORT =8899)) ) ) ) SID_LIST_apacstand= (SID_LIST = (SID_DESC = (SID_NAME =apacstand) (ORACLE_HOME = /oraeng/app/oracle/product/12.1.0) ) ) [oracle@dba12 ~]$ lsnrctl start apacstand LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2018 19:02:48 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /oraeng/app/oracle/product/12.1.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /home/oracle/listener.ora Log messages written to /oraeng/app/oracle/diag/tnslsnr/dba12/apacstand/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.12)(PORT=8899))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.12)(PORT=8899))) STATUS of the LISTENER ------------------------ Alias apacstand Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 04-JUN-2018 19:02:49 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/listener.ora Listener Log File /oraeng/app/oracle/diag/tnslsnr/dba12/apacstand/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.12)(PORT=8899))) Services Summary... Service "apacstand" has 1 instance(s). Instance "apacstand", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
At Primary Side : Configure tnsnames.ora and check whether its pings to standby listener or not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@dba13 ~]$ vi tnsnames.ora to_apacstand= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12 )(PORT = 8899)) ) (CONNECT_DATA = (SID = apacstand) ) ) [oracle@dba13 ~]$ tnsping to_apacstand TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2018 19:04:28 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 8899))) (CONNECT_DATA = (SID = apacstand))) OK (50 msec) |
At Primary Side :
3.Create a directory structure to store Initialization parameter file,password file & standby controlfile.
|
1 |
[oracle@dba13 ~]$ mkdir -p /u01/oradata/apacprod/dg |
4. Create standby controlfile using below command.
|
1 2 3 4 5 6 7 8 |
[oracle@dba13 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 4 19:58:24 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>alter database create standby controlfile as '/u01/oradata/apacprod/dg/stand.ctl'; Database altered. |
5.Modify Initialization parameter file .
|
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 |
[oracle@dba13 ~]$ cd $ORACLE_HOME/dbs [oracle@dba13 dbs]$ vi initapacprod.ora db_name=apacprod db_unique_name=apacprod_stand #memory_target=1G processes = 150 #audit_file_dest='<ORACLE_BASE>/admin/orcl/adump' audit_trail ='db' db_block_size=8192 db_domain='' #db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area' #db_recovery_file_dest_size=2G diagnostic_dest=/u01/oradata/apacprod dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS'# You may want to ensure that control files are created on separate physical # devices log_archive_dest_1='location=/u01/oradata/apacprod/arch' log_archive_dest_2='service=to_apacstand lgwr sync affirm reopen=3' standby_file_management=auto fal_server=to_apacstand fal_client=to_apacprod db_file_name_convert='/u02/oradata/apacstand', '/u01/oradata/apacprod' log_file_name_convert='/u02/oradata/apacstand', '/u01/oradata/apacprod' control_files =/u01/oradata/apacprod/control.ctl compatible ='12.1.0' |
6.Create password file and store at ‘/u01/oradata/apacpord/dg’ location.
|
1 |
[oracle@dba13 dbs]$ orapwd file=orapw$ORACLE_SID password=sys force=y |
|
1 |
[oracle@dba13 dbs]$ cp orapwapacprod initapacprod.ora /u01/oradata/apacprod/dg |
7. Send Intialization parameter file ,standby control file & password file to standby server.
|
1 2 3 4 5 6 7 8 |
[oracle@dba13 ~]$ cd /u01/oradata/apacprod/dg/ [oracle@dba13 dg]$ ls initapacprod.ora orapwapacprod stand.ctl [oracle@dba13 dg]$ scp * oracle@192.168.0.12:/u02/oradata/apacstand oracle@192.168.0.12's password: initapacprod.ora 100% 3051 3.0KB/s 00:00 orapwapacprod 100% 7680 7.5KB/s 00:00 stand.ctl 100% 8580 7.4KB/s 00:00 |
At Standby side :
8.Move the pfile and passwd file from primary instance name to standby instance name and modify pfile with required parameters.
|
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 |
[oracle@dba12 apacprod]$ ls initapacstand.ora orapwapacstand stand.ctl [oracle@dba12 apacprod]$ mv initapacprod.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora [oracle@dba12 apacprod]$ mv orapwapacprod $ORACLE_HOME/dbs/orapw$ORACLE_SID [oracle@dba12 apacprod]$ cd $ORACLE_HOME/dbs [oracle@dba12 dbs]$ vi initapacstand.ora db_name=apacprod db_unique_name=apacstand #memory_target=1G processes = 150 #audit_file_dest='<ORACLE_BASE>/admin/orcl/adump' audit_trail ='db' db_block_size=8192 db_domain='' #db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area' #db_recovery_file_dest_size=2G diagnostic_dest=/u01/oradata/apacprod dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS' # You may want to ensure that control files are created on separate physical log_archive_dest_1='location=/u01/oradata/apacprod/arch' log_archive_dest_2='service=to_apacprod lgwr sync affirm reopen=3' standby_file_management=auto fal_server=to_apacprod fal_client=to_apacstand db_file_name_convert='/u01/oradata/apacprod','/u02/oradata/apacstand' log_file_name_convert='/u01/oradata/apacprod','/u02/oradata/apacstand' control_files =/u01/oradata/apacprod/stand.ctl compatible ='12.1.0' The command completed successfully |
9. Connect to sqlplus and keep database in nomount state.
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@dba12 dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 5 14:50:42 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SYS>>startup nomount ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes |
At Primary Side :
10.Connect to RMAN prompt using auxiliary Oracle net services and issue below Duplicate command.
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
[oracle@dba13 ~]$ rman target sys/sys auxiliary sys/sys@to_apacstand Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 5 14:58:16 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: APACPROD (DBID=2487874075) connected to auxiliary database: APACPROD (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 05-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=23 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=24 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/oraeng/app/oracle/product/12.1.0/dbs/orapwapacprod' auxiliary format '/oraeng/app/oracle/product/12.1.0/dbs/orapwapacstand' ; } executing Memory Script Starting backup at 05-JUN-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=58 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=60 device type=DISK Finished backup at 05-JUN-18 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/oradata/apacprod/stand.ctl'; } executing Memory Script Starting backup at 05-JUN-1 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/oradata/apacprod/rman/snapcf_apacprod.f tag=TAG20180605T151139 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 05-JUN-18 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 "/u01/oradata/apacprod/temp.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/oradata/apacprod/system.dbf"; set newname for datafile 2 to "/u01/oradata/apacprod/sysaux.dbf"; set newname for datafile 3 to "/u01/oradata/apacprod/undotbs.dbf"; set newname for datafile 4 to "/u01/oradata/apacprod/userdata.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/oradata/apacprod/system.dbf" datafile 2 auxiliary format "/u01/oradata/apacprod/sysaux.dbf" datafile 3 auxiliary format "/u01/oradata/apacprod/undotbs.dbf" datafile 4 auxiliary format "/u01/oradata/apacprod/userdata.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/oradata/apacprod/temp.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 05-JUN-18 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/oradata/apacprod/system.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00002 name=/u01/oradata/apacprod/sysaux.dbf output file name=/u01/oradata/apacprod/system.dbf tag=TAG20180605T151151 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=/u01/oradata/apacprod/undotbs.dbf output file name=/u01/oradata/apacprod/sysaux.dbf tag=TAG20180605T151151 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:36 channel ORA_DISK_2: starting datafile copy input datafile file number=00004 name=/u01/oradata/apacprod/userdata.db output file name=/u01/oradata/apacprod/undotbs.dbf tag=TAG20180605T151151 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08 output file name=/u01/oradata/apacprod/userdata.dbf tag=TAG20180605T151151 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16 Finished backup at 05-JUN-18 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=978016367 file name=/u01/oradata/apacprod/system.dbf datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=978016367 file name=/u01/oradata/apacprod/sysaux.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=978016367 file name=/u01/oradata/apacprod/undotbs.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=978016367 file name=/u01/oradata/apacprod/userdata.dbf Finished Duplicate Db at 05-JUN-18. |
At Primary Side :
11.Check archive log files at both primary and standby side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@dba13 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 5 15:18:35 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oradata/apacprod/arch Oldest online log sequence 251 Next log sequence to archive 252 Current log sequence 252 |
At Standby Side :
|
1 2 3 4 5 6 |
SYS>>select name,applied from v$archived_log; NAME APPLIED ---------------------------------------- --------- /u01/oradata/apacprod/arch/1_250_976404635.dbf NO /u01/oradata/apacprod/arch/1_249_976404635.dbf NO /u01/oradata/apacprod/arch/1_251_976404635.dbf NO |
12.Enable Managed recovery mode at standby side and check archive logs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SYS>>alter database recover managed standby database disconnect; Database altered. SYS>>select name,applied from v$archived_log; NAME APPLIED ---------------------------------------- --------- /u01/oradata/apacprod/arch/1_250_976404635.dbf YES /u01/oradata/apacprod/arch/1_249_976404635.dbf YES /u01/oradata/apacprod/arch/1_251_976404635.dbf YES SYS>>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 251 SYS>> select name,database_role,controlfile_type,open_mode from v$database. NAME DATABASE_ROLE CONTROLFILE_TYPE OPEN_MODE --------------------- ------------------ -------------------- ---------- APACPROD PHYSICAL STANDBY STANDBY MOUNTED |
13.Convert physical standby into active standby database.
|
1 2 3 4 5 6 7 8 |
SYS>>alter database recover managed standby database cancel; Database altered. SYS>> alter database open; Database altered. SYS>> select name,database_role,controlfile_type,open_mode from v$database. NAME DATABASE_ROLE CONTROLFILE_TYPE OPEN_MODE -------------- ----------------- ----------------- ------------------------------- APACPROD PHYSICAL STANDBY STANDBY READ ONLY WITH APPLY |
Note: Please test scripts in Non Prod before trying in Production.
(13 votes, average: 4.85 out of 5)
Loading...
Vinod
please go through the video https://vimeo.com/275067477 and share your valuable feedback in comments section.
Ajay Kumar
If you are interested to know more details about future session please join below telegram group :
https://t.me/joinchat/JFVAtAv1TE9DGHLbJ6rZbw
Bala
hi,
you are issuing duplicate target database for standby from active database from primary.
this command is supposed to be executed @ standby side right? please confirm