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

Oracle 12.2.0.1 Bundle Patch 12.2.0.1.170516 DBBP on Linux x86-64 is available

All credits go to Ricardo Maeda as I knew that we’ll release a Bundle Patch for Oracle 12.2.0.1 sometime this week – but I couldn’t find it linked from the usual MOS notes. And please don’t ask my why that is.

Anyhow, with patch 2579308 you’ll get access to the first BP for Oracle Database 12.2.0.1. There will be a first bigger Proactive Bundle Patch in July at the usual schedule – but this one is at least a start.

12.2.0.1.170516BP

Patch 2579308 – Oracle 12.2.0.1.170516BP

Plus in addition get the OPatch version 12.2.0.1.7 via patch 6880880.

The BP contains:

12.2.0.1.170516BP

First 12.2.0.1 Bundle Patch – Contents: Database BP and GI PSU

Obrigado, Ricardo!

–Mike

GIMR DB in Oracle Database 12.2

I did blog in the past already about the Grid Infrastructure Management Repository Database (GIMR DB):

Grid Infrastructure Management Repository (GIMR)database now mandatory in Oracle GI 12.1.0.2

SID and DBNAME are kept the same in Oracle Database 12.2. But there are a few changes and additions in Oracle Database 12.2.0.1.

GIMR DB in its own Disk Group

During a fresh installation of Oracle Grid Infrastructure 12.2.0.1 you’ll have the chance to create a disk group for GIMR DB:

GIMR DB - Own Disk Group in Oracle 12.2

GIMR DB – Disk Group

But you can migrate GIMR DB into a separate disk group as well – just make sure the size is large enough (see below).

  1. Download MDBUtil from MOS Note 2065175.1 (see next chapter below)
  2. Create your designated disk group for GIMR DB with ASMCA
  3. Move GIMR DB into the new diskgroup with MDBUtil:
    mdbutil.pl --mvmgmtdb --target=+GIMRDB
  4. Verify and startup:
    srvctl config mgmtdb
    oclumon dumpnodeview –allnodes

Download the most recent MDBUtil

The MDBUtil got introduced to ease standard tasks to administer, create, relocate etc GIMR DB a while back. And it gets steadily improved. The most recent version has Oracle 12.2.0.1 support and dates (while I create this post) from mid of March 2017.

It’s a PERL script – and it’s not supported by Oracle Support as the note explaining the standard tasks and offering the tool for download explains:

MDBUtil usage:

# ./mdbutil.pl -h
Usage:
     Create/Enable MGMTDB & CHM
       mdbutil.pl --addmdb --target=
     Move MGMTDB to another location
       mdbutil.pl --mvmgmtdb --target=   
     Check MGMTDB status
       mdbutil.pl --status
   
     mdbutil.pl OPTIONS
       --addmdb            Create MGMTDB/CHM and reconfigure related functions
       --mvmgmtdb          Migrate MGMTDB to another location   
       --target='+DATA'    MGMTDB Disk Group location
       --status            Check the CHM & MGMTDB status
       --help              Display this help and exit
       --debug             Verbose commands output/trace
   
     Example:
       Create/Enable MGMTDB:
         mdbutil.pl --addmdb --target=+DATA
       Move MGMTDB to another location:
         mdbutil.pl --mvmgmtdb --target=+REDO             
       Check CHM:
         mdbutil.pl --status

The MOS Note contains examples for creating and relocating GIMR DB as well.

Avoid INS-43100 error during upgrade

To avoid the OUI logging an INS-43100 error please check the space requirements for GIMR DB in Oracle Database 12.2 upfront as they have been increased drastically from Oracle 12.1 to Oracle 12.2., from roughly 5GB for a two-node cluster in Oracle 12.1 to 36GB for the same cluster in Oracle Database 12.2.

See also MOS 2245603.1:12.2 Grid Infrastructure: INS-43100 and Storage Space Requirements

Further information

Please see this presentation by the RAC team held at several User Group conferences and meetings:

–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

Is your APEX version certified with your Database release?

Last week I’ve had an interesting discussion with a customer about the certification of APEX and Oracle Database 12.2.0.1.

I did a bit of research and emailing – and thanks to the APEX team – this is the source of truth for APEX certifications.

Minimum Database version for APEX

See MOS Note:1344948.1: Application Express (APEX) Database and Web Server Certification Reference:

  • For APEX 4.0, the minimum database version is 10.2.0.3
  • For APEX 5.0, the minimum database version is 11.1.0.7
  • For APEX 5.1, the minimum database version is 11.2.0.4

Minimum APEX version per Database

See MOS Note:1344948.1: Application Express (APEX) Database and Web Server Certification Reference:

  • For Oracle Database 11.1.0.x, the minimum APEX version is 3.0.
  • For Oracle Database 11.2.0.x, the minimum APEX version is 3.2.1
  • For Oracle Database 12.1.0.2, the minimum APEX version is 4.2.5
    • For usage with Oracle Multitenant, APEX 4.2.3 or newer must be installed
  • For Oracle Database 12.2.0.1, the minimum APEX version is 5.0.4
    (this information is currently missing in the MOS note and may be added later – read below for more information)

Checking APEX certifications on MyOracle Support’s Certification Tab

  1. Go to https://support.oracle.com
  2. Click Certifications
  3. Enter Oracle Application Express as Product
  4. Select APEX version from the Release list, for example 5.0.4
Oracle APEX - Certification MOS

Oracle APEX Certification in MyOracle Support

By clicking myself through all the possible combinations I could find out that APEX 5.0.3 is not certified with Oracle Database 12.2.0.1 as are all versions below. So the minimum version of APEX for Oracle Database 12.2.0.1 is APEX 5.0.4.

Unfortunately there’s no reverse certification search in MOS available (“Which APEX version(s) does a given databases release support?”).

–Mike

What happens to PASSWORD_VERSIONS during an upgrade to Oracle 12.2?

I did blog a day ago about ORA-1017 connection issues in Oracle Database 12.2 once you would like to use the deprecated init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON=FALSE:

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

But how can this change actually happen?

Let’s check how the PASSWORD_VERSIONS is set in a fresh Oracle Database 12.1.0.2 database with the January 2017 Proactive Bundle Patch applied to it:

SQL> select username, password_versions from dba_users order by 1;

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G 12C
AUDSYS			       10G 11G 12C
DBSNMP			       10G 11G 12C
DIP			       10G 11G 12C
GSMADMIN_INTERNAL	       10G 11G 12C
GSMCATUSER		       10G 11G 12C
GSMUSER 		       10G 11G 12C
ORACLE_OCM		       10G 11G 12C
OUTLN			       10G 11G 12C
SYS			       10G 11G 12C
SYSBACKUP		       10G 11G 12C
SYSDG			       10G 11G 12C
SYSKM			       10G 11G 12C
SYSTEM			       10G 11G 12C
WMSYS			       10G 11G 12C
XDB			       10G 11G 12C
XS$NULL

18 rows selected.

As I haven’t touched SEC_CASE_SENSITIVE_LOGON it will will default to TRUE. In my environment I use an Oracle 12.2 listener  therefore the default for the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12 already.

After the successful upgrade to Oracle Database 12.2 my DBA_USER’s PASSWORD_VERSIONS looks like this:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS order by 1

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G 12C
AUDSYS			       10G 11G 12C
DBSFWUSER		       11G 12C
DBSNMP			       10G 11G 12C
DIP			       10G 11G 12C
GGSYS			       11G 12C
GSMADMIN_INTERNAL	       10G 11G 12C
GSMCATUSER		       10G 11G 12C
GSMUSER 		       10G 11G 12C
ORACLE_OCM		       10G 11G 12C
OUTLN			       10G 11G 12C
REMOTE_SCHEDULER_AGENT
SYS			       10G 11G 12C
SYS$UMF 		       11G 12C
SYSBACKUP		       10G 11G 12C
SYSDG			       10G 11G 12C
SYSKM			       10G 11G 12C
SYSRAC			       11G 12C
SYSTEM			       10G 11G 12C
WMSYS			       10G 11G 12C
XDB			       10G 11G 12C
XS$NULL

23 rows selected.

None of the existing user account’s PASSWORD_VERSIONS get changed. Only new users will be created with either PASSWORD_VERSIONS11G 12C” or locked.

Upgrading with SEC_CASE_SENSITIVE_LOGON=FALSE

What happens if you have set SEC_CASE_SENSITIVE_LOGON=FALSE in your source database prior to an upgrade to Oracle Database 12.2?

First of all you will receive a preupgrade warning when you execute preupgrade.jar:

  RECOMMENDED ACTIONS
  ===================
   + Consider removing the following DEPRECATED initialization parameters.
     They are not OBSOLETE in version 12.2.0.1.0
     but probably will be OBSOLETE in a future release.

     Parameter
     ------------------------------
     sec_case_sensitive_logon

My users in my current – in this case Oracle 11.2.0.4 database – look like this before upgrade:

SQL> select username, password_versions from dba_users

USERNAME		       PASSWORD
------------------------------ --------
SYSTEM			       10G 11G
SYS			       10G 11G
LBACSYS 		       10G 11G
OUTLN			       10G 11G
DBSNMP			       10G 11G
APPQOSSYS		       10G 11G
ANONYMOUS
XDB			       10G 11G
WMSYS			       10G 11G
XS$NULL 		       11G
DIP			       10G 11G
ORACLE_OCM		       10G 11G

12 rows selected.

And the result is “as expected” – no changes to the PASSWORD_VERSIONS when you upgrade to Oracle Database 12.2:

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G
AUDSYS			       11G 12C
DBSFWUSER		       11G 12C
DBSNMP			       10G 11G
DIP			       10G 11G
GGSYS			       11G 12C
GSMADMIN_INTERNAL	       11G 12C
GSMCATUSER		       11G 12C
GSMUSER 		       11G 12C
LBACSYS 		       10G 11G
ORACLE_OCM		       10G 11G
OUTLN			       10G 11G
REMOTE_SCHEDULER_AGENT
SYS			       10G 11G
SYS$UMF 		       11G 12C
SYSBACKUP		       11G 12C
SYSDG			       11G 12C
SYSKM			       11G 12C
SYSRAC			       11G 12C
SYSTEM			       10G 11G
WMSYS			       10G 11G
XDB			       10G 11G
XS$NULL

24 rows selected.

But what happens when you try to connect now with user SYSTEM? SYSTEM has the PASSWORD_VERSIONS="10G 11G" as only the new users will get created with PASSWORD_VERSIONS="11G 12C".

SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Even if you ALTER now SYSTEM‘s password you can’t login as the SEC_CASE_SENSITIVE_LOGON=FALSE setting collides with the default SQL*Net authentication protocol (SQLNET.ALLOWED_LOGON_VERSION_SERVER=12).

SQL> alter user system identified by system;
User altered.

SQL> connect system/system
ERROR:
ORA-01017: invalid username/password; logon denied

SYSTEM‘s password is now "11G 12C" – but still you can’t connect because of the SEC_CASE_SENSITIVE_LOGON=FALSE setting:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='SYSTEM';

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
SYSTEM			       11G 12C

Simple solution: You change SEC_CASE_SENSITIVE_LOGON=TRUE, the default.

SQL> alter system set sec_case_sensitive_logon=true;
System altered.

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
Connected.

Or you use the workaround from my previous blog post:

What happens during export/import?

Next question I’ve had: What happens if I export and import a user, let’s say from Oracle Database 11.2.0.4 into Oracle Database 12.2.0.1. Quick test again:

SQL> grant connect, resource, dba to hugo identified by hugo;
Grant succeeded.

SQL>  select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME		       PASSWORD
------------------------------ --------
HUGO			       10G 11G

SQL> alter user hugo default tablespace users;
User altered.

SQL> create table hugo.tab1 as select * from tab$;
Table created.

Then over to my destination database:

SQL> create directory mydir as '/u02/oradata/DB12/mydir';
Directory created.

SQL> grant read, write on directory mydir to system;
Grant succeeded.

SQL> create public database link SOURCEDB connect to system identified by oracle using 'UPGR';
Database link created.

SQL> select instance from v$thread@SOURCEDB;

INSTANCE
--------------------------------------------------------------------------------
UPGR

Now let’s move HUGO over to the DB12 database using the database link:

$ impdp system/oracle network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:34:48 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
24-APR-17 15:34:54.082: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log 
24-APR-17 15:34:55.181: W-1 Startup took 2 seconds
24-APR-17 15:34:55.299: W-1 Estimate in progress using BLOCKS method...
24-APR-17 15:34:56.428: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
24-APR-17 15:34:56.485: W-1      Estimated 1 TABLE_DATA objects in 1 seconds
24-APR-17 15:34:56.490: W-1 Total estimation using BLOCKS method: 256 KB
24-APR-17 15:34:56.807: W-1 Processing object type SCHEMA_EXPORT/USER
24-APR-17 15:34:56.975: W-1      Completed 1 USER objects in 0 seconds
24-APR-17 15:34:56.975: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
24-APR-17 15:34:57.014: W-1      Completed 1 SYSTEM_GRANT objects in 1 seconds
24-APR-17 15:34:57.014: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
24-APR-17 15:34:57.095: W-1      Completed 3 ROLE_GRANT objects in 1 seconds
24-APR-17 15:34:57.095: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
24-APR-17 15:34:57.385: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
24-APR-17 15:34:57.385: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
24-APR-17 15:35:00.257: W-1      Completed 1 PROCACT_SCHEMA objects in 3 seconds
24-APR-17 15:35:00.257: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
24-APR-17 15:35:01.646: W-1      Completed 1 TABLE objects in 4 seconds
24-APR-17 15:35:01.841: W-1 . . imported "HUGO"."TAB1"                                 1325 rows in 0 seconds using network link
24-APR-17 15:35:02.015: W-1 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
24-APR-17 15:35:02.106: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
24-APR-17 15:35:02.495: W-1      Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
24-APR-17 15:35:02.609: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Apr 24 15:35:02 2017 elapsed 0 00:00:10

Check how the user got created:

SQL>  select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME                     PASSWORD_VERSIONS
---------------------------- --------------------
HUGO                         10G 11G

No worries – Data Pump creates the user exactly as it did exist before.

But what happens if a mandatory password change happens to HUGO?

SQL> alter user hugo identified by hugo;
User altered.

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
HUGO			       11G 12C

If you change SQLNET.ALLOWED_LOGON_VERSION_SERVER to 12a, the currently highest and most secure setting, and then ALTER the user within a new session, you’ll receive:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO'

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
HUGO			       12C

This leads now to the situation that a below-Oracle-12c client can’t connect to this database anymore. I tried to use my SQL*Plus from the Oracle 11.2.0.4 installation:

$ sqlplus "hugo/hugo@DB12"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 24 15:51:08 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol

So be aware of keepin your client environment in good shape when enforcing higher security standards – which we highly recommend of course.

–Mike

Hands-On Lab available: Upgrade to Oracle Database 12.2.0.1

Thanks to Roy – our Hands-On Lab “Upgrade, Migrate and Consolidate to Oracle Database 12.2” is now available for download from OTN.

Instructions and technical requirements are clearly documented on the OTN page as well. The lab is based on Virtual Box and can be easily imported upon download and unzip.

You can always access the lab via the blog’s top menu as well:

HOL Link on the Upgrade Blog

Hands-On Lab – Access via the Blog

Inside the lab you’ll find :

  • UPGR – an Oracle 11.2.0.4 database ready to be upgraded to Oracle Database 12.2.0.1
  • CDB2 – an Oracle 12.2.0.1 container database which  adopts the UPGR database after upgrade as a new PDB
  • FTEX – an Oracle 11.2.0.4 database ready to be migrated with Full Transportable Exp/Imp into a PDB in CDB2
  • CDB1 – an Oracle 12.1.0.2 container database – so you can test the unplug/plug/upgrade and many other things

An overview picture showing you always where you are and how to switch environments:Upgrade HOL Oracle 12.2

Upgrade Hands-On Lab – Oracle Database 12.2 including Multitenant

Hope you’ll enjoy it!

–Mike

Oracle Database 12.2.0.1 for Windows available

Oracle Database 12.2.0.1 for MS Windows is available as of today for download.

Oracle OTN - Download Oracle Database 12.2

Download it from:

The 64bit and 32bit clients are downloadable as well – click on the “See All” link and scroll down a bit on OTN.

For future dates and other platforms please see MOS Release Schedule 742060.1 .

–Mike

Oracle Database 12.2.0.1 on-premises is now available, too

Yep – it’s available … Oracle Database 12.2.0.1 on-premises is available for download now not only on Exadata and SuperCluster but on Linux x86-64, SPARC and Intel Solaris. For other platform plans please see MOS Note:742060.1.

–Mike