The following Perl command will change a string in every file of the present directory.
NOTE: Remove the <> symbols.
[old_string] = String you want replaced.
[new_string] = New string.
perl -p -i -e “s/[old_string]/[new_string]/g” *
Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com
The Oracle RDBMS produces a significant number of trace and log files, which record current status and other information related to the condition of the database system. Part of the administration of an ORACLE RDBMS is to remove these files from the OS layer once they become obsolete. This article covers the creation of shell script to remove such files from the Oracle database server in a UNIX or LINUX environment.
1. Logon to your Oracle database server as the Oracle software owner.
2. Logon to SQLPLUS with SYSDBA privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Dec 15 18:08:39 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. Use the SQLPLUS command show to see the location of your
background_dump_dest directory.
SQL> show parameter background_dump_dest
NAME TYPE VALUE
———————————— ———– ——————–
background_dump_dest string /orcl/admin/orcl/bdump
SQL>
4. Exit out of SQLPLUS.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>
5. Change directories to the location of you back
mylinux:> cd /orcl/admin/orcl/bdump
mylinux:>
6. Execute the following statement to see how many trace files exist greater than 3 days old.
find . -name “*trc” -mtime +3 -exec ls -lrt {} \;
mylinux:> find . -name “*trc” -mtime +3 -exec ls -lrt {} \;
…
…
…
-rw-r—– 1 oracle dba 943 Jul 12 13:00
./orcl_m001_11864.trc
-rw-r—– 1 oracle dba 811 Jul 12 23:00
./orcl_m001_17140.trc
-rw-r—– 1 oracle dba 897 Jul 13 13:00
./orcl_m001_7152.trc
-rw-r—– 1 oracle dba 789 Jul 13 23:00
./orcl_m001_29058.trc
mylinux:>
7. Execute the following command to remove all file which are older than 3 days: find . -name “*trc” -mtime +3 -exec rm {} \;
mylinux:> find . -name “*trc” -mtime +3 -exec rm {} \;
mylinux:>
8. This shell command can be placed in an executable shell script and executed through CRON to automatically delete files older then three days. Note: The example below will remove trace files from your current directory ending in trc. You will have to replace the find “.†with find “directory_structure†to remove files in a specific directory.
Example: You wish to remove all files older then 3 days in directory
/opt/oracle/db_1/bdump the command would be.
find /opt/oracle/db_1/bdump -name “*trc” -mtime +3 -exec rm {} \;
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com
As a DBA, regardless of RDBMS type, you will come across the need to replace text strings in dozens if not hundreds of files to facilitate the completion of your job. In this article we will cover the use of bash and perl scripts to perform text replacement of multiple files within a UNIX or LINUX environment.
1. Logon to your UNIX or LINUX server as the owner of the files you want to update or a user which has permission to update these files.
2. In this procedure we will create a file named files.txt containing a listing of all files we wish to update.
mylinux:> more files.txt
./test1.txt
./test2.txt
./test3.txt
./test4.txt
3. Next create a file called update.sh with the following text.
dt=`date “+%m%d%Y”` # Gets current date.
cat ./files.txt|while read line # Reads in all files from files.txt one line at a time.
do # Opens a loop
cp $line $line$dt # copies original file to backup with file_name+date.
ls $line |xargs perl -pi -e ‘s/{old_string}/{new_string}/g’ # if found replace old_string with new_stirng
done # ends loop
4. Change permissions on the update.sh to 770, so it will execute.
mylinx:>:>chmod 770 update.sh
mylinx:>:>
5. View the contents of one of the files in you files.txt file.
mylinx:>:>cat test*
one
one
one
one
mylinx:>:>
6. In this example, all of the files contain the text “one” which we will replace with the string “two”. Thus your update.sh file will look like the example below.
dt=`date “+%m%d%Y”`
cat ./files.txt|while read line
do
cp $line $line$dt
ls $line |xargs perl -pi -e ‘s/one/two/g’
done
7. Execute the update.sh file with the command: ./update.sh.
mylinx:>:>./update.sh
mylinx:>:>
8. Now cat all files named test*
mylinx:>:>cat test*
two
two
two
two
mylinx:>
As you can see all strings of “one” have been replaced with the string “two”. This completes replacement of strings in UNIX and LINUX.
Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com
As an Oracle DBA you will come across many situations where you need to replace string text within OS layer files. This procedure covers the use of Perl command to perform such a task. This procedure will work in both UNIX and LINUX.
1. Logon to you Oracle server as the owner of the files you wish to perform a string replacement on.
2. We will use the command (“perl -pi -e ‘s/{old_string}/{new_string}/g’ {file_name}”) to perform our string replacement.
3. In this example we will replace all of the references to the string “MARS” with the string “EARTH” in any file that ends in test*sh, the command will appear as:
perl -pi -e ‘s/MARS/EARTH/g’ test*sh
4. List all of the contents of files of the format test*sh with the cat command.
mylinux:>cat test*sh
MARS
MARS
MARS
MARS
mylinux:>
5. Execute your perl command in the same directory where your the files are located.
mylinux:>perl -pi -e ‘s/MARS/EARTH/g’ test*sh mylinux:>
6. List all of the contents of the files with format of test*sh with the cat command.
mylinux:>cat test*sh
EARTH
EARTH
EARTH
EARTH
mylinux:>
As you can see above the test string “MARS” has been replaced with “EARTH”. This completes replacement of text string with Perl.
Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com