Archives December 2020

no supported authentication methods available (server sent: publickey, gssapi-with-mic)

Cause:

You try to ssh to EC2 compute instance using a public key authentication with doesnot match the key value pair which you have generated

Solution:

Use puttygen to generate a .ppk file from the .pem file to generate ssh key value pair

Import the private key which you have generated into putty configuration

Works like charm!

Hope this post might save someones time !

ORA-14120: incompletely specified partition bound for a DATE column

Cause:

When you specify to_date() function,nls_date_format parameter should support the format which you give

Most probably ‘MM-DD-YYYY‘ format works well

SQL> !oerr ORA 14120
14120, 00000, "incompletely specified partition bound for a DATE column"
// *Cause:  An attempt was made to use a date expression whose format
//          does not fully (i.e. day, month, and year (including century))
//          specify a date as a partition bound for a DATE column.
//          The format may have been specified explicitly (using
//          TO_DATE() function) or implicitly (NLS_DATE_FORMAT).

Solution:

// *Action: Ensure that date format used in a partition bound for a
//          DATE column supports complete specification of a date
//          (i.e. day, month, and year (including century)).
//          If NLS_DATE_FORMAT does not support complete
//          (i.e. including the century) specification of the year,
//          use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-YYYY')
//          to fully express the desired date.

Check nls format for date

SQL> alter session set nls_date_format='DD-MM-YYYY';

Session altered.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MM-YYYY

Specify the below format when using to_date()

    PARTITION BY RANGE (ORDER_DATE)
    (PARTITION P_S1 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')),
     PARTITION P_S2 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY')),
     PARTITION P_S3 VALUES LESS THAN (TO_DATE('01-01-2018','DD-MM-YYYY')),
     PARTITION P_S4 VALUES LESS THAN (TO_DATE('01-01-2021','DD-MM-YYYY')),
     PARTITION P_S5 VALUES LESS THAN (TO_DATE('01-01-2024','DD-MM-YYYY')),
    PARTITION P_S6 VALUES LESS THAN (TO_DATE('01-01-2027','DD-MM-YYYY')),
    PARTITION P_S7 VALUES LESS THAN (TO_DATE('01-01-2030','DD-MM-YYYY')),
    PARTITION P_S8 VALUES LESS THAN (TO_DATE('01-01-2033','DD-MM-YYYY')),
    PARTITION P_S9 VALUES LESS THAN (maxvalue));

ORA-14020: this physical attribute may not be specified for a table partition

Cause:

Silly ! Syntax issue like Commas or braces missing ~Dont scratch your head and waste your time figuring out

    PARTITION BY RANGE (ORDER_DATE)
    (PARTITION P_S1 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY'))
    (PARTITION P_S2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')) <----------
    (PARTITION P_S3 VALUES LESS THAN (TO_DATE(MAXVALUE));  5    6    7    8    9                            10   11   12   13   14   15   16   17   18
    (PARTITION P_S2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')) <----------
    *
ERROR at line 17:
ORA-14020: this physical attribute may not be specified for a table partition

Solution:

Give the correct syntax with correct quotations and braces on the query

PARTITION BY RANGE (ORDER_DATE)   
(PARTITION P_S1 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')),
     PARTITION P_S2 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY')),
     PARTITION P_S3 VALUES LESS THAN (TO_DATE('01-01-2018','DD-MM-YYYY')),
     PARTITION P_S4 VALUES LESS THAN (TO_DATE('01-01-2021','DD-MM-YYYY')),
     PARTITION P_S5 VALUES LESS THAN (TO_DATE('01-01-2024','DD-MM-YYYY')),
    PARTITION P_S6 VALUES LESS THAN (TO_DATE('01-01-2027','DD-MM-YYYY')),
    PARTITION P_S7 VALUES LESS THAN (TO_DATE('01-01-2030','DD-MM-YYYY')),
    PARTITION P_S8 VALUES LESS THAN (TO_DATE('01-01-2033','DD-MM-YYYY')),
    PARTITION P_S9 VALUES LESS THAN (maxvalue));
ORA-20000: Unable to analyze INDEX “TEST1″.”FN_ORDER_DATE”, insufficient privileges or does not exist

Error:

User from which you try to gather stats doesnot have the necessary privilege!

SQL> !oerr ora 20000
20000, 00000, "%s"
// *Cause:  The stored procedure 'raise_application_error'
//          was called which causes this error to be generated.
// *Action: Correct the problem as described in the error message or contact
//          the application administrator or DBA for more information.

SQL> exec dbms_stats.gather_index_stats('TEST1','fn_order_date');
BEGIN dbms_stats.gather_index_stats('TEST1','fn_order_date'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "TEST1"."FN_ORDER_DATE", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 30110
ORA-06512: at "SYS.DBMS_STATS", line 30024
ORA-06512: at "SYS.DBMS_STATS", line 29963
ORA-06512: at "SYS.DBMS_STATS", line 30097
ORA-06512: at line 1

Solution:

Grant execute privilege on dbms stats !

SQL> grant execute on dbms_stats to test1;

Grant succeeded.

SQL> exec dbms_stats.gather_index_stats('TEST1','fn_order_date');

PL/SQL procedure successfully completed.

RMAN-06429: TARGET database is not compatible with this version of RMAN RMAN-06618: RMAN client and database version mismatch; indicated database version is 12.1.0.2

Cause:

When duplicate a database from target to auxiliary,there is a version mismatch in rman client version of auxiliary database

Solution:

Upgrade your catalog on your target database

RMAN> upgrade catalog;
Are invisible indexes special? (oracle)

Indexes in a database are not visible to our bare eyes but we have a logical idea that index is a brief record that contain the copy of the specific rowid of tables.

If god come in front of you and offer a wish to make you invisible ! what would it be like ?

will you use it for positive or negative purpose ? 😉

So lets get into invisible index

Invisible index is very useful in case of testing the efficiency of an index which you are not sure if that index performs well in a production or screw up the performance

  • When a normal index is created ,the optimizer starts its cost estimates and analyse the index if it can be used efficiently
  • Occasionally there are situations where we need to influence the optimizer to use index only during specific time
  • The invisible index can be switched visible or invisible like a button whenever we require
  • It is warned that we should be aware of the invisible indexes in our database while testing the applciation performance because oracle has to handle the index when DML operations occur
  • There is no major difference between a normal visible index and invisible index as both has to be maintained in the same way but only difference is their visibility to optimizer

Take a scenario,where we have to run a select query with join on another table

In this case,my select query takes 5.25 seconds complete the query without any index

SQL> set timing on
set autot traceonly
select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:05.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   615K|    38M|       | 34681   (1)| 00:00:02 |
|   1 |  SORT ORDER BY      |        |   615K|    38M|    42M| 34681   (1)| 00:00:02 |
|*  2 |   HASH JOIN         |        |   615K|    38M|  7640K| 25133   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| SALES  |   111K|  6328K|       |  3393   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES1 |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID")
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

I create a functional index on column trunc(order_date) on sales table to check if performance is good but im not sure if functional index improves the performance

SQL> create index ind_func_date on sales(trunc(order_id));

Index created.

Lets make the index visible first to test query ,already visible here

SQL> select index_name,visibility from dba_indexes where index_name='IND_FUNC_DATE';

INDEX_NAME                               VISIBILITY
---------------------------------------- --------------------
IDX_FUNC_DATE                            VISIBLE

Note: we can create an index as invisible 

SQL> create index ind_func_date on sales(trunc(order_id)) invisible;

Gather stats on table

SQL> exec dbms_stats.gather_table_stats(ownname=>'test1',tabname=>'sales',cascade=>true,degree=>4,method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

Hint is used to force the optimizer to use the index

The query perform worse than full table scan after adding an index

SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:08.53

Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   615K|    42M|       |  1083K  (1)| 00:00:43 |
|   1 |  SORT ORDER BY                        |                    |   615K|    42M|    47M|  1083K  (1)| 00:00:43 |
|*  2 |   HASH JOIN                           |                    |   615K|    42M|  8408K|  1073K  (1)| 00:00:42 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |   111K|  7092K|       |  1051K  (1)| 00:00:42 |
|   4 |     INDEX FULL SCAN                   | IND_SALES_ORDER_ID |  1048K|       |       |  2637   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                  | SALES1             |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

Lets add another index on order id column on sales table

SQL> create index ind_sales_order_id on sales(order_id);

Index created.

Gather stats for table for our satisfaction

SQL> exec dbms_stats.gather_table_stats(tabname=>'sales',ownname=>'test1',cascade=>true);

PL/SQL procedure successfully completed.

Even after forcing the second index,still full table scan is better than using index here

We know that full table scans are not always evil !

SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:08.53

Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   615K|    42M|       |  1083K  (1)| 00:00:43 |
|   1 |  SORT ORDER BY                        |                    |   615K|    42M|    47M|  1083K  (1)| 00:00:43 |
|*  2 |   HASH JOIN                           |                    |   615K|    42M|  8408K|  1073K  (1)| 00:00:42 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |   111K|  7092K|       |  1051K  (1)| 00:00:42 |
|   4 |     INDEX FULL SCAN                   | IND_SALES_ORDER_ID |  1048K|       |       |  2637   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                  | SALES1             |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

So here our testing says that both indexes are useless for this particular query

Better to make indexes invisible to optimizer,but not us as we already know there are indexes hiding behind the wall

SQL> alter index IND_SALES_ORDER_ID invisible;

Index altered.

SQL> alter index IND_FUNC_DATE invisible;

Index altered.

After forcing the optimizer to use index,still optimizer would not use index as they are unusable

In the below execution plan,the outline information shows that the index is not used eventhough we forced the index with hints

SQL> set timing on
set autot traceonly
select /*+ index(a ind_func_date) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
ordSQL> er by a.total_profit desc;SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:05.79

Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   615K|    38M|       | 34681   (1)| 00:00:02 |
|   1 |  SORT ORDER BY      |        |   615K|    38M|    42M| 34681   (1)| 00:00:02 |
|*  2 |   HASH JOIN         |        |   615K|    38M|  7640K| 25133   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| SALES  |   111K|  6328K|       |  3393   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES1 |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID")
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 -  SEL$58A6D7F6 / A@SEL$1
         U -  index(a ind_func_date)

Again i want to test the index for some other query

SQL> alter index ind_sales_order_id visible;

Index altered.

In this case,i need only single row to be retrieved from million rows

Hence,full table scan is not the correct access path here

The better way here is to use the index which we had created earlier for order_id column

SQL> set autot traceonly
SQL> select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a where a.order_id =1120;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 562010242

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |    50 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |     1 |    50 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_SALES_ORDER_ID |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

This is one of the smart way to reduce the application downtime

Monitoring the index usage

This is one way to monitor the index usage efficiently rather than investigating the execution plans

SQL> alter index IND_SALES_ORDER_ID monitoring usage;

Index altered.

SQL> alter index IND_FUNC_DATE monitoring usage;

Index altered.

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                               MONITORING           USED
---------------------------------------- -------------------- ----------------------------------------
IND_SALES_ORDER_ID                       YES                  NO
IND_FUNC_DATE                            YES                  NO

Once the index is tested,we can turn off the auto monitor

SQL> alter index IND_FUNC_DATE nomonitoring usage;

Index altered.

SQL> alter index IND_SALES_ORDER_ID nomonitoring usage;

Index altered.

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                               MONITORING           USED
---------------------------------------- -------------------- ----------------------------------------
IND_SALES_ORDER_ID                       NO                   NO
IND_FUNC_DATE                            NO                   NO

we can enable this parameter to make index visible to optimizer at session or system level using “OPTIMIZER_USE_INVISIBLE_INDEXES” 

Now we can force the invisible index to optimizer using hints

SQL> alter system set OPTIMIZER_USE_INVISIBLE_INDEXES=true;

System altered.

SQL> show parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
  • To conclude this article,obviously invisible indexes are special for adhoc query and to solve resource intensive operations like dropping of an index or make index unusable like in old times.
  • Before 11G ,index had to be tested by making them unusable.If the index performs well then index rebuild is required else drop index
  • we can keep application performance under observation for couple of days to determine if the application needs the index or not.
  • Multiple invisible indexes can be created to choose the index which is best.
  • For adhoc queries,an index might be useful suddenly but that index might not be useful in other application module

Sometimes rebuilding an index on a mammoth table takes huge time and are overhead to a production database.There are always better ways to deal with these kind of hurdles where one of them is here in front of you

RMAN-00554: initialization of internal recovery manager package failed|RMAN-03000: recovery manager compiler component initialization failed|RMAN-06001: error parsing job step library|RMAN-01009: syntax error: found “identifier”: expecting one of: “”|RMAN-01008: the bad identifier was: raschemaversion|RMAN-01007: at line 17 column 1 file: ?/rdbms/admin/recover.bsq

Cause:

There are multiple oracle home location A,B, and C running different oracle database version in host and you actually need rman to be launched for A but you launched rman for B from A

[oracle@orcl19x admin]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Dec 19 21:07:39 2020

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-03000: recovery manager compiler component initialization failed
RMAN-06001: error parsing job step library
RMAN-01009: syntax error: found "identifier": expecting one of: ""
RMAN-01008: the bad identifier was: raschemaversion
RMAN-01007: at line 17 column 1 file: ?/rdbms/admin/recover.bsq
[oracle@orcl19x apex]$ cat /etc/oratab |grep orcl19x
orcl19x:/apps01/product/12.1.0/dbhome_1:Y
orcl19x1:/data01/product/19.0.0:Y

Path or oracle home variable is not properly set for the database from which rman try to execute

Solution:

Set the correct variables

[oracle@orcl19x apex]$ export PATH=/data01/product/19.0.0/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

export ORACLE_HOME=/data01/product/19.0.0

Relink binaries from the correct oracle home

[oracle@orcl19x apex]$ /data01/product/19.0.0/bin/relink all
writing relink log to: /data01/product/19.0.0/install/relinkActions2020-12-20_07-21-44PM.log

[oracle@orcl19x apex]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 5 01:27:23 2021
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN>

UPGRADE ORACLE DATABASE MANUALLY FROM 12.1.0.2 to 19.0.0.0 (12c to 19C)

A system perform well, but we have to update it then and now due to this evolving era

Why upgrade to 19c

Long term release <-> Premier support <-> end of support coming soon for old versions

current DB version : 12.1.0.2.0 (single instance non PDB)

upgraded DB version : 19.0.0.0.0

12C oracle home : /apps01/product/12.1.0/dbhome

19C oracle home : /data01/product/19.0.0

Database name : orcl19x1

Direct upgrade paths recommendation :

Other database versions apart from below doesnot support direct upgrade

for example (12.1.0.1) has to be upgraded to 19c ,once it is upgraded to the below intermediate versions

From the above paths,we have 12.1.0.2 version which can be directly upgraded to 19c

Download the 19c software from the link and stage the file in oracle home directory.Please verify the link in future due to updation.

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

PREREQUISITES:

Unzip the 19c file:

[root@orcl19x 19.0.0]# unzip LINUX.X64_193000_db_home

Install the 19C software on the host

Take a tar ball of 12c oracle home

[root@orcl19x 12.1.0]# tar -cvf dbhome12c.tar /apps01/ >/dev/null 2>&1
[root@orcl19x 12.1.0]#
[root@orcl19x 12.1.0]# ls
dbhome_1  dbhome12c.tar

Notify application team to refrain access the database

Start the database in restricted mode to stop incoming connections to the database

Take consistent cold backup if downtime can be afford or take hot incremental backup of database (the database should be in archivelog mode for hot backup)

[oracle@orcl19x ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 23 18:47:45 2020

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

connected to target database: ORCL19X (DBID=4045524769)

RMAN> run
 {
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
 backup as backupset database plus archivelog delete input;
 backup current controlfile;
 release channel c1;
 release channel c2;
 }

Starting backup at 23-NOV-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
RECID=6 STAMP=1057258215
Finished backup at 23-NOV-20
piece handle=/data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_ncnnf_TAG20201123T185354_hvqfzf5d_.bkp tag=TAG20201123T185354 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-NOV-20

List the backups:

RMAN> list backup of database;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.27G      DISK        00:01:34     23-NOV-20
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20201123T184835
        Piece Name: /data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_nnndf_TAG20201123T184835_hvqfod5s_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1924263    23-NOV-20 /apps01/oradata/orcl19x/system01.dbf
  3       Full 1924263    23-NOV-20 /apps01/oradata/orcl19x/sysaux01.dbf
  4       Full 1924263    23-NOV-20 /apps01/oradata/orcl19x/undotbs01.dbf
  5       Full 1924263    23-NOV-20 /apps01/oradata/orcl19x/example01.dbf
  6       Full 1924263    23-NOV-20 /apps01/oradata/orcl19x/users01.dbf


Preview the restore of controlfile and database from rman

RMAN> restore controlfile from '/data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_ncnnf_TAG20201123T185354_hvqfzf5d_.bkp' preview summary;

Starting restore at 23-NOV-20
using channel ORA_DISK_1

Finished restore at 23-NOV-20
=======================================================================
RMAN> restore database preview summary;

Starting restore at 23-NOV-20
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2       B  F  A DISK        23-NOV-20       1       1       NO         TAG20201123T184835


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
4       B  A  A DISK        23-NOV-20       1       1       NO         TAG20201123T185015
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 1924263
Recovery must be done beyond SCN 1924263 to clear datafile fuzziness
Finished restore at 23-NOV-20

Create a new directory for 19c and give permissions to 19c oracle home

[root@orcl19x product]# mkdir 19.0.0
[root@orcl19x product]# pwd
/apps01/product
[root@orcl19x product]# chmod -R 777 19.0.0/
[root@orcl19x product]# chown -R oracle:oinstall 19.0.0/

Check the space on disk and clear space if the new oracle home location has no sufficient space

[root@orcl19x product]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        20G   15G  3.2G  83% /
tmpfs           1.9G   72K  1.9G   1% /dev/shm
/dev/sda1        29G   15G   13G  55% /apps01
/dev/sda5        29G  1.5G   26G   6% /data01

Copy the pfile,spfile,passwordfile,listener file,tnsnames.ora and sqlnet.ora files to 19c oracle directory from 12c

[oracle@orcl19x dbs]$ cp * /apps01/product/19.0.0/dbhome/dbs/
[oracle@orcl19x dbs]$ cd ../network/admin/
[oracle@orcl19x admin]$ cp * /apps01/product/19.0.0/dbhome/network/admin/
cp: omitting directory `samples'
[oracle@orcl19x admin]$

Check for invalid objects on database.If there are invalid objects then recompile objects with UTLRP or UTL_RECOMP.recomp_parallel(4)

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

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

If you find invalid objects,then recompile the objects

Create a temporary directory to store the upgrade scripts

[oracle@orcl19x admin]$ mkdir /tmp/preupgrade
[oracle@orcl19x admin]$ echo $ORACLE_SID
orcl19x
[oracle@orcl19x admin]$ echo $ORACLE_HOME
/apps01/product/12.1.0/dbhome_1

Invoke the preupgrade.jar script in 19c OH from 12c java package to generate the pre and post upgrade scripts

[oracle@orcl19x admin]$ /apps01/product/12.1.0/dbhome_1/jdk/bin/java -jar /apps0                                                                                                             1/product/19.0.0/dbhome/rdbms/admin/preupgrade.jar FILE DIR /tmp/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /tmp/preupgrade/preupgrade.log
  /tmp/preupgrade/preupgrade_fixups.sql
  /tmp/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/tmp/preupgrade/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2020-11-24T18:39:58

Check the preupgrade.log file and follow the steps

Note: If there are errors in any step,dont skip the errors until you resolve them.Otherwise you might get into trouble in the middle of the upgrade process and waste time

[oracle@orcl19x admin]$ more /tmp/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-24T18:39:57

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  ORCL19X1
     Container Name:  orcl19x1
       Container ID:  0
            Version:  12.1.0.2.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

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

Keep track of the tablespace size because after upgrade they increase in size

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             700 MB       808 MB
      SYSTEM                             790 MB      1136 MB
      UNDOTBS1                           155 MB       449 MB

It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using.After upgrade ,catalog also should be upgraded

SQL> conn rcat/password
Connected.

SQL> select * from rcver;

VERSION
------------
12.01.00.02

Run the preupgrade fixups script

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

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-11-24 18:39:52

For Source Database:     ORCL19X1
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  apex_manual_upgrade       NO          Manual fixup recommended.
    2.  mv_refresh                NO          Informational only.
                                              Further action is optional.
    3.  dictionary_stats          YES         None.
    4.  mv_refresh                NO          Informational only.
                                              Further action is optional.
    5.  pre_fixed_objects         YES         None.
    6.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    7.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

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

PL/SQL procedure successfully completed.

UPGRADE APEX:

Check Your Version of APEX

SQL> Col Comp_name Format a22
Col Status Format a12
Select Comp_name, status, Version
From Dba_Registry
Order by Comp_name;SQL> SQL>   2    3

COMP_NAME              STATUS       VERSION
---------------------- ------------ ------------------------------
JServer JAVA Virtual M VALID        12.1.0.2.0
achine

OLAP Analytic Workspac VALID        12.1.0.2.0
e

Oracle Application Exp VALID        4.2.5.00.08 < ---------
ress

Oracle Database Catalo VALID        12.1.0.2.0
g Views

COMP_NAME              STATUS       VERSION
---------------------- ------------ ------------------------------

Oracle Database Java P VALID        12.1.0.2.0
ackages

Oracle Database Packag VALID        12.1.0.2.0
es and Types

Oracle Database Vault  VALID        12.1.0.2.0
Oracle Label Security  VALID        12.1.0.2.0
Oracle Multimedia      VALID        12.1.0.2.0
Oracle OLAP API        VALID        12.1.0.2.0

COMP_NAME              STATUS       VERSION
---------------------- ------------ ------------------------------
Oracle Real Applicatio OPTION OFF   12.1.0.2.0
n Clusters

Oracle Text            VALID        12.1.0.2.0
Oracle Workspace Manag VALID        12.1.0.2.0
er

Oracle XDK             VALID        12.1.0.2.0
Oracle XML Database    VALID        12.1.0.2.0
Spatial                VALID        12.1.0.2.0

16 rows selected.

Determine the Installation Type

Check the count for schema APEX with the version on wwv_flows table

If the query returns 0, it is a runtime only installation, and apxrtins.sql should be used for the upgrade.

If the query returns 1, this is a development install and apexins.sql should be used.

APEX component should be manually upgraded,so it can be done before database upgrade to save downtime

Download the latest version from https://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

[oracle@orcl19x apex]$ cd /tmp/apex/apex/
[oracle@orcl19x apex]$ ls
apex_epg_config_cdb.sql     apxappcon.sql       apxremov1.sql         catapx.sql
apex_epg_config_core.sql    apxchpwd.sql        apxremov2.sql         core
apex_epg_config_nocdb.sql   apxconf.sql         apxremov_cdb.sql      coreins2.sql
apex_epg_config.sql         apxdevrm_cdb.sql    apxremov_nocdb.sql    coreins3.sql
apexins1.sql                apxdevrm_nocdb.sql  apxremov.sql          coreins4.sql
apexins2.sql                apxdevrm.sql        apxrtins1.sql         coreins5.sql
apexins3.sql                apxdvins_cdb.sql    apxrtins2.sql         coreins.sql
apexins_cdb.sql             apxdvins_nocdb.sql  apxrtins3.sql         dbcsconf.sql
apexins_cdb_upg.sql         apxdvins.sql        apxrtins_cdb.sql      dbcsins.sql
apexins_nocdb.sql           apxdwngrd.sql       apxrtins_cdb_upg.sql  devins.sql
apexins.sql                 apxexit.sql         apxrtins_nocdb.sql    images
apex_rest_config_cdb.sql    apxldimg_core.sql   apxrtins.sql          install2020-12-19_22-37-28.log
apex_rest_config_core.sql   apxldimg.sql        apxsdoins.sql         load_trans.sql
apex_rest_config_nocdb.sql  apxpatch_cdb.sql    apxsilentins.sql      utilities
apex_rest_config.sql        apxpatch_nocdb.sql  apxupgrd.sql
appins.sql                  apxpatch.sql        builder

Run apexins script to perform development install

This image has an empty alt attribute; its file name is image-27.png
# Actions in Phase 1:
#
    ok 1 - BEGIN                                                        |   0.00
    ok 2 - Creating FLOWS_FILES                                         |   0.00
    ok 3 - Creating APEX_PUBLIC_USER                                    |   0.00
    ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE                      |   0.03
    ok 5 - Creating SYS Objects                                         |   0.00
    ok 6 - Creating APEX Tables                                         |   0.47
    ok 7 - Installing Package Specs (Runtime)                           |   1.03
    ok 8 - Installing Package Bodies (Runtime)                          |   0.62
    ok 9 - Dev Grants                                                   |   0.02
    ok 10 - Creating Dev-Only Tables and Triggers                       |   0.00
    ok 11 - Installing Package Specs (Dev)                              |   0.47
    ok 12 - Installing Views (Dev)                                      |   0.05
    ok 13 - Installing Package Bodies (Dev)                             |   0.18
    ok 14 - Recompiling APEX_190200                                     |   1.78
    ok 15 - Creating APEX$ objects in APEX_190200                       |   0.03
    ok 16 - Creating Spatial Support                                    |   0.00
    ok 17 - Creating Instance Parameter Defaults                        |   0.00
    ok 18 - Installing Page Designer Metadata                           |   0.08
    ok 19 - Inherit Privileges                                          |   0.00
    ok 20 - Creating APEX_INSTANCE_ADMIN_USER                           |   0.00
ok 1 - 20 actions passed, 0 actions failed                              |   4.77

Thank you for installing Oracle Application Express 19.2.0.00.18

Oracle Application Express is installed in the APEX_190200 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:14.65
timing for: Complete Installation
Elapsed: 00:13:58.73

PL/SQL procedure successfully completed.

load the images files into the apex home directory where the installation files were unzipped

SQL> @apex_epg_config.sql /tmp/apex/

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

. Loading images directory: /tmp/apex//apex/images

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Directory dropped.

timing for: Load Images
Elapsed: 00:03:30.85

PL/SQL procedure successfully completed.


Commit complete.

Check for the materialized views in the database currently

Refresh the materialized views which are there on the database

Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
      empty before doing upgrade, unless you have strong business reasons not
      to do so. You can use dbms_mview.refresh() to refresh the MVs except
      those stale ones  to be kept due to business need. If there are any stale
      MVs depending on changes in sys.sumdelta$, do not truncate it, because
      doing so will cause wrong results after refresh.

Before upgrade,the sumdelta sys view should be empty

Gather stale data dictionary statistics prior to database upgrade in off-peak time.This will boost SQL execution performance

Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade.

Check for triggers which doesnot have ‘ADMINISTER DATABASE PRIVILEGE‘.

If not grant the right privileges.In my case,all triggers has correct privileges in place

Find the tablespaces for which autoextend is enabled

Tablespace grow after upgrade.Keep monitor them and check the estimates

Purge the recyclebin table

After the fixup,verify the actions column again if we need to take any action

We can ignore optional messages

Check the FRA and archive log destinations on the server for available space

Database should be in archivelog mode

Stop the listener

Enable guaranteed flashback on database for safer side in case of failure,we can rollback the database to previous state

Create a restore point with GF

Verify the restore point

Compatible parameter should be same as database version

START UPGRADE ACTIVITY

Verify if pfile,spfile,password file and network files are there in place in 19c dbs location

export the env variables like oracle sid and home

Connect to 19c home sqlplus and start the instance in upgrade mode

Verify the version and status of instance.Note that from this step onwards, i have renamed the database name from orcl19x to orcl19x1

Call the catctl and catupgrd scripts from 19c home and based on the CPU cores ,give the degree of parallelism for faster upgrade process or use dbupgrade script

Monitor the CPU consumption for peak utilization on the host using TOP during the upgrade process

[oracle@orcl19x upgrdlog]$ /data01/product/19.0.0/perl/bin/perl /data01/product/19.0.0/rdbms/admin/catctl.pl -l /tmp/upgrdlog -n 2 /data01/product/19.0.0/rdbms/admin/catupgrd.sql

Argument list for [/data01/product/19.0.0/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = /tmp/upgrdlog
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 2
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: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]


/data01/product/19.0.0/rdbms/admin/orahome = [/data01/product/19.0.0]
/data01/product/19.0.0/bin/orabasehome = [/data01/product/19.0.0]
catctlGetOraBaseLogDir = [/data01/product/19.0.0]

Analyzing file /data01/product/19.0.0/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/upgrdlog]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/upgrdlog/catupgrd_catcon_11652.lst]

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

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


Number of Cpus        = 2
Database Name         = orcl19x1
DataBase Version      = 19.0.0.0.0
Parallel SQL Process Count            = 2
Components in [orcl19x1]
    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-107]         Start Time:[2020_12_19 17:20:00]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl19x1] Files:1    Time: 221s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl19x1] Files:5    Time: 110s
Restart  Phase #:2    [orcl19x1] Files:1    Time: 2s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl19x1] Files:19   Time: 34s
Restart  Phase #:4    [orcl19x1] Files:1    Time: 1s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl19x1] Files:7    Time: 27s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl19x1] Files:1    Time: 34s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl19x1] Files:2    Time: 30s
Restart  Phase #:8    [orcl19x1] Files:1    Time: 4s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl19x1] Files:67   Time: 278s
Restart  Phase #:10   [orcl19x1] Files:1    Time: 4s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl19x1] Files:1    Time: 133s
Restart  Phase #:12   [orcl19x1] Files:1    Time: 5s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl19x1] Files:94   Time: 26s
Restart  Phase #:14   [orcl19x1] Files:1    Time: 1s
Parallel Phase #:15   [orcl19x1] Files:120  Time: 63s
Restart  Phase #:16   [orcl19x1] Files:1    Time: 3s
Serial   Phase #:17   [orcl19x1] Files:22   Time: 18s
Restart  Phase #:18   [orcl19x1] Files:1    Time: 8s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl19x1] Files:32   Time: 60s
Restart  Phase #:20   [orcl19x1] Files:1    Time: 2s
Serial   Phase #:21   [orcl19x1] Files:3    Time: 20s
Restart  Phase #:22   [orcl19x1] Files:1    Time: 2s
Parallel Phase #:23   [orcl19x1] Files:25   Time: 219s
Restart  Phase #:24   [orcl19x1] Files:1    Time: 2s
Parallel Phase #:25   [orcl19x1] Files:12   Time: 149s
Restart  Phase #:26   [orcl19x1] Files:1    Time: 2s
Serial   Phase #:27   [orcl19x1] Files:1    Time: 0s
Serial   Phase #:28   [orcl19x1] Files:3    Time: 11s
Serial   Phase #:29   [orcl19x1] Files:1    Time: 0s
Restart  Phase #:30   [orcl19x1] Files:1    Time: 13s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl19x1] Files:1    Time: 9s
Restart  Phase #:32   [orcl19x1] Files:1    Time: 5s
Serial   Phase #:34   [orcl19x1] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl19x1] Files:293  Time: 50s
Serial   Phase #:36   [orcl19x1] Files:1    Time: 0s
Restart  Phase #:37   [orcl19x1] Files:1    Time: 8s
Serial   Phase #:38   [orcl19x1] Files:6    Time: 12s
Restart  Phase #:39   [orcl19x1] Files:1    Time: 4s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl19x1] Files:3    Time: 122s
Restart  Phase #:41   [orcl19x1] Files:1    Time: 2s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl19x1] Files:13   Time: 296s
Restart  Phase #:43   [orcl19x1] Files:1    Time: 2s
Parallel Phase #:44   [orcl19x1] Files:11   Time: 32s
Restart  Phase #:45   [orcl19x1] Files:1    Time: 13s
Parallel Phase #:46   [orcl19x1] Files:3    Time: 12s
Restart  Phase #:47   [orcl19x1] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl19x1] Files:1    Time: 25s
Restart  Phase #:49   [orcl19x1] Files:1    Time: 6s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl19x1] Files:1    Time: 26s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl19x1] Files:1    Time: 4s
Restart  Phase #:52   [orcl19x1] Files:1    Time: 9s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl19x1] Files:2    Time: 14s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl19x1] Files:1    Time: 4s
Serial   Phase #:56   [orcl19x1] Files:3    Time: 9s
Serial   Phase #:57   [orcl19x1] Files:3    Time: 6s
Parallel Phase #:58   [orcl19x1] Files:10   Time: 24s
Parallel Phase #:59   [orcl19x1] Files:25   Time: 7s
Serial   Phase #:60   [orcl19x1] Files:4    Time: 18s
Serial   Phase #:61   [orcl19x1] Files:1    Time: 0s
Serial   Phase #:62   [orcl19x1] Files:32   Time: 12s
Serial   Phase #:63   [orcl19x1] Files:1    Time: 0s
Parallel Phase #:64   [orcl19x1] Files:6    Time: 18s
Serial   Phase #:65   [orcl19x1] Files:2    Time: 24s
Serial   Phase #:66   [orcl19x1] Files:3    Time: 26s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl19x1] Files:1    Time: 5s
Serial   Phase #:69   [orcl19x1] Files:1    Time: 34s
Parallel Phase #:70   [orcl19x1] Files:2    Time: 28s
Restart  Phase #:71   [orcl19x1] Files:1    Time: 2s
Parallel Phase #:72   [orcl19x1] Files:2    Time: 26s
Serial   Phase #:73   [orcl19x1] Files:2    Time: 5s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl19x1] Files:1    Time: 4s
Serial   Phase #:76   [orcl19x1] Files:1    Time: 178s
Serial   Phase #:77   [orcl19x1] Files:2    Time: 23s
Restart  Phase #:78   [orcl19x1] Files:1    Time: 4s
Serial   Phase #:79   [orcl19x1] Files:1    Time: 103s
Restart  Phase #:80   [orcl19x1] Files:1    Time: 9s
Parallel Phase #:81   [orcl19x1] Files:3    Time: 152s
Restart  Phase #:82   [orcl19x1] Files:1    Time: 4s
Serial   Phase #:83   [orcl19x1] Files:1    Time: 25s
Restart  Phase #:84   [orcl19x1] Files:1    Time: 3s
Serial   Phase #:85   [orcl19x1] Files:1    Time: 18s
Restart  Phase #:86   [orcl19x1] Files:1    Time: 2s
Parallel Phase #:87   [orcl19x1] Files:4    Time: 255s
Restart  Phase #:88   [orcl19x1] Files:1    Time: 2s
Serial   Phase #:89   [orcl19x1] Files:1    Time: 8s
Restart  Phase #:90   [orcl19x1] Files:1    Time: 2s
Serial   Phase #:91   [orcl19x1] Files:2    Time: 38s
Restart  Phase #:92   [orcl19x1] Files:1    Time: 6s
Serial   Phase #:93   [orcl19x1] Files:1    Time: 7s
Restart  Phase #:94   [orcl19x1] Files:1    Time: 3s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl19x1] Files:1    Time: 26s
Restart  Phase #:96   [orcl19x1] Files:1    Time: 2s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl19x1] Files:1    Time: 12s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl19x1] Files:1    Time: 371s
*******************   Migration   ******************
Serial   Phase #:99   [orcl19x1] Files:1    Time: 133s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl19x1] Files:1    Time: 5s
Serial   Phase #:101  [orcl19x1] Files:1    Time: 0s
Serial   Phase #:102  [orcl19x1] Files:1    Time: 121s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl19x1] Files:1    Time: 63s
****************   Summary report   ****************
Serial   Phase #:104  [orcl19x1] Files:1    Time: 7s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl19x1] Files:1    Time: 7s
Serial   Phase #:106  [orcl19x1] Files:1    Time: 0s
Serial   Phase #:107  [orcl19x1] Files:1     Time: 46s

------------------------------------------------------
Phases [90-107]         End Time:[2020_12_19 18:34:05]
------------------------------------------------------

Grand Total Time: 4455s

 LOG FILES: (/tmp/upgrdlog/catupgrd*.log)

Upgrade Summary Report Located in:
/tmp/upgrdlog/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:14m:5s]
[4]+  Killed                  /data01/product/19.0.0/perl/bin/perl /data01/product/19.0.0/rdbms/admin/catctl.pl -l /tmp/upgrdlog -n 2 /data01/product/19.0.0/rdbms/admin/catupgrd.sql

Check the upgrade summary logs for validation

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

Grand Total Upgrade Time:    [0d:1h:14m:5s]

Export the env and start the instance from 19c

[oracle@orcl19x upgrdlog]$ export ORACLE_HOME=/data01/product/19.0.0                                     [oracle@orcl19x upgrdlog]$ /data01/product/19.0.0/bin/sqlplus / as sysdba                                
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 19 19:19:48 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1052770304 bytes
Database Buffers          503316480 bytes
Redo Buffers                7864320 bytes
Database mounted.
Database opened.

Check the instance version

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

NAME      OPEN_MODE            STATUS       VERSION
--------- -------------------- ------------ -----------------
ORCL19X1  READ WRITE           OPEN         19.0.0.0.0

Check the dba registry for upgraded components

Compile all the invalid objects

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

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2020-12-19 19:34:13

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              2020-12-19 19:46:44

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.

Verify the objects

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

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

POSTUPGRADE TASKS

run the postupgrade scripts which were generated in the beginning phase

SQL> @/tmp/preupgrade/postupgrade_fixups.sql

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-12-18 21:50:23

For Source Database:     ORCL19X1
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  19.0.0.0.0

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

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

PL/SQL procedure successfully completed.


Session altered.

Its always required to fix and upgrade the timezone post upgrade

step 1:

SQL> @/data01/product/19.0.0/rdbms/admin/utltz_countstats.sql

Session altered.

.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date  - Owner.TableName.ColumnName - num_rows
07/07/2014 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
07/07/2014 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
07/07/2014 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
19/12/2020 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1
18/12/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
18/12/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
18/12/2020 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
18/12/2020 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
18/12/2020 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
19/12/2020 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
19/12/2020 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
18/12/2020 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
18/12/2020 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3
18/12/2020 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3
19/12/2020 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
19/12/2020 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
19/12/2020 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 250
19/12/2020 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
05/12/2020 - SYS.RADM_FPTM$.TSWTZ_COL - 1
19/12/2020 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
19/12/2020 - SYS.REG$.REG_TIME - 2
19/12/2020 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 1116
19/12/2020 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
19/12/2020 - SYS.SCHEDULER$_JOB.END_DATE - 25
19/12/2020 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 25
19/12/2020 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 25
19/12/2020 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 25
19/12/2020 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 25
19/12/2020 - SYS.SCHEDULER$_JOB.START_DATE - 25
18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 919
18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 919
18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 919
05/12/2020 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
05/12/2020 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
19/12/2020 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
19/12/2020 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
19/12/2020 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
19/12/2020 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
19/12/2020 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
19/12/2020 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 3
13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 3
13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 3
18/12/2020 - SYS.STATS_TARGET$.END_TIME - 703
18/12/2020 - SYS.STATS_TARGET$.START_TIME - 703
05/12/2020 - SYS.TAB_STATS$.SPARE6 - 1028
19/12/2020 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 28
19/12/2020 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 28
19/12/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 197447
19/12/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 197447
19/12/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 68176
19/12/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 68176
19/12/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 4274
19/12/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 4274
19/12/2020 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 1094
19/12/2020 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1094
19/12/2020 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 1094
19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 9206
19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 9206
19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 9206
19/12/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 4919
19/12/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 4919
19/12/2020 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 11
19/12/2020 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 1
19/12/2020 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 1
19/12/2020 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 1
19/12/2020 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 30
19/12/2020 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 30
07/07/2014 - SYS.XS$PRIN.END_DATE - 14
07/07/2014 - SYS.XS$PRIN.START_DATE - 14
Total numrows of SYS TSTZ columns is : 587732
There are in total 162 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date  - Owner.Tablename.Columnname - num_rows
07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
1
05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4
05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4
05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4
05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1
05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1
05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1
07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
07/07/2014 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
07/07/2014 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 23
There are in total 58 non-SYS TSTZ columns.
Total Minutes elapsed : 0

Session altered.

step 2:

SQL>  @/data01/product/19.0.0/rdbms/admin/utltz_countstar.sql

Session altered.

.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
.
For SYS tables first ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
SYS.KET$_CLIENT_TASKS.LG_DATE - 3
SYS.KET$_CLIENT_TASKS.LT_DATE - 3
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 631
SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
SYS.RADM_FPTM$.TSWTZ_COL - 1
SYS.REG$.NTFN_GROUPING_START_TIME - 2
SYS.REG$.REG_TIME - 2
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 1117
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
SYS.SCHEDULER$_JOB.END_DATE - 25
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 25
SYS.SCHEDULER$_JOB.LAST_END_DATE - 25
SYS.SCHEDULER$_JOB.LAST_START_DATE - 25
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 25
SYS.SCHEDULER$_JOB.START_DATE - 25
SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 954
SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 954
SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 954
SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
SYS.SCHEDULER$_WINDOW.END_DATE - 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
SYS.SCHEDULER$_WINDOW.START_DATE - 9
SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 3
SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 3
SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 3
SYS.STATS_TARGET$.END_TIME - 703
SYS.STATS_TARGET$.START_TIME - 703
SYS.TAB_STATS$.SPARE6 - 1025
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 28
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 28
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 200931
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 200931
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 68384
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 68384
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 4285
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 4285
SYS.WRI$_OPTSTAT_OPR.END_TIME - 1094
SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1094
SYS.WRI$_OPTSTAT_OPR.START_TIME - 1094
SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 9221
SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 9221
SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 9221
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 4923
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 4923
SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 11
SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 1
SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 1
SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 1
SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 30
SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 30
SYS.XS$PRIN.END_DATE - 15
SYS.XS$PRIN.START_DATE - 15
Total count * of SYS TSTZ columns is : 595680
There are in total 162 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4
IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4
IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4
IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1
IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1
IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total count * of non-SYS TSTZ columns is :  23
There are in total 58 non-SYS TSTZ columns.
Total Minutes elapsed : 0

Session altered.

step 3:

SQL> @/data01/product/19.0.0/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 DSTv18 .
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> @/data01/product/19.0.0/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.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1136656384 bytes
Database Buffers          419430400 bytes
Redo Buffers                7864320 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.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1136656384 bytes
Database Buffers          419430400 bytes
Redo Buffers                7864320 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
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
Table list: "SQLTXPLAIN"."SQLT$_SQL_STATEMENT"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_STGTAB_DIRECTIVE"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_COL_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_STATTAB"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLI$_STATTAB_TEMP"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_TAB_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_IND_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_HISTGRM_STATS_VERSN"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_OPTSTAT_OPERATIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_SCHEDULER_JOBS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_AUTOTASK_CLIENT_HST"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_OPTSTAT_USER_PREFS$"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_WRI$_OPTSTAT_AUX_HISTORY"
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.

Timezone has been upgraded

Check the post upgrade status of dba registry

SQL> @/data01/product/19.0.0/rdbms/admin/utlusts
Enter value for 1: TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    12-19-2020 20:37:4
Database Name: ORCL19X1

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

Oracle Server                             VALID      19.3.0.0.0  00:35:06
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:00:00
Oracle XDK                                VALID      19.3.0.0.0  00:00:00
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:00
OLAP Analytic Workspace                   VALID      19.3.0.0.0  00:00:00
Oracle Label Security                     VALID      19.3.0.0.0  00:00:00
Oracle Database Vault                     VALID      19.3.0.0.0  00:00:00
Oracle Text                               VALID      19.3.0.0.0  00:00:00
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:00:00
Oracle Real Application Clusters     OPTION OFF      19.3.0.0.0  00:00:00
Oracle XML Database                       VALID      19.3.0.0.0  00:00:00
Oracle Multimedia                         VALID      19.3.0.0.0  00:00:00
Spatial                                   VALID      19.3.0.0.0  00:20:37
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:22
Datapatch                                                        00:05:51
Final Actions                                                    00:08:26
Post Upgrade                                                     00:00:41
Post Compile                                                     00:12:30

Total Upgrade Time: 01:23:03

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

Run the catuppst.sql script to perform remaining upgrade actions to release the instance from upgrade state and drop the temporary view and tables created by upgrade process

SQL>  @/data01/product/19.0.0/rdbms/admin/catuppst

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART              2020-12-19 20:39:42
DBUA_TIMESTAMP DBRESTART     FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP DBRESTART         NONE 2020-12-19 20:39:42


TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST       STARTED 2020-12-19 20:39:42

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN             2020-12-19 20:39:42
DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP POSTUP_BGN        NONE 2020-12-19 20:39:42


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN             2020-12-19 20:39:42
DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP CATREQ_BGN        NONE 2020-12-19 20:39:42

catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END             2020-12-19 20:39:42
DBUA_TIMESTAMP CATREQ_END    FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP CATREQ_END        NONE 2020-12-19 20:39:42

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END             2020-12-19 20:39:42
DBUA_TIMESTAMP POSTUP_END    FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP POSTUP_END        NONE 2020-12-19 20:39:42


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST               2020-12-19 20:39:42
DBUA_TIMESTAMP CATUPPST      FINISHED 2020-12-19 20:39:42
DBUA_TIMESTAMP CATUPPST          NONE 2020-12-19 20:39:42

Run the postupgrade script again to revalidate the fixup recommendations

SQL> @/tmp/preupgrade/postupgrade_fixups.sql
No errors.
No errors.



No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-12-18 21:50:23

For Source Database:     ORCL19X1
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  19.0.0.0.0

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

Again check invalid objects

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

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

Drop the restore point

SQL> drop restore point GUA;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

Set compatible parameter to 19.0.0(need a bounce) and turn off the flashback if not required

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
SQL> alter system set compatible='19.0.0' scope=spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1136656384 bytes
Database Buffers          419430400 bytes
Redo Buffers                7864320 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter compatible

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

Check password file status

SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

Copy network files from 12c to 19c home -> /network/admin directory

[oracle@orcl19x admin]$ cp /apps01/product/12.1.0/dbhome_1/network/admin/* /data01/product/19.0.0/network/admin/
cp: omitting directory ‘/apps01/product/12.1.0/dbhome_1/network/admin/samples’

Modify the oracle home location inside listener.ora file

[oracle@orcl19x admin]$ cat listener.ora
# listener.ora Network Configuration File: /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl19x1)
      (ORACLE_HOME = /data01/product/19.0.0)
      (SID_NAME = orcl19x1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19x1)(PORT = 1521))
  )

ADR_BASE_LISTENER = /data01/base

Relink all the binaries from 19c home

[oracle@orcl19x bin]$ ./relink all
writing relink log to: /data01/product/19.0.0/install/relinkActions2020-12-19_09-09-43PM.log

Take incremental level 0 backup of database immediately

RMAN> run
2>  {
3> allocate channel ch1 device type disk;
4>  allocate channel ch2 device type disk;
5> crosscheck backup;
6> crosscheck archivelog all;
7> delete expired archivelog all;
8> backup as compressed backupset incremental level 0 database plus archivelog delete input;
9>  backup current controlfile;
10>  release channel ch1;
11>  release channel ch2;
12> }

allocated channel: ch1
channel ch1: SID=36 device type=DISK

allocated channel: ch2
channel ch2: SID=21 device type=DISK

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data01/FRA/ORCL19X1/autobackup/2020_12_19/o1_mf_s_1059599126_hxw7fzhg_.bkp RECID=1 STAMP=1059599127
Crosschecked 1 objects
....................................................................................................................................................
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-DEC-20

Starting Control File and SPFILE Autobackup at 19-DEC-20
piece handle=/data01/FRA/ORCL19X1/autobackup/2020_12_19/o1_mf_s_1059602828_hxwc1obp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-DEC-20

released channel: ch1

released channel: ch2

Test the application connectivity and behaviour with 19c oracle for any performance issues but obviously 19c has more performance benefits !

Delete the flashback logs from mountpoint to clear unwanted flashback logfile generated from guarantee flashback after application connections are validated

Just decrease the flashback retention target to delete files automatically

Here i given 1 sec to instantly delete the log files to clear space

SQL> alter system set db_flashback_retention_target=1;

System altered.

[oracle@orcl19x flashback]$ ls -lrt /data01/FRA/ORCL19X1/flashback/
total 0


Clean up the old version of apex

VERSION
------------------------------
COMP_NAME
------------------------------
4.2.5.00.08
Oracle Application Express

Run the script to remove the old apex from 12c home or create a new tablespace to hold latest apex version

SQL> @/apps01/product/12.1.0/dbhome_1/apex/apxremov.sql
SQL> drop public synonym apex_spatial;
SQL> drop public synonym apex_pkg_app_install_log;

Validate the apex component version

Create an apex user and validate his password

SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apex
User "apex" does not yet exist and will be created.
Enter apex's email [apex]
Enter apex's password []
Created instance administrator APEX.

Upgrade rman catalog version

RMAN> connect target /

connected to target database: ORCL19X1 (DBID=1992703147)

RMAN> register database;


database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>
RMAN> upgrade catalog;

recovery catalog owner is RCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 19.03.00.00.00
DBMS_RCVMAN package upgraded to version 19.03.00.00
DBMS_RCVCAT package upgraded to version 19.03.00.00.

Edit the bash profile to new oracle home for permanent environment variables change

[oracle@orcl19x dbs]$ cat /home/oracle/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin
ifcfg up
export PATH

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/data01/base <--
export DB_HOME=$ORACLE_BASE/product/19.0.0/ <--
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=orcl19x1
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

alias db=’. /home/oracle/db.env’

Edit the oratab for 19c home location

[oracle@orcl19x dbs]$ cat /etc/oratab |grep orcl19x
#orcl19x1:/apps01/product/12.1.0/dbhome_1:Y
orcl19x1:/data01/product/19.0.0:Y

Hope there are lot of new advanced features to explore in 19c

Thanks for the visit!

ORA-38708: not enough space for first flashback database log file

To switch on flashback on the database if FRA space is not sufficient to accomodate the flashback log files then,

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file


SQL> !oerr ora 38708
38708, 00000, "not enough space for first flashback database log file"
// *Cause: An ALTER DATABASE FLASHBACK ON command failed because there
//         was not enough space in the Recovery Area for the first
//         flashback database log file.
// *Action: Make more space in the Recovery Area.  For example, this
//          can be done by increasing the value of
//          DB_RECOVERY_FILE_DEST_SIZE.

Increase FRA!

SQL> alter system set db_recovery_file_dest_size=10000M;

System altered.

SQL> alter database flashback on;

Database altered.

ORA-02449: unique/primary keys in table referenced by foreign keys

I try to drop a tablespace with dependent objects but get below error

SQL> !oerr ora 02449
02449, 00000, "unique/primary keys in table referenced by foreign keys"
// *Cause: An attempt was made to drop a table with unique or
//         primary keys referenced by foreign keys in another table.
// *Action: Before performing the above operations the table, drop the
//          foreign key constraints in other tables. You can see what
//          constraints are referencing a table by issuing the following
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Solution:

include cascade constraints option to drop the dependent objects!