Database virtualization and thin provisioning are new and powerful ways to share/reuse storage among non-production database copies.
Solutions for reducing the disk footprint of non-production Oracle databases range from complete database virtualization (Delphix, etc), to solutions build around storage vendor’s features (EMC,NetApps, etc), to purely software solutions (cloneDB, etc).
Sharing read-only tablespaces – a “poor man’s” database virtualization – is less known approach for saving disk space and improving refresh times in non-production environments. Here is a link to the presentation I did at NYOUG (2012) about this topic.
What are we measuring and why?
In general, most solutions are quite effective, both in terms of storage and refresh time, in delivering a copy of the production database.
Once the copy is delivered, it is open for users and batch jobs, so it starts changing. Those changes belong to the new database and therefore cannot be shared with the other non-production databases. That means that they consume “real” disk space. The more DB changes since refresh, the lower the benefits of the DB virtualization/thin provisioning solution.
Measuring the amount of change after a refresh is important in understanding how much disk space would be needed for the non-production Oracle environments after DB virtualization/thin provisioning is in place. The number is essential in computing ROI of the project.
How are we measuring it?
One way to measure the change is to run an incremental RMAN backup and see how big the resulting backup file(s) is. In many cases, however, that is not doable.
The method described here works only on 11gR2. It only requires the SCN of the DB at the time of refresh.
The information can be find in V$DATABASE (RESETLOG_CHANGE#) for databases created by RMAN DUPLICATE.
If the data was transferred with DataPump, or other similar method, a suitable SCN can be found in V$ARCHIVED_LOG (FIRST_CHANGE#, NEXT_CHANGE#).
The new DBVerify feature allows us to utilize HIGH_SCN parameter to find out how many blocks where changes since HIGH_SCN.
Let’s see how many blocks were modified after SCN 69732272706.
It is really easy:
dbv userid=sys/?????????? file=/tmp/test.dbf HIGH_SCN=69732272706
Page 64635 SCN 1012797077 (16.1012797077) exceeds highest scn to check 1012795970 (16.1012795970)
Page 66065 SCN 1012796687 (16.1012796687) exceeds highest scn to check 1012795970 (16.1012795970)
Page 66187 SCN 1012796687 (16.1012796687) exceeds highest scn to check 1012795970 (16.1012795970)
Page 87759 SCN 1012796692 (16.1012796692) exceeds highest scn to check 1012795970 (16.1012795970)
DBVERIFY - Verification complete
Total Pages Examined : 93440
Total Pages Processed (Data) : 62512
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9399
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6119
Total Pages Processed (Seg) : 1705
Total Pages Failing (Seg) : 0
Total Pages Empty : 13705
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Total Pages Exceeding Highest Block SCN Specified: 39
Highest block SCN : 1012797131 (16.1012797131)
In this case, only 39 blocks (out of 100K) were modified after SCN 69732272706.
Please note that even though DBVerify (dbv) can work with open DB files, it will not account for changed blocks that were not yet written to the file.
Also, as per MOS document 985284.1 – ” Why HIGH_SCN Option Doesn’t Work While Running DBV Against ASM Files?” – the HIGH_SCN flag works only for files that are not stored in ASM. Hope they fix that problem soon.