Running Oracle Infrastructure Cloud database services on Bare Metal, VM, or ExaData is really great. From the console you spin up a new database by selecting your shape, answer some questions, and instant database. A lot of niceties are there to take care of things that normally has to do be done manually. Then suddenly, in my case, my Oracle Access Manager WebLogic instance, which was using a cloud database to store data, failed to start. The WebLogic logs output looked something like the following:
WARNING: Could not create credential store instance. Reason oracle.security.jps.service.policystore.PolicyStoreConnectivityException: JPS-00027: There was an internal error: java.sql.SQLException: ORA-28001: the password has expired
JPS-01055: Could not create credential store instance. Reason oracle.security.jps.service.policystore.PolicyStoreConnectivityException:
JPS-00027: There was an internal error: java.sql.SQLException: ORA-28001: the password has expired
Error: Diagnostics data was not saved to the credential store.
Error: Validate operation has failed.
Need to do the security configuration first!
In my case, it was clear the DEV_OAM schema password expired and the account was locked out. As a side note, DEV_OAM is a default name defined during the database schema creation when setting up OAM, yours may be different. That explanation is not so important because your use case could be different. Fast forward, I discovered the default database profile policies created on Oracle Cloud had some defaults that included expiring passwords on all accounts.
Below is an example of some of the profiles in my database, I will show you how to get this list later by executing a couple SQL statements.
PROFILE RESOURCE_NAME RESOURCE LIMIT ------- ------------------------ -------- ---------------------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD 365 DEFAULT PASSWORD_REUSE_MAX PASSWORD 5 DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
To solve this problem I would normally start a terminal and ssh into the machine running the database, use SQL Plus to connect as sysdba, and reset the password. It suddenly dawned on me I couldn't access the machine like a normal server. As I began my research I found some official documents that gave a little clue on how to connect to the database using SQL Plus, but it was not always clear and for me it did not work. Through some trial and error I finally figured out the trick and want to share it with you. I think this is a valuable little nugget for any of those people using a database that runs as the Oracle Cloud Infrastructure's database services.
If you have a machine that has 1) connectivity to the database, and 2) SQL Plus installed, you can skip to the next section. This section will show how to easily install SQL Plus client on a Linux machine. Note following list of RPMs you are asked to download were the latest at the time this article was written.
scp *.rpm opc@<mymachine>:/tmp
sudo yum install -y oracle-instantclient19.3-basic-22.214.171.124.0-1.x86_64.rpm sudo yum install -y oracle-instantclient19.3-sqlplus-126.96.36.199.0-1.x86_64.rpm
nc -v <database_hostname> 1521
This section is the key ingredient to the recipe. It shows you how to connect to the remote database services database and get a SQL prompt.
sqlplus sys/<yourDbPassword>@<entire Easy Connect string> as sysdba
If all you needed was to get to the SQL prompt on the database feel free to skip this section. This section covers more about showing the current database profiles and reseting a password from the SQL prompt.
SQL> SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS; SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT';
ALTER PROFILE DEFAULT LIMIT;FAILED_LOGIN_ATTEMPTS UNLIMITED; PASSWORD_LIFE_TIME UNLIMITED; NOAUDIT ALL; DELETE FROM SYS.AUD$;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED; SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
SQL> ALTER USER DEV_OAM IDENTIFIED BY <yourPasswordHere>; SQL> ALTER USER DEV_OPSS IDENTIFIED BY <yourPasswordHere>;
The biggest take away is understanding how to get a SQL prompt to a database that is runing in Oracle Infrastructure Cloud which is part of database services versus a database installed on a cloud virtual server that is more similar to on-premises where you would have full control. So to give up control in favor of a more automated platform you have to learn new ways to deal with situations like this. Consider this a little tidbit to keep in your back pocket for one of those days.