Bulletproofing, Backups, and Disaster Recovery Scenarios

1
March 8, 2005
Bulletproofing, Backups, and Disaster Recovery
Scenarios
Presented by Stephen Rea, University of Arkansas
Cooperative Extension Service
Tuesday, 200 PM 330 PM Evaluation Code 247
2
Session Rules of Etiquette

• Please turn off your cell phone/pager
• If you must leave the session early, please do so
  as discreetly as possible
• Please avoid side conversation during the session

Thank you for your cooperation!
3
Introduction

• Backups? We don NEED no steenkin
• backups! - Famous last words of an ex-DBA
• Stephen Rea - Oracle Database Administrator
• University of ArkansasCooperative Extension
  Service

4
Introduction

• This session will show you step-by-step
  instructions for
• Bulletproofing your database against data loss
• Creating database backups (cold and hot backups)
• Recovering from various disaster scenarios
• Examples shown are for Oracle 9i (and 8i) and AIX
  UNIX 5.2 (not using
• RMAN here originally developed under Oracle
  7.3.4 and AIX UNIX 4.1.5).
• Oracle Backup and Recovery Classes (instructor
  led training)
• Enterprise DBA Part 1B Backup and Recovery (8i)
• Oracle9i Database Administration Fundamentals II
• 4-5 days
• 2,000-2,500 registration
• ???? transportation, hotel, meals, etc.

5
Benefits of Attending

• After this session you will be able to
• Minimize the potential for loss of your data.
• Recognize Oracle problems that you may encounter.
• Know step-by-step how to recover from those
  problems.
• Gain confidence in your disaster recovery
  ability.
• Increase your worth as a DBA.
• And, youve saved a bundle of money (2,000) as
  well as several days of your valuable time.

6
Topics of Discussion

• Bulletproofing against data loss
• Creating backups (cold and hot)
• Recovering from disaster scenarios

7
Bulletproofing

• To lessen the possibility of data loss, you can
• Enable archivelog mode (to reapply the changes
  during recovery required by most disaster
  recovery scenarios)
• Separate the archive logs from the redo logs
  (allocate to separate drives likewise for the
  following items)
• Separate the redo logs and archive logs from the
  datafiles
• Multiplex (mirror) the redo log groups and
  members
• Multiplex (mirror) the control file
• Multiplex (mirror) the archive log files
• Oracle 9i Database Administrator's Guide
  (chapters 6 - 8, 12)

8
The Basics - Shutting Down Your Database

• Position to the correct database (setting the
  Oracle SID)
• For example, in UNIX Or, in NT
• . oraenv c\gt set ORACLE_SIDPROD
• PROD c\gt set ORACLE_HOMEd\oracle\v9
  204
• Connect as sysdba (from a dba group user login,
  such as oracle)
• Oracle 8.1.5 and above Prior to Oracle 9i, use
  Server Manager
• sqlplus / as sysdba svrmgrl
• (SQLgt connect / as sysdba) SVRMGRgt
  connect internal
• Shut down the database (connected as sysdba)
• SQLgt shutdown immediate (kills sessions,
  rolls back pending changes)
• If shutdown immediate fails or hangs (do this
  in another session)
• SQLgt shutdown abort
• SQLgt startup
• SQLgt shutdown immediate

9
The Basics - Starting Up Your Database in Various
Stages

• Database Startup Sequence (from shutdown state
  connected
• as sysdba in sqlplus)
• nomount reads initltSIDgt.ora (or spfile),
  allocates SGA memory, starts background processes
  (such as pmon - process monitor) SQLgt
  startup nomount (from shutdown state)
• mount opens control files SQLgt startup
  mount (from shutdown state, or, ) SQLgt
  alter database mount (from nomount state)
• open opens datafiles and online redo logs,
  performs crashrecovery SQLgt startup open
  (from shutdown state, or, ) SQLgt alter
  database open (from nomount or mount state)
• Note The pfileinitltSIDgt.ora option on the
  startup command can be
• used to specify the databases init.ora file
  pathname.

10
Enabling Archiving (to recover database changes)

• Steps Update init.ora, create archive log
  directory,
• shutdown, mount, set archivelog mode, open
• Edit the init.ora file to add the archive log
  parameters
• vi ORACLE_HOME/dbs/initPROD.ora
• log_archive_dest /u01/oradata/PROD/archive
  logs/
• log_archive_format arch_PROD_S.arc
• log_archive_start true
• mkdir /u01/oradata/PROD/archivelogs
• From the mount state after shutdown, start up
  archiving
• and open the database
• SQLgt alter database archivelog
• SQLgt alter database open
• SQLgt archive log list (or select from
  vdatabase)

11
Moving Datafiles (with shutdown)

• Steps Shutdown, move files, mount, rename files,
  open
• After shutdown, use an O/S command to move the
  datafile (done
• from within the unix sqlplus session here ! is
  the host command
• for unix is the host command for NT)
• SQLgt !mv /u03/oradata/PROD/devl_PROD_01.dbf
• /u04/oradata/PROD
• From the mount state, rename the datafile that
  you moved
• SQLgt alter database rename file
• '/u03/oradata/PROD/devl_PROD_01.dbf' to
• '/u04/oradata/PROD/devl_PROD_01.dbf'
• Then, open the database and look at the change
  made
• SQLgt alter database open
• SQLgt select from vdatafile

12
Moving Datafiles (without shutdown)

• Steps Offline tablespace, move files, rename
  files, online
• tablespace
• sqlplus "/ as sysdba"
• SQLgt alter tablespace development offline
• SQLgt !mv /u03/oradata/PROD/devl_PROD_01.dbf
• /u04/oradata/PROD
• SQLgt alter database rename file
• '/u03/oradata/PROD/devl_PROD_01.dbf' to
• '/u04/oradata/PROD/devl_PROD_01.dbf'
• SQLgt alter tablespace development online
• SQLgt select from vdatafile

13
Moving Datafiles (using control file)

• Steps Create textual control file, shutdown,
  move files,
• update datafile pathnames in control file, run
  control file
• sqlplus "/ as sysdba"
• SQLgt alter database backup controlfile to trace
• SQLgt show parameter user_dump
• SQLgt shutdown immediate
• SQLgt exit
• mv /u03/oradata/PROD/PROD.dbf
• /u04/oradata/PROD
• (continued )

14
Moving Datafiles (using control file)

• cd /u00/oracle/admin/PROD/udump
• ls -ltr .trc
• vi prod_ora_16060.trc (or similar name, like
  ora_16060.trc)
• Delete the lines before the STARTUP NOMOUNT line
• Rename the datafiles (g/DATAFILE/,//s/u03/u04/)
• RECOVER (comment out the RECOVER line)
• Execute the edited control file, which starts up
  the database
• sqlplus / as sysdba
• SQLgt _at_prod_ora_16060.trc
• SQLgt select from vdatafile

15
Adding Redo Log Members (mirror across drives)

• Steps Add member file to given group
• SQLgt alter database add logfile member
• '/u04/oradata/PROD/log_PROD_1C.rdo
• to group 1
• SQLgt select from vlogfile

16
Increasing Maximum Number of Members (to add)

• Steps Create textual control file, update
  maxlogmembers,
• shutdown, run control file
• sqlplus "/ as sysdba"
• SQLgt alter database backup controlfile to trace
• SQLgt !ls -ltr /u00/oracle/admin/PROD/udump
• SQLgt !vi /u00/oracle/admin/PROD/udump/prod_ora_16
  060.trc
• Delete the lines before the STARTUP NOMOUNT
  line
• Edit maxlogmembers value (such as changing
  from 2 to 3)
• RECOVER (comment out the RECOVER line)
• SQLgt shutdown immediate
• SQLgt _at_/u00/oracle/admin/PROD/udump/prod_ora_16060
  .trc

17
Adding Redo Log Groups (to limit log switch waits)

• Steps Add group with member files of given size
• SQLgt alter database add logfile group 4
• ('/u00/oradata/PROD/log_PROD_4A.rdo',
• '/u01/oradata/PROD/log_PROD_4B.rdo')
• size 500K
• SQLgt select from vlogfile

18
Multiplexing Control Files (to mirror across
drives)

• Steps Shutdown, make control file copies, update
  init.ora,
• startup
• sqlplus "/ as sysdba"
• SQLgt shutdown immediate
• SQLgt !cp -p /u03/oradata/PROD/ctrl_PROD_01.ctl
• /u01/oradata/PROD/ctrl_PROD_02.ctl
• SQLgt !vi ORACLE_HOME/dbs/initPROD.ora
• control_files (/u03/oradata/PROD/ctrl_PROD
  _01.ctl,
• /u01/oradata/PROD/ctrl_PROD_02.ctl)
• SQLgt startup
• SQLgt select from vcontrolfile

19
Topics of Discussion

• Bulletproofing against data loss
• Creating backups (cold and hot)
• Recovering from disaster scenarios

20
What To Back Up

• Files to back up during one backup cycle
• Datafiles (for all tablespaces)
• Control Files (binary and textual versions)
• Redo Log Files (cold backups only, not hot
  backups)
• Archive Log Files (archived redo logs, if
  archivelog mode is enabled)
• Parameter File (init.ora (and/or spfile)
  init.ora is not inthe database like
  ORACLE_HOME/dbs/initPROD.ora)
• Password File (if used it is not in the
  database namedlike ORACLE_HOME/dbs/orapwdPROD)
• Oracle9i User-Managed Backup and Recovery Guide
  (chapter 2)

21
What To Back Up (DB-based SQL)

• Getting names of datafiles, temp files, control
  files, and
• redo log files
• SQLgt select name from vdatafile
• SQLgt select name from vtempfile
• SQLgt select name from vcontrolfile
• SQLgt select member from vlogfile
• Getting names of datafile and temp file
  tablespaces
• SQLgt select tablespace_name,file_name from
• dba_data_files order by tablespace_name
• SQLgt select tablespace_name,file_name from
• dba_temp_files

22
What To Back Up (DB-based SQL)

• Getting locations of archive logs and archive
  parameters
• SQLgt select name,value from vparameter
• where name in ('log_archive_dest',
• 'log_archive_format','log_archive_start')
• SQLgt show parameter archive
• SQLgt archive log list
• For just the most recent archive log file names
• SQLgt select name from varchived_log
• where trunc(completion_time) gt
  trunc(sysdate)-5

23
What To Back Up (SID-based - preferred)

• Getting files using SID-based standard naming
• convention (preferred)
• find / -name 'PROD' ! -type d
• 2gt/dev/null gtbackemup.dat
• Scripts for getting the list of backup files
• backup_list.shl (SID-based best for script)
• backup_list.sql (DB-based - but, dont use this
  in
• an actual backup script for cold backups)

24
Cold Backups (with database down)

• Cold Backup Requirements
• Database is shut down (shutdown immediate)
• Complete backup from same point in time
• Database not available for queries or updates
• Either archivelog mode or noarchivelog mode
• All associated files are backed up
• ? Datafiles ? Control Files
• ? Redo Log Files ? Archive Log Files
• ? Parameter File ? Password File

25
Cold Backups - Getting the files to back up

• Create the textual control file (then, shutdown)
• sqlplus "/ as sysdba"
• SQLgt alter database backup controlfile to trace
• SQLgt shutdown immediate
• SQLgt exit
• ls -ltr /u00/oracle/admin/PROD/udump/ tail
  -1
• sed 's/. \//\//' gtgtbackemup.dat
• Get the SID-based file list with archivelogs at
  end of list
• find / -name 'PROD' ! -type d 2gt/dev/null
  grep -v 'arc' grep -v 'gz' gtgtbackemup.dat
• ls /u01/oradata/PROD/archivelogs/.arc.gz
  gtgtbackemup.dat ls /u01/oradata/PROD/archivelogs/
  .arc gtgtbackemup.dat

26
Cold Backups - File Backup Options

• Copy the files to a staging (backup) directory
• cat backemup.dat sed 's/\(.\)\/\(.\)/
• cp -p \1\/\2 \/u03\/backups\/prod1\/\2/'
  sh
• Or, compress (zip) the files to a staging
  directory
• cat backemup.dat sed 's/\(.\)\/\(.\)/
• gzip -cv1 \1\/\2 \/u03\/backups\/prod1\/\2.g
  z
• touch -r \1\/\2 \/u03\/backups\/prod1\/\2.gz
  /' sh
• Then, start the database and back up the staging
  directory
• SQLgt startup
• ls /u03/backups/prod1/ cpio -ovC64
  gt/dev/rmt0
• Or just copy the files directly to tape (no
  staging directory)
• cat backemup.dat cpio -ovC64 gt/dev/rmt0

27
Hot Backups (with database up)

• Hot Backup Requirements
• Database is up (tablespace begin/end backup)
• Backup of datafiles per tablespace over time
• Database is available for queries and updates
• Archivelog mode required
• Special backup processing required for files
• ? Datafiles (by ts) ? BACKUP Control
  Files
• ? NO Redo Logs ? Archive Logs (switch)
• ? Parameter File ? Password File

28
Hot Backups of Tablespaces

• Steps For each tablespace begin tablespace
  backup,
• copy (or zip) datafiles to a staging (backup)
  directory,
• end tablespace backup
• SQLgt alter tablespace development begin backup
• SQLgt !cp -p /u03/oradata/PROD/devl_PROD_.dbf
• /u03/backups/prod1
• SQLgt alter tablespace development end backup
• Note that no shutdown or startup is needed

29
Hot Backups of Log Files

• Steps Switch logfile, get list of archivelogs,
  copy log files
• SQLgt alter system switch logfile
• ls /u01/oradata/PROD/archivelogs/.arc.gz
  gtlogfile.lst
• ls /u01/oradata/PROD/archivelogs/.arc
  gtgtlogfile.lst
• sleep 5 (for script)
• cat logfile.lst sed "s/\(.\/\)\(\/.\)/
• cp -p \1\2 \/u03\/backups\/prod1\/\2/"
  gtlogfile.shl
• sh logfile.shl

30
Hot Backups of Control Files

• Steps Create binary and textual control files,
  find and copy
• textual control (back up the control file last)
• SQLgt alter database backup controlfile to
• '/u03/backups/prod1/controlfile.ctl'
• SQLgt alter database backup controlfile to trace
• ls -ltr /u00/oracle/admin/PROD/udump
• cp -p /u00/oracle/admin/PROD/udump/prod_ora_160
  60.trc
• /u03/backups/prod1

31
Backing Up the Staging (Backup) Directory

• Steps Change to the staging directory, compress
  or zip
• the files (optional), copy resulting files to
  tape
• cd /u03/backups/prod1
• gzip -vN1
• find /u03/backups/prod1 cpio -ovC64
  gt/dev/rmt0
• Bonus Compressing while Exporting using UNIX
  Pipes
• mknod /tmp/exp_pipe p
• gzip -cNf lt/tmp/exp_pipe gtprod.dmp.gz
• exp " '/ as sysdba' " file/tmp/exp_pipe
  fully
• compressn logprod.dmp.log
• rm -f /tmp/exp_pipe

32
Additional Nightly Processing (optional)

• Create and keep the current copy of your textual
  control file
• Back up the current copy of your textual init.ora
  file (if you are using a server parameter file
  (spfile), use the "create pfile from spfile"
  command to create it)
• Make a full database export for quick table
  restores and to check datafile integrity
• Generate sql definitions for all of your tables
  and indexes (using indexfile option of the import
  command)
• Gather statistics on datafile and index space
  usage and table extent growth for proactive
  maintenance (shown on next slide)

33
Other Nightly Processing

• Gathering space usage statistics
• SQLgt select segment_name,segment_type,extents,max_
  extents
• from sys.dba_segments where extents 5 gt
  max_extents or
• extents gt 50 order by segment_name,segment_type
  desc
• SQLgt col "Free" format 9,999,999,999
• SQLgt col "Total Size" format 9,999,999,999
• SQLgt select tablespace_name,sum(bytes) "Total
  Size" from
• dba_data_files group by tablespace_name
• SQLgt select tablespace_name,sum(bytes) "Free"
  from
• dba_free_space group by tablespace_name
• For each index, find the number of deleted rows
  (may rebuild it)
• SQLgt validate index ltindex namegt
• SQLgt select name,del_lf_rows_len from index_stats

34
Topics of Discussion

• Bulletproofing against data loss
• Creating backups (cold and hot)
• Recovering from disaster scenarios

35
Disaster Recovery What Happened?

• Things to check to determine the problem
• Messages from database startup (backup first)
• Alert log in the database's bdump directory
• (/u00/oracle/admin/PROD/bdump/alert_PROD.log)
• Recent bdump and udump trace (.trc) files
• (background_dump_dest, user_dump_dest)
• Oracle processes ( ps -ef grep ora, including
• ora_smon_PROD, pmon, dbwr, lgwr)
• Recent /home/jobsub/.lis and .log files

36
What To Restore (from backup)

• ONLY the affected datafile for full recovery
• ALL datafiles for incomplete recovery
• All archivelog files generated since the datafile
  backup was made
• DON'T restore control files (unless all of them
  are lost)
• DON'T restore online redo log files (unless they
  are not used during recovery - then must
  resetlogs)
• SQLgt connect / as sysdba
• SQLgt startup mount
• SQLgt alter database open resetlogs

37
Disaster Recovery Overview

• Backup first, then try startup (shutdown after)
• Primary recovery options
• Recover database (complete or incomplete
  recovery)
• Recover datafile (one datafile complete recovery
  only)
• Recover tablespace (all of its datafiles
  complete only)
• Generic recovery steps
• Shutdown database or offline tablespace or
  datafile
• Restore datafile(s) (and archivelogs) from backup
• Issue recover command (database, datafile, or
  tablespace)
• Bring tablespace or datafiles online
• Oracle9i User-Managed Backup and Recovery Guide

38
Basic Recover Database Complete Recovery

• State mount (from shutdown state), datafiles
  online
• Steps Restore datafile(s), mount, online the
  datafile(s)
• if needed, recover database, open
• cp -p /u03/backups/prod1/devl_PROD_01.dbf
• /u03/oradata/PROD (restore datafile(s))
• sqlplus "/ as sysdba"
• SQLgt startup mount
• SQLgt select from vdatafile if any
  offline, then
• SQLgt alter database datafile
• 'ltfull offline datafile pathnamegt' online
• SQLgt set autorecovery on (or recover
  automatic below)
• SQLgt recover database
• SQLgt alter database open

39
Basic Recover Database Incomplete Recovery

• State mount, datafiles online
• Steps Restore ALL datafiles, mount, online the
  datafiles,
• recover database until ..., open. Like complete
  recovery,
• except for "recover database" command (use only
  one)
• SQLgt recover automatic database until time
• '2005-02-14154500'
• SQLgt recover database until cancel
• (no autorecovery steps through logs until
  CANCEL
• entered)
• SQLgt recover automatic database until change
  43047423
• (SCN (system change number) for archivelogs is
  in
• varchived_log as first_change and
  next_change - 1
• online redo logs is in vlog as
  first_change (subtract 1))
• SQLgt alter database open resetlogs (then,
  backup)

40
Basic Recover Datafile From Mount State

• State mount, datafile online or offline
• Steps Restore datafile, mount, recover datafile,
  online the
• datafile if needed, open
• cp -p /u03/backups/prod1/devl_PROD_01.dbf
• /u03/oradata/PROD (restore datafile)
• sqlplus "/ as sysdba"
• SQLgt startup mount
• SQLgt recover automatic datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
• SQLgt select from vdatafile if datafile is
  offline, then
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf' online
• SQLgt alter database open

41
Basic Recover Datafile From Open State

• State open (with database up), datafile offline
• Steps Offline the datafile, restore datafile,
  recover datafile,
• online the datafile (not for system datafile)
• sqlplus "/ as sysdba"
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
  offline
• SQLgt !cp -p /u03/backups/prod1/devl_PROD_01.dbf
• /u03/oradata/PROD (restore datafile)
• SQLgt recover automatic datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf' online

42
Basic Recover Datafile From Open After Startup

• State open, datafile offline
• Steps Mount, offline the datafile, open, restore
  datafile,
• recover datafile, online the datafile (not for
  system datafile)
• sqlplus "/ as sysdba"
• SQLgt startup mount
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
  offline
• SQLgt alter database open
• SQLgt !cp -p /u03/backups/prod1/devl_PROD_01.dbf
• /u03/oradata/PROD (restore datafile)
• SQLgt recover automatic datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf' online

43
Basic Recover Tablespace From Open State

• State open, tablespace offline
• Steps Offline the tablespace, restore
  tablespaces datafiles,
• recover tablespace, online the tablespace (not
  for system
• tablespace)
• sqlplus "/ as sysdba"
• SQLgt alter tablespace development offline
  immediate
• (immediate rolls back currently pending
  transactions)
• SQLgt !cp -p /u03/backups/prod1/devl_PROD
• /u03/oradata/PROD (restore datafiles)
• SQLgt recover automatic tablespace development
• SQLgt alter tablespace development online

44
Basic Recover Tablespace From Open After Startup

• State open, tablespace offline
• Steps Mount, offline any bad datafiles, open,
  offline the
• tablespace,restore tablespaces datafiles,
  recover tablespace,
• online the tablespace (not for system
  tablespace)
• sqlplus "/ as sysdba"
• SQLgt startup mount
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf'
  offline
• SQLgt alter database open
• SQLgt alter tablespace development offline
• SQLgt !cp -p /u03/backups/prod1/devl_PROD
• /u03/oradata/PROD (restore datafiles)
• SQLgt recover automatic tablespace development
• SQLgt alter tablespace development online

45
Checking Logs and Trace Files

• Checking alert log and trace files for pmon,
  lgwr, dbwr, arch
• SQLgt select value from vparameter where name
• 'background_dump_dest'
• grep background_dump_dest
• ORACLE_HOME/dbs/initPROD.ora
• cd /u00/oracle/admin/PROD/bdump
• tail -200 alert_PROD.log
• ls -ltr .trc
• (look at latest pmon, lgwr, dbwr, arch trace
  files, if any)
• cat prod_pmon_13612.trc (process monitor trace)
• cat prod_lgwr_32306.trc (redo log writer
  trace)
• cat prod_dbwr_43213.trc (database writer
  trace)
• cat prod_arch_22882.trc (archiver trace)

46
Archivelogs Disk Volume Filled Up

• Symptoms
• Database freezes. No space for archivelogs (df
  -k).
• Messages
• Users None (sessions freeze).
• Logins ERROR ORA-00257 archiver error.
• connect internal only, until freed.
• Logs
• Alert log ORA-00272 error writing archive log.
• Arch trace ORA-00272 error writing archive
  log.
• Note Use oerr for error description, such as
  oerr ora 257

47
Archivelogs Disk Volume Filled Up

• Steps Free up space on archivelogs volume by
  moving files or
• older archivelogs off, or, by deleting old
  archive log files that have
• already been backed up
• File remove_old_logs.shl
• echo "You must be logged in as user Oracle to run
  this script,"
• echo "which removes all archivelog files older
  than X days."
• echo "Enter number of days to keep \c"
• read DAYS_KP export DAYS_KP
• find /u01/oradata/PROD/archivelogs -name
  '.arc.gz' -mtime
• DAYS_KP -exec rm \
• find /u01/oradata/PROD/archivelogs -name '.arc'
  -mtime
• DAYS_KP -exec rm \
• echo "Results after deletions"
• du -k
• df -k

48
Loss of Control Files

• Symptoms
• May be none until shutdown and/or startup.
• Messages
• Shutdown If deleted ORA-00210 cannot open
  control
• file '/u03/oradata/PROD/ctrl_PROD_01.ctl'. If
  overwritten
• ORA-00201 control file version incompatible
  with
• ORACLE version. (May have to shutdown abort.)
• Startup ORA-00205 error in identifying control
  file
• '/u03/oradata/PROD/ctrl_PROD_01.ctl'. Also, if
• overwritten ORA-07366 sfifi invalid file,
  file does not
• have valid header block.

49
Loss of Control Files Textual Control File
Recovery

• Steps Edit out header from latest textual
  control file you have
• available (in udump directory), run control file
  (as long as no
• datafiles have been added)
• sqlplus "/ as sysdba"
• SQLgt shutdown abort
• SQLgt !ls -ltr /u00/oracle/admin/PROD/udump/.trc
• SQLgt !vi /u00/oracle/admin/PROD/udump/prod_ora_31
  494.trc
• Delete the lines before the STARTUP NOMOUNT
  line
• SQLgt _at_/u00/oracle/admin/PROD/udump/prod_ora_31494
  .trc

50
Loss of Control Files Backup Control File
Recovery

• Steps Restore ALL datafiles AND control files
  (NOT online
• redo log files), recover using backup
  controlfile
• sqlplus "/ as sysdba"
• SQLgt shutdown abort
• At this point, restore ALL datafiles AND control
  files from the
• last backup, but, NOT the online redo log files.
• SQLgt connect / as sysdba
• SQLgt startup mount
• SQLgt recover automatic database using backup
  controlfile
• (AUTO on "ORA-00308 cannot open archived
  log ...")
• SQLgt alter database open resetlogs (then,
  backup)

51
Loss of TEMP Datafile

• Symptoms
• Error message on large sorts (select distinct,
  order
• by, group by, union).
• Messages
• Loss during ORA-01157 cannot identify data
  file 3
• file not found.
• Loss before ORA-01116 error in opening
  database
• file 3.
• Both Followed by ORA-01110 data file 3
• '/u03/oradata/PROD/temp_PROD_01.dbf'.
• Logs
• No alert.log entries or trace files generated.

52
Loss of TEMP Datafile

• State open or mount
• Steps Offline datafile, drop and recreate TEMP
  tablespace
• (here we are using locally managed temporary
  tablespace)
• SQLgt alter database datafile
• '/u03/oradata/PROD/temp_PROD_01.dbf' offline
• If from mount state SQLgt alter database open
• SQLgt select file_name,bytes/1024 kbytes from
  dba_temp_files
• SQLgt select initial_extent/1024 kbytes from
  dba_tablespaces
• where tablespace_name 'TEMP'
• SQLgt drop tablespace temp
• SQLgt !rm /u03/oradata/PROD/temp_PROD_01.dbf
• SQLgt create temporary tablespace temp
• tempfile '/u03/oradata/PROD/temp_PROD_01.dbf'
• size 40064K extent management local uniform size
  640K

53
TEMP Datafile Offline

• Symptoms
• Error message on large sorts (select distinct,
  order
• by, group by, union). (Like Loss of TEMP
  Datafile.)
• Messages
• ORA-00376 file 3 cannot be read at this time.
• ORA-01110 data file 3
• '/u03/oradata/PROD/temp_PROD_01.dbf'.
• Steps Recover datafile (DB open), online the
  datafile
• SQLgt recover automatic datafile
• '/u03/oradata/PROD/temp_PROD_01.dbf'
• SQLgt alter database datafile
• '/u03/oradata/PROD/temp_PROD_01.dbf' online

54
Loss of INACTIVE Online Redo Log Group (Archived)

• Symptoms
• Database crashes switching to lost log group.
• Messages
• Users ORA-01092 ORACLE instance terminated.
• Disconnection forced.
• Logins ERROR ORA-03114 not connected to
  ORACLE.
• ERROR ORA-00472 PMON process terminated with
  error.
• Logs
• Alert log No indication.
• Pmon trace ORA-00470 LGWR process terminated
  with error.
• Lgwr trace ORA-00313 open failed for members
  of log
• group 3 of thread 1. ORA-00312 online log 3
  thread 1
• '/u03/oradata/PROD/log_PROD_3B.rdo'.

55
Loss of INACTIVE Online Redo Log Group (Archived)

• Steps Mount, drop logfile group, add logfile
  group, open
• sqlplus "/ as sysdba"
• SQLgt startup mount
• If "ORA-01081 cannot start already-running
  ORACLE
• shut it down first", then "startup force" (or
  "shutdown
• abort" and "startup mount").
• Shows "Database mounted." and "ORA-00313 open
  failed
• for members of log group 3 of thread 1".
• SQLgt select bytes/1024 K from vlog where group
  3
• SQLgt select member from vlogfile where group
  3
• SQLgt alter database drop logfile group 3
• SQLgt alter database add logfile group 3
• ('/u03/oradata/PROD/log_PROD_3A.rdo',
• '/u03/oradata/PROD/log_PROD_3B.rdo') size 500K
• SQLgt alter database open

56
Loss of INACTIVE Online Redo Log Group Member

• Symptoms
• No apparent symptoms.
• Logs
• Alert log ORA-00313 open failed for members of
  log
• group 3 of thread 1. ORA-00312 online log 3
  thread 1
• '/u03/oradata/PROD/log_PROD_3A.rdo'. (Each
  switch.)
• Steps Drop logfile member, add logfile member to
  group
• SQLgt select from vlog where group 3
• If status is active or current for group, first
  do
• SQLgt alter system switch logfile
• SQLgt alter database drop logfile member
• '/u03/oradata/PROD/log_PROD_3A.rdo'
• SQLgt alter database add logfile member
• '/u03/oradata/PROD/log_PROD_3A.rdo' to group 3

57
Loss of CURRENT Online Redo Log Group (Unarchived)

• Symptoms
• Database freezes (after cycling back). Plenty of
  space for
• archivelogs (df -k).
• Messages
• Users None (sessions freeze).
• Logins ERROR ORA-00257 archiver error.
  Connect
• internal only, until freed.
• Logs
• Alert log ORA-00286 No members available, or
  no
• member contains valid data. ORACLE Instance
  PROD
• Can not allocate log, archival required. Thread
  1 cannot
• allocate new log, sequence 21. All online logs
  needed
• archiving.
• Arch trace ORA-00286 No members available, or
  no
• member contains valid data.

58
Loss of CURRENT Online Redo Log Group (Unarchived)

• Steps Startup to get log group number, then do
  an incomplete
• recovery (as described earlier) up to the start
  time for the lost
• log group (Note all changes in all redo log
  groups will be lost)
• sqlplus "/ as sysdba"
• SQLgt shutdown abort
• SQLgt startup
• Shows ORA-00313 open failed for members of log
• group 2 of thread 1.
• SQLgt shutdown abort
• (continued ) .

59
Loss of CURRENT Online Redo Log Group (Unarchived)

• At this point, restore ALL datafiles AND the lost
  online redo
• log group's files from the latest backup, but,
  NOT the control
• files.
• SQLgt connect / as sysdba
• SQLgt startup mount
• SQLgt select sequence, bytes, first_change,
• to_char(first_time, 'DD-MON-YY HH24MISS'),
  status
• from vlog where group 2
• SQLgt recover automatic database until time
• '2005-02-14125959' (1 second before loss)
• SQLgt alter database open resetlogs (and
  backup)

60
Failure During Hot Backup

• Steps Mount, determine datafiles in hot backup
  state,
• end backup (Oracle 7.2 and above), open
• sqlplus "/ as sysdba"
• SQLgt startup mount
• SQLgt select df.name,bk.time from vdatafile df,
• vbackup bk where df.file bk.file and
  bk.status
• 'ACTIVE'
• SQLgt alter database datafile
• '/u03/oradata/PROD/devl_PROD_01.dbf' end backup
• SQLgt alter database open

61
Bonus Topic

• The Ultimate Disaster

Photo Courtesy of NOAA
Evaluation Code 247
61
62
Summary

• Bulletproofing against data loss
• Enabling archiving of redo log files
• Separating types of database files
• Moving datafiles
• Adding redo log members
• Mirroring log files and control files
• Creating backups (cold and hot)
• Identifying files to back up
• Performing cold backups (DB down)
• Performing hot backups (DB up)
• Statistics, exports, and other tasks

63
Summary

• Recovering from disaster scenarios
• Determining cause of failure
• Identifying files to restore
• Basic disaster recovery scenarios
• Complete and incomplete
• Database, datafile, tablespace
• Specific file type recovery scenarios
• Step-by-step recovery commands

64
Summary

• Backup, backup, backup

• Practice, practice, practice your
• disaster recovery plans

• Dont Panic! Follow your plan

http//www.uaex.edu/srea/bkupreco.htm
65
Questions and Answers
Whew! Glad Thats Over!Any Questions?
66
Thank You!

• Stephen Rea
• srea_at_uaex.edu
• More Oracle and Banner information can be found
  at my Oracle Tips, Tricks, and Scripts web site
• http//www.uaex.edu/srea
• Please complete the on-line Evaluation Form
• Evaluation Code 247