How To Convert Physical Standby Database to Logical Standby Database

Logical Standby Database does not support replication of all data types, tables, commands and PL / SQL Packages. Therefore, before converting a Physical Standby database to Logical Standby, a series of checks must be made.

Pre Checks Before Converting Physical Standby Database to Logical Standby Database

Since the Redo Flow is from the Primary side to the Standby side, the controls will be made on the Primary side.

PreCheck1:

Internal Schemas that will not be replicated are queried. If the tables in these Schemes are created by Oracle, the same information is reflected to the Logical Standby side, but if it is created by the User, the data in those tables is not replicated to the Logical Standby side.

The tables of these users can also be seen as follows.

PreCheck2:

Tables that are not supported by Log Apply Services are queried.

PreCheck3:

Tables and columns whose data types are not supported are queried. DML operations performed in these tables are not replicated to the Logical Standby side and no error messages are returned.

PreCheck4:

Commands that will not be executed on the Logical Standby when run on the primary side must be known before the Convert operation takes place. These commands need to be run separately in Logical Standby.

  • ALTER DATABASE
  • ALTER MATERIALIZED VIEW
  • ALTER MATERIALIZED VIEW LOG
  • ALTER SESSION
  • ALTER SYSTEM
  • EXPLAIN
  • LOCK TABLE
  • SET CONSTRAINTS
  • SET ROLE
  • SET TRANSACTION
  • CREATE CONTROL FILE
  • CREATE DATABASE
  • CREATE DATABASE LINK
  • CREATE PFILE FROM SPFILE
  • CREATE MATERIALIZED VIEW
  • CREATE MATERIALIZED VIEW LOG
  • CREATE SCHEMA AUTHORIZATION
  • CREATE SPFILE FROM PFILE
  • DROP DATABASE LINK
  • DROP MATERIALIZED VIEW
  • DROP MATERIALIZED VIEW LOG

PreCheck5

The following PL/SQL packages that can change System Metadata or create a record for Archive Logs are also not supported in Logical Standby.

  • DBMS_JAVA
  • DBMS_REGISTRY
  • DBMS_ALERT
  • DBMS_SPACE_ADMIN
  • DBMS_REFRESH
  • DBMS_REDEFINITION
  • DBMS_AQ

PreCheck6:

Since rowids will not be the same on primary and logical sides, a healthy replication is done with primary keys or unique indexes. For this reason, columns without Primary ID and Unique index should also be known and precautions should be taken.

In the BAD_COLUMN column;

If the values in the columns other than “Y: LOB” are the same for 2 rows, the transfer to the Logical side cannot be performed properly and SQL Apply stops.

Although there is no obstacle for “N: SQL Apply” to work, it is recommended by Oracle to put Primary Key or Unique Index in these tables in order to make replication efficiently.

Also, adding “DISABLE RELY Constraint” to these tables can be considered. With the column information I will give when defining RELY Constraint, I want to say that there is a unique here to Oracle, trust me. Thus, Oracle does not make a full table scan and makes the necessary changes in the first record it finds.

How To Add RELY DISABLE CONSTRAINT

a)Tables that are not unique are queried.

b) Add RELY DISABLE constraint.

c) Query non-unique tables again.

Convert Physical Standby Database to Logical Standby Database

Logical Standby Database cannot be created directly. Physical Standby Database must be installed and then this Database must be converted to Logical.

A Physical Standby Database is converted to Logical as follows.

Step1:

Install Physical Standby Database. After installation, tnsnames.ora, oracle and grid bash_profile, listener.ora, pfile files of Physical Standby Database are as follows.

Step2:

On the primary side, I am also editing the LOG_ARCHIVE_DEST_3 parameter to send the archives to Logical, and the LOG_ARCHIVE_CONFIG parameter to include Logical in my DATA GUARD Environment.

Step3:

Physical Standby Database will be installed, whose DB_NAME is PRIMARY and DB_UNIQUE_NAME is LOGICAL.

Step4:

After installing Physical Standby Database with LOGICAL DB_UNIQUE_NAME, database, recovery and protection mode are queried.

Step5:

Check whether redo transport can be done without any problem by doing log switch operation from primary side.

Step6:

On the Logical Standby side, the Recovery is stopped to Convert to Logical.

Step7:

Enable LogMiner on the Primary side since the logic of Logical Standby is SQL-Apply and it does this by opening the Redos with LogMiner.

Step8:

Since we are working in RAC structure, close all other instances except one of the instances, set the Cluster Database as FALSE.

Then ALTER to Recovery Logical and finally set the Cluster Database to TRUE.

Step9:

I leave LOG_ARCHIVE _DEST_2 empty because I don’t want to use Logical Standby when Switchover and Failover occur.

Step10:

Open Logical Standby Database in RESETLOGS and start Recovery. Thus, we converted Physical Standby Database to Logical Standby.

Step11:

Query the OPEN mode, ROLE and RECOVERY mode of the database.

LOGICAL STANDBY DATABASE CHECK QUERIES

Check Redos are transferred from Primary Database to Logical Standby Database?

Learn Archive Redo Log numbers in Primary Database.

Query the existence and apply states of these sequences in Logical Standby.

Perform log switch operation in Primary.

Check if it’s coming to Logical.

The APPLIED column here is checked.

YES: The relevant archive has been applied to Logical Standby and this archive is no longer needed.

CURRENT: The relevant archive is currently being processed to Logical Standby.

NO: It means that the related archive has not been started to be processed to Logical Standby yet.

FETCHING: Indicates that a corruption is encountered while reading redo records from the relevant archive and tries to get the relevant archive from Primary using automatic GAP resolution.

CORRUPT: It indicates that a corruption is encountered while reading the redo records from the relevant archive, and that the archive received from Primary does not solve the problem, and manual intervention is required.

Query Statistics,current_state and Status Related SQL-Apply

We can see information such as statistics, current_state and status related to SQL-Apply from V $ LOGSTDBY_STATS view.

If the value of the Coordinator state is IDLE, it means that everything is fine and that it is Primary and SYNC.

Query the status of SQL-Apply processes from V $ LOGSTDBY_PROCESS view

In the STATUS column;

ORA-16116: no work available” Indicates that SQL Apply processes are IDLE.

ORA-16242: Processing log file (thread # 1, sequence # 355)” Indicates that READER Process is reading logs from logfile.

We can see the progress of SQL Apply services in V$LOGSTDBY_PROGRESS view.

APPLIED_TIME: It is the time of the last applied transaction.

LATEST_TIME: It is the time of the highest SCN encountered in the Logical Standby Database.

MINING_TIME: It is the time of the SCN of the last redo record processed by the BUILDER process.

We can see a summary of the current status of SQL Apply from the V$LOGSTDBY_STATE view.

Onur ARDAHANLI
Author: Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *