在virtualbox中建立虚拟机，往往是3个文件夹：节点1（ol6-121-rac1），节点2（ol6-121-rac2），共享存储文件夹（ol6-121-rac）。当我们选择“管理”-“导出虚拟电脑”，导出成ova文件备份，就能看到2个主机，每个主机一个ova文件。另外，细心的你可能会发现，在备 […]
当Parallel DML遇到分布式事务、分区表和LOB时
今天遇到一个问题，某sql的并发度虽然已经全部获得但是只有少数几个slave干活。
|
1 2 3 4 |
alter session enable parallel dml; insert /*+ parallel(t 16)*/一个本地分区表t select /*+ full(a parallel(a 32))*/* from 远程分区表a@dblink where 时间>=xxxx and 时间<xxxxx; |
insert要求有16个并发，虽然在v$PX_SESSION看到其获得的degree是16，但是并不是这16个slave都在干活，从sqlmonito […]
latch:transaction allocation
今天遇到一个sql，发生了大量的134的latch，134的latch是transaction allocation。数据库版本是9208. 该sql是：
|
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 |
SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess, s.SQL_HASH_VALUE, l.id1, l.id2, l.lmode, l.request, l.type FROM V$LOCK l, v$session s WHERE (l.id1, l.id2, l.type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0) and l.SID = s.SID and exists (select sw.seq#, sw.sid || ',' || s.serial# sids, s.username, s.SQL_HASH_VALUE from v$session_wait sw, v$session s, dba_objects a where sw.sid = s.sid and sw.event in ('latch free', 'enqueue') and s.status = 'ACTIVE' and sw.STATE = 'WAITING' and s.username is not null and sw.seconds_in_wait > 30 and (a.object_id = s.row_wait_obj# or a.data_object_id = s.row_wait_obj#)) ORDER BY l.id1, l.request; |
据客户反映，这个sql之前没有出现问题，只是昨天做了修改，增加了对v$sess […]
current_timestamp在job中不准的问题
有些job中跑应用程序，希望记下时间戳，但是取了current_timestamp之后，发现时间和实际的时间不准，查了几个小时。这是因为在job中跑的时候，current_timestamp是GMT格林尼治时间。 见下面的testcase，我们设置数据库服务器的时区是东八区的北京时间；客户端plsq […]
小谈12c的in memory option
（1） in memory option（以下简称imo）将随着12.1.0.2发布 （2）in memory option不会取代TimesTen（以下简称TT），因为这是2种层面的产品，TT还是会架在DB层之前，和应用紧密相连，为应用提供缓存，imo在DB层，可以做到高可用如RAC，DG等一些T […]
在跨年时出现执行计划的突变
遇到这样一个问题，客户的某系统在进入2014年后，某sql执行就非常慢，看到这个sql的执行计划发生了改变，原来很好的走hash连接的执行计划变成了走索引的nest loop的执行计划。在收集统计信息后，恢复正常。 这个故障的测试案例，我们可以这样模拟： [crayon-68c7c6205f6763 […]
增量分区统计信息收集的一点研究
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 […]