Data Guard Configuration / Setup

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)

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: