|
 |
|
Recover a Windows system
Oracle Tips by Burleson Consulting |
Example Recovery Script Generator for
Windows NT
REM Script to create a hot backup recovery script on Windows using ocopy
REM Created 6/23/98 MRA
REM
create table bu_temp (line_no number,line_txt varchar2(2000));
truncate table bu_temp;
set verify off embedded off esc ^
REM &&ora_home &&dest_dir
column dup new_value dup_it nopriWindows NTselect ''||chr(39)||'&&ora_home'||'\ocopy '||chr(39)||'' dup
from dual;
--
declare
--
-- Declare cursors
--
-- Cursor to get all tablespace names
--
cursor get_tbsp is
select tablespace_name from dba_tablespaces;
--
-- Cursor to create recovery commands
--
cursor rec_com (tbsp varchar2) is
select
&&dup_it||' '||'&&dest_dir'||'\datafiles\'||tbsp||file_id||'.bck '||file_name
from dba_data_files where tablespace_name=tbsp;
--
-- Cursor to create redo log recovery commands
--
cursor rec_rdo (num number) is
select
&&dup_it||
'
'||'&&dest_dir'||'\logs'||substr(member,instr(member,'\LOG',2,1),instr(member,'.',1,1))||'
'||
member
from v$logfile order by group#;
--
-- Temporary variable declarations
--
tbsp_name varchar2(64);
line_num number:=0;
line_text varchar2(2000);
num number:=0;
--
-- Begin build of commands into temporary table
--
begin
--
-- first, create script header
--
line_num := line_num+1;
select 'REM Recovery Script for '||name||' instance'
into line_text from v$database;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script uses ocopy - NT format backup commands'
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM created on '||to_char(sysdate, 'dd-mon-yyyy hh24:mi')||'
by user '||user
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM developed for RevealNet by Mike Ault - DMR Consulting
15-Dec-1998'
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM '
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script should be re-run anytime physical structure of
database altered.'
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM '
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
--
-- Now get tablespace names and loop through until all are handled
--
open get_tbsp;
loop
--
-- Get name
--
fetch get_tbsp into tbsp_name;
exit when get_tbsp%NOTFOUND;
--
-- Add comments to script showing which tablespace
--
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM Recovery for tablespace '||tbsp_name
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
--
-- The actual recovery commands are per datafile, open cursor and loop
--
open rec_com (tbsp_name);
loop
fetch rec_com into line_text;
exit when
rec_com%NOTFOUND;
line_num:=line_num+1;
insert into
bu_temp values (line_num,line_text);
end loop;
close rec_com;
end loop;
close get_tbsp;
--
-- Recover redo logs, normally you won't recover redo logs you
-- will use your current redo logs so current SCN information not lost
-- commands just here for completeness uncomment commands below to
-- enable redo log recovery (not advised)
--
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM Recovery for redo logs' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM Normally you will not recover redo logs' into line_text from
dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
--
-- Create host backup commands for all redo logs
--
/*open rec_rdo(num);
loop
fetch rec_rdo into line_text;
exit when rec_rdo%NOTFOUND;
num:=num+1;
line_num:=line_num+1;
insert into bu_temp values (line_num,line_text);
end loop;
close rec_rdo;*/
--
-- Now recover all archive logs
--
line_num:=line_num+1;
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM Recovery for archive logs' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
select 'REM' into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num:=line_num+1;
--
-- The next command builds the actual recovery command based on the
-- value of the log_archive_dest initialization parameter, it looks
for the
-- last right square bracket in the name and just uses that section
with
-- a wildcard
--
select &&dup_it||' '||'&&dest_dir'||'\archives\*.* '||value||'\*.*'
into line_text from v$parameter where name='log_archive_dest';
line_num:=line_num+1;
insert into bu_temp values (line_num,line_text);
end;
/
rem
rem Now generate output based on bu_temp table contents
rem
set verify off feedback off heading off termout off pages 0
set embedded on lines 132
column db_name new_value db nopriWindows NTcolumn line_no nopriWindows
NTselect name db_name from v$database;
spool rep_out\&&db\rec_db.bat
select * from bu_temp order by line_no;
spool off
rem
rem get rid of bu_temp table
rem
drop table bu_temp;
set verify on feedback on heading on termout on pages 22
set embedded off lines 80 esc \
clear columns
undef ora_home
undef dest_dir
exit
Once you have generated the scripts to
generate the online backup and recovery files, document them. The next
section is an example documentation procedure for the NT online backup
and recovery scripts.
Example Documentation Procedure for NT
Online Backup and Recovery Scripts
This section shows an example set of
procedures for use of the NT Oracle hot backup and recovery Scripts:
Backup:
1. Run nt_oline_bu.sql from SQLPLUS DBA
account.
2. Run nt_rec_db.sql script from SQLPLUS DBA
account.
3. Move a copy of the rec_db.bat script
generated in step 2 to the backup directory.
4. From a SQLPLUS command line session (using
the e:\orant81\bin\sqlplus executable) run the thot_bu.sql script
generated in step 1.
5. Once step 4 completes (should be less than
x hours) copy the backup directory (I:\backup) using the system backup
tool, to tape.
6. Remove the archive logs that were copied
from the database archive log destination to tape from the archive log
destination.
Recovery:
1. Using the system backup tools, restore the
Oracle backup files to the backup location on the database server
(Example: I:\backup).
2. Run the recovered copy of the rec_db.bat
script to restore the backup files to their proper locations.
3. Manually start the oracle services and the
tns listener process using the control panel services icon.
4. From the command line, use the svrmgrl
executable to startup and mount (but not open) the database:
>svrmgrl
svrmgrl>connect internal@<alias>.world
password: xxxxxxxxx
connected to an idle instance
svrmgrl>startup mount pfile=e:\orant\database\init<SID>.ora
(Be sure to use the
location of your initialization file)
<will see normal startup messages>
svrmgrl> recover
<server will prompt for needed files, they should be already copied to
machine so just
press return at each prompting>
media recovery complete
svrmgrl> alter database open
database altered
5. Shutdown and perform a cold backup of all
database files (essentially take the ocopy commands from inside the
thot_bu.sql script and run them as a .bat file. Do not backup the
archive logs, after a cold backup they are not needed anymore.
6. Remove all archive logs from system
7. Database is recovered, resume normal
operations.
The actual backup process can be automated on
NT using the WINAT scheduler available from the Microsoft website or
the Microsoft support or toolkit CD-ROM. A script similar to the one
shown in Source 5 should be used to start the backup.
Source 5
Example NT .bat Script To Start Backup
REM
do_hot_bu.bat
REM File to generate and execute hot backup script for Oracle
REM Used for ORTEST1 database only
REM Mike Ault DBMentors International
REM
REM First, generate the thot_bu.sql script
REM
cd c:\sql_scripts
REM
e:\orant81\bin\sqlplus -s system/manager@ortest1.world @c:\sql_scripts\nt_oline_bu.sql
REM
REM Now generate the recovery script so they are in-sync
REM
e:\orant81\bin\sqlplus -s system/manager@ortest1.world @c:\dmr_temp\nt_rec_db.sql
REM
REM Copy the recovery script to the backup destination
REM
copy c:\sql_scripts\rep_out\ortest1\rec_db.bat
i:\backup\rec_db.bat
REM
REM Run the backup script
REM
e:\orant81\bin\sqlplus -s
system/manager@ortest1.world
@c:\sql_scripts\rep_out\ortest1\thot_bu.sql
REM
REM End of script
REM
exit
|
