ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 记一次undo表空间数据块恢复

记一次undo表空间数据块恢复

原创 Linux操作系统 作者:tian1982tian 时间:2013-02-21 15:40:55 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    163889