Category: Databases

A better understanding of the Oracle result cache

I had a flawed understanding of the result cache in that I felt it was used as a method to bypass an expensive access of the data blocks. In other words, Oracle expends resources to fetch a result set and then caches it for future users obviating the need to repeat the expensive data retrieval. A colleague questioned this and demonstrated that the actual methodology seems to be different. All of this was done on an 11GR2 instance.

What is the results cache?
It is an area in the shared pool that Oracle writes the results of the query. The goal is to avoid reading the data again and thus returning it faster to the user who runs the same SQL.

How do I control what goes into the results cache?
This is controlled via the RESULT_CACHE_MODE parameter. This parameter has two possible values

MANUAL – This is the default mode. The users are responsible for deciding what when to use the cache. This is controlled via the use of the /*+ result_cache */ hint.

FORCE – Oracle adds it to all possible statements

For the purpose of this write up, the RESULT_CACHE_MODE is set to MANUAL.

Assume that user TOM creates a table, adds data and then runs a select against that table. The first select will access the table and this can be confirmed by checking the execution plan. The second select of the table will also access the table. For the third execution of the select, add in the /*+ result_cache */ hint. Checking the execution plan will now reveal that the data was returned from the results cache.

Now switch users to BOB, set up a synonym pointing to TOM’s table and run the same SQL without a hint. This execution will access the table. Add in the hint and run the SQL and the data will be fetched from the cache.

V$RESULT_CACHE_OBJECTS displays all the objects (both cached results and dependencies) and their attributes. You can confirm that the same SQL used the results cache in response to the hint by checking the column SCAN_COUNT. This column is incremented every time the results cache is used to satisfy the SQL.

Invalidating the cache
If the data in the table is updated, the next execution of the SQL with the hint will not use the results cache. A new row will be added to the V$RESULT_CACHE_OBJECTS table and future executions of the SQL with the hint will cause the SCAN_COUNT to be incremented on this new row.

Issues with storing the password file in ASM under Oracle 12.1

We were copying the password file into ASM as this was supposed to allow us to have the password file in one location on a multi-node RAC database. In the past the practice has been to have the same version/copy of the password file was in the oracle home in each node of the RAC. Moving to a single password file on ASM seemed like a much better solution.

The odd result that we encountered was that when we would issue the below command at the asmcmd prompt

pwcopy --dbuniquename PRODDB /my_backup_location/pwdproddb +DG/proddb/password/pwdproddb

We would then list the files and see

+DG/ASM/password/pwdasm

We opened an SR with Oracle and were pointed to the below bug

Bug 19777340 - asmcmd pwcopy creates DB password file as incorrect fully qualified file name if destination is ASM diskgroup (Doc ID 19777340.8)

Per Oracle, the solution is to go to 12.2.01.

Application error causing MAX PROCESSES to be exceeded

We had a situation where an issue in the application was causing a large number of threads to be spawned from Apache/Tomcat. These threads would persist in an inactive status until we hit the max processes limit and then impact new threads.

As a quick fix, we changed the user’s profile to set the IDLE_TIME at 60 minutes. We did see the sessions being snipped after the IDLE_TIME threshold was reached:

USERNAME                       STATUS     COUNT(*)                                                  
------------------------------ -------- ----------                                                  
XYZ                            INACTIVE         10                                                  
PRDUSR                         ACTIVE            1                                                  
PRDUSR                         INACTIVE         44                                                  
PRDUSR                         SNIPED           20                                                  
SYS                            ACTIVE            1                                                  
SYS                            INACTIVE          1                                                  
                               ACTIVE           35

However, PMON was not actually killing the sessions resulting in us continuing to hit max processes. Oracle support suggested that this was due to “Bug 16494960 – killed session not being cleaned up (Doc ID 16494960.8)”.

Adding the keyword “immediate” resolved this as shown below:

alter system kill session '443,16740' immediate;

Since we tended to hit max processes in the early hours of the morning, I created the below script to monitor, report on and kill the sessions in an ‘INACTIVE’,’SNIPED’,’KILLED’ status:

#!/bin/ksh
cd /dba_scripts
export ORACLE_HOME=/oracle_home/12.1.0/
export PATH=$PATH:/oracle_home/12.1.0/bin
export ORACLE_SID=proddb1
sqlplus -s / as sysdba << EOF
   SET NEWPAGE NONE
   set linesize 100
   SET HEADING ON
   spool /dba_scripts/dean_info_before.txt
   select    username, status, count(*) 
   from      gv\$session 
   group by  username, status 
   order by  username, status
   ;   
   select    username, status, last_call_et, sid, serial# 
   from      v\$session 
   where     username = 'PRDUSR' 
     and     status in ('INACTIVE','SNIPED','KILLED') 
     and     last_call_et > 6000
   order by  sid, serial#  
   ;
   spool off
   SET HEADING OFF
   spool /dba_scripts/dean_kills.sql
   select    'alter system kill session '''||sid||','||serial#||''' immediate;' as script 
   from      v\$session 
   where     username='PRDUSR' 
     and     status in ('INACTIVE','SNIPED','KILLED') 
     and     last_call_et > 6000
   order by  sid, serial#  
   ;
   spool off
   SET HEADING ON
   spool /dba_scripts/dean_killed.log
   set echo on
   set feedback on
   @@/dba_scripts/dean_kills.sql
   spool off
   spool /dba_scripts/dean_info_after.txt
   select    username, status, count(*) 
   from      gv\$session 
   group by  username, status 
   order by  username, status
   ;   
   spool off
   exit
EOF
echo 'Status before kills'  > dean_report.txt
echo '-------------------' >> dean_report.txt
echo ' ' >> dean_report.txt
cat /dba_scripts/dean_info_before.txt >> dean_report.txt
 
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo 'Sessions identified for kills' >> dean_report.txt
echo '-----------------------------' >> dean_report.txt
echo ' ' >> dean_report.txt
cat /dba_scripts/dean_kills.sql >> dean_report.txt
 
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo 'Kill output' >> dean_report.txt
echo '-----------' >> dean_report.txt
echo ' ' >> dean_report.txt
cat /dba_scripts/dean_killed.log >> dean_report.txt
 
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo ' ' >> dean_report.txt
echo 'Status after kills' >> dean_report.txt
echo '-------------------' >> dean_report.txt
echo ' ' >> dean_report.txt
cat /dba_scripts/dean_info_after.txt >> dean_report.txt
 
mail -s "Kills from proddb1" "My-email-id@mydomain.com" < /dba_scripts/dean_report.txt

This was then scheduled in cron as follows:

#
## Oct 04, 2019 - Dean Capps - Start
##     This scripts kills threads to stay under the 900 processes limit
#   
00 00,12 * * * * cd /dba_scripts; ksh ./dean_kills.ksh > /dba_scripts/dean_kills.ksh.cron.out 2>&1
#
## Oct 04, 2019 - Dean Capps - End
#

The output will be as follows:

Status before kills
-------------------
 
USERNAME                       STATUS     COUNT(*)                                                 
------------------------------ -------- ----------                                                 
XYZ                            INACTIVE         10                                                 
PRDUSR                         ACTIVE            1                                                 
PRDUSR                         INACTIVE         44                                                 
PRDUSR                         SNIPED           20                                                 
SYS                            ACTIVE            1                                                 
SYS                            INACTIVE          1                                                 
                               ACTIVE           35                                                 
 
7 rows selected.
 
USERNAME                       STATUS   LAST_CALL_ET        SID    SERIAL#                         
------------------------------ -------- ------------ ---------- ----------                         
PRDUSR                         SNIPED           6199        443      16740                         
PRDUSR                         SNIPED           6199        556      37500                         
PRDUSR                         SNIPED           6199        615      17620                         
PRDUSR                         SNIPED           6199        670      46290                         
 
 
 
 
Sessions identified for kills
-----------------------------
 
alter system kill session '443,16740' immediate;                                                   
alter system kill session '556,37500' immediate;                                                   
alter system kill session '615,17620' immediate;                                                   
alter system kill session '670,46290' immediate;                                                   
 
 
 
 
Kill output
-----------
 
 
System altered.
 
 
System altered.
 
 
System altered.
 
 
System altered.
 
 
 
 
Status after kills
-------------------
 
USERNAME                       STATUS     COUNT(*)                                                 
------------------------------ -------- ----------                                                 
XYZ                            INACTIVE         10                                                 
PRDUSR                         ACTIVE            2                                                 
PRDUSR                         INACTIVE         43                                                 
PRDUSR                         KILLED            1                                                 
PRDUSR                         SNIPED           16                                                 
SYS                            ACTIVE            1                                                 
SYS                            INACTIVE          1                                                 
                               ACTIVE           35                                                 
 
8 rows selected.

Oracle Recommendation – Do not use Alter user SYS to change the sys password with 12c

While supporting a number of databases there is often the need to change the SYS and other user’s passwords to stay in compliance with good security practices. To make it easier to change the passwords for multiple users on multiple databases, I created a C# solution that reads in a list of databases and issues the below commands to change the SYS, SYSTEM and DBSNMP passwords:

alter user SYS    identified by a_new_password;
alter user SYSTEM identified by a_new_password;
alter user DBSNMP identified by a_new_password;

In 11g and below, my C# solution would run the alter command on every instance of a RAC. If the RAC had a physical standby (Data Guard), I would then FTP the password file from the $ORACLE_HOME/dbs directory on one of the primary nodes to each of the standby nodes.

In the case of 12c RAC environment, the password file is stored within ASM and shared with all the nodes of the RAC. I modified the C# solution to issue the alter user SYS on only one node. This seemed to work in many instances but we did encounter issues in some cases. A colleague pointed me to Oracle support document:

“Should ALTER USER Statement Be Used For Changing SYS Password? (Doc ID 2452286.1)”

The recommendation is:

From an Oracle Database directly, ‘alter user username identified by password’ statement works for all users. But for SYS user it is advised to use the oracle ORAPWD utility to change its password. Though alter user statement should also work, but it may cause future unforeseen issues as well.

Also goes on to state “You may have to bounce the database for this (recommended).”

This applies to version 12.1.0.1 to 12.2.0.1 [Release 12.1 to 12.2]

The downside of making ALTER SYSTEM commands with scope=spfile in a RAC environment

There are often occasions where I have to make changes to parameters in the database some of which can only be made with scope=spfile as opposed to scope=both. The intention is to make this change now and pick it up on the next scheduled bounce of the RAC. Recently, a colleague had made such a change and we were waiting for the next opportunity to bounce the RAC. Unfortunately, the server supporting node 4 of the RAC crashed and Cluster Services attempted to restart node 4 after the server was back up. At this point we encountered the below error:

[srvrnode4-OMZPOAW4] srvctl start instance -d RACDB -i RACDB4
PRCR-1013 : Failed to start resource ora.racdb.db
PRCR-1064 : Failed to start resource ora.racdb.db on node srvrnode4
CRS-5017: The resource action "ora.racdb.db start" encountered the following error: 
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name conversion parameters differ from other instance
. For details refer to "(:CLSN00107:)" in "/oracle/product/diag/crs/srvrnode4/crs/trace/crsd_oraagent_oracle.trc".

At this point, nodes 1-3 were running with a set of parameters in their memory which differed from the parameters that were in the spfile that node 4 was attempting to start with. We were then left with two options:

a. shutdown nodes 1-3 of the RAC and start all nodes together from the spfile

b. reset the spfile to the values that nodes 1-3 were running with and start node 4

Option a. was not possible as this would have impacted the users. Option b was a difficult choice because

01. we would undo the work we had done pending a bounce

02. since a few days had passed between the ALTER SYSTEM scope=spfile commands we were not 100% sure of the changes that had been made

We decided to back up the spfile to a pfile (with all the desired changes) and then start looking for difference between the pfile and the parameters in memory on nodes 1-3. For each of the differences, we issued an alter command with scope=spfile. When this was complete, we were able to start node 4 as the spfile now matched what was in the memory of nodes 1-3. Instead of going back to square one with the desired changes in the spfile pending a bounce, we changed the .profile to display the required commands as a reminder to issue them with the next bounce of the RAC.

REXX RC=1 SQLSTATE=01506 on DSNREXX PREPARE statement after migrating to DB2 12

A colleague had a REXX process in place that had been working with DB2 11. After the upgrade to DB2 12, the REXX exec started failing with RC=1 on a PREPARE statement. The SQL statement being prepared contained logic to generate the boundaries used in the predicates referencing a time stamp column in a table:

  FROM creator.table_name       
WHERE TIME_STAMP_COL BETWEEN             
      'EndTS' - 2 HOURS-01 MONTHS  
  AND 'EndTS' + 2 HOURS-01 MONTHS  
   OR TIME_STAMP_COL BETWEEN             
      'EndTS' - 2 HOURS-02 MONTHS  
  AND 'EndTS' + 2 HOURS-02 MONTHS  
   OR TIME_STAMP_COL BETWEEN             
      'EndTS' - 2 HOURS-03 MONTHS  
  AND 'EndTS' + 2 HOURS-03 MONTHS  
   OR TIME_STAMP_COL BETWEEN             
      'EndTS' - 2 HOURS-04 MONTHS  
  AND 'EndTS' + 2 HOURS-04 MONTHS  
GROUP BY DATE(TIME_STAMP_COL)

The REXX code PREPAREs this statement with

address DSNREXX "EXECSQL PREPARE S40 FROM :SQL"

In DB2 11, this would return RC=0. After the upgrade to DB2 12, this started returning RC=1. The SQLCA contained:

SQLCODE = 0
SQLERRD.1 = 0
SQLERRD.2 = 0
SQLERRD.3 = 999
SQLERRD.4 = 1142028335
SQLERRD.5 = 0
SQLERRD.6 = 0
SQLWARN.1 =
SQLWARN.2 =
SQLWARN.3 =
SQLWARN.4 = 
SQLWARN.5 =           
SQLWARN.6 = W
SQLWARN.7 =  
SQLWARN.8 =
SQLWARN.9 =
SQLWARN.10 =          
SQLERRP = DSN
SQLSTATE = 01506 <---
SQLERRMC =

Per IBM

01506 = An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation.

The issue was caused because the SQL would generate an invalid date for February. In the past, the PREPARE would have ignored this.

Removing data vault from an Oracle 12c database

We had a change in requirements that necessitated the removal of data vault from a 12c database. Initially, the documentation indicated that this was not possible. However, a colleague provided the solution. The steps are:

As SYS run:

EXEC DBMS_MACADM.DISABLE_DV;

Output should be:

PL/SQL procedure successfully completed.

Now bounce the database. After the bounce, as SYS run 

alter session set recyclebin=off;

Followed by:

@?/rdbms/admin/dvremov.sql

SQL*Loader-704/ORA-12154 errors

A user was attempting to run SQLLDR with the below command:

sqlldr sys@DBUAT control=/db1/DB_UAT_data.ctl log=/db1/DB_UAT_data.log

The above command prompted for the SYS password and then displayed the below error message:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified 
11:22 AM

TNSPING to the DBUAT database were successful.

The issue was caused by the SYS password containing the “@” symbol. A quick workaround was to change the command to:

sqlldr \'/ as sysdba \' control=/db1/DB_UAT_data.ctl log=/db1/DB_UAT_data.log

ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037, Linux-x86_64 Error: 2: No such file or directory on IMPDP

I recently stepped in for another Database Base Administrator who was no longer with the group. The project being supported was in the middle of migrating from a non-RAC environment to a 4 node RAC environment. The move was designed to be accomplished via Expdp, FTP and then an Impdp. I typically perform this by separating the schemas from the data as this allows me a moment to check that all the schema objects migrated successfully before performing the usually larger data migration.

In this particular case, the schemas imported successfully. When I started the data import I got the below errors:

Import: Release 11.2.0.4.0 - Production on Mon Feb 25 12:49:46 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
Master table "SYS"."DEAN_IMPDP_DATA" successfully loaded/unloaded
Starting "SYS"."DEAN_IMPDP_DATA":  "/******** AS SYSDBA" parfile=dean_impdp_data.par 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "TABLE_OWNER"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "/data_pump/dean_expdp_data.dmp" for read
ORA-19505: failed to identify file "/data_pump/dean_expdp_data.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

What was further misleading was that some of the tables reported the above error while others loaded.

The file /data_pump/dean_expdp_data.dmp existed and had the required permissions.

The root cause of the error turned out to be that the data was exported from a single instance server and the import was being run on a RAC. The import operations for some of the tables were being tasked to the other nodes of the RAC that did not have access to the /data_pump/dean_expdp_data.dmp file and hence the “ORA-27037: unable to obtain file status“ errors.

I changed the import’s par file to add “CLUSTER=NO” and the issue was resolved.

Creating a database link on behalf of another user

I had to create a database link on behalf of another user whose password I did not know. This was performed as the “SYS” user in an Oracle 11.2.0.4.0 environment.

The requirements were as follows:
a. User test_user would connect to test_db
b. Execute an SQL over a db link as uat_user on uat_db
c. Database link should be named test_2_uat
d. uat_users password on uat_db was known. Assume it is mypassword
e. the tnsnames file has an entry for the uat_db named uat_db and is verified by
tnsping uat_db

Steps:
01. Log on to test_db as SYS user

02. Create the below procedure:

CREATE or replace PROCEDURE test_user.DEAN_DB_LINK AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE DATABASE LINK test_2_uat '
       ||'CONNECT TO UAT_USER IDENTIFIED BY "mypassword" '
       ||'USING ''uat_db''';
END DEAN_DB_LINK;
/

03. After the above is created grant privilege to test_user to create a link

GRANT CREATE DATABASE LINK TO TEST_USER;

04. Execute the above procedure (as SYS)

EXECUTE TEST_USER.DEAN_DB_LINK;

05. Check that the link was created 

SELECT * FROM DBA_DB_LINKS;

06. Revoke the privilege from test_user

REVOKE CREATE DATABASE LINK  FROM TEST_USER;

Update – March 21, 2019

A friend reminded me to cleanup the procedure:

drop PROCEDURE test_user.DEAN_DB_LINK;

This is based on the information at:
How to Create a Database Link in Another User’s Schema