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 12.1.0.2 to 18.3.0.0.0 Using Manual Method

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

Description:- 

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

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

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

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

Ensure backup is complete before upgrade.

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

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 Pre-upgrade checks :

Run the preupgrade tool .

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

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

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

Execute fixup scripts as indicated below:

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

After the upgrade:

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

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

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

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

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

 Run the preupgrade_fixup.sql

[oracle@test ]$ export ORACLE_SID=orcl

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

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

[oracle@test ]$ cd /u01/preupgrade

[oracle@test ~]$ sqlplus / as sysdba

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

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

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

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

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

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

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

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Check Timezone version :

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

SQL> SELECT version FROM v$timezone_file;

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

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

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

DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION     0
DST_UPGRADE_STATE            NONE

Upgrade Database from 12.1.0.2 to 18c :-

Shutdown the 12c database:

[oracle@test ~]$ sqlplus / as sysdba 

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

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

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

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

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

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

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

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

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

Run catcl.pl  script:

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

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

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

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

$ORACLE_HOME/bin/dbupgrade

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

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

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

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

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

Log file directory = [/home/oracle/]

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

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

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

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

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

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

Grand Total Time: 1836s

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

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

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

Execute Post-Upgrade Status Tool, utlu122s.sql.

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

SQL> @utlu122s.sql

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

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

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

Total Upgrade Time: 00:29:10

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

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

Execute utlrp.sql script to compile  invalid objects.

SQL> @utlrp.sql

Session altered.

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

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

PL/SQL procedure successfully completed.

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

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

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

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

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

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

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

Set COMPATIBLE parameter :

SQL> show parameter compatible;

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


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

System altered.

Restart the database :

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

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

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

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Upgrade Time Zone version :

Startup database in Upgrade mode.

SQL> startup upgrade
ORACLE instance started.

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

Start the upgrade window

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

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

PL/SQL procedure successfully completed.

Shutdown the database and startup in normal mode.

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

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

Upgrade Timezone version :

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

PL/SQL procedure successfully completed.

Check time zone file version :

SQL> SELECT * FROM v$timezone_file;

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


SQL> select TZ_VERSION from registry$database;

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

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

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

1 row updated.

SQL> commit;

Commit complete.

SQL> select TZ_VERSION from registry$database;

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

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

Post-upgrade checks:

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

SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

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

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

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

The fixup scripts have been run and resovled what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

Check Database version and status.

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

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

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

 

Catch Me On:- Hariprasath Rajaram 

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

 

 

Oracle 12c Background Process-LREG (Listener Registration)

LREG (Listener Registration Process) :-

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

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

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

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

To trace the service registration to Listener,

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

System altered.

SQL> alter system register;

System altered.

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

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

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

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

 

Catch Me On:- Hariprasath Rajaram 

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

Oracle 12c Database Cloning Using Hot Backup

Steps for Database Cloning using Hot Backup :-

  • Backup the parameter file

If SOURCE database is using spfile create pfile or if database is using pfile, use OS command to copy the pfile to a backup location.

  • Note down the oldest log sequence number.
  • Place the database to backup mode
  • Copy all data files of ‘Source’ database to a clone location.
  • After copying all datafiles, release the database from backup mode.
  • Switch the current log file and note down the oldest log sequence number
  • Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.
  • Take the control file trace backup to the trace path
  • Edit the clone database parameter file and make necessary changes to the clone
  • Startup the cloned database in NOMOUNT mode, using target pfile.
  • Create the control file for the clone database using the trace control file.
  • Create the control file by running trace file from the trace path
  • Recover the database using backup controlfile option.
  • Open the database with resetlogs option

Hot Backup :-

Hot Backups can be performed when the database is up and in Archive log mode.

  • First, check the v$database view to see if your database is set in Archive log mode:
SQL> select log_mode from v$database;

LOG_MODE
----------
ARCHIVELOG
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Enabled
Archive destination        /u01/app/oracle/product/12.1.0/db_1/dbs/arch
Oldest online log sequence 186
Current log sequence       187
  • Switch the current archivelogs,
SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archivelog
Oldest online log sequence     187
Next log sequence to archive   188
Current log sequence           188

Preparing Hot Backup for Database clone :-

  • Create parameter file
SQL> show parameter spfile;

NAME   TYPE	VALUE
------ -------- ------------------------------
spfile string   +DATA/orcl11g/spfileorcl11g.ora

SQL> create pfile='/home/oracle/initHCLONE.ora' from spfile;

File created.
  • Check number of tablespaces associated with database.
SQL> Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2

6 rows selected.
  • Ensure the tablespaces are ready for taking hot backup.
SQL> select * from v$backup;

FILE#      STATUS             CHANGE#    TIME      CON_ID
---------- ------------------ ---------- --------- ----------
1          NOT ACTIVE           0                  0
2          NOT ACTIVE           0                  0
3          NOT ACTIVE           0                  0
4          NOT ACTIVE           0                  0
5          NOT ACTIVE           0                  0
  • Put database in hot backup mode.
SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

FILE#      STATUS             CHANGE#    TIME      CON_ID
---------- ------------------ ---------- --------- ----------
1          ACTIVE             4282518    25-JAN-19 0
2          ACTIVE             4282518    25-JAN-19 0
3          ACTIVE             4282518    25-JAN-19 0
4          ACTIVE             4282518    25-JAN-19 0
5          ACTIVE             4282518    25-JAN-19 0
  • Create appropriate directory structure in clone database.
asmcmd> mkdir  +DATA/HCLONE/CONTROLFILE
asmcmd> mkdir  +DATA/HCLONE/DATAFILE
asmcmd> mkdir  +DATA/HCLONE/ONLINELOG

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE/adump
  • Copy the tablespaces from source to target location.
ASMCMD> cd ORCL11G/DATAFILE

ASMCMD> cp SYSTEM.256.995570219 +DATA/HCLONE/DATAFILE/system.dbf
copying +DATA/ORCL11G/DATAFILE/SYSTEM.256.995570219 -> +DATA/HCLONE/DATAFILE/system.dbf

ASMCMD> cp SYSAUX.257.995570219 +DATA/HCLONE/DATAFILE/sysaux.dbf
copying +DATA/ORCL11G/DATAFILE/SYSAUX.257.995570219 -> +DATA/HCLONE/DATAFILE/sysaux.dbf

ASMCMD> cp UNDOTBS1.258.995570219 +DATA/HCLONE/DATAFILE/undotbs1.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS1.258.995570219 -> +DATA/HCLONE/DATAFILE/undotbs1.dbf

ASMCMD> cp UNDOTBS2.264.995570411 +DATA/HCLONE/DATAFILE/undotbs2.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS2.264.995570411 -> +DATA/HCLONE/DATAFILE/undotbs2.dbf

ASMCMD> cp USERS.259.995570219 +DATA/HCLONE/DATAFILE/users.dbf
copying +DATA/ORCL11G/DATAFILE/USERS.259.995570219 -> +DATA/HCLONE/DATAFILE/users.dbf
  • After copying datafiles to backup location,release database from Hot backup mode.
SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE#      STATUS     CHANGE# TIME      CON_ID
---------- ---------- ------- --------- ------
1          NOT ACTIVE 4282518 25-JAN-19 0
2          NOT ACTIVE 4282518 25-JAN-19 0
3          NOT ACTIVE 4282518 25-JAN-19 0
4          NOT ACTIVE 4282518 25-JAN-19 0
5          NOT ACTIVE 4282518 25-JAN-19 0
  • Switch the current log sequence and note down the oldest log sequence number.
SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 188
Next log sequence to archive 189
Current log sequence 189
  • Backup the controlfile as trace.
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.
  • Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.
[oracle@racpb1 archivelog]$ ls -lrt
total 6636

-rw-r----- 1 oracle oinstall 4622848 Jan 25 00:39 1_187_995570300.dbf
-rw-r----- 1 oracle oinstall 2160128 Jan 25 01:19 1_188_995570300.dbf

Restoration using hot backup :-

  • Edit the clone database parameter file and make necessary changes to the HCLONE database.
HCLONE.__db_cache_size=436207616
HCLONE.__java_pool_size=4194304
HCLONE.__large_pool_size=8388608
HCLONE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
HCLONE.__pga_aggregate_target=373293056
HCLONE.__sga_target=700448768
HCLONE.__shared_io_pool_size=0
HCLONE.__shared_pool_size=243269632
HCLONE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/HCLONE/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/HCLONE/controlfile/current.260.995570299'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain.com'
*.db_name='HCLONE'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HCLONEXDB)'
*.event=''
*.log_archive_dest_1='LOCATION=/u01/archivelog'
*.memory_target=1073741824
*.open_cursors=300
*.processes=300
*.remote_listener='racsn:1521'
*.remote_login_passwordfile='EXCLUSIVE'
HCLONE.undo_tablespace='UNDOTBS'
  • Startup database in NOMOUNT stage using target PFILE.
[oracle@racpb1 ~]$ export ORACLE_SID=HCLONE
[oracle@racpb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 25 01:40:01 2019

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

Connected to an idle instance.

SQL> startup pfile='/home/oracle/initHCLONE.ora' nomount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 675283048 bytes
Database Buffers 390070272 bytes
Redo Buffers 5455872 bytes
  • Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles

Note: Change the “REUSE” parameter to “SET” and RESETLOGS option.

CREATE CONTROLFILE SET DATABASE "HCLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/HCLONE/onlinelog/group_1'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/HCLONE/onlinelog/group_2'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/HCLONE/onlinelog/group_3'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '+DATA/HCLONE/onlinelog/group_4'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/HCLONE/datafile/system.dbf',
  '+DATA/HCLONE/datafile/sysaux.dbf',
  '+DATA/HCLONE/datafile/undotbs1.dbf',
  '+DATA/HCLONE/datafile/users.dbf',
  '+DATA/HCLONE/datafile/undotbs2.dbf'
CHARACTER SET AL32UTF8
;

Control file created.
SQL> select status from v$instance;

STATUS
--------
MOUNTED
  • Recover the database using backup controlfile option.
SQL> recover database using backup controlfile until cancel;

ORA-00279: change 4283965 generated at 01/25/2019 01:19:16 needed for thread 1
ORA-00289: suggestion : /u01/archivelog/1_189_995570300.dbf
ORA-00280: change 4283965 for thread 1 is in sequence #189


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
  • Open database using resetlogs option :
SQL> alter database open resetlogs;

Database altered.
  • Check the database name and status :
SQL >select database_name, open_mode from v$database;

DATABASE_NAME OPEN_MODE
------------- ------------
HCLONE        READ WRITE
Catch Me On:- Hariprasath Rajaram 

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

Oracle 12c Background Process-MMAN (Memory Manager)

 

MMAN dynamically adjust the sizes of the SGA components like buffer cache, large pool, shared pool and java pool and serves as SGA memory broker.

It is a new process added to Oracle 10g as part of automatic shared memory management.

It used for internal database tasks such as Automatic Shared Memory Management (ASMM)

ASMM

Automatic Shared Memory Management, or ASMM is what you do when setting SGA_TARGET and not setting MEMORY_TARGET. Basically, you define the size of the SGA you want to allocate at startup and that will be available for the instance, most of it being buffer cache and shared pool.

SGA_TARGET and SGA_MAX_SIZE because on the most common platforms, all is allocated at instance startup. Then, in addition to this shared area used by all instance processes, each processes can allocate private memory, and you control this with PGA_AGGREGATE_TARGET.

The total size of SGA and PGA for all instances in a system must reside in physical memory for the simple reason that they are mostly used to avoid I/O (a large buffer cache avoids physical reads and optimizes physical writes, a large PGA avoids reads and writes to temp files).

MMAN background process manages the dynamic resizing of SGA memory components depending on load for database.

Responsible for managing instance memory based on the workloads.

Uses the the metrics collected to determine the ideal distribution of memory within oracle. It constantly monitors the database and adjusts the memory allocations according to workloads.

 

 

 

Catch Me On:- Hariprasath Rajaram 

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

Oracle 12c Background Process- CTWR (Change Tracking Writer)

 

CTWR (Change Tracking Writer) :-

  • CTWR (Change Tracking Writer) is the background process responsible for tracking the blocks.
  • CTWR will be useful in RMAN. Optimized incremental backups using block change tracking (faster incremental backups) using a file (named block change tracking file).
  • CTWR process is to write the changed block information in BCT file.

Block Change Tracking (file):-

RMAN’s change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

Change tracking is disabled by default, because it introduces some minimal performance overhead on database during normal operations. However, the benefits of avoiding full datafile scans during backup are considerable, especially if only a small percentage of data blocks are changed between backups. If backup strategy involves incremental backups, then we should enable change tracking.

One change tracking file is created for the whole database. By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can also specify the name of the block change tracking file, placing it in any desired location.

Using change tracking in no way changes the commands used to perform incremental backups, and the change tracking files themselves generally require little maintenance after initial configuration.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

Oracle saves enough change-tracking information to enable incremental backups to be taken using any of the 8 most recent incremental backups as its parent.

Although RMAN does not support backup and recovery of the change-tracking file itself, if the whole database or a subset needs to be restored and recovered, then recovery has no user-visible effect on change tracking. After the restore and recovery, the change tracking file is cleared, and starts recording block changes again. The next incremental backup after any recovery is able to use change-tracking data.After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

You can view the size of the CTWR dba buffer by looking at v$sgastat:

SQL> SELECT * FROM v$sgastat WHERE name like ‘CTWR%’;

POOL           NAME          BYTES
-------- -------------       -------
large pool  CTWR dba buffer  1220000

Enable Block Change Tracking:-

SQL> show parameter db_create_file_dest

NAME                TYPE   VALUE
----------------    -----  -----
db_create_file_dest string


SQL> alter system set db_create_file_dest='/home/oracle/app/oracle' scope=both;
System altered.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

System altered.

select filename, status, bytes from   v$block_change_tracking

STATUS       FILE                                    BYTES
-----------  -------------------------------------- --------------
ENABLED      /home/oracle/app/oracle/01_mf_nsrv.chg  10,000,000

Manually specify location for BCT :

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/app/oracle/block_change_tracking.dbf’;

or

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/app/oracle/block_change_tracking.dbf’ REUSE;

Disable Block Change Tracking:-

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> select * from V$BLOCK_CHANGE_TRACKING;

STATUS       FILE                                    BYTES
-----------  --------------------------------------  --------------
DISABLED      /home/oracle/app/oracle/               10,000,000
              block_change_tracking.dbf

 

Catch Me On:- Hariprasath Rajaram 

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

Oracle 12c Background Process-MMON (Manageability Monitor)

 

The manageability monitor process supports the Automatic
Workload Repository (AWR) by capturing statistics, monitoring
thresholds, and taking snapshots.

This is related to performance tuning and troubleshooting.

Restart MMON process without bouncing the database:

alter system enable restricted session;

alter system disable restricted session;

check status of mmon process, if it found as not running.

$ ps -ef |grep mmon

SQL> grant restricted session to public;
SQL> alter system enable restricted session;
SQL> alter system disable restricted session;
SQL> revoke restricted session from public;

 

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR).

For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.

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

How To Uninstall Oracle Database 12c

oracle software deinstall

Starting with Oracle Database 12c Release 1 (12.1.0.2), the roothas.sh script replaces the roothas.pl script in the Oracle Grid Infrastructure home.

[oracle@Ram dbhome_1]$ cd deinstall/

[oracle@Ram deinstall]$ ls
bootstrap_files.lst deinstall deinstall.xml readme.txt sshUserSetup.sh
bootstrap.pl deinstall.pl jlib response utl

The most common method of running the deinstallation tool is to use the version installed in the Oracle home being removed.

The deinstallation tool determines the software configuration for the local Oracle home, and then provides default values at each prompt.

You can either accept the default value, or override it with a different value.

If the software in the Oracle_home is not running (for example, after an unsuccessful installation), then the deinstallation tool cannot determine the configuration, and you must provide all the configuration details either interactively or in a response file.

You must run the deinstallation tool from the same release to remove Oracle software. Do not run the deinstallation tool from a later release to remove Oracle software from an earlier release.

For example, do not run the deinstallation tool from the Oracle Database 12.2 installation media to remove Oracle software from an existing 11.2.0.4 Oracle home.

[oracle@Ram deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2019-01-17_07-49-52PM/logs/

############ ORACLE DECONFIG TOOL START ############


############ DECONFIG CHECK OPERATION START ###################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/12.2.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/netdc_check2019-01-17_07-51-25-PM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/databasedc_check2019-01-17_07-51-27-PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []: 
Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /tmp/deinstall2019-01-17_07-49-52PM/logs//ocm_check8100.log
Oracle Configuration Manager check END

############# DECONFIG CHECK OPERATION END ######################


############## DECONFIG CHECK OPERATION SUMMARY ####################
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.2.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.err'

############### DECONFIG CLEAN OPERATION START ######################
Database de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/databasedc_clean2019-01-17_07-51-59-PM.log

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/netdc_clean2019-01-17_07-51-59-PM.log

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /tmp/deinstall2019-01-17_07-49-52PM/logs//ocm_clean8100.log
Oracle Configuration Manager clean END

################### DECONFIG CLEAN OPERATION END ######################


################## DECONFIG CLEAN OPERATION SUMMARY ###################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
#######################################################################


############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2019-01-17_07-49-52PM/response/deinstall_2019-01-17_07-51-16-PM.rsp
Location of logs /tmp/deinstall2019-01-17_07-49-52PM/logs/

############ ORACLE DEINSTALL TOOL START ############

############# DEINSTALL CHECK OPERATION SUMMARY #####################
A log of this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.err'

############## DEINSTALL CLEAN OPERATION START ####################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to Ram
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2019-01-17_07-49-52PM/oraInst.loc
Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/12.2.0/dbhome_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/12.2.0/dbhome_1' on the local node : Done

Delete directory '/u01/app/oraInventory' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is not empty.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##


## [END] Oracle install clean ##


############### DEINSTALL CLEAN OPERATION END #####################


############## DEINSTALL CLEAN OPERATION SUMMARY ###################
Successfully detached Oracle_home '/u01/app/oracle/product/12.2.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.2.0/dbhome_1' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.


Run 'rm -r /etc/oraInst.loc' as root on node(s) 'Ram' at the end of the session.

Run 'rm -r /opt/ORCLfmap' as root on node(s) 'Ram' at the end of the session.
Run 'rm -r /etc/oratab' as root on node(s) 'Ram' at the end of the session.
Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'Ram'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL TOOL END #############

[oracle@Ram deinstall]$

 

If the software in the Oracle_home is not running (for example, after an unsuccessful installation), then the deinstallation tool cannot determine the configuration and you must provide all the configuration details either interactively or in a response file.

 

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

Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Description:-

In this article we are going to see Zero downtime database upgrade from 11g to 12c using Oracle Goldengate
1)Using Goldengate we are going to upgrade from 11g to 12c database without any downtime.
2)Already Data is available source11g scheme Hari.

Environment Detail’s:-

High Level Steps  upgrade from 11g to 12c database

1)check network between source and target.
2)install goldengate Software both side
3)setup extract and datapump on source site
4)setup replict on target side
5)export and import initial load using SCN
6)start the replicat using on scn

Source 11g database side GG Configuration

Step1:-Login in to 11g server and connect to Goldengate 

[oracle@gg-11 gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (gg-11.2.com) dblogin userid gguser,password gguser
Successfully logged into database.

Step2:-Configure manager 

GGSCI (gg-11.2.com)view param mgr
PORT 7809

Step3:-Add schematrandata

GGSCI (gg-11.2.com )  add schematrandata hari

2018-07-31 19:56:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema hari.
2018-07-31 19:56:12 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hari.

GGSCI (gg-11.2.com ) info schematrandata hari

2018-07-31 19:56:34 INFO OGG-01785 Schema level supplemental logging is enabled on schema HARI.
2018-07-31 19:56:34 INFO OGG-01980 Schema level supplemental logging is enabled on schema HARI for all scheduling columns.

Step4:-Configure EXTRACT Process 

GGSCI (gg-11.2.com) view param ext1

EXTRACT ext1
SETENV (ORACLE_SID=”SOURCE”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/gghome/dirdat/ac
TABLE hari.*;

GGSCI (gg-11.2.com ) add extract ext1 tranlog begin now
EXTRACT added.

GGSCI (gg-11.2.com )  add exttrail /u01/gghome/dirdat/ac extract ext1
EXTTRAIL added.

GGSCI (gg-11.2.com as gguser@source)  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:00:40

Step4:-Configure Pump Process  

GGSCI (gg-11.2.com )  view param dpump1

EXTRACT dpump1
USERID gguser@source, PASSWORD gguser
RMTHOST gg-12.2, MGRPORT 7810
RMTTRAIL /u01/gghome/dirdat/ad
DDL INCLUDE ALL
TABLE hari.*;

GGSCI (gg-11.2.com) add extract dpump1 exttrailsource /u01/gghome/dirdat/ac
EXTRACT added.

GGSCI (gg-11.2.com ) add rmttrail /u01/gghome/dirdat/ad extract dpump1
RMTTRAIL added.

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED DPUMP1 00:00:00 00:01:00
EXTRACT STOPPED EXT1 00:00:00 00:04:40

Step5:-Start all the process and Check

GGSCI (gg-11.2.com )  start ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (gg-11.2.com )  start dpump1

Sending START request to MANAGER …
EXTRACT DPUMP1 starting

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:04:33
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Target side 12c database GG Configuration

Step1:-Configure manager 

GGSCI (gg-12.2.com) 9> dblogin userid gguser,password gguser
Successfully logged into database.

GGSCI (gg-12.2.com ) info mgr

Manager is running (IP port gg-12.2.com.7810, Process ID 3999).

GGSCI (gg-12.2.com ) view param mgr

PORT 7810

Step2:-Checkpoint table creation

GGSCI (gg-12.2.com)add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.

Step3:-Configure Replicat Process 

GGSCI (gg-12.2.com) view param rep1

REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;

GGSCI (gg-12.2.com )  add replicat rep1 exttrail /u01/gghome/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.

GGSCI (gg-12.2.com ) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:03

Source side check before datapump starts

GGSCI (gg-11.2.com ) info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:00
EXTRACT RUNNING EXT1 00:00:00 00:00:10

Start the initial dataload using Datapump on source side 11g database

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1584462

SQL> select count(*) from hari.chennai;

COUNT(*)
———-
1835008

Step1:-Export the table “chennai” using flashback_scn

[oracle@gg-11 gghome]$ expdp system/oracle dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462

Export: Release 11.2.0.3.0 - Production on Tue Jul 31 20:02:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/source/dpdump/hari1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:02:45

Step2:-copy the datapump files to 11g server to 12c server

[oracle@gg-11 gghome]$ cd /u01/app/oracle/admin/source/dpdump/
[oracle@gg-11 dpdump]$ scp hari1.dmp oracle@192.168.2.157:/u01/app/oracle/admin/source/dpdump/
The authenticity of host ‘192.168.2.157 (192.168.2.157)’ can’t be established.
RSA key fingerprint is c2:3d:72:16:52:01:ae:5c:41:6b:34:f5:c7:a1:df:74.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.2.157’ (RSA) to the list of known hosts.
oracle@192.168.2.157’s password:
hari1.dmp 100% 73MB 72.8MB/s 00:01

Step3:-After export i am inserting more records in Chennai table

[oracle@gg-11 ~]$ sqlplus hari/hari
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 31 20:06:47 2018
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> insert into chennai select * from chennai;
1835008 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from chennai;
COUNT(*)
———-
3670016     

Step4:-Now we are going to Import 12c database (1835008 Rows)

[oracle@gg-12 dpdump]$ impdp system/oracle dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR

Import: Release 12.2.0.1.0 - Production on Tue Jul 31 20:09:23 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 20:09:57 2018 elapsed 0 00:00:24

Step5:-Target (12c) side going to start replicat process Using CSN 

GGSCI (gg-12.2.com ) start replicat rep1 aftercsn 1584462    ---(this scn number we get from 11g database)

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg-12.2.com )info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01

Note :-Already initial load was completed using datapump,
Now extract was capturing all the changes happening on the source database 11g. These changes will now get applied to the target database 12c by the replicat process

[oracle@gg-12 dpdump]$ sqlplus hari/hari

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 20:19:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Tue Jul 31 2018 20:14:07 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from chennai;

COUNT(*)
----------
3670016 

Repoint the users from 11g to 12c server.

Successfully completed without downtime upgrade

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