Durham Oracle

Split Brain

I won't spend too much time on this as it has been covered in detail elsewhere.

Oracle Technetwork

The Geek Diary - Split Brain

If you are using clustering, especially with problematic networking just be careful that nodes don't start acting independently of each other. Nodes taking over the others as though they have failed when in fact the problem is the interconnectivity. It is not something many DBAs will see and probably never will.

So if someone mention "split brain" don't worry it is not a monster movie, just an Oracle clustering problem.

Dump a table to CSV

csv procedure dump_table_to_csv

One thing that Oracle does not do very well is CSV exports.

Used this procedure loads. Really handy.

You need to have DUMP_DIR set up as an Oracle directory with the correct permissions.

create or replace procedure dump_table_to_csv

( p_tname in varchar2,p_dir in varchar2, p_filename in varchar2 )

is

l_output utl_file.file_type;

l_theCursor integer default dbms_sql.open_cursor;

l_columnValue varchar2(4000);

l_status integer;

l_query varchar2(1000) default 'select * from ' || p_tname;

l_colCnt number := 0;

l_separator varchar2(1);

l_descTbl dbms_sql.desc_tab;

begin

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''';

dbms_sql.parse(l_theCursor,l_query,dbms_sql.native);

dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);

for i in 1 .. l_colCnt loop

utl_file.put(l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );

dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000 );

l_separator := ',';

end loop;

utl_file.new_line(l_output);

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

l_separator := '';

for i in 1 .. l_colCnt loop

dbms_sql.column_value(l_theCursor, i, l_columnValue );

utl_file.put(l_output, l_separator || '"' || l_columnValue || '"' );

l_separator := ',';

end loop;

utl_file.new_line(l_output );

end loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose(l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

exception

when others then

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

raise;

end;

SQL> exec dump_table_to_csv('ISMA.ORGANISATION','DUMP_DIR','organisation_$NOW.csv');

Hit this issue :

SQL> exec dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv');
BEGIN dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.DUMP_TABLE_TO_CSV", line 39
ORA-06512: at line 1

To fix :

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

l_output := utl_file.fopen( p_dir, p_filename, 'w',5000 );

but check space too as sometiumes the filesystem can fill and give the same error.

RMAN duplicate

Example RMAN duplicate quick instructions.

Ensure you have a successfully completed full rman backup.

We were going from ASM to non ASM so followed this

https://mohamedazar.com/2010/12/17/duplicate-database-from-asm-to-non-asm/

or use the official Oracle website.

In the init.ora file you will need.

db_file_name_convert=’+DATA’,’/prod1/db/apps_st/’
log_file_name_convert =("+FRA","/prod1/db/apps/onlinelog/")

I created a skeletion init_dup file.

Start up Auxiliary instance in NOMOUNT state

Note that the Oracle SID is set to database you want to copy into i.e. the one for prod1 –

you just need a basic skeleton init file to start the database in mount

SQL> startup nomount pfile=/xxxx/dbs/initPROD1_dup

ORACLE instance started.

Run the rman script to duplicate the database. The rman duplicate does all the hard work.

Set the environment and run:

$ rman auxiliary / cmdfile=commands.txt

Where commands.txt is :

set echo on;

run {
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk ;
allocate auxiliary channel d4 type disk ;

duplicate database to PROD1 backup location '/backups/masking/';

release channel d1;
release channel d2;
release channel d3;
release channel d4 ;
}

Startup / shutdown the database.

Run some integrity checks.