Create RAC Physical Standby Database using RMAN Active Duplicate Command
Table of Contents
___________________________________________________________________________________________________
On Primary (Step 3 to Step 9)
3. Enable Forced Logging on Primary
4. Copy Password File from Primary to standby
5. Configure Standby Redo Log on Primary
6. Verify Archive Mode Enabled on Primary
7. Set Primary Database Initialization Parameters
8. Configure LISTENER Entries on Primary
9. Configure TNS Entries on Primary
On STANDBY (Step 10 to Step 22)
10. Set Standby Database Initialization Parameters
11. Create required directories on Standby
12. Add below entry in ORATAB on Standby
13. Startup nomount
14. Configure LISTENER Entries on Standby
15. Configure TNS Entries on Standby
16. Verify TNS connectvity
17. Run the duplicate command
18. Verify Standby redo logs
19. Create spfile
20. Add init parameters for Instance 2 (DELL_DG2)
21. Add database to OCR
22. Enable MRP on Standby
Verification
___________________________________________________________________________________________________
AIM:Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database) Active Data Guard is a new option from Oracle Database 11g Enterprise Edition. PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files. Definition of Active Dataguard: Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.
Primary RAC cluster : rac-cluster
Platform : Linuxx86_64 Server Name : RAC1.RAJASEKHAR.COM,RAC2.RAJASEKHAR.COM DB Version : Oracle 12.2.0.1 File system : ASM Disk Groups : +DATA,+FRA Database Name : DELL DB_UNIQUE_NAME : DELL INSTANCES : DELL1,DELL2 Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/12.2.0/dbhome_1 Primary Cluster Status: [oracle@rac1 ~]$ crsctl check cluster -all ************************************************************** rac1: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** rac2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** [oracle@rac1 ~]$ [oracle@rac1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.DATA.dg ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.FRA.dg ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.LISTENER.lsnr ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.net1.network ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.ons ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.proxy_advm OFFLINE OFFLINE rac1 STABLE OFFLINE OFFLINE rac2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE rac2 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE rac1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE rac1 STABLE ora.asm 1 ONLINE ONLINE rac1 Started,STABLE 2 ONLINE ONLINE rac2 Started,STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE rac1 STABLE ora.dell.db 1 ONLINE ONLINE rac1 Open,HOME=/u01/app/o racle/product/12.2.0 /dbhome_1,STABLE 2 ONLINE ONLINE rac2 Open,HOME=/u01/app/o racle/product/12.2.0 /dbhome_1,STABLE ora.qosmserver 1 OFFLINE OFFLINE STABLE ora.rac1.vip 1 ONLINE ONLINE rac1 STABLE ora.rac2.vip 1 ONLINE ONLINE rac2 STABLE ora.scan1.vip 1 ONLINE ONLINE rac2 STABLE ora.scan2.vip 1 ONLINE ONLINE rac1 STABLE ora.scan3.vip 1 ONLINE ONLINE rac1 STABLE -------------------------------------------------------------------------------- [oracle@rac1 ~]$
Standby RAC Cluster: racdg-cluster
Platform : Linuxx86_64 Server Name : RAC1.RAJASEKHAR.COM,RAC2.RAJASEKHAR.COM DB Version : Oracle 12.2.0.1 File system : ASM Disk Groups : +DATA,+DATA_DG Database Name : DELL DB_UNIQUE_NAME : DELL_DG INSTANCES : DELL_DG1,DELL_DG2 Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/12.2.0/dbhome_1 Standby Cluster Status [grid@racdg1 ~]$ crsctl check cluster -all ************************************************************** racdg1: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** racdg2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** [grid@racdg1 ~]$ [grid@racdg1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.DATA.dg ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.DATA_DG.dg ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.LISTENER.lsnr ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.net1.network ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.ons ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.proxy_advm OFFLINE OFFLINE racdg1 STABLE OFFLINE OFFLINE racdg2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE racdg2 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE racdg1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE racdg1 STABLE ora.asm 1 ONLINE ONLINE racdg1 Started,STABLE 2 ONLINE ONLINE racdg2 Started,STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE racdg1 STABLE ora.qosmserver 1 OFFLINE OFFLINE STABLE ora.racdg1.vip 1 ONLINE ONLINE racdg1 STABLE ora.racdg2.vip 1 ONLINE ONLINE racdg2 STABLE ora.scan1.vip 1 ONLINE ONLINE racdg2 STABLE ora.scan2.vip 1 ONLINE ONLINE racdg1 STABLE ora.scan3.vip 1 ONLINE ONLINE racdg1 STABLE -------------------------------------------------------------------------------- [grid@racdg1 ~]$
On Primary (Step 3 to Step 12)
3. Enable Forced Logging on Primary
SQL> select name, open_mode,cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- DELL READ WRITE NO SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO <--------- SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES <------- SQL>
4. Copy Password File from Primary to standby
ASMCMD> pwd +data/dell/password ASMCMD> pwcopy pwddell.258.1000514183 /tmp copying +data/dell/password/pwddell.258.1000514183 -> /tmp/pwddell.258.1000514183 ASMCMD> [root@rac2 ~]# cd /tmp [root@rac2 tmp]# ls -ltr pwddell.258.1000514183 -rw-r-----. 1 grid oinstall 2048 Feb 20 13:16 pwddell.258.1000514183 [root@rac2 tmp]# [root@rac2 tmp]# chown oracle:oinstall pwddell.258.1000514183 [oracle@rac2 tmp]$ ls -ltr pwddell.258.1000514183 -rw-r-----. 1 oracle oinstall 2048 Feb 20 13:16 pwddell.258.1000514183 [oracle@rac2 tmp]$ [oracle@rac2 tmp]$ scp -p pwddell.258.1000514183 oracle@racdg1:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwDELL_DG1 [oracle@rac2 tmp]$ scp -p pwddell.258.1000514183 oracle@racdg2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwDELL_DG2
5. Configure Standby Redo Log on Primary
The standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is: (maximum # of logfiles +1) * maximum # of threads This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread. -- Standy Redo logs created in the primary and RMAN will create them in standby automatically while running duplicate command. -- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection. SQL> set lines 180 col MEMBER for a60 select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;SQL> SQL> THREAD# GROUP# MEMBER BYTES ---------- ---------- ------------------------------------------------------------ ---------- 1 2 +DATA/DELL/redo02.log 209715200 1 1 +DATA/DELL/redo01.log 209715200 2 3 +DATA/DELL/redo03.log 209715200 2 4 +DATA/DELL/redo04.log 209715200 SQL> SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+DATA/DELL/redo05.log') SIZE 200M, GROUP 6 ('+DATA/DELL/redo06.log') SIZE 200M, GROUP 7 ('+DATA/DELL/redo07.log') SIZE 200M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 ('+DATA/DELL/redo08.log') SIZE 200M, GROUP 9 ('+DATA/DELL/redo09.log') SIZE 200M, GROUP 10 ('+DATA/DELL/redo10.log') SIZE 200M; Database altered. SQL> SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- -------------------------- --- ---------- 2 ONLINE +DATA/DELL/redo02.log NO 0 1 ONLINE +DATA/DELL/redo01.log NO 0 3 ONLINE +DATA/DELL/redo03.log NO 0 4 ONLINE +DATA/DELL/redo04.log NO 0 5 STANDBY +DATA/DELL/redo05.log NO 0 6 STANDBY +DATA/DELL/redo06.log NO 0 7 STANDBY +DATA/DELL/redo07.log NO 0 8 STANDBY +DATA/DELL/redo08.log NO 0 9 STANDBY +DATA/DELL/redo09.log NO 0 10 STANDBY +DATA/DELL/redo10.log NO 0 10 rows selected. SQL> SQL> select b.thread#,a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; THREAD# GROUP# MEMBER BYTES ---------- ---------- ------------------------------------------------------------ ---------- 1 5 +DATA/DELL/redo05.log 209715200 1 6 +DATA/DELL/redo06.log 209715200 1 7 +DATA/DELL/redo07.log 209715200 2 8 +DATA/DELL/redo08.log 209715200 2 9 +DATA/DELL/redo09.log 209715200 2 10 +DATA/DELL/redo10.log 209715200 6 rows selected. SQL>
6. Verify Archive Mode Enabled on Primary
SQL> archive log list
Database log mode Archive Mode <------
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 5
Next log sequence to archive 6
Current log sequence 6
SQL>
7. Set Primary Database Initialization Parameters
SQL> create pfile='/home/oracle/initDELL.ora.bkp' from spfile; File created. SQL> alter system set db_unique_name='DELL' scope=spfile sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DELL,DELL_DG)' scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL' scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DELL_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL_DG' scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET fal_client=DELL scope=both sid='*'; System altered. SQL> Please note: The FAL_CLIENT database initialization parameter is no longer required from 11gR2 SQL> ALTER SYSTEM SET fal_server=DELL_DG scope=both sid='*'; System altered. SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA_DG','+DATA' SCOPE=SPFILE sid='*'; System altered. SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA_DG','+DATA' SCOPE=SPFILE sid='*'; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*'; System altered. SQL> create pfile='/home/oracle/initDELL.ora' from spfile; File created. SQL> [oracle@rac1 ~]$ cat /home/oracle/initDELL.ora DELL1.__data_transfer_cache_size=0 DELL2.__data_transfer_cache_size=0 DELL2.__db_cache_size=541065216 DELL1.__db_cache_size=520093696 DELL1.__inmemory_ext_roarea=0 DELL2.__inmemory_ext_roarea=0 DELL1.__inmemory_ext_rwarea=0 DELL2.__inmemory_ext_rwarea=0 DELL1.__java_pool_size=4194304 DELL2.__java_pool_size=4194304 DELL1.__large_pool_size=8388608 DELL2.__large_pool_size=8388608 DELL1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL1.__pga_aggregate_target=301989888 DELL2.__pga_aggregate_target=301989888 DELL1.__sga_target=905969664 DELL2.__sga_target=905969664 DELL2.__shared_io_pool_size=37748736 DELL1.__shared_io_pool_size=37748736 DELL2.__shared_pool_size=301989888 DELL1.__shared_pool_size=322961408 DELL1.__streams_pool_size=0 DELL2.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/DELL/adump' *.audit_trail='db' *.cluster_database=true *.compatible='12.2.0' *.control_files='+DATA/DELL/control01.ctl','+DATA/DELL/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='+DATA_DG','+DATA' *.db_name='DELL' *.db_recovery_file_dest='+FRA' *.db_recovery_file_dest_size=8016m *.db_unique_name='DELL' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DELLXDB)' *.fal_client='DELL' *.fal_server='DELL_DG' family:dw_helper.instance_mode='read-only' DELL1.instance_number=1 DELL2.instance_number=2 *.local_listener='-oraagent-dummy-' *.log_archive_config='DG_CONFIG=(DELL,DELL_DG)' *.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL' *.log_archive_dest_2='SERVICE=DELL_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL_DG' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='+DATA_DG','+DATA' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=288m *.processes=300 *.remote_listener='rac-scan:1622' *.remote_login_passwordfile='exclusive' *.sga_target=864m *.standby_file_management='AUTO' DELL2.thread=2 DELL1.thread=1 DELL2.undo_tablespace='UNDOTBS2' DELL1.undo_tablespace='UNDOTBS1' [oracle@rac1 ~]$
8. Configure LISTENER Entries on Primary
[oracle@rac1 ~]$ ps -ef | grep tns root 15 2 0 11:31 ? 00:00:00 [netns] grid 6429 1 0 11:33 ? 00:00:03 /u01/app/12.2.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit grid 6451 1 0 11:33 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit grid 6453 1 0 11:33 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit grid 6477 1 0 11:33 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit oracle 31300 16939 0 15:07 pts/0 00:00:00 grep tns [oracle@rac1 ~]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2019 15:07:47 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1622)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 20-FEB-2019 11:33:28 Uptime 0 days 3 hr. 34 min. 19 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.203)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_FRA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "DELL" has 1 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... Service "DELLXDB" has 1 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN3 LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2019 15:37:28 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN3 Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 20-FEB-2019 11:33:28 Uptime 0 days 4 hr. 4 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener_scan3/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.207)(PORT=1622))) Services Summary... Service "DELL" has 2 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... Instance "DELL2", status READY, has 1 handler(s) for this service... Service "DELLXDB" has 2 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... Instance "DELL2", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN2 LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2019 15:37:40 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN2 Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 20-FEB-2019 11:33:29 Uptime 0 days 4 hr. 4 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener_scan2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.206)(PORT=1622))) Services Summary... Service "DELL" has 2 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... Instance "DELL2", status READY, has 1 handler(s) for this service... Service "DELLXDB" has 2 instance(s). Instance "DELL1", status READY, has 1 handler(s) for this service... Instance "DELL2", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ cat /u01/app/12.2.0/grid/network/admin/listener.ora LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent # listener.ora Network Configuration File: /u01/app/12.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1 = OFF VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM = SUBNET ASMNET1LSNR_ASM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = ASMNET1LSNR_ASM)) ) ) VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent [oracle@rac1 ~]$ [grid@rac2 admin]$ cat listener.ora <--- 2nd node of Primary LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent REGISTRATION_INVITED_NODES_LISTENER_SCAN1=() # line added by Agent REGISTRATION_INVITED_NODES_LISTENER=() # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent [grid@rac2 admin]$
9. Configure TNS Entries on Primary
[oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdg-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_DG)(UR=A) ) ) [oracle@rac1 admin]$ [oracle@rac2 admin]$ cat tnsnames.ora <--- 2nd node of Primary # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdg-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_DG)(UR=A) ) ) [oracle@rac2 admin]$ [oracle@rac1 ~]$ tnsping dell TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2019 15:24:20 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL))) OK (10 msec) [oracle@rac1 ~]$ [oracle@rac1 ~]$ tnsping dell_dg TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 20-FEB-2019 16:26:23 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdg-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_DG)(UR=A))) OK (0 msec) [oracle@rac1 ~]$
On STANDBY (Step 10 – Step 22)
10. Set Standby Database Initialization Parameters
[oracle@racdg1 dbs]$ cat initDELL_DG1.ora DELL_DG1.__data_transfer_cache_size=0 DELL_DG1.__db_cache_size=520093696 DELL_DG1.__inmemory_ext_roarea=0 DELL_DG1.__inmemory_ext_rwarea=0 DELL_DG1.__java_pool_size=4194304 DELL_DG1.__large_pool_size=8388608 DELL_DG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL_DG1.__pga_aggregate_target=301989888 DELL_DG1.__sga_target=905969664 DELL_DG1.__shared_io_pool_size=37748736 DELL_DG1.__shared_pool_size=322961408 DELL_DG1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/DELL_DG/adump' *.audit_trail='db' *.cluster_database=false *.compatible='12.2.0' *.control_files='+DATA_DG/DELL_DG/control01.ctl','+DATA_DG/DELL_DG/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='+DATA/DELL','+DATA_DG/DELL_DG' *.db_name='DELL' *.db_recovery_file_dest='+DATA_DG' *.db_recovery_file_dest_size=8016m *.db_unique_name='DELL_DG' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DELL_DGXDB)' *.fal_client='DELL_DG' *.fal_server='DELL' family:dw_helper.instance_mode='read-only' *.instance_name='DELL_DG1' DELL_DG1.instance_number=1 *.log_archive_config='DG_CONFIG=(DELL,DELL_DG)' *.log_archive_dest_1='LOCATION=+DATA_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_DG' *.log_archive_dest_2='SERVICE=DELL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='+DATA/DELL','+DATA_DG/DELL_DG' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=288m *.processes=300 *.remote_listener='racdg-scan:1622' *.remote_login_passwordfile='exclusive' *.sga_target=864m *.standby_file_management='AUTO' DELL_DG1.thread=1 DELL_DG1.undo_tablespace='UNDOTBS1' [oracle@racdg1 dbs]$
11. Crete required directories on Standby
[oracle@racdg1 ~]$ mkdir -p /u01/app/oracle/admin/DELL_DG/adump [oracle@racdg2 ~]$ mkdir -p /u01/app/oracle/admin/DELL_DG/adump
12. Add below entry in ORATAB on Standby
[oracle@racdg1 ~]$ echo "DELL:/u01/app/oracle/product/12.2.0/dbhome_1:N" >> /etc/oratab [oracle@racdg1 ~]$ echo "DELL_DG1:/u01/app/oracle/product/12.2.0/dbhome_1:N" >> /etc/oratab [oracle@racdg2 ~]$ echo "DELL:/u01/app/oracle/product/12.2.0/dbhome_1:N" >> /etc/oratab [oracle@racdg2 ~]$ echo "DELL_DG2:/u01/app/oracle/product/12.2.0/dbhome_1:N" >> /etc/oratab
SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initDELL_DG1.ora';
ORACLE instance started.
Total System Global Area 905969664 bytes
Fixed Size 8627008 bytes
Variable Size 348130496 bytes
Database Buffers 545259520 bytes
Redo Buffers 3952640 bytes
SQL>
14. Configure LISTENER Entries on Standby
[oracle@racdg1 ~]$ ps -ef | grep tns root 15 2 0 11:36 ? 00:00:00 [netns] oracle 2239 31551 0 15:38 pts/0 00:00:00 grep tns grid 6070 1 0 11:38 ? 00:00:04 /u01/app/12.2.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit grid 6090 1 0 11:38 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit grid 6099 1 0 11:38 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit grid 6122 1 0 11:38 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit [oracle@racdg1 ~]$ [grid@racdg1 ~]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-FEB-2019 23:55:46 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 23-FEB-2019 12:52:44 Uptime 0 days 11 hr. 3 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/racdg1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.103)(PORT=1621))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.105)(PORT=1621))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA_DG" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "DELL_DG" has 1 instance(s). Instance "DELL_DG1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [grid@racdg1 ~]$ [grid@racdg1 ~]$ cat /u01/app/12.2.0/grid/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/12.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3 = OFF VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2 = OFF SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DELL_DG) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = DELL_DG) ) ) VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1 = OFF VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM = SUBNET ASMNET1LSNR_ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = ASMNET1LSNR_ASM)) ) VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) ) ADR_BASE_LISTENER = /u01/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON ADR_BASE_ASMNET1LSNR_ASM = /u01/app/grid LISTENER_SCAN3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3)) ) LISTENER_SCAN2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2)) ) ADR_BASE_LISTENER_SCAN3 = /u01/app/grid LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)) ) ADR_BASE_LISTENER_SCAN2 = /u01/app/grid ADR_BASE_LISTENER_SCAN1 = /u01/app/grid [grid@racdg1 ~]$ [grid@racdg2 admin]$ cat listener.ora <--- 2nd of standby LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent # listener.ora Network Configuration File: /u01/app/12.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools. VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM = SUBNET ASMNET1LSNR_ASM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = ASMNET1LSNR_ASM)) ) ) VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent REGISTRATION_INVITED_NODES_LISTENER_SCAN1=() # line added by Agent REGISTRATION_INVITED_NODES_LISTENER=() # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent [grid@racdg2 admin]$
15. Configure TNS Entries on Standby
[oracle@racdg1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdg-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_DG) (UR=A) ) ) [oracle@racdg1 admin]$ [oracle@racdg2 admin]$ cat tnsnames.ora <--- 2nd node of Standby # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdg-scan)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_DG) (UR=A) ) ) [oracle@racdg2 admin]$
On Primary
[oracle@rac1 ~]$ sqlplus sys/sys@dell as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 16:28:46 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> [oracle@rac1 ~]$ sqlplus sys/sys@dell_dg as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 16:28:54 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>
On Standby
[oracle@racdg1 ~]$ sqlplus sys/sys@dell as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 16:29:28 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> [oracle@racdg1 ~]$ sqlplus sys/sys@dell_dg as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 16:29:35 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>
Please note DB_CREATE_FILE_DEST parameter cannot be set together with DB_FILE_NAME_CONVERT during RMAN active duplication. [oracle@racdg1 ~]$ rman target sys/sys@DELL auxiliary sys/sys@DELL_DG Recovery Manager: Release 12.2.0.1.0 - Production on Sat Feb 23 23:41:12 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DELL (DBID=3971311101) connected to auxiliary database: DELL (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 23-FEB-19 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=50 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '+DATA/DELL/PASSWORD/pwddell.260.1000570117' auxiliary format '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwDELL_DG1' ; } executing Memory Script Starting backup at 23-FEB-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 instance=DELL1 device type=DISK Finished backup at 23-FEB-19 contents of Memory Script: { restore clone from service 'DELL1' standby controlfile; } executing Memory Script Starting restore at 23-FEB-19 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+DATA_DG/DELL_DG/control01.ctl output file name=+DATA_DG/DELL_DG/control02.ctl Finished restore at 23-FEB-19 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 "+DATA_DG/DELL_DG/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "+DATA_DG/DELL_DG/system01.dbf"; set newname for datafile 3 to "+DATA_DG/DELL_DG/sysaux01.dbf"; set newname for datafile 4 to "+DATA_DG/DELL_DG/undotbs01.dbf"; set newname for datafile 5 to "+DATA_DG/DELL_DG/undotbs02.dbf"; set newname for datafile 7 to "+DATA_DG/DELL_DG/users01.dbf"; restore from nonsparse from service 'DELL1' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to +DATA_DG/DELL_DG/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 Starting restore at 23-FEB-19 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DG/DELL_DG/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DG/DELL_DG/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DG/DELL_DG/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DG/DELL_DG/undotbs02.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service DELL1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA_DG/DELL_DG/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 23-FEB-19 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=1 STAMP=1001029332 file name=+DATA_DG/DELL_DG/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=1001029332 file name=+DATA_DG/DELL_DG/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=1001029332 file name=+DATA_DG/DELL_DG/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=1001029332 file name=+DATA_DG/DELL_DG/undotbs02.dbf datafile 7 switched to datafile copy input datafile copy RECID=5 STAMP=1001029332 file name=+DATA_DG/DELL_DG/users01.dbf Finished Duplicate Db at 23-FEB-19 RMAN>
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------ --- ---------- 2 ONLINE +DATA_DG/DELL_DG/redo02.log NO 0 1 ONLINE +DATA_DG/DELL_DG/redo01.log NO 0 3 ONLINE +DATA_DG/DELL_DG/redo03.log NO 0 4 ONLINE +DATA_DG/DELL_DG/redo04.log NO 0 5 STANDBY +DATA_DG/DELL_DG/redo05.log NO 0 6 STANDBY +DATA_DG/DELL_DG/redo06.log NO 0 7 STANDBY +DATA_DG/DELL_DG/redo07.log NO 0 8 STANDBY +DATA_DG/DELL_DG/redo08.log NO 0 9 STANDBY +DATA_DG/DELL_DG/redo09.log NO 0 10 STANDBY +DATA_DG/DELL_DG/redo10.log NO 0 10 rows selected. SQL> select b.thread#,a.group#, a.type, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; THREAD# GROUP# TYPE MEMBER BYTES ---------- ---------- ------- ------------------------------------------------------------ ---------- 1 5 STANDBY +DATA_DG/DELL_DG/redo05.log 209715200 1 6 STANDBY +DATA_DG/DELL_DG/redo06.log 209715200 1 7 STANDBY +DATA_DG/DELL_DG/redo07.log 209715200 2 8 STANDBY +DATA_DG/DELL_DG/redo08.log 209715200 2 9 STANDBY +DATA_DG/DELL_DG/redo09.log 209715200 2 10 STANDBY +DATA_DG/DELL_DG/redo10.log 209715200 6 rows selected. SQL>
SQL> create spfile='+DATA_DG/DELL_DG/PARAMETERFILE/spfileDELL_DG.ora' from pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initDELL_DG1.ora'; File created. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> [oracle@racdg1 ~]$ cd $ORACLE_HOME/dbs [oracle@racdg1 dbs]$ ls -ltr initDELL_DG1.ora -rw-r--r--. 1 oracle oinstall 1802 Feb 23 23:40 initDELL_DG1.ora [oracle@racdg1 dbs]$ mv initDELL_DG1.ora initDELL_DG1.ora.bkp [oracle@racdg1 dbs]$ echo "SPFILE='+DATA_DG/DELL_DG/PARAMETERFILE/spfileDELL_DG.ora'" > initDELL_DG1.ora [oracle@racdg1 dbs]$ scp initDELL_DG1.ora oracle@racdg2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initDELL_DG2.ora initDELL_DG1.ora 100% 58 0.1KB/s 00:00 [oracle@racdg1 dbs]$ SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes Database mounted. SQL>
20. Add init parameters for Instance 2 (DELL_DG2)
SQL> alter system set undo_tablespace=UNDOTBS2 sid='DELL_DG2' scope=spfile; System altered. SQL> alter system set instance_number=1 sid='DELL_DG1' scope=spfile; System altered. SQL> alter system set instance_number=2 sid='DELL_DG2' scope=spfile; System altered. SQL> alter system set instance_name='DELL_DG1' sid='DELL_DG1' scope=spfile; System altered. SQL> alter system set instance_name='DELL_DG2' sid='DELL_DG2' scope=spfile; System altered. SQL> alter system set thread=1 sid='DELL_DG1' scope=spfile; System altered. SQL> alter system set thread=2 sid='DELL_DG2' scope=spfile; System altered. SQL> alter system set cluster_database=TRUE scope=spfile; System altered. SQL> alter system set remote_listener='racdg-scan:1622' scope=spfile; System altered. SQL> SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes Database mounted. SQL> SQL> select name,open_mode,database_role,cdb from v$database; NAME OPEN_MODE DATABASE_ROLE CDB --------- -------------------- ---------------- --- DELL MOUNTED PHYSICAL STANDBY NO SQL>
[oracle@racdg1 dbs]$ srvctl add database -db DELL_DG -oraclehome /u01/app/oracle/product/12.2.0/dbhome_1 -role physical_standby -startoption mount -spfile +DATA_DG/DELL_DG/PARAMETERFILE/spfileDELL_DG.ora [oracle@racdg1 dbs]$ [oracle@racdg1 dbs]$ srvctl add instance -db DELL_DG -instance DELL_DG1 -node racdg1 [oracle@racdg1 dbs]$ srvctl add instance -db DELL_DG -instance DELL_DG2 -node racdg2 [oracle@racdg1 dbs]$ [oracle@racdg1 dbs]$ srvctl start database -d DELL_DG [oracle@racdg1 dbs]$ srvctl status database -d DELL_DG Instance DELL_DG1 is running on node racdg1 Instance DELL_DG2 is running on node racdg2 [oracle@racdg1 dbs]$ [grid@racdg1 trace]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.DATA.dg ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.DATA_DG.dg ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.LISTENER.lsnr ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.net1.network ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.ons ONLINE ONLINE racdg1 STABLE ONLINE ONLINE racdg2 STABLE ora.proxy_advm OFFLINE OFFLINE racdg1 STABLE OFFLINE OFFLINE racdg2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE racdg2 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE racdg1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE racdg1 STABLE ora.asm 1 ONLINE ONLINE racdg1 Started,STABLE 2 ONLINE ONLINE racdg2 Started,STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE racdg1 STABLE ora.dell_dg.db 1 ONLINE INTERMEDIATE racdg1 Mounted (Closed),HOM E=/u01/app/oracle/pr oduct/12.2.0/dbhome_ 1,STABLE 2 ONLINE INTERMEDIATE racdg2 Mounted (Closed),HOM E=/u01/app/oracle/pr oduct/12.2.0/dbhome_ 1,STABLE ora.qosmserver 1 OFFLINE OFFLINE STABLE ora.racdg1.vip 1 ONLINE ONLINE racdg1 STABLE ora.racdg2.vip 1 ONLINE ONLINE racdg2 STABLE ora.scan1.vip 1 ONLINE ONLINE racdg2 STABLE ora.scan2.vip 1 ONLINE ONLINE racdg1 STABLE ora.scan3.vip 1 ONLINE ONLINE racdg1 STABLE -------------------------------------------------------------------------------- [grid@racdg1 trace]$
SQL> select name,open_mode,database_role,cdb from v$database; NAME OPEN_MODE DATABASE_ROLE CDB --------- -------------------- ---------------- --- DELL MOUNTED PHYSICAL STANDBY NO SQL> SQL> alter database recover managed standby database disconnect from session; Database altered. SQL>
On Primary
SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 65 <---- 2 49 <---- SQL> On Primary Instance 1: SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> On Primary Instance 2: SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 69 <---- 2 53 <---- SQL>
On Standby
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 68 <------ 2 53 <------ SQL> SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 69 69 0 <--- 2 53 53 0 <--- SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/