Home » RDBMS Server » Backup & Recovery » backup
backup [message #222471] Mon, 05 March 2007 00:36 Go to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hi All,

Could any one explain me how do we perform online backup except export/import and rman.

Thanks in advance

Magesh
Re: backup [message #222478 is a reply to message #222471] Mon, 05 March 2007 01:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
First recommended method is RMAN for online backup.
and EXPORT/IMPORT is not a backup METHOD.


anyway you can use USER MANAGED BACKUP. for this method you have to do so many thing manual if you choose RMAN it will done by ORACLE SERVER.

1.your database in ARCHIVE LOG MODE.
2.define to parameter for archive log.
--log_archive_dest
for archive log destination.
--log_archive_format
for archive log file format.
typically>>>log_archive_format='log_%t_%s_%r.arc'
3.TAKE ALL tablespaces BEGIN BACKUP MODE
select 'alter tablespace ' || tablespace_name||' begin backup;'
from dba_data_files;

Copy all datafiles to BACKUP DESTINATION through OS copy command.

4.TAKE CONTROL FILE BACKUP
alter database backup controlfile to 'C:\BACKUP\CONTROL01.CTL';
create two more control file ( control02.ctl',control03.ctl')

5.after copy all datafile take all tablespace in end backup mode.
select 'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files;

NOTE:
backup procedure run low database activity time.
archive log and redo log store in separate destination.


regards
Taj
Re: backup [message #222624 is a reply to message #222471] Mon, 05 March 2007 12:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are going to be responsible for your databases Backup & Recovery process, you should review User-Managed Backup and Recovery Guide for details on the process.
Re: backup [message #223594 is a reply to message #222471] Fri, 09 March 2007 13:20 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Solaris only:
Hot backup

#!/bin/ksh
#@(#)oprmenu.ksh - S2000 database Operations menu
###
# Korn script to display a menu for our operators
# Ask operator which database maintenance job
# they would like to run.
#
# Autor: Neil MacDannald
###
#
# Ask operator what database job they would like to run
#
quit=n
clear
while test "$quit" = "n"
do
echo " "
echo " Menu"
echo "-----------------------------------------------"
echo " "
echo "1. Run S2000 hot backup "
echo "2. Run S2000 file compression "
echo "3. Run S2000 archive maintenance "
echo "4. Run S2000 export for Tracy "
echo "5. Run S2000 xfer export file to Tracy "
echo "6. Run S2000 hot backup cancel "
echo " "
echo "8. Run s2000 background process stop "
echo "9. Quit "
echo " "
echo "Enter choice: "
read choice
case $choice in
1) . ./hotbackup_start.ksh;;
2) . ./hotbackup_compress.ksh;;
3) . ./archivemaint.ksh;;
4) . ./export-s2000-database.ksh;;
5) . ./export-xfer-to-solarflare.ksh;;
6) . ./hotbackup_cancel.ksh;;
Cool . ./bgprockill.ksh;;
9) quit=y;;
*) echo "Invalid selection, enter 1, 2, 3, 4, 5, 6, 8, or 9 only \07\07"
sleep 5;;
esac
done




#!/bin/ksh
#@(#)hotbackup_start -- starts S2000 hotbackup
###
# Korn script to start hot backup of the S2000 database
#
# Autor: Neil MacDannald
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting hot backup of S2000 database
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
df -k /u26
echo `date`
thedir=`pwd`
echo " "
echo Enter password for S2000 ORADBA
sttyops=`stty -g`
stty -echo
echo "Password: "\\c
read password
echo " "
stty $sttyops
#
# Start the hot backup job
#
sqlplus ORADBA/$password << EOF
@$thedir/hotbackup_start
exit
EOF
sleep 20
cp /u02/oradata/os2p/arch/*.ARC* /u26/oradata/os2p/budir/
cp /u24/oradata/os2p/arch/*.ARC* /u26/oradata/os2p/budir/u24arch/
df -k
echo `date`
firem========================================================================
rem
rem Filename: hotbackup.sql
rem Purpose: Hot backup of Oracle database
rem It assumes :
rem - An underlying UNIX system
rem - The use of the 'cp' command to backup the files to a special
rem backup directory the name of which is prompted for in the
rem script
rem
rem Because of the high importance of backup procedures, due care is
rem given to the handling of errors. Any SQL or operating system error
rem will cause the script to exit and return a non-0 value to the
rem operating system.
rem
rem IT IS EXTREMELY IMPORTANT IN SUCH A CASE TO RUN THE ASSOCIATED
rem hotbackup_cancel.sql AS SOON AS POSSIBLE TO CANCEL THE
rem POSSIBLE 'BACKUP' STATE OF A TABLESPACE.
rem
rem========================================================================
set linesize 600
set pause off
set pagesize 0
set echo off
set feedback off
--
-- Hot-backup requires our running in ARCHIVELOG mode.
-- This is tested from the outset.
--
whenever sqlerror exit sql.sqlcode
declare
dummy char(1);
begin
select 'x'
into dummy
from v$database
where log_mode = 'ARCHIVELOG';
exception
when no_data_found then
raise_application_error(-20000, 'Database not in ARCHIVELOG mode !');
end;
/
set scan off
--
-- Interactively input the name of the directory (or device)
-- where to backup. This kind of dialogue is likely to prove
-- a hindrance in automated, daily operations.
-- You can either hard-code the directory name by using
-- define backup_dir = ...
-- or suppose it is passed as an argument to the current
-- script, in which case you should replace &&backup_dir by
-- &1 everywhere in this script and comment the following
-- 'accept' command
--
-- accept backup_dir prompt 'Backup directory : '
define backup_dir = /u26/oradata/os2p/budir/
--
set recsep off
set scan on
set verify off
--
-- You must absolutely check that everything goes according
-- to plans! If the backup fails because of, say, a
-- 'file system full' error, the script must exit with a
-- non-0 return code.
--
whenever sqlerror exit sql.sqlcode
whenever oserror exit 99
--
-- To be certain we have a consistent set of files which
-- we shall be able to recover, we also want to backup
-- all the redo log files generated during the backup,
-- and the final state of the control file.
-- Note that when recovering you normally must use the
-- current (i.e. at crash time) control file. However,
-- we want to be ready for the worst case scenario and
-- be able to use the current backup as if it were a
-- cold backup.
-- In order to know which redo log files have been generated
-- during the backup, we first identify which is, before
-- backup, the (current - 1) redo log file.
--
variable last_log varchar2(255)
begin
--
-- Format %S and %T (left-padded with zeros)
-- are not supported because length is OS-dependent.
-- Easy to support for a specific machine
--
select replace(pd.value, '?', '$ORACLE_HOME') ||
replace(replace(pf.value, '%s', rtrim(to_char(l.sequence#))),
'%t', rtrim(to_char(l.thread#)))
into :last_log
from v$parameter pd,
v$parameter pf,
v$log l
where pd.name = 'log_archive_dest'
and pf.name = 'log_archive_format'
and l.sequence# = (select max(sequence#)
from v$log
where status != 'CURRENT');
end;
/
--
-- Generate the backup script
--
-- online$ = 3 means a tablespace which once
-- existed but was dropped.
--
column dummy noprint
spool hbu_doit.sql
select ts# dummy,
1 dummy,
'alter tablespace ' || name || ' begin backup;'
from sys.ts$
where online$ != 3
and name <> 'TEMP_01'
union
select f.ts#,
2,
'host cp ' || d.name || ' &&backup_dir'
from sys.file$ f,
v$datafile d
where f.file# = d.file#
union
select ts#,
3,
'alter tablespace ' || name || ' end backup;'
from sys.ts$
where online$ != 3
and name <> 'TEMP_01'
order by 1, 2
/
spool off
--
-- Run the backup script just generated
--
set echo on
set feedback on
@hbu_doit
--
-- Take a backup of the control file for the worst-case scenario
-- Do not worry, parameter replacement is not echoed but done.
--
alter database backup controlfile to '&&backup_dir./control.ctl' reuse;
--
-- Trigger a redo log switch to close the current redo log file
--
alter system switch logfile;
set echo off
--
-- Force the archival of all the redo log files
--
declare
Stmt varchar2(100);
cid number;
dummy number;
nothing_to_archive exception;
PRAGMA EXCEPTION_INIT(nothing_to_archive, -271);
begin
Stmt := 'alter system archive log all';
cid := dbms_sql.open_cursor;
begin
dbms_sql.parse(cid, Stmt, dbms_sql.native);
dummy := dbms_sql.execute(cid);
exception
when nothing_to_archive then
null;
--
-- Unexpected errors
--
when others then
if (dbms_sql.is_open(cid))
then
dbms_sql.close_cursor(cid);
end if;
raise_application_error(-20000, Stmt || chr(10) || SQLERRM);
end;
dbms_sql.close_cursor(cid);
end;
/
set feedback off
--
-- Backup all the redo log files generated during hot backup
-- in ksh script when this completes until later
set echo on
--
-- All done!
--
exit 0

*******************!!!!!!!!!!!!!!!
Make sure you run this if the backup fails!


#!/bin/ksh
#@(#)hotbackup_cancel -- takes all S2000 tablespaces out of backup mode
###
# Korn script to cancel hot backup of the S2000 database
#
# Autor: Neil MacDannald
# Date: 9/30/1999
###
#
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Canceling hot backup, taking all tablespaces out of backup mode
echo "Continue? (Y/N)"
read answer
if [[ "$answer" = [Yy]* ]]
then
pwddir=`pwd`
echo Enter password for S2000 ORADB
sttyops=`stty -g`
stty -echo
echo "Password: "\\c
read password
echo " "
stty $sttyops
#
# Start the hot backup cancel
#
sqlplus ORADBA/$password << EOF
@$pwddir/hotbackup_cancel
exit
EOF
fi


Sqlfile for taking tablespaces out of backup mode

set pause off
set pagesize 0
set echo off
set feedback off
set recsep off
set scan off
spool hbu_cancel.sql
select 'alter tablespace ' || ts.name || ' end backup;'
from sys.ts$ ts
where ts.ts# in (select f.ts#
from sys.file$ f,
v$backup b
where f.file# = b.file#
and b.status = 'ACTIVE');
spool off
set echo on
set feedback on
@hbu_cancel
exit

#!/bin/ksh
#@(#)archivemaint -- removes old S2000 archive files & compresses others
###
# Korn script to do Database Archive file maintenance
# for the S2000 database. This should be run about once
# each week or so.
#
# Autor: Neil MacDannald
# Date: 10/25/1999
# Modified: 04/26/2002 to support multiplexing archive logfiles
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting S2000 Archive file maintenance
echo " "
echo This job should be run about once per week. It removes
echo S2000 database Archive files that are more than 2 weeks old.
echo Archive logs are located in both of the following directories:
echo /u02/oradata/os2p/arch
echo /u24/oradata/os2p/arch
echo " "
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
echo " "
echo `date`
echo " "
echo "Count Archive files /u02: " `ls /u02/oradata/os2p/arch/*.ARC* |wc -w`
echo "Count Archive files /u24: " `ls /u24/oradata/os2p/arch/*.ARC* |wc -w`
echo "Last time this job was run: " `cat <archmaint_date`
echo " "
echo "Remove Archive files more than 2 weeks old and compress "
echo " the newer ones? (Y/N)"
echo " "
read answer
if [[ "$answer" = [Yy]* ]]
then
`date >archmaint_date`
startdir=`pwd`
echo "starting dir= " $startdir
cd /u02/oradata/os2p/arch/
echo " temporarily changing to directory " `pwd`
`find ./ -name "*.ARC*" -mtime +14 -exec rm {} \;`
cd /u24/oradata/os2p/arch/
echo " temporarily changing to directory " `pwd`
`find ./ -name "*.ARC*" -mtime +14 -exec rm {} \;`
echo " "
echo "Old files removed, starting compression of new Archive files"
echo " "
echo `date`
echo " "
`compress -v /u02/oradata/os2p/arch/*.ARC`
`compress -v /u24/oradata/os2p/arch/*.ARC`
cd $startdir
echo " changed directory back to " `pwd`
echo " "
echo `date`
echo " "
fi
fi


#!/bin/ksh
#@(#)hotbackup_compress -- compresses S2000 backup files before copy to tape
###
# Korn script to start compression of the 2000 database
# hot backup files. This should be run after running
# hotbackup_start.ksh
#
# Autor: Neil MacDannald
# Date: 10/01/1999
# Revision: 10/16/1999 Add remove of old Z files
# Modified: 04/26/2002 - support multiplex of archive files
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting S2000 hotbackup file compression
echo " "
echo This job should be run after the hotbackup completes and
echo before the hotbackup files are backed up to tape.
echo Please verify that the hotbackup completed properly.
echo " "
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
echo " "
echo `date`
echo " "
echo "Number of old files: " `ls /u26/oradata/os2p/budir/*.Z |wc -w`
echo "Last time this job was run: " `cat <compress_date`
echo " "
echo "Remove old compressed files?(Y/N)"
read answer
if [[ "$answer" = [Yy]* ]]
then
rm /u26/oradata/os2p/budir/*.Z
rm /u26/oradata/os2p/budir/u24arch/*.Z
`date >compress_date`
echo " "
echo "Old files removed, starting compression of new backup files"
echo " "
echo `date`
echo " "
compress -v /u26/oradata/os2p/budir/*.*[!Zh]
compress -v /u26/oradata/os2p/budir/u24arch/*.*[!Z]
echo " "
echo `date`
echo " "
fi
fi

[Updated on: Fri, 09 March 2007 13:35]

Report message to a moderator

Previous Topic: Copy contents of oradata
Next Topic: How Media Management Layer works
Goto Forum:
  


Current Time: Sat Dec 03 03:58:36 CST 2016

Total time taken to generate the page: 0.20496 seconds