Oracle 12c to 19c upgrade using AutoUpgrade Method

In this blog, we will see how to upgrade Oracle Database 12c to 19c using AutoUpgrade Tool

What is the AutoUpgrade?

  • The Oracle Database AutoUpgrade utility is a small command-line tool that allows you to upgrade your databases very easily with very little interaction
  • The new AutoUpgrade utility in Oracle 19¢ performs almost 99% of the task by itself, we just have to provide inputs during the initial phase
  • So it performs Prechecks against multiple databases, upgrades multiple databases in one go
  • Also, it does Post upgrade, object recompilation, and time zone up-gradation
  • The only thing you need to provide is a contig file in text format

Which database releases are supported?

  • As a source, the minimum version is Oracle Database 11.2.0.4. onwards

Download the latest auto-upgrade jar file

  • Auto upgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory of Oracle 19c Home
  • Oracle strongly recommends downloading the latest AutoUpgrade version before doing the upgrade
  • You download the most recent version from MOS Note: 2485487.1  AutoUpgrade Tool
  • Once you download this jar file transfer it to the Server and create a new directory and place this file in that directory

cp -r autoupgrade.jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/

Java version:

  • Java version should be 8 or later, which is available by default in Oracle Database homes from release 12.1.0.2 and latest

Create the config file

  • Create New Directory Which will contain your config file and logs
  • mkdir /home/oracle/auto_upgrade_19c

Run Below command to create sample config file which we will use to make final config file.

  • cd /home/oracle/auto_upgrade_19c
  • $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file_config
  • cp sample_config.cfg prod_db_config.cfg
  • Make necessary changes

Modify the config file according to your env

prddb_config.cfg file should have following entry which specifies source and target database home location and DB name and log locations and other information’s:

global.autoupg_log_dir=/home/oracle/auto_upgrade_19c/upg_logs
upg1.dbname=PRDDB
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1/db_1
upg1.target_home=/u01/app/oracle/product/19.0.0/db_1
upg1.sid=PRDDB
upg1.log_dir=/home/oracle/auto_upgrade_19c/upg_logs/PRDDB
upg1.upgrade_node=orclagent.localdomain
upg1.target_version=19.12
upg1.run_utlrp=yes
upg1.timezone_upg=yes

Analyze the database using modified config file

Auto upgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.

Execute AutoUpgrade in analyze mode with the below syntax:

export ORACLE HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin

cd /home/oracle/auto_upgrade_19c

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config prddb_config.cfg -mode ANALYZE

We can monitor, manage and control the jobs from the autoupgrade console.

  • lsj – to list the jobs
  • status – to show the job status
  • tasks – shows the tasks executing

Crosscheck and verify all logs before proceeding further

Deploy the upgrade and monitor

  • Auto upgrade Deploy mode performs the actual upgrade of the database from pre-upgrade source database analysis to post-upgrade checks.
  • Before starting Deploy make sure you have taken a backup of your database.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config
prod_db_config.cfg -mode DEPLOY

  • Once the upgrade process is started consider monitoring the logs and database alert logs to see the progress of the upgrade. Auto upgrade logs are available under

/home/oracle/auto_upgrade_19c/upg_logs/

Once the upgrade finishes crosscheck the below.

SELECT VERSION FROM V$TIMEZONE_FILE;
select name, open_mode, version, status from v$database, v$instance;

Post-upgrade task

  • Once the upgrade is successful and all testing is done, drop the restore point.
  • Drop the Guaranteed restore point

select name from v$restore_point;
drop restore point restorepoint_name;

  • Change the compatible parameter
  • After the upgrade, the database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.

show parameter compatible;
alter system set compatible=’19.0.0′ scope=spfile;
shutdown immediate:
startup;
show parameter compatible;

Hope this blog was useful…

Please find out all of our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

 

How to change Apps Password in R12.2

 

Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well as some manual steps after changing the password using FNDCPASS.

Whether you use FNDCPASS or AFPASSWD to change the APPLSYS/APPS password, you must also perform some additional steps. This is because in R12.2, the old AOL/J connection pooling is replaced with Weblogic Connection Pool ( JDBC Data source ).  Currently this procedure is not yet automated. It would be good, if this can be automated using some WLS scripting.

Important: These steps must be carried out on the run file system.

  1. Shut down the application tier services using the $INST_TOP/admin/scripts/adstpall.sh script.
  1. Change the APPLSYS password, as described for the utility you are using.
  1. Start Admin Server using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.
  1. Change the “apps” password in WLS Data source as follows:
    •  Log in to WLS Administration Console.
    • Click Lock & Edit in Change Center.
    • In the Domain Structure tree, expand Services, then select Data Sources.
    • On the “Summary of JDBC Data Sources” page, select EBSDataSource.
    • On the “Settings for EBSDataSource” page, select the Connection Pool tab.
    • Enter the new password in the “Password” field.
    • Enter the new password in the “Confirm Password” field.
    • Click Save.
    • Click Activate Changes in Change Center.
  1. Start all the application tier services using the $INST_TOP/admin/scripts/adstrtal.sh script.
  1. Verify the WLS Datastore changes as follows:
  2. Log in to WLS Administration Console.
  3. In the Domain Structure tree, expand Services, then select Data Sources.
  4. On the “Summary of JDBC Data Sources” page, select EBSDataSource.
  5. On the “Settings for EBSDataSource” page, select Monitoring > Testing.
  6. Select “oacore_server1“.
  7. Click Test Data Source
  8. Look for the message “Test of EBSDataSource on server oacore_server1 was successful“.

Important: Steps 4, 5 and 6 are only applicable when changing the APPLSYS password. They are not applicable when changing passwords for product schemas or the SYSTEM schema.

In the next prepare phase after the password change, adop will invoke EBS Domain Configuration to ensure that the WLS data source on the patch file system will be synchronized with the new APPS password.

How To Change The Password For The SYSADMIN User
12.2 New Feature AFPASSWD utility to change password

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to change priority of concurrent request

 

Change the priority of the concurrent request

By changing priority of concurrent request you can run the important concurrent request before non-priority concurrent request.

  1. Open “System Administrator” responsibility. Go to Concurrent > Requests
  2. Select “Specific Requests“. In the field called “Requester” put the User ID for whom you want to change priority.
  3. Select “View Details“. Change the Priority from default “50“. Change it to “10” for high priority. [1 for highest and 99 for lowest].

Change the User priority

You can set the priority of individual user priority. For high priority user [CFO or CEO :-)] the report will get completed the request will soon start to run than a normal priority user. Change the value of profile option “Concurrent:Request Priority” in User level to make him or her a high or low priority user.

Alternatively you can change the priority of a “Concurrent Request” in define concurrent request screen.

You can do the following two steps to increase the performance of concurrent manager.

  1. Schedule the following concurrent request program “Purge Concurrent Requests and / or Manager Data“.

  2. Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid.  While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details
 
    col owner for a30
    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;


Objects which are invalids in the database along with the OWNER name
    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.
alter package <package_name> compile body;
alter package <package_name> compile;
Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER
and MATERIALIZED VIEW
Use the same above query to compile.
alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;
DBMS_DDL Package method:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EBS 12.2 Method
 
SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>
utlrp.sql tool
 
Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql
EBS 12.2 best methods to compile entire Schema
 
SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>
Compile Invalids with parallel thread
SQL> EXEC UTL_RECOMP.recomp_parallel(10);
Verify the Invalids
    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid.  While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details
 
    col owner for a30
    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;


Objects which are invalids in the database along with the OWNER name
    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.
alter package <package_name> compile body;
alter package <package_name> compile;
Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER
and MATERIALIZED VIEW
Use the same above query to compile.
alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;
DBMS_DDL Package method:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EBS 12.2 Method
 
SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>
utlrp.sql tool
 
Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql
EBS 12.2 best methods to compile entire Schema
 
SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>
Compile Invalids with parallel thread
SQL> EXEC UTL_RECOMP.recomp_parallel(10);
Verify the Invalids
    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19C UPGRADE METHODS ALL IN ONE

UPGRADE METHODS :

AUTO UPGRADE SAME SERVER – NEW FEATURE

CDB & PDB USING MANUAL UPGRADE

MANUAL UPGRADE NON CDB

12C TO 19C DBUA UPGRADE

12C TO 19C UPGRADE USING DATAPUMP

12C TO 19C GRID UPGRADE

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Upgrading a CDB & PDB from 12c to 19c using Manual Upgrade

In this article we are going to see how to upgrade a oracle 12c container database from 12c to 19c using manual upgrade method.

Here we are going to proceed with the same steps as we followed while upgrading a non container database from oracle 12c to 19c.

In my environment I am having one container database and two pluggable database in it. Overview Steps:  

Step:1 Create a new directory called backup, Take, Take backup of listener.ora, tnsnames.ora files ,then move all the files to the newly created directory called backup.

Step:2 Create a new directories called upgrade, while upgrade, Run the preupgrade.jar file which is in  the location. Move the summary of the preupgrade script to the upgrade directory.

/home/oracle/u03/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar 
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar

Step:3 Three files named preupgrade.log, preupgrade_fixups.sql, postupgrade_fixups.sql files     will be created after running the preupgrade script.

Step:4 We should execute the below scripts,

 Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql

After upgrade    – @/u01/preupgrade/postupgrade_fixups.sql

Step:5 Then check the preupgrade.log whether it is completed without any errors.

Step:6 Verify the tablespace sizes for upgrade.(enable the AUTO EXTENT ON and set the maximum size for tablespaces)

Step:7 Gather the dictionary statistics query to get the statistical data of fixed objects. we   should gather dictionary statistics  before and after the upgrade to maintain better database     performance. Because dictionary tables are modified and created during the upgrade.

Step:8 Purge the recyclebin

Step:9 Refresh all the materialized views before upgrade.

Step:10 Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.

 Step:11 You should enable the archive log list, it is mandatory to perform an upgrade.

Step:12 Create the flashback restore point before performing upgrade, it is important suppose if dbupgrade fails you can recover the database from this restore point.

Step:13 Set the db_recovery_file_dest_size=10GB

UPGRADE STEPS:

After we have completed all our pre-upgrade steps,

Step:14 shut down the database, copy the spfile, password files from 12c home to 19c home.

Step:15 startup the database in upgrade mode from 19c home.

Step:16 Run the upgrade, $ORACLE_HOME/bin/dbupgrade file. Here starts the manual upgrade process.

POST UPGRADE STEPS:  

Step:17 Run catcon.pl to start utlrp.sql to recompile the invalid objects.

Step:18 Then check for any invalid objects in the database after upgrade.

Step:19 Verify the timezone version of the upgraded database.(it should be changed to 32),for     that run utlusts.sql script.

 Step:20 We have to verify the DBA_REGISTRY view, it displays the information about the     components  loaded in the database.

Step:21 Now we can drop the restore point that we have created already before the upgrade.

Step:22 Update the compatible parameter value, it will be set in the base database version we have to update the upgraded database version in the compatible parameter value.

Step:23 Finally check the listener.ora and tnsnames.ora files  it should be updated to a upgraded database version.

PRE-UPGRADE TASK:

Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.

Backup TNS Files listener spfile and passwordfile.

[oracle@localhost ~]$ mkdir -p /u01/backup
[oracle@localhost u01]$ cd backup/
[oracle@orcldbs backup]$ ll
total 20
-rwxr-xr-x. 1 oracle oinstall 343 Mar 09 03:32 listener21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 555 Mar 09 03:32 listener.ora
-rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet.ora
-rwxr-xr-x. 1 oracle oinstall 614 Mar 09 03:32 tnsnames.ora
[oracle@orcldbs backup]$

Step:2 Login to the database &  Run the preupgrade_fixups.sql

Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.

Step:4 Purge the  Recyclebin, it gains some storage space and on other hand we have chosen the recovery options.

Step:5 Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing. 

Step:6 Verify archive log mode and enable it ,if not

 

Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.

Step:8 Copy init and password files from 12c home  to 19c  home.

preupgrade_CDB_ROOT.log

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:20

Upgrade-To version: 19.0.0.0.0

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

to read more lines click here.(preupgrade_CDB_ROOT.log)

preupgrade_PDB_SEED.log

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43

Upgrade-To version: 19.0.0.0.0

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

For more information click here.(preupgrade_PDB_SEED.log)

preupgrade_ARGENTINA.log

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43

Upgrade-To version: 19.0.0.0.0

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

Click here to view more lines.(preupgrade_ARGENTINA.log)

preupgrade_BRAZIL.log

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:42:09

Upgrade-To version: 19.0.0.0.0

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

To view the entire log file, click here.(preupgrade_BRAZIL.log)

For detailed information click here to view the preupgrade.log

UPGRADE TASK:

Step:9 Startup the database in Upgrade mode from 19c home, Run dbupgrade, $ORACLE_HOME/bin/dbupgrade file. Here starts the manual upgrade process. 

To view the complete database upgrade process, click here.

[oracle@localhost ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
[oracle@localhost admin]$ mkdir -p /u01/whileupgrade
[oracle@localhost admin]$  nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl 
catctl.pl -l /u01/whileupgrade -n 4 catupgrd.sql &
[1] 24608

[oracle@orcldbs admin]$ nohup: ignoring input and appending output to ‘nohup.out’
Start of Summary Report
------------------------------------------------------
Oracle Database Release 19 Post-Upgrade Status Tool    03-09-2021 06:06:0
Database Name: EUROPE
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
Oracle Server                          UPGRADED      19.3.0.0.0  00:18:34
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:01:34
Oracle XDK                             UPGRADED      19.3.0.0.0  00:00:47
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:13
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:12
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:06
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:20
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:31
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:00:27
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:01:31
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:00:47
Spatial                                UPGRADED      19.3.0.0.0  00:05:33
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:10
Datapatch                                                        00:03:27
Final Actions                                                    00:03:35
Post Upgrade                                                     00:00:17

Total Upgrade Time: 01:55:38

Database upgrade completed.

Step:10 Startup DB from 19c home.

POST-UPGRADE TASKS:

Step:-11 Run catcon.pl(it is used used to execute the command in all PDBs and CDB database)  to start utlrp.sql to recompile invalid objects.

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

Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN                   2021-03-09 06:30:35

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                    2021-03-09 06:34:29

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.

Step:12 Upgrade the time zone by running the following scripts,

First Script $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql 

Second Script $ORACLE_HOME/rdbms/admin/ utltz_upg_apply.sql 

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

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
INFO: There are no open PDBs .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

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

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
INFO: There are no open PDBs .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area     2432695832 bytes
Fixed Size                      9137688 bytes
Variable Size                 654311424 bytes
Database Buffers             1761607680 bytes
Redo Buffers                    7639040 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen …
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area     2432695832 bytes
Fixed Size                      9137688 bytes
Variable Size                 654311424 bytes
Database Buffers             1761607680 bytes
Redo Buffers                    7639040 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen …
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
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: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.

Step:13 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.

Step:14 Drop Restore point as we have successfully completed the manual upgradation.

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

Step:15 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0

Step:16 Verify DBA_REGISTRY 

Step:17 Add TNS Entries in 19c TNS home 

[oracle@localhost admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:41
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-MAR-2021 07:56:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...

Service "europe" has 1 instance(s).
Instance "europe", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:50
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-MAR-2021 07:56:41
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "europe" has 1 instance(s).
Instance "europe", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Step:18 Edit oratab,Put an entry of current database in the vi /etc/oratab file.

[oracle@localhost admin]$ cat /etc/oratab | grep -i europe
europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N
[oracle@localhost admin]$ cat /etc/oratab | grep -i europe
europe:/u01/app/oracle/product/19.0.0/dbhome_1:Y
europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N

In this document, We have seen how to upgrade an oracle 12c container database to 19c using Manual upgrade technique.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

 

Upgrade Oracle Database Manually from 12.2 to 19c

An another way to upgrade a database from lower version to higher version is we have to manually upgrade a database (ie) we have to verify whether it meets  all the pre-requests manually instead of using dbua (GUI) / datapump.

Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.

Backup TNS Files listener spfile and passwordfile .

[oracle@orcldbs ~]$ mkdir -p /u01/backup 
[oracle@orcldbs u01]$ cd backup/
[oracle@orcldbs backup]$ ll
total 20
-rwxr-xr-x. 1 oracle oinstall 343 Jan 30 23:32 listener21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 555 Jan 30 23:32 listener.ora
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet.ora
-rwxr-xr-x. 1 oracle oinstall 614 Jan 30 23:32 tnsnames.ora
[oracle@orcldbs backup]$
[oracle@orcldbs ~]$ /u01/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar 
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
FILE DIR /u01/upgrade


==================
PREUPGRADE SUMMARY
==================

/u01/upgrade/preupgrade.log
/u01/upgrade/preupgrade_fixups.sql
  /u01/upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

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

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

Step:2 Login to the database &  Run the preupgrade_fixups.sql

Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.

SET ECHO ON; 
SET SERVEROUTPUT ON;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; 

 PL/SQL procedure successfully completed. 

Step:4 Purge the  Recyclebin,it gains some storage space and on other hand we have choosen the recovery options.

SQL>  PURGE DBA_RECYCLEBIN; 

DBA Recyclebin purged

Step:5 Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing. 

SQL>  declare 
list_failures integer(3) :=0; 
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;


PL/SQL procedure successfully completed. 

Step:6 Verify archive log mode and enable it ,if not

SQL> archive log list
Database log mode                No Archive Mode
Automatic archival                  Disabled
Archive destination                /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence     1
Current log sequence           1

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size                                8623592 bytes
Variable Size                        654314008 bytes
Database Buffers             1761607680 bytes
Redo Buffers                         8151040 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list
Database log mode                Archive Mode
Automatic archival                  Enabled
Archive destination                /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> show parameter rec

NAME                                                 TYPE VALUE
------------------------------------ ----------- ------------------------------

buffer_pool_recycle                           string
control_file_record_keep_time                     integer            7
db_recovery_file_dest                      string
db_recovery_file_dest_size            big integer 0
db_recycle_cache_size                      big integer 0
db_unrecoverable_scn_tracking                   boolean          TRUE
ldap_directory_access                        string           NONE
ldap_directory_sysauth                              string            no
recovery_parallelism                           integer            0
recyclebin                                          string            on
remote_recovery_file_dest            string

In the Above snippet db_recovery_file_dest,db_recovery_file_dest_size is not set, here I am setting the db_recovery_file_dest_size to 10g and db_recovery_file_dest=’/u01/app/oracle/fra’

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;

System altered.
SQL> show parameter rec

NAME                                                 TYPE VALUE
------------------------------------ ----------- ------------------------------

buffer_pool_recycle                           string
control_file_record_keep_time                     integer            7
db_recovery_file_dest                      string
db_recovery_file_dest_size            big integer 10
db_recycle_cache_size                      big integer 0
db_unrecoverable_scn_tracking                   boolean          TRUE
ldap_directory_access                        string           NONE
ldap_directory_sysauth                              string            no
recovery_parallelism                           integer            0
recyclebin                                          string            on
remote_recovery_file_dest            string

Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
SQL>  select name,open_mode,log_mode from v$database;

NAME     OPEN_MODE          LOG_MODE
--------- -------------------- ------------
AUSTRALIA READ WRITE           ARCHIVELOG
SQL> show parameter db_recovery_file_dest_size

NAME                                     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size            big integer 10G
SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

Step:8 Copy init and password files from 12c home  to 19c  home

[oracle@orcldbs ~]$ cd $ORACLE_HOME/dbs
[oracle@orcldbs dbs]$ cp spfileaustralia.ora orapwaustralia
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
oracle@orcldbs dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@orcldbs dbs]$ ls
hc_oraprod.dat  lkORAPROD        orapworaprod      spfileaustralia.ora
init.ora        orapwaustralia  snapcf_oraprod.f  spfileoraprod.ora

Step:9 Startup DB in Upgrade mode from 19c home 

[oracle@orcldbs ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 01:24:40 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        536870912 bytes
Database Buffers             1879048192 bytes
Redo Buffers                         7876608 bytes
Database mounted.
Database opened.


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

NAME     OPEN_MODE    CDB VERSION         STATUS
--------- -------------------- --- ----------------- ------------
AUSTRALIA READ WRITE   NO  19.0.0.0.0       OPEN MIGRATE

Step:10 Run dbupgrade, using $ORACLE_HOME/bin/dbupgrade file (or) by catupgrade(catupgrd.sql). Here starts the manual upgrade process. 

Database upgrade completed. 

Step:11 Startup DB from 19c home 

[oracle@orcldbs whileupgrade]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 02:26:12 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        637534208 bytes
Database Buffers             1778384896 bytes
Redo Buffers                         7876608 bytes
Database mounted
Database opened
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME     OPEN_MODE       CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
AUSTRALIA READ WRITE        NO  19.0.0.0.0           OPEN

Step:-12. Run catcon.pl(it is used used to execute the command in all PDBs and CDB database)  to start utlrp.sql to recompile invalid objects.

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

COUNT(*)
----------
2552
SQL>  @?/rdbms/admin/utlrp.sql

Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN                   2021-01-31 02:30:35

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                    2021-01-31 02:34:29

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

Step:13 Upgrade the time zone by running the following scripts,

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

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.

SQL> @?/rdbms/admin/utltz_upg_apply.sql
Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        637534208 bytes
Database Buffers             1778384896 bytes
Redo Buffers                         7876608 bytes
Database mounted.
Database opened.

INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...

An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        637534208 bytes
Database Buffers             1778384896 bytes
Redo Buffers                         7876608 bytes
Database mounted.
Database opened.

INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
    32

1 row selected.

Step:14 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.

SQL>  @?/rdbms/admin/utlusts.sql text
Oracle Database Release 19 Post-Upgrade Status Tool    01-31-2021 02:43:1
Database Name: AUSTRALIA
Component                                                        Current           Full       Elapsed Time
Name                                                          Status         Version  HH:MM:SS
Oracle Server                                                      VALID      19.3.0.0.0  00:18:34
JServer JAVA Virtual Machine                      VALID      19.3.0.0.0  00:01:34
Oracle XDK                                                         VALID      19.3.0.0.0  00:00:47
Oracle Database Java Packages                    VALID      19.3.0.0.0  00:00:13
OLAP Analytic Workspace                               VALID      19.3.0.0.0  00:00:12
Oracle Label Security                                        VALID      19.3.0.0.0  00:00:06
Oracle Database Vault                                     VALID      19.3.0.0.0  00:00:20
Oracle Text                                                        VALID      19.3.0.0.0  00:00:31
Oracle Workspace Manager                          VALID      19.3.0.0.0  00:00:27
Oracle Real Application Clusters     OPTION OFF      19.3.0.0.0  00:00:00
Oracle XML Database                                       VALID      19.3.0.0.0  00:01:31
Oracle Multimedia                                             VALID      19.3.0.0.0  00:00:47
Spatial                                                      VALID      19.3.0.0.0  00:05:33
Oracle OLAP API                                                  VALID      19.3.0.0.0  00:00:10
Datapatch                                                                                       00:03:27

Final Actions                                                                                   00:03:35
Post Upgrade                                                                                    00:00:17
Post Compile                                                                                    00:03:53

Total Upgrade Time: 00:39:14

Database time zone version is 32. It meets current release needs.

Step:15 Drop Restore point as we have successfully completed the manual upgradation.

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

Step:16 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0

SQL> show parameter COMPATIBLE

NAME                                     TYPE VALUE
------------------------------------ ----------- ------------------------------

compatible                              string                12.2.0
noncdb_compatible                       boolean          FALSE


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

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        637534208 bytes
Database Buffers             1778384896 bytes
Redo Buffers                         7876608 bytes
Database mounted.
Database opened.

SQL> show parameter COMPATIBLE

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

Step:17 Verify DBA_REGISTRY 

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
SQL>

COMP_ID                                    COMP_NAME               VERSION              STATUS

------------------------------ ---------------------------------------- --------------- --------------------------------------------
CATALOG                                   Oracle Database Catalog Views     19.0.0.0.0             VALID
CATPROC                                    Oracle Database Packages and Types  19.0.0.0.0             VALID
JAVAVM                                      JServer JAVA Virtual Machine       19.0.0.0.0             VALID
XML                                         Oracle XDK                         19.0.0.0.0             VALID
CATJAVA                                     Oracle Database Java Packages      19.0.0.0.0             VALID
APS                                        OLAP Analytic Workspace            19.0.0.0.0             VALID
RAC                                        Oracle Real Application Clusters   19.0.0.0.0        OPTION OFF
XDB                                       Oracle XML Database         19.0.0.0.0             VALID
OWM                                        Oracle Workspace Manager        19.0.0.0.0             VALID
CONTEXT                                    Oracle Text                         19.0.0.0.0             VALID
ORDIM                                      Oracle Multimedia                  19.0.0.0.0             VALID
SDO                                        Spatial                             19.0.0.0.0             VALID                                
XOQ                                        Oracle OLAP API                19.0.0.0.0             VALID
OLS                                        Oracle Label Security          19.0.0.0.0             VALID
DV                                         Oracle Database Vault        19.0.0.0.0             VALID



15 rows selected.

Step:18 Add TNS Entries in 19c TNS home 

[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:33
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been starte
[oracle@orcldbs admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:38
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:41
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JAN-2021 02:56:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...

Service "australia" has 1 instance(s).
Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:50
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JAN-2021 02:56:41
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "australia" has 1 instance(s).
Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Step:19 Edit oratab,Put an entry of current database in the vi /etc/oratab file.

[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/12.2.0/db_1:N
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/19.0.0/dbhome_1:Y
australia:/u01/app/oracle/product/12.2.0/db_1:N

Database Upgrade from 12c to 19c using Datapump

Database Upgrade from 12c to 19c using Datapump

Overview steps:

Step:1 Check the database size in source.
Step:2 check which tablespace holds the schema objects.
Step:3 compile invalid objects in source.
step:4 check the  count of invalid dba_objects in the source.
Step:5 create a directory for export purpose both in OS level and database level.
Step:6 Estimate the size of dumpfile, so that we can know when will the export get completed.
Step:7 export the database(TANSTAL).
Step:8 create a fresh database for import.
Step:9 Now create a directory in both OS level and database level for import purpose in the newly created database.
Step:10 import the database.(ZHIGOMA)
Step:11 Post upgrade steps in target database.
               11.1 compile invalid objects in target database.
               11.2 check whether any invalid objects present.
               11.3 Run the query to check for currently installed database components.

Step:12  Verify the timezone of the upgraded database.

Step:13  Check the CONSTRAINTS count in both source & target.it is used to display the constraints that are defined in the database.

Step:1 Check the database size in source .

SQL> col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Database Size             Used space         Free space
-------------------- -------------------- --------------------
    2 GB                    2 GB               0 GB

SQL>

Step:2 execute the following query to check which tablespace holds the schema objects.

set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;

Step:3 compile invalid objects in source to reduce dependencies, so that we can find the best way to recompile it.

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

COMP_TIMESTAMP UTLRP_BGN 2021-01-31 20:19:36

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.

COMP_TIMESTAMP UTLRP_END 2021-01-31 20:19:39

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

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

0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

step:4 check the  count of invalid dba_objects in the source.

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

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

Step:5 create a directory for export purpose both in OS level and database level.

mkdir /u01/export

create directory export as '/u01/export';

Directory created

[oracle@orcldbs ~]$ mkdir export
[oracle@orcldbs ~]$ ls
12c.env Documents export oraprod.env Templates
19c.env Downloads Music Pictures utlrp.out
Desktop em13400_linux64-3.zip oradiag_oracle Public Videos

Create  a table employee in the user : rahul.

Step:6 Estimate the size of dumpfile by this we are able to know the completion time of export activity.

[oracle@orcldbs export]$ expdp directory=export full=Y nologfile=Y estimate_only=Y

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:09:14 2021

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

Username: system
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=export full=Y nologfile=Y estimate_only=Y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "SYS"."KU$_USER_MAPPING_VIEW" 64 KB
. estimated "ORDDATA"."ORDDCM_DOCS" 1.25 MB
. estimated "WMSYS"."WM$CONSTRAINTS_TABLE$" 320 KB
. estimated "SYS"."AUD$" 256 KB
. estimated "WMSYS"."WM$LOCKROWS_INFO$" 192 KB
. estimated "WMSYS"."WM$UDTRIG_INFO$" 192 KB
. estimated "LBACSYS"."OLS$AUDIT_ACTIONS" 64 KB
. estimated "LBACSYS"."OLS$DIP_EVENTS" 64 KB
. estimated "LBACSYS"."OLS$INSTALLATIONS" 64 KB
. estimated "LBACSYS"."OLS$PROPS" 64 KB
. estimated "SYS"."DAM_CLEANUP_EVENTS$" 64 KB
. estimated "SYS"."DAM_CLEANUP_JOBS$" 64 KB
. estimated "SYS"."DAM_CONFIG_PARAM$" 64 KB
. estimated "SYS"."TSDP_ASSOCIATION$" 64 KB
. estimated "SYS"."TSDP_CONDITION$" 64 KB
. estimated "SYS"."TSDP_FEATURE_POLICY$" 64 KB
. estimated "SYS"."TSDP_PARAMETER$" 64 KB
. estimated "SYS"."TSDP_POLICY$" 64 KB
. estimated "SYS"."TSDP_PROTECTION$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_DATA$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_TYPE$" 64 KB
. estimated "SYS"."TSDP_SOURCE$" 64 KB
. estimated "SYS"."TSDP_SUBPOL$" 64 KB
. estimated "SYSTEM"."REDO_DB" 64 KB
. estimated "SYSTEM"."REDO_LOG" 64 KB
. estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 64 KB
. estimated "WMSYS"."WM$CONS_COLUMNS$" 64 KB
. estimated "WMSYS"."WM$ENV_VARS$" 64 KB
. estimated "WMSYS"."WM$EVENTS_INFO$" 64 KB
. estimated "WMSYS"."WM$HINT_TABLE$" 64 KB
. estimated "WMSYS"."WM$MODIFIED_TABLES$" 64 KB
. estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 64 KB
. estimated "WMSYS"."WM$NEXTVER_TABLE$" 64 KB
. estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_LOCKING_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 64 KB
. estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 64 KB
. estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 64 KB
. estimated "WMSYS"."WM$VERSION_TABLE$" 64 KB
. estimated "WMSYS"."WM$VT_ERRORS_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 64 KB
. estimated "LBACSYS"."OLS$AUDIT" 0 KB
. estimated "LBACSYS"."OLS$COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$DIP_DEBUG" 0 KB
. estimated "LBACSYS"."OLS$GROUPS" 0 KB
. estimated "LBACSYS"."OLS$LAB" 0 KB
. estimated "LBACSYS"."OLS$LEVELS" 0 KB
. estimated "LBACSYS"."OLS$POL" 0 KB
. estimated "LBACSYS"."OLS$POLICY_ADMIN" 0 KB
. estimated "LBACSYS"."OLS$POLS" 0 KB
. estimated "LBACSYS"."OLS$POLT" 0 KB
. estimated "LBACSYS"."OLS$PROFILE" 0 KB
. estimated "LBACSYS"."OLS$PROFILES" 0 KB
. estimated "LBACSYS"."OLS$PROG" 0 KB
. estimated "LBACSYS"."OLS$SESSINFO" 0 KB
. estimated "LBACSYS"."OLS$USER" 0 KB
. estimated "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$USER_GROUPS" 0 KB
. estimated "LBACSYS"."OLS$USER_LEVELS" 0 KB
. estimated "SYS"."FGA_LOG$FOR_EXPORT" 256 KB
. estimated "SYS"."SQL$TEXT_DATAPUMP" 192 KB
. estimated "SYS"."SQLOBJ$DATA_DATAPUMP" 192 KB
. estimated "SYS"."SQL$_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$PLAN_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$_DATAPUMP" 128 KB
. estimated "SYSTEM"."SCHEDULER_JOB_ARGS" 128 KB
. estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 128 KB
. estimated "SYS"."AUDTAB$TBS$FOR_EXPORT" 64 KB
. estimated "SYS"."DBA_SENSITIVE_DATA" 64 KB
. estimated "SYS"."DBA_TSDP_POLICY_PROTECTION" 64 KB
. estimated "SYS"."NACL$_ACE_EXP" 64 KB
. estimated "SYS"."NACL$_HOST_EXP" 64 KB
. estimated "SYS"."NACL$_WALLET_EXP" 64 KB
. estimated "MDSYS"."RDF_PARAM$" 16 KB
. estimated "WMSYS"."WM$EXP_MAP" 16 KB
. estimated "WMSYS"."WM$METADATA_MAP" 16 KB
Total estimation using BLOCKS method: 6.546 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:09:32 2021 elapsed 0 00:00:11

Step:7 export the roles and privileges.

[oracle@orcldbs ~]$ expdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:8 Now it’s time to export our database(TANSTAL) 

[oracle@orcldbs ~]$ expdp system/oracle@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES;

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:16:25 2021
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
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES 
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 37 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.976 KB 2 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/orclfull.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:19:02 2021 elapsed 0 00:02:03

Step:9 create a fresh database for import.(the dumpfile that is being exported in tanstal db going to be imported in zhigoma) 

Step:10 Now create a directory in both OS level and database level for import purpose in the newly created database.

mkdir /u01/export 

create directory export as '/u01/export';

Step:11 import the roles and privileges to zhigoma db.

[oracle@orcldbs ~]$ impdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:12 Now it’s time to import our database.(ZHIGOMA)

[oracle@orcldbs ~]$ impdp system/oracle@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes;

Import: Release 19.0.0.0.0 - Production on Mon Feb 1 01:21:26 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER


Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . imported "WMSYS"."E$HINT_TABLE$" 9.984 KB 97 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$" 6.375 KB 1 rows
. . imported "WMSYS"."E$ENV_VARS$" 6.015 KB 3 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . imported "WMSYS"."E$EVENTS_INFO$" 5.812 KB 12 rows
. . imported "LBACSYS"."OLS_DP$OLS$AUDIT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$GROUPS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LAB" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LEVELS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POL" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROFILE" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROG" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$USER" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$" 0 KB 0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$VERSION_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MDSYS"."RDF_PARAM$TBL" 6.515 KB 3 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.976 KB 2 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 9.515 KB 12 rows
. . imported "WMSYS"."E$EXP_MAP" 7.718 KB 3 rows
. . imported "WMSYS"."E$METADATA_MAP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed at Mon Feb 1 01:23:21 2021 elapsed 0 00:01:54

Once the import is over, you can also review the log file.

13.1 compile invalid objects in target database.

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

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

COUNT(*)
-------- 0
SQL>  SELECT version FROM v$timezone_file;

  VERSION
-----------------
    32

13.3 Run the query to check for currently installed database components.

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

13.4 Check the CONSTRAINTS count,it is used to display the constraints that are defined in the database.

SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type; 

C NUM_CONSTRAINTS

C 5784
F 12
O 181
R 327
P 864
V 11
U 250

7 rows selected.
SQL>

yes now we can login to the zhigoma database and we can verify our user(rahul) and the table(employee) in that user is  imported into this database or not.

We have successfully upgraded our database from 12 c to 19c using datapump !!!!