Steps to convert Physical Standby database to Snapshot Standby database in oracle 12c


What is snapshot standby and its Benefits?

A snapshot standby database is a fully updatable standby database that is 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. 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.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected, however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.

Steps to perform Snapshot database conversion.

Step-1: Validate the standby database health.
Step-2: Convert from physical to snapshot standby.
Step-3: Handover the database to the Application team to test the functionality.
Step-4: Convert from snapshot to physical standby.
Step-5: Post converts to physical standby steps.


Step-1: Validate the standby lag status if any 

====last log applied and received along with log time on Standby database===
 
SELECT 'Last Applied  : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM gv$archived_log
 WHERE sequence# = (SELECT MAX (sequence#)
                      FROM v$archived_log
                     WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM gv$archived_log
 WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);


===== Last sequence # received and applied on the standby database =====
 
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  FROM (  SELECT thread# thrd, MAX (sequence#) almax
            FROM gv$archived_log
           WHERE resetlogs_change# = (  SELECT resetlogs_change# FROM v$database)
        GROUP BY thread#) al,
       (  SELECT thread# thrd, MAX (sequence#) lhmax
            FROM gv$log_history
          WHERE first_time = (  SELECT MAX (first_time) FROM v$log_history)
        GROUP BY thread#) lh
 WHERE al.thrd = lh.thrd;

=== Validate the standby instances status =====

$ srvctl status database -d STND12 -v 

Step-2: Convert from physical to snapshot standby

Shutdown the physical standby and startup in the mount to convert it to snapshot standby

$srvctl stop database -d STND12
$srvctl start database -d STND12 -o mount 

== Validating the status of the standby database ==

srvctl status database -d STND12 -v

== Run the command to convert the physical to snapshot standby ==

Sqlplus / as sysdba
SYS@STND121 > alter database convert to snapshot standby;
Database altered.

SYS@STND121 > alter database open;
Database altered.

SYS@STND121 > select database_role,open_mode from v$database;

DATABASE_ROLE            OPEN_MODE
----------------                      --------------------
SNAPSHOT STANDBY    READ WRITE

Note: Make sure you should OPEN the other all instances in READ-WRITE mode either by shutting down the standby database using SRVCTL

You could additionally validate the restore point that oracle has automatically created during the snapshot standby database conversion.  

SYS@STND121 > select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;


Step-3: Handover the database to Application team to test the functionality 

Step-4: Convert from snapshot to physical standby

== Shutdown the snapshot standby and startup in the mount to convert it to physical standby==

 $srvctl stop database -d STND12
 $srvctl start instance -i STND121 -d STND12 -o mount 

Note: Please start only instance-1 to avoid the error “ORA-38777: database must not be started in any other instance”

Validating the status of the snapshot standby database to make sure only one instance is currently running

 $ srvctl status database -d STND12 

== Run the command to convert Snapshot to Physical standby database ==

SYS@STND121 > alter database convert to physical standby;
Database altered.

SYS@STND121 > select database_role from v$database;
DATABASE_ROLE
-------------------------
PHYSICAL STANDBY

Stop and startup the physical standby database into Active standby database role (ReadOnly)

 $srvctl stop database -d STND12
 $srvctl start database -d STND12

SYS@STND121 > alter database recover managed standby database disconnect nodelay;
Database altered.

Validate logs are applying on the standby database

====last log applied and received along with log time on Standby database===
 

SELECT 'Last Applied  : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM gv$archived_log
 WHERE sequence# = (SELECT MAX (sequence#)
                      FROM v$archived_log
                     WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM gv$archived_log
 WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);


==== Last sequence # received and applied on the standby database  ===
 
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  FROM (  SELECT thread# thrd, MAX (sequence#) almax
            FROM gv$archived_log
           WHERE resetlogs_change# = (  SELECT resetlogs_change# FROM v$database)
        GROUP BY thread#) al,
       (  SELECT thread# thrd, MAX (sequence#) lhmax
            FROM gv$log_history
          WHERE first_time = (  SELECT MAX (first_time) FROM v$log_history)
        GROUP BY thread#) lh
 WHERE al.thrd = lh.thrd;

Step-5: Post convert to physical standby steps

=== Disable Flashback on the standby database === 

SYS@STND121 > alter database flashback off;
Database altered.

== Validate the database alert log files on both the primary and standby databases if any errors ==


Additional consideration before converting a Physical standby database to the Snapshot database.

1. Consider disabling archive log deletion jobs on Primary and Standby
2. Consider disabling backup jobs running on the standby database 
3. disable the alerting on the standby database to avoid any incidents 

Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more