Oracle 12cR2 DBMS_INTERNAL

Oracle DBMS_INTERNAL_LOGSTDBY Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"

~ Sen. Tammy Duckworth

Purpose Internal support utilities for Data Guard Logical Standby Databases

AUTHID DEFINER

Dependencies

ALL_OBJECTS	DBMS_IJOB	LOGSTDBY$EDS_TABLES
AUDIT_ACTIONS	DBMS_INTERNAL_SAFE_SCN	LOGSTDBY$EVENTS
CDEF$	DBMS_JOB	LOGSTDBY$HISTORY
COL$	DBMS_LOB	LOGSTDBY$PARAMETERS
DBA_LOGSTDBY_EDS_SUPPORTED	DBMS_LOCK	LOGSTDBY$SCN
DBA_LOGSTDBY_EDS_TABLES	DBMS_LOGMNR_INTERNAL	LOGSTDBY$SKIP
DBA_LOGSTDBY_LOG	DBMS_LOGSTDBY	LOGSTDBY$SKIP_SUPPORT
DBA_LOGSTDBY_PARAMETERS	DBMS_LOGSTDBY_LIB	LOGSTDBY$SKIP_TRANSACTION
DBA_LOGSTDBY_PROGRESS	DBMS_SQL	LOGSTDBY_INTERNAL
DBA_LOGSTDBY_SKIP	DBMS_STANDARD	OBJ$
DBA_LOGSTDBY_UNSUPPORTED	DBMS_SYSTEM	PRVT_COMPRESSION
DBA_NESTED_TABLES	DUAL	SYSLSBY_EDS_DDL_TRIG
DBA_OBJECT_TABLES	IND$	TAB$
DBA_PROCEDURES	INDPART$	TS$
DBA_REFS	JOB$	USER$
DBA_TABLES	KUPM$MCP	UTL_RECOMP
DBA_TAB_COLS	KUPV$FT	V$DATABASE
DBA_USERS	KUPV$FT_INT	V$INSTANCE
DBA_VIEWS	KUPW$WORKER	V$LOGSTDBY_STATE
DBMS_AQ_SYS_IMP_INTERNAL	LOGMNR_LOG$	V$PARAMETER
DBMS_ASSERT	LOGMNR_SESSION$	V$TRANSACTION
DBMS_AUDIT_MGMT	LOGSTDBY$APPLY_MILESTONE	X$KRVSLVST
DBMS_DDL

Documented No

First Available 12.1.0

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtlsis.plb

Subprograms

APPLY_GET	ENABLE_LOGICAL_REPLICATION	LSBY_LOCK_TABLE
APPLY_IS_OFF	END_STREAM	LSBY_UNLOCK_TABLE
APPLY_SET	END_STREAM_SHARED	MATCHED_PRIMARY
APPLY_STOP_NOWAIT	FGADEL	NEED_SCN
APPLY_UNSET	FGAINS	PARDEL
AUDDEL	FGAUPD	PARINS
AUDINS	FLUSH_SRLS	PARUPD
AUDUPD	GET_DB_ROLE	PREPARE_FOR_NEW_PRIMARY
BUILD	GET_EXPORT_DML_SCN	PRIMARY_DBID
CANCEL_FUTURE	GET_OBJ_NUM	PRINTLOB
CAPTURE_SCN	GET_SAFE_SCN	PURGE_LOGS
CHECK_SKIP_LIKE	GUARD_BYPASS_CHK	REBUILD
CLEAR_LOGICAL_INSTANTIATION	GUARD_BYPASS_OFF	REPAIR_LSBY
DATA_ONLY_PREREQ	GUARD_BYPASS_ON	REPLACE_DICTIONARY
DISABLE_LOGICAL_REPLICATION	GUARD_CHECK	REPORT_ERROR
DUMP_XDAT	HIST_READ_RECORD	RETRIEVE_STATEMENT
EDS_ADD_PREREQ	HIST_SYNCH	SEQUENCE_UPDATE
EDS_ADD_TABLE_FINISH	HIST_WRITE_RECORD_CANCEL	SEQUPD
EDS_ADD_TABLE_INT	HIST_WRITE_RECORD_CURRENT	SET_EXPORT_SCN
EDS_CHECK_EVOLVE_STARTED	HIST_WRITE_RECORD_FUTURE	SET_LOGICAL_INSTANTIATION
EDS_CLEANUP_METADATA	HIST_WRITE_RECORD_PREVIOUS	SET_TABLESPACE
EDS_DROP_MVIEW	HSTDEL	SET_TABLE_SCN
EDS_DROP_TRIGGER	HSTINS	SKIP_SUPPORT
EDS_EVAL_CHANGE_VECTOR	HSTUPD	SKIP_TRANSACTION
EDS_EVOLVE	INSTANTIATE_TAB_LOG	UNLOCK_LSBY_CON
EDS_EVOLVE_DISABLE	INSTANTIATE_TAB_PREREQ	UNLOCK_LSBY_META
EDS_EVOLVE_ENABLE	IS_EDS_MAINTAINED	UNSKIP_TRANSACTION
EDS_EVOLVE_TABLE_CANCEL	IS_EDS_SUPPORTABLE	UPCASE
EDS_EVOLVE_TABLE_END	IS_LSBY_SUPPORTABLE	UPDATE_DYNAMIC_LSBY_OPTION
EDS_EVOLVE_TABLE_START	IS_PDB_ROOT	VALIDATE_SET
EDS_GEN_MV	IS_SUPP_AND_NOTSKIP	VALIDATE_SKIP_ACTION
EDS_GEN_TRIGGERS	JOBDEL	VALIDATE_SKIP_AUTHID
EDS_GET_NAMES	JOBINS	VERIFY_NOSESSION
EDS_GET_TABLESPACE	JOBUPD	VERIFY_SESSION
EDS_REMOVE_TABLE_FINISH	LOCK_LSBY_CON	VERIFY_SESSION_LOGAUTODELETE
EDS_REMOVE_TABLE_INT	LOCK_LSBY_META	WAIT_FOR_SAFE_SCN
EDS_USER_CURSOR

APPLY_IS_OFF

Disables apply dbms_internal_logstdby.apply_is_off;

exec dbms_internal_logstdby.apply_is_off;

APPLY_STOP_NOWAIT

Immediate apply stop dbms_internal_logstdby.apply_stop_nowait;

exec dbms_internal_logstdby.apply_stop_nowait;

BUILD

Ensures supplemental logging is enabled properly and builds the LogMiner dictionary. Turns on supplemental logging automatically in 11gR2 or later. dbms_internal_logstdby.build;

exec dbms_internal_logstdby.build;

CANCEL_FUTURE

This is totally undocumented and I have no idea what it does but the name was just too good to allow for resistance and the syntax mindlessly simple dbms_internal_logstdby.cancel_future;

exec dbms_internal_logstdby.cancel_future;

CAPTURE_SCN

Undocumented dbms_internal_logstdby.capture_scn;

exec dbms_internal_logstdby.capture_scn;

      BEGIN dbms_internal_logstdby.capture_scn; END;

      *

      ERROR at line 1:

      ORA-16287: operation not permitted due to active apply state

      ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 373

      ORA-06512: at line 1

CLEAR_LOGICAL_INSTANTIATION

Clears instantiation dbms_internal_logstdby.clear_logical_instantiation;

exec dbms_internal_logstdby.clear_logical_instantiation;

DISABLE_LOGICAL_REPLICATION (new 12.2)

Poorly named ... returns the current logical replication state dbms_internal_logstdby.disable_logical_replication RETURN BOOLEAN;

See ENABLE_LOGICAL_REPLICATION below

DUMP_XDAT

Undocumented dbms_internal_logstdby.dump_xdat;

exec dbms_internal_logstdby.dump_xdat;

EDS_EVOLVE_DISABLE (new 12.2)

Undocumented dbms_internal_logstdby.eds_evolve_disable;

exec dbms_internal_logstdby.eds_evolve_disable;

EDS_EVOLVE_ENABLE (new 12.2)

Undocumented dbms_internal_logstdby.eds_evolve_enable;

exec dbms_internal_logstdby.eds_evolve_enable;

EDS_USER_CURSOR

Undocumented dbms_internal_logstdby.eds_user_cursor( user_issued OUT BOOLEAN, table_owner OUT VARCHAR2, table_name OUT VARCHAR2);

See {ORACLE_HOME}/rdbms/admin/catlsby.sql

ENABLE_LOGICAL_REPLICATION (new 12.2)

Enables logical replication dbms_internal_logstdby.enable_logical_replication(repl_state IN BOOLEAN);

DECLARE

       retVal BOOLEAN := dbms_internal_logstdby.disable_logical_replication;

      BEGIN

        IF retVal THEN 

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

      

      exec 
      dbms_internal_logstdby.enable_logical_replication(TRUE);

      

      DECLARE

       retVal BOOLEAN := dbms_internal_logstdby.disable_logical_replication;

      BEGIN

        IF retVal THEN 

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

      

      exec 
      dbms_internal_logstdby.enable_logical_replication(FALSE);

      

      DECLARE

       retVal BOOLEAN := dbms_internal_logstdby.disable_logical_replication;

      BEGIN

        IF retVal THEN 

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

GET_DB_ROLE

Returns the current database role

It would be interesting to know why anyone chose to do this with a proc rather than a function. dbms_internal_logstdby.get_db_role(dbrole OUT VARCHAR2);

DECLARE

       lDBRole VARCHAR2(30);

      BEGIN

        dbms_internal_logstdby.get_db_role(lDBRole);

        dbms_output.put_line(lDBRole);

      END;

      /

GET_OBJ_NUM

Return the object number for the identified table dbms_internal_logstdby.get_obj_num( table_owner IN VARCHAR2, table_name IN VARCHAR2, dblink IN VARCHAR2, objno OUT NUMBER);

conn / as sysdba

      

      SELECT object_id

      FROM dba_objects

      WHERE object_name = 'OBJ$';

      

      set serveroutput on

      

      DECLARE

       objid obj$.obj#%TYPE;

      BEGIN

        dbms_internal_logstdby.get_obj_num(USER, 'OBJ$', NULL, objid);

        dbms_output.put_line(TO_CHAR(objid));

      END;

      /

GET_SAFE_SCN

Appears to return the current SCN dbms_internal_logstdby.get_safe_scn(safe_scn OUT NUMBER);

DECLARE

       scnVal NUMBER;

      BEGIN

        dbms_internal_logstdby.get_safe_scn(scnVal);

        dbms_output.put_line('Safe SCN: ' || TO_CHAR(scnVal));

      

        dbms_output.put_line('Curr SCN" ' || TO_CHAR(dbms_flashback.get_system_change_number));

      END;

      /

GUARD_BYPASS_OFF (new 12.2)

Disable Data Guard Bypass dbms_internal_logstdby.guard_bypass_off;

exec dbms_internal_logstdby.guard_bypass_off;

GUARD_BYPASS_ON (new 12.2)

Enable Data Guard Bypass dbms_internal_logstdby.guard_bypass_on;

exec dbms_internal_logstdby.guard_bypass_on;

GUARD_CHECK

Undocumented dbms_internal_logstdby.guard_check;

exec dbms_internal_logstdby.guard_check;

IS_EDS_MAINTAINED (new 12.2)

Undocumented dbms_internal_logstdby.is_eds_maintained( owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;

SQL> BEGIN

       
      2    IF dbms_internal_logstdby.is_eds_maintained(USER, 'OBJ$') THEN

       
      3      dbms_output.put_line('Extended Data Type Supportable');

       
      4    ELSE

       
      5      dbms_output.put_line('Not Extended Data Type Supportable');

       
      6    END IF;

       
      7  END;

       
      8  /

      Not Extended Data Type Supportable

      

      PL/SQL procedure successfully completed.

IS_EDS_SUPPORTABLE

Extended Datatype Supportable dbms_internal_logstdby.is_eds_supportable( owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;

SQL> BEGIN

       
      2    IF dbms_internal_logstdby.is_eds_supportable(USER, 'OBJ$') THEN

       
      3      dbms_output.put_line('Extended Data Type Supportable');

       
      4    ELSE

       
      5      dbms_output.put_line('Not Extended Data Type Supportable');

       
      6    END IF;

       
      7  END;

       
      8  /

      Not Extended Data Type Supportable

      

      PL/SQL procedure successfully completed.

IS_LSBY_SUPPORTABLE

Is Logical Standby Supported dbms_internal_logstdby.is_lsdby_supportable( owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_internal_logstdby.is_lsby_supportable(USER, 'OBJ$') THEN

          dbms_output.put_line('Logical Standby Supportable');

        ELSE

          dbms_output.put_line('Not Logical Standby Supportable');

        END IF;

      END;

      /

IS_PDB_ROOT

Returns TRUE if the current container is CDB$ROOT otherwise appears to exit without returning FALSE dbms_internal_logstdby.is_pdb_root RETURN BOOLEAN;

conn / as sysdba

      

      BEGIN

        IF dbms_internal_logstdby.is_pdb_root THEN

          dbms_output.put_line('Current container is CDB$ROOT');

        ELSE

          dbms_output.put_line('Current container is ' || SYS_CONTEXT('USERENV','CON_NAME'));

        END IF;

      END;

      /

      

      alter session set container = 'PDBDEV';

      

      BEGIN

        IF dbms_internal_logstdby.is_pdb_root THEN

          dbms_output.put_line('Current container is CDB$ROOT');

        ELSE

          dbms_output.put_line('Current container is ' || SYS_CONTEXT('USERENV','CON_NAME'));

        END IF;

      END;

      /

-- unfortunately an other example of Oracle doing EXCEPTION WHEN OTHERS THEN NULL

IS_SUPP_AND_NOTSKIP (new 12.2)

Undocumented dbms_internal_logstdby.is_supp_and_skip( owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;

SQL> BEGIN

       
      2    IF dbms_internal_logstdby.is_supp_and_notskip(USER, 'OBJ$') THEN

       
      3      dbms_output.put_line('LTRUE');

       
      4    ELSE

       
      5      dbms_output.put_line('FALSE');

       
      6    END IF;

       
      7  END;

       
      8  /

      TRUE

      

      PL/SQL procedure successfully completed.

REPLACE_DICTIONARY

Undocumented dbms_internal_logstdby.replace_dictionary;

exec dbms_internal_logstdby.replace_dictionary;

SET_LOGICAL_INSTANTIATION

Undocumented dbms_internal_logstdby.set_logical_instantiation;

exec dbms_internal_logstdby.set_logical_instantiation;

UNLOCK_LSBY_META

Undocumented dbms_internal_logstdby.unlock_lsby_meta;

exec dbms_internal_logstdby.unlock_lsby_meta;

UPCASE

Apparently the UPPER function was inadequate for someone dbms_internal_logstdby.upcase(inname IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_internal_logstdby.upcase('sUrElY tHiS is A JokE')

      FROM dual;

      

      SQL> SELECT dbms_internal_logstdby.upcase(123.4)

        2  FROM dual;

      

      DBMS_INTERNAL_LOGSTDBY.UPCASE(123.4)

      --------------------------------------

      123.4

      

      SQL> SELECT dbms_internal_logstdby.upcase(12a3.4)

        2  FROM dual;

      SELECT dbms_internal_logstdby.upcase(12a3.4)

      *

      ERROR at line 1:

      ORA-00907: missing right parenthesis

-- all exactly the same behaviour as the UPPER function in STANDARD

VERIFY_NOSESSION

Undocumented dbms_internal_logstdby.verify_nosession;

SQL> exec dbms_internal_logstdby.verify_nosession;

      

      PL/SQL procedure successfully completed.

VERIFY_SESSION

Undocumented dbms_internal_logstdby.verify_session;

SQL> exec dbms_internal_logstdby.verify_session;

      BEGIN dbms_internal_logstdby.verify_session; END;

      *

      ERROR at line 1:

      ORA-16100: not a valid Logical Standby database

      ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 919

      ORA-06512: at line 1