Wednesday, September 12, 2012

How to know export START TIME in Oracle database?

Aim:
To determine export start time which is generally not published in export log file.

Situation:
Below is sample of export log.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to APPLSYS
. . exporting table                       FND_USER       5641 rows exported
Export terminated successfully without warnings.

You can find export finish time by

[oracle@myhost tmp]$ ls -l 1.log
-rw-r--r--   1 oracle   dba          547 Apr  4 16:39 1.log

But how to find old (might be 2 months old) export start time which you require now and don't remember at all.


Solution:
Oracle 10g has a nice feature called Active Session History which stores all the data regarding your old sessions.

By the logfile time stamp you atleast know which day export started and you can guess the start date. In this example my export ended at 4 Apr 2011. Out of which I can determine start date should be 4 only (if export is bigger, it might be 3 Apr)

Use your anticiated date below in the query

select  sid,sample_time,session_type,event,program,action,module from  V$ACTIVE_SESSION_HISTORY where EXTRACT(day FROM sample_time)=&dt and module like '%exp%';

Enter only day(number) when asked, in this case only enter 4 (for our example).

It will list all the sessions which ran export. See the example xls attached.
** Only thing make sure is all SIDs are same since active session history is watching session at a period of time called sample_time so you will find multiple rows in output.
But sort by sample_time and pick the oldest time which is your export start time :)

Tricky but simple!!

How to schedule AWR report(awrrpt.sql) via crontab?

Hi All,


I came across a requirement to schedule a AWR snap report between 9 AM to 6 PM on certain days. 


The aim here was to automatically identify snap id for morning 9 AM and 6 PM and run the report from background and email the report.


Following is a sample script. In my test instance begin snap id is returning only 1 snap so I am good :)
If you get multiple snap id by given select statement please use min(snap_id) and max(snap_id) in begin and end respectively. I have commented the script to help understanding better.


Save the following as any shell script and schedule in cron.


myAWR.sh


## START


# Following mechanism will record begin and end snap_id by given time 9 and 18 hour of the day
# It also generate define statements which are required to run awrrpti.sql from background


sqlplus -s '/as sysdba' << EOF
set echo off
set head off
set feed off
spool /tmp/tmpScr.sql
select 'Define begin_snap= '|| snap_id from dba_hist_snapshot where 
EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=9 and
EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and 
EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and
EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');


SELECT 'Define end_snap= '|| snap_id from dba_hist_snapshot where 
EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=18 and
EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and 
EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and
EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');


select 'define  report_type  = ' || '''text''' from dual;
select 'define  report_name  = /tmp/awrrep.txt' from dual;
select 'define  inst_name    = ' || INSTANCE_NAME from v\$instance;
select 'define  db_name    = ' || name from v\$database;
select 'define  dbid = ' || dbid from v\$database;
select 'define inst_num = ' || INSTANCE_NUMBER from v\$instance;
select 'define  num_days     = 3' from dual;
select '@$ORACLE_HOME/rdbms/admin/awrrpti.sql' from dual;
exit
EOF


# Following part runs the script composed earlier with snap ids and define statements
sqlplus -s '/as sysdba' << EOF
@/tmp/tmpScr.sql
exit
EOF


## END


Crontab Example (Linux)

## Running AWR everyday 7:00 PM
00 19 * * * /njadav/myAWR.sh | mailx -s "AWR Today" myemail@mydomain.com 2>&1

Note:
To further customize, please check parameters of $ORACLE_HOME/rdbms/admin/awrrpti.sql by opening it.

Following sample ..

define  inst_num     = 1;
define  num_days     = 3;
define  inst_name    = 'Instance';
define  db_name      = 'Database';
define  dbid         = 4;
define  begin_snap   = 10;
define  end_snap     = 11;
define  report_type  = 'text';
define  report_name  = /tmp/swrf_report_10_11.txt