LogMiner

LogMiner Enhancements

9i improves LogMiner in a variety of ways. The purpose of LogMiner is to allow you to view entries in the redo log (the log that records database changes).

LogMiner now has a GUI called the LogMiner Viewer. Use this new GUI in viewing the contents of the redo log files via the view V$LOGMNR_CONTENTS. LogMiner Viewer has Display Options that allow you to specify what information you want to view from the logs. Two of the key fields are SQL_REDO and SQL_UNDO. The former contains the redo log statement, while the latter has its “inverse” – a statement that would reverse its effects (or “undo” it).

9i now records DDL statements in the redo logs. (You no longer have to reverse-engineer them from the complex DML Oracle runs against its internal tables, as you had to when using LogMiner under 8i). You can track (but not) undo DDL commands. That is, you can still not recover from a table DROP or TRUNCATE using this feature.

Remember that LogMiner requires access to the data dictionary in order to translate object identifiers into their names and data according to proper data types. Without dictionary access, LogMiner returns object identifiers and hex data representations – not too useful. You can now extract the data dictionary to either a flat file or to the redo log files by using the procedure DBMS_LOGMNR_D.BUILD. Specify the OPTIONS parameter in this procedure as either STORE_IN_FLAT_FILE or STORE_IN_REDO_LOGS.

There is also a third OPTION – DICT_FROM_ONLINE_CATALOG. This uses the online data dictionary, which could possibly be inaccurate if the database changed since the logs were generated.

If you extract the dictionary to either a flat file or the redo logs, you can specify the additional option DDL_DICT_TRACKING. This prompts LogMiner to keep its extracted dictionary in sync with any changes to the real data dictionary by applying DDL it encounters in the redo logs. LogMiner detects that its dictionary export is stale (obsolete) through its use of object version numbers.

LogMiner stops whenever it encounters a corruption in the redo log it is viewing. Use the OPTION called SKIP CORRUPTIONS to force it to continue beyond corruptions.

Oracle DataGuard

Data Guard

Oracle’s Standby Database feature has been upgraded and renamed to Data Guard in 9i. Data Guard allows you to set up a standby database (locally or at a remote site) and keep it either completely or approximately “in sync” with the primary database by automatically shipping and applying redo logs to the standby database. This provides for quick disaster and/or off-site recovery should the primary database be lost or destroyed. Data Guard components are:

• Data Guard Manager – The new administrative Data Guard GUI (part of OEM)
• Data Guard Command Line Interface (CLI) – For issuing commands
• Data Guard Monitor (DMON) – A monitor process that supports the Data Guard administration

Update the standby database by one of four Data Guard modes:

• Guaranteed – Keeps primary and standby totally in sync at all times. Primary transactions are not committed until verification that the change has been applied to the standby
• Instant – Ensures changes are shipped to the standby but does not require they immediately be applied and confirmed
• Rapid – Log Writer (LGWR) on the primary sends changes to the standby as soon as it can
• Delayed – the Archiver Process (ARCH) process on the primary send changes to the standby. You can specify a time lag prior to propagation (this can help avoid propagating errors)

Guaranteed mode is the most conservative and keeps the standby totally synchronized with the primary, but impacts primary performance the most. The other modes are progressively “looser” in the coupling between the primary and standby but cause less performance impact on the primary. You configure the mode (and other Data Guard attributes) by the initialization parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n.

Role Management Service (RMS) helps you to perform database transitions (switching the roles of the primary and standby databases). A switchover is a planned transition whereby you purposefully make the primary the standby, and vice versa. Among the commands you use during a switchover are these key ones:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A switchback is the reverse of the switchover operation. It puts the two databases back to their original roles as primary and standby.

A graceful failover is performed when the primary becomes unavailable but you have its redo logs to apply to the standby. Apply any and all possible logs to the standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

then issue:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A forced failover occurs when the primary fails and redo logs are not all available. Some redo may be lost as you activate the standby. Key commands here include:

ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

An archive log gap occurs whenever a primary database archives the online redo log, but it is not also archived to the standby database. 9i automatically recovers archive log gaps for you. Set this up by initialization parameters that assign service names to the primary and standby databases:

FAL_CLIENT = ‘standby_name’

FAL_SERVER = ‘primary_name’

9i includes a Managed Recovery Process (MRP), a process that automatically applies archived redo logs on the standby database server to the standby database. Put a standby database into MRP mode by running:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Resumable Space Allocation

Resumable Space Allocation

Resumable space allocation suspends a long-running operation in event of a space allocation error so that you can fix the problem. Then Oracle automatically resumes the long-running operation. Operations that are resumable are:

• Queries that run out of temporary sort space
• DML – INSERT, UPDATE, and DELETE statements
• DDL – CREATE TABLE AS SELECT, ALTER TABLE, CREATE INDEX, ALTER INDEX, and statements that create materialized views or materialized view logs
• Import/Export – SQL*Loader operations

The space problems resumable space allocation addresses are:

• Out of space
• Maximum extents reached
• Space quota exceeded

To use resumable space allocation, set your session as resumable, and ensure you have the RESUMABLE system privilege:

ALTER SESSION ENABLE RESUMABLE ;

GRANT RESUMABLE TO my_id ;

If an operation is suspended, Oracle writes an error to its Alert log. Views USER_RESUMABLE and DBA_RESUMABLE contain info on the error, or you can run DBMS_RESUMABLE.SPACE_ERROR_INFO for error details. Fix the error, then Oracle will automatically resume the suspended statement.

Oracle also provides the AFTER SUSPEND system event to handle resumable space errors. You could use this, for example, in a trigger with custom code:

CREATE OR REPLACE TRIGGER my_space_handler

AFTER SUSPEND ON DATABASE...