I’ll be presenting my brand new presentation “Using image copies for Oracle database backups” at ilOUG Tech Days on 30. May in Israel.
More information about the event can be found here
Abstract of my presentation:
When databases get ever larger and larger, backing them up using traditional RMAN backupsets will quickly get unfeasible. Completing a backup requires too much time and resources, but more importantly the same also applies to restores. RMAN has always provided a solution as incrementally updated image copies, but they are much less manageable than backupsets. This presentation goes into detail on how to successfully implement incrementally updated image copy backups, automate them and implement features that together with a capable storage system can provide almost everything that Oracle ZDLRA promises and beyond.
Looking forward to the event!
- Written by: ilmarkerm
- Category: Blog entry
- Published: March 7, 2016
Since 11.1 RMAN has had a silent new feature – RMAN Backup Undo Optimization. This feature will exclude undo from committed transactions (after undo_retention time has also passed) from backups, possibly making the undo tablespace backup much smaller. The documentation just says that it will work for disk backups and Oracle Secure Backup tape backups. Since lately I’m been playing around a lot with image copy backups I wanted to find out if this feature only works with backupsets or does it also work for incrementally refreshed image copies.
I first thought that it cannot possibly work with image copies, since image copies should be exact datafile copies, but on the other hand when you refresh and image copy, then you at first also have to create incremental backupset of the changes that you then apply to the image copy, so maybe the optimization is applied silently there also 🙂 Would be really good. Better to test it out. Fingers crossed.
I’m using 12.1.0.2 on OEL 7.2.
Before taking the test I created an image copy from my undo tablespace (309 338 112 bytes):
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'image_copy_backup' TABLESPACE UNDOTBS1;
-rw-r-----+ 1 oracle oinstall 309338112 Dec 28 05:06 data_D-ORCL_I-1433672784_TS-UNDOTBS1_FNO-3_04qvtmir
Yes I know, my filesystem dates were wrong at that point 🙂 Ignore this, NTP wasn’t running on the storage box.
Also a level 0 uncompressed backupset of the same tablespace (207 110 144 bytes, so it has already been optimized, but I’m interested in the next incremental backup size):
RMAN> BACKUP INCREMENTAL LEVEL 0 TABLESPACE UNDOTBS1;
-rw-r-----+ 1 oracle oinstall 207110144 Dec 28 05:16 0kqvtpaj_1_1
Next I ran a large UPDATE statement and committed it immediately. I also had snapper running to catch the amount of undo my update caused. Snapper reported that my update generated 146MB of undo:
STAT, undo change vector size , 146 042 740
Now immediately I run incremental backup for both, backupset and to incrementally update the image copy.
BACKUP INCREMENTAL LEVEL 1 TABLESPACE UNDOTBS1 command produced file named 0mqvtpkf_1_1 and command BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘image_copy_backup’ tablespace undotbs1 produced file named 0oqvtpm2_1_1. As you can see, both are almost equally as big and close to the reported undo change vector size.
No surprise herem undo optimization did not kick in since undo_retention time has not yet passed.
-rw-r-----+ 1 oracle oinstall 151470080 Dec 28 05:21 0mqvtpkf_1_1
-rw-r-----+ 1 oracle oinstall 181190656 Dec 28 05:22 0oqvtpm2_1_1
Then I deleted both these files and removed them from RMAN catalog.
After 30 minutes or so (my undo_retention time is 600 = 10 minutes) I ran the backup commands again:
RMAN> backup incremental level 1 tablespace undotbs1;
Starting backup at 07-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_cfvpb5hx_.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAR-16
channel ORA_DISK_1: finished piece 1 at 07-MAR-16
piece handle=/nfs/backup/orcl/14qvtsgf_1_1 tag=TAG20160307T230238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAR-16
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'image_copy_backup' tablespace undotbs1;
Starting backup at 07-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_cfvpb5hx_.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAR-16
channel ORA_DISK_1: finished piece 1 at 07-MAR-16
piece handle=/nfs/backup/orcl/16qvtsj0_1_1 tag=IMAGE_COPY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 07-MAR-16
This can’t be good.. regular backupset took only 1 second to execute and taking an incremental backup for image copy refresh took 7 seconds.
Looking at the file sizes the difference is clear – 1,7MB for the incremental backup and 181MB (no change) for the image copy refresh:
-rw-r-----+ 1 oracle oinstall 1794048 Mar 7 23:02 14qvtsgf_1_1
-rw-r-----+ 1 oracle oinstall 181567488 Mar 7 23:04 16qvtsj0_1_1
So the backup undo optimization works, but only if you use backupsets.
- Written by: ilmarkerm
- Category: Blog entry
- Published: January 15, 2016
I published my 2014 presentation “Making MySQL highly available using Oracle Grid Infrastructure” in Slideshare.
Please also read my page how to set up the mysql scripts for Oracle GI
- Written by: ilmarkerm
- Category: Blog entry
- Published: January 14, 2016
I’m currently involved in a project where we are replacing one company’s entire hardware platform. They also have Oracle database 10.2.0.5 (that we cannot upgrade right now) and what is really unusual for me, is that this Oracle database runs under Windows (and we cannot migrate to another platform). We also decided to use Oracle Grid Infrastructure (aka Oracle Clusterware) 11.2.0.4 to implement active-passive standby server for this database. Why? Because Windows Cluster was not an option, we didn’t have RAC licenses, Oracle Clusterware is free (if you are protecting Oracle software or running on Oracle OS) and we have really good previous experience with it under Linux.
For more information on how I’ve used Oracle Grid Infrastructure to provide high availability for MySQL (or any other application), check out my page MySQL HA with Oracle Clusterware.
When I started testing this it was quite surprising that I didn’t find any Oracle Clusterware action script examples for Windows in Oracle documentation or even on google 🙂 Oracle documentation just refers that in Windows the action script has to be a batch script.
This is the example action script I came up with to manage stand alone Oracle 10.2.0.5 database. It uses oradim to start and stop the database instance dbgp and a small sqlplus script to check if the database instance is alive. Tested using Oracle Grid Infrastructure 11.2.0.4 under Windows 2008R2.
I named the action script: d:\scripts\dbgp.cmd
Just for completeness, this script refers to d:\scripts\check.sql that is just used to run a quick database healt check, here is its contents:
whenever sqlerror exit failure
conn / as sysdba
select 1 from dual;
exit
WINDOWS SPECIFIC ONE TIME OPERATION: Before clusterware can execute the action script in Windows, you need to create OracleCRSToken_username service for the OS user who is executing the script. In my setup both Oracle Clusterware, managed database and the action script are executed by the local user WINRAC1\oracle and on the second node as WINRAC2\oracle. It is actually easier if you use domain user, please check the referred note.
Reference: Windows: How to Modify OS User Privileges for 11gR2 Grid Infrastructure and RAC Services (Needed for Backup To Network Shares) (Doc ID 1339053.1) steps 2 and 3.
set ORACLE_HOME=d:\app\11.2.0.4\grid
%ORACLE_HOME%\bin\crsuser add winrac1\oracle
.. it will show errors ..
.. but repeat the command on other node also ..
set ORACLE_HOME=d:\app\11.2.0.4\grid
%ORACLE_HOME%\bin\crsuser add winrac2\oracle
After that need to open services.msc and edit service OracleCRSToken_oracle. First set its startup type to Automatic and then on Log On As tab also set the oracle user password. After that start service OracleCRSToken_oracle and repeat these steps on all cluster nodes.
ADDING THE RESOURCE TO CLUSTERWARE: Adding the resource to cluster is the same as under Linux:
set ORACLE_HOME=d:\app\11.2.0.4\grid
%ORACLE_HOME%\bin\crsctl add resource oradb_dbgp -type cluster_resource -attr "ACTION_SCRIPT=d:\scripts\dbgp.cmd, CHECK_INTERVAL=60, RESTART_ATTEMPTS=2, PLACEMENT=favored, HOSTING_MEMBERS=winrac1"
- Written by: ilmarkerm
- Category: Blog entry
- Published: December 5, 2015
Oracle has released it ZFS Storage Appliance software simulator. It is sold as an Tier 2 storage hardware, but using the simulator you can get all its powerful storage management features on your Virtualbox. The simulator is free and it has no time limits nor restrictions (although one restriction – the simulator is not clustered).
Some use cases for the simulator:
* You can try out the new ZFS Storage Applicance software patches before applying them on the real physical storage box
* You want to test some Oracle database features that are only activated when using Oracle storage (HCC)
* You want to provide shared network storage fro your VM-s, with advanced storage capabilities, like snapshots, cloning, compression, deduplication, remote replication, encryption etc
* You want to evaluate ZFS Storage Appliance features before purchasing the real box or before using a specific feature in production
Setting up the simulator under virtualbox is very simple and quick. On the configuration below I make no effort to secure the system, since it is intended to be used only in my Virtualbox environment.
Requirements
Virtualbox 4.2.12 or later.
I’m using Virtualbox 5.
Download the software
Go to the ZFS Storage software page and click Try the simulator. First you have to register the download and then you can download ZIP file containing the software.
Unzip the file you downloaded, then you’ll get OracleZFSStorageVM directory with 18 files under it.
Import VM to Virtualbox
Open Virtualbox Manager, go to File > Import Appliance.
Browse to Oracle_ZFS_Storage.ovf from the unzipped software directory.
Click Continue.
On the next screen you can view the imported VM settings and then press Import.
Initial configuration
After importing you will have a new VM called Oracle_ZFS_Storage.
Open Settings for this VM and go to Network tab. Verify that it is connected to the correct virtual network. Mine is automatically imported under the only Host-only Adapter network I have and I’m going to keep that setting.
Now launch the VM.
It will take a 10+ seconds to boot up and then on the first boot it will ask you a few questions on basic network settings and root user password.
Supply the requested values:
Press Enter when done. After that it will show you a blank screen for 30+ seconds and after it is done the system is ready.
Now open the URL requested https://192.168.56.98:215/ in your browser to finish the initial setup.
Log in as root supplying the password you set previously.
Welcome screen appears. Click Start to go through a small initial setup wizard. Everything can also be changed later.
First screen, networking, just press Commit.
Second screen, DNS, just press Commit.
Next, NTP, just press Commit.
Next, Name Services… here you need to set up LD… just kidding, press Commit.
Now, Configure storage, here you can set up the storage pool.
Press the plus sign before Available pools and supply a pool name – pool1 in my example.
On Verify and allocate devices screen you can just press Commit
The next, Choose storage profile is the most interesting. In production it requires a lot of consideration, because every choice has very different availability, read performance and write performance implications. Here, Iäll just choose Striped to get the maximum performance and no loss in available storage size. Obviously this would be a very bad choice for production system due to no availability or fault tolerance at all.
Press Commit. You are now back in Configure storage screen, here again press Commit.
On the final Registration & Support page there is no Commit button, but there is a button called Later, press it and then confirm it by pressing OK.
All done 🙂
Set up iSCSI
If you want to share iSCSI block devices, then first need to create iSCSI Target.
Go to Configuration > SAN and click on ISCSI.
Click the plus sign before Targets.
If you don’t care how the IQN looks, just provide some name for Alias and press OK.
It will then automatically generate IQN for you, so in my example it is named: iqn.1986-03.com.sun:02:455fe302-6504-6eaf-d478-9b3acf9f4afe
Set up SNMP
By default SNMP service is not enabled, but if you want to test Hybrid Columnar Compression it is required. You can get more details about it from this blog post.
Go to Configuration > Services, and click on the aroow button to expand the Services menu.
Click on SNMP from the left-side menu.
First click on the power button symbol under SNMP to enable the service and set the following values:
* Authorized network/mask: 0.0.0.0 / 0
* Appliance contact: your email address
* Trap destination: 127.0.0.1
Click Apply.
REST API
All storage configuration can also be done using REST API. You can create new LUNs, filesystems, change thir properties, snapshot, clone, drop… everything that you can do in GUI you can also do over REST API.
I use it a lot in production in database backup&restore scripts and also in providing production database clones for testing.
I have also blogged about it before: SAMPLE CODE: USING THE ORACLE ZFS STORAGE APPLIANCE REST API FROM PYTHON
I really hope I can soon publish my full Oracle database backup&restore script suite that also relies on ZFSSA sotrage features heavily.
ZFSSA REST API documentation is here
REST API is turned on by default and it is accessible over the same URL as the management GUI: https://192.168.56.98:215/
Create NFS filesystem
To create a new NFS filesystem go to Shares
Click the plus sign next to Filesystems.
Write filesystem name and you can also set the share UID, GID and permissions. For example if you are using it for oracle database and have installed the oracle-rdbms-preinstall rpm package under linux, then you could set User and Group values to 54321 to get the correct permissions on mount.
Click Apply.
The newly created filesystem appears on the list.
You can now mount the NFS filesystem on a target machine, in my example using the settings:
mount -t nfs 192.168.56.98:/export/oradata /mnt
If you hover over the filesystem entry, you notice a pencil icon on the left. If you click it you can change filesystem properties, restrictions and snapshots.
If the filesystem is going to be used for Oracle database data files (not for RMAN, UNDO, REDO, TEMP) , then one thing that you may want to change is Database record size to be equal to the tablespace block size 8K.
Click Apply.
Create iSCSI LUN
Go to Shares and click on LUNs. This way you can present block devices to servers, to be used for example as ASM disks.
Click plus sign next to LUNs.
Fill out the properties, again, if the LUN is to be used for Oracle tablespace data files, then you may want to set the Volume block size to tablespace block size (8K).
Click Apply.
The newly create LUN appears on the list.
Again, the pencil icon opens the LUN detailed properties page.
NB! LUNs can also be compressed and deduplicated!
Let’s try accessing this LUN from Linux. First connect to ZFS:
[root@localhost ~]# iscsiadm -m discovery -t sendtargets -p 192.168.56.98
192.168.56.98:3260,2 iqn.1986-03.com.sun:02:455fe302-6504-6eaf-d478-9b3acf9f4afe
[root@localhost ~]# iscsiadm -m node --login
Logging in to [iface: default, target: iqn.1986-03.com.sun:02:455fe302-6504-6eaf-d478-9b3acf9f4afe, portal: 192.168.56.98,3260] (multiple)
Login to [iface: default, target: iqn.1986-03.com.sun:02:455fe302-6504-6eaf-d478-9b3acf9f4afe, portal: 192.168.56.98,3260] successful.
Now I should have the new LUN mapped to linux:
[root@localhost proc]# cat /proc/scsi/scsi
Attached devices:
Host: scsi1 Channel: 00 Id: 00 Lun: 00
Vendor: VBOX Model: CD-ROM Rev: 1.0
Type: CD-ROM ANSI SCSI revision: 05
Host: scsi2 Channel: 00 Id: 00 Lun: 00
Vendor: ATA Model: VBOX HARDDISK Rev: 1.0
Type: Direct-Access ANSI SCSI revision: 05
Host: scsi3 Channel: 00 Id: 00 Lun: 00
Vendor: SUN Model: Sun Storage 7000 Rev: 1.0
Type: Direct-Access ANSI SCSI revision: 05
[root@localhost proc]# cat /proc/partitions
major minor #blocks name
11 0 57620 sr0
8 0 12582912 sda
8 1 204800 sda1
8 2 12377088 sda2
252 0 11325440 dm-0
252 1 1048576 dm-1
8 16 10485760 sdb
My new device is /dev/sdb. Lets confirm its SCSI ID.
[root@localhost proc]# /usr/lib/udev/scsi_id -g -u /dev/sdb
3600144f09ff1616800005662f2f40001
Matches perfectly with the ID in ZFS management interface GUID column (ignore the first digit).