Is “imp” still supported in Oracle Database 12.2?

exp imp

Good question we receive quite often:

Is “imp” still supported in Oracle Database 12.2?

For clarification: I don’t talk about “impdp” but about old “imp” (import). And yes, it is still supported in Oracle 12.2.

The oldexp” got desupported with Oracle Database 11.1.0.6 – but “imp” is still supported, even in Oracle Database 12.2.0.x. You should be able to import your old dumps into the newest release of the database and migrate even VERY old releases directly to Oracle Database 12.2.

Actually, the biggest step upwards we’ve heard about a year ago was an exp from Oracle V5 and migration into Oracle 12.1.0.2 Single Tenant. No joke – and it worked!

Only real pitfall we are aware of right now: The MOS Note 132904.1 (Compatibility Matrix for Export And Import Between Different Oracle Versions) hasn’t been updated recently for whatever reason.

–Mike

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1:

ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.pdb';

The nice thing with this command differing in the file ending of ‘pdb‘ instead of ‘xml as you used it in Oracle 12.1 (and the ‘xml‘ option is still available of course): Instead of just creating an xml description file it zips everything together into a PDB archive.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
SQL> create pluggable database PDB1 admin user adm identified by adm
  2  file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> alter pluggable database pdb1 close;
Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb';
Pluggable database altered.

Now lets have a quick look into the created file:

-rw-r--r--. 1 oracle dba   108414 May 19 16:20 pdb1.pdb
$ unzip pdb1.pdb 
Archive:  pdb1.pdb
  inflating: system01.dbf            
  inflating: sysaux01.dbf            
  inflating: undotbs01.dbf           
warning:  stripped absolute path spec from /home/oracle/pdb1.xml
  inflating: home/oracle/pdb1.xml    

Not bad. Actually pretty cool.

Of course the same command fails in Oracle Database 12.1.0.2:

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb';
alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb'
                                          *
ERROR at line 1:
ORA-65125: valid XML file name is required

Unfortunately there’s a significant flaw when your PDB is stored in ASM:
The zip file does not contain the datafiles of your PDB.

Please see:

It is supposed to be fixed with patch set 12.2.0.2.

A really handy feature – but don’t use it when your PDBs are located in ASM.

–Mike

Unified Auditing – is it ON or OFF in Oracle Database 12.2.0.1?

Just a quick update to my blog post from September 2014:

Unified Auditing – is it ON or OFF in Oracle 12c?

Any changes in Oracle Database 12.2.0.1?

Yes. Significant changes happen to Unified Auditing in Oracle Database 12.2.0.1. Mostly internally as the mechanism used to dump the audit records in Oracle 12.1.0.x when Unified Auditing was on turned out to be very good for write performance, but not so good when you tried to read data. See my blog post from a few weeks ago how to deal with this performance implication and a potential patch:

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

But I’d like to understand if the so called “Mixed Mode” is still existent in Oracle Database 12.2.0.1, meaning you can have the old audit trail enabled but the database is still auditing some activities via the newer Unified Auditing policies.

Comparison Oracle 12.1.0.2 vs Oracle 12.2.0.1

Quick check in Oracle 12.1.0.2:

SQL> column policy_name format a25
SQL> column user_name format a14
SQL> column enabled format a7
SQL> set line 200
SQL> set pages 1000
SQL> SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;

POLICY_NAME		  ENABLED_ USER_NAME
------------------------- -------- --------------
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

And now the same query in Oracle 12.2.0.1:

SQL> column policy_name format a25
SQL> column user_name format a14
SQL> column enabled format a7
SQL> set line 200
SQL> set pages 0
SQL> SELECT policy_name, enabled_opt, user_name
  FROM audit_unified_enabled_policies  2  ;
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

SQL> set pages 1000
SQL> r
  1  SELECT policy_name, enabled_opt, user_name
  2*   FROM audit_unified_enabled_policies

POLICY_NAME		  ENABLED_ USER_NAME
------------------------- -------- --------------
ORA_SECURECONFIG	  BY	   ALL USERS
ORA_LOGON_FAILURES	  BY	   ALL USERS

No change.

As in Oracle 12.1.0.x, in Oracle Database 12.2.0.1 two default Unified Auditing Policies are enabled. And still I’d recommend to turn them off if you are either going to use the old auditing via audit_trail or don’t want to have auditing at all.

SQL> noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.

SQL> noaudit policy ORA_LOGON_FAILURES;
Noaudit succeeded.

SQL> SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;
no rows selected

Again, to be clear, I’m not saying that you shouldn’t use the new Unified Auditing. But disable the Mixed Mode. Use the real (and enabled, i.e. linked into your kernel) Unified Auditing instead if you would like to audit in Oracle Database 12.2.0.1. Or stay with the old auditing if it does what you want and expect.

–Mike

SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago:

New SPFILE parameters in Oracle Database 12.2.0.1

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

  • You must set the SGA_MIN_SIZE value to a value that meets these requirements:
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
    • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

    When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
  
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

SET LINESIZE 150
COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            c.pdb_name,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4

–Mike

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default Value Change in Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

“If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.”

And please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

What happened in Oracle Database 12.1.0.2 when you set PGA_AGGREGATE_LIMIT too low?

How much is too low? Too low means it has been set lower that 2x the value of PGA_AGGREGATE_TARGET. And then it got adjusted “silently”, i.e. the value of PGA_AGGREGATE_LIMIT got adjusted internally after startup to reflect the minimum. Unfortunately this adjusted value does not get displayed by “show parameter”.

Short example in Oracle Database 12.1.0.2:

SQL> show parameter pga 

NAME			TYPE                      VALUE
----------------------- ------------------------- -----------------------------
pga_aggregate_limit     big integer               2G
pga_aggregate_target    big integer               120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

After restarting the database:

SQL> show parameter pga

NAME				     TYPE	  VALUE
------------------------------------ ------------ ------------------------------
pga_aggregate_limit		     big integer  1G
pga_aggregate_target		     big integer  1G
Different behavior in Oracle Database 12.2.0.1

Repeating the same test in Oracle Database 12.2.0.1 leads to ORA-93:

SQL> show parameter pga 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

SQL> create pfile from spfile;
File created.

SQL> startup force
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
ORA-01078: failure in processing system parameters

After increasing PGA_AGGREGATE_LIMIT=2G I can startup my database again. PGA_AGGREGATE_LIMIT gets enforced as at least 2x the value of PGA_AGGREGATE_TARGET since Oracle Database 12.2.0.1.

–Mike

Download Oracle Database 12.1.0.2 or older versions from the Oracle Software Cloud

I did blog a few weeks back about the download of older versions of the Oracle Database as OTN offers you only the most recent ones.

In case you miss the downloads of Oracle Database 12.1.0.2 on OTN

Looked pretty simply, huh?

Well, here’s quick “how to” to access older Oracle Software downloads from eDelivery aka Oracle Software Cloud.

  1. Login to https://edelivery.oracle.com/, the Oracle Software Cloud
  2. Start typing “Oracle Database Enter” into the Search by field. It will display you the option below – mark it with a click:

    Search - eDelivery - Oracle

    Search Box in the Oracle Software Cloud

  3. Then use the Select Platform drop down list to chose your desired operating system, then hit Select:

    eDelivery - Oracle - Platform Selection

    Oracle Software Cloud – Select Platform

  4. In the displayed Download Queue hit now Continue on the right side:

    eDelivery - Download Queue

    Oracle Software Cloud – Download Queue – Continue

  5. And now the magic has to happen – click on tiny little Select Alternate Release … option directly under “Oracle Database Enterprise Edition” as by default only the newest release will be displayed:

    Alternate Release Selection - Edelivery

    Click on Select Alternate Release …

  6. And then, finally, you get a drop-down list offering you to choose older releases as well:
    Drop Down List - alternate releases - edelivery

    Use the Drop-Down List to choose from all available releases

     

  7. You may use the SAVE LINK AS option of your browser to download whatever you need as the download manager may not work for your as it wants to install something.

    Download Links

    Download Software with SAVE LINK AS option of your browser

If you’d like to get access to even older releases you may please open an SR with Oracle Support and request access.

–Mike

Install components in Multitenant ALWAYS with catcon.pl

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.

How to install a component afterwards in Single-/Multitenant?

I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is catcon.pl, the perl driver meant to execute database scripts not only in the CDB$ROOT but also in the PDB$SEED and in all opened PDBs.

If you don’t use catcon.pl for script executions then you may get something in CDB$ROOT but not in the PDB$SEED and therefore not in any future PDB.

Example: Create JVM after database creation

Creating JVM is pretty well handled and described in MOS Note:1612279.1. With one exception: It does not talk about what to do in a Multitenant environment. I learned about this as one of my colleagues ran the steps described in the note from SQL*Plus connected to the CDB$ROOT – and recognized that JVM did not get created in the existing PDB(s).

The MOS Note:1612279.1 proposes the following steps:

-- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst.sql

But if you’d execute it you’ll get the Java components only in the CDB$ROOT – but not in the PDB$SEED: And as the PDB$SEED is untouchable for users you can’t simply force it open and run the script again.

Install JVM correctly in a Single-/Multitenant environment

Assuming that the above script does create everything correctly it needs to be transformed to deal with a Single-/Multitenant environment by using catcon.pl.:

-- Start of File full_jvminst_mike.sql
spool full_jvminst.log;
set echo on
alter pluggable database all open;
alter system set "_system_trig_enabled" = false scope=memory;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst_mike.sql

By the way, the (still) undocumented “-n 1” option simply does start only one worker and creates only one logfile. It is usually used to execute database standard scripts.

Testing it in my environment

Of course I did execute the script in my environment – an Oracle Database 12.1.0.2 container database:

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:03:36 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2932336 bytes
Variable Size             369099152 bytes
Database Buffers          671088640 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2          PDB$SEED                       READ ONLY  NO

SQL> create pluggable database PDB1
     admin user adm identified by adm
     file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb1');
Pluggable database created.

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2          PDB$SEED                       READ ONLY  NO
3          PDB1                           READ WRITE NO

SQL> @full_jvminst_mike.sql

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> alter system set "_system_trig_enabled" = false scope=memory;
System altered.

SQL> select obj#, name from obj$
     where type#=28 or type#=29 or type#=30 or namespace=32;
no rows selected

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initjvm_catcon_13696.lst
catcon: See /home/oracle/initjvm*.log files for output generated by scripts
catcon: See /home/oracle/initjvm_*.lst files for spool files, if any
catcon.pl: completed successfully

SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
832        JAVA RESOURCE
25893      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initxml_catcon_15160.lst
catcon: See /home/oracle/initxml*.log files for output generated by scripts
catcon: See /home/oracle/initxml_*.lst files for spool files, if any
catcon.pl: completed successfully

SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
908        JAVA RESOURCE
27097      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql;
catcon: ALL catcon-related output will be written to /home/oracle/xmlja_catcon_16210.lst
catcon: See /home/oracle/xmlja*.log files for output generated by scripts
catcon: See /home/oracle/xmlja_*.lst files for spool files, if any
catcon.pl: completed successfully


SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
908        JAVA RESOURCE
27097      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catjava_catcon_16273.lst
catcon: See /home/oracle/catjava*.log files for output generated by scripts
catcon: See /home/oracle/catjava_*.lst files for spool files, if any
catcon.pl: completed successfully


SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
911        JAVA RESOURCE
27473      JAVA CLASS
2          JAVA SOURCE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> set echo off
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[CDB1] oracle@localhost:/u01/app/oracle/product/12.1.0.2/rdbms/admin
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:28:53 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2932336 bytes
Variable Size             369099152 bytes
Database Buffers          671088640 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2          PDB$SEED                       READ ONLY  NO
3          PDB1                           READ WRITE NO
SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

SQL> alter session set container=pdb1;
Session altered.

SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

So it looks good – the required components got all created within each container. But will the component be there as well when I provision a new pluggable database?

Provisioning a new pluggable database

SQL> alter session set container=cdb$root;
Session altered.

SQL> create pluggable database PDB2
     admin user adm identified by adm
     file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb2');
Pluggable database created.

SQL> alter session set container=pdb2;
Session altered.

SQL> startup
Pluggable Database opened.

SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

SQL>

This looks good as well.

Summary

It’s important to change habits when dealing with Single Tenant or Multitenant environments. You must ensure to execute scripts with catcon.pl in order to ensure that those will be executed in all containers, not only in the CDB$ROOT. Of course it is hard to find the right balance between either too many components or the need to recreate components here and there. Especially in a Multitenant environment it is key that you don’t alter component sets to heavily as otherwise you’ll see yourself ending up in situations where you want to unplug the above PDB1 and plug it in somewhere else – but if the destination’s CDB has no JVM installed you can’t really plugin.

–Mike

Release Dates Oracle Database 12.2.0.1 on-prem – Extended Support Waiving for Oracle 11.2.0.4 / 12.1.0.2

Yesterday night the most important MOS Note:742060.1 got updated with the planned release date for Oracle Database 12.2.0.1 on-premises.

In addition, the dates for Waived Extended Support for Oracle Database 11.2.0.4 and 12.1.0.2 got extended as well.

Please see:

In summary:

  • Oracle Database 12.2.0.1 for Exadata and SuperCluster is supposed to be released soon.
  • Oracle Database 12.2.0.1 for Intel Linux x86 and Solaris platforms (SPARC and Intel) is supposed to be released soon after the Exadata/SuperCluster releaseFOR EXACT DATES PLEASE SEE MOS Note: 742060.1.
  • The Free (Waived) Extended Support for Oracle Database 11.2.0.4 will be extended until end of December 2018 (was end of May 2017 before)
  • The first year of Extended Support for Oracle Database 12.1.0.2 will be waived (free) as well until end of July 2019
    .

Oracle 12.2 12.1 11.2.0.4 Release Map

For further details please see the MOS Note:742060.1.

–Mike

Default Changes SPFILE Parameters – Oracle 12.2

This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Roy and I did a comparison between default parameter settings in Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 vs Oracle Database 12.2.0.1. And some changes are quite interesting – of course the memory driven parameters are left out in this list.

Color RED marks a change between releases.
The databases were all on OL (Oracle Linux) 6.8 and located on file system, not in ASM – therefore certain values may differ when the database is located in ASM and/or on a different OS port.

Parameter Oracle 11.2.0.4 Oracle 12.1.0.2 Oracle. 12.2.0.1
audit_sys_operations FALSE TRUE TRUE
compatible 11.2.0.4 12.1.0.2.0 12.2.0
control_file_record_keep_time 7 7 30
db_securefile PERMITTED PREFERRED PREFERRED
dml_locks 616 1416 2076
filesystemio_options NONE NONE setall
job_queue_processes 1000 1000 4000
object_cache_optimal_size 102400 102400 10240000
optimizer_features_enable 11.2.0.4 12.1.0.2 12.2.0.1
parallel_max_servers 48 80 80
parallel_min_servers 0 8 8
parallel_servers_target 64 32 32
parallel_adaptive_multi_user TRUE TRUE FALSE
pre_page_sga FALSE TRUE TRUE
resource_limit FALSE TRUE TRUE
sec_max_failed_login_attempts 10 3 4
sec_protocol_error_trace_action CONTINUE TRACE LOG
spatial_vector_acceleration FALSE FALSE TRUE
sql92_security FALSE FALSE TRUE

–Mike