Oracle | DBMS_LOGSTDBY

Oracle DBMS_LOGSTDBY Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides subprograms for configuring and managing a Data Guard Logical Standby database

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Skip Procedure
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_LOGMNR_SESSION_INT	LOGSTDBY$FLASHBACK_SCN
DBMS_AQ	DBMS_LOGSTDBY_LIB	LOGSTDBY$PARAMETERS
DBMS_AQADM_SYS	DBMS_PRVTAQIS	LOGSTDBY_INTERNAL
DBMS_INTERNAL_LOGSTDBY	DBMS_PRVTSQIS	SYSLSBY_EDS_DDL_TRIG
DBMS_ISCHED	DBMS_STANDARD	V$DATABASE

Documented Yes: Packages and Types Reference

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

Security Model Owned by SYS with EXECUTE granted to DBA and LOGSTDBY_ADMINISTRATOR

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

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

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 IN 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, con_name_p IN VARCHAR2 DEFAULT NULL);

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, con_name_p IN VARCHAR2 DEFAULT NULL);

exec dbms_logstdby.unskip_transaction(XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726);