CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_LOGSTDBY
Version 11.1
 
General
Source {ORACLE_HOME}/rdbms/admin/dbmslsby.sql
First Available 9i

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_PARAMETERS DBMS_LOGMNR_SESSION LOGSTDBY$FLASHBACK_SCN
DBA_LOGSTDBY_PROGRESS GV$DATABASE LOGSTDBY$PARAMETERS
DBMS_ASSERT KU$_JOBSTATUS1020 PLITBLM
DBMS_DATAPUMP KU$_LOGENTRY SESSION_PRIVS
DBMS_INTERNAL_LOGSTDBY KU$_LOGLINE1010 SMON_SCN_TIME
DBMS_LOCK KU$_STATUS  

Exceptions
Number Description
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
Security Model In 11g execute is revoked from PUBLIC and granted to DBA. A new role, logstdby_administrator is created execute on this package and RESOURCE is granted to the new role.
 
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 in 11g. dbms_logstdby.build;
exec dbms_logstdby.build;
 
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 (new 11.1.0.7)

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('TRUE');
  END IF;
END;
/
 
MAP_PRIMARY_SCN
Maps an SCN relevant to the primary database to a corresponding SCN at the logical standby database. The mapped SCN is conservative in nature, and can thus be used to flash back the logical standby database to compensate for a flashback database operation performed at the primary database. dbms_logstdby.instantiate_table(primary_scn NUMBER) RETURN NUMBER;
SELECT dbms_logstdby.map_primary_scn(13783989)
FROM dual;
 
PREPARE_FOR_NEW_PRIMARY
Used after a failover, this procedure ensures a local logical standby database that was 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 (including the LogMiner dictionary) in the redo stream in case a database that has recently changed its role to a primary database following a failover operation fails to do so during the failover process 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);
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);
 
VALIDATE_AUTH
Validates security aspects of skip procedures (sec bug 4315344) this proc is here because  dbms_logstdby_internal is declared with authid definer and this requires roles to be active. dbms_logstdby.validate_auth RETURN BOOLEAN;
DECLARE
 b BOOLEAN;
BEGIN
  b := dbms_logstdby.validate_auth;
END;
/
 
Related Topics
Data Guard
Logical Data Guard
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [74 users online]    © 2010 psoug.org