Shravan Mankala's Blog: August 2016

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:

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:

ORA-01722: invalid number

Issue:

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>#

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> 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>#

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