Steps to configure 11g Physical standby database

Steps to configure 11g Physical standby database:
=====================================

Primary side:
Primary DB_NAME = dell
Priamry DB_UNIQUE_NAME = dell
IP = 192.168.1.100

standby side:
Standby DB_NAME = dell
Standby DB_UNIQUE_NAME = std
IP = 192.168.1.101

Required Parameters:
DB_NAME - must be same on primary and on all standby
DB_UNIQUE_NAME - Must be different on primary adn standby
LOG_ARCHIVE_CONFIG - this parameter includes db_unique_name which are the part of dataguard configuration
LOG_ARCHIVE_DEST_n - local and remote archive log location
LOG_ARCHIVE_DEST_STATE_n - state of archiving (ENABLE OR DEFER)
REMOTE_LOGIN_PASSWORDFILE - EXCLUSSIVE
FAL_SERVER - use for archivelog gap resolution
DB_FILE_NAME_CONVERT - when directory structure is differ
LOG_FILE_NAME_CONVERT - when directory structure is differ
STANDBY_FILE_MANAGEMENT - Keep auto to create files automatically on standby

Steps:
=====

Perform the following steps on primary

Make database in archivelog mode
=========================
to verify the archive log mode

SQL> select log_mode from v$database;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

make sure database is in force logging mode
================================
SQL> select force_logging from v$database;
SQL> alter database force logging;

Verify the DB_NAME and DB_UNIQUE_NAME
====================================

SQL> show parameter db_name
SQL> show parameter db_unique_name

**************************************************************
Note: Configure the required services (dell n std) from netmgr
192.168.1.100$netmgr
to create the std service on the stanby datbase(192.168.1.101)
192.168.1.100$lsnrctl start
**************************************************************

Make sure DB_UNIQUE_NAME to be part of dataguard (std is stndby name)
========================================================

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

set the archive log destination
=====================

alter system set log_archive_dest_2='service=std valid_for=(online_logfile, primary_role)db_unique_name=std';

alter system set log_archive_dest_state_2=enable;

set remote login password to exclusive
============================

altter system set remote_login_passwordfile=exclusive scope=spfile;

show parameter remote_login

Set fal server and file name covert parameter in case if directory structure is different in primary and standby database:

alter system set fal_server=std
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 standby_file_management=auto;

Now backup the primary database using RMAN
===================================

$ rman target /
RMAN> backup database plus archivelog;

Now create standby controlfile and spfile
=============================

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

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

modify the initstd.ora file
==================
1. change db_unique_name
2. change fal_server
3. change log_arhive_dest_n
4. change location of controlfile

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

# standby controlefile to all location:
scp /tmp/stdcontrol.ctl oracle@192.168.1.101:/u01/app/oracle/oradata/std/contole01.ctl
cp /u01/app/oracle/oradata/std/contole01.ctl /u01/app/oracle/flash_ercovery_area/std/control02.ctl

# archivelogs and backupsets:
scp -r /u01/app/oracle/flash_recovery_area/DELL oracle@192.168.1.101:/u01/app/oracle/flash_ercovery_area/

# parameter file:
scp /tmp/initstd.ora oracle@192.168.1.101:/tmp/initstd.ora

# remote login passwordfile:
scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwddell oracle@192.168.1.101:/u01/app/oracle/product/dbhome_1/dbs/

On Physical standby server
====================

create the services on physical standby database and update /etc/oratab file

$netmgr

Now start listener on both server

lsnrctl start

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

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 pfille='/tmp/inistd.ora';
exit

restore backupfile
=============
rman target=/
startup mount
restore database;

create standby redolog files on primary and standby database

standby
======
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 the 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 new 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 from v$archived_log order by sequence#;

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

############### Steps to configure Read Only Standby ################
========================================================
shutdown immediate;
startup mount;
alter database open read only;
select * from scott.emp;

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

during the read only mode arch logs will be shipped to standby but will not be applied

############### Steps to configure mount Standby ################
=====================================================
shutdown immediate;
startup mount;
alter database recover managed stadby database disconnect from session;

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

############### Active dataguard configuration ################
===================================================
shutdown immediate;
startup mount;
alter database open read only;
alter database recover managed standby database disconnect from session;

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

select * from scott.tab1;

############### snapshot standby configuration ################
===================================================
you can open the database in read write  mode.

archive logs will be shipped from primary to standby but it will not be applied to standby

make sure recover process is not running

shutdown immediate;
startup mount;

and make sure flashback is on

SQL> select flashback_on from v$database;
SQL> alter database convert to snapshot standby;

alter database open;
select flashback_on from v$database;
above sql gives you the restore point info.

as soon as you cancel the snapshot all the changes will be rolled back.

create tabel scott.tab2 as select * from scott.emp;

select * from scott.tab2;

############### switch back to physical standby ################
===================================================
shutdown immediate;
startup mount;
alter database convert to physical standby;

shutdown immediate;
startup nomount;

alter database mount standby database;
alter database recover managed standby database disconnect;

select flashback_on from v$database;
which should say no.

select * from scott.tab2
which should say table does not exist.

############### database switch over roles ################
===============================================
on Primary:
select name, open_mode, database_role from v$database;
- role should be primay

on standby:
select name, open_mode, database_role from v$database;
- role should be physical standby

on Primary:
alter database commit to switchover to standby;

shutdown immediate;
startup nomunt;

alter database mount standby database;
alter database recover managed standby database disconnect from session;

select name, open_mode, database_role from v$database;
- now primary is in physical standby

on standby:
alter database commit to switch to primary;

shutdown immediate;
startup;

select name, open_mode, database_role from v$database;
- now standby is in primary mode

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

alter system switch logfile;

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

on other database:
select sequence#,first_time,next_time from v$archived_log order by sequence#;

Regards,

Mallik