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.
[sourcecode language=”sql”]
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;
/
[/sourcecode]
2. Test the Procedure
[sourcecode language=”sql”]
SQL> execute sys.proc_control_to_trace;
PL/SQL procedure successfully completed.
[/sourcecode]
3. Create Event Trigger
[sourcecode language=”sql”]
— AFTER STARTUP TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
AFTER STARTUP ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/
[/sourcecode]
[sourcecode language=”sql”]
— BEFORE SHUTDOWN TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/
[/sourcecode]
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.
