| 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;
/ |
