控制文件恢复系列（2）

[3.7]有trace ,活动/当前在线日志损坏，但有镜像
1：查看当前redo，

没有多个，使用alter database add logfile member 'XXXX' to group group#;

sys@ORCL> select * from v$log;

GROUP# THREAD# SEQUENCE#      BYTES  BLOCKSIZE MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
------ ------- --------- ---------- ---------- ------- --- ---------------- ------------- ------------ ------------ ------------
     1       1         7   52428800        512        2 NO  INACTIVE               402861484 07-DEC-14      2.8147E+14

     2       1         5   52428800        512        2 YES  CURRENT             402861478 07-DEC-14       402861481 07-DEC-14

     3       1         6   52428800        512        2 YES INACTIVE             402861481 07-DEC-14       402861484 07-DEC-14

2：创建控制文件备份文件，并异常关闭数据库

sys@ORCL> alter database backup controlfile to trace as '/backup/ctl.sql' reuse;
Database altered.

sys@ORCL> shutdown abort;
ORACLE instance shut down.

模拟控制文件和在线日志文件的损坏

[root@Lee orcl]# mv control0* /home/

[root@Lee orcl]# mv redo02.log /home/

3：启动数据库报错

sys@ORCL> startup;
ORACLE instance started.
Total System Global Area  784998400 bytes
Fixed Size                  2257352 bytes
Variable Size             499125816 bytes
Database Buffers          281018368 bytes
Redo Buffers                2596864 bytes
ORA-00205: error in identifying control file, check alert log for more info
4：使用ctl.sql备份创建控制文件
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
    MAXLOGHISTORY 4674
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/orcl/redo01.log',
    '/u01/app/oracle/oradata/orcl/redo012.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/orcl/redo02.log',
    '/u01/app/oracle/oradata/orcl/redo022.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/orcl/redo03.log',
    '/u01/app/oracle/oradata/orcl/redo032.log'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf',
  '/u01/app/oracle/oradata/orcl/users02.dbf'
28  CHARACTER SET ZHS16GBK;
ERROR:
ORA-06550: line 1, column 29:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
使用NORESETLOGS创建控制是，回去检查在线日志文件，前面已经损坏了，在线日志文件

此时我们就可以使用redo的镜像来恢复redo02.log

[oracle@Lee orcl]# cp redoo22.log redo02.log
在此创建控制文件

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

Control file created.
5：尝试打开数据库，需要介质恢复

sys@ORCL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs  media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
6：恢复数据库，打开数据库
sys@ORCL> recover database;
Media recovery complete.
sys@ORCL> alter database open;
Database altered.
sys@ORCL> select * from t1;
         X
----------
         1

[3.8]数据文件、控制文件损坏，采用控制文件TRACE恢复
     背景：数据文件损坏，但有备份，所有控制文件丢失，但有控制文件的TRACE脚本
1：热备份表空间

sys@ORCL> alter tablespace example begin backup;
Tablespace altered.
sys@ORCL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME                                                                            CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------------------------------------- ------------------ ------------ --------------- -------
/u01/app/oracle/oradata/orcl/system01.db                                                  402881590                            0 SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.db                                                  402881590                            0 ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.d                                                  402881590                            0 ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                                                  402881590                            0 ONLINE
/u01/app/oracle/oradata/orcl/example01.d                                                   402882931--最新检点SCN     0 ONLINE
/u01/app/oracle/oradata/orcl/users02.dbf                                                  402881590                            0 ONLINE
[oracle@Lee orcl]#  cp example01.dbf /home/
sys@ORCL> alter tablespace example end backup;
Tablespace altered.
2：干净关闭数据库，模拟损坏数据文件   example01.dbf  和控制文件
sys@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@Lee orcl]# mv control0*/mmt
[oracle@Lee orcl]# mv example01.dbf /mnt/
3：启动到nomount状态，创建控制文件

sys@ORCL> startup nomount;
ORACLE instance started.
Total System Global Area  784998400 bytes
Fixed Size                  2257352 bytes
Variable Size             499125816 bytes
Database Buffers          281018368 bytes
Redo Buffers                2596864 bytes
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4674
LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/orcl/redo01.log',
10      '/u01/app/oracle/oradata/orcl/redo012.log'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '/u01/app/oracle/oradata/orcl/redo02.log',
14      '/u01/app/oracle/oradata/orcl/redo022.log'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '/u01/app/oracle/oradata/orcl/redo03.log',
18      '/u01/app/oracle/oradata/orcl/redo032.log'
19    ) SIZE 50M BLOCKSIZE 512
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/u01/app/oracle/oradata/orcl/system01.dbf',
23    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
24    '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
25    '/u01/app/oracle/oradata/orcl/users01.dbf',
26    '/u01/app/oracle/oradata/orcl/example01.dbf',
27    '/u01/app/oracle/oradata/orcl/users02.dbf'
28  CHARACTER SET ZHS16GBK;
ERROR:
ORA-06550: line 1, column 29:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
使用NORESETLOGS创建数据文件，会检查数据文件头的信息，此时缺少example01.dbf，无法创建控制文件，所以此时使用备份的数据文件恢复

[oracle@Lee home]# cp example01.dbf /u01/app/oracle/oradata/orcl
再次创建控制文件

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4674
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/orcl/redo01.log',
10      '/u01/app/oracle/oradata/orcl/redo012.log'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '/u01/app/oracle/oradata/orcl/redo02.log',
14      '/u01/app/oracle/oradata/orcl/redo022.log'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '/u01/app/oracle/oradata/orcl/redo03.log',
18      '/u01/app/oracle/oradata/orcl/redo032.log'
19    ) SIZE 50M BLOCKSIZE 512
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/u01/app/oracle/oradata/orcl/system01.dbf',
23    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
24    '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
25    '/u01/app/oracle/oradata/orcl/users01.dbf',
26    '/u01/app/oracle/oradata/orcl/example01.dbf',
27    '/u01/app/oracle/oradata/orcl/users02.dbf'
28  CHARACTER SET ZHS16GBK;
Control file created

查询数据文件开始SCN和检查点SCN

sys@ORCL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                                                            CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------------------------------------- ------------------ ------------ --------------- -------
/u01/app/oracle/oradata/orcl/system01.db                                                  402883178    402883178               0 SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.db                                                  402883178    402883178               0 ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.d                                                  402883178    402883178               0 ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                                                  402883178    402883178               0 ONLINE
/u01/app/oracle/oradata/orcl/example01.d                                                  402883178    402883178               0 RECOVER
/u01/app/oracle/oradata/orcl/users02.dbf                                                  402883178    402883178               0 ONLINE

6 rows selected.

sys@ORCL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                                                            REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/orcl/system01.db                                         NO  NO           402883178              311
/u01/app/oracle/oradata/orcl/sysaux01.db                                         NO  NO           402883178              297
/u01/app/oracle/oradata/orcl/undotbs01.d                                         NO  NO           402883178              218
/u01/app/oracle/oradata/orcl/users01.dbf                                         NO  NO           402883178              300
/u01/app/oracle/oradata/orcl/example01.d                                         YES YES          402882931              212
/u01/app/oracle/oradata/orcl/users02.dbf                                         NO  NO           402883178              193
6 rows selected.

sys@ORCL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE          402714864 02-DEC-14
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
          5 ACTIVE              402882931 07-DEC-14
         6 NOT ACTIVE                  0
6 rows selected.

需要进行数据库的恢复

4：恢复数据库并open database

sys@ORCL> recover database;
Media recovery complete.
sys@ORCL> alter database open;
Database altered.

[3.9.1]UNDOFILE数据文件、控制文件损坏，采用控制文件TRACE恢复
     背景:UNDOFILE数据文件、控制文件损坏，UNDOFILE无备份，有控制文件的TRACE脚本，数据库已经干净的关闭
1：查看生成控制文件trace备份
SQL> alter database backup controlfile to trace as '/tmp/ctl.sql' reuse;
Database altered.
2：查看undo信息
SQL> select name,status from v$datafile;
NAME                                                         STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/orcl/system01.dbf                    SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf                    ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                     ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf                   ONLINE
SQL> show parameter undo
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
undo_management                      string                           AUTO
undo_retention                       integer                          900
undo_tablespace                      string                           UNDOTBS2
3：干净关闭数据库，备份数据文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@Lee orcl]$ cp system01.dbf system01.dbf.bak
4：启动数据库创建测试数据
SQL> create tablespace test datafile '/tmp/test.dbf' size 10m;
Tablespace created.
SQL> create table test tablespace test as select * from dba_objects;
Table created.
5：干净关闭数据库，模拟undo损坏，undo无备份
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@Lee orcl]$mv undotbs02.dbf undotbs02.dbf.bak
启动数据库报错
[oracle@Lee orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Sep 22 16:38:01 2014
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                   778660 bytes
Variable Size             367271516 bytes
Database Buffers          889192448 bytes
Redo Buffers                1048576 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 21 - see DBWR trace file
ORA-01110: data file 21: '/u01/app/oracle/oradata/orcl/ undotbs02.dbf'
6：创建控制文件，来尝试恢复数据库，此时我们创建控制文件应该不需要undo数据文件了
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                   778660 bytes
Variable Size             367271516 bytes
Database Buffers          889192448 bytes
Redo Buffers                1048576 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6  MAXLOGHISTORY 908
  7  LOGFILE
  8  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 200M,
  9    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 200M,
10    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 200M
11  DATAFILE
12    '/u01/app/oracle/oradata/orcl/system01.dbf',
13    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
14    '/u01/app/oracle/oradata/orcl/users01.dbf' --,
15    --'/u01/app/oracle/oradata/orcl/undotbs02.dbf'
16  CHARACTER SET ZHS16GBK
17  ;
Control file created.
7：尝试打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
数据库不能打开，我们可以通过查看警告日志文件可知：
alter database open
Thu Sep 22 16:49:22 2014
Assigning activation ID 1290327320 (0x4ce8d518)
Thread 1 advanced to log sequence 9
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 30 at log switch
Thread 1 opened at log sequence 9
  Current log# 2 seq# 9 mem# 0: /opt/app/oracle/oradata/orcl/redo02.log
Successful open of redo thread 1
Thu Sep 22 16:49:23 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Sep 22 16:49:23 2014
SMON: enabling cache recovery
Thu Sep 22 16:49:23 2014
Errors in file /opt/app/oracle/admin/orcl/udump/orcl_ora_30047.trc:
ORA-30046:  Undo tablespace UNDOTBS2 not found in controlfile.
Thu Sep 22 16:49:23 2014
Error 30046 happened during db open, shutting down database
USER: terminating instance due to error 30046
Instance terminated by USER, pid = 30047
从面的警告日志文件可以知道，我们在创建控制文件时，剔除了undo，但是open数据库时要做崩溃恢复，先前滚，打开，再回滚
为了回滚，因为我们设置了UNDO自动管理，而FILE$里仍然有UNDOTBS2，这个时候，在打开时必须保证UNDOTBS2是正常的
所以我们得处理undo管理模式
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL scope=spfile;
System altered.
8：在尝试启动数据库
SQL> startup force
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                   778660 bytes
Variable Size             367271516 bytes
Database Buffers          889192448 bytes
Redo Buffers                1048576 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
打开数据库后，查看警告日志文件信息
Dictionary check beginning
Tablespace 'UNDOTBS2' #5 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'T_TMP_01' #8 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEST' #15 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #2 found in data dictionary but not in controlfile.
Creating OFFLINE file  'MISSING00002' in the controlfile.
File #4 is online, but is part of an offline tablespace.
data file 4: '/opt/app/oracle/oradata/orcl/users01.dbf'
File #21 found in data dictionary but not in controlfile.
Creating OFFLINE file  'MISSING00021' in the controlfile.
Dictionary check complete
数据库要对控制文件的文件信息和数据字典FILE$的文件信息做比较，控制文件里剔除了UNDOTBS2但是FILE$里却有UNDOTBS2，所以，ORACLE打开数据库后，可以查看到FILE$，发现5号表空间UNDOTBS2没有在控制文件中，5号表空间下有个2号文件这2号文件就是undotbs02.dbf，但是控制文件丢失了，所以加入控制文件的信息是MISSING00002，存放在$ORACLE_HOME/dbs下，一开始还建立了TEST表空间，大的数据库中FILE$里有TEST表空间，而控制文件，我们把/tmp/test.dbf剔除了，此时又会多出一个missing文件
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /opt/app/oracle/oradata/orcl/system01.dbf                    SYSTEM
         2 /opt/app/oracle/product/10.1.0/db_1/dbs/MISSING00002         RECOVER
         3 /opt/app/oracle/oradata/orcl/sysaux01.dbf                    ONLINE
         4 /opt/app/oracle/oradata/orcl/users01.dbf                     ONLINE
        21 /opt/app/oracle/product/10.1.0/db_1/dbs/MISSING00021         RECOVER

SQL> select count(*) from test;
select count(*) from test       
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01111: name for data file 2 is unknown - rename to correct file
ORA-01110: data file 2: '/opt/app/oracle/product/10.1.0/db_1/dbs/MISSING00002'
此时我们需要恢复test表空间才能查看test表
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                   778660 bytes
Variable Size             367271516 bytes
Database Buffers          889192448 bytes
Redo Buffers                1048576 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6  MAXLOGHISTORY 908
  7  LOGFILE
  8  GROUP 1 '/opt/app/oracle/oradata/orcl/redo01.log'  SIZE 200M,
  9    GROUP 2 '/opt/app/oracle/oradata/orcl/redo02.log'  SIZE 200M,
10    GROUP 3 '/opt/app/oracle/oradata/orcl/redo03.log'  SIZE 200M
11  DATAFILE
12    '/opt/app/oracle/oradata/orcl/system01.dbf',
13  '/opt/app/oracle/oradata/orcl/sysaux01.dbf',
14    '/opt/app/oracle/oradata/orcl/users01.dbf' ,
15   --'/opt/app/oracle/oradata/orcl/undotbs02.dbf'
16  '/tmp/test.dbf'
17  CHARACTER SET ZHS16GBK
18 ;
  Control file created.
SQL> recover datafile 1;
Media recovery complete.
SQL> select name,status from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ -------
/opt/app/oracle/oradata/orcl/system01.dbf                    SYSTEM
/tmp/test.dbf                                                RECOVER
/opt/app/oracle/oradata/orcl/sysaux01.dbf                    ONLINE
/opt/app/oracle/oradata/orcl/users01.dbf                     ONLINE
SQL> recover datafile 2;
Media recovery complete.
    
SQL> alter database open;

Database altered.
SQL> select FILE#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /opt/app/oracle/oradata/orcl/system01.dbf                    SYSTEM
         2 /tmp/test.dbf                                                ONLINE
         3 /opt/app/oracle/oradata/orcl/sysaux01.dbf                    ONLINE
         4 /opt/app/oracle/oradata/orcl/users01.dbf                     ONLINE
        21 /opt/app/oracle/product/10.1.0/db_1/dbs/MISSING00021         RECOVER
test表空间也恢复回来了
SQL> select count(*) from test;

  COUNT(*)
----------
     48340
SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management                      string                           MANUAL
undo_retention                       integer                          900
undo_tablespace                      string                           UNDOTBS2
现在数据库虽然是打开的，UNDO表空间仍然是UNDOTBS2，由于UNDO管理是MANUAL，所以数据库能正常使用，这个时候，我们要删除UNDOTBS2表空间，然后创建一个新的UNDO表空间
创建一个新的undo表空间undotbs1
SQL> alter system set undo_tablespace=undotbs1 scope=spfile;

System altered.

SQL> alter system set  undo_management=auto scope=spfile;

System altered.
备份数据库，最后重启数据库

[3.9.2] UNDOFILE数据文件,有备份、控制文件损坏，采用控制文件TRACE恢复
     3.9.2跟3.9.1的区别在于UNDOFILE数据文件损坏，但是有备份
1：热备份undo表空间、控制文件trace备份

sys@ORCL> alter tablespace undotbs1 begin backup;
Tablespace altered.

[oracle@Lee orcl]$ cp undotbs01.dbf /backup/

sys@ORCL> alter database backup controlfile to trace as '/backup/ctl.sql' reuse;
Database altered.

2：关闭数据库，模拟控制文、undo损坏

sys@ORCL> shutdown abort;
ORACLE instance shut down.

[oracle@Lee orcl]$ mv control* /home

[oracle@Lee orcl]$ mv undotbs01.dbf /home

3：启动数据库，开始恢复数据库

idle> startup nomount;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size                  2257352 bytes
Variable Size             499125816 bytes
Database Buffers          281018368 bytes
Redo Buffers                2596864 bytes
idle> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 4674
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/orcl/redo01.log',
10      '/u01/app/oracle/oradata/orcl/redo012.log'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '/u01/app/oracle/oradata/orcl/redo02.log',
14      '/u01/app/oracle/oradata/orcl/redo022.log'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '/u01/app/oracle/oradata/orcl/redo03.log',
18      '/u01/app/oracle/oradata/orcl/redo032.log'
19    ) SIZE 50M BLOCKSIZE 512
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/u01/app/oracle/oradata/orcl/system01.dbf',
23    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
24    '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
25    '/u01/app/oracle/oradata/orcl/users01.dbf',
26    '/u01/app/oracle/oradata/orcl/example01.dbf',
27    '/u01/app/oracle/oradata/orcl/users02.dbf'
28  CHARACTER SET ZHS16GBK
29  ;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

此时就需要我们刚刚备份的undo来恢复了

idle> !cp /backup/undotbs01.dbf /u01/app/oracle/oradata/orcl
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 4674
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/orcl/redo01.log',
10      '/u01/app/oracle/oradata/orcl/redo012.log'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '/u01/app/oracle/oradata/orcl/redo02.log',
14      '/u01/app/oracle/oradata/orcl/redo022.log'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '/u01/app/oracle/oradata/orcl/redo03.log',
18      '/u01/app/oracle/oradata/orcl/redo032.log'
19    ) SIZE 50M BLOCKSIZE 512
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/u01/app/oracle/oradata/orcl/system01.dbf',
23    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
24    '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
25    '/u01/app/oracle/oradata/orcl/users01.dbf',
26    '/u01/app/oracle/oradata/orcl/example01.dbf',
27    '/u01/app/oracle/oradata/orcl/users02.dbf'
28  CHARACTER SET ZHS16GBK
29  ;
Control file created

查看检查点SCN和开始SCN

idle> select file#,status,checkpoint_change#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ -----------------------------------
         1 SYSTEM           403372481 /u01/app/oracle/oradata/orcl/system
                                      01.dbf

         2 RECOVER          403372481 /u01/app/oracle/oradata/orcl/sysaux
                                      01.dbf

          3 RECOVER          403372661 /u01/app/oracle/oradata/orcl/undotb
                                      s01.dbf

         4 RECOVER          403372481 /u01/app/oracle/oradata/orcl/users0
                                      1.dbf

         5 RECOVER          403372481 /u01/app/oracle/oradata/orcl/exampl
                                      e01.dbf

         6 RECOVER          403372481 /u01/app/oracle/oradata/orcl/users0
                                      2.dbf

idle> select file#,status,checkpoint_change#,name from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ -----------------------------------
         1 ONLINE           403372481 /u01/app/oracle/oradata/orcl/system
                                      01.dbf

         2 ONLINE           403372481 /u01/app/oracle/oradata/orcl/sysaux
                                      01.dbf

          3 ONLINE           403372661 /u01/app/oracle/oradata/orcl/undotb
                                      s01.dbf

         4 ONLINE           403372481 /u01/app/oracle/oradata/orcl/users0
                                      1.dbf

         5 ONLINE           403372481 /u01/app/oracle/oradata/orcl/exampl
                                      e01.dbf

         6 ONLINE           403372481 /u01/app/oracle/oradata/orcl/users0
                                      2.dbf

undo对应的SCN不一致，数据库需要恢复

idle> recover database;
Media recovery complete.
idle> alter database open;
Database altered.