How to transport a Tablespace to Databases in a Physical Standby Configuration [ID 467752.1]

How to transport a Tablespace to Databases in a Physical Standby Configuration [ID 467752.1]
		Modified 28-NOV-2007     Type HOWTO     Status PUBLISHED

In this Document
  Goal
  Solution
  References

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3
Information in this document applies to any platform.

Goal

To transport a tablespace from database 1 to database 2, database 2 is the primary site in a dataguard physical standby configuration.  This note assumes that the physical standby is configured and is operating normally with logs being transported and applied successfully.

Solution

Database 1
~~~~~~~~~

At the source site for the transportable tablespace, database 1, for demonstration purposes a new tablespace is created.  It is this tablespace that will transported from database 1 to the physical standby configuration.

SH10.sys.SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
USERS           /u01/oradata/sh10/users01.dbf
SYSAUX          /u01/oradata/sh10/sysaux01.dbf
UNDOTBS1        /u01/oradata/sh10/undotbs01.dbf
SYSTEM          /u01/oradata/sh10/system01.dbf

SQL> create tablespace plb datafile '/u01/oradata/sh10/plb01.dbf' size 10M;

Tablespace created.

SH10.sys.SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
USERS           /u01/oradata/sh10/users01.dbf
SYSAUX          /u01/oradata/sh10/sysaux01.dbf
UNDOTBS1        /u01/oradata/sh10/undotbs01.dbf
SYSTEM          /u01/oradata/sh10/system01.dbf
PLB             /u01/oradata/sh10/plb01.dbf

A new directory is created for use by datapump.  This will need to be created in database 1 from which we are transporting the tablespace and database 2, the target to which we are transporting the tablespace.

SH10.plb.SQL> create directory plb_dir as '/home/oracle/plb';

Directory created.

SH10.plb.SQL> select * from dba_directories;

OWNER      DIRECTORY_NAME       DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS        PLB_DIR              /home/oracle/plb

Mark the tablespace as read only prior to performing the datapump export of the tablespace metadata.

SH10.sys.SQL> alter tablespace plb read only;

Tablespace altered.

SH10.sys.SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Perform the datapump export of the metadata for the transported tablespace.

[oracle@aulnx8 plb]$ expdp plb/plb directory=plb_dir dumpfile=plb.dmp transport_tablespaces=plb transport_full_check=y logfile=plb.log

Export: Release 10.2.0.2.0 - Production on Monday, 19 November, 2007 9:05:52

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "PLB"."SYS_EXPORT_TRANSPORTABLE_01":  plb/******** directory=plb_dir dumpfile=plb.dmp transport_tablespaces=plb transport_full_check=y logfile=plb.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "PLB"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PLB.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/plb/plb.dmp
Job "PLB"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:06:04

 

Database 2
~~~~~~~~~

Move to the target  for the transportable tablespace, the primary database in the standby configuration, database 2.   In this case the objects from the source would be imported back into the same schema in the primary for the standby configuration.

DGA2.sys.SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
USERS           /u01/oradata/sh10/users01.dbf
SYSAUX          /u01/oradata/sh10/sysaux01.dbf
UNDOTBS1        /u01/oradata/sh10/undotbs01.dbf
SYSTEM          /u01/oradata/sh10/system01.dbf

SQL> create directory plb_dir as '/home/oracle/plb';

Directory created.

DGA2.sys.SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS PLB_DIR /home/oracle/plb

DGA2.sys.SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

DGA2.sys.SQL> ! cp /u01/oradata/sh10/plb01.dbf /u01/oradata/DGA2/plb01.dbf

DGA2.sys.SQL> ! scp /u01/oradata/sh10/plb01.dbf aulnx9:/u01/oradata/DGA2/plb01.dbf

DGA2.sys.SQL> exit

[oracle@aulnx8 plb]$ impdp system/dba directory=plb_dir dumpfile=plb.dmp transport_datafiles=/u01/oradata/DGA2/plb01.dbf

Import: Release 10.2.0.2.0 - Production on Monday, 19 November, 2007 9:46:10

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=plb_dir dumpfile=plb.dmp transport_datafiles=/u01/oradata/DGA2/plb01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 09:46:12

DGA2.sys.SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
-------------------- ----------------------------------------
USERS                /u01/oradata/DGA2/users01.dbf
SYSAUX               /u01/oradata/DGA2/sysaux01.dbf
UNDOTBS1             /u01/oradata/DGA2/undotbs01.dbf
SYSTEM               /u01/oradata/DGA2/system01.dbf
EXAMPLE              /u01/oradata/DGA2/example01.dbf
PLB                  /u01/oradata/DGA2/plb01.dbf

6 rows selected.

 

Database 3 - Physical Standby

~~~~~~~~~~~~~~~~~~~~~~~~

At the standby site we can see that the datapump import, performed in the primary site for the physical standby, has generated and transported to the standby, the relevant redo information required to create the  controlfile and dictionary information regarding the transported tablespace, its datafiles and the segments held within it.

DGA2P..SQL> select name from v$datafile;

NAME
------------------------------------------------------------
/u01/oradata/DGA2P/system01.dbf
/u01/oradata/DGA2P/undotbs01.dbf
/u01/oradata/DGA2P/sysaux01.dbf
/u01/oradata/DGA2P/users01.dbf
/u01/oradata/DGA2P/example01.dbf
/u01/oradata/DGA2P/plb01.dbf

6 rows selected.

DGA2P..SQL> select name from v$tablespace;

NAME
------------------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
PLB

7 rows selected.

References

NOTE:343424.1 - Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply
NOTE:388431.1 - Creating a Duplicate Database on a New Host.

---

 

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Keywords DATAGUARD; DATAPUMP; IMPDP; PHYSICAL STANDBY; PRIMARY SITE; READ-ONLY; TRANSPORTABLE; TRANSPORT_TABLESPACE