Last week I held a presentation in beautiful Buchares Romania (https://contech2024.rooug.ro) about “Modern ways to stage your non-prod systems”.
In this presentation I show some examples on how the Oracle REST API can be used to create and delete PDBs. The reason I used ORDS for that is, it is very easy to automate these steps in deployment pipelines. As an example during the deployment of an integration-test environment we can create a temporary PDB within seconds and delete this PDB after all the tests.
During the presentation I was not showing on how to configure ORDS for that purpose, therefore I decided to write a short blog post on this.
Some advantages of this way is we don’t need to copy data and we don’t need to have access to the database server or even execute SQL statements on the DB itself.
To use this rest endpoints we do not only have to install ORDS in the CDB, we have also define a user with some privileges. This was not part of the presentation itself and even if it are small steps I thought I write a short blog post on this.
Create a database user with the needed privileges
After the ORDS installation we do need to create a database user with some privileges:
CREATE USER "C##DBAPI_CDB_ADMIN" IDENTIFIED BY 'HIGHLY_SECURE_PASSWORD';
GRANT SYSDBA TO C##DBAPI_CDB_ADMIN CONTAINER=ALL;
Due to the situation that the user must be able to close and drop PDBs SYSDBA is required.
After creating the user in the database we have to set the required ords parameters.
Configure ORDS
If your ords configuration is not located in the default directory (/etc/ords/config) you have to specify the –config parameter.
[oracle@ordshost1~]$ ords --config /u01/app/oracle/admin/ords/config config set db.cdb.adminUser "C##DBAPI_CDB_ADMIN AS SYSDBA"
ORDS: Release 23.4 Production on Mon May 27 15:26:21 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/u01/app/oracle/admin/ords/config/
The setting named: db.cdb.adminUser was set to: C##DBAPI_CDB_ADMIN AS SYSDBA in configuration: default
[oracle@ordshost1~]$ ords --config /u01/app/oracle/admin/ords/config config secret db.cdb.adminUser.password
ORDS: Release 23.4 Production on Mon May 27 15:25:50 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/u01/app/oracle/admin/ords/config/
Enter the secret for db.cdb.adminUser.password:
Confirm password:
The setting named: db.cdb.adminUser.password was set to: ****** in configuration: default
After that, we have to restart ords to take these changes into effect.
To use the PDB Lifecycle management endpoints we have to create an ORDS user with the role “SQL Administrator”:
[oracle@ordshost1~]$ ords --config /u01/app/oracle/admin/ords/config config user add pipelineuser "SQL Administrator"
ORDS: Release 23.4 Production on Mon May 27 15:33:00 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/u01/app/oracle/admin/ords/config/
Enter the password for pipelineuser:
Confirm password:
Created user pipelineuser in file /u01/app/oracle/admin/ords/config/global/credentials
Now we’re ready to administer PDBs with this user. As an example, we can use curl to create a pdb with the following curl command:
[oracle@ordshost1~]$ curl --request POST --url http://ordshost1:8080/ords/_/db-api/stable/database/pdbs/ --header 'Authorization: Basic cGlwZWxpbmV1c2VyOlN1bjI0ME1pY3JvX18=' --header 'Content-Type: application/json' --data '
{
"method": "CREATE",
"pdb_name": "TEMPORARYPDB",
"adminName": "pdb_admin",
"adminPwd": "TEMPORARYPASSWSORD",
"unlimitedStorage": true,
"reuseTempFile": true,
"totalSize": "UNLIMITED",
"tempSize": "UNLIMITED"
}' | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1320 0 1093 100 227 307 63 0:00:03 0:00:03 --:--:-- 307
{
"env": {
"defaultTimeZone": "UTC"
},
"items": [
{
"statementId": 1,
"response": [
"\nPL/SQL procedure successfully completed.\n\n"
],
"result": 0,
"binds": [
{
"name": "g__pdbName",
"data_type": "VARCHAR2",
"value": "TEMPORARYPDB"
},
{
"name": "g__adminUsername",
"data_type": "VARCHAR2",
"value": "PDB_ADMIN"
},
{
"name": "g__totalSize",
"data_type": "VARCHAR2",
"value": "UNLIMITED"
},
{
"name": "g__tempSize",
"data_type": "VARCHAR2",
"value": "UNLIMITED"
},
{
"name": "g__adminPassword",
"data_type": "VARCHAR2",
"value": "TEMPORARYPASSWSORD"
},
{
"name": "g__filenameConversion",
"data_type": "VARCHAR2",
"value": "NONE"
}
]
},
{
"statementId": 2,
"response": [
"\nPL/SQL procedure successfully completed.\n\n"
],
"result": 0,
"binds": [
{
"name": "g__pdbName",
"data_type": "VARCHAR2",
"value": "TEMPORARYPDB"
},
{
"name": "g__adminUsername",
"data_type": "VARCHAR2",
"value": "PDB_ADMIN"
},
{
"name": "g__totalSize",
"data_type": "VARCHAR2",
"value": "UNLIMITED"
},
{
"name": "g__tempSize",
"data_type": "VARCHAR2",
"value": "UNLIMITED"
},
{
"name": "g__adminPassword",
"data_type": "VARCHAR2",
"value": "TEMPORARYPASSWSORD"
},
{
"name": "g__filenameConversion",
"data_type": "VARCHAR2",
"value": "NONE"
}
]
}
]
}
The username and password are stored as BASE64 in the header field “Authorization”. In this example I piped the command to jq for having a well formated response.
