Steps to configure 11g Physical Standby Database

Oracle rdbms software is installed with one database(dell) following is details:

Primary DB_NAME=dell                              IP - 192.168.56.105

Primary DB_UNIQUE_name=dell

On Physical standby I have installed only rdbms software (std will be my standby database)

Standby DB_NAME=dell                              IP - 192.168.56.106

Standby DB_UNIQUE_name=std

Required Parameters:

Parameter	Recommended Setting for Standby Database
DB_Name	Must be same on parimary and on all dtandby
DB_UNIQUE_NAME	Must be different on parimary and on all dtandby
LOG_ARCHIVE_CONFIG	This parameter includes db_unique_name which are the part of Dataguard configuration
LOG_ARCHIVE_DEST_n	Define local and remote archive log file location
LOG_ARCHIVE_DEST_STATE_n	Define state of archiving (ENABLE or DIFER)
REMOTE_LOGIN_PASSWORDFILE	Must be in EXCLUSIVE Mode
FAL_SERVER	Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT	required when directory structure is different datafile
LOG_FILE_NAME_CONVERT	required when directory structure is different logfile
STANDBY_FILE_MANAGEMENT	Keep auto to create file automatically on standby

Steps

Perform following steps on Primary database:

Make sure database is in archivelog mode

select name from v$database;

if note use following command to change to archive mode

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

Make sure database is in force logging mode

select force_logging from v$database;

alter database force logging;

Verify DB_NAME and DB_UNIQUE_NAME of Primary Database

show parameter db_name

show parameter db_unique_name

Make DB_UNIQUE_NAME to be part of dataguard.(std service we will create soon)

alter system set log_archive_config='dg_config=(dell,std)';

Set archivelog destinations

alter system set log_archive_dest_1='location=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=dell' scope=both;

alter system set log_archive_dest_2='service=std LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=std' scope=both;

alter system set log_archive_dest_state_1='ENABLE' scope=both;

alter system set log_archive_dest_state_2='ENABLE' scope=both;

Set remote login password to exclusive

alter system set remote_login_passwordfile=exclusive scope=spfile;

show parameter remote_login

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

alter system set fal_server=std scope=both;

alter system set fal_client=std scope=both;

alter system set db_file_name_convert='std','dell' scope=spfile;

alter system set log_file_name_convert='std','dell' scope=spfile;

alter system set log_archive_max_processes=10 scope=both;

alter system set standby_file_management=auto scope=both;

Now configure required service (dell and std)

netmgr

Now backup Primary Database using rman

rman target /

RMAN> backup database plus archivelog;

Now Create standby controlfile and pfile

alter database create standby controlfile as '/tmp/stdcontrol.ctl';

create pfile='/tmp/initstd.ora' from spfile;

Modify initstd.ora file

1.     Change db_unique_name

2.     Change fal_server

3.     Change log_archive_dest_n

4.     Change location of controlfile

ps: Standby add directory

[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/std/adump

[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/std/

[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/std/

Create appropriate directory on physical standby and copy backupset, archivelog, pfile, standby controlfile and password file to physical standby database

# Standby controlfile to all locations.

scp /tmp/stdcontrol.ctl oracle@standby:/u01/app/oracle/std/control01.ctl

cp control01.ctl /u01/app/oracle/fast_recovery_area/std/control02.ctl

Archivelogs and backups

scp -r /u01/app/oracle/fast_recovery_area/DELL oracle@standby:/u01/app/oracle/fast_recovery_area/

# Parameter file.

scp /tmp/initstd.ora oracle@standby:/tmp/initstd.ora

# Remote login password file.

scp /u01/app/oracle/product/11.2.0.4/db/dbs/orapwdell oracle@standby:/u01/app/oracle/product/11.2.0.4/db/dbs/

change orapwdell to orapwstd

On physical standby server

Create service on physical standby database and update /etc/oratab file

netmgr

Update /etc/oratab (std: /u01/app/oracle/product/11.2.0/dbhome_1:N)

Now start listener on both server

lsnrctl start

Restore backup on standby:

create spfile from pfile;

vi /etc/oratab

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

. oraenv

std

sqlplus / as sysdba

create spfile from pfile=’/tmp/initstd.ora’

exit

Restore backupfile

rman target /

startup mount

restore database;

Create standby redolog files on primary and standby database

alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo01.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo02.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo03.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo04.log') size 50M;

select member from v$logfile where type='STANDBY';

On Primary Database

alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo01.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo02.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo03.log') size 50M;

alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo04.log') size 50M;

select member from v$logfile where type='STANDBY';

                                                 

Now start redo apply process on standby

alter database recover managed standby database disconnect from session;

Following command is use to stop redo apply process

alter database recover managed standby database cancel;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sequence#, first_time, next_time from v$archived_log order by sequence#;

alter system switch logfile;

Check the now archived redo log has arrived at the standby server and been applied.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

select name, open_mode, database_role, db_unique_name, protection_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

PROTECTION_MODE

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

DELL      MOUNTED              PHYSICAL STANDBY std

MAXIMUM PERFORMANCE

Steps to Configure Read Only Standby

step standby

shutdown immediate;

startup mount;

alter database open read only;

Change start redo apply process on standby

shutdown immediate;

startup mount;

alter database recover managed standby database disconnect from session;

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

Active Data Guard aconfiguration

step standby

shutdown immediate;

startup mount;

alter database open read only;

alter database recover managed standby database disconnect from session;

select name, open_mode, database_role, protection_mode from v$database;

Snapshot Standby Configuration

step standby

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

SQL> create table hr.emp5 as select * from hr.employees;

Table created.

SQL> select count(*) from hr.emp5;

  COUNT(*)

----------

       107

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

shutdown again

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> select count(*) from hr.emp5;

select count(*) from hr.emp5

                        *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

Database Switchover Roles

step standby

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME      OPEN_MODE  DATABASE_ROLE    PROTECTION_MODE

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

DELL      MOUNTED    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

step primary

SQL> select name, open_mode, database_role, db_unique_name, protection_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

PROTECTION_MODE

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

DELL      READ WRITE           PRIMARY          dell

MAXIMUM PERFORMANCE

step primary

SQL> alter database commit to switchover to standby;

SQL> shutdown immediate;

SQL> start nomount;

SQL> alter database mount standby database;

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

SQL> select name, open_mode, database_role, db_unique_name, protection_mode from v$database;

step standby

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate;

SQL> startup

SQL> select name, open_mode, database_role, protection_mode from v$database;

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

SQL> alter system switch logfile;

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

step primary

SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;