1 / 18

Effective Database Backup Strategies and Recovery Options

Learn types of failures, backup methods, disaster recovery planning, security roles, and privileges in Oracle database management.

deanna-juarez
Download Presentation

Effective Database Backup Strategies and Recovery Options

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. Backup The flip side of recovery

  2. Types of Failures • Transaction failure • Transaction must be aborted • System failure • Hardware or software problem resulting in volatile memory loss • Media failure • Disks or tapes • Disasters

  3. Backup Methods • Physical (block by block copy) • Cold • db is shutdown, files are not changing • shutdown instance, backup files (datafiles, control files, parameter files, redo logs & archive logs), start db • Hot • db is open and files are being used • each tablespace is put into “backup” mode in turn and then backed up using RMAN or EBU, then put back online • MUST have archive logging on to use this method

  4. Backup Methods (cont.) • Logical (actually reads data) • Export • Rebuild scripts • Use to re-create the structure of the database • control file create script • alter database backup controlfile to trace noresetlogs; • database create script • roles and users create scripts

  5. So what’s best? • Use a combination of options • If must have point in time recovery, run with archive logging ENABLED • Additional points • mirror control files and redo logs • keep as many archive logs on disk as possible • disk copies of backup files and archive logs should be on their own disk • make backups of control files if db structure changes! • Can stage backups so they go faster (disk to disk to tape) • TEST, TEST, TEST!

  6. Developing a backup strategy... • Points to consider • downtime • backup window • types of failures • power loss, disk, natural disaster, human error? • recovery time • retention time • Basic database setup

  7. You are the DBA... • 24x7 database (or very small backup window) • need fast recovery • need ability to recover db objects • need point in time recovery

  8. More db availability options • 3-way mirror of db • expensive in disk space, but very safe • Hot standby database • copy of db is created, logs from primary db are applied directly to the copy • Replicated database • db is copied through snapshots or through replication rules • if replicated, transactions can occur against both copies of the db - must have conflict resolution in place!

  9. And Recovery... • Determine what kind of recovery is required • instance recovery or process recovery (restart the db) • user error recovery or statement failure recovery (fixing affected transaction only) • media recovery • which files are destroyed? • recover database, tablespace or datafile? • resetlogs? • Before doing recovery, perform precautionary backup • ALWAYS backup after recovery

  10. Disaster recovery planning • Situation and response • Include triggers and contacts • Backup/redundant hardware? • Off site hardware and facilities? • Off site backups?

  11. Security, Roles, and Privileges • Object vs. system (access) privileges • roles • privilege “groupings” • can have other roles granted to them • basic procedure • create user • create role • grant privs to role • grant role to user

  12. Predefined roles • DBA • create, alter, drop anything (user, procedure, table, etc.) • select, insert, update, delete or lock any table or view • force any transaction • execute any package, trigger, procedure • grant any privelege or role, become any user • backup any tablespace/table • audit anything, analyze anything, restrict or kill sessions • Connect • alter session • create database link, sequence, session, synonym, table, view • Resource • create procedure, sequence, table, trigger

  13. PUBLIC • Never grant to PUBLIC with grant or admin option • Not wise to grant anything other than SELECT to PUBLIC • PUBLIC should not own any data object • PUBLIC may own synonyms and views

  14. Finding Privileges and Roles • Dba_users, dba_tab_privs, dba_sys_privs, dba_role_privs • With admin option • sec_obj.sql • sec_priv.sql • sec_role.sql

  15. Profiles • In Oracle 8, can set up profiles to impose resource limits, enforce password policies, lock accounts after failed logins, etc. • sessions_per_user, idle_time, cpu_per_session • failed_login_attempts • password_life_time, password_grace_time, password_verify_function • dba_profiles

  16. Installing and configuring the client • Obtain CD from ATN or SILS library • Install software • Configure tnsnames.ora • Provides all necessary connection information • Is created automatically when use configuration wizard (last step of install) • Can be created manually, if desired

  17. Listener.ora Tnsnames.ora client server

  18. Tnsnames.ora Excerpt from tnsnames.ora [New style (8i)] : INLS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pearl.ils.unc.edu)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = INLS.ils.unc.edu) ) ) Excerpt from tnsnames.ora [Old style (8 or earlier)] : ... (CONNECT_DATA = (SID = INLS) ) ...

More Related