Data Guard Implementation (For Oracle 10g R2 Windows)

STEP TO PERFORM ON PRIMARY DATABASE

Step # 1

On PRIMARY site create a service in TNSnames.ora file through which the PRIMARY site will be connected to the Standby machine.

Path E:\oracle\product\10.2.0\db_1\netwok\admin\tnsnames.ora

Copy Orcl Service and paste now change the underline word below

TO_STANDBY =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = Name of Standby Machine)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = standby)

   ) )

Step # 2

Also check the connectivity from the SQL Prompt

SQL> connect sys/oracle@to_standby as sysdba

Connected.

Step # 3

Reconnect to Primary Database 

SQL> connect sys/oracle as sysdba

Connected.

Create Pfile form spfile on sql prompt 

SQL> Create pfile=‘d:\pfile_primary.ora’ from spfile

File Created. 

Step # 4

Now open the pfile we create above on wordpad and add the following parameter. 

*.db_unique_name='PRIMARY'

*.FAL_Client='to_primary'

*.FAL_Server='to_standby'

*.Log_archive_config='DG_CONFIG=(primary,standby)'

*.Log_archive_dest_1='Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'

*.Log_archive_dest_2='Service=to_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

*.Service_names='orcl'

*.Standby_File_Management='AUTO'

Step # 5

Create Pfysical Folder on C:\oracle\backeup à for archive_log_dest_1 parameter 

Step # 6

Now shutdown the primary database 

Shutdown immediate

Step # 7

Now startup the primary database with the pfile we create above

Startup mount pfile=d:\pfile_primary.ora

Check weather database is archive or not 

SQL> Archive Log list  

If not Convert to archive mode  --- using alter database archivelog

Step # 8

Add  standby redo logfile on primary site (this step is optional, used for Real time Apply)

Alter database add standby logfile 

(e:\oracle\product\10.2.0\oradata\standbyredo.log’) size 100m;

Step # 9

Now shutdown the primary database 

SQL>Shutdown immediate

Step # 10

Copy all datafiles and standby redolog file from primary database to standby database 

Check standby database is shutdown 

Step # 11

Now again start primary database at mount stage from the pfile wo create above 

Startup mount pfile=d:\pfile_primary.ora

Step # 12

Now create standby control file to primary site 

Alter database create standby controlfile as ‘c:\oracle\backup\standbycontrol.ctl’;

Step # 13 

Now copy this created standby control file to the standby site where other database file like databases, logfiles and control files are located 

First delete the previous control files 

Than paste this controlfile  Rename this file to control01.ctl, control02.ctl, control03.ctl 

Step # 14 

Now Create spfile from pfile 

Create spfile from pfile=‘D:\pfile_primary.ora’;

This spfile created on this path e:\oracle\product\10.2.0\db_1\database\spfileorcl.ora

Step # 15 

Now restart the primary database 

Shutdown immediate 

Startup 

Step Completed on Primary database 

STEP TO PERFORM ON SECONDARY DATABASE

Step # 1 

Create physical foleder on c: drice   

C:\oracle\backup 

Step # 2 

Create pfile from spfile on sql prompt  

SQL> Create pfile=‘d:\pfile_standby.ora’ from spfile

File Created 

or get it  from D:\oracle\product\10.2.0\Admin\pfile

Step # 3 

Shutdown the database 

add these parameters in Pfile you just created

*.db_unique_name=‘STANDBY’

*.FAL_Client=‘to_standby’

*.FAL_Server=‘to_primary’

*.Log_archive_config=‘DG_CONFIG=(primary,standby)’

*.Log_archive_dest_1=‘Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby’

*.Log_archive_dest_2=‘Service=to_primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’

*.Log_archive_dest_state_1=ENABLE

*.Log_archive_dest_state_2=ENABLE

*.Service_names=‘ORCL’

*.Standby_File_Management=‘AUTO’

Step # 4 

Now startup the secondary database with the pfile we create above

STARTUP MOUNT PFILE=‘D:\pfile_standby.ora’;

Step # 5 

Now Create spfile from pfile 

SQL> create spfile from pfile=‘D:\pfile_standby.ora’;

File created.

Step # 6 

Now restart the Secondary database 

Shutdown immediate 

Startup mount

Step Completed on Primary database 

NOW ON PRIMARY DATABASE

For checking log sequence number

SQL>Archive log list 

SQL>Alter system switch logfile ;

SQL>Select status, error from v$archive_dest where dest_id=2

The status column should return the Valid value

NOW ON STAND BY DATABASE

To apply logs start the MPR background process by executing the following statement

SQL>alter database recover managed standby database disconnect;

Database altered.

verify result by querying ....

SQL> select name, applied, archived from v$archived_log;

to check database role (Primary or Standby) perform Following Query.

SQL> select database_role from v$database;

this will return 

Primary                   for Primary database

Physical Standby     for Standby database