声明：本例请不要在生产库上试验，否则，后果自负
读了官方文档，尝试着重建了一下控制文件：
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博客

重建Oracle数据库控制文件

最新文章