Data Guard Configuration

Primary d/b:- prac1 and Standby d/b :-standby

Step-1:-   In primary data base prac1.

        SQL >alter database force logging

Step-2:- Create a password file for the primary d/b.

  $ orapwd file=orapwprac1 password=oracle entries=5 force=y

Step-3:- Configure Standby redo-logs.

The number and size of standby redo logs should be equal to or more than the number of online redo logs of the primary d/b i.e. in this case prac1.

     Size of the log file can be obtained from

          SQL> select byte/1024/1024 from v$log;

Add standby logfile accordingly:-

   >alter database add standby logfile group 4(‘/$ORACLE_HOME/prac1/redo04.log’) size 50M;

   >alter database add standby logfile group 5(‘/$ORACLE_HOME/prac1/redo05.log’) size 50M;

   >alter database add standby logfile group 6(‘/$ORACLE_HOME/prac1/redo06.log’) size 50M;

To check standby redo logs:-

     sql> select group#,status from v$standby_log;

See:  Usage, Benefits and Limitations of Standby Redo Logs (SRL) (Doc ID 219344.1)

Step-4:- Set Primary d/b Initialization parameters.

Edit the pfile of primary d/b prac1 i.e. initprac1.ora in $ORACLE_HOME/dbs

DB_NAME=prac1

DB_UNIQUE_NAME=prac1

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prac1,standby)'

CONTROL_FILES='/home/leo/oracle/product/10.2.0/db_1/prac1/control01.ctl', '/home/leo/oracle/product/10.2.0/db_1/prac1/control02.ctl', '/home/leo/oracle/product/10.2.0/db_1/prac1/control03.ctl'; 

LOG_ARCHIVE_DEST_1=

 'LOCATION=/home/leo/oracle/product/10.2.0/db_1/prac1/

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=prac1'

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

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=standby

FAL_CLIENT=prac1

DB_FILE_NAME_CONVERT=

'/home/leo/standby/','/home/leo/oracle/product/10.2.0/db_1/prac1/'

LOG_FILE_NAME_CONVERT=

'/home/leo/standby/','/home/leo/oracle/product/10.2.0/db_1/prac1/'

STANDBY_FILE_MANAGEMENT=AUTO

Step – 5:-  In prac1:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

Step – 6:- Create a Backup Copy of the Primary Database Datafiles (if using ADG then no need of backup)

Startup database;

$export ORACLE_SID=prac1;

$rman target / nocatalog;

Rman> backup database;

>shutdown database;

Even take backup of database physically by copying the datafiles to the standby file.

Step -7:- Create a Control File for the Standby Database

>startup mount; 

> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/leo/control01.ctl';

> ALTER DATABASE OPEN;

Step-8:- Prepare an Initialization Parameter File for the Standby Database

 Copy the primary database parameter file to the standby database.

In primary database:-

>startup pfile=’/home/leo/oracle/product/10.2.0/db_1/dbs/initprac1.ora’;

>create spfile from pfile;

>create pfile=’ home/leo/oracle/product/10.2.0/db_1/dbs/initstandby.ora’ from spfile;

Step-9:-  Set initialization parameters on the physical standby database

 DB_NAME=prac1

DB_UNIQUE_NAME=standby

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prac1,standby)'

CONTROL_FILES='/home/leo/standby/control1.ctl'

DB_FILE_NAME_CONVERT=’/home/leo/oracle/product/10.2.0/db_1/prac1/','/home/leo/standby/'

LOG_FILE_NAME_CONVERT=

 ’/home/leo/oracle/product/10.2.0/db_1/prac1/','/home/leo/standby/'

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=

 'LOCATION=/home/leo/standby/

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_2=

 'SERVICE=prac1 LGWR ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=prac1'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=prac1

FAL_CLIENT=standby

Step-10:-  Copy Files from the Primary System to the Standby System

Physically copy datafiles from primary d/b to the location at standby

Prac1>  cp *.dbf –v ~/standby

Step-11:- Create standby database password file

Dbs>orapwd file=orastandby password=oracle entries=5 force=y;

Step-10:- Setup listeners for “prac1” and “standby”, as well as Tnsnames for “prac1” and “standby”

Step-12:-  Start the physical standby database.

At standby database:- 
 SQL>startup mount;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step-13:- Test the data guard connection.

In primary d/b

>alter system switch logfile;

>archive log list;

In standby db

>archive log list

The log sequence number should be the same for both databases

ex:

ON PRIMARY :

SQL>  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD#  MAX(SEQUENCE#)

----------       --------------

         1             6045

or

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

————–

6045

ON STANDBY:

SQL>  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD#  MAX(SEQUENCE#)

----------         --------------

         1           6045

or

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

————–

6045

Here, the maximum sequence# generated on the Primary database is 6045 and the maximum sequence# applied on the standby database is also 6045 which means that the standby database is in sync with the primary database.

Ref:  Creating a Physical Standby Database (Doc ID 1475344.1)