Roundtable: How are Databases Adapting to New Cloud Architectures?

Hi all!
Stay sharp, it’s confirmed event for next 27th evening Brazilian Time. If you around Três de Maio, hope you can make it in person!
Otherwise, we are looking forward to record and share session, still evaluating streaming options and requirements.

Note Grepora is sponsoring this roundtable, so count with you all!
Oh, it will be fully in portuguese. Hope you don’t mind. 🙂

Event: Round Table: “How are Databases Adapting to New Cloud Architectures”
Date: 27/6/2019 19:30 BRT (18:30 EST) to 21:00 (20:00 EST)
Estimated Duration: 1:30 hours

We count with great names, see below:

MesaRedonda.jpeg

See you there, cheers!

How to Prevent Violate Active Dataguard License?

Hey folks,
As you all liked last week posts about preventing to violate Oracle Licensing, so let’s go another one, a quick one this time: Active Dataguard.

Matheus, is there a way to prevent using the Active Dataguard Option?

Well, there is. If you consider that this is basically a regular Dataguard that is opened in Read Only mode, here you go:

srvctl modify database -d GREPORADB_STBY -s MOUNT

By preventing the database open, we are preventing it to violate the Active Dataguard feature.

Quick reference on this:
– Which are Supported Methods to Prevent Active Data Guard Usage When License is Not Available? (Doc ID 2269239.1)

Hope it helps!

How to Disable AWR and Prevent Violating Diagnostic Tuning Pack License

Hey folks,
Seems you like it, so on same line that the post from last week: Let’s say you want to prevent yourself to violate Oracle licensing of your Enterprise Edition database… But as you know, Oracle is tricky sometimes. For example, all Diagnostic Pack features are enabled by default, you just need a JR DBA to go there and used for you to be stuck.

By the way, here is the first mistake: No, AWR is not part of Enterprise Edition, it also requires Diagnostic Pack!

Ok Matheus, so how to prevent it? Here it goes:

Disable AWR with Oracle provided script

Use the script dbmsnoawr.plb provided in MOS Doc ID 1909073.1 to disable AWR once the database has been created.

To install, run the package as SYS from SQL*Plus:
SQL> @dbmsnoawr.plb

To execute the package, use the command:
SQL> begin dbms_awr.disable_awr(); end;

Modify the CONTROL_MANAGEMENT_PACK_ACCESS init parameter to NONE and disable the AWR related advisors.

Disable AWR Related Options

ALTER SYSTEM SET control_management_pack_access=NONE;

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           ENABLED
sql tuning advisor                                               automatic sql tuning task                                        ENABLED


BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);

  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);

END;
/

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           DISABLED
sql tuning advisor                                               automatic sql tuning task                                        DISABLED

Some reference on it?
– How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)
– Controlling Diagnostic and Tuning Pack Usage (Doc ID 436386.1)
– Disabling and Uninstalling AWR (Doc ID 1909073.1)

Hope it helps!

Licensing: Remove Options During DB Creation

Hi all,
So, let’s say you want to prevent yourself to violate Oracle licensing of your Enterprise Edition database… What a better moment to do it then when you are creating the database?

During database creation, there are several options that can be opted out. DBCA CLI has the option “-dbOptions” to disable options at will. For example, see below how to disable all the options.

dbca -silent -createDatabase -responseFile /home/oracle/dbca/dbca.rsp -dbOptions IMEDIA:false, CWMLITE:false, SAMPLE_SCHEMA:false, ORACLE_TEXT:false, APEX:false, OMS:false, DV:false, JSERVER:false, SPATIAL:false

If any of the options is required after the database has been created, let’s say you bought the license, you can simply look up on how to install them from Oracle Docs public documentation.

Hope it helps, cheers!

DO NOT uninstall Database Vault from a CDB!

Hi all,

This is just a note for you that are now fully into database upgrades. As you may know, the Database Vault (DV) option could be reported as not installed in source when converting non-CDB to PDB, and the target CDB has the option installed. However, be aware that is not supported to remove DV option in multitenant (singletenant included) environments.

From Doc ID 2380950.1:

IMPORTANT:  Never run dvremov.sql on a container database (CDB) / Multitenant environment.  Doing so will cause serious problems when upgrading the PDBs.

 

In case you run dvremov.sql in a 12.1 multi-tenant DB, then the only option you might have is to restore the database to the state before running dvremov.sql. Assuming you have this backup, of course.

So stay sharp! 😀

Some references:
How To uninstall / install Database Vault in a 12c database ? (Doc ID 2112167.1)
DBA_DV_STATUS Is INVALID (Due to “ORA-00942: Table Or View Does Not Exist”) (Doc ID 2380950.1)

OEM Real-Time Data not Displayed

Hi all,
So I was facing the follwoing situation in a client:
– SYS as SYSDBA passoword working to connect on database.
– Connecting to targets with this credentials working fine.
– However when loading real-time graphs it was simply not working.
– Tried to create manually the named credentials, still not working now with the error:

Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of the service requested in connect descriptor.

See what I was getting from database home:

NewScreenshot 2019-05-09 às 11.34.33.png

Weird, but hey, the solution was already posted in Grepora!

Ok, I discovered anyway from MOS BUG 28513706 – 13c2EM: DB home page shows Connection Cache with this Cache Name does not exist. it miht be related to connection pools.
Even thought this was closed as not a bug, a learned a new workaround for it:

1. [OMS_HOME]/bin/emctl set property -name use_pooled_target_connections -value false 

2. Restart oms 

[OMS_HOME]/bin/emctl stop oms -all 

[OMS_HOME]/bin/emctl start oms

Hope it helps!
Cheers!

Oracle New Visual Online Tools

Hi all,
So, after a long time of same old documentation style, Oracle started sharing some nice and visual pages for some specifics. Here are 2 examples:

1. Oracle Move to Cloud  (http://www.oracle.com/goto/move)
This one was shown to me by Ricardo Gonzalez, and it’s a GREAT tool if you are planning migrations to Oracle Cloud in general.

The part I like most is where you select the source and destination formats/versions you want and all recommended migration methods are suggested. Of course additional validation on database are required for each method, but it can potentially remind you of things you may be forgetting.

NewScreenshot 2019-05-08 às 16.56.46

Something else I like very much as well is the section with executive summaries of each migration tool. It’s basically ready for a print screen to put in the slides you are going to present to your boss… 🙂

NewScreenshot 2019-05-08 às 16.57.16.png

Also, you have handy some links to papers for the most different migration scenarios, as per:

NewScreenshot 2019-05-08 às 16.57.24

2. Oracle Database Features: (https://apex.oracle.com/database-features/)
I actually got it from this post by Portilho, and found it great! See how it looks like:

NewScreenshot 2019-05-08 às 16.52.14

And by selecting any of the features you can see more info in a very simple way, as per below. By the way, with one click you can be directed to the documentation (the old fashioned one):

NewScreenshot 2019-05-08 às 16.55.08

 

Hope you enjoy those as much as I did and hope Oracle continue providing those sort of interfaces to us!

Cheers!

AWR Scripts List

Hey,
You also always need to lookup for the right AWR Script to run, just like me?

Here is a quick summary that may help you (and myself) in future. 🙂

@?/rdbms/admin/awrrpt.sql      --> basic AWR report
@?/rdbms/admin/awrsqrpt.sql    --> Standard SQL statement Report
@?/rdbms/admin/awrddrpt.sql    --> Period diff on current instance
@?/rdbms/admin/awrrpti.sql     --> Workload Repository Report Instance (RAC)
@?/rdbms/admin/awrgrpt.sql     --> AWR Global Report (RAC)
@?/rdbms/admin/awrgdrpt.sql    --> AWR Global Diff Report (RAC)
@?/rdbms/admin/awrinfo.sql     --> Script to output general AWR information

Hope it helps!
Cheers!

Everything Stopped on ORA-02002!

Hi all,
So, I got contacted by a client in emergency because no more sessions were being opened on database with error ORA-02002. Even worse, everything stopped working and started raising this error.

Unusual, right?

So , checking on this trace generated for error here is what I found:

ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace AUDIT_TBS

Seems client has moved the AUD$ to another tablespace to avoid filling SYSTEM, just like described in this post. The tablespace got full, however, due bad monitoring, it got full.

As the auditing facility was unable to write to the AUDIT_TRAIL table, SQL statements that were currently being audited also failed.

What to do?

  1. Turn off auditing for the database
  2. Restart database
  3. Add space to tablespace
  4. Re-enable auditing, if required.

To avoid application back and forth, I did it in restrict mode, until get all fixed.

To avoid this sort of issue:

  • Be sure you are monitoring properly the new tablespace.
  • Place the audit tablespace on a reliable disk location and perhaps mirrored for protection.

Hope it helps,
Cheers!

12.2 Scheduler Job Disappeared After Creation

Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.

I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.

Here is a test case that reproduces the problem:

In a 12.2 database:

begin 
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN null; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND',
    end_date        => NULL,
    enabled         => false,
    comments        => 'Test job');
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- Job should appear here

begin 
  dbms_scheduler.enable('TEST_JOB'); 
  dbms_scheduler.run_job('TEST_JOB',false); 
end;
/
 
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';

-- There is no job now in 12.2.

You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.

Hope it helps!
Cheers!