Oracle : Data Guard with two single instance 11gR2 databases

De Wiki de Romain RUDIGER
Aller à : navigation, rechercher

Introduction

The goal is to learn more about data guard by setting up a data guard replication between two single instance databases without Oracle Enterprise Manager.

I'll use two servers: nantes and strasbourg

Nantes is the primary database (db_name=db db_unique_name=db_prim)
Strasbourg will be the standby database (db_name=db db_unique_name=db_sec)

Primary database actions

This chapter present the primary database steps before creating a secondary standby database.

Install database

Install an empty single instance database: Oracle : install database 11g

Steps:

-Welcome: Next
-1: Create database
-2: General Purpose or Transaction processing
-3: Global database name: db
    SID: db
-4: Register or not this DB in EM and disable the Automatic Maintenance Tasks
-5: Set the password(s)
-6: Common location for all database files: /BDD/oradata
-7: no FRA
-8: no sample schema and no script
-9: >memory, set at least 303MB and enable Automatic Memory Management
    >character sets: AL32UTF8
-10: you can customize the storage here but for my test everything in the same dir is fine.
-11: create the DB

Enable FRA, force logging and archive log

[oracle@nantes ~]$ . oraenv
ORACLE_SID = [oracle] ? db
The Oracle base has been set to /BDD/oracle
[oracle@nantes ~]$ sqlplus '/ as sysdba'

select force_logging from v$database;

FOR
---
NO

select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup mount            
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  406848856 bytes
Database Buffers	  213909504 bytes
Redo Buffers		    3338240 bytes
Database mounted.

alter database archivelog;

Database altered.

alter database force logging;

Database altered.

show parameter recover

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /BDD/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 4122M
db_unrecoverable_scn_tracking	     boolean	 TRUE
recovery_parallelism		     integer	 0

!mkdir /BDD/fra

alter system set db_recovery_file_dest_size=2G scope=both;

System altered.

alter system set db_recovery_file_dest='/BDD/fra' scope=both;

System altered.

show parameter recover

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /BDD/fra
db_recovery_file_dest_size	     big integer 2G
db_unrecoverable_scn_tracking	     boolean	 TRUE
recovery_parallelism		     integer	 0

shutdown immediate 
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  406848856 bytes
Database Buffers	  213909504 bytes
Redo Buffers		    3338240 bytes
Database mounted.
Database opened.

select force_logging from v$database;

FOR
---
YES

select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

change the parameter file

SHOW parameter spfile
 
NAME	TYPE	 VALUE
------- -------- ------------------------------
spfile	string	 /BDD/oracle/product/11.2.0/dbs/spfiledb.ora
 
CREATE pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora' FROM spfile='/BDD/oracle/product/11.2.0/dbs/spfiledb.ora';

Edit the pfile:

db.__db_cache_size=213909504
db.__java_pool_size=4194304
db.__large_pool_size=4194304
db.__oracle_base='/BDD/oracle'#ORACLE_BASE set from environment
db.__pga_aggregate_target=218103808
db.__sga_target=411041792
db.__shared_io_pool_size=0
db.__shared_pool_size=176160768
db.__streams_pool_size=4194304
*.audit_file_dest='/BDD/oracle/admin/db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/BDD/oradata/db/control01.ctl','/BDD/oradata/db/control02.ctl'
*.db_block_size=8192
*.db_domain='novalan.priv'
*.db_name='db'
*.db_unique_name='db_prim'
*.dg_broker_config_file1='/BDD/oradata/db/dg1db_prim.dat'
*.dg_broker_start=FALSE
*.db_recovery_file_dest='/BDD/fra'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/BDD/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbXDB)'
*.log_archive_config='DG_CONFIG=(db_prim,db_sec)'
*.log_archive_dest_1='LOCATION=/BDD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db_prim'
*.log_archive_dest_2='SERVICE=db_sec VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=db_sec'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.memory_target=629145600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='shared'
*.undo_tablespace='UNDOTBS1'
*.fal_client='db_prim'
*.fal_server='db_sec'
  • DB_UNIQUE_NAME: stay unique even if the DB change of role
  • LOG_ARCHIVE_CONFIG: DG_CONFIG attribute list the DB_UNIQUE_NAME of the databases of this DG setup
  • LOG_ARCHIVE_DEST_1: archive redo from the local online redo log files to /BDD/arch
  • LOG_ARCHIVE_DEST_2: work only in primary role to send redo log to db_sec.
  • REMOTE_LOGIN_PASSWORDFILE: the password file is shared between DB.
  • FAL_SERVER: primary db
  • FAL_CLIENT: standby databases to send redo log

Enable the modifications:

[oracle@nantes ~]$ sqlplus '/ as sysdba'

Connected to an idle instance.

startup pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora';
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  406848856 bytes
Database Buffers	  213909504 bytes
Redo Buffers		    3338240 bytes
Database mounted.
Database opened.

CREATE spfile='/BDD/oracle/product/11.2.0/dbs/spfiledb.ora' FROM pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora';

File created.

shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup

Backup the database

With Recovery Manager:

[oracle@nantes ~]$ rman catalog 'rman/rman@rmandb' target /

connected to target database: DB (DBID=1570572138)
connected to recovery catalog database

RMAN> backup as compressed backupset database include current controlfile for standby;

Starting backup at 13-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/BDD/oradata/db/system01.dbf
input datafile file number=00002 name=/BDD/oradata/db/sysaux01.dbf
input datafile file number=00005 name=/BDD/oradata/db/APP_DATA01.dbf
input datafile file number=00003 name=/BDD/oradata/db/undotbs01.dbf
input datafile file number=00004 name=/BDD/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-FEB-13
channel ORA_DISK_1: finished piece 1 at 13-FEB-13
piece handle=/BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp tag=TAG20130213T151312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-FEB-13
channel ORA_DISK_1: finished piece 1 at 13-FEB-13
piece handle=/BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp tag=TAG20130213T151312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-FEB-13

[oracle@nantes ~]$ ls -lh /BDD/fra/DB_PRIM/backupset/2013_02_13/
total 287M
-rw-r----- 1 oracle dba 1.1M Feb 13 15:14 o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp
-rw-r----- 1 oracle dba 285M Feb 13 15:14 o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp
  • for standby: generate a new BDID

Secondary database actions

This chapter present the step to create a secondary standby database.

Copy the backup set of the primary

From the primary server:

[oracle@nantes ~]$ ssh strasbourg mkdir -p /BDD/fra/DB_PRIM/backupset/2013_02_13/
[oracle@nantes ~]$ scp /BDD/fra/DB_PRIM/backupset/2013_02_13/* strasbourg:/BDD/fra/DB_PRIM/backupset/2013_02_13/
o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp                                                 100% 1072KB   1.1MB/s   00:00    
o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp                                                 100%  285MB  57.0MB/s   00:05

Install database software only

Install only the RDBMS software, you can get the account creation and server customization here: Oracle : install database 11g.

Steps:

-Welcome: Next
-1: database software only
-6: Enterprise edition
-7: Base: /BDD/oracle
    Software location: /BDD/oracle/product/11.2.0
-8: Let default inventory configuration
-9: Administration group is dba

Setup oracle net

The tnsnames.ora is the same on primary and secondary oracle network folder.

[oracle@strasbourg ~]$ cat /BDD/oracle/product/11.2.0/network/admin/listener.ora
# listener.ora Network Configuration File: /BDD/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = strasbourg.novalan.priv)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /BDD/oracle

[oracle@strasbourg ~]$ cat /BDD/oracle/product/11.2.0/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /BDD/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB_PRIM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nantes.novalan.priv)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db_prim.novalan.priv)
    )
  )

DB_SEC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = strasbourg.novalan.priv)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db_sec.novalan.priv)
    )
  )

RMANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lab-oem.novalan.priv)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rmandb)
    )
  )

Get and change the parameter file

Copy the parameter file from the primary database (nantes).

Change :

*.db_unique_name='db_sec'
*.log_archive_dest_1='LOCATION=/BDD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db_sec'
*.log_archive_dest_2='SERVICE=db_sec VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=db_prim'
*.fal_client='db_sec'
*.fal_server='db_prim'

Start in nomount

Create some basic dir:

[oracle@strasbourg BDD]$ mkdir -p /BDD/oracle/admin/db/adump /BDD/oracle/admin/db/dpdump /BDD/oracle/admin/db/pfile /BDD/oradata/db /BDD/arch

Copy the password file:

[oracle@nantes ~]$ scp /BDD/oracle/product/11.2.0/dbs/orapwdb strasbourg:/BDD/oracle/product/11.2.0/dbs/orapwdb

Create spfile and startup in nomount mode:

[oracle@strasbourg BDD]$ . oraenv
ORACLE_SID = [oracle] ? db
ORACLE_HOME = [/home/oracle] ? /BDD/oracle/product/11.2.0
The Oracle base has been set to /BDD/oracle
[oracle@strasbourg BDD]$ sqlplus '/ as sysdba'

Connected to an idle instance.

CREATE spfile='/BDD/oracle/product/11.2.0/dbs/spfiledb.ora' FROM pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora';

File created.

startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  406848856 bytes
Database Buffers	  213909504 bytes
Redo Buffers		    3338240 bytes

Duplicate from the primary backup

The backup is at the same place in Strasbourg so its quite easy:

[oracle@strasbourg BDD]$ rman target sys/oracle@db_prim auxiliary sys/oracle

connected to target database: DB (DBID=1570572138)
connected to auxiliary database: DB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 13-FEB-13
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 13-FEB-13

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp
channel ORA_AUX_DISK_1: piece handle=/BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp tag=TAG20130213T151312
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/BDD/oradata/db/control01.ctl
output file name=/BDD/oradata/db/control02.ctl
Finished restore at 13-FEB-13

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/BDD/oradata/db/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/BDD/oradata/db/system01.dbf";
   set newname for datafile  2 to 
 "/BDD/oradata/db/sysaux01.dbf";
   set newname for datafile  3 to 
 "/BDD/oradata/db/undotbs01.dbf";
   set newname for datafile  4 to 
 "/BDD/oradata/db/users01.dbf";
   set newname for datafile  5 to 
 "/BDD/oradata/db/APP_DATA01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /BDD/oradata/db/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 13-FEB-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /BDD/oradata/db/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /BDD/oradata/db/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /BDD/oradata/db/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /BDD/oradata/db/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /BDD/oradata/db/APP_DATA01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp
channel ORA_AUX_DISK_1: piece handle=/BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp tag=TAG20130213T151312
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 13-FEB-13

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=807291174 file name=/BDD/oradata/db/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=807291174 file name=/BDD/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=807291174 file name=/BDD/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=807291174 file name=/BDD/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=807291174 file name=/BDD/oradata/db/APP_DATA01.dbf
Finished Duplicate Db at 13-FEB-13
  • switch clone datafile all -> set the restored datafiles as the current datafiles

At the end of the duplicate command, the state of the standby database is :

col DB_UNIQUE_NAME format a15
select NAME, DB_UNIQUE_NAME, LOG_MODE, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME  LOG_MODE     OPEN_MODE	    DATABASE_ROLE
--------- --------------- ------------ -------------------- ----------------
DB	  db_sec	  ARCHIVELOG   MOUNTED		    PHYSICAL STANDBY

The primary database state is:

col DB_UNIQUE_NAME format a15
select NAME, DB_UNIQUE_NAME, LOG_MODE, OPEN_MODE, DATABASE_ROLE from v$database;

NAME			       DB_UNIQUE_NAME  LOG_MODE     OPEN_MODE		 DATABASE_ROLE
------------------------------ --------------- ------------ -------------------- ----------------
DB			       db_prim	       ARCHIVELOG   READ WRITE		 PRIMARY

check redo transport

We must get the SCN of the backup from rman:

RMAN> list backupset tag=TAG20130213T151312;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    284.73M    DISK        00:01:11     13-FEB-13      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20130213T151312
        Piece Name: /BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_nnndf_TAG20130213T151312_8kq7vs2n_.bkp
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1822057    13-FEB-13 /BDD/oradata/db/system01.dbf
  2       Full 1822057    13-FEB-13 /BDD/oradata/db/sysaux01.dbf
  3       Full 1822057    13-FEB-13 /BDD/oradata/db/undotbs01.dbf
  4       Full 1822057    13-FEB-13 /BDD/oradata/db/users01.dbf
  5       Full 1822057    13-FEB-13 /BDD/oradata/db/APP_DATA01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    1.03M      DISK        00:00:01     13-FEB-13      
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20130213T151312
        Piece Name: /BDD/fra/DB_PRIM/backupset/2013_02_13/o1_mf_ncsnf_TAG20130213T151312_8kq7y59h_.bkp
  SPFILE Included: Modification time: 13-FEB-13
  SPFILE db_unique_name: DB_PRIM
  Standby Control File Included: Ckp SCN: 1822138      Ckp time: 13-FEB-13

The SCN is 1822138.

On the source database, let get the archived log sequences created after this SCN:

select SEQUENCE#, THREAD#, FIRST_CHANGE#, FIRST_TIME, ARCHIVED from V$ARCHIVED_LOG where FIRST_CHANGE# >= 1822138 AND DEST_ID = 1;

 SEQUENCE#    THREAD# FIRST_CHANGE# FIRST_TIM ARC
---------- ---------- ------------- --------- ---
	45	    1	    1825140 13-FEB-13 YES
	46	    1	    1825923 13-FEB-13 YES
	47	    1	    1826133 13-FEB-13 YES
	48	    1	    1826511 13-FEB-13 YES
	49	    1	    1826570 13-FEB-13 YES
	50	    1	    1859246 13-FEB-13 YES
	51	    1	    1890627 14-FEB-13 YES
	52	    1	    1917439 14-FEB-13 YES
	53	    1	    1918280 14-FEB-13 YES
	54	    1	    1918546 14-FEB-13 YES
  • DEST_ID -> 1 for the local archive log files to have one line by sequence number.

So the standby database must have receive all archived redolog since sequence number 44 (the SCN of the backup 1822138 is in this archive seq number).

On the secondary database, we can check that the archiver log file since the sequence number 44 have been received:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
	44 13-FEB-13 13-FEB-13
	45 13-FEB-13 13-FEB-13
	46 13-FEB-13 13-FEB-13
	47 13-FEB-13 13-FEB-13
	48 13-FEB-13 13-FEB-13
	49 13-FEB-13 13-FEB-13
	50 13-FEB-13 14-FEB-13
	51 14-FEB-13 14-FEB-13
	52 14-FEB-13 14-FEB-13
	53 14-FEB-13 14-FEB-13
	54 14-FEB-13 14-FEB-13

Without standby redo log files, the Remote File Server process (RFS) wait a full archived redo log file from the primary to get it:

db_prim -> Log Writer (LGWR) -> Online Redo Log files --> ARCH dest 1 -> Archive redo log file
                                                      '-> ARCH dest 2 -> Remote File Server process (RFS on stb srv) -\
                                                       \- ARCH dest 1 -> Archive redo log file -> Redo Apply -> db_sec

On the alert log on the secondary database, we can view the reception of the archived redo log files:

[oracle@strasbourg ~]$ grep -E "(RFS connections are allowed|Opened log for|Archived Log entry)" /BDD/oracle/diag/rdbms/db_sec/db/trace/alert_db.log
RFS connections are allowed
RFS[1]: Opened log for thread 1 sequence 44 dbid 1570572138 branch 806756333
RFS[2]: Opened log for thread 1 sequence 46 dbid 1570572138 branch 806756333
RFS[3]: Opened log for thread 1 sequence 45 dbid 1570572138 branch 806756333
Archived Log entry 1 added for thread 1 sequence 46 rlc 806756333 ID 0x5d9d936a dest 2:
Archived Log entry 2 added for thread 1 sequence 44 rlc 806756333 ID 0x5d9d936a dest 2:
Archived Log entry 3 added for thread 1 sequence 45 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[2]: Opened log for thread 1 sequence 47 dbid 1570572138 branch 806756333
Archived Log entry 4 added for thread 1 sequence 47 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[1]: Opened log for thread 1 sequence 48 dbid 1570572138 branch 806756333
Archived Log entry 5 added for thread 1 sequence 48 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[2]: Opened log for thread 1 sequence 50 dbid 1570572138 branch 806756333
RFS[1]: Opened log for thread 1 sequence 51 dbid 1570572138 branch 806756333
Archived Log entry 6 added for thread 1 sequence 50 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[3]: Opened log for thread 1 sequence 49 dbid 1570572138 branch 806756333
Archived Log entry 7 added for thread 1 sequence 51 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[2]: Opened log for thread 1 sequence 52 dbid 1570572138 branch 806756333
Archived Log entry 8 added for thread 1 sequence 49 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[1]: Opened log for thread 1 sequence 53 dbid 1570572138 branch 806756333
Archived Log entry 9 added for thread 1 sequence 52 rlc 806756333 ID 0x5d9d936a dest 2:
Archived Log entry 10 added for thread 1 sequence 53 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[3]: Opened log for thread 1 sequence 54 dbid 1570572138 branch 806756333
Archived Log entry 11 added for thread 1 sequence 54 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[4]: Opened log for thread 1 sequence 55 dbid 1570572138 branch 806756333

We can force redo log file switch and check the alert log on the standby database:

[oracle@nantes ~]$ sqlplus '/ as sysdba'
ALTER SYSTEM SWITCH LOGFILE;

[oracle@strasbourg ~]$ tail -f /BDD/oracle/diag/rdbms/db_sec/db/trace/alert_db.log
Thu Feb 14 11:48:42 2013
Archived Log entry 12 added for thread 1 sequence 55 rlc 806756333 ID 0x5d9d936a dest 2:
RFS[4]: No standby redo logfiles created
RFS[4]: Opened log for thread 1 sequence 56 dbid 1570572138 branch 806756333


Start redo apply

On the duplicate command we don't specify 'dorecover' action. So the database is mounted (the datafiles are locked by Oracle) and the redo log files have been received but not applied:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	44 13-FEB-13 13-FEB-13 NO
	45 13-FEB-13 13-FEB-13 NO
	46 13-FEB-13 13-FEB-13 NO
	47 13-FEB-13 13-FEB-13 NO
	48 13-FEB-13 13-FEB-13 NO
	49 13-FEB-13 13-FEB-13 NO
	50 13-FEB-13 14-FEB-13 NO
	51 14-FEB-13 14-FEB-13 NO
	52 14-FEB-13 14-FEB-13 NO
	53 14-FEB-13 14-FEB-13 NO
	54 14-FEB-13 14-FEB-13 NO
	55 14-FEB-13 14-FEB-13 NO

We must issue a database recover:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  • DISCONNECT FROM SESSION -> start the recover process in background :
Attempt to start background Managed Standby Recovery process (db)
MRP0 started with pid=27, OS id=23902 
MRP0: Background Managed Standby Recovery process started (db)

Just after issue the recover command, you can see the redo log files applied:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	44 13-FEB-13 13-FEB-13 YES
	45 13-FEB-13 13-FEB-13 YES
	46 13-FEB-13 13-FEB-13 YES
	47 13-FEB-13 13-FEB-13 YES
	48 13-FEB-13 13-FEB-13 YES
	49 13-FEB-13 13-FEB-13 YES
	50 13-FEB-13 14-FEB-13 YES
	51 14-FEB-13 14-FEB-13 YES
	52 14-FEB-13 14-FEB-13 IN-MEMORY
	53 14-FEB-13 14-FEB-13 NO
	54 14-FEB-13 14-FEB-13 NO
	55 14-FEB-13 14-FEB-13 NO

Snapshot Standby database

From Physical to Snapshot standby

Steps:

->stop recover process
->the database must be mounted not open
->convert to snapshot standby database
select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ ONLY WITH APPLY PHYSICAL STANDBY

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ ONLY	      PHYSICAL STANDBY

In the alert log, the MRP process stop:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Feb 14 14:07:52 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_mrp0_23902.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db)
Managed Standby Recovery Canceled (db)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Stop and mount the DB:

shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup mount
ORACLE instance started.
[...]
Database mounted.

Convert the physical standby database to a snapshot standby:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

In the alert log:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Starting background process RVWR
Thu Feb 14 14:14:31 2013
RVWR started with pid=24, OS id=24202 
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/14/2013 14:14:31
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1937375
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1570607978 (0x5d9d936a)
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_1_8ks9l55d_.log: Thread 1 Group 1 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_2_8ks9l5qp_.log: Thread 1 Group 2 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_3_8ks9l690_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1937373
Thu Feb 14 14:14:31 2013
Setting recovery target incarnation to 4
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY

Open now the database:

alter database open;

Database altered.

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      SNAPSHOT STANDBY

select FILE#, STATUS, ENABLED from v$datafile;

     FILE# STATUS  ENABLED
---------- ------- ----------
	 1 SYSTEM  READ WRITE
	 2 ONLINE  READ WRITE
	 3 ONLINE  READ WRITE
	 4 ONLINE  READ WRITE
	 5 ONLINE  READ WRITE

The redo log data from the primary database are still received bu not applied:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'NO' ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	60 14-FEB-13 14-FEB-13 NO
	61 14-FEB-13 14-FEB-13 NO
	62 14-FEB-13 14-FEB-13 NO

From Snapshot to Physical standby

Steps:

->the database must be mounted not open
->convert to snapshot standby database
->start redo apply
select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      SNAPSHOT STANDBY

Stop and mount the DB:

shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup mount
ORACLE instance started.
[...]
Database mounted.

Convert to Physical Standby:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

In the alert log:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (db)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point 
Stopping background process RVWR
Deleted Oracle managed file /BDD/fra/DB_SEC/flashback/o1_mf_8ksrsqcx_.flb
Deleted Oracle managed file /BDD/fra/DB_SEC/flashback/o1_mf_8ksrst5s_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 1571155318 (0x5da5ed76)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Thu Feb 14 14:32:36 2013
ARCH shutting down
ARC2: Archival stopped
Thu Feb 14 14:32:36 2013
ARCH shutting down
ARC0: Archival stopped
Thu Feb 14 14:32:36 2013
ARCH shutting down
ARC1: Archival stopped
Thu Feb 14 14:32:36 2013
ARCH shutting down
ARC3: Archival stopped
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY

Mount the database and start the standby recovering process:

alter database mount;

Database altered.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PHYSICAL STANDBY

Open Read only Standby Database

Without Real Time Redo Apply

Without the real time redo apply, the database apply only archived redo log file so you not exactly the same content between the primary and the standby database. Real Time Apply is subject to license.

Steps to be open in read only:

->stop recover process
->open the database
->start recover process
SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;
 
NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PHYSICAL STANDBY

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

alter database open read only;

Database altered.

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ ONLY	      PHYSICAL STANDBY

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ ONLY WITH APPLY PHYSICAL STANDBY

The redo log data from the primary database are still received ann applied:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'NO' ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	67 14-FEB-13 14-FEB-13 NO

Switchover

Switchover: Primary to Standby and Standby to Primary

These steps switch the roles between two databases:

->check if the switchover is possible
->switch the primary to standby mode
->shutdown and mount the primary
->switch the standby to primary mode
->open the new primary
->start redo apply on the new standby

Check if the switchover is possible:

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

From [oracle doc], the status can be TO STANDBY or SESSIONS ACTIVE.

You can also check if you have no redo gap on the standby database:

SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION STATUS     ARCHIVED_THREAD# ARCHIVED_SEQ#
----------- ---------  ---------------- -------------
/BDD/arch   VALID      1                76
db_sec      VALID      1                76

Initiate the switch over from PRIMARY to Physical Standby:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

In the alert log:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4395] (db)
Fri Feb 15 15:03:35 2013
Thread 1 advanced to log sequence 79 (LGWR switch)
  Current log# 1 seq# 79 mem# 0: /BDD/oradata/db/redo01.log
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 18273 user 'oracle' program 'oracle@nantes.novalan.priv'
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Archived Log entry 110 added for thread 1 sequence 78 ID 0x5d9d936a dest 1:
CLOSE: all sessions shutdown successfully.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential Physical Standby switchover target to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 79 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x1fd84c
ARCH: Noswitch archival of thread 1, sequence 79
ARCH: End-Of-Redo Branch archival of thread 1 sequence 79
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Archived Log entry 112 added for thread 1 sequence 79 ID 0x5d9d936a dest 1:
Deleted Oracle managed file /BDD/fra/db/DB/archivelog/2013_02_08/o1_mf_1_10_8k9tzz90_.arc
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /BDD/oracle/diag/rdbms/db_prim/db/trace/db_ora_4395.trc
Clearing standby activation ID 1570607978 (0x5d9d936a)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
Archivelog for thread 1 sequence 79 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
  • potential switchover target is: "LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target"
  • the control file is backup during this process

Shutdown and open in mount mode the old primary database:

shutdown abort;
ORACLE instance shut down.

startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  469763416 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    3338240 bytes
Database mounted.

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_prim			 MOUNTED	      PHYSICAL STANDBY

On the secondary database alert log you can see the switchover from the media recovery process:

Media Recovery Log /BDD/arch/1_79_806756333.dbf
Identified End-Of-Redo (switchover) for thread 1 sequence 79 at SCN 0x0.1fd84c
Resetting standby activation ID 1570607978 (0x5d9d936a)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing

Switch the Standby database to primary role:

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PRIMARY

In the alert log:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (db)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Fri Feb 15 15:16:39 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_mrp0_25879.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db)
Role Change: Canceled MRP
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Fri Feb 15 15:16:40 2013
SMON: disabling cache recovery
Backup controlfile written to trace file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_ora_29899.trc
SwitchOver after complete recovery through change 2086988
Online logfile pre-clearing operation disabled by switchover
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_1_8ks9l55d_.log: Thread 1 Group 1 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_2_8ks9l5qp_.log: Thread 1 Group 2 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_3_8ks9l690_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2086986
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

Open the new primary database:

ALTER DATABASE OPEN;

Database altered.

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      PRIMARY

At this stage, the former primary database now receive the archived redo log file from the new primary database:

Fri Feb 15 15:26:18 2013
Using STANDBY_ARCHIVE_DEST parameter default value as /BDD/arch
Fri Feb 15 15:26:21 2013
RFS[1]: Assigned to RFS process 6657
RFS[1]: Opened log for thread 1 sequence 82 dbid 1570572138 branch 806756333

Check the redo transport processing:

SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION	STATUS	  ARCHIVED_THREAD# ARCHIVED_SEQ#
--------------- --------- ---------------- -------------
/BDD/arch	VALID			 1	      85
db_prim 	VALID			 1	      85

Start recovery manager on the new primary standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Switchback: new Primary to Standby and Standby to Primary

Flip the servers status and repeat the steps of the switchover.

On the new Primary: switchover, stop and mount:

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
 
DESTINATION	STATUS	  ARCHIVED_THREAD# ARCHIVED_SEQ#
--------------- --------- ---------------- -------------
/BDD/arch	VALID			 1	      85
db_prim 	VALID			 1	      85

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

shutdown abort;
ORACLE instance shut down.

startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  406848856 bytes
Database Buffers	  213909504 bytes
Redo Buffers		    3338240 bytes
Database mounted.

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PHYSICAL STANDBY

On the Standby database: switchover and open

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_prim			 MOUNTED	      PHYSICAL STANDBY

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

alter database open;

Database altered.

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_prim			 READ WRITE	      PRIMARY

SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION	STATUS	  ARCHIVED_THREAD# ARCHIVED_SEQ#
--------------- --------- ---------------- -------------
/BDD/arch	VALID			 1	      89
db_sec		VALID			 1	      89

On the new Standby: check redo transport and restart recovery

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED = 'NO' AND DEST_ID = 2 ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
	86 15-FEB-13 15-FEB-13 NO
	87 15-FEB-13 15-FEB-13 NO
	88 15-FEB-13 15-FEB-13 NO
	89 15-FEB-13 15-FEB-13 NO

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, END_OF_REDO FROM V$ARCHIVED_LOG WHERE APPLIED ='NO' AND DEST_ID=2 ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED	 END
---------- --------- --------- --------- ---
	86 15-FEB-13 15-FEB-13 NO	 NO
	87 15-FEB-13 15-FEB-13 NO	 YES
  • The archived log received after the end of redo (switchover command) have been applied.

Configure Standby Redo Log

The Standby Redo Log must be at least the same or larger than the Redo Log files of the current primary database.

On the primary:

SELECT GROUP#, BYTES FROM V$LOG;

    GROUP#	BYTES
---------- ----------
	 1   52428800
	 2   52428800
	 3   52428800

col MEMBER format a30
SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER			  IS_
---------- ------- ------- ------------------------------ ---
	 3	   ONLINE  /BDD/oradata/db/redo03.log	  NO
	 2	   ONLINE  /BDD/oradata/db/redo02.log	  NO
	 1	   ONLINE  /BDD/oradata/db/redo01.log	  NO

To create the standby redo log files (primary and standby databases):

ALTER DATABASE ADD STANDBY LOGFILE ('/BDD/oradata/db/sredo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/BDD/oradata/db/sredo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/BDD/oradata/db/sredo03.log') SIZE 50M;

SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

    GROUP#	BYTES
---------- ----------
	 4   52428800
	 5   52428800
	 6   52428800

At the the recovery process (redo apply) start you have 'Managed Standby Recovery starting Real Time Apply' instead of 'Managed Standby Recovery not using Real Time Apply'.

Enable Flashback

shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size		    2230952 bytes
Variable Size		  469763416 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    3338240 bytes
Database mounted.

alter database flashback on;

Database altered.

alter system set db_flashback_retention_target=120;

System altered.

alter database open;

Database altered.

Data Guard Broker

Create the configuration

From the last steps, the Data Guard Broker is NOT running:

[oracle@nantes ~]$ sqlplus '/ as sysdba'

show parameter dg

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /BDD/oradata/db/dg1db_prim.dat
dg_broker_config_file2		     string	 /BDD/oracle/product/11.2.0/dbs/dr2db_prim.dat
dg_broker_start 		     boolean	 FALSE

[oracle@nantes ~]$ ps -edf | grep dmon | grep -v grep
[oracle@nantes ~]$ 

[oracle@strasbourg ~]$ sqlplus '/ as sysdba'

show parameter dg

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /BDD/oradata/db/dg1db_prim.dat
dg_broker_config_file2		     string	 /BDD/oracle/product/11.2.0/dbs/dr2db_sec.dat
dg_broker_start 		     boolean	 FALSE

[oracle@strasbourg ~]$ ps -edf | grep dmon | grep -v grep
[oracle@strasbourg ~]$

Start it on both instances:

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> exit

[oracle@nantes ~]$ ps -edf | grep dmon | grep -v grep
oracle   30820     1  0 10:32 ?        00:00:00 ora_dmon_db

Create the DG broker configuration:

[oracle@nantes ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/Oracle
Connected.
DGMGRL> create configuration 'db_DG_PROD' as primary database is 'db_prim' connect identifier is db_prim;
Configuration "db_DG_PROD" created with primary database "db_prim"
DGMGRL> add database 'db_sec' as connect identifier is db_sec;
Database "db_sec" added
DGMGRL> show configuration;

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_prim - Primary database
    db_sec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Edit the StaticConnectIdentifier (used to control instance as this will be static services)

edit database db_prim set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nantes)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db_prim_DGMGRL.novalan.priv)(INSTANCE_NAME=db)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

edit database db_sec set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=strasbourg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db_sec_DGMGRL.novalan.priv)(INSTANCE_NAME=db)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

Create the static service in the listeners configuration:

[oracle@nantes ~]$ cat /BDD/oracle/product/11.2.0/network/admin/listener.ora 
# listener.ora Network Configuration File: /BDD/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = nantes.novalan.priv)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (SID_NAME=db)(GLOBAL_DBNAME=db_prim_DGMGRL.novalan.priv)(ORACLE_HOME=/BDD/oracle/product/11.2.0))
  )

ADR_BASE_LISTENER = /BDD/oracle

[oracle@strasbourg ~]$ cat /BDD/oracle/product/11.2.0/network/admin/listener.ora
# listener.ora Network Configuration File: /BDD/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = strasbourg.novalan.priv)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (SID_NAME=db)(GLOBAL_DBNAME=db_sec_DGMGRL.novalan.priv)(ORACLE_HOME=/BDD/oracle/product/11.2.0))
  )

ADR_BASE_LISTENER = /BDD/oracle

Enable the configuration:

enable configuration

Enabled.

show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_prim - Primary database
      Warning: ORA-16789: standby redo logs not configured

    db_sec  - Physical standby database
      Warning: ORA-16789: standby redo logs not configured

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

On the standby alert log:

Mon Feb 18 10:32:20 2013
INSV started with pid=19, OS id=13644
Mon Feb 18 11:49:31 2013
NSV0 started with pid=31, OS id=13894
Mon Feb 18 11:49:36 2013
RSM0 started with pid=32, OS id=13904
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='db';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='db';
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET fal_server='db_prim' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Feb 18 11:49:41 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_mrp0_30981.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db)
Managed Standby Recovery Canceled (db)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY
Attempt to start background Managed Standby Recovery process (db)
Mon Feb 18 11:49:42 2013
MRP0 started with pid=28, OS id=13908
MRP0: Background Managed Standby Recovery process started (db)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 102 (in transit)
Mon Feb 18 11:49:48 2013
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY
Mon Feb 18 11:49:48 2013
Archived Log entry 69 added for thread 1 sequence 102 rlc 806756333 ID 0x5da76f5a dest 2:
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for thread 1 sequence 103 dbid 1570572138 branch 806756333
Mon Feb 18 11:49:52 2013
Media Recovery Log /BDD/arch/1_102_806756333.dbf
Media Recovery Waiting for thread 1 sequence 103 (in transit)

Alert log of the primary:

Mon Feb 18 11:44:44 2013
INSV started with pid=32, OS id=1114
Mon Feb 18 11:49:23 2013
NSV1 started with pid=37, OS id=1313
Mon Feb 18 11:49:36 2013
RSM0 started with pid=35, OS id=1322
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='db';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='db';
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Feb 18 11:49:48 2013
ALTER SYSTEM ARCHIVE LOG
Mon Feb 18 11:49:48 2013
Thread 1 advanced to log sequence 103 (LGWR switch)
  Current log# 1 seq# 103 mem# 0: /BDD/oradata/db/redo01.log
Archived Log entry 151 added for thread 1 sequence 102 ID 0x5da76f5a dest 1:
  • standby_file_management='MANUAL' -> if you create a new file on the primary DB, you must create it manually on the standby.

stop/start redo apply

Stop:

show database db_sec

Database - db_sec
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON

edit database db_sec set state = apply-off;

Succeeded.

show database db_sec

Database - db_sec
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF

Alert log:

Mon Feb 18 12:23:44 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Feb 18 12:23:45 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_mrp0_13908.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db)
Managed Standby Recovery Canceled (db)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Start:

show database db_sec

Database - db_sec
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF

edit database db_sec set state = apply-on;
Succeeded.

show database db_sec

Database - db_sec
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON

Alert log:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY
Attempt to start background Managed Standby Recovery process (db)
Mon Feb 18 12:26:55 2013
MRP0 started with pid=28, OS id=13999 
MRP0: Background Managed Standby Recovery process started (db)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 103 (in transit)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY

Snapshot Standby database

To snapshot

SQL> SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PHYSICAL STANDBY

DGMGRL> convert database db_sec to snapshot standby;
Converting database "db_sec" to a Snapshot Standby database, please wait...
Database "db_sec" converted successfully

DGMGRL> show database db_sec

Database - db_sec

  Role:            SNAPSHOT STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   3 minutes 58 seconds
  Apply Lag:       8 minutes 45 seconds

SQL> SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      SNAPSHOT STANDBY


Mon Feb 18 12:50:28 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Feb 18 12:50:28 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /BDD/oracle/diag/rdbms/db_sec/db/trace/db_mrp0_13999.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db)
Managed Standby Recovery Canceled (db)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/18/2013 12:50:29
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 2474529
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1571254106 (0x5da76f5a)
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_1_8ks9l55d_.log: Thread 1 Group 1 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_2_8ks9l5qp_.log: Thread 1 Group 2 was previously cleared
Online log /BDD/fra/DB_SEC/onlinelog/o1_mf_3_8ks9l690_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2474527
Mon Feb 18 12:50:30 2013
Setting recovery target incarnation to 5
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
Mon Feb 18 12:50:31 2013
Assigning activation ID 1571265056 (0x5da79a20)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /BDD/fra/DB_SEC/onlinelog/o1_mf_1_8ks9l55d_.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Feb 18 12:50:32 2013
SMON: enabling cache recovery
[13904] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:957549464 end:957550454 diff:990 (9 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Feb 18 12:50:38 2013
Starting background process QMNC
Mon Feb 18 12:50:38 2013
QMNC started with pid=25, OS id=14095 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
Mon Feb 18 12:50:42 2013
RFS[3]: Assigned to RFS process 14101
RFS[3]: No standby redo logfiles created
RFS[3]: Opened log for thread 1 sequence 107 dbid 1570572138 branch 806756333
Mon Feb 18 12:50:42 2013
RFS[4]: Assigned to RFS process 14107
RFS[4]: Opened log for thread 1 sequence 106 dbid 1570572138 branch 806756333
Archived Log entry 73 added for thread 1 sequence 106 rlc 806756333 ID 0x5da76f5a dest 2:
Mon Feb 18 12:50:46 2013
Starting background process CJQ0
Mon Feb 18 12:50:46 2013
CJQ0 started with pid=35, OS id=14135 
Mon Feb 18 12:50:47 2013
Starting background process SMCO
Mon Feb 18 12:50:47 2013
SMCO started with pid=33, OS id=14147 
Mon Feb 18 12:50:48 2013
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info; 
Mon Feb 18 12:50:55 2013
NSA2 started with pid=36, OS id=14177 
Mon Feb 18 12:50:58 2013
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 2 seq# 2 mem# 0: /BDD/fra/DB_SEC/onlinelog/o1_mf_2_8ks9l5qp_.log

To standby

SQL> SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      SNAPSHOT STANDBY

DGMGRL> show database db_sec

Database - db_sec

  Role:            SNAPSHOT STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   3 minutes 58 seconds
  Apply Lag:       8 minutes 45 seconds

DGMGRL> convert database db_sec to physical standby;
Converting database "db_sec" to a Physical Standby database, please wait...
Operation requires shutdown of instance "db" on database "db_sec"
Shutting down instance "db"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db" on database "db_sec"
Starting instance "db"...
ORACLE instance started.
Database mounted.
Continuing to convert database "db_sec" ...
Operation requires shutdown of instance "db" on database "db_sec"
Shutting down instance "db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db" on database "db_sec"
Starting instance "db"...
ORACLE instance started.
Database mounted.
Database "db_sec" converted successfully

DGMGRL> show database db_sec

Database - db_sec

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       (unknown)

SQL> SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 MOUNTED	      PHYSICAL STANDBY

Alter log (reduced):

Shutting down instance (immediate)
Instance shutdown complete
Starting ORACLE instance (normal)
alter database  mount
Completed: alter database  mount
alter database convert to physical standby
Completed: alter database convert to physical standby
Shutting down instance (immediate)
Instance shutdown complete
Starting ORACLE instance (normal)
alter database  mount
Physical Standby Database mounted.
Completed: alter database  mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY
Primary database is in MAXIMUM PERFORMANCE mode
Managed Standby Recovery not using Real Time Apply

Perform Switchover

DGMGRL> show configuration 

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_prim - Primary database
    db_sec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to db_sec;
Performing switchover NOW, please wait...
New primary database "db_sec" is opening...
Operation requires shutdown of instance "db" on database "db_prim"
Shutting down instance "db"...
ORACLE instance shut down.
Operation requires startup of instance "db" on database "db_prim"
Starting instance "db"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "db_sec"

DGMGRL> show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_sec  - Primary database
    db_prim - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Alert log of the old primary:

Mon Feb 18 15:15:53 2013 
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Switchover End-Of-Redo Log thread 1 sequence 120 has been fixed
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Shutting down instance (abort)
Mon Feb 18 15:16:05 2013
Instance shutdown complete
Mon Feb 18 15:16:06 2013
Starting ORACLE instance (normal)
Completed: alter database  mount
alter database  open
Beginning standby crash recovery.
Managed Standby Recovery starting Real Time Apply
Physical standby database opened for read only access.
Completed: alter database  open
Mon Feb 18 15:16:18 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Mon Feb 18 15:16:27 2013
Managed Standby Recovery starting Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Mon Feb 18 15:16:36 2013
Primary database is in MAXIMUM PERFORMANCE mode
Mon Feb 18 15:17:20 2013
Media Recovery Waiting for thread 1 sequence 126 (in transit)

Alert log of the standby (primary target):

Mon Feb 18 15:15:59 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
Media Recovery Log /BDD/arch/1_120_806756333.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Mon Feb 18 15:16:03 2013
ALTER DATABASE OPEN
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
PING[ARC2]: Heartbeat failed to connect to standby 'db_prim'. Error is 12514.
ORACLE Instance db - Archival Error. Archiver continuing.
FAL[server, ARC2]: Error 12528 creating remote archivelog file 'db_prim'
Error 12528 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'db_prim'. Error is 12528.
Mon Feb 18 15:16:17 2013
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Mon Feb 18 15:16:36 2013
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
ARC0: Standby redo logfile selected for thread 1 sequence 125 for destination LOG_ARCHIVE_DEST_2

The databases status:

[oracle@strasbourg ~]$ sqlplus '/ as sysdba'

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_sec			 READ WRITE	      PRIMARY

[oracle@nantes ~]$ sqlplus '/ as sysdba'

SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM v$database;

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DB	  db_prim			 READ ONLY WITH APPLY PHYSICAL STANDBY

Perform Failover

db_prim is the current primary database. I'll not the la SCN and then crash the server. After that, I will initiate a failover on db_sec.

Status:

DGMGRL> show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_prim - Primary database
    db_sec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Crash Nantes:

[oracle@nantes ~]$ /tmp/crash.sh

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 16:03:03 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
NAME	  DB_UNIQUE_NAME		 CURRENT_SCN
--------- ------------------------------ -----------
DB	  db_prim			     2535285

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Write failed: Broken pipe

Display the dg configuration:

DGMGRL> show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_prim - Primary database
    db_sec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12541: TNS:no listener
ORA-16625: cannot reach database "db_prim"
DGM-17017: unable to determine configuration status

SQL> select NAME, DB_UNIQUE_NAME, CURRENT_SCN FROM V$DATABASE; 

NAME	  DB_UNIQUE_NAME		 CURRENT_SCN
--------- ------------------------------ -----------
DB	  db_sec			     2535282

Alert log:
Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nantes.novalan.priv)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_prim_DGB.novalan.priv)(CID=(PROGRAM=oracle)(HOST=strasbourg)(USER=oracle))))
  • db_sec has not the same SCN.

Start the failover:

DGMGRL> failover to db_sec immediate;
Performing failover NOW, please wait...
Failover succeeded, new primary is "db_sec"

DGMGRL> show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_sec  - Primary database
    db_prim - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

in the alert log:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Recovered data files to a consistent state at change 2535283
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
RESETLOGS after incomplete recovery UNTIL CHANGE 2535283
Standby became primary SCN: 2535281
ALTER DATABASE OPEN
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Failover succeeded. Primary database is now db_sec.

Restart Nantes (old failed primary). The issue is that db_prim is at SCN 2535282 but db_sec recover at SCN 2535281. So we need to use flashback to return to the same SCN as the new primary database start.

On the new primary:

SELECT STANDBY_BECAME_PRIMARY_SCN FROM V$DATABASE;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
		   2535281

With dataguard broker, it's simple:

DGMGRL> reinstate database db_prim;
Reinstating database "db_prim", please wait...
Operation requires shutdown of instance "db" on database "db_prim"
Shutting down instance "db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db" on database "db_prim"
Starting instance "db"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db_prim" ...
Reinstatement of database "db_prim" succeeded
DGMGRL> show configuration

Configuration - db_DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    db_sec  - Primary database
    db_prim - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database db_prim

Database - db_prim

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       17 minutes 40 seconds
  Real Time Query: ON
  Instance(s):
    db

Database Status:
SUCCESS

In the alert log of the failed database:

Starting ORACLE instance (normal)
Completed: ALTER DATABASE   MOUNT
FLASHBACK DATABASE TO SCN 2535281
Using STANDBY_ARCHIVE_DEST parameter default value as /BDD/arch
Flashback Restore Complete
Flashback Media Recovery Start
Completed: FLASHBACK DATABASE TO SCN 2535281
alter database convert to physical standby
Completed: alter database convert to physical standby
Shutting down instance (immediate)
Instance shutdown complete
Starting ORACLE instance (normal)
Completed: alter database  mount
Data Guard: Failover target was Real Time Query standby; attempting to open this standby after reinstatement ...
ALTER DATABASE OPEN READ ONLY
Completed: ALTER DATABASE OPEN READ ONLY
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2535283
Primary database is in MAXIMUM PERFORMANCE mode

Ref

Oracle® Data Guard Concepts and Administration 11g

DG - Redo Transport Services 11g

DG - Commands reference

Oracle® Data Guard Broker 11g