undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lihuarongaini/article/details/99174026

undo表空间的数据文件丢失,如果没有备份的情况下,但是redo完好,这个时候可以这样恢复,下边给出一个例子。

 

undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/

 

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:13:13 2015

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select name from v$datafile;

 

NAME

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

/u03/app/oracle/oradata/ora1024g/system01.dbf

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf

/u03/app/oracle/oradata/ora1024g/users01.dbf

/u03/app/oracle/oradata/ora1024g/example01.dbf

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf

 

6 rows selected.

 

SQL> drop table bb;

 

Table dropped.

 

 

SQL> create table bb as select * from user_tables;

 

Table created.

 

SQL> insert into bb select * from user_tables;

 

707 rows created.

 

SQL>  select count(1) from bb;

 

  COUNT(1)

----------

      1413

 

SQL>

 

SQL>

 

 

SQL> ho rm   /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

 

SQL> shutdown abort;

ORACLE instance shut down.

 

 

SQL> startup;

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  2084616 bytes

Variable Size             130023672 bytes

Database Buffers          310378496 bytes

Redo Buffers                6303744 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'

 

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

 

SQL> set line 9999

SQL> col name format a100

SQL> select name,status,enabled from v$datafile;

 

NAME                                                                                                 STATUS  ENABLED

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

/u03/app/oracle/oradata/ora1024g/system01.dbf                                                        SYSTEM  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf                                                        ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/users01.dbf                                                         ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/example01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf                                                       ONLINE  READ WRITE

 

6 rows selected.

 

 

SQL> select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         3 OFFLINE OFFLINE FILE NOT FOUND                                                             0

 

 

SQL> alter database create datafile 2 as '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m;

 

Database altered.

 

SQL>  select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         2 ONLINE  ONLINE                                                                       1278091 12-MAR-15

 

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 2 needs media recovery

ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'

 

 

SQL>  select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         2 ONLINE  ONLINE                                                                       1278091 12-MAR-15

 

 

 

SQL> recover datafile 2;

ORA-00279: change 1278091 generated at 03/12/2015 20:14:19 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_31_%u_.arc

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

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1299920 generated at 03/12/2015 20:32:01 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_32_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_31_bj31wg6x_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1319924 generated at 03/12/2015 20:33:18 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_33_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_32_bj31wg77_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1339928 generated at 03/12/2015 20:33:59 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_34_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_33_bj31xqc4_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1359932 generated at 03/12/2015 20:35:20 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_35_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_34_bj3208q4_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1379936 generated at 03/12/2015 20:36:20 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_36_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_35_bj3224vc_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1399940 generated at 03/12/2015 20:37:20 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_37_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_36_bj32409g_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1419945 generated at 03/12/2015 20:40:48 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_38_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_37_bj32bj52_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1439949 generated at 03/12/2015 20:43:49 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_39_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_38_bj32j54p_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1459953 generated at 03/12/2015 20:45:50 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_40_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_39_bj32mygp_.arc' no longer needed for this recovery

 

 

ORA-00279: change 1479957 generated at 03/12/2015 20:48:27 needed for thread 1

ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_41_%u_.arc

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

ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_40_bj32rv2k_.arc' no longer needed for this recovery

 

 

Log applied.

Media recovery complete.

 

SQL> select name,status,enabled from v$datafile;

 

NAME                                                                                                 STATUS  ENABLED

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

/u03/app/oracle/oradata/ora1024g/system01.dbf                                                        SYSTEM  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf                                                        ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/users01.dbf                                                         ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/example01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf                                                       ONLINE  READ WRITE

 

6 rows selected.

 

SQL> select * from v$recover_file;

 

no rows selected

 

SQL> alter database open;

 

Database altered.

 

SQL> select count(1) from bb;

 

  COUNT(1)

----------

      706

 

 

 

 

展开阅读全文

Oracle数据库Redo故障的恢复

07-10

[size=18px][color=#FF0000]由于inactive日志文件组表示已经完成了检查点(dirty数据已经被写入数据文件)。数据库本身不会发生数据库丢失,如果在这个时候相应的redo丢失/损坏,可以通过clear重建日志文件组恢复。丢失active或current日志文件组的恢复分两种:一个是正常关闭数据库(如shutdown immediate),另一个是异常关闭数据库(如shutdown abort) [/color][/size]rnrnrn一.丢失inactive日志文件组的恢复:rnrn  由于inactive日志文件组表示已经完成了检查点(dirty数据已经被写入数据文件)。数据库本身不会发生数据库丢失,如果在这个时候相应的redo丢失/损坏,可以通过clear重建日志文件组恢复。rnrn  通过命令:rnrn  alter database clear logfile group nrnrn  如果数据库模式是archived的,则需要强制清除rnrn  alter database clear unarchived logfile group nrnrn  二.丢失active或current日志文件组的恢复:rnrn  丢失情况分两种:rnrn  一个是正常关闭数据库(如shutdown immediate)rnrn  另一个是异常关闭数据库(如shutdown abort)rnrn  1.在损失当前日志时,数据库是正常关闭状态。rnrn  由于shutdown immediate会执行全面的checkpoint,所以当前日志在实例恢复时可以不需要redornrn  在Oracle 8i中我们完全可以通过alter database clear logfile group n来进行恢复.rnrn  但是在Oracle 9i中,则可能无法对current的redo日志进行clear,需要通过recover database until cancel恢复后(必须要做的)rnrn  用resetlogs选项打开。rnrn  比如:rnrn  alter database clear logfile group nrnrn  recover database until cancel;rnrn  alter database open resetlogs;rnrn  2.在损失当前日志时,数据库是异常关闭的:rnrn  这种情况下,由于没有在执行全面检查点时,数据库就已经关闭了,那么Oracle在进行实例恢复的时候必须要求当前的日志,否则Oracle数据库将无法open.rnrn  这样的情况下,我们通常需要从备份中恢复数据文件,通过应用归档日志进行向前推演。直到最后一个完好的日志文件,然后可以通过resetlogs启动数据库完成恢复。那么丢失的数据则是被损坏的日志文件中的数据。rnrn  注意:_allow_resetlogs_corruption是Oracle中的一个隐含参数,如果系统实在不能resetlogs方式打开的后只能出此下策,在pfile进行相应设置打开数据库。该函数的含义是,允许在破坏一致性的情况下强制重置日志,打开数据库。_allow_resetlogs_corruption将使用所有数据文件最旧的SCN打开数据库,所以通常来讲需要保证SYSTEM表空间拥有最旧的SCN。在强制打开数据库之后,可能因为各种原因会有ora-600rnrn 论坛

没有更多推荐了,返回首页