Create Oracle 10gR2 standby database using Real Time Apply

  1. Configuration Details
  2. Configure Primary Database
  3. Create PHYSICAL standby
  4. Here’s how Real Time Apply works
  5. 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 3–4 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

I used a custom backup script


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”:

  1. this should match exactly what was done on primary database (see above “Configure Standby Redo Logs”)
  2. 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: 313 and 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

July 7, 2007

Posted In: Data Guard

Tags: ,