Hot Backup Scripts for database backup and apps backup

Yesterday I discussed about backup Overview & cold backup if you have not gone though it you can read it from

http://becomeappsdba.blogspot.com/2006/09/backup-recovery-in-oracle-apps.html

Yesterday I mentioned that I am going to post about hot backup scripts , here it comes

Enable Archive Log
First check if your database is in Archive mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
If you see Automatic archival is enabled that means database is in archive mode . If not then

SQL>SHUTDOWN
SQL>STARTUP MOUNT
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
SQL>alter system set LOG_ARCHIVE_DEST_1 =’LOCATION=/your/archive/location/arch’ scope=spfile;
SQL>shutdown immediate
SQL>Startup
Steps are for database 10g , These commands may vary according to your database version.
After that for Hotbackup
Change tablespace mode to begin backup like
alter tablespace tablespace_name begin backup; you have to do this for all tablesapces in your database except temporary tablespace (This can be tedious work if you are doing it manually so here is script to do so )

SQL>set head off
SQL>spool beginbackup.sql
SQL>select ‘alter tablespace ‘ tablespace_name ‘ begin backup;’ from dba_tablespaces;
SQL>spool off

This will create file beginbackup.sql with entry for all tablespaces, remove any unnecessary lines & then execute this script into SQL like
SQL>@beginbackup.sql (Once you execute this script this will put all tablespaces in to begin backup mode)

Now create backup of your control file in Human Readable format like
alter database backup controlfile to trace as ‘/some/path’; you can reuse it by removing comment at beginning & replace them with connect / as sysdba

Then
1. Copy all your datafiles, redo logs and control file from your database server to backup location.
after datafiles are copied don’t forget to end backup for all tablespace here is the scripts
SQL>set head off
SQL>spool endbackup.sql
SQL>select ‘alter tablespace ‘ tablespace_name ‘ end backup;’ from dba_tablespaces;
SQL>spool off

This will create file endbackup.sql with entry for all tablespaces, remove any unnecessary lines & then execute this script into SQL like
SQL>@endbackup.sql (Once you execute this script this will put all tablespaces in to end backup mode)

This completes your datafiles backup but you still have to backup database software & oracle Applications Middle Tier

2. Copy database software $ORACLE_HOME from server to backup location
3. Copy Apps Middle tier all TOPs (APPL_TOP, COMMON_TOP, ORA_TOP)

TIP: Sometime if you copy application tier while apache is Up , you copy httpd.pid or httpd_pls.pid from $IAS_ORACLE_HOME/Apache/Apache/logs/httpd.pid and if in restored Instance Apache doesn’t start & complain about httpd.pid or httpd_pls.pid already exists , you can simply delete these two files from restored Instance & start Apache Web Server.

Please note I am not in town for another 4 days so I’ll not be able to Upadte this Site till Tuesday. Thanks in Advance for your patience.

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

10 comments
Anonymous says August 1, 2007

Hi;
Just want to say Atul u r the best teacher.I’m learning alot from u. God bless ya!!

Reply
Anonymous says August 1, 2007

Hi Atul

We do not backup the redologs in hot backup.We backup the archives that are produced during the hot backup.
Also i did not understand why u want a human readable format of the controlfile? Instead you need to backup your controlfiles with .ctl extension, that is the correct procedure.

vikki

Reply
Jenrry Renaldi says August 2, 2007

I try to generate script for begin and end backup, is most efficient:

set head off;
spool beginbackup.sql;
select ‘alter tablespace ‘ || tablespace_name || ‘ begin backup;’ from DBA_TABLESPACES where tablespace_name <> ‘TEMP’;
spool out;

and:

set head off;
spool beginbackup.sql;
select ‘alter tablespace ‘ || tablespace_name || ‘ end backup;’ from DBA_TABLESPACES where tablespace_name <> ‘TEMP’;
spool out;

but not understand the part for:
Then
1. Copy all your datafiles, redo logs and control files …..

“this copy is a normal os copy: cp *.dbf /u02/hot_backup/oradata”????

I need your help..

Reply
Atul Kumar says August 3, 2007

Yes it is normal o.s. copy. Make sure you are backing up archives as well generated during this process.

Reply
Jenrry Renaldi says August 3, 2007

Thanks Athul, Your help resolve my big problem. I interest in your criteria about this:

Is mos efficient create multiples schema ‘s in oracle database for best manage and monitoring the db and efficient backup and restore process… or

Install oracle standard for a 5 standard tablespaces.

I ‘m show a presentation for my boss, he propose install oracle standard install, but i not…

I need your opinion respect to.

Thanks…

Reply
Atul Kumar says August 3, 2007

what do you mean by multiple schemas here ??

If you are talking about OATM , OATM in my opinion is better to manage.

Reply
Jenrry Renaldi says August 3, 2007

My English is not good, i from Dominican Republic, speak Spanish…

I ; m refer to distinct schemas, is a distinct users or (applications) for group your object in the database.

My work place is a Telecommunication company (ISP as residential telephonic services)

In Development ambient existing multiples Tablespaces: monitor, commissioning, billing, etc., .., .., .,

My boss said to production database not take a equal schema, but i not accord with her..

I write a propose for this to my boss.

thanks..

Reply
halidziya says September 10, 2008

Do you Know MS sql version of this

Reply
arun says November 26, 2010

Hi atul,

I am a Oracle Engineer, working in a MNC in india from last 4 year in DBA domain, but i did’t get as much exposure as experience i m showing. now i want to switch move farward. I am looking forward for Apps DBA, What you suggest to me for this? help me or suggest how i can move to apps dba or i should stay in oracle DBA
i am 10g ocp certified,and also RHCT.

Thanks
Arun

Reply
Atul Kumar says November 29, 2010

@ Arun,
Both DBA and Apps DBA are good , Do what you enjoy most .

Reply
Add Your Reply

Not found