v Managing control files
· What is a control file
· Backing up a control file:
Back up the control file to a binary file (duplicate of existing control file) using the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
Produce SQL statements that can later be used to re-create your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Or
alter database backup controlfile to trace as '/some/arbitrary/path';
· Creating Initial Control file – During the time of the database creation.
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u03/oracle/prod/control03.ctl)
If files with the specified names currently exist at the time
of database creation, you must specify the CONTROLFILE REUSE clause in the
CREATE DATABASE statement, or else an error occurs. Also, if the size of the
old control file differs from the SIZE parameter of the new one, you cannot use
the REUSE clause.
· Creating Additional Copies, Renaming, and Relocating Control Files
Adding Additional Copies:
To add a multiplexed copy of the current control file or to rename a control file:
1. Shut down the database.
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename.
4. Restart the database.
· When to create the new controlfile
· All control files for the database have been permanently damaged and you do not have a control file backup.
· You want to change the database name.
For example, you would
change a database name if it conflicted with another database name in a
distributed environment.
· Steps for Creating New Controlfile:
The CREATE CONTROLFILE statement can potentially damage
specified datafiles and redo log files. Omitting a filename can cause loss of
the data in that file, or loss of access to the entire database. Use caution
when issuing this statement and be sure to follow the instructions in
"Steps for Creating New Control Files".
1. Make a list of all datafiles and redo log files of the database.
If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that comprise the SYSTEM tablespace, you might not be able to recover the database.
1. Shut down the database
If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
2. Back up all datafiles and redo log files of the database
3. Start up a new instance, but do not mount or open the database:
STARTUP NOMOUNT
4. Create a new control file for the database using the CREATE CONTROLFILE statement.
When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.
5. Recover the database if necessary. If you are not recovering the database, skip next step
If you are creating the control file as part of recovery,
recover the database. If the new control file was created using the NORESETLOGS
clause (step 5), you can recover the database with complete, closed database
recovery.
If the new control file was created using the RESETLOGS
clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or
archived redo logs or datafiles, use the procedures for recovering those files.
6. Open the database using one of the following methods:
If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
ALTER DATABASE OPEN;
If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
v Recovering from Control File Corruption Using a Control File Copy
This procedure assumes that one of the control files
specified in the CONTROL_FILES parameter is corrupted, that the control file
directory is still accessible, and that you have a multiplexed copy of the
control file.
· With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
% cp /u03/oracle/prod/control03.ctl /u02/oracle/prod/control02.ctl
Start SQL*Plus and open the database:
STARTUP
v Recovering from Permanent Media Failure Using a Control File Copy
This procedure assumes that one of the control files
specified in the CONTROL_FILES parameter is inaccessible due to a permanent
media failure and that you have a multiplexed copy of the control file.
With the instance shut down, use an operating system command
to copy the current copy of the control file to a new, accessible location:
% cp /u01/oracle/prod/control01.ctl /u04/oracle/prod/control03.ctl
· Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u04/oracle/prod/control03.ctl)
· Start SQL*Plus and open the database:
SQL> STARTUP
v Dropping Control Files
You want to drop control files from the database, for example, if the location of a control file is no longer appropriate. Remember that the database should have at least two control files at all times.
1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name.
3. Restart the database.
This operation does not physically delete the unwanted
control file from the disk. Use operating system commands to delete the
unnecessary file after you have dropped the control file from the database.
v Control Files Data Dictionary Views
V$DATABASE
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$PARAMETER
· What is a control file
· Backing up a control file:
Back up the control file to a binary file (duplicate of existing control file) using the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
Produce SQL statements that can later be used to re-create your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Or
alter database backup controlfile to trace as '/some/arbitrary/path';
· Creating Initial Control file – During the time of the database creation.
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u03/oracle/prod/control03.ctl)
If files with the specified names currently exist at the time
of database creation, you must specify the CONTROLFILE REUSE clause in the
CREATE DATABASE statement, or else an error occurs. Also, if the size of the
old control file differs from the SIZE parameter of the new one, you cannot use
the REUSE clause.· Creating Additional Copies, Renaming, and Relocating Control Files
Adding Additional Copies:
To add a multiplexed copy of the current control file or to rename a control file:
1. Shut down the database.
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename.
4. Restart the database.
· When to create the new controlfile
· All control files for the database have been permanently damaged and you do not have a control file backup.
· You want to change the database name.
For example, you would
change a database name if it conflicted with another database name in a
distributed environment.· Steps for Creating New Controlfile:
The CREATE CONTROLFILE statement can potentially damage
specified datafiles and redo log files. Omitting a filename can cause loss of
the data in that file, or loss of access to the entire database. Use caution
when issuing this statement and be sure to follow the instructions in
"Steps for Creating New Control Files".1. Make a list of all datafiles and redo log files of the database.
If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that comprise the SYSTEM tablespace, you might not be able to recover the database.
1. Shut down the database
If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
2. Back up all datafiles and redo log files of the database
3. Start up a new instance, but do not mount or open the database:
STARTUP NOMOUNT
4. Create a new control file for the database using the CREATE CONTROLFILE statement.
When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.
5. Recover the database if necessary. If you are not recovering the database, skip next step
If you are creating the control file as part of recovery,
recover the database. If the new control file was created using the NORESETLOGS
clause (step 5), you can recover the database with complete, closed database
recovery.
If the new control file was created using the RESETLOGS
clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or
archived redo logs or datafiles, use the procedures for recovering those files.6. Open the database using one of the following methods:
If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
ALTER DATABASE OPEN;
If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
v Recovering from Control File Corruption Using a Control File Copy
This procedure assumes that one of the control files
specified in the CONTROL_FILES parameter is corrupted, that the control file
directory is still accessible, and that you have a multiplexed copy of the
control file.· With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
% cp /u03/oracle/prod/control03.ctl /u02/oracle/prod/control02.ctl
Start SQL*Plus and open the database:
STARTUP
v Recovering from Permanent Media Failure Using a Control File Copy
This procedure assumes that one of the control files
specified in the CONTROL_FILES parameter is inaccessible due to a permanent
media failure and that you have a multiplexed copy of the control file.
With the instance shut down, use an operating system command
to copy the current copy of the control file to a new, accessible location:% cp /u01/oracle/prod/control01.ctl /u04/oracle/prod/control03.ctl
· Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u04/oracle/prod/control03.ctl)
· Start SQL*Plus and open the database:
SQL> STARTUP
v Dropping Control Files
You want to drop control files from the database, for example, if the location of a control file is no longer appropriate. Remember that the database should have at least two control files at all times.
1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name.
3. Restart the database.
This operation does not physically delete the unwanted
control file from the disk. Use operating system commands to delete the
unnecessary file after you have dropped the control file from the database.v Control Files Data Dictionary Views
V$DATABASE
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$PARAMETER
No comments:
Post a Comment