WebDAV

Oracle versus Oracle … and yes it is as sad as it sounds

—

by

Dave
in , ,

Well, well, well … a chat to a colleague and I find myself fighting with Oracle Linux .. I started fighting with version 9.3, but alas, 9.3 doesn’t actually run Oracles own f’ing database … well of course it wouldn’t, why would it? I had to downgrade to 8.9, just time wasted, lots of my time wasted to nonsense. This isn’t meant to be a “how-to” or “what if” set of instructions, this is just an encounter I have had with apparently unbreakable software.

My internet speed isn’t the best here (if I got any further from the exchange, I would be signing up for star-link :/) and 9.3 was 10gb and 8.9 was 12.3gb. Bigger than an average 4.7gb DVD size, but DVD image’s they were, it said so in the file names.

ShellScript
> ls downloads/>   OracleLinux-R8-U9-x86_64-dvd.iso

Thankfully installing the OS’s into Virtual Box (of course, another Oracle product) was relatively painless. However, there were glitches installing the VBOX guest additions. The required kernel headers aren’t installed by default … go figure … I need someone to remind me why Oracle have this market share.

But after that, it all just goes downhill. As I mentioned, after my mistake of assuming something simple, the latest version of the Oracle Database Free (23C) doesn’t actually run, neigh, doesn’t even install on the latest version of Oracle’s own operating system. I fought with it for a while thinking I must be crazy or something, who would develop and release an operating system that did not run their very own championed and celebrated product. Clearly Oracle do, so upon conceding an undesirable defeat, I deleted the VM and started again with the Oracle Linux 8.9 DVD image.

Then there is more time lost hunting around for the RPMs you are going to need to install. Here I needed the database install file and the RPMs for the clients, e.g. sqlplus.

You can get them from here: https://www.oracle.com/database/technologies/free-downloads.html

ShellScript
ls -l *.rpm-rw-r--r--. 1 1751573448 Sep 11 14:59 oracle-database-free-23c-1.0-1.el8.x86_64.rpm-rw-rw-r--. 1 55788060 Oct 19 09:52 oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64.rpm-rw-rw-r--. 1 728100 Oct 19 09:51 oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64.rpm

I truly do not understand why are they not included with the OS, these bits should come as standard with the operating system because I bet dollars to doughnuts, that if someone just wants a Linux distribution they will not be installing Oracle’s offering out of personal choice. So Oracle needs to sort that out, and on top of that, they need to be making sure all of their products work together before releasing it into the wild.

When I first wrote up my notes (yup, these are my notes, I remember things by making them feel funnier than they actually are), I wrote a whole section about installing the database using dnf, using sqlplus to look inside what had just installed etc … however … I seem to have deleted those parts of my notes when I changed the order around of some of the funnies … of course, this adds to the whole scenario somewhat and is perfectly in keeping with everything that has happened thus far in the process.

But to give an oversight of what’s what, we need to understand that Oracle uses the term Database somewhat differently to what has now become “normalised” thinking. It is actually more akin to the good old days of DBase3+/Clipper or the beef cake IBM Db2 (although this is still in existence but significantly improved). Anyway, the term Database in Oracle terms is the server/software combo, upon with is going to sit the data store. It is this data store that is more akin to the usual suspects such as MySQL, PostgreSQL, MariaDB etc.

In Oracle Free 23c’s case, it creates a FREE file store, with a CDB and the ability to create up to 16 Plug-able Databases. It is these plug-able databases that are you equivalent to a default MySQL installation. Inside the PDB you then create your table spaces and then your data structures and stored procedures. Hopefully that all makes sense to you, if not, perhaps you should not be reading further, just in case you break something? (LOL Of course I am only kidding, like I care if you break something or not, gotta keep breaking things to be able un-break them and learn after all.) Anyway, when you install the software there are two ways you can go, create an oracle user and build its environment based directly, or you can create an overriding admin (instead of sys as sysdbm). I of course decided to make it more complicated for myself by taking the second of the two paths.

So, first add your nice shiny new PDB with your chosen user/pass combo that you specified at install.

ShellScript
sqlplus sys/adminpassword@//localhost:1521/FREE
SQL
CREATE PLUGGABLE DATABASE monopdb  ADMIN USER monouser IDENTIFIED BY monopassword    ROLES = (dba)  DEFAULT TABLESPACE library  DATAFILE '/opt/oracle/product/23c/dbhomeFree/dbs/monopdb/library01.dbf' SIZE 250M AUTOEXTEND ON  STORAGE (MAXSIZE 2G)  PATH_PREFIX = '/opt/oracle/product/23c/dbhomeFree/dbs/monopdb/';

That then creates a plugable database that can be used like any other database, with it’s own files, users, etc without interference. It’s quite interesting that you can still create separate paths for database files, well, maybe interesting is not the right word. I am not sure it is entirely required any more with seamless storage and cloud storage facilities, but still natty all the same. Now it is possible to log in as that user, but there are some other hurdles we need to overcome first. Of course there are, this is an Oracle install after all.

Plugable databases, or PDB’s, are created as mounted only, they aren’t open for access in any way shape or form. In version 12 of oracle you had to create a trigger on database start to reopen the pdb’s every time, however the newer versions do allow for saving the state, so you only have to open it once and save the state. the trigger does also still work though if you want to add it anyway.

SQL
SQL> show pdbs;
SQL
CON_ID  CON_NAME                     OPEN MODE  RESTRICTED------  ---------------------------  ---------  ----------     4  MONOPDB                      MOUNTED
SQL
ALTER PLUGGABLE DATABASE monopdb OPEN;
SQL
CON_ID  CON_NAME                     OPEN MODE  RESTRICTED------  ---------------------------  ---------  ----------     4  MONOPDB                      READ WRITE NO
SQL
ALTER PLUGGABLE DATABASE monopdb SAVE STATE;

As I said, the trigger still works if you would rather implement that over doing things manually, not sure I understand why, but here it is anyway.

SQL
CREATE OR REPLACE TRIGGER open_pdbs  AFTER STARTUP ON DATABASEBEGIN  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';END open_pdbs;/

Right, so we have an accessible PDB . woohoo! We need to adjust that users’ permissions so it can create things in the nice shiny new database.

We can check out user’s privileges by logging into sqlplus as them, presumably we remember the new password we assigned when we created the PDB? You must have written it down on a post-it note and stuck it to your screen surely? Wait, what year is it again?

SQL
sqlplus user/password@//localhost:1521/monopdb SQL> select * from session_roles;ROLE ---------------------------------------------------PDB_DBAWM_ADMIN_ROLE

As you can see, it is missing the ability to do anything other than log in, really, really useful default state. Anyway …

Firstly, as the CBD system user, you need to make sure you are in the PDB domain for this session otherwise when you try and do anything it will say “no such user”. It’s helpful like that, it has very intelligent error messages that are almost impossible to misinterpret … that is largely to do with the fact that they are completely unintelligible to start with.

SQL
SQL> alter session set container=monopdb Session altered.

Now let us add some useful abilities for this pup, like connecting and creating stuff, also being able to store stuff in the said things created would probably be a good idea to be fair, but you never really can tell.

Now we need to reconnect as the CDB system user and let the magic begin.

SQL
SQL> grant connect resource …………………………

Wait, I am not sure what is or isn’t needed, I think I will be better, in the first instance, to grant everything and then reverse out stuff that’s not needed.

SQL
SQL> grant ALL privileges to monouser;Grant succeeded.

Note:It complained with ALL was in lowercase, but worked when it was capitalised, go figure, that will be another little idiosyncrasy of Oracle then I guess.

I am not entirely sure that which one’s are needed at this point, so I am currently taking a belts and braces approach and just making stuff work, I can always revoke stuff after the fact.

In case you needed to know, revoking is as simple as granting.

SQL
SQL> revoke create session from PDBUSER;Revoke succeeded. // See, f&*k you PDB user .. Muwahahahaha

Right, so we have now updated our user responsible for the PDB, we can check what they can now do inside their little world.

SQL
> sqlplus monouser/password@//localhost:1521/monopdb SQL*Plus: Release 23.0.0.0.0 - Production on Mon Feb 5 13:39:10 2024Version 23.3.0.23.09Copyright (c) 1982, 2023, Oracle.  All rights reserved.Last Successful login time: Mon Feb 05 2024 13:23:34 +00:00Connected to:Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for FreeVersion 23.3.0.23.09SQL> show user;USER is "MONOUSER"SQL> select * from session_roles;ROLE--------------------------------------------------------------------------------CONNECTRESOURCESODA_APPPDB_DBAWM_ADMIN_ROLE

Of course, we can also check the privileges granted, but bare in mind we granted everything to the user and haven’t as yet, revoked anything.

SQL
SQL> select * from session_privs;
PRIVILEGE

EXECUTE ANY DOMAIN
DROP ANY DOMAIN
ALTER ANY DOMAIN
CREATE ANY DOMAIN
CREATE DOMAIN
ADMINISTER REDACTION POLICY
ADMINISTER FINE GRAINED AUDIT POLICY
ADMINISTER ROW LEVEL SECURITY POLICY
DROP ANY MLE
ALTER ANY MLE
CREATE ANY MLE

PRIVILEGE

CREATE MLE
READ ANY PROPERTY GRAPH
DROP ANY PROPERTY GRAPH
ALTER ANY PROPERTY GRAPH
CREATE ANY PROPERTY GRAPH
CREATE PROPERTY GRAPH
DROP LOGICAL PARTITION TRACKING
CREATE LOGICAL PARTITION TRACKING
DROP ANY ANALYTIC VIEW
ALTER ANY ANALYTIC VIEW
CREATE ANY ANALYTIC VIEW

PRIVILEGE

CREATE ANALYTIC VIEW
DROP ANY HIERARCHY
ALTER ANY HIERARCHY
CREATE ANY HIERARCHY
CREATE HIERARCHY
DROP ANY ATTRIBUTE DIMENSION
ALTER ANY ATTRIBUTE DIMENSION
CREATE ANY ATTRIBUTE DIMENSION
CREATE ATTRIBUTE DIMENSION
READ ANY TABLE
ALTER ANY CUBE BUILD PROCESS

PRIVILEGE

SELECT ANY CUBE BUILD PROCESS
ALTER ANY MEASURE FOLDER
SELECT ANY MEASURE FOLDER
EXECUTE DYNAMIC MLE
USE ANY JOB RESOURCE
LOGMINING
CREATE ANY CREDENTIAL
CREATE CREDENTIAL
ALTER LOCKDOWN PROFILE
DROP LOCKDOWN PROFILE
CREATE LOCKDOWN PROFILE

PRIVILEGE

SET CONTAINER
CREATE PLUGGABLE DATABASE
FLASHBACK ARCHIVE ADMINISTER
DROP ANY SQL TRANSLATION PROFILE
USE ANY SQL TRANSLATION PROFILE
ALTER ANY SQL TRANSLATION PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE SQL TRANSLATION PROFILE
ADMINISTER SQL MANAGEMENT OBJECT
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS

PRIVILEGE

SET CONTAINER
CREATE PLUGGABLE DATABASE
FLASHBACK ARCHIVE ADMINISTER
DROP ANY SQL TRANSLATION PROFILE
USE ANY SQL TRANSLATION PROFILE
ALTER ANY SQL TRANSLATION PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE SQL TRANSLATION PROFILE
ADMINISTER SQL MANAGEMENT OBJECT
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS

PRIVILEGE

DROP ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
CREATE CUBE BUILD PROCESS
INSERT ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
CREATE ANY MEASURE FOLDER
CREATE MEASURE FOLDER
UPDATE ANY CUBE
SELECT ANY CUBE
DROP ANY CUBE

PRIVILEGE

CREATE ANY CUBE
ALTER ANY CUBE
CREATE CUBE
SELECT ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE CUBE DIMENSION
COMMENT ANY MINING MODEL

PRIVILEGE

ALTER ANY MINING MODEL
SELECT ANY MINING MODEL
DROP ANY MINING MODEL
CREATE ANY MINING MODEL
CREATE MINING MODEL
EXECUTE ASSEMBLY
EXECUTE ANY ASSEMBLY
DROP ANY ASSEMBLY
ALTER ANY ASSEMBLY
CREATE ANY ASSEMBLY
CREATE ASSEMBLY

PRIVILEGE

ALTER ANY EDITION
DROP ANY EDITION
CREATE ANY EDITION
CREATE EXTERNAL JOB
CHANGE NOTIFICATION
CREATE ANY SQL PROFILE
ADMINISTER ANY SQL TUNING SET
ADMINISTER SQL TUNING SET
ALTER ANY SQL PROFILE
DROP ANY SQL PROFILE
SELECT ANY TRANSACTION

PRIVILEGE

MANAGE SCHEDULER
EXECUTE ANY CLASS
EXECUTE ANY PROGRAM
CREATE ANY JOB
CREATE JOB
ADVISOR
EXECUTE ANY RULE
DROP ANY RULE
ALTER ANY RULE
CREATE ANY RULE
CREATE RULE

PRIVILEGE

IMPORT FULL DATABASE
EXPORT FULL DATABASE
EXECUTE ANY RULE SET
DROP ANY RULE SET
ALTER ANY RULE SET
CREATE ANY RULE SET
CREATE RULE SET
EXECUTE ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT

PRIVILEGE

CREATE EVALUATION CONTEXT
GRANT ANY OBJECT PRIVILEGE
FLASHBACK ANY TABLE
DEBUG ANY PROCEDURE
DEBUG CONNECT ANY
DEBUG CONNECT SESSION
RESUMABLE
ON COMMIT REFRESH
MERGE ANY VIEW
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER

PRIVILEGE

DROP ANY OUTLINE
ALTER ANY OUTLINE
CREATE ANY OUTLINE
DROP ANY CONTEXT
CREATE ANY CONTEXT
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE
DROP ANY DIMENSION
ALTER ANY DIMENSION
CREATE ANY DIMENSION

PRIVILEGE

CREATE DIMENSION
UNDER ANY TABLE
EXECUTE ANY INDEXTYPE
GLOBAL QUERY REWRITE
QUERY REWRITE
UNDER ANY VIEW
DROP ANY INDEXTYPE
ALTER ANY INDEXTYPE
CREATE ANY INDEXTYPE
CREATE INDEXTYPE
EXECUTE ANY OPERATOR

PRIVILEGE

DROP ANY OPERATOR
ALTER ANY OPERATOR
CREATE ANY OPERATOR
CREATE OPERATOR
EXECUTE ANY LIBRARY
DROP ANY LIBRARY
ALTER ANY LIBRARY
CREATE ANY LIBRARY
CREATE LIBRARY
UNDER ANY TYPE
EXECUTE ANY TYPE

PRIVILEGE

DROP ANY TYPE
ALTER ANY TYPE
CREATE ANY TYPE
CREATE TYPE
DROP ANY DIRECTORY
CREATE ANY DIRECTORY
DROP ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
GRANT ANY PRIVILEGE

PRIVILEGE

ANALYZE ANY
ALTER RESOURCE COST
DROP PROFILE
ALTER PROFILE
CREATE PROFILE
DROP ANY TRIGGER
ALTER ANY TRIGGER
CREATE ANY TRIGGER
CREATE TRIGGER
EXECUTE ANY PROCEDURE
DROP ANY PROCEDURE

PRIVILEGE

ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE
FORCE ANY TRANSACTION
FORCE TRANSACTION
ALTER DATABASE
AUDIT ANY
ALTER ANY ROLE
GRANT ANY ROLE
DROP ANY ROLE
CREATE ROLE

PRIVILEGE

DROP PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE DATABASE LINK
SELECT ANY SEQUENCE
DROP ANY SEQUENCE
ALTER ANY SEQUENCE
CREATE ANY SEQUENCE
CREATE SEQUENCE
DROP ANY VIEW
CREATE ANY VIEW
CREATE VIEW

PRIVILEGE

DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE SYNONYM
DROP ANY INDEX
ALTER ANY INDEX
CREATE ANY INDEX
DROP ANY CLUSTER
ALTER ANY CLUSTER
CREATE ANY CLUSTER

PRIVILEGE

CREATE CLUSTER
REDEFINE ANY TABLE
DELETE ANY TABLE
UPDATE ANY TABLE
INSERT ANY TABLE
SELECT ANY TABLE
COMMENT ANY TABLE
LOCK ANY TABLE
DROP ANY TABLE
BACKUP ANY TABLE
ALTER ANY TABLE

PRIVILEGE

CREATE ANY TABLE
CREATE TABLE
DROP ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP USER
ALTER USER
BECOME USER
CREATE USER
UNLIMITED TABLESPACE
DROP TABLESPACE

PRIVILEGE

MANAGE TABLESPACE
ALTER TABLESPACE
CREATE TABLESPACE
RESTRICTED SESSION
ALTER SESSION
CREATE SESSION
AUDIT SYSTEM
ALTER SYSTEM

250 rows selected.

This now looks infinitely more promising, the new user and PDB should be able to be used with abandon. Let us start by creating some tablespace for our new database tables, procedures and whatever else we may need along the way.

So we are now in a position where we can create our very first table space, well, we can hope that we are at least. Without further ado.

SQL
SQL> create tablespace monodb  2  datafile 'monodb.dbf' size  500k reuse  3  autoextend on next 500k maxsize 100m;Tablespace created.SQL> alter pluggable database default tablespace monodb;Pluggable database altered.

A simple database that can grow as needed, that will do for these purposes and making it work and giving us somewhere to store stuff.

But what we have now confirmed at least, with the free version of Oracle 23c, we can make use of up to 16 PDB’s with their own user space without risk of bleed into other PDB spaces. More of the model we are used to with the usual suspects, MySQL, Maria, Postgres etc …

Of course the testing continues …

We will have to now create some of the usual tables etc in which we can store some stuff, why have a database with nothing in it? Well who knows, someone out there might want one, perhaps it looks pretty?

We will make a Book Library, because it was either that or a contact database, but as you can probably tell from my ramblings, I don’t know that many people to put in it LOL!

SQL
SQL> create table books (  2    id      number     generated by default on NULL as IDENTITY,  3    title   varchar(256) not null,  4    genre   varchar(20),  5    author  varchar(100),  6    isbn    varchar(13) unique  7  ) tablespace monodb;Table created.SQL> describe books; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                        NOT NULL NUMBER TITLE                                              VARCHAR2(256) GENRE                                              VARCHAR2(20) AUTHOR                                             VARCHAR2(100) ISN                                                VARCHAR2(13)

We have, despite Oracle’s best efforts to thwart my progress, a database with a single table exists and it is ready to populate. I don’t want to use WebLogic … I didn’t like it in 2000 … I am not about to start liking it now. I suspect there must be a PHP PDO driver out there waiting for me to find it and install to test it further.

But before we do all of that, I want to be able to use Ansible to create backups of the databases and server, but interestingly, I should have thought about this before starting anything because … it needs to have ARCHIVELOG enabled for backups and restores to work … of course it does.

SQL
SQL> alter database close;SQL> alter database archivelog;SQL> alter database open;

In my case, for some unknown reason I had to restart the oracle services at this point, this managed to get the databases and PDBs opened in READ / WRITE mode again, but with ARCHIVELOG on.

SQL
SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            /opt/oracle/product/23c/dbhomeFree/dbs/archOldest online log sequence     21Next log sequence to archive   21Current log sequence           20

So with that done, it should now be possible to use rman ro create backups, so let’s test that theory manually. If this works then creating a script similar to this that can be called by an Ansible script (sorry, ffs, Playbook, so many new words for the same shit, in this case YAML because why use one mark-up language when you can invent another one to do the same thing?) should be relatively straight forward.

ShellScript
>rman target sys/password@//localhost:1521/free> backup database2>  format "/home/oracle/backups/backup_%U";Starting backup at 23-FEB-24using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 23-FEB-24[oracle@mono backups]$ ls -latotal 3324920drwxr-xr-x. 2 oracle oinstall       4096 Feb 23 15:58 .drwx------. 8 oracle oinstall       4096 Feb 23 14:59 ..-rw-r-----. 1 oracle oinstall 1634172928 Feb 23 15:58 backup_012jse1u_1_1_1-rw-r-----. 1 oracle oinstall  627597312 Feb 23 15:58 backup_022jse21_2_1_1-rw-r-----. 1 oracle oinstall  635641856 Feb 23 15:58 backup_032jse22_3_1_1-rw-r-----. 1 oracle oinstall  507281408 Feb 23 15:59 backup_042jse23_4_1_1

And there we have a backup of everything, happy days.

Please note I want to backup everything, so I use the system user and password and connect to the actual core instance, this then allows me to backup every PDB and option.

To be quite honest, I got extremely bored by this point, I have managed to get Oracle Free 23c installed on Oracle Linux 8.9, I have created a PDB instance with its own admin user (granted the user is all powerful at this point and needs to have its wings clipped) and created a table space and basic data structure within it to store data in. Followed by making it work with rman to create backups that can be restored and in such a way as a script (sorry, playbook) can be written in YAML for Ansible to be able to do the backups remotely and in it’s own time without relying on cron or any other local system.

If you want me to provide a script and YAML example, or indeed a PHP example using the PDO library (which incidentally needs to be recompiled onto the PHP system, because it also requires the oracle clients to be installed on whichever environment is calling the library), then please do let me know, I can happily ignore as many requests as you want to send LOL!

I hope this experience has helped you along your way with Oracle Linux, Oracle FREE 23c, rman and Ansible … if not … I hope it has just entertained you for the last few minutes of reading.

#peaceout

Sources (in no particular order):

A note about the sources, most of them are oracles somewhat dubious documentation, but there are amongst them, a couple of diamond resources that you would be good to take note of. First of all, kewl.org. The wiki is full of really useful stuff, make sure you check it out. kewl.org is written and maintained by someone with a big brain! Then, then there is the Stack Overflow link, that proved absolutely invaluable in showing the order of things and to coin someone else's catch phrase, "This is the way".

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.