28 Aug 2020
How to change SYS password on ExaCC Gen 2
Changing Oracle Database user SYS password is quite straightforward on a normal deployment – not so much on an ExaCC Gen 2 environment. In fact, it could get really complicated with standby in place and no downtime allowed.
We need to consider the followings when doing so:
- Database user SYS password
Obviously. This is the purpose of this change.
- Backup assistant
We need to reconfigure backup assistant after changing the SYS password
- SYS credential stored in DB wallet (Keystore)
A Secure External Password Store (SEPS), a.k.a. DB wallet was created during database provision, which is used by Oracle internals. Only one record was created in the wallet that is SYS. This record needs to be updated after changing the SYS password.
- (optional but highly recommended) TDE wallet password
The admin password provided during database provision applies to both user SYS and TDE wallet. Though they could be different, we highly recommend changing the wallet password at the same time. This will reduce a lot of confusion in the future, and plus, there is one less password to maintain.
- DR site
If you have DR servers, obviously you need to carry out necessary updates on them as well.
Let’s see how we handle those.
DB user SYS and backup assistant
These are done by using dbaascli database changepassword.
Tips: Never use alter user sys identified by to change SYS password on ExaCC.
After configuring Oracle environment variables (ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH, etc.) as root, run dbaascli database changepassword and specify SYS to start the procedure for changing SYS password.
# export ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_2 # export ORACLE_SID=testdb01 # export LD_LIBRARY_PATH=/u02/app/oracle/product/19.0.0.0/dbhome_2/lib # export PATH=$ORACLE_HOME/bin:$PATH # dbaascli database changepassword DBAAS CLI version 19.4.4.2.0 Executing command database changepassword Enter username whose password change is required: sys Enter new password: Re-enter new password: corereg: new: rfile = Successfully changed the password for user sys Reconfiguring bkup Assistant, this might take some time, please take a look at log /var/opt/oracle/log/testdb01/bkup/bkup.log for progress Successfully reconfigured bkup assistant
It also updates the SYS password on your physical standby database on ExaCC. You can always verify it by logging into the database remotely.
From the log, we can see it also takes care of the backup assistance if you have one.
SYS credential stored in DB Wallet
A Secure External Password Store (SEPS) was created during the database provision on ExaCC. Unfortunately, the dbaascli database changepassword script does not update the credential within the database wallet. We have to do it manually.
The location of the database wallet is specified by WALLET_LOCATION parameter in the sqlnet.ora file.
Tips: SEPS and TDE wallet are different things. TDE wallet is specified by ENCRYPTION_WALLET_LOCATION parameter in the same sqlnet.ora file.
There is only one record in the wallet that is the SYS user with the TNS name alias the same as the database.
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -listCredential List credential (index: connect_string username) 1: testdb01 sys
The list of entries stored in the wallet:
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -list Oracle Secret Store entries: oracle.security.client.connect_string1 oracle.security.client.password1 oracle.security.client.username1 passwd tde_ks_passwd
We have only one credential, obviously, it’s connect_string1/password1/username1.
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -viewEntry oracle.security.client.connect_string1 oracle.security.client.connect_string1 = testdb01 $ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -viewEntry oracle.security.client.username1 oracle.security.client.username1 = sys $ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -viewEntry oracle.security.client.password1 oracle.security.client.password1 =
Use this command to update the SYS password stored in the wallet
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -modifyCredential testdb01 sys
We check it again after the change.
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -viewEntry oracle.security.client.password1 oracle.security.client.password1 =
You may also notice that we also have a tde_ks_passwd stored in this wallet.
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -viewEntry tde_ks_passwd tde_ks_passwd =
We want to change that as well. Please note we use modifyEntry instead of modifyCredential in this case.
$ mkstore -wrl /var/opt/oracle/dbaas_acfs/testdb01/db_wallet -modifyEntry tde_ks_passwd
Repeat these steps on the DR servers.
TDE Wallet Password
The TDE wallet password should be the same as SYS password to reduce confusion in the future. It’s a good practice to change the TDE wallet password at the same time we do SYS.
Log into the container database as SYSDBA, and run:
SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY software_keystore_old_password SET software_keystore_new_password WITH BACKUP;
Please note the keyword FORCE KEYSTORE is mandatory with auto-login enabled. Otherwise, you will get a misleading error message ORA-28417: password-based keystore is not open, or if you are very old-school and use alter system command, ORA-28365: wallet is not open.
This bug was fixed in 12.2 and also backported in 12.1.0.2 with Jan 2018 PSU patch applied. The keyword FORCE KEYSTORE was introduced in the patch fix.
DR Site
The SYS password and DB wallet have already been taken care of on the DR site in the previous steps. What we need to do now is to update the TDE wallet without any downtime.
First, we need to take file backup of wallet file ewallet.p12 and auto-login cwallet.sso on the DR ExaCC servers.
Then we copy and overwrite these files from primary site to standby site. At this time, the standby database is still up running because the master keys are already cached in the memory.
Next, we need to close the auto-login wallet.
SQL> administer key management set keystore close;
At this time, the master keys are cleared from memory and wallet closed.
Next time when there is any encrypted data accessed, standby DB would automatically open the newly copied TDE wallet because we have auto-login enabled.
In the below example, you can see the wallet is opened almost instantaneously because we have redo real-time apply enabled.
SQL> select wrl_parameter, status from v$encryption_wallet; WRL_PARAMETER STATUS ------------------------------------------------------------- ----- /var/opt/oracle/dbaas_acfs/testdb01/tde_wallet/ OPEN SQL> Administer key management set keystore close; keystore altered. SQL> select wrl_parameter, status from v$encryption_wallet; WRL_PARAMETER STATUS ------------------------------------------------------------- ----- /var/opt/oracle/dbaas_acfs/testdb01/tde_wallet/ OPEN
