Recently in the team we came across the fact that we had to be working a lot with technology inside the EMC storage environment which is called BCV ( business continuity volume) . This technology enables the possibility to clone a (production) Oracle database to another server. Another method would be to use Snaps in the EMC but that is out of scope for this blog now ( though I do work with them as well and to an Oracle Dba the End result is the same cause both technologies offer replication – cloning ).
In this scenario there are two ways to do to an replication of the data in the EMC env..
- Either you will do an alter database begin backup – and backup than do a split in the EMC , do and alter database end backup on the source side . After that on the Unix Side the disks need to be made visible and mounted (when using file systems). If you are using ASM then Linux administrators need to make sure that all the disks needed for the database are present in the multi-path configuration. Once that Unix/ Linux part is done you could mount the database ( when using a file system structure ) or mount the disk groups in ASM and then mount the database for backups or open the database.
- Or you do a split in the EMC ( without the begin and end backup) on the source environment. The rest of the scenario is similar to 1 which means make the disk visible and mount the database or mount the disk groups , then mount the database. In this scenario you will open the Database on this target side thus performing a crash recovery of the instance.
In this blog we will follow scenario 2. Once the file systems are mounted on the target Unix machine the database is opened. Purpose of this all would be to create a migration database where the project members could consolidate data in a local and extra created tablespace as part of a migration strategy.
After the file systems had been mounted by Unix Admins , by dbas request some extra local mount points had been set up on top of the mount points that had come across with the BCV. Once the database was opened the first time an extra tablespace was created on those file systems only locally present on the target machine. This tablespace was created 1.1TB in size. Together with project it was agreed that this tablespace would become a transportable tablespace since recreating it after a BCV refresh would take some 6 to 7 hours each time a refresh occurred. Making it a transportable tablespace would offer the idea of unplugging it right before a BCV refresh would occur and plugging it back in after a refresh thus saving time.
This scenario worked well many times, but as always one time things did not work out well. After a refresh and after the plug-in of the tablespaces there was technical issue on the local file systems . Unix Admins had run repair jobs on the file system where the datafiles of the transportable tablespace (TTS) was located and in the end even had cleaned out that file system .. So there i was with a refreshed database that had plugged in the transportable tablespace of 1.1TB and the database had crashed and would not open ( since the datafiles for the TTS where missing). A refresh from production would take 3 – 4 hours extra downtime and i would still have to recreate the tablespace by hand anyhow after that .
Then i recalled a scenario from the old days in which the scenario was that you had a database without a backup of the new created tablespace that had crashed. Once again Google was my friend and after doing some reading I set up the scenario below after confirming that this could be my way out:
## First of all it was needed to find all the datafiles supposed to be part of this TTS:
where name like ‘%CVN_TBS%’
order by 1;
55 rows selected.
## Next step was to offline drop them since there was no Backup present.
select ‘alter database datafile ‘||file#||’ offline drop ;’
where name like ‘%CVN_TBS%’
order by 1;
## Spooled it to a file and ran the script so that dropped the datafiles ..
## In the next step opened db
alter database open;
## Recreating the 1.1 TB tablespace would take long hours so i created the tablespace first with a small datafile then started alter tablespace with ranges of datafile to be added in script and ran it in background..
# start all steps
nohup /opt/oracle/SRCENS1/admin/create/step6_11.ksh& <– adding datafile #2 – 10
nohup /opt/oracle/SRCENS1/admin/create/step6_21.ksh& <– adding datafile #11 – 20
Since I knew that project would not need all 1.1 TB from the first minute that scenario minimized my downtime, thus saving time. And indeed at the next normal refresh first I unplugged the TTS again , got a new refreshed BCV and plugged the TTS back in . Thus making everybody happy again.
As always happy reading and till we meet again,