Creating Active Data Guard Physical Standby Environment
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 for Oracle Database 11g Enterprise Edition.
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.
0. Enviroment
1. Enable Forced Logging on Primary
2. Create Password File on Primary
3. Configure a Standby Redo Log on Primary
4. Verify Archive Mode Enabled on Primary
5. Set Primary Database Initialization Parameters
6. Transfer Spfile and Password file to Standby node
7. Set Standby Database Initialization Parameters
8. Crete required directories on Standby
9. Add below entry in ORATAB on Standby
10. Create spfile from pfile on Standby
11. Configure TNS Entries on Primary
12. Configure TNS Entries on Standby
13. Verify connection ‘AS SYSDBA’ from Primary
14. Create Standby Database
15. Enable MRP on Standby
16. Verify the Physical Standby Database Is Performing Properly
Source: Platform : Linuxx86_64 Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version : Oracle 11.2.0.3, File system: Normal DB Name : APAC, DB_UNIQUE_NAME: APAC Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/11.2.0/db_1 Target: Platform : Linuxx86_64 Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102 DB Version : Oracle 11.2.0.3, , File system: Normal DB Name : APAC, DB_UNIQUE_NAME: EMEA Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/11.2.0/db_1
1. Enable Forced Logging on Primary
SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- APAC READ WRITE SQL> select force_logging from v$database; FOR --- NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select force_logging from v$database; FOR --- YES <---- SQL>
2. Create Password File on Primary
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@rac1 dbs]$ orapwd file=orapwapac password=sys force=y
[oracle@rac1 dbs]$ ls -ltr orapwapac
-rw-r----- 1 oracle oinstall 1536 Jul 25 18:21 orapwapac
[oracle@rac1 dbs]$
3. Configure a Standby Redo Log on Primary
SQL> set lines 180 SQL> col MEMBER for a60 SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#; THREAD# GROUP# MEMBER BYTES ---------- ---------- ------------------------------------------------------------ ---------- 1 3 /u01/app/oracle/oradata/apac/redo03.log 52428800 1 2 /u01/app/oracle/oradata/apac/redo02.log 52428800 1 1 /u01/app/oracle/oradata/apac/redo01.log 52428800 SQL> SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/apac/redo04.log') SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/apac/redo05.log') SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/apac/redo06.log') SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/apac/redo07.log') SIZE 50M; Database altered. SQL> SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 3 ONLINE /u01/app/oracle/oradata/apac/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/apac/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/apac/redo01.log NO 4 STANDBY /u01/app/oracle/oradata/apac/redo04.log NO 5 STANDBY /u01/app/oracle/oradata/apac/redo05.log NO 6 STANDBY /u01/app/oracle/oradata/apac/redo06.log NO 7 STANDBY /u01/app/oracle/oradata/apac/redo07.log <-- NO 7 rows selected. SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- ------------------------------------------------------------ ---------- 4 /u01/app/oracle/oradata/apac/redo04.log 52428800 5 /u01/app/oracle/oradata/apac/redo05.log 52428800 6 /u01/app/oracle/oradata/apac/redo06.log 52428800 7 /u01/app/oracle/oradata/apac/redo07.log <----- 52428800 SQL>
4. Verify Archive Mode Enabled on Primary
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/apac
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
5. Set Primary Database Initialization Parameters
SQL> show parameter pfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileapac.ora SQL> create pfile='/home/oracle/initapac.ora' from spfile; File created. SQL> alter system set db_unique_name='apac' scope=spfile; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(apac,emea)' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both; System altered. SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_client=apac scope=both; System altered. SQL> ALTER SYSTEM SET fal_server=emea scope=both; System altered. SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered. SQL> SQL> create pfile='/home/oracle/initapac.ora' from spfile; File created. SQL> [oracle@rac1 ~]$ cat initapac.ora apac.__db_cache_size=339738624 apac.__java_pool_size=4194304 apac.__large_pool_size=4194304 apac.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment apac.__pga_aggregate_target=335544320 apac.__sga_target=503316480 apac.__shared_io_pool_size=0 apac.__shared_pool_size=146800640 apac.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/apac/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/apac/control01.ctl','/u01/app/oracle/oradata/apac/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac' *.db_name='apac' *.db_unique_name='apac' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=apacXDB)' *.fal_client='APAC' *.fal_server='EMEA' *.log_archive_config='DG_CONFIG=(apac,emea)' *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac' *.log_archive_dest_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=30 *.log_file_name_convert='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac' *.memory_target=838860800 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac1 ~]$
6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE
Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
[oracle@rac1 ~]$ scp initapac.ora oracle@rac2:/home/oracle/initemea.ora initapac.ora 100% 1487 1.5KB/s 00:00 [oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea [oracle@rac1 dbs]$ scp orapwapac oracle@rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/ orapwapac 100% 1536 1.5KB/s 00:00 [oracle@rac1 dbs]$
7. Set Standby Database Initialization Parameters
Edit shown highlighted to set it up for the standby role
[oracle@rac2 ~]$ cat initemea.ora emea.__db_cache_size=339738624 emea.__java_pool_size=4194304 emea.__large_pool_size=4194304 emea.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment emea.__pga_aggregate_target=335544320 emea.__sga_target=503316480 emea.__shared_io_pool_size=0 emea.__shared_pool_size=146800640 emea.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/emea/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/emea/control01.ctl','/u01/app/oracle/oradata/emea/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea','/u02/apac','/u02/emea' *.db_name='apac' *.db_unique_name='emea' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=emeaXDB)' *.fal_client='emea' *.fal_server='apac' *.log_archive_config='DG_CONFIG=(apac,emea)' *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/emea VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=emea' *.log_archive_dest_2='SERVICE=apac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=apac' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=30 *.log_file_name_convert='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea' *.memory_target=838860800 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac2 ~]$
8. Crete required directories on Standby
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/emea/adump [oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/emea [oracle@rac2 ~]$ mkdir -p /u01/app/oracle/arch/emea [oracle@rac2 ~]$ mkdir -p /u02/emea
9. Add below entry in ORATAB on Standby
emea:/u01/app/oracle/product/11.2.0/db_1:N
10. Create spfile from pfile on Standby
Once the new parameter file is ready we create from it the spfile:
[oracle@rac2 ~]$ . oraenv ORACLE_SID = [emca] ? emea The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac2 ~]$ [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:34:58 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/initemea.ora'; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2232960 bytes Variable Size 490737024 bytes Database Buffers 339738624 bytes Redo Buffers 2396160 bytes SQL> create spfile from pfile='/home/oracle/initemea.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2232960 bytes Variable Size 490737024 bytes Database Buffers 339738624 bytes Redo Buffers 2396160 bytes SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileemea.ora SQL>
11. Configure TNS Entries on Primary
[oracle@rac1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@rac1 admin]$ [oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. APAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac) ) ) EMEA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A) ) ) [oracle@rac1 admin]$ [oracle@rac1 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:22:10 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 25-JUL-2015 19:20:00 Uptime 0 days 0 hr. 2 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "apac" has 1 instance(s). Instance "apac", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ tnsping apac TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:14 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac))) OK (0 msec) [oracle@rac1 ~]$ [oracle@rac1 ~]$ tnsping emea TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:17 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A))) OK (0 msec) [oracle@rac1 ~]$
12. Configure TNS Entries on Standby
[oracle@rac2 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@rac2 admin]$ [oracle@rac2 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. APAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac) ) ) EMEA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A) ) ) [oracle@rac2 admin]$ [oracle@rac2 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:24:10 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 25-JUL-2015 19:22:24 Uptime 0 days 0 hr. 2 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.102)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "emea" has 1 instance(s). Instance "emea", status BLOCKED, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 ~]$ [oracle@rac2 ~]$ tnsping apac TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:17 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac))) OK (0 msec) [oracle@rac2 ~]$ [oracle@rac2 ~]$ tnsping emea TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:25 Copyright (c) 1997, 2011, 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.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A))) OK (10 msec) [oracle@rac2 ~]$
13. Verify connection ‘AS SYSDBA’ from Primary
[oracle@rac1 ~]$ sqlplus sys/sys@apac as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:37 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus sys/sys@emea as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:57 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@rac1 ~]$
[oracle@rac1 ~]$ rman target sys/sys@apac auxiliary sys/sys@emea Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 25 19:46:37 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: APAC (DBID=2914488844) connected to auxiliary database: APAC (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 25-JUL-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwapac' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea' ; } executing Memory Script Starting backup at 25-JUL-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=67 device type=DISK Finished backup at 25-JUL-15 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/emea/control01.ctl'; restore clone controlfile to '/u01/app/oracle/oradata/emea/control02.ctl' from '/u01/app/oracle/oradata/emea/control01.ctl'; } executing Memory Script Starting backup at 25-JUL-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_apac.f tag=TAG20150725T195200 RECID=1 STAMP=886017123 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 25-JUL-15 Starting restore at 25-JUL-15 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 25-JUL-15 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/app/oracle/oradata/emea/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/emea/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/emea/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/emea/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/emea/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/emea/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/emea/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/emea/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/emea/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/emea/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 25-JUL-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/apac/system01.dbf output file name=/u01/app/oracle/oradata/emea/system01.dbf tag=TAG20150725T195217 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/apac/sysaux01.dbf output file name=/u01/app/oracle/oradata/emea/sysaux01.dbf tag=TAG20150725T195217 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/apac/undotbs01.dbf output file name=/u01/app/oracle/oradata/emea/undotbs01.dbf tag=TAG20150725T195217 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/apac/users01.dbf output file name=/u01/app/oracle/oradata/emea/users01.dbf tag=TAG20150725T195217 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 25-JUL-15 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=886017341 file name=/u01/app/oracle/oradata/emea/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=2 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=3 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=4 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/users01.dbf Finished Duplicate Db at 25-JUL-15 RMAN>
[oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:58:37 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- APAC MOUNTED PHYSICAL STANDBY SQL> SQL> alter database open; Database altered. [oracle@rac2 ~]$ ps -ef | grep mrp oracle 1523 701 0 20:00 pts/1 00:00:00 grep mrp <---- [oracle@rac2 ~]$ SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- APAC READ ONLY WITH APPLY PHYSICAL STANDBY SQL> SQL> !ps -ef | grep mrp oracle 1533 1 0 20:01 ? 00:00:00 ora_mrp0_emea <---- oracle 1539 1524 0 20:01 pts/1 00:00:00 /bin/bash -c ps -ef | grep mrp oracle 1541 1539 0 20:01 pts/1 00:00:00 grep mrp SQL>
16. Verify the Physical Standby Database Is Performing Properly
On Primary
SQL> show parameters db_unique_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string apac SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME ---------- --------- --------- 5 25-JUL-15 25-JUL-15 6 25-JUL-15 25-JUL-15 7 25-JUL-15 25-JUL-15 8 25-JUL-15 25-JUL-15 4 rows selected. SQL> SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 10 <---- SQL>
On Standby
SQL> set lines 180 SQL> col MEMBER for a60 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 3 ONLINE /u01/app/oracle/oradata/emea/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/emea/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/emea/redo01.log NO 4 STANDBY /u01/app/oracle/oradata/emea/redo04.log NO 5 STANDBY /u01/app/oracle/oradata/emea/redo05.log NO 6 STANDBY /u01/app/oracle/oradata/emea/redo06.log NO 7 STANDBY /u01/app/oracle/oradata/emea/redo07.log NO 7 rows selected. SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME ---------- --------- --------- 5 25-JUL-15 25-JUL-15 6 25-JUL-15 25-JUL-15 7 25-JUL-15 25-JUL-15 8 25-JUL-15 25-JUL-15 9 25-JUL-15 25-JUL-15 10 25-JUL-15 25-JUL-15 6 rows selected. SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 10 <---- 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.
Reference:
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE (Doc ID 1075908.1)
Thanks Rajasekhar sharing this wonderful Document. Because of this Document i successfully created DATAGUARD today. Once again thanks for sharing this document.
very helpful documents, please upload without duplicate command DG creation document, in our current environment we not using catalog database, for backup
Thank you Rajasekhar for this active standby DG install guide. I’ve been through several guides and this was the first one I finished without a glitch! The writing was well balanced and critical info was not missing. Thanks again and keep writing!
My data guard configuration is ok and data sequence ok , now i am active a dataguard system it’s ok , but i need now connect standby server pdb database, i can’t connect pdb database showing tns error, others side when i am connect cdb database in sys user it will be connected, now how to fixed it. my database i oracle 18c, any suggestion please