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>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984478/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復
- undo表空間檔案丟失恢復（1）--有備份
- REDO檔案丟失的恢復__沒有任何備份的情況
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- 非歸檔無備份下控制檔案丟失的恢復
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- 【恢復】Redo日誌檔案丟失的恢復
- 系統表空間檔案丟失無備份用控制程式碼的辦法恢復
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- 歸檔模式無備份丟失資料檔案後恢復模式
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- mysql無備份恢復-獨立表空間MySql
- 系統表空間資料檔案丟失，無備份，無重啟，通過控制程式碼恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 撤消表空間資料檔案丟失的恢復.
- 恢復案例：歸檔模式下丟失非系統表空間資料檔案的恢復模式
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- Oracle 無備份情況恢復ocr和olrOracle
- UNDO 表空間檔案損壞的恢復
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- INDEX表空間檔案丟失或者損壞的恢復Index
- 恢復案例：無歸檔，掉電，控制檔案全部丟失恢復
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- 控制檔案全部丟失，無備份，通過異機trace恢復
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- 備份恢復之資料檔案丟失
- mysql 無備份恢復drop資料-共享表空間MySql
- REDO檔案丟失或者損壞的恢復
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 【備份恢復】無備份線上恢復非關鍵資料檔案