Transcript Backup - University of North Carolina at Chapel Hill

Backup
The flip side of recovery
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
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
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
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!
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
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
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!
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
Disaster recovery planning
• Situation and response
• Include triggers and contacts
• Backup/redundant hardware?
• Off site hardware and facilities?
• Off site backups?
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
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
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
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
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
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
Tnsnames.ora
Listener.ora
client
server
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
...
(CONNECT_DATA =
(SID = INLS)
)
...
[Old style (8 or earlier)] :