Using Transportable Tablespace to create a VLDB database

Problem statement : Need to create a VLDB database for testing operational issues.

Solution: We can use Oracle Transportable Tablespace feature to populate bulk amount of data quickly. First of all, we need to have some data populated in the database, which can be used for replication (of course multiple times).

Source Username: MYUSER
Tablespace: MYTBS
Datapump Directory : dump_dir mapped to OS /datapump
Datafile in MYTBS : +SOURCEDG/MYDB/DATAFILE/DATA10
Target Username : USER_[1..100]

STEP 1
======

We have a tablespace MYTBS, where we added the data. This tablespace will be used for replication purpose.

SQL> alter tablespace mytbs read only
/

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name=’MYTBS’
/

TABLESPACE_NAME                STATUS
—————————— ———
MYTBS                          READ ONLY

STEP 2
======

Take metadata backup of the tablespace MYTBS

$ expdp  system/oracle DIRECTORY=dump_dir TRANSPORT_TABLESPACES=MYTBS DUMPFILE=mytbs.dmp

Export: Release 10.2.0.4.0 – 64bit Production on Thursday, 07 October, 2010 18:38:18

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″:  system/******** DIRECTORY=dump_dir TRANSPORT_TABLESPACES=MYTBS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /datapump/mytbs.dmp
Job “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 18:40:20

STEP 3
======

Replicate the data using RMAN. Every statement will copy the source datafile to a new location.

run
{
COPY DATAFILE ‘+SOURCEDG/MYDB/DATAFILE/DATA10’ TO ‘+TARGETDG/MYDB/DATAFILE/TTSDF_1’;
COPY DATAFILE ‘+SOURCEDG/MYDB/DATAFILE/DATA10’ TO ‘+TARGETDG/MYDB/DATAFILE/TTSDF_2’;
COPY DATAFILE ‘+SOURCEDG/MYDB/DATAFILE/DATA10’ TO ‘+TARGETDG/MYDB/DATAFILE/TTSDF_3’;
…..
…..
…..
COPY DATAFILE ‘+SOURCEDG/MYDB/DATAFILE/DATA10’ TO ‘+TARGETDG/MYDB/DATAFILE/TTSDF_100’;
}

Sample RMAN COPY DATAFILE

Starting backup at Oct 7 2010 11:31:16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00015 name=+SOURCEDG/MYDB/DATAFILE/data_6.259.731010581
output filename=+TARGETDG/MYDB/DATAFILE/TTSDF_1 tag=TAG20101001T113116 recid=322 stamp=731245249
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:09:35
Finished backup at Oct 7 2010 11:40:51

Source datafile size is approximately 10G. RMAN took about 10 mins to make a copy.  So to populate 1TB, it took about 16 hours.

STEP 4
======

Create multiple users, as we can’t have multiple objects with same name in a schema (remember we are replicating same data every time. This will internally create same tables and indexes. This whole exercise is intended to pump the database in the database.)

SET SERVEROUTPUT ON

BEGIN
FOR I IN 1..100
LOOP
 /* This script will create 100 users with default tablespace*/
 /* Also grants DBA privilege to the user */
 
 EXECUTE IMMEDIATE ‘CREATE USER USER_’||I||’ IDENTIFIED BY PASSWORD’||I;
 EXECUTE IMMEDIATE ‘GRANT DBA TO USER_’||I;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,100));
END;
/

STEP 5
======

Once the users are created, we need to import the data. We are not importing the physical data, as we’ve already done that in RMAN COPY. Here we establishing the relationship between datafile, tablespaces and users.

for i in 1 ……. 100;
do
impdp  system/oracle DIRECTORY=dump_dir DUMPFILE=mytbs.dmp TRANSPORT_DATAFILES=’+TARGETDG/MYDB/DATAFILE/TTSDF_1’ REMAP_SCHEMA=MYUSER:USER_$i  REMAP_TABLESPACE=MYTBS:TTSTB_$i;
done

For example:

I copied the datafiles 100 times (TTSDF_1,TTSDF_2,TTSDF_3 … so on) – STEP 3
Created 100 users (USER_1,USER_2,USER_3 .. so on) STEP – 4
Import will create tablespaces (TTSTB_1,TTSTB_2,TTSTB_2…. so on) STEP 5

So user USER_1 will have now objects (same as MYUSER), in tablespace TTSTB_1 (which has datafile TTSDF_1 associated with it).

Sample output of impdp

Import: Release 10.2.0.4.0 – 64bit Production on Thursday, 7 October, 2010 15:38:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″:  system/******** DIRECTORY=dump_dir DUMPFILE=mytbs.dmp TRANSPORT_DATAFILES=+TARGETDG/MYDB/DATAFILE/TTSDF_1 REMAP_SCHEMA=MYUSER:USER_1 REMAP_TABLESPACE=MYTBS:TTSTB_1
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at 15:38:27

STEP 6
======

Last step is to make these tablespaces READ WRITE (after TTS import, the imported tablespaces will be in the READ ONLY mode)

SQL> set heading off
SQL> set pagesize 500

SQL> select ‘alter tablespace ‘||tablespace_name ||’ read write;’ from dba_tablespaces where status =’READ ONLY’
/

ALTER TABLESPACE MYTBS READ WRITE;
ALTER TABLESPACE TTSTB_1 READ WRITE;
ALTER TABLESPACE TTSTB_2 READ WRITE;
……
……
……
ALTER TABLESPACE TTSTB_100 READ WRITE;

SQL> ALTER TABLESPACE MYTBS READ WRITE;

Tablespace altered.

We all are set to experiment with 1 Terabyte+ size database.

Advertisements
This entry was posted in Oracle Data Pump, Oracle Transportable Tablespaces and tagged . Bookmark the permalink.

One Response to Using Transportable Tablespace to create a VLDB database

  1. Mubeen says:

    Your articles are very helpful.

    thanks for sharing ….Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s