Thursday, March 25, 2010

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
Posted by at No comments:

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



Posted by at No comments:

Monday, March 8, 2010

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;
Posted by at No comments:

Wednesday, March 3, 2010

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
----------------------------------------------------------------------------------------------
Posted by at No comments:
Subscribe to: Posts (Atom)