What is DataGuard:
Data Guard is term associated with Oracle standby database (DB) thereby ensuring high availability, data protection and disaster recovery. This technology is used for many OLTP systems and other databases based on the significance and how it affects the business.
I will go through few terms first before going forward with an example setup.
Terminology
Primary Database: Also called production database.
Standby Database: It is a consistent copy of the primary DB. You can have upto 9 standby DBs for a primary. A standby database can be either a physical standby database or a logical standby database.
Physical Standby: It is a physically identical copy to primary DB (in short block-on-block) on disk. Changes from primary are synched with standby using Redo-Apply method.
Logical Standby: This standby DB have the same data(logical information) as in primary but the physical structure can be different. The DB is kept current using SQL Apply method.
Switchover: During a switchover, the primary database transitions to a standby role,
and the standby database transitions to the primary role (in short roles are reversed). This is typically part of planned maintenance of the primary system.
Failover: Failover happens only in the event of a disaster or failure of the primary database, and the failover results in a transition of a standby database to the primary role.It can happen if the primary database server crashes or geographic disasters.
Dataguard Protection Mode/Level:
Maximum Protection: Zero loss of data. The redo of the primary have to be written to local online redo log and standby redo log before the transaction are saved. To ensure no data loss, primary DB shuts down if a fault prevents access to standby redo log.
Maximum Performance: Default mode. This allows transaction on primary to be saved if redo information are written to local online redo log.The primary redo data stream also writes to standby redo log but asynchronously to the local redo log.
Maximum Availability: This mode behaves as Maximum Protection mode. The redo transaction have to be written to both local online redo log and standby redo log but it doesn't shutdown primary database in case of any fault. During the at fault scenarios, it is changed to Maximum Performance mode until the fault is corrected and all the redo gaps are fixed. Once fixed, it automatically changes to Maximum Availability.
Below I am sharing step by step details to build a Maximum Performance DataGuard.
Note:
- It is easier if the primary DB uses SPFILE instead of PFILE.
- Please do understand that DB_NAME of Primary and Standby are same, but the DB_UNIQUE_NAME will be different
Database Name/Primary/Standby: ORCL
Primary DB Unique Name: ORCL
Standby DB Unique Name: ORCLSTY
Size of the DB: 300 GB
Operating System (OS): HP UX Itanium 11.31
Primary Database(ORCL):
File System: /p
1. Make sure the DB is in archive log mode.
SELECT LOG_MODE FROM V$DATABASE;
or ARCHIVE LOG LIST
If the DB is in "NOARCHIVELOG" mode, please perform the following:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2. Primary DB must have forced logging
SELECT FORCE_LOGGING FROM V$DATABASE;
If DB is not, run the below command:
ALTER DATABASE FORCE LOGGING;
3. Make sure the DB Name and DB Unique Name are set.
SYS@ORCL SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
SYS@ORCL SQL>SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL
4. Use DB_UNIQUE_NAME to configure LOG_ARCHIVE_CONFIG
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLSTY)' scope=both;
5. Set the Log information on the primary
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/p04/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL' scope=both;
If you receive the following error:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST
Solution:
SYS@ORCL SQL>show parameter LOG_ARCHIVE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string /p04/arch
log_archive_dest_1 string
log_archive_dest_10 string
..
..
Run the following -
SYS@ORCLSQL>ALTER SYSTEM set log_archive_dest='' scope=both;
System altered.
SYS@ORCLSQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/p04/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL' scope=both;
System altered.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
Make a note of SERVICE Name and DB_UNIQUE_NAME.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLSTY LGWR ASYNC=20480 OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTY' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
6. Set the *CONVERT parameters if primary and standby are on different file system. My standby is on /s file system while primary is on /p. Also set the FAL_SERVER and FAL_CLIENT parameter. This parameter is to make primary ready to switch roles.
ALTER SYSTEM SET FAL_SERVER=ORCLSTY scope=both;
ALTER SYSTEM SET FAL_CLIENT=ORCL scope=both;
Please note that for changing *CONVERT parameter 'scope=both' doesnt work. It has to be 'scope=spfile'.
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/s','/p' scope=spfile;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/s','/p' scope=spfile;
I also added DB_ULTRA_SAFE=DATA_AND_INDEX . The parameter control protection levels. This is not mandatory. This parameter can be changed only using PFILE.
ALTER SYSTEM SET DB_ULTRA_SAFE=DATA_AND_INDEX scope=both;
ALTER SYSTEM SET DB_ULTRA_SAFE=DATA_AND_INDEX scope=both
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
ALTER SYSTEM SET DB_ULTRA_SAFE=DATA_AND_INDEX scope=spfile;
ALTER SYSTEM SET DB_ULTRA_SAFE=DATA_AND_INDEX scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
7. Add Standby redo log
It's a good ideology to match the online redo log configuration of the primary server.
My primary database have 3 groups with 1 log member each of 100M, so I started with GROUP 5. It is important that you have an extra standby redo log member than the online redo log. Here I added two groups more than the online redo log.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/p02/redo/styredo_05a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/p03/redo/styredo_06a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/p02/redo/styredo_07a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/p03/redo/styredo_08a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/p02/redo/styredo_09a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
8. Make sure the autehntication is exclusive and password file is created.
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password='xxxx!yyyy' entries=10 force=y
I used quotes for password as I have a special character in the password.
9. Create Standby Control file and pfile- This is not mandatory step.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/orclsty.ctl';
It is easier to modify the pfile created from primary spfile for the standby
CREATE PFILE='/p01/app/oracle/orcldb/11.2.0/dbs/initORCLSTY.ora' from spfile;
10. Add tns details for primary and secondary $ORACLE_HOME/network/admin/tnsnames.ora.
Please note the difference in hostnames.
ORCL=
(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = orcldb.vaidiyan.com)
(PORT = 1521)))
(CONNECT_DATA =
(SID = ORCL)))
ORCLSTY=
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = orclstydb.vaidiyan.com)
(PORT = 1521)))
(CONNECT_DATA =
(SID = ORCL)))
Perform tnsping test.
Standby Database(ORCLSTY):
File system : /s
a. Copy the pfile (created on step 9) , password file (from step 8) to $ORACLE_HOME/dbs on the standby server.
You can use scp or rcp to move files across hosts.
b. Add the tns entries for primary and standby to $ORACLE_HOME/network/admin/tnsnames.ora on the standby server
ORCL =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = orcldb.vaidiyan.com)
(PORT = 1521)))
(CONNECT_DATA =
(SID = ORCL)
))
ORCLSTY =
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = orclstydb.vaidiyan.com)
(PORT = 1521)))
(CONNECT_DATA =
(SID = ORCL)
))
c. Open the pfile copied from primary and change the path of adump, diagnostic_dest, control_file from /p to /s. Make sure no reference to the primary file system (/p) exist in the pfile, replace everything with '/s)
The archive location on standby server is /s04, Also note the change in db_unique_name from that of primary.
*.log_archive_dest_1='LOCATION=/s04/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLSTY'
*.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC NOAFFIRM OPTIONAL MAX_CONNECTIONS=1 REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.fal_client='ORCLSTY'
*.fal_server='ORCL'
d. This is KEY that db_unique_name initialization parameter is changed to 'orclsty' and FAL_SERVER I also made sure DB_ULTRA_SAFE is set to the pfile.
db_unique_name=ORCLSTY
DB_ULTRA_SAFE=DATA_AND_INDEX
e. Start the listener on the standby server. My listener name is ORCL on primary and standby
lsnrctl start ORCL
Perform tnsping test.
f. Start the standby DB in nomount state using the pfile
SQL> startup nomount pfile=/s01/app/oracle/orcldb/11.2.0/dbs/initORCL.ora
Verify the db_unique_name and make sure you do NOT use spfile. If you use SPFILE or create SPFILE from pfile on the standby database, the following RMAN DUPLICATE step would throw error.
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLSTY
g. If you already have a standby DB (from EMC snapshot technology) copy, then you do not need to perform RMAN DUPLICATE steps. But you need to add the standby redo log files on the Standby Database.
Note that I used the same script as Primary but changed the file system from /p to /s. The redologs must be added when the standby database is in mount state.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/s02/redo/styredo_05a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/s03/redo/styredo_06a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/s02/redo/styredo_07a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/s03/redo/styredo_08a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/s02/redo/styredo_09a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;
RMAN DUPLICATE (Building Standby Database):
I have used RMAN duplicate from active database instead of full backup as the connection between the two host is 10G LAN and the size of the database is small (NOT in Terabyte). You can also build standby using full primary database backup to disk/tape.
During this step auxiliary database is the standby database.
1. Verify RMAN connectivity
rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 31 16:53:36 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys@ORCL
target database Password:
connected to target database: ORCL (DBID=1080107045)
RMAN> connect auxiliary sys@ORCLSTY
auxiliary database Password:
connected to auxiliary database: ORCL (not mounted)
RMAN>
2. I created a RMAN DUPLICATE script (orcl_standby.rman) with the following commands and executed from command line.
connect AUXILIARY sys/xxxx!yyyy@ORCLSTY
connect TARGET sys/xxxx!yyyy@ORCL
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='ORCLSTY' COMMENT "Is a duplicate standby"
SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC NOAFFIRM OPTIONAL MAX_CONNECTIONS=1 REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
SET diagnostic_dest='/s01/app/oracle/orcldb'
SET control_files='/s05/orcldata/cntrl01.dbf','/s05/orcldata/cntrl02.dbf','/s06/orcldata/cntrl03.dbf'
SET FAL_CLIENT='ORCLSTY' COMMENT "Is standby"
SET FAL_SERVER='ORCL' COMMENT "Is primary"
SET LOG_FILE_NAME_CONVERT='/p','/s'
SET DB_FILE_NAME_CONVERT='/p','/s'
NOFILENAMECHECK;
How to run the rman script from command line:
rman cmdfile=orcl_standby.rman log=orcl_standby_0731.log
3. Once the RMAN Duplicate is complete, Make sure the LOG_ARCHIVE_DEST* parameter is appropriate.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=DISABLE MAX_FAILURE=10 MAX_CONNECTIONS=1 REOPEN=15 DB_UNIQUE_NAME=ORCL NET_TIMEOUT=30 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/s04/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLSTY' scope=both;
4. Shutdown down the standby database and start up in mount state.
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
Start Applying the Logs:
There are two ways to do it:
- Automatic recovery of standby
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
- Foreground redo apply, where control is not returned
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
- Background redo apply, control is returned to the system but the apply process continues.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
OR
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
For 10g Database, the command is :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, execute the following command:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Verify the Standby Database (ORCLSTY) DataGuard mode:
SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select DATABASE_ROLE from V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
Transport Redo Transport Verification SQL/Script:
On the Primary Database (ORCL), Run the below script:
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
On the Standby Database (ORCLSTY), verify that the log are applied using the SQL:
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
OR
SELECT thread#,max(sequence#) FROM V$ARCHIVED_LOG where applied ='YES' group by thread#;
Reference:
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
