Steps to configure physical standy database 11gr2 (backup based)

I have a database installed in server with database name prod

Primary DB_NAME=prod

Primary DB_UNIQUE_NAME=prod

Ip: 192.168.187.137

On physical standby we need to install only oracle software 11gr2, in this example standby database name will be stdby

Standby DB_NAME=prod

Standby DB_UNIQUE_NAME=stdby

Ip:192.168.187.132

Required parameters

DB_NAME                                                               -must be same on all standby databases

DB_UNIQUE_NAME                                            -must be different primaey and all standby databases

LOG_ARCHIVE_CONFIG                                 -this parameter includes db_unique_name of primary

                                                                                    And all standby databases

LOG_ARCHIVE_DEST_n                                 -define local and standby servers archive log location

LOG_ARCHIVE_DEST_STATE_n                 -defines the status ENABLE or DIFFER

REMOTE_LOGIN_PASSWORDFILE             -must be EXCLUSIVE

FALL_SERVER                                                     -use for acknowledge gap resolution( required in                    

                                                                                     Standby server only

DB_FILE_NAME_CONVERT                              - it will convert the datafiles location from primary to

                                                                                      Standby datafiles structure(if you have different 

                                                                                       File location in standby)

LOG_FILE_NAME_CONVERT                              -it will convert redo log file locations in primary to

                                                                                         Standby database (if you have different 

                                                                                        File location in standby)

STANDBY_FILE_MANAGEMENT                        -to keep auto file creation in standby

Steps to perform in primary server

Make sure database in archive log mode:

Sql> archive log list;

If not put it in archive log mode

SHUT IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Make sure database in force logging mode

Sql> select force_logging from v$database;

Sql>alter database force logging;

Check DB_NAME and DB_UNIQUE_NAME of primary database

Sql>show parameter db_name

Sql>show parameter db_unique_name

Configure listener.ora and tnsnames.ora in primary database for stdby and prod;

$netca (or) netmgr

Make db_unique name in the part of the datagaurd

Sql>alter system set LOG_ARCHIVE_CONFIG= ‘DG_CONFIG=(orcl,stdby)’;

Set archivelog destination

Sql>Alter System Set LOG_ARCHIVE_DEST_2=’SERVICE=stdby LGWR SYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=stdby’;

Sql>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set remote login password to exclusive

Sql>alter system set remote_login_passwordfile=exclusive scope=spfile;

Set fal server and file name convert parameter incase if  directory structure is different in primary and standby database

Sql>alter system set fal_server=stdby;

Sql>alter system set standby_file_management=auto;

If you want to change directory structure, in this example we are using same directory structure in both primary and standby so we are ignoring this parameter 

Sql> alter system set db_file_name_convert=’stdby’,’prod’

Sql> alter system set log_file_name_convert=’stdby’,’prod’

Check the log  members

Sql>select member from v$logfile;

-Now configure services prod

$vi tnsnames.ora

prod =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.187.137)(PORT = 1521))

    )

    (CONNECT_DATA =

      (ORACLE_SID = prod)

    (UR=A)

    )

  )

Now backup primary database using rman

mkdir -p /u01/backup/

$rman target=/

RMAN>backup database format='/u01/backup/';

Now create standby controlfile and pfile;

Sql>alter database create standby controlfile as ‘/u01/backup/stdcontrol.ctl’;

Sql>create pfile=’/u01/backup/initstdby.ora’ from spfile;

Add standby log files

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo01.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo02.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo03.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo04.log’) size 50M;

copy the pfile, password file, standby controlfile and backups to physical standby (/u01/backup):

$scp /u01/backup/* oracle@192.168.187.132:/u01/backup/

On physical standby:

-----------------------------

create reqiored directories by checking in pfile(adump,controlfile locations… etc)

$mkdir –p  /u01/app/oracle/fast_recovery_area/stdby/

$mkdir –p  /u01/app/oracle/oradata/stdby/

$mkdir –p  /u01/app/oracle/admin/stdby/adump/

copy standby controlfile to all locations

$cp /u01/backup/stdcontrol.ctl /u01/app/oracle/oradata/stdby/control01.ctl

$cp /u01/backup/stdcontrol.ctl /u01/app/oracle/fast_recovery_area/stdby/control02.ctl

Copy parameter file

$scp /u01/backup/initstdby.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstdby.ora

Copy remote login password file

$cp /u01/backup/orapworcl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstdby

Modify initstdby.ora file

$vi initstdby.ora

Replace prod with stdby

:%s/prod/stdby/g

and modify following

fal_server=prod

db_unique_name=’stdby’
log_archive_dest_2=’SERVICE=prod LGWR SYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=prod’;

If you want to change directory structure, in this example we are using same directory structure in both primary and standby

db_file_name_convert=’prod’,’stdby’

log_file_name_conver=’prod’,’stdby’

update /etc/oratab

vi /etc/oratab

stdby: /u01/app/oracle/product/11.2.0/dbhome_1:N

:wq

Create service for prod

$netmgr

Start listener

$lsnrctl start

Restore backup on standby

if you copied backups to different location apart from '/u01/backup'  we have to catalog the backups as below.

RMAN> catalog start with '<backup_location>'

Restore backupfile

$rman target=/

RMAN>startup mount

RMAN>restore database;

now check  standby redo logs

sql>select member from v$logfile;

Add standby log files in standby

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo01.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo02.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo03.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo04.log’) size 50M;

now start redo apply process on standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For active datagaurd (form 11g)

Sql>startup

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

check the active datagaurd status in standby

sql>select open_mode from v$database;

OPEN_MODE

---------------------

READ ONLY WITH APPLY

If we want to change protection modes

In primary

Sql> alter database set standby to maximize availability/protection/performance;

Queries for standby database:

SQL> select process, status from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

MRP0      WAIT_FOR_LOG

SQL> select SEQUENCE#, applied from v$archived_log;

 SEQUENCE# APPLIED

---------- ---------

         8 YES

         9 YES

         7 YES

        10 YES

        11 YES

        12 YES

        13 YES

        14 YES

        15 YES

SQL> select protection_mode from v$database;

PROTECTION_MODE

--------------------

MAXIMUM AVAILABILITY

SQL> select * from v$archive_gap;

no rows selected