oacore not starting up troubleshooting tips

Hi DBA-mates,

Welcome back you all again. I would like to share one more real time environments experience which we faced recently in our environment.

Today, I am not telling about any ora-error or any particular things, just this is troubleshooting and sharing the real time experience.

I am sharing this because this may helpful in your interview also.

Scenario:

oacore is not starting or coming up after bounce the services.

We applied a patch and after that we started the services but oacore was not starting, even we tried the services one by one it was not starting.

We checked the logs but there was nothing in services start log. Then we checked for oacore log which is not easy to find out for any fresher or the people who did not worked in r12.

We checked in Google then we found about to persistence directory in which we have to rename or remove some files. We tried but this also did not work.

Like:

rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*

rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*

rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

Then again we reverted back the files at same places.

Then we checked others some log which we got from below command. Easy step to find out the log:

$echo $LOG_HOME

Then you can follow the path and error log.

After that we found out that there was issue with server.xml file and there was only a space which causes the issue and we were not able to start the services properly.

Then we remove the space and junk character from the file and saved it. And again we started the services which got up all the services without any issue.

Hope this may useful and helpful. Please let us know for any concerns or issue either in comment box or contact us @ora-data.blogspot.com.

Some more useful links:

ORA-12091 Cannot online redefine table with MV Fix.

CAREER PLANNING TIPS.

Differences between the R12.1 and R12.2.

How To Compile JSP Manually in R12.

How to restart a failed patch in r12.

 

Regards,

ora-data Team.

ora-01578: oracle data block corrupted Fix and Solution

Hi DBA-Mates,

We faced this below issue which is very critical and tricky also.

After cloning issue:

Self service url was not working https://ora-data.blogspot.com/OA_HTML/AppsLocalLogin.jsp

The only visible message was:

"You have encountered an unexpected error. Please contact the System Administrator for assistance."

Work-around:

1. Then we changed user’s password but still not working.

2. Checked db alert log, but didn’t get any no clue.

3. Checked apache error log, access log and jserv log but nothing found.

Error:

The error message is below. : file # 368, block # 29907

------------------------------------------------------------------------------------------------------------------------------------

Exception:oracle.apps.fnd.cache.CacheException at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:339) at oracle.apps.fnd.functionSecurity.User.getUser(User.java:447) at oracle.apps.fnd.functionSecurity.FunctionSecurity.getUser(FunctionSecurity.java:638) at oracle.apps.fnd.functionSecurity.FunctionSecurity.init(FunctionSecurity.java:2731) at oracle.apps.fnd.functionSecurity.FunctionSecurity.(FunctionSecurity.java:299) at oracle.apps.fnd.sso.SSOUtil.getStartPageUrl(SSOUtil.java:249) at oracle.apps.fnd.sso.SessionMgr.createAppsSession(SessionMgr.java:2808) at _oa__html._fndvald._jspService(_fndvald.java:222) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:220) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:496) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:360) at oracle.jsp.JspServlet.internalService(JspServlet.java:289) at oracle.jsp.JspServlet.service(JspServlet.java:259) at javax.servlet.http.HttpServlet.service(HttpServlet.java:699) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:567) at org.apache.jserv.JServConnection.run(JServConnection.java:205) at java.lang.Thread.run(Thread.java:645) Caused by: oracle.apps.jtf.base.resources.FrameworkException: Failure in CacheLoader: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:933) at oracle.apps.jtf.cache.CacheManager.getInternal(CacheManager.java:5805) at oracle.apps.jtf.cache.CacheManager.get(CacheManager.java:5728) at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:327) ... 16 more Caused by: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:343) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:200) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:285) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:250) at oracle.apps.jtf.cache.GenericCacheLoader.load(GenericCacheLoader.java:98) at oracle.ias.cache.CacheLoader.callLoad(Unknown Source) at oracle.ias.cache.CacheHandle.findObject(Unknown Source) at oracle.ias.cache.CacheHandle.locateObject(Unknown Source) at oracle.ias.cache.CacheAccess.get(Unknown Source) at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:874) ... 19 more Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 368, block # 29907) ORA-01110: data file 368: '/ora-data/sku/data2/applsysd09.dbf' ORA-26040: Data block was loaded using the NOLOGGING option   at oracle.apps.jtf.base.resources.FrameworkException.convertException(FrameworkException.java:718) at oracle.apps.jtf.base.resources.FrameworkException.addException(FrameworkException.java:696) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:77) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:99) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:313) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:329) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:350) ... 29 more

Then checked for WF related tables

The below error comes if we query the below tables

SQL> select count(1) from WF_LOCAL_USER_ROLES;

select count(1) from WF_LOCAL_USER_ROLES

                    *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 151, block # 82209)

ORA-01110: data file 2197: '/oradata/sku/data20/applsysd47.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Solution and Work-around:

Check details of the block which table it belongs.

select SEGMENT_NAME,SEGMENT_TYPE,BLOCK_ID,OWNER,TABLESPACE_NAME from dba_extents where BLOCK_ID like '29907' and BLOCK_ID like '82209';

select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,LOGGING,NUM_ROWS,LAST_ANALYZED from dba_tab_partitions where TABLE_NAME like 'WF_LOCAL_USER_ROLES';

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where SEGMENT_NAME like 'WF_LOCAL_USER_ROLES';

Run the dbv utility from OS prompt

$ dbv blocksize=8192  file=/oradata/sku/data2/applsysd09.dbf feedback=100

$ dbv blocksize=8192  file=/oradata/sku/data20/applsysd47.dbf feedback=100

Partition of the table WF_LOCAL_USER_ROLES will have problem. This Table partion will be having "LOGGING" column value "NO" which should be actually "YES".

Change the same.

SQL> ALTER TABLE WF_LOCAL_USER_ROLES MODIFY PARTITION <partition_name> LOGGING;

Table altered.

Create the necessary auxiliary tables for identifying corrupt blocks in the table in question:

SQL> exec DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');

PL/SQL procedure successfully completed.

SQL> desc repair_table

SQL> var c number
 

SQL> exec dbms_repair.check_object(schema_name=>'APPLSYS',
object_name=>'WF_LOCAL_USER_ROLES',
corrupt_count=>:c);

PL/SQL procedure successfully completed.

SQL> select :c from dual;

:C
----------
1934

SQL> select distinct SCHEMA_NAME, OBJECT_NAME, CORRUPT_TYPE, PARTITION_NAME, repair_DESCRIPTION, MARKED_CORRUPT from repair_table;

SQL> exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS (SCHEMA_NAME => 'APPLSYS', OBJECT_NAME=> 'WF_LOCAL_USER_ROLES', OBJECT_TYPE => dbms_repair.table_object, REPAIR_TABLE_NAME => 'REPAIR_TABLE', FIX_COUNT=> :c);

PL/SQL procedure successfully completed.

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( SCHEMA_NAME => 'APPLSYS', OBJECT_NAME => 'WF_LOCAL_USER_ROLES', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag);

PL/SQL procedure successfully completed.

SQL> select count(1) from applsys.WF_LOCAL_USER_ROLES;

Check front end login for the affected users.

Some more useful link:

12c Database Installation Steps

Oracle Data Guard Concepts

RAC Concepts and Details

Jobs Tips

Regards,

ORA-12091 Cannot online redefine table with materialized views error Solution

Hi DBA-mates,

We are back once more with some issue and solutions step by step with some explanations.

Recently when we attempt to re-organize a table ONLINE that table contains materialized views. We used the DBMS_REDEFINITION.START_REDEF_TABLE procedure to do this, and we got an ORA-12091 "cannot online redefine table "ora-data"."SKU" with materialized views error.

Here,

ora-data = user name

SKU = table name

‘Column mapping i.e. source table and reorg table’ 

For example:

SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');

   BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',...........);

   *

   ERROR at line 1:

   ORA-12091: cannot online redefine table "ora-data"."SKU" with materialized views

   ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

   ORA-06512: at "SYS.DBMS_REDEFINITION", line 146

   ORA-06512: at line 1

If you are also facing the same issue in your environments, you can check and do workaround which we have followed in our real time environments.

First we need to remove the snapshot log and materialized view which was created during failed execution of the command BMS_REDEFINITION.START_REDEF_TABLE procedure, which is mentioned above error.

Now, Check for log details as shown below commands:

SQL> select log_table from user_snapshot_logs;

   LOG_TABLE                     

   -------------------------

   MLOG$_SKU  

Now, we will drop the Materialized view log from below querry.

SQL> drop snapshot log on SKU;  

   Materialized view log dropped.

Now, we are checking for Interim Table

SQL> select TABLE_NAME , status from user_snapshots;

TABLE_NAME                     STATUS

   ------------------------------ -------

   INT_SKU                        INVALID

SQL> select mview_name, compile_state from user_mviews;

   MVIEW_NAME                     COMPILE_STATE

   ------------------------------ -------------------

   INT_SKU                         ERROR

Here, we need to drop this also, because some data may stored from failed above command.

So, please drop the interim table also as shown below:

SQL> drop materialized view int_sku;

   Materialized view dropped.

Now, we are trying again:

SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');

PL/SQL procedure successfully completed.

Hope this may useful and helpful. Please let us know for any concerns or suggestions either in comment box or contact us @ora-data.blogspot.com.

Some more useful links:

Oracle Apps DBA Realtime Interview Questions

Oracle DBA Troubleshooting Tips
How to Create Custom Schema in r12.2
Oracle DataGuard Concept and Setup
RAC Concepts and Details in Oracle

Regards,

ora-data Team.