Logical Standby Database Creation

In the previous article, we have seen Preparation and consideration we need to see before creating Logical Standby Database.

We can use following steps to create Logical Standby Database.

Prerequisites : Physical Standby database already created.

Step 1: Check Physical Standby Database is created and is synchronized with Primary Database.

Primary Database :

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

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
MGR	  READ WRITE	       PRIMARY

Archive log sequence at Primary Database.

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

MAX(SEQUENCE#)
--------------
	   136

Standby Database :

SQL> select name,open_mode from v$database;
 
NAME	  OPEN_MODE            DATABASE_ROLE
--------- -------------------- -----------------
MGR	  READ ONLY WITH APPLY PHYSICAL STANDBY

Maximum Archive log sequence at Standby database

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

MAX(SEQUENCE#)
--------------
	   136

Step 2 : At Primary Database , Build a Log Miner Dictionary in redo data so that sql apply can properly interpret changes in the redo

SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed.

SQL>

When execute this, supplemental logging is automatically enabled , if not already enabled.

Step 3 : Cancel recovery at Physical Standby Database :

SQL> recover managed standby database cancel;
Media recovery complete.

Step 4 : Shut down standby database:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 5 : Start database in mount state.

SQL> startup mount
ORACLE instance started.

Total System Global Area 392495104 bytes
Fixed Size 2253584 bytes
Variable Size 176164080 bytes
Database Buffers 209715200 bytes
Redo Buffers 4362240 bytes
Database mounted.

Note : Database needs to be in mount state to create logical standby database.

Step 6 : Use the following command to create logical standby database.

SQL> recover to logical standby mgr;
Media recovery complete.

Step 7: the Open database with a resetlogs  option

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Step 8 : Start logical apply recovery

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Step 9 : Check database role and open mode in logical standby database :

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
MGR READ WRITE LOGICAL STANDBY std_mgr

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp