Physical Standby Protection mode

 Types of protection mode in Oracle physical standby

 

Oracle Data Guard provides different protection modes that determine the level of data protection and availability provided by the standby database. The protection mode defines how transactions are committed and synchronized between the primary and standby databases. The three primary protection modes in Oracle Data Guard are:

1. Maximum Performance (ASYNC): In Maximum Performance mode, the primary database commits transactions as soon as possible without waiting for the standby database to acknowledge the redo data. This mode offers the highest level of performance for the primary database but provides the least level of data protection. There is a potential for data loss if a primary database failure occurs before the redo data is transmitted to the standby database.

2. Maximum Availability (SYNC): In Maximum Availability mode, the primary database waits for at least one standby database to acknowledge the redo data before committing the transaction. This ensures that data is protected from the loss of a single database in the event of a failure. However, it may introduce some additional latency and potentially impact the primary database performance due to the synchronous network round-trip.

3. Maximum Protection (SYNC): In Maximum Protection mode, the primary database waits for all standby databases to acknowledge the redo data before committing the transaction. This provides the highest level of data protection but can introduce additional latency and impact the primary database performance due to the synchronous network round-trip. It ensures zero data loss in case of a single or multiple standby database failures.

In addition to these primary protection modes, Oracle Data Guard also provides a few other advanced protection modes that offer more granular control over data protection. These advanced modes include:

• Far Sync Instance: A Far Sync Instance is an intermediary instance that acts as a buffer between the primary database and remote standby databases. It provides zero data loss protection in cases where the primary and standby databases are geographically distant.

• Fast-Start Failover (FSFO): Fast-Start Failover is a feature that enables automatic failover to a standby database in case of a primary database failure. It reduces downtime and minimizes the impact on the application.

• Cascading Standby Databases: Cascading standby databases allow the creation of multiple levels of standby databases. Redo data is cascaded from the primary database to a remote standby database, and then from that standby database to another standby database. This can be used to protect against disasters that affect an entire data center or region.

Each protection mode has its own trade-offs in terms of performance, data protection, and availability. It is important to carefully evaluate the requirements and constraints of the environment to choose the appropriate protection mode that aligns with the organization's objectives and data protection needs.

 

Oracle Physical Standby

 Oracle Physical Standby

 

In Oracle, a physical standby database is a type of standby database that maintains an exact copy of the primary database by continuously applying redo data from the primary database. It serves as a failover solution and provides high availability and data protection.

Here are key points to understand about a physical standby database in Oracle:

1. Data Synchronization: The physical standby database stays synchronized with the primary database by receiving and applying redo data, which contains all the changes made to the primary database. Redo data is shipped from the primary database and applied to the physical standby database using redo apply technology.

2. Data Protection: The physical standby database provides data protection by maintaining a synchronized copy of the primary database. In the event of a primary database failure, the physical standby database can be quickly activated to take over as the new primary database, minimizing downtime and data loss.

3. Continuous Redo Apply: The physical standby database continuously applies redo data received from the primary database, keeping the standby database up-to-date. Redo apply applies the changes to the standby database's data files, ensuring they mirror the primary database's data.

4. Read-Only Access: In addition to serving as a failover solution, the physical standby database can also be used for read-only reporting or offloading backup activities. This is possible because the standby database is an exact replica of the primary database.

5. Managed Recovery Process (MRP): The Managed Recovery Process (MRP) is the background process responsible for applying redo data to the physical standby database. It continuously runs on the standby database and applies redo data as it is received, keeping the standby database synchronized.

6. Data Guard: Oracle Data Guard is the primary technology used to configure and manage physical standby databases. It provides various features and options to ensure data synchronization, automatic failover, and management of the standby database.

7. Switchover and Failover: Switchover is the planned transition from the primary database to the standby database, where roles are reversed. Failover is the unplanned transition that occurs when the primary database becomes unavailable, and the standby database takes over as the new primary database.

By implementing a physical standby database, organizations can achieve high availability and data protection, ensuring that their critical Oracle databases remain accessible and their data remains safe in the event of primary database failures.

 

Oracle Datagaurd Background Process

 Oracle Datagaurd Background Process

 

In Oracle Data Guard, background processes play a vital role in managing and maintaining the standby database and ensuring data protection and availability. These processes work in conjunction with primary and standby databases to facilitate data replication, synchronization, and failover operations. Here are some important background processes in Oracle Data Guard:

1. Log Writer (LGWR): The LGWR process is responsible for writing redo log records to the online redo log files on the primary database. In a Data Guard configuration, LGWR also transmits redo data to the standby database(s) for applying the changes.

2. Archiver (ARCn): The archiver process (ARCn) copies the archived redo log files from the primary database to the standby database(s). These archived logs are essential for maintaining the standby database in sync with the primary database.

3. Media Recovery (MRP) Process: The Media Recovery Process (MRP) is responsible for applying redo data received from the primary database to the standby database(s). It continuously applies the archived redo logs to bring the standby database up to date with the primary database.

4. Data Guard Broker Processes: The Data Guard Broker is a management framework for configuring, monitoring, and controlling Data Guard configurations. It includes various background processes, such as DGMGRL (Data Guard Manager Command-Line Interface) and DMON (Data Guard Broker Monitor). These processes handle the configuration and management tasks of the Data Guard setup.

5. Log Apply Services (LNSn and RFS): Log Apply Services are responsible for transmitting and applying redo data from the primary database to the standby database(s). The Log Network Server (LNSn) on the primary database transmits redo data to the Remote File Server (RFS) process on the standby database, which applies the redo data.

6. RFS (Remote File Server) Process: The RFS process receives the archived redo logs and standby redo logs from the primary database and applies them to the standby database. It is responsible for managing the transmission, receipt, and application of redo data.

7. Data Guard Net Services (LNSn and NSSn): Data Guard Net Services processes handle the network communication between the primary and standby databases. The Log Network Server (LNSn) on the primary database and the Net Services Server (NSSn) on the standby database facilitate the efficient transmission of redo data and other communication required for Data Guard operations.

These are some of the key background processes involved in Oracle Data Guard. They work together to ensure data replication, synchronization, and failover capabilities, providing high availability and data protection for Oracle databases.

 

Fixing standby GAP issue with Primary using SCN backup

 Step 1:  The Current SCN of the Physical Standby Database.

Standby Database:

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

XYZDB     MOUNTED

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE  DATABASE_ROLE

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

XYZDB     MOUNTED    PHYSICAL STANDBY

 

SQL> select db_unique_name, to_char(current_scn, '999999999999999999') as Standby_Controlfile_SCN from v$database;

 

DB_UNIQUE_NAME                 STANDBY_CONTROLFILE

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

XYZDB                            10861224114929

Note down the CURRENT_SCN value of the standby database (10861224114929) to proceed further.

 

Last Sequence received:

SQL> select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

 

THREAD#    Last Standby Seq Received

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

1             266428

Last sequence applied :

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and applied='YES'

group by thread# order by 1; 

 

THREAD#    Last Standby Seq Applied

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

1             255271

Step 2:  Cancel the Managed Recovery Process on the Standby database.

Standby Database:

SQL> select PROCESS,STATUS,CLIENT_PROCESS from v$managed_standby;

PROCESS   STATUS       CLIENT_P

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

ARCH      CONNECTED    ARCH

ARCH      CONNECTED    ARCH

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

Step 3:

Primary database  :

Taken  the incremental SCN backup from the SCN that is currently recorded on the standby database.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE  DATABASE_ROLE

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

XYZDB     READ WRITE PRIMARY

SQL> select db_unique_name, to_char(current_scn, '999999999999999999') as Primary_Controlfile_SCN from v$database;

 

DB_UNIQUE_NAME                 PRIMARY_CONTROLFILE

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

XYZDB                            10878875895876

RMAN Backup script:

vi rman_standby_10NOV2012.sh

export ORACLE_SID=XYZDB

rman target sys/******* <<! >rman_incremental_10NOV2012.log

run

{

ALLOCATE CHANNEL C1 DEVICE TYPE DISK ;

ALLOCATE CHANNEL C2 DEVICE TYPE DISK ;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK ;

backup incremental from scn 10861224114929 database format ‘/u01/apps/oracle/admin/XYZDB/bkup/RmanIncremental_%U_%d_%T’;

backup current controlfile for standby format '/u01/apps/oracle/admin/XYZDB/bkup/control_file_%T';

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

}

exit

!

$ nohup sh rman_standby_10NOV2012.sh &

Step 4: Transfer the backups from the Primary  to the Standby .

Primary Database

scp RmanIncremental_06npu8a1_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp RmanIncremental_05npu8a1_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp RmanIncremental_04npu8a0_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp control_file_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

 

Step 5:

 Shutdown the physical standby database, start it in nomount stage and restore the standby control file and RMAN Incremental backup that we had taken from the primary database.

Standby Database:

$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 10 07:28:38 2012

 

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     2142240 bytes

Variable Size               1201623008 bytes

Database Buffers            1811939328 bytes

Redo Buffers                 138412032 bytes

 

RMAN> restore standby controlfile from '/global/CEXOE/XYZDB/RMAN_CENXCT_BK/control_file_20121110';

Starting restore at 10-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=702 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/global/CEXOE/XYZDB/oradata/group1/XYZDB_control01.ctl

output filename=/global/CEXOE/XYZDB/oradata/group2/XYZDB_control02.ctl

output filename=/global/CEXOE/XYZDB/oradata/group3/XYZDB_control03.ctl

Finished restore at 10-NOV-12

Backup restoration

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> catalog start with '/global/CEXOE/XYZDB/RMAN_CENXCT_BK';

searching for all files that match the pattern /global/CEXOE/XYZDB/RMAN_CENXCT_BK

List of Files Unknown to the Database

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

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_06npu8a1_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_05npu8a1_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_04npu8a0_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/control_file_20121110

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

cataloging files...

cataloging done

Step 6:

 Recover the standby database with the cataloged incremental backup pieces.

RMAN> recover database noredo;

Starting recover at 10-NOV-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=701 devtype=DISK

channel ORA_DISK_1: starting incremental datafile backupset restore

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

destination for restore of datafile 00004: /global/CEXOE/XYZDB/oradata/group1/XYZDB_tools01a.dbs

channel ORA_DISK_1: restored backup piece 1

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_06npu8a1_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

channel ORA_DISK_1: starting incremental datafile backupset restore

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_05npu8a1_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

channel ORA_DISK_1: starting incremental datafile backupset restore

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

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_04npu8a0_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

Finished recover at 10-NOV-12

Step 7:

 

Shutdown and startup mount standby database.

Standby Database:

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount

Note: List the files that have had nologging changes applied on the standby database

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

no rows selected

SQL> !date

Saturday, November 10, 2012  7:41:28 AM EST

Step 8:

Standby Database:

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

268176

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/apps/oracle/admin/XYZDB/arch

Oldest online log sequence     268173

Next log sequence to archive   268177

Current log sequence           268177

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enable the  Manager Recover process

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

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

Step9:

 Primary Database:

SQL>  select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

268207

Sql> alter system set log_archive_dest_2_state=enable scope=both;

 

Standby side

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

 268204