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.

Install SQLPlus

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.  

1. Download the following two binaries from https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html. If you are a Docker user you can get the SQL client here https://hub.docker.com/_/oracle-instant-client.
   oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm
    
2. Copy the Oracle sqlplus clients you downloaded previously to your target machine. scp *.rpm opc@<mymachine>:/tmp
3. Install the RPMs using yum because it is a little smarter since it can determine dependencies, but if you feel more comfortable using for example “rpm -Uvh” feel free.
   sudo yum install -y oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm sudo yum install -y oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm
4. Verify you have connectivity to your database by executing the following, it should return a message that it connected. Not sure what the database hostname is? Jump to the next section and find it in the Easy Connect string.
   nc -v <database_hostname> 1521
    
5. Now give it a shot and execute sqlplus.  If all went well you should get a login prompt.  Use control + C + Enter to break out of that and move on to the next section.
   SQL> SWEET!

Connect to the database

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.  

1. Login to the Oracle Cloud Infrastructure console.


2. Go to the Main Menu > Database > Bare Metal, VM, and Exadata.
    
3. Be sure to select your Compartment from the drop down and select your database from under the Display name column.
    
4. Under the Databases section and to the right click on the three dot ellipses menu and select View DB Connection.
    
5. Click the Copy link from the Easy Connect row.
    
6. Paste the Easy Connect string from the previous step into a text editor.
    
7. In your text editor change the string as follows replacing “<youstuff>” (Remove the less than "<" and greater than ">" too). sqlplus sys/<yourDbPassword>@<entire Easy Connect string> as sysdba
8. Now from a terminal on the machine running the SQL Plus client paste in the string created in the previous step and press enter. This should bring you to a SQL> prompt.

Resetting the password

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. 

1. From the SQL> prompt run the following two statements.  These will show the status of your users and existing DBA profiles.
   SQL> SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS; SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT';
2. On the Linux machine SQL plus client is installed create a text file named disable_pwd_expiry.sql then copy and paste the following statements into the file and save it.  
   ALTER PROFILE DEFAULT LIMIT;FAILED_LOGIN_ATTEMPTS UNLIMITED; PASSWORD_LIFE_TIME UNLIMITED; NOAUDIT ALL; DELETE FROM SYS.AUD$;
3. From the SQL> prompt execute the following command.
   SQL> @disable_pwd_expiry.sql
4. Now you can change the DEFAULT DBA PROFILE in order to reset the DEV_OAM password or any other application password that you have to keep the same for any reason.  I would revert this back to the default profile once you are done.
   SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED; SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
5. Finally reset the password for the two problem accounts DEV_OAM and DEV_OPSS; the examples are for OAM. 
   SQL> ALTER USER DEV_OAM IDENTIFIED BY <yourPasswordHere>; SQL> ALTER USER DEV_OPSS IDENTIFIED BY <yourPasswordHere>;
6. Now try to restart WebLogic.

Summary

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.