Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.