PL/SQL: Statement Ignored PLS-00201: identifier 'DBMS_OUTPUT' must be declared

Recently I migrated a database(11gR1) from Windows Platform to 12C on Linux Platform. Migration was successful. During the sanity check, found lot of Invalid objects even after all the grants and privileges were provided. Ran utlrp.sql couple times, still the invalids count remains the same.

So I choose an Invalid object, compiled it manually and faced the below issue.

This was the issue for almost all the invalid objects. So I understood something is not right with the object DBMS_OUTPUT. So I checked this object and to my surprise it was valid.

Since the status is Valid, before doing any change to the object 'DBMS_OUTPUT', I decided to check the coding of the invalid object, since the DBMS_OUTPUT is an oracle standard one.

If I comment the line "dbms_output.put_line" from the invalid object, it is getting compiled and then it is a valid one but this is not the solution. This can be accepted as an workaround when a minimal no.of objects becomes invalid due to this issue. In my case, the invalids count is 226 and it's a humungous task to comment this line in all the coding of those objects. So something must be fishy with the DBMS_OUTPUT object.

Since I know DBMS_OUTPUT is oracle's standard object, I decided to read some oracle documents to get a solution for this. Luckily I got what I looked for.

I ran the script 'dbmsotpt.sql' as SYS user and bingo!!!

Compiled an invalid object manually, and it got compile without any issues.

I ran utlrp.sql to compile all the objects, post which the count got reduced form 226 to 8.

Finally all my issues are resolved.

Ref: https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS67301

****************************நன்றி****************************

SEND_MAIL - ORA-01775: looping chain of synonyms

 In one of our environment, post migration we tested the SEND_MAIL procedure to initiate the mail via SMTP server from DB level and faced the below issue.

ORA-01775: looping chain of synonyms

Looping chain of synonyms means, the created synonyms points to another object in a circle.

Here for better understanding,

1. SEND_MAIL is the procedure used to trigger mails from DB Server
2. UTL_SMTP and DBMS_OUTPUT are the dependent objects for SEND_MAIL procedure
3. UTL_SMTP synonym is also dependent object for SEND_MAIL procedure

So it is clearly evident that the dependent objects should be valid before fixing the issues with the SEND_MAIL procedure.

So, I started the drilldown on the required dependent objects and found the procedure 'SEND_MAIL' and package body of the object 'UTL_SMTP' is invalid.

So I made an attempt to compile the UTL_SMTP body to make it a valid object and faced the below issue.

This issue indicates the format of the wrapped unit being compiled is not understood by the compiler. This may be because the unit was edited or modified after it was wrapped. In our case this is what exactly I did. I copied this packages from some other database and compiled it in this database which leads to this issue. So now the ideal solution would be create this package from this database itself. Also since this UTL_SMTP is a standard package it should remain under SYS schema's ownership. In my case it was under the ownership of custom user 'TFGADMIN'.

So I dropped the package and created it separately in this database as follows.

After I dropped, checked and found only the synonym is available and this is the required result.

So to create the package, run the below scripts as SYSDBA.

After I ran the scripts, found the packages created under the SYS ownership and the status is Valid.

Now the next step is to compile the SEND_MAIL procedure as it was invalid since the dependent objects were Invalid. Now the dependencies are valid, so I compiled the procedure and it becomes a valid object.

Post this, run the below given ACL to complete the mail sending process. 

grant execute on utl_http to TFGADMIN;

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ACL_TEST3.xml', description => 'ACL FOR TEST PURPOSES', principal => 'TFGADMIN' ,  is_grant => true,

privilege => 'connect', start_date => null,end_date => null );COMMIT; END;

SELECT DECODE( DBMS_NETWORK_ACL_ADMIN.check_privilege('ACL_TEST5.xml', 'TFGADMIN', 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege FROM dual;

BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ACL_TEST5.xml', principal => 'TFGADMIN', is_grant => true, privilege => 'connect', position => 1);COMMIT;END;

BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'ACL_TEST5.xml', host => '172.17.104.72', lower_port=> null, upper_port => null);COMMIT;END;

Once the ACL is created, I ran the SEND_MAIL procedure and the mail is triggered and the same is received in my mailbox.

****************************நன்றி****************************

ORA-65040: Operation not allowed from within a pluggable database

 I need to drop some package and synonyms from a PDB. When I attempted to do, I faced the below issue.

Quick surf on the metalink leads to the solution.

Warning: Object owned by SYS user should not be dropped without understanding its impact and proper backup.

When you created the synonym with the schema name also appended with the object name, then it can be dropped like below.

drop public synonym "TFGADMIN.DBMS_OUTPUT";

Request URI:/OA_HTML/AppsLocalLogin.jsp java.lang.NoClassDefFoundError

I'm using 11i(11.5.10.2) EBS in our environment. This is maintained for historical data and not in regular use. So the routine health check or any other formalities are not followed for this instance as we would do for any other normal EBS Instance.

Often I'll receive mails from users stating 'Cannot access 11i Instance'. On checking mostly the error will be

There are two ways to solve this issue.

Method 1:

Stop the apache, clear the cache and start the apache server as follows.

Once the apache is started, clear the browser cache and try to login. In most cases this workaround will solve the issue. If still you are facing issue even after this workaround, then follow the below given method 2.

Method 2:

Stop the application services, clear the cache, compile all the jsps, start the application services and try again.

Once the jsp compilation is finished, check any application related services are running. If so kill them and start the application services. 

Note: Here I killed all the specific user (applmgr --> My application OS user) related process, since it is not regular use and no business is running on this.

Now start the application services. Clear the browser cache and try to login. This time you can login.

****************************நன்றி****************************

ORA-20100: ORA-20100: tmp creation failed

 After we cloned our TEST instance, some of the reports(seeded and custom) completed with error. On checking the log file found the below error

After a short search found some metalink documents which shed light on this issue. The reason is UTL_FILE_DIR and the environment variable $APPLPTMP are pointing to the same directory but that mount is already used by the grid software in my environment.

Insight on the Issue

1. The EBS environment variable $APPLPTMP is set to temporary directory path on the 'Database' node.
2. The $APPLPTMP path is needed only on the Database node and not required on the Application node
3. The $APPLPTMP path needs to be the first path in the UTL_FILE_DIR database parameter.
4. If multiple databases runs on the same host, then use a unique value for this temporary directory(i.e. All DB should not use the same directory and also the directory should not be owned by a different user. Normally the owner for this directory will be root.)
5. If there are more than one database(i.e. RAC) then the path must be a shared point
6.  The environment variable $APPLPTMP / UTL_FILE_DIR database parameter is used to process concurrent requests that use
1. Database tools
2. Programs of the execution method: PL/SQL, Stored Procedure, SQL*Plus, SQL*Loader, etc.
7. Therefore this directory must have sufficient disk space and privileges to allow reports to be created and allow the applmgr(APPS OS User) to move the output to $APPLCSF/$APPLOUT
8. The sysadmin concurrent report "Generate concurrent processing environment information" can be used to check $APPLPTMP. 
1. No parameter is required to run this program. 
2. This program is safe to run as it does not change or create anything on the instance.
3. It only reports a listing of environment variables visible to the concurrent managers.

 Below are the steps, I followed to overcome this issue.

1. Take backup of pfile, spfile, context file on the DB Tier
2. Take backup of context file on the APPS Tier
3. Check the existing entries from pfile and context file on DB and APPS Tiers
4. Run the report "Generate concurrent processing environment information" to account the environment variables
5. Create your temporary folder at the desired location with appropriate ownership and permissions
6. Change the entries to your desired location on pfile and context file on DB and APPS Tiers
7. Run auto-config on both DB and APPS tiers
8. Check the entries from pfile and context file on DB and APPS Tiers
9. Re-test the issue.

Take backup of pfile, spfile, context file on the DB Tier

Take backup of context file on the APPS Tier

Check the existing entries from pfile and context file on DB and APPS Tiers

Run the report "Generate concurrent processing environment information" to account the environment variables

Create your temporary folder at the desired location with appropriate ownership and permissions

Change the entries to your desired location on pfile and context file on DB and APPS Tiers

Run auto-config on both DB and APPS tiers

Now shutdown the DB, startup with the modified pfile, check the entries for to be proper and run autoconfig on the DB Node.

Check the entries from pfile and context file on DB and APPS Tiers

Now Re-test the issue. This time the concurrent program(OAM Applications Dashboard Collection) has completed successfully without any issues.

Reference:

12.2 E-Business Suite Concurrent Processing Reports Fails With ORA-06512: at "APPS.FND_FILE" and "ORA-20100: ORA-20100: File O0047353.tmp Creation Failed" Errors (Doc ID 2329514.1)

How To Set APPLPTMP and UTL_FILE_DIR (Doc ID 2141438.1)

Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)

How To Change Temporary Directories in Apps? (Doc ID 469377.1)

****************************நன்றி****************************