Transport Transactional tablespace

From db01-lisprd

Create directory object for Data Pump

SQL> create directory pwprd_dir as '/d01/app/pwprd';

Directory created.

SQL> grant read, write on directory pwprd_dir to system;

Grant succeeded.

Bring HLPR tablespace to read only mode to facilitate transfer

SQL> alter tablespace hlpr read only;

Tablespace altered.

Export metadata for tablespace with Data Pump. We also choose the transport_full_check option so that Data Pump will check tablespace dependencies. This was already performed via SQL*Plus but is used here just as a precaution.

-bash-3.00$ expdp system/p1pathway dumpfile=hlpr.dmp directory=pwprd_dir transport_tablespaces=hlpr transport_full_check=y

Export: Release 10.2.0.1.0 - 64bit Production on Thursday, 28 September, 2006 11:25:38

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=hlpr.dmp directory=pwprd_dir transport_tablespaces=hlpr transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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:
/d01/app/pwprd/hlpr.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:26:22

Launch RMAN and use the convert command to capture the HLPR tablespace in a backupset.

-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 28 11:26:49 2006

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

connected to target database: PWHLPR (DBID=691297521)

RMAN> convert tablespace hlpr format '/d01/app/pwprd/hlpr_%U';

Starting backup at 28-SEP-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=510 instance=PWHLPR1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+HLPR_DATA/pwhlpr/datafile/hlpr01.ora
converted datafile=/d01/app/pwprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-6_09hudqct
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:36
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=+HLPR_DATA/pwhlpr/datafile/hlpr.266.602275983
converted datafile=/d01/app/pwprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-9_0ahudqnd
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:35
Finished backup at 28-SEP-06

Move all files to rptdb01-lisprd.ftw


From rptdb01-lisprd

-bash-3.00$ ls -lrt
total 68248184
drwxrwx--- 3 pwrpprd pdba 512 Sep 21 11:18 product
-rw-r----- 1 pwrpprd pdba 385024 Sep 28 12:43 hlpr.dmp
-rw-r----- 1 pwrpprd pdba 26214408192 Sep 28 13:12 hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-6_09hudqct
-rw-r----- 1 pwrpprd pdba 8560648192 Sep 28 13:21 hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-9_0ahudqnd
-rw-r----- 1 pwrpprd pdba 150499328 Sep 28 13:22 pathway.dmp
-rw-rw-r-- 1 pwrpprd pdba 26767 Sep 28 13:38 import.log
-bash-3.00$
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Thu Sep 28 13:39:23 2006

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

connected to target database: PWHLPR (DBID=693046759)

RMAN> convert datafile '/d01/app/pwrpprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-6_09hudqct',
2> '/d01/app/pwrpprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-9_0ahudqnd'
3> format '+HLPR_DATA';

Starting backup at 28-SEP-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=54 instance=PWHLPR1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/d01/app/pwrpprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-6_09hudqct
converted datafile=+HLPR_DATA/pwhlpr/datafile/hlpr.262.602343695
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:09:45
channel ORA_DISK_1: starting datafile conversion
input filename=/d01/app/pwrpprd/hlpr_data_D-PWHLPR_I-691297521_TS-HLPR_FNO-9_0ahudqnd
converted datafile=+HLPR_DATA/pwhlpr/datafile/hlpr.263.602344281
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:56
Finished backup at 28-SEP-06

-bash-3.00$ impdp system/pathway dumpfile=hlpr.dmp directory=pwprd_dir transport_datafiles='+HLPR_DATA/pwhlpr/datafile/hlpr.262.602343695','+HLPR_DATA/pwhlpr/datafile/hlpr.263.602344281'

Import: Release 10.2.0.2.0 - 64bit Production on Thursday, 28 September, 2006 14:05:30

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=hlpr.dmp directory=pwprd_dir transport_datafiles=+HLPR_DATA/pwhlpr/datafile/hlpr.262.602343695,+HLPR_DATA/pwhlpr/datafile/hlpr.263.602344281
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:05:55

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
5 UNDOTBS2 YES NO YES
6 HLPR YES NO YES

7 rows selected.

SQL> alter tablespace hlpr read write;

Tablespace altered.