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

原创 jieguo 2024-03-27

245

迁移需求：

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
源端信息：

目标端信息：

如上源端字符集：ZHS16GBK，目标端AL32UTF8
源端版本：19.3，目标端版本19.7
源端和目标端均启用了归档模式。
操作系统一致。

以上要素满足了使用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日志显示：

目标端检查告警视图：

[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.

主要参考：
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

最后修改时间：2024-04-26 15:07:24

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者