Oracle 9i Backup

1. Oracle 9i Backup ARCHIVELOG vs. NOARCHIVELOG mode User-managed backups: Logical vs. Physical Export utility Whole and Partial backups Hot and cold backups

2. Noarchivelog Mode vs. Archivelog Mode Source: http://www.adp-gmbh.ch/ora/concepts/backup_recovery/archive_vs_noarchive_log.html

3. ARCHIVELOG mode • Oracle copies filled online redo logs to disk rather than writing over them, creating archived redo logs.SQL> select log_mode from v$database; • Ways to set or change this mode: • Specify the mode at database creation using dbca • Use the ALTER DATABASE ARCHIVELOG;statement • To enable automatic archiving without shutting down the current instance, use ALTER SYSTEM ARCHIVE LOG START; • set the initialization parameter LOG_ARCHIVE_START to TRUE

4. User-managed backups • Logical backup • use the Export utility to make backups of logical objects (i.e., tables, views, and stored procedures) • use the Import utility to restore these logical objects • Physical backup • a backup of an Oracle database file or archived redo log located on the operating system • Can use OS utilities or RMAN to create and restore these files

5. Physical Backups (This information is from the following documentation: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/intro.htm#433631 )

6. Logical Backups • The Export utility: a simple way to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

7. The Export Utility Advantages: • An Export file: an Oracle binary dump file (typically located on disk or tape) • Files can be transferred using FTP or physically transported to a different site • Use the Import utility to transfer data between databases that are on systems not connected through a network • Files can also be used as backups in addition to normal backup procedures • Can display contents of an export file without actually performing an import (use Import SHOW parameter) Disadvantages: • Export dump files can only be read by the Oracle Import utility • The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file • Like snapshots of the database (therefore can’t apply redo to tables restored from export) • Has to have database fully open More on using the Export and Import utilities: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1004671

8. Whole and Partial backups • Whole (full) database backup • A backup of the control file and all datafiles that belong to a database • Is either consistent or inconsistent(an inconsistent backup: some of the files in the backup contain changes that were made after the files were checkpointed. An inconsistent backup needs recovery before it can be made consistent). • Can be done hot or cold • Partial database backup • any OS backup short of a full backup, taken while the database is open or shut down. • Examples of partial database backups: • a backup of all datafiles for an individual tablespace • a backup of a single datafile • a backup of a control file

9. Hot (online) and cold (offline) backups • Hot: • Database must be open • put the tablespaces in backup mode by issuing: SQL> ALTER TABLESPACE BEGIN BACKUP Oracle stops recording checkpoints to the tablespace's datafiles. copy datafiles in that tablespace on OS level SQL> ALTER TABLESPACE ... END BACKUP (or ALTER DATABASE END BACKUP) • Cold: *especially important when the db schema changes • performed while the tablespace or datafile is offline • take tablespaces offline: SQL> ALTER TABLESPACE tablespacename OFFLINE

10. Recommendations • Use archivelog mode • Perform regular full backups (not after unusual circumstances) • Consider time needed to perform backups and time to recover (the older your last backup is, the more redo log files will have to be appliedtakes longer to recover) • Perform scheduled cold backups (depends on database use) For example: before an upgrade or a mass load, after schema changes (new tablespace/datafile), after recovering, before using “resetlogs” • Take more frequent hot backups if the amount of downtime for cold backups must be limited • Backup archived log files on disks separate from the online redo logs

11. Oracle 9i Recovery User-managed recovery Recovery Manager (RMAN) Recommendations Recovery Resources

12. User-managed Recovery • Files used in recovery • Datafiles • Control files • Archived redo logs • Server parameter files

13. Record Keeping • Record locations for: • datafiles: V$DATAFILE • control files: V$CONTROLFILE • online redo logs: V$LOGFILE • archived redo logs: V$PARAMATER • use LIKE log_archive_dest% • Record backup file locations: • Correlate backup files with the original files • name backup files using same relative filename as the primary file

14. Datafile Recovery • Files to recover: • V$RECOVER_FILE • File location: • V$DATAFILE & V$TABLESPACE • Recovery: • Restore datafile from backup if available • If not available, can re-create when • all archived log files available • control file contains name of damaged file

15. Control File Recovery Scenarios • Losing member of multiplexed control file • Restoring to default location • Copy one of the other control files • Restoring to non-default location • Copy one of the other control files • Alter the CONTROL_FILES initialization parameter • Losing all members of multiplexed control files • Restore from backup using RESETLOGS option • Losing all current & backup control files • Create new control file if all online redo log files are intact

16. Archived Redo Logs & Parameter Files • Archived redo logs will be needed during recovery • All logs generated between last backup and recovery time • Logs will need to be on disk during recovery • Determine needed logs: V$RECOVERY_LOG • Keep copies of parameter files • Restore from backup if available • Use client file if server file not available

17. RMAN & User-Managed Procedures Comparison

18. Recovery Manager • Recommended by Oracle for 8i or higher databases • Do not need to place online tablespaces in backup mode • Performs incremental backups • Omits never-used blocks from datafile backups • Uses the repository to report on crucial information • Stores RMAN scripts in the recovery catalog • Easily creates duplicate of production database for testing purposes • Easily create a back up or standby database • Performs checks to determine whether backups on disk or in the media catalog are still available • Performs automatic parallelization of backup and restore operations • Tests whether files can be backed up or restored without actually performing the backup or restore • Performs archived log failover automatically for corrupt or missing logs

19. RMAN • RMAN Architecture • RMAN executable • Target database(s) • Recovery catalog database - optional • Media Management software • Running RMAN • From command line: rman • Oracle Enterprise Manager (OEM)

20. Recommendations RMAN • Don’t store the RMAN database on the same disk with the database being backed up • Don’t use the default account’s default password • Make sure to completely understand classical backup & recovery procedures. RMAN’s methodology is not different. • Synchronize RMAN recovery catalog when the database file layout changes • Monitor V$LONGOPS for long running backup sessions Generic • Once you have established your backup plan & scripts, test them and document your recovery.

21. Backup and Recovery Resources • Oracle9i Backup and Recovery Concepts http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96519/toc.htm • Oracle9i User-Managed Backup and Recovery Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/toc.htm • Oracle9i Recovery Manager User's Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm • Oracle9i DBA Handbook • Chapter 11: Backup and Recovery Options • Chapter 12: Using Recovery Manager (RMAN)