How to Check Archive Block Size ?
To monitor the applying performance of a standby database, we usually make a query on V$MANAGED_STANDBY like this:
SQL> select inst_id, process, status, sequence#, block#, blocks from gv$managed_standby where process like 'MRP%';
INST_ID PROCESS STATUS SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ----------
2 MRP0 APPLYING_LOG 29310 1394481 2097152
As we can see, BLOCK# shows that Managed Recovery Process (MRP) has applied 1394481 blocks of this archived log currently. Here are my questions:
- How many bytes of an archive block?
- Is the archive block size the same as the database block size?
- How many bytes it has applied?
- How many remaining bytes it needs to apply?
- Is it applying efficiently?
To answer those questions, we need to know the archived log block size in byte.
Archive Block Size = Redo Block Size
As we have known, archived logs are retired from redo logs, so the size of archived logs are the same as redo's. In the same theory, the block size of archived logs is inherited from redo.
Solution
To know the block size of an online redo log, we may check the dynamic view, V$LOG.
SQL> select distinct blocksize from v$log;
BLOCKSIZE
----------
512
In the result, BLOCKSIZE in V$LOG shows the bytes of a redo block, which defaults to the physical sector size of the disk. Usually, it's 512 bytes in Oracle.
Let's go back to our case. To know the total size (BLOCKS) and applied size (BLOCK#) of the archive, we can do some simple math.
Total Size of the Archive
Applied Size of the Archive
OK, it applied around 66% of this archived log.
Bytes Need to be Applied
There's 0.34 GB need to go.
Larger Redo Block Size
Is a redo block size the larger the better? Not always. The larger redo block size mildly improves performance, while redo wastage increases.
Database Block Size
The redo block size is different from datafile block size, it's usually from 4 KB to 32 KB in DB_BLOCK_SIZE, which also limit the maximum size of a database.
Conclusions
Now, I can answer my questions.
- How many bytes of an archive block?
- Is the archive block size the same as the database block size?
- How many bytes it has applied?
- How many remaining bytes it needs to apply?
- Is it applying efficiently?
It's 512 bytes of a redo block by default.
No, they are not the same thing.
According to the above calculation, it's 0.66 GB.
According to the above calculation, it's 0.34 GB.
In this case, applying has no problem, but the network has some difficulties to transport redo logs.