Oracle 19C下的pdb refresh方式升级迁移

最新推荐文章于 2024-05-30 14:44:15 发布

DBA狗剩儿

最新推荐文章于 2024-05-30 14:44:15 发布

阅读量328

点赞数 4

分类专栏：工作随记文章标签： oracle 数据库 refresh

本文链接：https://blog.csdn.net/weixin_41607523/article/details/139063337

版权

工作随记专栏收录该内容

12 篇文章 1 订阅

订阅专栏

文章目录

前言
一、数据库环境信息
二、避坑攻略
三、详细迁移步骤

前言

PDB Refresh是12C推出的特性，具有对源端PDB进行增量同步的功能，每次刷新会将源端PDB中的任何更改同步到目标PDB(在此环境中目标PDB被称作Refreshable PDB)中，目前增量同步方式有两种：手动方式与自动方式。

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据库环境信息

源端	目标端
OS：Oracle linux 7.9	OS：Oracle linux 7.9
Database：19.15	Database：19.22
Archive log：Enable	Archive log：Enable
Language：ZHS16GBK	Language：AL32UTF8
PDB name：ALLORCL	PDB name：ALLORCL
global_name：FALSE	global_name：FALSE

检查命令参考：

数据库版本：
select * from v$version;
归档模式：
archive log list;
字符集：
select userenv('language') from dual;
字节序平台兼容
select db.name,db.platform_id,db.platform_name,os.endian_format from v$database db,v$transportable_platform os where db.platform_id=os.platform_id;
undo本地启用：
select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
select name from v$datafile where name like '%undo%';

二、避坑攻略

1.global_name设置：

在global_name设置不一致时，dblink提示如下

SoL> select * from cxl.tab@refresh dblink;select e from cxl.tabarefresh dblink
ERROR at line 1:
ORA-02085:database link REFRESH_DBLINK connects to ALLORGL

SQL> alter system set global_names=FALSE scope=both sid='*';

System altered.

SQL> 
SQL> show parameter global

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
allow_global_dblinks		     boolean	 FALSE
global_names			     boolean	 FALSE
global_txn_processes		     integer	 1
SQL>

2.数据库组件查询：

代码如下（示例）：

SQL> select comp_id from dba_registry where status!='REMOVED';

在完成refresh时进行启动pdb时发现报错，在pdb_plug_in_violations视图中看到两种类型错误，一个是数据库组件、一个是版本问题（版本问题是已知的）。

3.手动补充组件：

dbca -silent -configureDatabase -sourceDB 实例名 -addDBOption JSERVER,ORACLE_TEXT,IMEDIA,CWMLITE,SPATIAL,OMS,DV -sysDBAUserName sys -sysDBAPassword SYS密码

三、详细迁移步骤

1.迁移前准备：创建用户、dblink等：

源端：创建用户并赋权：
create user c##refresh identified by refresh container=all;
grant create session,sysoper,create pluggable database to c##refresh container=all;
目标端：创建dblink:
SQL> drop public database link refresh_dblink;

Database link dropped.

SQL> create public database link refresh_dblink connect to c##refresh identified by refresh using '10.160.140.3:1521/allorcl';

Database link created.
SQL> select * from dual@refresh_dblink;

DUM
---
X

SQL>
目标端：创建pdb同步:
SQL> create pluggable database allorcl from allorcl@refresh_dblink refresh mode every 30 minutes;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  MOUNTED
SQL> 
##数据文件路径不一致时：FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCLA/XXX', '/opt/oracle/oradata/ORCLB/XXX');

2.迁移前手动刷新，保证割接最小数据量：

SQL> alter pluggable database allorcl refresh;

Pluggable database altered.

SQL> alter pluggable database allorcl open read only instances=all;

Warning: PDB altered with errors.

SQL> alter session set container=allorcl;

Session altered.
##可进行查下数据量
SQL> select * from cxl.tab;

	ID NAME
---------- ------------------------------
	 1 cxl
	 2 dey

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database allorcl close immediate instances=all;

Pluggable database altered.

SQL> 
SQL> alter pluggable database allorcl refresh;

Pluggable database altered.

3.正式割接时：

##此时目标端可以手动刷新一次数据
SQL> alter pluggable database allorcl refresh;

Pluggable database altered.

SQL>
##源端数据库进行停止，然后启动到read only模式，保证数据一致
SQL> alter pluggable database allorcl close immediate instances=all;

Pluggable database altered.

SQL> 
SQL> alter pluggable database allorcl open read only instances=all;

Pluggable database altered.
##目标端进行最后一次增量刷新，并取消refresh模式
SQL> alter pluggable database allorcl refresh;

Pluggable database altered.

SQL>show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  MOUNTED
	 
SQL> alter pluggable database allorcl refresh mode none;

Pluggable database altered.

SQL> alter pluggable database allorcl open instances=all;

Warning: PDB altered with errors.--此处有个错误（版本问题）

SQL> 
SQL> 
SQL> alter session set container=allorcl;

Session altered.

SQL> 
SQL> r
  1* select status,message from pdb_plug_in_violations where type ='ERROR'

STATUS
---------------------------
MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PENDING
Interim patch 35926646/25513953 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)): Installed in the CDB but not in the PDB

PENDING
Interim patch 33808367/24680225 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)): Not installed in the CDB but installed in the PDB

PENDING
'19.22.0.0.0 Release_Update 2401040239' is installed in the CDB but '19.15.0.0.0 Release_Update 2203311254' is installed in the PDB
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 5 ALLORCL			  READ WRITE YES   --此时是受限模式
SQL> 
##保证数据库是打开状态，进行更新数据字典,编译失效对象后进行一次启停
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@cxl1dbadm01 ~]$ 
[oracle@cxl1dbadm01 ~]$ 
[oracle@cxl1dbadm01 ~]$ $ORACLE_HOME/OPatch/datapatch -pdbs allorcl
SQL Patching tool version 19.22.0.0.0 Production on Sat May 18 00:13:10 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_374315_2024_05_18_00_13_10/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 32067171 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)):
  Binary registry: Not installed
  PDB ALLORCL: Rolled back successfully on 17-JUL-22 12.48.31.132460 AM
Interim patch 33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)):
  Binary registry: Not installed
  PDB ALLORCL: Applied successfully on 17-JUL-22 12.48.31.135432 AM
Interim patch 35926646 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)):
  Binary registry: Installed
  PDB ALLORCL: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.22.0.0.0 Release_Update 240104023954: Installed
  PDB ALLORCL:
    Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 17-JUL-22 12.04.33.583058 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: ALLORCL
    The following interim patches will be rolled back:
      33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367))
    Patch 35943157 (Database Release Update : 19.22.0.0.240116 (35943157)):
      Apply from 19.15.0.0.0 Release_Update 220331125408 to 19.22.0.0.0 Release_Update 240104023954
    The following interim patches will be applied:
      35926646 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646))

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 33808367 rollback (pdb ALLORCL): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24680225/33808367_rollback_UMCAREYG_ALLORCL_2024May18_00_13_30.log (no errors)
Patch 35943157 apply (pdb ALLORCL): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35943157/25527362/35943157_apply_UMCAREYG_ALLORCL_2024May18_00_13_39.log (no errors)
Patch 35926646 apply (pdb ALLORCL): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35926646/25513953/35926646_apply_UMCAREYG_ALLORCL_2024May18_00_13_39.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: ALLORCL

SQL Patching tool complete on Sat May 18 00:15:23 2024
[oracle@cxl1dbadm01 ~]$ 
[oracle@cxl1dbadm01 ~]$ 
[oracle@cxl1dbadm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 18 00:15:42 2024
Version 19.22.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> 
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  READ WRITE YES
SQL> 
SQL> 
SQL> alter session set container=allorcl;

Session altered.

SQL> 
SQL> 
SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN	      2024-05-18 00:16:21

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>	  number should decrease with time.
DOC>	     SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>	  should increase with time.
DOC>	     SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>	     SELECT job_name FROM dba_scheduler_jobs
DOC>		WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>	     SELECT job_name FROM dba_scheduler_running_jobs
DOC>		WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END	      2024-05-18 00:16:36

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
		  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>	   logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
			  0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  READ WRITE YES
SQL> 
SQL> alter pluggable database allorcl close instances=all;

Pluggable database altered.

SQL> alter pluggable database allorcl open instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  READ WRITE NO
SQL> 
SQL> alter session set container=allorcl;

Session altered.

SQL> 
SQL> 
SQL> 
##再次检查状态为：RESOLVED
SQL> select status,message from pdb_plug_in_violations where type ='ERROR';

STATUS
---------------------------
MESSAGE
--------------------------------------------------------------------------------
RESOLVED
Interim patch 35926646/25513953 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646
)): Installed in the CDB but not in the PDB

RESOLVED
Interim patch 33808367/24680225 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367
)): Not installed in the CDB but installed in the PDB

RESOLVED

STATUS
---------------------------
MESSAGE
--------------------------------------------------------------------------------
'19.22.0.0.0 Release_Update 2401040239' is installed in the CDB but '19.15.0.0.0
 Release_Update 2203311254' is installed in the PDB


SQL> 
SQL> 
SQL> conn / as sysdba
Connected.
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 ALLORCL			  READ WRITE NO
SQL> 
SQL> alter pluggable database all save state instances=all;

Pluggable database altered.

SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@cxl1dbadm01 ~]$ 
[oracle@cxl1dbadm01 ~]$

参考：https://www.modb.pro/db/1772873746251091968

DBA狗剩儿

关注

4
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle 19C下的pdb refresh方式升级迁移

PDB Refresh是12C推出的特性，具有对源端PDB进行增量同步的功能，每次刷新会将源端PDB中的任何更改同步到目标PDB(在此环境中目标PDB被称作Refreshable PDB)中，目前增量同步方式有两种：手动方式与自动方式。提示：以下是本篇文章正文内容，下面案例可供参考。
复制链接

扫一扫