tag:blogger.com,1999:blog-2807785752181271137.post846616866677336144..comments2024-03-06T12:33:57.350+01:00Comments on Damir Vadas, Oracle as I learned: Automated AWR reports in Oracle 10g/11gDamir Vadashttp://www.blogger.com/profile/15963017378937428976noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-2807785752181271137.post-31740893213825678872019-06-01T05:45:58.724+02:002019-06-01T05:45:58.724+02:00Any advise on how to get around this error?
ORA-20...Any advise on how to get around this error?<br />ORA-20019: Database/Instance 4009464705/1 was re-started during specified snapshot interval 39576-39587. Yes, the database was re-started sometime between 0700-1800. <br /><br />Also, is it a big change to have this run like awrgrpt where it generates AWR for all the nodes as a whole instead of one for each node?newbie01.oraclehttps://www.blogger.com/profile/09810535022731732348noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-57580727629155714162017-01-31T21:41:07.802+01:002017-01-31T21:41:07.802+01:00Hello Damir,
This reate_awr_report_for_database.sq...Hello Damir,<br />This reate_awr_report_for_database.sql has been working for me on my smaller databases for over a year. But when migrated to a bigger 8TB datase it is failing with <br /><br />ORA-29285: file write error<br />ORA-06512: at line 67<br />ORA-29285: file write error<br /><br />Line 67 is<br />execute immediate('create or replace directory awrdir as '''||v_dir||'''');<br /><br />I have over a TB of space in v_dir and the directory is being created and dropped as expected.<br />The .html output is produced with zero size - no data.<br /><br />Googling, I am of the impression that the file being assembled may be larger that expected by the output. Can the number 32767 on line 71 that starts with "v_file := UTL_FILE.fopen .......32767 " by increased or what do you think the problem is?<br /><br />Thanks<br />Mathias mawundagahttps://www.blogger.com/profile/15117367998546800417noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-18999535131635983562015-09-01T17:02:13.652+02:002015-09-01T17:02:13.652+02:00I will post in the future something like this. Kee...I will post in the future something like this. Keep connected.Damir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-24554491004748007922015-09-01T16:59:52.366+02:002015-09-01T16:59:52.366+02:00I got the script working.
Do you have a script to...I got the script working.<br /><br />Do you have a script to automate ADDM reports such the above?<br />Thanksmawundagahttps://www.blogger.com/profile/15117367998546800417noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-17184462420596587762015-08-27T17:37:31.996+02:002015-08-27T17:37:31.996+02:00Hi Damir,
I am still having this "ORA-29280:...Hi Damir,<br /><br />I am still having this "ORA-29280: invalid directory path" error. Where in this script do I replace 'awr' to 'AWR'? I have set<br />v_dir VARCHAR2(256) := '/u01/app/oracle/admin/OFD1/scripts/monitor/awr_reports';<br /><br />This is the location of my script and intended location of reports.<br /><br />Is the "Original code" above correct - posted by Anonymous on December 9, 2014 at 5:34 AM ?<br /><br />Thanksmawundagahttps://www.blogger.com/profile/15117367998546800417noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-63243419495517766032014-12-13T10:26:52.938+01:002014-12-13T10:26:52.938+01:00Hey Damir, Thanks for your help, it worked out wit...Hey Damir, Thanks for your help, it worked out with your advise, by making awr to AWR, but my doubt is , why it is not working with small case awrAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-36931412104561953112014-12-09T08:51:00.771+01:002014-12-09T08:51:00.771+01:00ORA-29280: invalid directory path, 4 errors
0) Is ...ORA-29280: invalid directory path, 4 errors<br />0) Is Oracle directory value passed correctly to script?<br />1) Check on OS level that this directory exists<br />2) Check that user you are running this script has read, write grants on that Oracle directory<br />3) Chack that oracle user can read and write to OS directory defined by Oracle dir value<br /><br /><br />I'm pretty sure that it is 1). So check to replace 'awr' to 'AWR' (capital letters).<br /><br />Hope this helps<br />Damir<br />Damir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-67853819632748708002014-12-09T05:34:24.765+01:002014-12-09T05:34:24.765+01:00Hi Damir
I am still getting the invalid director...Hi Damir <br /><br />I am still getting the invalid directory path error even after mentioning the paths correctly <br /><br />SQL> @awr1.sql<br />end snap_id 60102<br />begin snap_id 60091<br />v_start_date 141208_0700<br />v_end_date 1800<br />v_instance.instance_name:dwld1<br />ORA-29280: invalid directory path<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Original code : <br /><br />set serveroutput on<br />set linesize 166<br />set pagesize 600<br />set trimout on<br /><br />DECLARE<br /> cursor c_instance is<br /> SELECT instance_number, instance_name<br /> FROM gv$instance<br /> ORDER BY 1<br /> ;<br /><br /> c_dir CONSTANT VARCHAR2(256) := '/home/oracle';<br /> v_dir VARCHAR2(256) := '/tmp/awr_reports';<br /><br /> v_dbid v$database.dbid%TYPE;<br /> v_dbname v$database.name%TYPE;<br /> v_inst_num v$instance.instance_number%TYPE := 1;<br /> v_begin NUMBER;<br /> v_end NUMBER;<br /> v_start_date VARCHAR2(20);<br /> v_end_date VARCHAR2(20);<br /> v_options NUMBER := 8; -- 0=no options, 8=enable addm feature<br /> v_file UTL_FILE.file_type;<br /> v_file_name VARCHAR(50);<br /><br />BEGIN<br /> -- get database id<br /> SELECT dbid, name<br /> INTO v_dbid, v_dbname<br /> FROM v$database;<br /><br /> -- get end snapshot id<br /> SELECT MAX(snap_id)<br /> INTO v_end<br /> FROM dba_hist_snapshot<br /> WHERE to_char(begin_interval_time,'HH24') = '18';<br /> dbms_output.put_line('end snap_id '||v_end);<br /><br /> -- get start snapshot id<br /> SELECT MAX(snap_id)<br /> INTO v_begin<br /> FROM dba_hist_snapshot<br /> WHERE to_char(begin_interval_time,'HH24') = '07'<br /> AND snap_id < v_end;<br /> dbms_output.put_line('begin snap_id '||v_begin);<br /><br /> SELECT to_char(begin_interval_time,'YYMMDD_HH24MI')<br /> INTO v_start_date<br /> FROM dba_hist_snapshot<br /> WHERE snap_id = v_begin<br /> AND instance_number = v_inst_num<br /> ;<br /> dbms_output.put_line('v_start_date '||v_start_date);<br /><br /> SELECT to_char(begin_interval_time,'HH24MI')<br /> INTO v_end_date<br /> FROM dba_hist_snapshot<br /> WHERE snap_id = v_end<br /> AND instance_number = v_inst_num<br /> ;<br /> dbms_output.put_line('v_end_date '||v_end_date);<br /><br /> -- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!<br /> execute immediate('create or replace directory awrdir as '''||v_dir||'''');<br /> -- lets go to real work...write awrs to files...<br /> FOR v_instance IN c_instance LOOP<br /> dbms_output.put_line('v_instance.instance_name:'||v_instance.instance_name);<br /> v_file := UTL_FILE.fopen('awrdir', 'awr'|| v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html', 'w', 32767);<br /> FOR c_report IN (<br /> SELECT output FROM TABLE(dbms_workload_repository.awr_report_html( v_dbid,v_instance.instance_number,v_begin,v_end,v_options))<br /> ) LOOP<br /> UTL_FILE.PUT_LINE(v_file, c_report.output);<br /> END LOOP;<br /> UTL_FILE.fclose(v_file);<br /> END LOOP;<br /> execute immediate('drop directory awrdir');<br />EXCEPTION<br /> WHEN OTHERS THEN<br /> DBMS_OUTPUT.PUT_LINE(SQLERRM);<br /> IF UTL_FILE.is_open(v_file) THEN<br /> UTL_FILE.fclose(v_file);<br /> END IF;<br /> BEGIN<br /> execute immediate('drop directory awrdir');<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> null;<br /> END;<br />END;<br />/<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-71682660413433076222014-03-21T06:37:17.668+01:002014-03-21T06:37:17.668+01:00before that, look carefully at mine reply January ...before that, look carefully at mine reply January 16, 2010 at 1:11 AM.Damir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-90854048228714794782014-03-21T06:36:18.268+01:002014-03-21T06:36:18.268+01:00show me full log of sqlplus output....everything.show me full log of sqlplus output....everything.Damir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-18684538094977553552014-03-20T19:20:29.079+01:002014-03-20T19:20:29.079+01:00I ran your script w/mods and got this:
SQL> @cr...I ran your script w/mods and got this:<br />SQL> @create_awr_report_for database<br />end snap_id 189<br />begin snap_id 157<br />v_start_date 140319_0630<br />v_end_date 2230<br />ORA-01780: string literal required<br />PL/SQL procedure successfully completed.<br />Anonymoushttps://www.blogger.com/profile/05019014692909676863noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-7481311317368471072013-03-21T12:21:39.321+01:002013-03-21T12:21:39.321+01:00Thank you !!!!!!!Thank you !!!!!!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-48192017316944603042012-05-16T11:01:47.096+02:002012-05-16T11:01:47.096+02:00@Walter
If you feel the provided info is useless,...@Walter<br /><br />If you feel the provided info is useless, please give the right solution over here.<br /><br />Else stop doing this stupid thing in public forums.<br /><br />Thanks,<br />ManisAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-71905113830534867632012-05-01T16:04:35.667+02:002012-05-01T16:04:35.667+02:00@Walter,
You are RUDE. People offer a solution. Yo...@Walter,<br />You are RUDE. People offer a solution. You can take it or leave it. A we DBAs build on each other work and build <br />up each other. You are no DBA<br />mingAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-45771613525112837832011-12-20T23:10:38.854+01:002011-12-20T23:10:38.854+01:00@walter,
First this is global way, you can always ...@walter,<br />First this is global way, you can always fine grained to hours or smaller period you like-but always a start is from the top.<br />I have found in situations if anything happens now there is one strong perspective in the past in a quick way. <br />Also gives you a picture if DBA change or someone other comes "to play" at once.<br />But thank you for your honest words ... maybe you can give us your written thoughts on this. This would really interest me a lot.<br />Rg,<br />DamirDamir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-8859861505714084522011-12-20T23:01:50.750+01:002011-12-20T23:01:50.750+01:00This script is garbage, consolidates everything in...This script is garbage, consolidates everything into one report which is pretty much useless. Amateur scritp for amateur dba's.walternoreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-10050930916932689552010-12-01T18:45:37.409+01:002010-12-01T18:45:37.409+01:00@Sapna,
could you please be more specific?
I do n...@Sapna,<br /><br />could you please be more specific?<br />I do not follow you.<br /><br />Rg,<br />DamirDamir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-90798406163948097022010-12-01T17:32:31.597+01:002010-12-01T17:32:31.597+01:00Why are the date formats different for start date ...Why are the date formats different for start date and end dates?Unknownhttps://www.blogger.com/profile/11047035514968134672noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-16753266434588319022010-01-16T01:11:59.586+01:002010-01-16T01:11:59.586+01:00Denis,
you are right...
...
81 IF UTL_FILE.i...Denis,<br />you are right...<br />...<br /> 81 IF UTL_FILE.is_open(v_file) THEN<br /> 82 UTL_FILE.fclose(v_file);<br /> 83 END IF;<br /> 84 END;<br /> 85 /<br />end snap_id 481<br />begin snap_id 470<br />v_start_date 100114_0700<br />v_end_date 1800<br />v_instance.instance_name=xe<br />ORA-29280: invalid directory path<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> <br /><br />So:<br /><br />create directory "some_name as "v_dir" ;<br />is necessary needed.<br /><br />THX for pointing this out. I have put in original code....Damir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-65408763796726863732010-01-15T21:51:21.860+01:002010-01-15T21:51:21.860+01:00Damir,
Thanks for sharing this great tip.
However...Damir,<br /><br />Thanks for sharing this great tip.<br />However initially I can not make it work, I always got "ORA-29280: invalid directory path", even I just put /tmp in the utl_file.fopen('/tmp', ...). Later I did:<br />create directory awrdir as '/tmp/awr_reports';<br />utl_file.fopen('AWRDIR', ..)<br /><br />Then it works. Not sure if a DIRCTORY object is neccessary for utl_file to work in 10g. Based on your post, it seems it works just fine with path name ...<br /><br />Regards,<br /><br />Denisydshttps://www.blogger.com/profile/15884036034395020134noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-19908251484370923352010-01-05T12:01:24.997+01:002010-01-05T12:01:24.997+01:00Hi bb,
Which part is not clear? Please reply here...Hi bb,<br /><br />Which part is not clear? Please reply here or in private...<br /><br />Good wishes in 2010...<br /><br />Cheers!<br />DamirDamir Vadashttps://www.blogger.com/profile/15963017378937428976noreply@blogger.comtag:blogger.com,1999:blog-2807785752181271137.post-77291864511543394642010-01-05T11:23:32.862+01:002010-01-05T11:23:32.862+01:00Mate ... This is awesome ... Thanks for the post ....Mate ... This is awesome ... Thanks for the post ... But it would be great if you give bit more explanation and also bit detailed script...<br /><br /><br />CheersAnonymoushttps://www.blogger.com/profile/11201742723997834208noreply@blogger.com