11g new feature

How to use Flash Cache on Redhat (not OEL)

August 26, 2013 Architect, hardware, linux, system No comments

QQ图片20130826223025

By ask Surachart for help

Test: Flash Cache on 11gR2 + RHEL

A Flash Cache (11gR2) is supported by OEL or Solaris. If Want To use RHEL(Example: RHEL 5.3)

Patched 8974084 before

SQL> startup
ORA-00439: feature not enabled: Server Flash Cache
ORA-01078: failure in processing system parameters

TEST: ***use "strace" commnad to trace system & signals***
$ strace -o /tmp/file01.txt -f sqlplus '/ as sysdba' <<EOF
startup
EOF

Find 2 points:
1. about /etc/*-release files.

3884  open("/etc/enterprise-release", O_RDONLY) = 8
3884  read(8, "Enterprise Linux Enterprise Linu"..., 255) = 64


2. about "rpm" cammand
32278 execve("/bin/rpm", ["/bin/rpm", "-qi", "--info", "enterprise-release"], [/* 25 vars */] <unfinished ...>
Next, it greps for “66ced3de1e5e0159” from the following output…
 
try to check on Enterprise Linux.

$ rpm -qi --info "enterprise-release"

Name        : enterprise-release           Relocations: (not relocatable)
Version     : 5                                 Vendor: Oracle USA
Release     : 0.0.17                        Build Date: Wed 21 Jan 2009 06:00:33 PM PST
Install Date: Mon 11 May 2009 11:19:45 AM PDT      Build Host: ca-build10.us.oracle.com
Group       : System Environment/Base       Source RPM: enterprise-release-5-0.0.17.src.rpm
Size        : 59030                            License: GPL
Signature   : DSA/SHA1, Wed 21 Jan 2009 06:56:48 PM PST, Key ID 66ced3de1e5e0159
Summary     : Enterprise Linux release file
Description :
System release and information files
Name        : enterprise-release           Relocations: (not relocatable)
Version     : 5                                 Vendor: Oracle USA
Release     : 0.0.17                        Build Date: Wed 21 Jan 2009 06:00:33 PM PST
Install Date: Mon 11 May 2009 11:19:45 AM PDT      Build Host: ca-build10.us.oracle.com
Group       : System Environment/Base       Source RPM: enterprise-release-5-0.0.17.src.rpm
Size        : 59030                            License: GPL
Signature   : DSA/SHA1, Wed 21 Jan 2009 06:56:48 PM PST, Key ID 66ced3de1e5e0159
Summary     : Enterprise Linux release file
Description :
System release and information files


Fixed:
1. FAKE *-release file (don't forgot backup before)
- Modify /etc/redhat-release + /etc/enterprise-release files.
$ cat /etc/redhat-release
Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)

$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)

2. FAKE rpm to check "enterprise-release" package.
- Modify /bin/rpm
#  mv /bin/rpm /bin/rpm.bin

# vi /bin/rpm
#!/bin/sh
if [ "$3" = "enterprise-release" ]
then
     echo 66ced3de1e5e0159
else
      exec /bin/rpm.bin "$*"
fi

# chmod 755 /bin/rpm

Try... Again -> startup database.

SQL> startup

Advanced use of dbms_stats for extended stats of CG

June 4, 2013 Architect, hardware No comments

Optimizer in Oracle 11g by wwf.

April 11, 2013 11g, advanced tech, oracle No comments

By wwf from ebay COC about Optimizer in Oracle 11g.

Oracle Direct NFS Performance

November 23, 2012 11g, Architect, oracle, software 1 comment

在Oracle 11g中引入了Direct Network File System(Oracle Direct NFS)的新特性,通过一个打包在Oracle内核中的NFS client来提高使用NFS时的性能,DNFS同样适用于RAC等高可用场景。

DIRECT NFS CLIENT OVERVIEW
Standard NFS client software, provided by the operating system, is not optimized for Oracle Database file I/O access patterns. With Oracle Database 11g, you can configure Oracle Database to access NFS V3 NAS devices directly using Oracle Direct NFS Client, rather than using the operating system kernel NFS client. Oracle Database will access files stored on the NFS server directly through the integrated Direct NFS Client eliminating the overhead imposed by the operating system kernel NFS. These files are also accessible via the operating system kernel NFS client thereby allowing seamless administration.

Benefits of Direct NFS Client
Direct NFS Client overcomes many of the challenges associated with using NFS with the Oracle Database. Direct NFS Client outperforms traditional NFS clients, is simple Oracle Database 11g – Direct NFS Client

最近公司准备使用dnfs 作为测试数据库的存储模式,正好借机测试了一把。

具体环境为:

[root@DCA-SRV-0133 u01]# dmidecode -s system-product-name
PowerEdge R905
[root@DCA-SRV-0133 u01]# cat /proc/meminfo |grep  MemTotal
MemTotal:       132352732 kB
[root@DCA-SRV-0133 u01]# cat /proc/cpuinfo |grep processor |wc -l
24
[root@DCA-SRV-0133 u01]# cat /etc/issue
Oracle  Server release 5.8
Kernel \r on an \m

存储使用NETAPP 标准NAS结构

测试对比dnfs,nfs 性能指标,使用数据库版本11.2.0.3 

setup by DNFS

1.mount nfs 文件

[root@DCA-SRV-0133 /]# showmount  -e nas
Export list for nas:
/vol/vol0        192.168.254.0/24
/vol/vol_user_db 192.168.254.0/24,10.0.254.0/24
[root@DCA-SRV-0133 /]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1             localhost.localdomain localhost
::1          localhost6.localdomain6 localhost6
10.0.254.1   nas
10.63.0.221   DCA-SRV-0133
[root@DCA-SRV-0133 /]# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1            101572540   3671028  92658704   4% /
/dev/sda3            101572572    192352  96137408   1% /home
tmpfs                 66176364         0  66176364   0% /dev/shm
/dev/sda4            878850916  14454200 819753688   2% /data
10.0.254.1:/vol/vol_user_db
                     1518128384   1821888 1516306496   1% /u01
[root@DCA-SRV-0133 /]# umount /u01/
[root@DCA-SRV-0133 /]# mount -t nfs 10.0.254.1:/vol/vol_user_db  /u01
[root@DCA-SRV-0133 /]# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1            101572540   3671056  92658676   4% /
/dev/sda3            101572572    192352  96137408   1% /home
tmpfs                 66176364         0  66176364   0% /dev/shm
/dev/sda4            878850916  14454200 819753688   2% /data
10.0.254.1:/vol/vol_user_db
                     1518128384   1821888 1516306496   1% /u01

2.配置数据库 dnfs

[oracle@DCA-SRV-0133 lib]$ cd $ORACLE_HOME/lib
[oracle@DCA-SRV-0133 lib]$ mv libodm11.so libodm11.so.old
[oracle@DCA-SRV-0133 lib]$ ln -s libnfsodm11.so libodm11.so
[oracle@DCA-SRV-0133 lib]$ ls -l libodm11.so
lrwxrwxrwx 1 oracle oinstall 14 Nov 22 16:03 libodm11.so -> libnfsodm11.so
[oracle@DCA-SRV-0133 lib]$ ls -l  libodm11.so
lrwxrwxrwx 1 oracle oinstall 14 Nov 22 16:03 libodm11.so -> libnfsodm11.so
[oracle@DCA-SRV-0133 lib]$ ls -l  libodm11.so.old
-rwxr-xr-x 1 oracle oinstall 60431 Nov 22 15:08 libodm11.so.old


[oracle@DCA-SRV-0133 lib]$ cat $ORACLE_HOME/dbs/oranfstab
server: nas
local:10.0.254.21 ----->这里注意由于使用了网卡bind 使用了两个IP区分nfs 网络与IDC网络,需要手工指定local ip.
path: 10.0.254.1
path: 192.168.254.1  ------------------- > MP
export: /vol/vol_user_db     mount: /u01
[oracle@DCA-SRV-0133 lib]$

3.startup database

SQL> startup
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                2237088 bytes
Variable Size              3892317536 bytes
Database Buffers       3.6507E+10 bytes
Redo Buffers                 84631552 bytes

.....




Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 128 MB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
..


Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Fri Nov 23 13:34:34 2012
PMON started with pid=2, OS id=18612
Fri Nov 23 13:34:34 2012
PSP0 started with pid=3, OS id=18616
Fri Nov 23 13:34:35 2012
VKTM started with pid=4, OS id=18620 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms

..
ORACLE_BASE from environment = /data/oracle
Fri Nov 23 13:34:36 2012
ALTER DATABASE   MOUNT
Direct NFS: channel id [0] path [10.0.254.1] to filer [nas] via local [10.0.254.21] is UP
Direct NFS: channel id [1] path [10.0.254.1] to filer [nas] via local [10.0.254.21] is UP
Successful mount of redo thread 1, with mount id 2930804780
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT

4. 使用swingbench 导入10g数据

4.1设置database sga 为5G .


DNFS performance 100 users:

AVG TPS 2222 AVG response time delay 40ms

NFS performance 100 users:

AVG TPS 1401 AVG response time delay 80ms

4.2 设置database sga 为 800M

DNFS performance 1000 users:

AVG TPS 578 AVG response time delay 1.5S

NFS performance 1000 users:

AVG TPS 400 AVG response time delay 2.0S

Other DNFS Best practice Performance:

Incremental Statistics for Partitioned Tables in 11g

November 8, 2012 11g, oracle No comments

这篇文档是我们前leader wwf 写的分析文档,指出了incremental statistic针对partition table 的一些用法,并且指出了一些bug,看不了slideshare的TX可以直接下载Inc_Stat_For_PT.doc

针对这几个bug问题在11gR2下的表现 对这篇文档做了一个补充

第一个问题:It is only sensitive for newly load data partition

SQL> create table test_inc(a number, b date, c varchar2(30), d varchar2(100), e varchar2(100), partition_key number)
  2   partition by range(partition_key)
 (
    partition p00 values less than (1),
    partition p01 values less than (2),
    partition p02 values less than (3),
    partition p03 values less than (4),
    partition p04 values less than (5),
    partition p05 values less than (6),
    partition p06 values less than (7),
    partition p07 values less than (8),
    partition p08 values less than (9),
    partition p09 values less than (10),
    partition p10 values less than (11),
    partition p11 values less than (12),
    partition p12 values less than (13),
    partition p13 values less than (14),
    partition p14 values less than (15),
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19      partition p15 values less than (16),
    partition p16 values less than (17),
    partition p17 values less than (18),
    partition p18 values less than (19),
    partition p19 values less than (20),
    partition p20 values less than (21),
    partition p21 values less than (22),
    partition p22 values less than (23),
    partition p23 values less than (24),
    partition p24 values less than (25),
    partition p25 values less than (26),
    partition p26 values less than (27),
    partition p27 values less than (28),
    partition p28 values less than (29),
    partition p29 values less than (30),
    partition p30 values less than (31),
 20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35      partition pmax values less than(maxvalue)
 ) ;
 36  
Table created.

SQL> 
SQL> create table test_inc_bak(a number, b date, c varchar2(30), d varchar2(100), e varchar2(100), partition_key number)
  2     partition by range(partition_key)
   (
      partition p00 values less than (1),
      partition p01 values less than (2),
      partition p02 values less than (3),
      partition p03 values less than (4),
      partition p04 values less than (5),
      partition p05 values less than (6),
      partition p06 values less than (7),
      partition p07 values less than (8),
      partition p08 values less than (9),
      partition p09 values less than (10),
      partition p10 values less than (11),
      partition p11 values less than (12),
      partition p12 values less than (13),
      partition p13 values less than (14),
      partition p14 values less than (15),
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19        partition p15 values less than (16),
      partition p16 values less than (17),
      partition p17 values less than (18),
      partition p18 values less than (19),
      partition p19 values less than (20),
      partition p20 values less than (21),
      partition p21 values less than (22),
      partition p22 values less than (23),
      partition p23 values less than (24),
      partition p24 values less than (25),
      partition p25 values less than (26),
      partition p26 values less than (27),
      partition p27 values less than (28),
      partition p28 values less than (29),
      partition p29 values less than (30),
      partition p30 values less than (31),
 20   21   22   23   24   25   26   27   28        partition pmax values less than(maxvalue)
   );
 29   30   31   32   33   34   35   36  
Table created.

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC')
--------------------------------------------------------------------------------
FALSE

SQL>  exec DBMS_STATS.SET_TABLE_PREFS(user,'test_inc','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC')
--------------------------------------------------------------------------------
TRUE

SQL>  select DBMS_STATS.get_PREFS('incremental', 'liu', 'test_inc_bak') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','TEST_INC_BAK')
--------------------------------------------------------------------------------
FALSE

SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

Session altered.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 0 from dual connect by rownum <= 300000;   2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 2 from dual connect by rownum <= 300000;    2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 3 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 4 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 5 from dual connect by rownum <= 300000;    2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL>  select partition_name, blocks, num_rows, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

PARTITION_NAME			   BLOCKS   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				     6928     300000 2012-11-07 23:17:42
P01				     6928     300000 2012-11-07 23:17:42
P02				     6802     300000 2012-11-07 23:17:43
P03				     7054     300000 2012-11-07 23:21:51
P04				     7054     300000 2012-11-07 23:21:50
P05				     6928     300000 2012-11-07 23:21:50

6 rows selected.

SQL> delete from test_inc partition(p05) where rownum <= 100000;

100000 rows deleted.

SQL> alter table test_inc truncate partition p04 ;

Table truncated.

SQL> insert into test_inc select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	6928 2012-11-07 23:17:42
P01				   600000      14056 2012-11-07 23:22:59
P02				   300000	6802 2012-11-07 23:17:43
P03				   300000	7054 2012-11-07 23:21:51
P05				   200000	6928 2012-11-07 23:22:58

准确的判断了数据,插入同样数据到test_inc_bak;

SQL> insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  2  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 0 from dual connect by rownum <= 300000;  


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 2 from dual connect by rownum <= 300000;   



insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 3 from dual connect by rownum <= 300000;   



insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 4 from dual connect by rownum <= 300000;   


insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 5 from dual connect by rownum <= 300000;     

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL>   2  

300000 rows created.

SQL> SQL> SQL> SQL> SQL> SQL> 
SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc_bak');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC_BAK'and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	7054 2012-11-07 23:44:01
P01				   300000	7054 2012-11-07 23:44:01
P02				   300000	6928 2012-11-07 23:44:01
P03				   300000	7054 2012-11-07 23:44:02
P04				   300000	7054 2012-11-07 23:44:02
P05				   300000	7054 2012-11-07 23:44:02

6 rows selected.

SQL>  delete from test_inc_bak partition(p05) where rownum <= 100000;

100000 rows deleted.

SQL> alter table test_inc_bak truncate partition p04 ;

Table truncated.

SQL> 
SQL> insert into test_inc_bak select rownum, sysdate+mod(rownum, 30), 'wangweifengwangweifeng'||rownum, 
  rpad('x', 50, 'x')||rownum, rpad('y', 50, 'z')||rownum, 1 from dual connect by rownum <= 300000;  2  

300000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc_bak');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC_BAK'and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
P00				   300000	7054 2012-11-07 23:45:20
P01				   600000      14194 2012-11-07 23:45:21
P02				   300000	6928 2012-11-07 23:45:21
P03				   300000	7054 2012-11-07 23:45:22
P05				   200000	7054 2012-11-07 23:45:22

可以看到所有的分区重新被分析了一次

问题二:How about I truncate all tables? in wwf’s situation,even he truncated all table but still got no analyzed.

SQL> truncate table test_inc; 

Table truncated.

SQL> exec dbms_stats.gather_table_stats(user, 'test_inc');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'TEST_INC' and num_rows > 0;

no rows selected

OK In 11gR2 这个问题已经被解决。

问题三:For subpartition table, it is totally wrong We got different result querying user_tab_subpartitions and user_tab_partitions

SQL> CREATE TABLE composite_rng_list (
  2     cust_id     NUMBER(10),
   cust_name   VARCHAR2(25),
   cust_state  VARCHAR2(2),
   time_id     DATE)
   PARTITION BY RANGE(time_id)
   SUBPARTITION BY LIST (cust_state)
   SUBPARTITION TEMPLATE(
   SUBPARTITION west VALUES ('OR', 'WA'),
   SUBPARTITION east VALUES ('NY', 'CT'),
   SUBPARTITION cent VALUES ('OK', 'TX')) (
   PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2008','DD/MM/YYYY')),
   PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
   PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')),
   PARTITION future VALUES LESS THAN(MAXVALUE));  3    4    5    6    7    8    9   10   11   12   13   14   15  

Table created.



SQL>  exec dbms_stats.set_table_prefs('liu', 'composite_rng_list', 'incremental', 'true')  ;

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('incremental', 'liu', 'composite_rng_list') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','LIU','COMPOSITE_RNG_LIST')
--------------------------------------------------------------------------------
TRUE

SQL>  insert into composite_rng_list select rownum, 'customer'||rownum, 'OR', to_date('2007-01-01', 'yyyy-mm-dd') from dual connect by rownum <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST

SQL> exec dbms_stats.gather_table_stats(user, 'COMPOSITE_RNG_LIST', granularity=>'ALL')

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST		   100000	 494 2012-11-08 00:16:50

SQL> insert into composite_rng_list select rownum, 'customer'||rownum, 'WA', to_date('2007-01-01', 'yyyy-mm-dd') from dual connect by rownum <= 100000;

100000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 'COMPOSITE_RNG_LIST', granularity=>'ALL')

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'COMPOSITE_RNG_LIST';

TABLE_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
COMPOSITE_RNG_LIST		   200000	1006 2012-11-08 00:17:24


SQL> select partition_name, SUBPARTITION_NAME, num_rows, blocks, last_analyzed from user_tab_subpartitions where table_name = 'COMPOSITE_RNG_LIST' and num_rows > 0;

PARTITION_NAME		       SUBPARTITION_NAME		NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -------------------
PER1			       PER1_WEST			  200000       1006 2012-11-08 00:17:23

SQL>  select partition_name, num_rows, blocks, last_analyzed from user_tab_partitions where table_name = 'COMPOSITE_RNG_LIST' and num_rows > 0;

PARTITION_NAME			 NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
PER1				   200000	1006 2012-11-08 00:17:24

可以看到在11gR2中所有的问题均已解决

11g Smart Flash Cache

August 30, 2012 11g, oracle, RAC No comments

整理一篇关于11g smart flash cache 的文章,vmcd

我们来做一下演示 采用FusionIO SLC 160G

[oracle@db-41 ~]$ ls -l /dev/fioa 
brw-r----- 1 oracle oinstall 252, 0 Jul 17 14:26 /dev/fioa


[root@db-41 dev]# fio-status

Found 1 ioDrive in this system
Fusion-io driver version: 2.3.1 build 123

Adapter: ioDrive
	Fusion-io ioDrive 160GB, Product Number:VRG5T SN:479912
	External Power: NOT connected
	PCIE Power limit threshold: 24.75W
	Sufficient power available: Unknown
	Connected ioDimm module:
	  fct0:	Fusion-io ioDrive 160GB, Product Number:VRG5T SN:479912

fct0	Attached as 'fioa' (block device)
	Fusion-io ioDrive 160GB, Product Number:VRG5T SN:479912
	Located in slot 0 Upper of ioDrive SN:479912
	PCI:08:00.0, Slot Number:1
	Firmware v5.0.6, rev 101583
	160.00 GBytes block device size, 203 GBytes physical device size
	Sufficient power available: Unknown
	Internal temperature: 43.3 degC, max 44.8 degC
	Media status: Healthy; Reserves: 100.00%, warn at 10.00%
	


SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 11:13:32 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 68096M
sga_target			     big integer 68096M

SQL> alter system set db_flash_cache_size=150G scope=spfile;

System altered.

SQL> alter system set db_flash_cache_file='/dev/fioa' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 7.1085E+10 bytes
Fixed Size		    2235608 bytes
Variable Size		 8053064488 bytes
Database Buffers	 6.2814E+10 bytes
Redo Buffers		  215855104 bytes
Database mounted.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel


SQL> startup
ORACLE instance started.

Total System Global Area 7.1085E+10 bytes
Fixed Size		    2235608 bytes
Variable Size		 8053064488 bytes
Database Buffers	 6.2814E+10 bytes
Redo Buffers		  215855104 bytes
Database mounted.
Database opened.
SQL> show parameter flash_cache

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file		     string	 /dev/fioa
db_flash_cache_size		     big integer 149G
SQL> alter system set  db_flash_cache_size=150G scope=spfilel
  2  

SQL> alter system set  db_flash_cache_size=150G scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 7.1085E+10 bytes
Fixed Size		    2235608 bytes
Variable Size		 8053064488 bytes
Database Buffers	 6.2814E+10 bytes
Redo Buffers		  215855104 bytes
Database mounted.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel


ORA-27042: not enough space on raw partition to fullfill request
Additional information: 3


modify db_flash_cache_size to 149G


SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 11:04:10 2012

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 7.1085E+10 bytes
Fixed Size		    2235608 bytes
Variable Size		 8053064488 bytes
Database Buffers	 6.2814E+10 bytes
Redo Buffers		  215855104 bytes
Database mounted.
Database opened.
SQL> show parameter flash_cache

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file		     string	 /dev/fioa
db_flash_cache_size		     big integer 149G

SQL> select 149/68 from dual;

    149/68
----------
2.19117647

11G New feature: Failed Logon Delays

April 27, 2012 11g, oracle 4 comments

在pub上看到一个不错的帖子 oracle 11g password delay

对这个特性 做如下说明

Failed Logon Delays

A hacker may attempt a brute force hack to break into your Oracle Database. This is where they try constant logons to the database using some form of a word list. To try to make brute force hacking more difficult, Oracle 11g includes a logon delay that takes effect after the third failed password entry attempt. After the third failed logon attempt, Oracle will incrementally delay subsequent logon or password prompts up to a maximum of 10 seconds. No delay will occur if the logon is successful.

Oracle 11g has added other features that improve security. You can configure actions related to bad packet reception, or the trace action that should occur should a bad packet be received, as well as the maximum number of failed logon attempts that a client can make before it’s connection is dropped. Finally you can opt to enable or disable communication of the release banner to a client when it connects. These are all controlled by the following new parameters:

sec_protocol_error_further_action – This parameter defines the action that should take place in the event that a bad packet is received from a remote system. This parameter can be set to the following options:

CONTINUE – Do not disconnect the client session. This is the default setting.

DROP – Drop the client connection after a specific number of bad packets. This parameter takes an integer argument that defines the number of bad packets that are acceptable.

DELAY – Delay accepting client requests after a bad packet is requested. This parameter takes an integer argument that defines the delay time in seconds.
An example of setting this parameter is seen in this code sample. Note that the parameter sec_protocol_error_further_action is not a dynamic parameter, so you need to use the scope=spfile setting to properly set this parameter:

ALTER SYSTEM SET sec_protocol_error_further_action=’DROP’ scope=spfile;


sec_protocol_error_trace_action – This parameter defines the level of tracing that should occur when bad packets are received. This parameter can be set to the following values:

NONE – No logging occurs.

TRACE – A trace file is generated when bad packets are received. This is the default setting.

LOG – A small logging message is entered in the database alert log.

ALERT – An alert message is sent to the DBA via OEM.
An example of setting this parameter is seen in the following code example. Note that the sec_protocol_error_trace_action parameter is dynamic:

ALTER SYSTEM SET sec_protocol_error_trace_action=’LOG’;

sec_max_failed_login_attempts – This parameter controls the number of authentication attempts that a given client connection can make on the server before the client process is dropped. The default value is 10. The sec_max_failed_login_attempts parameter is not dynamic and an example of it’s use can be set as seen in this code sample:

ALTER SYSTEM SET sec_max_failed_login_attempts=5 scope=spfile;

sec_return_server_release_banner – This parameter indicates if the server banner will be returned to a client connection. Not returning the banner will make hacking a database more difficult since the user will not know which version of the database they are trying to hack. The default value is TRUE and sec_return_server_release_banner is a dynamic parameter. An example of the use of this parameter is seen in this example:

ALTER SYSTEM SET sec_return_server_release_banner=FALSE;

11gR2 rac ora.asm error (ASM)

April 26, 2012 maintain, oracle, RAC No comments ,

ASM – ora.asm error

11.2.0.3 RAC CRS 无法启动

[ohasd(25286)]CRS-2112:The OLR service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-1301:Oracle High Availability Service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred
2012-04-25 14:46:14.181
[/data/11.2.0/grid/bin/orarootagent.bin(25332)]CRS-5016:Process “/data/11.2.0/grid/bin/acfsload” spawned by agent “/data/11.2.0/grid/bin/orarootagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log”
2012-04-25 14:46:17.031
[gpnpd(25510)]CRS-2328:GPNPD started on node db-41.
2012-04-25 14:46:20.794
[cssd(25593)]CRS-1713:CSSD daemon is started in clustered mode
2012-04-25 14:46:22.520
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for ‘ora.diskmon’ as its target state is OFFLINE
2012-04-25 14:46:40.715
[cssd(25593)]CRS-1707:Lease acquisition for node db-41 number 1 completed
2012-04-25 14:46:42.003
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote03; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.008
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote02; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.015
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote01; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:52.173
[cssd(25593)]CRS-1601:CSSD Reconfiguration complete. Active nodes are db-41 .
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2407:The new Cluster Time Synchronization Service reference node is host db-41.
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2401:The Cluster Time Synchronization Service started on host db-41.
[client(25843)]CRS-10001:25-Apr-12 14:46 ACFS-9391: Checking for existing ADVM/ACFS installation.
[client(25848)]CRS-10001:25-Apr-12 14:46 ACFS-9392: Validating ADVM/ACFS installation files for operating system.
[client(25850)]CRS-10001:25-Apr-12 14:46 ACFS-9393: Verifying ASM Administrator setup.
[client(25853)]CRS-10001:25-Apr-12 14:46 ACFS-9308: Loading installed ADVM/ACFS drivers.
[client(25856)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleoks.ko’ driver.
[client(25913)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleadvm.ko’ driver.
[client(25974)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleacfs.ko’ driver.
[client(26099)]CRS-10001:25-Apr-12 14:46 ACFS-9327: Verifying ADVM/ACFS devices.
[client(26103)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device ‘/dev/asm/.asm_ctl_spec’.
[client(26107)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device ‘/dev/ofsctl’.
[client(26113)]CRS-10001:25-Apr-12 14:46 ACFS-9322: completed
2012-04-25 14:47:06.975
[ohasd(25286)]CRS-2807:Resource ‘ora.asm’ failed to start automatically.

[ctssd(25738)]CRS-2405:The Cluster Time Synchronization Service on host db-41 is shutdown by user
2012-04-25 14:51:25.852
[/data/11.2.0/grid/bin/orarootagent.bin(25523)]CRS-5016:Process “/data/11.2.0/grid/bin/acfsload” spawned by agent “/data/11.2.0/grid/bin/orarootagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log”
2012-04-25 14:51:35.716
[cssd(25593)]CRS-1603:CSSD on node db-41 shutdown by user.
2012-04-25 14:51:35.725
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for ‘ora.cssdmonitor’ as its target state is OFFLINE
2012-04-25 14:51:36.224
[cssd(25593)]CRS-1660:The CSS daemon shutdown has completed
2012-04-25 14:51:38.144
[gpnpd(25510)]CRS-2329:GPNPD on node db-41 shutdown.

重建spfile :


+ASM.asm_diskgroups=’DATA’,’ARCH’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
#*.local_listener=’LISTENER_+ASM’
*.remote_login_passwordfile=’EXCLUSIVE’

eg: pfile 拉起 ASM instance mount data group

SQL>create spfile=’+DATA’ from pfile=’xxx’;

[grid@db-41 ~]$ sqlplus ‘/as sysasm’

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 26 15:21:33 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile;

NAME TYPE
———————————— ———————————
VALUE
——————————
spfile string
+DATA/yhd-cluster/asmparameter
file/registry.253.781624261
SQL>

重新启动CRS OK

[client(2162)]CRS-10001:26-Apr-12 14:47 ACFS-9392: Validating ADVM/ACFS installation files for operating system.
[client(2164)]CRS-10001:26-Apr-12 14:47 ACFS-9393: Verifying ASM Administrator setup.
[client(2167)]CRS-10001:26-Apr-12 14:47 ACFS-9308: Loading installed ADVM/ACFS drivers.
[client(2170)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleoks.ko’ driver.
[client(2227)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleadvm.ko’ driver.
[client(2286)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleacfs.ko’ driver.
[client(2415)]CRS-10001:26-Apr-12 14:47 ACFS-9327: Verifying ADVM/ACFS devices.
[client(2418)]CRS-10001:26-Apr-12 14:47 ACFS-9156: Detecting control device ‘/dev/asm/.asm_ctl_spec’.
[client(2422)]CRS-10001:26-Apr-12 14:47 ACFS-9156: Detecting control device ‘/dev/ofsctl’.
[client(2428)]CRS-10001:26-Apr-12 14:47 ACFS-9322: completed
2012-04-26 14:47:44.001
[crsd(2755)]CRS-1012:The OCR service started on node db-41.
2012-04-26 14:47:44.472
[evmd(2146)]CRS-1401:EVMD started on node db-41.
2012-04-26 14:47:46.297
[crsd(2755)]CRS-1201:CRSD started on node db-41.
2012-04-26 14:47:47.586
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/bin/lsnrctl” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.592
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/bin/lsnrctl” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.685
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/opmn/bin/onsctli” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.832
[crsd(2755)]CRS-2772:Server ‘db-41’ has been assigned to pool ‘Generic’.
2012-04-26 14:47:47.833
[crsd(2755)]CRS-2772:Server ‘db-41’ has been assigned to pool ‘ora.user’.
2012-04-26 14:51:13.534

针对这个问题 可以提出这样的疑问 CRS 如何在 datagroup 还没有mount的时候 读取 spfile 而且CRS如何知道在哪去读 spfile 下面这篇精彩的文章 很详细的介绍了process读取的整个过程:

Oracle 11gR2 have many new futures in Grid Computing area. One of them is Oracle Local Repository (OLR), this repository designed to store information and profiles for local resources, resources that dedicated to particular node. It improves the performance of accessing local resources profile information, redundancy and manageability. In Grid Infrastructure RAC configuration there is usual one global shared OCR and OLR’s on each node. In Oracle Restart environment there is only OLR repository. In 11g R2 there is also new feature, Grid Plug and Play (GPNP), as the name implies, it helps to automate and simplify some of the aspects of grid administration. GPNP maintains profile, it is XML file that stores the configuration information of some components maintained by GPNP, for example vips and interconnect information is stored here.

But in this post, I wont to discuss another new feature of 11g R2, it is the possibility to store the server parameter file (spfile) on the ASM disks, also for ASM instance itself! And there is some reasonable questions arise:

1.How process can read the spfile from ASM disks, if they are not mounted yet
2.How process knows where to get spfile if there is no init.ora file in $ORACLE_HOME/dbs with spfile=[path to spfile] parameter
3.What about asm_diskstring parameter?

Third point is important for ASM instance because, before reading spfile from ASM disks, we need to identify them, to identify them we need spfile to get asm_diskstring parameter!

To make clear all this points I will use system calls tracing tools, to trace oracle processes. We can use tools like Dtrace, strace, truss and so on, it depends on platform, I will use strace because I am on Linux.

My environment is Oracle Grid Infrastructure 11g R2 (11.2.0.1.0) in Oracle Restart mode. I simulated ASM disks using loop devices, /dev/loop*, I have 5 disks

bash> /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5

SQL> conn / as sysasm
Connected.
SQL> col path format a15

SQL> select a.name,a.state,b.name,b.path from v$asm_diskgroup a, v$asm_disk b where a.group_number=b.group_number order by b.name;

NAME STATE NAME PATH
—————————— ———– —————————— —————
DGROUP1 MOUNTED DISK1 ORCL:DISK1
DGROUP1 MOUNTED DISK2 ORCL:DISK2
DGROUP2 MOUNTED DISK3 ORCL:DISK3
DGROUP2 MOUNTED DISK4 ORCL:DISK4
DGROUP2 MOUNTED DISK5 ORCL:DISK5

As seen from the listing, I have two disk groups DGROUP1 and DGROUP2. My spfile is located on DGROUP1

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– —————————————————-
spfile string +DGROUP1/asm/asmparameterfile/registry.253.740659373

Lets make some tests

SQL> conn / as sysasm
Connected.

SQL> startup
ASM instance started

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 1
SQL> alter system set asm_power_limit=3;

System altered.

SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 3

SQL>–We set asm_power_limit to 3 in spfile

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown

SQL> !crs_stat -t
Name Type Target State Host
————————————————————
ora.DGROUP1.dg ora….up.type OFFLINE OFFLINE
ora.DGROUP2.dg ora….up.type OFFLINE OFFLINE
ora….ER.lsnr ora….er.type ONLINE ONLINE testdb03
ora.asm ora.asm.type OFFLINE OFFLINE
ora.cssd ora.cssd.type ONLINE ONLINE testdb03
ora.diskmon ora….on.type ONLINE ONLINE testdb03

SQL>– Lets start instance in nomount mode, it will not mount the diskgroups

SQL> startup nomount;
ASM instance started

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– —————————————————-
spfile string +DGROUP1/asm/asmparameterfile/registry.253.740659373

SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 3

SQL> select * from v$spparameter;
select * from v$spparameter
*
ERROR at line 1:
ORA-15001: diskgroup “DGROUP1” does not exist or is not mounted

SQL> alter system set asm_power_limit=10;
alter system set asm_power_limit=10
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable

SQL> !asmcmd
ASMCMD> ls
ASMCMD> exit

SQL> !crs_stat -t
Name Type Target State Host
————————————————————
ora.DGROUP1.dg ora….up.type OFFLINE OFFLINE
ora.DGROUP2.dg ora….up.type OFFLINE OFFLINE
ora….ER.lsnr ora….er.type ONLINE ONLINE testdb03
ora.asm ora.asm.type ONLINE ONLINE testdb03
ora.cssd ora.cssd.type ONLINE ONLINE testdb03
ora.diskmon ora….on.type ONLINE ONLINE testdb03

So, what we see, we were able to start ASM instance without mounting the disks, our process picked up correct spfile. The command SHOW PARAMETER reads the parameter values from the memory, but if we try to read directly from spfile (select from v$spparameter) or write to it (alter system set) we will get errors, because diskgroups not mounted yet. It means that our process was read spfile directly from ASM disks. The design about from which disk to read it made based on information from disk header. I use ASMlib, and my asm_diskstring parameter is equal to default value null, ASM instance by default scans the /dev/oracleasm/disks/* on Linux, that is why process found my disks properly. But what if we use HP-UX and our disks is multipathing disks in /dev/rdisk/*, ASM will not scan them by default, we need to use asm_diskstring parameter, how our process will read asm_diskstring parameter before accessing spfile?

Lets start sqlplus, after we will connect as sysasm it will initialize server process, this process will do all job, that is why I will trace this server process

bash> sqlplus /nolog
SQL> conn / as sysasm
Connected.
SQL> !ps -aefH
.
.
oracle 15505 4255 0 13:26 pts/1 00:00:00 sqlplus
oracle 15507 15505 0 13:26 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
.
.
SQL>

Our sserver process number is 15507, now I will use strace to trace it


bash> strace -o userproc.out -p 15507

start the instance with nomount option, OCSSD must be running, or instace will not start

SQL> startup nomount;

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes

SQL>

Now we can analyze the contents of the userproc.out, I will not list all contents, just useful parts of it

First row is

read(9, “001\6\3\212\6\376\377\377\377\1\376\377\377\377\376\377\377\377″…, 8208) = 49

it is our “startup nomount” command transmitted to server process from sqlplus by socket

connect(6, {sa_family=AF_FILE, path=”/var/tmp/.oracle/sOCSSD_LL_testhost_”…}, 110) = 0
open(“/u01/oracle/product/grid11g/auth/css/testdb03/A6857753/84b0b692”, O_WRONLY|O_CREAT|O_EXCL, 0644) = 13
write(13, “\nS\336[“, 4) = 4
close(13)

from this lines we can see that our process connects to socket file of OCSSD /var/tmp/.oracle/sOCSSD_LL_testhost_ to communicate with it and authenticates itself. That is why it impossible to start instance without OCSSD, if process could not connect to this socket, it would fail. Then it establishes communication with OHASD (Oracle High Availability Services Deamon) through the socket file /var/tmp/.oracle/sCRSD_UI_SOCKET, after exchange messages with OHASD it will get information about the location of spfile and asm_diskstring parameter

access(“/var/tmp/.oracle/sCRSD_UI_SOCKET”, F_OK) = 0
connect(14, {sa_family=AF_FILE, path=”/var/tmp/.oracle/sCRSD_UI_SOCKET”…}, 110) = 0
open(“/u01/oracle/product/grid11g/auth/ohasd/testdb03/A4972914/5140f6df”, O_WRONLY|O_CREAT|O_EXCL, 0644) = 15
write(15, “\20\7\3519”, 4) = 4
close(15)
write(14, “4\2\2\1\1\1\1\3\1T\235\376\t”…, 52) = 52
write(14, “8\3\2\1\1\1\1\4\1T\235\376\t”…, 56) = 56
read(14, “8\3\2\1\1\1\1\3\1T\235\376\t”…, 32768) = 56
write(14, “\212\1PC\v\2\2\5\1T\235\376\t”…, 394) = 394
read(14, “\366\nPC\v\2\2\4\1T\235\376\t”…, 32768) = 2806
write(14, “0\2\20\1\1T\235\376\t”…, 48) = 48

write(3, “kggpnpSIHAGetItem 1 = +dgroup1/ASM/asmparameterfile/registry.253.740659373″…, 75) = 7
write(3, “kfspFileNameGet name=+dgroup1/ASSM/asmparameterfile/registry.253.740659373″…, 78) = 78

write(3, “kggpnpSIHAGetItem 2 = “, 23) = 23
write(3, “kgfspb_shallow_discover dscstr=\”\””…, 33) = 33

As we can see our process got spfile location (+dgroup1/ASM/asmparameterfile/registry.253.740659373) and asm_diskstring=”” which is null by default from OHASD. Now, lets see where OHASD itself gets this information. To identify that I traced the OHASD process, and I found this lines

open(“/etc/oracle/olr.loc”, O_RDONLY) = 4
read(4, “olrconfig_loc=/u01/oracle/produc”…, 4096) = 108
read(4, “”, 4096) = 0
close(4) = 0
open(“/u01/oracle/product/grid11g/cdata/localhost/testhost.olr”, O_RDONLY|O_SYNC|O_LARGEFILE) = 4
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202\300I#\4\3OCR\226\361nA”…, 4096, 143360) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202\236\363%\4\2\3OCR\226\361nA”…, 4096, 4345856) = 4096
pread64(4, “\1\202\334\n&\4\2\3OCR\226\361nA”…, 4096, 4349952) = 4096
pread64(4, “\1\202\325\357-\4\2\3OCR\226\361nA”…, 4096, 4378624) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202\325\357-\4\2\3OCR\226\361nA”…, 4096, 4378624) = 4096

As one would expect OHASD reads this information from OLR, the path to it, it gets from /etc/oracle/olr.loc. I want to note that it is true for Oracle Restart mode, in Oracle RAC environment information is stored in GPNP profile and there is GPNPD process that maintains and manages this profile information. In Oracle Restart, as we can see OHASD process executes this role, and because there is no PGNP profile it stores information in OLR file. So, what next? Our process starts to scan all disks to identify ASM headers, after identifying them, it identifies which disks belongs to which diskgroups by information from header. There is many other metadata in the ASM diskheader that it reads, including pointers to spfile and votig disk file, it is kfdhdb.spfile, kfdhdb.spfflg (first block and number of blocks) and kfdhdb.vfstart, kfdhdb.vfend (begin block and end block). It is possible to read disk header using kfed utility from $ORACLE_HOME/bin directory. For example, lets read disk header of the /dev/loop1 which corresponds to ORCL:DISK1, spfile is on this disk.

shell> kfed read /dev/loop1 | more
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD –Indicates that this is ASM disk header
.
kfdhdb.grptyp: 2 ; 0x026: KFDGTP_NORMAL –Indicates mirroring level, in my case it is NORMAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER –Indicates that disk is the member disk of diskgroup
.
kfdhdb.dskname: DISK1 ; 0x028: length=5 –Disk name
kfdhdb.grpname: DGROUP1 ; 0x048: length=7 –Disk group name, to which this disk belongs
kfdhdb.fgname: DISK1 ; 0x068: length=5 –To which failure group this disk belongs
.
kfdhdb.secsize: 512 ; 0x0b8: 0x0200 –Disk sector size
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000 –Disk block size
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000 –Allocation Unit size, by default 1M
.
kfdhdb.vfstart: 0 ; 0x0ec: 0x00000000 –Begin block address of voting disk file
kfdhdb.vfend: 0 ; 0x0f0: 0x00000000 –End block address of voting disk file
kfdhdb.spfile: 59 ; 0x0f4: 0x0000003b –Begin block address of spfile
kfdhdb.spfflg: 1 ; 0x0f8: 0x00000001 –Number of blocks containing spfile

Now, lets see what will be next steps of our process

open(“/opt/oracle/extapi/32/asm/orcl/1/libasm.so”, O_RDONLY) = 17
read(17, “\177ELF\1\1\1\3\3\1000\v004″…, 512) = 512
close(17) = 0
open(“/dev/oracleasm/.query_version”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\1\20”, 16) = 16
read(17, “MSA\2\1\20”, 16) = 16
close(17) = 0
open(“/dev/oracleasm/.get_iid”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\2\30”, 24) = 24
read(17, “MSA\2\2\30\3”, 24) = 24
close(17) = 0
open(“/dev/oracleasm/.check_iid”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\3\30\3”, 24) = 24
read(17, “MSA\2\3\30\3”, 24) = 24
close(17) = 0
open(“/dev/oracleasm/iid/0000000000000003”, O_RDWR|O_CREAT|O_LARGEFILE, 0770) = 17

open(“/dev/oracleasm/disks/DISK1”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200-%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK2”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200+%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK3”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200!%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK4”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200#%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK5”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200)%\310”, 32) = 32
close(18) = 0

read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
.
.

open(“/u01/oracle/diag/asm/+asm/+ASM/trace/alert_+ASM.log”, O_WRONLY|O_CREAT|O_APPEND|O_LARGEFILE, 0660) = 16
write(16, “Tue Jan 18 17:45:13 2011\n”, 25) = 25
write(16, “Starting ORACLE instance (normal”…, 33) = 33
write(16, “\n”, 1) = 1
close(16)
.
.

It opens /opt/oracle/extapi/32/asm/orcl/1/libasm.so library, reads it. Then it opens special files /dev/oracleasm/.query_version, /dev/oracleasm/.get_iid and /dev/oracleasm/.check_iid, this files is interfaces to ASM device manager. First one is used to get managers version, second one is used to get identifier of the ASM disk device manager instance (not ASM instance) and third for verifying of this instance identifier. In our case ASM device managers instance identifier is 0000000000000003. Then, our process opens /dev/oracleasm/iid/0000000000000003, in other words it establishes connection to the ASM device manager instance. Process will use this interface to read and write to ASM disks. Then it checks all disks from DISK1 to DISK5. After it gets all information about disks, i.e. groups, mirroring level, AU size, failure group and so on it starts reading ASM disk through established interface. I think in this step it reads the spfile. After it gets initialization parameters it starts to allocate memory structures and starting necessary background processes.

Now lets see what is stored in OLR regarding ora.asm resource

shell> cd $ORACLE_HOME/cdata/localhost
shell> strings testhost.olr | grep dgroup1/ASM/asmparameterfile/registry | sed ‘s/~/\n/g’
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
LOAD=1
LOGGING_LEVEL=1
NAME=ora.asm
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+dgroup1/ASM/asmparameterfile/registry.253.740659373
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE=ora.asm.type
TYPE_ACL=owner:oracle:rwx,pgrp:dba:rwx,other::r–
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.1.0

We can found there information about spfile “SPFILE=+dgroup1/ASM/asmparameterfile/registry.253.740659373”, we also can see that ora.cssd is hard dependency resource, because OCSSD is responsible for synchronization between ASM and database instance. But what about asm_diskstring parameter, where is it?

shell> strings testhost.olr | grep ASM_DISKSTRING | sed ‘s/~/\n/g’

nothing, it is because my asm_diskstring parameter equals to null, default value, lets change it

SQL> alter system set asm_diskstring=’ORCL:DISK1,ORCL:DISK2′ scope=spfile;

System altered.

check OLR file again

strings testdb03.olr | grep ASM_DISKSTRING | sed ‘s/~/\n/g’
bASM_DISKSTRING
ACL=owner:oracle:rwx,pgrp:dba:rwx,other::r–
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=ORCL:DISK1,ORCL:DISK2
AUTO_START=restore
BASE_TYPE=ora.local_resource.type

Now we can see that information about asm_diskstring is also stored in OLR profile. And next time, at ASM instance startup it will scan only specified disk strings. If we specify disks on which spfile not present, our instance will not start


SQL> alter system set asm_diskstring=’ORCL:DISK3,ORCL:DISK4,ORCL:DISK5′ scope=spfile;

System altered.

SQL> startup nomount force;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DGROUP1/asm/asmparameterfile/registry.253.740659373’
ORA-17503: ksfdopn:2 Failed to open file +DGROUP1/asm/asmparameterfile/registry.253.740659373
SQL>

You can also use the ocrdump utility to extract this entries on any platform in XML format:

shell> ocrdump -local -keyname SYSTEM.OHASD.RESOURCES.ora\!asm.CONFIG -xml -noheader
shell> more OCRDUMPFILE


SYSTEM.OHASD.RESOURCES.ora!asm.CONFIG
ORATEXT

PROCR_ALL_ACCESS
PROCR_NONE
PROCR_NONE
oracle
dba

同样 我们在11.2.0.3的OLR 文件中可以找到SPFILE 的位置:


SPFile=”+DATA/yhd-cluster/asmparameterfile/registry.253.781624261″/>

这里需要说明一点 11.2.0.3 修改diskstring OLR 文件并没有出现对应的修改记录

另外针对 ASM_DISKGROUPS 需要一点说明:

SQL> create pfile=’/tmp/tmp.ctl’ from spfile=’+DATA/yhd-cluster/asmparameterfile/registry.253.781624261′;

File created.

[grid@db-41 ~]$ cat /tmp/tmp.ctl
+ASM1.__oracle_base=’/data/11.2.0’#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups=’DATA’,’ARCH’
+ASM1.asm_diskgroups=’ARCH’#Manual Mount
*.asm_diskgroups=’ARCH’,’DATA’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’

SQL> alter system set asm_diskgroups=’ARCH’,’DATA’ scope=both sid=’+ASM1′;

System altered.

SQL> create pfile=’/tmp/tmp.ctl’ from spfile=’+DATA/yhd-cluster/asmparameterfile/registry.253.781624261′;

File created.

[grid@db-41 ~]$ cat /tmp/tmp.ctl
+ASM1.__oracle_base=’/data/11.2.0’#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups=’DATA’,’ARCH’
*.asm_diskgroups=’ARCH’,’DATA’
+ASM1.asm_diskgroups=’ARCH’,’DATA’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’

针对这个问题 这篇文章也做过说明:http://comtmr.blogspot.com/2011/01/11gr2-mysteriously-changing.html

Reference:https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1059856.1

Create a database link with the new host:port/service syntax

March 8, 2012 11g, maintain, oracle 1 comment

Create a database link with the new host:port/service syntax

Submitted by tanelpoder on Mon, 2012-03-05 15:57

Share
I just noticed that (finally) in 11.2 this syntax is supported:

SQL> CREATE DATABASE LINK demo_x2
2 CONNECT TO tanel IDENTIFIED BY password
3 USING ‘exadb03:1521/DEMO’;
Database link created.
This just makes life a bit easier as there’s no need to use the long TNS format entry (or a tnsnames.ora/LDAP alias). It might work in 11.1 too (haven’t tested) but it didn’t work on 10.2.0.4 …

Update: This feature works for dblinks in 10.2 onwards – when I tested it on my 10.2, I got an error initially, but it was because the hostname I used didn’t resolve to an IP. Thanks to Randolf Geist for pointing this out.

In case you didn’t know, the sqlplus supports such an easy connect method since 10g:

tanel@mac02:~$ sqlplus tanel/password@exadb03/DEMO
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 5 09:51:27 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
If you omit the port from the syntax, it defaults to 1521.

eg:MY test

SQL> create database link test11g connect to user_data2 identified by xxxxxx using ‘10.0.0.42:1521/user’;

Database link created.

SQL> select * from global_name@test11g;

GLOBAL_NAME
——————————————————————————–
USER

11g new feature Index Invisible

February 15, 2012 11g, oracle No comments

11g为我们提供了Index Invisible特性,使用这个特性可以轻松的将index置为不可用状态,对比之前的将索引置为unusable
最大的改进是,当index设置为unusable时,索引将不再随着表数据的更新而更新,导致如果重新使用index 需要rebuild等操作
这对大型的OLTP系统是极大的挑战,而invisible则不然

下面做个测试:

SQL> create table t_test1 as select * from all_objects;

Table created.

SQL> create index t_test1_idx on t_test1(object_name)invisible ;

Index created.

SQL> analyze table t_test1 compute statistics for table for all indexes;

Table analyzed.

SQL> set autot trace exp

SQL> select * from t_test1 where object_name like ‘T_%’;

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 1883417357

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1805 | 170K| 137 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 1805 | 170K| 137 (1)| 00:00:02 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_NAME” LIKE ‘T_%’)

SQL>

此时CBO走了全表扫描

SQL> alter index t_test1_idx visible;

Index altered.

将index置为 visible

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 3466041839

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1805 | 170K| 135 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 1805 | 170K| 135 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 325 | | 3 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“OBJECT_NAME” LIKE ‘T_%’)
filter(“OBJECT_NAME” LIKE ‘T_%’)

还有一种方法可以使CBO 使用到index

SQL> alter index t_test1_idx invisible;

Index altered.

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 3466041839

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1805 | 170K| 135 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 1805 | 170K| 135 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 325 | | 3 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“OBJECT_NAME” LIKE ‘T_%’)
filter(“OBJECT_NAME” LIKE ‘T_%’)

通过修改 optimizer_use_invisible_indexes同样可以强制使用 invisible index,同样需要注意的是即使使用hint 如果index 为 invisible hint将无法生效。


Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.