Transcript IT 21003 Database Administration

IT 21003 Database Administration
Section 09
Backup and Recovery
Backup: The available options
Full Consistent (cold) Backup
Database shutdown, all files copied
(data, control, redo logs)
Partial Offline Backup
Backup of individual tablespaces while
offline
Partial Online (hot) Backup
Individual tablespaces backed up
while database is open and
tablespace is online
Export/Import Logical Backups Database normally closed to users
while full export takes place
Backup and Recovery
Determining the Files
 Information available in OPEN and MOUNT states
 Datafiles
 Select name From v&datafile;
 From control files
 Select file_name From dba_data_files;
 From data dictionary
 Online Redo Log Files
Select member From v$logfile;
 Current Control File Names
 Select name From v$controlfile;
Backup and Recovery
Full Consistent Backups
 Database must be closed during the backup
Makes a block-by-block copy of all database and
control files
 On large databases, full backup can take a long
time
Might not be able to have database down for this
period to get a full backup
Requires lots of storage for the backup files
 Easier and more reliable than other methods
 Can be performed in ARCHIVELOG and
NOARCHIVELOG mode
Backup and Recovery
A Full Backup
 Determine all files to backup
 Shutdown the database in NORMAL mode
This ensures the database is left in a consistent state
Use SHUTDOWN IMMEDIATE to quickly remove users
if needed
If SHUTDOWN ABORT must be used, startup in
RESTRICTED mode and then SHUTDOWN NORMAL
 Use a backup utility or OS commands to perform
the backup
 Startup the Instance and then Open the database
Backup and Recovery
A Partial Offline (Tablespace-Level) Backup
 Only the tablespace being backed up needs to be
offline during the backup
The SYSTEM tablespace or any tablespace containing
active rollback segments cannot be taken offline
 Allows portions of large databases to be dumped
during separate down periods
 More error prone than a full backup due to
increased operator input needed
Backup and Recovery
A Partial Offline (Tablespace-Level) Backup
 Identify the datafiles belonging to the tablespace
in question
Select tablespace_name, file_name,
From dba_data_files
Where tablespace_name = ‘USER1_TBSPC’;
 Ensure the tablespace is offline
Alter Tablespace user1_tbspc Offline;
 Use a backup utility or OS commands to perform
the backup
 Bring the tablespace back online
 Alter Tablespace user1_tbspc Online;
Backup and Recovery
A Partial Online (Hot) Backup
 Identify the datafiles belonging to the tablespace
in question
 For each tablespace in turn, perform the following
steps:
 Mark the beginning of the backup period
 Alter Tablespace user1_tbspc Begin Backup;
 Oracle stops recording checkpoints in the file headers
 Deferred rollback segments are used while is backed up
 Backup all files within the tablespace
 Mark the end of the backup period
 Alter Tablespace user1_tbspc End Backup;
 Oracle updates the file headers to the current database
checkpoint
Backup and Recovery
A Partial Online (Hot) Backup
 The Begin and End Backup statements should be
as close in time as possible
 LGWR logs whole blocks of data at any change made
within the tablespace during the backup period
 Could result in heavy logging activity
 Make sure tablespaces are backed up one at a
time to minimize block logging
Backup and Recovery
Recovery from a Full Backup
 Database is in ARCHIVELOG mode
 Connect As Sysdba so that the required privileges
are enabled
 Shutdown the database
 Restore all datafiles but not the Control Files and
Redo Log Files from the cold backup
Do Not Restore the redo log files, because that will
overwrite the current log files, which are needed in the
last stages of the recovery
 Startup the instance in MOUNT state
 Startup Mount instance_name Pfile =
parameter_file_name
Backup and Recovery
Recovery from a Full Backup
 Recover the database
 Use Recover Database
 Oracle applies archived logs to roll the database forward in
time
 If archived files are not available in the destination area, Oracle
prompts for the location of each log
 Having consumed the archived logs, Oracle then applies all the
information held in the online redo logs to complete the recovery
 The magic words that the recovery is complete are:
 Media Recovery Complete (MRC)
 The database can now be made available to
users
Backup and Recovery
Recovery from a Partial (Hot or Cold) Backup
 Close the database
Described in previous slides
Performed in the MOUNT state
All tablespaces can be recovered this way
 Open the database
 Performed in an OPEN state
 The SYSTEM tablespace and tablespaces containing
active rollback segments cannot be recovered in this way
Backup and Recovery
Recovery of a tablespace on a Closed database
 Connect As Sysdba and shutdown the database
 Restore the files for the tablespace (Copy)
 MOUNT but do not OPEN the database
 Put any offline files back online
 Recover Database or Recover Tablespace
tblspc_name;
Look for the MRC result
 Alter the database to an OPEN state
Backup and Recovery
Recovery of a tablespace on a Open database
 Connect As Sysdba
 Take the target tablespace offline
 Restore (copy) the files for the tablespace
 Recover the tablespace
 Put the tablespace back online
Backup and Recovery
The Export/Import Utilities
 Export and Import can be used for a variety of
purposes
 As part of a backup strategy
 Reorganize database storage
 Transfer data across systems
 Upgrade (migrate) to different versions of Oracle
 Store data offline for future use
 Tablespace Point-In-Time Recovery
Backup and Recovery
The Export/Import Utilities
 Essential Features
 Users may export/import their own objects
 Easiest method to restore a single table
 Must have privileged access to export other user’s
objects
 Export data file is a binary file in “internal” Oracle format
 Export does not drop exported objects
 Import can create objects as well as import rows
 Errors do not halt processing – is simply reported
 Can COMPRESS/NOCOMPRESS extents
Backup and Recovery
Export Levels
 Table
 Specific tables can be exported with or without data
 Oracle is able to export partitions and subpartitions
 User
 Allows export of all objects owned by one user
 DBAs may use this to export a series of users
 Tablespace
 Transportable Tablespaces
 Database
 DBAs can export all objects except those owned by
SYS
Do Not Use This
Backup and Recovery
Using Export/Import as part of a Backup
strategy
 A full database-level export is a logical dump of
the database
 Can be very slow, usually slower than an OS physical
dump
 Useful for recovering single tables from a DROP
command
 Database must be OPEN to perform an export
 Export guarantees a read-consistent view of only single
tables
 If multiple tables are being exported
 Lock them in SHARE mode to prevent concurrent updates or
 OPEN DATABSE in RESTRICTED mode
Backup and Recovery
Using Export/Import as part of a Backup
strategy
 Export/Import can be used to restore/reorganize a
database
 No rollforward recovery is possible
 Rebuild database after a change in the block size
 Must prebuild rollback segments and all tablespaces first
 Move tables across users; reduce fragmentation,
migration effects
Backup and Recovery
Methods of Exporting
 Two basic methods
 Interactively with a GUI utility
 Command Line
 Can be run at the OS command prompt or from
within Oracle
 Perform exports before file-level backups
 This catches data block corruption problems
Backup and Recovery
Import
 The only utility that can read export files
 Can selectively import types of database objects
 Useful for delaying and controlling index creation
 Can ask to show the export file contents
Backup and Recovery
Points to Consider
 Good for restructuring tables
 Set up a good PCTFREE before exporting data
 Drop old tables, then import under new storage
definitions
 Can import database objects without including
rows
 Useful for moving from development to live databases
 Import will carry on past any errors
 Disable referential constraints during import, and
recompile stored procedures as necessary
Backup and Recovery
Transportable Tablespaces
 Fast method to move tablespaces from one
database to another
 Both databases must be compatible and on the same
OS
 Tablespaces must be self-contained
 No outside constraints or partitions
 Make tablespaces READ ONLY and export the
metadata
 Export Transport Tablespace = Y Tablespaces = users
File = tblspc_expdata.dmp
 Use the OS to copy datafiles and export file to the
target machine
Backup and Recovery
Transportable Tablespaces
 Plug in the transported tablespaces to the target
database
 Imp Transport_Tablespace = Y Datafiles = …
Tablespaces = … File = …
 Rowids in the target database are no longer
unique
 A rowid is only unique within a table
 Transportable tablespacees are covered more in
later course levels
Backup and Recovery
Questions?