Oracle 18c Database Upgrade From 12.2.0.1 to 18.3.0.0 Using DBUA

Description:-

There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade
  • Transportable Tablespaces
  • Datapump export/import
  • Oracle Streams
  • Oracle GoldenGate

Here I am going to choose DBUA to upgrade my database,

Backup 12c database using RMAN

rman target /

run
{
backup database plus archivelog;
backup current controlfile;
backup spfile;
}

Ensure backup is complete before upgrade.
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected
Empty Recycle bin.
SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.
Run Gather statistics to finish upgrade soon.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.
Stop the running 12c listener

lsnrctl stop LISTENER

Run preupgrade tool and fixups

Oracle strongly recommends that you run the Pre-Upgrade Information Tool before starting the upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is good practice to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.

Connect to 12.2.0.1 database environment as SYS,

 Pre-upgrade checks :
Run the preupgrade tool .
[oracle@ram admin]$ export ORACLE_SID=dbwr
[oracle@ram admin]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@ram admin]$ /u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0/db_1/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2019-02-12T00:53:56

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:     DBWR
Container Name:    DBWR
Container ID:      0
Version:           12.2.0.1.0
Compatible:        12.2.0
Blocksize:         8192
Platform:          Linux x86 64-bit
Timezone File:     26
Database log mode: ARCHIVELOG
Readonly:          FALSE
Edition:           EE

Oracle Component                   Upgrade Action   Current Status
----------------                   --------------   --------------
Oracle Server                      [to be upgraded]   VALID 
JServer JAVA Virtual Machine       [to be upgraded]   VALID 
Oracle XDK for Java                [to be upgraded]   VALID 
Real Application Clusters          [to be upgraded]   OPTION OFF 
Oracle Workspace Manager           [to be upgraded]   VALID 
OLAP Analytic Workspace            [to be upgraded]   VALID 
Oracle Label Security              [to be upgraded]   VALID 
Oracle Database Vault              [to be upgraded]   VALID 
Oracle Text                        [to be upgraded]   VALID 
Oracle XML Database                [to be upgraded]   VALID 
Oracle Java Packages               [to be upgraded]   VALID 
Oracle Multimedia                  [to be upgraded]   VALID 
Oracle Spatial                     [to be upgraded]   VALID 
Oracle OLAP API                    [to be upgraded]   VALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

None of the fixed object tables have had stats collected.

Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.

For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.

INFORMATION ONLY
================
2. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.

                         Min Size
Tablespace    Size       For Upgrade
----------   --------    -----------
SYSAUX        460 MB      675 MB
SYSTEM        800 MB      1242 MB
TEMP          32 MB       150 MB
UNDOTBS1      65 MB       439 MB

Minimum tablespace sizes for upgrade are estimates.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DBWR
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
3. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 26 and the target 18.0.0.0.0
release ships with time zone file version 31.

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 18.0.0.0.0 Oracle Database
Globalization Support Guide.

4. (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.

5. 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.2.0.1
Oracle Database SQL Tuning Guide.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DBWR
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-02-12T00:53:56
Run the preupgrade_fixup.sql
SQL> @/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-12 00:53:50

For Source Database: DBWR
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0

Preup                               Preupgrade
Action                              Issue Is
Number      Preupgrade  Check Name  Remedied   Further DBA Action
------     -----------------------  ---------  -------------------
1.            pre_fixed_objects      YES                None.
2.            tablespaces_info       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.

UPGRADE:

At this stage, we are still connected to 12c database which is up and running.

Now run the dbua utility from 12c oracle_home location.

[oracle@ram ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0/db_1
[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/bin
[oracle@ram bin]$ ./dbua

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

select the Upgrade Oracle Database option, then click the “Next” button.

If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

Amend the upgrade options if necessary, then click the “Next” button.

Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

 If the database is using the 12c listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, you can do that now too and click the “Next” button.

Select the desired management options, then click the “Next” button

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/app/oracle/cfgtoollogs/dbwr/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 18.0.0.0.0 Build: 1
Generated on:            2019-02-12 00:53:56

For Source Database:     DBWR
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  18.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  ------------------
    3.  old_time_zones_exist      YES         None.
    4.  post_dictionary           YES         None.
    5.  post_fixed_objects        NO          Informational only.
                                              Further action is                             optional.

The fixup scripts have been run and resovled 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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.

Session altered.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION            NAME          OPEN_MODE
------------     ---------      -----------
18.0.0.0.0         DBWR          READ WRITE
SQL> SELECT * FROM v$timezone_file;

FILENAME              VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat         31          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
    31
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                         FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/?                   Twitter: https://twitter.com/hariprasathdba

Oracle 19c Database Upgrade From 12.2.0.1 to 19.2.0.0 Using DBUA

Description:-

There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade
  • Transportable Tablespaces
  • Data Pump export/import
  • Oracle Streams
  • Oracle GoldenGate

Here I am going to choose DBUA to upgrade my database,

[oracle@standby dbs]$ export ORACLE_SID=upgr
[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:02:00 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2449473536 bytes
Fixed Size 8623640 bytes
Variable Size 654313960 bytes
Database Buffers 1778384896 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.

Ensure backup is complete before upgrade.

Empty Recycle bin.

Run Gather statistics to finish upgrade soon.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected


SQL> PURGE DBA_RECYCLEBIN ;

DBA_Recyclebin purged.


SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.


SQL> !
Stop the running 12c listener

lsnrctl stop LISTENER

Run preupgrade tool and fixups

Oracle strongly recommends that you run the Pre-Upgrade Information Tool before starting the upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is good practice to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.

Connect to 12.2.0.1 database environment as SYS,

 Pre-upgrade checks :
Run the preupgrade tool .
[oracle@standby dbs]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2019-03-21T20:07:45

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:      UPGR
Container Name:     upgr
Container ID:       0
Version:            12.2.0.1.0
DB Patch Level:     No Patch Bundle applied
Compatible:         12.2.0
Blocksize:          8192
Platform:           Linux x86 64-bit
Timezone File:      26
Database log mode:  NOARCHIVELOG
Readonly:           FALSE
Edition:            EE

Oracle Component                 Upgrade Action         Current Status
----------------                 --------------         --------------
Oracle Server                    [to be upgraded]         VALID
JServer JAVA Virtual Machine     [to be upgraded]         VALID
Oracle XDK for Java              [to be upgraded]         VALID
Real Application Clusters        [to be upgraded]         OPTION OFF
Oracle Workspace Manager         [to be upgraded]         VALID
OLAP Analytic Workspace          [to be upgraded]         VALID
Oracle Label Security            [to be upgraded]         VALID
Oracle Database Vault            [to be upgraded]         VALID
Oracle Text                      [to be upgraded]         VALID
Oracle XML Database              [to be upgraded]         VALID
Oracle Java Packages             [to be upgraded]         VALID
Oracle Multimedia                [to be upgraded]         VALID
Oracle Spatial                   [to be upgraded]         VALID
Oracle OLAP API                  [to be upgraded]         VALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

None of the fixed object tables have had stats collected.

Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.

For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.

INFORMATION ONLY
================
2. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.

Min Size
Tablespace      Size      For Upgrade
----------   ----------  -----------
SYSAUX         490 MB     509 MB
SYSTEM         800 MB     914 MB
TEMP           131 MB     150 MB
UNDOTBS1       70 MB      439 MB

Minimum tablespace sizes for upgrade are estimates.

3. 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 UPGR
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 26 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.

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

6. (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.

7. 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.2.0.1
Oracle Database SQL Tuning Guide.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database UPGR
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-03-21T20:07:46

Run the preupgrade_fixup.sql

[oracle@standby dbs]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:13:03 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @/u01/app/oracle/cfgtoollogs/upgr/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-03-21 20:07:39

For Source Database: UPGR
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup                            Preupgrade
Action                           Issue Is
Number   Preupgrade Check Name   Remedied      Further DBA Action
------ ------------------------  ----------   -------------------------
1.      pre_fixed_objects        YES            None.
2.      tablespaces_info         NO             Informational only.
                                            Further action is optional.
3.      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.
UPGRADE:

At this stage, we are still connected to 12c database which is up and running.

Now run the dbua utility from 12c oracle_home location.

[oracle@standby ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@standby ~]$ export ORACLE_SID=upgr
[oracle@standby ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@standby ~]$ dbua

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

select the Upgrade Oracle Database option, then click the “Next” button.

If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

Amend the upgrade options if necessary, then click the “Next” button.

Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

If the database is using the 12c listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, you can do that now too and click the “Next” button

Select the desired management options, then click the “Next” button

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-03-21 20:07:45

For Source Database: UPGR
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup                            Preupgrade
Action                           Issue Is
Number Preupgrade Check Name     Remedied      Further DBA Action
------ ------------------------ ---------- ----------------------------
4.     old_time_zones_exist     YES                None.
5.     dir_symlinks             YES                None.
6.     post_dictionary          YES                None.
7.     post_fixed_objects       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 upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_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.


Session altered.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION            NAME        OPEN_MODE
----------------- --------- --------------------
19.0.0.0.0         UPGR        READ WRITE

SQL> SELECT * FROM v$timezone_file;

FILENAME              VERSION    CON_ID
-------------------- ---------- ----------
timezlrg_32.dat        32         0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
   32
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

Oracle 18c Database Upgrade From 12.1.0.2 to 18.3.0.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 12.1.0.2 to 18.3.0.0.0 :-

Description:- 

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.

Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.3 and 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

Ensure backup is complete before upgrade.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 Pre-upgrade checks :

Run the preupgrade tool .

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.
This tool has reside in new oracle home.
/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar

[oracle@test db_1]$ mkdir /u01/preupgrade

[oracle@test db_1]$ export ORACLE_SID=orcl
[oracle@test db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@test db_1]$ /u01/app/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/
===================
PREUPGRADE SUMMARY
===================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/postupgrade_fixups.sql

Preupgrade complete: 2019-02-06T04:42:43

After run the above tool we will get below sql files.

[oracle@test db_1]$ cd /u01/preupgrade
[oracle@test u01/preupgrade]$ ls -lrt

total 660
-rw-r--r-- 1 oracle oinstall  14846 Feb  8 04:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 7963 Feb 6 04:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 6 04:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 14383 Feb 6 04:42 parameters.properties
-rw-r--r-- 1 oracle oinstall 83854 Feb 6 04:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 50172 Feb 6 04:42 components.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 6 04:42 upgrade
-rw-r--r-- 1 oracle oinstall 2 Feb 6 04:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 9181 Feb 6 04:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8875 Feb 6 04:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8120 Feb 6 04:42 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@test ]$ export ORACLE_SID=orcl

[oracle@test ]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[oracle@test ]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@test ]$ cd /u01/preupgrade

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:38

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. apex_manual_upgrade NO Manual fixup recommended.
2. mv_refresh NO Informational only.
Further action is optional.
3. tablespaces_info 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.

Check Timezone version :

Check if target database’s time zone version is lower than the source database time zone version.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                VALUE
--------------------------- ----------

DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION     0
DST_UPGRADE_STATE            NONE

Upgrade Database from 12.1.0.2 to 18c :-

Shutdown the 12c database:

[oracle@test ~]$ sqlplus / as sysdba 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy the parameter and password file from the 12c home to the new 18c home.

[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/spfileorcl.ora /u02/app/oracle/product/18.3.0/db_1/dbs/
[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/orapworcl /u02/app/oracle/product/18.3.0/db_1/dbs/

Set new ORACLE_HOME for 18c location and startup database in upgrade mode :

[oracle@test ]$ export ORACLE_SID=orcl
[oracle@test ]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1/
[oracle@test ]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin
[oracle@test db_1]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 6 22:02:24 2019
Version 18.3.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Run catcl.pl  script:

catctl.pl is introduce in Oracle Database 12c, Parallel Upgrade Utility replaces the SQL Upgrade Utility catupgrd.sql. With Parallel Upgrade Utility, we can run upgrade scripts and processes in parallel. Using this script will increase our server CPU capacity and reduce the upgrade time.

-n is used for parallelism (new feature on oracle 12c)

You can run the dbupgrade using either of the following commands.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

$ORACLE_HOME/bin/dbupgrade

[oracle@test db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

Argument list for [/u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = /u02/app/oracle/product/18.3.0/db_1//rdbms/admin
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [18.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]

/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/orahome = [/u02/app/oracle/product/18.3.0/db_1/]
/u02/app/oracle/product/18.3.0/db_1//bin/orabasehome = [/u02/app/oracle/product/18.3.0/db_1/]
catctlGetOrabase = [/u02/app/oracle/product/18.3.0/db_1/]

Analyzing file /u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_37104.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = orcl
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-108] Start Time:[2019_02_06 22:05:38]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 45s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 33s
Restart Phase #:2 [orcl] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 19s
Restart Phase #:4 [orcl] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 10s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 9s
Restart Phase #:8 [orcl] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:66 Time: 31s
Restart Phase #:10 [orcl] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 53s
Restart Phase #:12 [orcl] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:94 Time: 14s
Restart Phase #:14 [orcl] Files:1 Time: 0s
Parallel Phase #:15 [orcl] Files:117 Time: 26s
Restart Phase #:16 [orcl] Files:1 Time: 0s
Serial Phase #:17 [orcl] Files:17 Time: 3s
Restart Phase #:18 [orcl] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 24s
Restart Phase #:20 [orcl] Files:1 Time: 1s
Serial Phase #:21 [orcl] Files:3 Time: 6s
Restart Phase #:22 [orcl] Files:1 Time: 0s
Parallel Phase #:23 [orcl] Files:24 Time: 137s
Restart Phase #:24 [orcl] Files:1 Time: 0s
Parallel Phase #:25 [orcl] Files:12 Time: 79s
Restart Phase #:26 [orcl] Files:1 Time: 0s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:3 Time: 3s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 0s
Restart Phase #:32 [orcl] Files:1 Time: 1s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:288 Time: 19s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 1s
Serial Phase #:38 [orcl] Files:2 Time: 3s
Restart Phase #:39 [orcl] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 45s
Restart Phase #:41 [orcl] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 91s
Restart Phase #:43 [orcl] Files:1 Time: 0s
Parallel Phase #:44 [orcl] Files:11 Time: 13s
Restart Phase #:45 [orcl] Files:1 Time: 0s
Parallel Phase #:46 [orcl] Files:3 Time: 1s
Restart Phase #:47 [orcl] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 3s
Restart Phase #:49 [orcl] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 19s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 0s
Restart Phase #:52 [orcl] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 439s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 0s
Serial Phase #:56 [orcl] Files:3 Time: 8s
Serial Phase #:57 [orcl] Files:3 Time: 4s
Parallel Phase #:58 [orcl] Files:9 Time: 3s
Parallel Phase #:59 [orcl] Files:25 Time: 4s
Serial Phase #:60 [orcl] Files:4 Time: 5s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:31 Time: 3s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 3s
Serial Phase #:65 [orcl] Files:2 Time: 13s
Serial Phase #:66 [orcl] Files:3 Time: 31s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 1s
Serial Phase #:69 [orcl] Files:1 Time: 0s
Parallel Phase #:70 [orcl] Files:2 Time: 44s
Serial Phase #:71 [orcl] Files:1 Time: 44s
Restart Phase #:72 [orcl] Files:1 Time: 0s
Parallel Phase #:73 [orcl] Files:2 Time: 8s
Serial Phase #:74 [orcl] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:75 [orcl] Files:1 Time: 0s
Serial Phase #:77 [orcl] Files:1 Time: 29s
Serial Phase #:78 [orcl] Files:1 Time: 1s
Restart Phase #:79 [orcl] Files:1 Time: 0s
Serial Phase #:80 [orcl] Files:1 Time: 15s
Restart Phase #:81 [orcl] Files:1 Time: 0s
Parallel Phase #:82 [orcl] Files:3 Time: 53s
Restart Phase #:83 [orcl] Files:1 Time: 0s
Serial Phase #:84 [orcl] Files:1 Time: 3s
Restart Phase #:85 [orcl] Files:1 Time: 0s
Serial Phase #:86 [orcl] Files:1 Time: 5s
Restart Phase #:87 [orcl] Files:1 Time: 1s
Parallel Phase #:88 [orcl] Files:4 Time: 60s
Restart Phase #:89 [orcl] Files:1 Time: 0s
Serial Phase #:90 [orcl] Files:1 Time: 0s
Restart Phase #:91 [orcl] Files:1 Time: 1s
Serial Phase #:92 [orcl] Files:2 Time: 5s
Restart Phase #:93 [orcl] Files:1 Time: 0s
Serial Phase #:94 [orcl] Files:1 Time: 1s
Restart Phase #:95 [orcl] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [orcl] Files:1 Time: 11s
Restart Phase #:97 [orcl] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [orcl] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:99 [orcl] Files:1 Time: 189s
******************* Migration ******************
Serial Phase #:100 [orcl] Files:1 Time: 38s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 0s
Serial Phase #:103 [orcl] Files:1 Time: 68s
***************** Post Upgrade *****************
Serial Phase #:104 [orcl] Files:1 Time: 13s
**************** Summary report ****************
Serial Phase #:105 [orcl] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 0s
Serial Phase #:108 [orcl] Files:1 Time: 28s

------------------------------------------------------
Phases [0-108] End Time:[2019_02_06 22:36:13]
------------------------------------------------------

Grand Total Time: 1836s

LOG FILES: (/home/oracle//catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle//upg_summary.log

Grand Total Upgrade Time: [0d:0h:30m:36s]

Execute Post-Upgrade Status Tool, utlu122s.sql.

[oracle@test ]$ cd $ORACLE_HOME/rdbms/admin

SQL> @utlu122s.sql

Oracle Database Release 18 Post-Upgrade Status Tool 02-06-2019 23:16:2

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server UPGRADED 18.3.0.0.0 00:11:38
JServer JAVA Virtual Machine VALID 18.3.0.0.0 00:04:25
Oracle XDK UPGRADED 18.3.0.0.0 00:00:59
Oracle Database Java Packages UPGRADED 18.3.0.0.0 00:00:10
OLAP Analytic Workspace UPGRADED 18.3.0.0.0 00:00:14
Oracle Label Security UPGRADED 18.3.0.0.0 00:00:06
Oracle Database Vault UPGRADED 18.3.0.0.0 00:00:16
Oracle Text UPGRADED 18.3.0.0.0 00:00:33
Oracle Workspace Manager UPGRADED 18.3.0.0.0 00:00:31
Oracle Real Application Clusters UPGRADED 18.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 18.3.0.0.0 00:01:13
Oracle Multimedia UPGRADED 18.3.0.0.0 00:01:36
Spatial UPGRADED 18.3.0.0.0 00:02:52
Oracle OLAP API UPGRADED 18.3.0.0.0 00:00:10
Upgrade Datapatch 00:01:10
Final Actions 00:03:46
Post Upgrade 00:00:13
Post Upgrade Datapatch 00:00:15

Total Upgrade Time: 00:29:10

Database time zone version is 18. It is older than current release time
zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /home/oracle//upg_summary.log
SQL> @catuppst.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $
SQL> Rem
SQL> Rem catuppst.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem        catuppst.sql - CATalog UPgrade PoST-upgrade actions
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem        This post-upgrade script performs remaining upgrade actions that
SQL> Rem        do not require that the database be open in UPGRADE mode.
SQL> Rem        Automatically apply the latest PSU.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem        You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/catuppst.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql
SQL> Rem    SQL_PHASE: UPGRADE
SQL> Rem    SQL_STARTUP_MODE: UPGRADE
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    SQL_CALLING_FILE: rdbms/admin/catupgrd.sql
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem

Execute utlrp.sql script to compile  invalid objects.

SQL> @utlrp.sql

Session altered.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-06 23:24:56

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-02-06 23:31:50

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

Set COMPATIBLE parameter :

SQL> show parameter compatible;

NAME              TYPE    VALUE
----------------- -------- ---------
compatible        string  12.1.0.2.0
noncdb_compatible boolean FALSE


SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

System altered.

Restart the database :

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> show parameter compatible;

NAME              TYPE     VALUE
----------------- -------- ---------
compatible        string   18.0.0
noncdb_compatible boolean  FALSE

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Upgrade Time Zone version :

Startup database in Upgrade mode.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Start the upgrade window

SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL> 2 3 4 5 6 7 8 9
l_tz_version=31
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Shutdown the database and startup in normal mode.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Upgrade Timezone version :

SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL> 2 3 4 5 6 7 8 9
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

Check time zone file version :

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- -------
timezlrg_31.dat      31         0


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18

If they differ after an upgrade then updating registry$database can be done by

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> commit;

Commit complete.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
31

Reference: Steps to Upgrade Time Zone File and Timestamp with Time Zone Data

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:48

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup                          Preupgrade
Action                         Issue Is
Number Preupgrade Check Name    Remedied   Further DBA Action
------ ------------------------ ---------- ------------------
4. depend_usr_tables            YES        None.
5. old_time_zones_exist         YES        None.
6. post_dictionary              YES        None.
7. post_fixed_objects           NO         Informational only.Further action is optional.

The fixup scripts have been run and resovled 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 upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_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.

Session altered.

Check Database version and status.

SQL> select name,version,open_mode from v$database,v$instance;

NAME      VERSION           OPEN_MODE
--------- ----------------- --------------------
ORCL      18.0.0.0.0        READ WRITE

Reference : Oracle DB 18c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 18c (Doc ID 2418045.1)

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

 

 

Oracle 18c Database Upgrade From 12.2.0.1 to 18.3.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 12.2.0.1 to 18.3.0.0.0 :-

Description:- 

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.

Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.3 and 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Take RMAN backups before upgrade.

    rman target / 

    run
    {
        ALLOCATE CHANNEL chan_name TYPE DISK;
        BACKUP DATABASE FORMAT '/u01%U' TAG before_upgrade;
        BACKUP CURRENT CONTROLFILE FORMAT '/u01/control%U';
    }

Ensure backup is complete.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Run the preupgrade tool:

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.
This tool has reside in new oracle home.
/u01/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 7 05:03:08 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 671090984 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> 

[oracle@ram db_1]$ export ORACLE_SID=orcldb
[oracle@ram db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@ram db_1]$ /u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade1/
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade1/preupgrade.log
  /u01/preupgrade1/preupgrade_fixups.sql
  /u01/preupgrade1/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/preupgrade1/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/preupgrade1/postupgrade_fixups.sql

Preupgrade complete: 2019-02-08T22:48:35

After run the above tool we will get below sql files.

[oracle@ram db_1]$ cd /u01/preupgrade1/
[oracle@ram preupgrade1]$ ls -lrt
total 628
drwxr-xr-x. 3 oracle oinstall 4096 Feb 8 22:48 oracle
-rw-r--r--. 1 oracle oinstall 7963 Feb 8 22:48 preupgrade_driver.sql
-rw-r--r--. 1 oracle oinstall 14846 Feb 8 22:48 dbms_registry_extended.sql
-rw-r--r--. 1 oracle oinstall 422048 Feb 8 22:48 preupgrade_package.sql
-rw-r--r--. 1 oracle oinstall 14383 Feb 8 22:48 parameters.properties
-rw-r--r--. 1 oracle oinstall 83854 Feb 8 22:48 preupgrade_messages.properties
drwxr-xr-x. 3 oracle oinstall 4096 Feb 8 22:48 upgrade
-rw-r--r--. 1 oracle oinstall 50172 Feb 8 22:48 components.properties
-rw-r--r--. 1 oracle oinstall 2 Feb 8 22:48 checksBuffer.tmp
-rw-r--r--. 1 oracle oinstall 5838 Feb 8 22:48 preupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 7593 Feb 8 22:48 postupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 5641 Feb 8 22:48 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@ram ~]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/bin:$PATH
[oracle@ram ~]$ cd /u01/preupgrade1/
[oracle@ram preupgrade1]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 8 22:57:55 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @/u01/preupgrade1/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-08 22:48:32

For Source Database: ORCLDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0

Preup                            Preupgrade
Action                           Issue Is
Number   Preupgrade Check Name   Remedied         Further DBA Action
------ ------------------------  ----------    ------------------------
  1.        dictionary_stats      YES                  None.
  2.        tablespaces_info      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.

Check Timezone version :

Check if target database’s time zone version is lower than the source database time zone version.

SQL> SELECT version FROM v$timezone_file;

VERSION
-------
26

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME            VALUE
------------------------ -----
DST_PRIMARY_TT_VERSION   26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE        NONE

Upgrade Database from 12.2.0.1 to 18c :-

Shutdown the 12c database:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy the parameter and password file from the 12c home to the new 18c home.

[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@ram dbs]$ ls -lrth
total 20K
-rwxrwxr-x. 1 oracle oinstall 3.1K May 15 2015 init.ora
-rwxrwxr-x. 1 oracle oinstall 24 Feb 7 04:01 lkORCLDB
-rw-r-----. 1 oracle oinstall 3.5K Feb 7 04:06 orapworcldb
-rwxrwxr-x. 1 oracle oinstall 3.5K Feb 8 22:44 spfileorcldb.ora
-rwxrwxr-x. 1 oracle oinstall 1.6K Feb 8 23:06 hc_orcldb.dat
[oracle@ram dbs]$ cp spfileorcldb.ora orapworcldb /u01/app/oracle/product/18.0.0/db_1/dbs/

Set new ORACLE_HOME for 18c location and startup database in upgrade mode :

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0/db_1
[oracle@ram ~]$ export PATH=/u01/app/oracle/product/18.0.0/db_1/bin:$PATH
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 8 23:10:39 2019
Version 18.3.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Run the dbupgrade utility :

[oracle@ram ~]$ /u01/app/oracle/product/18.0.0/db_1/bin/dbupgrade -n 4 -l /u01/

Argument list for [/u01/app/oracle/product/18.0.0/db_1/rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /u01/
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [18.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]


/u01/app/oracle/product/18.0.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/db_1]
/u01/app/oracle/product/18.0.0/db_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/db_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/db_1]

Analyzing file /u01/app/oracle/product/18.0.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/catupgrd_catcon_93556.lst]

catcon::set_log_file_base_path: catcon: See [/u01/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/catupgrd_*.lst] files for spool files, if any


Number of Cpus = 1
Database Name = orcldb
DataBase Version = 12.2.0.1.0
Parallel SQL Process Count = 4
Components in [orcldb]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-108] Start Time:[2019_02_08 23:13:44]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcldb] Files:1 Time: 26s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcldb] Files:5 Time: 64s
Restart Phase #:2 [orcldb] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcldb] Files:19 Time: 43s
Restart Phase #:4 [orcldb] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcldb] Files:7 Time: 28s
***************** Catproc Start ****************
Serial Phase #:6 [orcldb] Files:1 Time: 18s
***************** Catproc Types ****************
Serial Phase #:7 [orcldb] Files:2 Time: 19s
Restart Phase #:8 [orcldb] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcldb] Files:66 Time: 52s
Restart Phase #:10 [orcldb] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [orcldb] Files:1 Time: 134s
Restart Phase #:12 [orcldb] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcldb] Files:94 Time: 22s
Restart Phase #:14 [orcldb] Files:1 Time: 1s
Parallel Phase #:15 [orcldb] Files:117 Time: 39s
Restart Phase #:16 [orcldb] Files:1 Time: 0s
Serial Phase #:17 [orcldb] Files:17 Time: 5s
Restart Phase #:18 [orcldb] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [orcldb] Files:32 Time: 47s
Restart Phase #:20 [orcldb] Files:1 Time: 0s
Serial Phase #:21 [orcldb] Files:3 Time: 13s
Restart Phase #:22 [orcldb] Files:1 Time: 1s
Parallel Phase #:23 [orcldb] Files:24 Time: 340s
Restart Phase #:24 [orcldb] Files:1 Time: 1s
Parallel Phase #:25 [orcldb] Files:12 Time: 208s
Restart Phase #:26 [orcldb] Files:1 Time: 1s
Serial Phase #:27 [orcldb] Files:1 Time: 0s
Serial Phase #:28 [orcldb] Files:3 Time: 4s
Serial Phase #:29 [orcldb] Files:1 Time: 0s
Restart Phase #:30 [orcldb] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcldb] Files:1 Time: 0s
Restart Phase #:32 [orcldb] Files:1 Time: 1s
Serial Phase #:34 [orcldb] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcldb] Files:288 Time: 38s
Serial Phase #:36 [orcldb] Files:1 Time: 0s
Restart Phase #:37 [orcldb] Files:1 Time: 0s
Serial Phase #:38 [orcldb] Files:2 Time: 12s
Restart Phase #:39 [orcldb] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcldb] Files:3 Time: 76s
Restart Phase #:41 [orcldb] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcldb] Files:13 Time: 226s
Restart Phase #:43 [orcldb] Files:1 Time: 1s
Parallel Phase #:44 [orcldb] Files:11 Time: 19s
Restart Phase #:45 [orcldb] Files:1 Time: 1s
Parallel Phase #:46 [orcldb] Files:3 Time: 3s
Restart Phase #:47 [orcldb] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [orcldb] Files:1 Time: 5s
Restart Phase #:49 [orcldb] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcldb] Files:1 Time: 5s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcldb] Files:1 Time: 0s
Restart Phase #:52 [orcldb] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcldb] Files:2 Time: 334s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcldb] Files:1 Time: 1s
Serial Phase #:56 [orcldb] Files:3 Time: 6s
Serial Phase #:57 [orcldb] Files:3 Time: 7s
Parallel Phase #:58 [orcldb] Files:9 Time: 5s
Parallel Phase #:59 [orcldb] Files:25 Time: 13s
Serial Phase #:60 [orcldb] Files:4 Time: 15s
Serial Phase #:61 [orcldb] Files:1 Time: 0s
Serial Phase #:62 [orcldb] Files:31 Time: 5s
Serial Phase #:63 [orcldb] Files:1 Time: 0s
Parallel Phase #:64 [orcldb] Files:6 Time: 5s
Serial Phase #:65 [orcldb] Files:2 Time: 36s
Serial Phase #:66 [orcldb] Files:3 Time: 54s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcldb] Files:1 Time: 1s
Serial Phase #:69 [orcldb] Files:1 Time: 1s
Parallel Phase #:70 [orcldb] Files:2 Time: 58s
Serial Phase #:71 [orcldb] Files:1 Time: 90s
Restart Phase #:72 [orcldb] Files:1 Time: 0s
Parallel Phase #:73 [orcldb] Files:2 Time: 15s
Serial Phase #:74 [orcldb] Files:2 Time: 2s
***************** Upgrading SDO ****************
Restart Phase #:75 [orcldb] Files:1 Time: 0s
Serial Phase #:77 [orcldb] Files:1 Time: 63s
Serial Phase #:78 [orcldb] Files:1 Time: 2s
Restart Phase #:79 [orcldb] Files:1 Time: 0s
Serial Phase #:80 [orcldb] Files:1 Time: 59s
Restart Phase #:81 [orcldb] Files:1 Time: 1s
Parallel Phase #:82 [orcldb] Files:3 Time: 85s
Restart Phase #:83 [orcldb] Files:1 Time: 1s
Serial Phase #:84 [orcldb] Files:1 Time: 5s
Restart Phase #:85 [orcldb] Files:1 Time: 1s
Serial Phase #:86 [orcldb] Files:1 Time: 8s
Restart Phase #:87 [orcldb] Files:1 Time: 0s
Parallel Phase #:88 [orcldb] Files:4 Time: 114s
Restart Phase #:89 [orcldb] Files:1 Time: 0s
Serial Phase #:90 [orcldb] Files:1 Time: 1s
Restart Phase #:91 [orcldb] Files:1 Time: 1s
Serial Phase #:92 [orcldb] Files:2 Time: 11s
Restart Phase #:93 [orcldb] Files:1 Time: 0s
Serial Phase #:94 [orcldb] Files:1 Time: 1s
Restart Phase #:95 [orcldb] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [orcldb] Files:1 Time: 19s
Restart Phase #:97 [orcldb] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [orcldb] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:99 [orcldb] Files:1 Time: 153s
******************* Migration ******************
Serial Phase #:100 [orcldb] Files:1 Time: 0s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [orcldb] Files:1 Time: 1s
Serial Phase #:102 [orcldb] Files:1 Time: 0s
Serial Phase #:103 [orcldb] Files:1 Time: 80s
***************** Post Upgrade *****************
Serial Phase #:104 [orcldb] Files:1 Time: 5s
**************** Summary report ****************
Serial Phase #:105 [orcldb] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [orcldb] Files:1 Time: 0s
Serial Phase #:107 [orcldb] Files:1 Time: 0s
Serial Phase #:108 [orcldb] Files:1 Time: 23s

------------------------------------------------------
Phases [0-108] End Time:[2019_02_08 23:59:27]
------------------------------------------------------

Grand Total Time: 2746s

LOG FILES: (/u01//catupgrd*.log)

Upgrade Summary Report Located in:
/u01//upg_summary.log

Grand Total Upgrade Time: [0d:0h:45m:46s]

Execute Post-Upgrade Status Tool, utlu122s.sql.

SQL> @utlu122s.sql


Oracle Database Release 18 Post-Upgrade Status Tool 02-09-2019 00:16:5

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server                 UPGRADED 18.3.0.0.0    00:24:12
JServer JAVA Virtual Machine  VALID 18.3.0.0.0       00:01:38
Oracle XDK                    UPGRADED 18.3.0.0.0    00:00:45
Oracle Database Java Packages UPGRADED 18.3.0.0.0    00:00:19
OLAP Analytic Workspace       UPGRADED 18.3.0.0.0    00:00:20
Oracle Label Security         UPGRADED 18.3.0.0.0    00:00:09
Oracle Database Vault         UPGRADED 18.3.0.0.0    00:00:30
Oracle Text                   UPGRADED 18.3.0.0.0    00:00:53
Oracle Workspace Manager      UPGRADED 18.3.0.0.0    00:00:55
Oracle Real Application Clusters UPGRADED 18.3.0.0.0 00:00:00
Oracle XML Database           UPGRADED 18.3.0.0.0    00:02:25
Oracle Multimedia             UPGRADED 18.3.0.0.0    00:02:44
Spatial                       UPGRADED 18.3.0.0.0    00:05:50
Oracle OLAP API               UPGRADED 18.3.0.0.0    00:00:17
Upgrade Datapatch                                    00:02:27
Final Actions                                        00:02:33
Post Upgrade                                         00:00:04
Post Upgrade Datapatch                               00:00:24

Total Upgrade Time: 00:44:17

Database time zone version is 26. It is older than current release time
zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u01//upg_summary.log

00:16:53 SQL>
00:16:53 SQL> @catuppst.sql 
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem catuppst.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem This post-upgrade script performs remaining upgrade actions that
00:21:32 SQL> Rem do not require that the database be open in UPGRADE mode.
00:21:32 SQL> Rem Automatically apply the latest PSU.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem You must be connected AS SYSDBA to run this script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catuppst.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql
00:21:32 SQL> Rem SQL_PHASE: UPGRADE
00:21:32 SQL> Rem SQL_STARTUP_MODE: UPGRADE
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem SQL_CALLING_FILE: rdbms/admin/catupgrd.sql
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem wesmith 08/07/17 - bug 22187143: fix dba_part/subpart_key_columns_v$
00:21:32 SQL> Rem cmlim 05/01/17 - bug 25248712 - Move the copy for bug 19651064
00:21:32 SQL> Rem from catuppst.sql to a1201000.sql
00:21:32 SQL> Rem cmlim 04/08/17 - bug 25248712: cut down on # of parallel slaves
00:21:32 SQL> Rem spawned
00:21:32 SQL> Rem frealvar 03/10/17 - move the gather_fixed_objects_stats code for Bug
00:21:32 SQL> Rem 14258301 as a preupgrade check
00:21:32 SQL> Rem raeburns 03/08/17 - Bug 25616909: Use UPGRADE for SQL_PHASE
00:21:32 SQL> Rem stanaya 12/08/16 - Bug-25191487 : adding sql metadata
00:21:32 SQL> Rem pyam 11/10/16 - 70732: add catalog app upgrade for post-shutdown
00:21:32 SQL> Rem anighosh 09/15/16 - Bug 24669189: Cleanup utlmmig replacement tables
00:21:32 SQL> Rem vperiwal 07/07/16 - 23726702: remove ORA-65173
00:21:32 SQL> Rem cmlim 06/06/16 - bug 23215791: add more DBUA_TIMESTAMPS during db
00:21:32 SQL> Rem upgrades
00:21:32 SQL> Rem anupkk 04/03/16 - Bug 22917286: Moved call to olstrig.sql to
00:21:32 SQL> Rem olsdbmig.sql
00:21:32 SQL> Rem raeburns 02/29/16 - Bug 22820096: revert ALTER TYPE to default
00:21:32 SQL> Rem CASCADE
00:21:32 SQL> Rem rmorant 02/11/16 - Bug22340563 add parallel hint
00:21:32 SQL> Rem atomar 02/04/16 - move aq action to release specific script
00:21:32 SQL> Rem raeburns 12/09/15 - Bug 22175911: add SERVEROUTPUT OFF after
00:21:32 SQL> Rem catuptabdata.sql
00:21:32 SQL> Rem rmorant 11/27/15 - bug22271668 add append hint
00:21:32 SQL> Rem welin 11/11/15 - Bug 21099929: 12.2 cleanup
..............................
.............................
............................
00:21:32 SQL> Rem rburns 10/23/06 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> @@?/rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/2 2017/05/28 22:46:11 stanaya Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2013, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem sqlsessstart.sql - SQL session start
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem Any commands which should be run at the start of all oracle
00:21:32 SQL> Rem supplied scripts.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem See sqlsessend.sql for the corresponding end script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_PHASE: SQLSESSSTART
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
00:21:32 SQL> Rem surman 03/08/13 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

Elapsed: 00:00:00.00
00:21:32 SQL> 
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> Rem Assure CHAR semantics are not used in the dictionary
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

Elapsed: 00:00:00.00
00:21:32 SQL> 
00:21:32 SQL> 
00:21:32 SQL> 
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> Rem Begin Catalog App Upgrade for Post-shutdown. All additional code
00:21:32 SQL> Rem should be placed after this.
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> @@catappupgbeg2.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/catappupgbeg2.sql /main/4 2017/09/29 14:04:55 pyam Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem catappupgbeg2.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2016, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem catappupgbeg2.sql - <one-line expansion of the name>
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem <short description of component this file declares/defines>
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem <other useful comments, qualifications, etc.>
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catappupgbeg2.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE:
00:21:32 SQL> Rem SQL_PHASE: INSTALL
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem SQL_CALLING_FILE:
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem pyam 09/27/17 - Bug 26856671: alter system with scope=memory
00:21:32 SQL> Rem pyam 08/28/17 - Bug 25857770: set _enable_cdb_upgrade_capture
00:21:32 SQL> Rem only during upgrade
00:21:32 SQL> Rem pyam 06/09/17 - RTI 19984265: catch no_data_found
00:21:32 SQL> Rem pyam 11/17/16 - Catalog Application Upgrade Begin Part 2
00:21:32 SQL> Rem pyam 11/17/16 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> @@?/rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/2 2017/05/28 22:46:11 stanaya Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2013, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem sqlsessstart.sql - SQL session start
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem Any commands which should be run at the start of all oracle
00:21:32 SQL> Rem supplied scripts.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem See sqlsessend.sql for the corresponding end script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_PHASE: SQLSESSSTART
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem END SQL_FILE_METADATA
......................
......................
......................
00:21:52 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:52 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
00:21:52 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
00:21:52 SQL> Rem SQL_PHASE: NOTHING
00:21:52 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:52 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:52 SQL> Rem SQL_CALLING_FILE: NONE
00:21:52 SQL> Rem END SQL_FILE_METADATA
00:21:52 SQL> Rem
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem EM End
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Do Java longer identifiers name translation, if necessary
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> declare
00:21:52 2 ret varchar2(20);
00:21:52 3 begin
00:21:52 4 ret := dbms_java_test.funcall('-lid_translate_all', ' ');
00:21:52 5 exception
00:21:52 6 when others then
00:21:52 7 null;
00:21:52 8 end;
00:21:52 9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Java longer identifiers name translation End
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2019-02-09 00:21:52
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-02-09 00:21:52
DBUA_TIMESTAMP POSTUP_END NONE 2019-02-09 00:21:52


1 row selected.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
00:21:52 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2019-02-09 00:21:52
DBUA_TIMESTAMP CATUPPST FINISHED 2019-02-09 00:21:52
DBUA_TIMESTAMP CATUPPST NONE 2019-02-09 00:21:52


1 row selected.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> Rem Set errorlogging off
00:21:52 SQL> SET ERRORLOGGING OFF;
00:21:52 SQL> 
00:21:52 SQL> Rem
00:21:52 SQL> Rem Set _ORACLE_SCRIPT to false
00:21:52 SQL> Rem
00:21:52 SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

Session altered.

Elapsed: 00:00:00.00
00:21:52 SQL> 
00:21:52 SQL> Rem *********************************************************************
00:21:52 SQL> Rem END catuppst.sql
00:21:52 SQL> Rem *********************************************************************

Execute utlrp.sql script to compile  invalid objects.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
1765

1 row selected.
00:27:47 SQL> @utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem $Header: rdbms/admin/utlrp.sql /main/23 2017/03/20 12:21:12 raeburns Exp $
00:28:27 SQL> Rem
00:28:27 SQL> Rem utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem Copyright (c) 1998, 2017, Oracle and/or its affiliates.
00:28:27 SQL> Rem All rights reserved.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NAME
00:28:27 SQL> Rem utlrp.sql - Recompile invalid objects
00:28:27 SQL> Rem
00:28:27 SQL> Rem DESCRIPTION
00:28:27 SQL> Rem This script recompiles invalid objects in the database.
00:28:27 SQL> Rem
00:28:27 SQL> Rem When run as one of the last steps during upgrade or downgrade,
00:28:27 SQL> Rem this script will validate all remaining invalid objects. It will
00:28:27 SQL> Rem also run a component validation procedure for each component in
00:28:27 SQL> Rem the database. See the README notes for your current release and
00:28:27 SQL> Rem the Oracle Database Upgrade book for more information about
00:28:27 SQL> Rem using utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem Although invalid objects are automatically re-validated when used,
00:28:27 SQL> Rem it is useful to run this script after an upgrade or downgrade and
00:28:27 SQL> Rem after applying a patch. This minimizes latencies caused by
00:28:27 SQL> Rem on-demand recompilation. Oracle strongly recommends running this
00:28:27 SQL> Rem script after upgrades, downgrades and patches.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NOTES
00:28:27 SQL> Rem * This script must be run using SQL*PLUS.
00:28:27 SQL> Rem * You must be connected AS SYSDBA to run this script.
00:28:27 SQL> Rem * There should be no other DDL on the database while running the
00:28:27 SQL> Rem script. Not following this recommendation may lead to deadlocks.
00:28:27 SQL> Rem
00:28:27 SQL> Rem BEGIN SQL_FILE_METADATA
00:28:27 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlrp.sql
00:28:27 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlrp.sql
00:28:27 SQL> Rem SQL_PHASE: UTILITY
00:28:27 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:28:27 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:28:27 SQL> Rem SQL_CALLING_FILE: NONE
00:28:27 SQL> Rem END SQL_FILE_METADATA
00:28:27 SQL> Rem
00:28:27 SQL> Rem MODIFIED (MM/DD/YY)
00:28:27 SQL> Rem raeburns 03/09/17 - Bug 25616909: Use UTILITY for SQL_PHASE
00:28:27 SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
00:28:27 SQL> Rem gviswana 06/12/03 - Switch default back to serial
00:28:27 SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
00:28:27 SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
00:28:27 SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
00:28:27 SQL> Rem gviswana 06/25/02 - Add documentation
00:28:27 SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
00:28:27 SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
00:28:27 SQL> Rem rburns 11/12/01 - validate all components after compiles
00:28:27 SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
00:28:27 SQL> Rem rburns 09/29/01 - use 9.2.0
00:28:27 SQL> Rem rburns 09/20/01 - add check for CATPROC valid
00:28:27 SQL> Rem rburns 07/06/01 - get version from instance view
00:28:27 SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
00:28:27 SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
00:28:27 SQL> Rem skabraha 09/25/00 - validate is now a keyword
00:28:27 SQL> Rem kosinski 06/14/00 - Persistent parameters
00:28:27 SQL> Rem skabraha 06/05/00 - validate tables also
00:28:27 SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
00:28:27 SQL> Rem rshaikh 09/22/99 - quote name for recompile
00:28:27 SQL> Rem ncramesh 08/04/98 - change for sqlplus
00:28:27 SQL> Rem usundara 06/03/98 - merge from 8.0.5
00:28:27 SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
00:28:27 SQL> Rem Mark Ramacher (mramache) was the original
00:28:27 SQL> Rem author of this script.
00:28:27 SQL> Rem
00:28:27 SQL> 
00:28:27 SQL> Rem ===========================================================================
00:28:27 SQL> Rem BEGIN utlrp.sql
00:28:27 SQL> Rem ===========================================================================
00:28:27 SQL> 
00:28:27 SQL> @@utlprp.sql 0
00:28:27 SQL> Rem Copyright (c) 2003, 2017, Oracle and/or its affiliates.
00:28:27 SQL> Rem All rights reserved.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NAME
00:28:27 SQL> Rem utlprp.sql - Recompile invalid objects in the database
00:28:27 SQL> Rem
00:28:27 SQL> Rem DESCRIPTION
..........................
..........................
..........................

Function dropped.

Elapsed: 00:00:00.01
00:37:06 SQL> SET serveroutput off
00:37:06 SQL> 
00:37:06 SQL> 
00:37:06 SQL> Rem =====================================================================
00:37:06 SQL> Rem Run component validation procedure
00:37:06 SQL> Rem =====================================================================
00:37:06 SQL> 
00:37:06 SQL> SET serveroutput on
00:37:06 SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.82
00:37:12 SQL> SET serveroutput off
00:37:12 SQL> 
00:37:12 SQL> 
00:37:12 SQL> Rem =======================================================================
00:37:12 SQL> Rem END utlrp.sql
00:37:12 SQL> Rem =======================================================================

Set COMPATIBLE parameter :

00:37:12 SQL> show parameter compatible;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------
compatible			     string	 12.2.0
noncdb_compatible		     boolean	 FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

System altered.

Restart the database :

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

SQL> show parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 18.0.0
noncdb_compatible		     boolean	 FALSE

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Upgrade Time Zone version :

SQL> DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_prepare(l_tz_version);
END;
/ 
l_tz_version=31
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

Now check the upgrade timezone version,we are going to upgrade DST_SECONDARY_TT_VERSION  is 31

SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         26
DST_SECONDARY_TT_VERSION       31
DST_UPGRADE_STATE              PREPARE

Shutdown and startup database in normal mode.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Upgrade the database zone file.

SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL> 
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

dba_tstz_tables view displays the tables that are processed by the time zone file upgrade, and their current upgrade status.

SQL> SELECT owner,table_name,upgrade_in_progress FROM   dba_tstz_tables
ORDER BY 1,2;

Once the upgrade is complete, check the time zone file version.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- -------
timezlrg_31.dat      31         0

SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name; 

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------
DST_PRIMARY_TT_VERSION         31
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Reference: Steps to Upgrade Time Zone File and Timestamp with Time Zone Data

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/preupgrade1/postupgrade_fixups.sql

Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-08 22:48:34
For Source Database: ORCLDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number   Preupgrade Check Name    Remedied       Further DBA Action
------ ------------------------ ---------- ----------------------------
3.       old_time_zones_exist      YES       None.
4.       post_dictionary           YES       None.
5.       post_fixed_objects        NO        Informational only.
Further action is optional.

The fixup scripts have been run and resovled 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 upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_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.
Session altered.

Check Database version and status.

SQL> select name,version,open_mode from v$database,v$instance;

NAME           VERSION            OPEN_MODE
--------- ----------------- --------------------
ORCLDB        18.0.0.0.0          READ WRITE

Reference : Oracle DB 18c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 18c (Doc ID 2418045.1)

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                        FB Group:https://www.facebook.com/groups/894402327369506/              FB Page:https://www.facebook.com/dbahariprasath/?                      Twitter:https://twitter.com/hariprasathdba

Duplicate a Database Using RMAN in Oracle Database 12c Release 1

Cloning Oracle 12c Database Using Active database Duplication :-

To create a Physical Standby database using RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

Steps for creating database using Active database Duplication :-

  1. Change the archive log mode :

If it is noarchivelog mode, switch  to archivelog mode.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> alter database archivelog

Database altered.

SQL> alter database open

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

3. Initialization Parameters :

Check the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “prod” on the primary database.

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.

SQL> show parameter db_name;

NAME     TYPE   VALUE
-------- -----  ------
db_name  string prod

SQL> show parameter db_unique;

NAME           TYPE   VALUE
-------------- -----  ------
db_unique_name string prod
SQL> alter system set log_archive_config='DG_CONFIG=(prod,clone)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=clone LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=clone' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=clone SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Listener Configuration in Target database :

[oracle@devserver ]$ export ORACLE_SID=clone
[oracle@devserver ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@devserver admin]$ cd $ORACLE_HOME/network/admin
[oracle@devserver admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = clone )

    )

  )

LISTENER_CLONE =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Service Name Registration in Target database :

CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = CLONE)
)
)

6. Create respective directories in Target Server:

[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/ctrl
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/data
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/logs
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/arch
[oracle@devserver admin]$ mkdir /oracle/app/oracle/admin/clone/adump

7. Start listener in Target side:

[oracle@proddr01 admin] $lsnrctl start LISTENER_CLONE

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

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

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=devserver.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_CLONE
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/devserver/listener_clone/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devserver.localdomain.com)(PORT=1521)))
Services Summary...
Service "clone" has 1 instance(s).
Instance "clone", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

  • After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@devserver ]$ cd $ORACLE_HOME/dbs
[oracle@devserver dbs]$ scp initprod.ora orapwprod
oracle@devserver:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@devserver's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ mv orapwprod orapwclone

[oracle@proddr01 dbs]$ cat initprod.ora
db_name='clone'

9. Check connectivity between primary and standby side :

[oracle@devserver ]$ tnsping prod  [In boths the nodes]

[oracle@devserver ]$ tnsping clone [In boths the nodes]

10. Clone Database Creation :-

Startup in nomount stage :

[oracle@proddr01 ]$ export ORACLE_SID=clone
[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create target  database,

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@clone
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 30 20:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/clone/data' 
set db_unique_name='clone'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/clone/logs' 
set control_files='/oracle/app/oracle/oradata/clone/ctrl/control.ctl'
set fal_client='clone'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(clone,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 30-JAN-19
.
.
.
.
.
Finished Duplicate Db at 30-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Disable Archivelog mode in Target database :

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog

Database altered.
SQL> alter database open

Database altered.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/              FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

Understanding The Oracle Sysaux Tablespace

 

The SYSAUX tablespace stores data for auxiliary applications such as the Log Miner, Work space Manager, Oracle Data Mining, Oracle Streams, and many other Oracle tools.

  • This tablespace is automatically created if you use the DataBase Creation Assistant(DBCA) software to build an Oracle database.
  • Like the SYSTEM tablespace, SYSAUX requires a higher level of security and it cannot be dropped or renamed.
  • Do not allow user objects to be stored in SYSAUX. This tablespace should only store system specific objects.
  • This is a permanent tablespace.
RECOVERY:

If you have backup of sysaux and following all archives then you can recover sysaux tablespace.

RMAN> restore tablespace sysaux;

RMAN> recover tablespace sysaux;

SQL> alter tablespace sysaux online;

If you say you lost sysaux file and you don’t have backup and all archive files, then you don’t have option of recreating sysaux tablespace

Solution for this scenario:

you may have to use CTAS or export backup of this database and rebuild complete database.

you may not be able to deal with physical backup.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Understanding Oracle System Tablespace

From Oracle 10g, the smallest Oracle database is two tablespaces. This applies to Oracle 11g.

  • SYSTEM – stores the data dictionary.
  • SYSAUX – stores data for auxiliary applications (sysaux page link).

In reality, a typical production database has numerous tablespaces. These include SYSTEM and NON-SYSTEM tablespaces.

SYSTEM – a tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.

  • Every Oracle database has to have a SYSTEM tablespace—it is the first tablespace created when a database is created.
  • Accessing it requires a higher level of privilege.
  • You cannot rename or drop a SYSTEM.
  • You cannot take a SYSTEM tablespace offline.
  • The SYSTEM tablespace could store user data, but this is not normally done—a good rule to follow is to never allow allow the storage of user segments in the SYSTEM
  • This tablespace always has a SYSTEM Undo segment.

It Contains,

  • Data Dictionary Information
  • system rollback segment
  • sys,system objects
  • should not contain user data

When creating a table it is customary to specify a “tablespace <tablespace name>” clause. If you didn’t mention the “tablespace” , Oracle will create the table in the user’s default tablespace. To change the default tablespace for a user you can issue this command:

alter user <username> default tablespace users;

This will keep new tables from getting into the system tablespace. To move a table from the system tablespace, you can use the alter table move tablespace <tablespace name> command.

You can also use the CTAS and rename commands but you must re-add the table constraints.

RECOVERY :

When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.

It’s not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.

If you have a good backup you can  restore it, but the database could not be open until the recovery process finishes.No problem for your committed transactions if your database is running in ARCHIVE LOG mode and they will be available as soon as the database open.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c Background Process-LREG (Listener Registration)

LREG (Listener Registration Process) :-

LREG (Listener Registration) is an Oracle background process created when you start an Oracle 12c or higher database instance. This function was handled by the PMON process in earlier releases.It registers the instance with the listeners.

The Listener Registration process attempts register the information about the database instance and services if the listener is running. If the listener is not running it will keep trying periodically connect to it. It may take some time till LREG makes all listener registration after you start it, if you want to make an immediate registration you must keep using “ALTER SYSTEM REGISTER;” exactly as in the older versions.

[oracle@PROD ]$ ps -ef | grep lreg

oracle 18174 1 0 2018 ? 00:04:37 asm_lreg_+ASM1
oracle 1675 1 0 Jan27 ? 00:00:02 ora_lreg_PROD

To trace the service registration to Listener,

SQL> alter system set events='immediate trace name listener_registration level 3';

System altered.

SQL> alter system register;

System altered.

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

VALUE
----------------------------------------------
/oracle/app/oracle/diag/rdbms/PROD/PROD1/trace

[oracle@PROD trace]$ cd /app/oracle/diag/rdbms/PROD/PROD1/trace
[oracle@PROD trace]$ ls -lrt *lreg*

-rw-r----- 1 oracle oinstall  175 Sep 19 23:44 PROD_lreg_9012.trm
-rw-r----- 1 oracle oinstall 4972 Sep 19 23:44 PROD_lreg_9012.trc

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c-2 Node Rac To Single Instance Standby Database Setup

Steps for creating Single instance standby database from RAC primary database :-

  1. Change the archive log mode :
$ sqlplus / as sysdba

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

3.  Parameter Configuration setup:

SQL> alter system set log_archive_config='DG_CONFIG=(prod,proddr)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=proddr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddr' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=proddr SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Standby Listener Configuration:

[oracle@proddr01 ]$ export ORACLE_SID=prod
[oracle@proddr01 ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = prod )

    )

  )

LISTENER_PRODDR=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Connection string Configuration :

Standby and primary tnsnames.ora entry should be available in both nodes:

[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat tnsnames.ora

PROD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))

    )

    (CONNECT_DATA =

       (SERVER = DEDICATED)

        (SID = prod1)

    )

  )

PRODDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = prod)

    )

  )

6. Create respective directories in Standby Server:

[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/ctrl
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/data
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/logs
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/arch
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/admin/proddr/adump

7. Start Standby listener :

[oracle@proddr01 admin] $lsnrctl start LISTENER_PRODDR

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

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

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddr01.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_PRODDR
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/proddr01/listener_proddr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=proddr01)(PORT=1521)))
Services Summary...
Service "proddr" has 1 instance(s).
Instance "proddr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

  • After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@proddr01 ]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp initprod.ora orapwprod
oracle@proddr01:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@proddr01's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ cat initprod.ora

db_name='prod'

9. Check connectivity between primary and standby side :

[oracle@proddr01 ]$ tnsping prod   [In boths the nodes]

[oracle@proddr01 ]$ tnsping proddr    [In boths the nodes]

10. Standby Database Creation :

Startup in nomount stage :

[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create standby database,

Set cluster_database is FALSE.

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@proddr
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 27 16:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/proddr/data' 
set db_unique_name='proddr'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/proddr/logs' 
set control_files='/oracle/app/oracle/oradata/proddr/ctrl/control.ctl'
set fal_client='proddr'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(proddr,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 28-JAN-19
.
.
.
.
.
Finished Duplicate Db at 28-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Enable Recovery Manager in standby side:

[oracle@proddr01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 28 10:36:39 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

13. Check Standby SYNC Verification:

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- -----------
1          299314                 299314                0
2          149803                 149803                0

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Purging/Cleaning Sysaux Tablespace In Oracle

Restrictions on SYSAUX tablespace

1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGEMENT LOCAL,SEGMENT SPACE MANAGEMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.

Check whats occupying SYSAUX tablespace:

SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script   ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

Run below query to know what all occupants are there in sysaux occupying all the space.

SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;

Once you find what are all occupying the space then we can work on it. If you have tackled with huge number of AWR reports occupying then you need to check AWR retention period.

SQL>select retention from dba_hist_wr_control;

Through this query we can get for how many days AWR reports maintaining, If that which we did not require that many days we can reduce it.

For Example : If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes.the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes through below query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query

SQL>exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SQL>SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW';

Then update it to correct value and then execute the above AWR retention query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SQL>SELECT snap_id, begin_interval_time, end_interval_time
FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

To cleanup all AWR reports between snap_id <start snap> to <end snap> execute below query.

SQL>BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => <start snap>, high_snap_id=><end snap>);
END;
/

Else you can run below commands to drop the old AWR reports and rebuild the repositories. This process is very fast too.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we were able to get space from SYSAUX tablespace.

High Storage Consumption for LOBs in SYSAUX Tablespace:

A number of LOB segments created for LOB columns of AWR tables in the SYSAUX tablespace consume significant storage.

This storage is not released even after AWR snapshots are dropped and row counts in the tables reduced.

SQL> select * from dba_lobs where table_name = 'WRH$_SQLTEXT';

SQL> select * from dba_segments where segment_name = '<seg-name>';

SQL> select count(*) from WRH$_SQLTEXT;

The LOB segments can be rebuilt using the same name, storage parameters and SYSAUX tablespace by executing a MOVE operation on them.
This builds a new LOB segment containing only the data currently in the LOB and exchanges it with the original LOB segment.

To implement the solution, Execute below steps:

1. Startup the database in RESTRICT mode to prevent interference from AWR operations during the rebuild.

2. From a SQL*Plus session connected as SYSDBA perform the move operation as follows:

SQL> alter table wrh$_sqltext move lob(sql_text) store as <name of LOB> tablespace sysaux;

the AWR table is WRH$_SQLTEXT, the LOB column is SQL_TEXT and <name of LOB> is the name of the LOB segment whose storage we want to reclaim

The new LOB segment will have a new data_object_id in dba_objects and a new (header_file,header_block) pair in dba_segments. It should also have a reduced number of bytes and blocks in dba_segments.

3. Check for and rebuild any unusable index on the table after the LOB move:

SQL> select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';

INDEX_NAME
-----------
SAMPLE

SQL> alter index SAMPLE rebuild;

Index altered.

4. Finally, restart the database in normal mode.

Other possible solutions

1) ALTER TABLE … SHRINK SPACE CASCADE on the table (assumes that ASSM and ENABLE ROW MOVEMENT) are used

  NOTE: This operation will lock the table during the shrink

2) Use DBMS_REDEFINITION to redefine the table online

  NOTE: a) This operation could require up to double the size of the existing table … if the table does not really have space to release

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba