3 small tricks for DBAs in Oracle Database 23c

Although the new Oracle Database 23c Free is in fact a Developer Release, there are still some interesting and useful DBA features.

Here are 3 that I find simple and good to know:

1. Control PDB Open Order:

DBAs can define a startup order or priority for each pluggable database (PDB) where the most important PDBs are started first. The priority is applied to PDB opening order and upgrade order as follows:

• Restoring PDB states when opening the CDB

• Setting PDB states when using the PDB OPEN ALL statement

• Setting the order for PDB database upgrade operations

• Starting PDBs in an Active Data Guard (ADG) switchover or failover

This feature allows critical PDBs to start and open before less important PDBs, reducing the time for the critical applications to become usable.

Note: if several pluggable databases have the same priority, then Oracle will open them based on CON_ID (lowest first).

2. Read only users:

DBAs can make a user to become READ-ONLY!

You can set the access of a local user to a PDB to READ ONLY or READ WRITE with the ALTER USER statement.

3. Increased Maximum Password Length:

The times of system/manager, sys/change_on_install and connect internal are clearly gone. In 23c, Oracle Database supports passwords up to 1024 bytes in length while in previous releases, the Oracle Database password length and the secure role password length could be up to 30 bytes.

The increased maximum password length to 1024 bytes provides the following benefits:

• It accommodates passwords that are used by Oracle Identity Cloud Service (IDCS) and Identity Access Management (IAM)

• The increase to 1024 bytes enables uniform password rules for all Cloud deployments

• The 30-byte limitation was too restrictive when password multi-byte characters used more than 1 byte in an NLS configuration

Really? 18446744073709551549 days 🙂 ? How many years would that be?

Obviously long time ago 🙂

Here is something more: the In-Memory column store will now (in 23c) automatically grow and shrink dynamically based on workload allowing IMCS to be available on ADB. With Automatic IM sizing, there is no longer a need to manually resize the In-Memory column store to accommodate different database workloads thus reducing the administrative effort of enabling Database In-Memory. Automatic In-Memory sizing also allows the In-Memory column store to be enabled on Autonomous Database, enabling applications running on ADB to also take advantage of faster analytic query performance.

This feature can be turned off with the underscore parameter _enable_space_preallocation:  

ALTER SYSTEM SET “_enable_space_preallocation” = 0;

The feature can be turned on again any time by setting “_enable_space_preallocation”=3 which is the default value:  

ALTER SYSTEM SET “_enable_space_preallocation” = 3;

The parameter is dynamic so it can be modified online and accepts 4 diffenent values:

0 turns off the tablespace pre-extension feature
1 enables tablespace extension
2 enables segment growth
4 enables chunk allocation

Also, Exadata scan performance in 23c is further improved for objects that are partially populated.

The Cloud Advisor and Cost Saving Opportunities in OCI

If you have a tenancy in OCI, you probably know that the cost can be controlled and managed in most cases by either shutting down the nodes and the databases when not being used (and this can be even automated) but there is more to that.

The main OCI console page contains the basis information about your tenancy (most right): name, usage, subscription number, used/left credits, etc:

In terms of operational availability, I am usually interested in the Frankfurt region where my main tenancy resides:

Under the health dashboard (worth checking on daily basis) there is an “Analyze costs” button which takes you to the “Cost Management” page. It contains links to 4 different sub-pages:

• Cost Analysis
• Cost and Usage Report
• Budget
• Scheduled Reports

You can choose a period, here is what my usage report looks like for the whole month of May 2023:

As you can observe, the graphics are extremely clear, you can see by day what you have paid for and how much. Just below the Cost Graph, you can see the details for the past days:

Cost and usage reports are CSV files generated daily that show usage data for each resource in your tenancy. The CSV files are stored in an object storage bucket that is accessible using a cross-tenancy policy.

The most interesting part is perhaps viewing the recommendations under “Cost saving opportunities”:

There are 3 types/categories of recommendations (see below) and if you need any security recommendations, just view Cloud Guard.

Under “Global recommendations”, you can disable the Cloud Advisor and modify your recommendation profile to customize the logic that Cloud Advisor uses to make recommendations:

The Cloud Advisor is not just easy to use but also the fastest way to manage, control your tenancy from cost savings point of view.

If you might be wondering why on the Recommendations page, the “Estimated savings” column contains no values: I am using the smallest possible configuration and starting the VMs and the databases only when I need them. I have a pretty small tenancy and using only few services.

If interested in all the details, check the Best practices for optimizing the performance and cost of cloud resources!

Here is how cost optimization works in AWS, GCP and Azure – I am also including below a screenshot from my Azure tenancy: