Shulou(Shulou.com)06/01 Report--

Detection of physical writes:

Select * from v$sysstat where lower (name) like 'physical writes%'

Physical writes 8 9 119 / / how many yuan have I written?

Select * from v$sysstat where upper (name) like 'DBW%'

104 DBWR checkpoint buffers written 8 173 12 / / how many blocks were written through the checkpoint.

Then you can use buffer writer / physical writers at 6%, 70 is normal.

Test:

SYS@_connect_identifier > SYS@_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 400 2313150541 11 rows have been selected. SYS@_connect_identifier > select * from v$sysstat where lower (name) like 'physical writ%' STATISTIC# NAME CLASS VALUE STAT_ID 48 physical write total IO requests 8 1301 1315894329 49 physical write total multi block requests 85 3540174003 50 physical write total bytes 8 16102400 2495644835 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 89 996415569 90 physical write bytes 8 2228224 31337102 physical writes non checkpoint 8 246 2602029796 156 physical writes direct (lob) 84308932835 11 rows have been selected.

SYS@_connect_identifier > select 259 from dual; 272 from dual; 259 Compact 272 Mel. 952205882

When will Oracle do instance recovery?

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

There are mainly parameters last_time and last_change#. in v$DATAFILE

You can first set the database mount status, and then query

Select last_time, last_change# from v$DATAFILE

You can observe it. The result is a normal shutdown, if there is no result, it is an abnormal shutdown.

Determine if the file requires media recovery:

From the control file

The v$datafile_header comes from the header of the data file.

Col name for a40select name,CHECKPOINT_CHANGE#, CHECKPOINT_TIME FROM Vacancy DATAFILEX select CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER

If that file checkpoint is different, media recovery is required.

Test:

Let's hot prepare a file:

Rman target / backup datafile'/ u01According to an oracle format / tmp/test_01%U.bak'

Change the time format:

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

There is also a comparison between v$database checkpoint_change# and v$datafile_header in oracle. If the former is smaller than the latter, then the control file is too old and needs to be restored.

Alter database mount recover database open noresetlog

If you restore, how to avoid resetlog (log file number is returned to zero)

You can use the rebuild control file:

Sql > alter database backup controlfile to trace

Then find the statement in the trace file, and use the rebuild control file statement after nomount after the shutdown database. After that, recover database; finally alter database open

Incremental checkpoints:

1) ckptq (checkpoint queue) when you make any modification, Oracle will first acquire the chpt latch lock

2) dbwr does not check the length of chptq in 3 seconds. If it is too long, write it to disk.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.