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
