Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | [Primary-1] SQL> select owner from dba_logstdby_skip where statement_opt='INTERNAL SCHEMA' order by owner; OWNER ------------------------------ ANONYMOUS APEX_030200 APPQOSSYS BI CTXSYS DBSNMP DIP EXFSYS MDSYS MGMT_VIEW OLAPSYS OWNER ------------------------------ ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN OWBSYS SI_INFORMTN_SCHEMA SYS SYSMAN SYSTEM WMSYS OWNER ------------------------------ XDB XS$NULL 24 rows selected. |
The tables of these users can also be seen as follows.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> select owner, table_name from dba_tables where owner in (select owner from dba_logstdby_skip where statement_opt='INTERNAL SCHEMA'); ... OWNER TABLE_NAME ------------------------------ ------------------------------ DBSNMP MGMT_LATEST DBSNMP MGMT_CAPTURE_SQL DBSNMP MGMT_CAPTURE DBSNMP MGMT_BASELINE_SQL 2809 rows selected. |
PreCheck2:
Tables that are not supported by Log Apply Services are queried.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [Primary-1] SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME; OWNER TABLE_NAME ------------------------------ ------------------------------ IX AQ$_ORDERS_QUEUETABLE_G IX AQ$_ORDERS_QUEUETABLE_H IX AQ$_ORDERS_QUEUETABLE_I IX AQ$_ORDERS_QUEUETABLE_L IX AQ$_ORDERS_QUEUETABLE_S IX AQ$_ORDERS_QUEUETABLE_T IX AQ$_STREAMS_QUEUE_TABLE_C IX AQ$_STREAMS_QUEUE_TABLE_G IX AQ$_STREAMS_QUEUE_TABLE_H IX AQ$_STREAMS_QUEUE_TABLE_I IX AQ$_STREAMS_QUEUE_TABLE_L OWNER TABLE_NAME ------------------------------ ------------------------------ IX AQ$_STREAMS_QUEUE_TABLE_S IX AQ$_STREAMS_QUEUE_TABLE_T IX ORDERS_QUEUETABLE IX STREAMS_QUEUE_TABLE OE CATEGORIES_TAB OE CUSTOMERS OE PURCHASEORDER OE WAREHOUSES PM ONLINE_MEDIA PM PRINT_MEDIA SH DIMENSION_EXCEPTIONS 22 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Primary-1] SQL> set linesize 500 SQL> column DATA_TYPE format a20 SQL> SELECT table_name, column_name, data_type FROM dba_logstdby_unsupported ORDER BY OWNER,TABLE_NAME; ... TABLE_NAME COLUMN_NAME DATA_TYPE ------------------------------ ------------------------------ -------------------- ONLINE_MEDIA PRODUCT_VIDEO OBJECT ONLINE_MEDIA PRODUCT_PHOTO OBJECT ONLINE_MEDIA PRODUCT_TESTIMONIALS OBJECT ONLINE_MEDIA PRODUCT_THUMBNAIL OBJECT PRINT_MEDIA AD_HEADER OBJECT PRINT_MEDIA AD_GRAPHIC BFILE PRINT_MEDIA AD_TEXTDOCS_NTAB NESTED TABLE DIMENSION_EXCEPTIONS BAD_ROWID ROWID 190 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Primary-1] SQL> column owner format a10 SQL> column bad_column format a11 SQL> SELECT * FROM dba_logstdby_not_unique; OWNER TABLE_NAME BAD_COLUMN ---------- ------------------------------ ----------- SCOTT BONUS N SCOTT SALGRADE N SH COSTS N SH SALES N SH SUPPLEMENTARY_DEMOGRAPHICS N TEST DBA_TABLES N TEST EMPLOYEES_YEDEK N TEST COUNTRIES_YEDEK N TEST REGIONS_YEDEK N 9 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | [Primary-1] SQL> select * from dba_logstdby_not_unique; OWNER TABLE_NAME B ------------------------------ ------------------------------ - SCOTT BONUS N SCOTT SALGRADE N SH COSTS N SH SALES N SH SUPPLEMENTARY_DEMOGRAPHICS N SH TOAD_PLAN_TABLE Y INSPIRIT STANDBY_STATS N INSPIRIT LOG_SWITCH N IPTVMWC COD_HISTORY N IPTVMWC ACCOUNT_TO_FAVOURITE_COD N IPTVMWC PURCHASED_COD_HISTORY N OWNER TABLE_NAME B ------------------------------ ------------------------------ - IPTVMWC COD_TO_CODGROUP N IPTVMWC CHANNEL_GENRE_DEFINITIONS N IPTVMWC COD_STAT N IPTVMWC EPG_PROVIDER_CHANNEL N IPTVMWC LATEST_FM_VERSION N IPTVMWC PPV_PROGRAM_CATEGORY_RELATION N IPTVMWC TSTV_PROGRAM_CATEGORY_RELATION N TEST JOB_HISTORY_YEDEK N TEST COUNTRIES_YEDEK N TEST EMPLOYEES_YEDEK N TEST DEPARTMENTS_YEDEK N 22 rows selected. |
b) Add RELY DISABLE constraint.
1 2 3 | [Primary-1] SQL> alter table test.employees_yedek add primary key (employee_id, last_name) rely disable; Table altered. |
c) Query non-unique tables again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [Primary-1] SQL> select * from dba_logstdby_not_unique; OWNER TABLE_NAME B ------------------------------ ------------------------------ - SCOTT BONUS N SCOTT SALGRADE N SH COSTS N SH SALES N SH SUPPLEMENTARY_DEMOGRAPHICS N SH TOAD_PLAN_TABLE Y INSPIRIT STANDBY_STATS N INSPIRIT LOG_SWITCH N IPTVMWC COD_HISTORY N IPTVMWC ACCOUNT_TO_FAVOURITE_COD N IPTVMWC PURCHASED_COD_HISTORY N OWNER TABLE_NAME B ------------------------------ ------------------------------ - IPTVMWC COD_TO_CODGROUP N IPTVMWC CHANNEL_GENRE_DEFINITIONS N IPTVMWC COD_STAT N IPTVMWC EPG_PROVIDER_CHANNEL N IPTVMWC LATEST_FM_VERSION N IPTVMWC PPV_PROGRAM_CATEGORY_RELATION N IPTVMWC TSTV_PROGRAM_CATEGORY_RELATION N TEST JOB_HISTORY_YEDEK N TEST COUNTRIES_YEDEK N TEST DEPARTMENTS_YEDEK N 21 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | #ORACLE BASH_PROFILE [Logical-1] [oracle@logical1 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=logical; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE GRID_HOME=/u01/11.2.0/grid; export GRID_HOME DB_HOME=$ORACLE_BASE/product/11.2.0/db_1; export DB_HOME ORACLE_HOME=$DB_HOME; export ORACLE_HOME ORACLE_SID=primary1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 65536 ulimit -n 65536 alias log='cd /u01/app/oracle/diag/rdbms/logical/logical1/trace; pwd' alias oh='cd $ORACLE_HOME; pwd' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [Logical-2] [oracle@logical2 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=logical; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE GRID_HOME=/u01/11.2.0/grid; export GRID_HOME DB_HOME=$ORACLE_BASE/product/11.2.0/db_1; export DB_HOME ORACLE_HOME=$DB_HOME; export ORACLE_HOME ORACLE_SID=primary2; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 65536 ulimit -n 65536 alias log='cd /u01/app/oracle/diag/rdbms/logical/logical2/trace; pwd' alias oh='cd $ORACLE_HOME; pwd' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | #GRID BASH_PROFILE [Logical-1] [grid@logical1 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=logical; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/11.2.0/grid; export ORACLE_HOME GRID_HOME=/u01/11.2.0/grid; export GRID_HOME ORACLE_SID=+ASM1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 65536 ulimit -n 65536 alias log='cd /u01/11.2.0/grid/log/logical01; pwd' alias oh='cd $ORACLE_HOME; pwd' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [Logical-2] [grid@logical2 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=logical; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/11.2.0/grid; export ORACLE_HOME GRID_HOME=/u01/11.2.0/grid; export GRID_HOME ORACLE_SID=+ASM2; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 65536 ulimit -n 65536 alias log='cd /u01/11.2.0/grid/log/logical02; pwd' alias oh='cd $ORACLE_HOME; pwd' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | #LOGICAL STANDBY DATABASE PFILE [Logical -1] [oracle@logical1 dbs]$ cat initlogical.ora *.audit_file_dest='/u01/app/oracle/admin/logical/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA/logical/controlfile/control01.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_create_online_log_dest_1='+DATA' *.db_create_online_log_dest_2='+FRA' *.db_domain='' *.db_file_name_convert='logical','primary' *.db_name='primary' *.db_unique_name='logical' *.db_recovery_file_dest='+FRA' *.db_recovery_file_dest_size=42949672960 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)' *.fal_server='PRIMARY' primary1.instance_number=1 primary2.instance_number=2 *.log_archive_config='DG_CONFIG=(primary,standby,logical)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical' *.log_archive_dest_2='SERVICE=primary SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=8 *.log_file_name_convert='primary','logical' *.open_cursors=300 *.pga_aggregate_target=2167406592 *.processes=150 *.remote_listener='logical-scan.mycompanylab.local:1521' *.remote_login_passwordfile='exclusive' *.sga_target=6502219776 *.standby_file_management='AUTO' primary2.thread=2 primary1.thread=1 primary2.undo_tablespace='UNDOTBS2' primary1.undo_tablespace='UNDOTBS1' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | # TNSNAMES.ORA [Primary 1-2 & Logical 1-2] [oracle@logical1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary-scan.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) PRMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mvpdb-scan.mycompanylab.local)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prmary) ) ) STANDBY2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby2-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) STANDBY1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby1-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) PRIMARY2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary2-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) LOGICAL2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = logical2-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logical) ) ) PRIMARY1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary1-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) LOGICAL1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = logical1-vip.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logical) ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby-scan.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) ) LOGICAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = logical-scan.mycompanylab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logical) ) ) |
1 2 3 4 5 6 7 8 9 10 11 12 13 | # LISTENER.ORA [Logical-1-2] [grid@logical1 admin]$ cat listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent |
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.
1 2 3 4 5 6 7 8 | [Primary-1] SQL> alter system set log_archive_dest_3='SERVICE=logical SYNC REOPEN=15 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=logical' scope=both sid='*'; System altered. SQL> alter system set log_archive_config='DG_CONFIG=(primary,standby,logical)' sid='*' scope=both; System altered. |
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.
1 2 3 4 5 6 | [Logical-1] SQL> select DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE from v$archive_dest_status; DATABASE_MODE RECOVERY_MODE PROTECTION_MODE --------------- ----------------------- -------------------- MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE |
Step5:
Check whether redo transport can be done without any problem by doing log switch operation from primary side.
1 2 3 4 | [Primary-1-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 7 | [Primary-1-2&Standby-1-2&Logical-1-2] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 321 1 184 2 |
Step6:
On the Logical Standby side, the Recovery is stopped to Convert to Logical.
1 2 3 4 | [Logical-1] SQL> alter database recover managed standby database cancel; Database altered. |
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.
1 2 3 4 | [Primary-1-2] SQL> execute dbms_logstdby.build; PL/SQL procedure successfully completed. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | [Logical-1] SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; System altered. SQL> shutdown abort; ORACLE instance shut down. SQL> STARTUP MOUNT EXCLUSIVE; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY LOGICAL; Database altered. SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
1 2 3 4 5 6 7 8 9 10 | [Logical-2] SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
Step9:
I leave LOG_ARCHIVE _DEST_2 empty because I don’t want to use Logical Standby when Switchover and Failover occur.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Logical-1] SQL> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_ DEST VALID_FOR=(ALL_LOGFILES,A LL_ROLES) DB_UNIQUE_NAME=logic al log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string SERVICE=primary SYNC VALID_FOR =(ONLINE_LOGFILES,PRIMARY_ROLE ) DB_UNIQUE_NAME=primary |
1 2 3 4 5 6 7 8 9 10 | [Logical-1] SQL> alter system set log_archive_dest_2='' scope=both sid='*'; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string |
Step10:
Open Logical Standby Database in RESETLOGS and start Recovery. Thus, we converted Physical Standby Database to Logical Standby.
1 2 3 4 5 6 7 8 | [Logical-1] SQL> alter database open resetlogs; Database altered. SQL> alter database start logical standby apply immediate; Database altered. |
Step11:
Query the OPEN mode, ROLE and RECOVERY mode of the database.
1 2 3 4 5 6 | [Logical-1-2] SQL> select OPEN_MODE, DATABASE_ROLE from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE LOGICAL STANDBY |
1 2 3 4 5 6 | [Logical-1-2] SQL> select DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE from v$archive_dest_status; DATABASE_MODE RECOVERY_MODE PROTECTION_MODE --------------- ----------------------- -------------------- OPEN LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE |
LOGICAL STANDBY DATABASE CHECK QUERIES
Check Redos are transferred from Primary Database to Logical Standby Database?
Learn Archive Redo Log numbers in Primary Database.
1 2 3 4 5 6 7 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 353 1 214 2 |
Query the existence and apply states of these sequences in Logical Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | [Logical-1] SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; Session altered. SQL> set linesize 500 SQL> column dict_begin format a10 SQL> column dict_end format a10 SQL> select sysdate from dual; SYSDATE ------------------- 10-01-2017 14:21:19 SQL> SELECT sequence#, first_time, next_time, dict_begin, dict_end, applied FROM dba_logstdby_log ORDER BY sequence#; SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 186 10-01-2017 10:17:01 10-01-2017 10:17:07 NO NO YES 187 10-01-2017 10:17:07 10-01-2017 10:17:10 NO NO YES 188 10-01-2017 10:17:10 10-01-2017 10:17:13 NO NO YES 189 10-01-2017 10:17:13 10-01-2017 10:17:19 NO NO YES 190 10-01-2017 10:17:19 10-01-2017 10:19:35 NO NO YES 191 10-01-2017 10:19:35 10-01-2017 10:19:37 NO NO YES 192 10-01-2017 10:19:37 10-01-2017 10:19:41 YES NO YES 193 10-01-2017 10:19:41 10-01-2017 10:19:43 NO YES YES 194 10-01-2017 10:19:43 10-01-2017 10:19:59 NO NO YES 195 10-01-2017 10:19:59 10-01-2017 10:22:29 NO NO YES 196 10-01-2017 10:22:29 10-01-2017 10:29:39 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 197 10-01-2017 10:29:39 10-01-2017 10:35:37 NO NO YES 198 10-01-2017 10:35:37 10-01-2017 10:40:53 NO NO YES 199 10-01-2017 10:40:53 10-01-2017 10:49:24 NO NO YES 200 10-01-2017 10:49:24 10-01-2017 10:50:31 NO NO YES 201 10-01-2017 10:50:31 10-01-2017 10:54:35 NO NO YES 202 10-01-2017 10:54:35 10-01-2017 10:54:38 NO NO YES 203 10-01-2017 10:54:38 10-01-2017 10:57:54 NO NO YES 204 10-01-2017 10:57:54 10-01-2017 10:57:56 NO NO YES 205 10-01-2017 10:57:56 10-01-2017 11:05:33 NO NO YES 206 10-01-2017 11:05:33 10-01-2017 11:06:01 NO NO YES 207 10-01-2017 11:06:01 10-01-2017 11:32:57 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 208 10-01-2017 11:32:57 10-01-2017 11:34:34 NO NO YES 209 10-01-2017 11:34:34 10-01-2017 11:38:59 NO NO YES 210 10-01-2017 11:38:59 10-01-2017 11:39:09 NO NO YES 211 10-01-2017 11:39:09 10-01-2017 11:47:33 NO NO YES 212 10-01-2017 11:47:33 10-01-2017 11:47:46 NO NO YES 213 10-01-2017 11:47:46 10-01-2017 12:22:48 NO NO YES 214 10-01-2017 12:22:48 10-01-2017 12:38:31 NO NO YES 324 10-01-2017 10:17:03 10-01-2017 10:17:08 YES NO YES 325 10-01-2017 10:17:08 10-01-2017 10:17:11 NO YES YES 326 10-01-2017 10:17:11 10-01-2017 10:17:14 NO NO YES 327 10-01-2017 10:17:14 10-01-2017 10:19:35 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 328 10-01-2017 10:19:35 10-01-2017 10:19:42 NO NO YES 329 10-01-2017 10:19:42 10-01-2017 10:19:55 NO NO YES 330 10-01-2017 10:19:55 10-01-2017 10:22:25 NO NO YES 331 10-01-2017 10:22:25 10-01-2017 10:29:38 NO NO YES 332 10-01-2017 10:29:38 10-01-2017 10:35:35 NO NO YES 333 10-01-2017 10:35:35 10-01-2017 10:40:51 NO NO YES 334 10-01-2017 10:40:51 10-01-2017 10:49:22 NO NO YES 335 10-01-2017 10:49:22 10-01-2017 10:50:30 NO NO YES 336 10-01-2017 10:50:30 10-01-2017 10:54:25 NO NO YES 337 10-01-2017 10:54:25 10-01-2017 10:54:31 NO NO YES 338 10-01-2017 10:54:31 10-01-2017 10:57:47 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 339 10-01-2017 10:57:47 10-01-2017 10:57:50 NO NO YES 340 10-01-2017 10:57:50 10-01-2017 11:05:33 NO NO YES 341 10-01-2017 11:05:33 10-01-2017 11:05:57 NO NO YES 342 10-01-2017 11:05:57 10-01-2017 11:29:15 NO NO YES 343 10-01-2017 11:29:15 10-01-2017 11:32:51 NO NO YES 344 10-01-2017 11:32:51 10-01-2017 11:32:53 NO NO YES 345 10-01-2017 11:32:53 10-01-2017 11:34:33 NO NO YES 346 10-01-2017 11:34:33 10-01-2017 11:38:53 NO NO YES 347 10-01-2017 11:38:53 10-01-2017 11:39:10 NO NO YES 348 10-01-2017 11:39:10 10-01-2017 11:47:29 NO NO YES 349 10-01-2017 11:47:29 10-01-2017 11:47:47 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 350 10-01-2017 11:47:47 10-01-2017 12:22:43 NO NO YES 351 10-01-2017 12:22:43 10-01-2017 12:37:18 NO NO YES 352 10-01-2017 12:37:18 10-01-2017 12:37:22 NO NO YES 353 10-01-2017 12:37:22 10-01-2017 13:55:16 NO NO YES 59 rows selected. |
Perform log switch operation in Primary.
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 354 1 215 2 |
Check if it’s coming to Logical.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | [Logical-1] SQL> SELECT sequence#, first_time, next_time, dict_begin, dict_end, applied FROM dba_logstdby_log ORDER BY sequence#; SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 186 10-01-2017 10:17:01 10-01-2017 10:17:07 NO NO YES 187 10-01-2017 10:17:07 10-01-2017 10:17:10 NO NO YES 188 10-01-2017 10:17:10 10-01-2017 10:17:13 NO NO YES 189 10-01-2017 10:17:13 10-01-2017 10:17:19 NO NO YES 190 10-01-2017 10:17:19 10-01-2017 10:19:35 NO NO YES 191 10-01-2017 10:19:35 10-01-2017 10:19:37 NO NO YES 192 10-01-2017 10:19:37 10-01-2017 10:19:41 YES NO YES 193 10-01-2017 10:19:41 10-01-2017 10:19:43 NO YES YES 194 10-01-2017 10:19:43 10-01-2017 10:19:59 NO NO YES 195 10-01-2017 10:19:59 10-01-2017 10:22:29 NO NO YES 196 10-01-2017 10:22:29 10-01-2017 10:29:39 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 197 10-01-2017 10:29:39 10-01-2017 10:35:37 NO NO YES 198 10-01-2017 10:35:37 10-01-2017 10:40:53 NO NO YES 199 10-01-2017 10:40:53 10-01-2017 10:49:24 NO NO YES 200 10-01-2017 10:49:24 10-01-2017 10:50:31 NO NO YES 201 10-01-2017 10:50:31 10-01-2017 10:54:35 NO NO YES 202 10-01-2017 10:54:35 10-01-2017 10:54:38 NO NO YES 203 10-01-2017 10:54:38 10-01-2017 10:57:54 NO NO YES 204 10-01-2017 10:57:54 10-01-2017 10:57:56 NO NO YES 205 10-01-2017 10:57:56 10-01-2017 11:05:33 NO NO YES 206 10-01-2017 11:05:33 10-01-2017 11:06:01 NO NO YES 207 10-01-2017 11:06:01 10-01-2017 11:32:57 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 208 10-01-2017 11:32:57 10-01-2017 11:34:34 NO NO YES 209 10-01-2017 11:34:34 10-01-2017 11:38:59 NO NO YES 210 10-01-2017 11:38:59 10-01-2017 11:39:09 NO NO YES 211 10-01-2017 11:39:09 10-01-2017 11:47:33 NO NO YES 212 10-01-2017 11:47:33 10-01-2017 11:47:46 NO NO YES 213 10-01-2017 11:47:46 10-01-2017 12:22:48 NO NO YES 214 10-01-2017 12:22:48 10-01-2017 12:38:31 NO NO YES 215 10-01-2017 12:38:31 10-01-2017 14:23:53 NO NO YES 324 10-01-2017 10:17:03 10-01-2017 10:17:08 YES NO YES 325 10-01-2017 10:17:08 10-01-2017 10:17:11 NO YES YES 326 10-01-2017 10:17:11 10-01-2017 10:17:14 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 327 10-01-2017 10:17:14 10-01-2017 10:19:35 NO NO YES 328 10-01-2017 10:19:35 10-01-2017 10:19:42 NO NO YES 329 10-01-2017 10:19:42 10-01-2017 10:19:55 NO NO YES 330 10-01-2017 10:19:55 10-01-2017 10:22:25 NO NO YES 331 10-01-2017 10:22:25 10-01-2017 10:29:38 NO NO YES 332 10-01-2017 10:29:38 10-01-2017 10:35:35 NO NO YES 333 10-01-2017 10:35:35 10-01-2017 10:40:51 NO NO YES 334 10-01-2017 10:40:51 10-01-2017 10:49:22 NO NO YES 335 10-01-2017 10:49:22 10-01-2017 10:50:30 NO NO YES 336 10-01-2017 10:50:30 10-01-2017 10:54:25 NO NO YES 337 10-01-2017 10:54:25 10-01-2017 10:54:31 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 338 10-01-2017 10:54:31 10-01-2017 10:57:47 NO NO YES 339 10-01-2017 10:57:47 10-01-2017 10:57:50 NO NO YES 340 10-01-2017 10:57:50 10-01-2017 11:05:33 NO NO YES 341 10-01-2017 11:05:33 10-01-2017 11:05:57 NO NO YES 342 10-01-2017 11:05:57 10-01-2017 11:29:15 NO NO YES 343 10-01-2017 11:29:15 10-01-2017 11:32:51 NO NO YES 344 10-01-2017 11:32:51 10-01-2017 11:32:53 NO NO YES 345 10-01-2017 11:32:53 10-01-2017 11:34:33 NO NO YES 346 10-01-2017 11:34:33 10-01-2017 11:38:53 NO NO YES 347 10-01-2017 11:38:53 10-01-2017 11:39:10 NO NO YES 348 10-01-2017 11:39:10 10-01-2017 11:47:29 NO NO YES SEQUENCE# FIRST_TIME NEXT_TIME DICT_BEGIN DICT_END APPLIED ---------- ------------------- ------------------- ---------- ---------- -------- 349 10-01-2017 11:47:29 10-01-2017 11:47:47 NO NO YES 350 10-01-2017 11:47:47 10-01-2017 12:22:43 NO NO YES 351 10-01-2017 12:22:43 10-01-2017 12:37:18 NO NO YES 352 10-01-2017 12:37:18 10-01-2017 12:37:22 NO NO YES 353 10-01-2017 12:37:22 10-01-2017 13:55:16 NO NO YES 354 10-01-2017 13:55:16 10-01-2017 14:23:50 NO NO YES 61 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | [Logical-1] SQL> column name format a33 SQL> column value format a20 SQL> select * from v$logstdby_stats; NAME VALUE --------------------------------- -------------------- logminer session id 1 number of preparers 1 number of appliers 5 server processes in use 9 maximum SGA for LCR cache (MB) 30 maximum events recorded 10000 preserve commit order TRUE transaction consistency FULL record skipped errors Y record skipped DDLs Y record applied DDLs N NAME VALUE --------------------------------- -------------------- record unsupported operations N realtime apply Y apply delay (minutes) 0 peak apply rate (bytes/sec) 16517240 current apply rate (bytes/sec) 8232708 coordinator state IDLE coordinator startup time 10-01-2017 10:51:00 coordinator uptime (seconds) 14020 txns received from logminer 920 txns assigned to apply 920 txns applied 920 NAME VALUE --------------------------------- -------------------- txns discarded during restart 0 large txns waiting to be assigned 0 session restart SCN 3544575 rolled back txns mined 299 DDL txns mined 8 CTAS txns mined 1 bytes of redo mined 64740224 bytes paged out 0 pageout time (seconds) 0 bytes checkpointed 77328081 checkpoint time (seconds) 0 NAME VALUE --------------------------------- -------------------- system idle time (seconds) 11715 standby redo logs mined 2 archived logs mined 59 gap fetched logs mined 25 standby redo log reuse detected 32 logfile open failures 0 current logfile wait (seconds) 0 total logfile wait (seconds) 0 thread enable mined 0 thread disable mined 0 distinct txns in queue 0 44 rows selected. |
If the value of the Coordinator state is IDLE, it means that everything is fine and that it is Primary and SYNC.
1 2 3 4 5 6 | Logical-1] SQL> SELECT name, value FROM v$logstdby_stats WHERE name = 'coordinator state'; NAME VALUE --------------------------------- -------------------- coordinator state IDLE |
Query the status of SQL-Apply processes from V $ LOGSTDBY_PROCESS view
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [Logical-1] SQL> column spid format a7 SQL> column type format a12 SQL> column status format a57 SQL> set linesize 500 SQL> select sid, serial#, spid, type, status from v$logstdby_process; SID SERIAL# SPID TYPE STATUS ---------- ---------- ------- ------------ --------------------------------------------------------- 143 9 25012 COORDINATOR ORA-16116: no work available 24 17 25387 ANALYZER ORA-16116: no work available 89 29 25391 APPLIER ORA-16116: no work available 148 17 25395 APPLIER ORA-16116: no work available 213 11 25399 APPLIER ORA-16116: no work available 27 11 25403 APPLIER ORA-16116: no work available 93 13 25407 APPLIER ORA-16116: no work available 208 11 25223 READER ORA-16242: Processing log file (thread# 1, sequence# 355) 23 11 25227 BUILDER ORA-16116: no work available 87 19 25231 PREPARER ORA-16116: no work available 10 rows selected. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> select sysdate from dual; SYSDATE ------------------- 10-01-2017 15:19:17 SQL> SELECT * FROM v$logstdby_progress; APPLIED_SCN APPLIED_TIME RESTART_SCN RESTART_TIME LATEST_SCN LATEST_TIME MINING_SCN MINING_TIME RESETLOGS_ID ----------- ------------------- ----------- ------------------- ---------- ------------------- ---------- ------------------- ------------ 3602487 10-01-2017 15:19:12 3602489 10-01-2017 15:19:13 3602503 10-01-2017 15:19:18 3602502 10-01-2017 15:19:15 932222810 |
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.
1 2 3 4 5 6 7 8 | [Logical-1] SQL> column REALTIME_APPLY format a15 SQL> column state format a7 SQL> SELECT * FROM V$LOGSTDBY_STATE; PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE ------------ ---------- --------------- ------- 1769705496 1 Y IDLE |
