Category Archives: ORA-xxxxx

ORA-00205问题处理

Posted on by

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-01578坏块解决(2)

Posted on by

ORA-01578坏块解决(1)续集
如果在坏块之前，有rman备份，可以使用rman的备份来进行恢复，确保数据不会被丢失

1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST (DBID=2056006906)

RMAN> blockrecover datafile 6 block 1477;

Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 2011-08-14 22:21:23

2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:22:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 2011
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> select file#,block#,blocks from v$database_block_corruption;

no rows selected

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@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)

3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 2011
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> select count(*) from t_rep;

COUNT(*)
———-
49857

和ORA-01578坏块解决(1)中的模拟环境比较，数据恢复正确，坏块问题解决

ORA-01578坏块解决(1)

Posted on by
一、创建测试表
SQL> create table t_rep as
2  select * from all_objects;
Table created.
SQL> select count(*) from  t_rep;
COUNT(*)
———-
49857
二、使用bbed修改数据块
三、错误现象
1、sqlplus窗口
SQL> select count(*) from  t_rep;
select count(*) from  t_rep
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
2、alert.log文件中
Sun Aug 14 22:01:14 2011
Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data
Sun Aug 14 22:01:15 2011
Corrupt Block Found
TSN = 6, TSNAME = XFF
RFN = 6, BLK = 1477, RDBA = 25167301
OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
四、验证是否真的坏块
1、dbv验证
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
Page 1477 is marked corrupt
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
DBVERIFY – Verification complete
Total Pages Examined         : 2560
Total Pages Processed (Data) : 1371
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1140
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 1256043 (0.1256043)
2、rman验证
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:09:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:09:53
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011
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>  select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
FILE#     BLOCK#     BLOCKS
———- ———- ———-
6       1477          1
五、跳过坏块读取其他数据
SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;
SKIP_COR
——–
ENABLED
SQL> select count(*) from t_rep;
COUNT(*)
———-
49794
说明：数据发生丢失6号文件的1477块中的数据丢失

物化视图on prebuilt table

Posted on by

1、ORA-12059
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-12059: prebuilt table “CHF”.”MV_T2″ does not exist

错误原因：
物化视图对应的表不存在（物化视图需要和表同名，结构相同）

解决方法：
CREATE TABLE mv_t2 AS
SELECT * FROM scott.emp WHERE 1=0;

2、ORA-23413
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on demand
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log

错误原因：
使用fast模式刷新物化视图，需要有物化视图日志

解决方法：
1）创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON scott.emp ;
2）刷新模式改为force，其实实质是采用了complete刷新模式

3、ORA-12058
执行语句（mv log是基于rowid，表无主键）
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
WITH ROWID
AS
SELECT * FROM SCOTT.emp

错误提示：
ORA-12058: materialized view cannot use prebuilt table

错误原因：
fast刷新模式不能基于rowid进行

解决方法：
1）删除mv log，表添加主键，采用基于主键模式重新建mv log和物化视图
DROP MATERIALIZED VIEW mv_t1;
ALTER TABLE mv_t2 ADD PRIMARY KEY (EMPNO);
CREATE MATERIALIZED VIEW LOG ON scott.emp;
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
–WITH ROWID
AS
SELECT * FROM SCOTT.emp;

2）刷新模式改为force，其实实质是采用了complete刷新模式

4、总结
1）在执行创建物化视图之前，需要先创建同名、同结构表
2）如果使用fast模式刷新（疑惑在线重定义可以通过rowid实现，为什么直接通过物化视图就不可以），物化视图同表需要主键，需要物化视图日志
3）通过drop mv，发现同名表还存在，和on prebuilt table本质区别

ORA-01012: not logged on

Posted on by

今天接到现场反馈，数据库出现ORA-01012: not logged on错误提示，这个问题上次也出现过一次，当时按照网上说的方法，直接重启数据库了，问题解决了，同时也导致因为数据库重启，现场破坏，而alert日志中无任何异常信息，所以不知从何处下手分析。这次我上数据库准备查看时，发现数据库已经正常，监控也显示正常，说明数据库已经恢复正常。从此我推理这个问题应该是外部因素导致，而不是数据库本身的bug，从而决定要找出该问题的原因来。有个重要的因素，该数据库是我几个月前因为undo损坏做过恢复的，查看相关参数，发现processes是默认值150，是不是该值导致的不敢肯定，因为一般process超了会报ORA-00020错误，而这次只有ORA-01012。但是心中还是没有底，总感觉这个的可能性最大，于是想通过试验来证实下自己的想法
1、数据库版本（10g结果相同）
SQL> select * from v$version where rownum=1;

BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

2、创建pfile，修改processes，重启数据库，查看processes值
SQL> select name,value from v$parameter where name=’processes’;

NAME VALUE
——————– ——————–
processes 30

3、查看系统processes数量
SQL> select count(*) from v$process;

COUNT(*)
———-
13

4、添加数据库连接数（为了增加process数目）
[oracle@localhost ~]$ sqlplus chf/xifenfei

SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:11:50 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production

SQL> !

5、重复3和4，发现当process数量为29时出现激动人心一幕
5.1)继续访问数据库
[oracle@localhost ~]$ sqlplus system/xifenfei

SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:06 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

Enter user-name:

5.2)使用其他用户访问
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:49 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on

SQL> conn chf/xifenfei
ERROR:
ORA-00020: maximum number of processes (%s) exceeded

[oracle@localhost ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:29:52 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
现象说明：
1)终于发现了ORA-01012错误，期待了很久。发现只有当sys登录系统，对数据库进行查询或者操作之时才会出现ORA-01012，其他用户只要一登录数据库就会提示ORA-00020错误。

2)当process达到数据库最大限时，sysdba登录系统是也显示不正常
2.1）当使用sqlplus “/ as sysdba”登录时，缺少：
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
2.2）当使用conn / as sysdba登录时，提示为：
Connected to an idle instance.