• Oracle Database 12c升级到19c(Redhat Linux12.2.0.1 Upgrade to 19.3.0.0)

    目录

    0. Check Compatibility Before Upgrading Oracle Database

    1. Environment

    2. Backup

    3. Run preupgrade script

    4. View Preupgrade log

    5. Verify tablespace sizes for upgrade

    6. Update INITIALIZATION PARAMETERS

    7. Gather DICTIONARY STATS

    8. Purge Recyclebin

    9. Refresh MVs

    10. Run preupgrade_fixups.sql

    11. Verify archive log dest size

    12. Stop LISTENER

    13. Create Flashback Guaranteed Restore Point

    14. Shutdown Database

    15. Copy init and password files from 12c to 19c dbs home

    16. Startup DB in Upgrade mode from 19c home

    17. Run dbupgrade

    18. Starup DB from 19c home

    19. Run utlrp.sql

    20. Run postupgrade_fixups.sql

    21. Upgrade Timezone

    22. Run utlusts.sql

    23. Run catuppst.sql

    24. Re-Run postupgrade_fixups.sql

    25. Reverify INVALID OBJECTS

    26. Drop Restore point

    27. Set COMPATIBALE parameter value to 19.0.0

    28. Verify DBA_REGISTRY

    29. Add TNS Entries in 19c TNS home

    30. Password File – orapwCID

    31. Edit oratab

    32. Back Up the Database

    近两天，因为系统维护的需求，我需将原来的12c数据实例升级到19c，版本号12.2.0.1>19.3.0(12.2.0.3)，中间花了较多时间来折腾，不过结果不错，毕竟成功了

    我的系统环境是小红帽Redhat Linux 7.6，参照一些外文资料，具体操作如下

    0. Check Compatibility Before Upgrading Oracle Database

    需了解的Oracle数据库产品什么周期

    1. Environment

    1. Hostname       		: RAC1.RAJASEKHAR.COM
    2. Database Name  		: CID
    3. DB VERSION		: 12.2.0.1
    4. CDB			: NON-CDB, Single Instance
    5. DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
    6. Datafile Location 	: /u01/app/oracle/oradata/CID
    7.  
    8. Target DB VERSION 	: 19c (19.4.0.0.0)
    9. Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1
    10.  
    11. Upgrade Method	       : Manual

    PRE-UPGRADE TASKS

    2. Backup

    Database Backup scripts - I have taken already

Sample Backup Database Script: rmanbackup.sh

    1. [oracle@rac1 CID]$ 
    2. [oracle@rac1 CID]$ chmod 775 rmanbackup.sh
    3. [oracle@rac1 CID]$ 
    4. [oracle@rac1 CID]$ nohup ./rmanbackup.sh & <--- hit ENTER twice.
    TNS Files 

    1. [oracle@rac1 ~]$ cd u01/app/oracle/product/12.2.0/dbhome_1/network/admin/ 
    2. [oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/ 
    PFILE/SPFILE/PASSWORD (orapwSID) FILES 

    1. [oracle@rac1 dbs]$ pwd 
    2. /u01/app/oracle/product/12.2.0/dbhome_1/dbs 
    3. [oracle@rac1 dbs]$ cp -p spfileCID.ora orapwCID /u01/app/backup/ 
    INVALID OBJECTS 

    1. SQL> select count(*) from dba_objects where status='INVALID';
    2.  
    3.   COUNT(*)
    4. ----------
    5.          0 <----
    6.  
    7. SQL>


    3. Run preupgrade script

    . oraenv  (CID)

    /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

    --- OR ---

    /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/CID/preupgrade
    1. [oracle@rac1 ~]$ mkdir -p /home/oracle/CID/preupgrade
    2. [oracle@rac1 ~]$
    3.  
    4. [oracle@rac1 ~]$ . oraenv
    5. ORACLE_SID = [oracle] ? CID
    6. The Oracle base has been set to /u01/app/oracle
    7. [oracle@rac1 ~]$
    8. [oracle@rac1 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/CID/preupgrade
    9. ==================
    10. PREUPGRADE SUMMARY
    11. ==================
    12.   /home/oracle/CID/preupgrade/preupgrade.log
    13.   /home/oracle/CID/preupgrade/preupgrade_fixups.sql
    14.   /home/oracle/CID/preupgrade/postupgrade_fixups.sql
    Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

    @/home/oracle/CID/preupgrade/preupgrade_fixups.sql
    After the upgrade:

    1. Log into the database and execute the postupgrade fixups
    2. @/home/oracle/CID/preupgrade/postupgrade_fixups.sql
    3. Preupgrade complete: 2020-01-28T20:45:05
    4. [oracle@rac1 ~]$

    4. View Preupgrade log

    [oracle@rac1 ~]$ cat /home/oracle/CID/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-01-28T20:45:05

Upgrade-To version: 19.0.0.0.0

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

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

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

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

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      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.

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

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

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

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

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

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             460 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  4.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
      4618 MB of archived logs.  Check alert log during the upgrade that there
      is no write error to the destination due to lack of disk space.

      Archiving cannot proceed if the archive log destination is full during
      upgrade.

      Archive Log Destination:
       Parameter    :  LOG_ARCHIVE_DEST_1
       Destination  :  /u01/app/archive/CID

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

  5.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

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

    SQL>@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

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

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

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

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

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  7.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  8.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

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

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

    SQL>@/home/oracle/CID/preupgrade/postupgrade_fixups.sql


[oracle@rac1 ~]$


    5. Verify tablespace sizes for upgrade

    ** Tablespace Auto extend ON and max size also set, hence no action taken.

DATABASE - Bright DBA


    1. TABLESPACE_NAME   AUT FILE_NAME                                  TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
    2. ----------------- --- ------------------------------------------ ----------- ---------- ---------- ----------
    3. SYSAUX            YES /u01/app/oracle/oradata/CID/sysaux01.dbf           460         23       5.08 31.9999847
    4. SYSTEM            YES /u01/app/oracle/oradata/CID/system01.dbf           800          4        .48 31.9999847
    5. UNDOTBS1          YES /u01/app/oracle/oradata/CID/undotbs01.dbf           70          3        4.2 31.9999847
    6. USERS             YES /u01/app/oracle/oradata/CID/users01.dbf              5          4         80 31.9999847
    7. *****************                                                ----------- ----------            ----------
    8. sum                                                                     1335         34            127.999939
    9.  
    10. SQL>


    6. Update INITIALIZATION PARAMETERS

    In this test scenario, noting to update as per preupgrade.log. Hence no action taken.


    7. Gather DICTIONARY STATS

    1. SQL> SET ECHO ON;
    2. SQL> SET SERVEROUTPUT ON;
    3. SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
    4.  
    5. PL/SQL procedure successfully completed.
    6.  
    7. SQL>


    8. Purge Recyclebin

    SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


    9. Refresh MVs

    *** Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Run the following SQL query:

    1. SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
    2.  
    3. no rows selected
    4.  
    5. SQL>
    6.  
    7. SQL> declare
    8. list_failures integer(3) :=0;
    9. begin
    10. DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
    11. end;
    12. /
    13.  
    14. PL/SQL procedure successfully completed.
    15.  
    16. SQL>


    10. Run preupgrade_fixups.sql

    1. SQL> @/home/oracle/CID/preupgrade/preupgrade_fixups.sql
    2. SQL> REM
    3. SQL> REM    Oracle PRE-Upgrade Fixup Script
    4. SQL> REM
    5. SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
    6. SQL> REM                             Version: 19.0.0.0.0 Build: 1
    7. SQL> REM    Generated on:            2020-01-28 20:45:02
    8. SQL> REM
    9. SQL> REM    Source Database:         CID
    10. SQL> REM    Source Database Version: 12.2.0.1.0
    11. SQL> REM    For Upgrade to Version:     19.0.0.0.0
    12. SQL> REM
    13. SQL>
    14. SQL> REM
    15. SQL> REM    Setup Environment
    16. SQL> REM
    17. SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
    18. Executing Oracle PRE-Upgrade Fixup Script
    19.  
    20. Auto-Generated by:       Oracle Preupgrade Script
    21.                          Version: 19.0.0.0.0 Build: 1
    22. Generated on:            2020-01-28 20:45:02
    23.  
    24. For Source Database:     CID
    25. Source Database Version: 12.2.0.1.0
    26. For Upgrade to Version:  19.0.0.0.0
    27.  
    28. Preup                             Preupgrade
    29. Action                            Issue Is
    30. Number  Preupgrade Check Name     Remedied    Further DBA Action
    31. ------  ------------------------  ----------  --------------------------------
    32.     1.  dictionary_stats          YES         None.
    33.     2.  pre_fixed_objects         YES         None.
    34.     3.  tablespaces_info          NO          Informational only.
    35.                                               Further action is optional.
    36.     4.  min_archive_dest_size     NO          Informational only.
    37.                                               Further action is optional.
    38.     5.  rman_recovery_version     NO          Informational only.
    39.                                               Further action is optional.
    40.  
    41. The fixup scripts have been run and resolved what they can. However,
    42. there are still issues originally identified by the preupgrade that
    43. have not been remedied and are still present in the database.
    44. Depending on the severity of the specific issue, and the nature of
    45. the issue itself, that could mean that your database is not ready
    46. for upgrade.  To resolve the outstanding issues, start by reviewing
    47. the preupgrade_fixups.sql and searching it for the name of
    48. the failed CHECK NAME or Preupgrade Action Number listed above.
    49. There you will find the original corresponding diagnostic message
    50. from the preupgrade which explains in more detail what still needs
    51. to be done.
    52.  
    53. PL/SQL procedure successfully completed.
    54.  
    55. SQL>


    11. Verify archive log dest size

    *** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations
    1. SQL> archive log list
    2. Database log mode              Archive Mode
    3. Automatic archival             Enabled
    4. Archive destination            /u01/app/archive/CID
    5. Oldest online log sequence     1
    6. Next log sequence to archive   2
    7. Current log sequence           2
    8. SQL>
    9. SQL> !df -h /u01/app/archive/CID
    10. Filesystem      Size  Used Avail Use% Mounted on
    11. /dev/sda5        67G   35G   33G  52% /u01 <----
    12.  
    13. SQL>


    12. Stop LISTENER

    1. [oracle@rac1 ~]$ ps -ef | grep tns
    2. root        15     2  0 20:01 ?        00:00:00 [netns]
    3. oracle    3943     1  0 20:08 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_CID -inherit
    4. oracle   16771  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns
    5. [oracle@rac1 ~]$ lsnrctl stop LISTENER_CID
    6.  
    7. LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 21:03:14
    8.  
    9. Copyright (c) 1991, 2016, Oracle.  All rights reserved.
    10.  
    11. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1524)))
    12. The command completed successfully
    13. [oracle@rac1 ~]$
    14. [oracle@rac1 ~]$ ps -ef | grep tns
    15. root        15     2  0 20:01 ?        00:00:00 [netns]
    16. oracle   16812  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns
    17. [oracle@rac1 ~]$


    13. Create Flashback Guaranteed Restore Point

    *** NO need to enable Flashback Database from 11.2.0.1 onwards
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version
    1. SQL> select flashback_on from v$database;
    2.  
    3. FLASHBACK_ON
    4. ------------------
    5. NO <-----
    6.  
    7. SQL> select name,open_mode,log_mode from v$database;
    8.  
    9. NAME      OPEN_MODE            LOG_MODE
    10. --------- -------------------- ------------
    11. CID       READ WRITE           ARCHIVELOG
    12.  
    13. SQL> show parameter compatible
    14.  
    15. NAME                                 TYPE        VALUE
    16. ------------------------------------ ----------- ------------------------------
    17. compatible                           string      12.2.0 <----
    18. noncdb_compatible                    boolean     FALSE
    19. SQL>
    20. SQL> show parameter recovery
    21.  
    22. NAME                                 TYPE        VALUE
    23. ------------------------------------ ----------- ------------------------------
    24. db_recovery_file_dest                string
    25. db_recovery_file_dest_size           big integer 0
    26. recovery_parallelism                 integer     0
    27. remote_recovery_file_dest            string
    28. SQL>
    29. SQL>!mkdir -p /u01/app/oracle/fast_recovery_area
    30.  
    31. SQL> alter system set db_recovery_file_dest_size=10G;
    32.  
    33. System altered.
    34.  
    35. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
    36.  
    37. System altered.
    38.  
    39. SQL> show parameter recovery
    40.  
    41. NAME                                 TYPE        VALUE
    42. ------------------------------------ ----------- ------------------------------
    43. db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
    44. db_recovery_file_dest_size           big integer 10G
    45. recovery_parallelism                 integer     0
    46. remote_recovery_file_dest            string
    47. SQL>
    48.  
    49. SQL> select * from V$restore_point;
    50.  
    51. no rows selected
    52.  
    53. SQL>
    54.  
    55. SQL> create restore point pre_upgrade guarantee flashback database;
    56.  
    57. Restore point created.
    58.  
    59. SQL>
    60.  
    61. SQL> col name for a20
    62. col GUARANTEE_FLASHBACK_DATABASE for a10
    63. col TIME for a60
    64. set lines 190
    65. select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
    66.  
    67. NAME                 GUARANTEE_ TIME
    68. -------------------- ---------- ------------------------------------------------------------
    69. PRE_UPGRADE          YES        28-JAN-20 09.05.50.000000000 PM
    70.  
    71. SQL>

    UPGRADE TASK

    14. Shutdown Database

    1. SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
    2.  
    3. NAME                 OPEN_MODE
    4. -------------------- --------------------
    5. CID                  READ WRITE
    6.  
    7. SQL> SHUT IMMEDIATE;
    8. Database closed.
    9. Database dismounted.
    10. ORACLE instance shut down.
    11. SQL>

    15. Copy init and password files from 12c to 19c dbs home

    1. [oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
    2. [oracle@rac1 dbs]$ ls -ltr *CID*
    3. -rw-r-----. 1 oracle oinstall   24 Jan 28 20:08 lkCID
    4. -rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwCID <---
    5. -rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileCID.ora <---
    6. -rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_CID.dat
    7. [oracle@rac1 dbs]$
    8. [oracle@rac1 dbs]$ cp orapwCID spfileCID.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
    9. [oracle@rac1 dbs]$
    10. [oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*CID*
    11. -rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileCID.ora
    12. -rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCID
    13. [oracle@rac1 dbs]$

    16. Startup DB in Upgrade mode from 19c home

    1. [oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
    2. [oracle@rac1 ~]$ export ORACLE_SID=CID
    3. [oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH
    4. [oracle@rac1 ~]$ which sqlplus
    5. /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
    6. [oracle@rac1 ~]$ sqlplus / as sysdba
    7.  
    8. SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 21:13:24 2020
    9. Version 19.4.0.0.0
    10.  
    11. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    12.  
    13. Connected to an idle instance.
    14.  
    15. SQL> startup upgrade;
    16. ORACLE instance started.
    17.  
    18. Total System Global Area 1560277408 bytes
    19. Fixed Size                  8896928 bytes
    20. Variable Size             939524096 bytes
    21. Database Buffers          603979776 bytes
    22. Redo Buffers                7876608 bytes
    23. Database mounted.
    24. Database opened.
    25. SQL>
    26. SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
    27.  
    28. NAME      OPEN_MODE            CDB VERSION           STATUS
    29. --------- -------------------- --- ----------------- ------------
    30. CID       READ WRITE           NO  19.0.0.0.0        OPEN MIGRATE <---
    31.  
    32. SQL>

    17. Run dbupgrade

    You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.

# Regular upgrade command

    1. [oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade
    2. [oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
    3. [oracle@rac1 ~]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &
    --- OR ---

# Shorthand command

    1. [oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
    2. [oracle@rac1 bin]$ ls -ltr dbupgrade
    3. -rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade
    4. [oracle@rac1 bin]$
    5. [oracle@rac1 bin]$ nohup ./dbupgrade & <--- Hit ENTER twice
    6. [1] 22584
    7. [oracle@rac1 bin]$ nohup: ignoring input and appending output to ânohup.outâ
    8.  
    9. [oracle@rac1 bin]$ jobs -l
    10. [1]+ 22584 Running                 nohup ./dbupgrade & <----
    11. [oracle@rac1 bin]$
    12. [oracle@rac1 bin]$ disown
    13. [oracle@rac1 bin]$
    14. [oracle@rac1 bin]$ ps -ef | grep -i catctl.pl
    15. oracle   22589 22584  0 21:33 pts/1    00:00:03 /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl -I/u01/app/oracle/product/19.0.0/dbhome_1/perl/lib /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
    16. oracle   24991  3093  0 21:40 pts/1    00:00:00 grep --color=auto -i catctl.pl
    17. [oracle@rac1 bin]$
    Monitor upgrade log under below location

    18. Starup DB from 19c home

    1. [oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
    2. [oracle@rac1 ~]$ export ORACLE_SID=CID
    3. [oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH
    4. [oracle@rac1 ~]$ which sqlplus
    5. /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
    6. [oracle@rac1 ~]$ sqlplus / as sysdba
    7.  
    8. SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 22:25:18 2020
    9. Version 19.4.0.0.0
    10.  
    11. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    12.  
    13. Connected to an idle instance.
    14.  
    15. SQL> startup;
    16. ORACLE instance started.
    17.  
    18. Total System Global Area 1560277408 bytes
    19. Fixed Size                  8896928 bytes
    20. Variable Size            1174405120 bytes
    21. Database Buffers          369098752 bytes
    22. Redo Buffers                7876608 bytes
    23. Database mounted.
    24. Database opened.
    25. SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
    26.  
    27. NAME      OPEN_MODE            CDB VERSION           STATUS
    28. --------- -------------------- --- ----------------- ------------
    29. CID       READ WRITE           NO  19.0.0.0.0        OPEN <-----
    30.  
    31. SQL>
    32. SQL> col COMP_ID for a10
    33. col COMP_NAME for a40
    34. col VERSION for a15
    35. set lines 180
    36. set pages 999
    37. select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
    38.  
    39. COMP_ID    COMP_NAME                                VERSION         STATUS
    40. ---------- ---------------------------------------- --------------- --------------------------------------------
    41. CATALOG    Oracle Database Catalog Views            19.0.0.0.0      UPGRADED
    42. CATPROC    Oracle Database Packages and Types       19.0.0.0.0      UPGRADED
    43. JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      UPGRADED
    44. XML        Oracle XDK                               19.0.0.0.0      UPGRADED
    45. CATJAVA    Oracle Database Java Packages            19.0.0.0.0      UPGRADED
    46. APS        OLAP Analytic Workspace                  19.0.0.0.0      UPGRADED
    47. RAC        Oracle Real Application Clusters         19.0.0.0.0      UPGRADED
    48. XDB        Oracle XML Database                      19.0.0.0.0      UPGRADED
    49. OWM        Oracle Workspace Manager                 19.0.0.0.0      UPGRADED
    50. CONTEXT    Oracle Text                              19.0.0.0.0      UPGRADED
    51. ORDIM      Oracle Multimedia                        19.0.0.0.0      UPGRADED
    52. SDO        Spatial                                  19.0.0.0.0      LOADING
    53. XOQ        Oracle OLAP API                          19.0.0.0.0      UPGRADED
    54. OLS        Oracle Label Security                    19.0.0.0.0      UPGRADED
    55. DV         Oracle Database Vault                    19.0.0.0.0      UPGRADED
    56.  
    57. 15 rows selected.
    58.  
    59. SQL>

    POST-UPGRADE TASKS WHEN DBUA USING

    19. Run utlrp.sql

    Run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

-- OR ---

    20. Run postupgrade_fixups.sql

    1. SQL> @/home/oracle/CID/preupgrade/postupgrade_fixups.sql
    2.  
    3. Session altered.
    4.  
    5. PL/SQL procedure successfully completed.
    6. PL/SQL procedure successfully completed.
    7. PL/SQL procedure successfully completed.
    8.  
    9. Package created.
    10. No errors.
    11.  
    12. Package body created.
    13. PL/SQL procedure successfully completed.
    14.  
    15. No errors.
    16. Package created.
    17.  
    18. No errors.
    19. Package body created.
    20.  
    21. No errors.
    22. Executing Oracle POST-Upgrade Fixup Script
    23.  
    24. Auto-Generated by:       Oracle Preupgrade Script
    25.                          Version: 19.0.0.0.0 Build: 1
    26. Generated on:            2020-01-28 20:45:05
    27.  
    28. For Source Database:     CID
    29. Source Database Version: 12.2.0.1.0
    30. For Upgrade to Version:  19.0.0.0.0
    31.  
    32. Preup                             Preupgrade
    33. Action                            Issue Is
    34. Number  Preupgrade Check Name     Remedied    Further DBA Action
    35. ------  ------------------------  ----------  --------------------------------
    36.     6.  old_time_zones_exist      NO          Manual fixup recommended.
    37.     7.  post_dictionary           YES         None.
    38.     8.  post_fixed_objects        NO          Informational only.
    39.                                               Further action is optional.
    40.  
    41. The fixup scripts have been run and resolved what they can. However,
    42. there are still issues originally identified by the preupgrade that
    43. have not been remedied and are still present in the database.
    44. Depending on the severity of the specific issue, and the nature of
    45. the issue itself, that could mean that your database upgrade is not
    46. fully complete.  To resolve the outstanding issues, start by reviewing
    47. the postupgrade_fixups.sql and searching it for the name of
    48. the failed CHECK NAME or Preupgrade Action Number listed above.
    49. There you will find the original corresponding diagnostic message
    50. from the preupgrade which explains in more detail what still needs
    51. to be done.
    52.  
    53. PL/SQL procedure successfully completed.
    54.  
    55. Session altered.
    56.  
    57. SQL>

    21. Upgrade Timezone

    For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory

The following scripts get delivered with Oracle Database 18c onward

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql
    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
    
    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql
    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.
    
    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
    Time zone upgrade check script
    
    $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
    Time zone apply script. Warning: This script will restart the database and adjust time zone data.

    1. [oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/
    2. [oracle@rac1 admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql
    3. -rw-r--r--. 1 oracle oinstall  8317 Feb 25  2017 utltz_countstats.sql
    4. -rw-r--r--. 1 oracle oinstall  7423 Feb 25  2017 utltz_countstar.sql
    5. -rw-r--r--. 1 oracle oinstall 33684 Sep  9  2017 utltz_upg_check.sql
    6. -rw-r--r--. 1 oracle oinstall 21526 Sep  9  2017 utltz_upg_apply.sql
    7. [oracle@rac1 admin]$
    8.  
    9. SQL> SELECT version FROM v$timezone_file;
    10.  
    11.    VERSION
    12. ----------
    13.         26 <-----
    14.  
    15. SQL>
    16.  
    17. SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_check.sql
    18.  
    19. Session altered.
    20.  
    21. INFO: Starting with RDBMS DST update preparation.
    22. INFO: NO actual RDBMS DST update will be done by this script.
    23. INFO: If an ERROR occurs the script will EXIT sqlplus.
    24. INFO: Doing checks for known issues ...
    25. INFO: Database version is 19.0.0.0 .
    26. INFO: Database RDBMS DST version is DSTv26 .
    27. INFO: No known issues detected.
    28. INFO: Now detecting new RDBMS DST version.
    29. A prepare window has been successfully started.
    30. INFO: Newest RDBMS DST version detected is DSTv32 .
    31. INFO: Next step is checking all TSTZ data.
    32. INFO: It might take a while before any further output is seen ...
    33. A prepare window has been successfully ended.
    34. INFO: A newer RDBMS DST version than the one currently used is found.
    35. INFO: Note that NO DST update was yet done.
    36. INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
    37. INFO: Note that the utltz_upg_apply.sql script will
    38. INFO: restart the database 2 times WITHOUT any confirmation or prompt.
    39.  
    40. Session altered.
    41.  
    42. SQL>
    43.  
    44. SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_apply.sql
    45.  
    46. Session altered.
    47.  
    48. INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
    49. INFO: The database RDBMS DST version will be updated to DSTv32 .
    50. WARNING: This script will restart the database 2 times
    51. WARNING: WITHOUT asking ANY confirmation.
    52. WARNING: Hit control-c NOW if this is not intended.
    53. INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
    54. Database closed.
    55. Database dismounted.
    56. ORACLE instance shut down.
    57. ORACLE instance started.
    58.  
    59. Total System Global Area 1560277408 bytes
    60. Fixed Size                  8896928 bytes
    61. Variable Size            1191182336 bytes
    62. Database Buffers          352321536 bytes
    63. Redo Buffers                7876608 bytes
    64. Database mounted.
    65. Database opened.
    66. INFO: Starting the RDBMS DST upgrade.
    67. INFO: Upgrading all SYS owned TSTZ data.
    68. INFO: It might take time before any further output is seen ...
    69. An upgrade window has been successfully started.
    70. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
    71. Database closed.
    72. Database dismounted.
    73. ORACLE instance shut down.
    74. ORACLE instance started.
    75.  
    76. Total System Global Area 1560277408 bytes
    77. Fixed Size                  8896928 bytes
    78. Variable Size            1191182336 bytes
    79. Database Buffers          352321536 bytes
    80. Redo Buffers                7876608 bytes
    81. Database mounted.
    82. Database opened.
    83. INFO: Upgrading all non-SYS TSTZ data.
    84. INFO: It might take time before any further output is seen ...
    85. INFO: Do NOT start any application yet that uses TSTZ data!
    86. INFO: Next is a list of all upgraded tables:
    87. Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
    88. Number of failures: 0
    89. Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
    90. Number of failures: 0
    91. Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
    92. Number of failures: 0
    93. Table list: "DVSYS"."SIMULATION_LOG$"
    94. Number of failures: 0
    95. Table list: "DVSYS"."AUDIT_TRAIL$"
    96. Number of failures: 0
    97. INFO: Total failures during update of TSTZ data: 0 .
    98. An upgrade window has been successfully ended.
    99. INFO: Your new Server RDBMS DST version is DSTv32 .
    100. INFO: The RDBMS DST update is successfully finished.
    101. INFO: Make sure to exit this SQL*Plus session.
    102. INFO: Do not use it for timezone related selects.
    103.  
    104. Session altered.
    105.  
    106. SQL>
    107. SQL> SELECT version FROM v$timezone_file;
    108.  
    109.    VERSION
    110. ----------
    111.         32 <----
    112.  
    113. 1 row selected.
    114.  
    115. SQL>


    22. Run utlusts.sql

    *** Note: utluNNNs.sql is replaced by utlusts.sql in 19c version 
*** Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql
*** Run utlusts.sql as many times as you want, at any time after the upgrade is completed. 
*** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.

    1. SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT
    2.  
    3. Oracle Database Release 19 Post-Upgrade Status Tool    01-29-2020 01:41:3
    4. Database Name: CID
    5.  
    6. Component                               Current         Full     Elapsed Time
    7. Name                                    Status          Version  HH:MM:SS
    8.  
    9. Oracle Server                             VALID      19.4.0.0.0  00:18:01
    10. JServer JAVA Virtual Machine              VALID      19.4.0.0.0  00:02:29
    11. Oracle XDK                                VALID      19.4.0.0.0  00:01:12
    12. Oracle Database Java Packages             VALID      19.4.0.0.0  00:00:13
    13. OLAP Analytic Workspace                   VALID      19.4.0.0.0  00:00:19
    14. Oracle Label Security                     VALID      19.4.0.0.0  00:00:11
    15. Oracle Database Vault                     VALID      19.4.0.0.0  00:00:32
    16. Oracle Text                               VALID      19.4.0.0.0  00:00:43
    17. Oracle Workspace Manager                  VALID      19.4.0.0.0  00:00:37
    18. Oracle Real Application Clusters     OPTION OFF      19.4.0.0.0  00:00:01
    19. Oracle XML Database                       VALID      19.4.0.0.0  00:01:49
    20. Oracle Multimedia                         VALID      19.4.0.0.0  00:00:55
    21. Spatial                                   VALID      19.4.0.0.0  00:07:19
    22. Oracle OLAP API                           VALID      19.4.0.0.0  00:00:14
    23. Datapatch                                                        00:08:34
    24. Final Actions                                                    00:08:43
    25. Post Upgrade                                                     00:00:12
    26. Post Compile                                                     00:04:58
    27.  
    28. Total Upgrade Time: 00:49:07
    29.  
    30. Database time zone version is 32. It meets current release needs.
    31.  
    32. SQL>


    23. Run catuppst.sql

    /*
In 12c:
Oracle Database Upgrade Utilities
You must run this script, either through DBUA or manually, if you perform a manual upgrade.

DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.

Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.

Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time.
*/
*** Actually it will run as part of upgrade. We have reviewed catupgrd0.log and below is the output... found catuppst.sql ran and don't see errors.
/*

    1. Rem catuppst.sql
    2. Rem
    3. Rem Copyright (c) 2006, 2018, Oracle and/or its affiliates. 
    4. Rem All rights reserved.
    5. Rem
    6. Rem    NAME
    7. Rem      catuppst.sql - CATalog UPgrade PoST-upgrade actions
    8. Rem
    9. Rem    DESCRIPTION
    10. Rem      This post-upgrade script performs remaining upgrade actions that
    11. Rem      do not require that the database be open in UPGRADE mode.
    12. Rem      Automatically apply the latest PSU.
    13. Rem
    14. Rem    NOTES
    15. Rem      You must be connected AS SYSDBA to run this script.
    16. ..
    17. ..
    18. 22:19:59 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
    19. 22:19:59 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;
    20.  
    21. TIMESTAMP
    22. --------------------------------------------------------------------------------
    23. COMP_TIMESTAMP CATUPPST 	      2020-01-28 22:19:59
    24. DBUA_TIMESTAMP CATUPPST      FINISHED 2020-01-28 22:19:59
    25. DBUA_TIMESTAMP CATUPPST 	 NONE 2020-01-28 22:19:59
    */
*** If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. we need to run it manually if did have errors.
*** However can run one more time make sure no errors during execution.

    1. SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql
    2.  
    3. TIMESTAMP
    4. --------------------------------------------------------------------------------
    5. COMP_TIMESTAMP DBRESTART              2020-01-29 02:24:23
    6. DBUA_TIMESTAMP DBRESTART     FINISHED 2020-01-29 02:24:23
    7. DBUA_TIMESTAMP DBRESTART         NONE 2020-01-29 02:24:23
    8.  
    9.  
    10. TIMESTAMP
    11. --------------------------------------------------------------------------------
    12. DBUA_TIMESTAMP CATUPPST       STARTED 2020-01-29 02:24:23
    13.  
    14. TIMESTAMP
    15. --------------------------------------------------------------------------------
    16. COMP_TIMESTAMP POSTUP_BGN             2020-01-29 02:24:23
    17. DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2020-01-29 02:24:23
    18. DBUA_TIMESTAMP POSTUP_BGN        NONE 2020-01-29 02:24:23
    19.  
    20.  
    21. TIMESTAMP
    22. --------------------------------------------------------------------------------
    23. COMP_TIMESTAMP CATREQ_BGN             2020-01-29 02:24:23
    24. DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2020-01-29 02:24:23
    25. DBUA_TIMESTAMP CATREQ_BGN        NONE 2020-01-29 02:24:23
    26.  
    27. catrequtlmg: b_StatEvt     = TRUE
    28. catrequtlmg: b_SelProps    = FALSE
    29. catrequtlmg: b_UpgradeMode = FALSE
    30. catrequtlmg: b_InUtlMig    = FALSE
    31.  
    32. TIMESTAMP
    33. --------------------------------------------------------------------------------
    34. COMP_TIMESTAMP CATREQ_END             2020-01-29 02:24:23
    35. DBUA_TIMESTAMP CATREQ_END    FINISHED 2020-01-29 02:24:23
    36. DBUA_TIMESTAMP CATREQ_END        NONE 2020-01-29 02:24:23
    37.  
    38. catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
    39. catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
    40. catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
    41. catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
    42. catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
    43. catuppst: Dropping table OBJ$MIG
    44. catuppst: Dropping table USER$MIG
    45. catuppst: Dropping table COL$MIG
    46. catuppst: Dropping table CLU$MIG
    47. catuppst: Dropping table CON$MIG
    48. catuppst: Dropping table BOOTSTRAP$MIG
    49. catuppst: Dropping table TAB$MIG
    50. catuppst: Dropping table TS$MIG
    51. catuppst: Dropping table IND$MIG
    52. catuppst: Dropping table ICOL$MIG
    53. catuppst: Dropping table LOB$MIG
    54. catuppst: Dropping table COLTYPE$MIG
    55. catuppst: Dropping table SUBCOLTYPE$MIG
    56. catuppst: Dropping table NTAB$MIG
    57. catuppst: Dropping table REFCON$MIG
    58. catuppst: Dropping table OPQTYPE$MIG
    59. catuppst: Dropping table ICOLDEP$MIG
    60. catuppst: Dropping table VIEWTRCOL$MIG
    61. catuppst: Dropping table ATTRCOL$MIG
    62. catuppst: Dropping table TYPE_MISC$MIG
    63. catuppst: Dropping table LIBRARY$MIG
    64. catuppst: Dropping table ASSEMBLY$MIG
    65. catuppst: Dropping table TSQ$MIG
    66. catuppst: Dropping table FET$MIG
    67.  
    68. TIMESTAMP
    69. --------------------------------------------------------------------------------
    70. COMP_TIMESTAMP POSTUP_END             2020-01-29 02:24:24
    71. DBUA_TIMESTAMP POSTUP_END    FINISHED 2020-01-29 02:24:24
    72. DBUA_TIMESTAMP POSTUP_END        NONE 2020-01-29 02:24:24
    73.  
    74.  
    75. TIMESTAMP
    76. --------------------------------------------------------------------------------
    77. COMP_TIMESTAMP CATUPPST               2020-01-29 02:24:24
    78. DBUA_TIMESTAMP CATUPPST      FINISHED 2020-01-29 02:24:24
    79. DBUA_TIMESTAMP CATUPPST          NONE 2020-01-29 02:24:24
    80.  
    81. SQL>

    24. Re-Run postupgrade_fixups.sql

    1. SQL> @/home/oracle/CID/preupgrade/postupgrade_fixups.sql
    2. No errors.
    3. No errors.
    4.  
    5.  
    6.  
    7. No errors.
    8. No errors.
    9. Executing Oracle POST-Upgrade Fixup Script
    10.  
    11. Auto-Generated by:       Oracle Preupgrade Script
    12.                          Version: 19.0.0.0.0 Build: 1
    13. Generated on:            2020-01-28 20:45:05
    14.  
    15. For Source Database:     CID
    16. Source Database Version: 12.2.0.1.0
    17. For Upgrade to Version:  19.0.0.0.0
    18.  
    19. Preup                             Preupgrade
    20. Action                            Issue Is
    21. Number  Preupgrade Check Name     Remedied    Further DBA Action
    22. ------  ------------------------  ----------  --------------------------------
    23.     6.  old_time_zones_exist      YES         None.
    24.     7.  post_dictionary           YES         None.
    25.     8.  post_fixed_objects        NO          Informational only.
    26.                                               Further action is optional.
    27.  
    28. The fixup scripts have been run and resolved what they can. However,
    29. there are still issues originally identified by the preupgrade that
    30. have not been remedied and are still present in the database.
    31. Depending on the severity of the specific issue, and the nature of
    32. the issue itself, that could mean that your database upgrade is not
    33. fully complete.  To resolve the outstanding issues, start by reviewing
    34. the postupgrade_fixups.sql and searching it for the name of
    35. the failed CHECK NAME or Preupgrade Action Number listed above.
    36. There you will find the original corresponding diagnostic message
    37. from the preupgrade which explains in more detail what still needs
    38. to be done.
    39. SQL>

    25. Reverify INVALID OBJECTS

    1. SQL> select count(*) from dba_objects where status='INVALID';
    2.  
    3.   COUNT(*)
    4. ----------
    5.          0 <-----
    6. SQL>

    26. Drop Restore point

    1. SQL> col name for a20
    2. col GUARANTEE_FLASHBACK_DATABASE for a10
    3. col TIME for a60
    4. set lines 190
    5. select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
    6.  
    7. NAME                 GUARANTEE_ TIME
    8. -------------------- ---------- ------------------------------------------------------------
    9. PRE_UPGRADE          YES        28-JAN-20 09.05.50.000000000 PM
    10. SQL>
    11. SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/CID/flashback
    12. total 1433680
    13. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:39 o1_mf_h30dfg5q_.flb
    14. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:52 o1_mf_h30dfkos_.flb
    15. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:56 o1_mf_h30gdpcm_.flb
    16. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:12 o1_mf_h30h4zjr_.flb
    17. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30hdtcw_.flb
    18. -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30jhoxw_.flb
    19. -rw-r-----. 1 oracle oinstall 209723392 Jan 29 02:35 o1_mf_h30jc0ct_.flb
    20.  
    21. SQL>
    22. SQL> drop restore point PRE_UPGRADE;
    23.  
    24. Restore point dropped.
    25.  
    26. SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
    27.  
    28. no rows selected
    29.  
    30. SQL>
    31. SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/CID/flashback
    32. total 0  <---
    33.  
    34. SQL>

    27. Set COMPATIBALE parameter value to 19.0.0

    Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

    If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.

    1. SQL> show parameter COMPATIBLE
    2.  
    3. NAME                                 TYPE        VALUE
    4. ------------------------------------ ----------- ------------------------------
    5. compatible                           string      12.2.0
    6. noncdb_compatible                    boolean     FALSE
    7. SQL>
    8. SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
    9.  
    10. System altered.
    11.  
    12. SQL> shut immediate;
    13. Database closed.
    14. Database dismounted.
    15. ORACLE instance shut down.
    16. SQL>
    17. SQL> startup;
    18. ORACLE instance started.
    19.  
    20. Total System Global Area 1560277408 bytes
    21. Fixed Size                  8896928 bytes
    22. Variable Size            1191182336 bytes
    23. Database Buffers          352321536 bytes
    24. Redo Buffers                7876608 bytes
    25. Database mounted.
    26. Database opened.
    27. SQL>
    28. SQL> show parameter COMPATIBLE
    29.  
    30. NAME                                 TYPE        VALUE
    31. ------------------------------------ ----------- ------------------------------
    32. compatible                           string      19.0.0 <----
    33. noncdb_compatible                    boolean     FALSE
    34. SQL>

    28. Verify DBA_REGISTRY

    1. SQL> col COMP_ID for a10
    2. col COMP_NAME for a40
    3. col VERSION for a15
    4. set lines 180
    5. set pages 999
    6. select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
    7.  
    8. COMP_ID    COMP_NAME                                VERSION         STATUS
    9. ---------- ---------------------------------------- --------------- --------------------------------------------
    10. CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
    11. CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
    12. JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
    13. XML        Oracle XDK                               19.0.0.0.0      VALID
    14. CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
    15. APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
    16. RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
    17. XDB        Oracle XML Database                      19.0.0.0.0      VALID
    18. OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
    19. CONTEXT    Oracle Text                              19.0.0.0.0      VALID
    20. ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
    21. SDO        Spatial                                  19.0.0.0.0      VALID
    22. XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
    23. OLS        Oracle Label Security                    19.0.0.0.0      VALID
    24. DV         Oracle Database Vault                    19.0.0.0.0      VALID
    25.  
    26. 15 rows selected.
    27.  
    28. SQL>

    29. Add TNS Entries in 19c TNS home

    1. [oracle@rac1 admin]$ cat listener.ora
    2. # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
    3. # Generated by Oracle configuration tools.
    4.  
    5. LISTENER_CID =
    6.   (DESCRIPTION_LIST =
    7.     (DESCRIPTION =
    8.       (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524))
    9.     )
    10.   )
    11.  
    12. SID_LIST_LISTENER_CID =
    13.   (SID_LIST =
    14.     (SID_DESC =
    15.       (GLOBAL_DBNAME = CID.rajasekhar.com)
    16.       (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    17.       (SID_NAME = CID)
    18.     )
    19.   )
    20. [oracle@rac1 admin]$
    21.  
    22. [oracle@rac1 admin]$ cat tnsnames.ora
    23. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
    24. # Generated by Oracle configuration tools.
    25.  
    26. LISTENER_CID =
    27.   (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524))
    28.  
    29. CID =
    30.   (DESCRIPTION =
    31.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524))
    32.     (CONNECT_DATA =
    33.       (SERVER = DEDICATED)
    34.       (SERVICE_NAME = CID.rajasekhar.com)
    35.     )
    36.   )
    37.  
    38. [oracle@rac1 admin]$
    39.  
    40. [oracle@rac1 admin]$ cat sqlnet.ora
    41. # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
    42. # Generated by Oracle configuration tools.
    43.  
    44. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
    45.  
    46. [oracle@rac1 admin]$
    47.  
    48. [oracle@rac1 admin]$ lsnrctl start LISTENER_CID
    49.  
    50. LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:28
    51.  
    52. Copyright (c) 1991, 2019, Oracle.  All rights reserved.
    53.  
    54. Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
    55.  
    56. TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    57. System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
    58. Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_cid/alert/log.xml
    59. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1524)))
    60.  
    61. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1524)))
    62. STATUS of the LISTENER
    63. ------------------------
    64. Alias                     LISTENER_CID
    65. Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    66. Start Date                29-JAN-2020 03:16:28
    67. Uptime                    0 days 0 hr. 0 min. 10 sec
    68. Trace Level               off
    69. Security                  ON: Local OS Authentication
    70. SNMP                      OFF
    71. Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
    72. Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_cid/alert/log.xml
    73. Listening Endpoints Summary...
    74.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1524)))
    75. Services Summary...
    76. Service "CID.rajasekhar.com" has 1 instance(s).
    77.   Instance "CID", status UNKNOWN, has 1 handler(s) for this service...
    78. The command completed successfully
    79. [oracle@rac1 admin]$
    80. [oracle@rac1 admin]$ tnsping cid
    81.  
    82. TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:50
    83.  
    84. Copyright (c) 1997, 2019, Oracle.  All rights reserved.
    85.  
    86. Used parameter files:
    87. /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
    88.  
    89.  
    90. Used TNSNAMES adapter to resolve the alias
    91. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CID.rajasekhar.com)))
    92. OK (0 msec)
    93. [oracle@rac1 admin]$

    30. Password File – orapwCID

    1. *** REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    2. *** Password file orapwCID copied automatically during upgrade process. 
    3. No action taken.
    4.  
    5. [oracle@rac1 dbs]$ pwd
    6. /u01/app/oracle/product/19.0.0/dbhome_1/dbs
    7. [oracle@rac1 dbs]$ ls -ltr orapwCID
    8. -rw-r-----. 1 oracle oinstall 3584 Jan 28 22:26 orapwCID <----
    9. [oracle@rac1 dbs]$
    10. SQL> show parameter password
    11.  
    12. NAME                                 TYPE        VALUE
    13. ------------------------------------ ----------- ------------------------------
    14. remote_login_passwordfile            string      EXCLUSIVE
    15. SQL>

    31. Edit oratab

    1. [oracle@rac1 ~]$ cat /etc/oratab | grep -i CID
    2. #CID:/u01/app/oracle/product/12.2.0/dbhome_1:N
    3. CID:/u01/app/oracle/product/19.0.0/dbhome_1:N
    4. [oracle@rac1 ~]$


    32. Back Up the Database

    cat rmanbackup_19c.sh
    Sample Database Backup Script:

rmanbackup_19c.sh


    1. [oracle@rac1 AFTER_UPGRADE_19C]$ cat backup_db.log 
    2. Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 29 03:41:08 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. RMAN>
    3. connected to target database: CID (DBID=3899729854)
    4.  
    5. RMAN> 2> 3> 4> 5> 6> 7> 8>
    6. using target database control file instead of recovery catalog
    7. allocated channel: t1
    8. channel t1: SID=79 device type=DISK
    9.  
    10. allocated channel: t2
    11. channel t2: SID=82 device type=DISK
    12.  
    13. allocated channel: t3
    14. channel t3: SID=80 device type=DISK
    15.  
    16. Starting backup at 29-JAN-20
    17. channel t1: starting full datafile backup set
    18. channel t1: specifying datafile(s) in backup set
    19. input datafile file number=00001 name=/u01/app/oracle/oradata/CID/system01.dbf
    20. input datafile file number=00003 name=/u01/app/oracle/oradata/CID/sysaux01.dbf
    21. channel t1: starting piece 1 at 29-JAN-20
    22. channel t2: starting full datafile backup set
    23. channel t2: specifying datafile(s) in backup set
    24. input datafile file number=00004 name=/u01/app/oracle/oradata/CID/undotbs01.dbf
    25. input datafile file number=00007 name=/u01/app/oracle/oradata/CID/users01.dbf
    26. channel t2: starting piece 1 at 29-JAN-20
    27. channel t2: finished piece 1 at 29-JAN-20
    28. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/database_CID_02un5nen_2 tag=TAG20200129T034111 comment=NONE
    29. channel t2: backup set complete, elapsed time: 00:00:15
    30. channel t1: finished piece 1 at 29-JAN-20
    31. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/database_CID_01un5nen_1 tag=TAG20200129T034111 comment=NONE
    32. channel t1: backup set complete, elapsed time: 00:00:26
    33. Finished backup at 29-JAN-20
    34.  
    35. Starting Control File and SPFILE Autobackup at 29-JAN-20
    36. piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938097_h313ml67_.bkp comment=NONE
    37. Finished Control File and SPFILE Autobackup at 29-JAN-20
    38.  
    39. released channel: t1
    40.  
    41. released channel: t2
    42.  
    43. released channel: t3
    44.  
    45. RMAN>
    46. sql statement: alter system archive log current
    47.  
    48. RMAN> 2> 3> 4> 5> 6> 7> 8>
    49. allocated channel: a1
    50. channel a1: SID=79 device type=DISK
    51.  
    52. allocated channel: a2
    53. channel a2: SID=82 device type=DISK
    54.  
    55. allocated channel: a3
    56. channel a3: SID=80 device type=DISK
    57.  
    58. Starting backup at 29-JAN-20
    59. current log archived
    60. channel a1: starting archived log backup set
    61. channel a1: specifying archived log(s) in backup set
    62. input archived log thread=1 sequence=1 RECID=1 STAMP=1030914018
    63. input archived log thread=1 sequence=2 RECID=2 STAMP=1030916097
    64. input archived log thread=1 sequence=3 RECID=3 STAMP=1030916252
    65. input archived log thread=1 sequence=4 RECID=4 STAMP=1030916367
    66. input archived log thread=1 sequence=5 RECID=5 STAMP=1030916430
    67. input archived log thread=1 sequence=6 RECID=6 STAMP=1030916704
    68. input archived log thread=1 sequence=7 RECID=7 STAMP=1030916941
    69. input archived log thread=1 sequence=8 RECID=8 STAMP=1030917153
    70. input archived log thread=1 sequence=9 RECID=9 STAMP=1030917188
    71. channel a1: starting piece 1 at 29-JAN-20
    72. channel a2: starting archived log backup set
    73. channel a2: specifying archived log(s) in backup set
    74. input archived log thread=1 sequence=10 RECID=10 STAMP=1030917335
    75. input archived log thread=1 sequence=11 RECID=11 STAMP=1030917430
    76. input archived log thread=1 sequence=12 RECID=12 STAMP=1030917598
    77. input archived log thread=1 sequence=13 RECID=13 STAMP=1030917716
    78. input archived log thread=1 sequence=14 RECID=14 STAMP=1030917786
    79. input archived log thread=1 sequence=15 RECID=15 STAMP=1030917852
    80. input archived log thread=1 sequence=16 RECID=16 STAMP=1030917938
    81. input archived log thread=1 sequence=17 RECID=17 STAMP=1030918034
    82. input archived log thread=1 sequence=18 RECID=18 STAMP=1030918175
    83. channel a2: starting piece 1 at 29-JAN-20
    84. channel a3: starting archived log backup set
    85. channel a3: specifying archived log(s) in backup set
    86. input archived log thread=1 sequence=19 RECID=19 STAMP=1030918283
    87. input archived log thread=1 sequence=20 RECID=20 STAMP=1030918326
    88. input archived log thread=1 sequence=21 RECID=21 STAMP=1030918365
    89. input archived log thread=1 sequence=22 RECID=22 STAMP=1030918454
    90. input archived log thread=1 sequence=23 RECID=23 STAMP=1030918512
    91. input archived log thread=1 sequence=24 RECID=24 STAMP=1030918594
    92. input archived log thread=1 sequence=25 RECID=25 STAMP=1030919293
    93. input archived log thread=1 sequence=26 RECID=26 STAMP=1030927180
    94. input archived log thread=1 sequence=27 RECID=27 STAMP=1030935285
    95. input archived log thread=1 sequence=28 RECID=28 STAMP=1030938102
    96. channel a3: starting piece 1 at 29-JAN-20
    97. channel a1: finished piece 1 at 29-JAN-20
    98. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_04un5nfn_4 tag=TAG20200129T034143 comment=NONE
    99. channel a1: backup set complete, elapsed time: 00:02:16
    100. channel a1: starting archived log backup set
    101. channel a1: specifying archived log(s) in backup set
    102. input archived log thread=1 sequence=29 RECID=29 STAMP=1030938103
    103. channel a1: starting piece 1 at 29-JAN-20
    104. channel a2: finished piece 1 at 29-JAN-20
    105. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_05un5nfn_5 tag=TAG20200129T034143 comment=NONE
    106. channel a2: backup set complete, elapsed time: 00:02:18
    107. channel a3: finished piece 1 at 29-JAN-20
    108. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_06un5nfo_6 tag=TAG20200129T034143 comment=NONE
    109. channel a3: backup set complete, elapsed time: 00:02:17
    110. channel a1: finished piece 1 at 29-JAN-20
    111. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_07un5nk1_7 tag=TAG20200129T034143 comment=NONE
    112. channel a1: backup set complete, elapsed time: 00:00:03
    113. Finished backup at 29-JAN-20
    114.  
    115. Starting Control File and SPFILE Autobackup at 29-JAN-20
    116. piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938246_h313rjrj_.bkp comment=NONE
    117. Finished Control File and SPFILE Autobackup at 29-JAN-20
    118.  
    119. released channel: a1
    120.  
    121. released channel: a2
    122.  
    123. released channel: a3
    124.  
    125. RMAN> 2> 3> 4> 5>
    126. allocated channel: c1
    127. channel c1: SID=79 device type=DISK
    128.  
    129. Starting backup at 29-JAN-20
    130. channel c1: starting full datafile backup set
    131. channel c1: specifying datafile(s) in backup set
    132. including standby control file in backup set
    133. channel c1: starting piece 1 at 29-JAN-20
    134. channel c1: finished piece 1 at 29-JAN-20
    135. piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/Control_CID_09un5nkm_9 tag=TAG20200129T034422 comment=NONE
    136. channel c1: backup set complete, elapsed time: 00:00:01
    137. Finished backup at 29-JAN-20
    138.  
    139. Starting Control File and SPFILE Autobackup at 29-JAN-20
    140. piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938266_h313rv5m_.bkp comment=NONE
    141. Finished Control File and SPFILE Autobackup at 29-JAN-20
    142.  
    143. released channel: c1
    144.  
    145. RMAN>
    Recovery Manager complete.

    [oracle@rac1 AFTER_UPGRADE_19C]$
  • 相关阅读:
    【MM小贴士】从 purchase 到 payment 全流程演示
    Linux网络随笔
    一百八十八、Hive——HiveSQL查询表中的日期是星期几（亲测，附截图）
    提交代码出现error Empty block statement no-empty，代码却没报错？
    Selenium4+Python3系列（五） - 多窗口处理之句柄切换
    【深度学习】GPU使用教程
    分享一款嵌入式开源按键框架代码工程MultiButton
    9.2 【MySQL】独立表空间结构
    下载stm32f4xx标准外设库
    C#NET6基于MailKit 进行邮件发送通知
  • 原文地址：https://blog.csdn.net/kping0306/article/details/127737131