南望阿方小站

有一个测试环境的Oracle数据库，原来工作在归档模式的，现在觉得在上面测试的应用有好几个了，也不能轻意挂掉，就打算不定期的进行RMAN备份。在线RMAN备份需要工作在归档模式下，于是早上趁大家还没开始工作时，把数据库切换到归档模式下。可在关闭Oracle数据库时，异常发生了。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

看到是LibraryCacheNotEmptyOnClose错误，感觉问题好像不是很严重，就先把归档打开，把数据库启动好再说。幸运的时，在重启到Mount，打开归档，打开数据库这个过程都没有报错。

事后，用Google搜索了一下ora-00600 librarycachenotemptyonclose，发现是一个bug [Bug 4483084 OERI[LibraryCacheNotEmptyOnClose]，Metalink账号过期了，也没办法下载了，测试环境也不打算打补丁。

看了网上一个博客，说建个触发器，在关闭系统时执行“ALTER SYSTEM FLUSH SHARED_POOL”就可以了。没有亲测，再说也是不定期发生。

附触发器脚本

CREATE or replace TRIGGER flush_shared_pool
BEFORE SHUTDOWN ON DATABASE
BEGIN
execute immediate ‘ALTER SYSTEM FLUSH SHARED_POOL’;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => ‘Error flushing pool’);
END;

–The End–

今天在授权时，研究了一下unlimited tablespace权限，发现比较奇怪。直接贴出网上别人研究的结果吧。

1、系统权限UNLIMITED TABLESPACE 不能被授予role，可以被授予用户！

2、UNLIMITED TABLESPACE 没有被包括在resource role中，但是UNLIMITED TABLESPACE 随着resource的授出而被授予用户的。

3、UNLIMITED TABLESPACE 不能伴随着resource被授予role而授予用户。

相关数据字典：DBA_SYS_PRIVS,DBA_ROLE_PRIVS

–The End–

今天在需要在开发数据库上加个字段，结果报错，报ORA-00054: resource busy and acquire with NOWAIT specified。

看到这个错误，原因很简单，估计是哪个同事打开表被锁定了。站起来吼了一声，没有人应，只好自己去杀进程了。

查杀进程，就要查到进程。

这个语句查看被锁定的对象，找到需要加字段的表
select /*+ rule */ * from dba_objects where object_id in (
select id1 from v$lock where type = ‘TM’);

这个查锁住这个对象的sid,其实可以把上面语句改成关联查询
select * from v$lock where type = ‘TM’;

查询这个sid是哪个人的，当然更主要是看SID,SERIAL#
select * from v$session where sid=126;

杀杀杀，或冲到锁表的同事那里让TA释放锁
alter system kill session ‘126,2154’;

然后就顺利添加字段成功。

–The End–

今天打算使用DBMS_Logmnr包恢复些数据看看，不过归档日志在RMAN备份完后被删除了。我用DBMS_Logmnr分析备份的归档文件时报错。

SQL> exec dbms_logmnr.add_logfile(’/u02/rman/arch_db1_20090624_29_1′,DBMS_LOGMNR.NEW);
BEGIN dbms_logmnr.add_logfile(’/u02/rman/arch_db1_20090624_29_1′,DBMS_LOGMNR.NEW); END;

*
ERROR at line 1:
ORA-01284: file /u02/rman/arch_db1_20090624_29_1 cannot be opened
ORA-00317: file type 0 in header is not log file
ORA-00334: archived log: /u02/rman/arch_db1_20090624_29_1
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1

看来Logmnr是无法识别备文件的，只有把归档文件从备份文件中还原出来才可以。查了下文档，用RMAN是可以搞定的。

RMAN> run
2> {
3> SET ARCHIVELOG DESTINATION TO ‘/u02/archive’;
4> restore archivelog sequence between 65 and 67;
5> }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 24-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/u02/archive
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=65
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=66
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_DISK_1: reading from backup piece /u02/rman/arch_db1_20090624_29_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rman/arch_db1_20090624_29_1 tag=TAG20090624T154331
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 24-JUN-09

这样就在目录/u02/archive里还原了65-67三个序号的归档文件。接着就可以用DBMS_Logmnr分析日志了。

exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_65.dbf’,DBMS_LOGMNR.NEW);
exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_66.dbf’,DBMS_LOGMNR.addfile);
exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_67.dbf’,DBMS_LOGMNR.addfile);

begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;

select * from v$logmnr_contents

–The End–

今天查看RMAN备份的日志，发现里面有报错，进一步查看是RMAN在删除过期备份时报的错。错误信息如下。

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 06/19/2009 09:15:55
ORA-19587: error occurred reading 16384 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1

看到这个消息，首先想会不会是硬盘有问题了。因不是删除文件，所以不会是空间满了。查看硬盘状况，还有很大的剩余空间。

于是打开trace进入RMAN

$ rman target = / trace rman.trc debug

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Jun 19 14:01:16 2009

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

RMAN-06005: connected to target database: PAYMENT (DBID=938111124)

RMAN> list copy;

RMAN-06009: using target database control file instead of recovery catalog
RMAN-20242: specification does not match any archive log in the recovery catalog
RMAN-06365: List of Control File Copies
RMAN-06366: Key     S Completion Time Ckp SCN    Ckp Time        Name
RMAN-06367: ——- – ————— ———- ————— —-
RMAN-06368: 36      A 19-JUN-09       44248899   19-JUN-09       /u02/oradata/rman/backup/2009-06-19/control.bak
RMAN-06368: 35      A 18-JUN-09       41511256   18-JUN-09       /u02/oradata/rman/backup/2009-06-18/control.bak
RMAN-06368: 34      A 17-JUN-09       39981536   17-JUN-09       /u02/oradata/rman/backup/2009-06-17/control.bak
RMAN-06368: 33      A 16-JUN-09       39213838   16-JUN-09       /u02/oradata/rman/backup/2009-06-16/control.bak

RMAN> crosscheck copy;

RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: sid=1134 instance=payment1 devtype=DISK
RMAN-20242: specification does not match any archive log in the recovery catalog
RMAN-06156: validation succeeded for control file copy
RMAN-08516: control file copy filename=/u02/oradata/rman/backup/2009-06-19/control.bak recid=36 stamp=689937350
RMAN-06156: validation succeeded for control file copy
RMAN-08516: control file copy filename=/u02/oradata/rman/backup/2009-06-18/control.bak recid=35 stamp=689850796
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 06/19/2009 14:01:50
ORA-19587: error occurred reading 16384 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1

RMAN> exit

发现在查检 /u02/oradata/rman/backup/2009-06-17/control.bak 时中断报错了。打开rman.trc，也的确在检查 /u02/oradata/rman/backup/2009-06-17/control.bak 文件处报ORA-19587

……
DBGPLSQL:   channel ORA_DISK_1:  processing (file/handle=/u02/oradata/rman/backup/2009-06-17/control.bak,recid=34, old_status=A, hdl_isdisk=0, devicetype=DISK) [14:01:49.998] (change)
DBGPLSQL:   channel ORA_DISK_1:  force: 0 [14:01:49.998] (change)
DBGRPC:     krmxrpc: xc=182924940192 kpurpc2 rc=19587 db=target proc=DBMS_BACKUP_RESTORE.VALIDATEDATAFILECOPY
DBGRPC:     krmxrpc: xc=182924940192 chid=ORA_DISK_1 increment rpc count=8
DBGMISC:    krmqexe: unhandled exception on channel ORA_DISK_1 [14:01:50.004]
DBGMISC:    error recovery releasing channel resources [14:01:50.004]
……

查看大小，发现是0字节

$ ls -lh /u02/oradata/rman/backup/2009-06-17/control.bak
-rw-r–r–  1 oracle oinstall 0 Jun 19 14:00 /u02/oradata/rman/backup/2009-06-17/control.bak

把这个文件删除，再执行crosscheck copy;就正常了。

–The End–