借助 Materialized View 技术实现 Amazon RDS for Oracle 存储卷缩容

背景介绍

很多企业的 Amazon RDS for Oracle（以下简称 RDS Oracle）数据库在多年持续运行之后，其存储卷会显著增大。而随着历史数据的归档，大量记录被清理之后实例中的有效数据可能变得很少。遗憾的是 RDS 存储卷不支持就地缩小容量，这意味着一方面客户不得不额外支付包括快照备份在内的存储费用，另一方面由于 HWM 下的存储碎片化导致数据库性能下降。

借助 Amazon Database Migration Service（以下简称 DMS），可以将数据逻辑导出到新的 RDS Oracle 实例，达到缩小容量的效果。但是在该场景中，DMS 的使用较有局限性。详情可参考官方文档的 Limitations 章节。本文将介绍不使用第三方数据抽取工具，通过 materialized view log 向预定义表中增量同步数据的方式实现逻辑导出/导入。除最终新旧系统切换需要停机窗口，大部分步骤均可在线完成。

方案及步骤简介

本文模拟源 RDS Oracle 实例的存储卷为 5T，在数据清理之后有效数据为大约 1.5T，其中 1.3T 数据被 6 张表占据。与业务部门确认得知这些表的数据变化最为频繁，而其它表的数据相对静态，因此本文定义这 6 张表为“活跃表” 。我们将对这些活跃表创建 materialized view log，以实现首次全量数据加载，后续增量刷新的功能，而其它“静态表”及对象则在停机窗口中借助数据泵导入。步骤如下：

源 – 统计有效数据为创建新 RDS Oracle 实例作参考
目标 – 创建新实例，创建表空间
目标 – 导入活跃表（metadata only）
源 – 给活跃表创建 materialized view log
目标 – 活跃表全量数据加载
目标 – 配置定时任务实现增量刷新
停止应用程序
目标 – 导入其余静态表及对象
源实例及目标实例改名
数据验证及启动应用程序

方案部署

1. 统计源数据库的有效数据

在源 RDS Oracle 实例中统计包含 SYS 在内的所有段。本文模拟环境实例卷为 5T，数据量约 1.5T。主用户 ROOT 及业务数据共约 1.3T。

SQL> select round(sum(bytes/1024/1024/1024)) from dba_segments;

ROUND(SUM(BYTES/1024/1024/1024))
--------------------------------
                            1515

SQL> select round(sum(bytes/1024/1024/1024)) from dba_segments where owner in ('ROOT','USR1', 'USR2', 'USR3');

ROUND(SUM(BYTES/1024/1024/1024))
--------------------------------
                            1308

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME in ('USR1', 'USR2', 'USR3');

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
USR3                           USR3_TBS
USR1                           USR1_TBS
USR2                           USR2_TBS

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER IN ('ROOT','USR1','USR2','USR3') AND STATUS = 'INVALID';

no rows selected

2. 在新实例中创建表空间

根据上一步统计到的信息创建新 RDS Oracle 实例的存储卷为 2T 容量。本文数据均存储在各用户的默认表空间中。如果索引等对象依赖其它表空间，也需分别创建，并创建指向源实例的 DBLINK。

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

CREATE TABLESPACE USR1_TBS DATAFILE SIZE 10G AUTOEXTEND ON;
CREATE TABLESPACE USR2_TBS DATAFILE SIZE 10G AUTOEXTEND ON;
CREATE TABLESPACE USR3_TBS DATAFILE SIZE 10G AUTOEXTEND ON;

CREATE PUBLIC DATABASE LINK SOURCEDB_LNK CONNECT TO root
IDENTIFIED BY "MyPassword" USING
'(description=(address=(protocol=tcp) (host= sourceinstance.xxxkise.rds.cn-northwest-1.amazonaws.com.cn)
(port=1521)) (connect_data=(sid=ORCL)))';

3. 导入活跃表的元数据

以下数据泵命令将导入用户以及 6 张活跃表的元数据，其中 ROOT 是实例的主用户名。如果同时导入主用户及其数据，在创建新实例时需使用相同的名称即 ROOT。如果选择不导入主用户可以在以下命令的 SCHEMA_EXPR 选项中将其移除。对于这 6 张主要业务表，我们使用 SUBQUERY 选项只导入表结构。这些表的数据将后续通过 MView 日志刷新的方式同步。

declare
h1 number;
l_status varchar2(200);
begin
h1:=dbms_datapump.open (operation=>'IMPORT',job_mode=>'SCHEMA',job_name=>'IMPDB_FROM_PRODUCTION_BIGTABLES',version=>'COMPATIBLE',remote_link  => 'SOURCEDB_LNK');
DBMS_DATAPUMP.ADD_FILE (HANDLE=>H1,FILENAME=>'impdb_from_production_bigtables.log',DIRECTORY=>'DATA_PUMP_DIR',FILETYPE=>DBMS_DATAPUMP.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'SCHEMA_EXPR',VALUE=>' IN (''ROOT'',''USR1'',''USR2'',''USR3'')');
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'EXCLUDE_PATH_LIST',VALUE=>'''MATERIALIZED_VIEW_LOG'',''MATERIALIZED_VIEW'',''SEQUENCE'',''TRIGGER'',''VIEW'',''PACKAGE'',''PACKAGE_SPEC'',''PACKAGE_BODY'',''FUNCTION'',''PROCEDURE'',''SYNONYM''');
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'NAME_EXPR',VALUE => 'IN (''PI_RESULT'',''SPARAMETER'',''T_MAINTAIN_LOGISTICS'',''PAR_SPARAMETER'')', OBJECT_TYPE => 'TABLE');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'PI_RESULT',SCHEMA_NAME=>'ROOT');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'SPARAMETER',SCHEMA_NAME=>'ROOT');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'PI_RESULT',SCHEMA_NAME=>'USR1');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'SPARAMETER',SCHEMA_NAME=>'USR1');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'PAR_SPARAMETER',SCHEMA_NAME=>'USR1');
DBMS_DATAPUMP.DATA_FILTER (HANDLE=>H1,NAME=>'SUBQUERY',VALUE => 'WHERE 2=1',TABLE_NAME=>'T_MAINTAIN_LOGISTICS',SCHEMA_NAME=>'USR1');
DBMS_DATAPUMP.SET_PARAMETER (HANDLE=>H1,NAME=>'TABLE_EXISTS_ACTION',VALUE=>'REPLACE');
DBMS_DATAPUMP.SET_PARALLEL (HANDLE=>H1, DEGREE => 4);
DBMS_DATAPUMP.START_JOB (HANDLE=>H1);
DBMS_DATAPUMP.WAIT_FOR_JOB (HANDLE=>H1, JOB_STATE => L_STATUS);
end;
/

使用以下 SQL 查看导入进度以及结果。由于主用户在新实例中已经存在，因此日志中的 ORA-告警可以被忽略。

SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file(p_directory => 'DATA_PUMP_DIR',p_filename  => 'impdb_from_production_bigtables.log'));

TEXT
-------------------------------------------------------------------------
FLASHBACK automatically enabled to preserve database integrity.
Starting "ROOT"."IMPDB_FROM_PRODUCTION_BIGTABLES":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1576. GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ROOT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01924: role 'RECOVERY_CATALOG_USER' not granted or does not exist

Failing sql is:
 GRANT "RECOVERY_CATALOG_USER" TO "ROOT" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'RDS_MASTER_ROLE'

Failing sql is:
 GRANT "RDS_MASTER_ROLE" TO "ROOT"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "USR1"."SPARAMETER"                              0 rows
. . imported "USR1"."T_MAINTAIN_LOGISTICS"                    0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6906"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6909"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6917"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6920"              0 rows
. . imported "USR2"."USPARAMETER"                            0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6903"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6923"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6932"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6926"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6929"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6947"              0 rows
. . imported "USR2"."UPI_RESULT"                               0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6935"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6938"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6941"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6944"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6950"              0 rows
. . imported "USR1"."PAR_SPARAMETER":"SYS_P6957"              0 rows
. . imported "USR1"."PI_RESULT"                                0 rows
. . imported "USR1"."PAR_SPARAMETER":"P_START"                0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "ROOT"."IMPDB_FROM_PRODUCTION_BIGTABLES" completed with 4 error(s) at Tue Jun 25 00:27:39 2024 elapsed 0 00:00:15

53 rows selected.

4. 创建 MView Log 及 MView

在源数据库中对 6 张活跃表开启 Materialized View Log

CREATE TABLESPACE MVLOG DATAFILE SIZE 1G AUTOEXTEND ON;

CREATE MATERIALIZED VIEW LOG ON USR2.USPARAMETER TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON USR2.UPI_RESULT TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES; 

CREATE MATERIALIZED VIEW LOG ON USR1.PI_RESULT TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES; 

CREATE MATERIALIZED VIEW LOG ON USR1.PAR_SPARAMETER TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON USR1.SPARAMETER TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON USR1.T_MAINTAIN_LOGISTICS TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES;

在目标数据库中创建 MView。不同于常规 MView，本文使用 PREBUILT TABLE 选项，旨在向预定义表中同步数据。

CREATE MATERIALIZED VIEW USR2.USPARAMETER ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM ROOT.SPARAMETER@SOURCEDB_LNK;

CREATE MATERIALIZED VIEW USR2.UPI_RESULT ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM ROOT.PI_RESULT@SOURCEDB_LNK;

CREATE MATERIALIZED VIEW USR1.SPARAMETER ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM USR1.SPARAMETER@SOURCEDB_LNK;

CREATE MATERIALIZED VIEW USR1.PI_RESULT ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM USR1.PI_RESULT@SOURCEDB_LNK;

CREATE MATERIALIZED VIEW USR1.PAR_SPARAMETER ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM USR1.PAR_SPARAMETER@SOURCEDB_LNK;

CREATE MATERIALIZED VIEW USR1.T_MAINTAIN_LOGISTICS ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM USR1.T_MAINTAIN_LOGISTICS@SOURCEDB_LNK;

5. 活跃表全量数据加载

考虑到全量数据加载会对源数据库的磁盘 IO 以及网络 IO 有一定的资源消耗，这些活跃表将分批次选在业务低峰期执行。例如可以使用以下 PL/SQL 创建一次性定时任务同时触发对表 ‘USR2.UPI_RESULT 的全量数据加载。

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'MV_REFRESH_UPI_RESULT_FULL',
    JOB_TYPE        => 'PLSQL_BLOCK',
    JOB_ACTION        => Q'[BEGIN DBMS_MVIEW.REFRESH(LIST=>'USR2.UPI_RESULT',METHOD=>'C',PARALLELISM=>4); END;]',
    START_DATE      => SYSTIMESTAMP,
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'Refreshes Materialized Views First Load Full');
END;
/

使用以下 SQL 查看 MView 状态及任务进度。

SQL> SELECT OWNER,MVIEW_NAME,REFRESH_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_END_TIME FROM DBA_MVIEWS;

OWNER                          MVIEW_NAME                     REFRESH_M LAST_REF LAST_REFRESH_END_TI
------------------------------ ------------------------------ --------- -------- -------------------
USR2                           UPI_RESULT                      DEMAND    NA

SQL> SELECT OWNER,JOB_NAME,ELAPSED_TIME,CPU_USED FROM DBA_SCHEDULER_RUNNING_JOBS;

OWNER                          JOB_NAME                                           ELAPSED_TIME                   CPU_USED
------------------------------ -------------------------------------------------- ------------------------------ ------------------------------
USR2                           MV_REFRESH_UPI_RESULT_FULL                          +000 06:28:12.65               +000 01:37:42.39

完成全量数据同步的任务及 MView 状态如下：

SQL> SELECT OWNER,MVIEW_NAME,REFRESH_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_END_TIME FROM DBA_MVIEWS;

OWNER                          MVIEW_NAME                     REFRESH_M LAST_REF LAST_REFRESH_END_TI
------------------------------ ------------------------------ --------- -------- -------------------
USR2                           UPI_RESULT                      DEMAND    COMPLETE 2024-06-25 11:00:47

SQL> SELECT JOB_NAME,STATUS,RUN_DURATION,ACTUAL_START_DATE FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'MV_REFRESH%';

JOB_NAME                                           STATUS     RUN_DURATION                   ACTUAL_START_DATE
-------------------------------------------------- ---------- ------------------------------ ----------------------------------------
MV_REFRESH_USR2_UPI_RESULT_FULL                     SUCCEEDED  +000 10:02:37                  25-JUN-24 11.00.10.299453 AM +00:00;

6. 配置定时任务实现增量刷新

受数据量的影响全量数据同步可能需要较长的时间，本文 USR2.UPI_RESULT 表历时约 10 小时完成。检查任务及 MView 的状态无误之后可以通过以下 PL/SQL 创建定时增量刷新任务，以实现每 30 分钟同步一次数据。

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'MV_REFRESH_USR2_UPI_RESULT',
    JOB_TYPE        => 'PLSQL_BLOCK',
    JOB_ACTION        => Q'[BEGIN DBMS_MVIEW.REFRESH(LIST=>'USR2.UPI_RESULT',METHOD=>'F',PARALLELISM=>4); END;]',
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=30;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'Refreshes Materialized Views Fast');
END;
/

增量同步之后的 MView 状态如下：

SQL> SELECT OWNER,MVIEW_NAME,REFRESH_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_END_TIME FROM DBA_MVIEWS;

OWNER                          MVIEW_NAME                     REFRESH_M LAST_REF LAST_REFRESH_END_TI
------------------------------ ------------------------------ --------- -------- -------------------
USR2                           UPI_RESULT                      DEMAND    FAST    2024-06-25 12:25:05

重复步骤 5 及步骤 6，依次对各活跃表完成全量以及定时增量刷新。截至到目前所有操作均可在线完成。接下来需停止应用以导入静态表，因此在继续之前需确保各活跃表的增量刷新任务均工作正常。

7. 停止应用程序

从第 7 步到第 10 步需停止应用程序，期间将导入其余静态表及对象，最后一次活跃表增量刷新以及通过更改实例名的方式完成新旧数据库切换。

停止应用程序之后检查数据库中的进程，如果有必要可以将其强制终止并确保在维护窗口内不再有新用户连接到数据库。

SQL> SELECT SID,SERIAL#,USERNAME,STATUS,PROGRAM FROM V$SESSION WHERE SID<>SYS_CONTEXT('USERENV','SID') AND USERNAME IN ('ROOT','USR1','USR2','USR3') AND PROGRAM NOT LIKE 'oracle@ip%';

  SID    SERIAL# USERNAME   STATUS   PROGRAM
---------- ---------- ---------- -------- ---------------------------------------------
   5713      17731 USR1       INACTIVE python3.9@shoot-python-688f4bc9bc-q2w4n (TNS V1-
   8886       8376 USR1       INACTIVE python3.9@shoot-python-688f4bc9bc-q2w4n (TNS V1-


BEGIN
    rdsadmin.rdsadmin_util.kill(
        sid    => 5713, 
        serial => 17731,
        method => 'IMMEDIATE');
END;
/

8. 导入其余静态表及对象

以下数据泵命令导入除 6 张活跃表以外的所有对象。

declare
h1 number;
l_status varchar2(200);
begin
h1:=dbms_datapump.open (operation=>'IMPORT',job_mode=>'SCHEMA',job_name=>'impdb_from_production',version=>'COMPATIBLE',remote_link  => 'SOURCEDB_LNK');
DBMS_DATAPUMP.ADD_FILE (HANDLE=>H1,FILENAME=>'impdb_from_production.log',DIRECTORY=>'DATA_PUMP_DIR',FILETYPE=>DBMS_DATAPUMP.ku$_file_type_log_file);
DBMS_DATAPUMP.SET_PARALLEL (HANDLE=>H1, DEGREE => 8);
DBMS_DATAPUMP.SET_PARAMETER (HANDLE=>H1, NAME =>'TABLE_EXISTS_ACTION', VALUE=>'REPLACE');
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'SCHEMA_EXPR',VALUE=>' IN (''ROOT'',''USR1'',''USR2'',''USR3'')' );
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'NAME_EXPR',VALUE => 'NOT IN (''PI_RESULT'',''SPARAMETER'',''T_MAINTAIN_LOGISTICS'',''PAR_SPARAMETER'')', OBJECT_TYPE => 'TABLE');
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'NAME_EXPR',VALUE=>'NOT LIKE ''MLOG$%''',OBJECT_TYPE => 'TABLE');
DBMS_DATAPUMP.METADATA_FILTER (HANDLE=>H1,NAME=>'NAME_EXPR',VALUE=>'NOT LIKE ''RUPD$%''',OBJECT_TYPE => 'TABLE');
DBMS_DATAPUMP.START_JOB (HANDLE=>H1);
DBMS_DATAPUMP.WAIT_FOR_JOB (HANDLE=>H1, JOB_STATE => L_STATUS);
end;
/

使用以下 SQL 查看导入进度以及结果。源数据库中用于存储临时日志的表空间 MVLOG 不需要被导入，因此日志中额外的 ORA-告警可以被忽略。

SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file(p_directory => 'DATA_PUMP_DIR',p_filename  => 'impdb_from_production.log'));

TEXT
----------------------------------------------------------------
FLASHBACK automatically enabled to preserve database integrity.
Starting "ROOT"."impdb_from_production":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 102.9 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ROOT" already exists
ORA-31684: Object type USER:"USR1" already exists
ORA-31684: Object type USER:"USR2" already exists
ORA-31684: Object type USER:"USR3" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01924: role 'RECOVERY_CATALOG_USER' not granted or does not exist

Failing sql is:
 GRANT "RECOVERY_CATALOG_USER" TO "ROOT" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'RDS_MASTER_ROLE'

Failing sql is:
 GRANT "RDS_MASTER_ROLE" TO "ROOT"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA:"USR1"."MVLOG" failed to create with error:
ORA-00959: tablespace 'MVLOG' does not exist

Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "USR1" QUOTA 107374182400 ON "MVLOG"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN    IF SQLCODE = -30041
THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''MVLOG'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;      IF TEMP_C
OUNT = 1 THEN RETURN;       ELSE RAISE;       END IF;    ELSE      RAISE;    END IF;END;

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported " USR1"."COMPACTION_QUEUE"                94066 rows
. . imported " USR1"."awsdms_history"                       5769 rows
. . imported " USR1"."COMPLETED_COMPACTIONS"           8976 rows
. . imported " USR1"."NOTIFICATION_LOG"                   18976 rows
. . imported " USR1"." wx_corp_moment_message_task"       501107 rows
. . imported " ROOT"."AUDIT_20210806"                         43 rows
. . imported " ROOT"."AUDIT_DBA_20210806"                     43 rows
. . imported " USR1"."awsdms_apply_exceptions"                 0 rows
. . imported " USR1"."awsdms_suspended_tables"                 0 rows
. . imported " USR1"."awsdms_status"                           1 rows
. . imported " USR2"."tp_std_project_plan"                     506100 rows
……<purged>
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "ROOT"."impdb_from_production" completed with 8 error(s) at Wed Jun 26 12:53:31 2024 elapsed 0 00:35:26

63 rows selected.

验证源数据库与新数据库中的对象是否一致。假如活跃表中使用了 LOB 数据类型的字段，其依赖段的名称会无法匹配，此为预期现象。可以使用以下 SQL 进一步检查 LOB 属性予以确认。如以下 SQL 及输出：

SQL> SELECT S.OWNER,S.OBJECT_NAME,S.OBJECT_TYPE FROM DBA_OBJECTS@SOURCEDB_LNK S WHERE S.OWNER IN ('ROOT','USR1','USR2','USR3') AND S.OBJECT_NAME NOT LIKE 'MLOG$%' AND S.OBJECT_NAME NOT LIKE 'RUPD$%' MINUS SELECT T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE FROM DBA_OBJECTS T;

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
USR1       SYS_LOB0000132561C00024$$      LOB
USR1       SYS_IL0000132561C00024$$      INDEX

2 rows selected.

SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,INDEX_NAME,'S' S FROM DBA_LOBS@SOURCEDB_LNK WHERE TABLE_NAME='PAR_SPARAMETER' UNION ALL SELECT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,INDEX_NAME,'T' T FROM DBA_LOBS WHERE TABLE_NAME='PAR_SPARAMETER';

OWNER      TABLE_NAME      COLUM SEGMENT_NAME              INDEX_NAME                S
---------- --------------- ----- ------------------------- ------------------------- -
USR1       PAR_SPARAMETER  MSG   SYS_LOB0000132561C00024$$ SYS_IL0000132561C00024$$  S
USR1       PAR_SPARAMETER  MSG   SYS_LOB0000024329C00024$$ SYS_IL0000024329C00024$$  T

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER IN ('ROOT','USR1','USR2','USR3') AND STATUS = 'INVALID';

no rows selected

手工刷新 MView 确保所有日志都同步到了新数据库。至此新旧两个实例中的数据是一致的。

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR1.PAR_SPARAMETER',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR1.SPARAMETER',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR1.PI_RESULT',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR1.PAR_SPARAMETER',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR2.UPI_RESULT',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST=>'USR2.USPARAMETER',METHOD=>'F',PARALLELISM=>4);

PL/SQL procedure successfully completed.

SQL> SELECT OWNER,MVIEW_NAME,REFRESH_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_END_TIME FROM DBA_MVIEWS;

OWNER          MVIEW_NAME                     REFRESH_M LAST_REF LAST_REFRESH_END_TI
------------ ------------------------------ --------- -------- -------------------
USR1          SPARAMETER                     DEMAND    FAST     2024-06-26 15:54:27
USR2          USPARAMETER                    DEMAND    FAST     2024-06-26 15:54:07
USR2          UPI_RESULT                       DEMAND    FAST     2024-06-26 15:44:00
USR1          PI_RESULT                        DEMAND    FAST     2024-06-26 15:55:53
USR1          PAR_SPARAMETER                 DEMAND    FAST     2024-06-26 15:55:01
USR1          T_MAINTAIN_LOGISTICS            DEMAND    FAST     2024-06-26 15:54:53

6 rows selected.

9. 新旧实例切换

旧实例重命名。例如添加“_backup”作为后缀。

新实例重命名以便使用原有的终端节点（endpoint），同时启用自动备份即开启归档模式。

10. 启动应用程序

注意事项

为提高 IO 性能，新实例使用单 AZ NOARCHIVELOG 模式（实例 Automated backups 设置为 Disabled），并且与源实例使用相同的 Availability Zones；
方案部署期间源数据库暂停 DDL，例如更改表字段类型，更改存储过程、函数等；
为避免 ORA-01555 报错，建议调整新数据库的 undo_retention 参数及 undo 表空间；
对表分区可以显著提高全量数据加载的效率。如果处理单个段所需的时间太长建议用 DBMS_REDEFINITION 对源表预先转换，此在线操作不需要停机；
本方案借助 MView Log 实现，只支持 Oracle EE 版本；
本方案亦可用于 Oracle 数据库迁移及升级，如自建环境 Oracle 迁移至 RDS Oracle。

小结

RDS 实例不支持就地缩小存储卷的容量。假如 RDS Oracle 数据库的 SYSAUX TableSpace 占用较大空间, 受限于无法对其中的对象执行 TRUNCATE/MOVE/EXPDB/IMPDT 等操作，这些空间无法被有效地复用。本方案的实施可以显著降低 RDS 存储方面的费用，同时新实例中的数据被逻辑导入，彻底消除碎片化。如果能处理活跃表的同时对其中的大段进行分区转换，则新数据库中 SQL 的执行效率会明显提升，达到事半功倍的效果。

亚马逊AWS官方博客