oracle Gather stats for a table

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘HR’,
tabname => ‘EMP’,
cascade => true, —- For collecting stats for respective indexes
method_opt=>’for all indexed columns size 1′,
granularity => ‘ALL’,
estimate_percent =>dbms_stats.auto_sample_size,
degree => 4);
END;
/

— For a single table partition
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘HR’,
tabname => ‘EMP’, — TABLE NAME
partname => ‘EMP_P1’ — PARTITOIN NAME
method_opt=>’for all indexed columns size 1′,
GRANULARITY => ‘APPROX_GLOBAL AND PARTITION’,
degree => 8);
END;
/

Oracle Archivelog generation per hour

set lines 300
SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM”
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

Oracle Database Size

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

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

Step by Step Oracle TimesTen In-Memory Database Installation On Linux

In this article, we will see the step by step installation of Oracle TimesTen In-Memory Database 11.2.2 and TimesTen Applications Database Cache 11.2.2 on RHEL 6.8.

Download the Oracle TimesTen Database Software from here.

Create an operating system group for the TimesTen instance administrators
group.

Create ttadmin group:-

$ /usr/sbin/groupadd -g 54331 ttadmin

Create /etc/TimesTen directory:-

$ mkdir /etc/TimesTen

Assign ownship permission to group ttadmin to directory /etc/TimesTen:-

$ chgrp -R ttadmin /etc/TimesTen
$ chmod -R 775 /etc/TimesTen
$ ls -ltrhd /etc/TimesTen/ls -ltrhd /etc/TimesTen/
drwxrwxr-x. 2 root ttadmin 4.0K Dec 23 13:21 /etc/TimesTen/

Create user oratta with group ttadmin:-

$ /usr/sbin/useradd -u 54331 -g ttadmin oratta
$ id oratta
uid=54331(oratta) gid=54331(ttadmin) groups=54331(ttadmin)

Create and change ownership to the directories to oratta as owner and group ttadmin:-

$ mkdir -p /oradb/app/oratta/TimesTen
$ chown -R oratta:ttadmin /oradb/app/oratta

Login as oratta user and start installation:-

$ su – oratta
$ unzip the TimesTen software
$ tar -zxvf timesten112280.linux8664.tar.gz
$ cd linux8664
$ ./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.

Please choose an instance name for this installation? [ tt1122 ]
Instance name will be ‘tt1122’.
Is this correct? [ yes ]

Of the three components:

[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only

Which would you like to install? [ 1 ]

Of the following options :

[1] /home/oratta
[2] /mnt/hgfs/shared/Softwares/oracle/Timesten
[3] Specify a location
[q] Quit the installation

Where would you like to install the tt1122 instance of TimesTen? [ 1 ] 3
Please specify a directory to install TimesTen? [ /home/oratta ] /oradb/app/oratta/TimesTen
Where would you like to create the daemon home directory? [ /oradb/app/oratta/TimesTen/tt1122/info ]

The daemon logs will be located in /oradb/app/oratta/TimesTen/tt1122/info
Would you like to specify a different location for the daemon logs? [ no ]
Installing into /oradb/app/oratta/TimesTen/tt1122 …
Uncompressing …

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
daemon port number must be the same across all TimesTen installations
managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running ‘ttVersion’.

The default port number is 53396.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (53396).

NOTE: For security, we recommend that you restrict access to the
TimesTen installation to members of a single OS group. Only members of
that OS group will be allowed to perform direct mode connections to
TimesTen, and only members of that OS group will be allowed to perform
operations that access TimesTen data stores, TimesTen files and shared
memory. The OS group defaults to the primary group of the instance
administrator. You can default to this group, choose another OS group
or you can make this instance world-accessible. If you choose to make
this instance world-accessible, all database files and shared memory
are readable and writable by all users.

Restrict access to the the TimesTen installation to the group ‘ttadmin’? [ yes ]

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ]

In order to use the ‘Oracle TimesTen Application-Tier Database Cache’ feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using <install_dir>/bin/ttModInstall.

Please enter a value for TNS_ADMIN (s=skip)? [ ] /oradb/app/oracle/product/11.2.0.4/db_1/network/admin

TNS_ADMIN will be set to /oradb/app/oracle/product/11.2.0.4/db_1/network/admin
You can change TNS_ADMIN later by running <install_dir>/bin/ttmodinstall.

NOTE: It appears that you are running version 4 or higher of the g++
compiler. TimesTen ships with multiple sets of client libraries and server
binaries : one built for compatibility with g++ 3.4.6 and one with
g++ 4.1.0. The installer has created links to the 4.1.0 library in the
<install_dir>/lib directory and to the 4.1.0 server binary in the
<install_dir>/bin directory. If you want to use a different compiler,
please modify the links to point to the desired library and server binary.

Installing server components …
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53397 ]
Do you want to install the Quick Start Sample Programs and the TimesTen Documentation? [ no ] yes
Where would you like to install the Quick Start and doc directories (s=skip)? [ /oradb/app/oratta/TimesTen/tt1122 ]
The TimesTen documentation has been installed in /oradb/app/oratta/TimesTen/tt1122.

The TimesTen Quick Start applications can take up to 64 Mbytes of disk space.
Depending on how your system is configured, you may not want to create the
Quick Start DemoDataStore directory in the default location,
/oradb/app/oratta/TimesTen/tt1122/info/DemoDataStore

Where would you like to create the DemoDataStore directory? [ /oradb/app/oratta/TimesTen/tt1122/info ]
Creating /oradb/app/oratta/TimesTen/tt1122/info/DemoDataStore …

Installing client components …

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the ‘setuproot’ script :
$ cd /oradb/app/oratta/TimesTen/tt1122/bin
$ ./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
‘/oradb/app/oratta/TimesTen/tt1122/startup/tt_tt1122’.

The Quickstart home page can be accessed here :
‘/oradb/app/oratta/TimesTen/tt1122/quickstart/index.html’

The 11.2.2.8 Release Notes are located here :
‘/oradb/app/oratta/TimesTen/tt1122/README.html’

Starting the daemon …
TimesTen Daemon startup OK.
End of TimesTen installation.

Set the Timeten Environment like below:-

$ cd /oradb/app/oratta/TimesTen/tt1122/bin
$ . ttenv.sh

LD_LIBRARY_PATH set to /oradb/app/oratta/TimesTen/tt1122/lib:/oradb/app/oratta/TimesTen/tt1122/ttoracle_home/instantclient_11_2

PATH set to /oradb/app/oratta/TimesTen/tt1122/bin:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/oci:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/odbc:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/odbc/xla:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/jdbc:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/odbc_drivermgr:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/proc:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/ttclasses:/oradb/app/oratta/TimesTen/tt1122/quickstart/sample_code/ttclasses/xla:/oradb/app/oratta/TimesTen/tt1122/ttoracle_home/instantclient_11_2:/oradb/app/oratta/TimesTen/tt1122/ttoracle_home/instantclient_11_2/sdk:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oratta/bin

CLASSPATH set to /oradb/app/oratta/TimesTen/tt1122/lib/ttjdbc6.jar:/oradb/app/oratta/TimesTen/tt1122/lib/orai18n.jar:/oradb/app/oratta/TimesTen/tt1122/lib/timestenjmsxla.jar:/oradb/app/oratta/TimesTen/tt1122/3rdparty/jms1.1/lib/jms.jar:.

TNS_ADMIN set to /oradb/app/oracle/product/11.2.0.4/db_1/network/admin

Verify the version:-

$ ttversion
TimesTen Release 11.2.2.8.0 (64 bit Linux/x86_64) (tt1122:53396) 2015-01-20T08:36:31Z
Instance admin: oratta
Instance home directory: /oradb/app/oratta/TimesTen/tt1122
Group owner: ttadmin
Daemon home directory: /oradb/app/oratta/TimesTen/tt1122/info
PL/SQL enabled.

$ ttstatus
TimesTen status report as of Sun Dec 23 14:10:09 2018

Daemon pid 16248 port 53396 instance tt1122
TimesTen server pid 16257 started on port 53397
————————————————————————
Accessible by group ttadmin
End of report

 

 

 

 

 

 

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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

Patching Oracle Database Release Updates Revision (RUR) 18.3.2.0.0 to 18.4.1.0.0

Description:-

We are going to perform Patching Oracle Database Release Update Revision 18.3.2.0.0 to 18.4.1.0.0

About Oracle Database Release Numbers:-
Oracle Database releases are released in version and version_full releases. The version is designated in the form major release numeral.0.0.0.0. The release version is the annual release designation of the database software. For example: 2018 is the release year, and the release version is 18.0.0.0.0. The version_full release is updated using numeric segments that change, based on the annual release designation of the software, the quarterly release update version (RU), and the quarterly release updates revision (RUR).
Oracle Database version_full releases are categorized by three numeric segments, which are divided by periods. The first, second, and third numerals provide information about the Oracle Database releases, starting with Oracle Database release 18c:

First numeral: Oracle Database major release number. For example: Oracle Database 18c; Oracle Database 12c. Starting with Oracle Database 18c, the first numeral indicates the initial year in which an Oracle Database version is released. For example: 2018 is the initial release year for Oracle Database 18c (18.0.0.0.0)
Second numeral: Oracle Database Release Update (Update) in Oracle Database 18c and later releases. For example: 18.3 is Release Update 3. In releases before Oracle Database 18c, the second numeral designates a maintenance release number. For example: Oracle Database 12c release 1 (12.1); Oracle 12c release 2 (12.2).
Third numeral: Oracle Database release updates revision (Revision). For example: 18.1.1.; 18.2.1, 18.3.0
Fourth numeral: Oracle Database increment version. This nomenclature can apply to updates in future releases, and it applies to all releases earlier than 18c. For example: 12.1.0.1, 12.2.0.1,
Fifth numeral: This numeral is reserved for future use.

Caution:

Oracle strongly recommends that you apply the most recent release update (Update) or bundle patch or patch set update to your source and target databases before starting an upgrade, and before starting a downgrade.

 

Check Database version :

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Feb 14 04:26:25 2019
Version 18.3.2.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.2.0.0

Check Opatch version and lsinventory :

Check the opatch version and inventory for ORACLE_HOME.

[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/OPatch:$PATH
[oracle@test ~]$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

Minimum version of OPatch utility version 12.2.0.1.14 or later to apply this patch.

[oracle@test ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-27-17AM_1.log

Lsinventory Output file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-02-14_04-27-17AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: test.localdomain.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 18c 18.0.0.0.0
There are 1 products installed in this Oracle Home.

Interim patches (4) :

Patch 28790643 : applied on Sat Feb 09 02:01:58 IST 2019
Unique Patch ID: 22605424
Patch description: "Database Release Update Revision : 18.3.2.0.190115 (28790643)"
Created on 1 Dec 2018, 05:13:21 hrs PST8PDT
Bugs fixed:
25929650, 9062315, 13554903, 21547051, 21766220, 21806121, 23003564
23310101, 24489904, 24689376, 24737581, 24925863, 25035594, 25035599
25287072, 25348956, 25634405, 25726981, 25743479, 25824236, 25943740
26226953, 26336101, 26423085, 26427905, 26450454, 26476244, 26598422
26615291, 26646549, 26654411, 26731697, 26785169, 26792891, 26818960
26822620, 26843558, 26843664, 26846077, 26894737, 26898279, 26928317
26933599, 26956033, 26961415, 26966120, 26986173, 26992964, 27005278
27026401, 27028251, 27030974, 27036408, 27038986, 27041253, 27044575
27047831, 27053044, 27058530, 27060167, 27060859, 27061736, 27066451
27066519, 27073066, 27086821, 27090765, 27101527, 27101652, 27110878
27112686, 27119621, 27126666, 27128580, 27135647, 27143756, 27143882
27147979, 27153641, 27155549, 27156355, 27163928, 27169796, 27181521
27181537, 27189611, 27190851, 27193810, 27199245, 27208953, 27210038
27210872, 27214085, 27215007, 27216224, 27221900, 27222121, 27222626
27224987, 27226913, 27232983, 27233563, 27236052, 27236110, 27240246
27240570, 27241221, 27241247, 27244337, 27244785, 27249215, 27250547
27254851, 27258578, 27259386, 27259983, 27262650, 27262945, 27263276
27263996, 27270197, 27274456, 27274536, 27275136, 27275776, 27282707
27283029, 27283960, 27284499, 27285244, 27288230, 27292213, 27294480
27301308, 27301568, 27302594, 27302681, 27302695, 27302711, 27302730
27302777, 27302800, 27302960, 27304410, 27304936, 27305318, 27307868
27310092, 27313687, 27314206, 27314390, 27318869, 27321179, 27321834
27326204, 27329812, 27330158, 27330161, 27333658, 27333664, 27333693
27334316, 27334648, 27335682, 27338912, 27338946, 27339115, 27339396
27339483, 27339495, 27341036, 27345190, 27345231, 27345450, 27345498
27346329, 27346644, 27346709, 27346949, 27347126, 27348081, 27348707
27349393, 27352600, 27354783, 27356373, 27357773, 27358241, 27359178
27359368, 27360126, 27364891, 27364916, 27364947, 27365139, 27365702
27365993, 27367194, 27368850, 27372756, 27375260, 27375542, 27376871
27378103, 27379233, 27381383, 27381656, 27384222, 27389352, 27392187
27395404, 27395416, 27395794, 27396357, 27396365, 27396377, 27396624
27396666, 27396672, 27396813, 27398080, 27398660, 27401637, 27405242
27405696, 27410300, 27410595, 27412805, 27417186, 27420715, 27421101
27422874, 27423251, 27425507, 27425622, 27426363, 27427805, 27430802
27432338, 27432355, 27433870, 27434050, 27434193, 27434486, 27434974
27435537, 27439835, 27441326, 27442041, 27444727, 27445330, 27445462
27445727, 27447452, 27447687, 27448162, 27450355, 27450400, 27450783
27451049, 27451182, 27451187, 27451531, 27452760, 27453225, 27457666
27457891, 27458164, 27459909, 27460675, 27467543, 27469329, 27471876
27472969, 27473800, 27479358, 27483974, 27484556, 27486253, 27487795
27489719, 27496224, 27496308, 27497950, 27498477, 27501327, 27501413
27501465, 27502420, 27504190, 27505603, 27506774, 27508985, 27511196
27512439, 27517818, 27518227, 27518310, 27520070, 27520900, 27522245
27523368, 27523800, 27525909, 27532375, 27533819, 27534509, 27537472
27544030, 27545630, 27547732, 27550341, 27551855, 27558557, 27558559
27558861, 27560702, 27563629, 27563767, 27570318, 27577758, 27579353
27580996, 27585755, 27585800, 27586810, 27586895, 27587672, 27591842
27592466, 27593389, 27595973, 27599689, 27602091, 27602488, 27603841
27604293, 27607805, 27608669, 27610269, 27613080, 27613247, 27615608
27616657, 27617522, 27625274, 27625620, 27631506, 27634676, 27635508
27644757, 27649707, 27652302, 27663370, 27664702, 27679488, 27679664
27679806, 27679961, 27680162, 27680509, 27682151, 27688099, 27688692
27690578, 27691809, 27692215, 27693713, 27697092, 27701795, 27705761
27707544, 27709046, 27718914, 27719187, 27723002, 27726269, 27726780
27732323, 27739006, 27740844, 27744211, 27745220, 27747869, 27748954
27751006, 27753336, 27757567, 27772815, 27773602, 27774320, 27774539
27779886, 27780562, 27782339, 27783289, 27786772, 27791223, 27797290
27803665, 27807441, 27812560, 27812593, 27813267, 27815347, 27818871
27832643, 27833369, 27834984, 27840386, 27847259, 27851757, 27861909
27869339, 27873643, 27882176, 27892488, 27924147, 27926113, 27930478
27934468, 27941896, 27945870, 27950708, 27952762, 27961746, 27964051
27970265, 27971575, 27984028, 27989849, 27993289, 27994333, 27997875
27999597, 28021205, 28022847, 28033429, 28057267, 28059199, 28072130
28098865, 28106402, 28132287, 28169711, 28174827, 28174951, 28184554
28188330, 28238264, 28264172, 28320117, 28321446, 28338399, 28492362
28571483, 28580528, 28713840, 28728272, 28758090, 28785022, 28805612
28805695, 28919145

Patch 27908644 : applied on Wed Jul 18 23:14:11 IST 2018
Unique Patch ID: 22153180
Patch description: "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171"
Created on 4 May 2018, 01:21:02 hrs PST8PDT
Bugs fixed:
27908644

Patch 27923415 : applied on Wed Jul 18 23:11:38 IST 2018
Unique Patch ID: 22239273
Patch description: "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)"
Created on 15 Jul 2018, 10:33:22 hrs PST8PDT
Bugs fixed:
27304131, 27539876, 27952586, 27642235, 27636900, 27461740

Patch 28090553 : applied on Wed Jul 18 23:10:01 IST 2018
Unique Patch ID: 22256940
Patch description: "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)"
Created on 11 Jul 2018, 19:20:31 hrs PST8PDT
Bugs fixed:
12816839, 18701017, 22734786, 23698980, 23840305, 25709124, 25724089
26299684, 26313403, 26433972, 26527054, 26586174, 26587652, 26647619
26827699, 26860285, 26882126, 26882316, 26943660, 26996813, 27012915
27018734, 27032726, 27034318, 27040560, 27080748, 27086406, 27092991
27098733, 27106915, 27114112, 27121566, 27133637, 27144533, 27153755
27166715, 27174938, 27174948, 27177551, 27177852, 27182006, 27182064
27184253, 27204476, 27212837, 27213140, 27220610, 27222423, 27222938
27238077, 27238258, 27249544, 27252023, 27257509, 27263677, 27265816
27267992, 27271876, 27274143, 27285557, 27299455, 27300007, 27302415
27309182, 27314512, 27315159, 27320985, 27334353, 27338838, 27346984
27358232, 27362190, 27370933, 27377219, 27378959, 27379846, 27379956
27393421, 27398223, 27399499, 27399762, 27399985, 27401618, 27403244
27404599, 27426277, 27428790, 27430219, 27430254, 27433163, 27452897
27458829, 27465480, 27475272, 27481406, 27481765, 27492916, 27496806
27503318, 27503413, 27508936, 27508984, 27513114, 27519708, 27526362
27528204, 27532009, 27534289, 27560562, 27560735, 27573154, 27573408
27574335, 27577122, 27579969, 27581484, 27593587, 27595801, 27600706
27609819, 27625010, 27625050, 27627992, 27654039, 27657467, 27657920
27668379, 27682288, 27691717, 27702244, 27703242, 27708711, 27714373
27725967, 27731346, 27734470, 27735534, 27739957, 27740854, 27747407
27748321, 27757979, 27766679, 27768034, 27778433, 27782464, 27783059
27786669, 27786699, 27801774, 27811439, 27839732, 27850736, 27862636
27864737, 27865439, 27889841, 27896388, 27897639, 27906509, 27931506
27935826, 27941514, 27957892, 27978668, 27984314, 27993298, 28023410
28025398, 28032758, 28039471, 28039953, 28045209, 28099592, 28109698
28174926, 28182503, 28204423, 28240153

-----------------------------------------------------------------------
OPatch succeeded.

Download and Unzip the Database Release Update Patch :

Release Date and Download link :

15-Jan-2019 Database Release Update Revision 18.4.1.0.0 Patch 28822587
[oracle@test u02]$ unzip p28822587_180000_Linux-x86-64.zip
Archive: p28822587_180000_Linux-x86-64.zip
creating: 28822587/
creating: 28822587/files/
creating: 28822587/files/javavm/
creating: 28822587/files/javavm/ojvmwcu/
creating: 28822587/files/javavm/ojvmwcu/lib/

Check Patch Conflict Detection and Resolution :

[oracle@test 28822587]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-46-45AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Shutdown the database and listener :

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

[oracle@test ~]$ lsnrctl stop

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:05:46
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
The command completed successfully

Apply the database upgrade patch :

[oracle@test 28822587]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.


Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-49-20AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28822587

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u02/app/oracle/product/18.3.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28822587' to OH '/u02/app/oracle/product/18.3.0/db_1'
ApplySession: Optional component(s) [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.assistants.server.oui, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.xdk.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.rman, 18.0.0.0.0...

Patching component oracle.rdbms.util, 18.0.0.0.0...

Patching component oracle.rdbms.rsf, 18.0.0.0.0...

Patching component oracle.xdk.parser.java, 18.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 18.0.0.0.0...

Patching component oracle.assistants.deconfig, 18.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0...

Patching component oracle.network.client, 18.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 18.0.0.0.0...

Patching component oracle.ons, 18.0.0.0.0...

Patching component oracle.ctx.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...

Patching component oracle.xdk, 18.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0...

Patching component oracle.sdo.locator, 18.0.0.0.0...

Patching component oracle.dbjava.ucp, 18.0.0.0.0...

Patching component oracle.rdbms.crs, 18.0.0.0.0...

Patching component oracle.server, 18.0.0.0.0...

Patching component oracle.dbjava.ic, 18.0.0.0.0...

Patching component oracle.rdbms, 18.0.0.0.0...

Patching component oracle.ctx.atg, 18.0.0.0.0...

Patching component oracle.dbjava.jdbc, 18.0.0.0.0...

Patching component oracle.sqlplus.ic, 18.0.0.0.0...

Patching component oracle.precomp.rsf, 18.0.0.0.0...

Patching component oracle.ctx, 18.0.0.0.0...

Patching component oracle.rdbms.oci, 18.0.0.0.0...

Patching component oracle.network.listener, 18.0.0.0.0...

Patching component oracle.sdo, 18.0.0.0.0...

Patching component oracle.sqlplus, 18.0.0.0.0...

Patching component oracle.assistants.server, 18.0.0.0.0...

Patching component oracle.rdbms.deconfig, 18.0.0.0.0...

Patching component oracle.assistants.acf, 18.0.0.0.0...

Patching component oracle.oracore.rsf, 18.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 18.0.0.0.0...

Patching component oracle.install.deinstalltool, 18.0.0.0.0...

Patching component oracle.network.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...

Patching component oracle.javavm.client, 18.0.0.0.0...

Patching component oracle.ldap.owm, 18.0.0.0.0...

Patching component oracle.ldap.security.osdt, 18.0.0.0.0...

Patching component oracle.rdbms.install.common, 18.0.0.0.0...

Patching component oracle.precomp.lang, 18.0.0.0.0...

Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28822587 successfully applied.
Sub-set patch [28790643] has become inactive due to the application of a super-set patch [28822587].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-14_04-49-20AM_1.log

OPatch succeeded.

Start the listener and database :

[oracle@test 28822587]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 14-FEB-2019 04:54:29

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

Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 14-FEB-2019 04:54:31
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


[oracle@test 28822587]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Feb 14 04:54:53 2019
Version 18.4.1.0.0

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

Connected to an idle instance.

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.

Loading Modified SQL Files into the Database : 

[oracle@test 28822587]$ datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Thu Feb 14 04:57:16 2019
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3312_2019_02_14_04_57_16/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
Binary registry: Installed
SQL registry: Applied successfully on 13-FEB-19 01.00.13.654106 AM

Current state of release update SQL patches:
Binary registry:
18.4.1.0.0 Release_Update_Revision 1811261143: Installed
SQL registry:
Applied 18.3.2.0.0 Release_Update_Revision 1811212344 successfully on 13-FEB-19 12.56.24.480442 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 28790643 (Database Release Update Revision : 18.3.2.0.190115 (28790643)):
Rollback from 18.3.2.0.0 Release_Update_Revision 1811212344 to 18.3.0.0.0 Release_Update 1806280943
Patch 28822587 (Database Release Update Revision : 18.4.1.0.190115 (28822587)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.4.1.0.0 Release_Update_Revision 1811261143
No interim patches need to be applied

Installing patches...
Patch installation complete. Total patches installed: 2

Validating logfiles...done
Patch 28790643 rollback: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28790643/22605424/28790643_rollback_TEST18C_2019Feb14_04_57_44.log (no errors)
Patch 28822587 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28822587/22600665/28822587_apply_TEST18C_2019Feb14_04_58_32.log (no errors)
SQL Patching tool complete on Thu Feb 14 04:59:23 2019

Patch 28822587 – Database Release Update Revision 18.4.1.0.0 is applied successfully and Database is upgraded from 18.3.2.0.0 to 18.4.1.0.0.

Run utlrp.sql to compile invalid objects :-

SQL> @?/rdbms/admin/utlrp

Session altered.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-09 02:12:45

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-09 02:12:47

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

dba_registry_sqlpatch :

From Oracle 12c,we are using dba_registry_sqlpatch instead of REGISTRY$HISTORY  to check latest applied patches like RU,PSU and CPU.

SQL> select STATUS,DESCRIPTION,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch;

STATUS DESCRIPTION                                      SOURCE_VERSION 
------ ----------------------------------------------   --------------
TARGET_VERSION
--------------
SUCCESS Database Release Update : 18.3.0.0.180717(28090523) 18.1.0.0.0  
18.3.0.0.0

SUCCESS OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)     18.3.0.0.0
18.3.0.0.0

SUCCESS Database Release Update Revision : 18.3.2.0.190115  18.3.0.0.0 
        (28790643) 
18.3.2.0.0

SUCCESS Database Release Update Revision 18.4.1.0.190115    18.3.0.0.0                            
        (28822587)
18.4.1.0.0

 

To check database version :

SQL> select BANNER,BANNER_FULL from v$version;

BANNER                     BANNER FULL  
-------------------------- --------------------------              
Oracle Database 18c        Oracle Database 18c        
Enterprise Edition Release Enterprise Edition Release
18.0.0.0.0 - Production    18.0.0.0.0 - Production 
                           Version 18.4.1.0.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

 

 

 

Oracle 18c Data Guard Broker Enhancements

Description:-

These are the changes in Oracle Data Guard Broker in Oracle Database Release 18c

This release provides several new commands:

  • VALIDATE DATABASE SPFILE performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.
  • VALIDATE NETWORK CONFIGURATION performs network connectivity checks between members of a broker configuration.
  •  VALIDATE STATIC CONNECT IDENTIFIER validates the static connect identifier of a database.
  •  SET ECHO controls whether or not to echo commands that are issued either at the command-line prompt or from a DGMGRL script.
  • SHOW ALL (DGMGRL CLI properties) shows the values of DGMGRL CLI properties.

The V$DATAGUARD_PROCESS view, introduced in Oracle Database 12c Release 2 (12.2.0.1), now shows broker processes. (Oracle Data Guard Monitor (DMON)
Process.)

SQL> select name,TYPE,role from V$DATAGUARD_PROCESS;

NAME  TYP ROLE
----- --- ------------------------
LGWR  KSB log writer
TMON  KSB redo transport monitor
DMON  KSB broker monitor
INSV  KSB broker instance slave
NSV2  KSB broker net slave
TT00  KSV gap manager
TT01  KSV redo transport timer
ARC0  KSB archive local
TT02  KSV async ORL multi
TT03  KSV heartbeat redo informer
ARC1  KSB archive redo
ARC2  KSB archive redo
ARC3  KSB archive redo
RSM0  KSB broker worker
drcx  NET switchover
15 rows selected.

VALIDATE DATABASE SPFILE

The VALIDATE DATABASE SPFILE command performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.

The validation of the server parameter file detects parameter value discrepancies between the primary and the specified standby database so that they can be rectified before a role change, thus ensuring that after a role change the databases perform at the same level they did prior to the role change. Additionally, using this command frees you from having to restart a database to correct improperly set parameters.

Format
VALIDATE DATABASE {database-name} SPFILE;

If the database to be validated is the primary database, then a message is returned saying the command cannot be issued on a primary database.

DGMGRL> VALIDATE DATABASE dbwrprim SPFILE;
This command cannot be used for the primary database.
DGMGRL> VALIDATE DATABASE dbwrstby SPFILE;
Connecting to "dbwrprim".
Connected to "dbwrprim"
Connecting to "dbwrstby".
Connected to "dbwrstby"
Parameter settings with different values:
audit_file_dest:
dbwrprim (PRIMARY) : /oradb/app/oracle/admin/dbwrprim/adump
dbwrstby : /oradb/app/oracle/admin/dbwrstby/adump
compatible:
dbwrprim (PRIMARY) : 18.0.0
dbwrstby : 18.3.0.0.0
dispatchers:
dbwrprim (PRIMARY) : (PROTOCOL=TCP) (SERVICE=dbwrprimXDB)
dbwrstby : (PROTOCOL=TCP) (SERVICE=dbwrstbyXDB)
fal_client:
dbwrprim (PRIMARY) : dbwrprim
dbwrstby : dbwrstby
sga_target:
dbwrprim (PRIMARY) : 1201668096
dbwrstby : 943718400

VALIDATE NETWORK CONFIGURATION

The VALIDATE NETWORK CONFIGURATION command performs network connectivity checks between members of a configuration.

The connect identifier for each connectivity check is generated based on the
DGConnectIdentifier property of the associated database.

Format
VALIDATE NETWORK CONFIGURATION FOR { ALL | member name };

Validating Network Configuration for a Specific Database

DGMGRL> VALIDATE NETWORK CONFIGURATION FOR dbwrstby;
Connecting to instance "dbwrstby" on database "dbwrstby" ...
Checking connectivity from instance "dbwrstby" on database "dbwrstby" to
instance "dbwrprim" on database "dbwprim"...
Succeeded.
Oracle Clusterware is not configured on database "dbwrstby".

VALIDATE STATIC CONNECT IDENTIFIER

The VALIDATE STATIC CONNECT IDENTIFIER command validates the static connect identifier of a database.
To perform this validation, the broker makes a new connection to the database using a static connect identifier based on the StaticConnectIdentifer property of the database. A new attribute, STATIC_SERVICE=TRUE is added to the connect identifier to ensure that a connection to the database is established using only a static service, not a dynamic service.

Format
VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | database name };

Command Examples
Validation of Static Connect Identifier For a Database on Which
Oracle Clusterware Is Configured

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR dbwrstby;
Oracle Clusterware is not configured on database "dbwrstby".

SHOW ALL

The SHOW ALL command shows the values of DGMGRL CLI properties.

Format
SHOW ALL;

Command Example

DGMGRL> SHOW ALL;
debug ON
echo OFF
time OFF
observerconfigfile = observer.ora

 

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 11.2.0.4 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 11g 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 11g listener

lsnrctl stop LISTENER_NAME

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 11.2.0.4 database environment as SYS,

 Pre-upgrade checks :
Run the preupgrade tool .
[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 12 19:49:03 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4893769728 bytes
Fixed Size 2261568 bytes
Variable Size 2717912512 bytes
Database Buffers 2164260864 bytes
Redo Buffers 9334784 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ram ~]$ java -jar /u01/app/oracle/product/18.3/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2019-02-12T19:49:51

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:        ORCLDB
Container Name:       Not Applicable in Pre-12.1 database
Container ID:         Not Applicable in Pre-12.1 database
Version:              11.2.0.4.0
Compatible:           11.2.0.4.0
Blocksize:            8192
Platform:             Linux x86 64-bit
Timezone File:        14
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 
Oracle Workspace Manager             [to be upgraded]     VALID 
OLAP Analytic Workspace              [to be upgraded]     VALID 
Oracle Enterprise Manager Repository [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 
Expression Filter                    [to be upgraded]     VALID 
Rule Manager                         [to be upgraded]     VALID 
Oracle OLAP API                      [to be upgraded]     VALID

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

REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 5082
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.

DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4050 MB
of free space remaining, which may not be adequate for the upgrade.

Currently:
Fast recovery area : /u01/app/oracle 
Limit :              4182 MB 
Used :               132 MB 
Available :          4050 MB 

The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.

RECOMMENDED ACTIONS
===================
2. Remove the EM repository.

- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
18.0.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

Step 1: If database control is configured, stop EM Database Control,
using the following command

$> emctl stop dbconsole

Step 2: Connect to the database using the SYS account AS SYSDBA

SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql

Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.

The database has an Enterprise Manager Database Control repository.

Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.

3. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.

The OLAP Catalog component, AMD, exists in the database.

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.

4. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.

The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 5.1.3.00.05.

Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.

5. Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

There is one or more database triggers whose owner does not have the
right privilege on the database.

The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.

6. (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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
7. 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                  510 MB          723 MB
SYSTEM                  740 MB          1180 MB
TEMP                    20 MB           150 MB
UNDOTBS1                30 MB           446 MB

Minimum tablespace sizes for upgrade are estimates.

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

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

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

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

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

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

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

10. 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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
11. Check the Oracle documentation for the identified components for their
specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB

The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.

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

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


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcldb/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/orcldb/preupgrade/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-02-12T19:49:51
Run the preupgrade_fixup.sql
SQL> @/u01/app/oracle/cfgtoollogs/orcldb/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 19:49:43

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

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

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

Now run the dbua utility from 18c oracle_home location.

[oracle@ram ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0/db_1
[oracle@ram ~]$ cd /u01/app/oracle/product/18.3/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 11g listener, accept the defaults, so the listener will be upgraded.

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/orcldb/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-12 19:49:51

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

Preup                              Preupgrade
Action                             Issue Is
Number   Preupgrade Check Name     Remedied      Further DBA Action
------   ---------------------     ----------    -------------------
8.      old_time_zones_exist       YES           None.
9.      post_dictionary            YES           None.
10.     post_fixed_objects         NO            Informational only.
                                          Further action is optional.
11.     upg_by_std_upgrd           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         ORCLDB       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 18c Database Upgrade From 12.1.0.2 to 18.3.0.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,

DBUA provides a graphical user interface to guide you through the upgrade of Oracle Database.

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

Steps for upgrade from 12.1.0.2 to 18.3.0.0 using DBUA :-

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.

Remove EM Database Control :-

Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 18.0.0.0.0 ORACLE_HOME into the source 12.1.0.2 ORACLE_HOME.

SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

Remove OLAP Catalog :-

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

SQL> @catnoamd.sql

Start the 18c listener for dbupgrade :

[oracle@test preupgrade]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1 [oracle@test preupgrade]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin:$PATH

[oracle@test ]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 12-FEB-2019 17:42:09

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

Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 12-FEB-2019 17:42:09
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

 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 ~]$ /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/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-02-12T23:36:18

Preupgrade fixups :

[oracle@test preupgrade]$ export ORACLE_SID=dbwr18c 
[oracle@test preupgrade]$ export ORACLE_HOME=u01/app/oracle/product/12.1.0/db_1
[oracle@test preupgrade]$ export PATH=u01/app/oracle/product/12.1.0/db_1/bin:$PATH

SQL> @/u01/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 23:36:16

For Source Database: TEST18C
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. purge_recyclebin YES None.
2. apex_manual_upgrade NO Manual fixup recommended.
3. dictionary_stats YES None.
4. 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 INVALID objects count before upgrade :

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

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

Upgrade Database from 12.1.0.2 to 18c  using DBCA :-

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.

[oracle@test ~]$ export ORACLE_SID=dbwr18c
[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/:$PATH
[oracle@test ~]$ dbua

Select database name to upgrade to 18c version.

Preupgrade checks :

If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.

@apexins.sql APEX APEX TEMP /i/

After preupgrade warnings are resolved,select upgrade options to upgrade the database.

Select Recovery options to recover the database in case of issues.

Create listener or use existing listener which is running.

Select option if you want to configure EM express and port number.

Post Upgrade checks :

[oracle@test ~]$ cd /u01/preupgrade/
[oracle@test preupgrade]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 13 01:56:58 2019
Version 18.3.2.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.2.0.0

SQL> @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-12 23:36:17

For Source Database: TEST18C
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
------ --------------------- ------------ -----------------------------
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.

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       TEST18C        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 18c Database Upgrade From 11.2.0.3 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,

DBUA provides a graphical user interface to guide you through the upgrade of Oracle Database.

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

Steps for upgrade from 11.2.0.3 to 18.3.0.0 using DBUA :-

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.

Remove EM Database Control :-

Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 18.0.0.0.0 ORACLE_HOME into the source 11.2.0.3.0 ORACLE_HOME.

[oracle@test admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 12 08:38:14 2019

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

Remove OLAP Catalog :-

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

SQL> @catnoamd.sql

Start the 18c listener for dbupgrade :

[oracle@test preupgrade]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 
[oracle@test preupgrade]$ export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH

[oracle@test ]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 12-FEB-2019 17:42:09

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

Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 12-FEB-2019 17:42:09
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

 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 ~]$ /u01/app/oracle/product/11.2.0/dbhome_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/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-02-08T19:42:18

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

[oracle@test ~]$ cd /u01/preupgrade/
[oracle@test preupgrade]$ ls -lrt
total 648
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:38  
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 oracle
-rw-r--r-- 1 oracle oinstall 7963 Feb 8 19:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 14846 Feb 8 19:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 8 19:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 83854 Feb 8 19:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 14383 Feb 8 19:42 parameters.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 upgrade
-rw-r--r-- 1 oracle oinstall 50172 Feb 8 19:42 components.properties
-rw-r--r-- 1 oracle oinstall 1 Feb 8 19:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 14521 Feb 8 19:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 9914 Feb 8 19:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 11816 Feb 8 19:42 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@test preupgrade]$ export ORACLE_SID=dbwr18c

[oracle@test preupgrade]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@test preupgrade]$ export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH

[oracle@test ]$ cd /u01/preupgrade 

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 8 19:48:36 2019

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/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-08 19:42:16

For Source Database: DBWR18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg NO Informational only.
Further action is optional.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. 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 INVALID objects count before upgrade :

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

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

Upgrade Database from 11.2.0.3 to 18c  using DBCA :-

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.

[oracle@test ~]$ export ORACLE_SID=dbwr18c
[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/:$PATH
[oracle@test ~]$ dbua

Select database name to upgrade to 18c version.

Preupgrade checks :

If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.

@apexins.sql APEX APEX TEMP /i/

After preupgrade warnings are resolved,select upgrade options to upgrade the database.

Select Recovery options to recover the database in case of issues.

Create listener or use existing listener which is running.

Select option if you want to configure EM express and port number.

Post Upgrade checks :

SQL> @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.
WARNING - This script was generated for database DBWR18C.
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 19:42:17

For Source Database: DBWR18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name   Remedied Further DBA Action
------ ----------------------- --------- ---------------------------
10. depend_usr_tables          YES       None.
11. old_time_zones_exist       YES       None.
12. post_dictionary            YES       None.
13. post_fixed_objects         NO        Informational only.Further action is optional.
14. upg_by_std_upgrd           YES       None.

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       DBWR18C        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