Script your RMAN backups using Oracle External Password Store with the SYSBACKUP privilege

21 Feb Script your RMAN backups using Oracle External Password Store with the SYSBACKUP privilege

As you may already know, scripting your RMAN backups and storing your connectivity credentials in the actual scripts is not a good security practice. Sometimes even hiding the actual password through another script or a mechanism for this will expose your password. I won’t go into details as of why you should not do this, but you can see Simon’s post about it.

The best way to avoid exposing your passwords is using Oracle External Password Store, but  One of the problems that you have by using this methodology, is that you can’t easily script your backups.

So how is it that you can script your way through this? 

First, I’ll show you how I setup my Oracle External Password Store, and then I will go into the details of how you can script this. 

I start by by creating a backup user that will be used with Oracle External Password Store.

create user RMANBKP identified by <PASSWORD>;
grant SYSBACKUP to RMANBKP;  

Create a directory to store your wallet and adjust the sqlnet.ora file to point to an Oracle Wallet location

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )
SQLNET.WALLET_OVERRIDE = TRUE

Adjust the tnsnames.ora file to include an entry that will be used by Oracle External Password Store for your database backups

oracle@rene-ace-test-inst1 admin]$ echo "DBTEST_RMANBKP =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(HOST = rene-ace-test-inst1)(KEY = LISTENER))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = DBTEST)
    )
 )" >> $ORACLE_HOME/network/admin/tnsnames.ora
  
[oracle@rene-ace-test-inst1 admin]$ tnsping DBTEST_RMANBKP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-FEB-2022 15:32:56

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(HOST = rene-ace-test-inst1)(KEY = LISTENER)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBTEST)))
OK (0 msec)

Now, I create the Oracle Wallet files. The password that is asked for below is for the wallet

[oracle@rene-ace-test-inst1 scripts]$ mkstore -wrl "/u01/app/oracle/wallet" -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:

Then I add a credential to the Oracle External Password Store
In the example below, we are using the tnsentry called DBTEST_RMANBKP and the user RMANBKP which was created in step 1.
The secret/Password is the password for RMANBKP
The following password is the wallet password

[oracle@rene-ace-test-inst1 scripts]$ mkstore -wrl "/u01/app/oracle/wallet" -createCredential DBTEST_RMANBKP rmanbkp
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:   
Re-enter your secret/Password:   
Enter wallet password:   

You can now test your Oracle External Password Store by using the TNSENTRY mentioned when you created the credential

[oracle@rene-ace-test-inst1 ~]$ echo "show user" | sqlplus -s /@DBTEST_RMANBKP as sysbackup
USER is "RMANBKP"

Now  that I have setup my Oracle External Password Store, I’ll go through the errors that led me to finding this solution, the first thing I did was try to connect using the DBTEST_RMANBKP tnsentry mentioned when you created the credential.

[oracle@rene-ace-test-inst1 scripts]$ rman /@DBTEST_RMANBKP

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 13:15:29 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges

So the next thing I did was search for how to solve this error , and I saw that it had to do with me not using SYSBACKUP , but as soon as I added it to my connection string for the TARGET, I faced the following error:

[oracle@rene-ace-test-inst1 scripts]$ rman target /@DBTEST_RMANBKP as sysbackup

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
auxiliary    quoted-string  connect-string for auxiliary database
...
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "@": expecting one of: "double-quoted-string, identifier, single-quoted-string, "
RMAN-01007: at line 2 column 1 file: command line arguments

So I started searching for a reason as to why this was happening, and found the MOS note RMAN SYSBACKUP fails with ORA-01031 (Doc ID 2143950.1) , which states the following “Connect to RMAN prompt first and use SYSBACKUP to connect.”,  and as you can see this works

[oracle@rene-ace-test-inst1 scripts]$ rman 

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 14:10:29 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target '/@DBTEST_RMANBKP as sysbackup'

connected to target database: DBTEST (DBID=1479858209)

RMAN> exit

Even though it works, I can’t script my way once I am in the RMAN command prompt. So I had to find another solution.

The first thing that came into my mind, was to pipe this 

[oracle@rene-ace-test-inst1 scripts]$ echo "connect target '/@DBTEST_RMANBKP as sysbackup'" |  rman

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 14:17:44 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> 
connected to target database: DBTEST (DBID=1479858209)

RMAN> 

Recovery Manager complete.

As I can see the pipe works. But what happens when I have a dynamic script where there are may be over 35 lines of RMAN code coming in? From my point of view this is not an optimal solution. So my next train of thought is ask the question: Can I pass a command file with a pipe ? And the short answer is no, as it will first try to run the command file and then the connection string that I piped

[oracle@rene-ace-test-inst1 scripts]$ echo "connect target '/@DBTEST_RMANBKP as sysbackup';" |  rman cmdfile=rman.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 14:33:00 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> show all;
2> 
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 02/18/2022 16:33:00
RMAN-06171: not connected to target database

Recovery Manager complete.

An idea a teamate gave me is, why not use a variable and try to connect, but I had the same problem

[oracle@rene-ace-test-inst1 scripts]$ export CONNECT_STRING="connect target '/@DBTEST_RMANBKP as sysbackup'"

[oracle@rene-ace-test-inst1 scripts]$ rman $CONNECT_STRING cmdfile=rman.cmd

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
...
checksyntax  none           check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "connect": expecting one of: "append, auxiliary, catalog, checksyntax, cmdfile, log, msgno, nocatalog, pipe, script, send, target, timeout, using, @, ;"
RMAN-01007: at line 2 column 1 file: command line arguments

After doing several tests, I was finally able to connect using double quotation marks.

[oracle@rene-ace-test-inst1 scripts]$ rman target '"/@DBTEST_RMANBKP as sysbackup"' cmdfile=rman.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 17:26:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBTEST (DBID=1479858209)

RMAN> show all;
2> 
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DBTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_DBTEST.f'; # default

Recovery Manager complete.

The problem became though that as soon as I put it in an sh script, it failed as the single and double quotation marks weren’t playing nice and causing errors in the connectivity as you can see when I debugged the script

[oracle@rene-ace-test-inst1 scripts]$ ./backup.sh 
set -x
...
++ rman 'target'\''"/@DBTEST_RMANBKP' as 'sysbackup"'\'''

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 18 21:24:54 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-10000: error parsing target database connect string ""/@DBTEST_RMANBKP"

After playing around for a while , I found the solution to this, which is to use the line below marked in bold red

[oracle@rene-ace-test-inst1 scripts]$ cat backup.sh 
ORACLE_SID=DBTEST
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
export ORAENV_ASK=YES
PATH=$PATH:$ORACLE_HOME/bin
TARGET_WALLET_ENTRY="DBTEST_RMANBKP"
rman target \'"/@${TARGET_WALLET_ENTRY} as sysbackup"\' cmdfile=show.cmd

[oracle@rene-ace-test-inst1 scripts]$ cat backup.cmd
show all;

[oracle@rene-ace-test-inst1 scripts]$ ./backup.sh 

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Feb 21 21:22:59 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBTEST (DBID=1479858209)

RMAN> show all;
2> 
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DBTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_DBTEST.f'; # default

Recovery Manager complete.

Basically what you have to do, is escape the single quotation marks, so that you can now use Oracle Secure Wallet entry with your RMAN backup scripts. Hope this small tip helps you along the way.