Friday, November 8, 2019

OPATCHAUTO-72059: Supplied patch is invalid for current platform.


Issue: While applying GRID patch, opatchauto was throwing below error, environment linux x86_64


# $GRID_HOME/OPatch/opatchauto apply /cme/oradm/software/30070242/26983807 -analyze -oh /opt/grid/product/12.1.0/grid_1

OPatchauto session is initiated at Thu Nov  7 08:35:40 2019
OPATCHAUTO-72059: Invalid patch.
OPATCHAUTO-72059: Supplied patch is invalid for current platform.
OPATCHAUTO-72059: Please provide a correct patch location.

OPatchauto session completed at Thu Nov  7 08:35:42 2019
Time taken to complete the session 0 minute, 2 seconds

opatchauto bootstrapping failed with error code 255.


Solution: Noticed that the "oraclehomeproperties.xml" file in Below location has missing highlighted entries. I corrected those by adding it and the opatchauto worked fine.

cd $GRID_HOME/inventory/ContentsXML

# vi oraclehomeproperties.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2014, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<ORACLEHOME_INFO>
   <GUID>638047284.418901274</GUID>
   <HOME CRS="T"/>
   <ARU_PLATFORM_INFO>
      <ARU_ID>226</ARU_ID>
      <ARU_ID_DESCRIPTION>Linux x86-64</ARU_ID_DESCRIPTION>
   </ARU_PLATFORM_INFO>
   <CLUSTER_INFO>
      <LOCAL_NODE NAME="linux01"/>
      <NODE_LIST>
         <NODE NAME="linux01"/>
         <NODE NAME="linux02"/>
      </NODE_LIST>
   </CLUSTER_INFO>
   <PROPERTY_LIST>
      <PROPERTY NAME="ORACLE_BASE" VAL="/opt/oracle"/>
   </PROPERTY_LIST>
</ORACLEHOME_INFO>






# $GRID_HOME/OPatch/opatchauto apply /cme/oradm/software/30070242 -analyze -oh $GRID_HOME

OPatchauto session is initiated at Thu Nov  7 09:00:37 2019

System initialization log file is /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchautodb/systemconfig2019-11-07_09-00-40AM.log.

Session log file is /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchauto/opatchauto2019-11-07_09-00-53AM.log
The id for this session is K4YH

Executing OPatch prereq operations to verify patch applicability on home /opt/grid/product/12.1.0/grid_1
Patch applicability verified successfully on home /opt/grid/product/12.1.0/grid_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:linux01
CRS Home:/opt/grid/product/12.1.0/grid_1
Version:12.1.0.2.0


==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /cme/oradm/software/30070242/26983807
Log: /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchauto/core/opatch/opatch2019-11-07_09-01-01AM_1.log

Patch: /cme/oradm/software/30070242/29938464
Log: /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchauto/core/opatch/opatch2019-11-07_09-01-01AM_1.log

Patch: /cme/oradm/software/30070242/29938481
Log: /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchauto/core/opatch/opatch2019-11-07_09-01-01AM_1.log

Patch: /cme/oradm/software/30070242/29972716
Log: /opt/grid/product/12.1.0/grid_1/cfgtoollogs/opatchauto/core/opatch/opatch2019-11-07_09-01-01AM_1.log



OPatchauto session completed at Thu Nov  7 09:01:19 2019

Sunday, July 15, 2018

ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error

I was doing import of a table and started noticing the below error. Even though the database was same.

Tried different methods but it didn't work. In Oracle support it was mentioned that I may be hitting a bug  and i may have to set NLS_LENGTH_SEMANTICS to char at DB level which was set to byte.

I noticed the table carefully and saw it has a dependency on a Oracle TYPE. 

When i saw its script saw that it was varchar2(20)

CREATE OR REPLACE TYPE ORAUSER1.OFFSET_HDR IS VARRAY(50) of VARCHAR2(20); 


SO I created it manually with an option of "char" in datatype.


SQL> CREATE OR REPLACE TYPE ORAUSER2.OFFSET_HDR IS VARRAY(50) of VARCHAR2(20 char)
  2  /

Type created.

Post that the table was imported successfully.


oracle@linux01:/home/oracle(ORADB)$ impdp / directory=DB_EXP_MOUNT dumpfile=exp_365137_2_%U.dmp logfile=imp_365137_2.log remap_schema=ORAUSER1:ORAUSER2 transform=oid:n parallel=4 cluster=no

Import: Release 12.1.0.2.0 - Production on Thu Jul 12 23:14:07 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testin
Master table "OPS$oracle"."SYS_IMPORT_FULL_05" successfully loaded/unloaded
Starting "OPS$oracle"."SYS_IMPORT_FULL_05":  /******** directory=DB_EXP_MOUNT dumpfile=exp_365137_2_%U.dmp logfile=imp_365137_2.log remap_schema=ORAUSER1:ORAUSER2 transform=oid:n parallel=4 cluster=no parfile=exclude_procobj.par table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "ORAUSER2"."RBX_CURVES" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "ORAUSER2"."RBX_CURVES" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "ORAUSER2"."OFFSET_HDR" not found or conversion to latest version is not possible
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "OPS$oracle"."SYS_IMPORT_FULL_05" completed with 1 error(s) at Thu Jul 12 23:14:10 2018 elapsed 0 00:00:02




Wednesday, November 8, 2017

EM12c : Authentication Failure while login to GUI console with the Correct Password

I was trying to login to OEM GUI console and was getting the below error, I was pretty sure that my password was correct and even tried with different user account but every time it was failing with “Authentication failed. If problem persists, contact your system administrator”

I checked the emoms.trc logs which had the below errors

omsserver1:/opt/oracle/gc_inst12cR5/em/EMGC_OMS1/sysman/log:(oracle)$ view emoms.trc

Error:
2017-11-07 21:52:31,166 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR audit.AuditLogHandler auditLog.378 - Could not log the audit record java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYSMAN.MGMT_AUDIT", line 919
ORA-06512: at "SYSMAN.MGMT_AUDIT", line 417
ORA-06512: at line 1

2017-11-07 21:52:31,168 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR authz.EMSecurityContext doEMLogout.522 - SQLException caught while destroying auditSessionGuid 5D6C463204EBB157E053B71ED00A7F0F
2017-11-07 22:01:37,537 [[ACTIVE] ExecuteThread: '18' for queue: 'weblogic.kernel.Default (self-tuning)'] WARN  auth.EMRepLoginFilter doFilter.458 - InvalidEMUserException caught in EMRepLoginFilter: Failed to login using repository authentication for user: JOBADM

Solution:
Verified that the job_queue_processes are not set to zero and then executed the below command to add the audit partitions. Verified that I am able to login to console post that.

SQL> alter session  set current_schema=sysman;
Session altered.

SQL> exec mgmt_audit_admin.add_audit_partition;

PL/SQL procedure successfully completed.

Wednesday, March 22, 2017

ORA-08104: this index object is being online built or rebuilt

Got this error after session was killed (or if we have done a ctrl + C) while doing an index rebuild and then trying to rebuild the index again, its seems the cause is – A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;
ALTER INDEX ORAUSER.TEST_INDX
*
ERROR at line 1:
ORA-08104: this index object 18902057 is being online built or rebuilt

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057        517


SQL> declare
   lv_ret BOOLEAN;
    begin
    lv_ret :=dbms_repair.online_index_clean(18902057);
    end;
    /

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057          5

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;

Index altered.

ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE

SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;
/

BEGIN
*
ERROR at line 1:
ORA-20000: ** FAILED TO SET STAT_TIME FOR THE TABLE **: TESTUSER.TEST_TABLE
ERROR: ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE **:
TESTUSER.TEST_TABLE Make sure Table has Standard Stats. ERROR: ORA-20000:
Unable to get values for column AVAILABLE_DATE
ORA-06512: at "SYS.DBMS_STATS", line 12211
ORA-06512: at "SYS.DBMS_STATS", line 12224
ORA-06512: at "STAT_MAINTENANCE", line 1072
ORA-06512: at "STAT_MAINTENANCE", line 1086
ORA-06512: at "STAT_MAINTENANCE", line 1171
ORA-06512: at "STAT_MAINTENANCE", line 1186
ORA-06512: at "STAT_MAINTENANCE", line 1645
ORA-06512: at "STAT_MAINTENANCE", line 1779
ORA-06512: at line 2



SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- ----------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1
  
SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1


Describe the table and check if any of the columns were removed.

SQL> DESC TESTUSER.TEST_TABLE

 Name     Null?    Type
 -------- -------- ----------------------------
 P1       NOT NULL NUMBER(38)
 C1                NUMBER(38)
 T1                VARCHAR2(3 CHAR)
 T2                TIMESTAMP(6)
 C2                VARCHAR2(32 CHAR)
 C3                FLOAT(126)
 C5                VARCHAR2(255 CHAR)
 C7       NOT NULL NUMBER(38)

  --- from PREFERENCE_VALUE, remove column dropped and use it below
SQL> EXEC DBMS_STATS.DELETE_TABLE_PREFS ('TESTUSER', 'TEST_TABLE','METHOD_OPT');


SQL> BEGIN
dbms_stats.Set_table_prefs('TESTUSER', 'TEST_TABLE', 'METHOD_OPT' , 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1');  
END;
/


SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- --------------------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1

SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
--------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1



SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;

/