Oracle 19c Feature Active Data Guard DML Redirection

Today, I will write about an extremely cool feature introduced in the Data Guard component on Oracle Database Active Data Guard DML Redirection

It was officially introduced in Oracle 19c, but was also present in the 18c version via the underscore parameter "_enable_proxy_adg_redirect=true".

SQL> alter session enable adg_redirect_dml;

With this feature, you can run DML operations on Active Data Guard standby databases. This enables you to run read-mostly applications, which occasionally execute DMLs, on the standby database". So imagine one reporting application that needs to create some staging tables, but that you couldn't have it running in the ADG as it was a fully read-only environment before. Now, this is no longer a problem.

The only issue I see with this feature is that it is controlled by a session modifiable level parameter. In other words, any database user can enable this for himself on the standby side.

Drawback:

However, starting on 19c, any user connected to the Data Guard environment could potentially change the data in the Production, as long as the user has the appropriate grants in primary to do so. Thus, leveraging the protection on being a "read-only" environment is not enough anymore. All the protections made on the primary should be extended to the DGs.

Solution:

Not sure but seems to be a potentially insecure feature, I found two options on the internet, but I haven't tested them yet.

*.Totally disabling the database links on the standby database.

*.Creating a logon trigger on the primary blocking connections coming from the standby database.

Refer Doc:

Active Data Guard DML Redirection 19c (Doc ID 2465016.1)

Role of Standby Redo Logs Why and How?

I came across an article about Standby Redo Logs, and I wanted to share it with you.

Purpose of Data Guard:

High Availability
Disaster Recovery
Data Protection
Workload Offloading & Testing

How Data Guard Works:  Dataguard Track the Changes on Primary Database and Replicate the same to Standby Databases through Redo Logs/Archive Logs.

In 19c, Oracle Introduced 2 exciting features.

*.Active DG DML Redirect, through which even the DML Statements can be executed directly on Standby Database which will be applied to Primary Database as well which reduces the workload of Primary Database. 

*.We have automatic Standby recovery through Flashback feature by which we don't need to flashback/rebuild the standby database after Primary Flashback..

Modes of Standby databases:

Maximum Protection Mode which ensure transaction on primary commits only after it is applied on Standby. There are no Dataloss in Max Protection mode.

Here the redo transport type is SYNC & AFFIRM.

Maximum availability Mode. In This also, the transaction on Primary gets commited only after it is applied on standby database. But If Standby not available due to outage/network issue, the transaction on primary will continue without any impact.. Usually there will not be any data loss. Here also the redo transport is sync & Affirm.

Maximum performance Mode in which the primary database transaction will not wait for Standby replication which improve the performance of the primary database. But there are significant chances of Data Loss. 

Why use SRLs?

If you configure your standby for Maximum Protection, then Standby Redo Logs are required. Most implementations are configured for Maximum Performance because they do not want the performance hit Max Protect may impart on their application. 

Even if you are using Max Performance, you still want to implement SRLs. 

To understand why, we first need to start by examining how redo transport works when SRLs do not exist. 

1. A transaction writes redo records into the Log Buffer in the System Global Area (SGA).

2. The Log Writer process (LGWR) writes redo records from the Log Buffer to the Online Redo Logs (ORLs).

3. When the ORL switches to the next log sequence (normally when the ORL fills up), the Archiver process (ARC0) will copy the ORL to the Archived Redo Log.

4. Because a standby database exists, a second Archiver process (ARC1) will read from a completed Archived Redo Log and transmit the redo over the network to the Remote File Server (RFS) process running for the standby instance.

5. RFS sends the redo stream to the local Archiver process (ARCn).

6. ARCn then writes the redo to the archived redo log location on the standby server.

7. Once the archived redo log is completed, the Managed Recovery Process (MRP0) sends the redo to the standby instance for applying the transaction.

With SRLs, not only do we have more resources, we also have different choices, i.e. different paths to get from the primary to the standby. The first choice is to decide if we are configured for Max Protect or Max Performance as I will discuss its impact below.

1. Just like without SRLs, a transaction generates redo in the Log Buffer in the SGA.

2. The LGWR process writes the redo to the ORL.

3. Are we in Max Protect or Max Performance mode?

4. If Max Protect, then we are performing SYNC redo transport. The Network Server SYNC process (NSSn) is a slave process to LGWR. It ships redo to the RFS process on the standby server.

5. If Max Performance mode, then we are performing ASYNC redo transport. The Network Server ASYNC process (NSAn) reads from the ORL and transports the redo to the RFS process on the standby server.

6. RFS on the standby server simply writes the redo stream directly to the SRLs.

7. How the redo gets applied depends if we are using Real Time Apply or not.

8. If we are using Real Time Apply, MRP0 will read directly from the SRLs and apply the redo to the standby database.

9. If we are not using Real Time Apply, MRP0 will wait for the SRL’s contents to be archived and then once archived and once the defined delay has elapsed, MRP0 will apply the redo to the standby database.

 Step 3 above is the entire reason we want to use Standby Redo Logs. If we are in Max Protect (SYNC) mode, then SRLs are required otherwise this process will not work. If we are in Max Performance mode, will still want SRLs. Why? We want SRLs to be configured, even in Max Performance mode because they reduce data loss to seconds, rather than minutes or hours. Max Performance mode with SRLs often achieves a near-zero data loss solution. The last sentence above is why you want to configure SRLs if you are in Max Performance mode. The other big benefit to SRLs is when Real Time Apply is being performed. As soon as the redo is in the SRL, it can be replayed on the standby database. We do not have to wait for a log switch to occur. Real Time Apply, only possible with SRLs, means the recovery time to open the standby database in a failover operation is as low as it can be.

 I often find that people are operating under the misconception that if you configure for ASYNC, configure for Max Performance, then only ARCn can transport redo from the primary to the standby. This used to be true in much older versions, but in 10g (maybe 9i), ARCn is only used to transport redo only if SRLs are not configured. If SRLs are configured, then for ASYNC, NSAn is used to transport redo. Furthermore, NSAn does this in near real time. I only ever configure Max Performance mode in my standby configurations and I often have 1 second or 2 second data loss.

Here comes the 2 new Processes. that is Network Server Async Process (NSA) and Network Server Sync Process (NSS). Prior to 12c, the Log-Write Network Server Process (LNS) Process which is was used instead of NSA and NSS.

 Without SRLs, then I must wait for a log switch to occur on the primary before the redo can be transported. If it takes one hour for the log switch to occur, then I can have one hour’s worth of data loss. If it takes six hours for that log switch to occur, then I can have six hour’s worth of data loss. This behavior was ameliorated by the DBA implementing the ARCHIVE_LAG_TARGET initialization parameter in their primary configuration. If the DBA set this parameter to 3600 seconds, then a log switch would occur at most once per hour. Even with this parameter, one hour of data loss may seem like a lot to most companies, especially when you do better. 

 All you have to do to enjoy data loss measured in a few seconds is to create Standby Redo Logs in your standby database. That’s it. It couldn’t be more simple.

 

 

Best Practice:

*.Make sure your ORL groups all have the same exact size. You want every byte in the ORL to have a place in its corresponding SRL.

*.Create the SRLs with the same exact byte size as the ORL groups. If they can’t be the same exact size, make sure they are bigger than the ORLs.

*.Do not assign the SRLs to any specific thread. That way, the SRLs can be used by any thread, even with Oracle RAC primary databases.

*.When you create SRLs in the standby, create SRLs in the primary. They will normally never be used. But one day you may perform a switchover operation. When you do switchover, you want the old primary, now a standby database, to have SRLs. Create them at the same time.

*.For an Oracle RAC primary database, create the number of SRLs equal to the number of ORLs in all primary instances. For example, if you have a 3-node RAC database with 4 ORLs in each thread, create 12 SRLs (3x4) in your standby. No matter how many instances are in your standby, the standby needs enough SRLs to support all ORLs in the primary, for all instances.

Data Guard Flow

PHYSICAL STANDBY DATA FLOW

Let us understand how the data flows in data guard setup as described above by Points 1 to 8 :

Point 1) On Primary Database, Transactions starts. All the buffer cache locks (exclusive locks) that are required for the transaction are acquired.

Point 2) On Primary Database, the redo blocks that describes the changes (or change vectors) are generated and stored in the processes’ Program Global Area (PGA). After successfully acquiring the redo allocation latch, space is then allocated in the redo log buffer. The redo generated then gets copied from the processes’ PGA into the redo log buffer.

Point 3) On Primary Database, The oracle foreground process tells the LGWR to flush the redo log buffers to disk. Remember that the database blocks in the database have not yet been updated with DML changes. The LGWR flushes the redo buffers to the ORL and acknowledges the completion to the session. At this point, the transaction is persistent on disk. No commit has occurred thus far.

---

At some future time, the database buffers that were previously changed will be written to disk by the database writer process (DBWR) at checkpoint time. This point is not marked in above diagram.

Note that before the DBWR process has flushed the database buffers to disks, the LGWR process must have already written the redo buffers to disk. This explicit sequence is enforced by the write-ahead logging protocol.

Also The ARCH process on the primary database archives the ORLs into archive log files. This point is also not marked in the above diagram.

---

Point 4) On Primary Database, the LNS process reads the recently flushed redo from the redo log buffer and sends the redo data to the standby database using the redo transport destination (LOG_ARCHIVE_DEST_n) that we defined during standby database creation. We are using ASYNC transport method, so the LGWR does not wait for any acknowledgment from the LNS for this network send operation. It does not communicate with the LNS except to start it up at the database start stage and after a failure of a standby connection.

Point 5) On Standby Database , the RFS reads the redo stream from the network socket into the network buffers, and then it writes this redo stream to the SRL.

Point 6) On Standby Database, The ARCH process archives the SRLs into archive log files when a log switch occurs at the primary database. The generated archive log file is then registered with the standby control file.
flow involves three distinct phases, as follows:

Point 7) On standby database, the actual recovery process starts from this step. The managed recovery process (MRP) will asynchronously read ahead the redo from the SRLs or the archived redo logs (when recovery falls behind or is not in real-time apply mode). The blocks that require redo apply are parsed out and placed into appropriate in-memory map segments.

Point 8) On standby database, the MRP process ships redo to the recovery slaves using the parallel query (PQ) interprocess communication framework. Parallel media
recovery (PMR) causes the required data blocks to be read into the buffer cache, and subsequently redo will be applied to these buffer cache buffers.

At checkpoint phase, the recently modified buffers (modified by the parallel recovery slaves) will be flushed to disk and also the update of datafile headers to record checkpoint completion.

PHYSICAL STANDBY DATABASE  RELATED PROCESSES

All the important processes are created in CIRCLE above diagram

On the Primary Database:

LGWR : The log writer process flushes log buffers from the SGA to Online Redo Log files.

LNS : The LogWriter Network Service (LNS below 12c version) reads the redo being flushed from the redo buffers by the LGWR and sends the redo over network to the standby database. The
main purpose of the LNS process is to free up the LGWR process from performing the redo transport role.

ARCH  : The archiver processes archives the ORL files to archive log files. Up to 30 ARCH processes can exist, and these ARCH processes are also used to fulfill gap resolution requests. Note that one ARCH process has a special role in that it is dedicated to local redo log archiving only and never communicates with a standby database.

On the Standby Database:

RFS : The main objective of the Remote File Server process is to perform a network receive of redo transmitted from the primary site and then writes the network buffer (redo data) to the standby redo log (SRL) files.

ARCH : The archive processes on the standby site perform the same functions performed on the primary site, except that on the standby site, an ARCH process generates archived log files from the SRLs.

MRP : The managed recovery process coordinates media recovery management. Remember that a physical standby is in perpetual recovery mode.

Basically we can categorize physical standby database  into three major components:

1) Data Guard Redo Transport Services

– To transfer the redo that is generated by the primary database to the standby database.

Point 4 and 5 in the above diagram are where Redo Transport works.

2) Data Guard Apply Services  

– To receive and apply the redo sent by Redo Transport Services to the standby database.

Point 7 and 8 in the above diagram are where Redo Apply works.

3) Data Guard Role Management Services

– To assist in the database role changes in switchover and failover scenarios.

This service works in the background and takes care of switchover/failover scenarios

GAP RESOLUTION FLOW

How to resolve the gap in between primary and standby.

When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again.  The fal_client and fal_server init.ora (spfile) parameters are set on the standby instance.

The Oracle docs note that fal_server specifies the FAL (fetch archive log) server for a standby database. The value for fal_server is an Oracle*Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

1.       The fetch archive log (FAL) client is the MRP process.  The fetch archive log (FAL) server is a foreground process that runs on the primary database and services the fetch archive log requests coming from the FAL client.  A separate FAL server is created for each incoming FAL client. 

2.       Fal_server and fal_client foreground process.

3.       Its resolve the gap in automatic mode when you define these parameter fal_Client and fal_server.

4.       Above diagram background process work to resolve the gap in automatic mode ARCH to RFS.

5.       If you not setup this parameter then manually transfer the log from primary to standby and register the archive logs to standby and perform recovery.

Role of Standby Redo Logs Why and How?

For more info Refer : https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1268294

Data Guard Archive Tracing

- You can trace the archive logs on either the primary or the standby database by setting the log_archive_trace initialization parameter

 alter system set log_archive_trace=8

- Trace files located in user_dump_dest location.

- On the primary database, the log_archive_trace parameter controls the output of the 
ARCn (archiver),
FAL (fetcharchived log), and the
LGWR (log writer) background processes.
On the standby databases, it traces the work of the
ARCn,
RFS (remote file server), and the
FAL processes.

- You can specify any of 17 levels of archive log tracing.

 0: Disables archivelog tracing (default)

 1: Tracks archival of redo log file

 2: Tracks archival status of each archivelog destination

 4: Tracks archival operational phase

 8: Tracks archivelog destination activity

 16: Tracks detailed archivelog destination activity

 32: Tracks archivelog destination parameter modifications

 64: Tracks ARCn process state activity

 128: Tracks FAL (fetch archived log) server related activities

 256: Tracks RFS Logical Client

 512: Tracks LGWR redo shipping network activity

 1024: Tracks RFS Physical Client

 2048: Tracks RFS/ARCn Ping Heartbeat

 4096: Tracks Real Time Apply

 8192: Tracks Redo Apply (Media Recovery or Physical Standby)

 16384: Tracks redo transport buffer management

 32768: Tracks LogMiner dictionary

If you specify Level 17, the trace file will include trace information from Levels 1, 2, 4, 8 and 16.

Data Guard Cascading Standby Database

11.2.0.2 > 

We can use the cascaded standby database feature to say offload reporting from the primary database to the cascaded standby database.

This is the environment:

A) Primary Database (TESTDB1)
B) Physical Standby Database (TESTDB2)
C) Cascaded Standby Database (TESTDB3)

So this is how the redo log transport will happen:

TESTDB1 >>>> TESTDB2
TESTDB2 >>>> TESTDB3

The assumptions are:

• The Data Guard will be configured in Maximum Availability mode
• The backup location on the Primary server and Standby server is different
• The directory structure on the Standby server is not the same as the Primary server
• The Standby server will host both the Standby database as well as the Cascaded standby database
• The environment used in this example is Oracle database version 11.2.0.3 on OEL Linux 5.7
• The db_unique_name of the Primary database is testdb1 , the Standby database is testdb2 and the Cascaded Standby database is testdb3
• It is assumed that a recent RMAN backup of the Primary database and archivelogs is available on the Primary server. In this example it exists in the FRA.

Steps

Network Configuration

The tnsnames.ora file on both the Primary database server and Standby database serve have the entries :

TESTDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb1)

)

)

TESTDB2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb2)

)

)

TESTDB3 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb3)

)

)

The listener.ora on the Standby database server has a static entry for testdb2 and testdb3

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=testdb2)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=testdb2)

)

(SID_DESC=

(GLOBAL_DBNAME=testdb3)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=testdb3)

)

)

On Primary take a backup of the current control file which will be used by the Standby database

RMAN> backup current controlfile for standby;

…….

…….

channel ORA_DISK_1: finished piece 1 at 06-MAY-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_ncnnf_TAG20130506T165754_8rgo3n7o_.bkp tag=TAG20130506T165754 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

………

Make a note of the backup piece name as we will be using this backup for the Standby database creation.

Take a backup of the database and archivelogs

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> backup database plus archivelog ;

Starting backup at 06-MAY-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=814288291

input archived log thread=1 sequence=4 RECID=2 STAMP=814288349

input archived log thread=1 sequence=5 RECID=3 STAMP=814289179

input archived log thread=1 sequence=6 RECID=4 STAMP=814289262

input archived log thread=1 sequence=7 RECID=5 STAMP=814295555

input archived log thread=1 sequence=8 RECID=8 STAMP=814296764

……..

……….

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb1/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/testdb1/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/testdb1/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/testdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 06-MAY-13

channel ORA_DISK_1: finished piece 1 at 06-MAY-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 06-MAY-13

…..

Copy the most recent database backup, archivelog backup and controlfile backup to the staging location on the Standby server

cd /u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06

[oracle@pdemvrhl061 2013_05_06]$ ls -l

total 1898088

-rw-r----- 1 oracle dba 684387840 May 6 17:01 o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

-rw-r----- 1 oracle dba 64000 May 6 17:02 o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

-rw-r----- 1 oracle dba 9994240 May 6 17:09 o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

-rw-r----- 1 oracle dba 1247256576 May 6 17:02 o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

scp -rp * oracle@host2:/home/oracle/backup

Copy password file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb2

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb3

Copy init.ora file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb2.ora

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb3.ora

Make the required changes to the Standby database (testdb2) init.ora file

These are the changes we have made to the init.ora which we have copied from the Primary database (testdb1) – the remaining parameters like sga_target, db_name, diagnostic_dest etc will be the same regardless if the database is a Primary database or Physical standby database.

Review particularly the entries related to redo transport like the log_archive_dest_* entries

*.audit_file_dest='/u01/app/oracle/admin/testdb2/adump'

*.control_files='/u01/app/oracle/oradata/testdb2/control01.ctl','/u01/app/oracle/oradata/testdb2/control02.ctl'

*.db_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'

*.db_unique_name='testdb2'

*.fal_client='testdb2'

*.fal_server='testdb1'

*.log_archive_config='DG_CONFIG=(testdb1,testdb2,testdb3)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb2'

*.log_archive_dest_2='SERVICE=testdb1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb1'

*.log_archive_dest_3='SERVICE=testdb3 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3'

*.log_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'

*.service_names='testdb2'

Create the required directory structure on the Standby site

$ mkdir -p /u01/app/oracle/admin/testdb2/adump
$ mkdir -p /u01/app/oracle/admin/testdb3/adump
$ mkdir -p /u01/app/oracle/oradata/testdb1
$ mkdir -p /u01/app/oracle/oradata/testdb3
$ mkdir  -p /u01/app/oracle/fast_recovery_area

Add entries in /etc/oratab

 testdb2:/u01/app/oracle/product/11.2.0/dbhome_1:N
testdb3:/u01/app/oracle/product/11.2.0/dbhome_1:N

Start the Standby database instance in NOMOUNT state

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             272633136 bytes

Database Buffers          134217728 bytes

Redo Buffers                8466432 bytes

SQL>

Restore the Standby Controlfile from the backup copied from Primary
 

RMAN> restore standby controlfile from '/home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp';

Starting restore at 06-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/testdb2/control01.ctl

output file name=/u01/app/oracle/oradata/testdb2/control02.ctl

Finished restore at 06-MAY-13

Mount the standby database (note in 11g we can just ‘ALTER DATABASE MOUNT’ command)

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database

released channel: ORA_DISK_1

Now catalog all the backup pieces which we have copied from Primary

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database

=====================================

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Do you really want to catalog the above files (enter YES or NO)? y

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Restore the database –on the Standby database testdb2

Note the files are being restored in the new location:/u01/app/oracle/oradata/testdb2

RMAN> restore database;

Starting restore at 06-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/testdb2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/testdb2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/testdb2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/testdb2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/testdb2/example01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

Finished restore at 06-MAY-13

Note the last archived log sequence # which has been backed up

RMAN> list backup of archivelog all;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

……

………

  List of Archived Logs in backup set 23

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    60      1602201    06-MAY-13 1604763    06-MAY-13

  1    61      1604763    06-MAY-13 1604769    06-MAY-13

  1    62      1604769    06-MAY-13 1629416    06-MAY-13

  1    63      1629416    06-MAY-13 1674083    07-MAY-13

  1    64      1674083    07-MAY-13 1694517    07-MAY-13

  1    65      1694517    07-MAY-13 1694574    07-MAY-13

  1    66      1694574    07-MAY-13 1694766    07-MAY-13

Recover the database – the SET UNTIL SEQUENCE will be the last archive log sequence backup available plus 1

RMAN> run

2> { set until sequence 67;

3> recover database;

4> }

executing command: SET until clause

Starting recover at 07-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=64

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=65

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=66

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp tag=TAG20130507T141625

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc thread=1 sequence=64

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc RECID=8 STAMP=814803519

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc thread=1 sequence=65

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc RECID=7 STAMP=814803519

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc thread=1 sequence=66

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc RECID=6 STAMP=814803519

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-MAY-13

Now we need to perform the same steps for the Cascaded Standby database testdb3

We make the following changes in the init.ora file which we have copied from the Primary database:

 *.audit_file_dest=’/u01/app/oracle/admin/testdb3/adump’
*.control_files=’/u01/app/oracle/oradata/testdb3/control01.ctl’,’/u01/app/oracle/oradata/testdb3/control02.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.log_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.db_unique_name=’testdb3′
*.fal_client=’testdb3′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb3′
*.service_names=’testdb3′
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(testdb1,testdb2,testdb3)’

•  Set the enevironment for the database testdb3
• Startup NOMOUNT the database
• Restore the backup of the controlfile taken from the Primary database
• Mount the database
• Restore the database
• Recover the database until the same archived log sequence number we used for the earlier standby database testdb2.

Add the Standby Redo Log Files

Create the Standby redo log files on all the three databases. Note that we use the same size as the redo log files and create one additional group in case of the standby redo log files as compared to the online redo log files.

For example:

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo04.log' size 50m;

Database altered.

Open both the Standby Database as well as the Cascaded standby database and put them in managed recovery mode

SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

Configure redo transport for the Primary Database

We add the following entries in the init.ora of the Parimary database

 *.fal_client=’testdb1′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb1′
*.log_archive_dest_2=’SERVICE=testdb2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb2′
*.log_archive_dest_3=’SERVICE=testdb3  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3′;
*.log_archive_dest_state_3=’DEFER’
*.standby_file_management=’AUTO’

Note that we have set parameter log_archive_dest_state_3 to DEFER because in normal operation, the Primary database testdb2 will only ship redo logs to the standby database testdb2 and NOT the cascaded standby database testdb3.

When the current Primary database testdb1 assumes the role of a standby database at some time in the future when a switchover happens, then only we need to enable the log shipping from testdb1 to testdb3.

Change the protection mode to MAXIMUM AVAILABILITY

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2233000 bytes

Variable Size             494931288 bytes

Database Buffers          335544320 bytes

Redo Buffers                6574080 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select  PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Lets Test!

Primary Database TESTDB1
 

SQL> update customers
  2  set cust_first_name=’Smith’
  3   where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

Standby Database/Cascading Standby TESTDB2
 

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME
——————–
Smith

Cascaded Standby Database TESTDB3

At this stage the cascaded standby database is lagging behind the Primary as well as the cascading Standby database because the changes from the TESTDB2 will only be cascaded to TESTDB3 when a log switch is triggered when the archive redo log file fills up

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME

--------------------

Sachin

Primary Database TESTDB1

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

Cascaded Standby Database TESTDB3

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME

--------------------

Smith

Reference

Usage, Benefits and Limitations of Standby Redo Logs (SRL) [ID 219344.1].

Que & Ans.

Question: I have cascade standby in our environment for two node rac. scenario is like that –two node rac primary –> two node rac standby –> standalone standby (cascade). IS cascade standby for two node rac is a standalone is possible?

Answer: if you are using 11.2.0.2 or a higher version, then the restriction has been removed. You can have a cascading standby built for a RAC primary and is supported irrespective of whether your cascaded standby is a standalone or RAC.