Upon that cosmic plate of the sky, the sun and the moon are the lamps. The stars and their orbs are the studded pearls. The
fragrance of sandalwood in the air is the temple incense, and the wind is the fan. All the plants of the world are the altar flowers in offering to You, O Luminous Lord. 1 What a beautiful Aartee, lamp-lit worship service this is! O Destroyer of Fear, this is Your Ceremony of Light. The Unstruck Sound-current of the Shabad is the vibration of the temple drums.
Index of all the posts of Gurpartap Singh's Blog
In this post we will do initial load using cross platform transportable tablespaces. Our source is Solaris 64 bit with big endian format and
target is Linux x86 64bit with little endian format. We will use package "DBMS_FILE_TRANSFER" to transfer files from ASM to ASM from
source to target. Incase it would have been a same eindian format you don't need extra space to place datafiles before transfer. In this case we will
need to do conversion from big endian format to little endian format so we will need extra space to place converted files. Database versions on source and target are 10.2.0.4. On 11g you can use 'cp' command to transfer files from ASM to ASM from source to target.
When you convert files it will not effect the orignal files and will create new files after conversion.
I am using note: 371556.1
Lets do it now.
Create a single instance databae on source
Server:goldengate
Database name:DFSRDV
Create a test rac instance on target
Server:rac1/rac2/tac2
Database name: DFTGDV
Instance Names: DFTGDV1 (on rac1)
Instance Names: DFTGDV2 (on rac2)
Instance Names: DFTGDV3 (on rac3)
Following is the tnsentry for the target database:
DFTGDV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 41521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 41521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3-vip)(PORT = 41521))
)
(CONNECT_DATA =
(SERVICE_NAME = DFTGDV)
)
)
Following is the tnsentry for the source database:
DFSRDV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = goldengate)(PORT = 41521))
)
(CONNECT_DATA =
(SID = DFSRDV)
(SERVER = DEDICATED)
)
)
Create a new tablespace in the source database for testing and then create a schema to use this tablespace as
a primay tablspace and add anoter 2 datafiles to it and then add some tables to this user. I will just give dba to this user
instead of doing grants one by one.
Create tablespace:
CREATE TABLESPACE migration DATAFILE '+ORADATA1_DG' SIZE 200M;
Create user:
create user migrate identified by migrate default tablespace migration temporary tablespace temp;
grant connect,resource,dba to migrate;
Alter tablespace:
alter tablespace migration add datafile '+ORADATA1_DG' SIZE 200M;
alter tablespace migration add datafile '+ORADATA1_DG' SIZE 200M;
Output is as:
oracle : goldengate : @DFSRDV : /home/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 18:59:54 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE TABLESPACE migration DATAFILE '+ORADATA1_DG' SIZE 200M;
Tablespace created.
SQL> create user migrate identified by migrate default tablespace migration temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to migrate;
Grant succeeded.
SQL> alter tablespace migration add datafile '+ORADATA1_DG' SIZE 200M;
Tablespace altered.
SQL> alter tablespace migration add datafile '+ORADATA1_DG' SIZE 200M;
Tablespace altered.
SQL>
Now we should have at least 3 datafiles in this tablespace and this being the deault tablespace of the user:
col FILE_NAME format a90
col TABLESPACE_NAME format a20
select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MIGRATION';
Output is as:
SQL> col FILE_NAME format a90
col TABLESPACE_NAME format a20
select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MIGRATION';
SQL> SQL>
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------------------------------- --------------------
+ORADATA1_DG/dfsrdv/datafile/migration.309.736802539 MIGRATION
+ORADATA1_DG/dfsrdv/datafile/migration.310.736802721 MIGRATION
+ORADATA1_DG/dfsrdv/datafile/migration.311.736802729 MIGRATION
SQL>
Now lets login as user migration and create some tables and data in there before we migrate:
conn migrate/migrate
create table transfer as select * from dba_tables;
select count(*) from transfer;
create table places as select * from dba_objects;
select count(*) from places;
Then lets verify that the tables are in this tablespce:
select table_name,tablespace_name from user_tables;
Here is the output:
SQL> conn migrate/migrate
Connected.
SQL> show user
USER is "MIGRATE"
SQL> create table transfer as select * from dba_tables;
Table created.
SQL> select count(*) from transfer;
COUNT(*)
----------
1589
SQL> create table places as select * from dba_objects;
Table created.
SQL> select count(*) from places;
COUNT(*)
----------
50602
SQL> commit;
Commit complete.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
PLACES MIGRATION
TRANSFER MIGRATION
SQL>
Now we are ready for the migration:
We will use ASM to ASM direct transfer using package DBMS_FILE_TRANSFER and since we are going to do cross platform migration with different
endian format i.e. [Solaris[tm] OE (64-bit)] to Linux [Linux x86 64-bit], we will have to use RMAN convert to to convert the endian format
from big to little as [Solaris[tm] OE (64-bit)] is big endian format and [Linux x86 64-bit] is small endian format.
Remeber that you need to read the document to see the limitations of using the transportable tablespaces, here I will discuss only few.
So what to use, exp/imp or expdp or impdp
Pre-steps:
From documentation we have:
Beginning with Oracle Database 10g Release 2, you can transport tablespaces
that contain XMLTypes, but you must use the IMP and EXP utilities,
not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS
parameters are set to Y (the default).
The following query returns a list of tablespaces that contain XMLTypes:
select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
t.tablespace_name=p.tablespace_name and
x.owner=u.username;
Output is as:
SQL> select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
t.tablespace_name=p.tablespace_name and
x.owner=u.username; 2 3 4 5
TABLESPACE_NAME
--------------------
USERS
SYSAUX
SQL>
We are going to migrate tablespace "MIGRATION" and that is not in the list so we can use datapump here.
Also, you cannot transport the SYSTEM tablespace or objects owned by the user SYS.
Remeber:
Opaque Types Types(such as RAW, BFILE, and the AnyTypes) can be transported, but
they are not converted as part of the cross-platform transport operation.
Their actual structure is known only to the application, so the application
must address any endianness issues after these types are moved to the new
platform.
Also:
Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable
using Data Pump but not the original export utility, EXP.
So, we are good to use expdb/impdp
==================================================================================================================
So, lets start with the process:
---------Check that the tablespace will be self contained:
conn / as sysdba
execute sys.dbms_tts.transport_set_check('MIGRATION', true);
--- Check for violations : select * from sys.transport_set_violations;
Output is as:
SQL> conn / as sysdba
Connected.
SQL> execute sys.dbms_tts.transport_set_check('MIGRATION', true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
SQL>
If you have any rows returned then these violations must be resolved before the tablespaces
can be transported
But we are good to go.
----------The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export.
ALTER TABLESPACE MIGRATION READ ONLY;
Output is as:
SQL> ALTER TABLESPACE MIGRATION READ ONLY;
Tablespace altered.
SQL>
Now we will create the directory object to export the metadata of the tablespace as:
CREATE OR REPLACE DIRECTORY dpump_dir AS '/ora/fs0000/work/dpump_dir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
expdp system/system DUMPFILE=exp_tablespace_migration.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = MIGRATION
If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter:
Syntax would be following but we are not doing that as we already checked that everythingis self contained:
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y
Output is as:
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 20:08:12 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/ora/fs0000/work/dpump_dir' ;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ expdp system/system DUMPFILE=exp_tablespace_migration.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = MIGRATION
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 03 December, 2010 20:09:01
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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=exp_tablespace_migration.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = MIGRATION
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:
/ora/fs0000/work/dpump_dir/exp_tablespace_migration.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:10:31
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ ls -lart
total 243
drwxr-xr-x 12 oracle dba 14 Dec 3 20:06 ..
drwxr-xr-x 2 oracle oinstall 4 Dec 3 20:09 .
-rw-r----- 1 oracle oinstall 118784 Dec 3 20:10 exp_tablespace_migration.dmp
-rw-r----- 1 oracle oinstall 1033 Dec 3 20:10 export.log
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ cat export.log
;;;
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 03 December, 2010 20:09:01
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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=exp_tablespace_migration.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = MIGRATION
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:
/ora/fs0000/work/dpump_dir/exp_tablespace_migration.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:10:31
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$
Execute the following command to see which platform are we on source and target and here it is:
On source we have:
set lien 300
SELECT tp.platform_id,substr(d.PLATFORM_NAME,2,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
Output is as:
SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,2,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
2 3
PLATFORM_ID SUBSTR(D.PLATFORM_NAME,2,30) ENDIAN_FORMAT
----------- ------------------------------------------------------------------------------------------------------------------------ --------------
2 olaris[tm] OE (64-bit) Big
SQL> SQL>
On target we have:
SQL> set line 300
SELECT tp.platform_id,substr(d.PLATFORM_NAME,2,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
SQL> 2 3
PLATFORM_ID SUBSTR(D.PLATFORM_NAME,2,30) ENDIAN_FORMAT
----------- ------------------------------------------------------------------------------------------------------------------------ --------------
13 inux x86 64-bit Little
SQL>
So we are going from big to little.
================================================================================================================
Now we will transfer files using DBMS_FILE_TRANSFER
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2,
destination_database IN VARCHAR2);
1) Create a directory at target database host, and give permissions to
local user.
This is the directory object into which the file is placed at the
destination site, it must exist in the remote file system.
CREATE OR REPLACE DIRECTORY target_dir AS '+DEST_ORADATA1_DG' ;
GRANT WRITE ON DIRECTORY target_dir TO system;
Output is as:
oracle : rac1 : @DFTGDV1 : /home/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 20:29:55 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE OR REPLACE DIRECTORY target_dir AS '+DEST_ORADATA1_DG' ;
Directory created.
SQL> GRANT WRITE ON DIRECTORY target_dir TO system;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
oracle : rac1 : @DFTGDV1 : /home/oracle
$
Now,
2) Create a directory at source database host. The directory object from which
the file is copied at the local source site. This directory object must
exist at the source site.
CREATE OR REPLACE DIRECTORY source_dir AS '+ORADATA1_DG/dfsrdv' ;
GRANT READ,WRITE ON DIRECTORY source_dir TO system;
CREATE OR REPLACE DIRECTORY source_dir_1 AS 'ORADATA1_DG/dfsrdv/datafile' ;
GRANT READ,WRITE ON DIRECTORY source_dir TO system;
Output is as:
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 20:34:12 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE OR REPLACE DIRECTORY source_dir AS '+ORADATA1_DG/dfsrdv' ;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY source_dir TO system;
Grant succeeded.
SQL> CREATE OR REPLACE DIRECTORY source_dir_1 AS '+ORADATA1_DG/dfsrdv/datafile' ;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY source_dir TO system;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$
Now,
Create a dblink to connect to target database host:
CREATE DATABASE LINK DFTGDV CONNECT TO system IDENTIFIED BY system USING 'DFTGDV';
Output is as:
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 20:36:38 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> conn system/system
Connected.
SQL> CREATE DATABASE LINK DFTGDV CONNECT TO system IDENTIFIED BY system USING 'DFTGDV';
Database link created.
SQL>
Test the dblink as:
select * from user_users@DFTGDV
Here is the output:
SQL> select * from user_users@DFTGDV
2 ;
USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE
------------------ ------------------ ------------------------------
TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
SYSTEM 5 OPEN
SYSTEM
TEMP 12-MAR-08 SYS_GROUP
SQL>
Now,
4) Connect to source instance:
DFSRDV => Connect string to source database
DFTGDV => dblink to target database
a1.dat => migration.309.736802539
a4.dat => migrated_from_source.dbf
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------------------------------- --------------------
+ORADATA1_DG/dfsrdv/datafile/migration.309.736802539 MIGRATION
+ORADATA1_DG/dfsrdv/datafile/migration.310.736802721 MIGRATION
+ORADATA1_DG/dfsrdv/datafile/migration.311.736802729 MIGRATION
CONNECT system/system@DFSRDV
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_1' , 'migration.309.736802539' , 'target_dir' , 'migrated_from_source.dbf' , 'DFTGDV' ) ;
END ;
Due to unpublished bug 8412695, I am getting error ORA-19563 (at the time of writing this note oracle was still working
on this bug and there is was no solution to it), so I have to convert the file at source.
SQL> BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_1' , 'migration.309.736802539' , 'target_dir' , 'migrated_from_source.dbf' , 'DFTGDV' ) ;
END ; 2 3
4 /
BEGIN
*
ERROR at line 1:
ORA-19563: File transfer: data file conversion header validation failed for
file +DEST_ORADATA1_DG/migrated_from_source.dbf
ORA-02063: preceding line from DFTGDV
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 60
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 168
ORA-06512: at line 2
SQL>
So, I will convert the file at source as:
RMAN> convert tablespace migration to platform="Linux x86 64-bit" FORMAT '+DFDV_ORADATA1_DG';
Output is as:
oracle : goldengate : @DFSRDV : /home/oracle
$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 3 21:06:23 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: DFSRDV (DBID=1616533891)
RMAN> convert tablespace migration to platform="Linux x86 64-bit" FORMAT '+DFDV_ORADATA1_DG';
Starting backup at 03-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+ORADATA1_DG/dfsrdv/datafile/migration.309.736802539
converted datafile=+ORADATA1_DG/dfsrdv/xtransport/migration.312.736808821
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=+ORADATA1_DG/dfsrdv/datafile/migration.310.736802721
converted datafile=+ORADATA1_DG/dfsrdv/xtransport/migration.313.736808829
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=+ORADATA1_DG/dfsrdv/datafile/migration.311.736802729
converted datafile=+ORADATA1_DG/dfsrdv/xtransport/migration.314.736808831
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 03-DEC-10
RMAN>
and files are here in ASM as:
ASMCMD> pwd
+ORADATA1_DG/DFSRDV/XTRANSPORT
ASMCMD> ls -lart
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y none => MIGRATION.312.736808821
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y none => MIGRATION.313.736808829
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y none => MIGRATION.314.736808831
ASMCMD>
Now, I will recreate the directory object to this dorectroy and then will move to target as:
CREATE OR REPLACE DIRECTORY source_dir_2 AS '+ORADATA1_DG/DFSRDV/XTRANSPORT' ;
GRANT READ,WRITE ON DIRECTORY source_dir_2 TO system;
Output is as:
oracle : goldengate : @DFSRDV : /home/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 3 21:10:28 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE OR REPLACE DIRECTORY source_dir_2 AS '+ORADATA1_DG/DFSRDV/XTRANSPORT' ;
GRANT READ,WRITE ON DIRECTORY source_dir_2 TO system;
Directory created.
SQL>
Grant succeeded.
SQL>
Now lets transfer files as:
File names to transfer are;
MIGRATION.312.736808821
MIGRATION.313.736808829
MIGRATION.314.736808831
On source system do following:
CONNECT system/system@DFSRDV
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.312.736808821' , 'target_dir' , 'migrated_from_source.dbf' , 'DFTGDV' ) ;
END ;
/
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.313.736808829' , 'target_dir' , 'migrated_from_source_1.dbf' , 'DFTGDV' ) ;
END ;
/
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.314.736808831' , 'target_dir' , 'migrated_from_source_2.dbf' , 'DFTGDV' ) ;
END ;
/
Output is as:
SQL> CONNECT system/system@DFSRDV
Connected.
SQL> BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.312.736808821' , 'target_dir' , 'migrated_from_source.dbf' , 'DFTGDV' ) ;
END ; 2 3
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.313.736808829' , 'target_dir' , 'migrated_from_source_1.dbf' , 'DFTGDV' ) ;
END ; 2 3
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_FILE_TRANSFER.PUT_FILE ( 'source_dir_2' , 'MIGRATION.314.736808831' , 'target_dir' , 'migrated_from_source_2.dbf' , 'DFTGDV' ) ;
END ;
/
2 3 4
PL/SQL procedure successfully completed.
SQL> SQL>
Now, lets find our transfered datafiles in target as:
Here they are:
ASMCMD> cd +DEST_ORADATA1_DG/DFTGDV/XTRANSPORT
ASMCMD> ls -lrt
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y FILE_TRANSFER.1552.736809213
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y FILE_TRANSFER.1553.736809313
XTRANSPORT UNPROT COARSE DEC 03 21:00:00 Y FILE_TRANSFER.1554.736809333
ASMCMD>
Now, lets plug these files in and here it is:
Now lets create new directory object in target to place the expdp dumpfile thatwe had extracted from source as:
CREATE OR REPLACE DIRECTORY dpump_dir AS '/ora/fs0000/work/oraexport/DFTGDV' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
Output is as:
connect as sysdba and do the following:
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/ora/fs0000/work/oraexport/DFTGDV' ;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
Grant succeeded.
SQL>
Now, scp the file exp_tablespace_migration.dmp from source to target as:
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$ scp exp_tablespace_migration.dmp rac1:/ora/fs0000/work/oraexport/DFTGDV/.
oracle@rac1's password:
exp_tablespace_migra 100% ********************************************************************************************************** 116 KB 00:00
oracle : goldengate : @DFSRDV : /ora/fs0000/work/dpump_dir
$
So now on source we have that dump:
oracle : rac1 : @DFTGDV1 : /ora/fs0000/work/oraexport/DFTGDV
$ ls -lart
total 128
drwxrwxr-x 51 4001 dba 4096 Dec 3 21:47 ..
drwxr-xr-x 2 oracle oinstall 4096 Dec 3 21:53 .
-rw-r----- 1 oracle oinstall 118784 Dec 3 21:53 exp_tablespace_migration.dmp
oracle : rac1 : @DFTGDV1 : /ora/fs0000/work/oraexport/DFTGDV
$
Now, we need to create the use "MIGRATE" in the taget database to plugin the datafiles. You can also use the remap schema option to do that.
But I will create a user and will not use the remap schema option.
create user migrate identified by migrate default tablespace users temporary tablespace temp;
grant connect,resource,dba to migrate;
I am giving same grants to this user as we have in soruce.
Here is the output:
SQL> create user migrate identified by migrate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to migrate;
Grant succeeded.
SQL>
Now lets import:
impdp system/system DUMPFILE=exp_tablespace_migration.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1552.736809213','+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1553.736809313','+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1554.736809333'
Here is the output:
oracle : rac1 : @DFTGDV1 : /ora/fs0000/work/oraexport/DFTGDV
$ 213','+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1553.736809313','+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1554.736809333' < dumpfile="exp_tablespace_migration.dmp" directory="dpump_dir" transport_datafiles="+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1552.736809213,+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1553.736809313,+DEST_ORADATA1_DG/DFTGDV/XTRANSPORT/FILE_TRANSFER.1554.736809333"> ALTER TABLESPACE MIGRATION READ WRITE;
Tablespace altered.
SQL>
Now lets test if we have transfered the data as:
SQL> conn migrate/migrate
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TRANSFER
PLACES
SQL> select count(*) from TRANSFER;
COUNT(*)
----------
1589
SQL> select count(*) from PLACES;
COUNT(*)
----------
50602
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
oracle : rac1 : @DFTGDV1 : /ora/fs0000/work/oraexport/DFTGDV
$
and its a success !!!!!!!
Now, if you want you can the default tablespace of user migrate from users to migration as:
SQL> conn / as sysdba
Connected.
SQL> alter user migrate default tablespace migration;
User altered.
SQL>
Thats it !!!!!!!!!!!
You have thousands of eyes, and yet You have no eyes. You have thousands of forms, and yet You do not have even one. You have thousands of Lotus Feet, and yet You do not have even one foot. You have no nose, but you have thousands of noses. This Play of Yours entrances me. 2 Amongst all is the Light?You are that Light. By this
Illumination, that Light is radiant within all.
"Upon that cosmic........vibration of the temple drums"
ReplyDeleteVery beautiful verses....where are they from?
All these verses are from Greatest of the Great "Sri Guru Granth Sahib Ji"
ReplyDelete