Standby Database

Physical Standby Database

Steps :

1. Prepare of standby database.

2. Prepare of primary database

3. Configure physical standby

Given :

Source (Primary) database = orcl

Target (Standby) database = angel

All database in single node/machine.

** Database is in archive log mode**

**Note :- If Primary & Standby database is different node / machine , then add the machine IP address & Hostname entry on each machine.

Prepare the Standby database

1. Create password file for database :-

     

 Go to dbs folder :-

oracle@r0179] cd  /10g/product/10.2.0/db_1/dbs

oracle@r0179] orapwd file= orapwangel password=sys entries=3

2. Create pfile for target database :-

Go to dbs folder and copy Primary pfile and paste Standby pfile and edit :-

**Note :-  If pfile is no there then create pfile from spfile.

oracle@r0179]  cd  /10g/product/10.2.0/db_1/dbs

oracle@r0179]  cp initorcl.ora initangel.ora

oracle@r0179]  vi initangel.ora { :%s/orcl/angel/g}

and ** And add these parameters in target pfile:- **

*.db_name='orcl'    # PRIMARY DATABASE SID

*.db_unique_name=angel  # STANGBY DATABASE SID

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,angel)'

*.LOG_ARCHIVE_DEST_1=

'LOCATION=/10g/flash_recovery_area/ANGEL/archivelog

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=angel'

*.LOG_ARCHIVE_DEST_2=

'SERVICE=orcl

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=orcl'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_SERVER=orcl

*.FAL_CLIENT=angel

*.STANDBY_FILE_MANAGEMENT=AUTO

*.Db_file_name_convert='/10g/oradata/orcl','/10g/oradata/angel'

*.Log_file_name_convert='/10g/oradata/orcl','/10g/oradata/angel'

**Note :-  1. Check the path and save the file

                2. You can chenge the log format

                3. db_name should be primary database name.

3.  Create Spfile from pfile :-

 Create  to dbs folder :-

oracle@r0179] export ORACLE_SID=angel

oracle@r0179] sqlplus / as sysdba

sql> startup nomount

sql> create spfile from pfile

sql> shutdown immediate;

4. Create folder :-

Go to admin folder :-

oracle@r0179] cd /10g/oracle/admin/

oracle@r0179] mkdir angel

oracle@r0179] cd angel

oracle@r0179] mkdir bdump adump udump cdump dpdump

5. Configure tns entry for target database :-

By netca or add database enter in tnsname.ora

6. Listener restart & check tnsping:-

oracle@r0170] lsnrctl stop

oracle@r0170] lsnrctl start  {tnsping orcl & tnsping angel}

Prepare the Primary database                                                                                                                                         

1.    In Primary database enable the two paraneter in mount stage :-

oracle@r0179] export ORACLE_SID=orcl

oracle@r0179] sqlplus / as sysdba

sql> startup mount

sql> alter database archivelog;

sql> alter database force logging;

2. Create logfile & controlfile for standby in primary database :-

sql> alter database open;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/10g/oradata/angel/stdby01.log' size 50m;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/10g/oradata/angel/stdby02.log' size 50m;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/10g/oradata/angel/stdby03.log' size 50m;

sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/10g/oradata/angel/control_sb01.ctl';

sql> create pfile from spfile;

sql> shutdown immediate;

**Note:- Standby logfile and controlfile path is save in target database ($ORACLE_BASE/oradata/angel )

3.  Edit primary parameter :-

** And add these parameters in primary pfile:- **

*.db_name=orcl

*.db_unique_name=orcl

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,angel)'

*.LOG_ARCHIVE_DEST_1=

'LOCATION=/10g/flash_recovery_area/ORCL/archivelog

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=orcl'

*.LOG_ARCHIVE_DEST_2=

'SERVICE=angel LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=angel'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.FAL_SERVER=angel

*.FAL_CLIENT=orcl

*.STANDBY_FILE_MANAGEMENT=AUTO

*.Db_file_name_convert='/10g/oradata/orcl','/10g/oradata/angel'

*.Log_file_name_convert='/10g/oradata/orcl','/10g/oradata/angel'

4.  Create Spfile from pfile :-
 Create  to dbs folder :-

oracle@r0179] export ORACLE_SID=orcl

oracle@r0179] sqlplus / as sysdba

sql> startup mount

sql> create spfile from pfile

sql> shutdown immediate;

Configure physical standby

1. Copy the datafile + standby log file + standby controlfile to standy database and multiplexing og cont :-

oracle@r0179] cp /10g/oradata/orcl/*.dbf ../angel

2.Create spfile from pfile :-

oracle@r0179] export ORACLE_SID=angel

oracle@r0179] sqlplus / as sysdba

sql> startup mount;

sql> create spfile from pfile;

sql> shutdown immediate;

3. Start the bothe database :-

oracle@r0179] export ORACLE_SID=orcl

oracle@r0179] sqlplus / as sysdba

sql> startup;

oracle@r0179] export ORACLE_SID=angel

oracle@r0179] sqlplus / as sysdba

sql> startup mount;

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

FOR READ ONLY (STANBY DATABASE):-

sql> alter database recover managed standby database cancel;

sql> alter database open read only;

Objective :- Physical Standby is completed  

 SQL> select sequence# from v$archived_log;

*****************************************END***************************************************