重建Oracle資料庫控制檔案

abstractcyj發表於2015-09-21
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章