用DROP表空间前备份的控制文件来恢复被DROP的表空间

最新推荐文章于 2024-09-14 19:55:29 发布

cuanyileng9226

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量105

点赞数

文章标签：数据库

采用数据文件新建后，该文件号最后一次变成1前，二进制控制文件备份恢复新建数据文件

文件号最后一次变成1，也就是说FILE$基表中的STATUS$字段最后一次变为1，指这个数据文件最后一次被删除之前撒

SQL> select file#,status$,CRSCNBAS from file$;

     FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          2     673840
         7          2     676237               数据文件7在线

7 rows selected.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select file#,status$,CRSCNBAS from file$;

     FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          2     673840
         7          1     676237               数据文件7已被删除

7 rows selected.

SQL> create tablespace test3 datafile '/u01/app/oracle/oradata/shujukuai/test3.dbf' size 10m;

Tablespace created.

SQL> select file#,status$,CRSCNBAS from file$;

     FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          2     673840
         7          2     691393                  数据文件7又在线

7 rows selected.

SQL> create table c tablespace test3 as select * from dict;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
691503

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to '/u01/backup_dir/control.ctl'; 这里二进制控制文件

Database altered.

SQL> drop tablespace test3 including contents and datafiles; 删除表空间test3以及数据文件

Tablespace dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里去删除控制文件，还原刚刚备份的控制文件

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@RHEL5 shujukuai]$ rm -rf /u01/app/oracle/oradata/shujukuai/control0*

[oracle@RHEL5 backup_dir]$ cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control01.ctl
[oracle@RHEL5 backup_dir]$ cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control02.ctl
[oracle@RHEL5 backup_dir]$ cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control03.ctl
[oracle@RHEL5 backup_dir]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 1 07:39:22 2010

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             121636312 bytes
Database Buffers          155189248 bytes
Redo Buffers                6303744 bytes
SQL> alter database mount;

Database altered.

SQL> col dname for a50
SQL> select substr(name, 1, 60) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,CREATION_CHANGE#,status from v$datafile;

DNAME                                              CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system01.dbf                 691503                       676798                5 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf                691503                       676798           600647 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf                 691503                       676798             6678 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf                  691503                       676798            10685 ONLINE
/u01/app/oracle/oradata/shujukuai/tbs01.dbf                    691503                       676798           631813 ONLINE
/u01/app/oracle/oradata/shujukuai/test2.dbf                    691503                       676798           673840 ONLINE
/u01/app/oracle/oradata/shujukuai/test3.dbf                    691503                            0           691393 ONLINE

7 rows selected.

SQL> alter database create datafile 7;

Database altered.

SQL> select file#,status,recover,fuzzy from v$datafile_header;

     FILE# STATUS REC FUZ
---------- ------- --- ---
         1 ONLINE      NO
         2 ONLINE      NO
         3 ONLINE      NO
         4 ONLINE      NO
         5 ONLINE      NO
         6 ONLINE      NO
         7 ONLINE      NO

7 rows selected.

SQL> recover database using backup controlfile until change 691503; 这里，我们就恢复到了这个SCN点撒，那被删除前的这个表空间的数据不是回来了么，那我把这个表空间的数据导出来，然后在再导入正在使用的库中去。就完成了被删除的表空间恢复了哈

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

转载于:http://blog.itpub.net/21158541/viewspace-666730/