Category Archives: Unix

Find Replace

I want to do a simple find replace of a string in one line for all the files in a Unix directory

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'

Oracle Montreal find replace Unix string

MassExecuteSQL

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

crontab examples

* * * * * 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/

oracle.xml

###########################################################
# 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
#
###########################################################
 





  
    
    

    
        
    
	
    
      
        
      
    

    
      
        
      
    

   

Move db to another host (scp, rename)

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