So I am not working in the Public Cloud for a week and last night I got an email message mentioning, “Experiencing Slow Performance with Oracle Cloud” which were related to my created database instances. Actually this was nice because I had noticed this already while executing some test scripts (inserting 1 million XML documents in a table) which I was running overnight.
This morning a follow up mail mentioned that the issue was solved. So I head over to my Oracle Cloud management pages, in the meantime killed my SQL*Developer script, and just to start with a clean slate, I “restarted” the Oracle 12.1.0.2 Extreme Performance (XP) environment.
The following is about “How to figure out stuff”, and not about “How I solved …” (in the end, quick and dirty). Just so you know and would have the urge to comment stuff about ASM or alternatives cleanup methods…
Banners…
First of all, be aware that databases in the Oracle Cloud have different banners than one would expect initially (thanks for the hint Christian Antognini).
The banner for Enterprise Edition High Performance environments shows:
SQL> SELECT BANNER FROM V$VERSION WHERE ROWNUM =1;
BANNER
——————————————————————————–
Oracle Database 11g EE High Perf Release 11.2.0.4.0 – 64bit Production
The banner for Enterprise Edition Extreme Performance environments shows:
SQL> SELECT BANNER FROM V$VERSION WHERE ROWNUM =1;
BANNER
——————————————————————————–
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production
This info for all the script kiddies out there 😎
Database will not start
After the successful restart of the database instance, according to the database instance service web page, SQL*Developer still displayed an error mentioning that the Listener didn’t know about the service after my attempt to connect to the public cloud database instance.
So what now?
DBaaS Monitor
In the Compute Cloud pages, I enabled all monitor and HTTP(s) listed ports, so I would be able to see and/or I could do via the provided Cloud tooling for the 12.1.0.2 database environment (DBaaS Monitor pages, Database Express, Glassfish, APEX).
Then I head over to the DBaaS Monitor console pages using the initial password I had used to create the instance and the user account dbaas_monitor as is explained in “Accessing Oracle DBaaS Monitor“.
This didn’t really help me. Although I could figure out, see the status of the Listener, the Database Instance pages returned no data, also not for the alert log trace file which probably would show what the actual problem was.
There is no menu or option to restart, stop, start the database.
The database menu won’t show you what is in the alert file (and while thinking about it, this should be possible even without a running database)
The OS pages give you some insight in Memory, CPU and Storage and, in hind site, this could have alerted me to the problem at hand.
The listener page overview, shows the status of the listener, and indeed, showed no ORCL services or others registered.
Database Express / APEX console
The Database Express console is part, based on, the XDB Repository environment (embedded Adobe Flash “servlet”) and works only with the SYS, SYSTEM, etc., accounts if the database is started. So its not surprising this didn’t work (database is not up). The same goes for the APEX environment due to that this environment is build in the Oracle database.
GlassFish
Logging in the GlassFish Application Server environment, via the admin account, will show you the two serviced applications, APEX and the dbaas_monitor environments, but no possibility here to get insight into an database alert log file or otherwise (which is expected).
So the only alternative that is still on the table is connecting to the environment via a SSH client
Finding the problem via a SSH client
First of course, we have to create a session via a SSH client (Putty or otherwise). This is explained in the “Connecting to a Compute Node Through Secure Shell (SHH)” document.
Then I made, probably, a classic newbie mistake. I had forgotten that I had created 2 SSH Keys. While using the iTerm client on my Mac I used the “oracleCloud.pub” key, which was used by me continuously, creating database cloud instances.
If you make the same mistake, and just like me, didn’t create a key with a password, you might be triggered by the problem that you will be asked for a password that you never used/created by omitting the password option creating the SSH keys.
Anyway, switching to the “oracleCloud” key worked perfectly and logged me into the environment without asking for additional password details.
By the way, you should really have created SSH keys using the password option. You now notice the effect, that anyone who has the “oracleCloud” file and the TCP/IP number (or know how to resolve the server node name) can log into the oracle Linux account without knowing its password.
The problem
Via the SSH client connection you now can do your normal routine. Starting the database gave me the following issue: “ORA-03113: end-of-file on communication channel”
Now that we have SSH access, it becomes normal database management routine stuff. In the database alert file we see errors related to not being able to create new archive files due to lack of space.
I don’t need any safeguard via archive log mode in my temporary test environment, so for me the “quick and dirty” solution was to just disable the archive log mode of the database.
This did the trick and I was able to open the database again.
While checking the environment, indeed my insert overnight 1 million XML documents, created a lot of archive files, which in the end froze up & slowed down, my database environment.
As I would expect (regular ASM+ACFS based directory structure – need further investigation though), the environment is set-up using mount points
- /u01 – Oracle software
- /u02 – oradata / database files
- /u03 – fast recovery area / archive
- /u03 – redo (files)
The three redo files, are properly sized, that is 1 GB each.
Observations
Now the database is started, everything (…) can be accessed again. For example, the Database Express environment can again be used.
Strangely enough, the DBaaS Monitor environment still shows a status “Stopped” for the Database. This didn’t change after waiting a longer period.
Also my attempts to bounce (relaunch/restart) the dbaas_monitor application via the GlassFish console, didn’t also change the status. Therefore also “alert” content or other menu options still show no data.
So this is one of the items I want to investigate a bit more further (I don’t like loose ends – at least I want to know how to solve this).
Some of the observations I made were:
- Although it is easy for, lets say a senior developer, to create database instances, you still need Database Administration skills with the DBaaS service offering to patch, backup, configure and… solve database problems if you don’t have a Oracle support option doing this for you via managed services.
- You still need SSH, shell, skills or an Oracle Enterprise Manager Agent solution in place to properly be able to manage DBaaS environments.
- If you create database instances, like me, disabling the “Backup and Recovery Configuration” via the Database Instance Oracle Cloud pages, the database will have archiving enabled anyway (something I didn’t expect it to have).
- This additional database archiving will take up additional space. I still have to check if this has an impact on your overall storage quota…(probably should properly clean out the current FRA environment anyway).
- My first glance via ssh about the provided database environment is that it is properly configured, easy to manage (among others due to proper OFA guidelines), and this all “out-of-the-box”.
In all I hoped you enjoyed this post, giving you some additional insight what to expect and how to, for example, connect to the different pages and/or via client software to your Oracle Public Database as a Service environments.
HTH/M
Addendum
In the end, to get the DBaaS monitor up to speed again, still didn’t show the database up after waiting one day, I bounced/restarted the GlassFish environment completely. This did the trick. The DBaaS monitor works again as would be expected.
As pointed out by some of the commenters online, there is also a Linux user called opc. This account gives you the ability to sudo to, for example, the root account. I am not sure yet what the full purpose is of this account, but I found references in the documentation that it is used for, for example, applying database patches. This opc account is created with the same password as your oracle Linux account. To give you a bit of insight:
