Trying to execute a package on a remote db to get AWR details

yes, if you can get the dblink working you can execute remote procedures
procx@dblinky(arg1, arg2,...);
--  however you can't use clob arguments over a database link

i'm not a fan ...
but why are you trying to rebuild em ?
https://www.oracle.com/technetwork/oem/db-mgmt/index.html

and why in apex ?

Hi Geert

They want all the DBA's to look in one place for all the information as related to the databases like AWR, Long ops, DG Performance, DB Capacity , GRid Control / monotiroing alerts, db health check etc...

Hope this helps.

Thanks,
R

hi flow1,

Can you give me some sample code which i can try .

Thanks,
R

flow1,

it throws this error ORA-30626: function/procedure parameters of remote object types are not supported

for the function -


create or replace FUNCTION read_remote_AWR (p_link_name IN VARCHAR2)
    RETURN awr_tab
IS
    c_sql_template   CONSTANT VARCHAR2(1000) := '
          SELECT awr_obj(
                         output
                 ) obj
            FROM dbms_workload_repository.Awr_REPORT_html@~insert_db_link_here~(p_DBID IN INTEGER, p_INSTANCE_NUM IN INTEGER, P_BID IN INTEGER, P_EID IN INTEGER)';
    v_results                 awr_tab;
    v_dblink                  VARCHAR2(2000);

BEGIN
   apex_debug.enter(
        'read_remote_AWR',
        'p_link_name',
         p_link_name);
    v_dblink := p_link_name;
    if is_valid_db_link(v_dblink)  
    THEN
        EXECUTE IMMEDIATE REPLACE(c_sql_template, '~insert_db_link_here~', p_link_name)
            BULK COLLECT INTO v_results;
    ELSE
        DBMS_OUTPUT.put_line('Invalid Link Name used: ' || p_link_name);
        v_results := awr_tab();  
    END IF;
    apex_debug.info('read_remote_AWR exiting with ' || to_char(v_results.count,'fm99999') || ' rows returned');
    RETURN v_results;
END;

sql ---
SELECT   output
    FROM   TABLE(dbms_workload_repository.Awr_REPORT_html@****(12345678,1,123456,123457));


but function is compiled successfully, not sure if it is correct.

Thanks,
R

I agree with Geert:  Why are you trying to re-invent Oracle Enterprise Manager (OEM)?

Oracle has a much larger development budget than you likely do and OEM is free-ish.  I added "ish" because some of the features require licenses to use but you would need the same licenses to use the tools to write your own UI anyway.

"they want all DBA's to look in one place"
What if that 1 place is wrong ?

surely you allow the dba's to go in and "get there hands dirty to investigate"

you want to centralize ... i do that too, but not everything.
stuff like backups, of which the data doesn't have to be last minute
and the silly "uptime percentage", because management likes there numbers: 99.99 + random(day of the year)

for performance we have our own tool, which, unlike em, can go back for decades
(until we upgrade it and break it for some time)
we call it ITSS, It's The Sql, you Stupid
> meaning that for performance reasons you only need to look at the crap Sql's the dev's wrote
unless, the dba made  a real mess of the startup parameters, which is just 6 or 7 in most cases

a good dba will have a set of scripts, specifically designed for what they do, and will most likely be using sql*plus for that

seems you have the luxury of having multiple dba's
you can get rid of the dba's who swear they need foglight (toad) ...

I use 2 main scripts for troubleshooting: session_waits and blockers (short=sw.sql and bl.sql)

i never look at long_ops ... well, ... almost never

Geert,

What you are saying is totally correct, if the management wants it, I don't have say in it. (It's reinventing the wheel), I know.

Not sure how many of the DBA's are going to use all the features and functionality implemented in this application. But it's my job and I got to do it.  And their main focus is on AWR.

R

Again, why are you trying to replace OEM?  It already has everything you are asking for an so much more.

Management is not technical. It's number driven.
You need to translate the numbers to something technical for the dba's.

A sample of a number: current archive rate of the database (in Gb/hour)
This is meaningless for management, but a dba should know what this is.
A dba usually knows the archive rate of a database, and knows at a glance if it is excessive.
If you put the archive rate of each db on that centralized report, you might see management cheering when it starts going higher and higher.
And then you'll have to tell management, it's a bad thing ...

Every db will usually have a different rate.
If you put that rate on the report you'll get the management question: But what archive rate is the good one ?

Find out what management really wants.
> they just want to know if everything is fine and if there are no risks.

I suppose you do Disaster Recovery Test every so often ?

Hi slightwv,

I don't have an answer to that , sorry !! They want it , I have to do it.

I would just like to know if it is really possible to execute remote procedures in apex?

Thanks,R

>>I would just like to know if it is really possible to execute remote procedures in apex?

I believe flow01 showed how to make a remote procedure call.

>>I don't have an answer to that , sorry !! They want it , I have to do it.

Many times Management asks for the improbable and borderline impossible simply because they don't understand.  In my career I have had to convince management of monstrous tasks and return on investment.  I never understand the need to reinvent the wheel especially when a completely free and quite exhaustive tool already exists that does exactly what you are trying to do.  The best part is Oracle developed it.

In my opinion taking the time to learn OEM and create customized dashboards tailored to your business would be a much better use of your time.

Have you asked if they have considered OEM ad if so, why the decision was made to write your own?

Your job is to convince them their logic of wanting something developed in-house is error-prone and costly.  How long do you think it will take to develop this product vs installing OEM?  You can have OEM up and running and monitoring all your systems in about a day.  It does MUCH MORE than just databases.  It can also monitor your systems.  It is pretty much a one-stop-shop.

"How long do you think it will take to develop this product"
to give you an idea:
our tuning expert started his own in-house development tuning system in 2003 ... it's not finished yet.
that's only the tuning part

if you know how to use it, finding the bottleneck goes in seconds

i get lost in those AWR reports

Hi Geert /slightwv,

I totally agree with both of you. Sorry but I am not able to answer any of your questions.

Thanks,
R

Is management aware of OEM?  I'm sure that someone read an article in CIO magazine and said, we need that.  Don't care, we need it.  Let's build it.  They don't even know that it is available off the shelf.

What happens when you upgrade the database?  If anything in the underlying parts of Oracle changes, you have months of development to get it working after an upgrade.  If you use OEM, it would work as soon as you installed it.  No months of down time and redevelopment.

It took us months once to convince a CIO that a technology that he wanted didn't exist.  He didn't care.  He wanted it.  That was almost 20 years ago now.  I still think that the technology doesn't exist.  Just because management says they need something doesn't mean they are right.  It is the job of the lowly technology people to show them the way.  If they are asking for something that is technically impossible, what do you do?

Hi Johnsone,

Yes,  they are aware of the OEM.