1 / 48

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

davina
Télécharger la présentation

Oracle Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Oracle Database Administration Session 11 Database Recovery

  2. 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

  3. 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

  4. 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

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

  6. 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

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

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

  9. 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

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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

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

  18. 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

  19. 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

  20. 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

  21. 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!!

  22. 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;

  23. 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;

  24. 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

  25. 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

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

  27. 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’

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

  29. 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;

  30. 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;

  31. 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

  32. 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

  33. 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

  34. 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>;

  35. 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

  36. 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

  37. 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.

  38. 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

  39. 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

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

  41. 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

  42. 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

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

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

  45. 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)

  46. 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

  47. 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

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

More Related