raccheck 2.2 beta released

raccheck 2.2 beta released
v. 2.2.0 Beta

Support for Single Instance Configurations (i.e., no longer limited to RAC configurations)
High Availability (HA) Best Practices
New checks and bug fixes

This new version was prodiced with collaboration with ORACLE HA team in Support to add their Top 20 checks for common problems related to backup and recovery and Data Guard. These new checks augment the MAA Scorecard. These new checks can be obtained with the following syntax:

./raccheck -r – includes the standard health checks as well as HA checks.
./raccheck -c hacheck -o -v – abbreviates the report output to ONLY the HA related checks

raccheck can be downloaded from RACcheck – RAC Configuration Audit Tool (Doc ID 1268927.1)

CREDIT: Bob Caldwell

November 2, 2012

Posted In: Data Guard, Installs, RAC, RMAN, Scripts

Tags: , , ,

Using Flashback Database to strengthen Data Guard Setup

MAA recommends to enable Flashback Database on the primary prior to FailOver:

Enable Flashback Database to reinstate failed production databases after a
failover operation has completed. Flashback Database provides a second
very significant function, enabling fast point in time recovery if needed.

See: MAA_WP_10gR2_SwitchoverFailoverBestPractices.pdf

Enabling Flashback Database involves setting up flash recovery area and setting a flashback retention target which specifies how far back you want to be able to restore your database using the Flashback Database feature.

Once Flashback Database is setup, the database starts to copy images of each altered block into the flashback logs — this works for all datafiles. When it’s time to Flashback the database, the copies of the blocks from flashback logs are used to reconstruct the datafiles to a state just prior to the desired flashback time, the redo/arch logs are then used to bring a datafiles to a consistent state.

WARNING::
Redo logs must be available for the entire time period spanned by the
flashback logs, whether on tape or on disk. (In practice, however, redo
logs are generally needed much longer than the flashback retention target
to support point-in-time recovery.)

There are also a number of operations you can perform on your database, such
as dropping a tablespace or shrinking a datafile, which cannot be reversed
with Flashback Database. After such an operation, the flashback database
window begins at the time immediately following that operation.

One thing to consider is that the only way to guarantee a database can be returned to a specific point in time is to use guaranteed restore points. In other words, don’t use “normal” restore points for this purpose and don’t rely on Flashback Database alone. The only constraint to how far you can go back, is the size of your disk space in the flash recovery area.

Again, WARNING:
Limitations that apply to Flashback Database also apply to guaranteed
restore points. For example, shrinking a datafile or dropping a tablespace
can prevent flashing back the affected datafiles to the guaranteed restore
point.

Creating a guaranteed restore point without having enough sufficient free space in the flash recovery area (FRA) will cause the FRA to fill completely, that’s because “No file in the flash recovery area is eligible for deletion if it is required to satisfy the guarantee [restore point]“. In many circumstances, this will cause your database to halt.

To save on space in FRA you can disable the Flashback Database and still create a guaranteed restore point. In this case, the first time a datafile block is modified, an image of this block before the modification is stored in the flashback logs. This saves on space because only one-time copy of every changed data block is stored there, but subsequent modifications to the same block do not cause the block contents to be logged again. This method works really well and it’s more efficient as long as your primary only needs to be able to return to the specific point in time at which the guaranteed restore point was created, such as to a before-state of a failed application upgrade that might have made changes to a database.

See: 5.1.1 About Flashback Database

That’s theory, how about some practice time? Follow along. First, we verify our setup (in this case no FRA [db_recovery_file_dest] is setup:

rac1.XRACP1-> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 12 19:25:07 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Connected.
SQL>    select * from v$recovery_file_dest;

no rows selected

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
SQL> select * from v$flash_recovery_area_usage;

no rows selected

SQL>

Lets create FRA (NOTE: since this is a RAC database, I am creating FRA on a clustered FS (OCFS2) /u02):

[root@rac1 log]# mkdir -p /u02/oradata/rcv_area
[root@rac1 log]# chown -R oracle:dba /u02/oradata

rac1.XRACP1-> df -k /u02
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/2000b080023002235p1
                      37899136   1971200  35927936   6% /u02
rac1.XRACP1->


SQL> alter system set db_recovery_file_dest_size=28g scope=both sid='*';

System altered.

SQL> alter system set db_recovery_file_dest='/u02/oradata/rcv_area' scope=both sid='*';

System altered.

SQL>

SQL> set lines 132
SQL> col name format a35
SQL> set trims on
SQL> select * from v$recovery_file_dest;

NAME                                SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------- ----------- ---------- ----------------- ---------------
/u02/oradata/rcv_area                3.0065E+10          0                 0               0

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

SQL>

So far we setup the FRA, now lets try to create guaranteed restore point. One thing to remember here though is this — if flashback database is not enabled (to save on space), then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped). If you attempt to create a guaranteed restore point when the database is opened you get ORA-38787 error:

SQL> CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE;
CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'GR_01'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.

A normal restore point however, works, but it will not do anything since flashback database is off, here’s an example:

SQL> !ls -l /u02/oradata/rcv_area
total 0

SQL> CREATE RESTORE POINT nr_01;

Restore point created.

SQL> !ls -l /u02/oradata/rcv_area
total 0

SQL> create table xyz(t number) tablespace tools;

Table created.

SQL> !ls -l /u02/oradata/rcv_area
total 0


SQL> insert into xyz values (1000);

1 row created.

SQL> commit;

Commit complete.

SQL> !ls -l /u02/oradata/rcv_area
total 0

SQL>

As you can see nothing happened … even though a normal restore point is created. Lets drop it (this syntax works for both NORMAL and GUARANTEED RPs):

SQL> drop restore point nr_01;

Restore point dropped.


SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
           GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
           FROM V$RESTORE_POINT;
  2    3
no rows selected

Now lets create GUARANTEED restore point WITHOUT enabling FLASHBACK DATABASE which requires database to be mounted only, and since this is RAC database all other instances must be shutdown first as well:

rac1.XRACP1-> srvctl stop database -d XRACP
rac1.XRACP1-> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 12 20:01:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             822084152 bytes
Database Buffers          771751936 bytes
Redo Buffers               14692352 bytes
Database mounted.
SQL> CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
rac1.XRACP1-> srvctl start database -d XRACP
rac1.XRACP1-> srvctl start service -d XRACP
rac1.XRACP1->

Now lets see what we can gather from V$RESTORE_POINT:

SQL> col name format a15
col time format a35
set lines 132
set trims on
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL>
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
           GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
           FROM V$RESTORE_POINT;
  2    3
NAME                   SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- ----------------------------------- --------------------- --- ------------
GR_01           9.0317E+12 12-JUN-09 08.02.19.000000000 PM                         1 YES     63766528

SQL>
   

rac1.XRACP1-> find /u02/oradata/rcv_area -ls
5161314    4 drwxr-xr-x   3 oracle   dba          4096 Jun 12 20:02 /u02/oradata/rcv_area
5161316    4 drwxr-x---   3 oracle   oinstall     4096 Jun 12 20:02 /u02/oradata/rcv_area/XRACP
5161317    4 drwxr-x---   2 oracle   oinstall     4096 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback
5161318 15576 -rw-r-----   1 oracle   oinstall 15949824 Jun 12 20:04 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365ov16_.flb
237846 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qm48_.flb
4128929 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qtxy_.flb
7227425 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qtyd_.flb
rac1.XRACP1->

Much better now — it is working, and a few days later:

SQL> !date;ls -l /u02/oradata/rcv_area/XRACP/flashback
Mon Jun 15 15:02:17 PDT 2009
total 358248
-rw-r-----  1 oracle oinstall 15949824 Jun 13 04:00 o1_mf_5365ov16_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qm48_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qtxy_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qtyd_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 22:00 o1_mf_536dm0jm_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 22:01 o1_mf_536dmnxj_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 02:00 o1_mf_536dntxs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 23:03 o1_mf_536j9b8b_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 23:28 o1_mf_536j9jvj_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 06:00 o1_mf_536kqnmf_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 15:10 o1_mf_536tnpky_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 21:00 o1_mf_5371or32_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 23:03 o1_mf_5378qrx2_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 12:00 o1_mf_537m8tox_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 07:33 o1_mf_5388yvsd_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 19:00 o1_mf_538xgm1v_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 02:00 o1_mf_5394pdx7_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 23:04 o1_mf_539h0tcs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 04:00 o1_mf_53b2lorr_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53bl5v8y_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53cbs5hs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53cs3y00_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53dbfmx5_.flb

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
              GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
              FROM V$RESTORE_POINT;  2    3

NAME                   SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- ----------------------------------- --------------------- --- ------------
GR_01           9.0317E+12 12-JUN-09 08.02.19.000000000 PM                         1 YES    366657536

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG               1.22                         0              23

6 rows selected.

SQL>

And that’s how you create a guaranteed restore point. But what do you use it for, with regards to STANDBY FAILOVER? You really shouldn’t, because you don’t know when a FAILOVER occurs so you can’t create a point right before it. You should however, use a guaranteed restore point during a SWITCHOVER because during a switchover you are in full control of both primary and standby databases. In a FAILOVER scenario, enabling Flashback Database [ ALTER DATABASE FLASHBACK ON; ] is more effective than trying to create guaranteed restore points.

Still have questions? Here are pointers to additional reading with excerpts:

From Note:565535.1 titled “Flashback Database Best Practices & Performance”:

Manual primary database reinstate- if Data Guard is being used without the
fast-start failover feature and a Data Guard failover is necessary, then
flashback database can be used to manually reinstate the failed primary
database. This is documented in the Data Guard Administration and Concepts
guide – see “12.4 Using Flashback Database After a Failover” here:

And from 12.4 Using Flashback Database After a Failover:

After a failover occurs, the original primary database can no longer
participate in the Data Guard configuration until it is repaired and
established as a standby database in the new configuration. To do
this, you can use the Flashback Database feature to recover the
failed primary database to a point in time before the failover
occurred, and then convert it into a physical or logical standby
database in the new configuration.

August 22, 2009

Posted In: Data Guard, RAC

Tags: , , , , ,

Data Guard — it’s real it’s Oracle, you know what you’ve got!

Data Guard — it’s real it’s Oracle, you know what you’ve got!” said Joe Meeks (Director of High Availability Product Management at Oracle) in his closing statement during the Live Webcast presentation titled “Maximize Availability with Oracle Database 11g” held today.

The focus of this presentation was on “Active Data Guard Option” of Oracle 11g. In one sentence — Active Data Guard Option lets you open your Physical Standby database READ-Only all while redo-apply is taking place from primary. In contrast, in 10g, redo-apply halted when physical standby was opened READ-Only, the redo transportation was still taking place but it was not being applied in 10g. 11g bridges this gap and it does so very efficiently — as Joe Meeks said “With 11gR2 Active Data Guard – we are confident that latency is very low”.

Data Guard != ZERO Downtime because rolling upgrade still requires downtime. For example, according to Joe’s recollection of a major upgrade at UPS — their downtime going from 10g to 11g took ~ 4 minutes. To get ZERO downtime you must design your application around Oracle Streams.

In contrast to Data Guard, which uses media recovery (block by block), Oracle Streams are building logical change records using SQL. Steams are providing full featured replication features at your disposal, for example you can:

  • replicate a subset of database
  • perform transformations
  • replicate across platforms
  • setup multimaster replications
  • replicate to a database and have it replicate different subsets of data out

Data Guard is simpler, one way replication — focused on DR using media recovery process (time tested process). Physical Standby is very horizontal, transparent to the storage and application.

PS: Active Data Guard is only available for Physical Standby (block by block changes) and it requires an additional license.

August 19, 2009

Posted In: Data Guard

Tags: , , , , , ,

What the heck is “snapshot standby”?

I was just reviewing Note:565535.1 Flashback Database Best Practices & Performance and came across the following paragraph under the title of “Measuring Impact of turning Flashback Database ON / Analyzing the Performance Impact“:

If you’re on RDBMS release 11g and have a physical standby, then you can enable flashback database on your physical standby and then convert it to a snapshot standby (this can also be done in 10.2 with a manual snapshot standby, but no redo is received and applied as in the 11g Snapshot Standby. See Dell 10g Snapshot Standby – Dell 10g Snapshot Standby). Then when you run a workload against the snapshot standby it will be with flashback database logging enabled.

So what the heck is “snapshot standby”?

Essentially it allows you to convert your 11g physical standy to a “Snapshot standby” so that you can “Make changes to the Primary and the the Snapshot standby in some common tables” and then “Convert the Snapshot standby back to a Physical standby”. Makes sense? NO? OK, I know, how about this:

  • A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
  • A snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
  • A snapshot standby can be created from Enterprise Manager, the Data Guard Broker command line interface (DGMGRL) or from SQL*Plus.

Better? YES. This is probably THE reason to go 11g if you haven’t already. Basically you get an instant REPORTING/TEST server without loosing your Physical Standby database. See: Using Snapshot Standby for an example.

June 15, 2009

Posted In: Data Guard

Tags: , , ,

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