Useful when the Oracle DBA has to update a lots of SQL or shell scripts.
Example of a find replace: ~/sql directory has thousands of SQL files and I’d like to find out my_source_string and replace it with my_target_string:
find my_folder -type f | xargs perl -pi -e 's/my_source_string/my_target_string/g'
I have some standard SQL that I want to run against multiple databases on a single server to help me report informations or diagnose problems.
This script intended for Oracle DBA, executes an sql statement on all Oracle 18c databases running on a Unix server.
First example: On all databases.
Second example: On a list of ORACLE_SID
Let’s do that!
-- ################################################
-- # Creator: Cyrille Modiano (DBA Oracle
-- # Created: 2011/09/08 Last Validation: sept. 2018
-- # Name: MassExecuteSQL
-- ################################################
-- #
-- # Compatible: Oracle 18c, 12c, 11g, 10g, 8i, 9i
-- #
-- ################################################
for base in `ps -ef | grep pmon | grep -v grep | awk -F_ '{print $3}'`
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo " " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done
# With a list of databases
for base in BASE1 BASE2 BASE3 BASE4 BASE5
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo " " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done
* * * * * command to be executed
– – – – –
| | | | |
| | | | +—– day of week (0 – 6) (Sunday=0)
| | | +———- month (1 – 12)
| | +————— day of month (1 – 31)
| +——————– hour (0 – 23)
+————————- min (0 – 59)
Examples for Linux, Solaris, Ubuntu:
# Export everyday at 8:pm
0 20 * * * /…/export_db.sh 1>/dev/null 2>&1
# Export monday to saturday at 01:am
0 1 * * 1-6 /…/export_db.sh 1>/dev/null 2>&1
# Automatique restart of oem agent at 07:00am 10:am 01:pm 03:pm
0 07,10,13,15 * * * /…/agent_restart.sh
#Refresh once a month
30 22 1 * * /…/refresh.sh> /tmp/refesh.log
# Run a script every 10 minutes and between 8am and 10pm (On Linux you can use */10 notation instead of 0, 10, 20…)
0,10,20,30,40,50 8-22 * * * /…/check_db.ksh 1>/dev/null 2>&1
If you want to generate your own cron job, check this useful tool:
http://www.generateit.net/cron-job/
########################################################### # Creator: Cyrille Modiano # Created: 2011/24/02 # Compatible: Solaris update 6 and higher ########################################################### # # An example of SMF file: Service Management Facility # Replacement of /etc/init.d/oracle file for Solaris 10 Update 6 and higher # Folder: /var/svc/manifest/application/database # Variables to change: user, exec # ###########################################################
REM ################################################ REM # Creator: Didier Vince REM # Created: 2009/10/01 REM # Name: MoveDB.sql REM ################################################ REM # REM # Compatible: Oracle 9i 10g 11g REM # REM ################################################ REM # REM # Move DB to a new machine REM # 1: creates scp commands for all the db files (but no init, spfile) REM # 2: renames files to our standard REM # REM ################################################ define newmachine="&1" SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER OFF undefine direPos dataPos redoPos miroPos col direPos new_value direPos set termout off select instr(b.name,'/',-1,1) + 1 as direPos from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.name='SYSTEM' / define dataPos=&direPos define redoPos=&direPos+8 define miroPos=&direPos+7 undefine iname col iname new_value iname select instance_name as iname from v$instance / set lines 254 pages 0 spool Copy&iname.FilesTo&newmachine..sh prompt exit select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./datafile/' || substr(name,&dataPos.) from v$datafile order by ts# / select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./work/' || substr(name,&dataPos.) from v$tempfile / select 'scp ' || member || ' oradba@&newmachine.:/oracle/bases/&iname./rlm1/' from v$logfile where member like '%/redolog/%' / select 'scp ' || member || ' oradba@&newmachine.:/oracle/bases/&iname./rlm2/' from v$logfile where member not like '%/redolog/%' / select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./controlfile/' from v$controlfile / select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./rlm1/' from v$controlfile / select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./rlm2/' from v$controlfile / spool off spool Rename&iname.Files.sql prompt exit select 'Alter database rename file ''' || name || ''' to ''/oracle/bases/&iname./datafile/' || substr(name,&dataPos.) || ''';' from v$datafile order by ts# / select 'Alter database rename file ''' || name || ''' to ''/oracle/bases/&iname./work/' || substr(name,&dataPos.) || ''';' from v$tempfile / select 'Alter database rename file ''' || member || ''' to ''/oracle/bases/&iname./rlm1/' || substr(member,&redoPos.) || ''';' from v$logfile where member like '%/redolog/%' / select 'Alter database rename file ''' || member || ''' to ''/oracle/bases/&iname./rlm2/' || substr(member,&dataPos.) || ''';' from v$logfile where member not like '%/redolog/%' / spool off quit