One .1  BLOG Document structure chart

wpsF6D5.tmp 

 

 

One .2   Preface

 

One .2.1   Guidance and notes

Technology enthusiasts , After reading this article , You can master the following skills , You can also learn something else you don't know ,~O(∩_∩)O~:

①  Implementation of transfer table space on heterogeneous platform

②  Transfer table space is based on table space read only and rman2 Ways of planting

③  Platform byte order 、 The concept of self inclusion

④ expdp/impdp Application

 

  Tips:

       ① If the article code format is wrong , It is recommended to use Sogou or 360 browser , You can also download pdf Format to view ,pdf Document download address :http://yunpan.cn/cdEQedhCs2kFz ( Extraction code :ed9b) 

       ②  This article BLOG I use gray background and pink font to show the output part of the command that needs special attention , For example, in the following example ,thread 1 The maximum archive log number for is 33,thread 2 The maximum archive log number for is 43 It's something that needs special attention ; Commands are usually marked with yellow background and red font ; Comments on the code or the output part of the code are generally in blue font .

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZFXDESKDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXDESKDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

 

 

 

 

If there are any mistakes or imperfections in this article, please correct them ,ITPUB Message or QQ All can , Your criticism is the biggest motivation of my writing .

 

 

One .2.2   Links to related reference articles

Other heterogeneous platform migration article reference :

【 recommend 】 oracle  An example of transport table space for heterogeneous platform migration  http://blog.itpub.net/26736162/viewspace-1391913/

【 recommend 】 oracle  An example of transfer table space  http://blog.itpub.net/26736162/viewspace-1375260/

【 recommend 】 utilize rman To achieve linux Copy the platform database to windows Platform database http://blog.itpub.net/26736162/viewspace-1352436/

【 recommend 】 Directly copy data files to achieve linux Copy the platform database to windows Platform database http://blog.itpub.net/26736162/viewspace-1352243/

【TTS】 Transfer table space Linux asm -> AIX asm  http://blog.itpub.net/26736162/viewspace-1987949/

 

 

One .3   Related knowledge points literacy

The feature of transportable table space is mainly used for library to library table space replication , The table space to be transferred must be placed in read-only Pattern . If the production library does not allow the table space to be set to read-only mode , No problem , There are ways , adopt RMAN Backup can also create a set of transportable table spaces . To use the features of transportable table spaces ,oracle At least 8i Enterprise version or higher . If it is the same operating system platform import each other , be 8i And above can support , But if it's a different operating system platform , Database version at least 10g. The transferred table space can be dictionary management , It can also be local management . And since the oracle9i Start , Transferred table space block size Can be linked to the target database block size Different .

 

Transportable tablespace ( There's also a collection ) The biggest advantage is the speed ratio export/import or unload/load Much faster . Because transportable table space mainly copies data files to the target path , And then use export/import or Data Pump export/import And so on / Import metadata of table space object to new database .

About transportable table spaces , There's also a collection (Transportable Tablespace Sets) The creation of , They all mentioned a very important point , That is, the transferred table space must be set to  read-only. And in the actual operation , For some production databases , Set the table space to  read-only  It's a very complicated thing, not even allowed at all , With  RMAN  Of  Transportable Tablespace, All this has been avoided .RMAN  Create a set of transportable table spaces from backup , It doesn't need to access active data files , Accordingly, there is no need to set the table space to  read-only. therefore , Improved database availability , Especially for very large table spaces , Because the transferred table space can still read and write during this period , And set the table space to  read-only  Patterns can take a long time ,

Use  RMAN  Create a set of transportable table spaces , Allows you to specify a target recovery time point or  SCN, In this way, the data can be transmitted more flexibly , You don't have to copy existing table spaces completely , As long as it's in the backup , You can selectively recover data . for example , Your backup strategy is to keep it for a week , The data in the transportable table space you want to create is the data as of the last day at the end of this month , Then you can transfer any time in the first week of next month, regardless of whether there will be write operations in the production library during this period .

 

 

One .3.1   matters needing attention

 

 

 

*  Be careful :

 

① source and target database It's better to have consistent database versions , Otherwise, it will be because of db time zone Inconsistency results in the following error , But if source Greater than or equal to target It's ok if you want to , Downward compatible

ORA-39002: invalid operation

ORA-39322: Cannot use transportabletablespace with timestamp with timezone columns and different timezone version.

 

② source and target The character set at the end must be consistent , For example, an error is reported in the following case :

source by ZHS16GBK,target by AL32UTF8

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29345: cannot plug a tablespace into a database using an incompatible character set

 

Tartget db char set AL32UTF8 is not a superset of ZHS16GBK.

Failed to plug in a tablespace due to incompatible

  database character set"AL32UTF8" and

  transportable set database character set "ZHS16GBK"

 

  ③ source and target database Of compatible The parameters should be consistent , but source If less than or equal to target It's ok if it's on the end , for example source by 11.2.0.4.0,target by 11.2.0.0.0 No way. ,impdp It's a mistake :

ORA-39123: Data Pump transportable tablespace job aborted

ORA-00721: changes by release 11.2.0.4.0 cannot be used by release 11.2.0.0.0

 

 

 

 

One .4   Experimental part

 

One .4.1   Introduction to the experimental environment

project

source db

target db

db  type

Single instance

Single instance

db version

11.2.0.3

11.2.0.4

db  Storage

ASM

ASM

ORACLE_SID

orclasm

ora2lhr

db_name

orclasm

ora2lhr

host IP Address :

192.168.59.30

22.188.194.66

OS Version and kernel edition

RHEL6.5 64 position ,2.6.32-504.16.2.el6.x86_64

AIX 64 position  7.1.0.0

OS hostname

rhel6_lhr

ZFXDESKDB2

platform_name

Linux x86 64-bit

AIX-Based Systems (64-bit)

db time zone

14

14

compatible

11.2.0.0.0

11.2.0.4.0

Character set

ZHS16GBK

ZHS16GBK

Archiving mode

Archive Mode

Archive Mode

 

One .4.2   The goal of the experiment

 

To implement the application table space that will be customized app1tbs,app2tbs,idxtbs From the source platform to the target platform , And in the actual work process , Need to put AIX The database on is migrated to Linux, Or will Linux The database on is migrated to AIX On , except exp/imp and expdp/impdp Outside , The most common is to transfer table space , If the whole library is migrated , What we need to do is to migrate the data of business users and business table space ,Undo、temp、system You don't have to wait , The whole process is similar to that of this document , What we need to pay attention to is the number of business objects 、 size 、 State, etc .

wpsF6E5.tmp 

One .4.3   Experimental process

 

 

-------------------------------------------------------------------------------------------------------------

 

 

 

 

 

 

 

One .5  source End environment preparation

wpsF6E6.tmp 

 

One .5.1   Create... On the source library 3 A table space applied by a user

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sunday 1 month 31 23:34:27 2016

 

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

 

 

Connect to :

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

23:34:27 SQL>  create tablespace app1tbs datafile '+DATA' size 10m;

 

Table space created .

 

Elapsed time :  00: 00: 07.60

23:34:42 SQL> create tablespace app2tbs datafile '+DATA' size 10m;

 

Table space created .

 

Elapsed time :  00: 00: 27.25

23:35:53 SQL> create tablespace idxtbs datafile '+DATA' size 10m;

 

Table space created .

 

Elapsed time :  00: 00: 09.45

23:36:09 SQL> set line 9999 pagesize 9999

23:36:12 SQL> SELECT   a.NAME,  b.NAME FROM vtablespacea,vtablespacea,vdatafile b WHERE a.TS#=b.TS#  ;

 

NAME                           NAME

-----------------------       -------------------------------------------------------------------------------

SYSTEM                         +DATA/orclasm/datafile/system.256.850260145

SYSAUX                         +DATA/orclasm/datafile/sysaux.257.850260145

UNDOTBS1                       +DATA/orclasm/datafile/undotbs1.258.851526539

USERS                          +DATA/orclasm/datafile/users.259.850260147

EXAMPLE                        +DATA/orclasm/datafile/example.265.850260295

APP1TBS                        +DATA/orclasm/datafile/app1tbs.268.902619275

APP2TBS                        +DATA/orclasm/datafile/app2tbs.280.902619327

TS_LHR                         +DATA/orclasm/datafile/ts_lhr.269.852632495

ENCRYPTED_TS                   +DATA/orclasm/datafile/encrypted_ts.272.854650889

GOLDENGATE                     +DATA/orclasm/datafile/goldengate.273.862829891

IDXTBS                         +DATA/orclasm/datafile/idxtbs.281.902619361

TS_LHR                         +DATA/orclasm/datafile/ts_lhr.284.869738273

USERS                          +FRA/orclasm/datafile/users.449.880121199

SYSTEM                         +FRA/orclasm/datafile/system.349.880121287

 

Have chosen 14 That's ok .

 

Elapsed time :  00: 00: 00.80

23:36:21 SQL>

 

 

One .5.2   Create tables and indexes in the corresponding table space

23:36:21 SQL> create user user_app1 identified by user_app1 default tablespace app1tbs;

 

The user has created .

 

Elapsed time :  00: 00: 00.14

23:40:13 SQL> create user user_app2 identified by user_app2 default tablespace app1tbs;

 

The user has created .

 

Elapsed time :  00: 00: 00.35

23:43:51 SQL> create user user_app2 identified by user_app2 default tablespace app2tbs;

 

The user has created .

 

Elapsed time :  00: 00: 02.72

23:43:56 SQL>  grant connect,resource to user_app1;

 

Authorized success .

 

Elapsed time :  00: 00: 00.06

23:44:50 SQL> grant connect,resource to user_app2;

 

Authorized success .

 

Elapsed time :  00: 00: 00.00

23:44:52 SQL> create table user_app1.app1_tab tablespace app1tbs as select * from scott.emp;

 

Table created .

 

Elapsed time :  00: 00: 01.02

23:45:09 SQL>  create table user_app2.app2_tab tablespace app2tbs as select * from scott.dept;

 

Table created .

 

Elapsed time :  00: 00: 00.23

23:45:27 SQL> create index user_app1.idx_emp_ename on user_app1.app1_tab(ename) tablespace idxtbs;

 

Index created .

 

Elapsed time :  00: 00: 00.25

23:45:51 SQL> create index user_app2.idx_dept_dname on user_app2.app2_tab(dname) tablespace idxtbs;

 

Index created .

 

Elapsed time :  00: 00: 00.01

23:46:13 SQL>

 

One .6   Judge the platform support and determine the byte order

If you transfer table space sets to different platforms , Make sure that this cross platform table space is supported for both source and target platforms , Also determine the byte order for each platform , If the platform has the same byte order , There is no need to transform , Otherwise, you have to do a table space set conversion , On the source side or the target side .

One .6.1   Query on the source platform

23:46:13 SQL> col platform_name for a40

23:48:55 SQL> select d.platform_name,tp.endian_format from vtransportableplatformtp,vtransportableplatformtp,vdatabase d where tp.platform_name=d.platform_name;

 

PLATFORM_NAME                            ENDIAN_FORMAT

---------------------------------------- --------------

Linux x86 64-bit                         Little

 

Elapsed time :  00: 00: 00.19

23:49:13 SQL>

 

Conclusion : The current system platform supports cross platform table space transmission ( Because the above query has records returned )

 

One .6.2   Query on the target platform

 

[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr

[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 1 13:47:14 2016

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@ora2lhr> col platform_name for a40

SYS@ora2lhr> select d.platform_name,tp.endian_format from vtransportableplatformtp,vtransportableplatformtp,vdatabase d where tp.platform_name=d.platform_name;

 

PLATFORM_NAME                            ENDIAN_FORMAT

---------------------------------------- ----------------------------

AIX-Based Systems (64-bit)               Big

 

 

Conclusion : Current AIX The platform supports cross platform table space transmission

Source platform and target platform Endian_format  Different ,source intention Little,target intention Big, So you need to do table space set conversion , As mentioned above, conversion can be carried out at both the source side and the target side , Here we choose to transform on the target side .

 

One .7   Select the set of self-contained table spaces

One .7.1   Inspection

Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.

First try to transmit app1tbs and idxtbs this 2 Table spaces :

SQL> execute dbms_tts.transport_set_check('app1tbs,idxtbs',true);

 

PL/SQL procedure successfully completed.

 

 

One .7.2   Check the results

SQL> col violations for a70

 

23:50:53 SQL> select * from transport_set_violations;

 

VIOLATIONS

-------------------------------------------------------------------------------------------------------------------------------------------

ORA-39907: Indexes USER_APP2.IDX_DEPT_DNAME ( In tablespace IDXTBS in ) Point to table USER_APP2.APP2_TAB ( In tablespace APP2TBS in ).

 

Elapsed time :  00: 00: 00.18

23:51:14 SQL>

 

Conclusion : stay idxtbs Tablespace IDX_DEPT_DNAME The index points to SYS.APP2_TAB surface , So here we choose app1tabs,app2tabs,idxtbs Check again as a new set of table spaces

 

23:51:14 SQL> execute dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);

 

PL/SQL Process completed successfully .

 

Elapsed time :  00: 00: 07.24

23:52:14 SQL> select * from transport_set_violations;

 

No line selected

 

Elapsed time :  00: 00: 00.00

23:52:54 SQL>

Conclusion : At this point, the set of table spaces no longer violates the condition of self inclusion , Can be determined as a set of transportable table spaces

 

One .8   Generate a set of transportable table spaces

One .8.1  rman Full backup

Perform a backup of the source database , Be sure to add include current controlfile Otherwise execution transport tablespace The following error message appears during the command :

Automatic instance removed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of tranport tablespace command at 03/26/2015 20:24:22

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

 

 

[oracle@rhel6_lhr orclasm]$ rman target /

 

Recovery Manager : Release 11.2.0.3.0 - Production on Monday 2 month 1 22:05:43 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to target database : ORCLASM (DBID=3424884828)

RMAN> list backup;

 

Description does not match any backup in the archive

 

RMAN> backup as backupset database  format  '/u05/oracle/oracle_bk/orclasm/full_%n_%T_%t_%s_%p.bak' include current controlfile plus archivelog delete input;

 

 

start-up backup On 2016-02-01 22:11:24

The current log is archived

Use channel ORA_DISK_1

Use channel ORA_DISK_2

passageway ORA_DISK_1: Starting archive log backup set

passageway ORA_DISK_1: Specifying archive logs within backup set

Enter the archive log thread =1 Sequence =2400 RECID=2409 STAMP=898694914

Enter the archive log thread =1 Sequence =2401 RECID=2410 STAMP=898702456

Enter the archive log thread =1 Sequence =2402 RECID=2411 STAMP=898703657

Enter the archive log thread =1 Sequence =2403 RECID=2412 STAMP=898703964

Enter the archive log thread =1 Sequence =2404 RECID=2413 STAMP=898705171

Enter the archive log thread =1 Sequence =2405 RECID=2414 STAMP=898705314

Enter the archive log thread =1 Sequence =2406 RECID=2415 STAMP=898707838

passageway ORA_DISK_1: Starting segment 1 On 2016-02-01 22:12:08

passageway ORA_DISK_2: Starting archive log backup set

passageway ORA_DISK_2: Specifying archive logs within backup set

Enter the archive log thread =1 Sequence =2407 RECID=2416 STAMP=898708044

Enter the archive log thread =1 Sequence =2408 RECID=2417 STAMP=902613950

Enter the archive log thread =1 Sequence =2409 RECID=2418 STAMP=902614593

Enter the archive log thread =1 Sequence =2410 RECID=2419 STAMP=902618226

Enter the archive log thread =1 Sequence =2411 RECID=2420 STAMP=902699234

Enter the archive log thread =1 Sequence =2412 RECID=2421 STAMP=902700168

Enter the archive log thread =1 Sequence =2413 RECID=2422 STAMP=902700702

passageway ORA_DISK_2: Starting segment 1 On 2016-02-01 22:12:38

passageway ORA_DISK_1: Completed segment 1 On 2016-02-01 22:12:47

Segment handle =/home/oracle/oracle_bk/orclasm/q9qss7lj_1_1_ORCLASM.bak Mark =TAG20160201T221152 notes =NONE

passageway ORA_DISK_1: Backup set completed , After time :00:00:39

passageway ORA_DISK_2: Completed segment 1 On 2016-02-01 22:13:17

Segment handle =/home/oracle/oracle_bk/orclasm/qaqss7lp_1_1_ORCLASM.bak Mark =TAG20160201T221152 notes =NONE

passageway ORA_DISK_2: Backup set completed , After time :00:00:39

complete backup On 2016-02-01 22:13:17

 

start-up backup On 2016-02-01 22:13:22

Use channel ORA_DISK_1

Use channel ORA_DISK_2

passageway ORA_DISK_1: Starting full backup of datasets

passageway ORA_DISK_1: Specifying data files in the backup set

Input data file : File number =00033 name =+FRA/orclasm/datafile/users.449.880121199

Input data file : File number =00002 name =+DATA/orclasm/datafile/sysaux.257.850260145

Input data file : File number =00001 name =+DATA/orclasm/datafile/system.256.850260145

Input data file : File number =00005 name =+DATA/orclasm/datafile/example.265.850260295

Input data file : File number =00008 name =+DATA/orclasm/datafile/ts_lhr.269.852632495

Input data file : File number =00014 name =+DATA/orclasm/datafile/ts_lhr.284.869738273

Input data file : File number =00034 name =+FRA/orclasm/datafile/system.349.880121287

passageway ORA_DISK_1: Starting segment 1 On 2016-02-01 22:13:40

passageway ORA_DISK_2: Starting full backup of datasets

passageway ORA_DISK_2: Specifying data files in the backup set

Input data file : File number =00004 name =+DATA/orclasm/datafile/users.259.850260147

Input data file : File number =00003 name =+DATA/orclasm/datafile/undotbs1.258.851526539

Input data file : File number =00010 name =+DATA/orclasm/datafile/goldengate.273.862829891

Input data file : File number =00006 name =+DATA/orclasm/datafile/app1tbs.268.902619275

Input data file : File number =00007 name =+DATA/orclasm/datafile/app2tbs.280.902619327

Input data file : File number =00011 name =+DATA/orclasm/datafile/idxtbs.281.902619361

Input data file : File number =00009 name =+DATA/orclasm/datafile/encrypted_ts.272.854650889

passageway ORA_DISK_2: Starting segment 1 On 2016-02-01 22:13:47

passageway ORA_DISK_1: Completed segment 1 On 2016-02-01 22:21:15

Segment handle =/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700816_1867_1.bak Mark =TAG20160201T221331 notes =NONE

passageway ORA_DISK_1: Backup set completed , After time :00:07:35

passageway ORA_DISK_1: Starting full backup of datasets

passageway ORA_DISK_1: Specifying data files in the backup set

The backup set contains the current control file

passageway ORA_DISK_1: Starting segment 1 On 2016-02-01 22:21:52

passageway ORA_DISK_1: Completed segment 1 On 2016-02-01 22:22:17

Segment handle =/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902701286_1869_1.bak Mark =TAG20160201T221331 notes =NONE

passageway ORA_DISK_1: Backup set completed , After time :00:00:25

passageway ORA_DISK_2: Completed segment 1 On 2016-02-01 22:26:07

Segment handle =/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700821_1868_1.bak Mark =TAG20160201T221331 notes =NONE

passageway ORA_DISK_2: Backup set completed , After time :00:12:20

complete backup On 2016-02-01 22:26:07

 

start-up backup On 2016-02-01 22:26:11

The current log is archived

Use channel ORA_DISK_1

Use channel ORA_DISK_2

passageway ORA_DISK_1: Starting archive log backup set

passageway ORA_DISK_1: Specifying archive logs within backup set

Enter the archive log thread =1 Sequence =2414 RECID=2423 STAMP=902701474

passageway ORA_DISK_1: Starting segment 1 On 2016-02-01 22:26:41

passageway ORA_DISK_2: Starting archive log backup set

passageway ORA_DISK_2: Specifying archive logs within backup set

Enter the archive log thread =1 Sequence =2415 RECID=2424 STAMP=902701578

passageway ORA_DISK_2: Starting segment 1 On 2016-02-01 22:26:46

passageway ORA_DISK_1: Completed segment 1 On 2016-02-01 22:26:53

Segment handle =/home/oracle/oracle_bk/orclasm/qeqss8h1_1_1_ORCLASM.bak Mark =TAG20160201T222636 notes =NONE

passageway ORA_DISK_1: Backup set completed , After time :00:00:12

passageway ORA_DISK_2: Completed segment 1 On 2016-02-01 22:27:01

Segment handle =/home/oracle/oracle_bk/orclasm/qfqss8h5_1_1_ORCLASM.bak Mark =TAG20160201T222636 notes =NONE

passageway ORA_DISK_2: Backup set completed , After time :00:00:15

complete backup On 2016-02-01 22:27:01

 

start-up Control File and SPFILE Autobackup On 2016-02-01 22:27:08

paragraph handle=/home/oracle/oracle_bk/orclasm/ctlfile_autoback_ORCLASM_c-3424884828-20160201-0a.bak comment=NONE

complete Control File and SPFILE Autobackup On 2016-02-01 22:27:33

 

RMAN> list backupset;

 

 

Backup set list

===================

 

 

BS keyword   size        Device type takes time Completion time           

------- ---------- ----------- ------------ -------------------

1743    276.69M    DISK        00:00:35     2016-02-01 22:12:38

        BP keyword : 1814   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T221152

Paragraph name :/home/oracle/oracle_bk/orclasm/q9qss7lj_1_1_ORCLASM.bak

 

  Backup set 1743 List of archived logs in

  Thread sequence      low SCN    The lower limit of time             next SCN   The next time

  ---- ------- ---------- ------------------- ---------- ---------

  1    2400    55314243   2015-12-17 11:42:30 55356997   2015-12-17 13:28:31

  1    2401    55356997   2015-12-17 13:28:31 55380655   2015-12-17 15:34:02

  1    2402    55380655   2015-12-17 15:34:02 55391244   2015-12-17 15:54:10

  1    2403    55391244   2015-12-17 15:54:10 55401844   2015-12-17 15:59:14

  1    2404    55401844   2015-12-17 15:59:14 55411350   2015-12-17 16:19:24

  1    2405    55411350   2015-12-17 16:19:24 55423877   2015-12-17 16:21:47

  1    2406    55423877   2015-12-17 16:21:47 55455247   2015-12-17 17:03:49

 

BS keyword   size        Device type takes time Completion time           

------- ---------- ----------- ------------ -------------------

1744    214.24M    DISK        00:01:01     2016-02-01 22:13:10

        BP keyword : 1815   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T221152

Paragraph name :/home/oracle/oracle_bk/orclasm/qaqss7lp_1_1_ORCLASM.bak

 

  Backup set 1744 List of archived logs in

  Thread sequence      low SCN    The lower limit of time             next SCN   The next time

  ---- ------- ---------- ------------------- ---------- ---------

  1    2407    55455247   2015-12-17 17:03:49 55466900   2015-12-17 17:07:18

  1    2408    55466900   2015-12-17 17:07:18 55509831   2016-01-31 22:05:41

  1    2409    55509831   2016-01-31 22:05:41 55521588   2016-01-31 22:16:26

  1    2410    55521588   2016-01-31 22:16:26 55554213   2016-01-31 23:17:01

  1    2411    55554213   2016-01-31 23:17:01 55596287   2016-02-01 21:47:10

  1    2412    55596287   2016-02-01 21:47:10 55599466   2016-02-01 22:02:41

  1    2413    55599466   2016-02-01 22:02:41 55620029   2016-02-01 22:11:25

 

BS keyword   type LV size        Device type After time Completion time           

------- ---- -- ---------- ----------- ------------ -------------------

1745    Full    2.34G      DISK        00:07:33     2016-02-01 22:21:09

        BP keyword : 1816   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T221331

Paragraph name :/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700816_1867_1.bak

  Backup set 1745 List of data files in

  file LV type Ckp SCN    Ckp Time             name

  ---- -- ---- ---------- ------------------- ----

  1       Full 55620642   2016-02-01 22:13:41 +DATA/orclasm/datafile/system.256.850260145

  2       Full 55620642   2016-02-01 22:13:41 +DATA/orclasm/datafile/sysaux.257.850260145

  5       Full 55620642   2016-02-01 22:13:41 +DATA/orclasm/datafile/example.265.850260295

  8       Full 55620642   2016-02-01 22:13:41 +DATA/orclasm/datafile/ts_lhr.269.852632495

  14      Full 55620642   2016-02-01 22:13:41 +DATA/orclasm/datafile/ts_lhr.284.869738273

  33      Full 55620642   2016-02-01 22:13:41 +FRA/orclasm/datafile/users.449.880121199

  34      Full 55620642   2016-02-01 22:13:41 +FRA/orclasm/datafile/system.349.880121287

 

BS keyword   type LV size        Device type After time Completion time           

------- ---- -- ---------- ----------- ------------ -------------------

1746    Full    12.77M     DISK        00:00:44     2016-02-01 22:22:10

        BP keyword : 1817   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T221331

Paragraph name :/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902701286_1869_1.bak

  Included control documents : Ckp SCN: 55622293     Ckp Time : 2016-02-01 22:21:30

 

BS keyword   type LV size        Device type After time Completion time           

------- ---- -- ---------- ----------- ------------ -------------------

1747    Full    2.54G      DISK        00:12:22     2016-02-01 22:26:03

        BP keyword : 1818   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T221331

Paragraph name :/u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700821_1868_1.bak

  Backup set 1747 List of data files in

  file LV type Ckp SCN    Ckp Time             name

  ---- -- ---- ---------- ------------------- ----

  3       Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/undotbs1.258.851526539

  4       Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/users.259.850260147

  6       Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/app1tbs.268.902619275

  7       Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/app2tbs.280.902619327

  9       Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/encrypted_ts.272.854650889

  10      Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/goldengate.273.862829891

  11      Full 55620651   2016-02-01 22:13:47 +DATA/orclasm/datafile/idxtbs.281.902619361

 

BS keyword   size        Device type takes time Completion time           

------- ---------- ----------- ------------ -------------------

1748    39.85M     DISK        00:00:10     2016-02-01 22:26:51

        BP keyword : 1819   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T222636

Paragraph name :/home/oracle/oracle_bk/orclasm/qeqss8h1_1_1_ORCLASM.bak

 

  Backup set 1748 List of archived logs in

  Thread sequence      low SCN    The lower limit of time             next SCN   The next time

  ---- ------- ---------- ------------------- ---------- ---------

  1    2414    55620029   2016-02-01 22:11:25 55623076   2016-02-01 22:24:03

 

BS keyword   size        Device type takes time Completion time           

------- ---------- ----------- ------------ -------------------

1749    16.97M     DISK        00:00:07     2016-02-01 22:26:52

        BP keyword : 1820   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T222636

Paragraph name :/home/oracle/oracle_bk/orclasm/qfqss8h5_1_1_ORCLASM.bak

 

  Backup set 1749 List of archived logs in

  Thread sequence      low SCN    The lower limit of time             next SCN   The next time

  ---- ------- ---------- ------------------- ---------- ---------

  1    2415    55623076   2016-02-01 22:24:03 55623713   2016-02-01 22:26:13

 

BS keyword   type LV size        Device type After time Completion time           

------- ---- -- ---------- ----------- ------------ -------------------

1750    Full    12.80M     DISK        00:00:20     2016-02-01 22:27:28

        BP keyword : 1821   state : AVAILABLE  Already compressed : NO  Mark : TAG20160201T222708

Paragraph name :/home/oracle/oracle_bk/orclasm/ctlfile_autoback_ORCLASM_c-3424884828-20160201-0a.bak

  Contains SPFILE: Modification time : 2015-11-11 19:53:11

  SPFILE db_unique_name: ORCLASM

  Included control documents : Ckp SCN: 55624410     Ckp Time : 2016-02-01 22:27:08

 

One .8.2   Generate result set

What needs special attention in this transformation step is /u05/tmp Space size of , Because you need to create a secondary instance , So the space size should be at least source The sum of the size of the library table space plus the size of the data file of the table space to be transferred , Otherwise, the conversion will fail due to insufficient space .

RMAN> transport tablespace app1tbs,app2tbs,idxtbs tablespace destination '/u05/tmp' auxiliary destination '/u05/tmp';

 

Replace the recovery directory with the target database control file

RMAN-05026: Warning : Assume that the following set of table spaces applies to the specified point in time

 

The table space list requires UNDO paragraph

Table space SYSTEM

Table space UNDOTBS1

 

Use SID='ypkw' Create automatic instances

 

Initialization parameters for automatic instances :

db_name=ORCLASM

db_unique_name=ypkw_tspitr_ORCLASM

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u05/tmp

log_archive_dest_1='location=/u05/tmp'

#No auxiliary parameter file used

 

 

Start the automatic instance ORCLASM

 

Oracle Instance started

 

System global area total      292278272 byte

 

Fixed Size                     2227744 byte

Variable Size                100663776 byte

Database Buffers             184549376 byte

Redo Buffers                   4837376 byte

Automatic instance created

Run... On the recovery set table space TRANSPORT_SET_CHECK

TRANSPORT_SET_CHECK Has been successfully completed

 

Contents of memory script :

{

# set requested point in time

set until  scn 55627689;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

Executing memory script

 

Executing order : SET until clause

 

start-up restore On 2016-02-01 22:44:21

Assigned channels : ORA_AUX_DISK_1

passageway ORA_AUX_DISK_1: SID=59 Device type =DISK

Assigned channels : ORA_AUX_DISK_2

passageway ORA_AUX_DISK_2: SID=10 Device type =DISK

 

passageway ORA_AUX_DISK_1: Starting restore of data file backup set

passageway ORA_AUX_DISK_1: Restoring control files

passageway ORA_AUX_DISK_1: Reading backup fragment /home/oracle/oracle_bk/orclasm/ctlfile_autoback_ORCLASM_c-3424884828-20160201-0a.bak

passageway ORA_AUX_DISK_1: Segment handle = /home/oracle/oracle_bk/orclasm/ctlfile_autoback_ORCLASM_c-3424884828-20160201-0a.bak Mark = TAG20160201T222708

passageway ORA_AUX_DISK_1: The backup fragment has been restored 1

passageway ORA_AUX_DISK_1: Restore complete , when : 00:00:01

Output file name =/u05/tmp/ORCLASM/controlfile/o1_mf_cbyvtc77_.ctl

complete restore On 2016-02-01 22:44:28

 

sql sentence : alter database mount clone database

 

sql sentence : alter system archive log current

 

Contents of memory script :

{

# set requested point in time

set until  scn 55627689;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  34 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  6 to

"/u05/tmp/o1_mf_app1tbs_%u_.dbf";

set newname for datafile  7 to

"/u05/tmp/o1_mf_app2tbs_%u_.dbf";

set newname for datafile  11 to

"/u05/tmp/o1_mf_idxtbs_%u_.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 34, 3, 2, 6, 7, 11;

switch clone datafile all;

}

Executing memory script

 

Executing order : SET until clause

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

Executing order : SET NEWNAME

 

The temporary file 1 Renamed... In control file /u05/tmp/ORCLASM/datafile/o1_mf_temp_%u_.tmp

 

start-up restore On 2016-02-01 22:44:42

Use channel ORA_AUX_DISK_1

Use channel ORA_AUX_DISK_2

 

passageway ORA_AUX_DISK_1: Starting restore of data file backup set

passageway ORA_AUX_DISK_1: Specifying data files to restore from backup set

passageway ORA_AUX_DISK_1: Put the data file 00001 Restore to /u05/tmp/ORCLASM/datafile/o1_mf_system_%u_.dbf

passageway ORA_AUX_DISK_1: Put the data file 00034 Restore to /u05/tmp/ORCLASM/datafile/o1_mf_system_%u_.dbf

passageway ORA_AUX_DISK_1: Put the data file 00002 Restore to /u05/tmp/ORCLASM/datafile/o1_mf_sysaux_%u_.dbf

passageway ORA_AUX_DISK_1: Reading backup fragment /u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700816_1867_1.bak

passageway ORA_AUX_DISK_2: Starting restore of data file backup set

passageway ORA_AUX_DISK_2: Specifying data files to restore from backup set

passageway ORA_AUX_DISK_2: Put the data file 00003 Restore to /u05/tmp/ORCLASM/datafile/o1_mf_undotbs1_%u_.dbf

passageway ORA_AUX_DISK_2: Put the data file 00006 Restore to /u05/tmp/o1_mf_app1tbs_%u_.dbf

passageway ORA_AUX_DISK_2: Put the data file 00007 Restore to /u05/tmp/o1_mf_app2tbs_%u_.dbf

passageway ORA_AUX_DISK_2: Put the data file 00011 Restore to /u05/tmp/o1_mf_idxtbs_%u_.dbf

passageway ORA_AUX_DISK_2: Reading backup fragment /u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700821_1868_1.bak

passageway ORA_AUX_DISK_2: Segment handle = /u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700821_1868_1.bak Mark = TAG20160201T221331

passageway ORA_AUX_DISK_2: The backup fragment has been restored 1

passageway ORA_AUX_DISK_2: Restore complete , when : 00:00:25

passageway ORA_AUX_DISK_1: Segment handle = /u05/oracle/oracle_bk/orclasm/full_ORCLASMx_20160201_902700816_1867_1.bak Mark = TAG20160201T221331

passageway ORA_AUX_DISK_1: The backup fragment has been restored 1

passageway ORA_AUX_DISK_1: Restore complete , when : 00:01:35

complete restore On 2016-02-01 22:46:21

 

Data files 1 Converted to a copy of the data file

Copy of input data file RECID=58 STAMP=902702782 file name =/u05/tmp/ORCLASM/datafile/o1_mf_system_cbyvtz00_.dbf

Data files 34 Converted to a copy of the data file

Copy of input data file RECID=59 STAMP=902702782 file name =/u05/tmp/ORCLASM/datafile/o1_mf_system_cbyvtz5j_.dbf

Data files 3 Converted to a copy of the data file

Copy of input data file RECID=60 STAMP=902702782 file name =/u05/tmp/ORCLASM/datafile/o1_mf_undotbs1_cbyvtyz4_.dbf

Data files 2 Converted to a copy of the data file

Copy of input data file RECID=61 STAMP=902702782 file name =/u05/tmp/ORCLASM/datafile/o1_mf_sysaux_cbyvtyxm_.dbf

Data files 6 Converted to a copy of the data file

Copy of input data file RECID=62 STAMP=902702782 file name =/u05/tmp/o1_mf_app1tbs_cbyvtz0l_.dbf

Data files 7 Converted to a copy of the data file

Copy of input data file RECID=63 STAMP=902702782 file name =/u05/tmp/o1_mf_app2tbs_cbyvtz1y_.dbf

Data files 11 Converted to a copy of the data file

Copy of input data file RECID=64 STAMP=902702782 file name =/u05/tmp/o1_mf_idxtbs_cbyvtz52_.dbf

 

Contents of memory script :

{

# set requested point in time

set until  scn 55627689;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  34 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  6 online";

sql clone "alter database datafile  7 online";

sql clone "alter database datafile  11 online";

# recover and open resetlogs

recover clone database tablespace  "APP1TBS", "APP2TBS", "IDXTBS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

Executing memory script

 

Executing order : SET until clause

 

sql sentence : alter database datafile  1 online

 

sql sentence : alter database datafile  34 online

 

sql sentence : alter database datafile  3 online

 

sql sentence : alter database datafile  2 online

 

sql sentence : alter database datafile  6 online

 

sql sentence : alter database datafile  7 online

 

sql sentence : alter database datafile  11 online

 

start-up recover On 2016-02-01 22:46:23

Use channel ORA_AUX_DISK_1

Use channel ORA_AUX_DISK_2

 

Starting media recovery

 

Threads 1 Sequence 2414 The archive log of has been used as a file +FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2414.283.902701451 On disk

Threads 1 Sequence 2415 The archive log of has been used as a file +FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2415.467.902701575 On disk

Threads 1 Sequence 2416 The archive log of has been used as a file +FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2416.332.902701653 On disk

Threads 1 Sequence 2417 The archive log of has been used as a file +FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2417.308.902701787 On disk

Threads 1 Sequence 2418 The archive log of has been used as a file +FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2418.366.902701939 On disk

Archive log file name =+FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2414.283.902701451 Threads =1 Sequence =2414

Archive log file name =+FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2415.467.902701575 Threads =1 Sequence =2415

Archive log file name =+FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2416.332.902701653 Threads =1 Sequence =2416

Archive log file name =+FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2417.308.902701787 Threads =1 Sequence =2417

Archive log file name =+FRA/orclasm/archivelog/2016_02_01/thread_1_seq_2418.366.902701939 Threads =1 Sequence =2418

Media recovery complete , when : 00:00:14

complete recover On 2016-02-01 22:46:41

 

Database is open

 

Contents of memory script :

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  APP1TBS read only';

sql clone 'alter tablespace  APP2TBS read only';

sql clone 'alter tablespace  IDXTBS read only';

# create directory for datapump export

sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''

/u05/tmp''";

}

Executing memory script

 

sql sentence : alter tablespace  APP1TBS read only

 

sql sentence : alter tablespace  APP2TBS read only

 

sql sentence : alter tablespace  IDXTBS read only

 

sql sentence : create or replace directory STREAMS_DIROBJ_DPDIR as ''/u05/tmp''

 

Performing metadata export ...

   EXPDP> start-up "SYS"."TSPITR_EXP_ypkw": 

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/INDEX/INDEX

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/INDEX_STATISTICS

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> Handle object types TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Successfully loaded / The main table is unloaded "SYS"."TSPITR_EXP_ypkw"

   EXPDP> ******************************************************************************

   EXPDP> SYS.TSPITR_EXP_ypkw The dump file set of is :

   EXPDP>   /u05/tmp/dmpfile.dmp

   EXPDP> ******************************************************************************

   EXPDP> Transportable tablespace APP1TBS Data files required :

   EXPDP>   /u05/tmp/o1_mf_app1tbs_cbyvtz0l_.dbf

   EXPDP> Transportable tablespace APP2TBS Data files required :

   EXPDP>   /u05/tmp/o1_mf_app2tbs_cbyvtz1y_.dbf

   EXPDP> Transportable tablespace IDXTBS Data files required :

   EXPDP>   /u05/tmp/o1_mf_idxtbs_cbyvtz52_.dbf

   EXPDP> Homework "SYS"."TSPITR_EXP_ypkw" Has been in 22:48:00 Successfully completed

Export finished

 

/*

   The following command may be used to import the tablespaces.

   Substitute values for <logon> and <directory>.

   impdp <logon> directory=<directory> dumpfile= 'dmpfile.dmp' transport_datafiles= /u05/tmp/o1_mf_app1tbs_cbyvtz0l_.dbf, /u05/tmp/o1_mf_app2tbs_cbyvtz1y_.dbf, /u05/tmp/o1_mf_idxtbs_cbyvtz52_.dbf

*/

--------------------------------------------------------------

-- Start of sample PL/SQL script for importing the tablespaces

--------------------------------------------------------------

-- creating directory objects

CREATE DIRECTORY STREAMSDIROBJDIROBJ1 AS  '/u05/tmp/';

CREATE DIRECTORY STREAMSDIROBJDIROBJDPDIR AS  '/u05/tmp';

/* PL/SQL Script to import the exported tablespaces */

DECLARE

  -- the datafiles

  tbs_files     dbms_streams_tablespace_adm.file_set;

  cvt_files     dbms_streams_tablespace_adm.file_set;

  -- the dumpfile to import

  dump_file     dbms_streams_tablespace_adm.file;

  dp_job_name   VARCHAR2(30) := NULL;

  -- names of tablespaces that were imported

  ts_names       dbms_streams_tablespace_adm.tablespace_set;

BEGIN

  -- dump file name and location

  dump_file.file_name :=  'dmpfile.dmp';

  dump_file.directory_object := 'STREAMSDIROBJDIROBJDPDIR';

  -- forming list of datafiles for import

  tbs_files( 1).file_name :=  'o1_mf_app1tbs_cbyvtz0l_.dbf';

  tbs_files( 1).directory_object :=  'STREAMSDIROBJDIROBJ1';

  tbs_files( 2).file_name :=  'o1_mf_app2tbs_cbyvtz1y_.dbf';

  tbs_files( 2).directory_object :=  'STREAMSDIROBJDIROBJ1';

  tbs_files( 3).file_name :=  'o1_mf_idxtbs_cbyvtz52_.dbf';

  tbs_files( 3).directory_object :=  'STREAMSDIROBJDIROBJ1';

  -- import tablespaces

  dbms_streams_tablespace_adm.attach_tablespaces(

    datapump_job_name      => dp_job_name,

    dump_file              => dump_file,

    tablespace_files       => tbs_files,

    converted_files        => cvt_files,

    tablespace_names       => ts_names);

  -- output names of imported tablespaces

  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN

    FOR i IN ts_names.first .. ts_names.last LOOP

      dbms_output.put_line('imported tablespace '|| ts_names(i));

    END LOOP;

  END IF;

END;

/

-- dropping directory objects

DROP DIRECTORY STREAMSDIROBJDIROBJ1;

DROP DIRECTORY STREAMSDIROBJDIROBJDPDIR;

--------------------------------------------------------------

-- End of sample PL/SQL script

--------------------------------------------------------------

 

Delete automatic instance

Turn off automatic instance

Database is down

Database dismounted

Oracle Instance closed

Automatic instance deleted

Secondary instance file deleted /u05/tmp/ORCLASM/datafile/o1_mf_temp_cbyvytdt_.tmp

Secondary instance file deleted /u05/tmp/ORCLASM/onlinelog/o1_mf_4_cbyvyqnp_.log

Secondary instance file deleted /u05/tmp/ORCLASM/onlinelog/o1_mf_3_cbyvypkj_.log

Secondary instance file deleted /u05/tmp/ORCLASM/onlinelog/o1_mf_2_cbyvyonl_.log

Secondary instance file deleted /u05/tmp/ORCLASM/onlinelog/o1_mf_1_cbyvykf7_.log

Secondary instance file deleted /u05/tmp/ORCLASM/datafile/o1_mf_sysaux_cbyvtyxm_.dbf

Secondary instance file deleted /u05/tmp/ORCLASM/datafile/o1_mf_undotbs1_cbyvtyz4_.dbf

Secondary instance file deleted /u05/tmp/ORCLASM/datafile/o1_mf_system_cbyvtz5j_.dbf

Secondary instance file deleted /u05/tmp/ORCLASM/datafile/o1_mf_system_cbyvtz00_.dbf

Secondary instance file deleted /u05/tmp/ORCLASM/controlfile/o1_mf_cbyvtc77_.ctl

 

RMAN>

 

[oracle@rhel6_lhr ~]$ cd /u05/tmp

[oracle@rhel6_lhr tmp]$ ll

total 30868

-rw-r----- 1 oracle asmadmin   118784 Feb  1 22:48 dmpfile.dmp

-rw-r--r-- 1 oracle oinstall     2373 Feb  1 22:48 impscrpt.sql

-rw-r----- 1 oracle asmadmin 10493952 Feb  1 22:47 o1_mf_app1tbs_cbyvtz0l_.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb  1 22:47 o1_mf_app2tbs_cbyvtz1y_.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb  1 22:47 o1_mf_idxtbs_cbyvtz52_.dbf

drwxr-x--- 5 oracle asmadmin     4096 Feb  1 22:44 ORCLASM

[oracle@rhel6_lhr tmp]$

 

 

One .9   Transfer data files and metadata to target End

Here we need to transfer the metadata file and data file to the target database

 

 

One .9.1   View the target library data file location and directory

[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr

[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 13:51:47 2016

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@ora2lhr> select name from v$datafile;

 

NAME

------------------------------------------------------------------------------------------------------------------------------------

+DATA/ora2lhr/datafile/system.335.902674033

+DATA/ora2lhr/datafile/sysaux.336.902674033

+DATA/ora2lhr/datafile/undotbs1.337.902674033

+DATA/ora2lhr/datafile/users.338.902674033

+DATA/ora2lhr/datafile/example.348.902674109

 

SYS@ora2lhr> set line 9999

SYS@ora2lhr> col directory_name for a28

SYS@ora2lhr> col directory_path for a100

SYS@ora2lhr> select directory_name,directory_path from dba_directories;

 

DIRECTORY_NAME               DIRECTORY_PATH

---------------------------- ----------------------------------------------------------------------------------------------------

SUBDIR                       /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep

SS_OE_XMLDIR                 /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry/

LOG_FILE_DIR                 /oracle/app/oracle/product/11.2.0/db/demo/schema/log/

MEDIA_DIR                    /oracle/app/oracle/product/11.2.0/db/demo/schema/product_media/

DATA_FILE_DIR                /oracle/app/oracle/product/11.2.0/db/demo/schema/sales_history/

XMLDIR                       /oracle/app/oracle/product/11.2.0/db/rdbms/xml

ORACLE_OCM_CONFIG_DIR        /oracle/app/oracle/product/11.2.0/db/ccr/state

DATA_PUMP_DIR                /oracle/app/oracle/admin/ora2lhr/dpdump/

ORACLE_OCM_CONFIG_DIR2       /oracle/app/oracle/product/11.2.0/db/ccr/state

 

9 rows selected.

 

SYS@ora2lhr>

SYS@ora2lhr>

 

One .9.2   Copy the file to the corresponding location of the target library and modify the permissions

 

wpsF707.tmp 

 

 

 

 

[ZFXDESKDB2:root]:/>cd /oracle/app/oracle/admin/ora2lhr/dpdump

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>l

total 61728

-rw-r-----    1 root     system       118784 Feb 01 22:48 dmpfile.dmp

-rw-r-----    1 root     system         2373 Feb 01 22:48 impscrpt.sql

-rw-r-----    1 root     system     10493952 Feb 01 22:47 o1_mf_app1tbs_cbyvtz0l_.dbf

-rw-r-----    1 root     system     10493952 Feb 01 22:47 o1_mf_app2tbs_cbyvtz1y_.dbf

-rw-r-----    1 root     system     10493952 Feb 01 22:47 o1_mf_idxtbs_cbyvtz52_.dbf

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>chown oracle:dba ./*

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>l

total 61728

-rw-r-----    1 oracle   dba          118784 Feb 01 22:48 dmpfile.dmp

-rw-r-----    1 oracle   dba            2373 Feb 01 22:48 impscrpt.sql

-rw-r-----    1 oracle   dba        10493952 Feb 01 22:47 o1_mf_app1tbs_cbyvtz0l_.dbf

-rw-r-----    1 oracle   dba        10493952 Feb 01 22:47 o1_mf_app2tbs_cbyvtz1y_.dbf

-rw-r-----    1 oracle   dba        10493952 Feb 01 22:47 o1_mf_idxtbs_cbyvtz52_.dbf

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>

 

 

 

One .10  target End to end conversion byte order

 

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>su - oracle

[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr

 

[ZFXDESKDB2:oracle]:/oracle>echo $ORACLE_SID

ora2lhr

[ZFXDESKDB2:oracle]:/oracle>rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 2 13:57:58 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORA2LHR (DBID=4055514164)

 

RMAN> CONVERT DATAFILE

2> "/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_app1tbs_cbyvtz0l_.dbf",

3> "/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_app2tbs_cbyvtz1y_.dbf",

4> "/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_idxtbs_cbyvtz52_.dbf"

5> TO PLATFORM="AIX-Based Systems (64-bit)"

6> FROM PLATFORM="Linux x86 64-bit"

7> FORMAT '+DATA';

 

Starting conversion at target at 2016-02-02 13:58:02

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=165 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_app1tbs_cbyvtz0l_.dbf

converted datafile=+DATA/ora2lhr/datafile/app1tbs.265.902757483

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile conversion

input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_app2tbs_cbyvtz1y_.dbf

converted datafile=+DATA/ora2lhr/datafile/app2tbs.264.902757485

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile conversion

input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/o1_mf_idxtbs_cbyvtz52_.dbf

converted datafile=+DATA/ora2lhr/datafile/idxtbs.262.902757485

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished conversion at target at 2016-02-02 13:58:06

 

RMAN>

RMAN>

 

 

[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>

[ZFXDESKDB2:root]:/>su - grid

[ZFXDESKDB2:grid]:/home/grid>asmcmd

ASMCMD> cd +DATA/ora2lhr/datafile/

ASMCMD> ls

APP1TBS.265.902757483

APP2TBS.264.902757485

EXAMPLE.348.902674109

IDXTBS.262.902757485

SYSAUX.336.902674033

SYSTEM.335.902674033

UNDOTBS1.337.902674033

USERS.338.902674033

ASMCMD> ls -l

Type      Redund  Striped  Time             Sys  Name

DATAFILE  UNPROT  COARSE   FEB 02 13:00:00  Y    APP1TBS.265.902757483

DATAFILE  UNPROT  COARSE   FEB 02 13:00:00  Y    APP2TBS.264.902757485

DATAFILE  UNPROT  COARSE   FEB 02 09:00:00  Y    EXAMPLE.348.902674109

DATAFILE  UNPROT  COARSE   FEB 02 13:00:00  Y    IDXTBS.262.902757485

DATAFILE  UNPROT  COARSE   FEB 02 09:00:00  Y    SYSAUX.336.902674033

DATAFILE  UNPROT  COARSE   FEB 02 09:00:00  Y    SYSTEM.335.902674033

DATAFILE  UNPROT  COARSE   FEB 02 09:00:00  Y    UNDOTBS1.337.902674033

DATAFILE  UNPROT  COARSE   FEB 02 09:00:00  Y    USERS.338.902674033

ASMCMD>

 

 

One .11  target The end starts importing data

One .11.1   establish source Library 2 Users and permissions

If the user is not created, the following error will be reported :

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29342: user USER_APP1 does not exist in the database

 

 

 

[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 14:08:33 2016

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@ora2lhr> create user user_app1 identified by user_app1;

 

User created.

 

SYS@ora2lhr> create user user_app2 identified by user_app2;

 

User created.

 

SYS@ora2lhr> grant connect , resource to user_app1;

 

Grant succeeded.

 

SYS@ora2lhr> grant connect , resource to user_app2;

 

Grant succeeded.

 

SYS@ora2lhr> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[ZFXDESKDB2:oracle]:/oracle>

 

 

One .11.2   Start import

 

 

[ZFXDESKDB2:oracle]:/oracle>export ORACLE_SID=ora2lhr

[ZFXDESKDB2:oracle]:/oracle>impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/ora2lhr/datafile/APP1TBS.265.902757483','+DATA/ora2lhr/datafile/APP2TBS.264.902757485','+DATA/ora2lhr/datafile/IDXTBS.262.902757485' LOGFILE=impdp_tts_20160202.log

 

Import: Release 11.2.0.4.0 - Production on Tue Feb 2 14:11:57 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/ora2lhr/datafile/APP1TBS.265.902757483,+DATA/ora2lhr/datafile/APP2TBS.264.902757485,+DATA/ora2lhr/datafile/IDXTBS.262.902757485 LOGFILE=impdp_tts_20160202.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Feb 2 14:12:11 2016 elapsed 0 00:00:07

 

[ZFXDESKDB2:oracle]:/oracle>

 

journal :

Tue Feb 02 14:12:04 2016

DM00 started with pid=45, OS id=25231610, job SYS.SYS_IMPORT_TRANSPORTABLE_01

Tue Feb 02 14:12:06 2016

DW00 started with pid=48, OS id=21430384, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01

Plug in tablespace APP1TBS with datafile

  '+DATA/ora2lhr/datafile/APP1TBS.265.902757483'

Plug in tablespace APP2TBS with datafile

  '+DATA/ora2lhr/datafile/APP2TBS.264.902757485'

Plug in tablespace IDXTBS with datafile

  '+DATA/ora2lhr/datafile/IDXTBS.262.902757485'

 

One .11.3   View target platform information

 

[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 14:13:50 2016

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@ora2lhr> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

APP1TBS                        READ ONLY

APP2TBS                        READ ONLY

IDXTBS                         READ ONLY

 

9 rows selected.

 

SYS@ora2lhr> alter tablespace APP1TBS read write;

 

Tablespace altered.

 

SYS@ora2lhr> alter tablespace APP2TBS read write;

 

Tablespace altered.

 

SYS@ora2lhr> alter tablespace IDXTBS read write;

 

Tablespace altered.

 

SYS@ora2lhr> alter user user_app1 default tablespace app1tbs;

 

User altered.

 

SYS@ora2lhr> alter user user_app2 default tablespace app2tbs;

 

User altered.

 

SYS@ora2lhr>  SELECT d.username,d.default_tablespace FROM dba_users d where d.username like 'USER_%' ;

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

USER_APP2                      APP2TBS

USER_APP1                      APP1TBS

 

SYS@ora2lhr>

 

 

 

One .12   View the results after import

SYS@ora2lhr> set line 9999 pagesize 9999

SYS@ora2lhr> select * from user_app1.app1_tab;

 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

 

12 rows selected.

 

SYS@ora2lhr> select * from user_app2.app2_tab;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

SYS@ora2lhr>  select D.owner,D.index_name,D.table_name,D.tablespace_name from dba_indexes d WHERE d.table_name in ('APP1_TAB','APP2_TAB');

 

OWNER                          INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------

USER_APP2                      IDX_DEPT_DNAME                 APP2_TAB                       IDXTBS

USER_APP1                      IDX_EMP_ENAME                  APP1_TAB                       IDXTBS

 

SYS@ora2lhr> SELECT   a.NAME,  b.NAME FROM vtablespacea,vtablespacea,vdatafile b WHERE a.TS#=b.TS#  ;

 

NAME                           NAME

------------------------------ --------------------------------------------------------------------------------

SYSTEM                         +DATA/ora2lhr/datafile/system.335.902674033

SYSAUX                         +DATA/ora2lhr/datafile/sysaux.336.902674033

UNDOTBS1                       +DATA/ora2lhr/datafile/undotbs1.337.902674033

USERS                          +DATA/ora2lhr/datafile/users.338.902674033

EXAMPLE                        +DATA/ora2lhr/datafile/example.348.902674109

APP1TBS                        +DATA/ora2lhr/datafile/app1tbs.350.902674809

APP2TBS                        +DATA/ora2lhr/datafile/app2tbs.351.902674809

IDXTBS                         +DATA/ora2lhr/datafile/idxtbs.352.902674811

 

8 rows selected.

 

SYS@ora2lhr>

 

 

So far 3 The table spaces are completely composed of Linux Platform migration to AIX On the platform .

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------------

 

 

One .13   summary

 

So far, all the processing is basically finished , The process is simple , But there are many ways to deal with different scenes , We should learn to be flexible .