分类目录归档：ORA-xxxxx

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

发表于 2015 年 6 月 10 日 由 惜分飞

rman 备份控制文件报ORA-00230: operation disallowed: snapshot control file enqueue unavailable错误

db1:/home/oracle>$rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:00:08 2015

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

connected to target database: ORCL (DBID=1150889877)

RMAN> backup current controlfile format '/tmp/xifenfei.ctl';

Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=628 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
cannot make a snapshot control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/10/2015 16:03:10
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

查看持有CF enqueue会话

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
  2  ACTION, LOGON_TIME "Logon"
  3  FROM V$SESSION s, V$ENQUEUE_LOCK l
  4  WHERE l.SID = s.SID
  5  AND l.TYPE = 'CF'
  6  AND l.ID1 = 0
  7  AND l.ID2 = 2;

       SID User
---------- ------------------------------
PROGRAM
------------------------------------------------
MODULE
------------------------------------------------
ACTION                           Logon
-------------------------------- ------------
       648 SYS
rman@db1 (TNS V1-V3)
backup full datafile
0000152 STARTED111               03-JUN-15

kill相关session

SQL> select spid from v$process where addr in(select paddr from v$session where sid=648);

SPID
------------
40108238

SQL> !ps -ef|grep 40108238
  oracle 39125244 65011720   0 15:59:27  pts/0  0:00 grep 40108238
  oracle 40108238        1   0   Jun 03      -  1:18 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


db1:/home/oracle>$kill -9 40108238

再次测试备份控制文件–OK

db1:/home/oracle>$rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:05:06 2015

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

connected to target database: ORCL (DBID=1150889877)

RMAN> backup current controlfile format '/tmp/xifenfei.ctl';

Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=632 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 10-JUN-15
channel ORA_DISK_1: finished piece 1 at 10-JUN-15
piece handle=/tmp/xifenfei.ctl tag=TAG20150610T160516 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-JUN-15
发表在 ORA-xxxxx, rman备份/恢复 | 标签为 | 评论关闭

ORA-21561: OID generation failed故障解决

发表于 2015 年 6 月 1 日 由 惜分飞

数据库无法登陆报ORA-21561: OID generation failed错误

[oracle@essc ~]$ sqlplus XIFENFEI/"www.xifenfei.com"@172.16.50.200/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 16:52:29 2015

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

ERROR:
ORA-21561: OID generation failed


Enter user-name: 
ERROR:
ORA-12545: Connect failed because target host or object does not exist


Enter user-name: 
ERROR:
ORA-12545: Connect failed because target host or object does not exist


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

主机名无法ping通

[root@essc ~]# ping essc
ping: unknown host essc
[root@essc ~]# hostname
essc

[root@essc ~]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.11 app1

[oracle@essc ~]$ ifconfig
eth3      Link encap:Ethernet  HWaddr 00:50:56:BB:00:6B  
          inet addr:172.16.10.30  Bcast:172.16.10.255  Mask:255.255.255.0
          inet6 addr: fe80::250:56ff:febb:6b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:9597 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4018 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1549999 (1.4 MiB)  TX bytes:470158 (459.1 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:164 errors:0 dropped:0 overruns:0 frame:0
          TX packets:164 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:11424 (11.1 KiB)  TX bytes:11424 (11.1 KiB)

修改hosts文件
让hosts中含主机名,也就是为了主机名能够ping通

[oracle@essc ~]$ more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.11 app1
172.16.10.30 essc

数据库登录测试

[oracle@essc ~]$ sqlplus XIFENFEI/"www.xifenfei.com"@172.16.50.200/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 16:56:39 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

具体见官方说明
Ora-21561: OID Generation Failed (Doc ID 1335327.1)

APPLIES TO:
Oracle Net Services - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2013*** 

SYMPTOMS
When attempting to connect to the database using SQL*Plus or DBCA, 
the following error occurs intermittently:

ERROR
-----------------------
ORA-21561: OID generation failed

CAUSE
This could be caused by not having the host name for the 
target database fully qualified in the hosts file.
To verify if you are hitting this issue, the following symptoms should be met:

- ORA-21561: OID generation failed.
- Hosts file has un-fully qualified entry for the target database host:

127.0.0.1 loopback localhost # loopback (lo0) name/address 
10.210.9.111 dbhost
In this sample, dbhost is the target db host.

This is reported in an unpublished Bug 12597261: 
"ORA-21561 IF HOSTNAME ENVIRONMENT VARIABLE IS NOT FULLY QUALIFIED",
 which should be resolved as of 12G.

SOLUTION
Modify the hosts file to have the fully qualified host names, 
by adding the fully qualified domain name to the entry.

127.0.0.1 loopback localhost # loopback (lo0) name/address 
10.210.9.111 dbhost.sample.com

Verify that other environment and service handles are properly defined as well.

If this is a Windows environment, please check : 
Windows: Connections Fail with ORA-12640 or ORA-21561 (Doc ID 744125.1)

再次建议:修改主机名请修改完全,具体参考：linux上安装oracle10g注意事项中修改主机名部分

发表在 ORA-xxxxx | 标签为 , , , | 评论关闭

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路

发表于 2015 年 5 月 29 日 由 惜分飞

今天一网友找到我,说数据库恢复在推scn的过程中遇到了ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified错误无法解决,让我给予支持.这不禁让我想起,现在由于数据库bug和dblink原因导致了很多数据库scn很大,距离天花板非常近,从而使得数据库恢复过程中无法直接简单的推scn,这里正好结合该例子,简单说明下ORA-01052故障的处理.类似文档以前也写过:ORA-01052发生原因的类似文章

由于坏块导致数据库进行实例恢复无法进行

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 1901 KB redo, 276 data blocks need recovery
Started redo application at
 Thread 1: logseq 1004, block 172771
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Fri May 29 10:59:56 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:00 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2048] [PC:0x6215134, __intel_new_memcpy()+260]
Fri May 29 11:00:12 2015
Trace dumping is performing id=[cdmp_20150529110012]
Fri May 29 11:00:12 2015
Slave exiting with ORA-1172 exception
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_p007_1612.trc:
ORA-01172: 线程 1 的恢复停止在块 439938 (在文件 19 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri May 29 11:00:27 2015
ORA-01578: ORACLE 数据块损坏 (文件号 19, 块号 450245)
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10564: tablespace PSTORE
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 91642
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [_intel_new_memcpy()+260] [ACCESS_VIOLATION] [ADDR:0x2048] 
[PC:0x6215134] [UNABLE_TO_READ] []
Fri May 29 11:00:27 2015
Aborting crash recovery due to slave death, attempting serial crash recovery
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:45 2015
Trace dumping is performing id=[cdmp_20150529110045]
Aborting crash recovery due to error 1172
ORA-1172 signalled during: alter database open...

设置_allow_resetlogs_corruption并resetlogs尝试打开数据库

Assigning activation ID 4272042346 (0xfea2316a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 29 11:30:47 2015
SMON: enabling cache recovery
Fri May 29 11:30:47 2015
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc  (incident=181236):
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Incident details in: f:\app\administrator\diag\rdbms\XFF\XFF\incident\incdir_181236\XFF_ora_3004_i181236.trc
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3004): terminating the instance due to error 704
Instance terminated by USER, pid = 3004
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3004) as a result of ORA-1092

这里可以看到数据库通过设置_allow_resetlogs_corruption参数，进行不完全恢复,跳过数据库启动的实例恢复,然后强制拉库,然后遭遇大家熟悉的ORA-600[2662]错误,使得恢复失败,根据经验,通过推scn来绕过该错误

使用_minimum_giga_scn尝试推SCN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
--------------------------------
*._minimum_giga_scn=13443
--------------------------------
 
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
 
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

通过运行Oracle Database Recovery Check检查发现数据库的scn已经非常大,距离天花板较近
_minimum_giga_scn
这里最大允许的推进的scn为13442.7,但是常规的最小的推scn的方法最小值为1024*1024*1024的倍数,因此这里遇到麻烦.
这里数据库遭遇了ORA-01052错误,导致推scn不成功,数据库无法正常启动.出现这类情况,由于scn可以增加的空间非常小,因此可以使用使用oradebug修改数据库scn或者直接修改控制文件scn的方式来精确控制推scn的值(可以实现任何值的scn增加,只要不超过天花板),也可以通过方法修改数据库scn距离天花板的距离,从而实现大幅度使用_minimum_giga_scn来推scn.另外还有一种解决方法:由于ORA-01052是由于scn过大导致(超过了数据库现在的天花板scn),因此出现了ORA-01052.所以另外一种变通的方法,就是通过调整数据库的天花板scn,从而使得_minimum_giga_scn可以继续推scn.在本次恢复中使用最为简单的增加天花板scn的方式来恢复(不过该方法恢复之后需要重建库,其实已经使用了隐含参数屏蔽redo恢复,本身就建议重建库保证数据字典一致性)

发表在 ORA-xxxxx, 非常规恢复 | 标签为 , , , , | 评论关闭