Maclean’s Oracle Database Tech Blog Archives

Initial buffer sizes: read 1024K, overflow 832K, change 805K

Initial buffer sizes: read 1024K, overflow 832K, change 805K 1691 #ifdef KCRFR_DEBUG 1692 ksdwrf(“Initial buffer sizes: read %luK, overflow %luK, change %luK\n”, 1693 (unsigned long)fx->krrxread_sz/1024, 1694 (unsigned long)fx->krrxovf_sz/1024, 1695 (unsigned long)kcocv_max_read_size/1024); …

March 21, 2015
oracle中导出统计信息到其他表的过程

oracle中导出统计信息到其他表的过程 exec dbms_stats.create_stat_table(‘&OWNER’,’MY_STATS_TAB’); exec dbms_stats.export_table_stats(‘&OWNER’,’&SOURCE_TABNAME’,NULL,’MY_STATS_TAB’); exec dbms_stats.import_table_stats(‘&OWNER’,’&TARGET_TABNAME’, null, ‘MY_STATS_TAB’); 检验： select table_name, num_rows from dba_tables where table_name in (‘&SOURCE_TABNAME’ ,’&TARGET_TABNAME’ );

March 20, 2015
rman 注册归档(arch)的语句

rman 注册归档(arch)的语句 One way of registering a logfile is via the following: SQL> alter database register logfile ‘/var/arch/arch_1_101.arc’; So this is a straightforward way of registering a logfile giving the full path to the logfile. However, what if you have a very large number of logfiles to register, the above does not really scale all…

March 20, 2015
resmgr:internal state change等待

resmgr:internal state change等待 resmgr:internal state change==> session 对应的resource manager plan正在改变中 Hang or similar symptoms when changing resource manager plans during heavy workload activity. One or more sessions block on a wait for “resmgr:internal state change”

March 19, 2015
drop index partition ORA-14076

SQL> select partition_name from dba_ind_partitions where index_name=’LOCAL_ONE’; PARTITION_NAME ——————————————————————————————— EMPLOYEES_PART1 EMPLOYEES_PART2 EMPLOYEES_PART3 SQL> alter index LOCAL_ONE drop partition EMPLOYEES_PART1; alter index LOCAL_ONE drop partition EMPLOYEES_PART1 * 第 1 行出现错误: ORA-14076: 提交的变更索引分区/子分区操作对本地分区的索引无效 You cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the…

March 19, 2015
clssnmvDiskCheck: Aborting, 0 of 1 configured voting disks available, need 1

cssd.log中的报错信息如下： 2013-09-25 08:46:03.739: [ CSSD][2834](:CSSNM00018:)clssnmvDiskCheck: Aborting, 0 of 1 configured voting disks available, need 1 2013-09-25 08:46:03.749: [ CSSD][2834]################################### 2013-09-25 08:46:03.749: [ CSSD][2834]clssscExit: CSSD aborting from thread clssnmvDiskPingMonitorThread 2013-09-25 08:46:03.749: [ CSSD][2834]################################### 2013-09-25 08:46:03.749: [ CSSD][2834](:CSSSC00012:)clssscExit: A fatal error occurred and the CSS daemon is terminating abnormally 2013-09-25 08:46:03.753: […

March 19, 2015
BUG 13931044 – ORA-600 [13009], [5000], [1], [17]

在BUG 13931044 – ORA-600 [13009], [5000], [1], [17]中相关的语句： SELECT * FROM PART_BRANCH_PC_BATCH_VIEW2 WHERE PAB_BRA_BRANCH_CODE = :B1 FOR UPDATE 也是没有Nested Loop的，但是通过”_nlj_batching_enabled”=false 绕过了该问题，所以还是建议先使一下使用“_nlj_batching_enabled”=false作为workaround的情况。 DIAGNOSTIC ANALYSIS =================== The same batch job fails on several databases but does not reproduce at will. The databases are not clones of eachother but are running the same batch job. The…

March 19, 2015
oracle中闪回数据库到还原点的操作

oracle中闪回数据库到还原点的操作 CREATE RESTORE POINT before_clean GUARANTEE FLASHBACK DATABASE; ==>必须保证闪回回复区flashback recovery area有足够的磁盘空间在standby 上，注意操作之前要记得关闭redo传输 alter database recover managed standby database finish force; alter database open; 操作 shutdown immediate; startup mount; flashback database to RESTORE POINT before_clean;

March 19, 2015
oracle中以测试为目的人为制造物理坏块的方法

oracle中以测试为目的人为制造物理坏块的方法 SQL> create table maclean_corrupt tablespace users as select * from dba_tables; 表已创建。 SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from maclean_corrupt where rownum<10; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ———————————— ———————————— 382307 …

March 19, 2015
Oracle ASM ACFS 安装失败问题

Oracle ASM ACFS 安装失败问题 [client(10813644)]CRS-10001:29-Sep-13 14:07 ACFS-9200: Supported [client(7798872)]CRS-10001:29-Sep-13 14:07 ACFS-9300: ADVM/ACFS distribution files found. [client(7798880)]CRS-10001:29-Sep-13 14:07 ACFS-9312: Existing ADVM/ACFS installation detected. [client(7798888)]CRS-10001:29-Sep-13 14:07 ACFS-9314: Removing previous ADVM/ACFS installation. [client(7798896)]CRS-10001:29-Sep-13 14:07 ACFS-9361: Removing device ‘acfsctl’ failed with error code ‘5888’. [client(7798898)]CRS-10001:29-Sep-13 14:07 ACFS-9307: Installing requested ADVM/ACFS software. [client(7143672)]CRS-10001:29-Sep-13 14:07 ACFS-9308: Loading installed ADVM/ACFS drivers.…

March 19, 2015