Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » Oracle 12c+

Oracle PDB升级/迁移实践

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

1.在线方式迁移
通过dblink的方式远程克隆。帮我们做了很多工作,这个也是简单的迁移方式。该方式对于相同版本的pdb之间的迁移没问题。如果是跨版本的,比如从12.1到19c也可以使用。在19c上clone完之后,需要运行dbupgrade脚本。
2.离线方式迁移
将原库离线或只读方式打开,将文件复制到目标库,然后再创建PDB。如果使用了ASM(默认使用了OMF这个特性,OMF这个特性贼棒),会变得比较复杂。

在线方式迁移
在新的容器数据库上执行以下操作
    1.在目标库上创建到源库的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)))';
    2.执行远程创建
       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 ZHS16GBK
   Deleting 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 open
   ALTER 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@xd08dbadm01 OPatch]$ ./datapatch -verbose -pdbs PDB1
如果datapatch成功执行后,数据库还处于restricted模式,那么大部分情况下,是因为一些无效对象导致的。在这个模式下,数据库时不正常的,千万不要切换和运行业务。
查询无效对象
select owner,object_name,object_type,status from dba_objects where status='INVALID' and OWNER IN ('PUBLIC','SYS','SYSTEM','XDB','ORDSYS','ORDPLUGINS','ORDDATA','MDSYS','CTXSYS');
SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID' and 
OWNER IN ('PUBLIC','SYS','SYSTEM','XDB','ORDSYS','ORDPLUGINS','ORDDATA','MDSYS','CTXSYS');
   OWNER     OBJECT_NAME       OBJECT_TYPE     STATUS
   --------- ----------------- --------------- --------
   XDB       DBMS_XDBUTIL_INT  PACKAGE BODY    INVALID
   XDB       DBMS_XDBT         PACKAGE BODY    INVALID
   CTXSYS    DRILOAD           PACKAGE BODY    INVALID
   CTXSYS    DRVDOC            PACKAGE BODY    INVALID
   MDSYS     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 declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/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 declared
   PL/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 declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/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 declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   PLS-00201: identifier 'DBMS_SQL' must be declared
   PL/SQL: Statement ignored
   10 rows selected.

   SQL> select text from dba_errors where name='SDO_OLS' and owner='MDSYS';
   TEXT
   -----------------------------------------------------------------------
   PLS-00201: identifier 'UTL_HTTP' must be declared
   PL/SQL: Item ignored
   PLS-00201: identifier 'UTL_HTTP' must be declared
   PL/SQL: Item ignored
   PLS-00201: identifier 'UTL_HTTP' must be declared
   PL/SQL: Statement ignored
   PLS-00320: the declaration of the type of this expression is incomplete or malformed
   PL/SQL: Statement ignored

   10 rows selected.
   SQL>
通过以上,可以发现这些无效对象是因为权限的问题,导致无法正常编译。
   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');
也可以通过catcon.pl脚本进行批量编译
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/logs -b comp_invalid_objs -- --x"@$ORACLE_HOME/rdbms/admin/utlrp.sql"
重启数据库后,就正常了。打开PDB数据库
   SQL> alter pluggable database pdb1 open instances=all;
启用并行,收集数据库和用户的统计信息
   SQL> select dbms_stats.get_prefs('CONCURRENT') from dual;
   如果输出是FALSE,通过下面的命令启用
   exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');
   收集统计信息
   SQL> exec dbms_stats.gather_dictionary_stats;
   SQL> exec dbms_stats.gather_schema_stats(ownname=>'ABC',degree=>6);
如果是RAC环境,可能会出现下面的问题。如果出现下面的问题,请确保所有的节点都处于打开状态,并能访问dblink正常工作
   ORA-65028: Unable to open plugin data file at path
   ORA-17503: ksfdopn:2 Failed to open file

   ORA-07202: sltln: invalid parameter to sltln.

2.离线方式迁移
对于使用文件系统的迁移来说,比较简单。可参考链接https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_unplug_plug/pdb_unplug_plug.html
如果使用了ASM,这种方式相对来说复杂一些,因为ASM默认使用了OMF,从源端复制到目标端时不能使用后面的文件编号信息,这些是系统自动生成的。想通过复制文件到目标端会比较不方便。我们可以通过asmcmd cp命令来传输数据文件
首先,以只读方式打开可插拔数据库
   sqlplus / as sysdba
   alter pluggable database pdb1 open read only;

其次,在线生成pdb的描述文件

如果用unplug into,必须删除、重建PDB后,PDB才能正常使用。建议用dbms_pdb.describe生成描述文件,效果是一样的。

   exec dbms_pdb.describe('/home/oracle/pdbxj01.xml','pdb1');

其次,生成传输到目标端的命令行

如果使用了bigfile tablespace,下面可以通过。如果不是,需要调整

     set lines 160
     select 'asmcmd cp '||file_name||' --port 1525 sys/oracle@192.168.10.X.+ASM2:+DATA/stage/'||tablespace_name from dba_data_files;
     asmcmd cp可参考How to Copy asm files between remote ASM instances using ASMCMD command (Doc ID 785580.1)
   注意:如果出现出现以下错误,主要是因为GI的版本导致的,需要应用补丁,请参考1918906.1。如果要跟踪asmcmd,可以设置export DBI_TRACE=1
   ORA-15056: additional error message        
   ORA-17630: Mismatch in the remote file protocol version client 2 server 3        
   ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 486        
   ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)       
再次,修改pdb描述文件中的位置,并传到目标端
最后,在目标端创建可插拔数据库
   create pluggable database pdb_plug_copy using '/home/oracle/pdb1.xml' copy;

Reference
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_unplug_plug/pdb_unplug_plug.html

https://docs.oracle.com/en/database/oracle/oracle-database/18/obeze/index.html

How to Copy asm files between remote ASM instances using ASMCMD command (Doc ID 785580.1)

Copying File Across Remote Servers: ASMCMD-8016, ORA-17628, ORA-17630, ORA-06512 (Doc ID 1918906.1)

Datapatch: Database 12c or later Post Patch SQL Automation (Doc ID 1585822.1)
Complete checklist for 12c R1 PDB upgrade (Upgrading single/multiple Pluggable database (PDB)) (Doc ID 1933391.1)


关键词:migration pdb 

相关文章

如何使用Autoupgrade升级RAC数据库
Oracle PDB升级/迁移实践
容器数据库(CDB)和可插拔数据库(PDB)概述
PDB Migration/Failover in Dataguard
Full Transportable Export/Import(fxtts)
Oracle Database 12c之后DataGuard环境中的PDB操作
在18c中通过ASM Flex DiskGroup克隆PDB
EBS跨平台迁移与传输表空间(TTS)
Create/Delete/Rename/Clone/Convert to PDB
12c Backup/Restore PDB/CDB
Top