audit_trail = 'xml,extened' + DB user audit all by access error..

We have enabled auditing for SYS user by
1- audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN\ICPORA\ADUMP
2- audit_sys_operations = TRUE
3- audit_trail = XML, EXTENDED

The sys user was being audited properly, later we wanted to audit ERP (db users). so we set auditing for erp_live user:

1-AUDIT ALL BY erp_live BY ACCESS;
2-AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY erp_live BY ACCESS;

after that we faced below issues
-The export of ERP_LIVE schema aborts, and ERP users application hangs and users complain that their sessions are automatically logging off. When I disabled auditing for ERP users, everything works fine.
later we found that there is bug in oracle 10g.2 with parameter 'xml, extended' and audit db user with by access.
To resolve the problem I disabled auditing for DB users and export and ERP application works fine, check my post at oracle forums.

http://forums.oracle.com/forums/thread.jspa?messageID=4094724&#4094724

Now our requirement is audit database users first and SYS user later.
In my next post I will write all the steps to audit db users.

crontab - scheduling a shell script (for oracle export)

Cron is a time-based job scheduler in Unix-like computer operating systems. 'cron' is short for Chronograph. Cron enables users to schedule jobs (commands or shell scripts) to run automatically at a certain time or date.

> connected with oracle user.
[oracle@oraclelinux bin]$ pwd
/bin

crontab -e - opens the user's crontab file for viewing/editing

crontab -l - simply lists the crontab file's contents for the user. Think of it as a "cat" function for the crontab.

-------------------------------
[oracle@oraclelinux bin]$ crontab -e

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=oracle
HOME=/
*/5 * * * * sh /home/oracle/rakesh.sh

-----------------------------------------
The script will execute every five minute.

http://en.wikipedia.org/wiki/Cron
http://www.crontabrocks.org/

--------------------------------------------------
rakesh.sh shell script :
--------------------------------------------------
#!bin/bash
#PATH=$PATH:$HOME/bin;export PATH
unset USERNAME
#DISPLAY=10.142.200.141:0.0; export DISPLAY
#Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=jsildb; export ORACLE_SID
ORACLE_HOME_LISTENER=LISTENER; export ORACLE_HOME_LISTENER
ORATAB=/etc/oratab; export ORATAB
#ORACLE_TERM=xterm; export ORACLE_TERM
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
export PATH=/usr/sbin/:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:lib:usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")

exp rakesh/rakesh@jsildb file=/home/oracle/erp_backups/rakesh_$DATE.dmp log=/home/oracle/erp_backups/rakesh_$DATE.log statistics=none

export bash script in linux with timestamp...

I have created export bash script in linux operating system to export schema and attached time of export with dump & log file..
- connect with oracle user
-home/backup.sh
- - - - - - - - - - - - - - - - - - - -
#!bin/bash
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")
/u01/app/oracle/product/10.2.0/db_1/bin/exp rakesh/rakesh@rockdb file=/home/oracle/backups/rakesh$DATE.dmp log=/home/oracle/backups/rakesh$DATE.log statistics=none
- - - - - - - - - - - - -
to run script...
./backup.sh

ORA-00031: session marked for kill

Today, i was trying to kill a session as explained in
http://rakeshocp.blogspot.com/2009/04/kill-session.html but i was getting error "ORA-00031: session marked for kill" i got to know that we need to kill an OS session then. I wanted to kill "hassan's" session.
select spid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr;
SPID OSUSER PROGRAM
------------ ------------------------------ ----------------
4844 rakesh.kumar
7168 rakesh.kumar plsqldev.exe
6012 Hassan plsqldev.exe


C:\Documents and Settings\Administrator>orakill icpora 6012
Kill of thread id 6012 in instance icpora successfully signalled.
C:\Documents and Settings\Administrator>

Thanks to the Don Burleson
http://www.dba-oracle.com/t_kill_process_windows.htm

When your query takes too long

What to do when your query is too slow? Check the link below for details:

http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0

COLD BACKUP batch file

We have scheduled a Cold backup of DR site: check the steps below
create a coldbackup.bat
-----------------------------
cd D:\oracle\product\10.2.0\db_1\BIN
sqlplus "sys/rock@rockstd as sysdba" @D:\COLDBACKUP_WEEKLY\coldbackup.sql
-----------------------------
steps for creating batch file: http://rakeshocp.blogspot.com/2007/09/batch-file-scripts.html
-----------------------------
coldbackup.sql
-----------------------------
ALTER DATABASE recover managed standby database cancel;
SHUTDOWN IMMEDIATE;
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\ARCHIVELOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES
host copy D:\oracle\product\10.2.0\db_1\database\INITrockstd.ORA F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES;
host copy E:\DATAFILES\*.DBF F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES;
host copy E:\REDOLOGS\*.LOG F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS;
host copy E:\CONTROLFILES\*.CTL F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES;
host copy E:\ARCHIVES\*.ARC F:\ROCKSTD_Coldbackup\"%DATE:/=_%\ARCHIVELOGS;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
exit;
exit

Failover Steps

Failover:
A failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.

We performed a failover in our Disastor Recovery scenario. Where the primary database was taken offline (as unavialble), and standby database was acticated as Primary Database.

The Standby database will now be activated as the new Primary database.

SQL> conn sys/****@rockstd as sysdba
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;