Pages

Thursday, September 23, 2010

Oracle 10g Data Guard (windows)

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

2 comments:

  1. Asalam o Elikum,

    I have read the physical data guard note on your blog but i facing diffculty after modifying the pfile parameters of standby database when i tried to execute " SQL> Startup mount pfile = 'C:\pfile_standby.ora';

    it gives the below error.

    lrm-00123 invalid character 145 found in the input file
    ora-01078: failure in processing system parameters

    and I cant proceed further.

    Mr. Adnan i will be very thankful to you, if you guide me through.

    thanks
    Waheed

    ReplyDelete
    Replies
    1. replace the single quotes ‘ to '

      e.g. *.db_unique_name=‘STANDBY’ to *.db_unique_name='STANDBY'

      Delete