oracle数据重构,重建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: ?????? ????, ??????????????

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: ?????? ????, ??????????????

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.