Data Guard Implementation (For Oracle 10g R2)

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'

then goto edit select replace option and ‘ replace with ‘ than save the file. 

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

Step # 8  (OPTIONAL)

Add standby redo logfile on primary site 

Alter database add standby logfile 

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

Step # 9

Now shutdown the primary database 

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 

Step # 3 

Shutdown the database 

*.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’

*.db_file_name_convert=‘/home/sanath/primary/’,’/home/sanath/standby/’

*.log_file_name_convert=‘/home/sanath/primary/’,’/home/sanath/standby/’

*.lock_name_space=standby

then goto edit select replace option and ‘ replace with ‘ than save the file. 

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

SQL> show parameter log_archive_dest_satate_2

SQL>Alter system set log_archive_dest_satate_2=enable scope=both;

System altered

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 STABD BY DATABASE

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

SQL>alter database recover managed standby database disconnect

SQL> /

Database altered.

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

SQL> select database_role from v$database;

For manual switchover

NOW ON PRIMARY DATABASE

connect sys/oracle@to_primary as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

NOW ON STABD BY DATABASE

connect /@to_standby as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ON previous primary

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> alter database recover managed standby database disconnect

New Primary (previous stand by )

SQL> ALTER DATABASE OPEN;

Failover Steps

Failover:
A failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.

We performed a failover in our Disastor Recovery scenario. Where the primary database was taken offline (as unavialble), and standby database was acticated as Primary Database.

The Standby database will now be activated as the new Primary database.

SQL> conn sys/****@rockstd as sysdba
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;

WHEN WE WANT TO PERFORM CHECKING WEATHER STANDBY DATABASE WORKING PROPERLY 

FIRST CHECK PRIMARY DATABASE WEATHER ANY JOB IS STILL RUNNING OR ANY 

Desc  v$database 

SQL>     Select switchover_status from v$database ;

SQL>     select username, program from v$session 

               Where sid in (select distinct sid from v$MYSTAT)

AND TYPE-‘user’;

SQL> ALTER SYSTEM SET JOB_QUEUSE_PREOCESSES=0;