label.username error- empty tablespace

[ebauchwitz]

We're experiencing an error in our Development Environment following a copy over from Production. From the screen shot attached we see "label.username". Our initial thought was that the data content was not loaded or failed to load successfully. Essentially, we felt that there was missing translation information within the database. Our DBA confirmed that all content was loaded correctly.

Secondly we questioned weather or not our servers had been restarted in the correct order. We checked with our DBA and found that the servers were bought back up in correct sequence. The error continued after we brought down the Application servers and restarted in order.

Our last attempt to determine the issue was reporting it with the vendor. We provided Oracle with error log. After reviewing their recommendation was to fix a Temporary Tablespace which was empty. Apparently this was causing all database queries to fail. The recommendation is to fix the error and restart the OTM instance. Does anyone know how to fix this type of error?

[nick.polites]

Re: label.username error- empty tablespace

Check your console.log files for both weblogic and tomcat and post them up here. There has got to be quite a few errors in both.

Did you also rerun the post migration scripts? Every time you copy the database over to a new instance you need to rerun all scripts from create_login_triggers.sql as mentioned in the admin guide.

[muthu.kumar]

Re: label.username error- empty tablespace

Hello,

We're getting similar errors too. Log files show the following messages :

INFO | 2013/01/07 08:58:31 | java.sql.SQLException: ORA-04068: existing state of packages has been discarded
INFO | 2013/01/07 08:58:31 | ORA-04065: not executed, altered or dropped stored procedure "GLOGOWNER.VPD"
INFO | 2013/01/07 08:58:31 | ORA-06508: PL/SQL: could not find program unit being called: "GLOGOWNER.VPD"
INFO | 2013/01/07 08:58:31 | ORA-06512: at line 1

I checked and confirmed that the VPD package is valid. We re-compiled the invalids 2 days back and bounced the services. Looks like it didn't help and we got the same error again today.

Our temp tablespace is not running low on space.

Any help would be highly appreciated.

Thanks
Muthu

[nick.polites]

Re: label.username error- empty tablespace

What do you have your temp tablespace set to? We have it set to 32GB. Anything that requires I would say over 10GB is a very poor query and needs to be tuned.

For the VPD error, try running the following commands as glogowner from the /opt/otm/glog/oracle/script8 directory.

1. @create_vpd_package.sql
2. @glogowner_grants.sql
3. @create_public_synonyms.sql
4. @create_logon_triggers
5. @recompile_invalid_objects.sql (be sure this returns 0 after the recompile)

Be sure that OTM is down when you run these, then restart. The will re-validate everything in glogowner is fine.

[muthu.kumar]

Re: label.username error- empty tablespace

Thanks Nick, for the quick reply.

// What do you have your temp tablespace set to? We have it set to 32GB. Anything that requires I would say over 10GB is a very poor query and needs to be tuned.
//
Ours is set to 32 Gb as well.

// For the VPD error, try running the following commands as glogowner from the /opt/otm/glog/oracle/script8 directory.

1. @create_vpd_package.sql
2. @glogowner_grants.sql
3. @create_public_synonyms.sql
4. @create_logon_triggers
5. @recompile_invalid_objects.sql (be sure this returns 0 after the recompile)

Be sure that OTM is down when you run these, then restart. The will re-validate everything in glogowner is fine.[/QUOTE]
//

Thanks. All the above scripts will only recreate the objects, correct ? I'm only concerned that this step shouldn't create additional problems.

Thanks again for the quick turnaround.

Regards
Muthu

[nick.polites]

Re: label.username error- empty tablespace

The won't cause any problems unless someone in your origination has modified the out of box scripts or altered any our of box procedures written for OTM. We run these after each clone just to be sure everything is re-validated.

[muthu.kumar]

Re: label.username error- empty tablespace

Thanks again Nick. Ours is kind of a small environment and no one changes any of the out of box scripts. So, we're good. Just one last question. Should I follow the same order while running those 5 scripts ?

Thanks
Muthu

[nick.polites]

Re: label.username error- empty tablespace

Yep that is how we run them.

Be sure you don't have any errors with step 5. If you do get anything post it here and I can try to help.

Nick

[muthu.kumar]

Re: label.username error- empty tablespace

Thanks so much again Nick. I ran those scripts and here's what had happened :

1. recompile_invalid_objects.sql has the following error :

Error enabling constraint SYS_C0010190 for table LOCATION_REMARK
ORA-02293: cannot validate (GLOGOWNER.SYS_C0010190) - check constraint violated
ORA-24344: success with compilation error

This is due to the data error. The above is a check constraint on the LOCATION_REMARK table for REMARK_TEXT column to be not null, whereas we've 8 rows with REMARK_TEXT = NULL. Hopefully we can ignore this.

2. Package SHIPMENT_STOP_RPT_V1 has the following error :

Errors for PACKAGE BODY SHIPMENT_STOP_RPT_V1:
17/5 PLS-00904: insufficient privilege to access object PUBLIC.REPORTS_LIBRARY
27/5 PLS-00904: insufficient privilege to access object PUBLIC.REPORTS_LIBRARY
29/17 PLS-00904: insufficient privilege to access object PUBLIC.REPORTS_LIBRARY
45/62 PLS-00904: insufficient privilege to access object PUBLIC.REPORTS_LIBRARY
51/54 PLS-00904: insufficient privilege to access object PUBLIC.REPORTS_LIBRARY

Not sure, if grant all on REPORTS_LIBRARY to glogowner will fix this.

Apart from this, here's the list of invalid objects count schemawise :

OWNER OBJECT_TYPE COUNT(*)
--------------- -------------------- ----------
BOQUERY FUNCTION 1
BOQUERY SYNONYM 2
BOQUERY TRIGGER 1
FSI_INT TRIGGER 1
GLOGOWNER PACKAGE BODY 1
GLOGOWNER PROCEDURE 1
LOS_INT TRIGGER 1
PREPRINT_INT TRIGGER 1
PUBLIC SYNONYM 1
REPORTOWNER TRIGGER 15
WORLDSHIP TRIGGER 1

Thanks
Muthu

[muthu.kumar]

Re: label.username error- empty tablespace

Another update. I ran utlrp.sql and now here's the invalid objects count :

OWNER OBJECT_TYPE COUNT(*)
--------------- -------------------- ----------
BOQUERY FUNCTION 1
BOQUERY SYNONYM 2
GLOGOWNER PACKAGE BODY 1
GLOGOWNER PROCEDURE 1
PUBLIC SYNONYM 1

Still GLOGOWNER.SHIPMENT_STOP_RPT_V1 package body has the error "insufficient privilege to access REPORTS_LIBRARY" I cross checked with production and it remains the same there too.

The only change in DEV is that, we recently applied the 6.2.8 patchset and did a DB refresh from production.

Thanks
Muthu

[nick.polites]

Re: label.username error- empty tablespace

I don't have a 6.2.8 environment but I don't have the SHIPMENT_STOP_RPT_V1 package in reportowner or glogowner. This may be something custom.

You may also want to try to run the following as reportowner to see if it helps. Again these won't cause any issues unless they have been modified.

As reportowner from /otm/glog/oracle/script8

1. @reportowner_grants.sql
2. @create_public_synonyms.sql
3. @recompile_invalid_objects.sql (be sure this returns 0 after the recompile)
4. exit

Are you still seeing that ORA-04068: existing state of packages has been discarded error?

[muthu.kumar]

Re: label.username error- empty tablespace

Nick,

Thanks again. No, I'm not getting the ORA-04068 errors anymore and just to test, I started the apps services and it got started without any issues and I got the login page properly. But I'm getting a different error this time when I tried logging in as DBA.ADMIN.

In the front-end, I'm getting the error "A connection to the server could not be established: null (user=DBA.ADMIN)"

Here's the excerpt from the tomcat console.log :

Error Exception java.rmi.AccessException: [EJB:010160]Security Violation: User: 'DBA.ADMIN' has insufficient permission to access EJB: type=<ejb>, application=GC3App, module=UserAccessSession.jar, ejb=UserAccessSessionHome, method=getUserAccess, methodInterface=Remote, signature={java.lang.String,java.lang.String,java. lang.String,java.lang.String}.

Hope I need to open a new thread for this error.

Thanks
Muthu

[nick.polites]

Re: label.username error- empty tablespace

You'll probably need to open an SR with Oracle on this. Did you upgrade your system recently or is this a new install? Check your import logs for any errors. Something in the DB is not setup correctly.

Nick

[nick.polites]

Re: label.username error- empty tablespace

Try also this:

Shut down Apache/Tomcat

rm -r /otm/tomcat/work/*

Then restart Tomcat and Apache. If this fixes your issue you will need add this to your OTM startup script. I have seen this directory get corrupted sometimes and I now have this in all of our OTM restarts.

Nick

[muthu.kumar]

Re: label.username error- empty tablespace

Thanks Nick again. No, this is not a fresh install. We originally upgraded from 5.5. I recently applied the 6.2.8 patch set and 2 weeks back, did a DB refresh of the DEV instance from production (which is still in 6.2 and doesn't have the 6.2.8 patch set installed yet)

Fortunately, your suggestion of running the 5 SQLs worked well and the developer could log on successfully.

I think the problem I've currently is only with respect to the DBA.ADMIN user and another user in ADMIN domain. I'll work with Oracle Support and see. Before that, I'll try as a last option of cleaning up the tomcat work directory and see.

THANKS A LOT AND I REALLY APPRECIATE IT.

Regards
Muthu