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

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

undo表空间的数据文件丢失,如果没有备份的情况下,而且redo也不可用,这个时候就要采用隐藏参数来恢复,下边给出一个例子。

 

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

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

 

[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> create undo tablespace undotbs2 datafile '/u03/app/oracle/oradata/ora1024g/undotbs02.dbf' size 5m autoextend on;

 

Tablespace created.

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 

 

SQL> create table bb as select * from user_tables;

 

Table created.

 

SQL> insert into bb select * from user_tables;

 

707 rows created.

 

SQL> shutdown abort;

ORACLE instance shut down.

SQL>

 

 

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

 

 

SQL> startup force;

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> alter system set undo_tablespace=undotbs2 scope=spfile;

 

System altered.

 

SQL> alter system set undo_management=manual scope=spfile;

 

System altered.

 

SQL> startup force mount;

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.

 

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>

SQL> alter database datafile 2 offline;

 

Database altered.

 

---注意这里undo的状态为recover

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

 

     FILE# NAME                                                                                                 STATUS  ENABLED

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

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

         2 /u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       RECOVER READ WRITE

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

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

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

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

 

6 rows selected.

 

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

 

 

SQL>

 

 

 

此时查看altert日志:

Thu Mar 12 18:16:17 2015

alter database open

Thu Mar 12 18:16:17 2015

Beginning crash recovery of 1 threads

parallel recovery started with 2 processes

Thu Mar 12 18:16:17 2015

Started redo scan

Thu Mar 12 18:16:17 2015

Completed redo scan

123 redo blocks read, 23 data blocks need recovery

Thu Mar 12 18:16:17 2015

Started redo application at

Thread 1: logseq 3, block 99

Thu Mar 12 18:16:17 2015

Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0

  Mem# 0: /u03/app/oracle/oradata/ora1024g/redo03.log

Thu Mar 12 18:16:17 2015

Completed redo application

Thu Mar 12 18:16:18 2015

Completed crash recovery at

Thread 1: logseq 3, block 222, scn 734292

23 data blocks read, 23 data blocks written, 123 redo blocks read

Thu Mar 12 18:16:18 2015

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=18, OS id=33684

Thu Mar 12 18:16:18 2015

ARC0: Archival started

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC1 started with pid=19, OS id=33686

Thu Mar 12 18:16:18 2015

Thread 1 advanced to log sequence 4 (thread open)

Thread 1 opened at log sequence 4

  Current log# 1 seq# 4 mem# 0: /u03/app/oracle/oradata/ora1024g/redo01.log

Successful open of redo thread 1

Thu Mar 12 18:16:18 2015

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Mar 12 18:16:18 2015

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

Thu Mar 12 18:16:18 2015

SMON: enabling cache recovery

Thu Mar 12 18:16:18 2015

ARC1: Becoming the heartbeat ARCH

Thu Mar 12 18:16:18 2015

db_recovery_file_dest_size of 2048 MB is 29.58% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Thu Mar 12 18:16:18 2015

Errors in file /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc:

ORA-00376: file 2 cannot be read at this time

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

Thu Mar 12 18:16:18 2015

Error 376 happened during db open, shutting down database

USER: terminating instance due to error 376

Instance terminated by USER, pid = 33662

ORA-1092 signalled during: alter database open...

 

查看文件: /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc

[root@rhel6_lhr ~]# more /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc

/u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc

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_HOME = /u03/app/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      rhel6_lhr

Release:        2.6.32-431.el6.x86_64

Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:        x86_64

Instance name: ora1024g

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 33662, image: oracle@rhel6_lhr (TNS V1-V3)

 

*** 2015-03-12 18:16:17.849

*** ACTION NAME:() 2015-03-12 18:16:17.849

*** MODULE NAME:(sqlplus@rhel6_lhr (TNS V1-V3)) 2015-03-12 18:16:17.849

*** SERVICE NAME:() 2015-03-12 18:16:17.849

*** SESSION ID:(159.3) 2015-03-12 18:16:17.849

Successfully allocated 2 recovery slaves

Using 550 overflow buffers per recovery slave

Thread 1 checkpoint: logseq 3, block 2, scn 713814

  cache-low rba: logseq 3, block 99

    on-disk rba: logseq 3, block 222, scn 714292

  start recovery at logseq 3, block 99, scn 0

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 61Kb in 0.12s => 0.50 Mb/sec

Total physical reads: 4096Kb

Longest record: 1Kb, moves: 0/284 (0%)

Change moves: 4/47 (8%), moved: 0Mb

Longest LWN: 38Kb, moves: 0/21 (0%), moved: 0Mb

Last redo scn: 0x0000.000ae633 (714291)

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

----- Recovery Hash Table Statistics ---------

Hash table buckets = 32768

Longest hash chain = 1

Average hash chain = 23/23 = 1.0

Max compares per lookup = 1

Avg compares per lookup = 176/225 = 0.8

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

*** 2015-03-12 18:16:17.970

KCRA: start recovery claims for 23 data blocks

*** 2015-03-12 18:16:17.999

KCRA: blocks processed = 23/23, claimed = 23, eliminated = 0

*** 2015-03-12 18:16:17.999

Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0

----- Recovery Hash Table Statistics ---------

Hash table buckets = 32768

Longest hash chain = 1

Average hash chain = 23/23 = 1.0

Max compares per lookup = 1

Avg compares per lookup = 191/199 = 1.0

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

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)

tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)

ORA-00376: file 2 cannot be read at this time

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

[root@rhel6_lhr ~]#

 

 

必须读取2号文件才能保证一致性,此时使用隐含参数:

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

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

 

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

 

Connected to an idle instance.

 

SQL> startup mount;

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.

SQL>  alter system set "_offline_rollback_segments"=true scope=spfile;

 

System altered.

 

 

SQL> startup force mount;

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.

SQL> alter database open;

 

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

 

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@rhel6_lhr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 20:36:56 2015

 

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

 

Connected to an idle instance.

 

SQL> startup mount;

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.

 

SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;

 

System altered.

 

SQL> alter database open;

 

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                                                       OFFLINE 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 segment_name,status,tablespace_name from dba_rollback_segs;

 

SEGMENT_NAME                   STATUS           TABLESPACE_NAME

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

SYSTEM                         ONLINE           SYSTEM

_SYSSMU10$                     OFFLINE          UNDOTBS1

_SYSSMU9$                      OFFLINE          UNDOTBS1

_SYSSMU8$                      OFFLINE          UNDOTBS1

_SYSSMU7$                      OFFLINE          UNDOTBS1

_SYSSMU6$                      OFFLINE          UNDOTBS1

_SYSSMU5$                      OFFLINE          UNDOTBS1

_SYSSMU4$                      OFFLINE          UNDOTBS1

_SYSSMU3$                      OFFLINE          UNDOTBS1

_SYSSMU2$                      OFFLINE          UNDOTBS1

_SYSSMU1$                      OFFLINE          UNDOTBS1

_SYSSMU20$                     OFFLINE          UNDOTBS2

_SYSSMU19$                     OFFLINE          UNDOTBS2

_SYSSMU18$                     OFFLINE          UNDOTBS2

_SYSSMU17$                     OFFLINE          UNDOTBS2

_SYSSMU16$                     OFFLINE          UNDOTBS2

_SYSSMU15$                     OFFLINE          UNDOTBS2

_SYSSMU14$                     OFFLINE          UNDOTBS2

_SYSSMU13$                     OFFLINE          UNDOTBS2

_SYSSMU12$                     OFFLINE          UNDOTBS2

_SYSSMU11$                     OFFLINE          UNDOTBS2

 

21 rows selected.

 

SQL> create undo tablespace undotbs1 datafile '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m autoextend on;

 

Tablespace created.

 

SQL> alter system set undo_tablespace=UNDOTBS1  scope=spfile;

 

System altered.

 

SQL> alter system set undo_management=auto  scope=spfile;

 

System altered.

 

SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';

 

System altered.

 

SQL> alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';

 

System altered.

 

SQL>

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

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.

Database opened.

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

 

 

 

展开阅读全文

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 论坛

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