Re: Datafiles: one more try

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 24 Jun 2002 12:38:08 +0100
Message-ID: <3d1704a0$0$231$ed9e5944@reading.news.pipex.net>

"Martijn Rutte" <opal_at_xs4all.nl> wrote in message news:aa4da990.0206240138.4116f062_at_posting.google.com...
> The thing is, I am trying to save what can be saved. Of course I'd
> like to use RMAN or any other device to safely backup an Oracle
> datbase.
>
> But i can't, because I am simply not allowed to do so.

If by 'any other device' you mean that you are not allowed to issue the relevant sql for a backup, then I'm afraid your cause is lost. You cannot backup without issuing alter .... begin backup and alter ... end backup around the backup commands in hot backup mode and a cold backup requires a database shutdown. However you do not have to use RMAN to backup sql scripts will do just fine. I enclose a copy of one that will backup to a disk location in windows nt/2000 below.

////// script below ///////
/*
NB Original internet source for this adapted script is lost

script to hot backup a database to a given backup location in this case c:\backup
assumes the location given has subdirectories named after the databases that will be backed up there. This will backup all tablespaces including read only tablespaces.

eg c:\backup\orcl\ would hold backups of the database named orcl

note you should test this script throughly before uncommenting the last line to actually perform the backup.

*/

define dbname = ''
define arch_dest = ''
define backup_dir = 'c:\backup'
column dbname new_value dbname
column arch_dest new_value arch_dest
select name dbname from v$database;
select value arch_dest from v$parameter where name = 'log_archive_dest'; set feed off VERIFY OFF echo off serveroutput on trimspool on pages 9999 spool c:\scripts\backup_&&dbname..sql
declare
 cursor tbspc is
 select tablespace_name from
 dba_tablespaces
 where
 contents <> 'TEMPORARY';

 cursor datfil (tbspcname varchar2) is
 select df.file_name from dba_data_files df, v$datafile d  where
 df.tablespace_name = tbspcname
 and
 df.file_name = d.name;

 tabcur tbspc%rowtype;
 datcur datfil%rowtype;
 sql_string Varchar2(500);
 tsname1 varchar2(30);
 tsname2 varchar2(30);

begin

dbms_output.enable(1000000);
dbms_output.put_line('set echo on feed on pages 9999');
dbms_output.put_line('spool &&backup_dir\backup_&&dbname..log');
dbms_output.put_line('select to_char(sysdate,''dd-mm-yyyy hh24:mi'') from

dual;');
for t in tbspc loop
 sql_string := 'ALTER TABLESPACE '||t.tablespace_name||' BEGIN BACKUP;';  dbms_output.put_line(sql_string);
  for d in datfil(t.tablespace_name) loop    sql_string := 'host copy '|| d.file_name ||' &&backup_dir\&&dbname';    dbms_output.put_line(sql_String);
  end loop;
 sql_string := 'ALTER TABLESPACE '||t.tablespace_name||' END BACKUP;';  dbms_output.put_line(sql_string);
end loop;

dbms_output.put_line('alter database backup controlfile to trace;'); dbms_output.put_line('alter database backup controlfile to &&backup_dir\&&dbname\ctl_' || to_char(sysdate,'ddmmyy') || '.ctl;');

dbms_output.put_line('alter system archive log all;');
dbms_output.put_line('alter system archive log current;');
dbms_output.put_line('host copy &&arch_dest\*.* &&backup_dir\&&dbname');
dbms_output.put_line('select to_char(sysdate,''dd-mm-yyyy hh24:mi'') from

dual');
dbms_output.put_line('exit;');
dbms_output.put_line(' ');
end;
/
spool off
-- @c:\scripts\backup_&&dbname

/////// end script /////////////

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Mon Jun 24 2002 - 06:38:08 CDT