I needed to create a large amount of test data (in table dean_table) from an existing table (prod_table). The tables were clones of each other and had unique index on TIME_STAMP and PHYSICAL_ID. The prod_table had 15k rows of data but I needed a few million for my test case. The first time I ran Continue reading “SQL to create data from a table”
Month: December 2016
Flashback experience on a RAC with a physical standby RAC
The below MS Word Document contains the steps required to set up Flashback on a primary and standby RAC.
Relocating GoldenGate to a different node
If you are running GoldenGate in a RAC environment, there may be occasions where you have to relocate to a different node. An example of this may be an OS upgrade that is being rolled across the RAC nodes one at a time. The below MS Word Document contains the steps to relocate the GoldenGate process.
Some Unix commands to check the performance of a server
Sends the first 25 lines of the top output to a file in batch mode. The “-n 1” runs the top command one time
top -b -n 1 | head -25 > top_data.txt
The mpstat command display activities for each available processor, Continue reading “Some Unix commands to check the performance of a server”
Autoclose file after reading
The below code snippet demonstrates how to open a file, read line by line and auto close the file:
with open('file.txt') as the_file:
for line in the_file:
print(line.rstrip())
Calling a function with multiple arguments
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')
Skipping a transaction in GoldenGate
The below MS Word Document contains the steps to skip a GoldenGate transaction.
ORA Skipping transaction in GG
PL/SQL example to create a list of tables and execute a dynamic SQL on each of the tables
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
Simple example of array variables
#!/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
Bad filename convert on a physical standby
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