jump to navigation

Executing sql on all Exadata nodes. 22 September 2011

Posted by David Alejo Marcos in Exadata, SQL - PL/SQL.
Tags: ,
add a comment

From time to time, I have to run scripts or single commands on all nodes for Exadata. This can take some time.

The problem:

We have a request from our developers to flush the shared pool on all nodes on our UAT Exadata. This is due to a bug we are still experiencing.

The solution:

This is a typical request for my team, were we have to run something on all our nodes. Flushing shared pool can be one of them.

Connecting and executing the same command 8 times, if you have a full rack, can be time-consuming and it is, probably, not the most exciting task to do.

For this reason I came up with the following script. It is something to help me with, so please, test it before using on production.

#!/bin/ksh

###############################
##
## Script to execute sql commands on all Exadata nodes
##
## Owner: David Alejo-Marcos
##
## usage: Exadata_sql_exec.ksh
## i.e.: Exadata_sql_exec.ksh MYTESTDB flush_shared_pool.sql
##
## Example of test.sql:
##
## oracle@hostname1 (MYTESTDB1)$ cat test.sql
## select sysdate from dual;
## select 'this is my test' from dual;
##
## alter system switch logfile;
##
###############################

NO_OF_ARGS=$#

typeset -u DB_SRC=$1
typeset -l DB_SCRIPT1=$2
typeset -l DB_SCRIPT=$2_tmp

if [[ "$NO_OF_ARGS" = "2" ]]; then
echo "Parameters passed: OK"
else
echo "usage: Exadata_sql_exec.ksh"
echo "i.e.: Exadata_sql_exec.ksh MYTESTDB flush_shared_pool.sql"
exit 1
fi
echo "sqlplus -s '/as sysdba'" >/tmp/${DB_SCRIPT}
echo "set linesize 500" >>/tmp/${DB_SCRIPT}
echo "set serveroutput on" >>/tmp/${DB_SCRIPT}
echo "select host_name, instance_name from v\\\$instance;" >>/tmp/${DB_SCRIPT}
echo "@/tmp/${DB_SCRIPT1}" >>/tmp/${DB_SCRIPT}
echo "exit;" >>/tmp/${DB_SCRIPT}
echo "EOF" >>/tmp/${DB_SCRIPT}

# Copy both scripts to all Exadata DB Servers.
dcli -l oracle -g ~/dbs_group -f /tmp/${DB_SCRIPT} -d /tmp
dcli -l oracle -g ~/dbs_group -f ${DB_SCRIPT1} -d /tmp

# Execute command on all Exadata DB
dcli -l oracle -g ~/dbs_group " (chmod 700 /tmp/${DB_SCRIPT} ; /tmp/${DB_SCRIPT} ${DB_SRC} ; rm /tmp/${DB_SCRIPT} ; rm /tmp/${DB_SCRIPT1})"

The sql file to be executed can be anything you would normally run from sqlplus.

Please, bear in mind this script will be executed as many times as nodes you have on your Exadata, so if you create a table of you perform inserts, it will be done 8 times…

Below is an output from a very simple test:

test.sql:

oracle@hostname1 (MYTESTDB1)$ cat test.sql
select sysdate from dual;
select 'this is my test' "ramdom_text" from dual;
alter system switch logfile;

Output:

oracle@hostname1 (MYTESTDB1)$ ./Exadata_sql_exec.ksh MYTESTDB test.sql
Parameters passed: OK
hostname1:
hostname1: Connect to instance MYTESTDB1 on hostname1
hostname1: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname1:
hostname1:
hostname1: HOST_NAME INSTANCE_NAME
hostname1: --------- ----------------
hostname1: hostname1 MYTESTDB1
hostname1:
hostname1:
hostname1: SYSDATE
hostname1: ---------
hostname1: 22-SEP-11
hostname1:
hostname1:
hostname1: ramdom_text
hostname1: ---------------
hostname1: this is my test
hostname1:
hostname1:
hostname1: System altered.
hostname1:
hostname2:
hostname2: Connect to instance MYTESTDB2 on hostname2
hostname2: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname2:
hostname2:
hostname2: HOST_NAME INSTANCE_NAME
hostname2: --------- ----------------
hostname2: hostname2 MYTESTDB2
hostname2:
hostname2:
hostname2: SYSDATE
hostname2: ---------
hostname2: 22-SEP-11
hostname2:
hostname2:
hostname2: ramdom_text
hostname2: ---------------
hostname2: this is my test
hostname2:
hostname2:
hostname2: System altered.
hostname2:
hostname3:
hostname3: Connect to instance MYTESTDB3 on hostname3
hostname3: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname3:
hostname3:
hostname3: HOST_NAME INSTANCE_NAME
hostname3: --------- ----------------
hostname3: hostname3 MYTESTDB3
hostname3:
hostname3:
hostname3: SYSDATE
hostname3: ---------
hostname3: 22-SEP-11
hostname3:
hostname3:
hostname3: ramdom_text
hostname3: ---------------
hostname3: this is my test
hostname3:
hostname3:
hostname3: System altered.
hostname3:
hostname4:
hostname4: Connect to instance MYTESTDB4 on hostname4
hostname4: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname4:
hostname4:
hostname4: HOST_NAME INSTANCE_NAME
hostname4: --------- ----------------
hostname4: hostname4 MYTESTDB4
hostname4:
hostname4:
hostname4: SYSDATE
hostname4: ---------
hostname4: 22-SEP-11
hostname4:
hostname4:
hostname4: ramdom_text
hostname4: ---------------
hostname4: this is my test
hostname4:
hostname4:
hostname4: System altered.
hostname4:
hostname5:
hostname5: Connect to instance MYTESTDB5 on hostname5
hostname5: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname5:
hostname5:
hostname5: HOST_NAME INSTANCE_NAME
hostname5: --------- ----------------
hostname5: hostname5 MYTESTDB5
hostname5:
hostname5:
hostname5: SYSDATE
hostname5: ---------
hostname5: 22-SEP-11
hostname5:
hostname5:
hostname5: ramdom_text
hostname5: ---------------
hostname5: this is my test
hostname5:
hostname5:
hostname5: System altered.
hostname5:
hostname6:
hostname6: Connect to instance MYTESTDB6 on hostname6
hostname6: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname6:
hostname6:
hostname6: HOST_NAME INSTANCE_NAME
hostname6: --------- ----------------
hostname6: hostname6 MYTESTDB6
hostname6:
hostname6:
hostname6: SYSDATE
hostname6: ---------
hostname6: 22-SEP-11
hostname6:
hostname6:
hostname6: ramdom_text
hostname6: ---------------
hostname6: this is my test
hostname6:
hostname6:
hostname6: System altered.
hostname6:
hostname7:
hostname7: Connect to instance MYTESTDB7 on hostname7
hostname7: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname7:
hostname7:
hostname7: HOST_NAME INSTANCE_NAME
hostname7: --------- ----------------
hostname7: hostname7 MYTESTDB7
hostname7:
hostname7:
hostname7: SYSDATE
hostname7: ---------
hostname7: 22-SEP-11
hostname7:
hostname7:
hostname7: ramdom_text
hostname7: ---------------
hostname7: this is my test
hostname7:
hostname7:
hostname7: System altered.
hostname7:
hostname8:
hostname8: Connect to instance MYTESTDB8 on hostname8
hostname8: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname8:
hostname8:
hostname8: HOST_NAME INSTANCE_NAME
hostname8: --------- ----------------
hostname8: hostname8 MYTESTDB8
hostname8:
hostname8:
hostname8: SYSDATE
hostname8: ---------
hostname8: 22-SEP-11
hostname8:
hostname8:
hostname8: ramdom_text
hostname8: ---------------
hostname8: this is my test
hostname8:
hostname8:
hostname8: System altered.
hostname8:

I have been told many times to do not use quotes on the following command:

sqlplus '/as sysdba'

but:

1.- I am too used to it
2.- you only save 2 keystrokes at the end of the day 🙂

As always, comments are welcome.

David Alejo-Marcos.

David Marcos Consulting Ltd.

Advertisements

How to list files on a directory from Oracle Database. 13 September 2011

Posted by David Alejo Marcos in Exadata, Oracle 11.2, RAC, SQL - PL/SQL.
Tags: , , , ,
5 comments

Couple of days ago I had an interesting request, “How can I see the contents of nfs_dir”?

The problem:

We were using DBFS to store our exports. This was the perfect solution as the business could “see” the files on the destination folder, but it did not meet our requirements performance wise on our Exadata.

We have decided to mount NFS and performance did improve, but we had a different problem. NFS is mounted on the database server and business do not have access for security reasons and segregation of duties.

Since then, the export jobs run, but business could not “see” what files were created, so the question was asked.

The solution:

After some research I came across with the following package:

SYS.DBMS_BACKUP_RESTORE.searchFiles

I did have to play a little bit, and I finished with the following script:
1.- Create an Oracle type

create type file_array as table of varchar2(100)
/

2.- Create the function as SYS:

CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null)
RETURN file_array pipelined AS

lv_pattern VARCHAR2(1024);
lv_ns VARCHAR2(1024);

BEGIN

SELECT directory_path
INTO lv_pattern
FROM dba_directories
WHERE directory_name = 'NFS_DIR';

SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns);

FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
FROM X$KRBMSFT
WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
PIPE ROW(file_list.file_name);
END LOOP;

END;
/

3.- Grant necessary permissions:

grant execute on LIST_FILES to public;
create public synonym list_files for sys.LIST_FILES;

4.- Test without WHERE clause:

TESTDB> select * from table(list_files);
COLUMN_VALUE
----------------------------------------------------------------------------------------------------
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB.log
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece2.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece3.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece4.dmp
/nfs/oracle/TESTDB/pump_dir/imdp_piece_TESTDB_09092011.log
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp_old
/nfs/oracle/TESTDB/pump_dir/imdp_piece_TESTDB_12092011.log

8 rows selected.

Elapsed: 00:00:00.10

5.- Test with WHERE clause:

TESTDB> select * from table(list_files) where column_value like '%dmp%';
COLUMN_VALUE
----------------------------------------------------------------------------------------------------
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece2.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece3.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece4.dmp
/nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp_old

Elapsed: 00:00:00.12

As always, comments are welcome.

David Alejo-Marcos.

David Marcos Consulting Ltd.