Tuesday, August 2, 2016

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

Saturday, June 18, 2016

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




Friday, June 17, 2016

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;