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

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>


上一篇:java面试一日一题:binlog undolog redolog的区别


下一篇:【等待事件】日志类 等待事件(4.5)--log file sequential read