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

��