Creating Standby Database – 11gR2 using ” FOR STANDBY FROM ACTIVE DATABASE” clause

Year 2011 comes with 11gR2 for me 🙂

As the title says, here i will demonstrate one of the way to create standby database which i did on a 11gR2 version windows box.The primary and the standby exists on the same box.

Primary DB Instance name – MATRIX
Standby DB Instance name – MATSTDBY

As the standby was also on the same box so changed the instance name for the standby to “MATSTDBY” .

1. Edited the TNSNAMES.ora and the LISTENER.ora file to have the below entry


TNSNAMES.ora
==============
MATRIX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ANAND-LAP) (PORT=1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MATRIX )
    )
  )
  
  
MATSTDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ANAND-LAP) (PORT=1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MATSTDBY)
    )
  )

LISTENER.ora
=============
Added the below in SID_LIST_LISTENER -

(SID_DESC =
          (GLOBAL_DBNAME = MATRIX)
          (ORACLE_HOME = D:\oracle\app\product\11.2.0\dbhome_1)
          (SID_NAME = MATRIX)
    )
    (SID_DESC =
          (GLOBAL_DBNAME = MATSTDBY)
          (ORACLE_HOME = D:\oracle\app\product\11.2.0\dbhome_1)
          (SID_NAME = MATSTDBY)
   )

Once, the changes has been done, check the network connectivity using the TNSPING .It must be OK.

2. Check pre-requisites and change the required parameter on Primary database

PRIMARY DB SESSION
====================

SYS@MATRIX> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.10
SYS@matrix> select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING from  v$database;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    FOR
--------- ------------ -------------------- ---------------- ---
MATRIX    NOARCHIVELOG READ WRITE           PRIMARY          NO

Elapsed: 00:00:00.20
19:52:08 SYS@matrix>
19:52:09 SYS@matrix> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     8
Current log sequence           10

Change the database to Archivelog mode and enable force logging.To change the database to archivelog mode bounce is required.So better to set all the parameters and bounce the database only once.

PRIMARY DB SESSION
====================


SYS@MATRIX> show parameter unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      MATRIX
SYS@MATRIX> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(matrix,matstdby)';

System altered.

SYS@MATRIX> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\app\admin\MATRIX\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=matrix';

System altered.
SYS@MATRIX> alter system set LOG_ARCHIVE_DEST_2='SERVICE=matstdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=matstdby';

System altered.
SYS@MATRIX> alter system set FAL_SERVER=matstdby;

System altered.
SYS@MATRIX> alter system set FAL_CLIENT=matrix;

System altered.
SYS@MATRIX> alter system set DB_FILE_NAME_CONVERT='D:\oracle\app\oradata\MATSTDBY','D:\oracle\app\oradata\MATRIX' scope=spfile;

System altered.
SYS@MATRIX> alter system set LOG_FILE_NAME_CONVERT='D:\oracle\app\oradata\MATSTDBY','D:\oracle\app\oradata\MATRIX' scope=spfile;

System altered.
SYS@MATRIX> alter database force logging;

Database altered.
SYS@MATRIX> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@MATRIX>
SYS@MATRIX> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
SYS@MATRIX> alter database archivelog;

Database altered.
SYS@MATRIX> alter database open;

Database altered.
SYS@MATRIX> select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING from  v$database;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    FOR
--------- ------------ -------------------- ---------------- ---
MATRIX    ARCHIVELOG   READ WRITE           PRIMARY          YES

3. Create INIT file, Password file and as it is on windows, create a new Instance service using ORADIM utility, for standby database.

i) Created the INITmatstdby.ora file with only one parameter

*.db_name=’MATRIX’

ii) Create a new instance using ORADIM utility

D:\scripts>oradim -new -sid MATSTDBY -pfile D:\oracle\app\product\11.2.0\dbhome_1\database\INITmatstdby.ORA
Instance created.

D:\scripts>

iii) Create the Password file using ORAPWD utility.It would be used for connecting with sys user using as auxiliary

D:\scripts>orapwd file=D:\oracle\app\product\11.2.0\dbhome_1\database\PWDMATSTDBY.ora entries=2 password=sys123


The password of the SYS user must be the same on the Primary and Standby database.

iv) Create the directories in the standby location for datafiles.In my case i created folder “D:\oracle\app\oradata\MATSTDBY”

4. Start nomount the standby database using the pfile

D:\scripts>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 29 01:14:27 2010

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

Enter password:
Connected to an idle instance.

01:14:29 SYS@MATSTDBY> startup nomount
ORACLE instance started.

Total System Global Area  150667264 bytes
Fixed Size                  1373152 bytes
Variable Size              92277792 bytes
Database Buffers           50331648 bytes
Redo Buffers                6684672 bytes
01:14:55 SYS@MATSTDBY>

When i nomounted the MATSTDBY database, the directories for ADR was created inside “D:\oracle\app\diag\rdbms\matrix\matstdby”.Observing carefully the directories should have been created inside “D:\oracle\app\diag\rdbms\matstdby”.

Alert log MATSTDBY showed

Wed Dec 29 01:14:53 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\oracle\app\product\11.2.0\dbhome_1\RDBMS
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side pfile D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITMATSTDBY.ORA
System parameters with non-default values:
 db_name = "MATRIX"
Wed Dec 29 01:14:54 2010
PMON started with pid=2, OS id=6784 
Wed Dec 29 01:14:54 2010
VKTM started with pid=3, OS id=1936 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Dec 29 01:14:54 2010
DIAG started with pid=5, OS id=8136 
Wed Dec 29 01:14:54 2010
DBRM started with pid=6, OS id=8056 
Wed Dec 29 01:14:54 2010
PSP0 started with pid=7, OS id=4264 
Wed Dec 29 01:14:54 2010
DIA0 started with pid=8, OS id=4432 
Wed Dec 29 01:14:54 2010
MMAN started with pid=9, OS id=6476 
Wed Dec 29 01:14:54 2010
DBW0 started with pid=10, OS id=7828 
Wed Dec 29 01:14:54 2010
LGWR started with pid=11, OS id=3392 
Wed Dec 29 01:14:54 2010
CKPT started with pid=12, OS id=7796 
Wed Dec 29 01:14:54 2010
SMON started with pid=13, OS id=6124 
Wed Dec 29 01:14:54 2010
MMON started with pid=15, OS id=6312 
Wed Dec 29 01:14:54 2010
MMNL started with pid=16, OS id=6432 
Wed Dec 29 01:14:54 2010
GEN0 started with pid=4, OS id=7108 
ORACLE_BASE from environment = D:\oracle\app
Wed Dec 29 01:14:54 2010
RECO started with pid=14, OS id=7200 

5. Invoke the RMAN to create the standby

D:\scripts>rman target sys/sys123@matrix auxiliary sys/sys123@matstdby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 29 01:41:54 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MATRIX (DBID=2278859282)
connected to auxiliary database: MATRIX (not mounted)

RMAN> run {
2> allocate channel C1 type disk;
3> allocate auxiliary channel STDBY type disk;
4> duplicate target database
5>for standby
6> from active database
7> spfile
8> SET SGA_TARGET="256M"
9> SET SGA_MAX_SIZE="256M"
10> set db_unique_name='matstdby'
11> set db_file_name_convert='D:\oracle\app\oradata\MATRIX','D:\oracle\app\oradata\MATSTDBY'
12> set log_file_name_convert='D:\oracle\app\oradata\MATRIX','D:\oracle\app\oradata\MATSTDBY'
13> set control_files='D:\ORACLE\APP\ORADATA\MATSTDBY\control01.ctl','D:\ORACLE\APP\ORADATA\MATSTDBY\control02.ctl'
14> set log_archive_max_processes='5'
15> set fal_client='matstdby'
16> set fal_server='matrix'
17> set standby_file_management='AUTO'
18> set log_archive_config='dg_config=(matrix,matstdby)'
19> set log_archive_dest_1='LOCATION=D:\oracle\app\admin\MATSTDBY\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=matstdby'
20> set log_archive_dest_2='service=matrix ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=matrix'
21> ;
22> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=69 device type=DISK

allocated channel: STDBY
channel STDBY: SID=5 device type=DISK

Starting Duplicate Db at 29-DEC-10

contents of Memory Script:
{
   backup as copy reuse
  targetfile  'D:\oracle\app\product\11.2.0\dbhome_1\DATABASE\PWDmatrix.ORA' auxiliary format
 'D:\oracle\app\product\11.2.0\dbhome_1\DATABASE\PWDmatstdby.ORA'   targetfile
 'D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMATRIX.ORA' auxiliary format
 'D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMATSTDBY.ORA'   ;
   sql clone "alter system set spfile= ''D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMATSTDBY.ORA''";
}
executing Memory Script

Starting backup at 29-DEC-10
Finished backup at 29-DEC-10

sql statement: alter system set spfile= ''D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMATSTDBY.ORA''

contents of Memory Script:
{
   sql clone "alter system set  SGA_TARGET =
 256M comment=
 '''' scope=spfile";
   sql clone "alter system set  SGA_MAX_SIZE =
 256M comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''matstdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''D:\oracle\app\oradata\MATRIX'', ''D:\oracle\app\oradata\MATSTDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''D:\oracle\app\oradata\MATRIX'', ''D:\oracle\app\oradata\MATSTDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''D:\ORACLE\APP\ORADATA\MATSTDBY\control01.ctl'', ''D:\ORACLE\APP\ORADATA\MATSTDBY\control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''matstdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''matrix'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(matrix,matstdby)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=D:\oracle\app\admin\MATSTDBY\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=matstdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=matrix ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=matrix'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  SGA_TARGET =  256M comment= '''' scope=spfile

sql statement: alter system set  SGA_MAX_SIZE =  256M comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''matstdby'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''D:\oracle\app\oradata\MATRIX'', ''D:\oracle\app\oradata\MATSTDBY'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''D:\oracle\app\oradata\MATRIX'', ''D:\oracle\app\oradata\MATSTDBY'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''D:\ORACLE\APP\ORADATA\MATSTDBY\control01.ctl'', ''D:\ORACLE\APP\ORADATA\MATSTDBY\control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''matstdby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''matrix'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(matrix,matstdby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=D:\oracle\app\admin\MATSTDBY\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=matstdby'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=matrix ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=matrix'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     267825152 bytes

Fixed Size                     1373996 bytes
Variable Size                 92276948 bytes
Database Buffers             167772160 bytes
Redo Buffers                   6402048 bytes
allocated channel: STDBY
channel STDBY: SID=63 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  'D:\ORACLE\APP\ORADATA\MATSTDBY\CONTROL01.CTL';
   restore clone controlfile to  'D:\ORACLE\APP\ORADATA\MATSTDBY\CONTROL02.CTL' from
 'D:\ORACLE\APP\ORADATA\MATSTDBY\CONTROL01.CTL';
}
executing Memory Script

Starting backup at 29-DEC-10
channel C1: starting datafile copy
copying standby control file
output file name=D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFMATRIX.ORA tag=TAG20101229T014246 RECID=4 STAMP=738985368
channel C1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-DEC-10

Starting restore at 29-DEC-10

channel STDBY: copied control file copy
Finished restore at 29-DEC-10

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
 "D:\ORACLE\APP\ORADATA\MATSTDBY\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\USERS01.DBF";
   set newname for datafile  5 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\EXAMPLE01.DBF";
   set newname for datafile  6 to
 "D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF";
   backup as copy reuse
   datafile  1 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\SYSTEM01.DBF"   datafile
 2 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\SYSAUX01.DBF"   datafile
 3 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\UNDOTBS01.DBF"   datafile
 4 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\USERS01.DBF"   datafile
 5 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\EXAMPLE01.DBF"   datafile
 6 auxiliary format
 "D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\APP\ORADATA\MATSTDBY\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

executing command: SET NEWNAME

Starting backup at 29-DEC-10
channel C1: starting datafile copy
input datafile file number=00001 name=D:\ORACLE\APP\ORADATA\MATRIX\SYSTEM01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\SYSTEM01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:45
channel C1: starting datafile copy
input datafile file number=00002 name=D:\ORACLE\APP\ORADATA\MATRIX\SYSAUX01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\SYSAUX01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00006 name=D:\ORACLE\APP\ORADATA\MATRIX\TEST_01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:15
channel C1: starting datafile copy
input datafile file number=00005 name=D:\ORACLE\APP\ORADATA\MATRIX\EXAMPLE01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\EXAMPLE01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:07
channel C1: starting datafile copy
input datafile file number=00003 name=D:\ORACLE\APP\ORADATA\MATRIX\UNDOTBS01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\UNDOTBS01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:03
channel C1: starting datafile copy
input datafile file number=00004 name=D:\ORACLE\APP\ORADATA\MATRIX\USERS01.DBF
output file name=D:\ORACLE\APP\ORADATA\MATSTDBY\USERS01.DBF tag=TAG20101229T014259
channel C1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-DEC-10

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=738985477 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=738985478 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=738985478 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=738985478 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=738985478 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=738985478 file name=D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF
Finished Duplicate Db at 29-DEC-10
released channel: C1
released channel: STDBY

RMAN>

RMAN>

In the above output, when the instance gets restarted, a folder with instance_name i.e.,”matstdby” is created inside the diag folder (D:\oracle\app\diag\rdbms\matstdby).The alert log shows of the standby shows :-

Wed Dec 29 01:42:42 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile D:\ORACLE\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMATSTDBY.ORA
System parameters with non-default values:
 processes = 150
 sga_max_size = 256M
 sga_target = 256M
 memory_target = 520M
 control_files = "D:\ORACLE\APP\ORADATA\MATSTDBY\CONTROL01.CTL"
 control_files = "D:\ORACLE\APP\ORADATA\MATSTDBY\CONTROL02.CTL"
 db_file_name_convert = "D:\oracle\app\oradata\MATRIX"
 db_file_name_convert = "D:\oracle\app\oradata\MATSTDBY"
 log_file_name_convert = "D:\oracle\app\oradata\MATRIX"
 log_file_name_convert = "D:\oracle\app\oradata\MATSTDBY"
 db_block_size = 8192
 compatible = "11.2.0.0.0"
 log_archive_dest_1 = "LOCATION=D:\oracle\app\admin\MATSTDBY\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=matstdby"
 log_archive_dest_2 = "service=matrix ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=matrix"
 log_archive_dest_state_1 = "ENABLE"
 log_archive_dest_state_2 = "DEFER"
 fal_client = "matstdby"
 fal_server = "matrix"
 log_archive_config = "dg_config=(matrix,matstdby)"
 log_archive_max_processes= 5
 db_recovery_file_dest = "D:\oracle\app\flash_recovery_area"
 db_recovery_file_dest_size= 3852M
 standby_file_management = "AUTO"
 undo_tablespace = "UNDOTBS1"
 remote_login_passwordfile= "EXCLUSIVE"
 db_domain = ""
 dispatchers = "(PROTOCOL=TCP) (SERVICE=MATRIXXDB)"
 audit_file_dest = "D:\ORACLE\APP\ADMIN\MATRIX\ADUMP"
 audit_trail = "DB"
 db_name = "MATRIX"
 db_unique_name = "matstdby"
 open_cursors = 300
 diagnostic_dest = "D:\ORACLE\APP"
Wed Dec 29 01:42:43 2010
VKTM started with pid=3, OS id=6884 at elevated priority
Wed Dec 29 01:42:43 2010
DIAG started with pid=5, OS id=6932 
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Dec 29 01:42:43 2010
DBRM started with pid=6, OS id=2288 
Wed Dec 29 01:42:43 2010
PSP0 started with pid=7, OS id=7972 
Wed Dec 29 01:42:43 2010
DIA0 started with pid=8, OS id=2620 
Wed Dec 29 01:42:43 2010
MMAN started with pid=9, OS id=6264 
Wed Dec 29 01:42:43 2010
DBW0 started with pid=10, OS id=3768 
Wed Dec 29 01:42:43 2010
LGWR started with pid=11, OS id=6588 
Wed Dec 29 01:42:43 2010
CKPT started with pid=12, OS id=6708 
Wed Dec 29 01:42:43 2010
SMON started with pid=13, OS id=2256 
Wed Dec 29 01:42:43 2010
RECO started with pid=14, OS id=6464 
Wed Dec 29 01:42:43 2010
MMON started with pid=15, OS id=6856 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Dec 29 01:42:43 2010
PMON started with pid=2, OS id=8116 
Wed Dec 29 01:42:43 2010
MMNL started with pid=16, OS id=532 
Wed Dec 29 01:42:43 2010
GEN0 started with pid=4, OS id=8064 
ORACLE_BASE from environment = D:\oracle\app
Wed Dec 29 01:42:51 2010
RFS connections have been disallowed
alter database mount standby database
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from MATRIX to matstdby
ARCH: STARTING ARCH PROCESSES
Wed Dec 29 01:42:55 2010
ARC0 started with pid=22, OS id=6384 
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Dec 29 01:42:56 2010
ARC1 started with pid=23, OS id=7544 
Wed Dec 29 01:42:56 2010
ARC3 started with pid=25, OS id=7260 
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Dec 29 01:42:57 2010
Successful mount of redo thread 1, with mount id 2279351067
Physical Standby Database mounted.
Lost write protection disabled
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Completed: alter database mount standby database
Wed Dec 29 01:42:56 2010
ARC2 started with pid=24, OS id=6668 
Wed Dec 29 01:42:56 2010
ARC4 started with pid=26, OS id=7272 
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
Wed Dec 29 01:44:38 2010
Switch of datafile 1 complete to datafile copy 
 checkpoint is 1204599
Switch of datafile 2 complete to datafile copy 
 checkpoint is 1204641
Switch of datafile 3 complete to datafile copy 
 checkpoint is 1204688
Switch of datafile 4 complete to datafile copy 
 checkpoint is 1204691
Switch of datafile 5 complete to datafile copy 
 checkpoint is 1204680
Switch of datafile 6 complete to datafile copy 
 checkpoint is 1204664
alter database clear logfile group 1
Clearing online log 1 of thread 1 sequence number 25
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Completed: alter database clear logfile group 1
alter database clear logfile group 2
Clearing online log 2 of thread 1 sequence number 23
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Completed: alter database clear logfile group 2
alter database clear logfile group 3
Clearing online log 3 of thread 1 sequence number 24
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle\app\diag\rdbms\matstdby\matstdby\trace\matstdby_ora_3220.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Completed: alter database clear logfile group 3
RFS connections are allowed

From Oracle Docs –

SPFILE — Copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.
If you execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When you specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.

If FROM ACTIVE DATABASE is specified on DUPLICATE, then a server parameter file must be in use by the source database instance. If FROM ACTIVE DATABASE is not specified on DUPLICATE, then RMAN restores a backup of the server parameter file to the standby database.

6.Once standby creation has completed , enable the log_archive_dest_state_2 on Primary DB

SYS@MATRIX> alter system set log_archive_dest_state_2='ENABLE';

System altered.

7. Login into Standby Database
=========================================

SYS@MATSTDBY> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
MATRIX    MOUNTED              PHYSICAL STANDBY
SYS@MATSTDBY> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby;

PROCESS          PID STATUS          THREAD#  SEQUENCE#     BLOCK# DELAY_MINS
--------- ---------- ------------ ---------- ---------- ---------- ----------
ARCH            6384 CONNECTED             0          0          0          0
ARCH            7544 CONNECTED             0          0          0          0
ARCH            7260 CONNECTED             0          0          0          0
ARCH            6668 CONNECTED             0          0          0          0
ARCH            7272 CONNECTED             0          0          0          0

SYS@MATSTDBY> alter database recover managed standby database disconnect from session;

Database altered.
SYS@MATSTDBY> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby;

PROCESS          PID STATUS          THREAD#  SEQUENCE#     BLOCK# DELAY_MINS
--------- ---------- ------------ ---------- ---------- ---------- ----------
ARCH            6384 CONNECTED             0          0          0          0
ARCH            7544 CONNECTED             0          0          0          0
ARCH            7260 CONNECTED             0          0          0          0
ARCH            6668 CONNECTED             0          0          0          0
ARCH            7272 CONNECTED             0          0          0          0
MRP0            7476 WAIT_FOR_LOG          1         25          0          0

On starting the MRP process alert log of standby shows –

alter database recover managed standby database disconnect from session
Thu Dec 30 00:12:51 2010
MRP0 started with pid=28, OS id=7476 
 started logmerger process
Thu Dec 30 00:12:57 2010
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 25

To use Real Time Apply , Standby redo logfiles are mandatory.Once , the standby redo logfiles has been created on the standby use

alter database recover manages standby database using current logfile disconnect from session;

8. Perform few switches on the Primary DB and check whether they are successfully applied on the Standby

SYS@MATRIX> alter system switch logfile;

System altered.

Elapsed: 00:00:00.32
SYS@MATRIX> /

System altered.

Alert Log of Standby

Media Recovery Waiting for thread 1 sequence 28 (in transit)
Thu Dec 30 00:21:00 2010
Archived Log entry 4 added for thread 1 sequence 28 rlc 738534549 ID 0x87d49f12 dest 2:
RFS[2]: Opened log for thread 1 sequence 29 dbid -2016108014 branch 738534549
Thu Dec 30 00:21:02 2010
Media Recovery Log D:\ORACLE\APP\ADMIN\MATSTDBY\ARCHIVE\ARC0000000028_0738534549.0001
Media Recovery Waiting for thread 1 sequence 29 (in transit)
Archived Log entry 5 added for thread 1 sequence 29 rlc 738534549 ID 0x87d49f12 dest 2:
RFS[2]: Opened log for thread 1 sequence 30 dbid -2016108014 branch 738534549
Media Recovery Log D:\ORACLE\APP\ADMIN\MATSTDBY\ARCHIVE\ARC0000000029_0738534549.0001
Media Recovery Waiting for thread 1 sequence 30 (in transit)

On standby

00:20:22 SYS@MATSTDBY> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby;

PROCESS          PID STATUS          THREAD#  SEQUENCE#     BLOCK# DELAY_MINS
--------- ---------- ------------ ---------- ---------- ---------- ----------
ARCH            6384 CONNECTED             0          0          0          0
ARCH            7544 CONNECTED             0          0          0          0
ARCH            7260 CONNECTED             0          0          0          0
ARCH            6668 CONNECTED             0          0          0          0
ARCH            7272 CONNECTED             0          0          0          0
MRP0            7476 WAIT_FOR_LOG          1         30          0          0
RFS             4696 IDLE                  0          0          0          0
RFS             2052 IDLE                  1         30        197          0
RFS             6476 IDLE                  0          0          0          0
RFS             2692 IDLE                  0          0          0          0

10. In case, the archives are not getting shipped to the standby

i) Check the alert log of both the Primary and Standby databases
ii) Check the ERROR column in v$archive_dest on the primary database

SYS@MATRIX> select DEST_NAME,status,error from  v$archive_dest;

DEST_NAME                      STATUS    ERROR
------------------------------ --------- -----------------------------------------------------
LOG_ARCHIVE_DEST_1             VALID
LOG_ARCHIVE_DEST_2             VALID
LOG_ARCHIVE_DEST_3             INACTIVE
..............................
..............................
LOG_ARCHIVE_DEST_31            INACTIVE

Note :- Always create/have STANDBY logfiles in DataGuard configuration.

Just for fun –>
+++++++++++++++++++++++++++++++
1. Add datafile on the primary and check it on the standby

On Primary DB
========================

00:37:34 SYS@MATRIX> select file#,name from v$datafile where TS#=7;

     FILE# NAME
---------- ------------------------------------------------------------
         6 D:\ORACLE\APP\ORADATA\MATRIX\TEST_01.DBF

Elapsed: 00:00:00.31
00:38:07 SYS@MATRIX> alter system switch logfile;

System altered.

Elapsed: 00:00:00.07
00:38:12 SYS@MATRIX> alter tablespace test add datafile 'D:\ORACLE\APP\ORADATA\MATRIX\TEST_02.dbf' size 5M;

Tablespace altered.

Elapsed: 00:00:01.04
00:38:38 SYS@MATRIX> alter system switch logfile;

System altered.

Elapsed: 00:00:00.07
00:38:40 SYS@MATRIX> select file#,name from v$datafile where TS#=7;

     FILE# NAME
---------- ------------------------------------------------------------
         6 D:\ORACLE\APP\ORADATA\MATRIX\TEST_01.DBF
         7 D:\ORACLE\APP\ORADATA\MATRIX\TEST_02.DBF

Elapsed: 00:00:00.39

On Standby DB
==============================

00:38:02 SYS@MATSTDBY> /

     FILE# NAME
---------- ------------------------------------------------------------
         6 D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF

Elapsed: 00:00:00.65
00:38:04 SYS@MATSTDBY>
00:38:04 SYS@MATSTDBY> /

     FILE# NAME
---------- ------------------------------------------------------------
         6 D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF
         7 D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_02.DBF

Elapsed: 00:00:00.73

ALERT LOG -

Thu Dec 30 00:38:43 2010
Media Recovery Log D:\ORACLE\APP\ADMIN\MATSTDBY\ARCHIVE\ARC0000000031_0738534549.0001
Recovery created file D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_02.DBF
Successfully added datafile 7 to media recovery
Datafile #7: 'D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_02.DBF'
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Thu Dec 30 00:40:33 2010

2. Adding a Logfile on the Primary and checking the Standby DB

Primary DB

00:39:10 SYS@MATRIX> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- -----------------------------------------------------
         3         D:\ORACLE\APP\ORADATA\MATRIX\REDO03.LOG
         2         D:\ORACLE\APP\ORADATA\MATRIX\REDO02.LOG
         1         D:\ORACLE\APP\ORADATA\MATRIX\REDO01.LOG

00:39:45 SYS@MATRIX> alter database add logfile group 4 'D:\ORACLE\APP\ORADATA\MATRIX\REDO04.LOG' size 50M;

Database altered.

Elapsed: 00:00:01.00
00:40:28 SYS@MATRIX> alter system switch logfile;

System altered.
00:42:30 SYS@MATRIX> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         3 D:\ORACLE\APP\ORADATA\MATRIX\REDO03.LOG
         2 D:\ORACLE\APP\ORADATA\MATRIX\REDO02.LOG
         1 D:\ORACLE\APP\ORADATA\MATRIX\REDO01.LOG
         4 D:\ORACLE\APP\ORADATA\MATRIX\REDO04.LOG

Elapsed: 00:00:00.04

On Standby —

00:48:47 SYS@MATSTDBY> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         3 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO03.LOG
         2 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO02.LOG
         1 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO01.LOG

Elapsed: 00:00:00.01
00:48:49 SYS@MATSTDBY>

ALERT LOG --
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Thu Dec 30 00:40:33 2010
Archived Log entry 8 added for thread 1 sequence 32 rlc 738534549 ID 0x87d49f12 dest 2:
RFS[2]: Opened log for thread 1 sequence 33 dbid -2016108014 branch 738534549
Thu Dec 30 00:40:34 2010
Media Recovery Log D:\ORACLE\APP\ADMIN\MATSTDBY\ARCHIVE\ARC0000000032_0738534549.0001
Media Recovery Waiting for thread 1 sequence 33 (in transit)
Thu Dec 30 00:49:51 2010
Archived Log entry 9 added for thread 1 sequence 33 rlc 738534549 ID 0x87d49f12 dest 2:
RFS[2]: Opened log for thread 1 sequence 34 dbid -2016108014 branch 738534549

So, Online redo logfile doesn’t matter for the Standby database, what matters is the Standby Logfile.Though said so,it is better to have same number and size of online redo logfile on the standby database as you may need to perform failover or switchover-switchback sometime.
https://aprakash.wordpress.com/2010/05/13/online-redo-logfiles-in-physical-standby/
To do online redo logfile on the standby —

00:54:59 SYS@MATSTDBY> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         3 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO03.LOG
         2 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO02.LOG
         1 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO01.LOG

00:55:00 SYS@MATSTDBY>
00:55:10 SYS@MATSTDBY> alter database recover managed standby database cancel;

Database altered.
00:55:56 SYS@MATSTDBY> alter system set standby_file_management=MANUAL;

System altered.

Elapsed: 00:00:00.06
00:56:00 SYS@MATSTDBY>
00:56:01 SYS@MATSTDBY>
00:56:01 SYS@MATSTDBY> alter database add logfile group 4 'D:\ORACLE\APP\ORADATA\MATSTDBY\REDO04.LOG' size 50M;

Database altered.

Elapsed: 00:00:01.26
00:56:07 SYS@MATSTDBY> alter system set standby_file_management=AUTO;

System altered.

Elapsed: 00:00:00.04
00:56:14 SYS@MATSTDBY> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:07.15
00:56:35 SYS@MATSTDBY> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         3 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO03.LOG
         2 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO02.LOG
         1 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO01.LOG
         4 D:\ORACLE\APP\ORADATA\MATSTDBY\REDO04.LOG

Elapsed: 00:00:00.06
00:57:32 SYS@MATSTDBY>

Reference :- http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/rcmbackp.htm#SBYDB4987
http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta020.htm

5 thoughts on “Creating Standby Database – 11gR2 using ” FOR STANDBY FROM ACTIVE DATABASE” clause

  1. Hey,

    Nice demo dude..

    I have created on hpux which will create the standby database from active production database. I tested it on small test database(not more than 5G) and it is working smoothly. The script is going live on Monday.

    Here are my concerns,

    I have scheduled rman backup of archive logs on production DB every 4 hrs. It will delete the archives after they are backed up.

    Since my production DB is size is 5TB, it may take some time(assuming 8 hrs) to complete the “rman duplicate fro standby” and start the managed recovery.

    My script will fail when there are no archives present which were needed for recovery of standby because they are deleted by RMAN backup.

    alert.log of standby database shows below message.

    RFS connections have been disallowed.
    alter database mount standby database.

    It tells that standby database is mounted beore starting the copy of datafiles to standby location.

    My question is why standby database does not allow the RFS process to fetch the archives from primary DB even if standby database is in mount stage?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s