================================ DBA Exam 4 - Backup and Recovery ================================ Some Questions to study-up on * When do you re-create the control file? * Considerations when developing a backup/recovery strategy * Sequence of events for complete/incomplete recovery * When to do cancel/change/time based incomplete recovery * Type of recovery (above) to do when you have corrupt redo logs, archived and not * Archive log mode init.ora parameters, start/stop, when to use, what it does for you * SMON, PMON use in recovery * Failure types and DBA intervention for recovery WHAT IS A CHECKPOINT? ==================== A Checkpoint is a database event which synchronizes the data blocks in memory with the datafiles on disk. A checkpoint has two purposes: (1) to establish data consistency, and (2) enable faster database recovery. How is recovery faster? Because all database changes up to the checkpoint have been recorded in the datafiles, making it unnecessary to apply redo log entries prior to the checkpoint. During a checkpoint the following occurs: - The database writer (DBWR) writes all modified database blocks in the buffer cache back to datafiles, - Log writer (LGWR) updates both the controlfile and the datafiles to indicate when the last checkpoint occurred (SCN) If the optional background process CKPT, the checkpoint process, is enabled, then CKPT performs the operations of LGWR above. The advantages of enabling CKPT are discussed below. A checkpoint occurs when Oracle performs a log switch from one group to another, when the number of operating system blocks specified by LOG_CHECKPOINT_INTERVAL have been written to the redo log, when the time specified by LOG_CHECKPOINT_TIMEOUT has expired, or when a checkpoint has been forced by the DBA. EBU (Enterprise backup utility) automated oracle7 backups executables and a backup catalog maintains current and historial backup info catelog is stored in an oracle db Export Cumulative Exports Only database data that has been changed since the last cumulative or complete export is exported. catexp.sql (for import and export) create session priv EXP_FULL_DATABASE to exp another user IMP_FULL_DATABASE or DBA role EXPort types COMPLETE CUMULATIVE INCREMENTAL DAY: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 X I I I I I I C I I I I I I C I I I I I I X Sun Sun Sun Sun To restore through day 18, first you import the system information from the incremental Export taken on day 18. Then, you import the data from: 0. Import SYSTEM information from 18, INCTYPE=SYSTEM 1.the complete Export taken on day 1 2.the cumulative Export taken on day 8 3.the cumulative Export taken on day 15 4.three incremental Exports taken on days 16, 17, and 18 Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into a buffer cache, and rows are transferred to the evaluation buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file. Direct path Export extracts data much faster than a conventional path export. Direct path Export achieves this performance gain by reading data directly, bypassing the SQL Command Processing layer and saves on data copies whenever possible USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) ROWS export data rows (Y) PARFILE parameter filename CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency LOG log file of screen output STATISTICS analyze objects (ESTIMATE) DIRECT direct path (N) TRIGGERS export triggers (Y) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file QUERY select clause used to export a subset of a table The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TABLESPACES list of tablespaces to transport RECORD tracks incremental export timestamps table in data dictionary of exports INCEXP table RECORDLENGTH write buffer size keep to multiple of OS block size BUFFER exp retrieved block in and stores in user process of size BUFFER CONSISTENT=y read-consistant export if not used, only read consistant at an object level if yes, exp does a SET TRANSACTION READ ONLY first generates large rollback export order: db links sequences table defs, table data synonyms views stored procs ref integrity triggers indexes bitmap/functional indexes snaphots snaphot logs SYS objects are NOT exported. only passwords for SYS and SYSTEM are exported Import BUFFER user process, amount of mem alloced to user size process to store incoming rows before being sent to the DB imp uses ARRAY INSERTS inserts multiple rows per trip setting the COMMIT=Y parameter tells oracle to commit per array insert RECORDLENGTH number of OS blocks read from imp file into BUFFER DESTROY=N retains current DB datafiles doesnt import with REUSE parameter setting import order: SCN System changes number Allocated to all committed transactions Rollforward/Rollback Recovery works via a rollforward in time, appling archive redo logs then rolling back uncommitted transactions from the rollback segments Backup Types CLOSED Database Offline backup Archive mode may or may not be required DB is closed, shutdown normal or immediate All DB files are backed up Simple, easy to do, little operator interaction, reliable Recovery only to the last complete closed backup DB file info v$datafile v$controlfile v$logfile dba_data_files Dont need to do read-only ts datafiles (only once) OPEN Database Online Backups 24hr databases Individual tablespaces backed-up We backup all datafiles, ctl files, param and passwd files, archived redo logs We can not backup the online redo logs at any time Can backup tablespaces (all datafies for the TS) or individual datafiles while DB is open Generates more redo as file headers are frozen on a BEGIN BACKUP Archive logging must be enabled. ARCH process must be enabled, either automatic archival or manually via the DBA/scripts alter tablespace BEGIN BACKUP checkpoint all datafiles for the tablespace transactions still applied to datafiles during the backup if transactions are ongoing whilst in backup mode, heavy redo is generated as the file are in a fuzzy state, ie. block SCN's may differ with the SCN of the datafile header (as it is currently frozen) during recovery, redo logs are applied from when the TS was frozen alter tablespace END BACKUP checkpoint all datafiles for the tablespace Consistent Exports Backup status info v$backup file#,status,change# (scn), time status (backup status) can be ACTIVE or NOT ACTIVE v$datafile_header status, fuzzy status is ONLINE, OFFLINE, fuzzy column may be YES or NO if fuzzy reads are occuring because of the online nature of the datafile Backup CTL file alter database backup controlfile tp ''; alter database backup controlfile to trace; gives an editable ctl file for ctl file recreation need a most recent copy of ctl after each backup holds critical db structure info, data file used, status of tablespaces, scn and log#'s CONTROL_FILES parameter in init.ora to multiplex them commands that change the DB structure (and the ctl file) alter db [add,drop] logfile alter db [add,drop] logfile member alter db [add,drop] logfile group alter db [noarchivelog, archivelog] alter db rename file create tablespace alter tablespace [add,drop,rename] datafile alter tablespace [read write, readonly] drop tablespace Readonly TS backup alter tablespace read only; checkpoint performed for all TS files file headers are frozen with current SCN perform a fiull backup of all TS files DBWR only writes to datafiles that are read write, normal checkpoints occur on these must backup ctl file after as status in ctl file for the TS has now changed if not, you will have to recover the control file Logging and NoLogging Logging all changes recorded to redo fully recoverable from last backup no additional backup req No-logging minimal redo generated not recoverable from last backup additional backup may be req Commands its available on are alter [table, index] create [table, index] Types of failures and trouble-shooting Statement logical error handling statement of an oracle program User Process abnormal disconnect, terminated abnormally, user program raised unhandled exception PMON detected abnormally terminated sessions, rollsback, release resources and locks User error usually requires recovery, ie. deletes, truncates, drops etc. Instance Failure power outage, HR faulure (cpu, mem, disk), bkground process fails re-start DB, rollword executes, SMON does rollforward by applying changes recorded in the online redo log files from last checkpoint uses onoine redos and rollback segs Media failure disk crash etc Instance Failure process unsync'ed files check for unsynced files rollforward rollback segments populated during rollforward since redo logs have committed and uncommitted trans a rollback seg entry is added if an uncommitted block is found in the datafile and no rollback entry exists redo logs applies directly to datafiles via disk=to=disk transfer (not via SGA) no archive logs read during this process since we start from LAST checkpoint during instance recovery (always in online redo's) committed and uncommitted data in datafiles all commited data resides in the datafiles now after rollforward uncommitted data may still exist in rollback segs rollback phase rollback segments based on who requests the block first commit data in data files after rollforward then rollback only committed data is in disk sync files all files are now in sync Datafile synchronization all data files must be in sync for the DB to OPEN includes datafiles, redologs, control files based on the current CHECKPOINT number (SNC) - CHANGE# not applicable for read-only tablespace files - SEQUENCE# sync is based on the current redo log checkpoint and sequence numbers archive and redos recover committed trans and rollback uncommited trans to sync the data files all commits get an checkpoint# a redolog may consist of many commites (checkppoints) we record 1st check-point in the log sequence only in the data dictionary Alert.Log written to by oracle server, db status info for the instance records all instance startups and shutdowns every create,alter,drop operation on rollbacks, tablespaces, database database errors and events Trace Files created by background processes written to background_dump_dest additional diagnostic ingo Datafile Checksum db_block_checksum=true every block given a checksum before a write used to check for corrupt blocks on a read returns an error and writes info to trace file affects every block, including temp blocks checksum computed and stored in block header upon a read, checksum us used to check for corruption ORA-01578 flaged, trace file written Log File Checksum log_block_checksum=true check for corrupt online redo logs checksum invalid, tries other member DB can hang if all members give checksum errors re-init log file with "alter database clear logfile" DB Verify verification of datafiles by checking structural integtiry of the data blocks can be used online datafiles portion of the datafiles or entire file verify offline datafiles can direct output to an error log dbv dbverf[80] Keyword Description (Default) ---------------------------------------------- FILE File to Verify (NONE) START Start Block (First Block of File) END End Block (Last Block of File) BLOCKSIZE Logical Block Size (2048) LOGFILE Output Log (NONE) FEEDBACK Display Progress (0) must specify block size. Recovery with Archiving Loss of disk/datafile/corruption restore all datafiles, ctl and redo from last complete backup restore passwd/param is optional if onoine redo hasnt been overwritten, then dont have to replace it easy-to-do recovery time = restore data file time lost data reapplied manaully point of last whole closed backup can restore files to disfferent location startup mount alter database rename file '' to ''; alter database open Complete Recovery with Archiving complete recovery datafiles restored must be offline restore only lost or damagaed datafiles dont restore ctl, redo, password or init.ora file alter database datafile offline place db in mount or open mode recover time = time to copy files + apply archived redo adv all need to restore damaged files no commited trans lost recover time = time to copy files + apply archived redo can be done while DB is open (except SYSTEM and ROLLBACK SEGs) disadv MUST have all redo logs. methods close DB recover database or recover datafile commands shutdown startup mount recover recover database; recover datafile ''; alter database recover database; open DB file lost (not for SYSTEM or rollback segs tablespace datafiles) alter database offline file copy from backup recover file recover tablespace recover datafile 2; alter database recover datafile 2; recovery [automatic] datafile tablespace To change the default archive log path alter system archive log start to ''; recover from '' database Auto recovery set autorecovery on or use AUTO command during prompts or automatic setting Files needed for recovery v$recover_file lists file#, error, time, last change# v$archived_log lists archived logs, seq#, next#, first#, archived? v$recovery_log lists archived needed for recovery