Home » RDBMS Server » Backup & Recovery » flash recovery area on nfs share?
| flash recovery area on nfs share? [message #219524] |
Wed, 14 February 2007 14:06  |
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
|
Member |
|
|
Hello,
Sinds a few days I started my new job as DBA for a internet hosting company. We're running about 20 databases (productie/test). I started to make an inventory of the backups of the databases and it seems to be a good starting point:-)
Right now only full database exports are made ones a day, the export are written to local disk (raid 10) and backup up to tape during the night. Archive logging is turned off.
The databases are oracle 9 and 10.
I would like to setup a new backup strategy. I would like to make use of a flash recovery area which is written to tape during the night.
Because the raid 10 aray may fail and the dataabase is on the same array I would like to write the flash recovery area over the network using smb protocol for windows machines and nfs for unix.
What files should I backup on the flash recovery area? My guess is to backup the archive log files, full and incremental backups of the datafiles, backup of the control file.
I don't think I can use the network share to write a copy the online redo log files because it will be to slow, am i right?
Will nfs be fast enought to hold a copy of the control files?
What if the raid array fails....and I write the on-line redo log files to the local disk? I think this way I can't do a full recovery of the database in case of media failure.
any advise would be welcome.
regards,
martin
|
|
|
|
| Re: flash recovery area on nfs share? [message #222895 is a reply to message #219524] |
Tue, 06 March 2007 21:21  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
archive logs
system.dbf
control files
redo logs
data.dbf files
ndx.dbf files
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 = <your backup directory>
--
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
|
|
|
|
Goto Forum:
Current Time: Wed May 15 21:28:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
