Oracle Data Guard Configuration in 11g Database

Oracle Data Guard Configuration in 11g Database

Step 1: OS installation similar to Primary Database OS. [Standby]
Step 2:   Install Database Software similar version to Primary Database. Only change the standby database’s bash Profile. [Standby]
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_UNQNAME=sbsdr; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=sbs; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Step 3: Create folder (Data files, control files, Redo logs and dump files) similar to Primary Database.
Step 4: Create Listener and tns in Standby Database and modify the tns Primary Database.
tnsnames.ora at Primary Database
sbs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.74)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
sbsdr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
listener.ora file at standby side
SID_LIST_LISTENER_SBSDR =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbs)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = sbs)
)
)
LISTENER_SBSDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
)
ADR_BASE_LISTENER_OEMGC = /u01/app/oracle
tnsname.ora file at standby side
sbs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.74)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
sbsdr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
Check Listener and Start (StandBy Database):
$ lsnrctl
$ status
$ start LISTENER_SBSDR — (Listener_Name)

Check the Both Server tns is working or not (tns Service):
$ tnsping sbsdr [from primary]
$ tnsping sbs   [from primary]
$ tnsping sbs [from standby]
$ tnsping sbsdr [from standby]
Step 5: PREPARE Primary DATABASE
(1) Moving database at force logging mode
SQL> ALTER DATABASE FORCE LOGGING;
(2) Create Standby redolog [Primary Database]
Connect to the physical standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.
You can check the number and group numbers of the redo logs by querying the V$LOG view: [Primary Databse]
SQL> SELECT * FROM V$LOG;
SQL> SELECT * FROM V$LOGFILE;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/sdata2/redologs/sbs/redo04.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/sdata2/redologs/sbs/redo05.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/sdata1/redologs/sbs/redo06.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/sdata1/redologs/sbs/redo07.log’) SIZE 50M;
(3) CONFIGURE PRIMARY DATABASE PARAMETER CHANGE
*.audit_file_dest=’/u01/app/oracle/admin/sbs/adump’
*.audit_sys_operations=TRUE
*.audit_trail=’DB’,'EXTENDED’
*.cell_offload_processing=FALSE
*.compatible=’11.2.0.0.0′
*.control_files=’/sdata1/controlfiles/sbs/control01.ctl’,'/sdata2/controlfiles/sbs/control02.ctl’#Restore Controlfile
*.db_block_size=8192
*.db_domain=”
*.db_name=’sbs’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_dest_1=’location=/sdata3/archivelog’
*.memory_target=8879341568
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=665
*.shared_pool_size=524288000
*.undo_tablespace=’UNDOTBS1′
Parameter changing required:
*.fal_client=’sbs’
alter system set fal_client=’sbs’ scope=both;
*.fal_server=’sbsdr’
alter system set fal_server=’sbsdr’ scope=both;
*.log_archive_config=’dg_config=(sbs,sbsdr)’
alter system set log_archive_config=’dg_config=(sbs,sbsdr)’ scope=both;
*.log_archive_dest_2=’service=IRISDR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=IRISDR’
alter system set log_archive_dest_2=’service=SBSDR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=SBSDR’ scope=both;
*.log_archive_dest_state_2=’ENABLE’
alter system set log_archive_dest_state_2=’DEFER’ scope=both;
*.standby_file_management=’AUTO’
alter system set standby_file_management=’AUTO’ scope=both;

(4) GATHER FILES AND PERFORM BACK UP
On the primary node, create a Backup directory at primary Database :
$ mkdir -p /u01/rman/backup_dg/
Create the same exact path on the [ standby host ]:
$ mkdir -p /u01/rman/backup_dg/
On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. [primary node 1]
SQL> CREATE PFILE=’/u01/rman/backup_dg/initsbs.ora’  FROM SPFILE;
On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory.
$ rman target /
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format ‘/u01/rman/backup_dg/DATA_%U’;
backup archivelog all format ‘/u01/rman/backup_dg/ARC_%U’;
backup current controlfile for standby format ‘/u01/rman/backup_dg/CTL_%U’;
release channel c1;
release channel c2;
release channel c3;
}
Copy the contents of the staging directory on the primary node to the staging directory on the standby host.
$ scp /u01/rman/backup_dg/*  oracle@sbsdr: /rman/backup_dg/

TRANSFER PASSWORD FILE AND RENAME
To enable secure transmission of redo data, make sure the primary and physical standby databases use a password file, and make sure the password for the SYS user is identical on every system.
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwsbs  password=XXXXX
            $ ftp sbsdr
            $cd $ORACLE_HOME/dbs
            $mput orapwsbs1
Rename orapwsbs to orapwsbs at standby side
Copy and rename the primary database PFILE from the staging area on the standby host to the $ORACLE_HOME/dbs directory on the standby host. [ standby host ]: 
$scp initsbs.ora  $ORACLE_HOME/dbs/initsbs.ora
Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table: [ standby host ]:
*.audit_file_dest=’/u01/app/oracle/admin/sbs/adump’
*.audit_sys_operations=TRUE
*.audit_trail=’DB’,'EXTENDED’
*.cell_offload_processing=FALSE
*.compatible=’11.2.0.0.0′
*.control_files=’/sdata1/controlfiles/sbs/control01.ctl’,'/sdata2/controlfiles/sbs/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’sbs’
*.db_unique_name=’sbsdr’
*.diagnostic_dest=’/u01/app/oracle’
*.memory_target=8879341568
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=665
*.shared_pool_size=524288000
*.undo_tablespace=’UNDOTBS1′
*.log_archive_config=’dg_config=(sbs,sbsdr)’
*.fal_client=’sbsdr’
*.fal_server=’sbs’
*.log_archive_dest_1=’location=/sdata3/archivelog MANDATORY’
*.log_archive_dest_2=’service=SBS ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=SBS’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’DEFER’
*.standby_file_management=’AUTO’
STANDBY DATABASE RESTOR & RECOVER
After setting up the appropriate environment variables on the standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the physical standby database instance without mounting the control file. [ standby host ]
SQL> STARTUP NOMOUNT
From the standby host, duplicate the primary database .  [ standby host ]
$ rman target sys/XXXXX@sbs  auxiliary /
 RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
Perform the following activity on Standby Database Server
Start Physical standby database
SQL> alter database mount standby database;
Initiate Log apply services
Run the following command on standby database
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Put standby database on normal stage as before ( Log Apply services mode)
Create Server Parameter File on Standby Database Server
SQL> CREATE SPFILE FROM PFILE=’Path/Name.ora’;
SQL>shutdown immediate;

SQL>startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database open into Read only mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
Step 6: VERIFY DATA GUARD CONFIGURATION
Test Case 1:
-Verify database role
On the primary server
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
On the standby server
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Test Case 2:
-The “alter system switch logfile” command forces a log switch.
On the primary server
-The “alter system switch logfile” command forces a log switch.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /sdata3/archivelog
Oldest online log sequence     8043
Next log sequence to archive   8045
Current log sequence           8045
SQL>
On the standby server
======================
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /sdata3/archivelog
Oldest online log sequence     8043
Next log sequence to archive   0
Current log sequence           8045
SQL>
Test Case 3:
-Below SQL will show Max sequence number of the archived log
On the primary server
=========================
SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;
PRIMARY
———-
8044
On the standby server
=========================
SQL> SELECT max(sequence#) AS “STANDBY”, applied
FROM v$archived_log GROUP BY applied;
STANDBY APPLIED
———- ———
8044 YES
Test Case 4:
-Create a tablespace on primary server
On the primary server
=========================
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SBS
TBL_BBL_AUDIT_DAY30
7 rows selected.
On the standby server
=========================
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SBS
TBL_BBL_AUDIT_DAY30
7 rows selected.

V$ARCHIVE_GAP displays information about archive gaps on a standby database. This view can be used to find out the current archive gap that is blocking recovery.