Configuring DATA GUARD ( with not even a single error)

 Database Steps

First, your primary database needs to be in ARCHIVE LOG MODE.

Both PRIMARY and STANDBY need to have same OS version and oracle database version.

Here, in my example

---PRIMARY DATABASE : prod

---STANDBY DATABASE : prods

---C:\prod_archives à location of prod archives

---C:\prods_archives àlocation of prods archives on another machine

---C:\rman_prod_backup àlocation of backup set and controlfiles on primary

      database machine.

---Install oracle on standby machine without starter database.

1.CONFIGURING PARAMETER FILE OF PRIMARY  DATABASE (prod)

Sql> create pfile=’C:\prod.ora’ from spfile;

My pfile is :-

prod.__db_cache_size=176160768

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=96468992

prod.__streams_pool_size=0

audit_file_dest='D:\oracle\product\10.2.0\admin\prod\adump'

background_dump_dest='D:\oracle\product\10.2.0\admin\prod\bdump'

compatible='10.2.0.1.0'

control_files='D:\oracle\product\10.2.0\oradata\prod\control01.ctl','D:\oracle\product\10.2.0\oradata\prod\control02.ctl','D:\oracle\product\10.2.0\oradata\prod\control03.ctl'

core_dump_dest='D:\oracle\product\10.2.0\admin\prod\cdump'

db_block_size=8192

db_cache_size=176160768

db_domain=''

db_file_multiblock_read_count=16

DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\prods\','D:\oracle\product\10.2.0\oradata\prod\'

db_name='prod'

db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'

db_recovery_file_dest_size=2147483648

DB_UNIQUE_NAME='prod'

FAL_CLIENT='prod'

FAL_SERVER='prods'

INSTANCE_NAME='prod'

java_pool_size=4194304

job_queue_processes=10

large_pool_size=4194304

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prods)'

log_archive_dest_1='location=C:\prod_archives VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_2='SERVICE=prods LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prods'

LOG_ARCHIVE_DEST_STATE_1='ENABLE'

LOG_ARCHIVE_DEST_STATE_2='ENABLE'

LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'

LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\prods\','D:\oracle\product\10.2.0\oradata\prod\'

open_cursors=300

pga_aggregate_target=95420416

processes=150

REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

SERVICE_NAMES='prod'

POINTS TO BE NOTED :-

---Use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT if locations on  primary  database (prod) and standby database (prods) differ.

---LOG_ARCHIVE_DEST_1 declares the archiving location on primary database and LOG_ARCHIVE_DEST_2 declares where the logs have to be transferred.

---LOG_ARCHIVE_DEST_STATE_1 and LOG_ARCHIVE_DEST_STATE_2 need to be enabled , so as to transport logs from primary to standby.

2.CREATE AN INSTANCE ON STANDBY WITH SAME PASSWORD AS THAT OF PRIMARY DATABASE

Sql>  oradim –new  –sid  prods  –intpwd  sagar  –startmode  auto

3.CREATE DESIRED LOCATIONS(FOLDERS) ON STANDBY

4.CREATE PFILE FOR STANDBY DATABASE

prods.__db_cache_size=176160768

prods.__java_pool_size=4194304

prods.__large_pool_size=4194304

prods.__shared_pool_size=96468992

prods.__streams_pool_size=0

audit_file_dest='C:\oracle\product\10.2.0\admin\prods\adump'

background_dump_dest='C:\oracle\product\10.2.0\admin\prods\bdump'

compatible='10.2.0.1.0'

control_files='C:\oracle\product\10.2.0\oradata\prods\control01.ctl','C:\oracle\product\10.2.0\oradata\prods\control02.ctl','C:\oracle\product\10.2.0\oradata\prods\control03.ctl'

core_dump_dest='C:\oracle\product\10.2.0\admin\prods\cdump'

db_block_size=8192

db_cache_size=176160768

db_domain=''

db_file_multiblock_read_count=16

DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\prod\','C:\oracle\product\10.2.0\oradata\prods\'

db_name='prod'

db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'

db_recovery_file_dest_size=2147483648

DB_UNIQUE_NAME='prods'

FAL_CLIENT='prods'

FAL_SERVER='prod'

INSTANCE_NAME='prods'

java_pool_size=4194304

job_queue_processes=10

large_pool_size=4194304

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prods)'

log_archive_dest_1='location=C:\prods_archives VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prods'

LOG_ARCHIVE_DEST_2='SERVICE=prod LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_STATE_1='ENABLE'

LOG_ARCHIVE_DEST_STATE_2='ENABLE'

LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'

LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\prod\','C:\oracle\product\10.2.0\oradata\prods\'

open_cursors=300

pga_aggregate_target=95420416

processes=150

REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

SERVICE_NAMES='prods'

sga_target=287309824

shared_pool_size=96468992

STANDBY_FILE_MANAGEMENT='AUTO'

streams_pool_size=0

undo_management='AUTO'

undo_tablespace='UNDOTBS1'

user_dump_dest='C:\oracle\product\10.2.0\admin\prods\udump'

5.CONFIGURE tnsnames.ora AND listener.ora ON PRIMARY DATABASE AND STANDBY DATABASE

6.TAKING BACKUP OF PRIMARY DATABASE USING RMAN

ON PRIMARY :

SQL> set oracle_sid=prod

SQL> RMAN

RMAN> connect target;

RMAN> backup database include current controlfile for standby plus archivelog;

7.COPY ALL BACKUP AND ARCHIVES FROM PRIMARY DATABASE TO STANDBY DATABASE

--copy C:\rman_prod_backup and C:\prod_archives from primary to standby at same location with same name i.e C:\rman_prod_backup and C:\prod_archives.

8.CREATING STANDBY DATABASE FROM BACKUP TAKEN AT PRIMARY DATABASE

Before doing it, our primary database must be open and standby database must be not mounted.

At primary :

 Sql >startup mount pfile = ‘C:\prod.ora’;

 Sql>alter database open;

 Sql>create spfile from pfile = ‘C:\prod.ora’;

At standby :

Sql>startup nomount pfile = ‘C:\prods.ora’;

At standby open another cmd window :

Sql > set_oracle_sid = prods

Sql>rman

Rman> connect target sys/sagar@prod auxiliary sys/sagar@prods

Rman>duplicate target database for standby nofilenamecheck dorecover;

SOME USEFUL COMMANDS :-

Sql> select sequence# , first_time , next_time from v$archived_log;

Sql> select sequence# , applied from v$archived_log order by sequence#;

Sql> select current_scn from v$database;

Sql>select archived_thread# , archived_seq# , applied_thread# , applied_seq# from  v$archive_dest_status;

Sql> select max(sequence#) from v$log_history;

NOTE:  if logs are not being applied on standby database, then use:

Sql> alter database recover managed standby database disconnect from session;