记一次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博客 ” ，链接：https://blog.itpub.net/16400082/viewspace-754448/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
客户端连不上数据库
请登录后发表评论
登录
全部评论
