Oracle Database Administration: June 2013

Thursday, June 27, 2013

Oracle application Audit files information

REM *

REM * PROGRAM: rahc_sec_profiles.sql

REM * USAGE: @rahc_sec_profiles.sql

REM *

REM * LANGUAGE: SQL*Plus

REM *

REM * DESCRIPTION: Check FND security profile

REM * Please pay attention in the following profile

REM * Sign-On:Audit Level (A=None,B=User,C=Responsibility,D=Forms)

REM * Sign-On:Notification Y/N

REM * Signon Password Custom custom function to encrypt/decrypt password

REM * Signon Password Failure Limit

REM * Signon Password Length

REM * Signon Password Nouse

REM * Signon Password Hard to Guess Y/N

REM * Password case sensitive 11.5.10.2

REM *

REM * EXAMPLES:

REM * @rahc_sec_profiles.sql

REM *

REM * NOTES:

REM *

REM * CHANGES:

REM *

REM * Version Date Who Comments

REM * 1.0.0 02-May-07 Somchart L. Initial version.

REM *

prompt

prompt ====================================================================================================================================

prompt Checking security profiles

prompt ====================================================================================================================================

prompt

CLEAR COLUMNS

CLEAR BREAKS

SET TRIMSPOOL ON

SET VERIFY OFF

SET FEEDBACK OFF

SET LINESIZE 132

SET PAGESIZE 50

col profile_option_id format 999999 heading "Profile|Option ID"

col Pro_Level format a20 wrap

col user_name format a20 wrap

col Prof_Name format a40 wrap

col Prof_Value format a20 wrap

SET HEADING ON

TTITLE 'Security Profiles'

SELECT v.profile_option_id

, decode(v.level_id,10001,'Site'

,10002,'Application'

,10003,'Responsibility'

,10004,'User'

,v.level_id) Pro_Level

, user_name

, pot.user_profile_option_name prof_name

, substr(v.profile_option_value, 1,45) prof_value

FROM

apps.fnd_profile_options o

, apps.fnd_profile_options_tl pot

, apps.fnd_profile_option_values v

, apps.fnd_user fu

WHERE

o.profile_option_name = pot.profile_option_name

AND o.profile_option_id = v.profile_option_id (+)

AND o.application_id = v.application_id (+)

AND v.level_value = fu.user_id (+)

AND (pot.user_profile_option_name like 'Sign%assword%'

or pot.user_profile_option_name like 'Sign%otific%'

or pot.user_profile_option_name like 'Sign%udit%'

or pot.user_profile_option_name like 'Utilities%Diag%'

or pot.user_profile_option_name like 'ICX:Session Timeout')

ORDER BY v.level_id, pot.user_profile_option_name

TTITLE OFF

prompt

prompt Note:

prompt Metalink Note 233436.1 recommends the following profiles

prompt - SIGNON_PASSWORD_LENGTH = 8

prompt - SIGNON_PASSWORD_HARD_TO_GUESS = YES

prompt - SIGNON_PASSWORD_NO_REUSE = 180

prompt - ICX_SESSION_TIMEOUT = 30 (unless using SSO with OID)

prompt

exit

Saturday, June 22, 2013

DBMS_SCHEDULER

set feedback off

set echo off

set lines 205

column owner format a15

column job_name format a22

column program_name format a24

column status format a10

column state format a10

column last_start format a25

column last_start_norm format a18

column "DURATION (d:hh:mm:ss)" format a21

column next_run format a25

column next_run_norm format a18

var local_offset number

begin

select extract(timezone_hour from systimestamp) into :local_offset from dual;

end;

select dsj.owner,

dsj.job_name,

dsj.program_name,

dsjlmax.status,

dsj.state,

-- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,

to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,

extract(day from dsj.last_run_duration) ||':'||

lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||

lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||

lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",

-- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,

to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm

from dba_scheduler_jobs dsj,

(select job_name, status

from dba_scheduler_job_log dsjl

where log_date =

(select max(log_date)

from dba_scheduler_job_log dsjl2

where dsjl2.job_name = dsjl.job_name)

) dsjlmax

where dsj.owner not in ('SYSTEM','EXFSYS','ORACLE_OCM')

and dsj.job_name = dsjlmax.job_name;

------------------------------------------------------------------------------------

select obj#, program_action, comments from sys.scheduler$_job;

select client_name, status from dba_autotask_client;

dba_job_run_scheduler_details

-------------------------------------------------------------------

column job_name format a10

column job_creator format a10

column job_action format a10

column start_date format a40

column repeat_interval format a30

select job_name, job_creator, job_type, job_action,start_date

from user_scheduler_jobs;

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It’s also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.

To show details on job run:

select log_date

, job_name

, status

, req_start_date

, actual_start_date

, run_duration

from dba_scheduler_job_run_details

To show running jobs:

select job_name

, session_id

, running_instance

, elapsed_time

, cpu_used

from dba_scheduler_running_jobs;

To show job history:

select log_date

, job_name

, status

from dba_scheduler_job_log;

show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval

from dba_scheduler_schedules;

show all jobs and their attributes:

select *

from dba_scheduler_jobs

show all program-objects and their attributes

select *

from dba_scheduler_programs;

show all program-arguments:

select *

from dba_scheduler_program_args;

select log_date ,JOB_NAME, STATUS, ERROR# from dba_scheduler_job_run_details where JOB_NAM

E like '%REFRESH_SPIDER%' and log_date > sysdate – 5

SELECT * FROM dba_scheduler_programs;

SELECT * FROM dba_scheduler_schedules;

select log_date

, job_name

, status

, req_start_date

, actual_start_date

, run_duration

from dba_scheduler_job_run_details

SELECT * FROM dba_scheduler_jobs;

SELECT *

FROM dba_scheduler_window_groups;

SELECT *

FROM dba_scheduler_windows;

Monday, June 10, 2013

UNIX ADMINISTRATION

http://dbawiki.wordpress.com/

Oracle logs clearing on Linux hosts

06Aug

Every two weeks on Linux hosts with Oracle RAC runs this script (/home/oracle/log_backup.sh). It deletes logs older 14 days from /u01/app/ folder. Archives big current log files (alert.log, listener.log) to folder /u01/app/oracle/log_backup/ and nullifies them.
It runs every second and fourth friday of every month at 1:15 am through crontab. The script’s logs are in u01/app/oracle/log_backup/.


cd /u01/app/oracle
 mkdir log_backup
 chmod 700 /home/oracle/log_backup.sh
 chmod 700 /home/oracle/mv_log_backup.sh

#Runs every second and fourth friday of every month at 1:15 am.
 crontab -l
 crontab -e
 15 1 8-14,22-28 * Fri /home/oracle/log_backup.sh >> /u01/app/oracle/log_backup/log_backup.log 2>&1
 15 3 8-14,22-28 * Fri /home/oracle/mv_log_backup.sh

#log_backup.sh
 #Deletes logs older 14 days from /u01/app/ folder. Archives big current log files.
 export timestamp=$( date +%d.%m.%y_%H.%M.%S )
 echo "------------------------------< Start Running at $timestamp >------------------------------"





#Find out what are the biggest logs
 #du -BM /u01/app | sort -nr | grep 'log\|diag' | more
 #find /u01/app -type f -mtime +14 | xargs ls -Slh | more
 #find /u01/app -type f | grep 'log\|diag' | xargs ls -Slh | more
 #find /u01/app -size +512M | xargs ls -Slh

#Drop log backups older then 14 days
 #find /u01/app/oracle/log_backup/ -type f -mtime +14 | xargs ls -Slh | more
 find /u01/app/oracle/log_backup/ -type f -mtime +14 | xargs /bin/rm -f







#Drop all logs older then 14 days




 #find /u01/app/oracle/diag -type f -mtime +14 | xargs ls -Slh | more
 #find /u01/app/11.2.0/grid112020/cdata -type f -mtime +14 | xargs ls -Slh
 find /u01/app/oracle/diag -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/oracle/*diag/diag -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/oracle/product/11.2.0/*/log/diag/tnslsnr/*/listener/alert -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/oracle/product/11.2.0/*/rdbms/log -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/11.2.0/grid112020/log -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/oracle/OracleHomes*/agent10g/*/sysman/log -type f -mtime +14 | xargs /bin/rm -f
 find /u01/app/11.2.0/grid*/cv/log -type f -mtime +14 | xargs /bin/rm -f



#Clear alert.logs in /u01/app/oracle/diag/ folder




 #find /u01/app/oracle/diag/ -type f -name 'alert*.log' | xargs ls -lh
 for item in $(find /u01/app/oracle/diag/ -type f -name 'alert*.log') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

#Clear listener.logs in /u01/app/oracle/diag/ folder
 #find /u01/app/oracle/diag/ -type f -name 'listener*.log' | xargs ls -lh
 for item in $(find /u01/app/oracle/diag/ -type f -name 'listener*.log') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

#Clear listener log.xmls in /u01/app/oracle/diag/ folder
 #find /u01/app/oracle/diag/ -type f -name 'log*.xml' | xargs ls -lh
 for item in $(find /u01/app/oracle/diag/ -type f -name 'log*.xml') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

#Clear listener.logs in /u01/app/11.2.0/grid112020/log folder
 #find /u01/app/11.2.0/grid112020/log -type f -name 'listener*.log' | xargs ls -Slh
 for item in $(find /u01/app/11.2.0/grid112020/log -type f -name 'listener*.log') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

#Clear logs in /u01/app/11.2.0/grid112020/opmn/logs folder
 #find /u01/app/11.2.0/grid112020/opmn/logs -type f -name '*.log.*'| xargs ls -Slh
 for item in $(find /u01/app/11.2.0/grid112020/opmn/logs -type f -name '*.log.*') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

#Clear listener.logs in /u01/app/oracle/product/11.2.0/*/log/ folder
 #find /u01/app/oracle/product/11.2.0/*/log/ -type f -name 'listener*.log' | xargs ls -lh
 for item in $(find /u01/app/oracle/product/11.2.0/*/log/ -type f -name 'listener*.log') ;do
 zip -9 -r /u01/app/oracle/log_backup/log_backup.zip $item
 cat /dev/null > $item ;done

mv /u01/app/oracle/log_backup/log_backup.zip /u01/app/oracle/log_backup/log_backup_"$timestamp".zip
 echo "------------------------------< End Running at $timestamp >------------------------------"

#mv_log_backup.sh
 #Renames the log file to include date and time
 export timestamp=$( date +%d.%m.%y_%H.%M.%S )
 mv /u01/app/oracle/log_backup/log_backup.log /u01/app/oracle/log_backup/log_backup_"$timestamp".log

Thursday, June 27, 2013

Oracle application Audit files information

Saturday, June 22, 2013

DBMS_SCHEDULER

DBMS_SCHEDULER

Monday, June 10, 2013

UNIX ADMINISTRATION

About Me