Learn more about why Eclipsys has been named the 2023 Best Workplaces in Technology and Ontario, Certified as a Great Place to Work in Canada and named Canada’s Top SME Employer!
Learn more!
In this piece, I will walk you through a real-life scenario recently faced with a client where I had to change the SYS password, wallet password, TDE keystore password, and SYS password stored in the encryption wallet of an Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 container DB without any PDBs present. This was done on an Oracle Exadata Cloud at Customer environment.
Below, I will provide a log of the steps I followed and explain the steps/commands along the way.
[root@test-vm oracle]# . TESTDB.env [root@test-vm oracle]# echo $ORACLE_HOME /u02/app/oracle/product/19.0.0.0/dbhome_2 [root@test-vm oracle]# echo $ORACLE_SID TESTDB1 [root@test-vm oracle]# cd
First, we set the environment variables to make sure we are working with the correct Oracle Home and Oracle SID.
[root@test-vm ~]# ps -ef | grep pmon grid 42122 1 0 Apr02 ? 00:01:45 asm_pmon_+ASM1 grid 50781 1 0 Apr02 ? 00:01:45 apx_pmon_+APX1 oracle 318201 1 0 15:31 ? 00:00:00 ora_pmon_TESTDB1
We checked the PMON process to ensure the database instance is running.
[root@test-vm ~]# dbaascli database changepassword --dbname TESTDB DBAAS CLI version 23.1.1.0.1 Executing command database changepassword Job id: 2bb5b757-db93-42yg-b306-a098c5daeac1 Session log: /var/opt/oracle/log/TESTDB/database/changePassword/dbaastools_2023-04-24_04-22-44-PM_176968.log Enter username whose password change is required: sys Enter new password: Enter new password (reconfirmation): Loading PILOT... Session ID of the current execution is: 1354 Log file location: /var/opt/oracle/log/TESTDB/database/changePassword/pilot_2023-04-24_04-22-58-PM_180400 ----------------- Running Plugin_initialization job Enter username whose password change is required Enter new password ***************** Completed Plugin_initialization job ----------------- Running DB_change_password_prechecks job Completed DB_change_password_prechecks job ----------------- Running Extract_blob_in_standby job Skipping. Job is detected as not applicable. ----------------- Running Copy_blob_contents_in_standby job Skipping. Job is detected as not applicable. ----------------- Running Change_password job Completed Change_password job ----------------- Running Update_db_wallet job Completed Update_db_wallet job ----------------- Running Prepare_blob_for_standby_in_primary job Skipping. Job is detected as not applicable. dbaascli execution completed [root@test-vm ~]#
We used the dbaascli utility to change the SYS password. The tool guides you through the process and prompts you for the necessary information.
[root@test-vm ~]# su - oracle Last login: Mon Apr 24 16:23:34 ADT 2023 [oracle@test-vm ~]$ . TESTDB.env [oracle@test-vm ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 24 16:24:33 2023 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> set linesize 200 SQL> col WRL_PARAMETER for a60 SQL> col status for a45 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- ------------------------------------------------------------ --------------------------------------------- -------------------- --------- -------- --------- ---------- FILE /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ OPEN AUTOLOGIN SINGLE NONE NO 1 FILE OPEN AUTOLOGIN SINGLE NONE NO 2 SQL> quit Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
We connected to the database using SQL*Plus and checked the wallet status using the v$encryption_wallet view.
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -listCredential Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username)
The above output indicates that there are no credentials in the wallet for the “SYS” user or any other user for that matter
The following command lists the entries stored in the wallet including encryption keys and other security-related data.
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -list Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.Ae56789jkhgfddfyguiopoiuytuiAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AX567890KLJHGFDSFGHJKL89765467890-KJHGHJKLJHG76 ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.RETYUIOP89765467890876578OIUYGFHJKLJHGF ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.34567890-LKJHGFDSDFGHJKL;KJHGFHJKL;987654567890YTRFGH ORACLE.SECURITY.KM.ENCRYPTION.4567890-98765GHJKLJHGFDGHJKL;KJHGFRTYUIO6546789UYTYUIOUYTGHJ
As shown below there are no present credentials for the sys user
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde -viewEntry oracle.security.client.connect_string1 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: PKI-03002: No entry found for the alias: oracle.security.client.connect_string1 [oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -viewEntry oracle.security.client.username1 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: PKI-03002: No entry found for the alias: oracle.security.client.username1
We updated the wallet entries as required, including creating and modifying the TDE keystore password entry and setting up the required credentials for the “sys” user
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -createCredential TESTDB sys Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, 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: [oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -viewEntry oracle.security.client.username1 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: oracle.security.client.username1 = sys
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde -viewEntry oracle.security.client.connect_string1 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: oracle.security.client.connect_string1 = TESTDB [oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde -viewEntry oracle.security.client.password1 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: oracle.security.client.password1 = B@nkuL!ze
Now we create “tde_ks_passwd” since from the output below it is not present
[oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -viewEntry tde_ks_passwd Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: PKI-03002: No entry found for the alias: tde_ks_passwd [oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -createEntry tde_ks_passwd Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, 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: [oracle@test-vm ~]$ mkstore -wrl /var/opt/oracle/dbaas_acfs/TESTDB/wallet_root/tde/ -viewEntry tde_ks_passwd Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. Enter wallet password: tde_ks_passwd = B@nkuL!ze
[oracle@test-vm ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 24 16:42:26 2023 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY old_password SET new_password WITH BACKUP; keystore altered. SQL> quit Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
Finally, we used the ADMINISTER KEY MANAGEMENT SQL command to change the TDE keystore password.
In this piece, we walked through the steps required to change the SYS password, wallet password, TDE keystore password, and SYS password stored in the encryption wallet of an Oracle Database 19c container DB. This process was performed on an Oracle Exadata Cloud at Customer environment. Remember to back up your wallets and keys before making any changes.
References:
https://eclipsys.ca/how-to-change-sys-password-on-exacc-gen-2/
Encountering errors can often lead us down a rabbit hole of troubleshooting and investigative work. Recently, a scenario encountered highlighted the ... Read More
Introduction: Creating a clone of a remote pluggable database across a database link can be a powerful tool for managing and replicating data across ... Read More
