Shailesh’s Blog

Oracle RDA September 6, 2012

Oracle RDA is used to run to collect the statistics required by Oracle support for the purpose of diagnosis.

Following metalink doc can be referred to know in details about RDA.

Article:314422.1 “(RDA) 4 – Overview”
Article:330363.1 “(RDA) 4 – FAQ”
Article:330344.1 “(RDA) 4 – Training”
Article:330362.1 “(RDA) 4 – Troubleshooting Guide”

Once it is downloaded as per the above document, there are 3 easy steps to run it:. first go to folder where it is unzipped and under oracle user run as follows:-

./rda.sh -L modules
./rda.sh -S OS DB DBA INST
./rda.sh -vCRP OS DB DBA INST

Last few lines of out put will show you what output file needs to be uploaded to oracle support.

Enjoy !!

Unix shell script: read TNS entry from tnsnames.ora April 14, 2012

for db in `awk -F”[ =]” ‘/DESCRIPTION/ { print X }{ X=$1 }’ $DICTFILE`
do
if [[ $dblist = ” ]]
then
print ‘No database found’
else
DB_NAME=$db
echo $DB_NAME
#echo $db
fi
done

Oracle Enterprise Manager Grid Control 11g R1: BusinessService Management book ….. My Reviews August 17, 2011

Most peculiar point about this book is that this explain every stage of life cycle of system from DBA perspective.

Today’s data center and how to utilize ITIL v3 standard to enhance system quality and efficiency based on business service management.

Oracle Enterprise Manager Grid Control 11g R1: BusinessService Management book

Following are feature of this books that I realized.

• Describes the challenges on managing today’s data centers, with an emphasis on the industry standard guidelines for managing these complexities
• Descibes about key terminologies relevant in the context of OEM Grid Control such as target models, availability, performance metrics, and alerts.
• This book include a detailed description of the various steps involved in creating and configuring the group and system target models extensively.
• This books explains about the diagnostic features present in OEM Grid Control through root cause analysis.
• This explains modeling, monitoring, and managing the business services using service tests and beacons in OEM Grid Control and the diagnostic features available for service targets based on synthetic transactions.
• There is detailed discussion on the monitoring these metrics in OEM Grid Control and generating alerts on metrics upon deviation from expected behavior.
• It has focused primarily on modeling and monitoring various business services in OEM Grid Control and explain the various real-time monitoring capabilities of OEM Grid Control.

Happy reading this book !!

Coming soon…….my books reviews July 27, 2011

Reading a book is something like exploring the authors state of mind with new knowledge specially in tech books and when any books provides something different then its always a pleasure to write review on it. So being in simple word I would say that I’m reading following two books and I will publish my reviews on these.

1) Oracle Enterprise Manager Grid Control 11g R1: BusinessService Management book

2) Oracle Warehouse Builder 11g R2: Getting Started 2011

Enjoy !!

“ORA-12545: Connect failed because target host or object does not exist” using / as sysdba July 5, 2011

ORA-12545 is very common error and people use to get this quite frequently as soon as they have some mis-configuration in oracle client configuration files.

sometime back while working on a recovery issue I got this issue when I tried to connect db as “/ as sysdba” on the same host where db was running. the strange is I got this error……. this was haunting for me and I thought lot of option but nothing worked.

Click this image to enlarge

Solution:

After couple of troubleshootings I came to know that we are missing $ORACLE_HOME/bin/oracle file. I asked unix to restore this file. database opened and now up and running.

Enjoy !!

transportable tablespace export by pl/sql stored procedure August 10, 2010

In one of my development project, we had requirement that there should be stored procedure which can perform export of a particular tablespace. Same procedure can be triggred within database. I tried certain options. Following is the source code and the highlight of procedure:

1) accept two parameter – Tablespace name & UNIX script path ( for archival)

2) create & start to write the logfile of stored proc as an logfile of tts export job.

3) change tablespace read only

4) start the datapump tts export job for the tablespace.

5) Monitor the job progress and every thing report to logfile

6) prepare the list of datafile belongs to tablespace and write in another file – rdi_prestg_datafiles.lst

7) drop the datapump job once it is done.

8) Invoke the UNIX script file as the external job in DB scheduler for immediate run
UNIX script will take following parameter:
dbname, data dir path, dumpfile , stored proc logfile, datafile list file, trigger file

Unix script and database should exist on same host.

create or replace PROCEDURE sp_tts_export
(
v_tbs_name VARCHAR2,v_unix_job_path VARCHAR2)
AS
idx         NUMBER;       — Loop index
JobHandle   NUMBER;       — Data Pump job handle
PctComplete NUMBER;       — Percentage of job complete
JobState    VARCHAR2(30); — To keep track of job state
LogEntry ku$_LogEntry;    — For WIP and error messages
JobStatus ku$_JobStatus;  — The job status from get_status
Status ku$_Status;        — The status object returned by get_status
dts           VARCHAR2(140):=TO_CHAR(sysdate,’YYYYMMDDHH24MISS’);
exp_dump_file VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts||’.dmp’;
exp_log_file  VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts||’.log’;
exp_job_name  VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts;
unix_job_name VARCHAR2(100):=’unix_tts_job’;
trigger_file  VARCHAR2(100):=v_tbs_name||’_tts_export_’||dts||’.trg’;
dp_dir        VARCHAR2(500):=’DATA_PUMP_DIR’;
data_dir      VARCHAR2(4000):=”;
log_file UTL_FILE.FILE_TYPE;
log_filename VARCHAR2(500):=exp_job_name||’_main’||’.log’;
err_log_file UTL_FILE.FILE_TYPE;
dbf_lst     UTL_FILE.FILE_TYPE;
dbf_lst_name VARCHAR2(100):=’rdi_prestg_datafiles.lst’;
v_db_name   VARCHAR2(1000);
v_username  VARCHAR2(30);
t_dir_name  VARCHAR2(4000);
t_file_name VARCHAR2(4000);
t_sep_pos   NUMBER;
t_dir       VARCHAR2(30):=’temp_rdi’;
v_sqlerrm   VARCHAR2(4000);
stmt        VARCHAR2(4000);
v_temp      VARCHAR2(4000);
FUNCTION get_file
(
filename VARCHAR2,
dir      VARCHAR2 := ‘TEMP’)
RETURN VARCHAR2
IS
contents VARCHAR2(32767);
file BFILE := BFILENAME(dir, filename);
BEGIN
DBMS_LOB.FILEOPEN(file, DBMS_LOB.FILE_READONLY);
contents := UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR(file));
DBMS_LOB.CLOSE(file);
RETURN contents;
END;
BEGIN

log_file:=UTL_FILE.FOPEN (dp_dir, log_filename, ‘w’);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.PUT_LINE(log_file,’REPORT: GENERATED ON ‘ || SYSDATE);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
SELECT global_name,USER INTO v_db_name,v_username FROM global_name;

UTL_FILE.PUT_LINE(log_file,’Database:’||v_db_name);
UTL_FILE.PUT_LINE(log_file,’user running the job:’||v_username);
UTL_FILE.PUT_LINE(log_file,’for tablespace:’||v_tbs_name);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.NEW_LINE (log_file);
stmt:=’ALTER TABLESPACE ‘||v_tbs_name || ‘ read only’;

SELECT status
INTO v_temp
FROM dba_tablespaces
WHERE tablespace_name=v_tbs_name;
IF v_temp NOT LIKE ‘%READ ONLY%’ THEN
EXECUTE immediate (stmt);
UTL_FILE.PUT_LINE(log_file,’ ‘||v_tbs_name || ‘ tablespace altered to read only mode.’);
ELSE
UTL_FILE.PUT_LINE(log_file,’ ‘||v_tbs_name || ‘ tablespace already in read only mode.’);
END IF;
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Initiating the Datapump engine for TTS export…………..’);
JobHandle := DBMS_DATAPUMP.OPEN( operation => ‘EXPORT’ ,job_mode => ‘TRANSPORTABLE’ ,remote_link => NULL ,job_name => NULL
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Allocating dumpfile…………….’);
DBMS_DATAPUMP.ADD_FILE( handle => JobHandle ,filename => exp_dump_file ,directory => dp_dir ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
— ,filesize => ‘100M’
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Allocating logfile…………….’);
DBMS_DATAPUMP.ADD_FILE( handle => JobHandle ,filename => exp_log_file ,directory => dp_dir ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE );
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Setting attributes…………….’);
DBMS_DATAPUMP.set_parameter(handle => JobHandle, name=>’TTS_FULL_CHECK’, value=>1);
DBMS_DATAPUMP.METADATA_FILTER( handle => JobHandle ,NAME => ‘TABLESPACE_EXPR’ ,VALUE => ‘IN (”’||v_tbs_name||”’)’
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Now starting datapump job…………….’);
DBMS_DATAPUMP.START_JOB(JobHandle);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Monitoring the job…………….’);
————–Monitor the job
PctComplete    := 0;
JobState       := ‘UNDEFINED’;
WHILE(JobState != ‘COMPLETED’) AND (JobState != ‘STOPPED’)
LOOP
DBMS_DATAPUMP.GET_STATUS( handle => JobHandle ,mask => 15 — DBMS_DATAPUMP.ku$_status_job_error + DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip
,timeout => NULL ,job_state => JobState ,status => Status );
JobStatus := Status.job_status;
— Whenever the PctComplete value has changed, display it
IF JobStatus.percent_done != PctComplete THEN
DBMS_OUTPUT.PUT_LINE(‘*** Job percent done = ‘ || TO_CHAR(JobStatus.percent_done));
PctComplete := JobStatus.percent_done;
END IF;
— Whenever a work-in progress message or error message arises, display it
IF (BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
LogEntry                                           := Status.wip;
ELSE
IF (BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
LogEntry                                                 := Status.error;
ELSE
LogEntry := NULL;
END IF;
END IF;
IF LogEntry IS NOT NULL THEN
idx       := LogEntry.FIRST;
WHILE idx IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(LogEntry(idx).LogText);
idx := LogEntry.NEXT(idx);
END LOOP;
END IF;
END LOOP;
–copy the datafiles to data dump dir

UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Preparing the list of datafiles and tts dumpfile to dump directory in ‘|| dbf_lst_name ||’…………….’);
dbf_lst:=utl_file.fopen(dp_dir,dbf_lst_name,’w’);
FOR rec IN
(SELECT file_name FROM sys.dba_data_files WHERE tablespace_name=v_tbs_name
)
LOOP
utl_file.put_line(dbf_lst,rec.file_name);
END LOOP;
select directory_path into v_temp
from dba_directories
where directory_name=upper(dp_dir);

if substr(v_temp,length(v_temp),1)=’/’ THEN
v_temp:= v_temp || exp_dump_file;
else
v_temp:= v_temp || ‘/’ || exp_dump_file;
end if;

utl_file.put_line(dbf_lst,v_temp);

utl_file.fclose(dbf_lst);
UTL_FILE.PUT(log_file,’Done.’);
UTL_FILE.NEW_LINE (log_file);
–Delete trigger file if any
begin
UTL_FILE.FREMOVE(dp_dir,trigger_file);
exception
when others then
UTL_FILE.PUT_LINE(log_file,’ Unable to delete any previous trigger file’);
end;

begin
DBMS_SCHEDULER.DROP_JOB(unix_job_name);
exception
when others then
UTL_FILE.PUT_LINE(log_file,’ There is not any previous job to delete.’);
end;

UTL_FILE.PUT_LINE(log_file,’ Invoking the UNIX job to archive the tts files…………….’);

dbms_scheduler.create_job(
job_name => unix_job_name,
job_type => ‘EXECUTABLE’,
job_action => v_unix_job_path,
number_of_arguments => 6
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,argument_position => 1,
argument_value => substr(v_db_name,1,instr(v_db_name,’.’)-1)
);

select directory_path into data_dir from dba_directories where directory_name=dp_dir;

if substr(data_dir,length(data_dir),1)=’/’ then
data_dir:=substr(data_dir,1,length(data_dir)-1);
end if;

v_temp:=’ ‘||v_unix_job_path||’ ‘||substr(v_db_name,1,instr(v_db_name,’.’)-1)||’ ‘||data_dir||’ ‘||log_filename||’ ‘||exp_dump_file||’ ‘||trigger_file||’ ‘||dbf_lst_name;
dbms_output.put_line(v_temp);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 2,
argument_value => data_dir
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 3,
argument_value => log_filename
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 4,
argument_value => exp_dump_file
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 5,
argument_value => trigger_file
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 6,
argument_value => dbf_lst_name
);

— dbms_scheduler.enable(unix_job_name);
dbms_scheduler.run_job(unix_job_name);

loop
begin
select status into v_temp from (
select status from dba_scheduler_job_run_details
where job_name=upper(unix_job_name) order by actual_start_date desc)
where rownum<=1;
exception
when no_data_found then
dbms_output.put_line(unix_job_name||’ job is not yet in dictionaries’);
end;
if (v_temp like ‘%SUCCEEDED%’) or (v_temp like ‘%FAIL%’) then exit; end if;

DBMS_LOCK.sleep(seconds => 2);

end loop;

UTL_FILE.PUT(log_file,’Done.’);
UTL_FILE.NEW_LINE (log_file);

select ‘JOB Name:’||JOB_NAME || ‘ Status:’||status || ‘ Error:’|| error# || ‘ Run Duration:’||run_duration
into v_temp from (
select JOB_NAME,status,decode(to_char(error#),’0′,’No Error’,to_char(error#)) error#,run_duration from dba_scheduler_job_run_details
where job_name=upper(unix_job_name) order by actual_start_date desc)
where rownum<=1;
UTL_FILE.PUT_LINE(log_file,v_temp);

DECLARE
ex    BOOLEAN;
flen  NUMBER;
bsize NUMBER;
BEGIN
utl_file.fgetattr(dp_dir, trigger_file, ex, flen, bsize);
IF ex THEN
UTL_FILE.PUT_LINE(log_file,’trigger File Exists’);
ELSE
UTL_FILE.PUT_LINE(log_file,’trigger File Does Not Exist’);
END IF;
end;

UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Altering tablespace to read write…………….’);
–EXECUTE immediate (‘ALTER TABLESPACE ‘||v_tbs_name || ‘ read write’);
UTL_FILE.PUT(log_file,’ Done’);
err_log_file:=utl_file.fopen(dp_dir, exp_log_file, ‘r’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ content of export logfile…………….’);
LOOP
BEGIN
utl_file.get_line(err_log_file,v_sqlerrm);
IF v_sqlerrm IS NULL THEN
EXIT;
END IF;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
utl_file.fclose(err_log_file);
utl_file.fclose(log_file);

SYSMAN.UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => ‘rdi_apps@sasbsp13.bsca.eds.com’,
recipients => ‘shailesh.mishra@blueshieldca.com’,
message =>
‘<HTML><BODY>See attachment</BODY></HTML>’,
subject => ‘TTS export log’,
mime_type => ‘text/html’,
attachment => get_file(log_filename),
att_inline => false,
att_mime_type => ‘text/plain’,
att_filename => ‘log.txt’);

EXCEPTION
WHEN OTHERS THEN
execute immediate(‘DROP DIRECTORY ‘||t_dir);
–execute immediate (‘ALTER TABLESPACE ‘||v_tbs_name || ‘ read write’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Following error occured during TTS export ‘);
v_sqlerrm := SQLERRM;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
— DBMS_DATAPUMP.stop_job(jobHandle);
err_log_file:=utl_file.fopen(dp_dir, exp_log_file, ‘r’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ content of export logfile…………….’);
loop
utl_file.get_line(err_log_file,v_sqlerrm);
if v_sqlerrm is null then
exit;
end if;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
end loop;
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’####################################################################’);
utl_file.fclose(err_log_file);
utl_file.fclose(log_file);
SYSMAN.UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => ‘rdi_apps@sasbsp13.bsca.eds.com’,
recipients => ‘shailesh.mishra@blueshieldca.com’,
message =>
‘<HTML><BODY>See attachment</BODY></HTML>’,
subject => ‘TTS export log’,
mime_type => ‘text/html’,
attachment => get_file(log_filename),
att_inline => false,
att_mime_type => ‘text/plain’,
att_filename => ‘log.txt’);

END;

find 3rd or Third saturday falling on which date in any month July 23, 2010

One of my colleage was struggling to crack the code to find on which date 3rd saturday falls for any month. He tried very well for a code but I took this as a tricky problem. I tried couple of options and finally reached to better solution. at the end I found it is pretty simple———

select next_day(to_date(’01’||to_char(to_date(‘&input_MMYYYY’,’MM-YYYY’),’MON-YYYY’)),’sat’)+14 from dual;

enjoy….

Oracle SQL Developer problem – Backspace, Enter and Delete button in editor not working July 6, 2010

Oracle SQL Developer is independent tool which is famous now to replace various tools and Oracle has promoted this a lot with Oracle Database 11g.

While working on this, suddenly SQL Developer editor stops taking input for certain keys like Enter, Back-space, Delete and may be many more ……… I didnt reach to test every key. All of sudden this happened and then I restarted and SQL developer. but this doesn’t came back. then restarted OS then also it does not come back.

Ah…. I was feeling helpless on this. Then I tried to reach each and every page of Preference Dialog box and tried with variuos option. Hurray………. at last I reached to solution. Follow below path to exhale the problem………..

Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Enjoy !!!!