R12 APPS DBA + R12 SYS ADMIN

I have recently started ORACLE APPS DBA (R12) TRAINING with 2 below mentioned courses.

<> R12 Oracle: Install, Patch, and Maintain Oracle Applications and

<> R12 Oracle Applications System Administrator.

We have learned how to install R12 EBS in our training session. The installation consist on number of DVDs and we need to create a STAGE directory, it requires over 150GB Hard Disk space, 4GB RAM (recommended) and installtion took couple of hours to complete.

I will write complete installation steps in my next post.

crontab doesnt execute bash profile..

Crontab doesnt execute bash profile.....check below errors and then solution with examples......
I have created a bash script rakesh.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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
The script is runing perfectly fine from commandline. ./rakesk.sh
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[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 above crontab command is failed when we check the /var/spool/mail/oracle file, the log file says:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Invalid format of Export Utility name
verify that ORACLE_HOME is properly set
Export terminiated unsuccessfully
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Solution: CRON does not execute .bash_profile or .profile. We need to set environment variables in the script. I have modified the script as below, and now its working fine, runing every five minutes..
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
#!/bin/bash
export ORACLE_SID=db
export PATH=$PATH:/usr/local/bin
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")/home/oracle/oracle/product/10.2.0/db_1/bin/exp rakesh/rakesh@rockdb file=/home/oracle/backups/test$DATE.dmp log=/home/oracle/backups/test$DATE.log statistics=none
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Now its working :) :)

Automating Oracle DB startup in Linux

I am writing steps that will auto start Oracle Database along with linux OS startup.

1. connect with Oracle user and edit the "/etc/oratab" file setting flag to 'Y'. soni:/home/oracle/oracle/product/10.2.0/db_1:Y
2. connect wih root user- created empty file (/etc/init.d/dbora) and write:

3. chmod 750 /etc/init.d/dbora
4. chkconfig --add dbora
5 check whether dbora is working or not.. as root issue /etc/init.d/dbora start
# /etc/init.d/dbora start
Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr
Processing Database instance "soni": log file /home/oracle/oracle/product/10.2.0/db_1/startup.log

We got any error in listner settings... This is due to a hard coded path in the dbstart script. To correct this, connect with Oracle user, edit the "$ORACLE_HOME/bin/dbstart" script and replace the following line (approximately line 78): ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle with ORACLE_HOME_LISTNER=$ORACLE_HOME and then check
# /etc/init.d/dbora start
Processing Database instance "soni": log file /home/oracle/oracle/product/10.2.0/db_1/startup.log
---------------
Congratz you are DONE :)

Oracle Installation in Linux

After Linux Installation, I have installed Oracle10g 2 in Linux machine. I followed steps given at below link.
http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html
http://www.oracle.com/technetwork/articles/smiley-10gdb-install-092939.html
Check "Part II: Configuring Linux for Oracle"

Linux Installation

Installing Linux
>Boot from RHEL4 disc1
>press enter for graphical install
** Need at least 512MB RAM / 2.5GB Space **
>Manually partition your hard drive with Disk Druid
/tmp = 512MB ext3 fixed size
/boot = 128MB ext3 fixed size
Swap = 1024MB fixed size
/ = fill to maximum allowable size

Oracle Linux

These days, I am working on Linux OS, migrating Oracle10g database from Windows to Linux.
I have installed Oracle10g in Linux. I will post the steps of "Oracle10g installation in Linux", soon.

Auditing database users..

We have enabled auditing for database users (ERP) with below parameters.

- AUDIT_TRAIL = 'db, extended'
- AUDIT ALL BY erp BY ACCESS
- Audit update table, delete table, insert table by erp by access;

DB users batch file, which is scheduled. check below post for batch file.
http://rakeshocp.blogspot.com/2010/03/dbusersbat.html

DB_USERS.bat

DB_USER.bat

@echo off

for /f "tokens=1,2,3,4 delims=/ " %%a in ('date /t') do set fdate=%%a_%%c_%%b_%%d

for /f "tokens=1,2,3,4,5,6 delims=: " %%i in ('time /t') do set HHMMSS=_%%i%%j%%k%%l%%m%%n

sqlplus "sys/****@dblive as sysdba" @D:\dblog_LIVE\audit_query_for_SYS_DB_usr.txt

zip.exe audit_log_users.zip audit_log_users.log
rename D:\dblog_LIVE\audit_log_users.zip audit_log_users_%fdate%_%HHMMSS%.zip
-------------------------------------------------------------------------

Check below post for Query returning the data from the database. http://rakeshocp.blogspot.com/2010/03/backupauditqueryforsysdbusers.html

audit_query_for_SYS_DB_users

set linesize 500

set pagesize 500

COLUMN username FORMAT A10
COLUMN owner FORMAT A10

COLUMN obj_name FORMAT A32

COLUMN os_username FORMAT A16
COLUMN userhost FORMAT A18
COLUMN sql_text FORMAT A300
COLUMN sql_bind FORMAT A300
COLUMN extended_timestamp FORMAT A35

column tm new_value file_time noprint

select to_char(sysdate, 'fmDD-MON-YYYY') tm from dual;

prompt&file_time

spool D:\dblog_LIVE\audit_log_users.log

select username, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM') time, owner, obj_name, os_username, userhost, sql_text, sql_bind fromDBA_audit_trailwhere to_char(extended_timestamp, 'fmDd-MM-YYYY') = to_char(SYSDATE, 'fmDd-MM-YYYY')and username not in ('sys', 'SYS', '/')and username = 'ERP_LIVE'and owner not in ('sys', 'SYS', '/')order by time;

spool off;

EXIT;

DATA PUMP - Parallel Import/Export

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read.

H:\>expdp hr/hr@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2.log

Export: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 12:05:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.

Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2.log

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.25 MB

. . exported "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . exported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . exported "HR"."A" 5.710 KB 10 rows
. . exported "HR"."AB" 4.929 KB 1 rows
. . exported "HR"."B" 5.695 KB 10 rows
. . exported "HR"."COUNTRIES" 6.093 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . exported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . exported "HR"."JOBS" 6.617 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . exported "HR"."LOCATIONS" 7.718 KB 23 rows
. . exported "HR"."REGIONS" 5.296 KB 4 rows
. . exported "HR"."TEST2" 4.937 KB 4 rows
. . exported "HR"."TEST3" 4.914 KB 1 rows
. . exported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
D:\HR_DIR\HR_HR_01.DMP
D:\HR_DIR\HR_HR_02.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:06:56
------------------------------------------------------------------------------------------
The two dumpfiles has been created with above export data pump command. I have deleted the HR tables, and now will import data from both dumpfiles with a single parameter, check below.

H:\>impdp hr/hr@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT.log

Import: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 12:19:12
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production


With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . imported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . imported "HR"."A" 5.710 KB 10 rows
. . imported "HR"."AB" 4.929 KB 1 rows
. . imported "HR"."B" 5.695 KB 10 rows
. . imported "HR"."COUNTRIES" 6.093 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . imported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . imported "HR"."JOBS" 6.617 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . imported "HR"."LOCATIONS" 7.718 KB 23 rows
. . imported "HR"."REGIONS" 5.296 KB 4 rows
. . imported "HR"."TEST2" 4.937 KB 4 rows
. . imported "HR"."TEST3" 4.914 KB 1 rows
. . imported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 12:23:08
H:\>
-------------------------------------------------------------------------
Without parallel clause, all data has been imported from both dumpfiles.
-------------------------------------------------------------------------
H:\>impdp hr/hr@rock directory=hrdir dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT_2.log
Import: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 15:27:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@rock directory=hrdir dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT_2.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . imported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . imported "HR"."A" 5.710 KB 10 rows
. . imported "HR"."AB" 4.929 KB 1 rows
. . imported "HR"."B" 5.695 KB 10 rows
. . imported "HR"."COUNTRIES" 6.093 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . imported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . imported "HR"."JOBS" 6.617 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . imported "HR"."LOCATIONS" 7.718 KB 23 rows
. . imported "HR"."REGIONS" 5.296 KB 4 rows
. . imported "HR"."TEST2" 4.937 KB 4 rows
. . imported "HR"."TEST3" 4.914 KB 1 rows
. . imported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEWORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 15:27:22
----------------------------------------------------------------------------------------------

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.