Oracle PDB迁移实践

oracledatabase12c之后推出了多租户模式,这个功能也是云时代的一个需求,对于DBA来说更便捷和弹性。我们可以轻松的创建和迁移一个数据库,比跨平台传输表空间和datapump方便很多。这里我们介绍pdb迁移/升级的方法,总的来说分为在线和离线,其中在线是最省事的。一.通过dblink的方式远程克隆

该方式对于相同版本的pdb之间的迁移没问题。如果是跨版本的,比如从12.1到19c也可以使用。在19c上clone完之后,需要运行dbupgrade脚本。

图片

二.开始迁移 1.锁定迁移用户

select ‘alter user ‘||username|| ‘ account lock;’ from dba_users where account_status=’OPEN’;

  2.关闭源库,并以read only启动

alter pluggable database pdb1 close immediate instances=all;alter pluggable database pdb1 open read only instances=all;

在新的容器数据库上执行以下操作  3.在目标库上创建到源库的dblink

create database link clone_link connect to system identified by oracle using ‘(description=(address=(protocol=tcp)(host=192.168.10.21)(port=1521))(connect_data=(service_name=pdb1)))’;

  4.执行远程创建

create pluggable database pdb1 from pdb1@clone_link;

下面是在通过dblink远程克隆时alert日志对应的输出

This instance was first to open pluggable database PDB1 (container=3)Database Characterset for PDB1 is ZHS16GBKDeleting old file#319 from file$Deleting old file#320 from file$Deleting old file#321 from file$Deleting old file#325 from file$Deleting old file#326 from file$Deleting old file#327 from file$Deleting old file#328 from file$Deleting old file#329 from file$Adding new file#73 to file$(old file#319)Adding new file#74 to file$(old file#320)Adding new file#75 to file$(old file#321)Adding new file#76 to file$(old file#325)Adding new file#77 to file$(old file#326)Adding new file#78 to file$(old file#327)Adding new file#79 to file$(old file#328)Adding new file#80 to file$(old file#329)Successfully created internal service pdb1 at openALTER SYSTEM: Flushing buffer cache inst=1 container=3 local****************************************************************Post plug operations are now complete.Pluggable database PPDBETC with pdb id – 3 is now marked as NEW.****************************************************************Completed: create pluggable database pdb1 from pdb1@clone_link

如果源端和目标端对应的patch不一致或者出现一些无效的组件等,PDB会处以restricted模式。

SQL> show pdbs     CON_ID CON_NAME                      OPEN MODE  RESTRICTED ———- —————————— ———- ———-          2 PDB$SEED                      READ ONLY  NO           3 PDB1                           READ WRITE YES

用来检查补丁或冲突的SQL语句

select patch_id, patch_uid, version, status, description from dba_registry_sqlpatch;
select inst_id,name,open_mode,restricted from gv$pdbs order by 1,2;select name,con_id,con_uid,open_mode,restricted,guid from v$pdbs order by 1,2;select status, message, action from pdb_plug_in_violations where status !=’RESOLVED’;

如果是补丁不一致,通过datapatch一般能解决大部分问题

oracle> ./datapatch -verbose -pdbs PDB1

如果datapatch成功执行后,数据库还处于restricted模式,那么大部分情况下,是因为一些无效对象导致的。在这个模式下,数据库时不正常的,千万不要切换和运行业务。

查询无效对象

SQL> select owner,object_name,object_type,status from dba_objects where status=’INVALID’ andOWNER IN (‘PUBLIC’,’SYS’,’SYSTEM’,’XDB’,’ORDSYS’,’ORDPLUGINS’,’ORDDATA’,’MDSYS’,’CTXSYS’);OWNER    OBJECT_NAME       OBJECT_TYPE     STATUS——— —————– ————— ——–XDB      DBMS_XDBUTIL_INT  PACKAGE BODY    INVALIDXDB      DBMS_XDBT         PACKAGE BODY    INVALIDCTXSYS   DRILOAD           PACKAGE BODY    INVALIDCTXSYS   DRVDOC            PACKAGE BODY    INVALIDMDSYS    SDO_OLS           PACKAGE BODY    INVALID

查询组件状态

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status from dba_registry;

查询后会发现,一些组件可能也是无效的,通过dba_errors去下钻出现问题的根本原因

SQL> select text from dba_errors where name=’DBMS_XDBUTIL_INT’ and owner=’XDB’;TEXT——————————————————————————PLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignored
10 rows selected.SQL> select text from dba_errors where name=’DBMS_XDBT’ and owner=’XDB’;
TEXT————————————————————————PLS-00201: identifier ‘CTX_DOC’ must be declaredPL/SQL: Statement ignored
2 rows selected.SQL> select text from dba_errors where name=’DRILOAD’ and owner=’CTXSYS’;TEXT————————————————————————-PLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignored
4 rows selected.SQL> select text from dba_errors where name=’DRVDOC’ and owner=’CTXSYS’;
TEXT————————————————————————PLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignoredPLS-00201: identifier ‘DBMS_SQL’ must be declaredPL/SQL: Statement ignored10 rows selected.
SQL> select text from dba_errors where name=’SDO_OLS’ and owner=’MDSYS’;TEXT———————————————————————–PLS-00201: identifier ‘UTL_HTTP’ must be declaredPL/SQL: Item ignoredPLS-00201: identifier ‘UTL_HTTP’ must be declaredPL/SQL: Item ignoredPLS-00201: identifier ‘UTL_HTTP’ must be declaredPL/SQL: Statement ignoredPLS-00320: the declaration of the type of this expression is incomplete or malformedPL/SQL: Statement ignored
10 rows selected.

通过以上,可以发现这些无效对象是因为权限的问题,导致无法正常编译。

SQL> grant execute on dbms_sql to XDB,CTXSYS;SQL> grant execute on CTX_DOC to XDB;SQL> grant execute on UTL_HTTP to MDSYS;

授权后,可以通过这下面的命令进行编译

SQL> exec dbms_pdb.exec_as_oracle_script(‘alter package XDB.DBMS_XDBT compile body’);SQL> exec dbms_pdb.exec_as_oracle_script(‘alter package CTXSYS.DRVDOC compile body’);SQL> exec dbms_pdb.exec_as_oracle_script(‘alter package CTXSYS.DRILOAD compile body’);SQL> exec dbms_pdb.exec_as_oracle_script(‘alter package XDB.DBMS_XDBUTIL_INT compile body’);SQL> exec dbms_pdb.exec_as_oracle_script(‘alter package MDSYS.SDO_OLS compile body’);

启动新数据库

alter pluggable database pdb1 open read write instances=all;

解锁数据库用户

select ‘alter user ‘||username|| ‘ account unlock;’ from dba_users where account_status=’LOCKED’;

与原库进行对比

1.无效对象数:select count(*)  from dba_objects where status = ‘INVALID’ and owner in (‘TEST1′,’TEST2′,’TEST3′,’TEST4′,’TEST5’);
2.对象总数为:select count(*)  from dba_objects where owner in (‘TEST1′,’TEST2′,’TEST3′,’TEST4′,’TEST5’);
3.用户下对象所使用的表空间select distinct TABLESPACE_NAME from dba_segments where owner in (‘TEST1′,’TEST2′,’TEST3′,’TEST4′,’TEST5’);

确认无误后,迁移完成。

来源:IT那活儿,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/119312

发表评论

登录后才能评论
侵权联系
返回顶部