Help - Search - Members - Calendar
Full Version: Hot Backup script Pls
Oracle DBA Forums > Oracle > Oracle Forum
Zaheer
Hi ALL,

Can you guys send me a good Hot Backup script?
Please send full code...
Most Urgent !!

NOTE:script not take from internet would be appreciated !!


Thanks in Advance !!
Zaheer
cr1sp1
Hi Zaheer,

Are you looking for a RMAN script or a normal Hot Backup script? huh.gif
Zaheer
Hi

Im looking only for Normal Hot backup script and NOT Rman Script...
Most urgent !!
Any help would be Great&appreciated....!!

Thanks
Zaheer
cr1sp1
Hi Zaheer,

Here is the script we used before migrating to RMAN.

The first one is a file called online_bck.bat

REM ***************************************************
REM VERSION: 1.2
REM CREATED DATE: 23 May 2005
REM CREATED BY: Ernst Karner
REM LAST MODIFIED ON: 17 March 2008
REM LAST MODIFIED BY: Christian Pretorius
REM
REM VERSION HISTORY:
REM 23/05/2005, V1.1 - Added version information
REM 23/05/2005, V1.2 - Removed duplicate environment variables in log file.
REM 17/03/2008, V1.3 - Added password variable for cross version compatibility
REM - Removed the SYS_READ from the datafile backup, this is to
REM eliminate ALERT.LOG errors
REM
REM
REM ***************************************************

set bck_ver=1.3

REM ***************************************************
REM * ENTER THE REQUIRED SETTINGS IN THE SECTION BELOW*
REM ***************************************************

REM ***************************************************
REM *** ENTER THE PROPERTY'S NAME AFTER THE = SIGN ****
REM ***************************************************

set property=

REM ***************************************************
REM ** IF THE DATABASE NAME IS NOT "OPERA", ENTER **
REM ** THE DATABASE NAME AFTER THE = SIGN, OTHERWISE **
REM ** LEAVE AS IS **
REM ***************************************************

set oracle_sid=opera

REM ***************************************************
REM ** ENTER THE DRIVE LETTER ON WHICH THE BACKUP **
REM ** FOLDER WILL BE CREATED. ONLY ENTER THE DRIVE **
REM ** LETTER FOLLOWED BY : (EXAMPLE: E:) **
REM ***************************************************

set backup_drive=d:

REM ***************************************************
REM ** ENTER THE ORACLE SYSTEM PASSWORD FOR THE **
REM ** BACKUP. THIS IS TO ENSURE CROSS VERSION **
REM ** COMPATIBILITY **
REM ***************************************************

set password=manager

REM ***************************************************
REM ** ENTER THE CLIENT'S SMTP (OUTGOING MAIL) **
REM ** SERVER NAME OR IP ADDRESS, **
REM ** EXAMPLE: smtp.is.co.za OR 196.37.124.29 **
REM ** OBTAIN THIS INFORMATION FROM THE PROPERTY'S **
REM ** ADMINISTRATOR **
REM ***************************************************

set smtp_server=mail1.micros.co.za

REM ***************************************************
REM ** ENTER A VALID EMAIL ADDRESS RECOGNIZED BY THE **
REM ** PROPERTY'S MAIL SERVER, EXAMPLE: **
REM ** it@client.co.za OBTAIN THIS **
REM ** INFORMATION FROM THE PROPERTY'S ADMINISTRATOR **
REM ***************************************************

set mail_username=cpretorius

REM ***************************************************
REM ** ENTER THE LIST OF RECIPIENTS WHO WILL RECEIVE **
REM ** THE BACKUP LOG FILE DAILY. SEPARATE THE **
REM ** RECIPIENTS BY A COMMA (,). DO NOT REMOVE **
REM ** DBA@micros.co.za **
REM ** EXAMPLE: DBA@micros.co.za,it@client.co.za **
REM ***************************************************

REM set mail_recipients=DBA@micros.co.za
set mail_recipients=cpretorius@micros.co.za



REM ***************************************************
REM ***************************************************
REM **** DO NOT CHANGE ANYTHING FROM HERE ON !!! ****
REM ***************************************************
REM ***************************************************


set backup_path=%backup_drive%\BACKUP
set log_path=%backup_path%\LOG
MD %backup_path%
MD %log_path%


echo ************************************************** > %log_path%\online_backup.log
echo ******** START OF OPERA ONLINE BACKUP ************ >> %log_path%\online_backup.log
echo ************************************************** >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log
set bck_ver >> %log_path%\online_backup.log
set property >> %log_path%\online_backup.log
set oracle_sid >> %log_path%\online_backup.log
set backup_drive >> %log_path%\online_backup.log
set smtp_server >> %log_path%\online_backup.log
set mail_username >> %log_path%\online_backup.log
set mail_recipients >> %log_path%\online_backup.log
set password >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log

date /t >> %log_path%\online_backup.log
time /t >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log

sqlplus system/%password%@opera @%backup_drive%\ONLINE\online_bck.sql %backup_path% %log_path%

echo. >> %log_path%\online_backup.log

type %log_path%\backupdb.log >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log

echo ************* BACKUP FOLDER CONTENTS ************* >> %log_path%\online_backup.log
echo. >> %log_path%\online_backup.log
dir %backup_path% /oe /on >> %log_path%\online_backup.log
echo ************************************************** >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log

date /t >> %log_path%\online_backup.log
time /t >> %log_path%\online_backup.log

echo. >> %log_path%\online_backup.log

echo ************************************************** >> %log_path%\online_backup.log
echo ******** END OF OPERA ONLINE BACKUP ************** >> %log_path%\online_backup.log
echo ************************************************** >> %log_path%\online_backup.log


The second one is the online_bck.sql

set heading off
set echo off
set feedback off
set verify off
set pagesize 0
set linesize 300

spool &1\backupdb.sql

select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;
select 'REM -- Cleaning Up...' from dual;
select 'host del /Q &1\*.*' from dual;
select 'REM -- START LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM -- SWICTHING LOGFILES...' from dual;
select 'alter system switch logfile;' from dual;
select 'REM --PERFORMING ONLINE BACKUP OF TABLESPACES...' from dual;
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||'
host copy '||file_name||' &1'||chr(10)||'
alter tablespace '||tablespace_name||' end backup;'
from dba_data_files
WHERE FILE_NAME NOT LIKE '%SYS_READ%';
select 'alter system switch logfile;' from dual;
select 'REM --FINISH LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM --COPYING REDO LOGS....' from dual;
select 'host copy '||member||' &1' from v$logfile;
select 'REM --COPYING CONTROLFILES ...' from dual;
select 'host copy '||name||' &1' from v$controlfile;
select 'REM --backing up controlfile to trace...' from dual;
alter database backup controlfile to trace;
select 'REM --COPYING ARCHIVE LOGS ...' from dual;
select 'host copy '||destination||'\*.* &1' from v$archive_dest
where status = 'VALID';
select 'REM --DELETING ARCHIVE LOGS ...' from dual;
select 'host del /Q /F '||destination||'\*.*' from v$archive_dest
where status = 'VALID';
select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;

spool off

set heading on
set echo on
set feedback on
set verify on

spool &2\backupdb.log

@&1\backupdb.sql

spool off

exit

We created two folders, one called ONLINE holding the above two files and then a folder called BACKUP where the files are copied to.

We moved away from this due to the fact that if you datafiles are 120GB you need 120GB diskspace for the backup.

If you need RMAN scripts, let me know and i will send them to you.

Regards,

Chris
Zaheer
Hi Chris,
i copied your above sql script and saved as hotbackup.sql.
when i execute the "hotbackup.sql" file from sql*plus,it's not executing.. i get the following unknown error...like this.
Please tel me how to do it.

SQL> @"C:\Documents and Settings\mzaheer109519\Desktop\R&D\hotbackup\Hotbackup.sql"
Enter value for d:
SP2-0310: unable to open file "C:\Documents and Settings\mzaheer109519\Desktop\R\hotbackup\Hotbackup.sql"
SQL>
cr1sp1
QUOTE (Zaheer @ Jul 14 2008, 12:32 PM) *
Hi Chris,
i copied your above sql script and saved as hotbackup.sql.
when i execute the "hotbackup.sql" file from sql*plus,it's not executing.. i get the following unknown error...like this.
Please tel me how to do it.

SQL> @"C:\Documents and Settings\mzaheer109519\Desktop\R&D\hotbackup\Hotbackup.sql"
Enter value for d:
SP2-0310: unable to open file "C:\Documents and Settings\mzaheer109519\Desktop\R\hotbackup\Hotbackup.sql"
SQL>


Hi Zaheer, the bat file calls the sql script, in the bat file you specify the location of the directories. The scripts were written to run out of a directory called ONLINE, i have attached the zipped file for you, hopefully this helps!

Cheers,

Chris
Zaheer
Hi
It seems it's a very complex script... can i get a simple Hotbackup script.
plsssssssss

Thanks
Zaheer
cr1sp1
Hi Zaheer,

I have modifed the script to spool, read and copy to D:\BACKUP

Should you want it to go to a different directory, just replace all the instances of D:\BACKUP with your location.

set heading off
set echo off
set feedback off
set verify off
set pagesize 0
set linesize 300

spool D:\BACKUP\backupdb.sql

select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;
select 'REM -- Cleaning Up...' from dual;
select 'host del /Q &1\*.*' from dual;
select 'REM -- START LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM -- SWICTHING LOGFILES...' from dual;
select 'alter system switch logfile;' from dual;
select 'REM --PERFORMING ONLINE BACKUP OF TABLESPACES...' from dual;
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||'
host copy '||file_name||' D:\BACKUP'||chr(10)||'
alter tablespace '||tablespace_name||' end backup;'
from dba_data_files
WHERE FILE_NAME NOT LIKE '%SYS_READ%';
select 'alter system switch logfile;' from dual;
select 'REM --FINISH LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM --COPYING REDO LOGS....' from dual;
select 'host copy '||member||' D:\BACKUP' from v$logfile;
select 'REM --COPYING CONTROLFILES ...' from dual;
select 'host copy '||name||' D:\BACKUP' from v$controlfile;
select 'REM --backing up controlfile to trace...' from dual;
alter database backup controlfile to trace;
select 'REM --COPYING ARCHIVE LOGS ...' from dual;
select 'host copy '||destination||'\*.* D:\BACKUP' from v$archive_dest
where status = 'VALID';
select 'REM --DELETING ARCHIVE LOGS ...' from dual;
select 'host del /Q /F '||destination||'\*.*' from v$archive_dest
where status = 'VALID';
select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;

spool off

set heading on
set echo on
set feedback on
set verify on

spool D:\BACKUP\backupdb.log

@D:\BACKUP\backupdb.sql

spool off


Hope this helps.

Cheers
dave
QUOTE (Zaheer @ Jul 14 2008, 01:14 PM) *
Hi
It seems it's a very complex script... can i get a simple Hotbackup script.
plsssssssss

Thanks
Zaheer


wow thats laziness on a scale i've never seen before
HAL9000
Why would ANYBODY run a script against their production database that they don't understand?
Zaheer
Hi cr1sp1 ..
Excellent....work.It's working.
Thank You very much..much appreciated.

For Dave,
You may be expert but always remember there are also newbie's here trying to understand various problems which might be confusing for them.....!!
cr1sp1
Hi Zaheer,

Glad I could help. I would however recommend that you move to RMAN, here are some benefits:



RMAN will detect corrupted blocks and report them.
RMAN can backup a database online without having to put the tablespaces in hot backup mode. Thus, the additional (and sometimes quite significant) redo generated during a hot backup is reduced.
RMAN will automatically track new datafiles and tablespaces, which means new tablespaces or datafiles no longer, have to be added to scripts.
RMAN will only backup used data blocks (up to the high water mark (HWM)). Thus, RMAN backup images typically are smaller than those of online backup scripts.
RMAN provides easy, automated backup, restore, and recovery operations. RMAN tracks all the backups needed to recover the database that you will require in the event that a restore will be required, and will restore only those objects that are needed.
RMAN can work fairly seamlessly with third-party media management products.
RMAN supports incremental backup strategies.
With RMAN, you can test your backups without restoring them.


Information obtained from Oracle9I RMAN Backup & Recovery By Robert G. Freeman, Matthew Hart

Cheers,
Zaheer
Good..I have started learning Rman also...
do you have script for Rman Too...!!?if Yes ... Please send it.

I guess Its always better to go for Rman then using Hot Backup especially in Production Databases.right..?

Thank You Very Much,
Much Appreciated..
aussie_dba
" have started learning Rman also... do you have script for Rman Too...!!?"

If you are learning RMAN, why ask someone for their script?

Why not write your own?

No offense intended, but does your boss know that you do not know how to do backups?

If you are not competent in Oracle, you should ask to be properly trained.
Zaheer
my primary responsibilities is something else.... dude.!!
HAL9000
"my primary responsibilities is something else"

Will you be hold responseible if you cannot recover from the backup, when you need it?

Just make sure that you INSIST on a full production recovery test, to make sure it works.

You will want to CYA!
cr1sp1
I have to agree with HAL9000 on this. Backup and recovery is such an integral part of being a DBA (or responsible for a database).

Many times I have saved a clients DB only due to the fact that we insist on daily backups and archivelog mode, I have had only 1 database I couldn't recover in the past 2 years and that was due to a hard drive failure. But we replaced the hard drive and restored from the last backup!

If you are responsible for getting the DB backed up, then you will be held responsible for its recovery....
Zaheer
Yes..I agree.now i have manage both in Hotback and Rman.
Thanks
HAL9000
Hi,

Just remember:

INSIST on a downtime window where you delivberately corrupt production, and do a full production restore.

If they won't do that, get it IN WRITING that your backup and recovery scripts were never "certified" nor tested.

That way, even though your company may go out of business due to loosing the data forever, at least you won't be held responsible.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.