当前位置:数据库 > Oracle >>

oracle用老的控制文件备份恢复新建的数据文件

oracle用老的控制文件备份恢复新建的数据文件
 
备份控制文件并在备份后新建表空间和数据:
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.sql';
 
Database altered.
 
SQL> create tablespace tbs_tbs datafile '/database/oradata/skyread/tbs_tbs.dbf' size 10M;
 
Tablespace created.
 
SQL> create table t02 tablespace tbs_tbs as select * from dict;                        
Table created
干净关闭数据库,模拟控制文件损坏:
SQL> shutdown immediate;
oracle@readerlogdb-> mv tbs_tbs.dbf tbs_tbs.dbf.bak
oracle@readerlogdb-> mv control01.ctl control01.ctl.bak
oracle@readerlogdb-> mv control02.ctl control02.ctl.bak
oracle@readerlogdb-> mv control03.ctl control03.ctl.bak
 
SQL> startup;
ORACLE instance started.
 
Total System Global Area           5049942016 bytes
Fixed Size                            2090880 bytes
Variable Size                      1375733888 bytes
Database Buffers                   3657433088 bytes
Redo Buffers                         14684160 bytes
ORA-00205: error in identifying control file, check alert log for more info
 
这个门用的是刚才备份的脚本新建,可见里面无 tbs_tbs这个数据文件:
SQL>CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 5
    MAXDATAFILES 1000
    MAXINSTANCES 8
    MAXLOGHISTORY 2337
LOGFILE
  GROUP 1 '/database/oradata/skyread/redo01.log'  SIZE 512M,
  GROUP 2 '/database/oradata/skyread/redo02.log'  SIZE 512M,
  GROUP 3 '/database/oradata/skyread/redo03.log'  SIZE 512M,
  GROUP 4 '/database/oradata/skyread/redo04.log'  SIZE 512M,
  GROUP 5 '/database/oradata/skyread/redo05.log'  SIZE 512M
DATAFILE
  '/database/oradata/skyread/system01.dbf',
  '/database/oradata/skyread/tbs_test.dbf',
  '/database/oradata/skyread/sysaux01.dbf',
  '/database/oradata/skyread/users01.dbf',
  '/database2/oradata/skyread/TBS_MRPMUSIC01.dbf',
  '/database/oradata/skyread/sf01.dbf',
  '/database2/oradata/skyread/undotbs02'
CHARACTER SET UTF8;
这里由于数据文件模拟破坏了,但是还是打开数据库,只不过tbs_tbs这个表空间的数据无法使用
SQL> alter database open;
Database altered.
标红的部分就是我们需要恢复的数据文件,这里由于控制文件备份的时候还没有这个表空间,不知道数据文件所以无法recover,当然由于我们刚才模拟损坏,rename的时候提示没有tbs_tbs.dbf文件
SQL> select file#,name,CHECKPOINT_CHANGE# ,status from v$datafile;
 
               FILE# NAME                                                 CHECKPOINT_CHANGE# STATUS
-------------------- -------------------------------------------------- -------------------- -------
                   1 /database/oradata/skyread/system01.dbf                     122694852856 SYSTEM
                   2 /database/oradata/skyread/tbs_test.dbf                     122694852856 ONLINE
                   3 /database/oradata/skyread/sysaux01.dbf                     122694852856 ONLINE
                   4 /database/oradata/skyread/users01.dbf                      122694852856 ONLINE
                   5 /app/oracle/product/10.2.0/db_1/dbs/MISSING00005                      0 RECOVER
                  18 /database2/oradata/skyread/TBS_MRPMUSIC01.dbf              122694852856 ONLINE
                  26 /database/oradata/skyread/sf01.dbf                         122694852856 ONLINE
                  31 /database2/oradata/skyread/undotbs02                       122694852856 ONLINE
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005'
 
SQL> alter database rename file '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/database/oradata/skyread/tbs_tbs.dbf';
alter database rename file '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/database/oradata/skyread/tbs_tbs.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 5 - new file '/database/oradata/skyread/tbs_tbs.dbf' not found
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
将数据文件进行恢复并ONLINE:
oracle@readerlogdb-> mv tbs_tbs.dbf.bak tbs_tbs.dbf
SQL> alter database rename file '/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/database/oradata/skyread/tbs_tbs.dbf';
Database altered.
SQL> recover datafile 5; 
Media recovery complete.
SQL> select count(*) from t02;
select count(*) from t02
                     *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/database/oradata/skyread/tbs_tbs.dbf'
 
SQL>  select file#,name,CHECKPOINT_CHANGE# ,status from v$datafile;
 
               FILE# NAME     &nb
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,