Oracle Database Recovery in Archive Log Mode: Best Practices and Procedures

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.