Oracle DBMS_LOGSTDBY
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides subprograms for configuring and managing a Data Guard Logical Standby database
AUTHID CURRENT_USER
Constants
Name Data Type Value
Skip Procedure Constants
SKIP_ACTION_SKIP NUMBER 1
SKIP_ACTION_APPLY NUMBER 0
SKIP_ACTION_REPLACE NUMBER -1
SKIP_ACTION_ERROR NUMBER -2
SKIP_ACTION_NOPRIVS NUMBER -3
Maximum Event Records
MAX_EVENTS NUMBER 2000000000
Data Types SUBTYPE CHAR1 IS CHAR(1);
Dependencies
DBA_LOGSTDBY_PROGRESS DBMS_LOGSTDBY_LIB LOGSTDBY$PARAMETERS
DBMS_AQADM_SYS DBMS_PRVTAQIS LOGSTDBY_INTERNAL
DBMS_ASSERT DBMS_STANDARD SYSLSBY_EDS_DDL_TRIG
DBMS_INTERNAL_LOGSTDBY LOGSTDBY$FLASHBACK_SCN V$DATABASE
DBMS_LOGMNR_SESSION    
Documented Yes
Exceptions
Error Code Reason
ORA-01031 Need DBA privileges to execute this procedure
ORA-01309 Invalid Session
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 Invalid Logical Standby option requested
ORA-16109 Failed to apply log data from previous primary
ORA-16203 Unable to interpret SKIP procedure return values
ORA-16236 Logical Standby metadata operation in progress
ORA-16276 Specified database link does not correspond to primary database
ORA-16277 Specified table is not supported by logical standby database
ORA-16278 Specified table has a multi-object skip rule defined
ORA-20001 Primary SCN before mapped range
ORA-20002 SCN mapping requires PRESERVE_COMMIT_ORDER true
First Available 9.2.0.1
Security Model Owned by SYS with EXECUTE granted to the DBA and LOGSTDBY_ADMINISTRATOR roles.
Source {ORACLE_HOME}/rdbms/admin/dbmslsby.sql
Subprograms
 
APPLY_SET
Sets the values of various parameters that configure and maintain SQL Apply dbms_logstdby.apply_set(
inname IN VARCHAR,
value  IN VARCHAR);

Parameter Description
APPLY_SERVERS The number of APPLIER processes used to apply changes
DEST_ALL Recorded all events in DBA_LOGSTDBY_EVENTS and in alert log
DEST_EVENTS_TABLE All events that contain information about user data will be recorded only in the DBA_LOGSTDBY_EVENTS view
LOG_AUTO_DEL_RETENTION_TARGET Only meaningful if LOG_AUTO_DELETE has been set to TRUE. The value you supply for this parameter controls how long (in minutes) a remote archived log that is received from the primary database will be retained at the logical standby database once all redo records contained in the log have been applied at the logical standby database. The default value is 1440 minutes.
LOG_AUTO_DELETE Automatically deletes archived redo log files once they have been applied on the logical standby database
MAX_EVENTS_RECORDED Number of recent events that will be visible through the DBA_LOGSTDBY_EVENTS view. To record all events encountered by SQL Apply, use the DBMS_LOGSTDBY.MAX_EVENTS constant as the number value. The default value is 10,000.
MAX_SERVERS Number of processes that SQL Apply uses to read and apply redo. The default value is 9. The maximum number allowed is 2048.
MAX_SGA Number of megabytes from shared pool in System Global Area (SGA) that SQL Apply will use. The default value is 30 megabytes or one quarter of the value set for SHARED_POOL_SIZE, whichever is lower. The maximum size allowed is 4095 megabytes.
PREPARE_SERVERS Controls the number of PREPARER processes used to prepare changes. The maximum number allowed is 1024, provided the MAX_SERVERS parameter is set to accommodate this.
PRESERVE_COMMIT_ORDER TRUE: Transaction are applied to the logical standby database in the exact order in which they were committed on the primary database. This is the default parameter setting.

FALSE: Transactions are applied out of order from how they were committed on the primary database, and no attempt is made to provide read-consistent results.

Regardless of the level chosen, modifications done to the same row are always applied in the same order as they happened in the primary database. See the Usage Notes for details and recommendations.

You cannot modify this parameter while SQL Apply is running.
RECORD_APPLIED_DDL Controls whether DDL statements that have been applied to the logical standby database are recorded in the location specified by the EVENT_LOG_DEST parameter.Specify one of the following values:

TRUE: Indicates that DDL statements applied to the logical standby database are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.

FALSE: Indicates that applied DDL statements are not recorded. This is the default parameter setting.
RECORD_SKIP_DDL Controls whether skipped DDL statements are recorded in the location specified by the EVENT_LOG_DEST parameter. Specify one of the following values:

TRUE: Skipped DDL statements are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. This is the default parameter setting.

FALSE: Skipped DDL statements are not recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.
RECORD_SKIP_ERRORS Controls whether skipped errors (as described by the SKIP_ERROR procedure) are recorded in the location specified by the EVENT_LOG_DEST parameter. Specify one of the following values:

TRUE: Skipped errors are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. This is the default parameter setting.

FALSE: Skipped errors are not recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.
RECORD_UNSUPPORTED_OPERATIONS Captures information about transactions running on the primary database that will not be supported by a logical standby database. This procedure records its information as events in the DBA_LOGSTDBY_EVENTS table.
exec dbms_logstdby.apply_set('MAX_SERVERS', '18');
 
APPLY_UNSET
Restores the default values of various parameters that configure and maintain SQL Apply dbms_logstdby.apply_unset(iname IN VARCHAR);
exec dbms_logstdby.apply_unset('MAX_SERVERS');
 
BUILD
Ensures supplemental logging is enabled properly and builds the LogMiner dictionary. Turns on supplemental logging automatically in 11gR2 or later. dbms_logstdby.build;
exec dbms_logstdby.build;
 
DB_IS_LOGSTDBY
Returns 1 if called from a Logical Standby database otherwise 0 dbms_logstdby.db_is_logstdby RETURN BINARY_INTEGER;
SELECT dbms_logstdby.db_is_logstdby
FROM dual;
 
EDS_ADD_TABLE
For Extended Datatype Support on logical standby. By calling this procedure on the primary first and then the standby, tables with extended datatypes can be supported on a logical standby. dbms_logstdby.eds_add_table(
table_owner IN VARCHAR2,
table_name  IN VARCHAR2,
p_dblink    IN VARCHAR2 DEFAULT NULL);
exec dbms_logstdby.eds_add_table(USER, 'NEWTABLE');
 
EDS_EVOLVE_AUTOMATIC (new 12.1)
Enables or disables the automated EDS evolve
infrastructure whereby EDS objects are automatically evolved
in response to DDL done on an EDS-maintained table
dbms_logstdby.eds_evolve_automatic(options IN VARCHAR2); -- options are ENABLE/DISABLE
exec dbms_logstdby.eds_evolve_automatic('ENABLE');
 
EDS_EVOLVE_MANUAL (new 12.1)
Call if the you chooses to manually evolve EDS infrastructure across a DDL on the EDS-maintained table dbms_logstdby.eds_evolve_manual(
options     IN VARCHAR2, -- START | FINISH | CANCEL
table_owner IN VARCHAR2,
table_name  IN VARCHAR2);
exec dbms_logstdby.eds_evolve_manual('START', 'UWCLASS', 'SERVERS');
 
EDS_REMOVE_TABLE
Extended Datatype Support on a logical standby. Can be invoked on the primary or the standby. If invoked on the primary, its actions will be replicated by way of an AUTO pragma. If invoked from the standby it will only drop EDS on that standby. dbms_logstdby.eds_remove_table(
table_owner IN VARCHAR2,
table_name  IN VARCHAR2);
SELECT dbms_logstdby.eds_remove_table(USER, 'NEWTABLE');
 
INSTANTIATE_TABLE
Creates and populates a table in the standby database from a corresponding table in the primary database dbms_logstdby.instantiate_table(
schema_name IN VARCHAR2,
table_name  IN VARCHAR2,
dblink      IN VARCHAR2);
exec dbms_logstdby.instantiate_table('UWCLASS', 'SERVERS', 'REMOTE');
 
IS_APPLY_SERVER
Returns TRUE/FALSE on whether called from apply process dbms_logstdby.is_apply_server RETURN BOOLEAN;
set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_logstdby.is_apply_server THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
MAP_PRIMARY_SCN
Maps an SCN relevant to the primary database to a corresponding SCN at the logical standby database dbms_logstdby.instantiate_table(primary_scn NUMBER) RETURN NUMBER;
SELECT dbms_logstdby.map_primary_scn(13783989)
FROM dual;
 
PREPARE_FOR_NEW_PRIMARY
After failover ensures a local logical standby not involved in the failover has not processed more redo than the new primary database and reports the set of archive redo log files that must be replaced to ensure consistency dbms_logstdby.prepare_for_new_primary(
former_standby_type IN VARCHAR2,
dblink              IN VARCHAR2);
exec dbms_logstdby.prepare_for_new_primary('LOGICAL', 'REMOTE');
 
PURGE_SESSION
Identifies the archived redo log files that have been applied to the logical standby database and are no longer needed by SQL Apply dbms_logstdby.purge_session;
exec dbms_logstdby.purge_session;

desc dba_logmnr_purged_log

col file_name format a90

SELECT file_name
FROM dba_logmnr_purged_log;
 
REBUILD
Records relevant metadata in the redo stream in case a database that has recently changed its role to a primary database following a failed failover operation dbms_logstdby.rebuild;
exec dbms_logstdby.rebuild;
 
SET_TABLESPACE
Moves metadata tables required by SQL Apply to the user-specified tablespace. By default, the metadata tables are created in the SYSAUX tablespace. dbms_logstdby.set_tablespace(new_tablespace IN VARCHAR2);
exec dbms_logstdby.set_tablespace('UWDATA');
 
SKIP
Specifies rules that control database operations that should not be applied to the logical standby database dbms_logstdby.skip(
stmt        IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL,
proc_name   IN VARCHAR2 DEFAULT NULL,
use_like    IN BOOLEAN  DEFAULT TRUE,
esc         IN CHAR1    DEFAULT NULL);
CREATE OR REPLACE PROCEDURE sys.handle_tbs_ddl (
 old_stmt IN  VARCHAR2,
 stmt_typ IN  VARCHAR2,
 schema   IN  VARCHAR2,
 name     IN  VARCHAR2,
 xidusn   IN  NUMBER,
 xidslt   IN  NUMBER,
 xidsqn   IN  NUMBER,
 action   OUT NUMBER,
 new_stmt OUT VARCHAR2) AS
BEGIN
  -- All primary file specification that contains a directory
  -- /usr/orcl/primary/dbs should go to /usr/orcl/stdby directory
  -- specification

  new_stmt = REPLACE(old_stmt,'/usr/orcl/primary/dbs', '/usr/orcl/stdby');

  action := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
  WHEN OTHERS THEN
    action := dbms_logstdby.skip_action_error;
    new_stmt := NULL;
END handle_tbs_ddl;
/

exec dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name =>'sys.handle_tbs_ddl');
 
SKIP_ERROR
Specifies rules regarding what action to take upon encountering errors dbms_logstdby.skip_error(
stmt        IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL,
proc_name   IN VARCHAR2 DEFAULT NULL,
use_like    IN BOOLEAN  DEFAULT TRUE,
esc         IN CHAR1    DEFAULT NULL); -- note: CHAR1 is not an error
CREATE OR REPLACE PROCEDURE handle_error_ddl(
 old_stmt  IN  VARCHAR2,
 stmt_type IN  VARCHAR2,
 schema    IN  VARCHAR2,
 name      IN  VARCHAR2,
 xidusn    IN  NUMBER,
 xidslt    IN  NUMBER,
 xidsqn    IN  NUMBER,
 error     IN  VARCHAR2,
 new_stmt  OUT VARCHAR2) AS

BEGIN
  -- default to what we already have
  new_stmt := old_stmt;

  -- ignore any GRANT errors on SYS or UWCLASS schemas
  IF INSTR(UPPER(old_stmt),'GRANT') > 0 THEN
    IF schema IS NULL
    OR (schema IS NOT NULL AND (UPPER(schema) = 'SYS'
    OR UPPER(schema) = 'UWCLASS') THEN
      new_stmt := NULL;
      -- record the fact that an error was skipped
      NULL;
    END IF;
  END IF;
END handle_error_ddl;
/

exec dbms_logstdby.skip_error(statement => 'NON_SCHEMA_DDL',
schema_name => NULL, object_name => NULL, proc_name => 'sys.handle_error_ddl');
 
SKIP_TRANSACTION
Specifies transactions that should not be applied on the logical standby database. May cause data corruption at the logical standby database. dbms_logstdby.skip_transaction(
xidusn_p IN NUMBER,
xidslt_p IN NUMBER,
xidsqn_p IN NUMBER);
exec dbms_logstdby.skip_transaction(XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726);
 
UNSKIP
Deletes rules specified by the SKIP procedure dbms_logstdby.unskip(
stmt        IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL);
TBD
 
UNSKIP_ERROR
Deletes rules specified by the SKIP_ERROR procedure dbms_logstdby.unskip_error(
stmt        IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL);
TBD
 
UNSKIP_TRANSACTION
Deletes rules specified by the SKIP_TRANSACTION procedure dbms_logstdby.unskip_transaction(
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER);
exec dbms_logstdby.unskip_transaction(XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726);

Related Topics
Data Guard
Logical Data Guard
Packages