A Standby Database is an exact binary copy of an operational database on a remote server, ready to be used for backup, replication, disaster recovery, analysis, shadow environment and reporting, to name a few applications.
The most exiting feature of Active Standby Database is that we can open the standby database in read only mode and at the sometime MRP process will be active, so we can redirect users to connect standby to perform select operations for reporting purpose. So that we can control much load on production database and there are plenty of option for active dataguard .
Here we will setup the standby database with active duplicate database feature available in 11g where we can create standby database without having any rman backup.In this setup,there is no need to copy the datafiles manually. Datafiles are copeid over the network . As i have setup the standby database on same machine in my earlier POST. Now i will step the standby database on two different machine.
Primary Database :
Machine ==> tech-199
Database ==> NOIDA
Standby Database :
Machine ==> tech-284
Database ==> RED(standby)
Platform used is WINDOW XP
While configuring the standby database lets' have a look on the directory structure to avoid any confusion .On Primary database all the datafiles and redologs file in directory C:\app\neerajs\oradata\noida\' and archive destination is in directory "D:\archive" on machine tech-199 where as in case of the Standby database all the datafile,redologs and control files are in directory 'D:\app\standby\oradata\' i.e, on machine tech-284 . In standby database, i have set the archive destination in 'D:\archive\' . Let us configure standby database step-by-step.
Step 1 : Enable force logging on the Primary database :
SQL> alter database force logging ;
Database altered.
Following steps are performed on Standby database Machine (i.e; tech-284)
Step 2 : Create Oracle Instance :
C:\> oradim -new -sid red -intpwd xxxx -startmode m
instance created
Note : Password should be same as that of user "sys" of production database.
Step 3 : Update Listener.ora on standby Machine
(SID_DESC =
(GLOBAL_DBNAME = noida)
(ORACLE_HOME = D:\app\Bishwanath\product\11.2.0\dbhome_1)
(SID_NAME = red)
)
Stop and start the listener on standby
Step 4 : Update the tnsnames.ora file on standby database :
red =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tech-284)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = red)
)
)
Step 5 : Create pfile for standby database
Add just one parameter in pfile. i.e;
db_name=Noida
save the pfile as initred.ora in $ORACLE_HOME\database\ folder.
Step 6 : Startup standby Instance in nomount state
C:\>sqlplus sys/xxxx@red as sysdba
SQL>startup nomount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 213911796 bytes
Database Buffers 41943040 bytes
Redo Buffers 6410240 bytes
Step 7 : On production database ,connect with RMAN and establish connection with auxiliary i.e; to standby database
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 1 16:56:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: NOIDA (DBID=1515011070)
connected to auxiliary database: NOIDA (not mounted)
RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='red'
SET LOG_ARCHIVE_DEST_2='service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'
Set STANDBY_FILE_MANAGEMENT='AUTO'
SET FAL_SERVER='noida'
SET FAL_CLIENT='RED'
SET CONTROL_FILES='D:\app\standby\oradata\CONTROL01.CTL'
SET DB_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\'
SET LOG_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\'
set log_archive_dest_1='location=D:\archive\'
set diagnostic_dest='D:\app\standby\diag\'
set db_recovery_file_dest='D:\app\standby\FRA\' ;
Starting Duplicate Db at 01-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile 'C:\app\neerajs\product\11.2.0\dbhome_1\DATABASE\PWDnoida.ORA' auxiliary format
'D:\app\Bishwanath\product\11.2.0\dbhome_1\DATABASE\PWDred.ORA' targetfile
'C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILENOIDA.ORA' auxiliary format
'D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA' ;
sql clone "alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''";
}
executing Memory Script
Starting backup at 01-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 01-OCT-11
sql statement: alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''red'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_2 =
''service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment=
'''' scope=spfile";
sql clone "alter system set STANDBY_FILE_MANAGEMENT =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_SERVER =
''noida'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_CLIENT =
''RED'' comment=
'''' scope=spfile";
sql clone "alter system set CONTROL_FILES =
''D:\app\standby\oradata\CONTROL01.CTL'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_FILE_NAME_CONVERT =
''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=D:\archive\'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''D:\app\standby\diag\'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''D:\app\standby\FRA\'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''red'' comment= '''' scope=spfile
sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''service=noida LGWR SYNC REGISTER
VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile
sql statement: alter system set STANDBY_FILE_MANAGEMENT = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set FAL_SERVER = ''noida'' comment= '''' scope=spfile
sql statement: alter system set FAL_CLIENT = ''RED'' comment= '''' scope=spfile
sql statement: alter system set CONTROL_FILES = ''D:\app\standby\oradata\CONTROL01.CTL'' comment= ''''
scope=spfile
sql statement: alter system set db_file_name_convert = ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile
sql statement: alter system set LOG_FILE_NAME_CONVERT = ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=D:\archive\'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''D:\app\standby\diag\'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''D:\app\standby\FRA\'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 192940276 bytes
Database Buffers 62914560 bytes
Redo Buffers 6410240 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format 'D:\APP\STANDBY\ORADATA\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFNOIDA.ORA tag=TAG20111001T165811
RECID=11 STAMP=763405095
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
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:\APP\STANDBY\ORADATA\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"D:\APP\STANDBY\ORADATA\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\APP\STANDBY\ORADATA\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\APP\STANDBY\ORADATA\USERS01.DBF";
set newname for datafile 5 to
"D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF";
set newname for datafile 6 to
"D:\APP\STANDBY\ORADATA\TEST01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\APP\STANDBY\ORADATA\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\APP\STANDBY\ORADATA\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\APP\STANDBY\ORADATA\USERS01.DBF" datafile
5 auxiliary format
"D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF" datafile
6 auxiliary format
"D:\APP\STANDBY\ORADATA\TEST01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\STANDBY\ORADATA\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 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSTEM01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSAUX01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\NOIDA\EXAMPLE01.DBF
output file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=C:\APP\NEERAJS\ORADATA\NOIDA\UNDOTBS01.DBF
output file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=C:\APP\NEERAJS\ORADATA\NOIDA\TEST01.DBF
output file name=D:\APP\STANDBY\ORADATA\TEST01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\APP\NEERAJS\ORADATA\NOIDA\USERS01.DBF
output file name=D:\APP\STANDBY\ORADATA\USERS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-OCT-11
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "D:\ARCHIVE\ARC0000000053_0761068614.0001" auxiliary format
"D:\ARCHIVE\ARC0000000053_0761068614.0001" ;
catalog clone archivelog "D:\ARCHIVE\ARC0000000053_0761068614.0001";
switch clone datafile all;
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=53 RECID=38 STAMP=763405284
output file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
cataloged archived log
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=1 STAMP=763405200
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\TEST01.DBF
contents of Memory Script:
{
set until scn 2184111;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 01-OCT-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK
starting media recovery
archived log for thread 1 with sequence 53 is already on disk as file
D:\ARCHIVE\ARC0000000053_0761068614.0001
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 thread=1 sequence=53
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-OCT-11
Finished Duplicate Db at 01-OCT-11
RMAN> **end-of-file**
Step 8 : On Primary database
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> alter system set fal_server=red scope=both;
System altered.
SQL> alter system set fal_client=noida scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=red LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=red' scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=noida' ;
System altered.
Step 9 : On standby database : shutdown the Standby and enable managed recovery (active standby mode)
C:\>sqlplus sys/xxxx@red as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:14:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
-----------------
MOUNTED
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 205523188 bytes
Database Buffers 50331648 bytes
Redo Buffers 6410240 bytes
Database mounted.
Database opened.
SQL> recover managed standby database using current logfile disconnect;
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
Standby redo logs are required to enable real time apply of redo data onto the standby.This standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Oracle recommends the below formula to calculate the number of Standby redo logs file as
(maximum number of logfiles for each thread + 1) * maximum number of threads
Since , I have three redo logs file so i will create four standby redo logs file. Oracle recommends that we should create standby redo logs on both i.e,primary and standby database so that we can safely switchover in future . Here, i am creating standby redo logs on standby database only .
SQL> alter database add standby logfile group 4 'D:\APP\STANDBY\ORADATA\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 'D:\APP\STANDBY\ORADATA\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 'D:\APP\STANDBY\ORADATA\REDO06.LOG' size 50m;
Database altered.
SQL>recover managed standby database using current logfile disconnect ;
Media recovery complete .
( On standby database )
SQL> select open_mode from V$database ;
OPEN_MODE
--------------------------
READ ONLY WITH APPLY
(The above output "read only with apply" shows the active mode is activated )
Following is the Command Used for Active Duplication
FROM ACTIVE DATABASE : (This is supplied if we want to do active database duplication) Specifies that the files for the standby database should be provided directly from the source database and not from a backup of the source database
NOFILENAMECHECK: Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if we want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then we must specify NOFILENAMECHECK
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 we 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 we specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.
DORECOVER: Specifies that RMAN should recover the standby database after creating it. If we specify an until Clause, then RMAN recovers to the specified SCN or time and leaves the database mounted.
RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, we must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-onlymode.For more detail about parameters click here
Enjoy :-)
1 comment:
Thank you so much, I am new to standby stuff and it worked like a charm.
Post a Comment