Issue:Archive backup failed with below error.

ORA-19506: failed to create sequential file, name="bk_2527_1_863554048", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

ORA-19506: failed to create sequential file, name="bk_2528_1_863554069", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

ORA-19506: failed to create sequential file, name="bk_2529_1_863554087", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

ORA-19506: failed to create sequential file, name="bk_2530_1_863554109", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

Resolution:

- Downgraded the NBU client software.

- reconfigured ORA policy from MKE To WAU(wau-dev-lrh-orarman-any-t3).

While installing the Oracle home facing   below issue

[INS-35423] THE INSTALLER HAS DETECTED THAT ORACLE CLUSTERWARE IS NOT RUNNING ON LOCAL NODE.

Cause

ContentsXML is having wrong entry

Old entry:

<HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/11.2.0.3/grid" TYPE="O" IDX="1" REMOVED="T"/>

Updated ContentsXML by using runinstaller.

/u01/app/11.2.0.4/grid/oui/bin/runInstaller -updateNodelist ORACLE_HOME="/u01/app/11.2.0.4/grid" CRS=true

New entry:

<HOME NAME="Ora11g_gridinfrahome2" LOC="/u01/app/11.2.0.4/grid" TYPE="O" IDX="15" CRS="true">

ORA-01722: invalid number

Issue:

DOC>#######################################################################

DOC>#######################################################################

DOC>     The following statement will cause an "ORA-01722: invalid number"

DOC>     error if the Oracle Database Vault option is TRUE.  Upgrades cannot

DOC>     be run with the Oracle Database Vault option set to TRUE since

DOC>     AS SYSDBA connections are restricted.

DOC>

DOC>     Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink

DOC>     the server without the Database Vault option, and restart the server

DOC>     using UPGRADE mode.

DOC>

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option

                 *

ERROR at line 1:

ORA-01722: invalid number

Cause:

Before running upgrade script disable dv  at OS level

chopt disable dv

Once the upgrade script is completed enable dv

 

chopt enable dv

ORA-01114 ERROR:

Issue:

PeopleTools 8.52.21 - Application Engine Server Copyright (c) 1988-2014 Oracle and/or its affiliates. All Rights Reserved PSAESRV started service request at 18.35.24 2014-12-05 Processed Order Request with Message ID - 000000000304166 and Order no - 900012043 (0,0) File: /vob/peopletools/src/pspcm/pcmobuf.cppSQL error. Stmt #: 5555  Error Position: 0  Return: 1114 - ORA-01114: IO error writing block to file  (block # ) Failed SQL stmt:INSERT INTO PS_ZZ_ITC_MSG_LOG (OPERATIONNAME,MESSAGE_ID,KEY_FIELD_NAME_01,KEY_FIELD_NAME_02,DESCR,GUID,DATETIME_STAMP,XML_TESTI,RESENDCONTENT,MESSAGE_PARM1,OPRID) VALUES (:1,:2,:3,:4,:5,:6,TO_TIMESTAMP(:7,'YYYY-MM-DD-HH24.MI.SS.FF'),:8,:9,:10,:11) SQL error. Function:  Record.Insert
 Error Position:  0
 Return:  1114 - ORA-01114: IO error writing block to file  (block # )

Cause:

Temp file Header of one of the Temporary Tablespace files is corrupted

   INST_ID TABLESPACE_NAME                   FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO

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

         2 TEMP                                    2 -1.565E+11   -19108352 1.6912E+11    20644352            2

         2 TEMP                                    3 1.0486E+10     1280000          0           0            3

         2 TEMP                                    1    1048576         128 9662627840     1179520            1

         3 TEMP                                    3 1.0486E+10     1280000          0           0            3

         3 TEMP                                    1    1048576         128 9662627840     1179520            1

         3 TEMP                                    2 -1.565E+11   -19108352 1.6912E+11    20644352            2

         1 TEMP                                    3 1.0486E+10     1280000          0           0            3

         1 TEMP                                    1    1048576         128 9662627840     1179520            1

         1 TEMP                                    2 -1.565E+11   -19108352 1.6912E+11    20644352            2

-ve (Negative) Values in BYTES_USED & BLOCKS_USED indicates Tempfile Header Corruption

Fix:

SQL> ALTER DATABASE TEMPFILE '+PRDFSCM_DG/prdfscm/tempfile/temp.463.763559877' DROP;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+PRDFSCM_DG' size 11g;

Tablespace altered.

ISSUE WHILE DOING THE DATABASE UPGRADE :

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following error is generated if (1) the old release uses a time

DOC>   zone file version newer than the one shipped with the new oracle

DOC>   release and (2) the new oracle home has not been patched yet:

DOC>

DOC>      SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')

DOC>                       *

DOC>      ERROR at line 1:

DOC>      ORA-01722: invalid number

DOC>

DOC>     o Action:

DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").

DOC>       Patch new ORACLE_HOME to the same time zone file version as used

DOC>       in the old ORACLE_HOME.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')

                 *

ERROR at line 1:

ORA-01722: invalid number

Solution:

1. We have crosschecked whether RDBMS DST patch is applied to the 11.2.0.4 home or not, and found it is fine. 

2. As per the given error (1) to make sure we have 11.2.0.3 RDBMS DST patch on 11.2.0.4 home, we have applied it manually (=not using opatch). 

We followed the below process.

•             2  *17.dat files and the readme_17.txt from the $ORACLE_HOME/oracore/zoneinfo of 11.2.0.3 home to 11.2.0.4 $ORACLE_HOME/oracore/zoneinfo directory,

•             2 *17.dat files $ORACLE_HOME/oracore/zoneinfo/big of 11.2.0.3 home to 11.2.0.4 $ORACLE_HOME/oracore/zoneinfo/big directory,

•             2 *17.dat files $ORACLE_HOME/oracore/zoneinfo/little of 11.2.0.3 home to 11.2.0.4 $ORACLE_HOME/oracore/zoneinfo/little directory, as it is simply adding new files, not replacing used ones.

Issue while doing database upgrade :

ERROR:

COMP_ID                        COMP_NAME                                VERSION                        STATUS

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

ORDIM                          Oracle Multimedia                        11.2.0.4.0                     loading

XDB                            Oracle XML Database                      11.2.0.4.0                     VALID

Solution:

We have to run below query.

execute sys.dbms_registry.loaded('ORDIM');

execute sys.dbms_registry.valid('ORDIM');

Issue with SYSAUX tablespace:

So we found that  WRH$_SQL_PLAN  table occupied  4G of data.

Followed this metalink  Doc ID 1478615.1  after doing all the steps and done re org for the table WRH$_SQL_PLAN

ORA-56920 ERROR WHILE DOING THE TIMEZONE UPGRADE : 
****************************************************

ERROR:

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(23);

BEGIN DBMS_DST.BEGIN_UPGRADE(23); END;

*

ERROR at line 1:

ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in

an active state

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DST", line 1162

ORA-06512: at line 1

While investigating to sort out this we found previous time zone upgrade to DST 17 was incomplete and below are the details.

We checked primary and secondary time zone version,it is as below.But,DST_SECONDARY_TT_VERSION value  should be 0.

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         17

DST_SECONDARY_TT_VERSION       11

DST_UPGRADE_STATE              UPGRADE

We have followed below procedure to made DST 17 upgrade complete. After completing  the upgrade, DST_SECONDARY_TT_VERSION automatically changed from 11 to 0.

FIX:

*****

SQL> VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

SQL>   2    3    4    5

  6  /

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         17

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

Hence the above issue got resolved and now we are able to upgrade the  time zone as usual.

DB connection issue :

LISTENER REFUSED THE CONNECTION WITH THE FOLLOWING ERROR :

ORA-12514, TNS :  LISTENER DOESN'T CURRENTLY KNOW OF SERVICE : 

we are using the MEMORY_TARGET feature on these databases, This feature requires the /dev/shm file system to be mounted for at least 10GB(10737418240 bytes). We get ORA-00845: MEMORY_TARGET not supported on this system error when /dev/shm is either not mounted or is mounted with available space  is less than the above mentioned size.  We need to fix this by reclaiming the space so that MEMORY_TARGET can work as expected. ensure that the mount point  /dev/shm for this AMM feature(memory_target ) needs larger /dev/shm

At the time of issue it  is at :

[jptyooradbd1l.ap.health.ge.com:/export/home/grid]

+ASM:grid>df -kh

Filesystem            Size  Used Avail Use% Mounted on

tmpfs                  32G   28G  4.3G  87% /dev/shm

after reboot it came down to:

[jptyooradbd1l.ap.health.ge.com:/dev/shm]

+ASM:grid>df -kh .

Filesystem            Size  Used Avail Use% Mounted on

tmpfs                  32G   17G   16G  52% /dev/shm

Afetr OS patch : GRD Apllication services start/stop processes :

GRD application services are running on the XXX  server, So any services down for GRD application, we need to connect this server and check the status as below.

Steps for App status checking:

1)      Login as oracle user

2)      Set oraenv with apex

3)      Cd $ORACLE_HOME/opmn/bin

4)      In the above location check the status using below command.

apex:oracle>opmnctl status

Processes in Instance: IAS-1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status

---------------------------------+--------------------+---------+---------

HTTP_Server                      | HTTP_Server        |   27005 | Alive

5)      Please check the above status column and it must be in Alive

6)      IF status is down, Then we need to start the services.

./Opmnctl startall

ORA-28575 Unable to open RPC connection to external procedure agent

After patching ,In oracle database user is facing the below error.

Error : ORA-28575

Resolution:

Please add the EXTPROC  entry in Listener.ora and tnsnames.ora and reload the LISTENER on all nodes of a cluster.

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

    )

  )

Note: Please make sure soft links are pointed to our common location from GRID_HOME/network/admin and ORACLE_HOME/network/admin.

[frbucoras1l.em.health.ge.com:/u01/app/oracle/product/11.2.0.3/dbee_1/network/admin]

stgcrln1:oracle>ls -ltrh

total 28K

lrwxrwxrwx 1 oracle oinstall   51 Apr 19  2013 tnsnames.ora -> /u02/app/oracle/admin/ORABUC1S/network/tnsnames.ora

lrwxrwxrwx 1 oracle oinstall   47 Apr 19  2013 ldap.ora -> /u02/app/oracle/admin/ORABUC1S/network/ldap.ora

lrwxrwxrwx 1 oracle oinstall   51 Apr 19  2013 listener.ora -> /u02/app/oracle/admin/ORABUC1S/network/listener.ora

** ** There was a problem getting a list: code: 29548 SQLState: 99999 Message: ORA-29548: Java system class reported: could not identify release specified in classes.bin

Run the post patch steps of JAN CPU 2015 (particularly OJVM post patch) once again in proper order as per the patch document to resolve the JAVA error.

ISSUE : Oracle DOC: OWB Component was not Upgraded While Upgrading The Oracle Database From 11.1 To 11.2 (Doc ID 1587473.1)

After upgrading below databases from 11.2.0.3 to 11.2.0.4. We found OWB (Oracle Warehouse Builder) was still in 11.2.0.3.0. As per oracle Doc I have followed below steps to upgrade OWD to 11.2.0.4.

Before Status:

COMP_ID         COMP_NAME         VERSION                        STATUS

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

OWB             OWB               11.2.0.3.0                     VALID

Work Around:

SQL>$ORACLE_HOME/owb/UnifiedRepos/clean_owbsys.sql

SQL>$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

After Status:

COMP_ID         COMP_NAME     VERSION                        STATUS

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

OWB             OWB           11.2.0.4.0                     VALID

User error while running the application

ERROR:

Nested exception is:
java.sql.SQLException: [SQLState=72000][ErrorCode=1591]ORA-01591: lock held by in-doubt distributed transaction 21.3.3075

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
       at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:911)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)

WORK AROUND:

As per meta link note 1012842.102 we followed below steps:

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from pending_trans$;

select * from pending_sessions$;

select * from pending_sub_sessions$;

From the above queries keep all the value of LOCAL_TRAN_ID in each table and try to force commit or rollback.

1)     ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

2)     EXEC DBMS_TRANSACTION.rollback_force('LOCAL_TRAN_ID');  (Need to mention the TRAN_ID which we got from the above queries)

3)   COMMIT ;

4)   ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

5)   EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');

 6)    COMMIT;

Repeat each steps for each LOCAL_TRAN_ID, turn on recovery for distributed transaction

crosscheck these tables (must be empty):

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from pending_trans$;

select * from pending_sessions$;

select * from pending_sub_sessions$;

Component status was INVALID after upgrade of 11.2.0.3 Oracle Database

Solution:

As per oracle metalink i have followed below steps.

1- Deinstall Multimedia

sqlplus '/as sysdba'
@$ORACLE_HOME/rdbms/admin/catcmprm.sql ORDIM

2- Review those objects and if they exist remove them

3- Install Multimedia

sqlplus '/as sysdba'
@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX
@$ORACLE_HOME/ord/im/admin/catim.sql

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=501353721580072&id=1932947.1&_afrWindowMode=0&_adf.ctrl-state=i52nhyv48_436

Locally vs. Dictionary Managed Tablespaces

 When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.


Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.
Execute the following statement to create a dictionary managed
tablespace:

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M 
      EXTENT MANAGEMENT DICTIONARY 
      DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed
tablespace:
SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;