Recover datafile (ORA-00376)

Errors in file /oracle/log/diag/rdbms/prod/trace/prod_smon_28281.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oracle/data/undotbs01.dbf'


SQL>  select * from v$recover_file
  2  /

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------------
         2 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
         3 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
        20 OFFLINE OFFLINE                                                                     45342425 31-AUG-11


SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id);
Enter value for give_file_id: 2
old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)
new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =2)

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSAUX                         ONLINE

SQL> /

Enter value for give_file_id: 3

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =3)

TABLESPACE_NAME                STATUS

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

UNDOTBS1                       ONLINE

SQL> /

Enter value for give_file_id: 20

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =20)

TABLESPACE_NAME                STATUS

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

SNPM                           ONLINE

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

     FILE#

----------

         2

         3

        20

SQL>  select status, enabled from v$datafile where file#=&give_file_id;

Enter value for give_file_id: 2

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=2

STATUS  ENABLED

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

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 3

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=3

STATUS  ENABLED

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

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 20

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=20

STATUS  ENABLED

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

RECOVER READ WRITE

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

 FROM V$DATAFILE_HEADER

 WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SQL>  alter database datafile '/oracle/data/snpm01.dbf' online;

Database altered.

SQL>  alter database datafile /oracle/data/undotbs01.dbf' online;

Database altered.

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

no rows selected

SQL> alter database open;

Terminate inactive database sessions from oracle database

Terminate all inactive sessions ..which are inactive for more than 3 hours & then kill the
    processes from O/S
   
    Run the below sql's and spool the out put
   
    select 'alter system kill session '||''''||a.sid|| ','||a.serial#||''''||';'
      from v$session a, v$process b where
      a.paddr =b.addr
      and a.last_call_et > 3600*3
      and status='INACTIVE'
    order by last_call_et
    /
   
    select 'kill -9 '||b.spid
      from v$session a, v$process b where
      a.paddr =b.addr
      and a.last_call_et > 3600*3
      and status='INACTIVE'
    order by last_call_et
    /
   
 use the first spool file..for db sessions kill..and run that 3-4 times
   
Then use the 2nd spool file..to terminate the processes at os level
   

Oracle datafile with junk character

I faced some issues where the datafile has a junk character.(Maybe some copy pasted the datafile creation command with spaces of special characters).This causes the hot backup on database to fail. This is how i fixed the issue.

Check the datafile,


SQL> select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='REPORTS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS    ONLINE_
------------------------------ --------- -------
/sid/data/reports.dbf
REPORTS                 AVAILABLE ONLINE


$ ls -lb /sid/data/reports.dbf*
-rw-r-----  1 orora dbora 524320768 Apr  2 00:39 /sid/data/reports.dbf\

Now to fix  the issue
---------------------------
alter database datafile '/sid/data/reports.dbf ' offline; -->Note the space after dbf



mv '/sid/data/reports.dbf  ' /sid/data/reports.dbf   -->Note the space

 alter database  rename file '/sid/data/reports.dbf  ' to '/sid/data/reports.dbf ';

 recover datafile '/sid/data/reports.dbf ';

 alter database datafile '/sid/data/reports.dbf' online;


SQL> select TABLESPACE_NAME,STATUS,ONLINE_STATUS,FILE_NAME from dba_data_files where FILE_NAME = /sid/data/reports.dbf';

TABLESPACE_NAME                STATUS                         ONLINE_STATUS                  FILE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
OBITHD_REPORTS                 AVAILABLE                      ONLINE                         /sid/data/reports.dbf
                                                                                     
[AMD64] orora@host > ls -ltr reports.dbf
-rw-r-----  1 orora dbora 524320768 Mar 18 21:38 reports.dbf

This fixed the issue!

Soa purge 11g

Since the start on fusion we have been plagued with purge related issues, 11.1.1.3 the purge job barely worked.So we applied patches on top of it to fix it.On 11.1.1.5 its far better.So i will talk W.R.T 11.1.1.5 or higher version of SOA,

There are 2 options for purging SOA Tables :
 1)  Looped Purge - The stored procedure soa.delete_instances provides the looped purge implementation. Debug mode can be enabled by executing  soa_purge/common/debug_on.sql and setting serverout on.
Sample client to execute looped purge is listed below.

DECLARE
   MAX_CREATION_DATE timestamp;
   MIN_CREATION_DATE timestamp;
   batch_size integer;
   max_runtime integer;
   retention_period timestamp;
  BEGIN
   MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
   batch_size := 10000;
     soa.delete_instances(
     min_creation_date => MIN_CREATION_DATE,
     max_creation_date => MAX_CREATION_DATE,
     batch_size => batch_size,
     max_runtime => max_runtime,
     retention_period => retention_period,
     purge_partitioned_component => false);
  END;
  /

 2) Parallel Purge: The stored  procedure soa.delete_instances_in_parallel provides the parallel purge implementation  To execute the parallel purge, the following additional privileges are required for the soainfra user.Parallel purge is similar to loop purge with additional flexibility of spawning parallel threads to do the purging (faster, multi threaded approach if dealing with huge number of records)
    i)  Create Job
    ii) execute on dbms_lock.

DECLARE
   max_creation_date timestamp;
   min_creation_date timestamp;
   retention_period timestamp;
  BEGIN
   min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
   max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
   retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
    soa.delete_instances_in_parallel(
     min_creation_date => min_creation_date,
     max_creation_date => max_creation_date,
     batch_size => 10000,
     max_runtime => 60,
     retention_period => retention_period,
     DOP => 3,
     max_count => 1000000,
     purge_partitioned_component => false);
 END;

 Debug mode can be enabled by executing the script 'soa_purge/common/debug_on.sql' and setting serverout on .  The debug logs from the  jobs  spawned by parallel purge will  be logged into files created in the directory 'SOA_PURGE_DIR' . This directory needs to be created(e.g: create directory SOA_PURGE_DIR as '\tmp\purge') and the soainfra user needs to be provided write access on this directory. Also ensure that the location of the  directory is  accessible to the oracle server.

There is another option ignore_state=> true ,which purges instances regardless of its state.Normally only completed instances are purged.

OIM xelsysadm password reset

We had a request from customer to reset the xelsysadm password reset.I thought it would be a complex process using ladapmodify etc..but it was very simple.

Solution
========
1)You need to use the Administrator Console, edit the "System Administrator" user (user login "xelsysadm"), select the "Change Password At Next Login" check-box, logout and login again. Then you can change the xelsysadm password.

ORA-00001: unique constraint (PROD_SOAINFRA.AT_PK) violated

Hello,
I was seeing a huge amount of errors pertaining to AT_PK table ,

============

####<Nov 27, 2012 3:01:57 AM CET> <Error> <EJB> <host> <WLS_SOA1> <orabpel.audit.pool-3.thread-4> <<anonymous>> <> <0000JfFsuuR6eLMaELq2US1G_pet000IDr> <1353981717680> <BEA-010026> <Exception occurred during commit of transaction Name=[EJB com.collaxa.cube.engine.ejb.impl.bpel.BPELAuditTrailBean.storeAuditBatch(com.collaxa.cube.engine.audit.BatchEvent,com.collaxa.cube.persistence.dto.AuditCounter)],Xid=BEA1-074BC784ED925837639D(810973762),Status=Rolled back. [Reason=Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PROD_SOAINFRA.AT_PK) violated

Error Code: 1
Call: INSERT INTO AUDIT_TRAIL (CIKEY, COUNT_ID, NUM_OF_EVENTS, BLOCK_USIZE, CI_PARTITION_DATE, BLOCK_CSIZE, BLOCK, LOG) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        bind => [305173755, 0, 39, 11816, 2012-11-27 03:01:51.677, 1891, 0, [B@30572981]

Solution as per: Doc ID 1338478.1

They suggest to apply the patch ,will apply this and see if issue gets fixed.

IPM Web Support Server Logging

IPM 10g is a very old and stable product. Once you set it up, it goes very smoothly.But this customer wanted to enable logging ,i followed the below procedure.


To enable logging, perform the following steps:

   1. Login to IBPMExpress client (typical URL to log in to IBPMExpress: http://ServerName/ibpmexpress).
   2. Click on Administration (located in the left pane).
      Note: If you do not see Administration then the user you logged in as has not been given Web Administration Privileges through IPM Security Policies.
   3. Scroll down to the Diagnostics Section.
   4. Enter in a valid location for Real Time Log File Location.
   5. Checkbox the following options:
      a) Trace all available reporting points
      b) Trace all datasets
      c) Trace all outbound notifications
      d) Trace Key Points
      e) Use Realtime Logging
   6. Restart the Oracle Web Support Server service.


The above options can also be found in the WSSConfigEditor.exe tool which is located in the install directory.  (Example location: C:\Program Files\Stellent\WSS\WSSConfigEditor.exe)

Upgrade Oid Status Shows Invalid In dba_registry and app_registry tables

I usually don't work on 11g OID,but was helping out a team here.Issue comes on OID version 10.1.2.0.2 to 10.1.4.3

OID showing as INVALID in dba_registry and app_registry tables.

select * from dba_registry where status='INVALID' and
select * from app_registry where status ='INVALID'

OID shows invalid.

OID 10.1.4.0.1 INVALID

OID showing as invalid in dba_registry and app_registry tables, however when viewing ODS objects -- all are valid
SQL> select owner,object_name,object_type from dba_objects where owner='ODS' and status='INVALID'; 

This could be caused due to possible problem with upgrade script (oidiugrd.sql).OID showing as invalid in dba_registry and app_registry tables, however when viewing ODS objects -- all are valid

Errors can be found in  $ORACLE_HOME/ldap/admin/LOGS/ldapupgrade.log

Fix
===
Check the ODS schema for invalid objects and correct as necessary

% sqlplus / as sysdba
SQL> select owner,object_name,object_type from dba_objects
2> where owner='ODS' and status='INVALID';

Cut the final validation portion of oidiugrd.sql and run it by itself:

alter session set current_schema = SYS;

execute DBMS_IAS_VERSION.SET_COMPONENT_UPGRADED(COMPONENT_ID=>'OID');
declare
rc integer;
begin
rc := ods.ldapUpgUtls.validateODS();
if rc = 0 then
DBMS_IAS_VERSION.SET_COMPONENT_VALID(COMPONENT_ID=>'OID');
else
DBMS_IAS_VERSION.SET_COMPONENT_INVALID(COMPONENT_ID=>'OID');
end if;
end;
/ 
 
 

Register OHS 11g

Sometime after upgrade on fusion we get issues with ohs.ohs shows as down on em, despite being up.We have faced this many times, so we have included below steps after all fusion upgrades.



--------UNREGISTER OHS ---------------------------
-bash-3.2$ ./opmnctl unregisterinstance -instanceName ohs1 -adminHost host -adminPort port

Command requires login to weblogic admin server (host):
  Username: weblogic
  Password:

Unregistering instance
Command succeeded.

--------REDEPLOY OHS ---------------------------
-bash-3.2$ ./opmnctl redeploy -adminHost host -adminPort port


Command requires login to weblogic admin server (host):
  Username: ohsadmin
  Password:

Redeploying NonJ2EEManagement Application...weblogic.Deployer invoked with options:  -adminurl vmohsautr048.oracleoutsourcing.com:45501 -username ohsadmin -name NonJ2EEManagement -source /test/fmw/product/111/ohs/opmn/applications/NonJ2EEManagement.ear -redeploy -upload -noexit
<Apr 24, 2012 3:25:01 PM CDT> <Info> <J2EE Deployment SPI> <BEA-260121> <Initiating redeploy operation for application, NonJ2EEManagement [archive: test/fmw/product/111/ohs/opmn/applications/NonJ2EEManagement.ear], to configured targets.>
Task 0 initiated: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Task 0 completed: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Target state: redeploy completed on Server AdminServer

Done
Command succeeded.

--------REGISTER OHS ---------------------------
 ./opmnctl registerinstance -adminHost host -adminPort port

Command requires login to weblogic admin server (host):
  Username: weblogic
  Password:

Registering instance
Command succeeded.


-adminHost is the weblogic admin server
-adminPort is the port for the admin server

Kill particular OS process

ps -ef |grep <Process> |awk '{print  $2}'|xargs kill -9

For example:
ps -ef |grep LOCAL=NO |awk '{print  $2}'|xargs kill -9

Disable 11g gather stats

I know this is very simple,but here it is,


 col JOB_NAME for a30
 col START_DATE for a30
 col LAST_START_DATE for a30
 col NEXT_RUN_DATE for a30
select JOB_NAME,START_DATE,LAST_START_DATE,NEXT_RUN_DATE,JOB_ACTION from  DBA_SCHEDULER_JOBS where job_name like '%GATHER%'

SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;

exec dbms_scheduler.disable('GATHER_SYS_STATS_JOB');

OIM Bundle patch

I was applying bundle patch on OIM as per,

https://updates.oracle.com/Orion/Services/download?type=readme&aru=15248238

Issue:
+ opatch apply was failing.The logs are here,

/test/fmw/product/111/iam_111/server/setup/deploy-files/patch_oim_wls.log

Saw this error,
/test/fmw/product/111/iam_111/server/setup/deploy-files/setup.xml:337: Unable to delete directory --->I renamed the folder manually.

+ The script "sh patch_oim_wls.sh"  looks for profile files.Need to update weblogic.profile as well.

+ Later got the error as in Doc ID 1441378.1 .Set the path as per the note.

Later it failed with errors like,

With errors like,
java.lang.RuntimeException: Can't find resource 'solrconfig.xml' in classpath or '${RBACX_HOME}/.indexes/cores/core01/conf/',

java.lang.RuntimeException: Can't find resource 'solrconfig.xml' in classpath or '${RBACX_HOME}/.indexes/cores/core08/conf/', cwd=/twrl2s/admin/user_projects/oia_domain

RBACX_HOME is set in .profile.But i added additionally in setDomainEnv.sh

JAVA_PROPERTIES="-Dplatform.home=${WL_HOME} -Dwls.home=${WLS_HOME} -Dweblogic.home=${WLS_HOME}  -DRBACX_HOME=/test/fmw/product/111/oia_111"


Hope this port will be helpful.Took me sometime to figure out

Portal 11g patch issue

While applying patch on portal came across this issue and was able to fix it.

Applying Portal Patch!!

Copying file /prod/fmw/patches/13985116/custom/scripts/P13985116_PATCHBUNDLE.zip to /prod/fmw/product/111/portal/portal/patch/oneoffs/P13985116_PATCHBUNDLE.zip

CMD=./ptlpatch.csh  -z P13985116_PATCHBUNDLE.zip -s dev1_portal -p oracle -c host:port:SID

Return Code = 1

Command invocation returned Error... '', Return Code = 1

Execution of PRE script failed, with return value = 1

Do you want to proceed? [y|n]

y

User Responded with: Y

Patching component oracle.portal.midtier, 11.1.1.4.0...

Copying file to "/prod/fmw/product/111/portal/portal/patch/oneoffs/13985116.txt"

ApplySession adding interim patch '13985116' to inventory

Verifying the update...

Inventory check OK: Patch ID 13985116 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 13985116 are present in Oracle Home.

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

The following warnings have occurred during OPatch execution:

1) OUI-67133:Execution of PRE script failed, with return value = 1

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

FIX:

===

+ ptlpatch.csh didnot have execute permission.Give it the 755 permission.
+ Added the tnsnames.ora in portal home ,it was unable to connect to database.
+ opatch apply /prod/fmw/patches/13985116 -pre -s dev1_portal -p oracle -c host:port:SID  -opatch_pre_end-->No quotes needed in syntax!

Issue with soa managed server

Today while starting soa managed server,i was getting issues.It used to go to starting mode and then to failed not restartable.I checked logs and found that AIA deployments were failing,

===========

WARNING: Error during preRegister for MBean oracle.dfw:name=Streamer,type=oracle.dfw.jmx.Streaming 

java.lang.RuntimeException: java.lang.NoClassDefFoundError: Could not initialize class oracle.dfw.impl.common.TempFileManager  at oracle.as.jmx.framework.generic.spi.interceptors.LoggingMBeanInterceptor.internalPreRegister(LoggingMBeanInterceptor.java:663) 

at oracle.as.jmx.framework.generic.spi.interceptors.AbstractMBeanInterceptor.doPreRegister(AbstractMBeanInterceptor.java:180) 

at oracle.as.jmx.framework.standardmbeans.spi.OracleStandardEmitterMBean.preRegister(OracleStandardEmitterMBean.java:611) 

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.preRegisterInvoke(DefaultMBeanServerInterceptor.java:1010) 

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerDynamicMBean(DefaultMBeanServerInterceptor.java:938) 

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerObject(DefaultMBeanServerInterceptor.java:917) 

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerMBean(DefaultMBeanServerInterceptor.java:312) 

at com.sun.jmx.mbeanserver.JmxMBeanServer.registerMBean(JmxMBeanServer.java:482) 

oracle/dfw/impl/common/TempFileManager --->Appeared many times.

It had a simple fix.

1. Check /tmp directory in your machine and check if having oracle-dfw* files created by a different user other than the "installation owner".

If so, delete them all.

2. If you want to have multiple installations, by different users, ensure to set the following java property Into the EXTRA_JAVA_PROPERTIES for the setDomainEnv.sh:

-Djava.io.tmpdir=<path>

Error on admin console

After a bounce on weblogic domain i got the below error after logging to admin console

================

Error opening /jsp/changemgmt/ChangeManager.jsp.

The source of this error is:
java.lang.NullPointerException
 at com.bea.console.taglib.changemgmt.ChangeCenterTag.doStartTag(ChangeCenterTag.java:82)
 at jsp_servlet._jsp._changemgmt.__changemanager._jspService(__changemanager.java:172)
 at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
 at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
 at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
 at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
 at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:183)
 at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:523)
 at weblogic.servlet.internal.RequestDispatcherImpl.include(RequestDispatcherImpl.java:444)
 at org.apache.beehiv

=====================

I thought someone edited the file and forgot to activate changes.I checked the DOMAIN_HOME pending folder to see if there was a change done.Found nothing here.

To fix this issue i tried restarting admin after clearing tmp and cache but no use,

We can fix this issue as below:

1. Navigate to <Domain_Home>/pending folder. In this folder, there are configuration files which are temporary config files which are cached with changes made from console. At the time of activating changes, the config.xml from the pending folder replaces the

 original config.xml and other configuration files inside the <Domain_Home>/config folder

2. Stop the Admin Server, and replace the config.xml from the <Domain_Home>/config folder with the config.xml from the <Domain_Home>/pending folder.

3. Delete all the .lok files inside <Domain_Home> like edit.lok and config.lok.

4. Restart Admin Server.

I my case i just removed the edit.lok file and restarted.