Determination of physical writes, media recovery, instance recovery, and incremental checkpoints

Physical write detection:

select  * from v$sysstat where lower(name) like ‘physical writes%‘;

Physical writes89119 // how many pieces have I written in total

 

select * from v$sysstat where upper(name) like ‘DBW%‘;

104 dbwrCheckpoint buffers written817312 // Number of chunks written through the checkpoint.

Then you can use the buffer writer/physical writers, which is basically 6%, and the 70 s is normal.

Test:

[email protected]_connect_identifier>[email protected]_connect_identifier>select * from v$sysstat where upper(name) like ‘DBWR%‘;STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- ---------- ----------       104 DBWR checkpoint buffers written 8 259 1208600358       105 DBWR thread checkpoint buffers written 8 0 3905787588       106 DBWR tablespace checkpoint buffers written 8 0 2649259263       107 DBWR parallel query checkpoint buffers written 8 0 1768645316       108 DBWR object drop buffers written 8 0 658143835       109 DBWR transaction table writes 8 19 2146120386       110 DBWR undo block writes 8 73 111270822       111 DBWR revisited being-written buffer 8 0 2773697723       112 DBWR lru scans 8 0 2139101792

113 dbwr checkpoints 8 0 1732023165 114 dbwr fusion writes 40 0 2313150541 selected 11 rows. [Email protected] _ connect_identifier> select * from V $ sysstat where lower (name) Like 'physical writ % '; statistic # name class value stat_id ---------- bytes ---------- 48 physical write total IO requests 8 1301 1315894329 49 physical write total multi block requests 8 5 3540174003 50 physical write Total Bytes 8 16102400 2 495644835 83 physical writes 8 272 1190468109 84 physical writes direct 8 13 2699895516 85 physical writes from cache 8 259 163083034 86 physical write IO requests 8 187 2904164198 89 physical writes direct temporary tablespace 8 9 996415569 90 physical write Bytes 8 2228224 3131337131 physical writes non checkpoint 8 102 246 2602029796 physical writes direct (LOB) 8 4 3308932835 select 11 rows.

[email protected]_connect_identifier>select 259/272 from dual;   259/272----------.952205882

When Will Oracle recover the instance?

In fact, Oracle has a flag. When it is set to 1, the instance will be restored. When it is set to 0, it will not be restored:

In V $ datafile, there is a parameter last_time and last_change #.

 

You can first mount the database, and then query

Select last_time, last_change # from V $ datafile;

You can see. If a result is returned, it is disabled normally. If no result is returned, it is disabled abnormally.

Determine whether the file needs media recovery:

V $ datafile; From Control File

V $ datafile_header comes from the data file header.

col name for a40select name,CHECKPOINT_CHANGE#, CHECKPOINT_TIME FROM V$DATAFILE;SELECT CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER;

If the file checkpoint is different, you need to restore the media.

Test:

First hot backup a file:

rman target /backup datafile ‘/u01/app/oracle/oradata/test/test_01‘  format  ‘/tmp/test_01%U.bak‘;

Change Time Format:

alter  session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;

In Oracle, there is a checkpoint_change # of V $ database and V $ datafile_header. If the former is smaller than the latter, the control file is too old and needs to be restored.

alter database mount recover database open noresetlog

 How can we avoid resetlog recovery)

You can use the reconstruction control file:

SQL> alter Database Backup controlfile to trace;

Find the statement in the trace file, and use the re-control file statement after the database is shut down and nomount. Then recover the database; finally alter database open;

Incremental checkpoint:

1) ckptq (checkpoint Queue) when you perform any modification operations, Oracle will first obtain the Chpt Latch Lock

2) dbwr does not check the chptq length for 3 seconds. If it is too long, it will be written to the disk.

3) ckpt writes the RBA (redo block address) in the first block to the control file within 3 seconds.

This article from the "technical life" blog, please be sure to keep this source http://jesnridy.blog.51cto.com/5554751/1529842