Easy/Different steps build lower environment or build your DR setup

PROD database to Lower environment (PROD -> DEV/TEST/UAT) 

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

With PROD backup (RMAN-backups):

Option 1: Restore recover from backup 

- restore 

- recover 

- open database with reset logs 

Option 2: RMAN clone / RMAN duplicate from backup 

- duplicate target database to TESTDB backup location '/u01/backup' nofilenamecheck;

Without PROD backup (without RMAN-backups):

Option 3: RMAN active database duplicate 

$rman target sys/password@PROD 

RMAN> connect auxiliary sys/password@TESTDB

RMAN> duplicate target database to TESTDB from active database;

PROD database to DR environment (PROD -> DR) 

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

With PROD backup (RMAN-backup):

Option 1: Restore recover from backup 

- restore 

- recover 

- start MRP 

Option 2: RMAN clone / RMAN duplicate from backup 

- duplicate target database for standby database backup location '/u01/backup' filenamecheck/nofilenamecheck;

- start MRP 

Without PROD backup (without RMAN-backup):

Option 3: RMAN active database duplicate 

$rman target sys/password@PROD 

RMAN> connect auxiliary sys/password@DR

RMAN> duplicate target database for standby database from active database;

- start MRP 

https://mallik034.blogspot.com/2021/05/19c-dataguard-build-document.html

Option 4: Service based standby build (from 12c onwards) (Without PROD backup)

rman target / (on Standby Side) 

RMAN> restore standby controlfile from PROD service;

RMAN> restore standby database from PROD service;

RMAN> recover standby database from PROD service;

ORA-01156: recovery or flashback in progress may need access to files

Issue:

Standby redo log file creation on Standby database or DR database is failing 

Error Message: 

ORA-01156: recovery or flashback in progress may need access to files

Cause: 

MRP process is running on standby database which is preventing to create standby redo logs

Fix:

Stop MPR and create standby redo logs and start the MRP process

Error Logs and commands output:

1. Standby redo log file creation on Standby database or DR database is failing 

[oracle@oraclelab3 2025_05_07]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:25:58 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

no rows selected

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL>

2. MRP process was up and running which is preventing us to create a standby redo logs on the standby database. Stop the MPR and create the standby redo logs

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

Database altered.

SQL> set pages 1000

SQL> set lines 1000

SQL> col DBID for a10

SQL> select * from v$standby_log;

    GROUP# DBID          THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS T_CHANGE# LAST_TIME     CON_ID

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

         4 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         5 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         6 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

         7 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

SQL>

3. Start the MPR process once the after the standby redo log files are created successfully

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab3 2025_05_07]$

DataGuard sync status monitor automation script

1. Connect PROD and check the oldest archive log and perform couple of logswitches

[oracle@oracledb ~]$ env |grep ORA

ORACLE_SID=ORCL

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 16:10:03 2024

Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/backup

Oldest online log sequence     11276

Next log sequence to archive   11278

Current log sequence           11278

SQL> alter system switch logfile;

System altered.

SQL>

2. Execute the DG gap script to monitore or check the DG sync status between PROD and DR 

[oracle@oraclesb ~]$ . oraenv

ORACLE_SID = [oracle] ? ORCLSB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclesb ~]$

[oracle@oraclesb ~]$ env |grep ORA

ORACLE_SID=ORCLSB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclesb ~]$ cd /mnt/script/

[oracle@oraclesb script]$ ll *gap*

-rw-r--r--. 1 oracle oinstall 2279 Nov 11  2022 dg_gap_on_standby                                                                   .sql

[oracle@oraclesb script]$ cd /mnt/script/

[oracle@oraclesb script]$ ll *gap*

-rw-r--r--. 1 oracle oinstall 2279 Nov 11  2022 dg_gap_on_standby.sql

[oracle@oraclesb script]$ sqlplus / as sysdba @dg_gap_on_standby.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 14:11:22 2024

Version 19.19.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

Recovery lag time from Source...

TIME LAG

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

This DR env is 0 Hrs and -30 Mins behind last archive log from PROD...

Recovery details per thread

'--------------------------'

Last logs applied

'----------------'

 SEQUENCE#    THREAD# ARCHIVED APPLIED COMPLETED

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

     11272          1 YES      YES     2024/04/01 13:56:42

     11271          1 YES      YES     2024/04/01 13:41:43

     11270          1 YES      YES     2024/04/01 13:26:43

     11269          1 YES      YES     2024/04/01 13:11:43

     11268          1 YES      YES     2024/04/01 12:56:43

     11267          1 YES      YES     2024/04/01 12:41:44

     11266          1 YES      YES     2024/04/01 12:26:41

     11265          1 YES      YES     2024/04/01 12:11:44

     11264          1 YES      YES     2024/04/01 11:56:41

     11263          1 YES      YES     2024/04/01 11:41:42

The following query should show varying block# for error thread#

'---------------------------------------------------------------'

PROCESS   STATUS        SEQUENCE#     BLOCKS     BLOCK#

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

ARCH      CLOSING           11266       1264          1

ARCH      CLOSING           11271       1328          1

ARCH      CLOSING           11259          7          1

ARCH      CLOSING           11272       1005          1

RFS       IDLE              11273          1       8489

MRP0      APPLYING_LOG      11273     409600       8489

Number of files to catch up; registered by NOT applied...

'--------------------------------------------------------'

   THREAD#   COUNT(1)

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

           ----------

sum

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oraclesb script]$

3. DG gap monitor script

[oracle@oraclesb script]$ more dg_gap_on_standby.sql

REM

REM Purpose: This script will help in cheking archive log gap or sync status between PROD & Standby Database

REM AUTHOR: Mallikarjun Ramadurg

REM SCRIPT Name: dg_gap_on_standby.sql

REM Usage: Connect to Standby Database on sql command prompt and run as below

REM Example: SQL>@dg_gap_on_standby.sql

REM

set verify off

set feed off

set timing off

PROMPT

PROMPT Recovery lag time from Source...

select  'This DR env is '||trunc(sysdate-max(first_time))||' Hrs and '||

        trunc(((sysdate-max(first_time))*24-trunc((sysdate-max(first_time))*24))*60)||' Mins'||

        ' behind last archive log from PROD...' "TIME LAG"

from v$archived_log

where applied = 'YES';

PROMPT

PROMPT Recovery details per thread

PROMPT '--------------------------'

 select al.thread# , min(al.sequence#) "MIN SEQ#", max(al.sequence#) "MAX SEQ#" ,

  min(to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss')) "MIN FIRST TIME", round(sum(blocks * block_size) /1024/1024,2) "MB"

  from v$archived_log al,

 (select thread#, nvl(max(sequence#),0) max_seq from v$archived_log

  where applied = 'YES' and registrar = 'RFS' and standby_dest = 'NO'

 group by thread#) maxa

 where applied= 'NO'

 and registrar = 'RFS'

 and standby_dest = 'NO'

and (al.thread# = maxa.thread# and al.sequence# > maxa.max_seq)

and exists (select 'x' from v$instance where status != 'OPEN')

group by al.thread#

having min(al.sequence#) > 0

;

PROMPT

PROMPT Last logs applied

PROMPT '----------------'

col archived for a8

col applied for a7

SELECT * FROM (

   SELECT sequence#, thread#, archived, applied,

        TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI:SS') AS completed

    FROM sys.v$archived_log

    WHERE applied='YES'

     ORDER BY completed DESC)

WHERE ROWNUM <= 10;

PROMPT

PROMPT The following query should show varying block# for error thread#

PROMPT '---------------------------------------------------------------'

select process, status, sequence#, blocks, block#

from v$managed_standby

where sequence# <> 0 ;

PROMPT

PROMPT Number of files to catch up; registered by NOT applied...

PROMPT '--------------------------------------------------------'

break on report

compute sum of count(1) on report

SELECT thread#, count(1) FROM V$ARCHIVED_LOG where applied <> 'YES' group by thread# order by thread#;

set timing on

[oracle@oraclesb script]$

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com

#DBAchallenge -5 || Questions on corruption, DR sync issues, FRA & RAC – Cache Fusion & Split Brain Syndrome

Interesting Question 11...

Detected block corruption on one of the datafile in your Physical Standby database..

How you will recover that block corruption on your Physical Standby Database?

Answer 11…

---We have db_block_checking, db_block_checksum, db_ultra_safe, db_lost_write_protect parameters in both primary and Standby and this can prevent it.

--- We can set the above parameter which will prevent the block corruption.

--- In case if corruption happens then Active DataGuard Database will perform corruption detection, prevention, and automatic repair where as in normal physical standby database we need to manually recover the corrupted blocks using valid backups.

 

Resolving Logical Block Corruption Errors in a Physical Standby Database (Doc ID 2821699.1)

V$DATABASE_BLOCK_CORRUPTION

RMAN> RECOVER BLOCK DATAFILE 7 BLOCK 3;

Interesting Question 12...

Production database archive destination is full What is your immediate workaround or fix?

Considering scenario...

Case1: Your archive destination is set to FRA

Case2: Your archive destination is set to some custom location other than FRA.

Answer 12…

Case1:

--- We can increase the FRA size if there free space available physically.

--- Delete older archive logs if possible from FRA location.

--- Take RMAN backup with delete input.

--- Temporarily change the archive log location to some temporary location

--- Move some files from FRA location to some temporary location

 

Case 2:

--- We can increase the Archive log destination size if there free space available physically.

--- Delete older archive logs if possible from Archive location.

--- Take RMAN backup with delete input.

--- Temporarily change the archive log location to some temporary location

--- Move some files from Archive location to some temporary location

Interesting Question 13...

Your Standby/DR database goes out of sync then How you will make it sync?

Considering scenario.

Case1: Few archive logs gap

Case2: Huge archive log gap

Case3: archive logs deleted/missing from primarily without applying DR/Standby

Answer 13…

Case1:

--- If its temporary network issue then fal_server & fal_client it will resolve the archive gap

--- Copy the missing archives to stand by side and catalogue them and start mrp.

Case2:

--- Do rollforward incremental restore by taking incremental backup from PROD and restore on DR

--- Do rollforward automatic incremental restore using service name starting from 12c

Case3:

--- Do rollforward incremental restore by taking incremental backup from PROD and restore on DR

--- Do rollforward automatic incremental restore using service name starting from 12c

 

Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)

Interesting Question 14...

What is TAF in RAC?

Where you configure TAF? Client side or Server side?

Will TAF support DML statements?

Answer 14…

--- TAF: Transparant application failover.

--- TAF can be configured on server side as well client side.

--- Server Side:

     --- Service attributes are used server-side to hold the TAF configuration.

--- Client side:

     --- We can use tnsnames.ora file to configure TAF.

Transparent application failover... In case any instance crashed The application sessions will be failed over to active instance and select statements will continue to run where it left over again.

--- Best recommendation is to configure on server side and TAF will not support DML statement

Interesting Question 15...

What is cache fusion in RAC?

What is split brain syndrome in RAC?

What is simple Majority Rule in RAC?

 

Why we should have odd number of voting disks in RAC? What happens if we have even number of voting disks?

Answer 15…

--- Cache Fusion: Transferring blocks from one cluster Instance to Other cluster Instance (SGA to SGA)

     --- Suppose if I have 4 nodes RAC cluster, if the block is available on Node1 and the user session connect to Node2/Node3/Node4 needs the same block then directly block is transferred from Node1 not from disk (or datafile)

     --- This Cache Fusion will use the cluster Private network for transferring the blocks

--- Spilt Brain Syndrome: If the nodes are not able to communicate each other then each cluster nodes act like individual clusters, This situation is called as split brain syndrome

--- When cluster get into a spilt brain syndrome any of the nodes which are not communicating those will be evicted from the cluster based on the voting disk.

     --- Based on the simple majority rule whichever nodes writing more data into voting disks will be kept on cluster and whichever nodes written less data into voting disks will be evicted from cluster.

     --- Always its recommended to keep odd number of voting disks to meet the simple majority rule

--- Simple Majority Rule: Keeping Odd number of voting disks will helps us to resolve the split brain syndrome situation.

--- If we have odd number of voting disks it easy identify the which nodes written the more data into voting disks and which nodes are written less data into disks.

trunc{(n/2)+1} n=number of voting disks configured and n>=1 

Network Heartbeat: Node to node communication via private network

     --- Network ping

Disk Heartbeat: Node to Voting disk communication via private network

     --- Disk ping

Interesting Question 16...

I have a server where 10 standalone database are running on filesystem and multiple Oracle Homes.

Somebody deleted /etc/oratab, in that case How we can check which database belongs to which Oracle Home?

Answer 16…

--- Check listener config files and listener status find out which are databases are registered on that listener

--- Check the respective tnsnames.ora file under each Oracle Home $ORACLE_HOME/network/admin location or $TNS_ADMIN location

--- Check central inventory.xml under central inventory (/u01/app/oraInventory/ContextXML/inventory.xml)

     --- Check /etc/oraInst.loc to get the central inventory location

--- Check local inventory.xml under local inventory on each Oracle Home ($ORACLE_HOME/oraInventory/ContextXML/inventory.xml & comps.xml)

     --- Check /etc/oraInst.loc to get the central inventory location

--- We can find it from password file under respective Oracle Home

--- orapw<SID/DBNAME>

--- If there are any setting under .bash_profile or any login bash script we can refer

Regards,

Mallik

Live RAC Dataguard Implementation & Dataguard Internals

Live RAC Dataguard Implementation & Dataguard Internals

Join Zoom Meeting - Sat 22-May-2021 @ 7:30 PM

https://us02web.zoom.us/j/84744191482?pwd=R0dNQ3pRTVJtcWkyZVlHYUVYeDlvZz09

Agenda:
Dataguard Complete Understanding
Dataguard Parameters – Review & Understanding
RAC Dataguard Implementation – Demo
Q&A

Regards,
Mallik

Create Physical Standby Database using RMAN Backup Restore

In this article, we will see Physical Standby database creation and configuration using RMAN backup and restore. 

Step 1: Connect to the Primary database and check if recovery area

show parameter db_recovery

Step 2: Connect to RMAN and take backup

rman target /

backup database plus archivelog;

Step 3: Create standby control file from the primary database and create pfile from spfile.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/DEVDRDB.ctl';

CREATE PFILE FROM SPFILE;

Step 4: Change following parameter in pfile.

CHANGE FOLLOWING PARAMETER IN PFILE

*.db_unique_name='DEVDRDB'

*.fal_server='DEVDB'

*.log_archive_dest_2='SERVICE=DEVDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEVDB'

Step 5: Connect to Standby database server and create necessary directories.

mkdir -p /u01/app/oracle/oradata/DEVDRDB/datafile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/onlinelog

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog

Step 6: Transfer standby control file to standby database and rename it as defined in control_files initialization parameter.

Step 7: Transfer backup to Standby database server

Step 8: Transfer pfile to standby database

Step 9: Transfer password file to standby database.

Step 10: Connect to Standby database and create spfile from pfile.

 sqlplus / as sysdba 

create spfile from pfile;

Step 11: In standby database connect to RMAN and start the database in mount stage.

rman target /

startup mount

Step 12: Restore database using restore database command.

restore database;

Step 13: Connect to SQL prompt of standby database and create redo log files.

alter system set standby_file_management=manual;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo03.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo03.log') size 512m;

alter system set standby_file_management=AUTO;

Check Standby database synchronization with the Primary database

Step 14: Connect to the Primary database and check the role of the primary database.

select name,open_mode,database_role from v$database;

Step 15: Connect to Standby database and check the role of the database.

select name,open_mode,database_role from v$database;

Step 16: Check maximum archive log sequence from the primary.

select max(sequence#) from v$thread;

Step 17: Check maximum archive log sequence from standby database.

select max(sequence#) from v$thread;

Step 18: Start the MRP process at standby side.

alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

Step 19: Switch logfile at primary database

alter system switch logfile;

Step 20: Check again max archive log sequence at the standby database.

select max(sequence#) from v$thread;

 

Regards,

Mallik

Data Guard Complete Understanding

Data Guard Complete Understanding

Ø  Data Guard Basics

Ø  Types of Standby Databases.

What is Data Guard?

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

Ø  Data Guard provides a set of services that create, maintain, manage, and monitor one or more standby databases

Ø  Data Guard maintains these standby databases as transactionally consistent copies of the production database

Ø  Data Guard can switch any standby database to the production role

Without Data Guard:

With Data Guard:

Standby Database Types

Ø    Physical Standby Databases

Ø    Logical Standby Databases

Ø    Snapshot Standby Databases

 

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.

A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time. Thus, the logical standby database can be used concurrently for data protection and reporting operations.

A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database. A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Physical Standby

•       Standby is identical copy of primary database

•       Redo changes

–      transported from primary to standby

–      applied on standby (Redo Apply)

•       Can switch operations to standby

–      Planned (switchover / switchback)

–      Unplanned (failover)

Logical Standby

•       Redo copied from primary to standby

•       Changes converted into logical change records (LCR)

•       Logical change records applied on standby (SQL Apply)

•       Standby database can be opened for updates

–      Can modify propagated objects

–      Can create new indexes for propagated objects

•       May need larger system for logical standby

–      LCR apply can be less efficient than redo apply

–      Array updates on primary become single row updates on standby

A standby database is a transactionally consistent copy of an Oracle production database that is initially created from a backup copy of the primary database. Once the standby database is created and configured, Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system, where the redo data is applied to the standby database. A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database. A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database.

Redo Log Shipping

•       ARCH background process

–      Copies completed redo log files to standby

•       LGWR background process - modes are:

–      ASYNC - asynchronous

–      redo written by LGWR to local disk

–      read from disk by LNSn background process

–      SYNC - synchronous

           –   Redo written to standby by LGWR - modes are:

                 –      AFFIRM - wait for confirmation redo written to disk

                 –      NOAFFIRM - do not wait

ARCH Redo Transmission

LGWR Redo (ASYNC) Transmission

LGWR Redo (SYNC) Transmission

Regards,

Mallik