Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> shutdown normal vs. immediate

shutdown normal vs. immediate

From: Michael A. Fons <mfons_at_IX.NETCOM.COM>
Date: Tue, 26 Dec 1995 06:51:44 -0800
Message-Id: <9512261515.AA03023@alice.jcc.com> 





Regarding this guy:


>>

>>> Tomas Tenzer <t.tenzer_at_MAIL.DPC.UMASSP.EDU>


12/19/95 10:30am >>>


Hi



We have automated our nitely backups via the
following scripts. Our problem is that sometimes
the database does not come down completely.
The message I get the next morning when I logon is
"shutdown in progress".


No one is accessing Oracle at the time the backups
are run. Other nites everything runs fine. Any
ideas what the problem may be & solutions?
snip


<<



I think you might think about doing hot backups????  Then you would not
have to shut down at all.   If you are running version 6 then I under
stand.  If you are afraid to migrate to running in archive log mode I
think you should reconsider.  To help you on your decision here is the
process I went through in getting a site running OS/2 up and running
from noarchivelog mode to archivelog mode.  Before this upgrade they
were simply backing up their database with sytos brand backup while the
database was up, which I explained to them was probably not a very good
idea (later we learned that this backup was responsible for system
failures).  Note that this user had a requirement to be up 24/7:



ARCHIVE MODE:   Implementation Details and Corresponding Backup
Procedures for TFC Systems   OS/2 instructions



    
  
  1.   You can confirm that you are not currently in archive log mode in
the following way:
 sqldba
 connect internal;
 select log_mode from sys.v$database;
 exit
  
  2.   List files that you will be backing up and redo log files (since
you can skip backing them up on the monthly everything-but-the-database
full disk backup) and set them aside for the moment.
 sqldba
 connect internal;
 spool printme.lis
 select name from sys.v$datafile;
 select member from sys.v$logfile;
 show parameter control_files;
 spool off
 host
 print printme.lis
  
  3.   Nothing needed here for OS/2.  (Depending on what the Installation
or User s Guide looks like for the operating system Oracle is installed
on, you may have to do some additional steps here to archive filled
groups.  T
hese steps may involve exiting sqldba to configure how Oracle will
perform the archiving of the filled groups)
  
  4.   Since Oracle RDBMS on OS/2 provides for the ability to archive off
full redo logs add the following lines to your init<sid>.ora:
 log_archive_start = true
 log_archive_dest = drive:\<archived redo log directory>
 # in OS/2 the above parameters right-hand side can be %RDBMS70%\arch
after you have
 # created an arch directory under the rdbms70 directory.
 log_archive_buffers = 1
 log_archive_buffer_size = <a value close the maximum possible value
for this parameter/OS>
 # for OS/2 the above maximum is 127.
 log_archive_format = arc%S.arc   # this setting works for OS/2.
 (Regarding the last two parameters listed here:  the value of
log_archive_buffer_size should start out as  the max. value for that
parameter in the Installation or User s Guide.  If performance is
affected by this value
adjust it down until performance is the same as it was before archiving
was instigated.  If performance is still an issue it can be further
improved by dedicating a tape drive to backing up these files   use
queries on th
e topic Displaying Archiving Status Information of  Oracle 7 Server
Administrators Guide)
  
  5.   Create (if not already created) an archive directory on a disk with
lots of room as you specified as the value to be assigned to the
log_archive_dest variable in the init<sid>.ora file.
  
  6.   Set database to archive mode in a mounted but not open mode by
shutting down and restarting the database (this also will use the new
init<sid>.ora settings added above):
 (if re-entering sqldba:  connect internal;)
 shutdown;
 startup mount;
 alter database archivelog;
  
  7.   Open the database:
 alter database open;

  
  8.   Backup all files on disk except datafiles, redo logs, and control
files (see list of these files from step 2. Above) with a file backup
utility.  To do this create a backup directory on a disk that has
adequate room
 to hold all the datafiles, and then do the following:
        sqldba
        connect internal;
spool printme.lis
        select tablespace_name, bytes, file_name from
sys.dba_data_files order by 1, 3;
        spool off
        host print printme.lis
 then for each tablespace in turn do the following sequence of commands
(down to and including "alter tablespace <ts_name> end backup;"):
 alter tablespace <ts_name> begin backup;
 host
 ocopy <files associated with ts_name> <backup directory>
 <back up copy of datafile(s) just ocopy d using a file backup utility;
delete that copy of datafile is space is short>
 exit
 alter tablespace <ts_name> end backup;
These backups can occur while the database is up and running.






When you are done backing up all the datafiles, backup the control file
as well.


        alter database backup controlfile to
 \backup-directory\ctlback.ora  reuse;

        host
        <backup drive:\backup-directory\ctlback.ora to tape using a
file backup utility>
        exit





Notes:


For added security it is very wise to mirror your control files and
redo logs so if one file is corrupted it is recoverable.  The Oracle 7
Server Database Administration manual explains how to accomplish these
mirrored se


tups.



With archiving of redo logs complete recovery is often possible when
media failure occurs.  Note that there are a variety of types and
severities of media failure, and each combination has a different
method of recovery.


 Fortunately Oracle has documented these procedures well.



If you find one day that your disk is filled almost completely you may
get an oracle error like " archiver is stuck "  To fix this simply back
up to tape (or floppy in OS/2 use ocopy if necessary) and delete all
but the l


atest-numbered archived redo log in the arch directory.



 The following are instructions on for Mike Bauers and Sgt. Johnson
regarding backups required:



    
  
  1.   Once per month backup every thing on both disks except datafiles,
control files and redo log files.

  
  2.   Back up all datafiles (e.g., one/per night; see how backup affects
system performance).  The more you back up active datafiles the faster
any recovery will be since there will be less to recover. Save two or
three bac
kups of each datafile in the  before overwriting a backup copy of  that
datafile.  To obtain a list of tablespace to data file mapping do the
following:
        sqldba
        connect internal;
        spool printme.lis
        select tablespace_name, bytes, file_name from
sys.dba_data_files order by 1, 3;
        spool off
        host print printme.lis
After you decide what file to backup determine the associated
tablespace and do the following  (down to and including "alter
tablespace <ts_name> end backup;"):
 alter tablespace <ts_name> begin backup;
 host
 ocopy <files associated with ts_name> <backup directory>
 <back up copy of datafile(s) just ocopy d using a file backup utility;
delete that copy of datafile is space is short>
 exit
 alter tablespace <ts_name> end backup;
These backups can occur while the database is up and running.

  
  3.   Back up control files whenever a structural change is made to
the database (new datafile, etc.) and nightly after that night s
datafiles are backed up; again save three separate nights of control
files before recir
culating the file space:
 sqldba
 connect internal;
 alter database backup controlfile to  \backup-directory\ctlback.ora
reuse;
 host
 <backup drive:\backup-directory\ctlback.ora to tape using a file
backup utility>
 exit






The following is a listing of a command file and a SQL script which,
when run by typing "databack" at the OS/2 prompt, backs up whatever
datafiles are currently associated with a particular instance.  Note
that databack.c


md must be customized by changing the system password to that of your
instance, changing the directory location of databack.sql and changing
the location of the drive and directory where the backed up files
should go.  Th


ese scripts assume that you will be backing up your database to a
network drive.  It also assumes that this script is run on a machine
that can get into sqlplus, get into sqldba, and connect internal; that
would usually b


e the machine that is the database server itself.  This procedure
produces a file called databack.dba; be sure to keep an accurate hard
copy of this file on hand as it shows the name and directory of the
database datafile


s.  Having this listing might make life much easier in the event of a
hard drive failure:



File:  DATABACK.CMD


/*


Purpose:  to kick off databack.sql


Date:  12/19/95


Programmer:  Michael A. Fons, T-NETIX, Inc.

 */
/**************************************************/
/* NOTE:  TO CUSTOMIZE THIS PROGRAM CHANGE THE      */
/* LOCATION THAT THE DATABACK.SQL PROGRAM RESIDES */
/* IN AND CHANGE THE PARAMETER TO BE THE DRIVE AND */
/* DIRECTORY WHERE DATA AND CONTROL FILES ARE TO   */
/* TO BE WRITTEN.                                              */
/***************************************************/





"sqlplus system/manager @c:\fons\databack.sql c:\backup"



 File:  DATABACK.SQL


/*


Purpose:  to backup all datafiles and the control file to some
destination


directory while remaining up and running.
Date:  12/17/95


Programmer:  Michael A. Fons, T-Netix, Inc.
 */



define BACKUP_DRIVE_AND_DIRECTORY="&&1."



set pagesize 0


set feedback off


set verify off


column x format a132


set linesize 132


spool databack.dba


prompt spool databack.lis


prompt connect internal



select

'alter tablespace '||tablespace_name||' begin backup;' x,
'host ocopy '||file_name||' &&BACKUP_DRIVE_AND_DIRECTORY.' x,
'alter tablespace '||tablespace_name||' end backup;' x


from sys.dba_data_files


order by 1, 3;



prompt alter database backup controlfile to
'&&BACKUP_DRIVE_AND_DIRECTORY.\ctlback.ora'
prompt reuse


prompt /



prompt spool off


spool off



host sqldba lmode=yes < databack.dba


prompt ***********************************************


prompt  Check out databack.dba and databack.lis for any errors
prompt  that might have occured.

prompt ***********************************************





exit






4.  Archive logs are located in a directory that you can find the
location of in this manner:

        sqldba
        connect internal
        show parameter log_archive_dest
        exit
       The file names of these archived redo logs follows the format


arc#####.arc.  So for example a sample name might be arc00123.arc.
Oracle creates these files automatically, and the numbers on the files
increase sequ


entially from one file to the next.  Periodically you will need to
backup and clear out these archived redo logs.  To do this backup all
these files except the one with the largest number; then delete all but
the file wit


h the largest number.  Try doing this once a day (perhaps as part of
this nightly routine described above for datafiles and control files).
So for example if a directory of the archive log directory showed the
following


files

arc00123.arc
arc00124.arc
arc00125.arc




 then you would need to backup and delete arc00123.arc and
arc00124.arc, leaving arc00125.arc alone.
 The following is a listing of a command procedure that will back up
the archive logs in the above prescribed manner.  Note that it must be
customized to include the drive and path for not only the backup
directory but al


so the directory where the archived redo logs reside.  This command
procedure assumes that backups go to a network file server or some
other drive/directory-specifiable place:
File:  ARCHBACK.CMD


/*


  Purpose:  The purpose of this command procedure is to delete all
archived redo logs


        except the latest one (because that one may be currently being
created).


  Author:  Michael A. Fons, T-NETIX, Inc.
  Date: 12/7/95

 */
/************************************************/
/*  BELOW ARE THE ONLY STRINGS YOU SHOULD HAVE  */
/*  TO CHANGE IN THIS FILE:  THE RIGHT HAND          */
/*  SIDE OF THE FIRST ASSIGNMENT BELOW SHOULD BE */
/*  THE DIRECTORY TO WHICH THE ARCHIVED REDO     */
/*  LOGS SHOULD BE BACKED UP.  THE RIGHT HAND     */
/*  SIDE OF THE SECOND ASSIGNMENT BELOW SHOULD   */
/*  BE THE DIRECTORY WHERE THE ARCHIVED LOGS     */
/*  CURRENTLY RESIDE.                                     */
/************************************************/


backspec = "c:\backup"


archdir = "c:\oraos2\rdbms70\arch"


/************MAIN PROGRAM************************/


call RxFuncAdd 'SysFileSearch', 'RexxUtil', 'SysFileSearch'



ftmp = "tempfile.lis"


archspec = insert(archdir, "\arc*.arc")
del ftmp


dir "/f/o" archspec ">" ftmp


rc = SysFileSearch('.ARC', ftmp, 'file')
do i=1 to (file.0 - 1)

        say "Moving" file.i "to" backspec "..."
        copy "/v" file.i backspec
        if rc = 0 then do
                del file.i
        end
        else do
                say "Error copying file" file.i "."
        end




end

Received on Tue Dec 26 1995 - 10:16:04 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US