Oracle 11.2.0.3 … released

Oracle Database Server Release 11.2.0.3 is now out under patch Id 10404530.

As the previous patchset, you can use it for a fresh install or to upgrade an existing one.

This release is available for linux x86 and x86-64.

Some things about Statistics …

Every Oracle DBA knows how statistics are very important for the CBO and for database performance.

Oracle have many kind of statistics :

- object statistics : statistics on tables, indexes …

- system statistics: statistics on the system where your database runs : CPU speed, time to perform a single I/O etc.

- dictionary statistics : statistics on the dictionary tables, indexes etc. (OBJ$, TAB$ tables … I_SOURCE1,  I_FILE1 indexes …)

- Fixed objects statistics : statistics on the fixed tables (X$BH etc. A non exhaustive list and explanation of X$ tables can be found here : http://yong321.freeshell.org/computer/x$table.html)

Each statistic is important for the performance of your database and so, it’s important to know where to find information about them.

• Object statistics

Object statistics are the most known statistics. Information about object statistics can be found in the DBA_ views. For example, LAST_ANALYZED column of the DBA_TABLES, DBA_INDEXES, DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS etc. told us when was last analyzed an object. Other columns like BLOCKS, NUM_ROWS etc. are populated after this analyze.

• System statistics

System statistics informs the CBO about the performance of the system. For example, information collected are CPU Speed, time to perform a single I/O or a multiblock I/O operation etc.

System statistics are available in the table SYS.AUX_STATS$. System stats can be gathered in a “NOWORKLOAD” mode (default) or in a “WORKLOAD” mode. The No workload mode gathers basic information about the system. WORKLOAD mode gathers more information and give more precise information about the performance of the system.

It’s recommanded to gather system stats in a workload mode during a representative workload phase. For example, if your system have a representative load every tuesday during 9 AM to 12 AM, you can gather system stats during 3 hours every tuesday.

Ex: Content of the AUX_STATS$ table after a  noworkload statistics gathering:

SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:47
SYSSTATS_INFO   DSTOP                      09-26-2011 11:47
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

Ex: Content of the AUX_STATS$ table after a  workload statistics gathering of 5 minutes:

SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>5);

SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:50
SYSSTATS_INFO   DSTOP                      09-26-2011 11:55
SYSSTATS_INFO   FLAGS                    0
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM             1,537
SYSSTATS_MAIN   MREADTIM             1,818
SYSSTATS_MAIN   CPUSPEED               920
SYSSTATS_MAIN   MBRC                    19
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

For more information about what mean PNAME values, just have a look here : http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i41496

• Dictionary Statistics

Dictionary statistics are similar to object statistics, but they are specific to SYS object that describe the dictionary. DBMS_STATS.GATHER_DICTIONARY_STATISTICS is the procedure used to gather statistics on these objects. To verify statistics on it, you can proceed like a classical objects except in the name of the object.

Ex:

SQL> select table_name,last_analyzed,blocks,num_rows from dba_tables where table_name in ('OBJ$','TAB$','IND$','FILE$');

TABLE_NAME LAST_ANALYZED                BLOCKS   NUM_ROWS
---------- ------------------------ ---------- ----------
TAB$       26/SEPT./2011 04PM:28:22       1236       2617
OBJ$       26/SEPT./2011 04PM:27:49        862      70094
IND$       26/SEPT./2011 04PM:28:21       1236       4103
FILE$      26/SEPT./2011 04PM:27:46          1          5

NB : Dictionary statistics are automatically gathered during DBMS_STATS.GATHER_DATABASE_STATS which is launched with the automatic optimizer statistic collection task.

• Fixed objects

Fixed objects are a little bit particular. Indeed, a fixed table (X$) seems to be a kind of C structure mapped into a table. So, fixed tables exist but not really and are not referenced in the dictionary tables. (more information here : http://www.oracle-internals.com/?p=11)

To identify statistics on fixed objects, you have to query dictionary with this query. If there’s no fixed objects statistics gathered, the query will answer 0 line, otherwise you will have one line per fixed object.

SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> select fo.name, analyzetime, ROWCNT,samplesize
  2  from tab_stats$ t, (select OBJECT_ID,NAME from V$FIXED_TABLE) fo
  3  where t.obj#=fo.object_id
  4  order by 2;

NAME                           ANALYZETIME                  ROWCNT SAMPLESIZE
------------------------------ ------------------------ ---------- ----------
X$KGLJSIM                      26/SEPT./2011 05PM:44:27          4          4
X$KGLJMEM                      26/SEPT./2011 05PM:44:27         68         68
X$KGLAU                        26/SEPT./2011 05PM:44:27        800        800
X$KGLLC                        26/SEPT./2011 05PM:44:27         12         12
X$KGLDP                        26/SEPT./2011 05PM:44:27      14976      14976
X$KGLLK                        26/SEPT./2011 05PM:44:28        831        831
X$KGLMEM                       26/SEPT./2011 05PM:44:28         68         68
X$KGLNA1                       26/SEPT./2011 05PM:44:28      38695      38695
X$KGLNA                        26/SEPT./2011 05PM:44:28      38671      38671
X$KGLOB                        26/SEPT./2011 05PM:44:31      21054      21054
X$KGLPN                        26/SEPT./2011 05PM:44:32         38         38
X$KGLST                        26/SEPT./2011 05PM:44:32         68         68
X$KGLSIM                       26/SEPT./2011 05PM:44:32         14         14
X$KGLRD                        26/SEPT./2011 05PM:44:32       8788       8788
X$KGLSN                        26/SEPT./2011 05PM:44:32        173        173
X$KGLTR                        26/SEPT./2011 05PM:44:32       1206       1206
X$KGLXS                        26/SEPT./2011 05PM:44:33      13029      13029
X$KKSCS                        26/SEPT./2011 05PM:44:33       2311       2311
X$KKSAI                        26/SEPT./2011 05PM:44:33          0          0
X$KKSBV                        26/SEPT./2011 05PM:44:33       3116       3116
X$KQLFBC                       26/SEPT./2011 05PM:44:34       3116       3116
.../...

In a next post, I will show you how system and fixed objects statistics are very important for your database performance.

BBED in Oracle 11g

If you like playing with oracle internals, maybe you use frequently BBED (Block Editor). In Oracle 11g, BBED becomes unavailable but if you search in the ins_rdbms.mk makefile (located in $ORACLE_HOME/rdbms/lib directory), you will see that the entry is still there.

If you want to use it with an Oracle 11g database, you will have to get some files from an old Oracle 10g installation and build the binary in your Oracle 11g home.

The operation is detailed in the differents points above:

1- If you have installed Oracle 10g and 11g on the same server, define your two Oracle Home (otherwise you can use scp to copy files across the network)

export ORA10G_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORA11G_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

2- Copy the librairies shipped with Oracle 10g but unavailable in the Oracle 11g

cp $ORA10G_HOME/rdbms/lib/ssbbded.o $ORA11G_HOME/rdbms/lib
cp $ORA10G_HOME/rdbms/lib/sbbdpt.o $ORA11G_HOME/rdbms/lib

3- Copy the message files shipped with Oracle 10g but unavailable in Oracle 11g

cp $ORA10G_HOME/rdbms/mesg/bbed* $ORA11G_HOME/rdbms/mesg

4- Build the bbed executable

make -f $ORA11G_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORA11G_HOME/bin/bbed $ORA11G_HOME/bin/bbed

5- Have fun …

[oracle@linux1 ~]$ . oraenv
ORACLE_SID = [oracle] ? db112
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@linux1 ~]$ $ORACLE_HOME/bin/bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 9 10:10:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

Don’t forget to use the library in the same kernel architecture …

Managing database audit Trail in Oracle 11gR2

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

- The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

- The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

- The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.

Proud to work for DIGORA

Today, no technical post, but I would like to say that I’m very proud to work for a company (DIGORA) which counts 3 Oracle Certified Masters.

Create or modify your virtualBox disks to be used in a RAC Cluster

If you want to create a disk to be used in a RAC Cluster, you have to flag your disk to be shared by many virtual machines.

Currently, it is not possible to create a shared disk by using the GUI.So, you must use the VirtualBox command line tool : VBoxManage.

Your disk has be a fixed size disk, and you have to create it first and modify it next to make it shareable.

To create a shared disk, use the createhd command :

VBoxManage createhd --filename /VDisks/asmdisk1.vdi --size 5120 --format VDI --variant Fixed

Next,  use the option –type of the modifyhd command. You can use this command to transform a disk created with the GUI to be shareable.

VBoxManage modifyhd /VDisks/asmdisk1.vdi --type shareable

Now, you can attach your disk to multiple virtual machines and use it as a shared storage device.

ORA-01031: Insufficient Privileges while creating a 11.2.0.2 Database with ASM

I recently encountered this strange error during a database creation process on my RAC :

How to resolve Could not validate ASMSNMP password due to following error- "ORA-01031: insufficient privileges"

I engaged the search mode, and I found a message in my ASM instance alert.log :

ORA-01918: user 'ASMSNMP' does not exist

By a mysterious way, the ASMSNMP user hasn’t been created during the Grid Infrastructure installation process.

So, to resolve this problem,  I have executed the steps above to create the ASMSNMP user:

1- Create or replace the existing password file for the ASM instance

[grid@rac1 ~]$ orapwd file=/u01/app/11.2.0/grid/dbs/orapw+ASM password=oracle

2- Copy this password file on every node of your cluster

[grid@rac1 dbs]$ scp orapw+ASM grid@rac2:/u01/app/11.2.0/grid/dbs
orapw+ASM                                     100% 1536     1.5KB/s   00:00

3- Create the ASMSNMP user in the ASM instance, and grant it the SYSDBA Privilege.

[grid@rac1 dbs]$ sqlplus / as sysasm
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user asmsnmp identified by oracle ;

User created.
SQL> grant sysdba to asmsnmp;

Grant succeeded.

Don’t forget … the ASM instance parameter “REMOTE_LOGIN_PASSWORDFILE” has to be set to EXCLUSIVE or you will raise an ORA-01999 error.

When the ASMSNMP user has been created, I have been able to create my clustered database and to connect to ASM instance.