- Configuration Details
- Configure Primary Database
- Create PHYSICAL standby
- Here’s how Real Time Apply works
- References
Configuration Details
PRIMARY site
hostname=zephir
ORACLE_SID=10GR2
DB_UNIQUE_NAME=leftcube
------------- $HOME/.profile ----------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_SID=10GR2
export ORACLE_HOSTNAME=zephir.domain.com
umask 022
##export DISPLAY="zephir:0.0"
export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin
PS1="`/usr/ucb/hostname`.$ORACLE_SID-> "
set -o vi
set history=50
###################################################################
# Oracle Alias' For Trace Files and Alert Files
###################################################################
alias -x pfile="cd /u01/app/oracle/admin/10GR2/pfile"
alias -x bdump="cd /u01/app/oracle/admin/10GR2/bdump"
alias -x cdump="cd /u01/app/oracle/admin/10GR2/cdump"
alias -x udump="cd /u01/app/oracle/admin/10GR2/udump"
###################################################################
STANDBY site
hostname=atlas
ORACLE_SID=10GR2
DB_UNIQUE_NAME=rightcube
------------- $HOME/.profile ----------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_SID=10GR2
export ORACLE_HOSTNAME=atlas.domain.com
umask 022
##export DISPLAY="atlas:0.0"
export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin
PS1="`/usr/ucb/hostname`.$ORACLE_SID-> "
set -o vi
set history=50
###################################################################
# Oracle Alias' For Trace Files and Alert Files
###################################################################
alias -x pfile="cd /u01/app/oracle/admin/10GR2/pfile"
alias -x bdump="cd /u01/app/oracle/admin/10GR2/bdump"
alias -x cdump="cd /u01/app/oracle/admin/10GR2/cdump"
alias -x udump="cd /u01/app/oracle/admin/10GR2/udump"
###################################################################
Configure Primary Database
Create a Password File (or verify you have one)
ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle dba 1536 Oct 1 15:00 /u01/app/oracle/product/10.2.0/db_1/dbs/orapw10GR2
Enable Forced Logging
alter database force logging;
Configure Standby Redo Logs
NOTE: A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.
the recommended number of standby redo log file groups is:
(maximum number of logfiles for each thread + 1) * maximum number of threads
For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.
Although the standby redo log is only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention.
SQL> col member format a35
SQL> set lines 132
SQL> set trims on
SQL>
SQL> r
1* select group#,type,member from v$logfile
GROUP# TYPE MEMBER
---------- ------- -----------------------------------
1 ONLINE /u01/oradata/10GR2/redo01.log
1 ONLINE /u02/oradata/10GR2/redo01.log
1 ONLINE /u03/oradata/10GR2/redo01.log
2 ONLINE /u01/oradata/10GR2/redo02.log
2 ONLINE /u02/oradata/10GR2/redo02.log
2 ONLINE /u03/oradata/10GR2/redo02.log
3 ONLINE /u01/oradata/10GR2/redo03.log
3 ONLINE /u02/oradata/10GR2/redo03.log
3 ONLINE /u03/oradata/10GR2/redo03.log
9 rows selected.
SQL> select group#,THREAD#,BYTES,status from v$log;
GROUP# THREAD# BYTES STATUS
---------- ---------- ---------- ----------------
1 1 52428800 INACTIVE
2 1 52428800 INACTIVE
3 1 52428800 CURRENT
based on the above information create 3+1 = 4 new standby groups each with 3 member – you could do 2 members or just 1 it’s up to you the main thing is that there needs to be 4 standby groups, multiple members simply add redundancy to the setup they do not increase performance.
Note that I name each group member the same this underscores that these files are multiplexed and are identical
alter database add standby logfile group 4
('/u01/oradata/10GR2/stby04.log',
'/u02/oradata/10GR2/stby04.log',
'/u03/oradata/10GR2/stby04.log')
size 52428800;
alter database add standby logfile group 5
('/u01/oradata/10GR2/stby05.log',
'/u02/oradata/10GR2/stby05.log',
'/u03/oradata/10GR2/stby05.log')
size 52428800;
alter database add standby logfile group 6
('/u01/oradata/10GR2/stby06.log',
'/u02/oradata/10GR2/stby06.log',
'/u03/oradata/10GR2/stby06.log')
size 52428800;
alter database add standby logfile group 7
('/u01/oradata/10GR2/stby07.log',
'/u02/oradata/10GR2/stby07.log',
'/u03/oradata/10GR2/stby07.log')
size 52428800;
Verify the standby redo log file groups were created
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -----------------------------------
1 ONLINE /u01/oradata/10GR2/redo01.log
1 ONLINE /u02/oradata/10GR2/redo01.log
1 ONLINE /u03/oradata/10GR2/redo01.log
2 ONLINE /u01/oradata/10GR2/redo02.log
2 ONLINE /u02/oradata/10GR2/redo02.log
2 ONLINE /u03/oradata/10GR2/redo02.log
3 ONLINE /u01/oradata/10GR2/redo03.log
3 ONLINE /u02/oradata/10GR2/redo03.log
3 ONLINE /u03/oradata/10GR2/redo03.log
4 STANDBY /u01/oradata/10GR2/stby04.log
4 STANDBY /u02/oradata/10GR2/stby04.log
GROUP# TYPE MEMBER
---------- ------- -----------------------------------
4 STANDBY /u03/oradata/10GR2/stby04.log
5 STANDBY /u01/oradata/10GR2/stby05.log
5 STANDBY /u02/oradata/10GR2/stby05.log
5 STANDBY /u03/oradata/10GR2/stby05.log
6 STANDBY /u01/oradata/10GR2/stby06.log
6 STANDBY /u02/oradata/10GR2/stby06.log
6 STANDBY /u03/oradata/10GR2/stby06.log
7 STANDBY /u01/oradata/10GR2/stby07.log
7 STANDBY /u02/oradata/10GR2/stby07.log
7 STANDBY /u03/oradata/10GR2/stby07.log
21 rows selected.
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
SQL>
Setup Archive Log
NOTE: this is not the right setup for DataGuard but it’s a typical setup on our production systems so I am simulating it here which will be changed in the next step
zephir.10GR2-> mkdir /u02/oradata/10GR2/arch
zephir.10GR2-> sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> alter database close;
Database altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfile10GR2.ora
SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/10GR2/arch' scope=both;
System altered.
SQL> alter database archivelog;
Database altered.
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 327155712 bytes
Fixed Size 2029880 bytes
Variable Size 171968200 bytes
Database Buffers 150994944 bytes
Redo Buffers 2162688 bytes
Database mounted.
Database opened.
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/10GR2/arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
SQL>
SQL> !ls -l /u02/oradata/10GR2/arch
total 0
SQL> alter system switch logfile;
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> r
1* alter system switch logfile
System altered.
SQL> !ls -l /u02/oradata/10GR2/arch
total 64826
-rw-r----- 1 oracle dba 33161216 Oct 5 14:08 1_30_634834860.dbf
-rw-r----- 1 oracle dba 1024 Oct 5 14:08 1_31_634834860.dbf
-rw-r----- 1 oracle dba 3584 Oct 5 14:08 1_32_634834860.dbf
SQL>
Set Primary Database Initialization Parameters
in this case there are two boxes both are in the same cubicle. Since DB_UNIQUE_NAME is normally tied to a physical location which removes any confusion when database’s roles are switched I am setting DB_UNIQUE_NAME to:
leftcube AND rightcube
at this moment leftcube’s role is PRIMARY and rightcube’s role is STANDBY
DB_NAME on both will be 10GR2
TWO tns-aliases will be setup for each of these names
Note that the example specifies the LGWR process and asynchronous (ASYNC) network transmission to transmit redo data on the LOG_ARCHIVE_DEST_2 initialization parameter. These are the recommended settings and require standby redo log files
alter system set db_unique_name=leftcube scope=spfile;
alter system set log_archive_config='DG_CONFIG=(leftcube,rightcube)' scope=spfile;
alter system set log_archive_dest_1=
'LOCATION=/u02/oradata/10GR2/arch
VALID_FOR=(all_logfiles,all_roles)
DB_UNIQUE_NAME=leftcube'
scope=spfile;
alter system set log_archive_dest_2=
'SERVICE=rightcube LGWR ASYNC
VALID_FOR=(online_logfiles,primary_role)
DB_UNIQUE_NAME=rightcube'
scope=spfile;
alter system set log_archive_dest_state_1=ENABLE scope=spfile;
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
NOTE: Specifying the initialization parameters shown in Example 34 sets up the primary database to resolve gaps, converts new datafile and log file path names from a new primary database, and archives the incoming redo data when this database is in the standby role. With the initialization parameters for both the primary and standby roles set as described, none of the parameters need to change after a role transition.
I am not using DB_FILE_NAME_CONVERT because I always try to keep directory structure exactly the same
alter system set fal_server=rightcube scope=spfile;
alter system set fal_client=leftcube scope=spfile;
alter system set standby_file_management=AUTO scope=spfile;
## this one is just a DUMMY setup to avoid ORA-19527 see:
## http://kb.dbatoolz.com/tp/2692.avoid_ora-19527_set_dummy_log_file_name_convert.html
##
alter system set log_file_name_convert='junk','junk' scope=spfile;
shutdown immediate;
startup;
AFTER you enable these setting and until you have setup the actual standby database you will be receiving the following errors in the alert log:
ORA-12154: TNS:could not resolve the connect identifier specified
LGWR: Error 12154 creating archivelog file 'rightcube'
LNS: Failed to archive log 2 thread 1 sequence 35 (12154)
this is due to LGWR not being able to send REDO over to rightcube IT’S OK since log_archive_dest_2 has a flag of ASYNC which is implicitly makes this destination OPTIONAL
Take A Hotbackup Of Primary Database
zephir.10GR2-> pwd
/u01/app/oracle/admin/10GR2/backup
zephir.10GR2-> grep "Header" hotbk.sh
# $Header$ hotbk.sh 05-OCT-2007 2.1
zephir.10GR2-> grep bkdir= hotbk.sh
bkdir=/copy/db_backup/10GR2/dbfs
zephir.10GR2-> mkdir -p /copy/db_backup/10GR2/dbfs
zephir.10GR2-> ./hotbk.sh
zephir.10GR2-> Fri Oct 5 16:48:03 PDT 2007: getting datafile list ...
Fri Oct 5 16:48:04 PDT 2007: building backup script ...
mv: cannot access /copy/db_backup/10GR2/dbfs/recover.sh
Fri Oct 5 16:48:04 PDT 2007: running backup script (logfile=/tmp/13363.hotbk.sh.log) ...
zephir.10GR2-> ls -l /copy/db_backup/10GR2/dbfs/
total 1831154
-rw-r----- 1 oracle dba 14270464 Oct 5 16:48 2007_10_05.backup.ctl
-rw-r--r-- 1 oracle dba 212 Oct 5 16:48 recover.sh
-rw-r----- 1 oracle dba 230694912 Oct 5 16:48 sysaux01.dbf
-rw-r----- 1 oracle dba 471867392 Oct 5 16:48 system01.dbf
-rw-r----- 1 oracle dba 214966272 Oct 5 16:48 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Oct 5 16:48 users01.dbf
zephir.10GR2-> cat /copy/db_backup/10GR2/dbfs/recover.sh
cp -p ./sysaux01.dbf /u03/oradata/10GR2/sysaux01.dbf
cp -p ./system01.dbf /u02/oradata/10GR2/system01.dbf
cp -p ./undotbs01.dbf /u03/oradata/10GR2/undotbs01.dbf
cp -p ./users01.dbf /u02/oradata/10GR2/users01.dbf
zephir.10GR2->
Create a Control File for the Standby Database
alter database create standby controlfile as
'/copy/db_backup/10GR2/dbfs/stby_control.ctl';
alter system archive log current;
Prepare Initialization Parameter File for the Standby Database
## run this on PRIMARY
create pfile='/copy/db_backup/10GR2/dbfs/init10GR2.ora' from spfile;
## Set initialization parameters on the physical standby database
##
cp -p /copy/db_backup/10GR2/dbfs/init10GR2.ora \
/copy/db_backup/10GR2/dbfs/init10GR2_stby.ora
vi /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora
--------- MODIFY AS FOLLOWS --------
*.db_unique_name='RIGHTCUBE'
*.fal_client='RIGHTCUBE'
*.fal_server='LEFTCUBE'
*.log_archive_dest_1='LOCATION=/u02/oradata/10GR2/arch
VALID_FOR=(all_logfiles,all_roles)
DB_UNIQUE_NAME=rightcube'
*.log_archive_dest_2='SERVICE=leftcube LGWR ASYNC
VALID_FOR=(online_logfiles,primary_role)
DB_UNIQUE_NAME=leftcube'
zephir.10GR2-> diff /copy/db_backup/10GR2/dbfs/init10GR2.ora \
> /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora
15c15
< *.db_unique_name='LEFTCUBE'
---
> *.db_unique_name='RIGHTCUBE'
17,18c17,18
< *.fal_client='LEFTCUBE'
< *.fal_server='RIGHTCUBE'
---
> *.fal_client='RIGHTCUBE'
> *.fal_server='LEFTCUBE'
23,25d22
< DB_UNIQUE_NAME=leftcube'
< *.log_archive_dest_2='SERVICE=rightcube LGWR ASYNC
< VALID_FOR=(online_logfiles,primary_role)
26a24,26
> *.log_archive_dest_2='SERVICE=leftcube LGWR ASYNC
> VALID_FOR=(online_logfiles,primary_role)
> DB_UNIQUE_NAME=leftcube'
Copy Files from the Primary System to the Standby System
NOTE: I used rdist you can also use FTP or any other method
## on standby verify primary host is added to .rhosts
atlas.10GR2-> grep zephir $HOME/.rhosts
zephir.domain.com oracle
## rdist all files over to standby host
zephir.10GR2-> rsh -l oracle -n atlas "mkdir -p /copy/db_backup/10GR2/dbfs"
zephir.10GR2-> rdist -c /copy/db_backup/10GR2/dbfs/* oracle@atlas:/copy/db_backup/10GR2/dbfs/
updating host atlas
installing: /copy/db_backup/10GR2/dbfs/2007_10_05.backup.ctl
installing: /copy/db_backup/10GR2/dbfs/init10GR2.ora
installing: /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora
installing: /copy/db_backup/10GR2/dbfs/recover.sh
installing: /copy/db_backup/10GR2/dbfs/stby_control.ctl
installing: /copy/db_backup/10GR2/dbfs/sysaux01.dbf
installing: /copy/db_backup/10GR2/dbfs/system01.dbf
installing: /copy/db_backup/10GR2/dbfs/undotbs01.dbf
installing: /copy/db_backup/10GR2/dbfs/users01.dbf
zephir.10GR2->
## rdist archive logs
zephir.10GR2-> rsh -l oracle -n atlas "mkdir -p /u02/oradata/10GR2/arch"
zephir.10GR2-> rdist -c /u02/oradata/10GR2/arch/* oracle@atlas:/u02/oradata/10GR2/arch/
updating host atlas
installing: /u02/oradata/10GR2/arch/1_30_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_31_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_32_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_33_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_34_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_35_634834860.dbf
installing: /u02/oradata/10GR2/arch/1_36_634834860.dbf
## copy password file
zephir.10GR2-> cd /u01/app/oracle/product/10.2.0/db_1/dbs/
zephir.10GR2-> rdist -c orapw10GR2 oracle@atlas:/u01/app/oracle/product/10.2.0/db_1/dbs/orapw10GR2
updating host atlas
installing: orapw10GR2
Configure listeners for the primary and standby databases.
## primary (leftcube)
zephir.10GR2-> cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = 10GR2.DOMAIN.COM)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = 10GR2)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521))
)
zephir.10GR2-> cat tnsnames.ora
rightcube =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = 10GR2)
)
)
10GR2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 10GR2.domain.com)
)
)
## standby (rightcube)
atlas.10GR2-> cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = 10GR2.DOMAIN.COM)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = 10GR2)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521))
)
atlas.10GR2-> cat tnsnames.ora
leftcube =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = 10GR2)
)
)
10GR2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 10GR2.domain.com)
)
)
Startup Listener Services On Both Standby And Primary
zephir.10GR2-> lsnrctl start
atlas.10GR2-> lsnrctl start
# test TNS aliases
zephir.10GR2-> tnsping rightcube
TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 05-OCT-2007 17:44:43
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = atlas.domain.com)(PORT = 1521))) (CONNECT_DATA = (SID = 10GR2)))
OK (0 msec)
atlas.10GR2-> tnsping leftcube
TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 05-OCT-2007 17:45:09
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = zephir.domain.com)(PORT = 1521))) (CONNECT_DATA = (SID = 10GR2)))
OK (0 msec)
Create PHYSICAL standby
Create server parameter file on the standby database.
atlas.10GR2-> ls -l /copy/db_backup/10GR2/dbfs/
total 1859050
-rw-r----- 1 oracle dba 14270464 Oct 5 16:48 2007_10_05.backup.ctl
-rw-r--r-- 1 oracle dba 1391 Oct 5 16:56 init10GR2.ora
-rw-r--r-- 1 oracle dba 1391 Oct 5 17:06 init10GR2_stby.ora
-rw-r--r-- 1 oracle dba 212 Oct 5 16:48 recover.sh
-rw-r----- 1 oracle dba 14270464 Oct 5 16:55 stby_control.ctl
-rw-r----- 1 oracle dba 230694912 Oct 5 16:48 sysaux01.dbf
-rw-r----- 1 oracle dba 471867392 Oct 5 16:48 system01.dbf
-rw-r----- 1 oracle dba 214966272 Oct 5 16:48 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Oct 5 16:48 users01.dbf
atlas.10GR2->
atlas.10GR2-> sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 5 17:46:49 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> !ls -l /u01/app/oracle/product/10.2.0/db_1/dbs
total 48
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 1536 Oct 1 15:00 orapw10GR2
SQL> create spfile from pfile='/copy/db_backup/10GR2/dbfs/init10GR2_stby.ora';
File created.
SQL> !ls -l /u01/app/oracle/product/10.2.0/db_1/dbs
total 56
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 1536 Oct 1 15:00 orapw10GR2
-rw-r----- 1 oracle dba 3584 Oct 5 17:48 spfile10GR2.ora
Copy Standby Controlfile To It’s Proper Location
SQL> !grep control /u01/app/oracle/product/10.2.0/db_1/dbs/spfile10GR2.ora
*.control_files='/u01/oradata/10GR2/control01.ctl',
'/u02/oradata/10GR2/control02.ctl','/u03/oradata/10GR2/control03.ctl'
SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u01/oradata/10GR2/control01.ctl
SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u02/oradata/10GR2/control02.ctl
SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u03/oradata/10GR2/control03.ctl
Copy All Datafiles To Their Locations
SQL> !cat /copy/db_backup/10GR2/dbfs/recover.sh
cp -p ./sysaux01.dbf /u03/oradata/10GR2/sysaux01.dbf
cp -p ./system01.dbf /u02/oradata/10GR2/system01.dbf
cp -p ./undotbs01.dbf /u03/oradata/10GR2/undotbs01.dbf
cp -p ./users01.dbf /u02/oradata/10GR2/users01.dbf
SQL> !cd /copy/db_backup/10GR2/dbfs/; sh ./recover.sh
SQL> !find /*/oradata/10GR2 -type f -ls
95290 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u01/oradata/10GR2/control01.ctl
5829 32408 -rw-r----- 1 oracle dba 33161216 Oct 5 14:08 /u02/oradata/10GR2/arch/1_30_634834860.dbf
5830 1 -rw-r----- 1 oracle dba 1024 Oct 5 14:08 /u02/oradata/10GR2/arch/1_31_634834860.dbf
5831 4 -rw-r----- 1 oracle dba 3584 Oct 5 14:08 /u02/oradata/10GR2/arch/1_32_634834860.dbf
5832 192 -rw-r----- 1 oracle dba 187904 Oct 5 14:51 /u02/oradata/10GR2/arch/1_33_634834860.dbf
5833 144 -rw-r----- 1 oracle dba 134656 Oct 5 15:01 /u02/oradata/10GR2/arch/1_34_634834860.dbf
5834 1648 -rw-r----- 1 oracle dba 1678336 Oct 5 16:48 /u02/oradata/10GR2/arch/1_35_634834860.dbf
5835 12 -rw-r----- 1 oracle dba 11776 Oct 5 16:55 /u02/oradata/10GR2/arch/1_36_634834860.dbf
5836 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u02/oradata/10GR2/control02.ctl
5837 461048 -rw-r----- 1 oracle dba 471867392 Oct 5 16:48 /u02/oradata/10GR2/system01.dbf
5838 5136 -rw-r----- 1 oracle dba 5251072 Oct 5 16:48 /u02/oradata/10GR2/users01.dbf
5905 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u03/oradata/10GR2/control03.ctl
5906 225408 -rw-r----- 1 oracle dba 230694912 Oct 5 16:48 /u03/oradata/10GR2/sysaux01.dbf
5907 210040 -rw-r----- 1 oracle dba 214966272 Oct 5 16:48 /u03/oradata/10GR2/undotbs01.dbf
Start The Physical Standby Database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 327155712 bytes
Fixed Size 2029880 bytes
Variable Size 192939720 bytes
Database Buffers 130023424 bytes
Redo Buffers 2162688 bytes
Database mounted.
SQL>
Create Standby Redo Files
NOTE: this is a pre-req to enable “Real Time Apply”:
- this should match exactly what was done on primary database (see above “Configure Standby Redo Logs”)
- this is optional in fact the first time around I forgot to create these logs on standby and recovery process was still working it just reports the following error:
RFS[1]: Unable to open standby log 5: 313and uses ARCHIVE logs instead
alter database add standby logfile group 4
('/u01/oradata/10GR2/stby04.log',
'/u02/oradata/10GR2/stby04.log',
'/u03/oradata/10GR2/stby04.log')
size 52428800;
alter database add standby logfile group 5
('/u01/oradata/10GR2/stby05.log',
'/u02/oradata/10GR2/stby05.log',
'/u03/oradata/10GR2/stby05.log')
size 52428800;
alter database add standby logfile group 6
('/u01/oradata/10GR2/stby06.log',
'/u02/oradata/10GR2/stby06.log',
'/u03/oradata/10GR2/stby06.log')
size 52428800;
alter database add standby logfile group 7
('/u01/oradata/10GR2/stby07.log',
'/u02/oradata/10GR2/stby07.log',
'/u03/oradata/10GR2/stby07.log')
size 52428800;
Start Redo Apply
NOTE: The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session. See Section 6.3, “Applying Redo Data to Physical Standby Databases” for more information.
OPTION A is recommended
## OPTION A) [enables "Real Time Apply"]
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
## OPTION B) ["Real Time Apply" is disabled]
SQL> alter database recover managed standby database disconnect from session;
Database altered.
On Primary: verify ARCHIVE seqs
SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';
Session altered.
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;
2
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
30 2007-OCT-03 19:35:08 2007-OCT-05 14:08:29
31 2007-OCT-05 14:08:29 2007-OCT-05 14:08:31
32 2007-OCT-05 14:08:31 2007-OCT-05 14:08:37
33 2007-OCT-05 14:08:37 2007-OCT-05 14:51:48
34 2007-OCT-05 14:51:48 2007-OCT-05 15:01:54
35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24
35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24
36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46
36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46
37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07
37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42
38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42
13 rows selected.
SQL>
compare them to standby
SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';
Session altered.
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#; 2
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24
36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46
37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07
38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42
SQL>
Verify new archived redo log files were applied
SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APP
---------- ---
35 YES
36 YES
37 YES
38 YES
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/10GR2/arch
Oldest online log sequence 34
Next log sequence to archive 0
Current log sequence 39
SQL> !ls -lta /u02/oradata/10GR2/arch
total 71926
-rw-r----- 1 oracle dba 52429312 Oct 5 18:17 1_39_634834860.dbf
-rw-r----- 1 oracle dba 1379328 Oct 5 18:11 1_37_634834860.dbf
drwxr-xr-x 2 oracle dba 512 Oct 5 18:11 .
-rw-r----- 1 oracle dba 11776 Oct 5 18:11 1_36_634834860.dbf
-rw-r----- 1 oracle dba 1678336 Oct 5 18:11 1_35_634834860.dbf
-rw-r----- 1 oracle dba 113152 Oct 5 18:11 1_38_634834860.dbf
drwxr-xr-x 3 oracle dba 512 Oct 5 18:03 ..
-rw-r----- 1 oracle dba 134656 Oct 5 15:01 1_34_634834860.dbf
-rw-r----- 1 oracle dba 187904 Oct 5 14:51 1_33_634834860.dbf
-rw-r----- 1 oracle dba 3584 Oct 5 14:08 1_32_634834860.dbf
-rw-r----- 1 oracle dba 33161216 Oct 5 14:08 1_30_634834860.dbf
-rw-r----- 1 oracle dba 1024 Oct 5 14:08 1_31_634834860.dbf
SQL>
Here’s how Real Time Apply works
Test 1) – switch a log on PRIMARY
here’s what you’ll see in the primary alert log:
LNS1 started with pid=18, OS id=13637
Fri Oct 5 18:48:38 2007
Thread 1 advanced to log sequence 41
Current log# 2 seq# 41 mem# 0: /u01/oradata/10GR2/redo02.log
Current log# 2 seq# 41 mem# 1: /u02/oradata/10GR2/redo02.log
Current log# 2 seq# 41 mem# 2: /u03/oradata/10GR2/redo02.log
Fri Oct 5 18:48:39 2007
ARC0: Standby redo logfile selected for thread 1 sequence 40 for destination LOG_ARCHIVE_DEST_2
Fri Oct 5 18:48:39 2007
LNS: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2
here’s what’s reported in the STANDBY alert log:
Fri Oct 5 18:48:38 2007
RFS[1]: Successfully opened standby log 4: '/u01/oradata/10GR2/stby04.log'
Fri Oct 5 18:48:39 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 3909
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u01/oradata/10GR2/stby04.log'
Fri Oct 5 18:48:40 2007
Media Recovery Log /u02/oradata/10GR2/arch/1_40_634834860.dbf
Media Recovery Waiting for thread 1 sequence 41 (in transit)
Fri Oct 5 18:48:40 2007
Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0
Mem# 0: /u01/oradata/10GR2/stby04.log
Mem# 1: /u02/oradata/10GR2/stby04.log
Mem# 2: /u03/oradata/10GR2/stby04.log
Test 2) – WITHOUT SWITCHING A LOG
On STANDBY run the following SQL:
SQL> select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 40 1 16 0
ARCH CLOSING 41 1 1611 0
MRP0 APPLYING_LOG 42 42 102400 0
RFS IDLE 0 0 0 0
RFS IDLE 42 45 1 0
SQL>
On PRIMARY create a test table
SQL> create table test(t number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> insert into test values (2);
1 row created.
SQL> insert into test values (3);
1 row created.
SQL> commit;
Commit complete.
SQL>
*** DO NOT SWITCH A LOG ***
On STANDBY run the same SQL (note BLOCK# increasing):
SQL> r
1* select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 40 1 16 0
ARCH CLOSING 41 1 1611 0
MRP0 APPLYING_LOG 42 76 102400 0
RFS IDLE 0 0 0 0
RFS IDLE 42 78 1 0
SQL>
Cancel out of real time apply and open it in read only mode:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from test;
T
----------
1
2
3
Place the standby back in managed recovery mode This will take the standby directly from read only mode and place it in managed recovery mode
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL>
References
- http://download.oracle.com/docs/cd/B19306_01/server.102/b14239.pdf
- http://www.oracle.com/technetwork/database/features/availability/maa-wp-10grecoverybestpractices-129577.pdf
- https://www.scribd.com/document/252240161/MAA-WP-10gR2-SwitchoverFailoverBestPractices-pdf
Vitaliy Mogilevskiy July 7, 2007
Posted In: Data Guard
Tags: Real-Time-Apply, standby