Notastrophe: November 2012

Wednesday, 14 November 2012

Oracle 11g Auditing Failed Statements

To find out what queries cause ORA-00942 - table or view does not exist errors you can use the auditing features of Oracle. You can audit successful as well as unsuccessful attempt to run particular queries against the Oracle database.

audit select table by access whenever not successful; -- also insert table, delete table, update table

To read the audit trail -

select * from dba_audit_trail

To make sure auditing is on do -
SQL> SHOW PARAMETER AUDIT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>
Auditing is disabled by default,
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

as SYSDBA
alter system set audit_trail = 'db_extended' scope=pfile; -- or none, os, db, xml, xml_extended

db_extended audits sql_text and bind values;

SHUTDOWN
STARTUP

Otherwise do this from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414

create table t( id number, seq number, msg varchar2(4000) );

create sequence s;

create or replace trigger failed_to_extend_temp
   after servererror on database
   declare
       l_sql_text ora_name_list_t;
     l_n        number;
   begin
       if ( is_servererror(942) )
       then
           insert into t values ( s.nextval, 1, 'ora_sysevent = ' || ora_sysevent );
           insert into t values ( s.currval, 2, 'ora_login_user = ' || ora_login_user );
           insert into t values ( s.currval, 3, 'ora_server_error = ' || ora_server_error(1) );
           l_n := ora_sql_txt( l_sql_text );
           for i in 1 .. l_n
           loop
              insert into t values ( s.currval, 3+i, 'l_sql_text(' || i || ') = ' || 
l_sql_text(i) );
           end loop;
       end if;
   end;

Monday, 12 November 2012

Managing Oracle Middleware ORACLE_HOME and opatch

On a test system with Oracle 11gR2 and Oracle OBIEE 11.1.1.6 running opatch gives the following error -

OPatch version : 11.1.0.9.0
OUI version : 11.2.0.2
The OUI version is not applicable for the current OPatch version

I have an ORACLE_HOME for the database at /u01/app/.... and an ORACLE_HOME for the middleware piece. The ORACLE_HOME for the middleware needs to be correctly set as /u01/MiddlewareLocation/oracle_common to corretly find the middleware home and not revert to the database home.

The Oracle inventory location is stored in /etc/oraInst.loc
The inventory.xml file at <inventory_loc from oraInst.loc>/ContentsXML/inventory.xml store all Oracle homes.

For Oracle Middleware OPatch download is available from support site patch 6880880 or 5912518 for standalone installs like JDeveloper and Web Logic Server 10.3.6 (Read support note 224346.1)

For running OPatch with WLS 10.3.6 unpack the 5912518 patch and copy the OPatch folder into the wls_server10.3 location.
Make sure $ORACLE_HOME is set to point to the wls_server10.3 folder and run
opatch apply -jdk /usr/local/jdk1.6 /path/to/wls_patch

Friday, 9 November 2012

Configuring Database Vault in Oracle 11gR2 after database install

Database Vault is an additional option on the Oracle database that allows management of sensitive data to be performed by a data manager that is separate and distinct from the database administrator role. It allows the DBA to perform database maintenance tasks without ever having rights to view sensitive data. The option is critical where privacy of data is required for PII or PHI requirements.

Some scripts and products that are designed to run on Oracle will require the database vault to be disabled.

To enable and disable database vault follow the commands below:

After installing Oracle 11gR2 Enterprise Edition with Database Vault option :

Checking if Oracle Database Vault is enabled

select * from v$option where parameter = 'Oracle Database Vault';

PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault FALSE

emctl stop dbconsole

Shut down the database instance.

For single-instance installations:
sqlplus sys as sysoper
Enter password: password
SHUTDOWN NORMAL
EXIT

For Oracle RAC installations:
srvctl stop database -d db_name

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on ioracle

For Oracle RAC installations, run these commands on all nodes.
sqlplus sys as sysoper
Enter password: password
STARTUP
EXIT

select * from v$option where parameter = 'Oracle Database Vault';

PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault FALSE

Then run dbca and configure the database This will add the db owner and db admin accounts.

Then log in to EM as dbowner.

To disable audit vault so you avoid insufficient privilege errors when running RCU for OBIEE or other middleware components do -

emctl stop dbconsole

sqlplus sys as sysoper
SHUTDOWN NORMAL
EXIT

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

sqlplus sys as sysoper
STARTUP

Thursday, 8 November 2012

Adding more disk to OVM Linux image

To add more disk space to a LINUX Oracle Virtual Machine image, create the disk via the OVM administration console under the specific server that needs access to the disk. Then log in to the server and perform the commands below. This will format the newly created device and make it available to the image and ensure it is mounted at boot time.

root@myserver ~]# fdisk -l /dev/xvdc

Disk /dev/xvdc: 104.8 GB, 104857600000 bytes
255 heads, 63 sectors/track, 12748 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/xvdc doesn't contain a valid partition table

========================
Create the partitions

root@myserver ~]# fdisk /dev/xvdc

The number of cylinders for this disk is set to 12748.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-12748, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-12748, default 12748): 800

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (801-12748, default 801):
Using default value 801
Last cylinder or +size or +sizeM or +sizeK (801-12748, default 12748):
Using default value 12748

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@myserver ~]# /sbin/mkfs -t ext3 /dev/xvdc2
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
12009472 inodes, 23993077 blocks
1199653 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
733 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@myserver ~]# /sbin/e2label /dev/xvdc2 u02
[root@myserver ~]# mkswap -L SWAP3 /dev/xvdc1
Setting up swapspace version 1, size = 6580187 kB
LABEL=SWAP3, no uuid
[root@myserver ~]# swapon -L SWAP3
[root@myserver ~]# fdisk -l

Disk /dev/xvda: 12.8 GB, 12884901888 bytes
255 heads, 63 sectors/track, 1566 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/xvda1 * 1 13 104391 83 Linux
/dev/xvda2 14 1305 10377990 83 Linux
/dev/xvda3 1306 1566 2096482+ 82 Linux swap / Solaris

Disk /dev/xvdb: 18.8 GB, 18874368000 bytes
255 heads, 63 sectors/track, 2294 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/xvdb1 1 390 3132643+ 82 Linux swap / Solaris
/dev/xvdb2 391 2294 15293880 83 Linux

Disk /dev/xvdc: 104.8 GB, 104857600000 bytes
255 heads, 63 sectors/track, 12748 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/xvdc1 1 800 6425968+ 83 Linux
/dev/xvdc2 801 12748 95972310 83 Linux

[root@myserver ~]# more /proc/swaps
Filename Type Size Used Priority
/dev/xvda3 partition 2096472 4 -1
/dev/xvdb1 partition 3132632 0 -2
/dev/xvdc1 partition 6425960 0 -3
[root@myserver ~]# mkdir /u02

Edit /etc/fstab
LABEL=u02 /u02 ext3 defaults 1 2

Oracle Installation create a reference in /etc/oraInst.loc on Linux

The following error installing Oracle 11gR2 on Linux -
You do not have sufficient permissions to access the inventory '/u01/app/oraInventory'

is caused by an old install of 11gR2 pointing to a directory that no longer exists.

The inventory location is kept in /etc/oraInst.loc
altering oraInst.loc or deleting it fixes the problem

Thursday, 1 November 2012

Oracle Find all External Table Paths

Use this query to find out the location on disk of all external tables in Oracle:

select a.owner||’.'||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

Oracle 11gR2 Links

Oracle Gather Table Stats

After a large load, update or delete of data in an Oracle database schema the query optimizer statistics can lead to sub-optimal performance. To gather statistics and improve performance run the following command:

begin
dbms_stats.gather_schema_stats(
ownname => 'SCHEMA',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 8
);
end;

How many times have I seen this make a huge performance difference after a massive data load!
Make sure this is set as a regular job to keep the statistics up to date

Oracle Set Session Date

To alter the date format of the current connection's session information on an Oracle database run the following command:

alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';