Executing sql on all Exadata nodes. 22 September 2011
Posted by David Alejo Marcos in Exadata, SQL - PL/SQL.Tags: Exadata, SQL - PL/SQL
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.
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: Exadata, Oracle 11.2, RAC, SQL, SQL - PL/SQL
4 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.
