記一次undo表空間資料塊恢復
[root@project ~]#su - oracle
[oracle@project ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 21 14:00:11 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 973079128 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 407 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
SQL>
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@project ~]$
[oracle@project ~]$
[oracle@project ~]$
[oracle@project ~]$
[oracle@project ~]$
[oracle@project ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 21 14:14:52 2013
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> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
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@project ~]$
[oracle@project ~]$ exit
logout
[root@project ~]#
[root@project ~]#ps -ef | grep ora_
root 8060 6076 0 14:15 pts/0 00:00:00 grep ora_
[root@project ~]#su - oracle
[oracle@project ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 21-FEB-2013 14:15:28
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 21-FEB-2013 14:07:36
Uptime 0 days 0 hr. 7 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/ora10/product/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=project)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@project ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 21 14:15:36 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 973079128 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 407 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/ora10/product/dbs/arch
Oldest online log sequence 12
Current log sequence 14
SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
SQL> col name format a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /oracle/ora10/oradata/oram/system01.dbf
2 /oraundo/undotbs01.dbf
3 /oracle/ora10/oradata/oram/sysaux01.dbf
4 /oracle/ora10/oradata/oram/users01.dbf
5 /orax/task01.dbf
6 /oray/task01.dbf
7 /orax/tbs_testdata01.dbf
7 rows selected.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- --------------------------------------------- -------
1 /oracle/ora10/oradata/oram/system01.dbf SYSTEM
2 /oraundo/undotbs01.dbf ONLINE
3 /oracle/ora10/oradata/oram/sysaux01.dbf ONLINE
4 /oracle/ora10/oradata/oram/users01.dbf ONLINE
5 /orax/task01.dbf ONLINE
6 /oray/task01.dbf ONLINE
7 /orax/tbs_testdata01.dbf ONLINE
7 rows selected.
SQL> alter tablespace undo offline;
alter tablespace undo offline
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database datafile 2 offline;
alter database datafile 2 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------- - -------
1 /oracle/ora10/oradata/oram/system01.dbf SYSTEM
2 /oraundo/undotbs01.dbf ONLINE
3 /oracle/ora10/oradata/oram/sysaux01.dbf ONLINE
4 /oracle/ora10/oradata/oram/users01.dbf ONLINE
5 /orax/task01.dbf ONLINE
6 /oray/task01.dbf ONLINE
7 /orax/tbs_testdata01.dbf ONLINE
7 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 407 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
SQL> recovery datafile 2;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> alter database recovery datafile 2;
alter database recovery datafile 2
*
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected
SQL> drop tablespace undo;
drop tablespace undo
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database drop datafile 2;
alter database drop datafile 2
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> alter database datafile 2 offline;
alter database datafile 2 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 407 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> 資料庫成功開啟！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-754448/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 記一次sysaux表空間壞塊修復UX
- UNDO 表空間檔案損壞的恢復
- rman恢復資料檔案 恢復表空間
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 表空間級資料庫備份恢復資料庫
- 改變資料庫undo表空間資料庫
- undo表空間檔案丟失恢復（1）--有備份
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- 直接刪除undo及temp表空間檔案後的資料庫恢復一例資料庫
- Oracle RMAN 表空間恢復Oracle
- mysql 無備份恢復drop資料-共享表空間MySql
- 撤消表空間資料檔案丟失的恢復.
- 記一次ORACLE的UNDO表空間爆滿分析過程Oracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- oracle undo 表空間Oracle
- 理解UNDO表空間
- 記一次資料恢復資料恢復
- 恢復資料,資料塊恢復
- 10.管理UNDO表空間.(筆記)筆記
- 【UNDO】使用重建UNDO表空間方法解決UNDO表空間過大問題
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復
- undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- 【Oracle 恢復表空間】 實驗Oracle
- 恢復Oracle表空間的方法Oracle
- SYSAUX表空間管理及恢復UX
- oracle UNDO表空間一個bug——undo表空間快速擴充套件Oracle套件
- 自動undo表空間模式下切換新的undo表空間模式
- Oracle undo 表空間管理Oracle
- oracle重建UNDO表空間Oracle
- oracle undo表空間管理Oracle
- undo表空間總結
- Oracle 11g 資料庫恢復:場景9-系統預設undo表空間資料檔案損壞Oracle資料庫
- 非歸檔庫誤刪表空間後的資料恢復資料恢復
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 表空間TSPITR恢復-實驗