Oracle在11g中能增量收集分区表的统计信息。在11g之前，收集global的统计信息时，oracle需要扫描每个分区的统计信息，才能得出global的统计信息。而在11g中，Oracle会简单记录分区的概要信息，（synopsis，记录在WRI$_OPTSTAT_SYNOPSIS_HEAD$ […]
11g的reference partition的一个bug
在11g上有个新特性，叫reference partition table，但是有时候在查询reference partition的时候，会报错ORA-00600 [6305] 我们可以看下面的testcase：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
TestCase: SQL> CREATE TABLE BILL_DATA_T ("BILLID" CHAR(10 BYTE) NOT NULL ENABLE, -----<<<<<<< 4.BILLID是char 10 "COGCODE" CHAR(7 BYTE), CONSTRAINT "PK_BILL_DATA_T" PRIMARY KEY ("BILLID")) PARTITION BY RANGE ("COGCODE") (PARTITION "BILLS_2012" VALUES LESS THAN ('X'), PARTITION "BILLS_OPEN" VALUES LESS THAN (MAXVALUE)); 2 3 4 5 6 7 8 Table created. SQL> SQL> CREATE TABLE FREIGHT_ITEM_T ("BILLID" CHAR(10 BYTE) NOT NULL ENABLE, "FRTITEMID" CHAR(3 BYTE) NOT NULL ENABLE, CONSTRAINT PK_FREIGHT_ITEM_T PRIMARY KEY ("BILLID"), CONSTRAINT FK_FREIGHT_ITEM_T FOREIGN KEY ("BILLID") REFERENCES ---<<<<<<< 3.外键是参考BILL_DATA_T表的BILLED字段 BILL_DATA_T ("BILLID")) PARTITION BY REFERENCE ----<<<<<<<<<<<<< 1.是partition by reference ("FK_FREIGHT_ITEM_T") ----<<<<<<<<<<< 2.参考了FK_FREIGHT_ITEM_T (PARTITION "BILLS_2012", PARTITION "BILLS_OPEN"); 2 3 4 5 6 7 8 9 10 Table created. SQL> SQL> SQL> select * from freight_item_t where billid='123456789000000'; ----<<<<< 5.超过char 10，就报错 select * from freight_item_t where billid='123456789000000' * ERROR at line 1: ORA-00600: internal error code, arguments: [6305], [16], [12], [1], [], [], [], [], [], [], [], [] SQL> SQL> SQL> SQL> c/123456789000000/123456789 1* select * from freight_item_t where billid='123456789' -----<<<<<< 6.不超过char 10，就不会报错。 SQL> / no rows selected SQL> |
这其实是bug 13 […]
SQL执行时间受游标影响不准
sql的执行时间，我们往往可以通过ash中的sample时间，减去sql exec start，得出该sql执行的时间。有一个很不错的sql，可以看某个sql的历次执行时间，历次执行计划。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT T.SQL_ID, T.SQL_EXEC_ID, CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME, T.SQL_EXEC_START EXEC_START_TIME, ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS, T.SQL_PLAN_HASH_VALUE, T.MODULE FROM v$active_session_history T WHERE T.SQL_ID = '&SQL_ID' AND T.SAMPLE_TIME > SYSDATE - 36 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE ORDER BY EXEC_END_TIME DESC; 或者 SELECT T.SQL_ID, T.SQL_EXEC_ID, CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME, T.SQL_EXEC_START EXEC_START_TIME, ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS, T.SQL_PLAN_HASH_VALUE, T.MODULE FROM sys.WRH$_ACTIVE_SESSION_HISTORY T WHERE T.SQL_ID = '&SQL_ID' AND T.SAMPLE_TIME > SYSDATE - 36 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE ORDER BY EXEC_END_TIME DESC; |
但是在使用游标的时候，如，在某个proced […]
9i catalog的一个bug
一个rman的catalog库，是9i的版本。在连接这个库做backup的时候，能正常完成，但是在做restore的时候，就报错了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
ecovery Manager: Release 9.2.0.8.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. RMAN> executing command: SET DBID RMAN> connected to target database: (not mounted) RMAN> connected to recovery catalog database RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> allocated channel: ch1 channel ch1: sid=11 devtype=SBT_TAPE channel ch1: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch2 channel ch2: sid=12 devtype=SBT_TAPE channel ch2: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch3 channel ch3: sid=13 devtype=SBT_TAPE channel ch3: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch4 channel ch4: sid=14 devtype=SBT_TAPE channel ch4: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch5 channel ch5: sid=15 devtype=SBT_TAPE channel ch5: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch6 channel ch6: sid=16 devtype=SBT_TAPE channel ch6: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch7 channel ch7: sid=17 devtype=SBT_TAPE channel ch7: Veritas NetBackup for Oracle - Release 7.1 (2011120716) allocated channel: ch8 channel ch8: sid=18 devtype=SBT_TAPE channel ch8: Veritas NetBackup for Oracle - Release 7.1 (2011120716) sent command to channel: ch1 sent command to channel: ch2 sent command to channel: ch3 sent command to channel: ch4 sent command to channel: ch5 sent command to channel: ch6 sent command to channel: ch7 sent command to channel: ch8 Starting restore at 2013-10-28 18:35:47 released channel: ch1 released channel: ch2 released channel: ch3 released channel: ch4 released channel: ch5 released channel: ch6 released channel: ch7 released channel: ch8 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/28/2013 18:35:48 RMAN-06004: ORACLE error from recovery catalog database: ORA-01455: converting column overflows integer datat ype RMAN> Recovery Manager complete. |
根据RMAN-06004和ORA-01455，我们很容易就能发现9i catalog库的一个bug： […]
Wnnn进程导致buffer busy wait
某日，在一个数据库中，发现大量buffer busy wait的进程，经检查，是监控等查询表空间使用率的进程处于buffer busy wait。而其blocking session wnnnn进程。 (1)看到监控程序被1013进程阻塞，blocking session是1013 [crayon- […]
控制文件丢失的恢复
1. rman设置自动备份的情况（CONFIGURE CONTROLFILE AUTOBACKUP ON ），可以 注：11g自动备份的控制文件是放在FRA区：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
RMAN> restore controlfile from autobackup; Starting restore at 15-NOV-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK recovery area destination: e:\ora11g\app\oracleuser\flash_recovery_area database name (or database unique name) used for search: ORA11G channel ORA_DISK_1: AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\CONTROL01.CTL output file name=E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\CONTROL02.CTL Finished restore at 15-NOV-13 RMAN> RMAN> restore database; Starting restore at 15-NOV-13 Starting implicit crosscheck backup at 15-NOV-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=191 device type=DISK Crosschecked 20 objects Finished implicit crosscheck backup at 15-NOV-13 Starting implicit crosscheck copy at 15-NOV-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 15-NOV-13 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\ARCHIVELOG\2013_11_07\O1_MF_1_509_97POOJ85_.ARC File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSTEM01.DBF channel ORA_DISK_1: restoring datafile 00002 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSAUX01.DBF channel ORA_DISK_1: restoring datafile 00003 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\UNDOTBS01.DBF channel ORA_DISK_1: restoring datafile 00004 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\USERS01.DBF channel ORA_DISK_1: restoring datafile 00005 to E:\USERS2_FOR_TEMP_USE.DBF channel ORA_DISK_1: reading from backup piece C:\FULL_9OOP1K9R_1_312.RMN channel ORA_DISK_1: piece handle=C:\FULL_9OOP1K9R_1_312.RMN tag=TAG20131115T165211 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:35 Finished restore at 15-NOV-13 RMAN> recover database; Starting recover at 15-NOV-13 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 25 is already on disk as file E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG archived log file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG thread=1 sequence=25 media recovery complete, elapsed time: 00:00:02 Finished recover at 15-NOV-13 RMAN> alter database open resetlogs; database opened RMAN> RMAN> |
2.rman一般会配置snapshot（CONFIGURE SNAPSH […]
DBMS_AUDIT_MGMT的一些小结
当设置了DBMS_AUDIT_MGMT.init_cleanup之后，aud$的相关行为会发生一些变化。如原来设置了by session的审计，在进行初始化后，by session的记录变得像by access一样，或者更准确的说，变得像11g的行为一样。（详见下面的Q5） 在11g之前，by se […]
HP的进程优先级和cursor pin S
进程的优先级往往会不可思议的影响数据库的性能，在很多时候，可能突发性的（持续时间只有几秒钟到几分钟）、很难确认原因的latch，都和进程优先级有关。下面就是这样一个例子，在没有正确设置进程优先级的情况下，客户的系统常常时不时的出现高CPU的情况，持续时间几分钟，且伴随着cursor pin S的等待 […]
当scheduler超过时间窗口时停止job
要在scheduler的window结束的时候，也让job停止，需要对job加’stop_on_window_close’的属性。 测试如下： 1.建立测试表t1：
|
1 2 3 |
drop table t1; create table t1 as select 'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mycol from dual; |
2. 建立需要定期执行的存储过程： [cra […]
impdp时报错Ora-28031
今天一客户说在做impdp导入时，报错Ora-28031。我们看看ora-28031的报错是什么意思：
|
1 2 3 |
ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-31625: Schema SYS is needed to import this object, but is unaccessible ORA-28031: maximum of 148 enabled roles exceeded |
从报错信息看，是SYS用户的role超过148个，一开始，我们用
|
1 |
select count(*) from dba_role_privs where grantee = 'SYS'; |
[…]
runInstaller的debug
收到一个请求，客户说安装11.2.0.2 的RAC数据库的时候，到prerequisite check这一步报错PRVF-5300和PRVF-5434。 我们可以运行下面的命令来追踪：
|
1 |
./runInstaller -debug -logLevel finest >inst1.out 2>inst2.out |
运行debug后，我们还在OUI中操作，到了pr […]
谈谈死事务的回滚
死事务，一般是事务正在跑的时候，被kill掉了，或者数据库shutdown abort了，那么当数据库再次启动的时候，这些事务就需要做回滚。 可以通过下面的语句查到回滚的事务：
|
1 2 3 |
select * from v$fast_start_transactions; 或者 select * from x$ktuxe where KTUXECFL='DEAD' AND KTUXESTA!='INACTIVE' |
根据上面的语句，我们可以查到事务的undo的segmen […]