【博客文章2024】Oracle 19c创建虚拟专用恢复目录（基于VPD，不同于之前版本的实施方式）
1. 虚拟专用恢复目录的结构和原理：
从工作原理上看，这一项功能使得rman的恢复目录实现分级管理。这一点与之前版本中基本原理是完全相同的（参见多年前本人写的一篇博客文章：https://www.botangdb.com/mytec/mytec_basicadmin/201605/00900009.html）。 但是在12.1.0之后，该功能的实施方式发生了重大的变化。RMAN采用Oracle Virtual Private Database (VPD)来实施虚拟专用恢复目录。而在创建Recovery Base Catalog时，默认并未打开VPD功能，需要将其使能。
2. 创建Recovery Base Catalog并打开其VPD功能：
我们的实验环境中，在rcat.example.com数据库中，创建普通的Catalog Owner，并创建一个传统的恢复目录： [oracle@db ~]$ . oraenvORACLE_SID = [oracle] ? rcat
The Oracle base has been set to /u01/app/oracle
[oracle@db ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 16 07:15:51 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create tablespace tbsrcat datafile '/u01/app/oracle/oradata/rcat/tbsrcat01.dbf' size 20M autoextend on;
Tablespace created.
SQL> create user urcat identified by oracle_4U default tablespace tbsrcat quota unlimited on tbsrcat;
User created.
SQL> grant recovery_catalog_owner to urcat;
Grant succeeded. |
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ rman catalog urcat/oracle_4U
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 11:17:06 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> create catalog;
recovery catalog created |
执行$ORACLE_HOME/rdbms/admin/dbmsrmanvpc.sql脚本来为Recovery Base Catalog使能VPD模型：
[oracle@db ~]$ . oraenv ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 16 11:20:20 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmanvpc.sql -vpd urcat
Checking the operating user... Passed
Granting VPD privileges to the owner of the base catalog schema URCAT
========================================
VPD SETUP STATUS:
VPD privileges granted successfully!
Connect to RMAN base catalog and perform UPGRADE CATALOG. |
升级该Recovery Base Catalog：
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ rman catalog urcat/oracle_4U
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 11:26:49 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> upgrade catalog;
recovery catalog owner is URCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 19.03.00.00.00
DBMS_RCVMAN package upgraded to version 19.03.00.00
DBMS_RCVCAT package upgraded to version 19.03.00.00. |
3. Base Catalog用户注册目标数据库（展示ORCL数据库的注册过程，其余略）：
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ rman target sys/oracle_4U@orcl catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 12:40:11 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1696799610)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete |
4. 创建多个虚拟专用恢复目录，Base Recovery Catalog分配虚拟专用恢复目录的实验过程（展示VORCL，其余略）：
创建虚拟专用恢复目录用户vorcl：
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 16 11:59:21 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create user vorcl identified by oracle_4U default tablespace tbsrcat quota unlimited on tbsrcat;
User created.
SQL> grant create session to vorcl;
Grant succeeded. |
与以前版本不同，现在不需要grant recover_catalog_owner权限给虚拟专用catalog用户。也不需要create virtual catalog。 虚拟专用catalog用户还需要被授予catalog相应数据库权限：
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ rman catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 12:46:51 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> grant catalog for database orcl to vorcl;
Grant succeeded. |
该虚拟专用catalog用户只能访问被授权的数据库，并可以执行备份：
[oracle@db ~]$ rman target sys/oracle_4U@orcl catalog vorcl/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 12:54:23 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1696799610)
connected to recovery catalog database
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup tag 'TEST_CONTROLFILE' current controlfile;
Starting backup at 16-MAY-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3269 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-MAY-24
channel ORA_DISK_1: finished piece 1 at 16-MAY-24
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2024_05_16/o1_mf_ncnnf_TEST_CONTROLFILE_m4dgmy2d_.bkp tag=TEST_CONTROLFILE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-MAY-24
Starting Control File and SPFILE Autobackup at 16-MAY-24
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2024_05_16/o1_mf_s_1169124929_m4dgn12n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-24 |
Base Catalog用户能获得到虚拟专用catalog用户所注册的备份恢复数据：
[oracle@db ~]$ rman target sys/oracle_4U@orcl catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 12:59:17 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1696799610)
connected to recovery catalog database
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
310 Full 10.17M DISK 00:00:01 16-MAY-24
BP Key: 311 Status: AVAILABLE Compressed: NO Tag: TEST_CONTROLFILE
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2024_05_16/o1_mf_ncnnf_TEST_CONTROLFILE_m4dgmy2d_.bkp
Control File Included: Ckp SCN: 1166165 Ckp time: 16-MAY-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
318 Full 10.20M DISK 00:00:00 16-MAY-24
BP Key: 320 Status: AVAILABLE Compressed: NO Tag: TAG20240516T125529
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2024_05_16/o1_mf_s_1169124929_m4dgn12n_.bkp
SPFILE Included: Modification time: 15-MAY-24
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1166181 Ckp time: 16-MAY-24 |
5. 虚拟专用恢复目录用户主动注册数据库需要授权的实验过程：
虚拟专用数据库用户如果没有被授权，则无法主动注册任何其他数据库：
[oracle@db ~]$ rman target sys/oracle_4U@cdb2 catalog vorcl/oracle_4U@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 13:20:53 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB2 (DBID=916065645)
connected to recovery catalog database
RMAN> register database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 05/16/2024 13:21:02
RMAN-20012: not authorized to register new database |
Base Catalog用户授权虚拟专用数据库用户能主动注册任何其他数据库：
[oracle@db ~]$ rman catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 13:23:49 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> grant register database to vorcl;
Grant succeeded. |
虚拟专用数据库用户主动注册任何其他数据库：
[oracle@db ~]$ rman target sys/oracle_4U@cdb2 catalog vorcl/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 13:24:24 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB2 (DBID=916065645)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete RMAN> backup tag 'TEST_CONTROLFILE' current controlfile;
Starting backup at 17-MAY-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=368 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 17-MAY-24
channel ORA_DISK_1: finished piece 1 at 17-MAY-24
piece handle=+FRA/CDB2/BACKUPSET/2024_05_17/ncnnf0_test_controlfile_0.260.1169189399 tag=TEST_CONTROLFILE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAY-24
Starting Control File and SPFILE Autobackup at 17-MAY-24
piece handle=+FRA/CDB2/AUTOBACKUP/2024_05_17/s_1169189400.261.1169189401 comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-24 |
而Base catalog owner可以看到所有虚拟专用catalog用户注册的数据库：
[oracle@db ~]$ rman target sys/oracle_4U@cdb2 catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 17 06:50:45 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB2 (DBID=916065645)
connected to recovery catalog database
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
413 Full 17.92M DISK 00:00:01 17-MAY-24
BP Key: 414 Status: AVAILABLE Compressed: NO Tag: TEST_CONTROLFILE
Piece Name: +FRA/CDB2/BACKUPSET/2024_05_17/ncnnf0_test_controlfile_0.260.1169189399
Control File Included: Ckp SCN: 2408435 Ckp time: 17-MAY-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
421 Full 17.95M DISK 00:00:00 17-MAY-24
BP Key: 423 Status: AVAILABLE Compressed: NO Tag: TAG20240517T065000
Piece Name: +FRA/CDB2/AUTOBACKUP/2024_05_17/s_1169189400.261.1169189401
SPFILE Included: Modification time: 16-MAY-24
SPFILE db_unique_name: CDB2
Control File Included: Ckp SCN: 2408451 Ckp time: 17-MAY-24 |
6. 通过db表的rowid来验证Recovery Catalog的VPD属性：
分别连入Base Catalog用户和虚拟专用恢复目录用户，查询db表，就可以发现VPD功能在发挥作用。即：实际上以下两个查询访问的是同一张db表，虚拟专用恢复目录用户的查询被隐式地添加了过滤谓词，以只显示被授权的数据行：
[ora@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ sqlplus /nolog SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 17 06:54:55 2024
Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. SQL> conn urcat/oracle_4U
Connected.
SQL> select db_id, REG_DB_UNIQUE_NAME, rowid from db; DB_ID REG_DB_UNIQUE_NAME ROWID
---------- ------------------------------ ------------------
1142923459 CDB1 AAAcNIAACAAAAEcAAA
916065645 CDB2 AAAcNIAACAAAAEdAAA
92903991 EMREP AAAcNIAACAAAAEdAAB
1696799610 ORCL AAAcNIAACAAAAEfAAA
376305118 ORCL2 AAAcNIAACAAAAEfAAB
|
[oracle@db ~]$ . oraenv
ORACLE_SID = [rcat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db ~]$ sqlplus /nolog SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 17 06:59:44 2024
Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. SQL> conn vorcl/oracle_4U
Connected.
SQL> select db_id, REG_DB_UNIQUE_NAME from db; DB_ID REG_DB_UNIQUE_NAME ROWID
---------- ------------------------------ ------------------
916065645 CDB2 AAAcNIAACAAAAEdAAA
1696799610 ORCL AAAcNIAACAAAAEfAAA
|
附注： 之前版本的Recovery Base Catalog的升级过程如下：1）以sys身份执行$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql/脚本来更新recovery_catalog_owner这个权限；2）与本博客介绍的相同，执行$ORACLE_HOME/rdbms/admin/dbmsrmanvpc.sql脚本来为Recovery Base Catalog用户使能VPD模型；3）升级该Recovery Base Catalog。
|
