Category: Oracle

I had the below input file and a script whose logic was to process each line of the input file. Within the logic, I needed to perform an OS command such as the “more /etc/oratab” command below.

Contents on the input file:

[server_name] more temp.txt
a
b
c

Code

#! /bin/bash
function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done < temp.txt

I was expecting three lines of output; one for each line in the input file. However, the output was:

[server_name] ./temp.ksh
a
***
[server_name] 

Basically processing stops after the first line of the input file. The issue is that the “more /etc/oratab” is overlaying the standard input/output of the while command. After I changed the code to:

#! /bin/bash
function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r –u3 service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done 3< temp.txt 

Now the output displays that all lines of the input file are processed:

[server_name] ./temp.ksh
a
***
b
***
c
***
[dt2ompd2-splunk2]

#! /bin/bash
clear

function perform_logic
{
  echo '***'
  temp=`more /etc/oratab | grep -i dean | wc -l`
}
  
while IFS=',' read -r -u3 service lsnr port
#while IFS=',' read -r service lsnr port
do
   echo $service $lsnr $port 
   perform_logic
done 3< temp.txt 
#done < temp.txt

[dt2ompd2-splunk2] ./temp.ksh
a
***
[server_name]

An offshore user contacted me today about an ongoing issue. Apparently the insert statements that his process was performing were disappearing from the table. We stepped through his java code and the code looked fine; the insert was occurring and was successful, no exceptions etc.

We ran a trace on the session and see the insert being executed.

TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 26 11:52:46 2020
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: PRDA11_ora_8025.trc
Sort options: default
 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
SQL ID: dgya487q14n43 Plan Hash: 0
 
insert into ACCTOWN.ACC_CUST (ACCOUNT_NUMBER, STATUS, ACTIVATE_DATE, 
  CUST_ID) 
values
 (:1, :2, :3, :4)

However, the row was missing from the table.

My suspicion was that someone or some process was deleting the data from the table. In order to confirm this, I turned on auditing on the table:

AUDIT all ON ACCTOWN.ACC_CUST by ACCESS;

Auditing this database is set to write to the database.

show parameter audit;
audit_trail          string  DB

At this point I asked the user to run the process again and then checked the audit table:

alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select    os_username, username, userhost, 
          timestamp, owner,obj_name,action_name
from      dba_audit_trail
where     obj_name = 'ACC_CUST'
  and     action_name not in ('SELECT','SESSION','SESSION REC')
order by  timestamp
;
 

And I was able to confirm that a different process from the same application was deleting the data:

os_username username userhost timestamp          owner   obj_name action_name
ACCTOWN     ACCTOWN  srsr012  26-FEB-20 10.08.42 ACCTOWN ACC_CUST INSERT
kundjo4     ACCTOWN  srvr012  26-FEB-20 10.08.46 ACCTOWN ACC_CUST DELETE

After this was confirmed, turn off auditing on the table:

NOAUDIT ALL ON ACCTOWN.ACC_CUST;

We are currently in the process of migrating our applications from DB2 LUW to Oracle and needed a method to do this without any investment in third party software or tools. After some experimentation, we settled on SQL Developer as the free tool of choice. I was going to write up the approach; however, my colleague Praveen Somisetty went above and beyond and created an excellent video explaining the process on his YouTube channel. The video can be viewed at:

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.

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.

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]

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.

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

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