Let’s take a look at the various options to connect to PDB target via RMAN, and the pitfalls associated, especially when using the environment variable ORACLE_PDB_SID. I ran into an issue with a script misbehaving when the parameter was set, which prompted this post.
First, let’s take a look at the most commonly used authentication options: these would be Password-based and Operating System (OS) based authentications. But, irrespective of the authentication option used, the user needs either the SYSDBA or SYSBACKUP privileges in order to connect via RMAN .
Password-Based Authentication
Password-Based authentication is perhaps the easiest way to connect to a PDB target. That being said, connections using wallets (Secure External Password Store) are the recommended option and should always be preferred over-specifying the password on the command line.
- User with SYSDBA Privilege – The ”
as sysdba” clause is implicit and hence not required to be written explicitly to connect to the PDB
-bash-4.2$ rman target sys@pdb2
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Oct 6 05:30:21 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: CDB1:PDB2 (DBID=2163078853)
RMAN> select user, ora_database_name from dual;
using target database control file instead of recovery catalog
USER ORA_DATABASE_NAME
---- ------------------------------------------------------------------
SYS PDB2
- User with SYSBACKUP Privilege – The
as sysbackupis not implicit, hence if the user has sysbackup privilege then this clause needs to be stated in the connect string.
rman target '"rman_backup@pdb2 as sysbackup"'
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Oct 6 05:31:54 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: CDB1:PDB2 (DBID=2163078853)
RMAN> select user, ora_database_name from dual;
using target database control file instead of recovery catalog
USER ORA_DATABASE_NAME
--------- ------------------------------------------------------------------
SYSBACKUP PDB2
OS Based Authentication
At the first glance using OS based authentication to PDB seems pretty straightforward given that we have the ORACLE_PDB_SID variable at our disposal, however, there is a catch. Although this works with RMAN, it only works for OS users that belong to the OSDBA group, and not for the OSBACKUP Group. Let’s take a look
Note – The OSDBA and OSBACKUP groups are defined during installation. To view this information use the osdbagrp executable
-bash-4.2$ echo -e "OSDBA group is ` osdbagrp -d ` \nOSBACKUP group is ` osdbagrp -b `"
OSDBA group is dba OSBACKUP group is backupdba- Attempt to logon as an OS user that is a part of the OSDBA group (e.g. dba) on the server.
As we see the oracle user is connected using the sysdba privileged account – SYS
-bash-4.2$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$ export ORACLE_PDB_SID=PDB2
-bash-4.2$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Oct 6 06:33:32 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1:PDB2 (DBID=2163078853)
RMAN> select user, ora_database_name from dual;
using target database control file instead of recovery catalog
USER ORA_DATABASE_NAME
---- ------------------------------------------------------------------
SYS PDB2 - Attempt to connect as sysbackup using an account that is part of OSBACKUP group.
As we see here, this does NOT work as expected, or does it? As discussed by Mike.Dietrich in the Pitfalls of using ORACLE_PDB_SID, the after logon trigger DBMS_SET_PDB is only designed for SYS & SYSTEM users. This is the thing we need to be careful about.
In the below configuration ( Use of ORACLE_PDB_SID + sysbackup), our connection will be logged as SYSBACKUP, and the trigger does not work for SYSBACKUP. So the trigger will NOT fire, and we will be connected to the CDB and not the PDB.
Hence the use of ORACLE_PDB_SID should be avoided, given that it depends on a database package and its behaviour could change in upcoming releases.
The script I came across had ORACLE_PDB_SID set and when trying to connect as SYSBACKUP, it would connect to CDB instead of the PDB and thereby subsequent RMAN commands were not behaving as expected.
-bash-4.2$ export ORACLE_PDB_SID=PDB2
-bash-4.2$ rman target '"/ as sysbackup"'
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Oct 6 06:39:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=1058171882)
RMAN> select user, ora_database_name from dual;
using target database control file instead of recovery catalog
USER ORA_DATABASE_NAME
--------- ------------------------------------------------------------------
SYSBACKUP CDB1