暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 使用pdb refresh方式升级迁移pdb安全快捷

使用pdb refresh方式升级迁移pdb安全快捷

原创 jieguo 2024-03-27
276

迁移需求：

Oracle19.3版本的一个pdb（jyc）计划迁移到19.7版本的cdb中

使用pdb refresh迁移的优点如下：
1.增量刷新，割接迁移停机时间短
2.操作安全，回退方便

使用pdb refresh迁移的限制条件：
1.源端归档模式（其实变化量不大的情况下，非归档模式也可以）
2.源端的字符集与目标端相同或为目标端的子集
3.源端的组件与目标端相同或为目标端的子集
4.源端和目标端字节序一致

环境检查：

源端：192.168.207.164
目标端：192.168.207.199
源端信息：
image.png
image.png
目标端信息：
image.png
image.png
image.png
如上源端字符集：ZHS16GBK，目标端AL32UTF8
源端版本：19.3，目标端版本19.7
源端和目标端均启用了归档模式。
操作系统一致。
image.png
以上要素满足了使用pdb refresh的迁移条件。

检查的命令参考：

版本：
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%';

复制

割接前同步准备：建用户、dblink、同步pdb：

源端：创建用户并赋权：
create user c##refresh identified by abcd1234 container=all;
grant create session,sysoper,create pluggable database to c##refresh container=all;
目标端：创建dblink:
create public database link refresh_dblink_jyc connect to c##refresh identified by abcd1234 using '192.168.207.164:1521/jyc';
select * from dual@refresh_dblink_jyc;
目标端：创建pdb同步:
create pluggable database jj from jyc@refresh_dblink_jyc refresh mode every 30 minutes FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/jyc', '/opt/oracle/oradata/JYC/jj');

复制

正式割接步骤：（可参考）

1.目标端操作：应用停止前，手动刷新目标库(可操作多次，目的是降低每一次的增量恢复时间)


SQL> alter session set container=jj;

Session altered.

SQL> select table_name from dba_tables where owner='JYC';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
T5
T0
T6
T1

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 JJ                             READ ONLY  NO
SQL> alter pluggable database jj close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database jj refresh;

Pluggable database altered.

SQL> alter pluggable database jj refresh;

Pluggable database altered.

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

Pluggable database altered.

SQL> select table_name from dba_tables where owner='JYC';--检查增量情况

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
T2
T5
T0
T6
SQL> alter pluggable database jj close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database jj refresh;

Pluggable database altered.

复制

2.源端操作：割接开始，应用停止后，将源库pdb关闭后以read only方式打开：

SQL> alter pluggable database jyc close immediate instances=all;--关闭源pdb

Pluggable database altered.

SQL>  alter pluggable database jyc open read only instances=all;  --只读方式打开，确保数据一致性不变

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 JYC                            READ ONLY  NO


复制

3.目标端操作：最后一次刷新增量，并激活打开目标库


SQL> alter pluggable database jj close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database jj refresh;--最后一次刷新增量


Pluggable database altered.

SQL> SQL> alter pluggable database jj refresh mode none;--激活操作开始

Pluggable database altered.

SQL> alter pluggable database jj open instances=all;  --打开目标库

Pluggable database altered.

SQL> select table_name from dba_tables where owner='JYC';--检查数据一致

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
T2
T5
T0
T6
T1
T3

6 rows selected.


复制

4.目标端操作：更新目标库数据字典

由于目标端小不丁更高，所以日志有警告，需要更新数据字典解决

目标端alert日志显示：
image.png
目标端检查告警视图：
image.png

[oracle@oracle admin]$ $ORACLE_HOME/OPatch/datapatch -pdbs jj
SQL Patching tool version 19.7.0.0.0 Production on Wed Mar 27 16:00:53 2024
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_4696_2024_03_27_16_00_53/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:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.7.0.0.0 Release_Update 200404035018: Installed
  PDB JJ:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 18-MAR-24 12.36.48.961958 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: JJ
    No interim patches need to be rolled back
    Patch 30869156 (Database Release Update : 19.7.0.0.200414 (30869156)):
      Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.7.0.0.0 Release_Update 200404035018
    No interim patches need to be applied

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

Validating logfiles...done
Patch 30869156 apply (pdb JJ): SUCCESS
  logfile: /opt/oracle/cfgtoollogs/sqlpatch/30869156/23493838/30869156_apply_JYC_JJ_2024Mar27_16_01_23.log (no errors)
SQL Patching tool complete on Wed Mar 27 16:04:24 2024


复制

5.目标端操作：编译失效对象，并重启pdb

[oracle@oracle admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 27 16:05:29 2024
Version 19.7.0.0.0

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


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

SQL> show pdbs;

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

Session altered.

SQL> set timing on
SQL> @?/rdbms/admin/utlrp.sql

Session altered.

Elapsed: 00:00:00.00

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2024-03-27 16:05:54

Elapsed: 00:00:00.04
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.

Elapsed: 00:00:00.66

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2024-03-27 16:05:55

Elapsed: 00:00:00.00
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

Elapsed: 00:00:00.01
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

Elapsed: 00:00:00.00

Function created.

Elapsed: 00:00:00.02

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Function dropped.

Elapsed: 00:00:00.07

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.98
SQL> --重启一遍pdb，解决select * from PDB_PLUG_IN_VIOLATIONS的pending状态。
SQL> shutdown immediate 
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> 
Pluggable Database opened.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 JJ                             READ WRITE NO
SQL> alter pluggable database jj save state; --保存打开状态

Pluggable database altered.


复制

image.png
image.png

主要参考：
https://cloud.tencent.com/developer/article/1968395

附：建库命令参考：

dbca -silent -ignorePreReqs  -ignorePrereqFailure  -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname jyc -sid jyc \
-createAsContainerDatabase TRUE \
-numberOfPDBs 1 \
-pdbName pdb1 \
-pdbAdminPassword abcd1234 \
-sysPassword abcd1234 -systemPassword abcd1234 -dbsnmpPassword abcd1234 \
-datafileDestination '/opt/oracle/oradata' -recoveryAreaDestination '/opt/oracle/arch' \
-enableArchive true \
-redoLogFileSize 200 \
-storageType FS \
-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
-sampleSchema false \
-initParams sga_target=3G,cluster_database=false,processes=1000,pga_aggregate_target=1G,nls_language='AMERICAN',nls_territory=AMERICA,db_recovery_file_dest_size=20GB,db_recovery_file_dest=/opt/oracle/arch,audit_trail=none \
-totalMemory 0 \
-databaseType MULTIPURPOSE \
-emConfiguration none \
-databaseConfigType SI \
-nodelist oracle


复制

相关参考：
https://oracle-base.com/articles/12c/multitenant-pdb-refresh-12cr2#prerequisites

墨力计划pdb19c
最后修改时间：2024-04-26 15:07:24
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

Oracle Concepts（Oracle 19c）：07 SQL
Ryan Bai
876次阅读
2025-04-09 10:57:11
升级到oracle 19.8后vm_concat函数不可用怎么解决
Digital Observer
39次阅读
2025-03-17 09:41:51
热门文章
oracle19c rac grid安装报错的快速处理libasmclntsh19.ohso libasmperl19.ohso client_sharedlib' of makefile
2023-03-15 12848浏览
奇怪的ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
2021-11-26 8326浏览
oracle19c rac+asm-->oracle 19c single+fs的adg搭建(实战于生产)
2021-11-11 5783浏览
(1)一次失败的割接经历：麒麟linux6.5+oracle 12c rac打PSU补丁
2022-09-21 5687浏览
gpu p2p多卡训练运行不正常问题
2023-03-09 5298浏览
最新文章
Exadata 核心优势技术全景
2025-03-25 35浏览
Kafka3.8.0+Centos7.9的安装参考
2024-09-20 282浏览
查看oracle长时间运行任务的进度
2024-09-10 106浏览
系统重启后两节点mgr自动恢复的脚本
2024-07-23 60浏览
git@github.com: Permission denied (publickey).
2024-07-10 82浏览
目录
  • 迁移需求：
  • 环境检查：
  • 割接前同步准备：建用户、dblink、同步pdb：
  • 正式割接步骤：（可参考）
    • 1.目标端操作：应用停止前，手动刷新目标库(可操作多次，目的是降低每一次的增量恢复时间)
    • 2.源端操作：割接开始，应用停止后，将源库pdb关闭后以read only方式打开：
    • 3.目标端操作：最后一次刷新增量，并激活打开目标库
    • 4.目标端操作：更新目标库数据字典
    • 5.目标端操作：编译失效对象，并重启pdb