Oracle: Auto Backup

Oracle Auto Back up

1. Create ‘filname.sh’ file with following code anywhere in server after amendments. (Possible amendments in RED.) e.g. filename is make_db.sh. 

#!/bin/bash 

# 

# 

dy=`date +%a` 

hn=`hostname` 

bk='/opt/back' 

sb=$bk'/'$hn 

# 

#!/bin/bash 

# 

# 

dy=`date +%a` 

hn=`hostname` 

bk='/opt/back' 

sb=$bk'/'$hn 

# 

# 

cm_d01='tar -Pczf '$sb'/'$hn'_'$dy'_d01.tar.gz /opt/d01'       

cm_d02='tar -Pczf '$sb'/'$hn'_'$dy'_d02.tar.gz /opt/d02'       

cm_d02_da='tar -Pczf '$sb'/'$hn'_'$dy'_d02_data.tar.gz /opt/d02/oracle/proddata'   

# 

# 

dy1="Tue" 

dy2="Wed" 

dy3="Thu" 

dy4="Fri" 

dy5="Sat" 

dy6="Sun" 

dy7="Mon" 

both_bk_on=$dy7 

# 

# 

printf "\n" 

if test -d /opt/d01 ; then 

  printf "Source Folder d01 Exist ............... OK\n" 

  if test -d /opt/d02 ; then 

    printf "Source Folder d02 Exist ............... Ok\n" 

    if test -d $bk ; then 

      printf "$bk Folder Exist ............... Ok\n" 

    else 

      printf "Going to create $bk Folder\n" 

      mkdir $bk 

    fi 

    if test -d $sb ; then 

      printf "$sb Sub Folder Exist ............... Ok\n" 

    else 

      printf "Going to create $sb $ub Folder\n" 

      mkdir $sb 

    fi 

#   ------------------------------------------------------------------ 

    printf "Going to create tar file for d02 \n" 

    /home/oracle/down.sh 

    ls -l /opt/d02/oracle/proddata > $sb/list"_"$hn"_"$dy"_d02_b4.txt" 

    if test "$dy" = "$both_bk_on" ; then 

    $cm_d02 

   else 

   $cm_d02_da 

  fi 

    ls -l /opt/d02/oracle/proddata > $sb/list"_"$hn"_"$dy"_d02_aft.txt" 

    tar -tzvf $sb"/"$hn"_"$dy"_"d02.tar.gz > $sb"/list_"$hn"_"$dy"_d02_tar.txt" 

    if test "$dy" = "$both_bk_on" ; then 

      printf "Also going to create tar file for d01\n" 

      $cm_d01 

    fi 

    /home/oracle/up.sh 

#   ------------------------------------------------------------------ 

  else 

    printf "Source Folder D02 Does Not Exist\n" 

  fi 

else 

  printf "Source Folder d01 Does Not Exist\n" 

fi 

2. Now we will edit CRONTAB utility according to our requirement. Crontab is just like a scheduler. (See basics of CRONTAB at the end.) 
3. Add this line in CRONTAB ‘00 21 * * * /opt/back/make_db.sh’ 

The above line will automatically run the make_db.sh file everyday at 21:00. 

Manual Bakcup 

This document will guide you to take backup of single Tier R12. DB and Apps combine. Incase you are working on multi node repeat the steps for DB and Apps server separately. 

Take Backup 

1. Go to the directory where you want to place the backup...e.g. /opt/back. 
2. Down services. (So that no further live entries should made and it will speedup the process) 
3. Now take the backup as followed. 

tar -Pczf haseeb_oracle_04apr11.ar.gz /opt/haseeb_oracle/ 

(where tar -Pczf are the keywords then target folder with backup file name and then source folder with path(of which you want to take backup.)) 

4. Backup done. 

Place Backup 

5. Go to the path where you want to place the extracted file.(target folder) and run the following command 

tar -xzf back/haseeb_oracle_04apr11.ar.gz 

6. This will place the your extracted files in that directory. 
7. Now move the files accordingly so that your directory structure remains the same. 

In my case when I extracted the files it creates the ‘opt’ folder in existing ‘/opt’ folder (make sure there is hell off difference in '/opt' and 'opt/'. In '/opt','/'itself is directory.) 

While I need to retain the file structure like opt/oracle/. 

So I will remove the existing 'oracle' directory from '/opt' and move the oracle directory from 'opt/' to '/opt'. 

Now I’ll have the exact file structure what I need to have. 

All done. 

All the best. 

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

DBLINK : Connect to remote database

Connect to remote database

Create database link by executing following command in TOAD.

1.     create database link dblink1 connect to apps identified by apps  using 'TESTUPGRADE';

  Where ‘dblink1’ is link alias, first ‘APPS’ is schema name ,second ‘APPS’ is password and    ‘TESTUPGRADE’ is service name.

2.  select * from tablename@dblink1

   Before executing query you need to add entry of remote database in the ‘TNSNAMES.ORA’     as illustrated below using following command.

  vi /opt/d02/oracle/proddb/9.2.0/network/admin/PROD_test/tnsnames.ora

how to connect to remote database using dblink

Now you can access the tables of remote database by just adding DBLINK  alias at the end of the table e.g. tablename@dblink1. Best way to compare data of same table from two different databases.

If you want to remove the link then:

3.       drop database link dblink1 (if you want to drop link).

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

SQL: Update table from another table.

SQL: Update table from another table.

1- Create a temp table with a primary key column and fields which is to be update
2- Insert data into temp table using sql script or any other tool
3- Run the update statement as given below

update ra_customer_trx_all rcta
set  rcta.COMMENTS = (select COMMENTS from RA_TEMP a where a.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID)
where rcta.CUSTOMER_TRX_ID = (select CUSTOMER_TRX_ID from RA_TEMP a where a.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID)

Here:

ra_customer_trx_all = is the table to be updated
RA_TEMP  = is the other temp table having primary key and new data.
rcta.COMMENTS = is the filed to be updated.
rcta.CUSTOMER_TRX_ID = is the primary key in main table.
a.CUSTOMER_TRX_ID = is the primary key in temp table

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More