Lsnrctl services [listener01]
Mean_time_between_failures               //*should be bigger in value*//
Mean_time_to_recover                         //*should be smaller in value*//
Statement failure
Statement failure
logic error
insufficient privileges
Add filespace to the tablespace
Process failure
‐user session abnormally terminated
User errors
‐drop table
‐truncate table
Recover from a backup
Instance failure
Restart the instance
- pool
- name
- bytes
- group_number
- log_sequence_number
- size_of_the_group
- number_of_members
- name
- status
- group
large pool
- backup restore operations
- I/O server processes
- Session memory fro shared users
- Large_pool_size (approximately 2gb)
- dbwr_io_slaves (integer)
- backup_tape_io_slaves (true|false)
SQL> alter system switch logfile;
Alter database db_name rename file ‘...’ to ‘...’;
expected mttr specified in seconds amount of the time that has passed since the incremental checkpoint at the position where the last write to redo log file occured.
 number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log.
v$instance recovery
recovery_estimated_ios: ‐ recovery_estimated_ios: number of dirty buffers in the buffer cache
actual_redo_blks: - current actual number of redo blocks required for recovery
taret_redo_blks: - current number of redo blocks that must be processed for recovery
log_file_size_redo_blks: - current number of redo blocks required to guarantee that
a log switch doesnt occur before checkpoint.
estimated_mttr:  current estimated mean time to recover (mttr). Based on the number of dirty buffers and redo log blocks.
ckpt_block_writes: number of blocks written by checkpoint writes
rolling forward phase
- set recovery_parallelism <integer>
- use parallel clause in the recover database statement
rolling back phase
‐ set fast_start_parallel_rollback [false | low | high] //low is default
user managed backup and recovery
offline backup – consistant whole database setup
SQL> shutdown immediate
!cp <files> /backup/... //control file, datafile, redo log file, password file, parameter file
startup open
online backup – the database should be in archive log mode
SQL> alter tablespace users begin backup
!copy the datafiles
alter tablespace users end backup
alter system archivelog current                  //archive the unarchived redologs so that the redo
required to recover the tablespace backup is achieved query the following views.
v$backup                   //file,status,change,time
SQL> alter database end backup;
Alter database datafile ‘...’ end backup;
Alter tablespace <tablespace name> read only;
!backup the datafiles
Alter tablespace <tablespace name> read write;
Logging and nologging
Set database to nologging for faster data loading After this you should backup the datafiles
SQL> Alter database backup controlfile to ‘control01.bck’;
Alter database backup controlfile to trace;
Obtain database information by quering the following views
user managed recovery
-time based
 cancel based
 change based
recovery steps
1 damaged files are restored from backup
2 changes from archived redo logs or online redo logs are applied if necessary
3 the database may now contain commited and uncommited changes
4 the undo block are used to rollback any uncommited changes
5 the database is now in recoverd state
recovery in noarchivelog mode
1 restore all datafiles even one of them needs recovery
2 shutdown the instance
3 perform cancel based recovery
4 open database with resetlogs
SQL> shutdown immediate
!cp ‘...’ ‘...’
recover database until cancel using backup control file
alter database open resetlogs
recovery in archivelog mode
1 query the v$recover_file, v$archived_log, v$recovery_log
2 recover database                      //mounted database
3 recover datafile ‘...’                 //mounted database
4 recover tablespace users       //open database
5 recover datafile ‘...’                //open database
SQL> shutdown abort
Startup mount
Recover database                         //recover datafile ‘...’
Alter database open
!cp ‘...’ ‘...’
alter database rename file ‘...’ to ‘...’
startup mount
alter database datafile ‘...’ offline
alter database open
restore datafile
alter database rename file ‘...’ to ‘...’
alter database recover
recover datafile ‘...’             //recover tablespace <tablespace name>
alter database datafile ‘...’ online;
alter tablespace user_data offline immediate;
alter database create datafile ‘...’ [as] ‘...’;
alter database recover;
alter tablespace table_data online;
create controlfile;
recover database using backup controlfile;
1 shutdown and backup the database
2 restore all datafiles, dont restore controlfile, redo logs, password file and parameter file
3 mount database
4 recover datafiles
5 open database with resetlogs
6 perform a closed database backup
SQL> Recover database until cancel
Recover [automatic] database until time ‘2013‐10‐26:14:22:03’
Time based database recovery
1 shutdown and backup the database
2 restore all datafiles          //may need to recover archivelogs
3 mount database
4 recover database until time
5 open with resetlogs
6 backup the database
Cancel based database recovery
1 redo logs are not multiplexed
    one of the redo logs is missing
    the missing redo log is not archived
2 shutdown database
3 restore all datafiles from backup
4 mount the database
5 recover database until cancel
6 open database with resetlogs
7 backup database
loss of current redo log files
 attempt to open database
 find the current log sequence number               //select * from v$log
 recover database until cancel
 drop and recreate log files if necessary
 open database with resetlogs
 perform whole database backup
SQL> alter database clear unarchived logfile group 2;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: