标签归档：ORA-00205

ORACLE 12C 控制文件异常恢复

发表于 2014 年 3 月 20 日 由 惜分飞

在oracle 12c引进了pdb的概念,但重建控制文件的过程还是完全和no-cdb(以前版本ORACLE)相同
数据库启动异常

idle> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2404552 bytes
Variable Size             205524792 bytes
Database Buffers          306184192 bytes
Redo Buffers                7823360 bytes
ORA-00205: error in identifying control file, check alert log for more info


idle> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

alert日志信息

Wed Mar 19 20:30:20 2014
ALTER DATABASE   MOUNT
Wed Mar 19 20:30:20 2014
ORA-00210: ???????????
ORA-00202: ????: ''E:\APP\XIFENFEI\ORADATA\CDB\CONTROL01.CTL''
ORA-27048: skgfifi: ????????
OSD-04001: 逻辑块大小无效
ORA-205 signalled during: ALTER DATABASE   MOUNT...

利用strings命令找出来数据文件和联机日志路径

E:\APP\XIFENFEI\ORADATA\CDB\REDO04.LOG
E:\APP\XIFENFEI\ORADATA\CDB\REDO06.LOG
E:\APP\XIFENFEI\ORADATA\CDB\REDO05.LOG
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\TEMP01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\TEMP01.DBF

如果有完整的alert日志也可以通过alert日志来分析数据文件路径和redo路径;
通过alert日志分析数据库编码,如果没有alert日志通过分析prop$分析编码;
redo group 可以在alert日志中查看,写入错误,在创建时候会提示正确值;
redo size 可以查看redo file,也可以先写入错误值,创建时候会提示正确block数量

重建控制文件

idle> CREATE CONTROLFILE REUSE DATABASE "CDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8  GROUP 4 'E:\APP\XIFENFEI\ORADATA\CDB\REDO04.LOG' size 50M,
  9  GROUP 6 'E:\APP\XIFENFEI\ORADATA\CDB\REDO06.LOG'  size 50M,
 10  GROUP 5 'E:\APP\XIFENFEI\ORADATA\CDB\REDO05.LOG'  size 50M
 11  DATAFILE
 12  'E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF',
 13  'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF',
 14  'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF',
 15  'E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF',
 16  'E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF',
 17  'E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF',
 18  'E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF',
 19  'E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF',
 20  'E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF'
 21  CHARACTER SET ZHS16GBK
 22  ;

控制文件已创建。

idle> recover database;
完成介质恢复。
idle> alter database open;

数据库已更改。

idle> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
idle> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF

已选择 9 行。

CDB_CDB$ROOT@SYS> alter session set container=pdb;

Session altered.

CDB_CDB$ROOT@SYS> alter database open;

Database altered.

数据库在mount状态重建控制文件请参考:重建控制文件

发表在 ORACLE 12C, Oracle备份恢复 | 标签为 , , | 评论关闭

ORACLE丢失各种文件导致数据库不能OPEN恢复

发表于 2014 年 2 月 21 日 由 惜分飞

在ORACLE的运行过程中,总会遇到这样那样的故障,本篇主要大概介绍关于因硬件,系统,误删除等各种原因导致数据库的部分文件丢失,这里列出来由于文件丢失而出现的常见错误和基本处理思路

1.丢失数据文件(ORA-01157)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/oracle/oradata/XFF/users01.dbf’
数据文件丢失，处理方法：
1).使用备份还原丢失数据然后
2).非undo,system可以offline 掉该文件继续打开数据库
3).如果是undo需要谨慎,可能导致ORA-00376错误
4).如果是system offline可能导致ORA-01147

2. 丢失redo(ORA-00313)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oracle/oradata/XFF/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
redo文件丢失，处理步骤：
1).查询v$log确认该redo是否是current或者active
2).确定该redo是否被归档
3).如果是inactive使用clear 或者 clear unarchived
4).如果是active或者current,需要通过不完全恢复,甚至隐含参数等方法解决

3. 丢失undo(ORA-01092 ORA-00376)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01092是前台错误,通过查询alert日志发现后台错误主要是：
Fri Oct 25 08:16:36 2013
Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_7437.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’
因为undo文件丢失,有事务无法正常回滚，从而出现该错误,需要通过使用隐含参数屏蔽事务来解决

4. 丢失system(ORA-01147)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’

SQL> alter database datafile 1 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’
system表空间是系统表空间，该表空间中的数据文件不能被offline,如果该表空间数据文件丢失，数据库无法正常方法,可以考虑使用bbed模拟system文件欺骗数据库(非file# 1)或者使用dul抽取数据

5. 丢失控制文件(ORA-00205 ORA-00202)
SQL> startup
ORACLE instance started.

Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
ORA-00205: error in identifying control file, check alert log for more info

ORA-00205是前台错误，具体需要结合日志分析：
Fri Oct 25 08:35:40 2013
ALTER DATABASE MOUNT
Fri Oct 25 08:35:40 2013
ORA-00202: control file: ‘/u01/oracle/oradata/XFF/control01.ctl’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
这里可以看出来，是因为控制问文件丢失该值该错误,处理办法：
1).使用备份控制文件还原
2).查找是否还有其他控制文件，拷贝一份
3).列举数据文件重建控制文件

如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

姊妹篇
undo异常总结和恢复思路
ORACLE REDO各种异常恢复

发表在 Oracle备份恢复 | 标签为 , , , , , , , , , | 评论关闭

ORA-00205问题处理

发表于 2011 年 8 月 23 日 由 惜分飞

1、启动数据库异常
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
ORA-00205: error in identifying control file, check alert log for more info

alert.log日志中记录
ALTER DATABASE MOUNT
Mon Jul 18 17:32:58 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/oradata/xienfei/control01.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory

2、根据提示缺少控制文件，第一步是看看有没有冗余的控制文件，然后修改pfile或者复制控制文件处理，如果没有利用备份控制文件恢复
RMAN> restore controlfile from ‘/tmp/rman_1kmhorc2_1_1′;

Starting restore at 18-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/oradata/xienfei/control01.ctl
Finished restore at 18-JUL-11

3、恢复控制文件后，数据库至于mount状态
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

4、添加归档日志到控制文件
RMAN> catalog start with ‘/u01/archive’;

Starting implicit crosscheck backup at 18-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 29 objects
Crosschecked 12 objects
Finished implicit crosscheck backup at 18-JUL-11

Starting implicit crosscheck copy at 18-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-JUL-11

searching for all files in the recovery area
cataloging files…
no files cataloged

searching for all files that match the pattern /u01/archive

List of Files Unknown to the Database
=====================================
…………
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
…………
RMAN> exit

[oracle@node2 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 18 17:43:47 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

5、使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008′
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008′

6、发现控制文件后又新增数据文件，需要重命名控制文件中的新增数据文件
SQL> select file#,name from v$datafile where file#=8;

FILE# NAME
———- ———————————————-
8 /u01/oracle/dbs/UNNAMED00008

SQL> alter database rename file ‘/u01/oracle/dbs/UNNAMED00008′ to ‘/u01/oradata/xienfei/cfxff01.dbf’;

Database altered.

7、继续进行不完成恢复（利用归档日志）
SQL> recover database using backup controlfile;
ORA-00279: change 1158476 generated at 07/18/2011 16:43:27 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_1_756837539.arc
ORA-00280: change 1158476 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1158594 generated at 07/18/2011 16:44:01 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_2_756837539.arc
ORA-00280: change 1158594 for thread 1 is in sequence #2
ORA-00278: log file ‘/u01/archive/1_1_756837539.arc’ no longer needed for this
recovery

…………

ORA-00308: cannot open archived log ‘/u01/archive/1_7_756837539.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

8、根据提示，应该是归档日志恢复完成，需要利用联机日志继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo03.log
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: ‘/u01/oradata/xienfei/redo03.log’

SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo01.log
Log applied.
Media recovery complete.

9、resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 | 评论关闭