Tuesday, 31 July 2012

TABLESPACE TRANSPORTATION FROM DATABASE VINAY TO ORCL1


[oracle@localhost ~]$ export ORACLE_SID=vinay
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010

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 from v$database;

NAME
---------
vinay

SQL> select status from v$instance;

STATUS
------------
OPEN


12:25:33 SQL> select username,default_tablespace from dba_users;

--make sure sid will tablespace name and sid.dbf file will be the same name of tablespace then tablespace can be created only...

12:26:02 SQL> create tablespace sid datafile '/u01/app/oracle/oradata/vinay/sid1.dbf' size 10m;
Tablespace created.

12:27:57 SQL> create user tts_user identified by tts default tablespace sid;
User created.

--now we will check this user has been created in the same tablespace which we have just created..

12:28:32 SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      SYSTEM
SYS                            SYSTEM
SYSTEM                         SYSTEM
DBSNMP                         SYSAUX
SYSMAN                         SYSAUX
HR                             USERS
TTS_USER                       SID
OUTLN                          SYSTEM
MDSYS                          SYSAUX
ORDSYS                         SYSAUX
EXFSYS                         SYSAUX

--Now we will check our created tablespace is online or offline of readonly so that....????

12:29:50 SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TBSALERT                       ONLINE
TTS                            READ ONLY
SID                            ONLINE

9 rows selected.


--after checking it online we will make it Read Only Istead of online...

12:30:17 SQL> alter tablespace sid read only;
Tablespace altered.

12:30:33 SQL> exec dbms_tts.transport_set_check('SID');

PL/SQL procedure successfully completed.

--THIS THE PACKAGE WE ARE RUNNING HERE TO verify is it able to transport or not(package)...IF IT IS SUCCESSFULLY COMPLETED THEN WE CAN MOVE TO ANY DATABASE...WE WILL ALSO CHECK THROUGH THIS STATMENT TO CHECK THE VOILATIONS IF ANY IT WILL SHOW OR IT WILL SAY NO ROWS SELECTED MEANS WE CAN GO AHEAD SUSSESSFULLY...


12:31:03 SQL> select * from transport_set_violations;

no rows selected

--it will check is there any object created by sys in current tablespace or not if yes then it wont allow..


12:31:20 SQL> host pwd
/home/oracle

12:31:29 SQL> host exp file=sid.dmp transport_tablespace=Y tablespaces=sid

Export: Release 10.2.0.1.0 - Production on Fri Sep 24 12:32:01 2010

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 SID ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

12:32:18 SQL> alter tablespace sid online;

Tablespace altered.

12:32:54 SQL>




[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/ as sydba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:15 2010

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


SQL*Plus: Release 10.2.0.1.0 - Production

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 [oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010

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

--THIS IS VERY IMPORTANT STEP TO COPY THE sid1.dbf FILE FROM FIRST DATABSE & PAST IT INTO SECOND DATABASE...
--now go to oradata folder n from first database copy the first datafile sid1.dbf and past into second database .

SQL> select name from v$database;

NAME
---------
ORCL1

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create user tts_user identified by tts_user;   --Here we will create same user which has been created in first db.

User created.

SQL> grant connect,resource to tts_user;

Grant succeeded.


SQL>  select tablespace_name from dba_tablespaces;

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

6 rows selected.

--we confirmed it no such table space availabe which we created above in current databse...



--Now we r going to import the datafile & tablespace so make sure here we will give orcl1 which is current database....

SQL> host imp file=sid.dmp transport_tablespace=Y tablespaces=sid datafiles='/u01/app/oracle/oradata/orcl1/sid1.dbf'

Import: Release 10.2.0.1.0 - Production on Fri Sep 24 12:42:03 2010

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 vinay
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
Import terminated successfully without warnings.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
SID                            READ ONLY

7 rows selected.

SQL> alter tablespace sid read write;

Tablespace altered.


SQL> conn tts_user/tts_user
Connected.
-----------------------------one more time practice detials given below date was-5-feb-2011-------------

[oracle@localhost ~]$ export ORACLE_SID=orcl2
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:10:33 2011

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> conn hr/hr
Connected.
SQL> conn as sysdba
Enter user-name: sys
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> select name from v$database;

NAME
---------
ORCL2

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS                          SYSAUX
ORDSYS                         SYSAUX
EXFSYS                         SYSAUX
DMSYS                          SYSAUX
DBSNMP                         SYSAUX
SCOTT                          USERS
WMSYS                          SYSAUX
TSMSYS                         USERS
BI                             USERS
PM                             USERS
MDDATA                         USERS

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
IX                             USERS
CTXSYS                         SYSAUX
ANONYMOUS                      SYSAUX
SH                             USERS
OUTLN                          SYSTEM
DIP                            USERS
OE                             USERS
HR                             USERS
SYSMAN                         SYSAUX
XDB                            SYSAUX
ORDPLUGINS                     SYSAUX

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      SYSTEM
SI_INFORMTN_SCHEMA             SYSAUX
OLAPSYS                        SYSAUX
SYS                            SYSTEM
SYSTEM                         SYSTEM

27 rows selected.

SQL> Create TableSpace Saba datafile '/u01/app/oracle/oradata/orcl2/saba1.dbf' size 10m;

Tablespace created.

SQL> create user sabau identified by salim default tablespace saba;

User created.

SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS                          SYSAUX
ORDSYS                         SYSAUX
EXFSYS                         SYSAUX
DMSYS                          SYSAUX
DBSNMP                         SYSAUX
SCOTT                          USERS
WMSYS                          SYSAUX
TSMSYS                         USERS
SABAU                          SABA
BI                             USERS
PM                             USERS

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDDATA                         USERS
IX                             USERS
CTXSYS                         SYSAUX
ANONYMOUS                      SYSAUX
SH                             USERS
OUTLN                          SYSTEM
DIP                            USERS
OE                             USERS
HR                             USERS
SYSMAN                         SYSAUX
XDB                            SYSAUX

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORDPLUGINS                     SYSAUX
MGMT_VIEW                      SYSTEM
SI_INFORMTN_SCHEMA             SYSAUX
OLAPSYS                        SYSAUX
SYS                            SYSTEM
SYSTEM                         SYSTEM

28 rows selected.

SQL> host emctl start dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://localhost.localdomain:5500/em/console/aboutApplication
 - An instance of Oracle Enterprise Manager 10g Database Control is already running.

SQL> alter tablespace saba read only;

Tablespace altered.

SQL> exe dbms_tts.transport_set_check('saba');
SP2-0734: unknown command beginning "exe dbms_t..." - rest of line ignored.
SQL> exec dbms_tts.transport_set_check('saba');

PL/SQL procedure successfully completed.

SQL> select *from transport_set_violations;

no rows selected

SQL> host pwd
/home/oracle

SQL> host exp file=saba.dmp transport_tabaspace=Y tablespaces=saba;
LRM-00101: unknown parameter name 'transport_tabaspace'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

SQL> host exp file=saba.dmp transport_tablespace=Y tablespaces=saba;

Export: Release 10.2.0.1.0 - Production on Sat Feb 5 19:29:08 2011

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 SABA ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace saba online;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:32:58 2011

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 from v$database;

NAME
---------
ORCL1

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create user sabau identified by salim;

User created.

SQL> Grant connect,resource to sabau;

Grant succeeded.

SQL> select tablespace_name from dba_tablespaces;

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

7 rows selected.

SQL> host imp file=saba.dmp transport_tablespace=Y tablespaces=saba datafiles='/u01/app/oracle/oradata/orcl1/saba1.dbf';

Import: Release 10.2.0.1.0 - Production on Sat Feb 5 19:52:28 2011

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 vinay
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
Import terminated successfully without warnings.

SQL> select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
SAMDBA                         ONLINE
SABA                           READ ONLY

8 rows selected.

SQL> alter tablespace saba read write;

Tablespace altered.

SQL> conn sabau/salim
Connected.
SQL> create table saba(R Number);

Table created.

SQL> Insert Into saba values(1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
---Run Programm successufully.....

No comments: