Friday Feb 17, 2017

Client Certification for Oracle Database 12.1.0.2/12.2.0.1

I've received a question about client certification for Oracle Database 12.2.0.1 the other day.
And this reminded me on this very helpful MOS Note:

It spans the client interoperability back to the Oracle 9.2 days and is a very helpful resource.

Client Certification Oracle Database

And don't forget that sometimes client software may need a patch as well. A customer I exchange emails with on a regular basis just upgraded the Database server to Oracle Database 12.1.0.2 and saw several strange errors with the JDBC client despite the fact that they were already using the most recent JDBC client.

ORA-904, ORA-923 and ORA-920 were signaled together with:
oracle.jdbc.driver.OracleParameterMetaDataParser.computeBasicInfo(OracleParameterMetaDataParser.java:277)

It turned out that the JDBC client need some patch treatment with merge patch 21623553 on top of the 12.1.0.2. client. It got combined together with some other useful fixes into merge patch 24012252 for the JDBC client:

JDBC Patch for Bug# 24012252 for Generic Platform 

Afterwards the errors were gone. For further JDBC client patch recommendations please see:

Furthermore don't forget that there's the Instant Client as well which is easy to deploy and downloadable from OTN:

Oracle Instant Client Download OTN

--Mike
.

Monday Feb 13, 2017

Oracle Database 12.2 for Exadata/SuperCluster available

Oracle Database 12.2.0.1 on-premises for Exadata and SuperCluster is now available for download from Oracle eDelivery/SoftwareCloud.

Connect to eDelivery and login:

Search for:

"Oracle Database" and mark "Oracle Database Enterprise Edition ..."


.

Click on "Select Platforms" and select "Linux x86-64", then hit "Continue":


.

Once you accepted the license agreement you'll be able to download the software:


.

Please be aware that the software can be installed on Oracle Exadata and Oracle SuperCluster ONLY. To take advantage of the full potential of Oracle Database 12c Release 2 you should upgrade to the latest Exadata Software Release 12.2.1.1.0 before upgrading databases to 12c Release 2. Exadata Software 12.2.1.1.0 supports Smart Scan functionality for Oracle Database 12c Release 2 and is available via OSDC.

For the release of Oracle Database 12.2.0.1 on-premises for other platforms please see MOS Note:742060.1.
.

--Mike
,

Thursday Feb 09, 2017

The OJVM Patching Saga - and how to solve it - Part IV

Related Posts on
"The OJVM Patching Saga - and how to solve it":


What is missing with the "Mitigation Patch?

Michael, the team lead from a large Oracle customer mailed me the other day. He wanted to disable Java on all their +600 databases by using the Mitigation Patch for OJVM. But he received a strange error when trying to enable the mitigation patch which is clearly included in the April 2016 PSU he is using on his Oracle 12.1.0.2 databases:

SQL>  exec dbms_java_dev.disable
BEGIN dbms_java_dev.disable; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_JAVA_DEV.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


MOS didn't reveal an immediate solution. And we've exchanged some emails detecting some issues with one-offs applied only on one cluster node. But actually that was not the root cause for the mitigation patch not allowing to disable java.

We did check if the SQL changes from the April 2016 PSU really had been applied with the script I published here:

But the results were ok - datapatch had been executed in the database:

SQL> SET LINESIZE 400
SQL> COLUMN action_time FORMAT A20
SQL> COLUMN action FORMAT A10
SQL> COLUMN status FORMAT A10
SQL> COLUMN description FORMAT A60
SQL> COLUMN version FORMAT A10
SQL> COLUMN bundle_series FORMAT A10

SQL> SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
  2         action,
  3         status,
  4         description,
  5         version,
  6         patch_id,
  7         bundle_series
  8  FROM   sys.dba_registry_sqlpatch
  9  ORDER by action_time;

ACTION_TIME          ACTION   STATUS  DESCRIPTION                                            VERSION  PATCH_ID BUN
-------------------- -------- ------- ------------------------------------------------------ -------- -------- ---

02-MAR-2016 08:59:35 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)      12.1.0.2 21359755 PSU

02-MAY-2016 09:44:45 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU

13-MAY-2016 12:52:01 ROLLBACK SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU

17-MAY-2016 11:16:56 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU


The Solution

Finally Michael found the solution.
This tiny little script didn't get run: dbmsjdev.sql

It only gets mentioned in the PSU's and BPs READMEs and easily can be overlooked.
Furthermore I couldn't find it mentioned in any other MOS note.

And I missed it on my previous blog post as well and added it now.

Once you executed dbmsjdev.sql then you can disable Java by using:

SQL> exec dbms_java_dev.disable
PL/SQL procedure successfully completed.


Further information

.
--Mike
.

Wednesday Feb 08, 2017

DBMS_QOPATCH does not work in PDBs (right now)

Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER.
.

DBMS_QOPATCH in Multitenant

DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well.
.

Testcase

I "borrowed" this test case from Jeannette's SR:

SQL> COLUMN NAME FORMAT A8
SQL>
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME     CON_ID DBID       CON_UID    GUID
-------- ------ ---------- ---------- ------------------
CDB$ROOT      1 3424772713 1          47C8525C0DFE49...
PDB$SEED      2 3983775695
3983775695 E6204BB1F6EB4F...
MYPDB1        3 7270044002
7270044002 B975668B860049...
MYPDB2        4 1943363979
1943363979 BCD7AAFAF3F641...

In a PDB:

ALTER SESSION SET container = myPDB;

Session altered.

SQL> select * from OPATCH_XML_INV ;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object OPATCH_LOG_DIR not found


no rows selected

SQL> select dbms_qopatch.get_opatch_install_info from dual;
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1986
ORA-06512: at "SYS.DBMS_QOPATCH", line 133


In the CDB:

SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<InventoryInstance>


SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-2d1c0910-36ac-429b-98db-96a353d423b6</UId><targetTyp



Solution

There's no solution available right now for Oracle Database 12.1.0.2. And this behavior does not seem to be documented yet. The SR resulted in an (unpublished) Enhancement Request. In a PDB the following workaround may help in Oracle Database 12.1.0.2:

select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;

But this is not as fancy and easy to deal with as an API call to a DBMS package.
.

I tested in Oracle Database 12.2.0.1 - and there everything seems to work fine there :-)

SQL> create pluggable database PDB3 admin user adm identified by adm file_name_convert=( '/u02/oradata/CDB2/pdbseed', '/u02/oradata/CDB2/pdb3');

Pluggable database created.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> alter session set container=pdb3;

Session altered.

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-3cb04a3a-3999-4767-86f1-bc845cab158e</UId><targetTyp

SQL>   select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Inv
entoryInstance> <ora

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
-----------------------------------------


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB3                           READ WRITE NO

.
Cheers
--Mike

Friday Feb 03, 2017

February 2017 version of PREUPGRD.SQL is available

The new version of the preupgrd.sql (including the utluppkg.sql package) in its February 2017 version is available for download from MOS Note:884522.1.

New Preupgrd.sql - MOS Note 884522.1

If you are going to upgrade to Oracle Database 12.1.0.2 please exchange your current preupgrd.sql/utluppkg.sql either in ?/rdbms/admin or in the location of the source system you've copied it to with the new version.
.

--Mike
.

Wednesday Feb 01, 2017

New SPFILE parameters in Oracle Database 12.1.0.2 with July 2016 (and newer) PSU/BP

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database 12.1.0.2 with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database 12.2.0.1 manual:

.
The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory on UNIX platforms.

The default value is FALSE, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (12.2.0.1), database shared memory was created with owner and group access.

When this parameter is set to TRUE, database shared memory is created with owner and group access. This behavior grants permissions to DBAs to manage shared memory outside the database, but also allows DBAs to read and write to shared memory, which may not be desirable for certain installations.

So there's a tiny correction required:
It should say "prior to Oracle Database 12.1.0.2 July 2016 PSU/BP".
.

The ENCRYPT_NEW_TABLESPACES parameter came in for the cloud deployments and is documented in the description of the July Proactive BP:

12.1.0.2.DBBP:160719 21281607E Transparently encrypt tablespace at creation in Cloud (adds "encrypt_new_tablespaces";)

 .

Why does ALLOW_GROUP_ACCESS_TO_SGA appear in Oracle Database 12.1.0.2?

Simple reasons: it will make Oracle Database 12.1.0.2 more secure. Default for this parameter is FALSE - which actually changes behavior but may not affect you at first sight. And that's why I blog about it.

You will recognize that the Oracle executable runs now with permission "600" - whereas it was "640" before. See my example of an 12.1.0.2 database with the January 2017 BP in place:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status     
0x00000000 23298063   oracle     600        2932736    76                     
0x00000000 23330832   oracle     600        1040187392 38                     
0x00000000 23363601   oracle     600        5455872    38                     
0xc8969114 23396370   oracle     600        20480      38      

...

whereas my 11.2.0.4 database runs with different permissions:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status    
0x00000000 22872084   oracle     640        12582912   21                     
0x00000000 22904853   oracle     640        721420288  21                     
0xd41b1c5c 22937622   oracle     640        2097152    21
...

.
Does this effect the connection of your applications to the database?
No, of course not.

It has only an effect if you try to access the SGA from the OS level, i.e. attaching to the shared memory segment. In the old behavior an OS user being in the same group can attach and read from the SGA. With the new "600" protection only the OWNER can attach to it - and read out the SGA.

This is the standard behavior in Oracle Database 12.2.0.1 and onward. And it has been backported to the July 2016 PSU and Proactive Bundle Patches which are cumulative, i.e it is in all following PSUs and BPs included as well.
.

--Mike
.



Monday Jan 30, 2017

Where is the Jan 2017 PSU for Oracle Database 11.2.0.4?

Where is it?

This question reached me a week ago:
"Where is the January 2017 PSU for Oracle Database 11.2.0.4?"

"What's the deal?" was my first thought - just go and download it from MOS.
Well, not really ...
.

Where to download Proactive Bundle Patches, Patch Set Updates and Patch Sets?

My favorite note is:
MOS Note:1454618.1
Quick Reference to Patch Numbers for PSU, SPU(CPU), BPs and
Patchsets

It gives me access to all the download links for the patch bundles I'm looking for.

But for 11.2.0.4. there's only "NA" (not available) stated:

And a very small asterisk "m":


.

Why is there no January 2017 PSU for Oracle Database 11.2.0.4?

The comment says it all - but I was looking for a more precise explanation. And Roy knew where to look at (thanks!):

  • MOS Note: 854428.1
    Patch Set Updates for Oracle Products

    .
    Section 3 (Patch Sets Updates Lifecycle) says:

    • PSUs will reach a plateau of non-security content as the version stabilizes. Once this is reached, there will be a tapering off of non-security content. Oracle expects this plateau to be reached with the third or fourth PSU.
    • PSU content will be primarily security-related once the next patch set in the series is released.

So yes, this is possible and somehow documented.
As there are no Security Fixes for Oracle Database 11.2.0.4 this quarter there are no SPUs or PSU released.
.

Further Information?

Please see the blog post of Rodrigo Jorge from Enkitec with more details:

Obrigado, Rodrigo!
.

--Mike
.




Friday Jan 27, 2017

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 release

    FOR 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
.

Wednesday Jan 25, 2017

Restarting a failed Database Upgrade with DBUA 12.2

In my previous blog post I did showcase how to restart a failed database upgrade on the command line before Oracle Database 12c, with Oracle Database 12.1.0.x and with the new Oracle Database 12.2.0.x:

Here I'd like to show the new capability of the Database Upgrade Assistant (DBUA) to restart an upgrade, a functionality the DBUA was missing until now. Please note that you can always go back to the command line, no matter in which version you have used the DBUA.

I won't describe an upgrade with the DBUA in this blog post as this is showcased in the documentation already:

Starting the database upgrade with DBUA

I will upgrade the UPGR database well known from our Hands-On Lab.

DBUA Database Selection Oracle 12.2

And please don't put in your credentials in the fields below - if you are logged in as the oracle user then this will lead to failure and drive you crazy ...

Another thing which puzzles me:
I still have to execute the olspreupgrade.sql script from the newer (in my case the 12.2) home by myself. I'd wish the DBUA would do this for me as well as I'll have to open an xterm, set my environment and type in a very long path name to point to the new 12.2 home in order to execute this script in my source environment.

DBUA Database Selection Oracle 12.2

Ok, let's kick off the upgrade:

Oracle 12.2 DBUA Database Upgrade

The progress bar is very imprecise in relation to the duration - you can ignore it more or less.

And - very sad - the Alert and Activity Monitor buttons disappeared - but they may reappear in a later release of the DBUA.
.

The Error Scenario

It's always fun to kill pmon finding out how cool this database is ;-) It survives the deadly attack :-) Of course it does ... it's the Oracle Database ;-)

kill -9 pmon

Bang!
.

The DBUA recognizes the failure

Even though the DBUA recognized the failure quite quickly it still tries to complete the upgrade - which of course results in a ton of errors. It just means that you'll have to wait until the DBUA has "progressed" the upgrade till the end - and DON'T CLOSE THE DBUA as otherwise you'll lose the ability to RETRY.

DBUA - upgrade failure

You'll see the error count going up until the DBUA has reached the "end" of the (failed) upgrade.

DBUA Oracle 12.2 - upgrade failure scenario 2

And then it displays the RETRY button:

DBUA Oracle 12.2 - upgrade failure scenario

.

Rerun the Database Upgrade

Once you hit RETRY the DBUA will try to solve the situation - and in my case it will start up my source database in STARTUP UPGRADE mode again - and then process the upgrade using the -R option of catctl.pl (described in the previous blog post)

DBUA Oracle 12.2 - upgrade failure scenario

You'll find also a new set of logfiles in $ORACLE_BASE/cfgtoollogs/dbua/ subdirectories indication with a number (here: 1) and an "R" that this is the first restart attempt's logs:

Logfiles from DBUA rerun 12.2

--Mike
.

Tuesday Jan 24, 2017

Restarting a failed Database Upgrade with catctl.pl

What if ...

What if a database upgrade fails in between? First of all you should have enabled one of the common protections for issues happening during an upgrade:

  • Guaranteed Restore Point to Flashback Database
  • Restore an RMAN Online Backup
  • Restore a partial Offline Backup

See our slide's Fallback section for further details.

But actually there are ways to restart the upgrade without the need to fallback in case of a non-destructive incident such as running out of archivelog space.
.

Restart the Upgrade before Oracle Database 12c

Before Oracle Database 12c the most simple way to restart an upgrade was to restart catupgrd.sql from SQL*Plus making sure the database is back in upgrade mode. Upgrade (and downgrade) scripts are designed to run again ... and again ... if necessary. Even if you kicked off the upgrade with the Database Upgrade Assistant (DBUA) who by itself is not able to rerun the upgrade you can invoke the command line upgrade and rerun it without the need for restore.

SQL> startup upgrade

SQL> spool /home/oracle/catupgrd.log

SQL> @?/rdbms/admin/catupgrd.sql

This will restart the upgrade from the beginning and rerun it again.
.

Restart the Upgrade in Oracle Database 12.1.0.x

With this release we introduced the parallel upgrade utility, catctl.pl.  The database needs to be in startup upgrade mode again but the upgrade will be driven from the command line with the Perl tool running the upgrade with a maximum of 8 parallel workers. And if the upgrade fails non-destructive for whatever reason you invoke catctl.pl from the phase where it has stopped with the -p option specifying the phase number.

In the following example the upgrade has been stopped in phase 100 (in my example by a CTRL-C):

*********** Upgrading Misc. ODM, OLAP **********

Serial Phase  #:95 [UPGR] Files:1 Time: 0s

**************** Upgrading APEX ****************

Restart Phase #:96 [UPGR] Files:1 Time: 0s

Serial Phase  #:97 [UPGR] Files:1 Time: 1s

Restart Phase #:98 [UPGR] Files:1 Time: 0s

*********** Final Component scripts ***********

Serial Phase  #:99 [UPGR] Files:1 Time: 0s

************* Final Upgrade scripts ************

Serial Phase #:100 [UPGR] Files:1   ^[c

Now I simply can restart it from this phase again without the need to rerun all the previous steps:

Make sure the database is in STARTUP UPGRADE mode. Then invoke from ?/rdbms/admin:

$ORACLE_HOME/perl/bin/perl catctl.pl -p 100 -l /home/oracle/ catupgrd.sql

************* Final Upgrade scripts ************

Serial Phase #:100 [UPGR] Files:1 Time: 142s

********** End PDB Application Upgrade *********

Serial Phase #:101 [UPGR] Files:1 Time: 1s

******************* Migration ******************

Serial Phase #:102 [UPGR] Files:1 Time: 60s

Serial Phase #:103 [UPGR] Files:1 Time: 0s

Serial Phase #:104 [UPGR] Files:1 Time: 71s

***************** Post Upgrade *****************

Serial Phase #:105 [UPGR] Files:1 Time: 30s

**************** Summary report ****************

Serial Phase #:106 [UPGR] Files:1 Time: 1s

Serial Phase #:107 [UPGR] Files:1 Time: 0s

Serial Phase #:108 [UPGR] Files:1 Time: 38s

This works also when the upgrade has been started with the DBUA and failed. The DBUA is unable to rerun the upgrade but you can always invoke the command line upgrade by looking at the logfiles DBUA produced finding the phase where it has been stopped.
.

Restart the Upgrade in Oracle Database 12.2.0.x

In Oracle Database 12.2.0.1 we have again improved the rerun ability of the upgrade - now you don't have to know the phase where it stopped - but we introduce the -R option for catctl.pl to rerun the upgrade automatically after the last completed phase.

$ORACLE_HOME/perl/bin/perl catctl.pl -R -l /home/oracle/ catupgrd.sql

See the documentation for further details:

Rerunning Upgrade for Oracle Databases - Oracle Database Upgrade Guide 12.2

And the DBUA has been improved as well. It can now rerun an failed upgrade but only from within the same DBUA session (so please don't close it). And of course you can always fall back to the command line and complete it from there.

See a separate blog post about:

Rerunning the Upgrade with the DBUA in Oracle Database 12.2

.
--Mike
.




Wednesday Jan 18, 2017

Default Changes SPFILE Parameters - Oracle 12.2

Parameters in Oracle Database 12.2.0.1 - part 4 of the series:


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

Tuesday Jan 17, 2017

Having fun with PDB LOCKDOWN PROFILES

In Oracle Database 12.2 (available in the Oracle DBaaS Cloud) there's a new functionality called "PDB LOCKDOWN PROFILES". Actually the parameter came already in undocumented and not functional in Oracle Database 12.1.0.2 (see here).

PDB Lockdown Profiles

PDB Lockdown Profiles are meant as a granular way to restrict or enable access - mostly under the aspect of using the database in a highly shared environment but requiring security. The idea is to embed restrictions on-top of a grant, i.e. taking away certain things. As an example you could allow a user logged into a certain PDB only to change the optimizer_mode and cursor_sharing parameters when doing an ALTER SYSTEM.

The glossary in the Concepts Guide says:

A security mechanism to restrict operations that are available to local users connected to a specified PDB.

And here is a good overview in the Concepts Guide and the Security Guide for details:

Lockdown Profiles in Oracle Multitenant

Lockdown Profiles - Create, Drop, Alter

How-to-Lockdown-Profile

Lets start with a fresh PDB:

SQL> create pluggable database PDB2 admin user adm identified by adm file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb2');

First we need to create a lockdown profile:

SQL> create lockdown profile P1;

Then we can ALTER the profile and allow only to change optimizer_mode and cursor_sharing with an ALTER SYSTEM.

SQL> alter lockdown profile P1 disable statement=('ALTER SYSTEM') clause=('SET') OPTION ALL EXCEPT=('optimizer_mode','cursor_sharing');

And finally, the PDB Lockdown Profile needs to be enabled:

SQL> alter system set PDB_LOCKDOWN=P1;

Check:

SQL> show parameter pdb_l

NAME          TYPE    VALUE
------------- ------- ------
pdb_lockdown  string  P1
.

Where the fun begins ...

Now lets connect with my default SYS user - SYS per definitionem is a common user - and switch to my PDB2:

$> sqlplus / as sysdba

SQL> alter session set container=PDB2;

SQL> alter system set sql_trace=TRUE;
*
ERROR at line 1:
ORA-01031: insufficient privileges

Aha! Ok, so let's try ...

SQL> alter system set cursor_sharing='FORCE';
System altered.


SQL> alter system set optimizer_mode='FIRST_ROWS_10';
System altered.

Ok, this works. But can I still change SQL_TRACE on the session level? Of course I can ...

SQL> alter session set SQL_TRACE=TRUE;
Session altered.

Makes sense as I restricted only ALTER SYSTEM but not ALTER SESSION.
So let's do this as well:

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

SQL> alter lockdown profile P1 disable statement=('ALTER SESSION') clause=('SET') OPTION ALL EXCEPT=('optimizer_mode','cursor_sharing')
Lockdown Profile altered.

Another tiny exercise taken directly from the documentation:

CREATE LOCKDOWN PROFILE medium;
ALTER LOCKDOWN PROFILE medium DISABLE STATEMENT=('ALTER SYSTEM');
ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=('ALTER SYSTEM') CLAUSE=('FLUSH SHARED POOL');

This will allow ONLY the ALTER SYSTEM FLUSH SHARED POOL command with ALTER SYSTEM.

SQL>   alter system set pdb_lockdown='MEDIUM';
  alter system set pdb_lockdown='MEDIUM'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter system set sql_trace=true;
alter system set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

And of course you can add more things to an existing profiles such as disabling certain features:

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

SQL> ALTER LOCKDOWN PROFILE medium DISABLE FEATURE=('XDB_PROTOCOLS');
Lockdown Profile altered

.

Which profiles do exist and what's defined?

First of all, the parameter PDB_LOCKDOWN is modifiable on a PDB level meaning you can have different profiles for different PDBs. But as far as I tested only one profile per PDB can be active and enabled.

You can look up existing profiles and their contents?

SQL>  select profile_name, rule_type, rule, clause, clause_option, status from DBA_LOCKDOWN_PROFILES order by 1;

PROFILE_NAME   RULE_TYPE  RULE           CLAUSE     CLAUSE_OPTION   STATUS
-------------- ---------- -------------- ---------- --------------- -------
MEDIUM         STATEMENT  ALTER SYSTEM                              DISABLE
MEDIUM         FEATURE    XDB_PROTOCOLS                             DISABLE
P1             STATEMENT  ALTER SESSION  SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SESSION  SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SESSION  SET                        DISABLE
P1             STATEMENT  ALTER SYSTEM   SET                        DISABLE
PRIVATE_DBAAS                                                       EMPTY
PUBLIC_DBAAS                                                        EMPTY
SAAS                                                                EMPTY

11 rows selected.

.

Summary

This is a very powerful feature - but it can be a bit tricky in the future to find out why certain things don't work. The ORA-1031 error may now always guide into the correct direction.

One thing which puzzles me a bit:
We speak always about simplifying things, easing administration. And with PDB Lockdown Profiles you can highly complicate things. Or tease your colleagues :-) (please don't, ok?!). Just don't over-complicate things ... :-)
.

--Mike

Wednesday Dec 21, 2016

APEX is in CDB$ROOT again - Journey to the Cloud VII

Journey to the Oracle CloudWell ... it's been a while ... but I would like to continue my journey to the cloud ...

What happened so far on my Journey to the Cloud?

Oracle Database 12.2.0.1 in the Cloud

Since November 2016 Oracle Database 12.2.0.1 is available in the Oracle DBaaS Cloud. And I received this question in my inbox yesterday:

I have a customer who wants to migrate Apex 4.2 applications to DBCS and ORDS.war to Weblogic on Compute.

I recently went through your blog on removing Apex from CDB Root. The customer is planning to do something similar but had questions on repercussions of doing so in Oracle Public Cloud.

What are the factors that need to be considered. Also how would DBCS patching work in this scenario.

.

Is APEX is in CDB$ROOT again

I haven't checked this yet as we've had a very productive conversation with the APEX folks a while back. And I'm 100% sure that the APEX group wasn't involved in this decision as they recommend clearly in the doc to NOT HAVE APEX installed in the CDB$ROOT ;-)
Ok, I did connect to our Cloud environment and ... voila ...

COLUMN VERSION FORMAT A15
COLUMN NAME FORMAT A12
COLUMN COMP_ID FORMAT A10

SQL> select r.comp_id, r.version, c.name from cdb_registry r, v$containers c where r.con_id=c.con_id and r.comp_id='APEX' order by 3

COMP_ID    VERSION         NAME
---------- --------------- ------------
APEX       5.0.4.00.12     CDB$ROOT
APEX       5.0.4.00.12     ROYPDB1

Ouch ...

The presence of APEX in the CDB$ROOT may have to do with the Oracle DBaaS Monitor Console. This is just an assumption but when I removed the APEX in my old 12.1.0.2 cloud deployment I had to clean-up the DBaaS Monitor as well.

See:

for my experience a few months back.
.

Solution?

Well, having APEX in the CDB$ROOT is still a brilliant [IRONY!] idea. As soon as you start unplug/plug operations with APEX in the PDB only or with a different APEX version you are asking for trouble.

See this blog post for the potential pitfalls:

Which options does the customer have assuming that his APEX 4.2 application is in a non-CDB?

  • Upgrade APEX locally to 5.0.4.00.12 before migrating the database to the DBaaS cloud
    This would be the easiest workaround if it wouldn't involve an application software upgrade. Through the Oracle glasses this looks simple - but from a customer's and application developer's standpoint this isn't trivial as most likely it will involve application testing
    .
  • Export the APEX application and import it
    I haven't done this by myself but first of all with APEX 4.2 (or below) you must take care to move the image files as well - and you'll have to move data as well. And, of course, you won't end up in APEX 4.2 but in APEX 5.0 so the above mentioned application upgrade will hit you as well. I don't see any benefit over solution 1.
    .
  • Remove APEX from the 12.2.0.1 DBaaS Cloud deployment's CDB$ROOT
    This is - in my humble opinion - the only viable solution here if the customer can't upgrade APEX in the current environment for whatever reason. But this will most likely remove the DBaaS Monitor as well. I can live without it but I know that it offers a lot of good stuff especially when dealing with encrypted tablespaces which is otherwise hard to handle on the command line. The good part of this solution is the freedom and flexibility you'll get once APEX is removed from the CDB$ROOT for unplug/plug operations in the future.
    .

Finally, regarding patching:
I don't see any issues. And the same for a future upgrade as we decouple APEX upgrades from the database upgrade with Oracle Database 12.2.0.
.

--Mike




Thursday Dec 08, 2016

Create a database with NON-DEFAULT Time Zone

One requirement of Transportable Tablespaces (and of course Full Transportable Export/Import as well) is to have identical database character sets AND identical time zone settings.

Problem

Source database has a lower time zone setting than the default target database in the destination home.

Lets assume you'd like to migrate an Oracle 11.2.0.4 off IBM AIX and migrate it into Oracle 12.1.0.2 on an Exadata meaning Oracle Linux 6. The source time zone version if it has never been upgraded would be TZ V14 - whereas the new Oracle 12.1.0.2 database you'd create would get TZ V18 by default.

Time Zone Setting Oracle Database

Solution 1 - Upgrade Time Zone version in Source

You could now "simply" upgrade the Time Zone version in the source database meaning you'd apply the time zone patch matching the default time zone of the destination database home (see MOS Note: 412160.1 for the list of available Time Zone patches). Then you would have to adjust the data with the scripts provided in MOS Note:1585343.1

Or you could go a step further and apply the most recent available time zone patches to both homes which I'd recommend in this case. Then you'll adjust the source database before initiating the transport steps including the copy operations.

I have put "simply" in quotation marks on purpose as often this may not be an option. Changing the source's time zone version will incur downtime and a restart of the database. Furthermore in case of somebody has a very high amount of time zone dependent data it may take a while.
.

Solution 2 - Create Destination Database with lower Time Zone version

Therefore I'd consider the second solution as the more interesting and practical one: Create the destination database to transport to with a different time zone version than the default. In Oracle Database 12.1.0.2 the default would be TZ V18.

Step 1: Create database creation scripts with DBCA

I haven't tried if it would work as well to let the DBCA directly create a custom database but I don't trust Java applications using my environment variables so I prefer the script version. It does not work to use one of the seed databases in DBCA (DWH and OLTP and GENERAL PURPOSE) as those all use the default version of the release without an option to override it.

Step 2: Set environment variable ORA_TZFILE

The time zone files are located in $ORACLE_HOME/oracore/zoneinfo. By using the environment variable ORA_TZFILE we can override the default of using the highest numbered time zone file in this directory:

$ export  ORA_TZFILE=/u01/app/oracle/product/12.1.0.2/oracore/zoneinfo/timezone_14.dat

Step 3: Create the database with the "sh" script

DBCA has created the "create database" scripts and one named <SID>.sh. Start the creation by:

$ ./MIKE.sh

Step 4: Verify the correct time zone setting of your new database

The following query reflects the current time zone version of this database:

SQL> select value$ "TZ Version" from SYS.PROPS$ where name='DST_PRIMARY_TT_VERSION';

In my example the result will be 14 now instead of 18 (the default for an Oracle 12.1.0.2 database).
.

--Mike
.



Tuesday Dec 06, 2016

Upgrade to Oracle Database 12.2 - Slides are available

The workshops in Brussels and Utrecht were awesome with great audience - I enjoyed both events a lot. And thanks for your patience with my voice and the microphone issues we've had in Brussels before lunch.

I promised the workshop slides - and here they are, ready for download:

Upgrade, Migrate and Consolidate to Oracle Database 12.2 and the Cloud

Slides Oracle 12.2 Upgrade Migrate Consolidate

Please be aware: It's the first drop of the slides, not everything has been updated yet for Oracle Database 12.2, and of course there are no 12.2 customer examples in the slides yet. But as usual, if you plan to go live early on Oracle Database 12.2 - in the Oracle Cloud or as soon as on-premises is available please get in touch with me as we are looking for future reference customer cases.

--Mike & Roy
.

Wednesday Nov 30, 2016

DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH

At the DOAG Conference in November in Nürnberg in November 2016 a customer asked me right after my talk about "Upgrade to Oracle Database 12.2. - Live and Uncensored" why the DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch and follows all instructions including the "./datapatch -verbose" call.

I was wondering as well and asked him to open an SR. Which he did. And he received the message from Support that it is not supposed to appear in Oracle 12c anymore this way but only in DBA_REGISTRY_SQLPATCH. Now I dug a bit deeper internally to get a clear statement (thanks to Carol (my boss) and Rae (my teammate) and Scott (the man who owns datapatch) for following up!).
.

Patch Query in Oracle Database 11g

Tim Hall has published this simple and quite helpful script to query applied PSUs and BPs in Oracle Database 11g:
Script to monitor DBA_REGISTRY_HISTORY

And the output in my environment looked like this:

ACTION_TIME          ACTION  NAMESPE VERSION  ID      COMMENTS             BUN
-------------------- ------- ------- -------- ------- -------------------- ---
01-JUL-2016 15:24:56 APPLY   SERVER  11.2.0.4 160419  PSU 11.2.0.4.160419  PSU
21-OCT-2016 17:40:32 APPLY   SERVER  11.2.0.4 161018  PSU 11.2.0.4.161018  PSU

But running the same script on Oracle Database 12.1.0.2 returnes (as for the customer) "no rows selected".
.

Patch Query for Oracle Database 12c

Since Oracle Database 12.1.0.1 we use DBA_REGISTRY_SQLPATCH instead of DBA_REGISTRY_HISTORY to track PSUs and BPs applied to the database. My output in Oracle Database 12.1.0.2 looks like this:

ACTION_TIME          ACTION  STATUS   DESCRIPTION          VERSION  PATCH_ID BUND
-------------------- ------- -------- -------------------- -------- -------- ----
21-OCT-2016 17:29:36 APPLY   SUCCESS  DBP: 12.1.0.2.161018 12.1.0.2 24340679 DBBP

when using this tiny script:

SET LINESIZE 400

COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
       action,
       status,
       description,
       version,
       patch_id,
       bundle_series
FROM   sys.dba_registry_sqlpatch
ORDER by action_time;

But the question remains if - as in Oracle Database 12.1.0.1 - both views should get updated.

Explanation

In 11.2.0.4, we used the script catbundle.sql to apply bundle patches.  It uses DBA_REGISTRY_HISTORY only.  For 12.1.0.1 with the introduction of datapatch, we now have the (much better) DBA_REGISTRY_SQLPATCH.  This is used for both, bundle and non-bundle patches.  In Oracle Database 12.1.0.1. for bundle patches we actually called catbundle internally, so in 12.1.0.1 both registries were updated for bundle patches.
Starting in 12.1.0.2, however, only DBA_REGISTRY_SQLPATCH is queried and updated for bundle and non bundle patches.

Update [Dec 23, 2016]

After discussing this and other issues with the owners of datapatch my teammate Rae logged a bug for this issue as we believe both views should be updated as it happened in 12.1.0.1. Bug# 25269268 tracks the issue.
.

--Mike

.


Thursday Nov 24, 2016

New PREUPGRADE.JAR is available - MOS 884522.1

As promised - and thanks to everybody from the Database Upgrade Development Team - the new preupgrade.jar for Oracle Database 12.2 is available:

And of course the previous preupgrd.sql with the package utlupkkg.sql and the old utlu112i.sql for upgrade to Oracle 11.2.0.4 (which I wouldn't do anymore) are available as well.

New Preupgrd.sql - MOS Note 884522.1

Please ALWAYS use the version downloaded from this MOS note as this version is much newer and better than the version getting shipped due to code freeze dates. And you will recognize that the new preupgrade.jar is now identical on all platforms.

In the next days I will explain a bit what will change with preupgrade.jar.
.

--Mike
.

Wednesday Nov 23, 2016

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS.

For more information please see:

But Oracle Database 12.2 on-premises is not out yet - so what should you do when upgrading to Oracle Database 12.1 - or struggling with some of the "adaptive" features in Oracle 12.1?

It recommends to adopt the Oracle Database 12.2 defaults when upgrading to Oracle Database 12.1. This can be achieved by installing two patches - we call it the recommended approach.

  • The patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
  • The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.
  • Please make sure you'll remove OPTIMIZER_ADAPTIVE_FEATURES from your spfile:
    alter system reset optimizer_adaptive_features;
    when applying the patches.

Both patches should help as well once you have upgraded already to Oracle Database 12.1 but encountering performance issues.

Please note that it's not necessary to set OPTIMIZER_DYNAMIC_SAMPLING to a non-default value because the patches will disable the use of adaptive dynamic sampling to match the default behavior in Oracle Database 12.2 when both new parameters are used in their default settings.
.

--Mike
.

Tuesday Nov 22, 2016

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

  • OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
  • OPTIMITER_ADAPTIVE_STATISTICS=FALSE by default

Nigel Bayliss, our "Optimizer" Product Manager has blogged about it already with way more detailed insights.

But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?

--Mike
.

Monday Nov 14, 2016

SPFILE Parameter: max_pdbs - a must for Single Tenant

Sometimes my job has a aspect making me smile at the end of the day ;-)

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there's no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won't be the correct solution as unplug/plug operations leave leftovers in it unless you DROP PLUGGABLE DATABASE. And even if you drop the remains the constraint solution does not work.

At the breakfast the next morning Johannes mentioned a trigger - and he published one soon after. But it is not a good idea in terms of keeping support for your database when you fiddle around with the data dictionary.

So I did ask my contacts internally and received a message saying clearly
"Somebody doesn't want this.". :-)
Well, I work long enough for Oracle to know how to read it. It's a common thing to blame it on "Somebody" when you don't want to discuss things further. One can hide perfectly well behind "Somebody".

The higher my surprise was when I started testing and playing with Oracle Database 12.2 - and collecting init.ora parameters between release labels to detect changes and additions. And apparently, this one here appeared:

saying "max number of pdbs allowed in CDB or Application ROOT" in its parameter description.
I was (a) surprised and (b) happy and (c) had to try it out immediately in my environment where I had already 3 PDBs (the PDB$SEED does not count):

SQL> alter system set max_pdbs=3;
System altered.

SQL> show pdbs

CON_ID CON_NAME               OPEN MODE  RESTRICTED
------ ---------------------- ---------- ----------
2      PDB$SEED               READ ONLY  NO
3      PDB1                   READ WRITE NO
4      PDB2                   READ WRITE NO
5      CLONE_PDB              MOUNTED

SQL> alter system set max_pdbs=
2;
alter system set max_pdbs=2
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65331: DDL on a data link table is outside an application action.
.

So even though the error message is a bit rough (at least in my release drop) the parameter does what we want. In a single tenant environment you'll set it to "1" and prevent the creation or plugin of a 2nd PDB in this container database..

Another test with a fresh container database:

SQL> show pdbs

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


SQL> alter system set max_pdbs=1;
System altered.

SQL> show parameter max_pdbs

NAME                     TYPE     VALUE
------------------------ -------- -----
max_pdbs                 integer      1


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

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');

create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2')
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created


SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.

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

.
Looks like a solid solution to me.

But please see also this blog post by Oracle ACE Director Franck Pachot about an issue with the parameter:
http://blog.dbi-services.com/oracle-12cr2-max_pdbs/

--Mike
.

Friday Nov 11, 2016

Deprecated Parameters in Oracle Database 12.2.0.1

Parameters in Oracle Database 12.2.0.1 - part 3 of the series:

Finally, here's the list of DEPRECATED parameters in Oracle Database 12.2.0.1:

O7_DICTIONARY_ACCESSIBILITY
active_instance_count
asm_preferred_read_failure_groups
background_dump_dest
buffer_pool_keep
buffer_pool_recycle
commit_write
cursor_space_for_time
db_block_buffers
fast_start_io_target
instance_groups
lock_name_space
log_archive_start
parallel_adaptive_multi_user
plsql_debug
plsql_v2_compatibility
rdbms_server_dn
remote_os_authent
resource_manager_cpu_allocation
sec_case_sensitive_logon
serial_reuse
sql_trace
standby_archive_dest
unified_audit_sga_queue_size
user_dump_dest
utl_file_dir

Only the ones in BOLD were newly marked as DEPRECATED in Oracle Database 12.2.0.1. The non-bold ones had been deprecated in earlier releases already.
Please see also the column ISDEPRECATED in V$PARAMETER.
.

--Mike
.

Thursday Nov 10, 2016

Obsolete SPFILE Parameters in Oracle Database 12.2.0.1

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

Find the list of the 159 obsoleted parameters here (and of course in V$OBSOLETE_PARAMETERS):

_app_ctx_vers
_average_dirties_half_life
_aw_row_source_enabled
_compatible_no_recovery
_data_transfer_cache_size
_db_no_mount_lock
_dlm_send_timeout
_dtree_bintest_id
_dtree_compressbmp_enabled
_evolve_plan_baseline_report_level
_fast_start_instance_recovery_target
_fic_max_length
_fic_outofmem_candidates
_idl_conventional_index_maintenance
_kgl_latch_count
_kks_free_cursor_stat_pct
_kspptbl_mem_usage
_lm_direct_sends
_lm_multiple_receivers
_lm_rcv_buffer_size
_lm_statistics
_log_archive_buffer_size
_log_io_size
_max_log_write_io_parallelism
_module_action_old_length
_optimizer_adaptive_plans
_optimizer_choose_permutation
_oracle_trace_events
_oracle_trace_facility_version
_plan_verify_local_time_limit
_plsql_conditional_compilation
_px_async_getgranule
_px_slaves_share_cursors
_seq_process_cache_const
_spr_use_hash_table
_sqlexec_progression_cost
_use_hidden_partitions
_very_large_partitioned_table
allow_partial_sn_results
always_anti_join
always_semi_join
arch_io_slaves
b_tree_bitmap_plans
backup_disk_io_slaves
cache_size_threshold
cell_partition_large_extents
cleanup_rollback_entries
close_cached_open_cursors
complex_view_merging
db_block_checkpoint_batch
db_block_lru_extended_statistics
db_block_lru_latches
db_block_lru_statistics
db_block_max_dirty_target
db_file_simultaneous_writes
dblink_encrypt_login
ddl_wait_for_locks
delayed_logging_block_cleanouts
discrete_transactions_enabled
distributed_recovery_connection_hold_time
distributed_transactions
drs_start
enqueue_resources
exclude_seed_cdb_view
fast_full_scan_enabled
freeze_DB_for_fast_instance_recovery
gc_defer_time
gc_files_to_locks
gc_latches
gc_lck_procs
gc_releasable_locks
gc_rollback_locks
hash_join_enabled
hash_multiblock_io_count
instance_nodeset
job_queue_interval
job_queue_keep_connections
large_pool_min_alloc
lgwr_io_slaves
lm_locks
lm_procs
lm_procs
lm_ress
lock_sga_areas
log_block_checksum
log_files
log_parallelism
log_simultaneous_copies
log_small_entry_max_size
logmnr_max_persistent_sessions
max_commit_propagation_delay
max_rollback_segments
max_transaction_branches
mts_circuits
mts_dispatchers
mts_listener_address
mts_max_dispatchers
mts_max_servers
mts_multiple_listeners
mts_servers
mts_service
mts_sessions
ogms_home
ops_admin_group
ops_interconnects
optimizer_adaptive_features
optimizer_max_permutations
optimizer_percent_parallel
optimizer_search_limit
oracle_trace_collection_name
oracle_trace_collection_path
oracle_trace_collection_size
oracle_trace_enable
oracle_trace_facility_name
oracle_trace_facility_path
parallel_automatic_tuning
parallel_broadcast_enabled
parallel_default_max_instances
parallel_degree_level
parallel_io_cap_enabled
parallel_min_message_pool
parallel_server
parallel_server_idle_time
parallel_server_instances
parallel_transaction_resource_timeout
partition_view_enabled
plsql_compiler_flags
plsql_native_c_compiler
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_native_linker
plsql_native_make_file_name
plsql_native_make_utility
push_join_predicate
remote_archive_enable
row_cache_cursors
row_locking
sequence_cache_entries
sequence_cache_hash_buckets
serializable
shared_pool_reserved_min_alloc
snapshot_refresh_interval
snapshot_refresh_keep_connections
snapshot_refresh_processes  
sort_direct_writes
sort_multiblock_read_count
sort_read_fac
sort_spacemap_size
sort_write_buffer_size
sort_write_buffers
spin_count
sql_version
standby_preserves_names
temporary_table_locks
text_enable
transaction_auditing
undo_suppress_errors
use_indirect_data_buffers
use_ism

--Mike
.

Wednesday Nov 09, 2016

New SPFILE parameters in Oracle Database 12.2.0.1

Oracle Database 12.2.0.1 is available now in the Oracle Cloud.

And this is the list of 46 new init.ora/spfile parameters compared to Oracle Database 12.1.0.2 - including the links (where possible) to the Oracle Database 12.2 Reference documentation.

Parameter

Description

allow_global_dblinks

LDAP lookup for DBLINKS

allow_group_access_to_sga

Allow read access for SGA to users of Oracle owner group

approx_for_aggregation

Replace exact aggregation with approximate aggregation

approx_for_count_distinct

Replace count distinct with approx_count_distinct

approx_for_percentile

Replace percentile_* with approx_percentile

asm_io_processes

number of I/O processes per domain in the ASM IOSERVER instance

autotask_max_active_pdbs

Setting for Autotask Maximum Maintenance PDBs

awr_pdb_autoflush_enabled

Enable/Disable AWR automatic PDB flushing

cdb_cluster [undocumented

if TRUE startup in CDB Cluster mode

cdb_cluster_name [undocumented]

CDB Cluster name

clonedb_dir

CloneDB Directory

containers_parallel_degree

Parallel degree for a CONTAINERS() query

cursor_invalidation

default for DDL cursor invalidation semantics

data_guard_sync_latency

Data Guard SYNC latency

data_transfer_cache_size

Size of data transfer cache

default_sharing

Default sharing clause

disable_pdb_feature [undocumented]

Disable features

enable_automatic_maintenance_pdb

Enable/Disable Automated Maintenance for Non-Root PDB

enable_dnfs_dispatcher

Enable DNFS Dispatcher

enabled_PDBs_on_standby

List of Enabled PDB patterns

encrypt_new_tablespaces

whether to encrypt newly created tablespaces

exafusion_enabled

Enable Exafusion

external_keystore_credential_location

external keystore credential location

inmemory_adg_enabled

Enable IMC support on ADG

inmemory_expressions_usage

Controls which In-Memory Expressions are populated in-memory

inmemory_virtual_columns

Controls which user-defined virtual columns are stored in-memory

instance_abort_delay_time

time to delay an internal initiated abort (in seconds)

instance_mode

indicates whether the instance read-only or read-write or read-mostly

long_module_action

Use longer module and action

max_datapump_jobs_per_pdb

maximum number of concurrent Data Pump Jobs per PDB

max_idle_time

maximum session idle time in minutes

max_iops

MAX IO per second

max_mbps

MAX MB per second

max_pdbs

max number of pdbs allowed in CDB or Application ROOT

ofs_threads

Number of OFS threads

one_step_plugin_for_pdb_with_tde [undocumented]

Facilitate one-step plugin for PDB with TDE encrypted data

optimizer_adaptive_plans

controls all types of adaptive plans

optimizer_adaptive_statistics

controls all types of adaptive statistics

outbound_dblink_protocols

Outbound DBLINK Protocols allowed

remote_recovery_file_dest

default remote database recovery file location for refresh/relocate

resource_manage_goldengate

goldengate resource manager enabled

sga_min_size

Minimum, guaranteed size of PDB's SGA

shrd_dupl_table_refresh_rate

duplicated table refresh rate (in seconds)

standby_db_preserve_states

Preserve state cross standby role transition

target_pdbs [undocumented]

Parameter is a hint to adjust certain attributes of the CDB

uniform_log_timestamp_format

use uniform timestamp formats vs pre-12.2 formats


.
--Mike
.

Tuesday Nov 08, 2016

Oracle Database 12.2.0.1 - Cloud Documentation

And here it is ...

The documentation for Oracle Database 12.2.0.1 in the Oracle Cloud:

Oracle Database 12.2 - Cloud Documentation

.
And this is the All Database 12.2 Books link:

--Mike
.

Upgrades to Oracle Database 12.2.0.1 (and Downgrades)

Oracle Database 12c Release 2 (12.2) is available now in the Oracle Cloud in DBCS and ECS in both NAS and EMEA zones, as well as being available on Exadata Express Cloud Service. The Oracle Database 12.2 documentation should be published later today.

Just to note down, the versions supporting direct upgrade either with catctl.pl on the command line or with DBUA are:

  • Oracle Database 11.2.0.3
  • Oracle Database 11.2.0.4
  • Oracle Database 12.1.0.1
  • Oracle Database 12.1.0.2

Direct Upgrade to Oracle Database 12.2

No direct upgrades are supported from versions below Oracle Database 11.2.0.3.

For versions older than Oracle Database 11.2.0.3 other tools such as Data Pump or techniques such as Transportable Tablespaces may be used in order to avoid double- or triple-hops jumping from one release to another. And of course those will work when migrating into the Oracle Cloud as well.

For Downgrades you can downgrade back down to the version you have upgraded from for non-CDBs.

--Mike
.

About

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

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search


Archives
« February 2017
SunMonTueWedThuFriSat
   
2
4
5
6
7
10
11
12
14
15
18
19
20
21
22
23
24
25
26
27
28
    
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers