At the end of Oracle Open World my last BLOG concerning OOW 2016 covering day 4 and 5:

Wednesday is the day of the Party: Oracle’s appreciation event, a concert with Gwen Stefani and Sting at the AT&T Park (Stadium of the San Francisco Baseball team, the Giants). It was a great event with awesome musicians.

Before the party I visited the session “Oracle Active Data Guard: Power, Speed, Ease and Protection” provided by Larry M. Carpenter, the grandfather of Data Guard. Here a couple of nice new features of (Active) Data Guard in 12gR2:

  • Multi-Instance Redo Apply in RAC: Up until now the managed recovery processes (MRP) could only run on a single node on the standby RAC site and hence limited redo apply to the CPU and IO power of that one node. In the new release a coordinator process can distribute the redo data to MPR-processes on all nodes of the RAC cluster. This is called Multi-Instance Apply and is configured as follows:
    Without broker: recover managed standby database disconnect using instances 4;
    With broker: Through the ‘ApplyInstances’ property.
    Caveats in the first 12gR2 release:
    Using Multi-Instance Redo Apply disallows the use of the new feature In-Memory Column Store on Active Data Guard.
    RMAN block change tracking file is disabled.

 

  • Data Guard Broker enhancement for Multitenant: As Redo is generated at Container (CDB) level, switchover and failover will also happen for the whole CDB. In 12gR2 there is a new command when using the Data Guard Broker to migrate or failover a Pluggable DB (PDB) to another CDB on the same server:

    MIGRATE PLUGGABLE DATABASE PDBx TO CONTAINER CDB2 USING PDBx.xml CONNECT AS sys/mypassword@CDB2;

    Depending on what role the CDB I’m connected to has determines if a PDB is migrated to another CDB or is failed over to another CDB. So if e.g. a PDB has a failure on the primary site then I can “failover” its standby-equivalent to another Primary CDB on the standby machine and hence make the “standby PDB” a “primary PDB”. This works best when having 2 CDBs in 2 sites and replicate in opposite directions: CDB1 at site A replicates to CDB1 at site B. CDB2 at site B replicates to CDB2 at site A. So let’s assume PDBx in Primary DB CDB1 fails at site A. You can then migrate PDBx at site B to Primary DB CDB2. PDBx at site A in CDB1 will be dropped automatically, but the CDB2 at site A needs to be manually updated with the new data files of PDBx.

 

  • Use In-Memory Column Store on an Active Data Guard DB: As mentioned in my previous BLOG, in 12cR2 In-Memory can be used on an Active Data Guard Instance.
    Restrictions for In-Memory on Active Data Guard:
    In-Memory expressions are captured based on queries executed on the primary only. I.e. the expression statistics store (ESS) is maintained on the primary only.
    Automatic Data Optimization (ADO) policies are triggered only on access recorded on the primary database.
    In-Memory Fast-Start and In-Memory Join-Groups are not supported in an Active Data Guard

 

  • Diagnostics and Tuning for Active Data Guard: The Diagnostics Pack (AWR), the Tuning Pack features and SQL Plan Analyzer are supported in the new release on Active Data Guard.
    AWR: In an AWR catalog database the Active Data Guard DB is registered. From there remote snapshots can be taken from the Active Data Guard instance and stored in the AWR catalog: dbms_workload_repository.create_remote_snapshot("TYPICAL", ADG-id);
    SQL Tuning Advisor: All SQL Tuning Advisor Tasks are executed on the Active Data Guard instance. Necessary write activity are done through a DB-Link on the primary DB.

 

  • Repair blocks from NOLOGGING-operations: Blocks from NOLOGGING operations on primary can now be validated and repaired on Standby with rman commands:

    validate/recover ... nonlogged blocks;

    I.e. the primary DB does not necessarily need to be in FORCE LOGGING mode anymore. If NOLOGGING operations are necessary then they can be repaired on the Standby-DB. Previously complete datafiles had to be restored to repair NOLOGGING operations.

On Thursday I visited the panel discussion with the subject “Thinking clearly about Database Application Architecture”. Toon Koppelaars, Connor Mcdonald, Cary Milsap and Gerald Venzl discussed about the correct Application architecture when accessing data in an Oracle Database. The discussion was moderated by Bryn Llewellyn. Toon Koppelaars from the Real World Performance team at Oracle explained why the ThickDB approach by writing business logic (which need data processing) in PLSQL through set or bulk processing is the best method to have a well performing application (see also here). However, today the approach to process the data in layers outside the DB is being preferred (“data to processing” instead of “processing to data”). Unfortunately that results in row by row processing with lots of network roundtrips and higher CPU-usage on the DB-server due to the many times the whole stack on the DB-server has to be traversed.
It was clear and agreed in the audience that the ThickDB approach (“processing to data”) is correct, but why do developers not change their behavior since many years? The opinions on that differed, but also critical statements were expressed that “we as DBAs and DB-Consultants are part of the problem”, because there is no effort to change something in the base education of students to better understand the inner workings of a relational database system and the importance of “processing at the data”.

I’ll leave it to the reader to think about that and end my BLOGs about the Oracle Openworld 2016.