To restore the data dictionary and control file inconsistent recovery (2)

Jan 15 posted in Oracle (RSS), 154 views.

Author: yangtingkun

In the implementation of restoration, you should check the database alert file to see whether there is control file in the data file name and data dictionaries in the data file name can not be one to one situation.

This article describes the approach to rebuilding through NORESETLOGS control file and open the database, if inconsistencies are found situations and solutions.

If it is to use a backup control file, in addition to the new and delete data files other than that there is no inconsistency. This inconsistency in more of a control file appears in the manual renewal process.

SQL> CONN / @ TEST AS SYSDBA
Is connected.
SQL> ALTER TABLESPACE INDX READ ONLY;

The table space has changed.

SQL> ALTER TABLESPACE TOOLS OFFLINE;

The table space has changed.

SQL> CREATE TABLESPACE TEST DATAFILE 'F: ORACLEORADATATESTTEST01.DBF' SIZE 10M;

Table space has been created.

SQL> SELECT 'ALTER TABLESPACE' | | TABLESPACE_NAME | | 'BEGIN BACKUP;'
2 FROM USER_TABLESPACES WHERE CONTENTS! = 'TEMPORARY' AND STATUS = 'ONLINE';

'ALTERTABLESPACE' | | TABLESPACE_NAME | | 'BEGINBACKUP;'
-------------------------------------------------- -----------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE YANGTK BEGIN BACKUP;
ALTER TABLESPACE TEST BEGIN BACKUP;

SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE YANGTK BEGIN BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE TEST BEGIN BACKUP;

The table space has changed.

SQL> HOST COPY F: ORACLEORADATATEST *. DBF F: ORACLEBACKUPTEST20060308

SQL> SELECT 'ALTER TABLESPACE' | | TABLESPACE_NAME | | 'END BACKUP;'
2 FROM USER_TABLESPACES WHERE CONTENTS! = 'TEMPORARY' AND STATUS = 'ONLINE';

'ALTERTABLESPACE' | | TABLESPACE_NAME | | 'ENDBACKUP;'
-------------------------------------------------- ---------
ALTER TABLESPACE SYSTEM END BACKUP;
ALTER TABLESPACE UNDOTBS1 END BACKUP;
ALTER TABLESPACE USERS END BACKUP;
ALTER TABLESPACE YANGTK END BACKUP;
ALTER TABLESPACE TEST END BACKUP;

SQL> ALTER TABLESPACE SYSTEM END BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE USERS END BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE YANGTK END BACKUP;

The table space has changed.

SQL> ALTER TABLESPACE TEST END BACKUP;

The table space has changed.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The database has changed.

Assuming the recovery process, we found all the missing control file and the backup control file. Then you need to manually rebuild control file, but this time manually rebuild control files may be missing part of the data files.

The script should create the control file is as follows:

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 680

LOGFILE

GROUP 1 'F: ORACLEORADATATESTREDO01.LOG' SIZE 100M,

GROUP 2 'F: ORACLEORADATATESTREDO02.LOG' SIZE 100M,

GROUP 3 'F: ORACLEORADATATESTREDO03.LOG' SIZE 100M

DATAFILE

'F: ORACLEORADATATESTSYSTEM01.DBF',

'F: ORACLEORADATATESTUNDOTBS01.DBF',

'F: ORACLEORADATATESTUSERS01.DBF',

'F: ORACLEORADATATESTYANGTK01.DBF',

'F: ORACLEORADATATESTTEST01.DBF'

CHARACTER SET ZHS16GBK

;

Suppose in the absence of the script saved to the trace file, completely by hand to create, in creating the time lost USERS01.DBF. Also in the restore operation prior to implementation, to remove from the database tablespace TEST, simulated in the control file contains data files in the data dictionary does not contain the situation.

SQL> DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;

The table space has been discarded.

SQL> SHUTDOWN IMMEDIATE
Database has been closed.
The database has been uninstalled.
ORACLE instance shut down.
SQL> HOST DEL F: ORACLEORADATATEST *. CTL

SQL> HOST COPY F: ORACLEBACKUPTEST20060308 * F: ORACLEORADATATEST

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 76619308 bytes
Fixed Size 454188 bytes
Variable Size 50331648 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 50
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 680
7 LOGFILE
8 GROUP 1 'F: ORACLEORADATATESTREDO01.LOG' SIZE 100M,
9 GROUP 2 'F: ORACLEORADATATESTREDO02.LOG' SIZE 100M,
10 GROUP 3 'F: ORACLEORADATATESTREDO03.LOG' SIZE 100M
11 DATAFILE
12 'F: ORACLEORADATATESTSYSTEM01.DBF',
13 'F: ORACLEORADATATESTUNDOTBS01.DBF',
14 'F: ORACLEORADATATESTYANGTK01.DBF',
15 'F: ORACLEORADATATESTTEST01.DBF'
16 CHARACTER SET ZHS16GBK
17;

Control file has been created

SQL> RECOVER DATABASE
To complete media recovery.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

The system has changed.

Check the alert log files, you will find the following information:

Recovery deleting file # 7: 'F: ORACLEORADATATESTTEST01.DBF'
Recovery dropped tablespace 'TEST'
Media Recovery Complete
Completed: ALTER DATABASE RECOVER DATABASE

This shows that the Oracle redo logs by the way has been deleted TEST tablespace.

SQL> ALTER DATABASE OPEN;

The database has changed.

SQL> SELECT NAME FROM V $ DATAFILE;

NAME
-------------------------------------------------- ---------
F: ORACLEORADATATESTSYSTEM01.DBF
F: ORACLEORADATATESTUNDOTBS01.DBF
F: ORACLEORACLE920DATABASEMISSING00003
F: ORACLEORACLE920DATABASEMISSING00004
F: ORACLEORACLE920DATABASEMISSING00005
F: ORACLEORADATATESTYANGTK01.DBF

Have chosen 6 rows.

SQL> COL FILE_NAME FORMAT A50
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ----------------
F: ORACLEORADATATESTSYSTEM01.DBF SYSTEM
F: ORACLEORADATATESTUNDOTBS01.DBF UNDOTBS1
F: ORACLEORACLE920DATABASEMISSING00003 INDX
F: ORACLEORACLE920DATABASEMISSING00004 TOOLS
F: ORACLEORACLE920DATABASEMISSING00005 USERS
F: ORACLEORADATATESTYANGTK01.DBF YANGTK

Have chosen 6 rows.

From the above data dictionary can be seen, there is not a normal table spaces and data files. Alert log can also be obtained from the corresponding information.

Dictionary check beginning
Tablespace 'TEMP' # 2 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'INDX' # 3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TOOLS' # 4 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' # 5 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File # 3 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00003' in the controlfile.
File # 4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile.
File # 5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
Dictionary check complete

As the TOOLS tablespace offline normally before the backup of the. Therefore, TOOLS tablespace does not require additional recovery, the data files directly RENAME on it.

SQL> ALTER DATABASE RENAME FILE 'F: ORACLEORACLE920DATABASEMISSING00004'
2 TO 'F: ORACLEORADATATESTTOOLS01.DBF';

The database has changed.

INDX tablespace before the backup is read-only state, so no need to recover, RENAME after ONLINE on it.

SQL> ALTER DATABASE RENAME FILE 'F: ORACLEORACLE920DATABASEMISSING00003'
2 TO 'F: ORACLEORADATATESTINDX01.DBF';

The database has changed.

SQL> ALTER TABLESPACE INDX ONLINE;

The table space has changed.

SQL> SELECT STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'INDX';

STATUS
---------
READ ONLY

For the temporary table space, a direct increase in temporary files on it.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'F: ORACLEORADATATESTTEMP01.DBF'
2 SIZE 41943040 REUSE AUTOEXTEND OFF;

The table space has changed.

However, for tablespace USERS, because of not being added to the control file, so, in the restoration process is offline, will change the name after the data file can not be directly on-line to the data file, you must recover the table space.

SQL> ALTER DATABASE RENAME FILE 'F: ORACLEORACLE920DATABASEMISSING00005'
2 TO 'F: ORACLEORADATATESTUSERS01.DBF';

The database has changed.

SQL> ALTER TABLESPACE USERS ONLINE;
ALTER TABLESPACE USERS ONLINE
*
ERROR at line 1:
ORA-01113:?? 5 ??????
ORA-01110:???? 5: 'F: ORACLEORADATATESTUSERS01.DBF'

SQL> RECOVER TABLESPACE USERS;
To complete media recovery.
SQL> ALTER TABLESPACE USERS ONLINE;

The table space has changed.

If we adopt the NORESETLOGS open the database, only to restore the database log files are not lost in the reconstruction was omitted when the control file or data files can be restored to normal.