Oracle datapatch errors ORA-29913 ORA-25153

After successfully applying the database patch on the binary, the next step is to run the datapatch, however when running it….it failed with below errors:

./datapatch –verbose

SQL Patching tool version 12.1.0.2.0 Production on Wed May  1 10:03:02 2019

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

Connecting to database…OK

Bootstrapping registry and package to current versions…done

Queryable inventory could not determine the current opatch status.

Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

and/or check the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Wed May  1 10:03:08 2019

Solution:

After reviewing the log file sqlpatch_invocation.log:

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

so I have executed the following query:

SQL> select * from OPATCH_XML_INV ;

ERROR:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-25153: Temporary Tablespace is Empty

So this is an indication of temporary tabelspace issue, so I have first created a “new” temporary tablespace:

SQL> CREATE TEMPORARY TABLESPACE TEMP_DATA_02 TEMPFILE

  ‘/oradata1/oradbx26/TEMPTD01.dbf’ SIZE 120M AUTOEXTEND ON

TABLESPACE GROUP ”

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Then, I have made the new temporary tablespace the default one:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_DATA_02;

And finally dropped the old temporary tablespace:

SQL> DROP TABLESPACE TEMP_DATA_01 INCLUDING CONTENTS AND DATAFILES;

 

I hope this helps someone !

 

Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

ORA-00600 & ORA-48254 when upgrading to Oracle 12cR2

while upgrading a database from Oracle 12cR1 to 12cR2….the following errors were thrown in the alert log file:

Errors in file /oraclex/orcl/diag/rdbms/orcl/orcl/trace/orcl_m000_125839.trc (incident=708075):

ORA-00600: internal error code, arguments: [Migrate Relation Failure], [], [], [], [], [], [], [], [], [], [], []

ORA-48254: Migration Error [dbgrmmdmr_max_length_change] [PDB_SPACE_MGMT] [2] [66] [32] [9]

Incident details in: /oraclex/orcl/diag/rdbms/orcl/orcl/incident/incdir_708075/orcl_m000_125839_i708075.trc

Use ADRCI or Support Workbench to package the incident.

 

This is due to old ADR setup, so you need to reset ADR location

SQL> show parameter diagnostic_dest
SQL> ALTER SYSTEM SET diagnostic_dest=/orcl/ora23/diagnostic/ scop=spfile;

ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1] in the alert log !

one of the databases i am supporting ( on version 12cR1 12.1.0.2) was throwing ORA-600 in the alert log:

Archived Log entry 94742 added for thread 1 sequence 48014 ID 0x6a20c315 dest 2:
Errors in file /oracle/orcl12/diag/rdbms/orcl12/orcl12/trace/orcl12_j003_115141.trc (incident=582200):
ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc
Non critical error ORA-48913 caught while writing to trace file “/oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc”
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Solution:

if this error is infrequent then you can ignore, otherwise as a workaround is to disable the Automatic SQL Tuning Tasks by executing the following:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL );
END;
/

As a consequence, this will disable all automatic SQL tuning tasks but you can still perform “on-demand” SQL tuning to get advice on tuning specific SQL statements.

 

Presenting NoSQL Technology in Imam Abdulrahman Bin Faisal University

I have presented at Imam Abdulrahman Bin Faisal University to Computer Science Students “Introduction to NoSQL Technology

EMAD_SadaIT

Introduction_To_No_SQL

 

 

 

 

physical standby error when applying archive logs ORA-00317

in a physical standby that was out of synchronization….i found that the below errors were thrown:

ORA-00317: file type 0 in header is not log file

ORA-00334: archived log: ‘/stdby/arc/arch_t211_1_972641998_48514.arc’

To resolve this issue:

the error is indicating that the archive log was is corrupted. so i restored the archive log using RMAN:

rman target /

RMAN> restore archivelog logseq=48514;

the archive log restored successfully and then applied on the standby.

hope this helps

LOCAL TEMPORARY TABLESPACE in 12cR2 and 18c

when upgrading a database from 12cR1 to either 12cR2 or 18c , you will notice when querying dba_users view a new column added: LOCAL_TEMP_TABLESPACE

local temp tablespace is most probably used for RAC.

you need to ensure that its not assigned to “SYSTEM” tablesapce, to check this:

SQL>  select username from dba_users where local_temp_tablespace=’SYSTEM’;

if there any….you can set it to temp tablespace as follows:

SQL> alter user C##ADAM LOCAL TEMPORARY TABLESPACE TEMP;

 

Automate Startup/Shutdown Of Oracle Database 12cR2 in Linux Red Hat

if you are not using Grid Infrastructure and Oracle Restart, this guide will help you
automate single instance Oracle database.

create a directory for your shell scripts for example:

mkdir /app/oracle/scripts

1. create a script called setEnv.sh

vi setEnv.sh

# Oracle Settings

export TMP=$ORACLE_BASE/tmp

export TMPDIR=$ORACLE_BASE/tmp

export ORACLE_HOSTNAME=mycomputer.com

export ORACLE_UNQNAME=db12c

export ORACLE_BASE=/orcl/db12

export ORACLE_HOME=/orcl/db12/product/12.2.0.1

export ORACLE_SID=/orcl/db12

export PATH=$PATH:$ORACLE_HOME/bin:/usr/vacpp/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:/$ORACLE_HOME/rdbms/jlib

2. create two scripts called start_all.sh & stop_all.sh

vi start_all.sh

#!/bin/sh

. /app/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

dbstart \$ORACLE_HOME

vi stop_all.sh

#!/bin/sh

. /app/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

dbshut \$ORACLE_HOME

3. edit scripts unde $ORACLE_HOME/bin for dbstart and dbshut by performing the following:

replace ORACLE_HOME_LISTNER=$1

with

ORACLE_HOME_LISTNER=$ORACLE_HOME

and replace $ORACLE_HOME/bin/lsnrctl start >> $LOG 2>&1 &

with your listener name configured in your listener.ora file

$ORACLE_HOME/bin/lsnrctl start listener_db12c >> $LOG 2>&1 &

4. make sure /etc/oratab is reflecting the right oracle home path

5. Ask Linux Admin to create the following file:

vi /lib/systemd/system/dbora.service

[Unit]

Description=Oracle DB 12cR2

After=syslog.target network.target

[Service]

RemainAfterExit=yes

User=oracle

Group=dba

ExecStart=/app/oracle/scripts/start_all.sh

ExecStop=/app/oracle/scripts/stop_all.sh

[Install]

WantedBy=multi-user.target

6. Linux Admin should execute the following:

systemctl daemon-reload

systemctl start dbora.service

systemctl enable dbora.service

8. Ask Linux Admin to reboot the Linux server to TEST automatic startup of the database

Oracle 18c installation in Linux

in this blog post i will describe the necessary steps to install Oracle 18c in Linux Red Hat.

First, pre-requisite steps:

check that all packages required are installed, check the following link as reference:

https://docs.oracle.com/en/database/oracle/oracle-database/18/cwlin/supported-red-hat-enterprise-linux-7-distributions-for-x86-64.html#GUID-2E11B561-6587-4789-A583-2E33D705E498

Kernel values: https://docs.oracle.com/en/database/oracle/oracle-database/18/cwlin/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31

Another way to check pre-requisites is ORACHK tool , this tool can be downloaded from Oracle Support Doc ID 1268927.2.

copy the zip folder downloaded for ORACHK to database file system:

unzip orachk.zip

To check Installation Readiness, execute the following:

./orachk -profile preinstall

To check Upgrade Readiness, execute the following:

./orachk -u -o pre

Second, Installation using GUI:

reconfigure your profile to point to Oracle 18c binaries (new home).

use the following command to unzip the binaries:

unzip -q /oracle/app/LINUX.X64_180000_db_home.zip -d /oracle/ora15/product/18.3

 

cd /oracle/ora15/product/18.3

./runInstaller

11

22

33

44

55

66

77

88

Third, Silent Installation Option:

please note that you need to edit(modify) the file db_reponse_tmp.rsp located under $ORACLE_HOME/install/response

*** checking prerequisites before installation :

$ORACLE_HOME/runInstaller -silent -executePrereqs -responseFile $ORACLE_HOME/install/response/db_reponse_tmp.rsp

*** Silent Installation :

$ORACLE_HOME/runInstaller -silent -responseFile $ORACLE_HOME/install/response/db_reponse_tmp.rsp

 

you will need to run root.sh

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Applying RU (Release Update) to Oracle 18c

Since Oracle 12cR2 RU(Release Update) and RUR (Release Update Revision) was introduced by Oracle for patching every quarter.

The following is the guide in how to apply RU to Oracle 18c release, as you might know Oracle introduced 18c on-premise with 18.3 release….here i am applying January 2019 Oracle Security Patch release which is 18.5.

its worth pointing out that you might face patching issues…..check the below helpful references i blogged about it before:

https://geodatamaster.com/2018/07/19/oracle-psu-july-2018-error-ora-20001-latest-xml-inventory-is-not-loaded-into-table/

https://geodatamaster.com/2018/11/04/oracle-ojvm-psu-error/

download the latest RU patch and place it in a directory accessible by your database server:

cd /oracle-app/linux/18c_RU/18.5_DB/28822489

/$ORACLE_HOME/OPatch/opatch apply

opatch2

opatch222

sqlplus / as sysdba

SQL> STARTUP

exit

cd $ORACLE_HOME/OPatch

./datapatch -verbose

datapatch1

*** Post-Patch verification:

sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql
//check your database components

SQL> select COMP_NAME,STATUS from dba_registry;
// query to list applied patches on the database instance:

SQL > select * from dba_registry_sqlpatch;

dba_registry_sqlpatch1

so the database has been successfully patched with full release number 18.5.0.0190115

if your database has “java” component, you need to download the java patch (OJVM) and repeat the above steps….except that you will startup the database in upgrade mode before running the data-patch.

I hope this is helpful……have a nice day.