We published this VirtualBox Appliance last week, and I even made a video talking about how to get started using it.
But, as always, things come after you release stuff. Here’s a few notes on things you may run into.
Diagnostic & Tuning Pack Features don’t seem to be working…
I was trying to run some reports and noticed these errors popping up in my server logs.
SQL Error Code 13716, Error Message: ORA-13716: Diagnostic Package License is needed for using this feature.
I pinged Gerald, who confirmed it needs to be enabled.
So if you want a SQL Monitoring Report, you’re going to need to do this first.
Connect to the FREE CDB instance as SYS, run the ALTER SYSTEM, then bounce the database.
[email protected] ~]$ unset TWO_TASK [[email protected] ~]$ SQL / AS sysdba ... Connected TO: Oracle DATABASE 23c Free, Release 126.96.36.199.0 - Developer-Release Version 188.8.131.52.0 SQL> ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' SCOPE=SPFILE; System altered. SQL> shutdown IMMEDIATE DATABASE closed. DATABASE dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1608409424 bytes Fixed SIZE 10043728 bytes Variable SIZE 872415232 bytes DATABASE Buffers 721420288 bytes Redo Buffers 4530176 bytes DATABASE mounted. DATABASE opened. SQL>
Now to try it out…
Run this query –
SELECT /*+MONITOR*/ t.table_name, USER AS owner, t.cluster_name, t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, ROUND (t.num_rows) num_rows, t.TEMPORARY, t.table_type, t.table_type_owner, t.NESTED, DECODE ( NVL (tablespace_name, 'x') || UPPER (partitioned) || NVL (iot_type, 'x') || TO_CHAR (pct_free), 'xNOx0', 'YES', 'NO' ) is_external, t.dropped, t.initial_extent FROM SYS.user_all_tables t WHERE 1 = 1;
Then as that same user, in SQL Developer (desktop or Web), access the SQL Monitoring Reports.
Performance Hub feature in SQL Developer Web or Tools > Monitor SQL in SQL Developer.
ORDS seems to be crashing on the Linux VM
If SQL Developer Web starts to load, and then…doesn’t, Java has likely crashed on the host. What’s happening? Java is setup by default to reserve 25% of the SYSTEM installed memory for an application. Um, there’s not quite enough free, probably.
1 – give your VM more memory. This is in the VirutalBox config, and the easiest point-and-click if you have the memory to burn.
2 – tell Java to back off!
Well, set an ENV VAR to send some JVM flags that ORDS will see when it starts up.
$ $ export _JAVA_OPTIONS="-Xms128m -Xmx1024m" $ /bin/bash /home/oracle/ords/bin/ords --config /home/oracle/ords_config serve --port 8080 --apex-images /home/oracle/apex_images Picked up _JAVA_OPTIONS: -Xms128m -Xmx1024m ORDS: Release 23.1 Production on Mon Apr 10 19:44:00 2023 Copyright (c) 2010, 2023, Oracle. Configuration: /home/oracle/ords_config/ 2023-04-10T19:44:00.918Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080 2023-04-10T19:44:00.995Z INFO Disabling document root because the specified folder does not exist: /home/oracle/ords_config/global/doc_root 2023-04-10T19:44:07.886Z INFO Configuration properties for: |default|lo| ...
You should be good to go now – just edit the startup scripts to add that line before ORDS is brought up when the machine boots up.
Enabling the MongoAPI for Oracle feature of ORDS
Hermann wrote this great blog post. You have two steps:
- enabling the feature in ORDS config
- creating a port forward so it’s easy to connect from your host machine
This is also shown in the ORDS Docs. Note that 23c isn’t required for this feature. Ping me if you want try it on 19c.
We’ll keep these things in mind for our next VirtualBox update
We want the experience to be as easy and seamless as possible for our developers, so if/when we next update the VirtualBox Appliance, we’d love your feedback! Leave a comment with suggestions/ideas or even just your questions.
Also don’t forget to like and subscribe my YouTube stuff. Remember, my kids don’t believe that I’m a ‘real’ YouTuber.
Disclaimer: Jeff isn’t really a real YouTuber, but he likes to pretend.
Did the database api supposed to work on the vm ?
I enabled ssl, granted dba, pdb_dba too pdbadmin and ords_admin.enebled the user and most of the api’s are not working.
returning 403 forbidden on 401 not authorized.
for example: http://localhost:8080/ords/pdbadmin/_/db-api/stable/database/status -> 403 -> and many others
but, this works: http://localhost:8080/ords/pdbadmin/_/db-api/stable/database/version
It’s enabled, yes. It should be working just fine. The only thing not setup there are the CDB/PDB Lifecycle Management operations.
VM is great, couple of suggestions:
1.Run EXEC DBMS_STATS.gather_database_stats; before you package it
2. On the info HTML the Database Information section says “Oracle SID = free/freepdb1”. If as you suggest connecting from your host desktop, that won’t work in SQL Developer’s connection settings if you put them in the SID field. Those values are Service names. If I wasn’t an Oracle dev I wouldn’t know that 🙂
2a. Feedback for SQL Developer: swap the position of SID and Service name on connection properties so the modern one is first
Oh did we not gather stats on the dictionary, ouch. Yeah I’ll add to the list.
Where are you seeing the ORACLE SID = free? I mean, which HTML page exactly?
For the SID/SERVICE that’s indeed happening – SQLDev Next has a dropdown and Service is listed first 🙂
“Oracle SID” is in these two files, they’re displayed in Firefox and the Terminal when you start the VM:
Yup, that text, I wrote that, blame me.
The SID is indeed ‘FREE’ – if you want to connect to the container, you supply FREE in the connection details. If you want to connect to the pluggable database, you use freepdb1
I have examples below that show how to connect…does that not clear things up?
UTM/QEMU might work to emulate intel x64 on Mac ARM – I have not tried it myself. Virtualbox does not emulate a different chip architecture – i.e. VirtualBox ARM will only run ARM images.
Does this support Mac M1/M2 Machines, the Virtual Box does, but what about the guest machine?
I am getting this error after while booting.
Translated Report (Full Report Below)
Process: VirtualBoxVM 
Version: 7.0.6 (7.0.6)
Code Type: ARM-64 (Native)
Parent Process: VBoxSVC 
Responsible: VirtualBox 
User ID: 501
Date/Time: 2023-04-12 14:05:25.5628 +0300
OS Version: macOS 13.3.1 (22E261)
Report Version: 12
Time Awake Since Boot: 73000 seconds
Time Since Wake: 6858 seconds
System Integrity Protection: enabled
Crashed Thread: 9 EMT-0
Exception Type: EXC_CRASH (SIGABRT)
Exception Codes: 0x0000000000000000, 0x0000000000000000
Application Specific Information:
You can try the VirtualBox Beta release which supports arm architectures. You might want to look at Docker and Colima as well.