控制文件恢复(五) 利用trace中的控制文件备份执行恢复

注：在利用trace备份恢复控制文件的时候，有一下两个点要特别注意：
   - 只读表空间
   - 临时表空间

1.准备只读表空间环境

SYS@PROD1>create tablespace ts datafile '/u01/app/oracle/oradata/PROD1/ts01.dbf' size 20m;

Tablespace created.

SYS@PROD1>select tablespace_name,status from dba_tablespaces where tablespace_name='TS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS                             ONLINE

SYS@PROD1>col file_name for a40;
SYS@PROD1>col online_status for a20;
SYS@PROD1>set linesize for 200;
SYS@PROD1>col online_status for a20;  
SYS@PROD1>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS';

  FILE_ID FILE_NAME                                STATUS    ONLINE_STATUS
---------- ---------------------------------------- --------- --------------------
        7 /u01/app/oracle/oradata/PROD1/ts01.dbf   AVAILABLE ONLINE

将表空间设为只读        
SYS@PROD1>alter tablespace ts read only;

Tablespace altered.

SYS@PROD1>select tablespace_name,status from dba_tablespaces where tablespace_name='TS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS                             READ ONLY

SYS@PROD1>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS';

  FILE_ID FILE_NAME                                STATUS    ONLINE_STATUS
---------- ---------------------------------------- --------- --------------------
        7 /u01/app/oracle/oradata/PROD1/ts01.dbf   AVAILABLE ONLINE
       
2.查看控制文件位置
SYS@PROD1>col name for a70;
SYS@PROD1>select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/oradata/PROD1/control03.ctl

3.转储控制文件到trace文件中

设定追踪
SYS@PROD1>oradebug setmypid
Statement processed.

备份控制文件到trace文件中
SYS@PROD1>alter database backup controlfile to trace;

Database altered.

显示产生的追踪文件
SYS@PROD1>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_30074.trc

4.打开转储后文件，以下为部分内容
[oracle@host01 ~]$ cd /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/
[oracle@host01 trace]$ ls PROD1_ora_30074.trc
PROD1_ora_30074.trc
[oracle@host01 trace]$ more PROD1_ora_30074.trc
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS  ARCHIVELOG
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   MAXINSTANCES 8
   MAXLOGHISTORY 292
LOGFILE
 GROUP 1 (
   '/u01/app/oracle/oradata/PROD1/redo01.log',
   '/u01/app/oracle/oradata/PROD1/redo01_a.log'
 ) SIZE 50M BLOCKSIZE 512,
 GROUP 2 (
   '/u01/app/oracle/oradata/PROD1/redo02.log',
   '/u01/app/oracle/oradata/PROD1/redo02_a.log'
 ) SIZE 50M BLOCKSIZE 512,
 GROUP 3 (
   '/u01/app/oracle/oradata/PROD1/redo03.log',
   '/u01/app/oracle/oradata/PROD1/redo03_a.log'
 ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
 '/u01/app/oracle/oradata/PROD1/system01.dbf',
 '/u01/app/oracle/oradata/PROD1/sysaux01.dbf',
 '/u01/app/oracle/oradata/PROD1/undotbs01.dbf',
 '/u01/app/oracle/oradata/PROD1/users01.dbf',
 '/u01/app/oracle/oradata/PROD1/example01.dbf',
 '/u01/app/oracle/oradata/PROD1/ts_01.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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/backup/1_1_824297850.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_927290701.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_928248930.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_928254317.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_930744220.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_930746071.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_930997733.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/backup/1_1_931001715.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00007'
 TO '/u01/app/oracle/oradata/PROD1/ts01.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "TS" 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/PROD1/temp01.dbf'
    SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

注：在转储的控制文件中，没有只读表空间的内容，本测试是先生成转储文件，再恢复的，
往往在控制文件丢失之前很少会有人转储控制文件的（当然了，一般情况都会备份控制文件的），
如果没有转储控制文件的话，我们也可以在其他数据库中转储控制文件，按照目标库的情况进行调整，然后再创建。

5.手工删除控制文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/control01.ctl

SYS@PROD1>! rm /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/control03.ctl

6.查看告警日志，提示控制文件丢失
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m001_30528.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

7.一致性关闭数据库已经不可能了（控制文件丢失，无法同步SCN），采取强制手段
SYS@PROD1>shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@PROD1>shutdown abort;
ORACLE instance shut down.

8.按照转储文件中的步骤恢复控制文件，nomount启动数据库
SYS@PROD1>startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             520096848 bytes
Database Buffers          310378496 bytes
Redo Buffers                2371584 bytes

9.采用NORESETLOGS方式创建控制文件

SYS@PROD1>CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS  ARCHIVELOG
 2      MAXLOGFILES 16
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 100
 5      MAXINSTANCES 8
 6      MAXLOGHISTORY 292
 7  LOGFILE
 8    GROUP 1 (
 9      '/u01/app/oracle/oradata/PROD1/redo01.log',
10      '/u01/app/oracle/oradata/PROD1/redo01_a.log'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '/u01/app/oracle/oradata/PROD1/redo02.log',
14      '/u01/app/oracle/oradata/PROD1/redo02_a.log'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '/u01/app/oracle/oradata/PROD1/redo03.log',
18      '/u01/app/oracle/oradata/PROD1/redo03_a.log'
19    ) SIZE 50M BLOCKSIZE 512
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/u01/app/oracle/oradata/PROD1/system01.dbf',
23    '/u01/app/oracle/oradata/PROD1/sysaux01.dbf',
24    '/u01/app/oracle/oradata/PROD1/undotbs01.dbf',
25    '/u01/app/oracle/oradata/PROD1/users01.dbf',
26    '/u01/app/oracle/oradata/PROD1/example01.dbf',
27    '/u01/app/oracle/oradata/PROD1/ts_01.dbf'
28  CHARACTER SET AL32UTF8
29  ;

Control file created.

10.因为之前shutdown没有采取一致性关闭，所以，需要recover database，recover后，可正常open数据库
SYS@PROD1>select status from v$instance;

STATUS
------------
MOUNTED

打开数据库
SYS@PROD1>alter database open;

Database altered.

SYS@PROD1>select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/oradata/PROD1/control03.ctl

控制文件已正常恢复
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/oradata/PROD1/control01.ctl

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/control03.ctl
/u01/app/oracle/oradata/PROD1/control03.ctl

SYS@PROD1>! ls /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl

注：看视恢复完成，不过可以很清晰的看到转储的控制文件中，还有几步没有完成，进行；

11.再次查看表空间和数据文件状态：
注：我们可以看到，只读表空间的状态依然是read only，不过数据文件的路径、名字及状态都有所变化

SYS@PROD1>col file_name for a60;
SYS@PROD1>select file_name,status,online_status from dba_data_files;

FILE_NAME                                                    STATUS    ONLINE_STATUS
------------------------------------------------------------ --------- --------------------
/u01/app/oracle/oradata/PROD1/ts_01.dbf                      AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/example01.dbf                  AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/users01.dbf                    AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf                  AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/sysaux01.dbf                   AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/system01.dbf                   AVAILABLE SYSTEM
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00007     AVAILABLE OFFLINE

7 rows selected.

12.按照转储的步骤继续rename数据文件（按照转储文件内的内容修改）
SYS@PROD1>alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00007' to
 2  '/u01/app/oracle/oradata/PROD1/ts01.dbf';

Database altered.

13.按照转储步骤，修改表空间online

SYS@PROD1>alter tablespace ts online;

Tablespace altered.

14.再次查看表空间、数据文件的状态，已经恢复原貌
SYS@PROD1>select file_name,status,online_status from dba_data_files;

FILE_NAME                                                    STATUS    ONLINE_STATUS
------------------------------------------------------------ --------- --------------------
/u01/app/oracle/oradata/PROD1/ts_01.dbf                      AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/example01.dbf                  AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/users01.dbf                    AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf                  AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/sysaux01.dbf                   AVAILABLE ONLINE
/u01/app/oracle/oradata/PROD1/system01.dbf                   AVAILABLE SYSTEM
/u01/app/oracle/oradata/PROD1/ts01.dbf                       AVAILABLE ONLINE

7 rows selected.

15.转储步骤中还涉及到了临时表空间，我们验证一下，重建控制文件后，临时表空间的情况，没有任何数据文件

临时表空间对应的物理文件及其路径，都没有
SYS@PROD1>select file_name,tablespace_name from dba_temp_files;

no rows selected

16.按照转储中的语句给临时表空间增加数据文件
SYS@PROD1>col PROPERTY_NAME for a20;
SYS@PROD1>col PROPERTY_VALUE for a20;
SYS@PROD1>col DESCRIPTION for a30;
SYS@PROD1>select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME        PROPERTY_VALUE       DESCRIPTION
-------------------- -------------------- ------------------------------
DEFAULT_TEMP_TABLESP TEMP                 Name of default temporary tabl
ACE                                       espace

SYS@PROD1>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/temp01.dbf' size 87031808  
 2  REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

17.再次查看

SYS@PROD1>select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------------------------
TEMP                           /u01/app/oracle/oradata/PROD1/temp01.dbf

控制文件恢复完毕

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2130941/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2130941/