Notastrophe: 2012

Tuesday 18 December 2012

Adding User Accounts to OpenLDAP

To add user accounts to an LDAP repository determine what account objects are provided by the LDAP software and ensure the correct schema are enabled. In openLDAP, as an example, I've used the posix account object to create a user with a defined dn and added that user with a password to a posixGroup using the users dn as memberuid.

In slap.conf add:

include /usr/local/etc/openldap/schema/core.schema
include /usr/local/etc/openldap/schema/cosine.schema
include /usr/local/etc/openldap/schema/nis.schema

access to attr=userPassword
by self =xw
by anonymous auth
by * none

access to *
by self write
by users read
by * none

suffix "dc=us,dc=ldap,dc=com"
rootdn "cn=Manager,dc=us,dc=ldap,dc=com"

rootpw {SSHA}Bh/flrQNsDkzFV9c1C6uKkY7UuoPvuKU

(root password created using slappasswd)

create an initial structure in initial.ldif -

dn: dc=us,dc=ldap,dc=com
objectClass: dcObject
objectClass: organization
o: us.ldap.com
dc: us

dn: ou=People,dc=us,dc=ldap,dc=com
objectClass: organizationalUnit
objectClass: top
ou: People

dn: ou=Groups,dc=us,dc=ldap,dc=com
objectClass: organizationalUnit
objectClass: top
ou: Groups

run

ldapadd -x -D cn=Manager,dc=us,dc=ldap,dc=com -W -f initial.ldif
(Use password from slapd.conf)

Create a user in adduser.ldif

dn: uid=user5,ou=People,dc=us,dc=ldap,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
cn: user5
uid: user5
uidNumber: 3000
gidNumber: 3000
homeDirectory: /home/user1
#userPassword: {SSHA}4t7lrhU5hIbMyqQMMYoRK35+hhC6FZtB
userPassword: welcome1

ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f adduser.ldif

Create a group in addgroup.ldif

dn: cn=admingroup,ou=Groups,dc=us,dc=ldap,dc=com
objectClass: top
objectClass: posixGroup
cn: admingroup
userPassword: <password>
gidNumber: 3000

ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f addgroup.ldif

add the user to the group in adusertogroup.ldif

dn: cn=admingroup,ou=Groups,dc=us,dc=ldap,dc=com
changetype: modify
add: memberuid
memberuid: uid=user5,ou=People,dc=us,dc=ldap,dc=com
# this must be the dn of the user to work with WebLogic query for
# Static Group DNs from Member DN filter - (&(memberUid=%M)(objectClass=posixGroup))

ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f addusertogroup.ldif

Now search using

ldapsearch -x -w secret1 -D "cn=Manager,dc=us,dc=ldap,dc=com" -b
"dc=us,dc=ldap,dc=com" '(objectclass=*)'

#search using user5 pwd welcome1
ldapsearch -x -w welcome1 -D "uid=user5,ou=People,dc=us,dc=ldap,dc=com" -b
"dc=us,dc=ldap,dc=com" '(objectclass=*)'
# returns directory entries

Friday 7 December 2012

Recovering Oracle Enterprise Manager with Audit Vault Console after disk full.

On RHEL5 with Oracle 11gR2 Audit Vault Server if hard disk fills up then the Oracle Enterprise Manager (OEM) server.xml file has all its contents removed and the dbconsole won't start.

To fix do -

Disable Database Vault
(From Oracle Database Vault Admin Guide 11gR2)

stop the dbconsole if it is up.

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

Recreate the EM
export $ORACLE_HOSTNAME=[host]
make sure SYS, SYSMAN, DBSNMP users have CREATE SESSION grant

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

Then log in to Enterprise Manager to verify it is recreated correctly.

Recreate the Audit Vault app.
avca deploy_av
This updates OC4J config files and then errors
avca redeploy

## Had to deploy_av again, using
avca deploy_av -sid <sid> -dbalias <sid>

eg
avca deploy_av -sid av.host.com -dbalias av -avconsoleport 5500

Otherwise avca start_av and other avca commands returned "Internal Error".
Also avorcldb add_source -help returned an error saying add_source command is invalid.

Then log in to av console at [emhost]:[em_port]/av

Now go and find all server.xml files and create a backup of them.

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';

Monday 22 October 2012

Restart Oracle Database when FRA is full

The flash recovery area on Oracle is set to a fixed maximum size and it is possible for it to become full. When this happens Oracle will stop accepting inserts, updates, queries etc and will stop accepting connections except for SYSDBA. It will also fail to start if it is shutdown. To restart the server follow the steps below, they involve running commands as SYSDBA and editing the init.ora file.

If Oracle fails to start and startup.log shows eg:

ORA-03113: end-of-file on communication channel
Process ID: 9174
Session ID: 191 Serial number: 3

Look in alert_SID.log.

If it shows:

Errors in file /u01/app/oracle/diag/rdbms/t1234/T1234trace/T1234D_ora_10245.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46773248 bytes disk space from 8388608000 limit
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/T1234/archivelog/2012_11_13/o1_mf_1_20_%u_.arc'

Then the flash recovery area is full.

To fix on a test instance (ie not production data) run

sqlplus / as SYSDBA

startup mount

alter database noarchivelog;

Remove all unwanted files from the file system

Then go into rman

rman TARGET sys/pwd@SID

crosscheck archivelog all;

delete expired archivelog all;
delete force obsolete;

Then sqlplus / as SYSDBA

alter database archivelog;

alter database open;

To set the recovery window - in RMAN
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

To manage flash recovery area size -
alter system set db_recovery_file_dest_size=xG SCOPE=BOTH;

To Move the FRA create a directory (preferably on a different disk) and
vi <ORACLE_HOME>/dbs/init.ora
update the line:
db_recovery_file_dest='/u02/flash_recovery_area'

(You can also change db_recovery_file_dest in here. Back up the init.ora first)

Thursday 18 October 2012

Audit unsuccessful login in Oracle 11gR2

Oracle can audit access by object, statement and by privilege as well as via the fine grained auditing capability. This is the command to enable auditing of unsuccessful login which might indicate an attack on the database.

audit session whenever not successful;

Shrink Oracle DBF Files

SQL commands for finding block size and size of all tablespace files in an Oracle database. THe script produces the necessary commands to reduce the disk space used in the related dbf files.

select value from v$parameter where name = 'db_block_size'

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';
SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||
'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0;

SHRINK_DATAFILES
---------------------------------------------------------------------------
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\RBS01.DBF' RESIZE 25M;

Unlock Locked SYSDBA

Commands for unlocking a locked SYSDBA account in Oracle.

SET ORACLE_SID=orcl
sqlplus / AS SYSDBA
ALTER USER SYSTEM ACCOUNT UNLOCK
or ALTER USER SYSTEM IDENTIFIED BY <NEW PASSWORD> ACCOUNT UNLOCK