One .1  BLOG Document structure chart

 

wps82D4.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

⑤ What information and corresponding scripts should be collected for database migration

 

  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/

【TTS】 Transfer table space Linux asm -> AIX asm  be based on rman  http://blog.itpub.net/26736162/viewspace-1987953/

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

【TTS】 Transfer table space AIX asm -> linux asm  be based on rman http://blog.itpub.net/26736162/viewspace-1987961/

 

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 migration environment

project

source db

target  db

db  type

Single instance

Single instance

db version

11.2.0.3

11.2.0.3

db  Storage

ASM

ASM

ORACLE_SID

oraSKY

oraSKY

db_name

ORASKY

ORASKY

host IP Address :

22.188.139.33

192.168.59.30

OS Version and kernel edition

AIX 64 position  5.3.0.0

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

OS hostname

ZDMTRAIN2

rhel6_lhr

platform_name

AIX-Based Systems (64-bit)

Linux x86 64-bit

compatible

11.2.0.0.0

11.2.0.0.0

db time zone

14

14

Character set

AL32UTF8

AL32UTF8

Archiving mode

Archive Mode

Archive Mode

Migration required SCHEMA Number

3 (T,XPADAD,TEST1)

3 (T,XPADAD,TEST1)

Migration required TS Number

3 (USERS,XPADDATA,TEST_USER1)

3(USERS,XPADDATA,TEST_USER1)

Number of invalid objects

0

0

Data file path

+DATA1/ora11g/datafile/

+DATA

Logs and log groups

+DATA1/ora11g/onlinelog/group_3.388.936264969

+DATA1/ora11g/onlinelog/group_3.389.936264969

+DATA1/ora11g/onlinelog/group_2.386.936264967

+DATA1/ora11g/onlinelog/group_2.387.936264967

+DATA1/ora11g/onlinelog/group_1.384.936264967

+DATA1/ora11g/onlinelog/group_1.385.936264967

+DATA

Control documents

+DATA1/ora11g/controlfile/current.381.936264963,

+DATA1/ora11g/controlfile/current.383.936264963

+DATA

Use spfile still pfile

spfile

spfile

The actual size of the library to be migrated

100M


Total table space size

14G


need copy To target The file size of the client side

450M


 

One .4.2   The goal of the experiment

 

In the actual process of work , 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 , This document aims to realize AIX Database on oraSKY From the source platform to the target platform Linux On .

 

 

One .4.3   Experimental process

 

 

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

 

 

Chapter two  AIX Platform database migration to Linux-- be based on RMAN( Real environment )

Two .1    Source database information collection

 

 

Two .1.1   Run for a health check first ( Optional )

According to my own script , Run a health check on the source database , The main purpose is to collect the information of the source database , Script can talk to me in private , After examination html The documents are as follows :

 

wps82D5.tmp

for example :

wps82E5.tmp 

 

Two .1.2   Table space and data files

 

Two .1.2.1   Table space size

WITH wt1 AS

 (SELECT ts.TABLESPACE_NAME,

         df.all_bytes,

         decode(df.TYPE,

                'D',

                nvl(fs.FREESIZ, 0),

                'T',

                df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,

         df.MAXSIZ,

         ts.BLOCK_SIZE,

         ts.LOGGING,

         ts.FORCE_LOGGING,

         ts.CONTENTS,

         ts.EXTENT_MANAGEMENT,

         ts.SEGMENT_SPACE_MANAGEMENT,

         ts.RETENTION,

         ts.DEF_TAB_COMPRESSION,

         ts.STATUS,

         df.ts_df_count,

         df.FILE_NAME

  FROM   dba_tablespaces ts,

         -- Table space size

         (SELECT 'D' TYPE,

                 TABLESPACE_NAME,

                 COUNT(*) ts_df_count,

                 SUM(BYTES) all_bytes,

                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ,

                 to_char(wm_concat(d.FILE_NAME)) FILE_NAME

          FROM   dba_data_files d

          GROUP  BY TABLESPACE_NAME

          UNION ALL

          -- Temporary table space size can also be used (SELECT SUM(bytes)  FROM v$tempfile)

          SELECT 'T',

                 TABLESPACE_NAME,

                 COUNT(*) ts_df_count,

                 SUM(BYTES) all_bytes,

                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)),

                 to_char(wm_concat(d.FILE_NAME)) FILE_NAME

          FROM   dba_temp_files d

          GROUP  BY TABLESPACE_NAME) df,

         -- Available space size

         (SELECT TABLESPACE_NAME,

                 SUM(BYTES) FREESIZ

          FROM   dba_free_space

          GROUP  BY TABLESPACE_NAME

          UNION ALL

          SELECT tablespace_name,

                 SUM(d.BLOCK_SIZE * a.BLOCKS) bytes -- Here is the used size

          FROM   gv$sort_usage   a, -- or  v$tempseg_usage

                 dba_tablespaces d

          WHERE  a.tablespace = d.tablespace_name

          GROUP  BY tablespace_name) fs

  WHERE  ts.TABLESPACE_NAME = df.TABLESPACE_NAME

  AND    ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

SELECT (SELECT A.TS#

        FROM   V$TABLESPACE A

        WHERE  A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,

       t.TABLESPACE_NAME TS_Name,

       t.contents,

       round(t.all_bytes / 1024 / 1024) ts_size_M,

       round(t.freesiz / 1024 / 1024) Free_Size_M,

       round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

       round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,

       round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g,

       round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 3) MAX_Size_free_g,

       round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /

             MAXSIZ,

             3) USED_per_MAX,

       round(t.BLOCK_SIZE) BLOCK_SIZE,

       t.LOGGING,

       t.STATUS,

       t.ts_df_count,

       t.FILE_NAME data_file_name,

       t.FORCE_LOGGING,

       t.EXTENT_MANAGEMENT,

       t.SEGMENT_SPACE_MANAGEMENT,

       t.RETENTION,

       t.DEF_TAB_COMPRESSION

FROM   wt1 t

UNION ALL

SELECT to_number('') TS#,

       ' All table spaces ' TS_Name,

       '' contents,

       round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,

       round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,

       round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

       round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,

       round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,

       round((SUM(MAXSIZ) - SUM(t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024,

             3) MAX_Size_free_g,

       to_number('') "USED,% of MAX Size",

       to_number('') BLOCK_SIZE,

       '' LOGGING,

       '' STATUS,

       to_number('') ts_df_count,

       '' data_file_name,

       '' FORCE_LOGGING,

       '' EXTENT_MANAGEMENT,

       '' SEGMENT_SPACE_MANAGEMENT,

       '' RETENTION,

       '' DEF_TAB_COMPRESSION

FROM   wt1 t

ORDER  BY TS#;

 

wps82F6.tmp 

 

From this we can see that , The real migrated data is about 100M, But the table space has 14G, That is to say, local files need at least 14G+100M In order to complete the subsequent operation .

 

Two .1.2.2   The size of the data file to be transferred

SELECT d.FILE_ID,

       d.TABLESPACE_NAME,

       (SELECT (SUM(nb.BYTES/1024/1024))

          FROM dba_data_files nb

         WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m,

       d.FILE_NAME,

       (d.BYTES/1024/1024) file_size_m, 

       (d.USER_BYTES/1024/1024) file_use_size_m

  FROM dba_data_files d

WHERE d.TABLESPACE_NAME  in ('USERS','XPADDATA','TEST_USER1') 

 ORDER BY  file_id;wps8307.tmp

 

That is to say, it will eventually need to be copied to target The size of the data file is 450M.

Two .1.3   User information ( password 、 Default tablespace 、 Roles and permissions , Migration required schema Object size 、 Number 、 list )

Two .1.3.1   Users that need to be migrated

 

SELECT d.username,

       d.default_tablespace,

       D.temporary_tablespace,

       d.account_status

  FROM dba_users d

 WHERE d.account_status = 'OPEN'

   and d.username not like '%SYS%';

wps8308.tmp 

Two .1.3.2   User permissions

drop table t_tmp_user_lhr; 

create table   t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_typevarchar2(20) ); 

DROP  sequence   s_t_tmp_user_lhr;

create sequence s_t_tmp_user_lhr; 

 

begin

 

  for cur in (SELECT d.username,

                     d.default_tablespace,

                     d.account_status,

                     'create user ' || d.username || ' identified by ' ||

                     d.username || ' default tablespace ' ||

                     d.default_tablespace || '  TEMPORARY TABLESPACE  ' ||

                     D.temporary_tablespace || ';' CREATE_USER,

                     replace(to_char(DBMS_METADATA.GET_DDL('USER',

                                                           D.username)),

                             chr(10),

                             '') create_USER1

                FROM dba_users d

  WHERE d.username   in  ('T', 'XPADAD', 'TEST1')) loop

 

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

    values

      (s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');

     

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_SYS_PRIVS'

        FROM dba_sys_privs d

       WHERE D.GRANTEE = CUR.USERNAME;

 

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION;'

               ELSE

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_ROLE_PRIVS'

        FROM DBA_ROLE_PRIVS d

       WHERE D.GRANTEE = CUR.USERNAME;

 

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type) 

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN d.grantable = 'YES' THEN

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE ||

                '  WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_TAB_PRIVS'

        FROM DBA_TAB_PRIVS d

       WHERE D.GRANTEE = CUR.USERNAME;

  end loop;

  COMMIT;

end;

/

 SELECT * FROM t_tmp_user_lhr;

wps8318.tmp 

create user TEST1 identified by TEST1 default tablespace TEST_USER1  TEMPORARY TABLESPACE  TEMP;

GRANT UNLIMITED TABLESPACE TO TEST1;

GRANT CONNECT TO TEST1;

GRANT RESOURCE TO TEST1;

GRANT WRITE ON SYS.TEST_DIR TO TEST1;

GRANT READ ON SYS.TEST_DIR TO TEST1;

GRANT WRITE ON SYS.TEST_LOG TO TEST1;

GRANT READ ON SYS.TEST_LOG TO TEST1;

create user XPADAD identified by XPADAD default tablespace XPADDATA  TEMPORARY TABLESPACE  TEMP;

GRANT CREATE VIEW TO XPADAD;

GRANT UNLIMITED TABLESPACE TO XPADAD;

GRANT CREATE DATABASE LINK TO XPADAD;

GRANT DBA TO XPADAD;

GRANT CONNECT TO XPADAD;

GRANT RESOURCE TO XPADAD;

create user T identified by T default tablespace USERS  TEMPORARY TABLESPACE  TEMP;

GRANT UNLIMITED TABLESPACE TO T;

GRANT RESOURCE TO T;

GRANT CONNECT TO T;

GRANT WRITE ON SYS.TT TO T;

GRANT READ ON SYS.TT TO T;

 

 

 

 

Two .1.3.3   User table size

 

 select d.owner, (sum(bytes) / 1024 / 1024) sizes_m

  from dba_segments d

  where d.owner   in ('T', 'XPADAD', 'TEST1')

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner)

 GROUP BY d.owner

 order by sum(bytes) desc; 

 ;

wps8319.tmp 

Two .1.3.4   Number of objects

 

  SELECT D.OWNER,COUNT(1)

  FROM dba_objects d

 WHERE d.OWNER   in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

wps831A.tmp 

 

 

 SELECT D.OWNER, D.OBJECT_TYPE, COUNT(1)

   FROM dba_objects d

  WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')

    and d.OWNER not in ('PUBLIC')

    AND NOT EXISTS (SELECT 1

           FROM DBA_RECYCLEBIN B

          WHERE B.object_name = D.OBJECT_NAME

            AND D.OWNER = B.owner)

  GROUP BY D.OWNER, D.OBJECT_TYPE

  ORDER BY D.OWNER;

 

wps832B.tmp 

 

 

 

Two .1.3.5   Object details

----  The following data is exported to excel Form backup

 SELECT d.OWNER, d.OBJECT_NAME, d.SUBOBJECT_NAME, d.OBJECT_TYPE,d.status

  FROM dba_objects d

 WHERE d.OWNER   in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)

 ORDER BY D.OWNER ;


OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

STATUS

1

T

T1_IND


INDEX

VALID

2

T

TTT


TABLE

VALID

3

T

MONTH_PART

SYS_P65

TABLE PARTITION

VALID

4

T

MONTH_PART

SYS_P64

TABLE PARTITION

VALID

5

T

MONTH_PART

SYS_P63

TABLE PARTITION

VALID

6

T

MONTH_PART

SYS_P61

TABLE PARTITION

VALID

7

T

MONTH_PART


TABLE

VALID

8

T

T1


TABLE

VALID

9

T

PT1

PT1_20161001

TABLE PARTITION

VALID

10

T

PT1

PT1_20250918

TABLE PARTITION

VALID

11

T

PT1

PT1_20250620

TABLE PARTITION

VALID

12

T

PT1


TABLE

VALID

13

T

PT1_IND1


INDEX

VALID

14

T

PT2

PT1_20161001

TABLE PARTITION

VALID

15

T

PT2

PT1_20250918

TABLE PARTITION

VALID

16

T

PT2

PT1_20250620

TABLE PARTITION

VALID

17

T

PT2


TABLE

VALID

18

T

PT2_IND1


INDEX

VALID

19

T

MONTH_PART

PART2

TABLE PARTITION

VALID

20

T

MONTH_PART

PART1

TABLE PARTITION

VALID

21

TEST1

TEST


TABLE

VALID

22

TEST1

TEST_TABLE


TABLE

VALID

23

XPADAD

WH_CONCAT_IMPL_LHR


TYPE BODY

VALID

24

XPADAD

WH_CONCAT_IMPL_LHR


TYPE

VALID

25

XPADAD

TEST


TABLE

VALID

26

XPADAD

WH_CONCAT_LHR


FUNCTION

VALID

 

 

  SELECT d.owner,

       d.segment_name,

       d.partition_name,

       d.segment_type,

       d.tablespace_name,

       d.BYTES

  FROM dba_segments d

 WHERE d.OWNER  in ('T', 'XPADAD', 'TEST1')

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner) 

 ORDER BY D.OWNER ; 

 

 


OWNER

SEGMENT_NAME

PARTITION_NAME

SEGMENT_TYPE

TABLESPACE_NAME

BYTES

1

T

T1


TABLE

USERS

65536

2

T

PT2

PT1_20250918

TABLE PARTITION

USERS

8388608

3

T

PT1_IND1


INDEX

USERS

65536

4

T

PT2_IND1


INDEX

USERS

65536

5

T

TTT


TABLE

USERS

65536

6

T

PT1

PT1_20250620

TABLE PARTITION

USERS

8388608

7

T

PT1

PT1_20250918

TABLE PARTITION

USERS

8388608

8

T

PT1

PT1_20161001

TABLE PARTITION

USERS

8388608

9

T

PT2

PT1_20250620

TABLE PARTITION

USERS

8388608

10

T

T1_IND


INDEX

USERS

65536

11

T

PT2

PT1_20161001

TABLE PARTITION

USERS

8388608

12

T

MONTH_PART

PART1

TABLE PARTITION

USERS

8388608

13

T

MONTH_PART

PART2

TABLE PARTITION

USERS

8388608

14

T

MONTH_PART

SYS_P61

TABLE PARTITION

USERS

8388608

15

T

MONTH_PART

SYS_P63

TABLE PARTITION

USERS

8388608

16

T

MONTH_PART

SYS_P64

TABLE PARTITION

USERS

8388608

17

T

MONTH_PART

SYS_P65

TABLE PARTITION

USERS

8388608

18

TEST1

TEST


TABLE

TEST_USER1

9437184

19

TEST1

TEST_TABLE


TABLE

TEST_USER1

65536

20

XPADAD

TEST


TABLE

XPADDATA

9437184

 

 

 

 

 

Two .1.4   Invalid object case

 

 SELECT owner owner,

       count(1) 

  FROM dba_objects   d

 WHERE status <> 'VALID'

 and  d.OWNER  in ('T', 'XPADAD', 'TEST1') 

 AND D.OWNER NOT IN ('PUBLIC')

 group by d.OWNER

 ORDER BY owner;

 

 

 

 SELECT owner owner,

       object_name,

       object_type,

       status,

       'alter ' || decode(object_type,

                          'PACKAGE BODY',

                          'PACKAGE',

                          'TYPE BODY',

                          'TYPE',

                          object_type) || ' ' || owner || '.' ||

       object_name || ' ' ||

       decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' hands_on

  FROM dba_objects   d

 WHERE status <> 'VALID'

 and  d.OWNER in ('T', 'XPADAD', 'TEST1') 

 ORDER BY owner, object_name;

Two .1.5   The index case

 

SELECT D.OWNER,COUNT(1)

  FROM dba_indexes d

 WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.index_name ANDD.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

 

wps833C.tmp 

Two .1.6   Determine if there is business data 、 Scripts in, for example sys Users and so on under the default user

Confirm with open whether there is a business data table in sys Wait for the default user , If there is a need to migrate .

 

Two .2   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 , Conversion can be done on either the source side or the target side .

 

col platform_name for a40

select d.platform_name,tp.endian_format from vtransportableplatformtp,vtransportableplatformtp,vdatabase d

where tp.platform_name=d.platform_name;

 

col platform_name for a40

select tp.platform_name, tp.endian_format

  from v$transportable_platform tp

where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');

 

 

SQL> col platform_name for a40

SQL> select tp.platform_name, tp.endian_format

  2   from v$transportable_platform tp

  3   where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');

 

PLATFORM_NAME                            ENDIAN_FORMAT

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

AIX-Based Systems (64-bit)              Big

Linux x86 64-bit                        Little

 

SQL>

 

You can see source The byte order of the end is Big, and target The byte order of the end is Little, So we need to convert the byte order , 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 .

 

Two .3   Determine whether the set of table spaces is self-contained

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.

execute sys.dbms_tts.transport_set_check('TEST_USER1,USERS,XPADDATA',true);

col violations for a70

select * from sys.transport_set_violations;

 

oracle@ZDMTRAIN2:/oracle$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 17 16:59:34 2017

 

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, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SYS@oraSKY> exec  sys.dbms_tts.transport_set_check('TEST_USER1,USERS,XPADDATA',true);

 

PL/SQL procedure successfully completed.

 

SYS@oraSKY> col violations for a70

SYS@oraSKY> select * from sys.transport_set_violations;

 

no rows selected

 

SYS@oraSKY>

 

 

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 . This is also the case in the actual production environment , If it's a full database migration , The included table needs to be modified to .

 

Two .4   Generate a set of transportable table spaces

Two .4.1  rman Backup source library

Of course , If you have a full database backup, you can omit this step .

 

 

oracle@ZDMTRAIN2:/oracle$ mkdir -p /lxm/oracle_bk/

oracle@ZDMTRAIN2:/oracle$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 17:14:24 2017

 

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

 

connected to target database: ORASKY (DBID=98202371)

 

RMAN> backup  as compressed backupset  format  '/lxm/oracle_bk/full_%n_%T_%t_%s.bak' database include current controlfile plus archivelog delete input ;

 

 

Starting backup at 2017-02-17 17:14:34

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1716 device type=DISK

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=28 RECID=3 STAMP=936206075

channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:14:36

channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:14:37

piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206076_11.bak tag=TAG20170217T171436 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/oracle/app/oracle/product/11.2.0/db/dbs/arch1_28_1268916931.dbf RECID=3 STAMP=936206075

Finished backup at 2017-02-17 17:14:37

 

Starting backup at 2017-02-17 17:14:37

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA1/orasky/datafile/system.288.1268916951

input datafile file number=00002 name=+DATA1/orasky/datafile/sysaux.271.1268916981

input datafile file number=00003 name=+DATA1/orasky/datafile/undotbs1.270.1268917011

input datafile file number=00004 name=+DATA1/orasky/datafile/users.257.1268917057

input datafile file number=00005 name=+DATA1/orasky/datafile/xpaddata.258.917189283

input datafile file number=00006 name=+DATA1/orasky/datafile/xpaddata.330.917189289

input datafile file number=00007 name=+DATA1/orasky/datafile/xpaddata.323.917189295

input datafile file number=00008 name=+DATA1/orasky/datafile/xpaddata.320.917189301

input datafile file number=00009 name=+DATA1/orasky/datafile/xpaddata.307.917189307

input datafile file number=00010 name=+DATA1/orasky/datafile/xpaddata.301.917189313

input datafile file number=00011 name=+DATA1/orasky/datafile/xpaddata.298.917189319

input datafile file number=00012 name=+DATA1/orasky/datafile/xpaddata.261.917189327

input datafile file number=00013 name=+DATA1/orasky/datafile/xpaddata.259.917189333

input datafile file number=00014 name=+DATA1/orasky/datafile/xpaddata.262.917189339

input datafile file number=00015 name=+DATA1/orasky/datafile/xpadindex.269.917189671

input datafile file number=00016 name=+DATA1/orasky/datafile/xpadindex.263.917189713

input datafile file number=00017 name=+DATA1/orasky/datafile/xpadindex.264.917189751

input datafile file number=00018 name=+DATA1/orasky/datafile/xpadindex.265.917189757

input datafile file number=00019 name=+DATA1/orasky/datafile/xpadindex.266.917189763

input datafile file number=00020 name=+DATA1/orasky/datafile/xpadtemp.375.917189803

input datafile file number=00021 name=+DATA1/orasky/datafile/xpadtemp.376.917189809

input datafile file number=00022 name=+DATA1/orasky/datafile/test_user1.377.921670035

channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:14:39

channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:54

piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206078_12.bak tag=TAG20170217T171438 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:15:55

channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:56

piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206154_13.bak tag=TAG20170217T171438 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2017-02-17 17:15:56

 

Starting backup at 2017-02-17 17:15:56

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=29 RECID=4 STAMP=936206156

channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:15:56

channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:57

piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206156_14.bak tag=TAG20170217T171556 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/oracle/app/oracle/product/11.2.0/db/dbs/arch1_29_1268916931.dbf RECID=4 STAMP=936206156

Finished backup at 2017-02-17 17:15:57

 

RMAN> list backupset;

 

 

List of Backup Sets

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

 

 

BS Key  Size       Device Type Elapsed Time Completion Time   

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

6       2.50K      DISK        00:00:00     2017-02-17 17:14:36

        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TAG20170217T171436

        Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206076_11.bak

 

  List of Archived Logs in backup set 6

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    28      8892357    2017-02-17 17:13:28 8892419    2017-02-17 17:14:34

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   

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

7       Full    266.39M    DISK        00:01:13     2017-02-17 17:15:51

        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20170217T171438

        Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206078_12.bak

  List of Datafiles in backup set 7

  File LV Type Ckp SCN    Ckp Time            Name

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

  1       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/system.288.1268916951

  2       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/sysaux.271.1268916981

  3       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/undotbs1.270.1268917011

  4       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/users.257.1268917057

  5       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.258.917189283

  6       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.330.917189289

  7       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.323.917189295

  8       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.320.917189301

  9       Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.307.917189307

  10      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.301.917189313

  11      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.298.917189319

  12      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.261.917189327

  13      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.259.917189333

  14      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.262.917189339

  15      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.269.917189671

  16      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.263.917189713

  17      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.264.917189751

  18      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.265.917189757

  19      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.266.917189763

  20      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadtemp.375.917189803

  21      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadtemp.376.917189809

  22      Full 8892431    2017-02-17 17:14:39 +DATA1/orasky/datafile/test_user1.377.921670035

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   

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

8       Full    1.03M      DISK        00:00:01     2017-02-17 17:15:55

        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20170217T171438

        Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206154_13.bak

  SPFILE Included: Modification time: 2017-02-17 16:28:52

  SPFILE db_unique_name: ORASKY

  Control File Included: Ckp SCN: 8892462      Ckp time: 2017-02-17 17:15:54

 

BS Key  Size       Device Type Elapsed Time Completion Time   

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

9       2.00K      DISK        00:00:00     2017-02-17 17:15:56

        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20170217T171556

        Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206156_14.bak

 

  List of Archived Logs in backup set 9

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    29      8892419    2017-02-17 17:14:34 8892467    2017-02-17 17:15:56

 

RMAN> exit

 

 

Recovery Manager complete.

oracle@ZDMTRAIN2:/oracle$

 

Two .4.2  transport tablespace  Generate the file

oracle@ZDMTRAIN2:/oracle/transportdest$ df -g

oracle@ZDMTRAIN2:/oracle/transportdest$

oracle@ZDMTRAIN2:/oracle/app$ df -g        

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

/dev/hd4           6.00      2.29   62%    12356     3% /

《《《《........ The space for , With Ellipsis ........》》》》

/dev/Tlv_fta       8.00      7.74    4%     2627     1% /fta

/dev/fslv100       0.12      0.12    1%        9     1% /zling

/dev/lxmlv        20.00     19.73    2%       18     1% /lxm

22.188.189.42:/privatebk   8000.00   7954.59    1%     4381     1% /privatebk

oracle@ZDMTRAIN2:/oracle/app$

 

 

 

oracle@ZDMTRAIN2:/oracle$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 18:07:19 2017

 

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

 

connected to target database: ORASKY (DBID=98202371)

 

RMAN> transport tablespace TEST_USER1,USERS,XPADDATA tablespace destination '/lxm/transportdest' auxiliary destination '/lxm/transportdest';

 

using target database control file instead of recovery catalog

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

 

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

 

Creating automatic instance, with SID='wmch'

 

initialization parameters used for automatic instance:

db_name=ORASKY

db_unique_name=wmch_tspitr_ORASKY

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/lxm/transportdest

log_archive_dest_1='location=/lxm/transportdest'

#No auxiliary parameter file used

 

 

starting up automatic instance ORASKY

 

Oracle instance started

 

Total System Global Area     292278272 bytes

 

Fixed Size                     2220880 bytes

Variable Size                100666544 bytes

Database Buffers             184549376 bytes

Redo Buffers                   4841472 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

 

contents of Memory Script:

{

# set requested point in time

set until  scn 8915883;

# 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 command: SET until clause

 

Starting restore at 2017-02-17 18:07:39

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=80 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208508_21.bak

channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208508_21.bak tag=TAG20170217T175351

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/lxm/transportdest/ORASKY/controlfile/o1_mf_dbflvdrg_.ctl

Finished restore at 2017-02-17 18:07:41

 

sql statement: alter database mount clone database

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

# set requested point in time

set until  scn 8915883;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 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  22 to

"/lxm/transportdest/o1_mf_test_use_%u_.dbf";

set newname for datafile  4 to

"/lxm/transportdest/o1_mf_users_%u_.dbf";

set newname for datafile  5 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  6 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  7 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  8 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  9 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  10 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  11 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  12 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  13 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

set newname for datafile  14 to

"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";

# switch all tempfiles

switch clone tempfile all;

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

restore clone datafile  1, 3, 2, 22, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14;

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /lxm/transportdest/ORASKY/datafile/o1_mf_temp_%u_.tmp in control file

 

Starting restore at 2017-02-17 18:07:49

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /lxm/transportdest/ORASKY/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00022 to /lxm/transportdest/o1_mf_test_use_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /lxm/transportdest/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00008 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00009 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00010 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00011 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00012 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00013 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00014 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208432_20.bak

channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208432_20.bak tag=TAG20170217T175351

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:09

Finished restore at 2017-02-17 18:11:04

 

datafile 1 switched to datafile copy

input datafile copy RECID=16 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_system_dbflvvqx_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_dbflvvr9_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=18 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_dbflvvr1_.dbf

datafile 22 switched to datafile copy

input datafile copy RECID=19 STAMP=936209464 file name=/lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=20 STAMP=936209464 file name=/lxm/transportdest/o1_mf_users_dbflvvv1_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=21 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=22 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=23 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=24 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=25 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=26 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=27 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=28 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=29 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=30 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  scn 8915883;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  22 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  6 online";

sql clone "alter database datafile  7 online";

sql clone "alter database datafile  8 online";

sql clone "alter database datafile  9 online";

sql clone "alter database datafile  10 online";

sql clone "alter database datafile  11 online";

sql clone "alter database datafile  12 online";

sql clone "alter database datafile  13 online";

sql clone "alter database datafile  14 online";

# recover and open resetlogs

recover clone database tablespace  "TEST_USER1", "USERS", "XPADDATA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  1 online

 

sql statement: alter database datafile  3 online

 

sql statement: alter database datafile  2 online

 

sql statement: alter database datafile  22 online

 

sql statement: alter database datafile  4 online

 

sql statement: alter database datafile  5 online

 

sql statement: alter database datafile  6 online

 

sql statement: alter database datafile  7 online

 

sql statement: alter database datafile  8 online

 

sql statement: alter database datafile  9 online

 

sql statement: alter database datafile  10 online

 

sql statement: alter database datafile  11 online

 

sql statement: alter database datafile  12 online

 

sql statement: alter database datafile  13 online

 

sql statement: alter database datafile  14 online

 

Starting recover at 2017-02-17 18:11:06

using channel ORA_AUX_DISK_1

 

starting media recovery

 

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=36

channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208510_22.bak

channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208510_22.bak tag=TAG20170217T175510

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/lxm/transportdest/1_36_1268916931.dbf thread=1 sequence=36

channel clone_default: deleting archived log(s)

archived log file name=/lxm/transportdest/1_36_1268916931.dbf RECID=11 STAMP=936209471

media recovery complete, elapsed time: 00:00:00

Finished recover at 2017-02-17 18:11:12

 

database opened

 

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  TEST_USER1 read only';

sql clone 'alter tablespace  USERS read only';

sql clone 'alter tablespace  XPADDATA read only';

# create directory for datapump export

sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''

/lxm/transportdest''";

}

executing Memory Script

 

sql statement: alter tablespace  TEST_USER1 read only

 

sql statement: alter tablespace  USERS read only

 

sql statement: alter tablespace  XPADDATA read only

 

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/lxm/transportdest''

 

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_wmch": 

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_wmch" successfully loaded/unloaded

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

   EXPDP> Dump file set for SYS.TSPITR_EXP_wmch is:

   EXPDP>   /lxm/transportdest/dmpfile.dmp

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

   EXPDP> Datafiles required for transportable tablespace TEST_USER1:

   EXPDP>   /lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf

   EXPDP> Datafiles required for transportable tablespace USERS:

   EXPDP>   /lxm/transportdest/o1_mf_users_dbflvvv1_.dbf

   EXPDP> Datafiles required for transportable tablespace XPADDATA:

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf

   EXPDP>   /lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_wmch" successfully completed at 18:13:03

Export completed

 

/*

   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= /lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf, /lxm/transportdest/o1_mf_users_dbflvvv1_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf

*/

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

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

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

-- creating directory objects

CREATE DIRECTORY STREAMSDIROBJDIROBJ1 AS  '/lxm/transportdest/';

CREATE DIRECTORY STREAMSDIROBJDIROBJDPDIR AS  '/lxm/transportdest';

/* 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_test_use_dbflvw0f_.dbf';

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

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

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

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

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

  tbs_files( 4).file_name :=  'o1_mf_xpaddata_dbflvw2s_.dbf';

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

  tbs_files( 5).file_name :=  'o1_mf_xpaddata_dbflvw3p_.dbf';

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

  tbs_files( 6).file_name :=  'o1_mf_xpaddata_dbflvwhy_.dbf';

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

  tbs_files( 7).file_name :=  'o1_mf_xpaddata_dbflvwpy_.dbf';

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

  tbs_files( 8).file_name :=  'o1_mf_xpaddata_dbflvwrv_.dbf';

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

  tbs_files( 9).file_name :=  'o1_mf_xpaddata_dbflvx6o_.dbf';

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

  tbs_files( 10).file_name :=  'o1_mf_xpaddata_dbflvxgk_.dbf';

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

  tbs_files( 11).file_name :=  'o1_mf_xpaddata_dbflvxjw_.dbf';

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

  tbs_files( 12).file_name :=  'o1_mf_xpaddata_dbflvy06_.dbf';

  tbs_files( 12).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

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

 

Removing automatic instance

shutting down automatic instance

database closed

database dismounted

Oracle instance shut down

Automatic instance removed

auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_temp_dbfm2j8n_.tmp deleted

auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_3_dbfm29jx_.log deleted

auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_2_dbfm258v_.log deleted

auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_1_dbfm20wr_.log deleted

auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_dbflvvr1_.dbf deleted

auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_dbflvvr9_.dbf deleted

auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_system_dbflvvqx_.dbf deleted

auxiliary instance file /lxm/transportdest/ORASKY/controlfile/o1_mf_dbflvdrg_.ctl deleted

 

RMAN>

 

--------- Execute the process to see the size of the file

 

root@ZDMTRAIN2:/# df -g

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

《《《《........ The space for , With Ellipsis ........》》》》

/dev/fslv100       0.12      0.12    1%        9     1% /zling

/dev/lxmlv        20.00      5.78   72%       43     1% /lxm

22.188.189.42:/privatebk   8000.00   7954.59    1%     4381     1% /privatebk

22.188.189.42:/publicbk   8000.00    186.82   98% 21670216    34% /publicbk

ZTDNETAP3:/nfs   1240.00     25.15   98%   509154     8% /nfs

22.188.129.202:/nfs   1240.00     25.15   98%   509154     8% /nfs

root@ZDMTRAIN2:/#

 

 

File size when done :

root@ZDMTRAIN2:/lxm/transportdest# df -g

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

《《《《........ The space for , With Ellipsis ........》》》》

/dev/Tlv_fta       8.00      7.74    4%     2627     1% /fta

/dev/fslv100       0.12      0.12    1%        9     1% /zling

/dev/lxmlv        20.00     19.29    4%       36     1% /lxm

22.188.189.42:/privatebk   8000.00   7954.59    1%     4381     1% /privatebk

22.188.189.42:/publicbk   8000.00    186.82   98% 21670216    34% /publicbk

/dev/Tlv_zca       4.00      4.00    1%       17     1% /zca

/dev/Tlv_tt       10.00      9.05   10%       18     1% /tt

ZTINIMSERVER:/sharebkup   5500.00   1629.20   71%  2455829     1% /sharebkup

ZTDNETAP3:/nfs   1240.00     25.15   98%   509154     8% /nfs

22.188.129.202:/nfs   1240.00     25.15   98%   509154     8% /nfs

root@ZDMTRAIN2:/lxm/transportdest#

 

 

 

thus , There is no relationship between source and library .

Two .5   Transfer data files and metadata to target End

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

wps835C.tmp 

wps835D.tmp 

wps836D.tmp 

 

Two .5.1  dbca establish target library

 

[oracle@rhel6_lhr dbca]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oraSKY -sid oraSKY -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'FRA/' -storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA'  -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 20 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true

Copying database files

1% complete

3% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oraSKY/oraSKY.log" for further details.

[oracle@rhel6_lhr dbca]$ ORACLE_SID=oraSKY

[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wednesday 2 month 3 00:14:49 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

 

00:14:49 SYS@oraSKY > archive log list;

Database logging mode             Archive Mode

Auto archive              Enable

End of archive             USE_DB_RECOVERY_FILE_DEST

The earliest online log sequence      3

Next archive log sequence    5

Current log sequence            5

00:14:53 SYS@oraSKY >

 

Two .5.2   View the target library data file location and Import Directory

 

[oracle@rhel6_lhr dbs]echoechoORACLE_SID

oraSKY

[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tuesday 2 month 2 22:58:34 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

 

00:14:53 SYS@oraSKY >  select name from v$datafile;

 

NAME

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

+DATA/orasky/datafile/system.295.902793257

+DATA/orasky/datafile/sysaux.294.902793261

+DATA/orasky/datafile/undotbs1.293.902793263

+DATA/orasky/datafile/users.292.902793265

+DATA/orasky/datafile/example.274.902793775

 

Elapsed time :  00: 00: 00.03

00:15:31 SYS@oraSKY >

 

SYS@oraSKY >  select directory_name,directory_path from dba_directories;

 

DIRECTORY_NAME                    DIRECTORY_PATH

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

SUBDIR                            /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SS_OE_XMLDIR                      /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

LOG_FILE_DIR                      /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

MEDIA_DIR                         /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

XMLDIR                            /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

DATA_FILE_DIR                     /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

DATA_PUMP_DIR                     /u01/app/oracle/admin/oraSKY/dpdump/

ORACLE_OCM_CONFIG_DIR             /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

Have chosen 8 That's ok .

 

 

Two .5.3   utilize ftp The tool transfers the metadata to the target library

wps837E.tmp 

 

Two .5.4   Copy the file to the corresponding location of the target library and modify the file permissions

 

[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/oraSKY/dpdump

[root@rhel6_lhr dpdump]# ll

total 461108

-rw-r--r-- 1 root root    204800 Feb 17  2017 dmpfile.dmp

-rw-r--r-- 1 root root      3943 Feb 17  2017 impscrpt.sql

-rw-r--r-- 1 root root 104865792 Feb 17  2017 o1_mf_test_use_dbflvw0f_.dbf

-rw-r--r-- 1 root root 314580992 Feb 17  2017 o1_mf_users_dbflvvv1_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw2j_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw2s_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw3p_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwhy_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwpy_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwrv_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvx6o_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvxgk_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvxjw_.dbf

-rw-r--r-- 1 root root   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvy06_.dbf

[root@rhel6_lhr dpdump]# chown oracle:dba *

[root@rhel6_lhr dpdump]# ll

total 461108

-rw-r--r-- 1 oracle dba    204800 Feb 17  2017 dmpfile.dmp

-rw-r--r-- 1 oracle dba      3943 Feb 17  2017 impscrpt.sql

-rw-r--r-- 1 oracle dba 104865792 Feb 17  2017 o1_mf_test_use_dbflvw0f_.dbf

-rw-r--r-- 1 oracle dba 314580992 Feb 17  2017 o1_mf_users_dbflvvv1_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw2j_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw2s_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvw3p_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwhy_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwpy_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvwrv_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvx6o_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvxgk_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvxjw_.dbf

-rw-r--r-- 1 oracle dba   5251072 Feb 17  2017 o1_mf_xpaddata_dbflvy06_.dbf

[root@rhel6_lhr dpdump]#

 

 

Two .6  target End to end conversion byte order

 

 

[oracle@rhel6_lhr dbca]$ rman target /

 

Recovery Manager : Release 11.2.0.3.0 - Production on Wednesday 2 month 3 00:24:06 2016

 

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

 

Connected to target database : ORASKY (DBID=4027046368)

 

RMAN> CONVERT DATAFILE

2> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf",

3> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf",

4> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf",

5> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf",

6> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf",

7> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf",

8> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf",

9> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf",

10> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf",

11> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf",

12> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf",

13> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf"

14> TO PLATFORM="Linux x86 64-bit"

15> FROM PLATFORM="AIX-Based Systems (64-bit)"

16> FORMAT '+DATA';

 

start-up conversion at target On 2016-02-03 00:24:09

Replace the recovery directory with the target database control file

Assigned channels : ORA_DISK_1

passageway ORA_DISK_1: SID=147 Device type =DISK

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf

Converted data file = +DATA/orasky/datafile/users.280.902795051

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:45

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf

Converted data file = +DATA/orasky/datafile/test_user1.278.902795095

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:25

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.277.902795121

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.276.902795121

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.275.902795123

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:02

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.270.902795125

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.267.902795125

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.268.902795127

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.281.902795127

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.296.902795129

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:04

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.297.902795133

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

passageway ORA_DISK_1: Start data file conversion

Input file name =/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf

Converted data file = +DATA/orasky/datafile/xpaddata.298.902795133

passageway ORA_DISK_1: Data file conversion complete , After time : 00:00:01

complete conversion at target On 2016-02-03 00:25:34

 

RMAN>

 

 

 

[grid@rhel6_lhr ~]$ asmcmd

[grid@rhel6_lhr asmdisk]$ cd

[grid@rhel6_lhr ~]$ asmcmd

ASMCMD> cd +data/ORASKY/datafile

ASMCMD> ls -lt

Type      Redund  Striped  Time             Sys  Name

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.298.902795133

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.297.902795133

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.296.902795129

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.281.902795127

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.277.902795121

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.276.902795121

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.275.902795123

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.270.902795125

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.268.902795127

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    XPADDATA.267.902795125

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    USERS.292.902793265

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    USERS.280.902795051

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    UNDOTBS1.293.902793263

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    TEST_USER1.278.902795095

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    SYSTEM.295.902793257

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    SYSAUX.294.902793261

DATAFILE  MIRROR  COARSE   FEB 03 00:00:00  Y    EXAMPLE.274.902793775

ASMCMD>

 

ASMCMD>

 

 

Two .7   Start import

Two .7.1   establish source The library needs to be migrated 3 Users and permissions ( The previous script has been generated , Take it directly and execute it )

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

 

create user TEST1 identified by TEST1  TEMPORARY TABLESPACE  TEMP;

GRANT UNLIMITED TABLESPACE TO TEST1;

GRANT CONNECT TO TEST1;

GRANT RESOURCE TO TEST1;

GRANT WRITE ON SYS.TEST_DIR TO TEST1;

GRANT READ ON SYS.TEST_DIR TO TEST1;

GRANT WRITE ON SYS.TEST_LOG TO TEST1;

GRANT READ ON SYS.TEST_LOG TO TEST1;

create user XPADAD identified by XPADAD TEMPORARY TABLESPACE  TEMP;

GRANT CREATE VIEW TO XPADAD;

GRANT UNLIMITED TABLESPACE TO XPADAD;

GRANT CREATE DATABASE LINK TO XPADAD;

GRANT DBA TO XPADAD;

GRANT CONNECT TO XPADAD;

GRANT RESOURCE TO XPADAD;

create user T identified by T default TEMPORARY TABLESPACE  TEMP;

GRANT UNLIMITED TABLESPACE TO T;

GRANT RESOURCE TO T;

GRANT CONNECT TO T;

GRANT WRITE ON SYS.TT TO T;

GRANT READ ON SYS.TT TO T;

 

Two .7.2   Start import

 

 

[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.292.902793265','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log

 

Import: Release 11.2.0.3.0 - Production on Wednesday 2 month 3 00:35:45 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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

Successfully loaded / The main table is unloaded "SYS"."SYS_IMPORT_TRANSPORTABLE_01"

start-up "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.292.902793265,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log

Handle object types TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data pump transportable table space job aborted

ORA-29349: Table space 'USERS' Already exists

 

Homework "SYS"."SYS_IMPORT_TRANSPORTABLE_01" Because of a fatal mistake in 00:35:50 stop it

 

 

users The table space already exists , Here is the target Terminal users Just rename the table space :

 

[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wednesday 2 month 3 00:36:26 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

 

 

SYS@oraSKY > alter tablespace users rename to users01;

 

The table space has changed .

 

SYS@oraSKY > exit

from 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 To break off

 

[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.280.902795051','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log

 

Import: Release 11.2.0.3.0 - Production on Wednesday 2 month 3 00:40:46 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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

Successfully loaded / The main table is unloaded "SYS"."SYS_IMPORT_TRANSPORTABLE_01"

start-up "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.280.902795051,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log

Handle object types TRANSPORTABLE_EXPORT/PLUGTS_BLK

Handle object types TRANSPORTABLE_EXPORT/TABLE

ORA-39151: surface "SCOTT"."EMP" Already exists . Because of skipping table_exists_action, All relevant metadata and data will be skipped .

Handle object types TRANSPORTABLE_EXPORT/INDEX/INDEX

Handle object types TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Handle object types TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Homework "SYS"."SYS_IMPORT_TRANSPORTABLE_01" Already completed , But there are 1 A mistake ( On 00:40:51 complete )

 

[oracle@rhel6_lhr dbca]$

[oracle@rhel6_lhr dbca]$

 

 

[ZFXDESKDB2:oracle]:/oracle>

 

Two .7.2.1   Report errors :source and target Of compatible Different parameters lead to ora-00721 error

[oracle@rhel6_lhr dbs]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/orclasm/datafile/app1tbs.271.90278175','+DATA/orclasm/datafile/APP2TBS.276.902781757','+DATA/orclasm/datafile/IDXTBS.279.902781761' LOGFILE=impdp_tts_20160202.log  version=latest

 

Import: Release 11.2.0.3.0 - Production on Tuesday 2 month 2 21:04:29 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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

Successfully loaded / The main table is unloaded "SYS"."SYS_IMPORT_TRANSPORTABLE_01"

start-up "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/orclasm/datafile/app1tbs.271.90278175,+DATA/orclasm/datafile/APP2TBS.276.902781757,+DATA/orclasm/datafile/IDXTBS.279.902781761 LOGFILE=impdp_tts_20160202.log version=latest

Handle object types TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data pump transportable table space job aborted

ORA-00721: Distribution version 11.2.0.4.0 Changes in cannot be used for distribution 11.2.0.3.0

 

Homework "SYS"."SYS_IMPORT_TRANSPORTABLE_01" Because of a fatal mistake in 21:04:37 stop it

 

[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tuesday 2 month 2 21:04:58 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

 

21:04:58 SYS@orclasm > show parameter com

 

NAME                                 TYPE        VALUE

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

cell_offload_compaction              string      ADAPTIVE

commit_logging                       string

commit_point_strength                integer     1

commit_wait                          string

commit_write                         string

compatible                           string      11.2.0.3.0

nls_comp                             string      BINARY

plsql_v2_compatibility               boolean     FALSE

21:05:03 SYS@orclasm >

 

terms of settlement : keep source and target Consistent versions of , or source The end is less than or equal to target End , If the versions are the same , The modified target Terminal compatible Parameters and source End consistent .

 

Two .7.3   View target platform information

 

[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wednesday 2 month 3 00:42:23 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

 

SYS@oraSKY > select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                                              STATUS

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

SYSTEM                                                       ONLINE

SYSAUX                                                       ONLINE

UNDOTBS1                                                     ONLINE

TEMP                                                         ONLINE

USERS01                                                      ONLINE

EXAMPLE                                                      ONLINE

TEST_USER1                                                   READ ONLY

USERS                                                        READ ONLY

XPADDATA                                                     READ ONLY

 

Have chosen 9 That's ok .

 

SYS@oraSKY > alter tablespace  TEST_USER1 read write;

 

The table space has changed .

 

SYS@oraSKY > alter tablespace  USERS read write;

 

The table space has changed .

 

SYS@oraSKY > alter tablespace  XPADDATA read write;

 

The table space has changed .

 

SYS@oraSKY > select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                                              STATUS

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

SYSTEM                                                       ONLINE

SYSAUX                                                       ONLINE

UNDOTBS1                                                     ONLINE

TEMP                                                         ONLINE

USERS01                                                      ONLINE

EXAMPLE                                                      ONLINE

TEST_USER1                                                   ONLINE

USERS                                                        ONLINE

XPADDATA                                                     ONLINE

 

Have chosen 9 That's ok .

 

 

 

Two .8   Check the result after importing

 

Two .8.1   Verify user information ( password 、 Default tablespace 、 Roles and permissions , Migration required schema Object size 、 Number 、 list )

Two .8.1.1   Verify user

 

SELECT d.username,

       d.default_tablespace,

       D.temporary_tablespace,

       d.account_status

  FROM dba_users d

 WHERE d.account_status = 'OPEN'

   and d.username in ('T','TEST1','XPADAD');

wps837F.tmp 

SQL> alter user T default tablespace users;

 

User altered.

 

SQL> alter user XPADAD default tablespace XPADDATA;

 

User altered.

 

SQL> alter user TEST1 default tablespace TEST_USER1;

 

User altered.

 

SQL>

wps8390.tmp 

 

 

Two .8.1.2   Number of user objects

 

SELECT D.OWNER,COUNT(1)

  FROM dba_objects d

 WHERE d.OWNER   in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

wps8391.tmp 

 

 

 SELECT D.OWNER, D.OBJECT_TYPE, COUNT(1)

   FROM dba_objects d

  WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')

    and d.OWNER not in ('PUBLIC')

    AND NOT EXISTS (SELECT 1

           FROM DBA_RECYCLEBIN B

          WHERE B.object_name = D.OBJECT_NAME

            AND D.OWNER = B.owner)

  GROUP BY D.OWNER, D.OBJECT_TYPE

  ORDER BY D.OWNER;

 

wps8392.tmp 

 

 

 

Two .8.1.3   Object details

----  The following data is exported to excel Form backup

SELECT d.OWNER, d.OBJECT_NAME, d.SUBOBJECT_NAME, d.OBJECT_TYPE,d.status

  FROM dba_objects d

 WHERE d.OWNER   in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)

 ORDER BY D.OWNER ;


OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

STATUS

1

T

T1_IND


INDEX

VALID

2

T

TTT


TABLE

VALID

3

T

MONTH_PART

SYS_P65

TABLE PARTITION

VALID

4

T

MONTH_PART

SYS_P64

TABLE PARTITION

VALID

5

T

MONTH_PART

SYS_P63

TABLE PARTITION

VALID

6

T

MONTH_PART

SYS_P61

TABLE PARTITION

VALID

7

T

MONTH_PART


TABLE

VALID

8

T

T1


TABLE

VALID

9

T

PT1

PT1_20161001

TABLE PARTITION

VALID

10

T

PT1

PT1_20250918

TABLE PARTITION

VALID

11

T

PT1

PT1_20250620

TABLE PARTITION

VALID

12

T

PT1


TABLE

VALID

13

T

PT1_IND1


INDEX

VALID

14

T

PT2

PT1_20161001

TABLE PARTITION

VALID

15

T

PT2

PT1_20250918

TABLE PARTITION

VALID

16

T

PT2

PT1_20250620

TABLE PARTITION

VALID

17

T

PT2


TABLE

VALID

18

T

PT2_IND1


INDEX

VALID

19

T

MONTH_PART

PART2

TABLE PARTITION

VALID

20

T

MONTH_PART

PART1

TABLE PARTITION

VALID

21

TEST1

TEST


TABLE

VALID

22

TEST1

TEST_TABLE


TABLE

VALID

23

XPADAD

WH_CONCAT_IMPL_LHR


TYPE BODY

VALID

24

XPADAD

WH_CONCAT_IMPL_LHR


TYPE

VALID

25

XPADAD

TEST


TABLE

VALID

26

XPADAD

WH_CONCAT_LHR


FUNCTION

VALID

 

 

  SELECT d.owner,

       d.segment_name,

       d.partition_name,

       d.segment_type,

       d.tablespace_name,

       d.BYTES

  FROM dba_segments d

 WHERE d.OWNER  in ('T', 'XPADAD', 'TEST1')

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner) 

 ORDER BY D.OWNER ; 

 

 


OWNER

SEGMENT_NAME

PARTITION_NAME

SEGMENT_TYPE

TABLESPACE_NAME

BYTES

1

T

T1


TABLE

USERS

65536

2

T

PT2

PT1_20250918

TABLE PARTITION

USERS

8388608

3

T

PT1_IND1


INDEX

USERS

65536

4

T

PT2_IND1


INDEX

USERS

65536

5

T

TTT


TABLE

USERS

65536

6

T

PT1

PT1_20250620

TABLE PARTITION

USERS

8388608

7

T

PT1

PT1_20250918

TABLE PARTITION

USERS

8388608

8

T

PT1

PT1_20161001

TABLE PARTITION

USERS

8388608

9

T

PT2

PT1_20250620

TABLE PARTITION

USERS

8388608

10

T

T1_IND


INDEX

USERS

65536

11

T

PT2

PT1_20161001

TABLE PARTITION

USERS

8388608

12

T

MONTH_PART

PART1

TABLE PARTITION

USERS

8388608

13

T

MONTH_PART

PART2

TABLE PARTITION

USERS

8388608

14

T

MONTH_PART

SYS_P61

TABLE PARTITION

USERS

8388608

15

T

MONTH_PART

SYS_P63

TABLE PARTITION

USERS

8388608

16

T

MONTH_PART

SYS_P64

TABLE PARTITION

USERS

8388608

17

T

MONTH_PART

SYS_P65

TABLE PARTITION

USERS

8388608

18

TEST1

TEST


TABLE

TEST_USER1

9437184

19

TEST1

TEST_TABLE


TABLE

TEST_USER1

65536

20

XPADAD

TEST


TABLE

XPADDATA

9437184

 

 

 

 

 

Two .8.2   Invalid object case

 

 SELECT owner owner,

       count(1) 

  FROM dba_objects   d

 WHERE status <> 'VALID'

 and  d.OWNER  in ('T', 'XPADAD', 'TEST1') 

 AND D.OWNER NOT IN ('PUBLIC')

 group by d.OWNER

 ORDER BY owner;

 

 

 

 SELECT owner owner,

       object_name,

       object_type,

       status,

       'alter ' || decode(object_type,

                          'PACKAGE BODY',

                          'PACKAGE',

                          'TYPE BODY',

                          'TYPE',

                          object_type) || ' ' || owner || '.' ||

       object_name || ' ' ||

       decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' hands_on

  FROM dba_objects   d

 WHERE status <> 'VALID'

 and  d.OWNER in ('T', 'XPADAD', 'TEST1') 

 ORDER BY owner, object_name;

Two .8.3   The index case

 

 SELECT D.OWNER,COUNT(1)

  FROM dba_indexes d

 WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')

 and d.OWNER not in ('PUBLIC') 

 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.index_name ANDD.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

 

wps83A2.tmp 

 

Two .9   Migration follow-up close out

After ensuring that the data has been completely migrated to the new host , And then there's some little finishing work , Include sys password , monitor ,job,crontab Wait for work .

 

 

 

 

 

 

 

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

 

 

Two .10   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 .