Dontcheff

Archive for the ‘DBA’ Category

SQL Trace and X-ADG in the Oracle Autonomous Database

In Autonomous, DBA, OCI, Oracle database, Replication on October 6, 2021 at 09:12

Two very different in nature but equality useful features are now available in the Oracle Autonomous Database:

  1. SQL Tracing in Autonomous Database
  2. Cross-Region Autonomous Data Guard in ADB-S

Here is how to enable and use them:

SQL Trace in ADB:

You need first a standard bucket as SQL tracing files are only supported with buckets created in the standard storage tier. Also, create a token (you can have at most 2 tokens) and do not use your OCI password when creating the credentials.

Next, you have to create a credential for your Cloud Object Storage account. Note the full username below – do not simply use the one with what you login to the console.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'JULIANDON_CREDENTIAL',
    username => 'oracleidentitycloudservice/juliandon@yahoo.com', 
    password => 'generated_token'
);
END;
/

PL/SQL procedure successfully completed.

Afterwards, set the init.ora parameters DEFAULT_LOGGING_BUCKET to specify the Cloud Object Storage URL for a bucket for SQL trace files:

SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/juliandon/b/adbkofa/o/';

Database altered.

Next, specify the credentials to access the Cloud Object Storage. Note that although I am doing this as the ADMIN user, I still have to prefix the credential with ADMIN. Otherwise, you get an error message.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.JULIANDON_CREDENTIAL';

Database altered.

Before we can enable SQL trace, we configure the database to save SQL Trace files:

exec DBMS_SESSION.SET_IDENTIFIER('sqltrace_jd');

PL/SQL procedure successfully completed.

exec DBMS_APPLICATION_INFO.SET_MODULE('module_jmd', null);

PL/SQL procedure successfully completed.

ALTER SESSION SET SQL_TRACE = TRUE;

After running the SQLs, disable SQL tracing so that the collected data for the session is written to a table in your session and to a trace file in the bucket you configured when you set up SQL trace.

ALTER SESSION SET SQL_TRACE = FALSE;
ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';

The SQL Trace facility writes the trace data collected in the session to Cloud Object Store in the following format:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

When you enable SQL Tracing, the same trace information that is saved to the trace file on Cloud Object Store is available in the SESSION_CLOUD_TRACE view in the session where the tracing was enabled.

SELECT trace FROM SESSION_CLOUD_TRACE ORDER BY row_number;

After you close the session, the data is no longer available in SESSION_CLOUD_TRACE.

DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

Check Connor McDonald’s blog entitled SQL trace on your cloud database.

Cross-Region Autonomous Data Guard in ADB-S

Autonomous Data Guard provides a standby database instance in a different availability domain in the same region or in a standby database instance in different region.

If you create the standby database in the current/local region and if the primary instance becomes unavailable – the Autonomous Database automatically switches the role of the standby database to primary and begins recreating a new standby database.

ADB currently supports up to 2 standby databases – a local one in the same-region and an additional one which is remote – called cross-region.

So, with the new cross-region standby database, you can perform a manual failover to the standby database if the current region goes down.

A detailed blog by Nilay Panchal entitled Cross-Region Autonomous Data Guard – Your complete Autonomous Database disaster recovery solution! covers in detail how to create the remote standby database and how to manually switch over.

Note that each region has one or a few nearby paired regions in which a remote standby may be created. As you can see from the screenshot above my tenancy in Frankfurt is subscribed to 3 remote regions in which I can create a remote standby.

It is important to know that ADB-S does not allow us access to the standby databases but after a switchover or failover, the database wallet downloaded in the primary database region can be used in the remote region.

It is extremely simple to manually switchover to the other region – in my case from Frankfurt to Zurich, just with a click of a button:

Simple and elegant!

The Oracle Cloud Infrastructure Mobile App

In Autonomous, DBA, OCI on September 1, 2021 at 06:46

Being on summer vacation without my laptop, I still have access to my OCI tenancy via the mobile app by simply using my phone.

The OCI mobile app is available for both Apple iOS and Android. With the app, we can check the resources and view alarms, billing, days elapsed and limits.

First, you have to download the app – search for “Oracle Cloud Infrastructure”:

After you login in to the mobile app, you get the following screen from where you can either modify the settings of your profile or/and view your resources, billing status, alarms and limits:

Indeed, we cannot do much besides viewing some of your resources and billing charges (you cannot drill down). And for now, I can see only my ADW and ATP databases, not AJD or APEX.

But, at least I can check if my databases were stopped before I left for vacation:

For faster sign-in to the mobile app, you can enable automatic sign-in. Automatic sign-in uses an API key to authenticate you when you access the app, keeping you signed in until you sign out. Each user has a limit of 3 API keys. If your account has reached this limit, you can’t use this feature in the mobile app until you delete one of the existing API keys. You can use the Console to delete API signing keys. My virtual private vault count is zero – so I could not enable automatic sign-in:

It is also possible to switch the regions (my default is Frankfurt as you can see from above) and you can set the mobile app to use UTC time or local time.

Life, Grace and Rollover time of passwords in the Oracle Database

In DBA, Oracle database, Security and auditing on August 6, 2021 at 10:26

The latest Release Update of Oracle Database 19c, namely 19.12, comes with two new features: Oracle memory speed support for PMEM devices and gradual database password rollover for applications. The gradual database password rollover is backported from Oracle 21c.

I still remember very well the times when changing the password of a databases schema/user required shutting down both the database and the application and this practice has not really changed much until now. You can change database credentials without downtime thanks to proxy users:

Password rolling change before Oracle 21c

With the latest RU of 19c, there is a way to do this online. And of course also with 21c.

Now, there is a password rollover time period when the user can log in using either the old password or the new password. Here is how it works.

Oracle Database 19.12 introduces a new parameter related to the already existing PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters called PASSWORD_ROLLOVER_TIME.

Note the default and the minimum and maximum values for the 3 parameters above. All numbers show days.

In order to enable the feature, we have to modify first the user profile with a non-zero limit for PASSWORD_ROLLOVER_TIME. This allows the database password of the application user to be changed to a new one and at the same time the old password can be used for the time specified by the PASSWORD_ROLLOVER_TIME. During the rollover period of time defined by PASSWORD_ROLLOVER_TIME, the application user/schema can use both the old password and the new password. When the rollover time expires (that is 1a), only the new password can be used.

After a password is created for a new user or the password is being changed, then the password follows a life cycle and grace period in four phases: 1a&1b, 2, 3 an 4:

We can query DBA_USERS to find the user’s account status from the ACCOUNT_STATUS column (check the screenshot on the top of the post). It is important to point out that after the rollover period has begun, we can still change the password: with or without the REPLACE clause. The rollover start time is fixed at the time when the user changes the password. The start time is not affected by further password changes during the password rollover period. 

Here is how I could connect to the database with 2 different passwords after the initial profile re-configuration:

If needed, we can quit the rollover time period at any time with the following command:

ALTER USER JULIAN EXPIRE PASSWORD ROLLOVER PERIOD;

We cannot configure the gradual database password rollover for the following connection types:

  • Direct logins for Oracle Real Application Security users
  • Kerberos-, certificate-, or RADIUS-based externally authenticated connections
  • Centrally managed user (CMU) connections
  • Administrative connections that use external password files
  • The Oracle Data Guard connection between the primary and the standby

For more on the topic check Rodrigo Jorge’s post Gradual Database Password Rollover brings new backdoor opportunities to find out how to prevent from possible hackers when using this new feature or if interested in the internals, check Understanding internally how 21c Gradual Database Password Rollover works.

A good example on how to use the feature is given by Mouhamadou Diaw in his blog post Oracle 21c Security: Gradual Database Password Rollover

And here is something from Oracle v4:

It is OK for DBAs to make mistakes

In DBA on July 26, 2021 at 18:42

At OpenWorld 2016, one of the presentations I gave was entitled “My 13 DBA mistakes in 13 years“. Seldom I see so many smiley faces in the audience.

Earlier, in 2009 at the BGOUG, Plamen Zyumbyulev and I gave a very similar talk called “Don’t do like they do. People would make fun of you“. We were both quoting mistakes we have done while working with the databases.

In my opinion, it ok for DBAs to make mistakes – it just happens from time to time. It can happen during the day, during oncall and it happens also to the very best ones.

I have heard from some DBAs that they have never made any mistakes. May be. Who knows. But when spending years and years of database administration once in a while it is OK to press the wrong button or type the wrong command. Or the right command and press the right button but … in the wrong window 🙂

For me, the transition from DBA to Senior DBA comes at the moment when you start admitting your mistakes. I believe this is the borderline.

Here is a good collection of mistakes DBAs have made but first my top 3 ones 🙂 I mean my mistakes.

  1. New patcheset – I think 9.2.0.6 or something. I patched the RMAN catalog database running on a standalone server. So far, so good. All done. Perfect! I wanted to remove the patchset binaries afterwards. Simple rm –rf  * Then I got a phone call from the Unix admin: Julian: are you connected to ..? Cold sweat. I noticed screens were changes one after each other. Then even before typing pwd I knew I was root and I would see that single slash – live and learn. We had to reinstall the OS and recreate the RMAN catalog database.
  2. In a production database I saw a user called ABC – not too many tables, I thought it was some test schema remained from who knows what and where. Then: drop user cascade; Why didn’t I asked what that schema was and who created it and what it was used for! The answer I got (after the schema was dropped) was the worst I could expect. Like the worst of the worst. Luckily, for some reason I ran a schema export before the drop. I have never ever as DBA run an import so quickly in my life 🙂
  3. Heavily loaded 24×7 OLTP database – I got call that a filesystem was 99% full – it had only UNDO tablespace files. One of them rather huge. Something I have done so many times: recreate the UNDO online:

Only one problem: I deleted with rm the wrong UNDO file – imagine what happened – all databases which work within one business application got messed up – at least transitions were failing. I still remember the reaction of the datacenter manager when with a pale face I went to report what happened: “This is just a website Julian – no human lifes are at stake – go and fix it”. We managed to fix it without shutting the databases down but I had another DBA behind me following what I am typing. Afterwards, most of us were often watching each other when doing something important – 4 eyes are always better than 2.

What is the biggest mistake you made in production? by Tara Kizer

5 DBA Mistakes That Can Cost You Your Job by Robert Davis

Confessions of a DBA: My worst mistake by Phil Factor

Don’t Just Do Something, Stand There! Avoiding Junior DBA Mistakes by Jim Czuprynski

Top 6 MySQL DBA Mistakes by Rob Gravelle

Common Mistakes of DBA in MS SQL Server by Evgeniy Gribkov

The 3 DBA Mistakes You Don’t Know You Are Making by Thomas LaRock

And here are interesting videos to watch: Top 10 DBA Mistakes: Horror Stories!

DBAs: 20 years after

In DBA, Oracle database on June 28, 2021 at 16:37

Oracle 9i was released 20 years ago. Oracle Real Application Clusters (RAC) and Oracle XML DB were cool new things except for a few OPS DBAs who even stopped playing ping-pong if you know what I mean.

Now, Oracle 21c is available from the public cloud and and several other database brands are competing shoulder to shoulder with the Oracle database.

However, DBAs still go their separate own ways, just like at the end of the book “20 years after” by Alexandre Dumas.

Some are still focused mostly into on-premises database work, some are very cloud oriented and some are positioning themselves into the golden middle. Just like the Three Musketeers.

The DBA profession still stays the same: solid, desired by IT experts and for good or for bad – still arguably the most complex job in IT. Complex in both cloud and off-cloud.

In a recent study, the DBA profession is #7 in Best Technology Jobs, #19 in Best STEM Jobs and #55 in the top 100 Best Jobs:

According to the study, Database Administrators made a median salary of $93,750 in 2019. The best-paid 25 percent made $120,880 that year, while the lowest-paid 25 percent made $68,340:

As we all know, it is still not all about the money… So, let us look at the rest.

Upward Mobility, meaning opportunities for advancements and salary are “Above Average”, same is the Stress Level, meaning work environment and complexities of the job’s responsibilities. But the Flexibility which means an alternative working schedule and work life balance is rated as “Average”.

I personally think that all 3 categories above are well rated, however there is another category called Job Security which totally depends on the DBA – it cannot be generalized.

It is worth reading Tim Hall’s What Employers Want : A Series of Posts and Learning New Things : A Series of Posts.

If you would like to know what will change, what new skills are required and how to work in hybrid environment, check The Cloud and Database Administration by Craig S. Mullins.

The Next Generation of DBAs is well described in Cloud DBA: The Next Generation of Database Administrator?

A DBA should be nowadays master of at least few database brands. There are several list and websites pointing towards the top/best databases but imho every database has its use cases and clients. Here are few:

Top 15 databases to use in 2021

Top 25 Best Database Management Software in 2021

6 Best Databases To Use In 2021

Best database software in 2021

And this one is from today, June 28th, 2021: Top 30 Most Popular Database Management Software: Complete List

Let me point out at the end that the Database field is rapidly changing – cloud native databases, NewSQL, etc. so DBAs are even more and more important. The application architecture is getting more diverse because of cloud and the newly emerging databases plus the transformation of traditional databases – think of Oracle Autonomous Database for instance.

As a DBA, there is always a database you like and prefer more than another one but once you are comfortable with the work you are doing, either on-prem or in the cloud – enjoy, learn new things and look into the future remembering you have chosen one of the best professions in the world!

HeatWave MySQL DB Systems in OCI

In DBA, MySQL, OCI on June 7, 2021 at 10:44

HeatWave is a distributed, scalable, shared-nothing, inmemory, columnar, query processing engine designed for fast execution of analytic queries. It is enabled when you add a HeatWave cluster to a MySQL DB System.

You can think of HeatWave as an easy way to run high performance analytics against the MySQL database.

When creating a MySQL DB System in Oracle Cloud Infrastructure, the options are now Standalone, High Availability and (the newest) HeatWave:

Here is a how the HeatWave architecture looks like:

A HeatWave cluster supports up to 64 nodes. The number you choose depends on the size of the database and the amount of compression that is achieved when loading the data into the HeatWave cluster. You cannot connect the app/session drectly to the HeatWave cluster.

Starting from scratch, choose a MySQL DB system from the OCI Databases menu:

And then start creating a MySQL DB system with the HeatWave option:

Once ready, adding additional clusters (when needed) is simple:

As of now, the only available shape is VM.Standard.E3, so you might get error messages when trying to add more than 4 clusters:

Here is what I got: “You have reached your Analytics Cluster service limit of 4 in this Availability Domain for MySQL.HeatWave.VM.Standard.E3. Please try launching in a different Availability Domain or Region, or try using a different shape. If you have reached all Service limits, please contact Oracle support to request a limit increase.”

Note that 1 node matches to ½ a TB of memory. The increase is linear.

You can rely also on Oracle to estimate the number of nodes:

Before loading data into the HeatWave cluster, the data/tables have to be prepared. Preparing tables involves modifying table definitions to exclude certain columns, define string column encodings, add data placement keys, and specify HeatWave (RAPID) as the secondary engine for the table – note that InnoDB is the primary engine. Loading a table into a HeatWave cluster requires executing an ALTER TABLE operation with the SECONDARY_LOAD keyword.

If a query accesses a table that is not loaded, the query is not offloaded to the HeatWave cluster for processing. Note that queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave cluster for accelerated processing. Results are returned to the MySQL DB System node and to the MySQL client or application that issued the query.

When a table is loaded, data is sliced horizontally and distributed among HeatWave nodes. After a table is loaded, changes to a table’s data on the MySQL DB System node are automatically propagated to the HeatWave nodes. No user action is required to keep data synchronized.

All the details can be found in the HeatWave documentation (it is short – 68 pages only). This is the best place to understand how to prepare & load the data and how to unload tables. The document is full of examples and lists and string functions and operators. Also, you can find a list of all functions, data types, variables, JOIN types, SQL modes, and other expressions and functionality that are not supported by HeatWave.

Unloading table is pretty straightforward:

mysql> ALTER TABLE sales SECONDARY_UNLOAD;

Here are some additional link if interested in more detail:

Licensing Types of the Oracle Database

In Cloud, Database options, Databases, DBA, New features, Oracle database on May 16, 2021 at 13:18

After being asked on daily basis all kinds of questions on Oracle Database Licensing, as time goes by, you sort of understand it. Sort of, because the Oracle Database Licensing Guide is 602 pages long and gets often updated. The latest one is from April 2021 – now it is mid-May.

Moreover, you have perhaps seen all Oracle certifications but if you search for one on licensing you will find what I did – there isn’t one.

What I am trying to do now, is to summarize Database Licensing in a short blog post – this might be helpful for many to at least understand the concept.

There are 3 types of licenses for the Oracle Database: Packs, Options and Features and 9 Oracle Database Offerings: Standard Edition 2, Enterprise Edition, Oracle Database Appliance, Exadata, Exadata Cloud Service and Cloud@Customer, Database Cloud Service Standard Edition, Database Cloud Service Enterprise Edition, Database Cloud Service Enterprise Edition – High Performance and Database Cloud Service Enterprise Edition – Extreme Performance (you can see their abbreviations in the table below).

  1. Packs: there are 5 different packs for the Oracle Database:

2. Options: there are 15 database options for the Oracle Database:

  • Oracle Active Data Guard
  • Oracle Advanced Compression
  • Oracle Advanced Security
  • Oracle Database In-Memory
  • Oracle Database Vault
  • Oracle Label Security
  • Oracle Machine Learning
  • Oracle Multitenant
  • Oracle On-Line Analytical Processing (OLAP)
  • Oracle Partitioning
  • Oracle RAC One Node
  • Oracle Real Application Clusters (Oracle RAC)
  • Oracle Real Application Testing
  • Oracle Spatial and Graph
  • Oracle TimesTen Application-Tier Database Cache

Here are the ones related to Consolidation, HA, Managability and Performance:

3. Features: there are 131 features that can be licensed with the Oracle Database out of which 105 are for EE and 123 are for Exadata. As you can see, there are 3 features available for Exadata, ExaCS and ExaC@C falling under the functional category of Autonomous:

If you would like to drill down in detail, use the Database Feature and Licensing tool which is available online without the need to register or have an Oracle account.

Moreover, the Oracle Enterprise Manager Licensing Manual is 366 pages, so there is more to read if you are done with the Database Licensing Manual.

You might think that is way too much for me, and perhaps it is, but the situation is very similar with other database vendors. Let us look at AWS and GCP for instance:

AWS have more than 10 database offering:

Amazon Aurora
Amazon RDS
Amazon Redshift
Amazon DynamoDB
Amazon ElastiCache
Amazon DocumentDB (with MongoDB compatibility)
Amazon Keyspaces (for Apache Cassandra)
Amazon Neptune
Amazon Timestream
Amazon Quantum Ledger Database (QLDB)
AWS Database Migration Service (DMS)

GCP have also more than 10 database offerings:

Relational: Bare Metal Solution for Oracle workloads
Cloud SQL: Managed MySQL, PostgreSQL and SQL Server
Cloud Spanner and BigQuery
Key value: Cloud Bigtable
Document: Firestore and Firebase Realtime Database
In-memory: Memorystore
NoSQL: MongoDB Atlas and managed offerings from open source partner network including MongoDB, Datastax, Redis Labs, and Neo4j

And, after all, Azure are not much behind:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Virtual Machines
Azure Database for PostgreSQL
Azure Database for MySQL
Azure Database for MariaDB
Azure Cosmos DB
Azure Cache for Redis
Azure Database Migration Service
Azure Managed Instance for Apache Cassandra

After all, being expert in database licensing in a skill of its own!

Applying one-off patches in the Cloud on Oracle Database 21c

In Cloud, DBA, OCI, Oracle database on April 27, 2021 at 08:37

Oracle have just released new fixes for the 21c version of database release: a security fix and a JDK bundle patch.

The recommendation is to apply these two patches mentioned below to your databases:

• 32640471 21C SECURITY FIXES FOR CPUAPR2021
• 32685286 JDK BUNDLE PATCH 21.0.0.0.210420

Most likely, you will first get an email from Oracle to let you know that the patches are already available:

How to apply the patch? The one-off patches (now they are call interim patches) can be applied via the Console, API or even manually. To apply an interim patch manually, you can use the Opatch utility. The detailed steps are provided in the Applying one-off patches on Oracle Database 21c documentation. The patches can be applied in any order.

Here is how simple and easy it is:

1. For the database on which you want to apply the patches, just click its name to display details and under Resources, click Updates:

2. Click on “Apply a one-off patch“:

3. Then, in the Apply one-off patch dialog, enter the patch numbers. Use a comma-separated list to enter more than one patch. I did apply them one after each other. Paste the patch number and then click Apply.

While the patch is being applied, the database’s status displays as Updating:

A work request is created for the patching operation allowing us to monitor the progress of the operation.

If the operation completes successfully, the database’s status changes to Available:

It is that simple!

Migrating databases with several database links

In Cloud, Consolidation, Databases, DBA, Oracle database, Replication on April 1, 2021 at 09:08

In a couple of recent database migration cases, one of the main questions raised, was how to figure out all outgoing and incoming database links as they have to be modified after the massive migrations.

DBLINKS5

Outgoing database links is simple: DBA_DB_LINKS describes all database links in the database. And this view has been part of the database (at least) since 7.3.4

The tricky part is how to find all incoming database links. At least before 12.2, where a new view called DBA_DB_LINK_SOURCES, shows the information of the source databases that opened database links to the local database.

So, how about the databases that are version 12.1 and below?

An Oracle community discussion on the MOS DBA forum gives several ideas:

Option 1: Bruno suggests to “start from the listener logfile; with some “awk/sed/vi” work it should be possible to extract the list of “origins” of the connections… -> From this list, identify the database servers -> Search database links on relevant databases on these servers”.

Might work but might be rather tedious work if there are 100s of different servers.

Option 2: Brian suggests “to query V$SESSION to see active sessions from the other database server. Hint…look at the MACHINE column to see if it matches the other database server name. Querying V$SESSION will only work if the link is open when you query it. As such, you may want to add an AFTER LOGON trigger which writes an audit entry to a table if the connection is from that database server.”

If you create a logon trigger to insert all incoming connection via database link note that in 11g, you can do that using value sys_context(‘USERENV’,’DBLINK_INFO’) which will give us all information. But check first Doc ID 2593966.1 as there is Bug 18974508: sys_context(‘userenv’, ‘dblink_info’) returns incomplete information.

But before 10g, there is no DBLINK_INFO, we we must use x$k2gte:

 
select username, osuser, status, sid, serial#, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte);

The above is documented in Doc ID 332326.1: How to identify a session started by a remote distributed transaction? The fixed table x$k2gte contains 2PC Global Transaction Entry. The column k2gtdses in x$k2gte has the session state object and this can be mapped to the saddr column of v$session.

But as explained by Mark, the problem is that until the trigger finishes the session the remote db link session is not considered to exist and only upon successful session connection does Oracle then go and update related facts about the session.  Oracle does not guarantee read consistency on v$ views and the v$ views are based on x$ tables which are really program storage areas.  These areas get updated at various points in the logic.  It is possible that a logon trigger may not work in this specific case.  An alternate approach would be to run a process every N time that just snapshots what is out there and records new remote queries.  After all you really only need one capture per remote source whether you care about only database links or care about each client server.

One of the top database experts, Mariami Kupatadze, gave us a very elegant way of how to find remote sessions executing over a database link using x$k2gte, x$ktcxb, x$ksuse and v$session_wait in a single SQL statement.

A more detailed version called Identifying database link usage was written by John Hallas in 2015.

Long story short: for databases from 7.3 till 12.1 create a job capturing the distributed transactions based on the script given in Doc ID 104420.1 “Script to show Active Distributed Transactions”. And you can modify the scripts if not only the active remote transactions need to be captured. For 12.2 and after, just use the view  DBA_DB_LINK_SOURCES. 

create_database_link

 

Oracle Cloud Infrastructure (OCI) Database Management

In DBA, OCI, Oracle database on March 18, 2021 at 15:51

Early this year, Oracle announced the Announcing the general availability of Oracle Cloud Infrastructure Database Management.

With Database Management Cloud Service, DBAs get a unified console for on-premises and cloud databases with lifecycle database management capabilities for monitoring, performance management, tuning and administration.

As of today, Database Management is available to use with external Oracle databases deployed on-premises. Support for Oracle Databases on Oracle Cloud Infrastructure will be coming soon.

The Database Management pricing is quite simple and clear, here it is:

DBM_price

This is a deep dive into the new OCI Database Management Service, and here are the basics:

  1. From the main menu go down and search for Database Management:

DMB2

2. On the right side, go to “Oracle Databases” in order to enable Database Management (opens a new tab):

DBM3

3. Start registering external pluggable databases – as of today we cannot register cloud databases:

DBM4

4. Select the compartment and choose a database display name:

DBM5

5. Select the database and click on register.

DBM6

The Database Management service comes with 4 main features:

  • Fleet monitoring and management
  • Database groups
  • Database summary
  • Custom PL/SQL jobs

Before you enable and use Database Management, you must review and complete some prerequisite tasks:

  • Install Management Agents for Database Management
  • Register the Oracle Database with the External Database service
  • Connect the Oracle Database to the External Database handle
  • Create Oracle Cloud Infrastructure IAM user groups

After you complete these prerequisite tasks, you must create policies to assign permissions to user groups in order to enable and use Database Management. 

In Oracle Cloud Infrastructure, policies provide the permissions required to allow users to work in certain ways with specific types of resources in a tenancy or a particular compartment.

A policy is written to determine who can perform what functions on which resources using the following basic syntax:

Allow <subject> to <verb> <resource> in <location>

In this policy example, you can create to grant the DB-AEG user group the permission to enable Database Management for all External Databases my your tenancy JULIANDON:

Allow group DB-AEG to use external-database-family in JULIANDON

The external-database-family is the family resource-type for the External Database service, which includes the following individual resource-types:

  • external-container-databases
  • external-pluggable-databases
  • external-non-container-databases
  • external-database-connectors

For more details, check the Oracle Cloud Infrastructure Documentation.