The ZDL, like other Oracle appliances and technologies, can be managed from the Oracle Enterprise Manager, a graphical web tool, to manage its infrastructure. However, the tool has some bugs. This error appeared after the user responsible for the RMAN catalog in the ZDL was changed, where the OEM tries to create a catalog, with each user added in the Recovery Catalog configurations.
The error does not impact the environment critically, but it would be good to fix it.
rman target / catalog /@zdlra01
Recovery Manager: Release 19.0.0.0.0 – Production on Wed Aug 16 09:25:39 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: XXX (DBID=123456789)
connected to recovery catalog database
recovery catalog schema version 21.01.00.00. is newer than RMAN version
RMAN-07540: Cleanup is required for the VPC user schema
To do this, we will first find out which VPC user is actually responsible for the RMAN catalog.
sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc -scan
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Sep 13 08:54:21 2023
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.13.0.0.0
Checking the operating user… Passed
RMAN base catalog owners schemas report as of 2023/09/13 08:54:21
================================================================================
Acronyms used:
VPC — Virtual Private Catalog users
VPD — RECOVERY_CATALOG_OWNER_VPD role
RCO — RECOVERY_CATALOG_OWNER role
RCU — RECOVERY_CATALOG_USER role
RMAN Base Catalog VPD RCO VPC VPC dropped | VPC with VPC with VPC with
schema owner role role count from DB | VPD role RCO role RCU role
——————– —- —- —— ———– – ——– ——– ——–
RASYS YES YES 2 0 | 0 0 0
RMAN Base Catalog VPD RCO RCU
schema owner VPC user role role role Dropped
——————– ——————– —- —- —- ——-
RASYS RAVPC1 NO NO NO NO
REPUSER_FROM_RA01 NO NO NO NO
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.13.0.0.0
You will get an output like this. The user RASYS is the default administrator user for the ZDL database, similar to SYS, but it contains the appliance’s administrative packages like dbms_ra.
REPUSER is a naming convention used for replication users between appliances. These users, although Virtual Private Users, are not the same as those used as owners of the RMAN catalog.
The user we are looking for is the VPC user RAVPC1, created during the appliance deployment.
Next, we can execute the creation extraction of this user with the query below.
sqlplus / as sysdba
set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
undefine User_in_Uppercase;
set linesize 1000
set long 2000000000
select (case
when ((select count(*)
from dba_users
where username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '&User_in_Uppercase') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&User_in_Uppercase') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V$PWFILE_USERS
where username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'&User_in_Uppercase'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual
/
The output of this query will be something like the output below:
Enter value for user_in_uppercase: RAVPC1
— Note: Profile
CREATE PROFILE “VPC_STIG”
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME UNLIMITED
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION “ORA12C_STIG_VERIFY_FUNCTION”
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
INACTIVE_ACCOUNT_TIME 3024000/86400
PASSWORD_ROLLOVER_TIME 28800/86400 ;
— Note: Create user statement
CREATE USER “RAVPC1” IDENTIFIED BY VALUES ‘S:XXXXXXXXXX’
DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”
PROFILE “VPC_STIG”;
— Note: No TS Quotas found!
— Note: No granted Roles found!
— Note: No sysdba administrative Privilege found!
— Note: System Privileges
GRANT CREATE SESSION TO “RAVPC1”;
GRANT CREATE TYPE TO “RAVPC1”;
— Note: No Object Privileges found!As a proposed solution by Oracle, the solution was to remove the additional privilege that the OEM granted while configuring the Replication Catalog.
-- 1) Login to zdlra database and revoke the additional privileges
sqlplus / as sysdba
REVOKE CREATE TYPE FROM “RAVPC1”;
-- 2) From protected database , reattempt
rman target / catalog /@zdlra01
Recovery Manager: Release 19.0.0.0.0 – Production on Thu Sep 21 14:19:21 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: XXX (DBID=123456789)
connected to recovery catalog database
recovery catalog schema version 21.01.00.00. is newer than RMAN version
RMAN> show all;
RMAN configuration parameters for database with db_unique_name XXX are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ FORMAT ‘%d_%U’ PARMS “SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/lib/libra.so, ENV=(RA_WALLET=’location=file:/u01/app/oracle/product/WALLETS/XXX/geral credential_alias=zdlra01’)”;
CONFIGURE MAXSETSIZE TO 125 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/oracle/XXX/snapcf_XXX.f’;
My Piece of Data 