Its been a long time that i had abandon my blog, in this occasion i would like to share my experience as oracle consultan.. okay just straight to the point
Author
Febry Ginanjar Koestiwandhana
Time to Complete
Approximately 1 hours
Overview
Transportable tablespaces is a feature of the Oracle database, introduced with Oracle 8i, that allows DBAs to copy or move tablespaces between databases, in this tutorial the source and target database are the same character set
Action
WHAT TO DO ON SOURCE SERVER
1. Create Table space, User and Table on Source Server
--CREATE TABLESPACE
CREATE TABLESPACE TTS DATAFILE
'/data/TTS/TTS01.dbf' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE 1024M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--CREATE USER
CREATE USER TTSUSER
IDENTIFIED BY ttsuser
DEFAULT TABLESPACE TTS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for TTSUSER
GRANT CONNECT TO TTSUSER;
GRANT RESOURCE TO TTSUSER;
GRANT SELECT ON SYS.DBA_SOURCE TO TTSUSER;
ALTER USER TTSUSER DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO TTSUSER;
--CREATE TABLE
create table source as select * from sys.dba_source where rownum < 50;
2. Execute job transport check to check if there is any violation on the tablespace
--EXECUTE JOB
SQL> execute dbms_tts.transport_set_check('TTS', true, true);
PL/SQL procedure successfully completed.
--QUERY TO CHECK IF THERE’S ANY VIOLATION
SQL> select * from transport_set_violations;
no rows selected
3. Set the source tablespace read only
--SET TABLESPACE READ ONLY
SQL> alter tablespace TTS read only;
Tablespace altered.
4. Export the TableSpace Metadata
--EXPORT THE METADATA
bash-3.00$ expdp \'/ as sysdba\' dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_tablespaces=TTS
Export: Release 11.1.0.6.0 - 64bit Production on Friday, 09 March, 2012 15:45:45
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": '/******** AS SYSDBA' dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_tablespaces=TTS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/data/TTS/TTS.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/data/TTS/TTS01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:46:18
5. Copying datafile and Dumpfile to target server
--COPYING DATAFILE & DUMPFILE
>scp TTS.dmp oracle@10.204.46.82:/backup/dumpfile/
The authenticity of host '10.204.46.82 (10.204.46.82)' can't be established.
RSA key fingerprint is d5:a7:f2:08:de:d4:ca:cf:08:9c:7e:24:c5:e7:e1:d0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.204.46.82' (RSA) to the list of known hosts.
oracle@10.204.46.82's password:
TTS.dmp 100% |*********************************************************************************| 86016 00:00
>pwd
/oradata1/dumpfile
>cd ..
>ls
brcdbprd dumpfile eisbdprd lost+found oracle TTS
>cd TTS/
>ls
TTS01.dbf
>du -sh .
2.0G .
>scp TTS01.dbf oracle@10.204.46.82:/backup/dumpfile/
oracle@10.204.46.82's password:
TTS001.dbf 100% |*********************************************************************************| 2048 MB 00:53
WHAT TO DO ON TARGET SERVER
1. Create User on Target Server
--CREATE USER
CREATE USER TTSUSER
IDENTIFIED BY ttsuser
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for TTSUSER
GRANT EXP_FULL_DATABASE TO TTSUSER;
GRANT IMP_FULL_DATABASE TO TTSUSER;
ALTER USER TTSUSER DEFAULT ROLE ALL;
-- 1 System Privileges for TTSUSER;
GRANT UNLIMITED TABLESPACE TO TTSUSER;
2. Copying datafile to ASM and Import the Tablespace set
--COPYING DATAFILE TO ASM DISK GROUP
ASMCMD> cp /backup/dumpfile/TTS001.dbf +DATA/ORADB/TES
--IMPORT TABLESPACE
The firstime I do the import task I got error message below, The error message ORA-29339 means, tablespace block size does not match configured block size
Solution on metalink note ID 806284.1
The BLOCKSIZE size by default is the standard block size defined by DB_BLOCK_SIZE of the parameter set at the time of database creation. The standard block size defined at the database creation has a default buffer cache initialized at instance startup for all blocks that need to be read from datafiles of the standard blocksize tablespaces.
The tablespaces with non-standard blocksizes need specific blocksize buffer cache and can be initialized by parameters such as DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE or DB_32K_CACHE_SIZE depending on the tablespace block size at startup or by ALTER SYSTEM command.
First allocate a buffer cache with the non-standard block size of 32m for a 32k block size:
connect / as sysdba
alter system set db_32k_cache_size=32m scope=memory;
Or ( For RAC ):
alter system set db_32k_cache_size=32m scope=both sid='oradb1';
alter system set db_32k_cache_size=32m scope=both sid='oradb2';
-bash-3.2$ impdp \'/ as sysdba\' dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_datafiles='+DATA/ASM/DATAFILE/TTS01.dbf.368.777485011';
Import: Release 11.2.0.3.0 - Production on Fri Mar 9 16:05:50 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_datafiles=+DATA/ASM/DATAFILE/TTS01.dbf.368.777485011
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29339: tablespace block size 32768 does not match configured block sizes
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:05:52
And Finally the import task successfully completed as shown below
-bash-3.2$ impdp \'/ as sysdba\' dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_datafiles='+DATA/ASM/DATAFILE/TTS01.dbf.368.777485011';
Import: Release 11.2.0.3.0 - Production on Fri Mar 9 16:22:38 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=TTS.dmp logfile=TTS.log directory=DATAPUMP transport_datafiles=+DATA/ASM/DATAFILE/TTS01.dbf.368.777485011
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:22:40
3. Make the Tablespace in to rad write mode & try to query
--ALTER THE TABLESPACE
SQL> alter tablespace tts read write;
Tablespace altered.
--TES TO QUERY
-bash-3.2$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 9 16:58:28 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: ttsuser
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> desc source;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> select name from dba_source where rownum < 10;
NAME
------------------------------
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
AGGXMLIMP
9 rows selected.
okay homely this information helpful for you
No comments:
Post a Comment