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
====================
[email protected]> 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 [email protected]> 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 [email protected]> 19:52:09 [email protected]> 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
====================
[email protected]> show parameter unique NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string MATRIX [email protected]> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(matrix,matstdby)'; System altered. [email protected]> 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. [email protected]> alter system set LOG_ARCHIVE_DEST_2='SERVICE=matstdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=matstdby'; System altered. [email protected]> alter system set FAL_SERVER=matstdby; System altered. [email protected]> alter system set FAL_CLIENT=matrix; System altered. [email protected]> alter system set DB_FILE_NAME_CONVERT='D:\oracle\app\oradata\MATSTDBY','D:\oracle\app\oradata\MATRIX' scope=spfile; System altered. [email protected]> alter system set LOG_FILE_NAME_CONVERT='D:\oracle\app\oradata\MATSTDBY','D:\oracle\app\oradata\MATRIX' scope=spfile; System altered. [email protected]> alter database force logging; Database altered. [email protected]> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. [email protected]> [email protected]> 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. [email protected]> alter database archivelog; Database altered. [email protected]> alter database open; Database altered. [email protected]> 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 [email protected]> 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 [email protected]>
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/[email protected] auxiliary sys/[email protected] 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
[email protected]> alter system set log_archive_dest_state_2='ENABLE'; System altered.
7. Login into Standby Database
=========================================
[email protected]> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- MATRIX MOUNTED PHYSICAL STANDBY [email protected]> 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 [email protected]> alter database recover managed standby database disconnect from session; Database altered. [email protected]> 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
[email protected]> alter system switch logfile; System altered. Elapsed: 00:00:00.32 [email protected]> / 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 [email protected]> 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
[email protected]> 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 [email protected]> 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 [email protected]> alter system switch logfile; System altered. Elapsed: 00:00:00.07 00:38:12 [email protected]> 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 [email protected]> alter system switch logfile; System altered. Elapsed: 00:00:00.07 00:38:40 [email protected]> 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 [email protected]> / FILE# NAME ---------- ------------------------------------------------------------ 6 D:\ORACLE\APP\ORADATA\MATSTDBY\TEST_01.DBF Elapsed: 00:00:00.65 00:38:04 [email protected]> 00:38:04 [email protected]> / 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 [email protected]> 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 [email protected]> 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 [email protected]> alter system switch logfile; System altered. 00:42:30 [email protected]> 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 [email protected]> 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 [email protected]> 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.
http://aprakash.wordpress.com/2010/05/13/online-redo-logfiles-in-physical-standby/
To do online redo logfile on the standby —
00:54:59 [email protected]> 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 [email protected]> 00:55:10 [email protected]> alter database recover managed standby database cancel; Database altered. 00:55:56 [email protected]> alter system set standby_file_management=MANUAL; System altered. Elapsed: 00:00:00.06 00:56:00 [email protected]> 00:56:01 [email protected]> 00:56:01 [email protected]> 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 [email protected]> alter system set standby_file_management=AUTO; System altered. Elapsed: 00:00:00.04 00:56:14 [email protected]> alter database recover managed standby database disconnect from session; Database altered. Elapsed: 00:00:07.15 00:56:35 [email protected]> 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 [email protected]>
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”
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?
Good one!
New Year’s first comment for you Sir :). Thanks for visiting the blog and your comments 🙂
Regards,
Anand
Hi Anand,
Nice demonstration dude.. .!!
Thanks buddy 🙂