So, let`s start. Selected SID name is FCDBT.
Source
-bash-4.2$ uname -a
AIX localhost 1 6 00F697214C00
Target
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ uname -a
Linux fc-db-tst1 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
1. create same name DB on target.
ex: dbca
Note: nls_language should be same as source
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.3.0
20 rows selected.
AL32UTF8
Source:
SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id; 2 3
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
SQL>
Target:
SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id; 2 3
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
13 Linux x86 64-bit Little
SQL>
2. select users and identify tablespaces to be transported
select username, account_status, default_tablespace from dba_users
--where .....
order by created;
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE
------------------------------ -------------------------------- ------------------------------
FCDBAPPDEV121 OPEN FCATDEV121
FCDBADMINDEV121 OPEN FCATDEV121
TMPDBA OPEN SYSTEM
FCDBAPP_DEV21 OPEN FCAT_DEV21
FCDBADMIN_DEV21 OPEN FCAT_DEV21
FCDBB001_DEV21 OPEN FCAT_DEV21
SQL> SELECT tablespace_name, segment_type, COUNT(*),
SUM (bytes) / 1024 / 1024 mb
FROM dba_segments
WHERE owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;
TABLESPACE_NAME SEGMENT_TYPE COUNT(*) MB
------------------------------ ------------------ ---------- ----------
FCATDEV121 TABLE 209 57.375
FCATDEV121 LOBSEGMENT 24 44.375
FCATDEV121 LOBINDEX 24 1.5
FCATDEV121 INDEX 253 37.4375
FCAT_DEV21 TABLE 230 80.5625
FCAT_DEV21 LOBSEGMENT 33 131.375
FCAT_DEV21 LOBINDEX 33 2.4375
FCAT_DEV21 INDEX 266 40.25
TBS_OFFLINE_DEV21 TABLE 9 16.75
TBS_OFFLINE_DEV21 LOBSEGMENT 12 88.6875
TBS_OFFLINE_DEV21 LOBINDEX 12 .75
11 rows selected.
3.create OS dir (in this case I will create same dircetories on both host)
-bash-4.2$ pwd
/home/oracle
-bash-4.2$ mkdir FCDBT_EXPTPS
-bash-4.2$ cd FCDBT_EXPTPS
Ran the following on the source database to verify there were no self-containment problems:
SQL> BEGIN
SYS.dbms_tts.transport_set_check('FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21', incl_constraints=>TRUE, full_check=>FALSE);
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SYS.transport_set_violations;
no rows selected
SQL> SELECT B.data_type, COUNT(*)
FROM dba_tables A, dba_tab_columns B
WHERE A.owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')
AND B.owner = A.owner
AND B.table_name = A.table_name
GR 2 3 4 5 6 OUP BY B.data_type
ORDER BY B.data_type; 7
DATA_TYPE COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
BLOB 135
CHAR 1240
CLOB 4
DATE 774
FLOAT 2
LONG 8
LONG RAW 10
NUMBER 1505
TIMESTAMP(1) 19
TIMESTAMP(6) 34
VARCHAR2 6691
DATA_TYPE COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
XMLTYPE 4
12 rows selected.
SQL>
SQL> SELECT B.owner, B.table_name
FROM dba_xml_tables A, all_all_tables B
WHERE B.owner = A.owner
AND B.table_name = A.table_name
AND B.tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');SQL> 2 3 4 5
no rows selected
SQL>
SQL> SELECT owner, COUNT(*)
FROM dba_segments
WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner; 2 3 4
OWNER COUNT(*)
------------------------------ ----------
FCDBADMIN_DEV21 585
FCDBADMINDEV121 510
FCDBB001_DEV21 10
SQL>
Next we verify that this schema did not yet exist on the target database:
[oracle@fc-db-tst1 admin]$ export ORACLE_SID=FCDBT
[oracle@fc-db-tst1 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 17 16:29:19 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select name from v$database;
NAME
---------
FCDBT
SQL> SELECT username
FROM dba_users
WHERE username in ( 'FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21');
no rows selected
SQL>
Now we have to create missing schemas on the target database and grant necessary system privileges:
SQL> CREATE USER FCDBAPPDEV121 IDENTIFIED BY FCDBAPPDEV121;
SQL> CREATE USER FCDBADMINDEV121 IDENTIFIED BY FCDBADMINDEV121;
SQL> CREATE USER TMPDBA IDENTIFIED BY TMPDBA;
SQL> CREATE USER FCDBAPP_DEV21 IDENTIFIED BY FCDBAPP_DEV21;
SQL> CREATE USER FCDBADMIN_DEV21 IDENTIFIED BY FCDBADMIN_DEV21;
SQL> CREATE USER FCDBB001_DEV21 IDENTIFIED BY FCDBB001_DEV21;
SQL> GRANT connect, resource, create library TO FCDBAPPDEV121;
SQL> GRANT connect, resource, create library TO FCDBADMINDEV121;
SQL> GRANT connect, resource, create library TO TMPDBA;
SQL> GRANT connect, resource, create library TO FCDBAPP_DEV21;
SQL> GRANT connect, resource, create library TO FCDBADMIN_DEV21;
SQL> GRANT connect, resource, create library TO FCDBB001_DEV21;
Check exist tablespaces on TARGET
SQL> SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');
2 3
no rows selected
ON SOURCE db make tablespace READ ONLY
Make Tablespaces Read-only in Source Database
SQL> select name from v$database;
NAME
---------
FCDBT
SQL>
SQL> ALTER TABLESPACE FCATDEV121 READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE FCAT_DEV21 READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ ONLY;
Tablespace altered.
SQL>
Extract Metadata from Source Database
exp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y tablespaces=FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21
Copy Files to Target Server and Convert if Necessary
-bash-4.2$ echo $ORACLE_SID
FCDBT
-bash-4.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 17 16:46:04 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: FCDBT (DBID=1987414639)
RMAN> CONVERT TABLESPACE FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21
TO PLATFORM 'Linux x86 64-bit'
FORMAT='/home/oracle/FCDBT_EXPTPS/%U';2> 3>
Starting conversion at source at 17-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcatdev1_94vkgg0m_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00011 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcat_dev_94vkl66p_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00014 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_tbs_offl_94vkn2n1_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 17-DEC-13
RMAN>
now copy
-bash-4.2$ scp -r /home/oracle/FCDBT_EXPTPS/* 192.168.10.168:/home/oracle/FCDBT_EXPTPS
oracle@192.168.10.168's password:
FCDBT_tabsp.dmp 100% 7852KB 7.7MB/s 00:00
data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8 100% 500MB 45.5MB/s 00:11
data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf 100% 500MB 45.5MB/s 00:11
data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm 100% 160MB 53.3MB/s 00:03
-bash-4.2$
change back read write source db`s tablespace
ALTER TABLESPACE FCATDEV121 READ WRITE;
ALTER TABLESPACE FCAT_DEV21 READ WRITE;
ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;
Import Metadata into Target Database
imp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y \
datafiles=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm
Checking on target database
SELECT owner, COUNT(*)
FROM dba_segments
WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner;
SELECT B.data_type, COUNT(*)
FROM dba_tables A, dba_tab_columns B
WHERE A.owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')
AND B.owner = A.owner
AND B.table_name = A.table_name
GROUP BY B.data_type
ORDER BY B.data_type;
SELECT tablespace_name, segment_type, COUNT(*),
SUM (bytes) / 1024 / 1024 mb
FROM dba_segments
WHERE owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
FCATDEV121 READ ONLY
FCAT_DEV21 READ ONLY
TBS_OFFLINE_DEV21 READ ONLY
8 rows selected.
SQL>
Alter status to Read Write
SQL> ALTER TABLESPACE FCATDEV121 READ WRITE;
Tablespace altered.
SQL> ALTER TABLESPACE FCAT_DEV21 READ WRITE;
Tablespace altered.
SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
FCATDEV121 ONLINE
FCAT_DEV21 ONLINE
TBS_OFFLINE_DEV21 ONLINE
8 rows selected.
SQL>
That is all. :)
No comments:
Post a Comment