Backup Controlfile to Trace : Automate using Triggers

PL/SQL Procedure to Automatically Backup ControlFile to trace whenever you are shutting down or starting up the database.

‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’ command at
database startup or shutdown.

For DBA’s requiring to take regular backup copies of the controlfile.It is highly recommended to systematically keep a backup of the controlfiles as soon as the structure of a database occurs.The triggers before or after events are appropriate to automate administrative tasks.

You can in theory use any commands that can be put in pl/sql
scripts, and automate the execution of them.

*******************************************************
*** Technical method
*******************************************************

1. Create the procedure that executes the ‘ALTER DATABASE
BACKUP CONTROLFILE TO TRACE’ statement.

CREATE OR REPLACE PROCEDURE proc_control_to_trace AS
 cursor1 INTEGER;
 BEGIN
 cursor1 := dbms_sql.open_cursor;
 dbms_sql.parse(cursor1, 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE',
 dbms_sql.native);
 dbms_sql.close_cursor(cursor1);
 END;
/

2. Test the Procedure

SQL> execute sys.proc_control_to_trace;
PL/SQL procedure successfully completed.

3.      Create Event Trigger

-- AFTER STARTUP TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
AFTER STARTUP ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/
-- BEFORE SHUTDOWN TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/

On older versions of Oracle you may get an error on BEFORE SHUTDOWN triggers.
Those versions are since long desupported. The workaround is to use an AFTER STARTUP trigger.

4. Shutdown or start the database to check that the trace file is generated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s