Creating multiple database clones on off-host node for reporting or testing

Article:HOWTO74598  |  Created: 2012-03-15  |  Updated: 2012-03-15  |  Article URL http://www.symantec.com/docs/HOWTO74598
Article Type
How To

Product(s)

Environment

Subject

Languages

Creating multiple database clones on off-host node for reporting or testing

Customer scenario

If you have a large enterprise setup comprising three-node parallel Oracle database using Storage Foundation for Oracle RAC, you can use Storage Foundation for Databases (SFDB) tools to create multiple database clones on the off-host node. You can use these database clones for testing or reporting without loading the primary database with queries.

Figure: Storage Foundation for Oracle RAC Cluster and Off-host Node with Shared Storage shows the three-node Oracle RAC cluster and the off-host node with shared storage.

Solution

Symantec recommends the following solution for this scenario. For this solution, an additional off-host node must be attached to the cluster. However, this node is not a part of the Cluster Volume Manager (CVM) cluster.

  1. As root, use the Storage Foundation commands to create FlashSnap setup on the SF Oracle RAC cluster.

  2. As a DBA, use SFDB tools to perform the following operations.

    • Take a snapshot of the database using FlashSnap.

    • Clone the snapshot on the off-host node.

    • Create multiple instant-mode clones of the clone database using Database Storage Checkpoint.

      These clones databases can be used for various testing or reporting purposes.

    • Destroy the Checkpoint clones after the reporting or testing tasks are completed.

    • Resynchronize the mirrors with primary database after the Checkpoint clones are destroyed.

    • Repeat the previous operations as necessary or on a daily basis.

Note:

The off-host node configuration is recommended but optional. You can use any cluster node as the off-host node. The use of the additional off-host node ensures that multiple database clones do not affect the performance of the production database. The primary database is never loaded with the report processing on the secondary using multiple clones because the snapshot is taken off-host. Therefore, the clone processing is independent of the primary.

Detailed steps

Create the FlashSnap setup .

See “To create the FlashSnap setup”.

Create multiple database clones on the off-host node.

See “To create multiple database clones on the off-host node”.

The outline of the SF Oracle RAC cluster and the off-host node with shared storage is as follows.

Figure: Storage Foundation for Oracle RAC Cluster and Off-host Node with Shared Storage

Storage Foundation for Oracle RAC Cluster and Off-host Node with Shared Storage

Use the Storage Foundation commands to create the FlashSnap setup.

To create the FlashSnap setup

  1. Prepare the volumes for instant snapshots.

    # vxsnap -g datadg prepare datavol alloc=disk1
# vxsnap -g datadg prepare archvol alloc=disk2

  2. Add mirrors to the volumes on which the database is configured.

    # vxsnap -g datadg addmir datavol alloc=disk_6
# vxsnap -g datadg addmir archvol alloc=disk_7

Use the SFDB tools to create multiple database clones for testing or reporting.

To create multiple database clones on the off-host node

  1. Take a snapshot of the database using FlashSnap

    $ vxsfadm -s flashsnap -a oracle --oracle_sid sfaedb1 \
--oracle_home /ora_base/db_home -o snap --name snap
Validating database configuration for 
third-mirror-break-off snapshot:
    ORACLE_SID                  = sfaedb1
    ORACLE_HOME                 = /ora_base/db_home
    APP_MODE                    = online
    ARCHIVELOG_DEST             = /arch/sfaedb
    SNAPSHOT_ARCHIVE_LOG        = yes
Database validation successful.
snapshot started at Fri Oct 14 19:15:31 2011.
Putting database in backup mode ...                       Done
Validating database volume layout for 
third-mirror-break-off snapshot:
  Data volumes ready for snapshot:
     Volume/volume-set datavol of diskgroup datadg mounted on /data.
  Storage units to be used for snapshot from diskgroup datadg:
        disk_6
  Archivelog volume ready for snapshot:
     Volume/volume-set archvol of diskgroup datadg mounted on /arch.
  Storage units to be used for snapshot from diskgroup datadg:
        disk_7
    SNAPSHOT_VOL_PREFIX         = SNAP_
    SNAPSHOT_DG_PREFIX          = SNAP_
Database volume layout validated successfully.
Creating snapshot volumes for data volumes ...            Done
Taking database out of backup mode ...                    Done
Flushing online redo logs ...                             Done
Creating snapshot volume for archivelog volume ...        Done
Copying snapshot information to snapshot volume ...       Done
Creating snapshot diskgroups ...                          Done
Deporting snapshot diskgroups ...                         Done
Snapshot of database sfaedb1 is in diskgroups:
SNAP_datadg
snaphot ended at Fri Oct 14 19:16:15 2011.

  2. Clone the database on the off-host node using FlashSnap.

    $ vxsfadm -s flashsnap -a oracle --oracle_sid sfaedb1 \
--oracle_home /ora_base/db_home -o clone --name snap \
--clone_name clon --clone_path /mnt --secondary_host bkphost
Retrieving snapshot information ...                        Done
Importing snapshot diskgroups ...                          Done
Mounting snapshot volumes ...                              Done
Preparing parameter file for clone database ...            Done
Mounting clone database ...                                Done
Starting database recovery ...
Clone database clon is open.

  3. Create multiple clones of the clone database using Database Storage Checkpoint.

    • Create an instant Database Storage Checkpoint of the clone database.

      $ vxsfadm -s checkpoint -a oracle --oracle_sid clon \
--oracle_home /ora_base/db_home -o create \
--name ckpt1 --app_mode instant
Creating Storage Checkpoint ckpt1 ...                    Done

    • Use the instant Database Storage Checkpoint to create multiple clones.

      $ vxsfadm -s checkpoint -a oracle --oracle_sid clon \
--oracle_home /ora_base/db_home -o clone --name ckpt1 \
--clone_name cln1 --clone_path /clnmnt1
Creating Storage Checkpoint ckpt1_rw_1318606917 ...      Done
Storage Checkpoint ckpt1_rw_1318606917 created
Mounting Checkpoint to /clnmnt1...                       Done
Cloning the Pfile ...                                    Done
Mounting the database...                                 Done
Recovering the clone database...                         Done

      $ vxsfadm -s checkpoint -a oracle --oracle_sid clon \
--oracle_home /ora_base/db_home -o clone --name ckpt1 \
--clone_name cln2 --clone_path /clnmnt2
Creating Storage Checkpoint ckpt1_rw_1318611737 ...      Done
Storage Checkpoint ckpt1_rw_1318611737 created
Mounting Checkpoint to /clnmnt2...                       Done
Cloning the Pfile ...                                    Done
Mounting the database...                                 Done
Recovering the clone database...                         Done

  4. Destroy the Checkpoint clones when they are no longer needed.

    $ vxsfadm -s checkpoint -a oracle --oracle_sid clon \
--oracle_home /ora_base/db_home -o delete --name ckpt1 \
--clone_name cln1
Shutting down the clone database cln1 ...                  Done
Unmounting the checkpoint...                               Done
Deleting the Checkpoint ckpt1_rw_1318606917...
Deleting Checkpoint from /mnt/data...                      Done

    $ vxsfadm -s checkpoint -a oracle --oracle_sid clon \
--oracle_home /ora_base/db_home -o delete --name ckpt1 \
--clone_name cln2
Shutting down the clone database cln2 ...                  Done
Unmounting the checkpoint...                               Done
Deleting the Checkpoint ckpt1_rw_1318611737...
Deleting Checkpoint from /mnt/data...                      Done

  5. Resynchronize the mirrors with the primary volumes after the Checkpoint clones are destroyed.

    • Unmount the FlashSnap clone.

      $ vxsfadm -s flashsnap -a oracle --oracle_sid sfaedb1 \
--oracle_home /ora_base/db_home -o unmount --name snap \
--clone_name clon --clone_path /mnt --secondary_host bkphost
Shutting down clone database clon ...
Database shut down.
Retrieving snapshot information ...                      Done
Unmounting snapshot volumes ...                          Done
Deporting snapshot diskgroups ...                        Done

    • Resynchronize the mirrors with the primary volumes.

      $ vxsfadm -s flashsnap -a oracle --oracle_sid sfaedb1 \
--oracle_home /ora_base/db_home -o resync --name snap
resync started at Fri Oct 14 20:53:33 2011.
Importing snapshot diskgroups ...                        Done
Joining snapshot diskgroups to original diskgroups ...   Done
Reattaching snapshot volumes to original volumes ...     Done
resync ended at Fri Oct 14 20:54:08 2011.

  6. Repeat steps 1 to 5 as necessary.

See About storage and availability management use cases for databases

Legacy ID



uxrt-60_v70617351_uxrt-60_v72974483


Article URL http://www.symantec.com/docs/HOWTO74598


Terms of use for this information are found in Legal Notices

Please Sign In

Login using SymAccount.

Knowledge Base Search

Knowledge Base Search

Rate this Article

Help us improve your support experience.

MySymantec

MySymantec

Contacting Support

Contacting Support