DBA help: March 2023

How to monitor FRA (FLASH_RECOVERY_AREA_USAGE) using OEM

I have done this before but again when I search I could not find any good link on how to monitor FRA using OEM.

There are two sub metrics under 'Recovery Area', 'Recovery Area Free Space (%)' and 'Recovery Area Used Space (%)'.

Recovery Area Free Space (%) by definition is only for metric collection purpose. No thresholds can be set for this metric and hence no alerts will be generated for this metric.

Recovery Area Used Space (%) is used for collection and alerting. Enterprise Manager allows customers to set thresholds for this metric starting from 18c databases and alerts will be generated accordingly.

For customer using older version of database like 11g or 12c there is no metric in OEM to monitor FRA.

So the best and simple option I found is to create a metric extension and use it.

How to create metric extension for FRA usage:

1. Login to OEM console --> Enterprise --> Monitoring --> Metric Extensions

2. Click on create --> Metric Extension

3. You will see below screen, so choose

Target type = Database Instance

Name ME$ = provide any name like FRA

Display name = FRA

Adapter = SQL

Description = FRA free percentage

You don't need to change Collection schedule for now.

4. Click next and use below SQL to monitor FRA. (I used below SQL but you can use any other SQL if you are already using)

SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
  FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE);

This query will provide FRA free percentage value. You should run this query on any database before using in metric extension like below.

Once you ready with query use it in Metric Extension

5. Click next and here we will specify the Metric columns , as you see in above SQL output screenshot , I have only one column returned, PERCENT_FREE so I will use that to setup threshold.

click Add --> New metric column

6. Provide values like name, display name , unit, value type. Alert threshold is optional you van provide now or later, I provide here.

7. Click next, here you have to provide DB credentials, You can use Default Monitoring credentials

8. Click Next, here you can test if the metric is working or not before finish it.

Add target and click on run test and you will see the result in Test result.

9. Click Finish and Metric Extension is ready to Deploy.

10. Now click on Actions and click on "Save As Deployable Draft"

11. Again Click on Actions and click Deploy to Targets, here you have to add all Database instance targets where you want to add this extension.

12. Click Submit and now you can see Deployed targets.

13. Last thing is we need to publish this Metric Extension.

Click on Actions again --> "Publish Metric Extension" and it will publish.

14. Now you can go to any deployable target DB home page and go to Oracle database --> Monitoring--> Metric and collection settings

15. You can see this metric added now and you can modify threshold here if needed.

Same way we can setup Metric Extension for anything which we want to monitor and if Oracle does not provide any default metric in OEM.

Automation - Deploy DBCS on OCI using REST API

I was working on automation using REST API and found it is very interesting and easy to implement with a simple command to deploy services on OCI.

In this blog, I will show you how to deploy DBCS using REST API.

I know you are thinking why I need to deploy using REST API if I have console. Well, you can schedule a job to deploy/terminate DBCS. Specially when you doing any testing, it will help, it will save time and money both.

There are many ways to use APIs, I am using oci-curl function in this blog available by OCI team. OCI_CURL will sign and encrypt your APIs so they are not sent in clear over the internet.

OCI-CURL can be used on Linux and windows, I am using on windows here.

You can use below link to copy oci-curl script.

oci-curl

You only have to change these 4 parameters in this script as per your environment.

Tenancy OCID, USER OCID, KeyFingerprint and private key .pem file location. You can easily get from OCI console.

Pre-requisite:

1. I am assuming you already have VCN, subnet, security rules and compartment.

2. Also, you need to generate a key pair using ssh-keygen so you can use public key in json configuration file later.

To Deploy DB system we need below 4 things:-

1. OCI-CURL - I have it above.

2. API endpoint for database: I used database.ca-toronto-1.oraclecloud.com , if your cloud database is located somewhere else you can use that. full list here .. API Endpoints

3. Config File: I also need to create a small JSON file for configuration containing information like availability domain, compartment id, db related info and public key to access node.

4. DB System API command POST /20160918/dbSystems

here is my JSON file here..

Now I have everything, so I just created a small shell script

So to deploy DB system, I just need to run the shell script like ./createdbsystem.sh and magic start to begin :)

Here is the output

and when I go to console, I can see its provisioning..

You can go to the work request to see how much completed..

It will take like 20-30 mins to complete and then you can connect to the node using your private key.

To Delete or terminate the DB System even more simpler because you don't need any configuration file , you just need OCID of the DB System.

Here is the script I created for terminate....

Now we can schedule these jobs in crontab or windows scheduler to automate deployment.

Hope you like the Blog!

ORA-00600: internal error code, arguments: [4000], [447], [1], [1], [], [], [], [], [], [], [], []

I am writing this blog because I could not find the solution of this issue online or on MOS.

Issue: I received this ORA-00600 error after I upgrade PDB from 12c to 19c.

ORA-00600: internal error code, arguments: [4000], [447], [1], [1], [], [], [], [], [], [], [], []

This error occurs when I run some SQL's on this PDB and I also can see this in alert log file.

Cause: Actually this is 12c bug and there is no fix for this except the workaround.

Workaround: The workaround is to switch to shared undo and then switch back to local undo, unfortunately this can only be performed at CDB level

Steps:

1) connect to CDB as sysdba


connect / as sysdba
SQL> alter system set cluster_database=false scope=spfile;

System altered.

2)Stop all the database instances
srvctl stop database -d DB_NAME

3) Connect with sqlplus to one of the instances and execute


SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 7953271232 bytes
Fixed Size                  9157056 bytes
Variable Size            4731174912 bytes
Database Buffers         2936012800 bytes
Redo Buffers              276926464 bytes
Database mounted.
Database opened.
SQL> alter database local undo off;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 7953271232 bytes
Fixed Size                  9157056 bytes
Variable Size            4731174912 bytes
Database Buffers         2936012800 bytes
Redo Buffers              276926464 bytes
Database mounted.
Database opened.
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 7953271232 bytes
Fixed Size                  9157056 bytes
Variable Size            4731174912 bytes
Database Buffers         2936012800 bytes
Redo Buffers              276926464 bytes
Database mounted.
Database opened.
SQL> alter database local undo on;

Database altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4) Start the database normally and open all the pdbs if needed
srvctl start database -d DB_NAME

Now connect to the PDB and run the SQL again and we won't see the error.

Hope this helps!

Setup Alert Notification for EBS Stack monitoring targets

This blog is continuation of my previous blog where I setup the EBS stack monitoring and now I am going to setup alert notification for discovered EBS targets.

Here is the link for my previous blog.

OCI Stack Monitoring

To setup alarm notification rule, we have to create alarm.

Go to OCI Console --> Observability and Management --> Monitoring --> Alarm Definition

and click on create alarm

Here I am going to setup alarm for "number of request per minute" for one of WebLogic Server.

So I am going too use Metric "WebRequestRate".

To see all metrics for all EBS discovered targets in stack monitoring please refer below link.

EBS Stack Monitoring Metric Reference

So Define alarm, you can give any name as per metric you want to use.

Metric description, here I use metric name = Web Request rate

Metric dimension, here I use "Domain admin server" name but metric dimension is optional, if you wont specify it will use all WebLogic server in stack and apply metric on all.

then the trigger rule..

Before setting up this value.. I went to the target in stack monitoring and click on performance in resources--> go to charts

Check the Web Request rate chart to see the value.( This will help in setting up trigger rule value), so I saw high value is currently 343.3 ops/min. (That's why I setup trigger for > 200 to get alert)