Part 3: Oracle 12c Point-in-Time-Recovery

In this Blog post I will share another short video demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c Point-in-Time-Recovery

This video starts the same as a Part 1: Oracle 12c database reset by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before simulating a the loss of datafile.

I then use another python script to determine the last snapshot before shutting the database down and rolling-back to the recovery point. Once the datafiles have been restored RMAN is used to roll-forward the database using the database log files performing a PITR (Point-in-Time-Recovery) with no data-loss.

Advertisements

Part 2: Oracle 12c database rewind

In this Blog post I will share another short video demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c database rewind

This video starts the same as a Part 1: Oracle 12c database reset by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before rewinding the database back to the last crash-consistent snapshot enabling rapid, iterative testing.

Part 1: Oracle 12c database reset

In this series of Blog posts I will share short videos demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c Database Reset

The video starts by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before resetting the database back to the initial clone simulating an application test issue due to problems with the initial set-up and configuration.

Oracle dNFS Throughput testing

Oracle Direct NFS

In my previous Oracle dNFS Blog post I detailed how to configure Oracle 12cR2 to use Oracle dNFS multi-path, in this Blog I will share some test results that I achieved in my Oracle 12cR2 Pure Storage FlashBlade lab.

Oracle dNFS Flashblade configuration

Theoretical Limits

A Gigabit Ethernet network can transmit 1,000,000,000 bits per second, as we normally talk about X Bytes per second we need to divide this by 8 as there are 8 bits in a byte.

1GbE = (1,000,000,000 / 8) = 125,000,000 bytes/second
=125,000KB/s
=125MB/s
=0.125GB/s

Unfortunately we can not use all 125,000,000 bytes/second to send data as we have multiple layers of overhead,  when using a default MTU of 1500 we can expect to experience ~6% overhead, this gives us:

1GbE= (1,000,000,000 / 8) less 6% overhead = 117,500,000 bytes/second
=117,500KB/s
=117.5MB/s
=0.118GB/s

1 x  1GbE =   0.118GB/s
1 x 10GbE = 1.175GB/s
4 x 10GbE = 4.7GB/s
8 x 10GbE = 9.4GB/s  (2 Node RAC Cluster)

The dNFS Throughput Test

In the tests below I used Kevin Closson’s excellent SLOB with a custom configuration to produce Oracle FTS (Full Table Scans), many thanks to Frits Hoogland for sharing. This set-up allowed me to generate sufficient load to utilise the available bandwidth and see how close I can get to the ‘theoretical limits’.

Test 1 – Oracle 12cR2 dNFS disabled

OK, during this test we can see that Oracle EM Express is showing a single instance driving ~1.08GB/s IO Throughput

Oracle Express 12c
The Flashblade reporting throughput at ~1.09GB/s

FB_PSTG1

Test 2 – Oracle 12cR2 dNFS enabled

For out next test, I have enabled dNFS with make -f ins_rdbms.mk dnfs_on but have not activated multipath, this was confirmed with the SQL below.

SELECT svrname, dirname, wtmax, rtmax FROM v$dnfs_servers;

SVRNAME        DIRNAME             WTMAX  RTMAX
-------        ------------------  ------ ------
192.168.4.100 /z-fbhosts_oradata   524288 524288
192.168.4.100 /z-fbhosts_orafra    524288 524288
192.168.4.100 /z-fbhosts_oraconfig 524288 524288

Oracle Express EM reports almost identical results with and without dNFS
FB_PSTG1_dnfs
And the Flashblade dashboard reports a similar result.

FB_dnfs
So lets enable multi-path and see the impact.

Test 3 – Oracle 12cR2 dNFS multi-path

So, lets install our oranfstab file and bounce the database and check again using the previous SQL script.

SELECT svrname, dirname, wtmax, rtmax FROM v$dnfs_servers; 
SVRNAME        DIRNAME          WTMAX  RTMAX 
-------        ---------------- ------ ------ 
flashblade /z-fbhosts_oradata   524288 524288 
flashblade /z-fbhosts_orafra    524288 524288 
flashblade /z-fbhosts_oraconfig 524288 524288

We can see the Server Name has been picked up from the oranfstab rather than the mtab, we can also check that we are using multipath with the v$dnfs_channels view.

SELECT distinct svrname, path FROM v$dnfs_channels;
SVRNAME              PATH                                            
-------------------- -------------
flashblade           192.168.7.100
flashblade           192.168.4.100
flashblade           192.168.6.100
flashblade           192.168.5.100

Oracle EM Express is now showing we are using all 4 x 10GbE interfaces as expected and driving 4.03GB/s, also note the increase in Host CPU utilisation.
FB_PSTG1_MP

FB_MP
Ok, our final test will be to see if we get similar results with Oracle RAC

Test 4 – Oracle 12cR2 2 node RAC with dNFS multi-path

For this Oracle RAC test the slob.conf SQLNET_SERVICE_MAX parameter has been changed to 2 and to distribute the workload across the 2 nodes.

FB_PSTG_RAC
Oracle EM Express is showing that the workload has been evenly balanced across the 2 RAC nodes, achieving impressive throughput figures exceeding 8.3GB/s.

FB_RACThe Flashblade dashboard show that the 7 blades were able to deliver a consitent bandwidth of over 8.8GB/s for the Oracle workload utilising all 8 x 10GbE interfaces getting close to wire speed.

In my next Blog Post I plan to enable Jumbo Frames (JF) end-to-end and repeat the above tests.  With JF (MTU 9000) configured, I am hoping to see a ~5% improvement in bandwidth utilisation.