Subject:  How to Automate Controlfile Backup at Database Startup 
Type:  BULLETIN 
Status:  PUBLISHED 
 Content Type:  TEXT/PLAIN 
Creation Date:  18-APR-2000 
Last Revision Date:  12-JUN-2000 
Language:  USAENG 
 

PURPOSE

This note explains how to automatically launch the 
'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' command at 
database startup.

SCOPE & APPLICATION

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.

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

1/ Create the procedure that executes the 'ALTER DATABASE
   BACKUP CONTROLFILE TO TRACE' statement.

2/ Create the 'AFTER STARTUP ON DATABASE' trigger that executes the 
   procedure.

3/ Shutdown or startup to test if the trigger generates the
   trace file that contains the 'CREATE CONTROLFILE' statement.

*******************************************************
*** Example
*******************************************************

1/ Create the procedure:

SQL> CREATE OR REPLACE PROCEDURE proc_control_to_trace AS
  2   cursor1 INTEGER;
  3  BEGIN
  4   cursor1 := dbms_sql.open_cursor;
  5   dbms_sql.parse(cursor1, 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE',
      dbms_sql.native);
  6   dbms_sql.close_cursor(cursor1);
  7  END;
  8  /
Procedure created.

Test that the procedure executes properly:

SQL> execute sys.proc_control_to_trace;

PL/SQL procedure successfully completed.

2/ Create the event trigger:

SQL> CREATE OR REPLACE TRIGGER db_startup_control_trace
  2   AFTER STARTUP ON DATABASE
  3  BEGIN
  4   sys.proc_control_to_trace;
  5  end;
  6  /
Trigger created.

3/ Startup the database to check that the trace file is 
   generated:

Tue Apr 18 16:12:30 2000
Starting ORACLE instance (force)
License high water mark = 3
...
alter database  mount
Tue Apr 18 16:12:37 2000
...
Completed: alter database  mount
Tue Apr 18 16:12:37 2000
alter database open 
...
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Apr 18 16:12:42 2000
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Tue Apr 18 16:12:42 2000
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Completed: alter database open 

In the trace file,
Dump file /8i/ora815/admin/hp11_815/udump/ora_183.trc
...
*** SESSION ID:(9.1) 2000.04.18.16.12.42.448
*** 2000.04.18.16.12.42.448
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "HP11_815" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 8658
LOGFILE ...

Errors
------
If you decide to fire the trigger before shutdown,
the shutdown process does not proceed successfully.

SQL> CREATE OR REPLACE TRIGGER db_shutdown_control_trace
  2   BEFORE SHUTDOWN ON DATABASE
  3   declare
  4   cursor1  INTEGER;
  5  BEGIN
  6   cursor1 := dbms_sql.open_cursor;
  7    dbms_sql.parse(cursor1, 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE',dbm;
  8     dbms_sql.close_cursor(cursor1);
  9  end;
 10  /

Trigger created.

SVRMGR> connect internal
Connected.
SVRMGR> shutdown immediate
ORA-00600: internal error code, arguments: [2801], [], [], [], [], [], [], []

...
and does not end.

In the alert.log, you find the following messages:

Shutting down instance (immediate)
License high water mark = 4
Tue Apr 18 16:35:30 2000
ALTER DATABASE CLOSE NORMAL
Tue Apr 18 16:35:30 2000
SMON: disabling tx recovery
Tue Apr 18 16:35:30 2000
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Tue Apr 18 16:35:30 2000
ORA-1155 signalled during: ALTER DATABASE BACKUP CONTROLFILE TO TRACE...
Tue Apr 18 16:35:30 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_14477.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01155: the database is being opened, closed, mounted or dismounted
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 5
Tue Apr 18 16:35:30 2000
LGWR: prodding the archiver
Tue Apr 18 16:35:30 2000
ARC0: received prod
ARC0: Beginning to archive log# 2 seq# 11478
...
Tue Apr 18 16:35:31 2000
Thread 1 closed at log sequence 11479
Tue Apr 18 16:35:31 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_14477.trc:
ORA-00600: internal error code, arguments: [2801], [], [], [], [], [], [], []
Tue Apr 18 16:35:34 2000
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Apr 18 16:35:34 2000
ORA-600 signalled during: ALTER DATABASE CLOSE NORMAL...
Tue Apr 18 16:35:34 2000
ALTER DATABASE DISMOUNT
Tue Apr 18 16:35:34 2000
Errors in file /8i/ora815/admin/hp11_815/bdump/smon_9460.trc:
ORA-00376: file 1 cannot be read at this time
ORA-01110: data file 1: '/8i/ora815/oradata/hp11_815/system01.dbf'
Tue Apr 18 16:35:34 2000
Errors in file /8i/ora815/admin/hp11_815/bdump/smon_9460.trc:
ORA-00376: file 1 cannot be read at this time
ORA-01110: data file 1: '/8i/ora815/oradata/hp11_815/system01.dbf'


Conclusion
----------

To have the ALTER DATABASE BACKUP CONTROLFILE TO TRACE
statement processed automatically, create a trigger AFTER STARTUP.


References
----------

[NOTE:69493.1]
[BUG:1297939]