Configure Oracle Dataguard
Today I am going to cover step by step configuration for Oracle Dataguard & may be in next or future posts I will discuss on common mistakes which you can do while configure dataguard / standby database.
Below steps are based on assumptions that Primary Database is on Machine1.domain.com & Standby database is on Machine2.domain.com . Database Instance Name is PROD and database listener is listening on port 1525. Mount point on primary & standby database are same (If they are not you need to set parameter db_file_convert)
Assumptions
I am assuming using LGWR, ASYNC option with log shipping service which means ,
LGWR (Log Writer process will be used to write to standby site instead of ARC archiver process)
ASYNC (Redo logs to standby is asynchronous to primary site)
You may have to change options with log shipping service (LOG_ARCHIVE_DEST_n) depending on data protection mode you wish to choose. (I am using Maximum Performance Mode – Default Mode)
Enable Archive log
For standby database configuration your primary database should be running in achieve log mode. In order to convert your primary database into archive log mode follow these steps
SQL> SHUTDOWN
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> alter system set LOG_ARCHIVE_DEST_1 =’LOCATION=/u01/oracle/data/arch’ scope=spfile; (I am assuming that you are using spfile here , if you are using pfile skip scope=spfile)
SQL> alter system set LOG_ARCHIVE_DEST_2 =’SERVICE=PROD_remote1.domain.com LGWR ASYNC REOPEN=60′ scope=spfile; (We will create above TNS Alias in next step)
SQL> alter system log_archive_dest_2=’DEFER’ (Defer this until you have standby system Up)
SQL> shutdown immediate
SQL> startup
You should see archive logs generated after this on standby site
Enable Force Logging
You should enable Force logging in primary database else if some transaction which doesn’t generate redo log can corrupt your standby database. (Careful in OLTP transactions or long running requests in Apps)
SQL> ALTER DATABASE FORCE LOGGING;
Set Initialization Parameter
SQL> alter system set fal_client =