By Alex Polishchuk
Often, production databases need to be copied over to development and other environments for performance tuning or testing. The traditional methods of backup and restore or export and import take a long time, especially on large databases. The following approach, utilizing Oracle transportable tablespace (TTS), has been devised to allow fast data propagation from production to several other environments with large data volumes exceeding terabyte capacity.
Oracle TTS feature is a very useful and convenient way of transferring data between databases. It becomes even more powerful when combined with SAN snap technology that allows very fast data copying, regardless of the data volume.
Another aspect that makes using this approach even more attractive is that all the definitions and scripts required to successfully import into the target database are stored on the same SAN logical disk(s) that will be snapped, making it a repeatedly deployable package. This package will be stored as level 1 or “golden copy” SAN snap.
Preparation
Let’s consider the scenario where the tablespaces from production database are copied over to development and other environments utilizing TTS and SAN snap mechanisms. There are several prerequisites for this.
- The objects inside the tablespaces to be transported must be in the self-contained transportable set.
- The tablespaces that will be snapped must reside on separate volume(s) or disk group(s) if using ASM, so that nothing else is affected.
- These volume(s) or disk group(s) in turn must reside on an independent SAN logical device(s), as those will be snapped.
When creating a transportable tablespace set, Oracle generates a file with definitions that contains all objects DDLs, grants, and statistics. This file size depends on the number of transported objects, rather than their size and in most cases is well under 1 GB in size. It is 130 MB in the case described below.
The target database where the transportable tablespace set will be imported must have the users associated with these tablespaces, otherwise the import will fail. Users, in turn, are usually associated with roles. So, both must be created during the process if the target system does not have those. Also, the sequences that belong to the affected users should be re-created, as the values between the systems will be different. Other objects, such as materialized views definitions should be considered, if applicable.
All the required datapump export / import parameter files, DDL for users, roles, sequences, and other necessary objects creation should be placed on the same volume that will be snapped, if using a file system. In case of ASM an ACFS should be created on the disk group that will be transported. The total required space for all these objects is negligible.
The following figure shows the layout described above.
Figure 1: Storage configuration for TTS over SAN snap
A Real Life Example
Let’s consider the following example. The source database is an Oracle 11gR2 RAC with Advanced DataGuard, while the target is also a RAC or a standalone database configured same as source. All these databases are configured with ASM. Copying is always done between the first RAC nodes on both ends, as transportable tablespace works only on one node. An ACFS of 1GB in size has been created on the source database and mounted on the first node.
The source database has 50 schemas residing on one ASM disk group called APP that is comprised of three SAN logical discs. The disk group is 200 GB in size with 3,766 tables, 41,215 object grants, 5,853 indexes, and a small number of other objects. It takes about 40 minutes for the whole process, of which 11 minutes to export the objects definitions, 16 minutes to import the definitions, 9 minutes for dropping target tablespaces, and only 10 seconds for snapping SAN logical devices. The rest is taken by other necessary steps.
Implementation
The following script is executed on an independent node that has password-less SSH access to the source and target systems. This improves security by preventing password-less SSH access between the source and all the targets, especially that the source is a production system. The following are the actual steps performed in the script:
1. Initialize the environment
2. Check the source and target nodes for connectivity
3. Check the source and target nodes for ACFS availability
4. Generate and save the SQL for sequences on the source for schemas involved in the TTS to recreate on the target
5. Generate and save the SQL for grants on each target to reapply after TTS completion
6. Generate and save the roles SQL on the source for users involved in the TTS to create on the targets
7. Generate and save the SQL on the source for users that own objects involved in the TTS to create on the targets
8. Create the required roles on all targets from step 6
9. Create the required users on all targets from step 7
10. Enable and mount ACFS volume on the source host
11. If the source is a standby database, then convert physical standby to snapshot standby, as it has to be in the read-write mode
12. Check and prepare transportable tablespaces on the source
13. Check and prepare data pump directory on all the targets
14. Generate datapump export / import parameter files
15. Export transportable tablespaces
16. Kill user sessions on all the targets and put those into restricted mode to allow dropping tablespaces, so that users do not hold any locks that may prevent it
17. Drop guaranteed restore points on all the targets if those exist
18. Drop any target tablespaces that will be imported from the source (this can be done in parallel on all targets to save time)
19. Unmount and disable ACFS volumes on all the target hosts
20. Unmount disk group(s) from all the target ASM nodes that will be replaced by the SAN snap
21. Copy the data via SAN snap to all the targets
22. Set the source tablespaces to read-write if on primary database, otherwise convert snapshot standby to physical standby
23. Mount the snapped disk group(s) on all the target ASM nodes
24. Import transportable tablespaces on all the targets (this can be done in parallel on all targets to save time)
25. Set all the targets tablespaces to read-write mode
26. Recompile invalid objects on all the targets (this can be done in parallel on all targets to save time)
27. Recreate sequences on all the targets that were generated in step 4
28. Restore missing grants on all the targets that were saved in step 5
29. Create guaranteed restore points on all the targets if required
30. Unmount and disable ACFS volumes on the source and all target hosts
31. Cleanup
The core of this script copies the source tablespaces over SAN snap mechanism after the target tablespaces are dropped and those disk groups are unmounted from ASM. The source disk group does not need to be unmounted, as the tablespaces residing there are all in read-only mode when the copying starts.
One of the preparation steps is to drop the target database’s guaranteed restore points followed by dropping the target tablespaces, otherwise the operation will fail. The potential risk of these steps is that the target database will have to be restored from backups if TTS fails for some reason.
This script copies the data to the several target databases simultaneously, thus saving time on preparing the source database. Also, it runs the longest operations of dropping target tablespaces and importing transportable tablespaces in parallel in the background on the target databases.
In addition, the script gracefully manages the primary or physical standby source database by determining its type dynamically. If the source is a physical standby database, then the script will put it into physical snapshot mode while running, as the preparation requires the source database to be in read-write mode.
The target database may have grants from the objects to be transported to the other users. These grants are saved before dropping the target tablespaces and restored after transport is completed.
Issues
At the time of this writing, there are some open Oracle bugs that may affect this approach. One bug 13642185 pertains to ACFS device file group ownership reset to root on server reboot. It will cause ACFS failure if not patched. The other bug 13968048 is about transportable tablespace import taking twice as long on RAC compared to single instance. The first one will not be an issue if using file system and the other one can be ignored if time is not an issue or the target is a standalone database.
Conclusion
This approach offers great performance improvement and time saving by utilizing well-known technologies combined together to produce a repeatedly deployable package. Significantly reduced time and resource usage allows for multiple deployments in the same time period for testing, such as Oracle Real Application Testing, development, and other required activities.
About the author
Alex Polishchuk is the founder and president of Advanced Computer Consulting (www.advcomputerconsulting.com). Alex has over twenty years of professional experience designing, developing, and implementing database applications in various industries and companies ranging from small to Fortune 50 corporations. Alex’s primary area of expertise is in database performance optimization and tuning. He can be contacted at alex@advcomputerconsulting.com.
