oracle数据字典不一致,备份与恢复--重新控制文件数据字典和控制文件不一致的恢复...

在执行恢复后，应该检查数据库的alert文件，看是否出现控制文件中的数据文件名称和数据字典中数据文件名称无法一一对应的现象。

这篇文章描述通过RESETLOGS方式打开数据库时，如果发现不一致情况及解决方法。[@more@]

如果是利用备份的控制文件，除了数据文件的新增和删除以外，不会出现不一致的情况。这种不一致的情况更多的是出现在手工重建控制文件过程中。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

JGLU ONLINE

TEST ONLINE

8 rows selected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace jglu offline;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS READ ONLY

EXAMPLE ONLINE

JGLU OFFLINE

TEST ONLINE

8 rows selected.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

Database altered.

trace出来的控制文件如下：

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf',

'/u01/app/oracle/oradata/orcl/test02.dbf'

CHARACTER SET WE8ISO8859P1

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/admin/orcl/archive/1_1_675592897.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Files in normal offline tablespaces are now named.

ALTER DATABASE RENAME FILE 'MISSING00006'

TO '/u01/app/oracle/oradata/orcl/jglu01.dbf';

ALTER DATABASE RENAME FILE 'MISSING00007'

TO '/u01/app/oracle/oradata/orcl/jglu02.dbf';

-- Files in read-only tablespaces are now named.

ALTER DATABASE RENAME FILE 'MISSING00004'

TO '/u01/app/oracle/oradata/orcl/users01.dbf';

-- Online the files in read-only tablespaces.

ALTER TABLESPACE "USERS" ONLINE;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

此时模拟数据库有问题需要恢复,假设由于没有将脚本保存到trace文件中，而完全通过手工创建。因此，

在创建的时候丢失了test02.dbf。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 335544320 bytes

Fixed Size 1219280 bytes

Variable Size 71304496 bytes

Database Buffers 260046848 bytes

Redo Buffers 2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,

10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M

11 -- STANDBY LOGFILE

12 DATAFILE

13 '/u01/app/oracle/oradata/orcl/system01.dbf',

14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

16 '/u01/app/oracle/oradata/orcl/example01.dbf' 17 CHARACTER SET WE8ISO8859P1;

Control file created.

SQL> recover database using backup controlfile;

ORA-00279: change 691653 generated at 01/18/2009 01:52:39 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf

ORA-00280: change 691653 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS READ ONLY

EXAMPLE ONLINE

JGLU OFFLINE

TEST ONLINE

8 rows selected.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/product/10201/dbs/MISSING00004

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/product/10201/dbs/MISSING00006

/u01/app/oracle/product/10201/dbs/MISSING00007

/u01/app/oracle/product/10201/dbs/MISSING00008

8 rows selected.

SQL> set linesize 200

SQL> col file_name format a60

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

------------------------------------------------------------ ------------------------------

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

/u01/app/oracle/product/10201/dbs/MISSING00004 USERS

/u01/app/oracle/product/10201/dbs/MISSING00006 JGLU

/u01/app/oracle/product/10201/dbs/MISSING00007 JGLU

/u01/app/oracle/product/10201/dbs/MISSING00008 TEST

8 rows selected.

从上面的数据字典可以看到，存在不正常的表空间和数据文件。也可以从alert日志中得到相应的信息。

Dictionary check beginning

Tablespace 'TEMP' #3 found in data dictionary,

but not in the controlfile. Adding to controlfile.

Tablespace 'USERS' #4 found in data dictionary,

but not in the controlfile. Adding to controlfile.

Tablespace 'JGLU' #7 found in data dictionary,

but not in the controlfile. Adding to controlfile.

Tablespace 'TEST' #8 found in data dictionary,

but not in the controlfile. Adding to controlfile.

File #4 found in data dictionary but not in controlfile.

Creating OFFLINE file 'MISSING00004' in the controlfile.

Corrected file 5 plugged in read-only status in control file

File #6 found in data dictionary but not in controlfile.

Creating OFFLINE file 'MISSING00006' in the controlfile.

File #7 found in data dictionary but not in controlfile.

Creating OFFLINE file 'MISSING00007' in the controlfile.

File #8 found in data dictionary but not in controlfile.

Creating OFFLINE file 'MISSING00008' in the controlfile.

This file can no longer be recovered so it must be dropped.

Dictionary check complete

由于USER表空间在重建控制文件之前是只读状态，因此不需要进行恢复，直接RENAME后ONLINE就可以了。

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00004'

2 to '/u01/app/oracle/oradata/orcl/users01.dbf';

Database altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

由于JGLU表空间在重建设控制文件之前是OFFLINE的，也不需要恢复，直接RENAME后ONLINE就可以了。

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00006'

2 to '/u01/app/oracle/oradata/orcl/jglu01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00007'

2 to '/u01/app/oracle/oradata/orcl/jglu02.dbf';

Database altered.

SQL> alter tablespace jglu online;

Tablespace altered.

对于临时表空间只要新增加一下就行了

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

对于表空间TEST由于没有被添加到控制文件中，且数据库以RESETLOGS方式打开，用来恢复表空间的日志

已经被重置，在以前的版本中，因此只能将表空间删除。但10g是可以recover的

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00008'

2 to '/u01/app/oracle/oradata/orcl/test02.dbf';

Database altered.

SQL> alter tablespace test online;

alter tablespace test online

*

ERROR at line 1:

ORA-01190: control file or data file 8 is from before the last RESETLOGS

ORA-01110: data file 8: '/u01/app/oracle/oradata/orcl/test02.dbf'

SQL> recover tablespace test;

ORA-00279: change 691653 generated at 01/18/2009 01:52:39 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf

ORA-00280: change 691653 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

Log applied.

Media recovery complete.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select owner,table_name,TABLESPACE_NAME from dba_tables where tablespace_name='TEST';

OWNER TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

JGLU TEST1 TEST

SQL> select count(*) from jglu.test1;

COUNT(*)

----------

40688