Step by step dataguard configuration
In this exercise I have provided steps in detailed manner to build physical standby database in oracle 11g. We will be doing standby setup in the following servers.
DB details :
SID DB_Unique_Name Service Name
Primary Primary Primary PRIMARY
Standby Primary Standby STANDBY
Prerequisites for primary server :
- Primary should be running in archive log mode and Force logging should be enabled as below
Please issue the below to check whether db is running in archive log mode or no archive log mode :
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Please issue the below to check whether db is running in force_logging mode :
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
In case your db is running in noarchivelog mode and non-force logging mode follow the below :
Before issuing the queries below make sure to keep your db in mount state :
SQL> alter database archivelog enable;
SQL>alter database force logging;
make sure you have connectivity between primary and standby servers.
From primary :
[oracle@dev186 oracle]$ ping 192.168.55.34
From standby :
[oracle@dev186 oracle]$ ping 192.168.55.42
Primary listener file : (/oracle/11g/11gr2/network/admin/listener.ora)
# listener.ora Network Configuration File: /oracle/11g/11gr2/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_PRIMARY = (SID_LIST = (SID_DESC =(SID_NAME = primary) (ORACLE_HOME = /oracle/11g/11gr2)))
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = primary)
(ORACLE_HOME = /oracle/11g/11gr2)
)
)
ADR_BASE_LISTENER = /oracle/11g
TNS files : (/oracle/11g/11gr2/network/admin/tnsnames.ora)
# tnsnames.ora Network Configuration File: /oracle/11g/11gr2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMARY)
)
)
LISTENER_PRIMARY =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev201.chain-sys.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)
Once you have updated both the listener and tnsnames files, start the listener in both the servers and check the below :
Primary :
[oracle@dev186 oracle]$ lsnrctl start
to check status,
[oracle@dev186 oracle]$ lsnrctl status
Standby :
[oracle@dev201 oracle]$ lsnrctl start
once listener is up issue the below and check the connectivity
[oracle@dev186 oracle]$tnsping primary
[oracle@dev186 oracle]$tnsping standby
In order to configure dataguard, your primary and standby should use same password file. Create a new password file if you dont remember current sys password else you can move the current password file to standby as below :
[oracle@dev186 oracle]$ cd $ORACLE_HOME
[oracle@dev186 11gr2]$ cd dbs
[oracle@dev186 dbs]$ ls
hc_clone.dat hc_primary.dat initCLONE.ora initstandby.ora lkSTANDBY redo.sql spfileprimary.ora
hc_DBUA0.dat hc_standby.dat init.ora lkCLONE orapwprimary snapcf_primary.f
hc_orcl.dat initclone.ora initPRIMARY.ora lkPRIMARY primary.ora spfileclone.ora
[oracle@dev186 dbs]$ scp orapwprimary oracle@192.168.55.34:/oracle/11g/product/11.2.0/dbhome_1/dbs/ orapwstandby
Add standby redo logs :
SQL>alter database add standby logfile group 4 ‘/oracle/11g/flash_recovery_area/PRIMARY/onlinelog/log4a.log’ size 500M;
Database altered.
Update required parameters in primary pfile. Your primary pfile should be as below :
Primary pfile = $ORACLE_HOME/dbs/initprimary.ora
initPRIMARY.ora :
primary.__db_cache_size=520093696
primary.__java_pool_size=16777216
primary.__large_pool_size=16777216
primary.__oracle_base='/oracle/11g'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=771751936
primary.__sga_target=855638016
primary.__shared_io_pool_size=0
primary.__shared_pool_size=285212672
primary.__streams_pool_size=0
*.audit_file_dest='/oracle/11g/admin/primary/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/11g/oradata/PRIMARY/controlfile/o1_mf_9q0v8cyw_.ctl','/oracle/11g/flash_recovery_area/PRIMARY/controlfile/o1_mf_9q0v8d1v_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/11g/oradata'
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/oracle/11g/flash_recovery_area'
*.db_recovery_file_dest_size=5242880000
*.DB_UNIQUE_NAME='primary'
*.diagnostic_dest='/oracle/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.FAL_SERVER='standby'
*.local_listener='LISTENER_PRIMARY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.memory_target=1613758464
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
initstandby.ora :
primary.__db_cache_size=671088640
primary.__java_pool_size=16777216
primary.__large_pool_size=16777216
primary.__oracle_base='/oracle/11g'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=654311424
primary.__sga_target=973078528
primary.__shared_io_pool_size=0
primary.__shared_pool_size=251658240
primary.__streams_pool_size=0
*.audit_file_dest='/oracle/11g/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/11g/oradata/standby/control01.ctl','/oracle/11g/flash_recovery_area/standby/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/11g/oradata/standby'
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/oracle/11g/flash_recovery_area'
*.db_recovery_file_dest_size=5242880000
*.diagnostic_dest='/oracle/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.local_listener='LISTENER_STANDBY'
*.memory_target=1613758464
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME='standby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby
#db_create_online_log_dest_1='/oracle/11g/oradata/standby'
STANDBY_FILE_MANAGEMENT=AUTO
db_file_name_convert=('/oracle/11g/oradata/PRIMARY','/oracle/11g/oradata/STANDBY')
log_file_name_convert=('/oracle/11g/oradata/PRIMARY','/oracle/11g/oradata/STANDBY')
Once you have updated standby pfile, bring up standby instance to nomount state.
[oracle@dev201 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 7 12:06:04 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstandby.ora'
Your instance should be started without any error.
Next, we have to clone primary db from primary server to standby server. In order to achieve this, we can use any of the cloing of the method as per our covenience. Here we will use duplicate statement to replicate db. Make sure you have updated the parameters below in standby pfile if your directory structure is different.
db_file_name_convert='/oracle/11g/oradata/STANDBY','/oracle/11g/oradata/PRIMARY'
log_file_name_convert='/oracle/11g/oradata/STANDBY','/oracle/11g/oradata/PRIMARY'
Take backup of primary database and controlfile using RMAN as below :
[oracle@dev186 oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 7 12:14:37 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMARY (DBID=1684851363)
RMAN> backup current controlfile for standby;
Starting backup at 07-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
channel ORA_DISK_1: finished piece 1 at 07-JUN-14
piece handle=/oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_07/o1_mf_ncnnf_TAG20140607T121717_9s5fcrs3_.bkp tag=TAG20140607T121717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-14
RMAN> backup database plus archivelog;
Starting backup at 07-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_system_9q0v6hgo_.dbf
input datafile file number=00002 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_sysaux_9q0v6hkx_.dbf
input datafile file number=00005 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_example_9q0v8sz0_.dbf
input datafile file number=00003 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_undotbs1_9q0v6hl7_.dbf
input datafile file number=00004 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_users_9q0v6hm1_.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
Starting backup at 07-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=71 RECID=87 STAMP=849615566
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
channel ORA_DISK_1: finished piece 1 at 07-JUN-14
piece handle=/oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_07/o1_mf_annnn_TAG20140607T121926_9s5fhp63_.bkp tag=TAG20140607T121926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-14
once backup is finished, Move all your backup including controlfile backup to standby server using scp command. Move all backup pieces to flash_recovery_area in standy server.
FRA ==> /oracle/11g/flash_recovery_area but your backup pieces should be in the below as same as primary server,
Move backup pieces to the follwing in standby server --> oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_06
Before we duplicate standby db we have create following directory structure into standby server.
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_06
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/flash_recovery_area/PRIMARY/onlinelog
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/oradata/standby
Now connect both target and auxiliary servers using RMAN,
[oracle@dev201 trace]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 7 12:27:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliray /
connected to target database: PRIMARY (DBID=1684851363)
RMAN> connect target sys/sys@primary
connected to auxiliary database: PRIMARY (DBID=1684851363)
RMAN> duplicate target database for standby nofilenamecheck dorecover;
once done.
Start MRP process in standby database,
SQL> alter database recover managed standy database disconnect;
once you issued, mrp bg process will be started in standby, confirm be the below :
[oracle@dev201 STANDBY]$ ps -ef | grep mrp
oracle 9543 1 0 09:28 ? 00:00:01 ora_mrp0_standby
once MRP process have been stared in standby, our standby db is all set receive redo log changes from primary. Now manually switch log file and check archive log sequences to make sure both have same count.
Connect to primary db and issue the below :
SQL>alter system switch logfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Next log sequence to archive 74
Current log sequence 74
Connect to standy db and issue the below :
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Next log sequence to archive 0
Current log sequence 74
Check both the primary and standby alert logs for any errors. If not we have successfully configured physical standby and we can confirm it by the following queries,
DG Queries :
Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
Hope this document helps! :) Happy learning! Cheers!
In this exercise I have provided steps in detailed manner to build physical standby database in oracle 11g. We will be doing standby setup in the following servers.
DB details :
SID DB_Unique_Name Service Name
Primary Primary Primary PRIMARY
Standby Primary Standby STANDBY
Prerequisites for primary server :
- Primary should be running in archive log mode and Force logging should be enabled as below
Please issue the below to check whether db is running in archive log mode or no archive log mode :
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Please issue the below to check whether db is running in force_logging mode :
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
In case your db is running in noarchivelog mode and non-force logging mode follow the below :
Before issuing the queries below make sure to keep your db in mount state :
SQL> alter database archivelog enable;
SQL>alter database force logging;
make sure you have connectivity between primary and standby servers.
From primary :
[oracle@dev186 oracle]$ ping 192.168.55.34
From standby :
[oracle@dev186 oracle]$ ping 192.168.55.42
Primary listener file : (/oracle/11g/11gr2/network/admin/listener.ora)
# listener.ora Network Configuration File: /oracle/11g/11gr2/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_PRIMARY = (SID_LIST = (SID_DESC =(SID_NAME = primary) (ORACLE_HOME = /oracle/11g/11gr2)))
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = primary)
(ORACLE_HOME = /oracle/11g/11gr2)
)
)
ADR_BASE_LISTENER = /oracle/11g
TNS files : (/oracle/11g/11gr2/network/admin/tnsnames.ora)
# tnsnames.ora Network Configuration File: /oracle/11g/11gr2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMARY)
)
)
LISTENER_PRIMARY =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev186.chainsys.com)(PORT = 1521))
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev201.chain-sys.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)
Once you have updated both the listener and tnsnames files, start the listener in both the servers and check the below :
Primary :
[oracle@dev186 oracle]$ lsnrctl start
to check status,
[oracle@dev186 oracle]$ lsnrctl status
Standby :
[oracle@dev201 oracle]$ lsnrctl start
once listener is up issue the below and check the connectivity
[oracle@dev186 oracle]$tnsping primary
[oracle@dev186 oracle]$tnsping standby
In order to configure dataguard, your primary and standby should use same password file. Create a new password file if you dont remember current sys password else you can move the current password file to standby as below :
[oracle@dev186 oracle]$ cd $ORACLE_HOME
[oracle@dev186 11gr2]$ cd dbs
[oracle@dev186 dbs]$ ls
hc_clone.dat hc_primary.dat initCLONE.ora initstandby.ora lkSTANDBY redo.sql spfileprimary.ora
hc_DBUA0.dat hc_standby.dat init.ora lkCLONE orapwprimary snapcf_primary.f
hc_orcl.dat initclone.ora initPRIMARY.ora lkPRIMARY primary.ora spfileclone.ora
[oracle@dev186 dbs]$ scp orapwprimary oracle@192.168.55.34:/oracle/11g/product/11.2.0/dbhome_1/dbs/ orapwstandby
Add standby redo logs :
SQL>alter database add standby logfile group 4 ‘/oracle/11g/flash_recovery_area/PRIMARY/onlinelog/log4a.log’ size 500M;
Database altered.
Update required parameters in primary pfile. Your primary pfile should be as below :
Primary pfile = $ORACLE_HOME/dbs/initprimary.ora
initPRIMARY.ora :
primary.__db_cache_size=520093696
primary.__java_pool_size=16777216
primary.__large_pool_size=16777216
primary.__oracle_base='/oracle/11g'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=771751936
primary.__sga_target=855638016
primary.__shared_io_pool_size=0
primary.__shared_pool_size=285212672
primary.__streams_pool_size=0
*.audit_file_dest='/oracle/11g/admin/primary/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/11g/oradata/PRIMARY/controlfile/o1_mf_9q0v8cyw_.ctl','/oracle/11g/flash_recovery_area/PRIMARY/controlfile/o1_mf_9q0v8d1v_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/11g/oradata'
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/oracle/11g/flash_recovery_area'
*.db_recovery_file_dest_size=5242880000
*.DB_UNIQUE_NAME='primary'
*.diagnostic_dest='/oracle/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.FAL_SERVER='standby'
*.local_listener='LISTENER_PRIMARY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.memory_target=1613758464
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
initstandby.ora :
primary.__db_cache_size=671088640
primary.__java_pool_size=16777216
primary.__large_pool_size=16777216
primary.__oracle_base='/oracle/11g'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=654311424
primary.__sga_target=973078528
primary.__shared_io_pool_size=0
primary.__shared_pool_size=251658240
primary.__streams_pool_size=0
*.audit_file_dest='/oracle/11g/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/11g/oradata/standby/control01.ctl','/oracle/11g/flash_recovery_area/standby/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/11g/oradata/standby'
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/oracle/11g/flash_recovery_area'
*.db_recovery_file_dest_size=5242880000
*.diagnostic_dest='/oracle/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.local_listener='LISTENER_STANDBY'
*.memory_target=1613758464
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME='standby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby
#db_create_online_log_dest_1='/oracle/11g/oradata/standby'
STANDBY_FILE_MANAGEMENT=AUTO
db_file_name_convert=('/oracle/11g/oradata/PRIMARY','/oracle/11g/oradata/STANDBY')
log_file_name_convert=('/oracle/11g/oradata/PRIMARY','/oracle/11g/oradata/STANDBY')
Once you have updated standby pfile, bring up standby instance to nomount state.
[oracle@dev201 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 7 12:06:04 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstandby.ora'
Your instance should be started without any error.
Next, we have to clone primary db from primary server to standby server. In order to achieve this, we can use any of the cloing of the method as per our covenience. Here we will use duplicate statement to replicate db. Make sure you have updated the parameters below in standby pfile if your directory structure is different.
db_file_name_convert='/oracle/11g/oradata/STANDBY','/oracle/11g/oradata/PRIMARY'
log_file_name_convert='/oracle/11g/oradata/STANDBY','/oracle/11g/oradata/PRIMARY'
Take backup of primary database and controlfile using RMAN as below :
[oracle@dev186 oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 7 12:14:37 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMARY (DBID=1684851363)
RMAN> backup current controlfile for standby;
Starting backup at 07-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
channel ORA_DISK_1: finished piece 1 at 07-JUN-14
piece handle=/oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_07/o1_mf_ncnnf_TAG20140607T121717_9s5fcrs3_.bkp tag=TAG20140607T121717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-14
RMAN> backup database plus archivelog;
Starting backup at 07-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_system_9q0v6hgo_.dbf
input datafile file number=00002 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_sysaux_9q0v6hkx_.dbf
input datafile file number=00005 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_example_9q0v8sz0_.dbf
input datafile file number=00003 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_undotbs1_9q0v6hl7_.dbf
input datafile file number=00004 name=/oracle/11g/oradata/PRIMARY/datafile/o1_mf_users_9q0v6hm1_.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
Starting backup at 07-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=71 RECID=87 STAMP=849615566
channel ORA_DISK_1: starting piece 1 at 07-JUN-14
channel ORA_DISK_1: finished piece 1 at 07-JUN-14
piece handle=/oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_07/o1_mf_annnn_TAG20140607T121926_9s5fhp63_.bkp tag=TAG20140607T121926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-14
once backup is finished, Move all your backup including controlfile backup to standby server using scp command. Move all backup pieces to flash_recovery_area in standy server.
FRA ==> /oracle/11g/flash_recovery_area but your backup pieces should be in the below as same as primary server,
Move backup pieces to the follwing in standby server --> oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_06
Before we duplicate standby db we have create following directory structure into standby server.
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/flash_recovery_area/PRIMARY/backupset/2014_06_06
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/flash_recovery_area/PRIMARY/onlinelog
[oracle@dev201 oracle]$ mkdir -pr oracle/11g/oradata/standby
Now connect both target and auxiliary servers using RMAN,
[oracle@dev201 trace]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 7 12:27:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliray /
connected to target database: PRIMARY (DBID=1684851363)
RMAN> connect target sys/sys@primary
connected to auxiliary database: PRIMARY (DBID=1684851363)
RMAN> duplicate target database for standby nofilenamecheck dorecover;
once done.
Start MRP process in standby database,
SQL> alter database recover managed standy database disconnect;
once you issued, mrp bg process will be started in standby, confirm be the below :
[oracle@dev201 STANDBY]$ ps -ef | grep mrp
oracle 9543 1 0 09:28 ? 00:00:01 ora_mrp0_standby
once MRP process have been stared in standby, our standby db is all set receive redo log changes from primary. Now manually switch log file and check archive log sequences to make sure both have same count.
Connect to primary db and issue the below :
SQL>alter system switch logfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Next log sequence to archive 74
Current log sequence 74
Connect to standy db and issue the below :
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Next log sequence to archive 0
Current log sequence 74
Check both the primary and standby alert logs for any errors. If not we have successfully configured physical standby and we can confirm it by the following queries,
DG Queries :
Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
Hope this document helps! :) Happy learning! Cheers!
No comments:
Post a Comment