To create an Oracle logical standby, you need to first have a physical data guard.
Make sure to read this post first and then come back.
A logical standby 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.
1. Check both databases
First check all the tables are good to go.
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';
Make sure there is no gap between the primary and the standby database.
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Stop the apply process on the standby database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Prepare the primary database
Create this folder.
mkdir -p /u01/app/oracle/arch/standby
Modify these parameters on the primary database
show parameter LOG_ARCHIVE_DEST_1
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BCN' scope=both;
show parameter LOG_ARCHIVE_DEST_3
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=BCN' scope=both;
Now execute this on the primary database to build a dictionary in the redo data
EXECUTE DBMS_LOGSTDBY.BUILD;
3. Prepare the logical database
Convert the standby database to a logical database
ALTER DATABASE RECOVER TO LOGICAL STANDBY PARIS;
Create this folder on the standby server
mkdir -p /u01/app/oracle/arch/standby
Now stop the standby database and start it in mount mode
shutdown immediate
startup mount
Modify these parameters on the standby database
show parameter LOG_ARCHIVE_DEST_1
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PARIS' scope=both;
show parameter LOG_ARCHIVE_DEST_2
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=BCN ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BCN' SCOPE=BOTH ;
show parameter LOG_ARCHIVE_DEST_3
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PARIS' scope=both;
Open the standby database with resetlogs.
ALTER DATABASE OPEN RESETLOGS;
Start the apply process on the standby database.
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
You now have a working logical standby database.
4. Testing
If you want to test your new logical standby, you can create a new user and a new table in the primary database and then insert some rows and the table and the new rows should be replicated to the logical standby.
4.1 On the primary
I have a PDB called pdb1, so I will work with that PDB.
SQL> alter session set container=pdb1;
Session altered.
SQL> create user fernando identified by fernando123;
User created.
SQL> grant dba to fernando;
Grant succeeded.
SQL> create table fernando.table1 (col1 number);
Table created.
SQL> insert into fernando.table1 values (1);
1 row created.
SQL> insert into fernando.table1 values (2);
1 row created.
SQL> commit;
Commit complete.
4.2 On the standby
SQL> alter session set container=pdb1;
Session altered.
SQL> select * from fernando.table1;
COL1
----------
2
1