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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- Oracle Redo丟失恢復方案Oracle
- 【BBED】丟失歸檔檔案情況下的恢復
- SYSTEM 表空間管理及備份恢復
- dg丟失歸檔，使用rman增量備份恢復
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- MySQL異常恢復之無主鍵情況下innodb資料恢復的方法MySql資料恢復
- oracle drop table purge無備份bbed恢復(3/3)Oracle
- 電腦檔案丟失資料恢復資料恢復
- 丟失的隨身碟檔案如何恢復？
- DATA GUARD主庫丟失資料檔案的恢復(3)
- oracle drop table purge無備份bbed恢復(1/3)Oracle
- oracle drop table purge無備份bbed恢復(2/3)Oracle
- XFS檔案系統的備份、恢復、修復
- Oracle RMAN 表空間恢復Oracle
- RAC備份恢復之Voting備份與恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 【伺服器資料恢復】XFS檔案系統分割槽分割槽丟失無法訪問的資料恢復案例伺服器資料恢復
- 12 使用RMAN備份和恢復檔案
- Linux中XFS檔案系統的備份，恢復，修復Linux
- 表空間TSPITR恢復-實驗
- 【Oracle 恢復表空間】 實驗Oracle
- innobackupex 部分表備份和恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- MySQL 非常規恢復與物理備份恢復MySql
- 檔案的基本管理和XFS檔案系統備份恢復