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>
?
?
?
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的undo表空间文件丢失恢复（3）--无备份无redo的情况下恢复的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 微软职位内部推荐-Senior Deve
- 下一篇： Android 监听Home键