I. Controlling the role of a file:1. The physical state of the database is recorded. 2. Maintain database consistency . The control file records the SCN number of the database system, the SCN number of the data file, and the starting SCN number of the data file header, if the three SCN numbers agree that the database can be started. If the inconsistency is going to recover. 3. define the location and number of control files in the parameter file. The control file has a minimum of 1, a maximum of 8, and multiple files are mirrored relationships. Defining control Files*.control_files= '/opt/oracle/oradata/orcl/control01.ctl ', '/opt/oracle/oradata/orcl/control02.ctl ', '/opt/ Oracle/oradata/orcl/control03.ctl '4. The control file is binary file generally not more than 100m. 5. The database starts using the control file from the Mount stage ,6. Control files can only connect to one database. 7. Missing to restore
two. The control file contains the following content:database name and identifier (DB ID)time stamp for database creationtable Space's namelocation and name of data files and online redo log filessequence number of the current online redo log fileCheckpoint Informationstart and end of rollback segmentsarchived information for online redo logsBackup Information
three. Dump the control file:Since the control file is a binary file, it is not possible to open the lookup directly, but the following command, described in the previous chapter, can be used to dump the contents of the control file for easy viewing: Alter session SET events ' immediate trace name Controlf level 12 ‘; The following is a dump test from Oracle Database 11g: sql> alter session SET events ' immediate trace name Controlf level 12 '; Session altered. #备份控制文件到trace文件中SQL > ALTER DATABASE backup Controlfile to trace;
Database altered.
Sql> Select value from V$diag_info where Name= ' Default Trace File '; VALUE--------------------------------------------------------------------------------/u01/app/oracle/diag/ Rdbms/orcl/orcl/trace/orcl_ora_4929.trc
Attention:starting with 11g, you can get the name of the current session dump file via V$diag_info。
Four. Control file Check Database consistency:1. The control file records the SCN number of the database system, the SCN number of the data file, and the starting SCN number in the header column of the data file, if the three SCN numbers agree that the database can be started. If the inconsistency is going to recover. #系统scn号SQL > select checkpoint_change# from V$database;
checkpoint_change#------------------469717# file SCN number sql> select checkpoint_change# from V$datafile;
checkpoint_change#------------------469717 469717 469717 469717
Start SCN number sql> select checkpoint_change# from V$datafile_header;
checkpoint_change#------------------469717 469717 469717 469717
Conversion of 2.SCN to time: #全局检查点SQL > Alter system checkpoint;
System altered. #创建一个函数SQL > Create or Replace function Scn_to_timestamp (QUERY_SCN in number) return Timestampis Externalname "Ktfexscntot" with CONTEXT PARAMETERS (context, QUERY_SCN Ocinumber,return) LIBRARY dbms_tran_lib;/ 2 3 4 5 6 7 8 9
Function created. #系统scnSQL > select checkpoint_change# from V$database;
checkpoint_change#------------------473363#scn turn into time sql> select Scn_to_timestamp (473363) from dual;
Scn_to_timestamp (473363)---------------------------------------------------------------------------16-jun-14 06.16.14.000000000 AM
#时间转成scnSQL > select TIMESTAMP_TO_SCN (' 16-jun-14 06.16.14.000000000 AM ') from dual;
TIMESTAMP_TO_SCN (' 16-jun-1406.16.14.000000000am ')------------------------------------------------- 473362
Five. Control file diversification (increase or decrease control file data volume)1. Planning principle: Multiple reuse , define multiple control files, put on separate disks.
2. Number and location of management control files:SPFile or pfile can be used to manage the number and location of control files.spfile steps:Modify the SPFile parameter Control_fileConsistency Close Databaseincrease or decrease control filesstarting a database with SPFileverify the results.
Example: Modifying the number of control files
--Reduce control files sql> alter system set control_files= '/opt/oracle/oradata/orcl/control01.ctl ' scope=spfile;
System altered. or
--Add control files:sql> alter system set control_files= '/opt/oracle/oradata/orcl/control01.ctl ', '/opt/oracle/oradata/orcl/control02.ctl ','/opt/oracle/oradata/orcl/control03.ctl ' scope=spfile;
System altered.
--Close the database
sql> shutdown immediate
--copy control files to add directories or delete control files [email protected] disk3]$ CP/u01/app/oracle/oradata/prod/disk3/Control01.ctl/u01/app/oracle/oradata/prod/disk4/control01.ctl
--Start the database
Sql> Startup ORACLE instance started.
Total System Global area 418484224 bytesfixed size 1336932 bytesvariable size 276826524 byte Sdatabase buffers 134217728 Bytesredo buffers 6103040 bytesdatabase mounted. Database opened.
3. Pfile Steps: modifying the Pfile parameterConsistency Close Database Increase or decrease control files starting a database with Pfileverify the results.
Modify control File: *.control_files= '/u01/app/oracle/oradata/prod/disk1/control01.ctl ', '/u01/app/oracle/oradata/prod/disk2/ Control01.ctl ', '/u01/app/oracle/oradata/prod/disk3/control01.ctl ', '/u01/app/oracle/oradata/prod/disk4/ Control01.ctl ', '/u01/app/oracle/oradata/prod/disk5/control01.ctl ' Close database: Sql>shutdown Immediate
Copy file: [[email protected] DBS] $CP/u01/app/oracle/oradata/prod/disk4/control01.ctl/u01/app/oracle/oradata/prod/ Disk5/control01.ctl start the database:sql> Startuporacle instance started.
Total System Global area 418484224 bytesfixed size 1336932 bytesvariable size 276826524 byte Sdatabase buffers 134217728 Bytesredo buffers 6103040 bytesdatabase mounted. Database opened.
Six. Control File Recovery: (the control file is lost to rebuild the control file) 1. When there are multiple control files, a recovery of one of the control files is lost.
Resolution: Copy the other control files to the missing directory.
2. Control file is lost, but controlfile back to trace file: Executed ALTER DATABASE backup Controlfile to trace;
FIX: Start Nomount state, rebuild control file: noresetlogs example: ①. Start database: Error sql> startuporacle instance started.
Total System Global area 418484224 bytesfixed Size 133 6932 bytesvariable Size 276826524 bytesdatabase buffers &NB Sp 134217728 Bytesredo buffers 6103040 Bytesora-00205:error in Identifying control file, check alert log for more Info②. View Alarm LOG: Error alter DATABASE Mountora-00210:cannot Open the SP Ecified control Fileora-00202:control file: '/u01/app/oracle/oradata/prod/disk3/control01.ctl ' ora-27037:unable to Obtain file Statuslinux error:2: No such file or directoryadditional Information:3ora-00210:cannot open the specified C Ontrol Fileora-00202:control file: '/u01/app/oracle/oradata/prod/disk2/control01.ctl ' ora-27037:unable to obtain file Statuslinux error:2: No such file or directoryadditional Information:3ora-00210:cannot open the specified control Fileo Ra-00202:control file: '/u01/app/oracle/oradata/prod/disk1/control01.ctl ' ora-27037:unable to obtain file Statuslinux error:2: No such file or direct Oryadditional Information:3mon June 15:16:04 2014Checker run found 2 new persistent data failuresORA-205 signalled Duri Ng:alter DATABASE MOUNT ... ③. Look for a word in the trace file (at the end of the trace file) to rebuild the control file. Note: The prod here is db_name.CREATE controlfile Reuse DATABASE "PROD" NoresetlogsNoarchivelogMaxlogfilesmaxlogmembers 2Maxdatafilesmaxinstances 1maxloghistory 292LOGFILEGROUP 1 ('/u01/app/oracle/oradata/prod/disk1/redo01.log ','/u01/app/oracle/oradata/prod/disk2/redo01.log ') SIZE 100M BLOCKSIZE,GROUP 2 ('/u01/app/oracle/oradata/prod/disk1/redo02.log ','/u01/app/oracle/oradata/prod/disk2/redo02.log ') SIZE 100M BLOCKSIZE,GROUP 3 ('/u01/app/oracle/oradata/prod/disk1/redo03.log ','/u01/app/oracle/oradata/prod/disk2/redo03.log ') SIZE 100M BLOCKSIZE--STANDBY LOGFILEdatafile'/u01/app/oracle/oradata/prod/disk3/system01.dbf ','/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf ','/u01/app/oracle/oradata/prod/disk3/undotbs01.dbf ','/u01/app/oracle/oradata/prod/disk3/users01.dbf 'CHARACTER SET Al32utf8;The Nomount executes the above rebuild control file statement.
③. Start the database to the open state,sql> ALTER DATABASE open;
Database altered. View table space information sql> Select Tablespace_name, contents from Dba_tablespaces;
Tablespace_name CONTENTS---------------------------------------SYSTEM Permanentsys AUX Permanentundotbs UNDOTEMPTS1 temporaryusers PERMANENT
Verify that the data file for the temporary tablespace exists sql> select File#,name from V$tempfile;
file# NAME------------------------------------------------------------1/u01/app/oracle/oradata/prod/disk3/temp0 1.dbf
3.control file is lost, backup of control file: (experiment unsuccessful)FIX: Boot to nomount status, copy old control file, control file corresponding directory, boot to mount, executeALTER DATABASE backup Controlfile to trace;Close the database, delete the old control file,Find the Rebuild control file statement in trace, rebuild the control file,start the database to open, check the existence of the data file for the temporary tablespace (the normal temporary tablespace does not exist, recreate a temporary tablespace, and set the default temporary tablespace).
Create temporary tablespace sql> create temporary tablespace tempts2 tempfile '/u01/app/oracle/oradata/prod/disk5/temp01.dbf ' size 50m;
Tablespace created. Set as default temporary tablespace sql> ALTER DATABASE default temporary tablespace tempts2;
Database altered. View sql> select File#,name from V$tempfile;
file# NAME------------------------------------------------------------1/u01/app/oracle/oradata/prod/disk5/temp0 1.dbf
4. If you do not have a backup of the control file, and you can find the Repository statement, modify (log file, the path of the data file is consistent with the database), then execute it.
5. Recover control files in various situations
Rebuilding a control file does not necessarily require a resetlogs open library. Try ALTER DATABASE open first; Case 1: Open library successfully
Case 2: Need not complete recovery, after the normal open library recover database;alter database open; Case 3: After incomplete recovery, only resetlogs open library if it is just shutdown abort, useRecover database until cancel;
If you need to do incomplete recovery, you will be prompted to use the using Backup controlfile; sql> Recover database using Backup controlfile; After you execute the above sentence, enter the log file.
"oracle11g,9" Control file
