SkillBuilders | Oracle 9i Administration | Lesson 18: Managing Control Files

Instructor's Notes

(If applicable)

Lesson 18: Managing Control Files
This hands-on lesson will teach you how to protect, add, backup and recreate control files.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

In this course, the control file was introduced in the lessons on database architecture. Let's spend a minute to review the major concepts…
The control file is a relatively small file – typically a few megabytes – which contains control information for the database. This information includes the name of the database, the name and location of the database files, the names of the online and archived redo log files and information required for database recovery.
The control file is read at startup time (specifically, when the database is mounted). Reading the control file gives the instance the names and locations of the datafiles and redo log files. If the control file is not available then the instance will not mount (i.e start). The control file is also updated during normal database operations:

All structural changes such as adding or removing datafiles are recorded in the control file.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select name
2 from v$controlfile;

NAME
------------------------------------
C:\ORACLE\ORADATA\DAVE\CONTROL01.CTL
C:\ORACLE\ORADATA\DAVE\CONTROL02.CTL

Instructor's Notes

(If applicable)

The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of instance failure. i.e. In the event of instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file. Refer to the lessons on Backup and Recovery for more information.
The archive process (ARCH) will record the name and location of archived redo logs in the control file.
RMAN processes update the control file to record backup and recovery operations. Refer to the Introduction to RMAN lesson for more information.

As we will learn in this lesson, it is easy for the database administrator to keep more than one copy of the control file. These copies should obviously be on separate disk drives to guard against loss of the entire disk. Query the V$CONTROLFILE view to determine the name and location of current in-use control files.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The database administrator has the following responsibilities related to the control file:

To protect the control file. Since we cannot start or run the database without the it, the control file is of utmost importance. There are three levels of protection:

The first level of protection comes from maintaining multiple mirror-image copies on separate disk devices. As we will learn in this lesson, this is easily accomplished with the CONTROL_FILES initialization parameter.
The second level of protection is to backup the control file. Since the control is updated to record any structural change made to the database (such as adding a datafile with CREATE TABLESPACE), it should be backed up whenever a structural change occurs. Oracle9i Release 2 RMAN will do this automatically. Also, since the control file is regularly updated during normal database operations, include control file backups in all regularly scheduled backups.
The third level of protection comes at the operating system level. Prohibit all access to this file from all OS users except for the owner of the database, typically a user named "oracle".

Refer to the lesson on Backup and Recovery for detailed instructions on backing up the control file with RMAN.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The administrator will be responsible for restoring the control file if all copies are lost. This should not happen! Remember, you will have a minimum of two, preferably three or four copies of the control file on separate disk devices. However, if you do lose all copies, you can restore from backups or recreate with the CREATE CONTROLFILE command. (We will study the CREATE CONTROLFILE command in this lesson.)
Another reason to use the CREATE CONTROLFILE command is to recreate the control file to change the "fixed" parameter values specified on the CREATE DATABASE command. A little background information on CREATE DATABASE command (which is covered in detail in the Creating a New Database lesson): The control file is created by the CREATE DATABASE command. The parameters specified on CREATE DATABASE cause Oracle to reserve records (also known as "slots") in the control file. For example, "MAXLOGFILES 5" will reserve 5 slots for recording the names of redo logs. If, over time, you determine that the database will perform better with 10 log files, you will need to recreate the control file – which a larger MAXLOGFILES value – to add more slots.
Yet another reason to situation that would require control file to be recreated would be when moving a database to a new server where the directory structure does not match the original server.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

We have established that it is critical to maintain more than one – at least two – copies of the control file, on separate disk devices. Follow the steps summarized here and demonstrated in the next few pages if your database does not have enough control files or you would like to move a control file to a new disk device.

Other Text:
(Examples or comments displayed on slide, if any).

See the following page for an example

Instructor's Notes

(If applicable)

Start by determining the current in-use control files. This can be accomplished with a query on the V$PARAMETER2 view as shown above. V$PARAMETER2 is a new view available with Oracle9i. It is similar to the V$PARAMETER view in that it shows the current, in-use initialization parameter values. The difference is that for parameters such as CONTROL_FILES that contain multiple values it contains one row per value. The ORDINAL column allows you to extract the parameter values in the order specified in the parameter file.

As I demonstrated in the review earlier in this lesson, another method of locating current control files is to query the V$CONTROLFILE view:
SQL> select name
2 from v$controlfile;

NAME
--------------------------------------
C:\ORACLE\ORADATA\DAVE\CONTROL01.CTL
C:\ORACLE\ORADATA\DAVE\CONTROL02.CTL

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select value
2 from v$parameter2
3 where name = 'control_files'
4 order by ordinal;

VALUE
-------------------------------------
C:\oracle\oradata\dave\CONTROL01.CTL
C:\oracle\oradata\dave\CONTROL02.CTL

Instructor's Notes

(If applicable)

The next step is to cleanly shutdown the database. Use SHUTDOWN, SHUTDOWN IMMEDIATE or SHUTDOWN TRANSACTIONAL. This is required to get a consistent copy of the control file. Do not use SHUTDOWN ABORT because that would cause the control files to be out of sync with the datafiles .
Next, use the operating system to copy an existing control file to the new location.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> $copy C:\ORACLE\ORADATA\DAVE\CONTROL01.CTL
D:\ORACLE\ORADATA\DAVE\CONTROL03.CTL
1 file(s) copied.

Create a new copy of the control file.

Instructor's Notes

(If applicable)

Next, we need to update the CONTROL_FILES parameter in the initialization parameter file to reflect the new, additional control file. As of Oracle9i, the default initialization parameter file is called "server parameter file" (SPFILE). Its is a binary file and is updated with the ALTER SYSTEM command as demonstrated above.
Backup the SPFILE
- I recommend that you backup the server parameter file (SPFILE) before you update it. Why? Because I discovered (the hard way) that the ALTER SYSTEM command will let you put invalid file names in the CONTROL_FILES parameter and thus cause a failure when you attempt to restart the database. It is easy to backup the SPFILE; use the CREATE PFILE FROM SPFILE command as shown above (yes, this command works with the database shut down). The text parameter file is created in the default directory, $ORACLE_HOME\database on a Windows server.

Notes continues on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Backup SPFILE before changing

SQL> create pfile from spfile;
File created.

Start DB to change SPFILE. Use NOMOUNT so control file is not opened
Update SPFILE SQL> startup nomount

SQL> alter system
2 set control_files = 'C:\oracle\oradata\dave\CONTROL01.CTL' ,
3 'C:\oracle\oradata\dave\CONTROL02.CTL' ,
4 'D:\oracle\oradata\dave\CONTROL03.CTL'
5 scope = spfile
6 /

Shutdown the database and restart

System altered.
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup

Instructor's Notes

(If applicable)

Update the SPFILE
The database must be in NOMOUNT mode for this next step. We do not want to MOUNT the database at this time because the control file would then be opened and updated – and we have not yet told Oracle about our additional new control file. The ALTER SYSTEM command, as shown above, will update the CONTROL_FILES parameter in the SPFILE.
CAUTION: Next, shutdown and restart the database. This is a critical step because when we restart the database the updated parameter file is read and the instance recognizes the existence of the new control file. Do not do an “ALTER DATABASE MOUNT” and “ALTER DATABASE OPEN” because the new control file is not recognized. If you do this, the database will open without the new control file in effect. Therefore the new control file will not be kept in sync with the existing control files. The next time you shutdown and startup, the new control file will be opened, but Oracle will give a ORA-00214: control file ‘xxx’ version vvv inconsistent with file ‘yyy’ version www. You can recover from this error my doing a shutdown and recopying one of the original control files to the new control file .
Recovering from an Error
- If you experience errors when you try to restart the database, shutdown the database (you will probably be in nomount stage), restore the original copy of the server parameter file, then restart the database. Use the CREATE SPFILE command to restore the server parameter file:
- SQL> create spfile from pfile;

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Add Control File Workshop
1. Determine the control files currently in use.
2. Add another control file to your database.
3. Check that the new control file is in use.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

In addition to protecting the control file, backing up the control file is also a primary responsibility of the DBA. This can be accomplished:

With user-managed backups.
With server-managed (RMAN) backups. Refer to the lesson Introduction to RMAN for more information.
With the ALTER DATABASE BACKUP CONTROLFILE command.

The example shown above uses the "TO TRACE" option to create a script to recreate the control file. The script is written to the USER_DUMP_DEST directory. Unfortunately, the name of the script is not intuitive or obvious. It will typically follow the format "SID_ora_SPID.trc", where SID is the database system id and SPID is the session process id of the session that executed the ALTER DATABASE command. The easiest method of determining which file in the USER_DUMP_DEST directory is the script file is by the date/time stamp. However, we can get more precise. The SID can be found with this query:
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dave
Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter database backup controlfile to trace;

Database altered.

SQL> show parameter user_dump_dest

NAME VALUE
---------------------------------- --------------------------
user_dump_dest C:\oracle\admin\class\udump

Instructor's Notes

(If applicable)

The SPID can be found – unless you are connected as SYSDBA - by executing this query:
SQL> select a.spid
2 from v$process a, v$session b
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid');
SPID
------------
1752

If the session that ran the ALTER DATABASE command was connected as SYSDBA, the SPID's of all sessions are returned. In this case, you are back to searching using the file date/time stamp.

The following pages in this lesson will describe the process of using this script to recreate a control file.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A new control file can be created with the CREATE CONTROLFILE command. This can be useful when you have lost all copies of the current control file, you are moving the database to a server with different mount points or directories or you want to change some of the "fixed" parameters on the CREATE DATABASE command.
Create a new control file carefully: remember that you cannot start your database without a valid control file. It is recommended to perform a full cold backup before attempting this procedure. This is because bad things can happen; e.g. omitting a datafile name in the CREATE CONTROLFILE command can cause loss of data.
Note that the Oracle9i Administration Guide and Metalink (http://metalink.oracle.com) also have detailed descriptions of this procedure. See document Note:1012929.6

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The script created by the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command will not run without some editing. (The next two pages contain an excerpt of this script.)

First, copy the script from the USER_DUMP_DEST directory to a directory easily accessible from SQL*Plus. Rename the script to something like "CREATE_CONTROLFILE.SQL".
Next, delete all the header and comment records up to, but not including, the STARTUP NOMOUNT command.
There are two versions of the CREATE CONTROLFILE command in the file. The first should be used if you have all online redo logs available. It is noted with this comment "Set#1. NORESETLOGS case". The second version, noted "Set #2. RESETLOGS case" will not attempt to apply any changes found in the redo logs. Use this version if you have lost the redo log files. Note that detailed discussions of RESETLOGS and NORESETLOGS can be found in Oracle’s documentation. It is not necessary at this time to have a complete understanding of this.
Next, if the database is running, shutdown the database. It is recommended to perform a full cold backup before continuing. This will provide a fallback point should things go awry.

Note that the Oracle9i Administration Guide and Metalink (http://metalink.oracle.com) also have detailed descriptions of this procedure. See Metalink document 1012929.6

Other Text:
(Examples or comments displayed on slide, if any).

Procedure continues…

Instructor's Notes

(If applicable)

Excerpt from the BACKUP CONTROLFILE TO TRACE trace file:
Dump file c:\oracle\admin\dave\udump\dave_ora_1752.trc
Fri Oct 03 13:05:25 2003
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 1752, image: ORACLE.EXE

*** SESSION ID:(12.430) 2003-10-03 13:05:25.000
*** 2003-10-03 13:05:25.000
# The following are current System-scope REDO Log Archival related
# parameters and can be included in the database initialization file.
#
# LOG_ARCHIVE_DEST=''
# LOG_ARCHIVE_DUPLEX_DEST=''
#
# LOG_ARCHIVE_FORMAT=ARC%S.%T
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_MAX_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\oradata\dave\archive\'
# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# LOG_ARCHIVE_DEST_STATE_1=ENABLE

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

# Below are two sets of SQL statements, each of which creates a new
# control file and uses it to open the database. The first set opens
# the database with the NORESETLOGS option and should be used only if
# the current versions of all online logs are available. The second
# set opens the database with the RESETLOGS option and should be used
# if online logs are unavailable.
# The appropriate set of statements can be copied from the trace into
# a script file, edited as necessary, and executed when there is a
# need to re-create the control file.
#
# Set #1. NORESETLOGS case
#
# 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 <--[Delete all records before this]
CREATE CONTROLFILE REUSE DATABASE "DAVE" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\DAVE\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\DAVE\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\DAVE\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\ORADATA\DAVE\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\DAVE\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\DAVE\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\DAVE\DRSYS01.DBF',
'C:\ORACLE\ORADATA\DAVE\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\DAVE\INDX01.DBF',
'C:\ORACLE\ORADATA\DAVE\ODM01.DBF',
'C:\ORACLE\ORADATA\DAVE\TOOLS01.DBF',
'C:\ORACLE\ORADATA\DAVE\XDB01.DBF',
'C:\ORACLE\ORADATA\DAVE\O1_MF_USERS_ZMNKR400_.DBF'
CHARACTER SET WE8MSWIN1252
;

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Description of steps to recreate the control file, continued:

If any control files exist, rename or move them so they do not interfere with the CREATE CONTROLFILE command.
Next, use SQL*Plus to login with SYSDBA privileges and execute the script. Check for errors. If everything is OK, shutdown the database and perform a full cold backup.

Note that since the database parameter file contains the location of the control files, the script above will create all control file copies.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect system/dave as sysdba
Connected.
SQL> shutdown

SQL> @create_controlfile
ORACLE instance started.

Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 96468992 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes

Control file created.

Instructor's Notes

(If applicable)

This is an optional workshop. The instructor will assign only if time permits.
1. Execute the ALTER DATABASE command that creates the CREATE CONTROLFILE command script.
2. Shutdown your database.
3. Rename or move all control files to an alternate location. Do not delete, lose or edit the control files (in case we need them for recovery).
4. Try to start your database. The startup will fail because the control files cannot be found.
5. Locate the error message in the alert file.
6. Recreate all your control files and startup your database by executing the script created in step 1.
7. Shutdown and restart your database. The startup should succeed. If it does not, shutdown the database, restore the original control files and bounce the database.

Other Text:
(Examples or comments displayed on slide, if any).