undo表空间误删除,数据库无法打开,itpub搜遍,未解决
数据库版本 10.2.0.3.0
System name: Linux
Node name: node1
Release: 2.4.21-37.EL
Version: #1 Wed Sep 7 13:35:21 EDT 2005
Machine: i686
Instance name: oracle
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 1261852 bytes
Variable Size 180358884 bytes
Database Buffers 180355072 bytes
Redo Buffers 2928640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
############
alert_oracle.ora 报错
Errors in file /u01/app/oracle/admin/oracle/bdump/oracle_dbw0_1774.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Aug 24 09:59:04 2009
ORA-1157 signalled during: alter database open...
############
SQL> select status from v$instance;
STATUS
------------
MOUNTED
1、创建参数文件
SQL> create pfile='/home/oracle/test.ora' from spfile;
File created.
2、修改参数
vi /home/oracle/test.ora
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
3、以pfile启动数据库
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/home/oracle/test.ora'
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 1261852 bytes
Variable Size 184553188 bytes
Database Buffers 176160768 bytes
Redo Buffers 2928640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
4、将undo01文件脱机
SQL> alter database datafile '/u02/oradata/oracle/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
############
alert_oracle.ora
alter database open
Mon Aug 24 10:46:37 2009
Beginning crash recovery of 1 threads
Mon Aug 24 10:46:37 2009
Started redo scan
Mon Aug 24 10:46:38 2009
Completed redo scan
11596 redo blocks read, 803 data blocks need recovery
Mon Aug 24 10:46:38 2009
Started redo application at
Thread 1: logseq 98, block 289
Mon Aug 24 10:46:38 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 98 Reading mem 0
Mem# 0: /u02/oradata/oracle/redo04_1.log
Mon Aug 24 10:46:38 2009
Completed redo application
Mon Aug 24 10:46:38 2009
Completed crash recovery at
Thread 1: logseq 98, block 11885, scn 1730816
803 data blocks read, 803 data blocks written, 11596 redo blocks read
Mon Aug 24 10:46:38 2009
Thread 1 advanced to log sequence 99
Thread 1 opened at log sequence 99
Current log# 5 seq# 99 mem# 0: /u02/oradata/oracle/redo05_1.log
Successful open of redo thread 1
Mon Aug 24 10:46:38 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 24 10:46:38 2009
SMON: enabling cache recovery
Mon Aug 24 10:46:38 2009
Errors in file /u01/app/oracle/admin/oracle/udump/oracle_ora_2001.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
Mon Aug 24 10:46:38 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 2001
ORA-1092 signalled during: alter database open...
############
查看 数据文件状态
select file#||' '||name||' '||status from v$datafile;
FILE#||''||NAME||''||STATUS
--------------------------------------------------------------------------------
1 /u02/oradata/oracle/system01.dbf SYSTEM
2 /u02/oradata/oracle/undotbs01.dbf RECOVER
3 /u02/oradata/oracle/sysaux01.dbf ONLINE
4 /u02/oradata/oracle/users01.dbf ONLINE
5 /u02/oradata/oracle/wap.dbf ONLINE
8 /u02/oradata/oracle/test.dbf ONLINE
6 rows selected.
发现数据文件处于 RECOVER 状态
recover 数据文件，发现还是报错
SQL> recover datafile '/u02/oradata/oracle/undotbs01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
再次尝试打开数据库，仍然报错。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
======================
实在找不到解决的办法了,请各位帮助,谢谢！
我想只要能打开，exp/imp下就好，可是就是open不了，唉。。 用了的
SQL> startup pfile='/home/oracle/test.ora' mount;
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 1261852 bytes
Variable Size 150998756 bytes
Database Buffers 209715200 bytes
Redo Buffers 2928640 bytes
Database mounted.
SQL> show parameter resetlog
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
*._CORRUPTED_ROLLBACK_SEGMENTS= 这个不知道 _SYSSMU $ 这个的序号
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
这两个加了的
我现在的test.ora文件内容：
*.audit_file_dest='/u01/app/oracle/admin/oracle/adump'
*.background_dump_dest='/u01/app/oracle/admin/oracle/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u02/oradata/oracle/control01.ctl','/u02/oradata/oracle/control02.ctl','/home/oracle/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/oracle/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='oracle'
*.db_recovery_file_dest=''
*.db_recovery_file_dest_size=1
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracleXDB)'
*.job_queue_processes=10
*.local_listener=''
*.open_cursors=300
*.pga_aggregate_target=120586240
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=364904448
*.sga_target=361758720
*.shared_pool_size=138412032
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*.user_dump_dest='/u01/app/oracle/admin/oracle/udump'
_allow_resetlogs_corruption=true
oracle_ora_2001.trc
内容如下：
/u01/app/oracle/admin/oracle/udump/oracle_ora_2001.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2/db_1
System name: Linux
Node name: node1
Release: 2.4.21-37.EL
Version: #1 Wed Sep 7 13:35:21 EDT 2005
Machine: i686
Instance name: oracle
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 2001, image: oracle@node1 (TNS V1-V3)
*** 2009-08-24 10:46:37.938
*** SERVICE NAME) 2009-08-24 10:46:37.937
*** SESSION ID324.3) 2009-08-24 10:46:37.937
Thread 1 checkpoint: logseq 98, block 2, scn 1710411
cache-low rba: logseq 98, block 289
on-disk rba: logseq 98, block 11885, scn 1710816
start recovery at logseq 98, block 289, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 5798Kb in 0.22s => 25.74 Mb/sec
Total physical reads: 8192Kb
Longest record: 12Kb, moves: 0/4460 (0%)
Change moves: 7/81 (8%), moved: 0Mb
Longest LWN: 943Kb, moves: 1/37 (2%), moved: 0Mb
Last redo scn: 0x0000.001a1adf (1710815)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 803/802 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 3731/4607 = 0.8
----------------------------------------------
*** 2009-08-24 10:46:38.164
KCRA: start recovery claims for 803 data blocks
*** 2009-08-24 10:46:38.334
KCRA: blocks processed = 803/803, claimed = 803, eliminated = 0
*** 2009-08-24 10:46:38.335
Recovery of Online Redo Log: Thread 1 Group 4 Seq 98 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 803/802 = 1.0
Max compares per lookup = 2
Avg compares per lookup = 4520/4533 = 1.0
----------------------------------------------
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
正在整理数据，几个dml操作都没提交。
突然断电，重启后，磁盘阵列起不来了，因为空间不够，undo表空间的数据文件放在磁阵上的，结果就出现这种情况了。
其实不算是误删，只是和误删除的结果一样，为了看的方便，所以就写误删了。
这个磁阵拿去修了，估计要1-2个星期的，比较老的磁阵了。我们的数据库现在要用的，不可能等这么久的啊。
而且，修理的人说了，数据就不要抱太大期望了。。。
strings system01.dbf | grep _SYSSMU，找到了 _SYSSMU1$ -- _SYSSMU10$
修改了init文件，加上了
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/home/oracle/test.ora' mount;
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 1261852 bytes
Variable Size 150998756 bytes
Database Buffers 209715200 bytes
Redo Buffers 2928640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
还是老样子， alert 文件报错
alter database open
Tue Aug 25 09:44:48 2009
Beginning crash recovery of 1 threads
Tue Aug 25 09:44:48 2009
Started redo scan
Tue Aug 25 09:44:50 2009
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Tue Aug 25 09:44:50 2009
Started redo application at
Thread 1: logseq 118, block 2, scn 2110896
Tue Aug 25 09:44:50 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 118 Reading mem 0
Mem# 0: /u02/oradata/oracle/redo04_1.log
Tue Aug 25 09:44:50 2009
Completed redo application
Tue Aug 25 09:44:50 2009
Completed crash recovery at
Thread 1: logseq 118, block 3, scn 2130898
0 data blocks read, 0 data blocks written, 1 redo blocks read
Tue Aug 25 09:44:50 2009
Thread 1 advanced to log sequence 119
Thread 1 opened at log sequence 119
Current log# 5 seq# 119 mem# 0: /u02/oradata/oracle/redo05_1.log
Successful open of redo thread 1
Tue Aug 25 09:44:51 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 25 09:44:51 2009
SMON: enabling cache recovery
Tue Aug 25 09:44:51 2009
Errors in file /u01/app/oracle/admin/oracle/udump/oracle_ora_4564.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
Tue Aug 25 09:44:51 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 4564
ORA-1092 signalled during: alter database open...
删除*.undo_tablespace='SYSTEM' ，再次重启，还是不行啊~报错同上
页:
[1]