B&R I
\n\u2022Oracle structure
\nv$sga
\nv$sgastat
\nv$database
\nv$instance
\nv$process
\nv$bgprocess
\nv$datafile<\/p>\n
\u2022Large pool
\n-set by LARGE_POOL_SIZE
\n-min 300k max 2gb
\n-DBWR_IO_SLAVES (if > 0, ARCn,LGWR,RM are set to 4)
\n-BACKUP_TAPE_IO_SLAVES (if true, an i\/o slave process is used to rw tape device)<\/p>\n
\u2022Data Buffer Cache
\n-stores mru data blocks<\/p>\n
\u2022DBWn
\n-writes dirty buffers from db buffer cache to data files
\n-triggered by ckpt event
\n-DBW1 -> DBW9
\n-not useful on uniprocessor systems<\/p>\n
\u2022Data files
\n-commited: normal
\n-uncommited: shutdown abort, db fail<\/p>\n
\u2022Tablespace
\n-temp: can be recreated
\n-rbs: should have own tablespace
\n-readonly tblspc
\n-index data: can be recreated<\/p>\n
\u2022Redo Log Buffer
\n-circular buffer about changes made<\/p>\n
\u2022LGWR
\n-writes redo entries to redo log files:
\n\tredo log buff is 1\/3 full
\n\ttime out occurs (every 3 sec)
\n\tbefore DBWn writes
\n\twhen transaction commits<\/p>\n
\u2022Redo Log Files
\n-need at least two log groups
\n-each group should >2 members
\n-multiplexing Redo Log Files.
\n-To move a redo log file
\n\t1 alter system switch log file;
\n\t2 copy file to new location
\n\t3 alter database db
\n\t\trename file ‘file'[, ‘file’] …
\n\t\tto ‘file'[, ‘file’]…
\n-To add a member to a group
\n\talter database db
\n\tadd logfile member
\n\t…
\n-To drop a member from a group
\n\talter database db
\n\tdrop logfile member
\n\t‘file'[, ‘file’]…<\/p>\n
\u2022Redo Log switch
\n-current redo log group is assigned a log seq no which is also used for sync
\n-when
\n\tLGWR filled one log file group
\n\tdba issues ALTER SYSTEM SWITCH LOGFILS
\n-checkpoint automatically occurs<\/p>\n
\u2022Dynamic views
\n-v$log
\n-v$logfile
\n-v$log_history<\/p>\n
\u2022CKPT
\n-at ckpt
\n\t1 modified db buffers written to db file -> db header files marked curr
\n\t2 ckpt seq no written to cont file
\n-occurs
\n1 every log switch
\n2 fast-start checkpointing
\n3 > log_checkpoint_interval need to be read during instance recovery
\n4 instance shutdown unless aborted
\n5 alter system checkpoint
\n6 tblspc taken offline or an online backup is started
\n-sync
\n\tat ckpt, ckpt no is updated in evere db file header and in cont file
\n\tif redo, control, and data files have same ckpt no -> consistent db
\n-expedite instance recovery<\/p>\n
\u2022Multiplexing control files
\n1 shutdown db
\n2 copy cont file to different loc
\n3 edit CONTROL_FILES
\n4 start db<\/p>\n
\u2022ARCn and Archive Log Files<\/p>\n
B&R II
\n\u2022Statement failure
\n-no dba intervention required
\n\u2022User Process Failure
\n-no dba intervention required
\n-PMON automatically handles it
\n\u2022User error failure
\n\u2022Instance failure
\n-no dba intervention required
\n-SMON handles it automatically<\/p>\n
\u2022Fast-start parallel rollback
\n-SMON uses parallel query slaves when a transaction is larger than 100 rollback blocks
\n-FAST_START_PARALLEL_ROLLBACK
\n\tFALSE\tnone
\n\tLOW\t2 * cpu_count
\n\tHIGH\t4 * cpu_count
\n-v$fast_start_servers
\n-v$fast_start_transactions<\/p>\n
\u2022Archivelog mode
\n-media recovery can be restored to
\n\tspecific point in time
\n\tend of a specific arch log file
\n\tspecific scn
\n-log_archive_dest_n where n = 1 – 5
\n~_1 = “location=\/arch1 mandatory reopen” (default to reopen=300)
\n~_2 = “service=db1”
\n-log_archive_min_succeed_dest
\n-log_archive_dest_state_n=[defer|enable]
\n\u2022Enable archivelog mode
\n1 shutdown immediate
\n2 startup mount
\n3 alter database [archivelog| noarchivelog]
\n4 alter database open
\n5 shutdown immediate
\n6 full db backup<\/p>\n
\u2022Multiple arch process
\n-log_archive_max_processes
\n-log_archive_start<\/p>\n
\u2022Enabling archive process in an open instance
\n1 archive log list
\n2 alter system archive log start to ‘\/u04\/ora\/test\/log’;<\/p>\n
\u2022Start or stop additional arch proc
\n-alter system set loq_archive_max_processes=3;<\/p>\n
-alter system archive log stop;
\n-log_archive_start=false
\n-set db to noachivelog<\/p>\n
\u2022Dynamic views
\nv$archived_log
\nv$archived_dest
\nv$log_history
\nv$database
\nv$archive_processes
\narchive log list;<\/p>\n
\u2022Closed Database Backups
\nFiles to backup
\n-data files V$DATAFILE dba_datafiles
\n-control files V$CONTROLFILE
\n-parameter files V$LOGFILE
\n-password files
\n-redo log files (optional)<\/p>\n
Procedure
\n1 shutdown db
\n2 backup files
\n3 open db<\/p>\n
\u2022Open db backup
\n1 alter tablespace user begin backup;
\n2 cp user01.dbf \/backup!user01.dbf
\n3 alter tablespace user end backup;
\n4 alter system switch logfiles;
\n5 repeat for all tblspc including system, temp, rbs…<\/p>\n
-v$backup
\n-v$datafile_header<\/p>\n
\u2022Backup control file
\n-alter database backup control file to ‘control.bak’;
\n-alter database backup control file to
\ntrace;
\n-needed after every change to db config.<\/p>\n
\u2022Read-only Tablespace backup
\n-alter tablespace query_data read only<\/p>\n
\u2022Logging and nologging options
\n-enable nologging on tblspc, tbl, ind, or partition for direct load operations
\n-enable logging afterward<\/p>\n
B&R III<\/p>\n
Complete recoveryin achive mode<\/p>\n
\u2022Restore file to diff location
\n1 startup mount pfile=init.ora;
\n2 alter database rename file
\n‘\/d1\/user.dbf’ to ‘\/d2\/user.dbf;
\n3 alter database open;<\/p>\n
\u2022Files needed for recovery
\n-v$recover_file
\n-v$log_history
\n-v$recovery_log<\/p>\n
\u2022Apply redo log files automatically
\nsql>set autorecovery on;
\nor
\nsql>recover automatic datafile 4<\/p>\n
\u2022Recovering a closed database
\n1 restore file from backup
\n2 startup mount pfile=’\/d2\/df2.dbf’
\n3 alter database open;<\/p>\n
\u2022recover open db initially opened
\n-recovered files do not belong to system or rb tblspc
\n1 restore datafile
\n2 recover datafile ‘\/d2\/db2.dbf’;
\nor recover tablespace USER_DATA;
\n3 alter database datafile ‘\/d2\/db2.dbf’ online;
\nor alter tablespace USER_DATA online<\/p>\n
\u2022recover open db initially closed
\n-recovered files do not belong to system or rb tblspc
\n1 startup mount pfile=\/d1\/init.ora;
\n2 alter database datafile ‘\/d1\/df2.dbf’ offline;
\n3 alter database open;
\n4 copy datafile
\n5 alter database rename file ‘\/d1\/df2.dbf’ to ‘\/d3\/df2.dbf’;
\n6 recover datafile ‘\/d2\/db2.dbf’;
\nor recover tablespace USER_DATA;
\n7 alter database datafile ‘\/d2\/db2.dbf’ online;
\nor alter tablespace USER_DATA online<\/p>\n
\u2022Recover loss of data file w\/ no bak
\n1 alter tablespace TABLE_DATA offline immediate;
\n2 alter database create datafile ‘\/d2\/df2.dbf’ as ‘\/d1\/df2.dbf’;
\n3 recover tablespace TABLE_DATA;
\n4 alter tablespace TABLE_DATA online<\/p>\n","protected":false},"excerpt":{"rendered":"
B&R I \u2022Oracle structure v$sga v$sgastat v$database v$instance v$process v$bgprocess v$datafile \u2022Large 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 … Continue reading