B&R I
•Oracle structure
v$sga
v$sgastat
v$database
v$instance
v$process
v$bgprocess
v$datafile
•Large pool
-set by LARGE_POOL_SIZE
-min 300k max 2gb
-DBWR_IO_SLAVES (if > 0, ARCn,LGWR,RM are set to 4)
-BACKUP_TAPE_IO_SLAVES (if true, an i/o slave process is used to rw tape device)
•Data Buffer Cache
-stores mru data blocks
•DBWn
-writes dirty buffers from db buffer cache to data files
-triggered by ckpt event
-DBW1 -> DBW9
-not useful on uniprocessor systems
•Data files
-commited: normal
-uncommited: shutdown abort, db fail
•Tablespace
-temp: can be recreated
-rbs: should have own tablespace
-readonly tblspc
-index data: can be recreated
•Redo Log Buffer
-circular buffer about changes made
•LGWR
-writes redo entries to redo log files:
redo log buff is 1/3 full
time out occurs (every 3 sec)
before DBWn writes
when transaction commits
•Redo Log Files
-need at least two log groups
-each group should >2 members
-multiplexing Redo Log Files.
-To move a redo log file
1 alter system switch log file;
2 copy file to new location
3 alter database db
rename file ‘file'[, ‘file’] …
to ‘file'[, ‘file’]…
-To add a member to a group
alter database db
add logfile member
…
-To drop a member from a group
alter database db
drop logfile member
‘file'[, ‘file’]…
•Redo Log switch
-current redo log group is assigned a log seq no which is also used for sync
-when
LGWR filled one log file group
dba issues ALTER SYSTEM SWITCH LOGFILS
-checkpoint automatically occurs
•Dynamic views
-v$log
-v$logfile
-v$log_history
•CKPT
-at ckpt
1 modified db buffers written to db file -> db header files marked curr
2 ckpt seq no written to cont file
-occurs
1 every log switch
2 fast-start checkpointing
3 > log_checkpoint_interval need to be read during instance recovery
4 instance shutdown unless aborted
5 alter system checkpoint
6 tblspc taken offline or an online backup is started
-sync
at ckpt, ckpt no is updated in evere db file header and in cont file
if redo, control, and data files have same ckpt no -> consistent db
-expedite instance recovery
•Multiplexing control files
1 shutdown db
2 copy cont file to different loc
3 edit CONTROL_FILES
4 start db
•ARCn and Archive Log Files
B&R II
•Statement failure
-no dba intervention required
•User Process Failure
-no dba intervention required
-PMON automatically handles it
•User error failure
•Instance failure
-no dba intervention required
-SMON handles it automatically
•Fast-start parallel rollback
-SMON uses parallel query slaves when a transaction is larger than 100 rollback blocks
-FAST_START_PARALLEL_ROLLBACK
FALSE none
LOW 2 * cpu_count
HIGH 4 * cpu_count
-v$fast_start_servers
-v$fast_start_transactions
•Archivelog mode
-media recovery can be restored to
specific point in time
end of a specific arch log file
specific scn
-log_archive_dest_n where n = 1 – 5
~_1 = “location=/arch1 mandatory reopen” (default to reopen=300)
~_2 = “service=db1”
-log_archive_min_succeed_dest
-log_archive_dest_state_n=[defer|enable]
•Enable archivelog mode
1 shutdown immediate
2 startup mount
3 alter database [archivelog| noarchivelog]
4 alter database open
5 shutdown immediate
6 full db backup
•Multiple arch process
-log_archive_max_processes
-log_archive_start
•Enabling archive process in an open instance
1 archive log list
2 alter system archive log start to ‘/u04/ora/test/log’;
•Start or stop additional arch proc
-alter system set loq_archive_max_processes=3;
-alter system archive log stop;
-log_archive_start=false
-set db to noachivelog
•Dynamic views
v$archived_log
v$archived_dest
v$log_history
v$database
v$archive_processes
archive log list;
•Closed Database Backups
Files to backup
-data files V$DATAFILE dba_datafiles
-control files V$CONTROLFILE
-parameter files V$LOGFILE
-password files
-redo log files (optional)
Procedure
1 shutdown db
2 backup files
3 open db
•Open db backup
1 alter tablespace user begin backup;
2 cp user01.dbf /backup!user01.dbf
3 alter tablespace user end backup;
4 alter system switch logfiles;
5 repeat for all tblspc including system, temp, rbs…
-v$backup
-v$datafile_header
•Backup control file
-alter database backup control file to ‘control.bak’;
-alter database backup control file to
trace;
-needed after every change to db config.
•Read-only Tablespace backup
-alter tablespace query_data read only
•Logging and nologging options
-enable nologging on tblspc, tbl, ind, or partition for direct load operations
-enable logging afterward
B&R III
Complete recoveryin achive mode
•Restore file to diff location
1 startup mount pfile=init.ora;
2 alter database rename file
‘/d1/user.dbf’ to ‘/d2/user.dbf;
3 alter database open;
•Files needed for recovery
-v$recover_file
-v$log_history
-v$recovery_log
•Apply redo log files automatically
sql>set autorecovery on;
or
sql>recover automatic datafile 4
•Recovering a closed database
1 restore file from backup
2 startup mount pfile=’/d2/df2.dbf’
3 alter database open;
•recover open db initially opened
-recovered files do not belong to system or rb tblspc
1 restore datafile
2 recover datafile ‘/d2/db2.dbf’;
or recover tablespace USER_DATA;
3 alter database datafile ‘/d2/db2.dbf’ online;
or alter tablespace USER_DATA online
•recover open db initially closed
-recovered files do not belong to system or rb tblspc
1 startup mount pfile=/d1/init.ora;
2 alter database datafile ‘/d1/df2.dbf’ offline;
3 alter database open;
4 copy datafile
5 alter database rename file ‘/d1/df2.dbf’ to ‘/d3/df2.dbf’;
6 recover datafile ‘/d2/db2.dbf’;
or recover tablespace USER_DATA;
7 alter database datafile ‘/d2/db2.dbf’ online;
or alter tablespace USER_DATA online
•Recover loss of data file w/ no bak
1 alter tablespace TABLE_DATA offline immediate;
2 alter database create datafile ‘/d2/df2.dbf’ as ‘/d1/df2.dbf’;
3 recover tablespace TABLE_DATA;
4 alter tablespace TABLE_DATA online