Control File Management in Oracle

1. View Control File Information

SQL * Plus: Release 9.2.0.1.0-Production on Saturday January 5 10:12:00 2008

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

SQL> conn sys/as sysdba
Enter the password :*****
Connected.
SQL> select name from v $ controlfile;

NAME
--------------------------------------------------------------------------------
D: \ ORACLE \ ORADATA \ HQH \ CONTROL01.CTL
D: \ ORACLE \ ORADATA \ HQH \ CONTROL02.CTL
D: \ ORACLE \ ORADATA \ HQH \ CONTROL03.CTL

SQL> select value from v $ parameter where name = 'control _ files ';

VALUE
--------------------------------------------------------------------------------
D: \ oracle \ oradata \ hqh \ CONTROL01.CTL, D: \ oracle \ oradata \ hqh \ CONTROL02.CTL, D: \ ora
Cle \ oradata \ hqh \ CONTROL03.CTL

View the data record information in the data file in the control file: (Note that DATAFILE must be capitalized; otherwise, the message "unselected rows" is displayed ")

SQL> select type, record_size, records_total, records_used from v $ controlfile_record_section
Where type = 'datafile ';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED
--------------------------------------------------------
DATAFILE 180 100 13

2. Create copies of control files and backup control files

(1). Create a copy of the Control File

In this example, you will create a new control file copy for the database hqh d: \ oracle \ backup \ hqh \ control02.ctl

SQL> alter system set control_files = 'd: \ oracle \ oradata \ hqh \ control01.ctl ', 'd: \ oracle \ oradata \ hqh \ control02.ctl ',
'D: \ oracle \ backup \ hqh \ control02.ctl'
Scope = spfile

SQL>/

System changed

Then shut down the database with shutdown. because the above control file D: \ oracle \ backup \ hqh \ control02.ctl does not exist, copy the 'd: \ oracle \ oradata \ hqh \ control02.ctl 'file to D: \ oracle \ backup \ hqh.

SQL> shutdown;
The database has been closed.
The database has been detached.
ORACLE routine disabled

Start the database with startup, and the database starts normally.

SQL> startup
The ORACLE routine has been started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened.

Note: If you forget to copy the file to the specified location, an error will be reported during startup.

SQL> startup;
The ORACLE routine has been started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205 :?????????????????????

Check the control file information. The new control file is already in use.

SQL> select name from v $ controlfile;

NAME
--------------------------------------------------------------------------------
D: \ ORACLE \ ORADATA \ HQH \ CONTROL01.CTL
D: \ ORACLE \ ORADATA \ HQH \ CONTROL02.CTL
D: \ ORACLE \ ORADATA \ HQH \ CONTROL03.CTL
D: \ ORACLE \ BACKUP \ HQH \ CONTROL02.CTL

Directly, a new control file copy has been created.

(2) backup control file

SQL> alter database backup controlfile to 'd: \ oracle \ backup \ hqh \ control. bkp ';

The database has been changed.

That's simple. The backup control file is successful! Open the directory where the file is located.

(3). Use copies to restore control files

A. First Use shutdown to close the instance, and then disable SQL * Plus.

B. Open the 'd: \ oracle \ oradata \ hqh \ control01.ctl 'file, modify it at will, and save it.

C. Start SQL * Plus and use sysdba to connect to the database hqh.

D. Start the database instance hqh with startup. The system returns the ORA-00205 error.

E. Use the data file copy for restoration. shutdown first.

F. delete the file 'd: \ oracle \ oradata \ hqh \ control01.ctl '. Replace control02.ctl with control01.ctl.

H. start up again.