笑看数据库江湖的那些事儿……
12c的GI中，/oracle_grid/app/oracle/crsdata/@global/cvu/baseline/cvures这个目录下的文件占用空间巨大。某生产环境中，目录已经到了32G以上。 这可能和如下3个bug有关：
|
1 2 3 |
Unpublished Bug 18143707 CVU NEED TO DO CVU BASELINE REPORT CLEAN TO AVOID DISK FULL will limit .txt files to 5 Unpublished Bug 19703199 CVU NEED TO DO CVU BASELINE REPORT CLEAN TO AVOID DISK FULL will limit .xml files to 5 Unpublished Bug 20177779 BIGBH U01 SPACE CONSUMPTION BY CVUCHECKREPORT.XML will purge the files on remote nodes |
[…]
客户的一个数据库，是12c的cluster环境，这个环境中有11g的db和12c的db。当客户准备在一个diskgroup上的数据库拉起来的时候（这个cluster是放多个测试库，是从几十个生产库的disk通过存储级复制的方式，集中起来的。生产库那边一个cluster只有一个database，只有2 […]
今天一个10g的客户端，连接12c的数据库，报错：
|
1 2 3 4 5 6 7 8 |
sqlplus user/passwd@10.11.22.33:1525/mysrv SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jun 11 10:05:29 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. SP2-0642: SQL*Plus internal error state 2130, context 0:0:0 Unsafe to proceed |
而这个客户端连接其他12c的数据库是没有问题的。 这是因为建立的service已经是非标准的service，包含了failover type等参数。见下面的Test Case： [c […]
12.1.0.2之后，duplicate target database for standby from active database的时候，总是hang死在restore controlfile的情况。这个由于Bug 19664695引起。（Bug22468652和Bug 20721271最终 […]
将10.2.0.5的一个大表导入到12.1.0.2的时候， 导出参数是： [oracle10g@testdb tmp]$ cat expdp.par userid=’/ as sysdba’ DIRECTORY=DUMPDIR dumpfile=mytable_%U.dmp tables=schem […]
在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 […]
一个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： […]
最近遇到这样一个事情，在动态的调整sga的一个参数的时候，数据库就down了：
|
1 2 3 4 5 6 7 8 |
SQL> alter system set DB_KEEP_CACHE_SIZE=1g; alter system set DB_KEEP_CACHE_SIZE=1g * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> |
对应的，在alertlog中的报错为：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sat Nov 13 03:56:00 2010 Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc: ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], [] Sat Nov 13 03:56:01 2010 Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc: ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], [] Sat Nov 13 03:56:01 2010 MMAN: terminating instance due to error 822 Sat Nov 13 03:56:01 2010 Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc: ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], [] Sat Nov 13 03:56:03 2010 Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc: ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [600], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], [] Instance terminated by MMAN, pid = 3264732 |
对应的tace文件中的call stack为： […]
今天收到监控的一个告警：
|
1 |
ORA-16146: standby destination control file enqueue unavailable |
登录主库和备库检查其报错信息： 备库上的alertlog：
|
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 |
Media Recovery Waiting for thread 1 seq# 2506 Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2506.arc Wed Oct 27 20:30:24 2010 Media Recovery Waiting for thread 1 seq# 2507 Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2507.arc Thu Oct 28 00:13:35 2010 Media Recovery Waiting for thread 1 seq# 2508 Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2508.arc Thu Oct 28 01:24:57 2010 RFS: controlfile enqueue unavailable Possible invalid cross-instance archival configuration Thu Oct 28 01:25:00 2010 Errors in file /oracle_log/JPJDE/admin/udump/jpjde_rfs_1331404.trc: ORA-16146: standby destination control file enqueue unavailable Thu Oct 28 01:44:42 2010 RFS: Error State mode '2' Thu Oct 28 01:45:31 2010 Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2509.arc Thu Oct 28 01:46:53 2010 RFS: Forced Shutdown due to RFS_ERROR state Thu Oct 28 01:50:33 2010 Media Recovery Waiting for thread 1 seq# 2510 (in transit) Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2510.arc Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2511.arc Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2512.arc Thu Oct 28 02:11:34 2010 Media Recovery Waiting for thread 1 seq# 2513 Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2513.arc |
对应同一时间，主库上的信息： [crayon-676db4e48717d78229 […]
今天收到告警邮件，某省的数据库无法登录，在alertlog中有如下的报错：
|
1 2 3 4 5 6 |
Tue Mar 31 16:38:11 2009 Errors in file /oracle/app/oracle/admin/zjfs/bdump/zjfs_ora_22423.trc: ORA-27102: out of memory HPUX-ia64 Error: 12: Not enough space Additional information: 103 Additional information: 524288 |
登录数据库主机检查内存剩余量不到10%，用top检查：
|
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 |
System: zj-db01 Wed Apr 1 14:10:47 2009 Load averages: 0.12, 0.18, 0.28 387 processes: 362 sleeping, 25 running Cpu states: CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 0.21 8.4% 0.0% 7.0% 84.7% 0.0% 0.0% 0.0% 0.0% 1 0.09 3.4% 0.0% 2.4% 94.2% 0.0% 0.0% 0.0% 0.0% 2 0.11 3.4% 0.0% 0.0% 96.6% 0.0% 0.0% 0.0% 0.0% 3 0.10 3.2% 0.0% 1.0% 95.8% 0.0% 0.0% 0.0% 0.0% --- ---- ----- ----- ----- ----- ----- ----- ----- ----- avg 0.12 4.6% 0.0% 2.6% 92.8% 0.0% 0.0% 0.0% 0.0% Memory: 4456068K (2521320K) real, 6115756K (3135960K) virtual, 391416K free Page# 1/15 CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 0 ? 2307 oracle 178 20 3015M 24612K sleep 4383:34 15.11 15.08 oraclezjfs 2 ? 2228 oracle 178 20 2986M 9624K sleep 3580:19 2.94 2.93 oraclezjfs 1 ? 2309 oracle 178 20 2986M 5536K sleep 541:29 2.92 2.91 oraclezjfs 1 ? 4400 oracle 154 20 1375M 1340M sleep 11224:04 2.04 2.04 tnslsnr 1 ? 2528 oracle 178 20 2986M 5612K sleep 15:05 1.32 1.32 oraclezjfs 1 ? 4380 oracle 178 20 3009M 35136K sleep 268:00 1.16 1.16 ora_lgwr_zjfs 3 ? 4378 oracle 178 20 3009M 35152K sleep 244:54 0.74 0.74 ora_dbw0_zjfs 1 ? 54 root 152 20 3312K 2944K run 136:01 0.45 0.45 vxfsd 3 ? 1439 root 152 20 207M 84724K run 103:21 0.40 0.40 cimprovagt 3 ? 1442 root 152 20 38312K 2820K run 2725:46 0.36 0.36 cimprovagt 3 ? 12064 oracle 178 20 2986M 5264K sleep 0:20 0.32 0.32 oraclezjfs 2 ? 1436 root 152 20 56792K 13976K run 180:41 0.23 0.23 cimserver 3 pts/ta 21448 oracle 168 20 10836K 1284K sleep 0:00 0.22 0.22 top 2 ? 2381 oracle 178 20 2987M 5944K sleep 419:20 0.18 0.18 oraclezjfs 1 ? 38 root 152 20 432K 384K run 60:21 0.16 0.16 schedcpu 2 pts/tc 21573 oracle 178 20 10964K 1412K run 0:00 0.27 0.15 top 2 ? 21551 oracle 178 20 2986M 5260K sleep 0:00 0.16 0.14 oraclezjfs 3 ? 1793 root 152 20 113M 17208K run 6:09 0.14 0.14 vxsvc 0 ? 20 root 191 20 360K 320K run 33:43 0.13 0.13 ksyncer_daemon 2 ? 1429 root 152 20 25516K 5536K run 4:42 0.12 0.12 rpcd 1 ? 4297 root -27 20 46772K 38548K run 31:58 0.12 0.12 cmcld 2 ? 21518 oracle 178 20 2986M 5260K sleep 0:00 0.13 0.12 oraclezjfs 2 ? 1228 root 154 20 7812K 848K sleep 126:00 0.10 0.10 sendmail: 2 ? 39 root 191 20 288K 256K run 305:44 0.08 0.08 pagezerod 2 ? 1589 root 152 20 25072K 3992K run 1:20 0.08 0.08 swagentd $ $ |
发现listener占用的内存非常大 […]
今天接到这样一个问题，某省的报表系统的一个某个处理进程在前几天处理的速度突然变慢，而且从应用的log上还看到1555的报错：
|
1 2 3 |
[2009-02-25 00:02:27] [200010000000006] [5]: 任务1执行失败!!! [2009-02-25 01:36:55] [200010000000007] [5]: servid(015027016400) not found in map_servid_servattr!!! [2009-02-25 03:24:55] [200010000600008] [0]: 从光标中fetch数据出错 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small |
当时第一个反应就是加大undo表空间大小和undo retention参数。但是，之前的程序跑的还比较正常， […]
环境： oracle 9206 三节点rac。hp 11.11。 【10月29日，故障发生】 在一次truncate一个大表的过程中（以下操作均在节点1（gdrac1）上操作）：
|
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 |
SQL> select sum(bytes)/1024/1024/1024 size_g from user_segments where segment_name='ALERTBUFF'; SIZE_G ---------- 329.8125 1* select tablespace_name,PARTITIONED from user_tables where table_name='ALERTBUFF' SQL> / TABLESPACE_NAME PAR ------------------------------ --- DATA_DYNAMIC NO SQL> select EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from user_tablespaces where tablespace_name='DATA_DYNAMIC'; EXTENT_MAN SEGMEN ---------- ------ LOCAL MANUAL SQL> select index_name,tablespace_name,PARTITIONED from user_indexes where table_name='ALERTBUFF'; INDEX_NAME TABLESPACE_NAME PAR ------------------------------ ------------------------------ --- IDX_ALERTBUFF_ALERTTIME DATA_DYNAMIC NO PK_ALERTBUFF DATA_DYNAMIC NO |
我们执行了truncate table xxx reu […]