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
0. Enviroment
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 ~]$
14. Create Standby Database
[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>
15. Enable MRP on Standby
[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)