TABLESPACE_TRANSPORTATION

TABLESPACE TRANSPORTATION

from Linux server1 to Linux Server2

Server 1 : 192.168.188.129
Server 2 : 192.168.188.128
                                                                   
                                                        
login as: root
root@192.168.188.129's password:
[root@localhost ~]# su - oracle

[oracle@localhost ~]$ cat /etc/oratab
rcat:/u01/app/oracle/product/10.2.0/db_1:N

[oracle@localhost ~]$ ps -ef | grep pmon
oracle    4745     1  0 11:31 ?        00:00:00 asm_pmon_+ASM
oracle    8046     1  0 12:42 ?        00:00:00 ora_pmon_rcat
oracle   11244 11076  0 14:17 pts/5    00:00:00 grep pmon

[oracle@localhost ~]$ export ORACLE_SID=rcat
[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:17:42 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
RCAT      READ WRITE

SQL> select name from v$datafile;

NAME
-----------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf

SQL> create tablespace newtbs datafile '/u01/app/oracle/oradat/rcat/new01.dbf' size 20M;

Tablespace created.

SQL> select name from v$datafile;

NAME
-----------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf
/u01/app/oracle/oradata/rcat/new01.dbf    <--------------- this is the new datafile added

6 rows selected.

SQL> create user newusr identified by oracle default tablespace newtbs;

User created.

SQL> grant connect , resource  to newusr;

Grant succeeded.

SQL> conn newusr/oracle
Connected.

SQL> create table tbnew (eno number, ename varchar2(20));

Table created.

SQL> insert into tbnew values(1,'check');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> select username,default_tablespace from dba_users where username='NEWUSR';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
NEWUSR                         NEWTBS

SQL> select tablespace_name , status from dba_tablespaces where tablespace_name='NEWTBS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
NEWTBS                         ONLINE

SQL> alter tablespace newtbs read only;

Tablespace altered.

SQL>
SQL>
SQL> exec dbms_tts.transport_set_check('NEWTBS');

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> host

[oracle@localhost ~]$ exp file=newtbs.sid transport_tablespace=Y tablespaces=newtbs

Export: Release 10.2.0.1.0 - Production on Sun Aug 12 14:27:36 2012

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace NEWTBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TBNEW
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@localhost ~]$
[oracle@localhost ~]$

[oracle@localhost ~]$ ls -lrt *.sid
-rw-r--r-- 1 oracle oinstall 16384 Aug 12 14:27 newtbs.sid

COPY THE EXPORT METADATA TO SERVER 2

[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ scp newtbs.sid oracle@192.168.188.128:/u01/app/oracle/oradata/orcl/

The authenticity of host '192.168.188.128 (192.168.188.128)' can't be established.
RSA key fingerprint is df:a9:ca:4d:d7:f6:ee:47:33:fa:77:8f:17:40:7e:0a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.188.128' (RSA) to the list of known hosts.
oracle@192.168.188.128's password:
newtbs.sid                                    100%   16KB  16.0KB/s   00:00

COPY THE DATAFILE TO SERVER 2

[oracle@localhost ~]$ scp /u01/app/oracle/oradata/rcat/new01.dbf oracle@192.168.188.128:/u01/app/oracle/oradata/orcl/
oracle@192.168.188.128's password:
new01.dbf                                     100%   20MB  10.0MB/s   00:02
[oracle@localhost ~]$

__________________________________________________________________________________________________________________________________

ON SERVER 2
__________________________________________________________________________________________________________________________________



[oracle@localhost etc]$ ps -ef | grep pmon
oracle   11832     1  0 14:16 ?        00:00:00 ora_pmon_orcl
oracle   11859 11688  0 14:16 pts/1    00:00:00 grep pmon


[oracle@localhost etc]$ export ORACLE_SID=orcl
[oracle@localhost etc]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:18:13 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
ORCL      READ WRITE

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> show user
USER is "SYS"

SQL> create user newusr identified by oracle;

User created.

SQL>  grant connect , resource  to newusr;

Grant succeeded.

SQL> host

[oracle@localhost etc]$ cd $ORACLE_BASE/oradata/orcl
[oracle@localhost orcl]$ ls -lrt *.sid
total 1088968
-rw-r--r-- 1 oracle oinstall     16384 Aug 12 14:31 newtbs.sid  <--------------------- this is copied from server 1

[oracle@localhost etc]$ cd $ORACLE_BASE/oradata/orcl
[oracle@localhost orcl]$ ls -lrt *.dbf

-rwxrwxrwx 1 oracle oinstall  20979712 Aug 11 10:33 temp01.dbf
-rwxrwxrwx 1 oracle oinstall   5251072 Aug 12 14:17 users01.dbf
-rwxrwxrwx 1 oracle oinstall 104865792 Aug 12 14:17 example01.dbf
-rwxrwxrwx 1 oracle oinstall 503324672 Aug 12 14:31 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug 12 14:31 new01.dbf     <--------------------- this is copied from server 1
-rwxrwxrwx 1 oracle oinstall  31465472 Aug 12 14:32 undotbs01.dbf
-rwxrwxrwx 1 oracle oinstall 251666432 Aug 12 14:33 sysaux01.dbf

[oracle@localhost orcl]$ imp file=newtbs.sid transport_tablespace=Y tablespaces=newtbs datafiles='/u01/app/oracle/oradata/orcl/new01.dbf'

Import: Release 10.2.0.1.0 - Production on Sun Aug 12 14:34:44 2012

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing NEWUSR's objects into NEWUSR
. . importing table                        "TBNEW"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@localhost orcl]$
[oracle@localhost orcl]$
[oracle@localhost orcl]$ exit
exit

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
NEWTBS   <--------------------- this tablespace is transported from the server 1 to server 2

7 rows selected.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/new01.dbf

6 rows selected.

SQL> alter tablespace newtbs read write;

Tablespace altered.

SQL> conn newusr/oracle
Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TBNEW                          TABLE

SQL> select * from tbnew;

       ENO ENAME
---------- --------------------
         1 check

SQL>