How To Purge A SQL In 10.2.0.4 And 10.2.0.5

Today I was purge a SQL in an old 10.2.0.5 database, I used the DBMS_SHARED_POOL, but it don’t work. Look for notes in ML/MOS I find in this version there is a bug, you can workaround it by set the event 5614566.

I posted a entry some time ago about how to purge simple sql from buffer cache, you can see it following the nex link;

https://sql1.wordpress.com/2014/03/20/get-out-of-here/

Below I show an example using this event;

 
SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         1

1 fila seleccionada.

SBBD*ANTO> alter session set events '5614566 trace name context forever';

Sesión modificada.

SBBD*ANTO> exec SYS.DBMS_SHARED_POOL.PURGE ('0000000507C52778,720914062', 'S');

Procedimiento PL/SQL terminado correctamente.

SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         0

 

HTH – Antonio NAVARRO

 

Advertisements

How TO Know If My Backup Is Ok

Last morning I was killeing a backup (Sybase IQ). I don’t sure if it ends fine or bad. The issue is this backup is a incremental (from last backup indiferent from backup type) the policy for this database is a full backup on Saturday and incrementals every day of the week. The dude surge where can I see the sucessfully last backup. In this case the next query solve my problem;

SELECT * FROM sp_iqstatus () WHERE name LIKE ‘%ackup%’

I put a screenshot of the result.

last_bck_iq

Here you can see the last succesfully backup, and other data like estimate size the next incremental backup.

HTH – Antonio NAVARRO

 

Calculating MD5 In HPUX

Today I need to check a patch downloaded from Oracle. I don’t remember the unix command to verify the md5 code. I put it here.

The MD5 code (is an algorithm) is used to generate a code (in hexadecimal) that is opted after passing this algorithm for the file that is downloaded. Normally the one that offers the software puts the same and a code that is that it has to match when we generate it when passing the MD5 to the downloaded file. This is used to certify that the downloaded file is good and has not been tampered with. Although the MD5 is being discontinued because it is already quite insecure, more robust algorithms are being used instead.

 
cloudex> openssl dgst -md5 p8202632_10205_HPUX-IA64.zip 
MD5(p8202632_10205_HPUX-IA64.zip)= 342811de6db4e4d3ac86b35a2e6efb7c 
cloudex> openssl dgst -sha1 p8202632_10205_HPUX-IA64.zip 
SHA1(p8202632_10205_HPUX-IA64.zip)= 514735cea58a32a0f1c53c87ddb77575d8016b97 

In the example I genereted the code for sha-1 too. Although this patch has code for the SHA-256 algorithm, openssl command not support SHA-256

HTH – Antonio NAVARRO

How To Know The OS Of Installation Using The Inventory

Of course, it sounds like a little stupied, if you are executing the binarys from that ORACLE_HOME you need know which operating system you are executing. The issue is when you restore from tape a older backup of an ORACLE_HOME or a client send you a copy but don’t specify (or maybe he/she don’t know it).

This is a easy way to check it. After you unzip o tar -x if you must verify in

$ORACLE_HOME/inventory/ContentsXML

The file oraclehomeproperties.xml. With a cat or view it will be enough. The output is similar to this;

 

<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- 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>1399291201#.#542123581</GUID>
   <HOME>
   <ARU_PLATFORM_INFO>
      <ARU_ID>23</ARU_ID>
      <ARU_ID_DESCRIPTION>Solaris Operating System (SPARC 64-bit)
                </ARU_ID_DESCRIPTION>
   </ARU_PLATFORM_INFO>
   <PROPERTY_LIST>

   <PROPERTY NAME="ARCHITECTURE" VAL="64"/&gt;&lt;PROPERTY NAME="ORACLE_BASE" VAL="/XXXX/XXXX/XXXX"/&gt;&lt;/PROPERTY_LIST>
<CLUSTER_INFO&gt;&lt;LOCAL_NODE NAME="NODE-1"/&gt;&lt;NODE_LIST&gt;&lt;NODE NAME="NODE-1"/&gt;&lt;NODE NAME="NODE-2"/&gt;&lt;/NODE_LIST&gt;&lt;/CLUSTER_INFO&gt;&lt;/ORACLEHOME_INFO>

 

In this case, <ARU_ID_DESCRIPTION> tag give us the information we are looking for: Solaris Operating System (SPARC 64-bit). You can see in this case it is member of a cluster too.

HTH – Antonio NAVARRO

 

ORA-01034, ORA-27101 and HPUX-ia64 Errors

Today I was configuring an old RDBMS 10.2.0.3 in a HPUX. I have created a database and configured a listener, but when I was testing the network connection I get the next results;

For the tnsping, all was ok;

 

 
C:\XXX\BIN> tnsping XXX

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-DIC-2017 15:55:05

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Archivos de parßmetros utilizados:
C:\XXX\network\admin\sqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXX)(PORT = XXXXX)) (CONNECT_DATA = (SID = rdb1)))
Realizado correctamente (10 mseg)

For the sqlplus, I get the error;

 

 
C:\XXX\BIN> sqlplus XXX@XXX

SQL*Plus: Release 10.2.0.1.0 - Production on Lun Dic 18 15:55:13 2017

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca la contrase±a:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HPUX-ia64 Error: 2: No such file or directory

Maybe it can look a problem from Operating System. After a little research work I find the problem in the listener configuration, I was configured the SID_NAME in lowercases. I think moderm versions of Oracle on unix box don’t difference between uppercases or lowercases. In this case I have changed from lowercase and it resolved the problem.

HTH – Antonio NAVARRO

 

Failed: JDK version is incompatible Error

Today I was installing last PSU GRID/DB for 12.1.0.2, when hit the next error ;Today I was installing last PSU GRID/DB for 12.1.0.2, when hit the next error ;

root@wolf-3:~# opatchauto apply /data/ho/crs/anr/26635815 -oh /cloud/prod/server/121
OPatchauto session is initiated at Tue Dec 5 12:37:27 2017
System initialization log file is /cloud/prod/server/121/cfgtoollogs/opatchautodb/systemconfig2017-12-05_12-37-31PM.log.
Failed: JDK version is incompatible.Please use a jdk version the same as, or later than 16
The result of cluvfy command does not contain OVERALL_STATUS String.
OPATCHAUTO-72050: System instance creation failed.
OPATCHAUTO-72050: Failed while retrieving system information.
OPATCHAUTO-72050: Please check log file for more details.
OPatchauto session completed at Tue Dec 5 12:37:35 2017
Time taken to complete the session 0 minute, 9 seconds
Topology creation failed.

I haven’t clearly the error, but the problem is because of the path where I executed the opatchauto. In my case I moved to $ORACLE_HOME/OPatch.

root@wolf-3:~# cd /cloud/prod/server/121
root@wolf-3:/cloud/prod/server/121# cd OPatch
root@wolf-3:/cloud/prod/server/121/OPatch# ./opatchauto apply /data/ho/crs/anr/26635815 -oh /cloud/prod/server/121

Use a complete path for opatchauto solve the problem too.
HTH – Antonio NAVARRO