Skip navigation

Oracle 12.1.0.2

I had to load a metadata_only dump on several environnements. Prior to running impdp, I had to create a couple of tablespaces and a couple of users.

 

On environment 1 (ENV1), here's how I created my 2 tablespaces:

CREATE bigfile tablespace ARCHIVE_DATA datafile '/data/cr05/ora1/ARCHIVE_DATA.dbf' size 2G autoextend ON next 500M;

CREATE bigfile tablespace ARCHIVE_INDEX datafile '/data/cr05/ora2/ARCHIVE_INDEX.dbf' size 600M autoextend ON next 500M MAXSIZE 9T;

Then I created my 2 users, and used a par file to remap tablespaces and users for my impdp job, which ran as thus:

impdp system dumpfile=structure80tables.dmp parfile=import80tablesdeOAQ1dansOAL1.par job_name=metadata80tables

So it took 23 minutes total, including 20mn (1185 seconds) on loading 930 empty indexes.  As I said, this is metadata_only, so we're loading empty tables and indexes.  The resulting schema weighs 77GB:

Here you're probably wondering (as i did!) how come so much physical space for empty tables and indexes?  Impdp actually creates the segments for those objects and some indexes have a big INITIAL SEGMENT (up to 500Mb), and there are 968 indexes total!

 

Then I moved on to ENV2. On ENV2, here's how I created my 2 tablespaces:

CREATE bigfile tablespace ARCHIVE_DATA datafile '/data/pkgrcr04/ora1/OAL2/ARCHIVE_DATA.dbf' size 2G autoextend ON next 500M;

CREATE bigfile tablespace ARCHIVE_INDEX datafile '/data/pkgrcr04/ora2/OAL2/ARCHIVE_INDEX.dbf' size 60G autoextend ON next 500M MAXSIZE 9T;

 

Then I created my 2 users, and used a par file to remap tablespaces and users for my impdp job, which ran as thus:

impdp system dumpfile=structure80tables.dmp parfile=import80tablesdeOAQ1dansOAL1et2.par job_name=metadata80tables

This time it took 4 minutes total, including 77 seconds for those 930 empty indexes, because I had pre-allocated 60GB!

Dear DBA Frank

19c preupgrade.jar

Posted by Dear DBA Frank Aug 20, 2019

See https://docs.oracle.com/en/database/oracle/oracle-database/19/spuss/using-the-pre-upgrade-information-tool-for-oracle-db…

 

Oracle provides a tool that runs several checks before upgrading your database, be it CDB or non-CDB, to 19c.

preugrade.jar comes with Oracle 19c, but you can also download it separately, so as to put it on several servers where you will run upgrades to 19c in the future.  Here are the contents of the zip file:

Here is an extract from the Oracle documentation:

To run preupgrade.jar, you must set your environment to the old ORACLE HOME:

 

mobo12cR1@oracle::~ # export ORACLE_BASE=/app/mobo12cR1/oracle

mobo12cR1@oracle::~ # export ORACLE_HOME=/app/mobo12cR1/oracle/product/12.1.0

mobo12cR1@oracle::~ # export ORACLE_SID=RMP2FD

mobo12cR1@oracle:RMP2FD:~ # export PATH=.:$ORACLE_HOME/bin:$PATH

 

Then run preupgrade.jar with the java from your old ORACLE_HOME:

mobo12cR1@oracle:RMP2FD:~ # $ORACLE_HOME/jdk/bin/java -jar /app/mobo12cR1/oracle/19c/rdbms/admin/preupgrade.jar TERMINAL TEXT

 

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2019-08-13T11:30:13

 

Upgrade-To version: 19.0.0.0.0

 

=======================================

Status of the database prior to upgrade

=======================================

      Database Name:  RMP2FD

     Container Name:  RMP2FD

       Container ID:  0

            Version:  12.1.0.2.0

     DB Patch Level:  No Patch Bundle applied

         Compatible:  11.2.0.0.0

          Blocksize:  8192

           Platform:  Linux x86 64-bit

      Timezone File:  18

  Database log mode:  ARCHIVELOG

           Readonly:  FALSE

            Edition:  EE

 

  Oracle Component                       Upgrade Action    Current Status

  ----------------                       --------------    --------------

  Oracle Server                          [to be upgraded]  VALID

  Oracle XML Database                    [to be upgraded]  VALID

 

==============

BEFORE UPGRADE

==============

 

  REQUIRED ACTIONS

  ================

  None

 

  RECOMMENDED ACTIONS

  ===================

  1.  Remove initialization parameters that Oracle has obsoleted or removed.

      This action may be done now or when starting the database in upgrade mode

      using the target ORACLE HOME.

 

      Parameter

      ---------

      utl_file_dir

 

      If parameters that are obsolete or removed from the target release are

      present in the pfile/spfile, the Oracle database may not start, or it may

      start with an ORA- error.

 

  2.  Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid

      objects.  You can view the individual invalid objects with

 

        SET SERVEROUTPUT ON;

        EXECUTE DBMS_PREUP.INVALID_OBJECTS;

 

      2 objects are INVALID.

 

      There should be no INVALID objects in SYS/SYSTEM or user schemas before

      database upgrade.

 

  3.  Perform one of the following:

       1) Expire user accounts that use only the old 10G password version and

      follow the procedure recommended in Oracle Database Upgrade Guide under

      the section entitled, "Checking for Accounts Using Case-Insensitive

      Password Version".

       2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19

      SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short

      term approach and is not recommended because it will retain known

      security risks associated with the 10G password version.)

 

      Your database system has at least one account with only the 10G password

      version (see the PASSWORD_VERSIONS column of DBA_USERS).

 

      Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new

      default password-based authentication mode. All Exclusive Mode

      login/authentication attempts will fail for preexisting user accounts

      which only have the 10G password version and neither the 11G or 12C

      password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,

      refer to "Understanding Password Case Sensitivity and Upgrades" in the

      Oracle Database Upgrade Guide.

 

  4.  Remove the SEC_CASE_SENSITIVE_LOGON instance initialization parameter

      setting, to configure your system to use case sensitive password

      authentication by default.

 

      Your database system is configured to enforce case insensitive password

      authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization

      parameter setting is FALSE).

 

      Starting with Oracle Database release 12.2, Exclusive Mode is the default

      password-based authentication mode. Case insensitive password

      authentication is not supported in Exclusive Mode. If your system needs

      to use case insensitive password authentication, Exclusive Mode must be

      switched off prior to the upgrade. See the Network Reference Manual

      chapter about the SQLNET.ORA parameter

      SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.

 

  5.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database

      upgrade in off-peak time using:

 

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

 

      Dictionary statistics do not exist or are stale (not up-to-date).

 

      Dictionary statistics help the Oracle optimizer find efficient SQL

      execution plans and are essential for proper upgrade timing. Oracle

      recommends gathering dictionary statistics in the last 24 hours before

      database upgrade.

 

      For information on managing optimizer statistics, refer to the 12.1.0.2

      Oracle Database SQL Tuning Guide.

 

  6.  (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the

      owner of the trigger or drop and re-create the trigger with a user that

      was granted directly with such. You can list those triggers using: SELECT

      OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE

      TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM

      DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').

 

      There is one or more database triggers whose owner does not have the

      right privilege on the database.

 

      The creation of database triggers must be done by users granted with

      ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted

      directly.

 

  INFORMATION ONLY

  ================

  7.  Consider removing the following deprecated initialization parameters.

 

      Parameter

      ---------

      sec_case_sensitive_logon

 

      These deprecated parameters probably will be obsolete in a future release.

 

  8.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least

      2181 MB of archived logs.  Check alert log during the upgrade that there

      is no write error to the destination due to lack of disk space.

 

      Archiving cannot proceed if the archive log destination is full during

      upgrade.

 

      Archive Log Destination:

       Parameter    :  LOG_ARCHIVE_DEST_1

       Destination  :  /data/lxsora04/ora10/RMP2FD/arch

 

      The database has archiving enabled.  The upgrade process will need free

      disk space in the archive log destination(s) to generate archived logs to.

 

  9.  Check the Oracle Backup and Recovery User's Guide for information on how

      to manage an RMAN recovery catalog schema.

 

      If you are using a version of the recovery catalog schema that is older

      than that required by the RMAN client version, then you must upgrade the

      catalog schema.

 

      It is good practice to have the catalog schema the same or higher version

      than the RMAN client version you are using.

 

  ORACLE GENERATED FIXUP SCRIPT

  =============================

  All of the issues in database RMP2FD

  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

 

    SQL>@/app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

 

=============

AFTER UPGRADE

=============

 

  REQUIRED ACTIONS

  ================

  None

 

  RECOMMENDED ACTIONS

  ===================

  10. (AUTOFIXUP) If you use the -T option for the database upgrade, then run

      $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,

      to VALIDATE and UPGRADE any user tables affected by changes to

      Oracle-Maintained types.

 

      There are user tables dependent on Oracle-Maintained object types.

 

      If the -T option is used to set user tablespaces to READ ONLY during the

      upgrade, user tables in those tablespaces, that are dependent on

      Oracle-Maintained types, will not be automatically upgraded. If a type is

      evolved during the upgrade, any dependent tables need to be re-validated

      and upgraded to the latest type version AFTER the database upgrade

      completes.

 

  11. Upgrade the database time zone file using the DBMS_DST package.

 

      The database is using time zone file version 18 and the target 19 release

      ships with time zone file version 32.

 

      Oracle recommends upgrading to the desired (latest) version of the time

      zone file.  For more information, refer to "Upgrading the Time Zone File

      and Timestamp with Time Zone Data" in the 19 Oracle Database

      Globalization Support Guide.

 

  12. To identify directory objects with symbolic links in the path name, run

      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.

      Recreate any directory objects listed, using path names that contain no

      symbolic links.

 

      Some directory object path names may currently contain symbolic links.

 

      Starting in Release 18c, symbolic links are not allowed in directory

      object path names used with BFILE data types, the UTL_FILE package, or

      external tables.

 

  13. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the

      command:

 

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

 

      Oracle recommends gathering dictionary statistics after upgrade.

 

      Dictionary statistics provide essential information to the Oracle

      optimizer to help it find efficient SQL execution plans. After a database

      upgrade, statistics need to be re-gathered as there can now be tables

      that have significantly changed during the upgrade or new tables that do

      not have statistics gathered yet.

 

  14. Gather statistics on fixed objects after the upgrade and when there is a

      representative workload on the system using the command:

 

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

 

      This recommendation is given for all preupgrade runs.

 

      Fixed object statistics provide essential information to the Oracle

      optimizer to help it find efficient SQL execution plans.  Those

      statistics are specific to the Oracle Database release that generates

      them, and can be stale upon database upgrade.

 

      For information on managing optimizer statistics, refer to the 12.1.0.2

      Oracle Database SQL Tuning Guide.

 

  ORACLE GENERATED FIXUP SCRIPT

  =============================

  All of the issues in database RMP2FD

  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

 

    SQL>@/app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/postupgrade_fixups.sql

 

 

==================

PREUPGRADE SUMMARY

==================

  /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade.log

  /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

  /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/postupgrade_fixups.sql

 

Execute fixup scripts as indicated below:

 

Before upgrade:

 

Log into the database and execute the preupgrade fixups

@/app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

 

After the upgrade:

 

Log into the database and execute the postupgrade fixups

@/app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/postupgrade_fixups.sql

 

Preupgrade complete: 2019-08-13T11:30:14

 

For information, preupgrade.jar generates a log in the DIR parameter of its command line or in Oracle-base/cfgtoollogs/dbunique_name/preupgrade if $ORACLE_BASE has been defined and in Oracle-home/cfgtoollogs/dbunique_name/preupgrade if no $ORACLE_BASE has been defined. The log is /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade.log

 

Then run the preupgrade_fixups.sql script that preupgrade.jar generated (if you are upgrading a CDB, several preupgrade_fixups scripts will have been generated, each bearing a PDB name):

mobo12cR1@oracle:RMP2FD:~ #  sqlplus '/as sysdba'

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 11:32:35 2019

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

 

SQL> @/app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

 

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2019-08-13 11:30:10

 

For Source Database:     RMP2FD

Source Database Version: 12.1.0.2.0

For Upgrade to Version:  19.0.0.0.0

 

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

------  ------------------------  ----------  --------------------------------

    1.  parameter_obsolete        NO          Manual fixup recommended.

    2.  invalid_objects_exist     NO          Manual fixup recommended.

    3.  exclusive_mode_auth       NO          Manual fixup recommended.

    4.  case_insensitive_auth     NO          Manual fixup recommended.

    5.  dictionary_stats          YES         None.

    6.  trgowner_no_admndbtrg     YES         None.

    7.  parameter_deprecated      NO          Informational only.

                                              Further action is optional.

    8.  min_archive_dest_size     NO          Informational only.

                                              Further action is optional.

    9.  rman_recovery_version     NO          Informational only.

                                              Further action is optional.

 

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade.  To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

 

PL/SQL procedure successfully completed.

 

Oracle is kind enough to tell us which issues have been solved and which have not. For the latter, we must go to preupgrade_fixups.sql and look up the appropriate paragraph. Isn't it nice that we get an explanation on what to do with those outstanding issues? 

 

mobo12cR1@oracle:RMP2FD:/app/exploit/shell # view +/parameter_obsolete /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

 

 

So the first issue is the obsolete utl_file_dir parameter. Let's get rid of it.

SQL>  alter system reset utl_file_dir;

System altered.

 

The second issue is the invalid_objects_exist one.  In my case it was a couple of objects in a user schema that are not used. Still I ran utlrp.sql:

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

then had no more invalid objects.

 

The third issue is exclusive_mode_auth. Let's see what preupgrade_fixups.sql says about that:

mobo12cR1@oracle:RMP2FD:/app/exploit/shell # view +/exclusive_mode_auth /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

 

To identify those accounts, use the procudere labelled ""checking for accounts using case-insensitive password version" in https://docs.oracle.com/cd/F19136_01/upgrd/database-upgrade-guide.pdf

which says that you must make a note of those accounts, make  a change in sqnet.ora, run the upgrade to 19c then expire the password of those accounts.

SELECT  username,password_versions

FROM dba_users

where ( password_versions = '10G ' OR password_versions = '10G HTTP ' ) and username !='ANONYMOUS';

So I make a note that I'll have to expire the password of that user after the upgrade (ALTER USER username PASSWORD EXPIRE;).

Right now I must add a line in my sqlnet.ora:

 

mobo12cR1@oracle:RMP2FD:/app/mobo12cR1/oracle/product/12.1.0/network/admin # echo "SQLNET.ALLOWED_LOGON_VERSION_SERVER=10" >> sqlnet.ora ; tail sqlnet.ora

DIAG_ADR_ENABLED=on

ADR_BASE=/app/mobo12cR1/oracle/diag/clients

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

 

The next one is case_insensitive_auth

view +/case_insensitive_auth /app/mobo12cR1/oracle/cfgtoollogs/RMP2FD/preupgrade/preupgrade_fixups.sql

 

Since 12.2, case-sensitive passwords are compulsory.  So I must reset my sec_case_sensitive_logon=FALSE parameter

The dictionary_stats and trgowner_no_admndbtrg issues got fixed by this wonderful preupgrade_fixups.sql and the remaining issues are optional. So we can move on to the upagrde to 19c itself (the subject of another blog post maybe).

As Tim Hall would say: hope this helps!