Oracle Database Administration

Oracle Database Administration Session 10 Database Backups

Why Do Systems Fail • System Outage Types • Physical Problems • CPU, Disk, etc • Design Problems • Software bugs in the Operating System, the database or the Application software • Operational Problems • Human error (DBA) • Environmental Problems • Power, earthquakes, temperature, etc

What can the DBA do? • A DBA has most control over operational failures. • But a DBA must be ready for the other types of failures. • Know failure points and work with other groups, outside of DBA realm.

What can the DBA do? • You must have a solid, proven, tested, backup plan. • You must have tested the backup • And practiced the recovery methods. • The Goal: minimize down time!

Hardware Protection • Try to have hardware and system redundancy. • UPS (uninterrupted power supply). • Disk mirroring, RAID technology or SAN technology. • On-site spare parts. • Redundant systems switch-over. • Switch-over sites.

DB Protection: Archivelog Mode • Archivelog files are copies of the redo log files. • Using the archive logs, the database can be completely recovered. • Makes it possible to take online (hot) backups.

DB Protection: Archivelog Mode • One of the disadvantages of archiving log files is that more disk space is required. • More administrative work is required to maintain the archive log destination directory. Must make sure they are backed up to tape. • If there is not enough space in the log area to write archived logs, the database will hang.

DB (Missing)protection :nonarchivelog mode • If you run your production database in noarchivelog mode, you can only recover using what’s in your current on-line logs. • Or if on-line logs aren’t sufficient, you can only recover up to the last complete backup and that’s it!

Types of Backup • Operating system, offline or cold backup • ‘Hot’ or on-line backup • Logical backup

OS or Cold Backup • Simplest form of backup. • Involves shutting down all database processes on a server and then backing up all Oracle related files. • This is also known as offline backup, since the database is offline or in a “cold” state. • Take a full backup weekly if you can. • Keep business demands in mind.

OS or Cold Backup • Copy or tar all the files to a local or remote location. • Back them up to tape or other media. • Once the database has restarted, all datafiles are touched and the timestamp on them is changed. • Result: consistent backup!

OS or Cold Backup • What does consistent mean? • That all datafiles and control files are consistent to a point in time - have the same SCN. • SCN is the system change number which Oracle marks in each file to maintain database consistency. • When transactions are committed they are assigned an SCN which Oracle records with the transaction entries in the redo log files. So, SCN is incremented with each new committed transaction.

OS or Cold Backup • The checkpoint process not only updates datafiles with modified blocks from memory (db buffer cache) but also updates datafile headers and control files with the current SCN. • When recovery takes place, Oracle looks at the current SCN in the datafiles and then starts from that point in the redo log files (or archive log files) and rolls committed transactions forward from that SCN point onward.

Cold Backup Steps • Shutdown the instance in normal mode. Also shutdown the listener, just to be complete. • Use O/S utilities for backup, e.g. tar or 3rd party tools (Legato, RMAN).

Cold Backup Steps • Backup Oracle related files, include control files, configuration files (init<SID>.ora, spfile) and the Oracle RDBMS code - i.e. $ORACLE_HOME. • Backup datafiles and online redo logs. • Backup archive log files, if available. • Startup the instance and listener.

Cold Backup Steps • Automate the process to eliminate errors. • Ensure that automated scripts have logging, so you can verify that they ran correctly and to help debug, if there is a failure.

Hot Backup • Also known as an online backup. • Taken while the database is open. • Provides for high database availability! • The database must be in archivelog mode. • The database is available to users during the backup.

Hot Backup • Schedule this type of backup during a low load period. • Normally used in conjunction with cold backups.

Hot Backup • Consists of backing up all the datafiles associated with the tablespaces. • Backing up the archived redo logs and the control files. • Tablespaces are placed into backup mode one at a time, during which time the associated datafiles are copied to another location.

Hot Backup • When a tablespace is placed in backup mode, all changes to the datafiles associated with that tablespace are handled as follows: • “When an 'alter tablespace begin backup' command is issued, the datafiles that belong to the tablespace are marked as hot-backup-in-progress. The dirty data buffers in the database buffer cache that belong to the database files are written out to the files and the datafiles are checkpointed. “

Hot Backup • “The datafile headers are updated to the SCN captured when the begin backup is issued. The datafile headers are not updated until the 'alter tablespace end backup' command is issued; however, the data blocks within the database files can continue to be read and updated.” • “When the datafile is restored from a hot backup, the recovery will begin from the SCN captured when the begin backup is issued. All changes applied during the hot backup time period will be rolled forward from the redo logs.” • Recovery is the process of getting all files to the same SCN.

Hot Backup Steps • Issue the following command to backup the “users” tablespace: • Alter tablespace users begin backup; • Backup the datafiles associated with the “users” tablespace using O/S utilities. • Then issue the following command: • Alter tablespace users end backup;

Hot Backup Steps • Backup all tablespaces in this manner until complete. • Backup the control files. • Issue a command to force an archive log switch and then backup all existing archived redo logs.

Hot Backup Steps • Look at hotbackup files as a complete unit: datafiles, control files, archive logs. • If you need to restore your database from a hot backup, you need this complete set of files. • Select * from v$backup to view file status.

Useful Commands • Alter database archivelog; • Archive log start; • Alter database open; • Archive log list; – to get the oldest online archive log sequence number • Alter database backup controlfile to trace;

Logical Backup • Also known as an export. • Creates a logical copy of database objects and stores them in a binary file. • Use it to import into another Oracle database. • It reads the data and stores it in the binary file. • It does not provide point-in-time recovery. • It cannot be used with archived redo log files.

Export Parameters • USERID username/password • BUFFER size of data buffer • FILE output files (EXPDAT.DMP) • COMPRESS import into one extent (Y) • FULL export entire database (N) • LOG log file of screen output

Export Modes • Table Mode • Use the TABLES parameter to export selected tables • The following objects/definitions are exported • Table definitions • Table data • Owner’s grants • Owner’s indexes • Table constraints • Table triggers

Export Modes • User Mode • Use the OWNER parameter to export a specific schema • The following objects are exported • Table mode objects/definitions • Database links • Views • Private synonyms

Export Modes • Full Database Mode • Use the FULL parameter to export all the database • The following objects are exported • User mode objects/definitions • roles • Tablespace definitions • Rollback segment definitions

Export Modes • All triggers • System privileges • Everything that is needed to recreate the database

Export Modes • A full export can be divided into the following • Export has 4 levels • Full • Tablespace • User • Table

Export Steps • Exp userid=system/passwd full=y file=full_export_file buffer=64K • exp help=Y • Tar -cvf • Tar -tvf • Tar -xvf

Export Steps • SQL> shutdown immediate • SQL> startup restrict open • exp username/passwd full=y file=exp.dmp constraints=y • SQL> alter system disable restricted session

Export • The export/import toolset has been the main utility to move data between databases • Import reads each record from the export dump file and inserts it into the target using the INSERT INTO command • It is slow

Data pump • Oracle Data Pump is the new and faster export/import toolkit available in the Oracle 10g Database • Instead of using SQL to move the data, it uses an API • It is 10 to 15 times faster than the current export

Data Pump • It is 5 times faster on import • It can also export and/or import specific types of objects, e.g. functions • The new utility is known as ‘expdp’ instead of ‘exp’ • Must create a directory to hold ‘dump’ file • Create directory <name> as ‘/u10/data’

Data Pump • Must create a directory to hold ‘dump’ file • Create directory <name> as ‘/u10/data’ • Must grant read, write on directory <name> to user • Example • Expdp user/pw directory=< > dumpfile=metadate_only.dmp content=metadata_only • Expdp help=y

Data Vaulting • Complete copy of system taken • Updated as change occurs to the original • Many other copies (sites) • Distributed risk • Backup • Recovery • Disaster Management

Flashback Database • Returns the database to a past time or SCN • Must open the database using • alter database flashback on; • Must be in archive log mode • Must set db_flaskback_retention_target • Time value

Flashback Database • Set db_recovery_file_dest_size • Size value • Set db_recovery_file_dest • Location, directory • After flashback must run ‘alter database open resetlogs;

Flashback Query • It allows us to see the value of a column as of a specific time • If the before-image copy of the block is available in the undo segment • Flashback Query only provides a fixed snapshot of the data as of a specific time • It is not a running representation of changed data between two time points.

Flashback Table • It allows the retrieval of a dropped table • The only way to do this before was to use tablespace point-in-time recovery • The dropped table stays in the same tablespace under a system defined name. • The table and it’s associated objects are placed into a ‘recycle bin’ • FLASHBACK TABLE EMP TO BEFORE DROP; • It can flashback other DDL operations

RMAN • Oracle Recovery Manager utility • Uses a catalog (database) to track what ever it backs up • Archivelog mode and nonarchivelog mode • File level backup • Block level backup • Writes directly to tape or to disk

RMAN • Prime time in 9i • Easier to manage in 10g, better in 11g • Integrates with various backup products, e.g.. Legato • Scriptable • Integrated with Oracle Enterprise Manager (OEM) • RMAN compatibility matrix

Keep in Mind • If you are backing up the Oracle files to disk, make sure no active database related files are on these disks. • Keep multiple copies of the control file. • Keep multiple groups of redo log files. • Keep the archive log files on separate disks from the database files.

Keep in Mind • Backup the control file using the following command: • alter database backup controlfile to trace • Take an offline backup at least once a week. • If 24 X 7 take daily online backups.

Reading • Chapter 11 DBA Handbook (11g) • Oracle Metalink • Backup and Recovery Handbook, Oracle Press • 10g or 11g Concepts Guides, Chap 15

Oracle Database Administration