重建Oracle数据库控制文件

声明：本例请不要在生产库上试验，否则，后果自负
读了官方文档， 尝试着重建了一下控制文件：
SQL> startup nomount
ORACLE instance started.


Total System Global Area 2488635392 bytes
Fixed Size                  2798928 bytes
Variable Size             721423024 bytes
Database Buffers         1744830464 bytes
Redo Buffers               19582976 bytes
SQL> create controlfile set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG')
  3  logfile group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG')
  4  logfile group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG')
  5  resetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF',
 23  'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
 24  archivelog
 25  /
logfile group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG')
        *
ERROR at line 3:
ORA-02167: LOGFILE clause specified more than once




SQL> create controlfile set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG'),
  3   group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG'),
  4   group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG')
  5  resetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF',
 23  'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
 24  archivelog
 25  /
create controlfile set database orcl
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF'




SQL> create controlfile set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG'),
  3   group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG'),
  4   group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG')
  5  resetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF'
 23  archivelog
 24  /
create controlfile set database orcl
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01198: must specify size for log file if RESETLOGS
ORA-01517: log member: 'C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG'




SQL> create controlfile set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG'),
  3   group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG'),
  4   group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG')
  5  noresetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF'
 23  archivelog
 24  /
create controlfile set database orcl
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01223: RESETLOGS must be specified to set a new database name




SQL> create controlfile --set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG'),
  3   group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG'),
  4   group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG')
  5  noresetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF'
 23  archivelog
 24  /
archivelog
         *
ERROR at line 23:
ORA-01970: You must specify a database name for CREATE CONTROLFILE




SQL> create controlfile set database orcl
  2  logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG') size 256M,
  3   group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG') size 256M,
  4   group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG') size 256M
  5  resetlogs
  6  datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  7  'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  8  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  9  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
 10  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
 11  'F:\H2DATA\H2DB.DBF',
 12  'F:\H2DATA\H2DB_INDEX.DBF',
 13  'F:\H2DATA\H2_BUSI.DBF',
 14  'F:\H2DATA\H2_BUSI_INDX.DBF',
 15  'F:\H2DATA\H2_TEST.DBF',
 16  'F:\H2DATA\H2_TEST_INDX.DBF',
 17  'F:\H2DATA\H2_LOG.DBF',
 18  'F:\H2DATA\H2_LOG_INDX.DBF',
 19  'F:\H2DATA\H2_IMAGE.DBF',
 20  'F:\H2DATA\H2_IMAGE_INDX.DBF',
 21  'F:\H2DATA\WMS_DATA.DBF',
 22  'F:\H2DATA\WMS_INDX.DBF'
 23  archivelog
 24  /
create controlfile set database orcl
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL'
ORA-27038: created file already exists
OSD-04010: ?????? <create> ????, ??????????????




SQL> /
create controlfile set database orcl
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLE\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL'
ORA-27038: created file already exists
OSD-04010: ?????? <create> ????, ??????????????




SQL> /


Control file created.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




SQL> alter database open resetlogs;


Database altered.


SQL> select count(*) from h2.s_print_h;


  COUNT(*)
----------
       188


SQL> select count(*) from h2.t_intent_d;


  COUNT(*)
----------
     19615


SQL>

到这里，控制文件就已经重建成功了。
如果丢失了数据库控制文件，可以采用这种方式重建之后再打开数据库.

检查alert日志，日志里有错误：
PLS-00905: object H2.PROC_BATSALE_TRAN_WMS is invalid
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
ORA-06512: at "H2.PROC_EXEC_TRIGGER_EXT", line 18
ORA-06512: at "H2.TR_T_BATSALE_H_TABLE", line 13
ORA-04088: error during execution of trigger 'H2.TR_T_BATSALE_H_TABLE'
ORA-06512: at "H2.PROC_WANZHONG_BORI", line 214
Mon Sep 21 22:23:06 2015
Errors in file C:\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_m003_12124.trc:
ORA-25153: 临时表空间为空

增加临时文件：
alter tablespace temp add tempfile 'C:\oracle\oradata\orcl\temp01.dbf' size 256M autoextend on


create controlfile set database orcl
logfile group 1 ('C:\ORACLE\ORADATA\ORCL\REDO01_1.LOG') size 256M,
 group 2 ('C:\ORACLE\ORADATA\ORCL\REDO02_1.LOG') size 256M,
 group 3 ('C:\ORACLE\ORADATA\ORCL\REDO03_1.LOG') size 256M
resetlogs 
datafile 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
'F:\H2DATA\H2DB.DBF',
'F:\H2DATA\H2DB_INDEX.DBF',
'F:\H2DATA\H2_BUSI.DBF',
'F:\H2DATA\H2_BUSI_INDX.DBF',
'F:\H2DATA\H2_TEST.DBF',
'F:\H2DATA\H2_TEST_INDX.DBF',
'F:\H2DATA\H2_LOG.DBF',
'F:\H2DATA\H2_LOG_INDX.DBF',
'F:\H2DATA\H2_IMAGE.DBF',
'F:\H2DATA\H2_IMAGE_INDX.DBF',
'F:\H2DATA\WMS_DATA.DBF',
'F:\H2DATA\WMS_INDX.DBF'
archivelog

最后，重建控制文件的步骤：
1. Make a list of all data files and redo log files of the database
2. Shut down the database
3. Back up all data files and redo log files of the database
4. Startup a new instance, but do not mount or open the database
   startup nomount
5. Create a new control file for the database using the CREATE CONTROLFILE statement
6. Store a backup of the new control file on an offline storage device
7. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5(
  not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.
8. Recover the database if necessary.  If you are not recovering the database, skip to step 9
9. Open the database using one of the following methods.
  a. If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally:
     ALTER DATABASE OPEN;
  b. If you specified RESETLOGS when creating the contol file, use the ALTER DATABASE statement, indicating RESETLOGS:
      ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1805287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-1805287/