ITPub博客

首页 > 数据库 > Oracle > 【TTS】AIX->Linux--基于RMAN(真实环境)--续

【TTS】AIX->Linux--基于RMAN(真实环境)--续

原创 Oracle 作者:lhrbest 时间:2016-02-12 12:04:26 0 删除 编辑

【TTS】AIX平台数据库迁移到Linux--基于RMAN(真实环境)


     本篇接上文:【TTS】AIX平台数据库迁移到Linux--基于RMAN(真实环境)  http://blog.itpub.net/26736162/viewspace-1987971/

 

 

 

1  target端转换字节序

 

 

[oracle@rhel6_lhr dbca]$ rman target /

 

恢复管理器: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:24:06 2016

 

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

 

已连接到目标数据库: 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';

 

启动 conversion at target 于 2016-02-03 00:24:09

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: SID=147 设备类型=DISK

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf

已转换的数据文件 = +DATA/orasky/datafile/users.280.902795051

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:45

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf

已转换的数据文件 = +DATA/orasky/datafile/test_user1.278.902795095

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:25

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.277.902795121

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.276.902795121

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.275.902795123

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:02

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.270.902795125

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.267.902795125

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.268.902795127

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.281.902795127

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.296.902795129

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.297.902795133

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

通道 ORA_DISK_1: 启动数据文件转换

输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf

已转换的数据文件 = +DATA/orasky/datafile/xpaddata.298.902795133

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01

完成 conversion at target 于 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>

 

 

一.2  开始导入

一.2.1  创建source库的需要迁移的3个用户并赋权限(前边的脚本已经生成,直接拿过来执行)

如果不创建用户会报如下的错误:

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;

 

一.2.2  开始导入

 

 

[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 星期三 2月 3 00:35:45 2016

 

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

 

连接到: 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"."SYS_IMPORT_TRANSPORTABLE_01"

启动 "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

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: 数据泵可传输的表空间作业中止

ORA-29349: 表空间 'USERS' 已存在

 

作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 00:35:50 停止

 

 

users表空间已经存在了,这里把target端的users表空间重命名一下就可以了:

 

[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:36:26 2016

 

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

 

 

连接到:

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;

 

表空间已更改。

 

SYS@oraSKY > exit

从 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 断开

 

[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 星期三 2月 3 00:40:46 2016

 

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

 

连接到: 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"."SYS_IMPORT_TRANSPORTABLE_01"

启动 "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

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

处理对象类型 TRANSPORTABLE_EXPORT/TABLE

ORA-39151: 表 "SCOTT"."EMP" 已存在。由于跳过了 table_exists_action, 将跳过所有相关元数据和数据。

处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX

处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS

处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已经完成, 但是有 1 个错误 (于 00:40:51 完成)

 

[oracle@rhel6_lhr dbca]$

[oracle@rhel6_lhr dbca]$

 

 

[ZFXDESKDB2:oracle]:/oracle>

 

一.2.2.1  报错:sourcetargetcompatible参数不同引起ora-00721错误

[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 星期二 2月 2 21:04:29 2016

 

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

 

连接到: 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"."SYS_IMPORT_TRANSPORTABLE_01"

启动 "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

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: 数据泵可传输的表空间作业中止

ORA-00721: 发行版 11.2.0.4.0 中的更改无法用于发行版 11.2.0.3.0

 

作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 21:04:37 停止

 

[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 2 21:04:58 2016

 

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

 

 

连接到:

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 >

 

解决办法:保持sourcetarget的版本一致,或source端小于等于target端,若版本一致,则修改target端的compatible参数和source端一致。

 

一.2.3  查看目标平台信息

 

[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:42:23 2016

 

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

 

 

连接到:

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

 

已选择9行。

 

SYS@oraSKY > alter tablespace  TEST_USER1 read write;

 

表空间已更改。

 

SYS@oraSKY > alter tablespace  USERS read write;

 

表空间已更改。

 

SYS@oraSKY > alter tablespace  XPADDATA read write;

 

表空间已更改。

 

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

 

已选择9行。

 

 

 

一.3  导入完成后的结果校验

 

一.3.1  校验用户情况密码、默认表空间、角色和权限,需迁移的schema对象大小、个数、列表

一.3.1.1  校验用户

 

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');

wps5BF1.tmp[4] 

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>

wps5BF2.tmp[4] 

 

 

一.3.1.2  用户对象个数

 

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 AND D.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

wps5C03.tmp[4] 

 

 

 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;

 

wps5C04.tmp[4] 

 

 

 

一.3.1.3  对象详细信息

---- 以下数据导出到excel表格备份

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 AND D.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 AND D.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

 

 

 

 

 

一.3.2  无效对象情况

 

 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;

一.3.3  索引情况

 

 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 AND D.OWNER=B.owner) 

 GROUP BY D.OWNER

 ORDER BY D.OWNER ;

 

wps5C14.tmp[4] 

 

一.4  迁移后续收尾工作

确保数据已经完全迁移到新的主机上后,接下来就是一些琐碎的收尾工作,包括sys密码,监听,jobcrontab等等工作。

 

 

 

 

 

 

 

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

 

 

一.5  总结

 

到此所有的处理算是基本完毕,过程很简单,但是不同的场景处理方式有很多种,我们应该学会灵活变通。

 

 

 

一.6  About Me

 

...........................................................................................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

ITPUB BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1987971/

本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b)

QQ:642808185 若加QQ请注明所正在读的文章标题

2016-01-26 10:00~ 2016-02-06 19:00 在中行完成

<版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任!>

...........................................................................................................................................................................................

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1987974/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1350
  • 访问量
    8123826