签到成功

知道了

CNDBA社区CNDBA社区

验证数据泵排除index下是否可Rebuild

2018-04-29 18:04 2888 0 原创 数据库迁移
作者： Marvinn
原文链接：http://www.cndba.cn/Marvinn
本文链接：http://www.cndba.cn/Marvinn/article/2757

实验目的：验证在全库或者某个SCHEMA数据泵导出情况下，

​ 1、源端导出完整Dump文件，目标端导入排除索引http://www.cndba.cn/Marvinn/article/2757http://www.cndba.cn/Marvinn/article/2757

​ 2、源端导出Dump排除索引,目标端完整导入

上述两情况下，归根揭底为排除索引情况下，目标端是否可以直接Rebuild重建索引….

http://www.cndba.cn/Marvinn/article/2757

答案是 NO…..不可以 因为索引信息 已经从表上剥离，不能重新Rebuild,只能重新导出创建索引脚本，目标端重新Createhttp://www.cndba.cn/Marvinn/article/2757http://www.cndba.cn/Marvinn/article/2757

http://www.cndba.cn/Marvinn/article/2757 


10:36:18 SYS@orcl>alter session set container=cndba;

Session altered.

Elapsed: 00:00:00.14

10:48:02 SYS@orcl>create or replace directory dp_dir as  '/u01/backup';

Directory created.

Elapsed: 00:00:00.01

10:48:37 SYS@orcl>grant read,write on directory dp_dir to system;

Grant succeeded.

Elapsed: 00:00:00.03

创建测试数据http://www.cndba.cn/Marvinn/article/2757

10:50:29 SYS@orcl>conn marvin/marvin@cndba
Connected.

10:57:18 MARVIN@cndba>create table cndba as select * from dba_objects;

Table created.

Elapsed: 00:00:06.32

11:02:52 MARVIN@cndba>create index IDX_CNDBA on cndba(OBJECT_ID);

Index created.

Elapsed: 00:00:00.24

导出12C Singlehttp://www.cndba.cn/Marvinn/article/2757

oracle@12cDG:/u01>expdp system/yunq111@cndba directory=dp_dir dumpfile=fulldb_cndba.dmp logfile=expdp.log full=y exclude=index,statistics parallel=2

Export: Release 12.2.0.1.0 - Production on Sat Apr 28 11:06:48 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
导出报错：
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12098
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 9039
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 3423
ORA-06512: at "SYS.DBMS_METADATA", line 4760
ORA-06512: at "SYS.DBMS_METADATA", line 5079
ORA-06512: at "SYS.DBMS_METADATA", line 9020
ORA-06512: at "SYS.KUPW$WORKER", line 14367

发现MOS资料，文档2369062.1,大致意思是当前PDB是新插入到CDB中的，并且PDB中视图 view KU$_DV_ISR_VIEW 未发现，公共对象(sharing=metadata)应该同时存在于CDB以及PDB，但是新插入的PDB中并没有存在，所以导致报错
CDB:
11:34:52 > select sharing from dba_objects where object_name='KU$_DV_ISR_VIEW';

SHARING
------------------------------------
METADATA LINK

Elapsed: 00:00:00.25

PDB:
11:24:36 SYS@orcl>alter session set container=cndba;

Session altered.

Elapsed: 00:00:00.30
11:35:56 SYS@orcl>
11:35:57 SYS@orcl>select sharing from dba_objects where object_name='KU$_DV_ISR_VIEW';

no rows selected

Elapsed: 00:00:00.29
11:36:09 SYS@orcl>select * from DVSYS.KU$_DV_ISR_VIEW;
select * from DVSYS.KU$_DV_ISR_VIEW
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.01

解决方式：在当前PDB中手动安装OLS, DV 组件，参考MOS(NOTE:2362597.1)，方法如下：
1、登录PDB
11:24:36 SYS@orcl>alter session set container=cndba;

Session altered.
2、Install Oracle Label Security by executing the catols.sql script.

SQL>@$ORACLE_HOME/rdbms/admin/catols.sql

3. Install Oracle Database Vault by executing the catmac.sql script.

SQL>@$ORACLE_HOME/rdbms/admin/catmac.sql

At the Enter value for 1 prompt, enter the default tablespace for the PDB.
At the Enter value for 2 prompt, enter the temporary tablespace for the PDB.

查看临时表空间值位置
11:47:02 SYS@orcl>alter session set container=cndba;

Session altered.

Elapsed: 00:00:00.43
11:47:23 SYS@orcl>select file_name,tablespace_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
/data/oradata/yunqu/YUNQU/datafile/o1_mf_temp_fd6cxv02_.tmp
TEMP
复制临时表空间FILE_NAME，填入写到Value 2

4、最后，执行两个组件包catalog.sql、catproc.sql,其实这两个包内就有涉及OLS, DV组件重编译以及重赋权
SQL>alter session set container=cndba;
SQL>shutdown immediate
SQL>startup upgrade;
SQL>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 CNDBA                          MIGRATE    YES
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

5、最后编译
@$ORACLE_HOME/rdbms/admin/utlrp.sql
6、正常模式重启PDB
14:20:55 SYS@orcl>shutdown immediate;
Pluggable Database closed.
14:23:47 SYS@orcl>startup
Pluggable Database opened.
14:23:54 SYS@orcl>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 CNDBA                          READ WRITE NO

 重新导出，显示正常
 oracle@12cDG:/u01>expdp system/yunq111@cndba directory=dp_dir dumpfile=fulldb_cndba.dmp logfile=expdp.log full=y exclude=index,statistics parallel=2

 Export: Release 12.2.0.1.0 - Production on Sat Apr 28 14:08:53 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_04":  system/********@cndba directory=dp_dir dumpfile=fulldb_cndba.dmp logfile=expdp.log full=y exclude=index,statistics parallel=2 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
                    中间输出过程省略.....
 Master table "SYSTEM"."SYS_EXPORT_FULL_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_04 is:
  /u01/backup/fulldb_cndba.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_04" successfully completed at Sat Apr 28 14:16:01 2018 elapsed 0 00:05:51

导入12C Single

SQL> alter session set container=marvinpdb;

Session altered.

SQL> create or replace directory dp_dir as  '/u01/backup';

Directory created.

SQL> grant read,write on directory dp_dir to system;

Grant succeeded.

[root@rac12chub1 ~]# chown oracle.oinstall /u01/backup/fulldb_cndba.dmp
oracle@rac12chub1:/u01>impdp system/yunq111@marvinpdb directory=dp_dir dumpfile=fulldb_cndba.dmp logfile=impdp.log full=y  parallel=2

验证是否可以Rebuild索引http://www.cndba.cn/Marvinn/article/2757http://www.cndba.cn/Marvinn/article/2757

SQL> alter session set container=marvinpdb;

Session altered.

SQL> conn marvin/marvin@marvinpdb;
已连接。
SQL>
SQL> select count(*) from cndba;

  COUNT(*)
----------
     79440

SQL> alter index IDX_CNDBA rebuild online parallel 2;
alter index IDX_CNDBA rebuild online parallel 2
*
第 1 行出现错误:
ORA-01418: 指定的索引不存在

尝试重建之前测试表上的索引，可以发现索引并不存在，所以，可推测只要在排除索引的情况，Rebuild是并不存在的，只能重新利用脚本SQLFILE创建索引Create

重新搜集统计信息
SQL>exec dbms_stats.gather_database_stats(estimate_percent=>80,degree=>4,cascade=>true,granularity=>'ALL');

PL/SQL过程执行成功。

SQL>

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Marvinn

Marvinn

关注

路漫漫其修远兮、吾将上下而求索

  • 99
    原创
  • 0
    翻译
  • 2
    转载
  • 36
    评论
  • 访问：458440次
  • 积分：449
  • 等级：中级会员
  • 排名：第12名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ