| Lesson 5 | Backing up a control file |
| Objective | Back up a control file to trace. |
Backing up control file
To protect yourself from the complete loss of your database control file further,
Oracle enables you to back up a control file while the database is running. Usually you do this by telling Oracle to generate the
necessary commands to re-create the control file in the event that all multiplexed copies are lost.
The following SQL command backs up a control file by generating the commands necessary to re-create it:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
You can issue this command from either SQL*Plus or Server Manager. You need to have the ALTER DATABASE system privilege to issue it. As a result of executing this command, Oracle will generate a CREATE CONTROLFILE statement and place it in a trace file . The trace file will be in the directory pointed to by the user_dump_dest parameter.
View the Code below to see an example.
*** SESSION ID:(13.9826) 1999.11.08.16.34.35.860
*** 1999.11.08.16.34.35.860
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "COIN" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
...
'E:\ORACLE\ORADATA\COIN\TEST_1.DBF'
CHARACTER SET WE8ISO8859P1
;
# Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('E:\ORACLE\ORA81\DATABASE\SNCFCOIN.ORA');
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
To find the file containing the generated command, you need to look at the time stamp on all the files in the user_dump_dest directory. Find the
file (or files) created at about the time that you issued the ALTER DATABASE command and look in those files. One of them should contain the CREATE CONTROLFILE command. The following simulation walks you through the process of backing up a control file to trace:
- This is the SQL*Plus screen. You have already connected to the database as the SYSTEM user.
Now issue the alter database backup controlfile to trace; command. Be sure to press the ENTER key
- SQL prompt
- alter database backup controlfile to trace
- Syntax error. Type alter database backup controlfile to trace; then press Enter
Back up to trace
- This is the SQL*Plus screen. You have already connected to the database as the SYSTEM user. Now issue the
alter database backup
controlfile to trace; command. Be sure to press the ENTER key.
- The command has executed successfully. Now you need to find the file. Normally you would press Alt + Tab to switch to a window showing the
folders on your hard drive. For the purpose of this simulation, press your Enter key and we will perform this task for you.
- Trace files usually end up under the Admin folder, so double-click that to open it.
- Under the Admin folder, you will find a directory for each database on your system. You are working with the COIN database, so double-click
the coin folder.
- Trace files are written to the user dump destination. That's probably the udump folder, so double-click that.
- You now see a list of trace files for the COIN database. Assume that it was 5:32 p.m. on November 8 when you backed up the control file. Which
of these trace files should you look at? Click the most likely file.
- Because ora00270.trc was created at 5:32 p.m. on November 8, and because that's when you issued the command to back up the
control file, you have found the file most likely to hold the results.
In the next lesson, you will learn how to create a new control file from scratch.
Backing Control File - Exercise