AI 助理
备案控制台
开发者社区 数据库 文章 正文

对于创建控制文件后MISSINGnnnn文件的处理

2017-11-16 1131
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
1 MISSINGnnnn文件产生的原因
数据字典file$中存在某个数据文件，而新创建控制文件时没有指定该数据文件，则数据库会在新的控制文件中创建一个该数据文件条目，文件名为MISSINGnnnn其中nnnn为十进制文件号
2 对于missingnnnn数据文件的处理
如果MISSSINGnnnn文件属于只读或者正常离线，则可通过重命名MISSINGnnnn文件为真实文件来进行处理。
如果MISSINGnnnn文件不属于只读或者正常离线，则需要通过重命名和介质恢复来处理。
如果file$中不存在该数据文件，而新创建控制文件时指定该数据文件，则数据库会在数据库中删除该数据文件，而数据文件还会存在于系统。
3 5个相关例子
例一：测试如果file$中不存在某个数据文件，而新建控制文件时指定该数据文件，则数据库会在数据库中删除该数据文件，而数据文件不会删除
例二：测试以noresetlogs方式创建控制文件，产生missingnnnn文件的处理方法：
例三：测试以noresetlogs方式创建控制文件+正常离线数据文件，产生missingnnnnn文件的处理过程
例四：测试以resetlogs方式创建控制文件+正常离线数据文件，产生missingnnnn文件的处理过程
例五：测试以resetlogs方式创建控制文件产生missingnnnn文件的处理过程
4 实验过程如下：        
例一：测试如果file$中不存在某个数据文件，而新建控制文件时指定该数据文件，则数据库会在数据库中删除该数据文件，而数据文件不会删除。
1 准备重建控制文件的语句如下：
[oracle@oracle CRM]$ cat /oracle/control.sql 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/zxb.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/zxa.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/zxc.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf',
  '/oracle/CRM2/CRM/jiujian.dbf'
CHARACTER SET ZHS16GBK
;
2 删除file$中关于file#=9的条目：
SQL> select file#,status$,ts#,relfile# from  file$;
     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          6          2
         3          2          2          3
         4          2          4          4
         5          2          6          5
         6          2          5          6
         7          2          6          7
         8          2          7       1024
         9          2         13          9
9 rows selected.
SQL> delete from file$ where file#=9;
1 row deleted.
SQL> commit;
Commit complete.
注意v$datafile 中存在file#=9的文件信息。
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
         9 /oracle/CRM2/CRM/jiujian.dbf             ONLINE
3 重建控制文件：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Control file created.   
4 打开数据库查看v$datafile视图确认结果
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open;
Database altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
这里已经没有file#=9的文件信息
8 rows selected.   
   
    
例二：测试以noresetlogs方式创建控制文件，产生missingnnnn文件的处理方法：
1 数据文件状态如下：
SQL> select file#,status$,ts#,relfile# from file$;
     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          6          2
         3          2          2          3
         4          2          4          4
         5          2          6          5
         6          2          5          6
         7          2          6          7
         8          2          7       1024
8 rows selected.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/CRM2/zxb.dbf           ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
2 准备重新创建控制文件的语句如下（注意以noresetlogs方式）
[oracle@oracle ~]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/zxa.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/zxc.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
注意上面的语句中少了数据文件/oracle/CRM2/zxb.dbf则重建控制文件，打开数据库会产生missingnnnn2的文件。
3 重建控制文件过程如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              88083328 bytes
Database Buffers          226492416 bytes
Redo Buffers                6365184 bytes
Control file created.
4 查看产生的missingnnnn文件注意状态为recover如下：
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open;
Database altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/app/db1/dbs/MISSING000 RECOVER
           02
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
5 重命名数据文件：
SQL> alter tablespace zx rename datafile '/oracle/app/db1/dbs/MISSING00002' to '/oracle/CRM2/zxb.dbf';
Tablespace altered.
6 提示数据文件需要恢复：
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/CRM2/zxb.dbf           RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
7 恢复数据文件：
SQL> recover datafile 2;
Media recovery complete.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/CRM2/zxb.dbf           OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
SQL> alter database datafile 2 online;
Database altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/CRM2/zxb.dbf           ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
例三：测试以noresetlogs方式创建控制文件+正常离线数据文件，产生missingnnnnn文件的处理过程
1 重建控制文件的语句：
[oracle@oracle ~]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
注意上面创建语句少了表空间zx的3个数据文件
2 以normal方式离线表空间zx制造数据文件的正常离线。
SQL> alter tablespace zx offline;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                           STATUS
---------- ------------------------------ -------
         1 /oracle/CRM2/system1.dbf       SYSTEM
         2 /oracle/CRM2/zxb.dbf           OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE
         4 /oracle/CRM2/CRM/users01.dbf   ONLINE
         5 /oracle/CRM2/zxa.dbf           OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE
         7 /oracle/CRM2/zxc.dbf           OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE
8 rows selected.
3 重建控制文件：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Control file created.
4 打开数据库查看产生的missingnnnn文件：
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter  database open;
Database altered.

SQL> select file#,name,status from v$datafile
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/app/db1/dbs/MISSING00002         OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/app/db1/dbs/MISSING00005         OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/app/db1/dbs/MISSING00007         OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
5 重命名数据文件：
SQL> alter tablespace zx rename datafile '/oracle/app/db1/dbs/MISSING00002',
  2                                      '/oracle/app/db1/dbs/MISSING00005',
  3                                      '/oracle/app/db1/dbs/MISSING00007'
  4                               to     '/oracle/CRM2/zxb.dbf',
  5                                      '/oracle/CRM2/zxa.dbf',
  6                                      '/oracle/CRM2/zxc.dbf';
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
SQL> alter tablespace zx online;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
例四：测试以resetlogs方式创建控制文件+正常离线数据文件后产生missingnnnn文件的处理
1 重建控制文件的语句（注意以resetlogs方式）
[oracle@oracle ~]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM"  RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
注意以上重建控制文件语句少了zx表空间的3个数据文件
2 正常离线数据文件如下：
SQL> alter tablespace zx offline normal;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
3 开始重建控制文件如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Control file created.
4 查看产生的missingnnn文件注意状态是offline的
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2148787301 generated at 11/07/2012 00:18:06 needed for thread
1
ORA-00289: suggestion : /oracle/archive/1_134_797756641.dbf
ORA-00280: change 2148787301 for thread 1 is in sequence #134

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> col name for a40
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
          2 /oracle/app/db1/dbs/MISSING00002         OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
          5 /oracle/app/db1/dbs/MISSING00005         OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
          7 /oracle/app/db1/dbs/MISSING00007         OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
5重命名missing数据文件
SQL>  alter tablespace zx rename datafile  '/oracle/app/db1/dbs/MISSING00002',
  2                                        '/oracle/app/db1/dbs/MISSING00005',
  3                                        '/oracle/app/db1/dbs/MISSING00007'
  4                                to      '/oracle/CRM2/zxb.dbf',
  5                                        '/oracle/CRM2/zxa.dbf',
  6                                        '/oracle/CRM2/zxc.dbf';
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
          2 /oracle/CRM2/zxb.dbf                     OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
          5 /oracle/CRM2/zxa.dbf                     OFFLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
          7 /oracle/CRM2/zxc.dbf                     OFFLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
SQL> alter tablespace zx online;
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
例五：测试以resetlogs方式创建控制文件产生missingnnnn文件的处理过程：
1 查看数据文件的状态如下
SQL> select file#,status$,ts#,relfile# from file$;
     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          6          2
         3          2          2          3
         4          2          4          4
         5          2          6          5
         6          2          5          6
         7          2          6          7
         8          2          7       1024
8 rows selected.
SQL> col name for a40
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
2 重建控制文件的语句如下：
[oracle@oracle ~]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM"  RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
注意以上重建控制文件语句少了zx表空间的3个数据文件
3 开始重建控制文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Control file created.
4 查看产生的missingnnnn数据文件，注意状态提示需要recover
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2148787890 generated at 11/07/2012 00:35:11 needed for thread
1
ORA-00289: suggestion : /oracle/archive/1_1_798682919.dbf
ORA-00280: change 2148787890 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
          2 /oracle/app/db1/dbs/MISSING00002         RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
          5 /oracle/app/db1/dbs/MISSING00005         RECOVER
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/app/db1/dbs/MISSING00007         RECOVER
          8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
5 重命名misssingnnnnn数据文件
SQL>  alter tablespace zx rename datafile  '/oracle/app/db1/dbs/MISSING00002',
  2                                        '/oracle/app/db1/dbs/MISSING00005',
  3                                        '/oracle/app/db1/dbs/MISSING00007'
  4                                to      '/oracle/CRM2/zxb.dbf',
  5                                        '/oracle/CRM2/zxa.dbf',
  6                                        '/oracle/CRM2/zxc.dbf';
                                    
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
          2 /oracle/CRM2/zxb.dbf                     RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
          5 /oracle/CRM2/zxa.dbf                     RECOVER
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
          7 /oracle/CRM2/zxc.dbf                     RECOVER
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
6 注意这里恢复数据文件时会报错如下：
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [2],
[fhcrt =], [797369834], [cptim =], [0], []
ORA-01110: data file 2: '/oracle/CRM2/zxb.dbf'
很显应用日志对数据文件进行恢复失败！！！当然，我们使用bbed对数据文件头部进行修改后便可以对数据文件进行恢复
以下是更改数据文件头部以后再进行恢复不会报错了。
SQL> recover datafile 2
Media recovery complete.
SQL> recover datafile 5;
Media recovery complete.
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 2 online;
Database altered.
SQL> alter database datafile 5 online;
Database altered.
SQL> alter database datafile 7 online;
Database altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     ONLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE









本文转自 zhangxuwl 51CTO博客，原文链接：http://blog.51cto.com/jiujian/1052707，如需转载请自行联系原作者

文章标签：
数据库备份
关系型数据库
Oracle
数据库
余二五
目录
相关文章
郭一军_guoyJoe
|
Oracle 关系型数据库 数据库
创建数据文件：恢复一个备份中没有的数据文件
备份恢复
郭一军_guoyJoe
740 0
lfreeali
|
SQL Oracle 关系型数据库
[20171225]没有备份数据文件的恢复.txt
[20171225]没有备份数据文件的恢复.txt --//别人问的问题,增加了数据文件没有备份,如何恢复,实际上很简单,因为当前控制文件有记录建立时间只要从建立数据文件开始的 --//归档日志都存在恢复是没有任何问题的.
lfreeali
913 0
余二五
|
Oracle 关系型数据库 数据库
浅析控制文件的创建
余二五
910 0
lfreeali
|
Oracle 关系型数据库 数据库管理
[20161101]rman备份与数据文件变化7.txt
[20161101]rman备份与数据文件变化7.txt --//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？ --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.
lfreeali
1194 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20171123]rman备份与数据文件变化6.txt
[20171123]rman备份与数据文件变化6.txt --//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？ --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.
lfreeali
795 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171122]恢复数据文件块头5.txt
[20171122]恢复数据文件块头5.txt --//前几天做了恢复数据文件块头,通过备份文件直接取出文件块头,覆盖原来的数据块,然后修复. --//今天测试使用image copy来恢复.
lfreeali
1127 0
科技小能手
|
Oracle 关系型数据库 数据库
备份控制文件之后，添加数据文件,数据文件无备份丢失后的恢复
科技小能手
1384 0
技术小美
|
Oracle 关系型数据库 数据库
用备份控制文件做不完全恢复下的完全恢复（数据文件备份<旧>--新建表空间--控制文件备份<次新>--日志归档文件<新>）
技术小美
1088 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171114]恢复数据文件块头2.txt
[20171114]恢复数据文件块头2.txt --//曾经写过一篇[20161111]数据库文件头的修复.txt,但是利用大小相似的数据文件头覆盖来恢复,那是属于特种恢复.
lfreeali
1084 0
科技小能手
|
数据库 数据库连接
清理RMAN Catalog恢复目录
科技小能手
1316 0

热门文章

最新文章

  • 1
    多中心容灾实践：如何实现真正的异地多活？
  • 2
    Canal 数据同步（应用场景） | 学习笔记
  • 3
    你用过的网站前端优化的技术有哪些？
  • 4
    Confluence 6 那些文件需要备份
  • 5
    解决LR-Controller场景下1个用户运行出错
  • 6
    [IOS]本地化
  • 7
    Visual Studio Extensions for SharePoint Services, Really Cool !
  • 8
    基于链表编写“猫吃老鼠”
  • 9
    证码识别--type1
  • 10
    2012年我的关键词
  • 1
    「全网最细 + 实战源码案例」设计模式——外观模式
    94
  • 2
    《迈向绿色智能：探寻人工智能硬件可持续发展之路》
    37
  • 3
    《光存储与3D存储：开启人工智能硬件存储新时代》
    25
  • 4
    《解锁AI潜能：深度挖掘工程数据宝藏》
    33
  • 5
    《深度解析：VAEs如何重塑数据生成与重建格局》
    34
  • 6
    《AI 造梦：解锁虚拟场景与角色逼真丰富密码》
    27
  • 7
    jQuery+Slick插件实现游戏人物轮播展示切换源码
    21
  • 8
    网安入门之MySQL后端基础
    32
  • 9
    【03】优雅草央千澈详解关于APP签名以及分发-上架完整流程-第三篇安卓APP上架华为商店后面的步骤-华为应用商店相对比较麻烦一些-华为商店安卓上架
    31
  • 10
    OpenHands：能自主检索外部知识的 AI 编程工具，自动执行命令、网页浏览和生成代码等操作
    56

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 快照备份与恢复
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅