I’m off the beaten path today courtesy of a friend and colleague in Chicago. We both used to work at Oracle so when he gives me a call it’s typically not about my VMAX/VMware fare. In this case he had a customer who wanted to use SnapVX to instantiate a physical standby database, i.e. Data Guard. More to the point – do we need to use hot backup mode? Well turns out it all depends on your Oracle version.
Starting with 12c, Oracle no longer requires that we place the database in hot backup mode before taking a snapshot. This holds true for either a crash-consistent or a recoverable copy of the database. There’s a good whitepaper from our group on VMAX with Oracle that talks about these two use cases. The one use case it does not address, however, is whether you can take that snapshot and use it to instantiate a physical standby database. Well, dispensing with the suspense, you can. I’ll run through my particular test which I configured on, you guessed it, vSphere with a VMAX All Flash.
When I set out to run the test I wanted to be sure it followed a typical Oracle procedure. Therefore the steps I include, save for the VMAX specific ones, are directly from Oracle’s documentation. There’s nothing fancy about the process – the only thing I will change is instead of using RMAN to instantiate, I use SnapVX.
Let’s start with my environment. I have a vSphere cluster with 2 hosts and each host is running an Oracle VM (OEL). Looks like this:
I put the OS on a single vmdk on both VMs in a shared datastore. On the primary database VM (dsib2031), I added a second vmdk on a 25 GB datastore (presented only to host .139) to house the database, including the archive logs. I used a simple file system (ext4 – /oracledg) setup for the DB rather than ASM (though the whitepaper previously mentioned uses that file system) because it is easier to explain the process that way. Note the standby VM (dsib2032) doesn’t require the second vmdk since we’ll be adding that as a snapshot later.
After installing OEL and applying the correct packages, I installed the Oracle 12c software on each VM – single user/group oracle/dba. I then created a general purpose database called “chicago” and placed it in archivelog mode on the file system (/oracledg). At this point, we are ready to complete the steps to instantiate the standby database which in keeping with the Oracle docs I’ll call “boston.”.
- First turn on forced logging on the primary database to ensure consistency in the standby database:
ALTER DATABASE FORCE LOGGING;
- Setup a password file on the primary server and copy it to a new file on the standby server to authenticate for redo. Alternatively you can use SSL.
- Create standby redo logs on the primary. Here are my SQL statements:
ALTER DATABASE ADD STANDBY LOGFILE (‘/oracledg/oracle/slog1.rdo’) SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/oracledg/oracle/slog2.rdo’) SIZE 500M;
- Add init.ora parameters to the primary database. In Oracle’s example in the documentation, they make it possible for the primary database to revert to a standby database. I saw no reason to deviate so I added these verbatim – note they include some parameters which you’ll have already like DB_NAME. The most important here is LOG_ARCHIVE_DEST_2 which includes the service name for the standby database (boston) so that the redo can be sent:
- At this point I go a little out of order to the documentation’s. We need a control file for the standby and a parameter file. Oracle has you create these after creating a copy of the primary for the standby; however since I am using a single mount, it is much easier to create these files on the disk I am going to snapshot so they are there when I bring it over. So to create the standby control file:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/ORACLEDG/ORACLE/BOSTON.CTL’;
- Next the parameter file for the standby:
CREATE PFILE=’/ORACLEDG/ORACLE/INITBOSTON.ORA’ FROM SPFILE;
- OK now we’re good to get a copy of the primary with SnapVX without doing anything else to the DB (like hot backup mode). Although you are welcome to use Solutions Enabler CLI, since I had a single device in my storage group it was so much easier to use Unisphere for VMAX. If your storage group contains more than the devices you need to copy, you will have to use CLI since Unisphere only takes snapshots at the storage group level. Here are the 4 steps to create the snapshot of my single device, in the storage group, with Unisphere. Note here in my test I am using NOCOPY for SnapVX but in a real-world I’d do a COPY (full) and then the link would no longer be required.
- With the snapshot in place, I can link it to a device so I can then present it to my ESXi server where the standby VM is running. Again, Unisphere makes this easy since it will create the device and storage group on-the-fly for me. All I have to do it create a masking view to present it (not including that part here). Here is the linking.
- So, my snapshot is linked and presented to the standby ESXi server. Since the device is a snapshot from an ESXi perspective, I added the datastore through the standard wizard, assigning a new signature.
- The final step from the VMware side is to add the vmdk in that snapshot datastore to the standby VM.
- Even though the disk is now added to the standby VM, you will still have to rescan in the OS to see the disk (or reboot) and then mount the device. I used the exact same mount /oracledg to make my life easier.
- So let’s finish up. I created a new directory /oracledg/oracle/boston to separate it from the chicago one and put my DB files from the snapshot into the new folder. Open up the INITBOSTON.ora parameter file and add the necessary changes for the standby database – this will include the convert parameters for the new boston directory and the new control file.
- Be sure to double-check that all the necessary directories on the OS mount are there before continuing. For instance, since there has never been a database on the standby server, none of the audit directories are going to be present. If you forget to do this your STARTUP MOUNT will fail on the standby telling you these directories are missing.
- As far as the network, make sure you have tnsnames.ora entries so that chicago can transfer redo to boston. The Oracle docs cover this – you can manually edit the files or use the GUI netca.
- Create an spfile from the edited parameter file (optional).
- We’re ready to put the database into standby mode. First mount it, then set it to auto-recovery. You need to also disconnect in the statement or it is going to hang at the SQL prompt, recovering.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE –
DISCONNECT FROM SESSION;
- So we’re good right? Well I suppose we should check. Oracle provides this syntax to be sure we are applying redo.
SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM
V$MANAGED_STANDBY WHERE CLIENT_PROCESS=’LGWR’ OR PROCESS=’MRP0′;
Turns out we are good. I think the aforementioned whitepaper will be updated with this information in some form in the future, but for now hopefully this will suffice.
I should note that I had a colleague run through a similar scenario and he was unable to get Oracle to stop asking for recovery logs and thereby could not open the database. I double-checked my environment and I was able to open my database but I’m happy to hear from users who try this to see if they can put the database in standby but can’t open it (write or read-only).