Wednesday, February 15, 2012

Show compilation errors

Another simple solution that I manage to forget every time I need it.

Invalid objects in the database and unable to see the errors associated with a compile.

SQL> alter procedure scott.get_addy compile;

Warning: Procedure altered with compilation errors.
SQL> sho err
No errors.

This occurs anytime I try to compile objects in ANOTHER schema outside of the one I am logged in as.  If I am troubleshooting invalid objects across all schemas, I am likely logged in as SYSTEM or some other generic administrative user and do not want to log into individual schemas to do the compiles.

The "solution" or rather the how-to is simple:

SQL> sho err procedure scott.get_addy
Errors for PROCEDURE scott.get_addy:

-------- -----------------------------------------------------------------
70/1     PL/SQL: Statement ignored
70/1     PLS-00905: object PUBLIC.SEND_EMAIL is invalid


Grid Control shows database Unmounted and status Up

Known issue.

I am working in Grid Control 11g ( but I am fairly certain the issue appeared for me in 10g as well.

The scenario is this...  After a clone/duplicate of a database, the new db is discovered and configured successfully but then shows a status of unmounted while the details say the instance is up.  I have confirmed through sqlplus that the instance is in fact accessible and open.

In Grid Control, you will see this:


Referring to Metalink note: 736047.1
Bug is: 6073721

The first solution is simple. It is documented as workaround B in the note listed above.  All you need to do is bounce the instance after the duplicate and that should correct the status in Grid Control.  Unfortunately, in my case, the database has already been released for user access and I do not have the option to bounce the instance now.

The fix that worked for me:

1. Click on "Last Known State"
2. Click on "Metric and Policy Settings" under Related Links
3. Scroll down to the "Unmounted" metric and change the Warning value to =1 instead of the default =0
4. Click Ok and refresh the database home page.

This should fix the problem.