Archive for July, 2015

Oracle dbms_workload_repository awr_global_report_text fails with ORA-06553 PLS-307

My last post was about generating AWR reports from SQL – not a very complicated case. “But” – little word, big meaning. And suddenly we get a strange error message.

In my example, I was especially interested in a Real Application Cluster’s “global” report. The subprogram call for that purpose is dbms_workload_repository.awr_global_report_text(), accepting the DBID, begin snapshot, end snapshot, an option field, and, l_inst_num for the instance number. Regarding the latter, Oracle documentation says: “List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available …”

But when I did that, I got an error that felt strange to me:

ORA-06553: PLS-307: too many declarations of ‘AWR_GLOBAL_REPORT_TEXT’ match this call

Read more…


By Martin Klier in Cluster and RAC,Oracle  .::. Read Comments (2)

How to create an Oracle AWR report with SQL and PL/SQL

AWR is a great tool (but needs diagnostics pack). It can create very useful reports for performace analysis over a given period. Most people will pull the AWR Report  from the OS level, with ‘@?/rdbms/admin/awrrpt.sql” (or one of the other scripts there). But sometime it’s not possible to access a database server’s OS level, or gain permission to do so. So we also can create the whole bunch of possible AWR and ASH reports from SQL level, too. We can have it in text or HTML, it’s just a matter of personal taste. I always preferred the text version, but there are many facts pro HTML. Anyway, here is my cheat sheet how to do it, and the difference in RAC.

What we have to know

Our database ID (DBID):

select dbid 
  from v$database;

The period we are interested in, spoken in snapshot IDs:

select /*+ FIRST_ROWS */ * 
  from DBA_HIST_SNAPSHOT
  order by snap_id desc, instance_number desc;

How to create an Oracle AWR report with SQL and PL/SQL

SELECT OUTPUT
FROM TABLE (dbms_workload_repository.awr_report_text(
 l_dbid=>123456789,
 l_inst_num=>1,
 l_bid=>24142,
 l_eid=>24143
 )
);

Read more…


By Martin Klier in Cluster and RAC,Oracle  .::. Read Comment (1)

Oracle 12c RMAN delete obsolete behaves interactively in DBMS_SCHEDULER job type BACKUP_SCRIPT

Situation

In Oracle Database 12c, we have the long-missed feature of DBMS_SCHEDULER job type “BACKUP_SCRIPT”, that allows us to create backup jobs without creating them as an OS file, and without need for a wrapper script that’s called by a job type EXECUTABLE (as we usually did that in the last years).

Sometimes we also have to use the RMAN command “delete obsolete” to get rid of unneeded backup pieces. When executed interactively, it comes back with the question “Do you really want to delete the above objects (enter YES or NO)?”. But when running from a script, it just deletes:

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           67     30-JUN-15 
Backup Piece         67     30-JUN-15          +ORAFRA/...nnndf...
deleted backup piece
backup piece handle=+ORAFRA/...nnndf0_tag...
Deleted 1 object
Recovery Manager complete.

Problem

Strange is, that when using it within a DBMS_SCHEDULER job type BACKUP_SCRIPT, “delete obsolete” thinks it was running interactively, and the scheduler seems to compensate it, by simply answering “NO” – what leaves us with a growing number of old backup pieces!
(Taken from v$rman_output):

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           61     30-JUN-15
Backup Piece         61     30-JUN-15          +ORAFRA/.../nnndf0_tag....
RMAN-06546 Error occurred getting response - assuming NO response
 Read more...

By Martin Klier in Oracle  .::. (Add your comment)


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.