Category: Miscellaneous

  • Validate Database Links – Script

    Here is a PL/SQL script to validate the database links accessible to an user.  I wrote this to identify the working links after a database refresh or to verify links that connect from non-prod to prod…

    Hope it helps…

     

    REM Validate Database Links
    REM Private links under connected user and Public links
    REM
    REM Biju Thomas - 29-Oct-2013
    REM
    set serveroutput on size 99999
    set pages 0 lines 300 trims on
    col spoolfile new_value spoolfname
    select '/tmp/checklinks_'||user ||'_'||
           substr(global_name, 1, instr(global_name,'.')-1)||
           '.txt' spoolfile from global_name;
    spool '&spoolfname'
    declare
     --
     -- Get list of links the user has access to
     cursor mylinks is select db_link, owner, created, host, username
                       from all_db_links;
     --
     -- Identify other links in the DB for information
     cursor otherlinks is select db_link, owner
                          from dba_db_links
                          minus
                          select db_link, owner
                          from all_db_links;
     dbname varchar2 (200);
     currentuser varchar2 (30);
     linkno number := 0;
    begin
    
     -- Current database and connected user
     select name, user into dbname, currentuser from v$database;
     dbms_output.put_line('Verifying Database Links '||currentuser||'@'||dbname);
     dbms_output.put_line('========================================================');
     --
     for linkcur in mylinks loop
      linkno := linkno + 1;
      dbms_output.put_line('Checking Link: ' || linkno) ;
      dbms_output.put_line('Link Name    : ' || linkcur.db_link) ;
      dbms_output.put_line('Link Owner   : ' || linkcur.owner) ;
      dbms_output.put_line('Connect User : ' || linkcur.username) ;
      dbms_output.put_line('Connect To   : ' || linkcur.host) ;
      begin
        --
        -- Connect to the link to validate, get global name of destination database
        execute immediate 'select global_name from global_name@"'||linkcur.db_link||'"' into dbname;
        dbms_output.put_line('$$$$ DB LINK SUCCESSFULLY connected to '||dbname);
        --
        -- end the transaction and explicitly close the db link
        commit;
        execute immediate 'alter session close database link "'||linkcur.db_link||'"';
      exception
        --
        -- DB Link connection failed, show error message
        when others then
        dbms_output.put_line('@@@@ DB LINK FAILED  @@@@');
        dbms_output.put_line('Error: '||sqlerrm);
      end;
      dbms_output.put_line('---------------------------------------');
      dbms_output.put_line(' ');
     end loop;
     dbms_output.put_line('Tests Completed.');
     --
     -- List other Links in the DB
     dbms_output.put_line('Other Private Links in the Database');
     dbms_output.put_line('Connect as respective owner to validate these.');
     dbms_output.put_line('----------------------------------------------');
     for olinks in otherlinks loop
       dbms_output.put_line(olinks.owner ||' :: '||olinks.db_link);
     end loop;
    end;
    /
    
    spool
    spool off
    set pages 99 lines 80 trims off

     

    Sample Output:

    Verifying Database Links APPS@TST
    ========================================================
    Checking Link: 1
    Link Name    : CL.FE.NET
    Link Owner   : PUBLIC
    Connect User : M_S_DBLINK
    Connect To   : prod
    @@@@ DB LINK FAILED  @@@@
    Error: ORA-28000: the account is locked
    ORA-02063: preceding line from CL
    ---------------------------------------
    Checking Link: 2
    
    ...
    ...
    ...
    
    ---------------------------------------
    Checking Link: 28
    Link Name    : EC1.E.NET
    Link Owner   : APPS
    Connect User : QUERY
    Connect To   : (DESCRIPTION=(FAILOVER=yes)(LOAD_BALANCE=yes)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=B902A-VIP.e.net)(PORT=1532))(ADDRESS=(PROTOCOL=TCP)(HOST=B902B-VIP.e.net)(PORT=1532)))(CONNECT_DATA=(SERVICE_NAME=PROD)))
    $$$$ DB LINK SUCCESSFULLY connected to PROD
    ---------------------------------------
    Tests Completed.
    Other Private Links in the Database
    Connect as respective owner to validate these.
    ----------------------------------------------
    SYSTEM :: CPROD.FE.NET
    FQUERY :: GPROD2.FE.NET
    FQUERY :: GASPRD.FE.NET
    FQUERY :: PPROD.FE.NET
    XXG :: PPROD.FE.NET
    FQUERY :: SPROD.FE.NET
    
    PL/SQL procedure successfully completed.
    
    currently spooling to /tmp/checklinks_APPS_TST.txt

     

  • OPatch Options!

    A friend of mine was working to apply the July 2013 PSU, and had to rollback two one off patch conflicts. Since the instance was cloned from production, OPatch was failing with this error…

    $ $ORACLE_HOME/OPatch/opatch rollback -id 12834800
    
    The patch should be applied/rolled back in '-all_nodes' mode only.
    Converting the RAC mode to '-all_nodes' mode.
    Prerequisite check "CheckRollbackSid" failed.
    
    Patch ID: 12834800
    The details are:
    The specified online patch(es) are installed on the following Database Instances (SIDs) "xyzprod".
    The SIDs given for rollback are not the same. OPatch cannot continue.
    [ Error during Prerequisite for rollback Phase]. Detail: RollbackSession failed during prerequisite checks: Prerequisite check "CheckRollbackSid" failed.
    Log file location: /u01/app/oracle/xyzdev/11.2.0.3/cfgtoollogs/opatch/12834800_Oct_23_2013_16_37_13/rollback2013-10-23_16-37-13PM_1.log
    Recommended actions: Please look at log file to locate the prerequisite that failed. Invoke 'opatch apply -report' or 'opatch rollback -report'.
    OPatch failed with error code 30

    So, though we are trying to rollback the conflicting patch from dev instance, the inventory still has reference to prod instance, where the oracle home was cloned from… Adding the -connectString xyzdev:sys:passsword:  did not work either….

    I did not know until I reviewed the OPatch documentation, it has a lot of cool options in 11gR2… See documentation.

    Thanks to Oracle support, for identifying the right option to remove the patch from the inventory.

    $ORACLE_HOME/OPatch/opatch rollback -id 12834800 -no_sysmod -connectString xyzdev:sys:passsword: -local
    
    $ORACLE_HOME/OPatch/opatch rollback -id 13004894 -no_sysmod -connectString xyzdev:sys:passsword: -local

    Now, able to apply the PSU without issue…