Oracle Faq

#!/bin/bash
ORACLE_SID=OTM;export ORACLE_SID
echo $ORACLE_SID

echo “Please Specify the kind of backup you want to take”
echo “1) COLD BACKUP”
echo “2) HOT BACKUP”
echo “3) EXPORT BACKUP”
echo “Enter your option”

read option

while [ $option -gt 3 ]||[ $option -le 0 ]
do
echo “Please Enter the correct option”
read option
done

case $option in
1|2) echo “You are taking rman backup of DB”
   rman target sys/sys @/oracle/product/11g/rman_backup_$option.txt;exit;;
3) echo “You are taking export backup of DB”
   exp system/sys file=/oracle/exp_dat.dmp log=/oracle/exp_dat.log full=y;
   exit;;
esac

exit

 The above script can call anyone of the following rman script depending upon the user who wants take cold or hot backup

The content of rman_backup_1.txt 

run {
   shutdown immediate;
   startup mount;
   allocate channel dup1 device type disk;
   allocate channel dup2 device type disk;
   backup format ‘/oracle/%U’ database;
   release channel dup1;
   release channel dup2;
   alter database open;
  }

The content of rman_backup_2.txt

run {
allocate channel dup1 device type disk;
allocate channel dup2 device type disk;
backup format ‘/oracle/%U’ database;
backup format ‘/oracle/arch_%U’ archivelog all;
backup format ‘/oracle/ctl_%U’ current controlfile;
release channel dup1;
release channel dup2;
}

Share This

1) To use hot backup and clone the database the
production database has to be in archivelog mode.

2) Create a new init.ora from the production
database’s init.ora file and make the required
modifications like the location of bdump,udump,
cdump,log_archive_dest_1,instance_name,db_name and
also make sure the folders are physically present
at the OS level(i.e create them).

3) On the target machine (i.e where the clone database has been created)
we have to create a new service as well as the password file.

Commands for the same are:-
C:\oracle\ora92\bin>oradim -new -sid <new SID>
C:\oracle\ora92\database>orapwd file=PWD<new_sid>.ora password=sys

4) Now on your production database fire the following command
 to create a trace of the controlfile

SQL>alter database backup controlfile to trace;

This will create a trace file in your udump folder
 (kindly check the time to make sure it is the latest trace file).

5) Open the trace file which was created in step 4 and
make the following modifications

a) Change the
CREATE CONTROLFILE REUSE DATABASE “<production database name>” NORESETLOGS
             TO
CREATE CONTROLFILE SET DATABASE “<new database name>” RESETLOGS ARCHIVELOG

b) Change all directories in the create controlfile clause
to point to the correct directories for the new target database, if necessary
  
c) Leave “only” the CREATE CONTROLFILE clause. The other statements,
like the recover command, will be done manually.
    
Be sure you also remove the STARTUP NOMOUNT command.
So remove all the other statements other then the script starting
with CREATE CONTROLFILE …and ending with CHARACTER SET WE8MSWIN1252;

After making the modifications save the changed script
as a sql file (i.e for example like create_contol.sql)
 and send it to the target machine.

6) Now on the produstion database
we begin the hot backup procedure

To find out the datafile associated with each tablespace
fire the following query

SQL>select file_name,tablespace_name from dba_data_files;
SQL>alter tablespace <tablespace_name> begin backup;

Copy all the datafiles associated with this tablespace
to the required location as specified in the CREATE CONTROLFILE script.
After copying is over fire the following command to end the backup

SQL>alter tablespace <tablespace_name> end backup;
Do this for all the tablespaces.

7) After all the datafiles have been copied and no more tablespace
 is in backup mode fire the following query to archive the current logfile;

SQL>alter system archive log current;

These were the steps to be performed on the production database
except for step 3 which needs to be performed on the target machine.

The following steps needs to be performed on the target machine.

On the target machine log into SQL with the following commands

C:\>set oracle_sid=<new_database_sid>
C:\>sqlplus
and then enter the username and password.

9) Ater logging into the sql prompt startup the database in nomount phase
with the modified init file.

SQL>startup nomount pfile=<location of the modified init file>

10) After the instance is started then we have to run the script
to create the controlfile.

SQL>@<location of the create controlfile sql script>

11) After the command has been executed successfully and
the controlfile is created then we have to recover the database
using the following command.

SQL>recover database until cancel using backup controlfile;

It will ask for the archives which were generated in the production database
during the hot backup and till the latest archive.Kindly copy those
archives to the target machine at the loaction
specified by the log_archive_dest_1 parameter in init file
     .

12) After recovery is completed then open the database with resetlogs option.

SQL>alter database open resetlogs;

Now after this command completed successfully your clone database is ready.
                     
        

Share This