Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › alter database open 遇ORA-01092:

anbob

87 积分	0 好友	5 主题

发消息

alter database open 遇ORA-01092:

1^#

发表于 2012-3-22 14:58:18 | 查看: 11152| 回复: 11

前面的操作如下
alter database backup controlfile to trace;

因为里面有个数据文件不存在了删除了一个控制文件中的一个数据文件后，重建控制文件

sql>startup nomount
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                1220796 bytes
Variable Size          218107716 bytes
Database Buffers       377487360 bytes
Redo Buffers             7163904 bytes
sql>CREATE CONTROLFILE REUSE DATABASE "NCME" NORESETLOGS  NOARCHIVELOG
  2    MAXLOGFILES 16
  3    MAXLOGMEMBERS 3
  4    MAXDATAFILES 100
  5    MAXINSTANCES 8
  6    MAXLOGHISTORY 4672
  7  LOGFILE
  8 GROUP 1 '/oracle/oradata/ncme/redo01.log'  SIZE 50M,
  9 GROUP 2 '/oracle/oradata/ncme/redo02.log'  SIZE 50M,
10 GROUP 3 '/oracle/oradata/ncme/redo03.log'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13 '/oracle/oradata/ncme/system01.dbf',
14 '/oracle/product/10.2.0/dbs/BJHR_INDEX',
15 '/oracle/oradata/ncme/sysaux01.dbf',
16 '/oracle/oradata/ncme/users01.dbf',
17 '/oracle/oradata/ncme/BJHR_DEV.dbf',
18 '/oracle/oradata/ncme/ncme01.dbf',
19 '/oracle/oradata/ncme/undotbs01.dbf',
20 '/oracle/oradata/ncme/keyan01.dbf',
21 '/oracle/oradata/ncme/users02.dbf'
22  CHARACTER SET ZHS16GBK
23  ;

Control file created.

sql>RECOVER DATABASE;
Media recovery complete.
sql>ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

[ 本帖最后由 anbob 于 2012-3-22 15:49 编辑 ]

alert.tar.gz

4.99 KB, 下载次数: 2642

trace.tar.gz

297.48 KB, 下载次数: 2689

分享0

收藏0 回复只看该作者道具举报

anbob

2^#

发表于 2012-3-22 14:59:12

alert 日志出现了

========== FRAME [2] (ksedmp()+557 -> ksedst()) ==========
Dump of memory from 0xBFFF6108 to 0xBFFF6260
[oracle@ora10g bdump]$ tail -n 20 alert_ncme.log
Thread 1 opened at log sequence 40725
Current log# 2 seq# 40725 mem# 0: /oracle/oradata/ncme/redo02.log
Successful open of redo thread 1
Thu Mar 22 14:54:22 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Mar 22 14:54:22 2012
SMON: enabling cache recovery
Thu Mar 22 14:54:22 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Thu Mar 22 14:54:22 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28668.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Thu Mar 22 14:54:23 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28668.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 28668
ORA-1092 signalled during: alter database open...

回复只看该作者道具举报

anbob

3^#

发表于 2012-3-22 14:59:46

有点类似这个人的问题http://www.experts-exchange.com/Database/Oracle/Q_21866358.html

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-3-22 15:12:57

ACTION PLAN:

上传完整的 ALERT.LOG 和 /oracle/admin/ncme/udump/ncme_ora_28668.trc ，压缩打包后上传

回复只看该作者道具举报

anbob

5^#

发表于 2012-3-22 15:50:11

回复 4# 的帖子

ok, 打包上传了。

回复只看该作者道具举报

anbob

6^#

发表于 2012-3-22 16:05:15

Successfully onlined Undo Tablespace 1.
Dictionary check beginning
File #2 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX'
Tablespace 'BJHR_INDEX' #30 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #5 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 5: '/oracle/oradata/ncme/BJHR_DEV.dbf'
Tablespace 'BJHR_DEV' #25 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #6 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 6: '/oracle/oradata/ncme/ncme01.dbf'
Tablespace 'NCME' #7 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #9 in the controlfile not found in data dictionary.
Removing file from controlfile. =====>
data file 9: '/oracle/oradata/ncme/keyan01.dbf'
Tablespace 'KEYAN' #18 found in controlfile,
but not in the data dictionary. Deleting from controlfile. ====>
Thu Mar 22 14:45:30 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28605.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Thu Mar 22 14:45:30 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28605.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Error 600 happened during db open, shutting down database

MOS 上说这个错误原因

4.  Make sure that this controlfile has the correct datafiles before
proceeding.

刚备的再重建不明白数据字典怎么没了，

sql>l
  1* select file#,name from v$datafile
sql>/

   FILE# NAME
---------- --------------------------------------------------
      1 /oracle/oradata/ncme/system01.dbf
      3 /oracle/oradata/ncme/sysaux01.dbf
      4 /oracle/oradata/ncme/users01.dbf
      7 /oracle/oradata/ncme/undotbs01.dbf
      12 /oracle/oradata/ncme/users02.db
数据文件变剩这几个基本的了

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-3-22 16:31:27

Thu Mar 22 14:15:30 2012
Errors in file /oracle/admin/ncme/bdump/ncme_ckpt_28254.trc:
ORA-01110: data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX'
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

data file 2在 Mar 22 14:15:30 (重建控制文件之前)就丢失了。

如果你已经彻底丢失 DATAFILE 2 :
那么建议你在 CREATE CONTROLFILE 时跳过 data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX' 这个数据文件，当然这会造成 DATAFILE 2 在OPEN RESETLOGS很难再加入DB。

如果你有DATAFILE 2的可用COPY，那么在CREATE CONTROLFILE之前把它复制到 '/oracle/product/10.2.0/dbs/BJHR_INDEX'

回复只看该作者道具举报

anbob

8^#

发表于 2012-3-22 17:00:19

回复 7# 的帖子

嗯，后来我发现那个数据文件是给打包了，重建前我是解压了的，我查了历史操作记录如下
[oracle@ora10g dbs]$ ls
BJHR_INDEX.tar.gz  hc_ncme.dat  initncme.ora  lkNCME    spfilencme.ora.bak
crencme.sql       initdw.ora init.ora    orapwncme
[oracle@ora10g dbs]$ tar -zxvf BJHR_INDEX.tar.gz
BJHR_INDEX
[oracle@ora10g dbs]$ exit
exit

SYS>alter database open;

Database altered.
当时我找到后是有恢复的，我只是修改了backup control trace文件中删掉了
ORA-01110: data file 14: '/oracle/oradata/ncme/undotbs02.dbf'
这个文件14#

回复只看该作者道具举报

lanseguhun

9^#

发表于 2012-3-22 17:04:31

File #2 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX'
Tablespace 'BJHR_INDEX' #30 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #5 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 5: '/oracle/oradata/ncme/BJHR_DEV.dbf'
Tablespace 'BJHR_DEV' #25 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #6 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 6: '/oracle/oradata/ncme/ncme01.dbf'
Tablespace 'NCME' #7 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #9 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 9: '/oracle/oradata/ncme/keyan01.dbf'
Tablespace 'KEYAN' #18 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
兄弟不会拷贝错了system数据文件吧，好好的数据字典里没了呢，难道删掉了没有同步进control file？

回复只看该作者道具举报

anbob

10^#

发表于 2012-3-22 17:19:55

应该不会，我是先用@traclefile名中间失败了，后来又手动从tracefile中一个条条copy 执行的同样都是到open处出错，我刚才又物理恢复了，

回复只看该作者道具举报

anbob

11^#

发表于 2012-3-22 17:21:03

问题的起点是
SYS>select file_id,file_name,tablespace_name from dba_data_files order by 1;

FILE_ID FILE_NAME                                        TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
      1 /oracle/oradata/ncme/system01.dbf                SYSTEM
      2 /oracle/product/10.2.0/dbs/BJHR_INDEX             BJHR_INDEX
      3 /oracle/oradata/ncme/sysaux01.dbf                SYSAUX
      4 /oracle/oradata/ncme/users01.dbf                USERS
      5 /oracle/oradata/ncme/BJHR_DEV.dbf                BJHR_DEV
      6 /oracle/oradata/ncme/ncme01.dbf                   NCME
      7 /oracle/oradata/ncme/undotbs01.dbf                UNDOTBS1
      9 /oracle/oradata/ncme/keyan01.dbf                KEYAN
      12 /oracle/oradata/ncme/users02.dbf                USERS

9 rows selected.

SYS>select file#,name from v$datafile;

   FILE# NAME
---------- ------------------------------------------------------------
      1 /oracle/oradata/ncme/system01.dbf
      2 /oracle/product/10.2.0/dbs/BJHR_INDEX
      3 /oracle/oradata/ncme/sysaux01.dbf
      4 /oracle/oradata/ncme/users01.dbf
      5 /oracle/oradata/ncme/BJHR_DEV.dbf
      6 /oracle/oradata/ncme/ncme01.dbf
      7 /oracle/oradata/ncme/undotbs01.dbf
      9 /oracle/oradata/ncme/keyan01.dbf
      12 /oracle/oradata/ncme/users02.dbf
      14 /oracle/oradata/ncme/undotbs02.dbf

10 rows selected.

SYS>select file#,ts# from file$;

   FILE#       TS#
---------- ----------
      1       0
      2       30
      3       2
      4       4
      5       25
      6       7
      7       1
      8
      9       18
      10
      11
      12       4
      13

13 rows selected.

ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/oracle/oradata/ncme/undotbs02.dbf'

后台一直在报这个错，发现控制文件中多记录了一个文件。就是解决这个问题的。

回复只看该作者道具举报

回忆未来

12^#

发表于 2012-3-22 20:42:10

Database altered.
File #7 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 7: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007'
Dictionary check complete

关于这个错误简单测试了,可以确定你目前controlfile 跟数据库字典不一致至于为什么不一致怀疑你执行过删除表空间操作
SYS@willson>  SELECT file#,STATUS$,TS#,RELFILE#  FROM file$  ORDER BY 1;

   FILE# STATUS$       TS# RELFILE#
---------- ---------- ---------- ----------
      1       2       0       1
      2       2       1       2
      3       2       2       3
      4       2       4       4
      5       2       5       5
      6       2       6       6
      7       2       5       7

7 rows selected.

SYS@willson> col name for a26
SYS@willson> select * from v$tablespace;

   TS# NAME                      INC BIG FLA ENC
---------- -------------------------- --- --- --- ---
      0 SYSTEM                   YES NO  YES
      5 TEST                      YES NO  YES
      2 SYSAUX                   YES NO  YES
      4 USERS                   YES NO  YES
      1 UNDOTBS1                YES NO  YES
      6 UNDOTBS2                YES NO  YES
      3 TEMP                      NO  NO  YES

7 rows selected.

SYS@willson> delete from file$ where file#=7;

1 row deleted.

SYS@willson> commit;

Commit complete.

SYS@willson>  SELECT file#,STATUS$,TS#,RELFILE#  FROM file$  ORDER BY 1;

   FILE# STATUS$       TS# RELFILE#
---------- ---------- ---------- ----------
      1       2       0       1
      2       2       1       2
      3       2       2       3
      4       2       4       4
      5       2       5       5
      6       2       6       6

6 rows selected.

CREATE CONTROLFILE REUSE DATABASE "WILLSON" NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\REDO03.LOG'  SIZE 50M
DATAFILE
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\SYSTEM01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\TEST1.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\SYSAUX01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\USERS01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\UNDOTBS01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WILLSON\UNDOTBS2.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007'
CHARACTER SET ZHS16GBK
;

SYS@willson> recover datafile 7;
Media recovery complete.
SYS@willson> alter database open;

Database altered.
File #7 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 7: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007'
Dictionary check complete

SYS@willson> SELECT file#,STATUS$,TS#,RELFILE#  FROM file$  ORDER BY 1;

   FILE# STATUS$       TS# RELFILE#
---------- ---------- ---------- ----------
      1       2       0       1
      2       2       1       2
      3       2       2       3
      4       2       4       4
      5       2       5       5
      6       2       6       6

6 rows selected.

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册