ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle 11g 表空间的误删除, 当前的控制文件不识别该表空间

Oracle 11g 表空间的误删除, 当前的控制文件不识别该表空间

原创 Oracle 作者：LuiseDalian 时间：2014-05-05 07:16:56 0 删除 编辑
场景描述：表空间的误删除, 当前的控制文件不识别该表空间，必须用能识别该表空间的备份的控制文件

--做3次日志切换

--查看归档日志信息, 注意resetlogs前后,incarnation的变化

sys@TESTDB11>select sequence#, name from v$archived_log;

 

 SEQUENCE# NAME

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

        93 /archive1/1_93_813665348.dbf

        93 /archive2/1_93_813665348.dbf

        94 /archive1/1_94_813665348.dbf

        94 /archive2/1_94_813665348.dbf

        95 /archive1/1_95_813665348.dbf

        95 /archive2/1_95_813665348.dbf

        94 /archive1/1_94_813665348.dbf

        94 /archive2/1_94_813665348.dbf

        95 /archive1/1_95_813665348.dbf

        95 /archive2/1_95_813665348.dbf

        96 /archive1/1_96_813665348.dbf

        96 /archive2/1_96_813665348.dbf

         1 /archive1/1_1_823322444.dbf

         1 /archive2/1_1_823322444.dbf

 

14 rows selected.

 

--查看当前表空间的信息

sys@TESTDB11>select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

 

6 rows selected.

 

--创建一个新的表空间并在该表空间上创建一张表

 

sys@TESTDB11>create tablespace newts datafile '/oradata/newts01.dbf' size 20m;

 

Tablespace created.

 

scott@TESTDB11>create table tab_newts tablespace newts as select * from salgrade;

 

Table created.

 

--手工备份该表空间

sys@TESTDB11>alter tablespace newts begin backup;

 

[oracle@S1011:/export/home/oracle]$ cp /oradata/newts01.dbf /pooldisk02/backup01/inconsistent/

 

sys@TESTDB11>alter tablespace newts end backup;

 

--把该表空间存在时的控制文件备份出来

sys@TESTDB11>alter database backup controlfile to '/pooldisk02/backup01/inconsistent/control_newsts.bak';

 

Database altered.

 

--做3次日志切换

--查看当前SCN，恢复时就恢复到这个SCN

sys@TESTDB11>select current_scn from v$database;

 

CURRENT_SCN

-----------

    2529246

 

1 row selected.

 

--表空间误删除，包括相应的对象和数据文件

sys@TESTDB11>drop tablespace newts including contents and datafiles;

 

Tablespace dropped.

 

--进行不完全恢复，是要还原所有的数据文件，然后执行不完全恢复，但当前的控制文件不认识newts表空间

sys@TESTDB11>select name from v$tablespace;

 

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

EXAMPLE

TEMP

 

6 rows selected.

 

--数据文件也不包括了

sys@TESTDB11>select name from v$datafile;

 

NAME

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

/oradata/system01.dbf

/oradata/sysaux01.dbf

/oradata/undotbs01.dbf

/oradata/users01.dbf

/oradata/example01.dbf

 

5 rows selected.

 

--因此需要使用一个可以识别该表空间的备份的控制文件来恢复

 

sys@TESTDB11>shutdown abort;

ORACLE instance shut down.

 

--还原控制文件和数据文件

[oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/control_newsts.bak /u01/app/oracle/oradata/TestDB11/control01.ctl

[oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/control_newsts.bak /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

 

[oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/*.dbf /oradata

 

sys@TESTDB11>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

 

--查看数据文件中检查点

idle>col name for a60

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

 

NAME                                                         CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                                                   2526335

/oradata/sysaux01.dbf                                                   2526361

/oradata/undotbs01.dbf                                                  2526381

/oradata/users01.dbf                                                    2526397

/oradata/example01.dbf                                                  2526409

/oradata/newts01.dbf                                                    2529022

 

6 rows selected.

 

--恢复

idle>recover database using backup controlfile until change 2529246;

ORA-00279: change 2526335 generated at 08/13/2013 04:43:44 needed for thread 1

ORA-00289: suggestion : /archive2/1_1_823322444.dbf

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

 

 

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

auto

ORA-00279: change 2527038 generated at 08/13/2013 04:56:13 needed for thread 1

ORA-00289: suggestion : /archive2/1_2_823322444.dbf

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

ORA-00278: log file '/archive2/1_1_823322444.dbf' no longer needed for this recovery

 

 

ORA-00279: change 2527103 generated at 08/13/2013 04:58:35 needed for thread 1

ORA-00289: suggestion : /archive2/1_3_823322444.dbf

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

ORA-00278: log file '/archive2/1_2_823322444.dbf' no longer needed for this recovery

 

 

ORA-00279: change 2529206 generated at 08/13/2013 05:30:22 needed for thread 1

ORA-00289: suggestion : /archive2/1_4_823322444.dbf

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

ORA-00278: log file '/archive2/1_3_823322444.dbf' no longer needed for this recovery

 

 

ORA-00279: change 2529221 generated at 08/13/2013 05:31:02 needed for thread 1

ORA-00289: suggestion : /archive2/1_5_823322444.dbf

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

ORA-00278: log file '/archive2/1_4_823322444.dbf' no longer needed for this recovery

 

 

ORA-00279: change 2529225 generated at 08/13/2013 05:31:04 needed for thread 1

ORA-00289: suggestion : /archive2/1_6_823322444.dbf

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

ORA-00278: log file '/archive2/1_5_823322444.dbf' no longer needed for this recovery

 

 

ORA-00308: cannot open archived log '/archive2/1_6_823322444.dbf'

ORA-27037: unable to obtain file status

Solaris-AMD64 Error: 2: No such file or directory

Additional information: 3

 

--缺少日志，判断当前的数据文件中检查点，没有到达指定位置

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

 

NAME                                                         CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                                                   2529225

/oradata/sysaux01.dbf                                                   2529225

/oradata/undotbs01.dbf                                                  2529225

/oradata/users01.dbf                                                    2529225

/oradata/example01.dbf                                                  2529225

/oradata/newts01.dbf                                                    2529225

 

6 rows selected.

--查看当前的联机日志，确定哪一个没有归档，需要的信息就在其中

idle>select sequence#, group#, status from v$log;

 

 SEQUENCE#     GROUP# STATUS

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

         1          1 INACTIVE

         3          3 CURRENT

         2          2 INACTIVE

 

idle>col member for a50

idle>select group#, member from v$logfile;

 

    GROUP# MEMBER

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

         2 /u01/app/oracle/oradata/TestDB11/redo02.log

         1 /u01/app/oracle/oradata/TestDB11/redo01.log

         3 /u01/app/oracle/oradata/TestDB11/redo03.log      

 

        

idle>recover database using backup controlfile until change 2529246;

ORA-00279: change 2529225 generated at 08/13/2013 05:31:04 needed for thread 1

ORA-00289: suggestion : /archive2/1_6_823322444.dbf

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

 

 

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

/u01/app/oracle/oradata/TestDB11/redo03.log

Log applied.

Media recovery complete.       

 

--开库，确认已经恢复到删除表空间之前

Alter database open resetlogs;

 

scott@TESTDB11>select * from tab_newts;

 

     GRADE      LOSAL      HISAL

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

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

--查看表空间和相应的数据文件

sys@TESTDB11>col file_name for a30

sys@TESTDB11>select tablespace_name, file_name from dba_data_files;

 

TABLESPACE_NAME                FILE_NAME

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

EXAMPLE                        /oradata/example01.dbf

USERS                          /oradata/users01.dbf

UNDOTBS1                       /oradata/undotbs01.dbf

SYSAUX                         /oradata/sysaux01.dbf

SYSTEM                         /oradata/system01.dbf

NEWTS                          /oradata/newts01.dbf

 

6 rows selected.

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

0
0
上一篇： Oracle 11g 表空间的误删除前传, 有关控制文件的备份和恢复（预热）
下一篇： Oracle 11g 闪回查询
请登录后发表评论 登录
全部评论
LuiseDalian

注册时间：2012-02-06

  • 博文量
    1986
  • 访问量
    6040882

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593