Step-by-Step  Creation of a Logical Standby Database on RAC 19c

Contains the same logical information as the production database, although the physical organization and structure of the data can be different.
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 logical standby database uses standby redo logs (SRLs) for redo received from the primary database, and also writes to online redo logs (ORLs) as it applies changes to the standby database.
Thus, logical standby databases often require additional ARCn processes to simultaneously archive SRLs and ORLs.
Additionally, because archiving of ORLs takes precedence over archiving of SRLs, a greater number of SRLs may be needed on a logical standby during periods of very high workload.

Prerequisite Conditions for Creating a Logical Standby Database

Before you create a logical standby database, you must first ensure the primary database is properly configured.
Perform the following tasks on the primary database to prepare for logical standby database creation:

A=> Determine Support for Data Types and Storage Attributes for Tables

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database.

Some schemas that ship with the Oracle database (for example, SYSTEM) contain objects that are implicitly maintained by SQL Apply.
However, if you put a user-defined table in SYSTEM, then it is not maintained even if it has columns of supported data types.
To discover which objects are not maintained by SQL Apply, you must run two queries. The first query is as follows:

SQL> SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

This returns all schemas that are considered to be internal. 
User tables placed in these schemas are not replicated on a logical standby database and do not show up in the DBA_LOGSTDBY_UNSUPPORTED view.
Tables in these schemas that are created by Oracle are maintained on a logical standby, if the feature implemented in the schema is supported in the context of logical standby.

The second query you must run is as follows. It returns tables that do not belong to internal schemas and are not maintained by SQL Apply because of unsupported data types:

SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;

B=> Ensure Table Rows in the Primary Database Can Be Uniquely Identified

Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database.
Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y';

Steps for Logical Standby Database Creation

Task 1: Create a Physical Standby Database

You create a logical standby database by first creating a physical standby database and then transitioning it to a logical standby database.
Please Click here for creating Physical Standby database

Task 2: Stop Redo Apply on the Physical Standby Database

Before proceeding Further ensure that redo is applied and there is NO GAP on Standby Site.

Check on Standby as below.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET      READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> @sync

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
     1              242            242      0
     2              177            177      0

However, before converting to a logical standby database, stop Redo Apply on the physical standby database.
Stopping Redo Apply is necessary to avoid applying changes past the redo that contains the LogMiner dictionary

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> SQL> exit

Task 3: Prepare the Primary Database to Support a Logical Standby Database

Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo.

As part of building the LogMiner dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns.
The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Task 4: Transition to a Logical Standby Database

As we are using RAC System for Standby database , We first need to Shut all node execpt one as below.

=> Set Cluster_database to FALSE as below

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
System altered.

=> Stop all node and start any 1 node in Mount mode.

[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start instance -d DBTAR -i DBTAR1 -o "MOUNT"
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> @name
NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  MOUNTED	       PHYSICAL STANDBY MAXIMUM PERFORMANCE


=> Covert standby database to Logical Standby Database as below.

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY DBLOG;

Database altered.

SQL> show parameter db_uniq

NAME				     TYPE	     VALUE
------------------------------------ ----------
db_unique_name			  string	 DBTAR

Here DBLOG is new Logical Name for current Physical Standby DB_NAME "DBTAR"

For db_name, specify a database name that is different from the primary database to identify the new logical standby database.
If you are using a server parameter file (spfile) at the time you issue this statement, 
then the database updates the file with appropriate information about the new logical standby database.

=> Change Cluster_database to TRUE and recycle Standby database and OPEN in MOUNT mode only.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> alter system set cluster_Database=TRUE scope=spfile;
System altered.

SQL> exit

[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR

[oracle@racsetn1 ~]$ srvctl start database -d DBTAR -o "MOUNT"

[oracle@racsetn1 ~]$ ps -eaf |grep pmon
grid      4415     1  0 14:36 ?        00:00:00 asm_pmon_+ASM1
oracle   21197     1  0 19:04 ?        00:00:00 ora_pmon_DBTAR1
oracle   21525  7273  0 19:04 pts/0    00:00:00 grep --color=auto pmon

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> show parameter db_name
NAME				     TYPE	 VALUE
------------------------------------ -----------
db_name 			     string	 DBLOG

SQL> show parameter db_uniq
NAME				     TYPE	 VALUE
------------------------------------ ----------
db_unique_name			     string	 DBTAR

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBLOG      MOUNTED           LOGICAL STANDBY    MAXIMUM PERFORMANCE
DBLOG      MOUNTED           LOGICAL STANDBY    MAXIMUM PERFORMANCE

SQL>

=> Create Directories to accomodate archives for ONLINE LOGFILE & archives of STANDBY LOGFILE as below.

[oracle@racsetn1 ~]$ su - grid
Password: 
Last login: Mon Jul 25 18:54:00 IST 2022
Last failed login: Mon Jul 25 19:06:50 IST 2022 on pts/0
There was 1 failed login attempt since the last successful login.
[grid@racsetn1 ~]$ 
[grid@racsetn1 ~]$ 
[grid@racsetn1 ~]$ 
[grid@racsetn1 ~]$ asmcmd
ASMCMD> ls 
DATA/
FRA/
OCR/
ASMCMD> cd FRA
ASMCMD> cd DBTAR
ASMCMD> ls 
ARCHIVELOG/
AUTOBACKUP/
CONTROLFILE/
ONLINELOG/
STANDBYLOGS/

ASMCMD> mkdir ONLINE_ARCH
ASMCMD> mkdir STANDBY_ARCH

ASMCMD> exit
[grid@racsetn1 ~]$ 

=> Change Log_archive_Dest parameters to accomodate archives for ONLINE LOGFILE & archives of STANDBY LOGFILE as below.

SQL> alter system set log_archive_dest_1='LOCATION=+FRA/DBTAR/ONLINE_ARCH VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBTAR';

System altered.
SQL> show parameter log_archive_dest_state_1

log_archive_dest_state_1	     string	 enable


SQL> ALTER SYSTEM SET log_archive_dest_3='LOCATION=+FRA/DBTAR/STANDBY_ARCH VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DBTAR';

System altered.
SQL> show parameter log_archive_dest_state_3

log_archive_dest_state_3	     string	 enable


Task 5: Open the Logical Standby Database

Use an ALTER DATABASE SQL statement to open the newly created logical standby.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

Task 6: Recycle Logical Standby Database as below

[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start database -d DBTAR -o "OPEN"

Task 7: Begin applying redo data to the logical standby database:

Issue the following statement to begin applying redo data to the logical standby database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.

SQL> exit

Check Alert log from Standby as below

LOGMINER: End   mining logfile for session 1 thread 1 sequence 254, +FRA/dbtar/standby_arch/1_254_1107118395.arc
2022-07-25T19:35:56.820863+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 255, +FRA/dbtar/standby_arch/1_255_1107118395.arc
2022-07-25T19:35:58.790100+05:30
LOGMINER: End   mining logfile for session 1 thread 1 sequence 255, +FRA/dbtar/standby_arch/1_255_1107118395.arc
2022-07-25T19:36:00.082025+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 256, +FRA/dbtar/standby_arch/1_256_1107118395.arc
2022-07-25T19:36:00.608503+05:30
LOGMINER: End   mining logfile for session 1 thread 1 sequence 256, +FRA/dbtar/standby_arch/1_256_1107118395.arc
2022-07-25T19:36:01.817964+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 257, +FRA/dbtar/standby_arch/1_257_1107118395.arc
2022-07-25T19:36:01.852578+05:30
LOGMINER: End   mining logfile for session 1 thread 2 sequence 192, +FRA/dbtar/standby_arch/2_192_1107118395.arc
2022-07-25T19:36:03.593066+05:30
LOGMINER: Begin mining logfile for session 1 thread 2 sequence 193, +FRA/dbtar/standby_arch/2_193_1107118395.arc
2022-07-25T19:36:04.104390+05:30
LOGMINER: End   mining logfile for session 1 thread 1 sequence 257, +FRA/dbtar/standby_arch/1_257_1107118395.arc
2022-07-25T19:36:05.353883+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 258, +FRA/dbtar/standby_arch/1_258_1107118395.arc
2022-07-25T19:36:10.234886+05:30
LOGMINER: End   mining logfile for session 1 thread 1 sequence 258, +FRA/dbtar/standby_arch/1_258_1107118395.arc
2022-07-25T19:36:12.136744+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 259, +FRA/dbtar/standby_arch/1_259_1107118395.arc
2022-07-25T19:36:12.570082+05:30
LOGMINER: End   mining logfile for session 1 thread 1 sequence 259, +FRA/dbtar/standby_arch/1_259_1107118395.arc
2022-07-25T19:36:14.361035+05:30