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