ReName or ReCreating Data Files to Recover

最新推荐文章于 2025-11-24 12:14:31 发布

原创最新推荐文章于 2025-11-24 12:14:31 发布 · 105 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

Oracle 专栏收录该内容

193 篇文章

订阅专栏

本文介绍Oracle数据库中使用旧控制文件进行手动恢复的过程，包括重新创建和重命名数据文件的方法。通过具体步骤展示如何解决恢复过程中遇到的问题，如UNNAMED和MISSING文件的处理。

手动恢复时可能根据情况需要重命名或重建数据库来进行恢复，RMAN完全不需要额外手动操作，可同时参考《恢复表空间或数据文件与使用控制文件测试》

手动恢复不会创建不存在于控制文件中的数据文件或表空间，会在控制文件中将此文件标识为UNNAMEDnnnnn(实际不存在此文件)并中止恢复，需要手动创建数据文件后继续恢复
对于不存在控制文件但存在于数据字典的数据文件，会在控制文件中将此文件标识为MISSINGnnnnn(实际不存在此文件)，需要重命令此文件后继续恢复

After you create a control file and attempt to mount and open the database, the database performs a data dictionary check against the files listed in the control file. For each file that is not listed in the CREATE CONTROLFILE statement but is present in the data dictionary, an entry is created for them in the control file. These files are named as MISSINGnnnnn, where nnnnn is a 5-digit number starting with 0.

如果有多个文件被命名为UNAMED或MISSING，导致与源数据文件对不上号需要查看ALTER.LOG信息再创建或重命令

If multiple unnamed files exist, then determine which unnamed file corresponds to which data file by using one of these methods:

Open the alert_SID.log, which contains messages about the original file location for each unnamed file.
Derive the original file location of each unnamed file from the error message and V$DATAFILE: each unnamed file corresponds to the file in the error message with the same file number.

（一）Re-Create Data files to Recover

情景：数据文件备份 --> 控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句

目标：使用旧的控制文件恢复创建的表空间或数据文件

You cannot re-create any of the data files for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo is not available.

备份全库

SQL> Alter database begin backup;

$ cp -r orcl orclbak

SQL> Alter database end backup;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/cf.bak' REUSE;

创建表空间与测试表

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

执行恢复

SQL> startup nomount

SQL> show parameter control

NAME VALUE

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

control_files /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl

$ cp /u01/app/oracle/cf.bak /u01/app/oracle/oradata/orcl/control01.ctl

$ cp /u01/app/oracle/cf.bak /u01/app/oracle/oradata/orcl/control02.ctl

SQL> alter database mount;

$ cp orclbak/*.dbf orcl

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 5349219 generated at 07/15/2021 17:20:00 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_15/o1_mf_1_1_%u_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 24: '/u01/app/oracle/oradata/orcl/tools01.dbf'

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00024

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00024' AS '/u01/app/oracle/oradata/orcl/tools01.dbf';

此语句会创建一个空间文件，不用指定SIZE

This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old data file is renamed as the new data file.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 5349375 generated at 07/15/2021 17:24:01 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_15/o1_mf_1_1_%u_.arc

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

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

Log applied.

Media recovery complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> SELECT * FROM T1; --111

SQL> select name from v$datafile;

NAME

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

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

（二）Re-Name Data files to Recover

情景：控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句 --> 全备

目标：使用旧的控制文件恢复到全备之后状态

SQL> alter database backup controlfile to '/u01/app/oracle/cf.bak';

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/orcl/tools01.dbf' size 20m;

SQL> create table t1(id number) tablespace tools;

SQL> insert into t1 values(111);

SQL> commit;

SQL> alter database begin backup;

$ cp -r orcl/ orcl-bak

SQL> alter database end backup;

进行恢复

SQL> startup nomount force;

SQL> show parameter control

NAME VALUE

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

control_files /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl

$ cp cf.bak /u01/app/oracle/oradata/orcl/control01.ctl

$ cp cf.bak /u01/app/oracle/oradata/orcl/control02.ctl

SQL> alter database mount;

$ cp orcl-bak/*.dbf orcl

SQL> recover automatic database using backup controlfile;

ORA-00279: change 5464510 generated at 07/16/2021 09:32:50 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc

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

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc

' no longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 27: '/u01/app/oracle/oradata/orcl/tools01.dbf'

ORA-01112: media recovery not started

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00027

$ ls /u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00027

ls: cannot access /u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00027: No such file or directory

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00027' TO '/u01/app/oracle/oradata/orcl/tools01.dbf';

SQL> recover automatic database using backup controlfile;

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

SQL> select name from v$datafile;

NAME

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

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

SQL> select * from t1; --111