Oracle Database Administration

Oracle Database Administration Session 11 Database Recovery

ArchiveLog Mode • Log_archive_dest = filename • Filename is /disk1/archive/arch • Log_archive_dest should not be on a raw device • Log_archive_format = extension • Extension includes %s, so that the log sequence number is part of the filename • Log_archive_format = %s.arc

ArchiveLog Mode • The file will be named arch%s.arc • Example /disk1/archive/arch23005.arc • We can duplex the archive log files • Log_archive_duplex_dest = filename

ArchiveLog Mode • After changing a database from noarchivelog mode to archivelog mode, take a backup of the database files and the control file • The previous backups are now unusable

The Change Sequence • Shutdown immediate • Startup mount • Alter database archivelog; • Alter database open; • Shutdown immediate • Take a full backup

The Change Sequence • In the init.ora set the log_archive_start = true, so that the database will startup in archivelog mode, automatically • You can set log_archive_max_processes = N if you want multiple archive processes, ARCn

Recovery Process • Oracle performs recovery based on scn# • Two step Process • Roll Forward Redo files • Roll back Transaction recovery

Failure Recovery • Media failure • User failure • Instance failure • Network failure • Process failure

Failure Recovery • Each redo file has a low and high SCN • select * from v$log_history • Log switches • Alter system switch logfile • Checkpoints • Flushes modified data from cache to disk

Database Startup Status • Nomount init.ora • Mount recover, alter database • Open database

Noarchivelog Mode Recovery • When media failure occurs, a valid closed database backup must exist in order to recover • All Oracle files must be restored , even if only one data file is damaged or lost • Restore all data files, control files and redo logs

Noarchivelog Mode Recovery • To recover a database that is in noarchivelog mode • Shutdown abort • Delete existing files • Recover all the files • Restart the instance • Inform users they need to re-enter data from the time of the last backup

Recovery to a Different Location • To restore file(s) to a different location, if the original disk is unusable • Copy the file(s) to the new location • Mount the instance, use ‘startup mount’ • alter database rename file ‘/u04/oradata/user_01.dbf’ to ‘/u05/oradata/user_01.dbf’; • This command updates the control file with the new file location

Recovery to a Different Location • Make sure that there is a copy of the file in the new location before issuing the command • Open the database

Archivelog Mode Recovery • To recover a database that is in archivelog mode, we need • A valid backup containing the lost or damaged data files, after the database was set in archivelog mode • All archived logs from the backup you are restoring from, to the present • The redo logs that contain the transactions that are not yet archived

Archivelog Mode Recovery • If we meet all the above requirements • Make sure that the file(s) that are to be restored are not open • Check v$datafiles and v$tablespace to get the status • Restore only the lost or damaged file(s) • Do not restore online redo logs

Archivelog Mode Recovery • Place the database in either mount or open mode • Recover the data files by using the recover command

Archivelog Mode Recovery • The advantages of the archivelog mode • Only need to restore the lost or damaged file(s) • No committed data is lost • Restoring the files and then applying the archived and redo logs, takes the database up to the current point-in-time • The restore time is reduced

Archivelog Mode Recovery • The disadvantages of the archivelog mode • We need all the archived logs from the time of the last backup to the current time • If one is missing or damaged, we cannot complete recovery, because the archives are applied in sequence

Recover Syntax • Recover [automatic] database, can only be used for a closed database recovery • Recover [automatic] tablespace <number>| |<name> Can be used for open database recovery • Recover [automatic ] datafile <number> | <name> Can be used for both opened and closed database recovery

Recover Syntax • Automatic means that the archives and redo logs are applied automatically • Alter database can be placed in front of the recover command but will suppress some of the error messages. Do not use!!

Recover Syntax • Recover database; • Recover datafile ‘/u04/oradata/data_01.dbf’; • Alter database recover database; • Recover tablespace user_data; • Recover datafile 2; • Alter database recover datafile 2;

Recover Syntax • If we restore the archived logs from a different location than log_archive_dest, we must tell the database where they are • Alter system archive log start to < new location, full path> • Recover from ‘<new location, full path>’ database;

Recovery Process • When we issue recover datafile 4;, oracle will suggest(request) a specific log to apply. • If it exists in the log_archive_dest , enter auto and it will be applied • If you issue recover automatic datafile 4; It will apply the log , if it exists

Recovery Process • To find the files needing recovery • select * from v$recover_file; • The change# column returns the SCN (system change number) from where recovery must start • To get a list of all the archived log files needed for recovery, look in v$recovery_log

Recovery Process • V$archived_log gives a list of all archived log files

Scenario 1 • Corrupt data blocks on /u02, in a data file belonging to the SYSTEM tablespace • Shutdown the database • Restore the damaged file from the most recent valid backup • Startup mount • Recover datafile ‘/u02/oradata/system01.dbf’

Scenario 1 • Apply all needed archived and redo logs • Alter database open

Scenario 2 • A datafile is accidentally removed using unix commands • See what tablespace it belongs to • Select file_id f#, file_name, tablespace_name tablespace, status from dba_data_files;

File Offline • Do we need to take the file offline • Select d.file# f#, d.name, d.status, h.status from v$datafile d, v$datafile_header h where d.file# = h.file#; • If the file is offline , restore it • Recover datafile ‘/u04/oradata/data02.dbf’; • Or use recover tablespace user_data;

File Not Offline • If the file is not offline • Alter database datafile ‘/u04/oradata/data02.dbf’ offline • After all recovery is complete, alter the datafile online • If a tablespace is taken offline, then all the datafiles related to that tablespace are offline

Incomplete Recovery • Reasons • User error, like table dropped or bad data committed • Recovery fails because an archive log is lost • Loss of all control files • Loss of all unarchived redo log files and a data file

Incomplete Recovery • Types of incomplete recovery • Cancel-based recovery • Time-based recovery • Recovery using a backup control file • recover database until cancel; • Used when redo log file or group is damaged or when needed archive log file is lost

Incomplete Recovery • Recover [automatic] database until time ‘2000-04-12:14:22:03’; • Recover database until time ‘2000-04-12:14:22:03’ using backup controlfile; • Time is ‘YYYY-MM-DD:HH:MI:SS’; • The application of the archived redo logs can be automatic • Recover database until scn <integer>;

Incomplete Recovery • Incomplete recovery can only occur if the database is in archivelog mode • We need a valid offline or online backup of all data files • All archived logs, from the restored backup to a time before the failure

Incomplete Recovery • Perform a full offline backup of the existing database, including control files and redo logs • Restore all data files to take the database back in time • Place the database in mount mode and recover the database

Incomplete Recovery • Open the database using the resetlogs option • Alter database open resetlogs; • This prevents the application of redo logs that were skipped • Most recovery errors are due to the DBA.

Import • The import utility reads the export dump file and runs the commands that are stored there • It can be used to selectively bring back objects or users from the export dump file • If the export is incremental, then import from the incremental dump file first, then from the most recent complete export

Import • Some of the import parameters conflict with one another, eg FULL=Y and OWNER=GL • DESTROY=N is useful if we have two databases on a server and wish to export from one to another. The tablespace definitions are in the file and it will overwrite the existing files

Import • Another way is to recreate tablespaces first, on the second database. This will avoid the overwrite

Import • To import into another account use the following example • Exp system/passwd file=user1.dmp owner=joe grants=N indexes=Y compress=Y rows=Y • Imp system/passwd file=user1.dmp fromuser=joe touser=jane rows=Y indexes=Y

Import • Create a parameter file that hold all the parameters for an export or an import • List each one on a new line of a file • Use exp parfile=exp_parm_file • Userid=system/passwd Tables=(emp,dept) File=export.dmp

Data Pump Utility • Transfers data objects and data between Oracle databases • expdp help=y • Impdp help=y

Data Pump Utility • Import options (modes) • Full • Schema (user) • Tablespace • Table • No mode, defaults to full

Data Pump Utility • To import the directory must be already setup for that database • The dump file must be in that directory • Example • Impdp user/pw parfile=name.par • A parameter file hold the various parameters to complete the import ( or export)

Data Pump Utility • Parameter file example • Directory= <location> • Dumpfile=name.dmp • Content=metedata_only • Remap=joe:jane • The export and import function are desupported in 11g

Failure analysis • Mean time between failures • Mean time to recover when a failure occurs • Downtime • Scheduled outage for regular preventative maintenance • Unscheduled outage due to physical failure • Record why failure occurred • Service level agreements

Reading • Chapter 11 DBA Handbook • Oracle Metalink • Backup and Recovery Handbook, Oracle Press • Backup and Recovery Concepts, Oracle Guide.

Oracle Database Administration