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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

JVM Corruption and ORA-7445

The intention of this blog is to share a problem faced in a production instance. its strongly advised when you face ORA-7445 error is to contact Oracle Support and open a case with them.

So, my objective is to help here ….

what are the symptoms faced ?

Database version is 12cR1 (12.1.0.2.181016)

Database Alert log file has the following errors:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7440038090] [PC:0x7FEA4DB100D4, java_nio_Bits__byteOrder()+204] [flags: 0x0, count: 1]

DDE: Problem Key ‘ORA 7445 [java_nio_Bits__byteOrder]’ was completely flood controlled (0x6) Further messages for this problem key will be suppressed for up to 10 minutes

When executing SQL Scripts using sqlplus….for example utlrp.sql the following errors were thrown:

SQL> @/rdbms/admin/utlrp.sql
ERROR at line 1:
ORA-03113: end-of-file on communication channel 

Process ID: 105404
Session ID: 2878 Serial number: 20833

Errors received from application side deployed in weblogic:

No More Data To Read From Socket

 

The reason of such symptoms is JVM Corruption !

To resolve this issue:

 

  1. Check JVM status by executing the following script:

 

spool jvm_info.log
—— REGISTRY INFO ——
SELECT SUBSTR(comp_name, 1, 30) comp_name, SUBSTR(version, 1, 20) version, status
FROM dba_registry
ORDER BY comp_name;
—— REGISTRY HISTORY ——-
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) act_time, action, version, id, comments
FROM dba_registry_history
ORDER BY action_time DESC;

—— JAVA OBJECT INFO ——
— Are there a substantial number of VALID Java objects in SYS?
SELECT owner, object_type, status, COUNT(*)
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
GROUP BY owner, object_type, status
ORDER BY owner, object_type, status;
— Is the DBMS_JAVA package VALID?
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name LIKE ‘DBMS_JAVA%’
OR object_name LIKE ‘%INITJVMAUX%’
ORDER BY owner, object_name, object_type;
— Are there any INVALID Java objects in SYS?
SELECT owner, NVL(longdbcs,object_name) long_name, object_type, status
FROM dba_objects, sys.javasnm$
WHERE object_type LIKE ‘%JAVA%’
AND status <> ‘VALID’
AND short (+) = object_name
ORDER BY owner, long_name, object_type;
—— JAVA ROLE INFO ——
— The number expected varies by release.
SELECT role
FROM dba_roles
WHERE role LIKE ‘%JAVA%’
ORDER BY role;
—— MEMORY INFO ——
SELECT *
FROM v$sgastat
WHERE pool = ‘java pool’ OR name = ‘free memory’
ORDER BY pool, name;
—— DATABASE PARAMETER INFO ——
show parameter pool_size
show parameter target
show parameter sga
—— TEST JAVAVM USAGE (and return the JDK version if > 11g) ——
— Calling routines in DBMS_JAVA will invoke the JavaVM and expose certain problems.
SET SERVEROUTPUT ON
DECLARE
ver NUMBER := 0;
val VARCHAR2(30);
BEGIN
BEGIN
SELECT TO_NUMBER( SUBSTR(version, 1, 2) )
INTO ver
FROM dba_registry
WHERE comp_name = ‘JServer JAVA Virtual Machine’;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
IF ver >= 12 THEN
EXECUTE IMMEDIATE
‘SELECT ”JDK version is ” ||
dbms_java.get_jdk_version() FROM dual’
INTO val;
ELSE
val := dbms_java.longname(‘JDK version not available’);
END IF;
dbms_output.put_line(val);
END;
/

spool off;

  1. Repair JVM:

 

**** Note down the current value of parameters JAVA_JIT_ENABLED & JOB_QUEUE_PROCESSES

 

spool create_Java.log

set echo on

connect / as sysdba

Alter system set JAVA_JIT_ENABLED=FALSE scope=both;

alter system set “_system_trig_enabled” = false scope=memory;

alter system set JOB_QUEUE_PROCESSES=0;

create or replace java system

/

@?/rdbms/admin/utlrp.sql

set echo off

spool off

exit

Then reset it back to original values you noted down:

SQL> alter system set java_jit_enabled = true;

SQL> alter system set “_system_trig_enabled”=TRUE;

SQL> alter system set JOB_QUEUE_PROCESSES=50;

SQL> @?/rdbms/admin/utlrp.sql

  1. Test that everything is ok:

 

Check database components

SQL> select * from dba_registry;

SQL> SELECT dbms_java.longname(‘TEST’) long_name FROM dual;

 

 

 

 

 

 

 

 

 

 

 

Oracle database premier support for Oracle 12cR1,12cR2,18c,19c

As a DBA you need to regularly check and review Oracle Support Matrix especially if you have a big infrastructure with mixed Oracle database releases. Also, its very important from a cost perspective as (it depends on your contract with Oracle), to avoid “Paid Extended Support”.

What can be read from different Oracle resource 18c & 19c are considered under 12.2 release umbrella.

To the date of writing this blog (December 15 2018), the following are support dates:

Oracle Support Dates

So 19c is considered the long term support !

Resources:

Release Schedule of Current Database Releases (Doc ID 742060.1)

https://mikedietrichde.com/2018/12/14/premier-support-extension-for-oracle-19c-and-more/

 

Oracle 18c sqlplus new features

This is a short blog about new features introduced in Oracle 18c sqlplus ….that could be helpful for you !

SET ROWLIMIT

The objective of this command is to display certain number of rows, especially with large tables

 

In the below example the COUNTRIES table has 25 records, when I set ROWLIMIT to 3 , only three rows will be displayed when querying the table (the first 3 rows)

Picture1

The below command will display the current setting:

Picture2

SET FEEDBACK ON SQL_ID

This command will return the sql_id for the SQL or PL/SQL statements that is currently being executed. This will consequently help in debugging activities.

As shown below the executed query will generate the SQL_ID with the query result:

Picture3

You can check it from v$sql by executing:

SQL> SELECT sql_text,sql_id FROM v$sql WHERE sql_id = ‘agjjdvhgdzd1q’;

Picture4