Experiences of a DBA
The below code snippet demonstrates how you can call a Python function with multiple arguments:
def say_hi (first, last):
print('Hi {} {}!'.format(name))
say_hi('Jane','Doe')
The above line of code is calling with positional parameters. The first parameter is associated with first variable and so on. The function can also be called as
say_hi(last = 'Doe', first = 'Jane')
The below MS Word Document contains the steps to skip a GoldenGate transaction.
ORA Skipping transaction in GG
This PL/SQL code identifies a list of tables that exist in a schema. For each table, it issues a count(*) dynamically.
set serveroutput on;
spool get_all_tables
declare
dyn_sql varchar(300);
count_x number (10);
temp_tab_name char(40);
begin
for t in(select owner, substr(table_name,01,40) as table_name from dba_tables where owner = '' order by table_name)
loop
temp_tab_name := t.table_name;
dyn_sql := 'select count(*) from ' || t.owner || '.' || t.table_name;
execute immediate dyn_sql into count_x;
dbms_output.put_line(t.owner || ' ' || temp_tab_name || ' = ' || count_x || ' rows');
end loop;
end;
/
spool off
#!/bin/ksh
ary_ndx=1
ary_val[$ary_ndx]="First"
ary_ndx=2
ary_val[$ary_ndx]="Second"
a=0
while [ $a -le $ary_ndx ]
do
echo ${ary_val[$a]}
a=`expr $a + 1`
echo $a
done
If a data file is added on a primary with a physical standby and there is no appropriate file name convert, the file gets created in the Oracle home. On a RAC this may also report as a certain node unable to lock a file as the file got created in the Oracle home (non ASM) of a different node.
Log on to the standby
Stop mrp
Set standby_file_management = ‘MANUAL’
Find the name of the “bad file”. In my case it was /oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034
You can find the bad file with:
select name from v$datafile where not name like '+
Find the correct name of the file from the primary.
The file will not actually exist in /opt/oracle. Then
alter database create datafile '/oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034' as '+ASM mount point/usually DB name/datafile/correct name of file from primary';
The goal is to create the file in the correct location as it would have been created if the correct file name convert was in place.
Check v$datafile again
Set standby_file_management = ‘AUTO’
Start MRP
There are times where a particular SQL statement gets a poor access path and needs to be removed from the cache. Instead of flushing the entire cache, I used this method:
Identify the address and SQL hash value of the problem SQL: Continue reading “Remove a particular SQL access path from the cache”
The GoldenGate data pump was abending on the source:
GGSCI (uiiompd17) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT ABENDED DPUMPxx 00:00:00 04:00:29 EXTRACT RUNNING EXAxxxx 00:00:00 00:00:02
The GoldenGate error log contained the below message:
2016-12-17 07:22:13 ERROR OGG-01031 Oracle GoldenGate Capture for Oracle, dpumprp.prm: There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "/ggs//dirdat/rp013263" (error 11, Resource temporarily unavailable)).
The file directory that was being reported was for the target of the GoldenGate process. On the target side, the file did exist and there were no issues with the file system, mount point etc. I moved the file as follows:
mv /ggs//dirdat/rp013263 /ggs/ /dirdat/rp013263.old
I then restarted the abended data pump and normal processing was resumed.
One of our applications began to experience ORA-02049: timeout: distributed transaction waiting for lock errors during their processing. The application was connecting via a service name to a four node RAC cluster. Continue reading “Mysterious contention!”
os.environ is a useful way to set an environment variable in the shell as shown below:
#!/usr/bin/python
import pdb
import os
import sys
import subprocess
import commands
import time
import socket
import locale
locale.setlocale(locale.LC_ALL, 'en_US')
team_name = 'Delta Team'
msg_line = 'The '+team_name+' is executing this script.'
os.environ['msg_line'] = msg_line
os.system('echo "${msg_line}" | mail -s "Email subject goes here" "Dean.Capps@somecompany.com"')
(I may have extra imports as this was taken from a much larger script.)
I had a requirement to identify the actual user account and full name of the current user who was currently “Sued” to a higher privileged user account such as Oracle. The below script provides me with that functionality:
#!/usr/bin/python
import pdb
import os
import sys
import subprocess
import commands
import time
import socket
import locale
locale.setlocale(locale.LC_ALL, 'en_US')
user_name = os.popen("who am i| awk '{print $1}'").read()
user_name=user_name.rstrip('\n')
print user_name
os.environ['py_user_name'] = user_name
user_name = os.popen("cat /etc/passwd | grep ${py_user_name} | awk -F \":\" '{print $5}'").read()
user_name=user_name.rstrip('\n')
print user_name
(I may have extra imports as this was taken from a much larger script.)