Oracle Tools

Oracle 12.2 – New Features I like – Part 1: Multitenant “Hot Clones”

I like the Oracle Multitenant architecture which was introduced in 12.1. But the concept to clone a source database to multiple copies in a small step had one big problem.  In Oracle 12.1, to clone a pluggable database the source database had to be in state read-only.

In 12.2 is it not longer necessary to set the source pluggable database in state read-only, the source database has not to be modified to create a clone. Oracle calls it in the documentation “Hot Clone”.

Link to the official Oracle documentation: https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN13584

Here is a clone of the pluggable database PDB1 into PDB2 in the Oracle Database Cloud Service.

Verify existing Pluggable Databases and States – PDB1 is in state READ-WRITE

Verify if Oracle Managed Files are in use

Using Oracle Managed Files makes the file name convert much easier. You don’t have to care about files and directories. Like the feature says, Oracle is managing that for you.

Create TEMP directory

Is it a bug or is it a feature? The directory for the temporary datafile will not be created, we have to do it manually.

Error message when the directory is not created in advanced when a clone is started:

Clone PDB1 into PDB2

Attention: Databases in the Oracle Database Cloud Service are created “Secure by Default”, that means that Transparent Data Encryption (TDE) is enabled. This is why I need the KEYSTORE INDENTIFIED BY command in line 6.

Verify PDB2 state

The cloned PDB2 is in state MOUNTED after the clone procedure.

Open PDB2

Now the pluggable database PDB2 is ready to use, just open it. For more information how TDE works in a multitenant environment (export key etc.) take a look here: http://docs.oracle.com/database/122/ASOAG/using-transparent-data-encryption-with-other-oracle-features.htm#ASOAG10353

 

 

 

Your Oracle Cloud Products in My Oracle Support – Now you are Administrator

Three weeks ago I have ordered the Oracle Database Backup Service. The registration via shop.oracle.com was easy, minutes later after I have entered my credit card details and pressed the submit button, I got the login information via email and Oracle has given me a new CSI number.

Some time later I took a look in My Oracle Support, my new CSI was added but in state PENDING.

cloud_pending01

Now I clicked on the button to Request Access. This message cames up, this is the normal procedure when you buy a product at at Oracle and when you get a new CSI. I clicked on Accept Responsability.

cloud_pending02

Afterwards the button below has changed to Request Access.

cloud_pending03

And now I am administrator of the CSI, the status is now Approved.

cloud_pending04

After a fresh login, the main page has changed in my My Oracle Support dashboard, in the top right corner now I can change to another dashboard to open service requests for cloud products.

cloud_pending05

And this is the new Oracle Cloud Support dashboard

cloud_pending06

Enterprise Manager 13c – How to apply p23095307: enterprise manager for oms plugins 13.1.1.0.160429

Patch 23095307 is the newest system patch for the Enterprise Manager 13c family. This patch will be applied with the new patch tool OMSPatcher (see blog post http://dbakevlar.com/2016/05/em13c-applying-system-patches-with-the-oms-patcher from @dbakevlar for details). To apply this patch, the OMS has to be stopped.

Prerequisites

  • OMS 13.1.0.0.0
  • OMSPatcher 13.6.0.0.1 – I have installed it in directory /u00/app/oracle/product/em13cr1/OMSPatcher
  • URL / Username / Password from your EM13c Weblogic AdminServer (emctl status oms -details)
  • Patch 23095307 is unzipped and available in a local directory, in my case it’s in /u00/app/oracle/stage

The newest OMSPatcher can be downloaded according the My Oracle Support Note How to upgrade the OMSPatcher to latest version of OMSPatcher (Doc ID 2135028.1)

Patch Verification

For the verification you don’t have to shutdown the OMS. Be sure that your ORACLE_HOME is set to the EM13c installation directory.

As you can see, there is a WARNING in the output that a sub-patch is incompatible with the oracle.sysman.ssa.oms.plugin version 13.1.1.0.0. The SSA.OMS plugin is a plugin for the cloud self service zone. The patch 23092170 is the EM Cloud Plugin Bundle Patch 13.1.1.0.160429. I don’t have installed the cloud plugin during the installation of the EM13 because we don’t use it,  now the OMSPatcher wants this plugin.

Installation of the missing Plugins

To resolve the warning, I have installed the two missing plugins. The Oracle Cloud Application plugin requires the Oracle Virtualization plugin first. Attention: If you install the plugins on the Oracle Management Server, the OMS will be restarted during plugin installation process.

The Oracle Virtualization Plugin

plugin_virtualization

The Oracle Cloud Application Plugin

plugin_cloud

OMSPatcher Re-Analyze

Now the analyze job runs without any warnings or errors. I have removed some lines in the output for better reading.

The problem is solved, the system is ready to patch.

Patch apply

Before you apply the patch, you have to stop the OMS. Then you can apply it.

I have removed some output. if you want to have the full output, you can download it HERE.

Patch successfully applied

The patch is applied, the OMS can be started again. I don’t know why the patch needs the plugins. My understanding from the plugins is to have a small installation, and only the components which will be used should be configured. And at this point, personally I would expect only an INFORMATION  that some components are not installed so they not will be patched and not a WARNING.

The OMSPatcher is a new tool for the EM13c patching, maybe there are some improvements in one of the next releases.

EM13c – Database Monitoring Template with DB Alert Log Metric

The new  DB Alert Log metric

Since the Oracle Enterprise Manager database plugin version 12.1.0.4, there is a new metric “DB Alert Log” available. This metric replaces the existing “Alert Log” metric. The benefit of the new plugin is if an ORA error is detected multiple times in the database alert log based on same error stack, it will be de-duplicated and only one metric alert is generated. Oracle’s recommendation is to replace this metric on the database targets – Changes to the Metric Alert Log Monitoring in Database Plug-in 12.1.0.4 and later (Doc ID 1587020.1).

How to create such metrics  and many more is very well described in Kellyn Pot’Vin blog post serie http://dbakevlar.com/2013/08/em12c-enterprise-monitoring-part-i/

The old metric Alert Log:

alert_log

The new metric DB Alert Log:

db_alert_log

In use the following thresholds for ORA- errors:

  • Critical alerts for ORA-600 and 7445
  • Warning alerts for all other ORA- errors

A basic Monitoring Template

To make my DBA job easier, I have created a basic template which monitors me the most important things. Sure, there are many more things what you can verify in EM13c, but I wanted to keep it simple. Feel free to extend your monitoring templates.

Metric Comparison Operator Warning Threshold Cricital Threshold Collection Schedule
Archive Area Archive Area Used (%) > 80 Every 15 Minutes
DB Alert Log Alert Log Error Trace File Every 5 Minutes
Alert Log Name
Archiver Hung Alert Log Error Contains ORA-
Data Block Corruption Alert Log Error Contains ORA-
Generic Alert Log Error Matches ORA-[0-9]+[^0-9] ORA-0*(600?|7445)[^0-9]
Media Failure Alert Log Error Contains ORA-
Session Terminated Alert Log Error Contains ORA-
Time/Line Number
Incident Generic Incident Matches .* Every 5 Minutes
Operational Error Data Block Corruption Matches .* Every 5 Minutes
Response Status = Down Every 15 Seconds
Tablespaces Full Tablespace Space Used (%) >= 85 97 Every 30 Minutes
Tablespaces Full (dictionary managed) Tablespace Space Used (%) (dictionary managed) >= 85 97 Every 30 Minutes

XML for Import

If you like to use this metric, you can download the XML here: db_monitoring_template_1.0  – just save the code as XML and import the metric in your Oracle EM13c Enterprise Manager.

Verification

After deploying of the template to the targets, verify on the target databases that a) the template is deployed and b) that the collection is enabled.Go to Oracle Database – Monitoring – Metric and Collection Settings (click on image for better view):

metrics

Useful My Oracle Support Notes

Houston – we have deleted all SYS DBMS_SCHEDULER Jobs !

Bad, bad PL/SQL….

A customer of me has executed a simple script to cleanup DBMS_SCHEDULER jobs and programs for an application. Unfortunately he did as user SYS. The content of the SQL script:

All the Oracle internal maintenance jobs and programs like the AUTO_SPACE_ADVISOR_PROG, GATHER_STATS_PROG, FILE_WATCHER_PROGRAM are deleted.

On a 12.1.0.2 single instance database on Linux are per default 19 DBA_SCHEDULER_JOBS and 10 DBA_SCHEDULER_PROGRAMS pre-configured.

Oracle has a MOS note called  How to Rebuild DBMS_SCHEDULER Default Jobs and Autotasks (Doc ID 2089546.1) – but this note is old and incomplete. It does not help to rebuild all the jobs and programs in a 12.1.0.2 database.

After some discussions with My Oracle Support, this way here works to rebuild the jobs. Unfortunately it’s an “offline” task, you have to restart your database in UPGRADE mode. But it works.

1. Make sure that you have a Backup of your Database

2. Restart the Database in UPGRADE Mode

3. Run these Commands as SYSDBA in Order to recreate the missing Scheduler Jobs

All jobs except XMLDB_NFS_CLEANUP_JOB are re-created now. To rebuild the XMLDB_NFS_CLEANUP_JOB you can execute this command as user SYS in SQL*Plus. It’s just an extract from the file ?/rdbms/admin/xdbu102.sql which creates the required job during the regular instance creation process.

4. Restart the Database

5. Run these Queries to verify invalid Objects

6. Verify that DBMS_SCHEDULER Jobs are back

7. Verify DBMS_SCHEDULER Programs are back

Summary

Deleting the SYS DBMS_SCHEDULER jobs is one of these mistakes which can be easily repaired. But during the rebuild action, the database is not available for the end users and this is bad. I’m looking forward that there will be an online solution available maybe in the future.

Another approach could be to execute a FLASHBACK QUERY against the dba_source view to get the DDL for the missing jobs and programs before the delete action. But this has to be tested first.