Data Guard Physical Standby configuration in Oracle Database 11g R2
===================================================================
Machine setup
————-
We have two servers (physical or VMs) with Linux OS and Oracle installed on them. In this case I’ve used Oracle Linux 5.5
and Oracle Database 11.2.0.1.0.
– The primary server has a running database instance.
– The standby server has a software only installation.
1. Primary Server Setup
———————–
– Logging:
Check that the primary database is in archivelog mode.
—
sql> SELECT log_mode FROM v$database;
—
LOG_MODE
———-
ARCHIVELOG
—
If it is noarchivelog mode, switch is to archivelog mode using sql.
sql> SHUTDOWN IMMEDIATE;
sql> STARTUP MOUNT;
sql> ALTER DATABASE ARCHIVELOG;
sql> ALTER DATABASE OPEN;
—
2. Enabled forced logging
————————-
Enabled forced logging by issuing the following command.
sql> ALTER DATABASE FORCE LOGGING;
—
SELECT force_logging FROM v$database;
—
3. Initialization Parameters
—————————-
– Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “orcl” on the primary database.
sql> show parameter db_name
—
NAME TYPE VALUE
————– ——- ——
db_name string orcl
—
sql> show parameter db_unique_name
—
NAME TYPE VALUE
————– ——- ——
db_unique_name string orcl
—
– The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.
The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG
parameter. For this example, the standby database will have the value “ORCL_STNDBY”.
—
sql> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ORCL,ORCL_STNDBY)’;
—
– Set suitable remote archive log destinations. In this case I’m using the default archive, but we can specify a location explicitly.
Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
—
sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=ORCL_STNDBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STNDBY’;
sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
—
– The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE
must be set to exclusive.
—
sql> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ SCOPE=SPFILE;
sql> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
sql> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
—
– In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby.
For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename
and path differences between the servers.
sql> ALTER SYSTEM SET FAL_SERVER=ORCL_STNDBY;
sql> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
4. Service Setup
—————-
Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers.
We can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup:
—
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.107.130)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_STNDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.107.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
—
5. Backup Primary Database
————————–
– For a backup-based duplicate, or a manual restore, take a backup of the primary database.
$ rman target=/
—
RMAN> BACKUP DATABASE format ‘/u01/backup/full_bkp_%D_%s_%u’ PLUS ARCHIVELOG format ‘/u01/backup/arch_bkp_%D_%s_%u’;
—
6. Create Standby Controlfile and PFILE
—————————————
– Create a controlfile for the standby database by issuing the following command on the primary database.
sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/ORCL_STNDBY.ctl’;
—
sql> CREATE PFILE=’/tmp/initORCL_STNDBY.ora’ FROM SPFILE;
—
– Amend the PFILE making the entries relevant for the standby database. I’m making a replica of the original server,
so in my case I only had to amend the following parameters.
*.db_unique_name=’ORCL_STNDBY’
*.fal_server=’ORCL’
*.log_archive_dest_2=’SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL’
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7. Standby Server Setup (Manual by restoring backup)
=====================================================
– Create directories
Create the necessary directories on the standby server.
—
$ mkdir -p /u01/app/oracle/oradata/orcl
$ mkdir -p /u01/app/oracle/flash_recovery_area/orcl
$ mkdir -p /u01/app/oracle/admin/orcl/adump
—
– Copy the files from the primary to the standby server.
$ scp /tmp/ORCL_STNDBY.ctl oracle@192.168.107.131:/u01/app/oracle/oradata/orcl/control01.dbf — confirm filename before starting standby instance
—
# Parameter file.
scp /tmp/initORCL_STNDBY.ora oracle@192.168.107.131:/tmp/initORCL_STNDBY.ora
—
# Remote login password file.
scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.107.131:$ORACLE_HOME/dbs
—
# Transfer backup files
scp /u01/backup/* oracle@192.168.107.131:/u01/backup
—
i) Start Listener
—————–
– Make sure the listener is started on the standby server.
$ lsnrctl start
ii) Restore Backup
——————
– Make copy of control file
$ cd /u01/app/oracle/oradata/orcl/
$ cp control01.ctl control02.ctl
– Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
—
sql> CREATE SPFILE FROM PFILE=’/tmp/initORCL_STNDBY.ora’;
—
– Restore the backup files.
$ rman target=/
RMAN> STARTUP MOUNT;
RMAN> list backup of database;
RMAN> RESTORE DATABASE;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8. Standby Server Setup (using RMAN DUPLICATE)
———————————–
– Create directories
Create the necessary directories on the standby server.
—
$ mkdir -p /u01/app/oracle/oradata/orcl
$ mkdir -p /u01/app/oracle/flash_recovery_area/orcl
$ mkdir -p /u01/app/oracle/admin/orcl/adump
—
– Copy the files from the primary to the standby server.
$ scp /tmp/ORCL_STNDBY.ctl oracle@192.168.107.131:/u01/app/oracle/oradata/orcl/control01.ctl
—
# Parameter file.
scp /tmp/initORCL_STNDBY.ora oracle@192.168.107.131:/tmp/initORCL_STNDBY.ora
—
# Remote login password file.
scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.107.131:$ORACLE_HOME/dbs
—
i) Start Listener
—————–
– Make sure the listener is started on the standby server.
$ lsnrctl start
ii) Start instance
$ export ORACLE_SID=orcl
sql> sqlplus / as sysdba
sql> STARTUP NOMOUNT PFILE=’/tmp/initORCL_STNDBY.ora’;
—
ii) Duplicate database
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
$ rman TARGET sys/oracle@orcl AUXILIARY sys/oracle@ORCL_STNDBY
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’ORCL_STNDBY’ COMMENT ‘Is standby’
SET LOG_ARCHIVE_DEST_2=’SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL’
SET FAL_SERVER=’ORCL’ COMMENT ‘Is primary’
NOFILENAMECHECK;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9. Add standby redolog files
—————————-
Add standby redolog files for switchover and real time apply.
sql> ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/orcl/standby_redo01.log’) SIZE 5G;
sql> ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/orcl/standby_redo02.log’) SIZE 5G;
sql> ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/orcl/standby_redo03.log’) SIZE 5G;
sql> ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/orcl/standby_redo04.log’) SIZE 5G;
10. Start Apply Process
———————–
Start the apply process on standby server.
# Foreground redo apply. Session never returns until cancel.
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
# Background redo apply. Control is returned to the session once the apply process is started.
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
sql> ALTER DATABASE OPEN READ ONLY;
If you need to cancel the apply process, issue the following command.
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
11. Test Log Transport
———————–
– On primary server
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE ARCHIVED=’YES’;
– On standby server
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
Above result on both server should be same. else it is not synchronized with Primary.
12. Troubleshooting Dataguard
—————————–
i) Standby Database Does Not Receive Redo Data from the Primary Database
If the standby site is not receiving redo data, query the V$ARCHIVE_DEST view and check for error messages. For example,
enter the following query:
—
SQL> SELECT DEST_ID “ID”, STATUS “DB_status”,
DESTINATION “Archive_dest”, ERROR “Error”
FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;