Monday May 18, 2015

Create a PDB directly from a stand-alone database?

The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant:

Remote Cloning with the NON$CDB option.

If you'll read the documentation it doesn't say much about this option, neither the requirements nor the exact syntax or an example:
http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

Scroll down to the FROM clause:

... FROM NON$CDB@dblink ... this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let's try it.

Test 1 - Try to plugin an Oracle 11.2.0.4 database

Well, the documentation doesn't say anywhere anything about source release limitans. So I tried it simply with an Oracle 11.2.0.4 database. 

  1. Created a database link from my existing CDB pointing into my 11.2.0.4 database
  2. Started my SOURCEDB in read-only mode
  3. Tried to create a pluggable database from my SOURCEDB - and failed ...
    SQL> create pluggable database PDB1 from non$cdb@sourcedb;
    create pluggable database PDB1 from non$cdb@sourcedb
                                                 *
    ERROR at line 1:
    ORA-17627: ORA-28002: the password will expire within 7 days
    ORA-17629: Cannot connect to the remote database server

Test 2 - Try to plugin an Oracle 12.1.0.2 database in file system 

Thanks to Tim Hall - his blog post did the magic trick for me:
http://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1.php#cloning-remote-non-cdb

First of all, the reason why my Test 1 failed is simply that I can't have a user in an Oracle 11.2.0.4 database with the privilege CREATE PLUGGABLE DATABASE - but this is a requirement as I learned later on.

  1. You'll need a user in SOURCEDB with the privilege to CREATE PLUGGABLE DATABSE:
    GRANT CREATE PLUGGABLE DATABASE TO sourcedb_user;
  2. Start SOURCEDB in read-only mode after shutting it down:
    SHUTDOWN IMMEDIATE
    STARTUP OPEN READ ONLY;
  3. Create a database link pointing from the CDB back into the SOURCEDB:
    CREATE DATABASE LINK sourcedblink
    CONNECT TO sourcedb_user IDENTIFIED BY password USING 'upgr12';
  4. Now create the pluggable database from the stand-alone UPGR12 database:
    CREATE PLUGGABLE DATABASE pdb_upgr12 FROM NON$CDB@sourcedblink
    FILE_NAME_CONVERT=('/oradata/UPGR12','/oradata/CDB2/pdb_upgr12');
  5. But when you check the status of the new PDB you'll realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:
    ALTER SESSION SET CONTAINER=pdb_upgr12;
    @?/rdbms/admin/noncdb_to_pdb.sql

Summary

What will you get from this command? Actually it will allow a simple way to plug in a stand-alone database into a container database but the following restrictions apply:

  • Source database must be at least Oracle 12.1.0.1
  • Source database must be on the same OS platform
  • Source database must be at the same (equal) version as the container database
  • Script noncdb_to_pdb.sql needs to be run

You may have a look at this MOS Note:1928653.1 Example for Cloning PDB from NON-CDB via Dblink as well [Thanks Krishnakumar for pointing me to this note].

Finally the only simplification seems to be to avoid the extra step of creating the XML manifest file with DBMS_PDB.DESCRIBE - but apart from that I can't see many other benefits - except for easing of remote cloning with the above restrictions.

--Mike 

,, 

Friday Aug 22, 2014

Automatic Maintenance Jobs in every PDB?
New SPM Evolve Advisor Task in Oracle 12.1.0.2

A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:

"According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB - but the slides are not clear here. So what is the truth?"

Ok, that's good question. In my understanding all tasks will get executed within each PDB - that's why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and guess what will happen when 25 PDBs start gathering object statistics at the same time ...

The documentation indeed says:

Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged. A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement.

This sounds reasonable. But when we have a look into our PDBs or into the CDB_AUTOTASK_CLIENT view the result is different from what the doc says. In my environment I did create just two fresh empty PDBs (CON_ID 3 and 4):

SQL> select client_name, status, con_id from cdb_autotask_client;

CLIENT_NAME                           STATUS         CON_ID
------------------------------------- ---------- ----------
auto optimizer stats collection       ENABLED             1
sql tuning advisor                    ENABLED             1
auto space advisor                    ENABLED             1
auto optimizer stats collection       ENABLED             4
sql tuning advisor                    ENABLED             4
auto space advisor                    ENABLED             4
auto optimizer stats collection       ENABLED             3
sql tuning advisor                    ENABLED             3
auto space advisor                    ENABLED             3

9 rows selected.

I haven't verified the reason why this is different from the docs but it may have been related to one change in Oracle Database 12.1.0.2: The new SPM Evolve Advisor Task ( SYS_AUTO_SPM_EVOLVE_TASK) for automatic plan evolution for SQL Plan Management. This new task doesn't appear as a stand-alone job (client) in the maintenance window but runs as a sub-entity of the Automatic SQL Tuning Advisor task. And (I'm just guessing) this may be one of the reasons why every PDB will have to have its own Automatic SQL Tuning Advisor task 

Here you'll find more information about how to enable, disable and configure the new Oracle 12.1.0.2 SPM Evolve Advisor Task:

-Mike

Tuesday Aug 05, 2014

Upgrade PDBs - One at a Time (unplug/plug)

*** I have added an important change on May 26, 2015 ***
***      Please see below marked in YELLOW           *** 
********************************************************

Basically there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will refer to the "One at a Time" approach and describe the steps. During some presentations, discussions etc people were left with the impression that it will be a very simple approach to unplug one or many PDBs from a CDB in lets say Oracle 12.1.0.1 and plug it into an Oracle 12.1.0.2 Container Database. Bingo, upgraded!

Well, unfortunately this is not true. In fact it is completely wrong.


If you want to upgrade via unplug/plug the following steps will have to be followed:

  • In CDB1 environment - e.g. Oracle 12.1.0.1 with an PDB1
    • In SQL*Plus: 
      • alter session set container=PDB1;
      • @$ORACLE_HOME_12102/rdbms/admin/preupgrd.sql
        (The output of the preupgrade.log will show you the location of the fixups)
      • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql
        (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)
      • exec dbms_stats.gather_dictionary_stats;
        (plus include all additional treatments recommended by the preupgrade.log)
      • alter session set container=CDB$ROOT; 
      • alter pluggable database PDB1 close;
      • alter pluggable database PDB1 unplug into '/stage/pdb1.xml';
      • drop pluggable database PDB1 keep datafiles;
        The reason why you will need to DROP the PDB afterwards is simply to cleanup leftovers in the CDB views. It is under observation if this is a bug or not. The information does not get removed to allow quick plugin again but the leftovers may cause plenty of trouble once you'll try to upgrade this CDB1 later on.
      • exit
        .
  • In CDB2 environment - e.g. Oracle 12.1.0.2
    • In SQL*Plus:
      • alter session set container=CDB$ROOT;
      • At this point we "could" do a Plug In Check but as the COMPATIBLE of the new CDB2 created as per recommendation with DBCA defaults to "12.1.0.2" the Plug In Check will result in "NO" - but obviously the plugin operation will work. Just for the records here's the procedure to check plugin compatibility
        • SET SERVEROUTPUT ON
          DECLARE
            compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
            pdb_descr_file => '/stage/pdb1.xml',
            pdb_name => 'PDB1')
            WHEN TRUE THEN 'YES' ELSE 'NO'
          END;
          BEGIN
          DBMS_OUTPUT.PUT_LINE(compatible);
          END;
          /

          .
          select message, status from pdb_plug_in_violations where type like '%ERR%';
          .
      • create pluggable database pdb1 using '/stage/pdb1.xml' file_name_convert=('/oradata/CDB1/pdb1', '/oradata/CDB2/pdb1');
      • alter pluggable database PDB1 open upgrade;
      • exit
    • On the command prompt:
      • cd $ORACLE_HOME/rdbms/admin 
      • $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /home/oracle/upgrade catupgrd.sql
    • Back into SQL*Plus:
      • alter session set container=pdb1;
      • startup
      • @?/rdbms/admin/utlrp.sql
      • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups.sql
        (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)
Of course this technique will work also with more than one PDB at a given time. You'll have to repeat the steps, and your upgrade call on the command line will look like this:

      • $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1, PDB2" -l /home/oracle/upgrade catupgrd.sql

Well, not really unplug+plug=upgraded ;-)

-Mike 

PS: I did add a few pieces of information based on the excellent feedback given to me by Frank Kobylanski from the MAA Team - cheers, Frank!!! 

Tuesday Dec 03, 2013

Starting up 252 PDBs automatically?

In my recent posting I have explained the startup of many PDBs at the same time.

But once you startup the container database CDB$ROOT the PDBs will stay in MOUNT status. So how do you start them during CDB$ROOT startup (or immediately afterwards) in an automatic fashion?

A startup trigger will do this job.

CREATE OR REPLACE TRIGGER startup_all_pdbs
AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END;

/

And of course you can use the EXCEPT command option to exclude one or more PDBs from the automatic startup.

CREATE OR REPLACE TRIGGER startup_all_pdbs_except_a_few
AFTER STARTUP ON DATABASE

BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN EXCEPT PDB100, PDB101';
END;
/

How does this work in an Oracle Real Application Clusters environment?
In an RAC environment you won't need the startup trigger as clusterware takes over this role of ensuring the automatic startup of a PDB on designated nodes within the CDB$ROOT's instances.

srvctl add service -db rac -service pdbrac_srv -pdb pdbrac -preferred "rac1,rac2"

A snipet from the crsctl status output will look like this:

   crsctl status resource -t
    :
   ora.rac.db
         1    ONLINE  ONLINE   rac-server01       Open,STABLE
         2    ONLINE  ONLINE   rac-server02       Open,STABLE
   ora.rac.pdbrac_srv.svc
         1    ONLINE  ONLINE   rac-server01       STABLE
         2    ONLINE  ONLINE   rac-server02       STABLE
    :

-Mike

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
22
23
24
25
28
29
30
31
      
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers