lundi 27 juin 2022

How I organized the upgrade to 19c of a hundred oracle production databases with Dataguard-TDE enabled

 I. Introduction

For one of my banking client, i have developped a shell script to upgrade databases from 12c to 19c, including dataguards & TDE.

The script helped my collegues to upgrade a hundred of production databases, and more in developpment environnement. 

The script is based on autougrade.jar oracle binary ....

I also provided a documentation to learn how to use this script ....

 

 

Always download the last version of :

AutoUpgrade Tool (Doc ID 2485457.1)

 

 

II. Upgrade Mode 

I relied on the two modes of autoupgrade.jar : ANALYZE & DEPLOY.

So my script uses also these two modes ...

 

III. ANALYZE Mode

 

On the Primary database:

 

    o   Ask the DBA if he has a Full backup, with a minimum verification of a Full backup less than 7 days old

    o    Primary Instance: All PDBs must be in OPEN and NOT RESTRICTED state: it outputs an error otherwise

    o    Check the status of the broker if Dataguard: it outputs an error if the broker is not valid

    o    Check that at least 75% of the FRA is not used : it outputs an error otherwise

    o    If Dataguard, ask the DBA to launch this script in ANALYZE mode on the Standby

    o    Analyze the database: preCheck and PostCheck recommendations

    o    A table will summarize the number of errors, warnings and Recommendations

   
 o Others checks ....

 

On the Standby bases:

      o Check the status of the broker if Dataguard: it outputs an error if the broker is not valid

      o Check that at least 75% of the FRA is not used (in case of ARCHIVELOG): it outputs an error otherwise

     o Others checks ....

 

The script checks this status ...

 the script must be lauched :

- First on the database server
- Next on the standby server (if exists)
 
 
to launch the script ...

upgrade.sh -s  MYDATABASE -m ANALYZE

 The output is :


some outputs : 

 

  autoupgrade.jar version ...
build.version 22.2.220324
build.date 2022/03/24 10:38:48 -0400
build.hash 122250c
build.hash_date 2022/03/24 10:31:13 -0400
build.supported_target_versions 12.2,18,19,21
build.type production



...


....

2022/05/25 11:57:29 - INFO -  Check_dba_registry ... : OH ==> /home/.../ora12102_2001

Oracle Application Express               4.2.5.00.08                              VALID
Oracle Label Security                    12.1.0.2.0                               VALID
Spatial                                  12.1.0.2.0                               VALID
Oracle Multimedia                        12.1.0.2.0                               VALID
Oracle Text                              12.1.0.2.0                               VALID
Oracle Workspace Manager                 12.1.0.2.0                               VALID
Oracle XML Database                      12.1.0.2.0                               VALID
Oracle Database Catalog Views            12.1.0.2.0                               VALID
Oracle Database Packages and Types       12.1.0.2.0                               VALID
JServer JAVA Virtual Machine             12.1.0.2.0                               VALID
Oracle XDK                               12.1.0.2.0                               VALID
Oracle Database Java Packages            12.1.0.2.0                               VALID
OLAP Analytic Workspace                  12.1.0.2.0                               VALID
Oracle OLAP API                          12.1.0.2.0                               VALID
Oracle Real Application Clusters         12.1.0.2.0                               OPTION OFF



2022/05/25 11:58:13 - INFO - Brocker status ....
 Status Broker SUCCESS

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL>
Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
 
MYDATABASE_PRIMARY - Primary database
   
MYDATABASE_STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

DGMGRL> DGMGRL>


 2022/05/25 11:58:18 - INFO - User choices:

    MODE                     : ANALYZE

    INSTANCE                 : MYDATABASE

    DB_UNIQUE_NAME           : MYDATABASE_PRIMARY

    ARCHIVELOG MODE          : YES

    FLASHBACK                : YES

    DB_ROLE                  : PRIMARY

    MULTITENANT              : YES

    FRA                      :        .98 %

    PRIMARY DATABASE         : MYDATABASE_PRIMARY

    STANDBY DATABASE         : MYDATABASE_STANDBY

    DATAGUARD                : OK

    VERSION                  : 12.1.0.2.0

    ORACLE_HOME_SOURCE       : /home/xxxx/ora12102_2001

    ORACLE_HOME_CIBLE        : /home/xxxx/ora191000_2101

    VERSION SCRIPT           : 1.38

    VERSION AUTOUPGRADE      : build.date 2021/11/15 11:57:54

    ORACLE NATIVE ENCRYPTION : NO

    TDE                      : YES

    TDE_WALLET_LOCATION      : /…/MYDATABASE/wallets

    PFILE AVANT MIGRATION    : /…/initMYDATABASE_avant_upgrade.ora

    CONFIG FILE              : /…/config_file_analyse_20220525_1155.cfg

    LOG DU SCRIPT            : /…/ANALYZE/upgrade_ANALYZE_20220525_1155.upg

    LOG AUTOUPGRADE.JAR      : /ANALYZE/Log_20220525_1155/upgr/MYDATABASE/[1-9]**/prechecks/

2022/05/25 11:58:18 - INFO - Validation to continue : (y/n) ?

 

 2022/05/25 11:58:31 - INFO - Once  autoupgrade.jar is launched, you can check the log  : /home/.../log/upgrade_19c/MYDATABASE/ANALYZE/Log_20220525_1155/upgr/MYDATABASE/[1-9]**/prechecks/


====================================================================


2022/05/25 11:58:31 - INFO - Start  /home/.../ora191000_2101/jdk/bin/java -jar /home/.../ora191000_2101/rdbms/admin/autoupgrade.jar -config /home/.../log/upgrade_19c/
MYDATABASE/config_file_analyse_20220525_1155.cfg -mode analyze.


 ...

 

 


 

 

 


At the end, status of the ANALYZE mode :

2 cases :

 

Case 1   : Failed status

2022/05/25 11:59:51 - INFO - Summarize ANALYZE Mode :

             MODE                                : ANALYZE

             ERRORS                              : 2

             WARNINGS                            : 18

             RECOMMEND                           : 14

 

 

Case 2  : Success status

2022/05/25 11:59:51 - INFO - Bilan ANALYZE Mode :

             MODE                                : ANALYZE

             ERRORS                              : 0

             WARNINGS                            : 18

             RECOMMEND                           : 14

 

 

IV. DEPLOY Mode 

 First : the ANALYZE mode must be in Success status.

The script checks this status ...

 the script must be lauched :

- First on the database server
- Next on the standby server (if exists)
 
to launch the script ...

upgrade.sh -s  MYDATABASE -m DEPLOY

 

 The output is :


some outputs : 

 

Minimum version of the database that can be directly upgraded to Oracle 19c.
              ==================  ====================
               Source Database     Target Database
              ==================  ====================
              11.2.0.4              19c
              ==================  ====================
              12.1.0.2              19c
              ==================  ====================
              12.2.0.1              19c
              ==================  ====================
              18.1                  19c
              ==================  ====================



Upgrade Mode:
       ============================================================================
                         A. UPGRADE
       ============================================================================

                ==================  ====================
                                Case No-Dataguard
                ==================  ====================
                1 :: ./upgrade_19c.sh -s <ORACLE_SID> -m ANALYZE
                                 Puis
                2 :: ./upgrade_19c.sh -s <ORACLE_SID> -m DEPLOY
                ==================  ====================

                ==================  ====================
                                Case Dataguard
                ==================  ====================
                1 :: ./upgrade_19c.sh -s <ORACLE_SID::PRIMARY> -m ANALYZE
                                 Puis
                2 :: ./upgrade_19c.sh -s <ORACLE_SID::STANDBY> -m ANALYZE
                                 Puis
                3 :: ./upgrade_19c.sh -s <ORACLE_SID::PRIMARY> -m DEPLOY
                                 Puis
                4 :: ./upgrade_19c.sh -s <ORACLE_SID::STANDBY> -m DEPLOY
                                 Puis
                5 :: ./upgrade_19c.sh -s <ORACLE_SID::PRIMARY> -b BROKER

     





  autoupgrade.jar version ...
build.version 22.2.220324
build.date 2022/03/24 10:38:48 -0400
build.hash 122250c
build.hash_date 2022/03/24 10:31:13 -0400
build.supported_target_versions 12.2,18,19,21
build.type production


...


....

2022/05/25 11:57:29 - INFO -  Check_dba_registry ... : OH ==> /home/.../ora12102_2001

Oracle Application Express               4.2.5.00.08                              VALID
Oracle Label Security                    12.1.0.2.0                               VALID
Spatial                                  12.1.0.2.0                               VALID
Oracle Multimedia                        12.1.0.2.0                               VALID
Oracle Text                              12.1.0.2.0                               VALID
Oracle Workspace Manager                 12.1.0.2.0                               VALID
Oracle XML Database                      12.1.0.2.0                               VALID
Oracle Database Catalog Views            12.1.0.2.0                               VALID
Oracle Database Packages and Types       12.1.0.2.0                               VALID
JServer JAVA Virtual Machine             12.1.0.2.0                               VALID
Oracle XDK                               12.1.0.2.0                               VALID
Oracle Database Java Packages            12.1.0.2.0                               VALID
OLAP Analytic Workspace                  12.1.0.2.0                               VALID
Oracle OLAP API                          12.1.0.2.0                               VALID
Oracle Real Application Clusters         12.1.0.2.0                               OPTION OFF



2022/05/25 12:14:49 - INFO -  Check Rman Backup  ...
      ARCHIVELOG                               2022/05/18 05:00:11            2022/05/18 05:00:15            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 06:00:13            2022/05/18 06:00:18            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 07:00:18            2022/05/18 07:00:22            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 08:00:14            2022/05/18 08:00:19            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 09:00:10            2022/05/18 09:00:14            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 10:00:10            2022/05/18 10:00:15            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 11:00:12            2022/05/18 11:00:18            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 12:00:12            2022/05/18 12:00:16            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 13:00:10            2022/05/18 13:00:29            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 14:00:16            2022/05/18 14:00:22            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 15:00:48            2022/05/18 15:00:51            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 16:00:11            2022/05/18 16:00:15            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 17:00:30            2022/05/18 17:00:37            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 18:00:19            2022/05/18 18:00:28            COMPLETED WITH WARNINGS
ARCHIVELOG                             2022/05/18 19:00:15            2022/05/18 19:00:19            COMPLETED WITH WARNINGS


2022/05/25 11:58:13 - INFO - Brocker status ....
 Status Broker SUCCESS

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL>
Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
 
MYDATABASE_PRIMARY - Primary database
   
MYDATABASE_STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

DGMGRL> DGMGRL>


 2022/05/25 11:58:18 - INFO - User choices:

   

    MODE                     : DEPLOY

    INSTANCE                 : MYDATABASE

    DB_UNIQUE_NAME           : MYDATABASE_PRIMARY

    ARCHIVELOG MODE          : YES

    FLASHBACK                : YES

    DB_ROLE                  : PRIMARY

    MULTITENANT              : YES

    FRA                      :        .98 %

    PRIMARY DATABASE         : MYDATABASE_PRIMARY

    STANDBY DATABASE         : MYDATABASE_STANDBY

    DATAGUARD                : OK

    VERSION                  : 12.1.0.2.0

    ORACLE_HOME_SOURCE       : /home/xxxx/ora12102_2001

    ORACLE_HOME_CIBLE        : /home/xxxx/ora191000_2101

    VERSION SCRIPT           : 1.38

    VERSION AUTOUPGRADE      : build.date 2021/11/15 11:57:54

    ORACLE NATIVE ENCRYPTION : NO

      Timezone upg by Autoupgrade : NO

    TDE                      : YES

    TDE_WALLET_LOCATION      : /…/MYDATABASE/wallets

    PFILE AVANT MIGRATION    : /…/initMYDATABASE_avant_upgrade.ora

    CONFIG FILE              : /…/config_file_deploy_20220525_1155.cfg

    LOG DU SCRIPT            : /…/ DEPLOY/upgrade_ANALYZE_20220525_1155.upg

    LOG AUTOUPGRADE.JAR      : / DEPLOY/Log_20220525_1155/upgr/MYDATABASE/[1-9]**/prechecks/

2022/05/25 11:58:18 - INFO - Validation to continue : (y/n) ?

 

 ...

 

2022/05/25 12:20:53 - INFO - Résultat : Execution of the  script @/home/.../ora12102_2001/rdbms/admin/utlrp.sql 



2022/05/25 12:20:56 - INFO -  Dbms_Stats.Gather_Fixed_Object_Stats Is Hanging While Reading X (Doc ID 2417784.1),  exec dbms_stats.lock_table_stats('SYS', 'X$DBKFDG');: OH ==> /home/logiciel/oracle/ora12102_2001

2022/05/25 12:25:14 - INFO -  Disable Dataguard : Primary = MYDATABASE_PRIMARY , Standby = MYDATABASE_STANDBY .... Oracle_Home : /home/.../ora12102_2001


 Stop_redo_apply : Standby
MYDATABASE_STANDBY. in progress...  OH ==> /home/../ora12102_2001




 2022/05/25 12:25:32 - INFO -  Disable_Triggers : OH ==> /home/.../ora12102_2001


2022/05/25 12:25:32 - INFO -  Disable_Unified_Audit : OH ==> /home/.../ora12102_2001

...



 2022/05/25 12:25:39 - INFO - Once  autoupgrade.jar is launched, you can check the log  : /home/.../log/upgrade_19c/MYDATABASE/DEPLOY/Log_20220525_1155/upgr/MYDATABASE/[1-9]**/autoupgrade_20220525.log


====================================================================


2022/05/25 12:25:39 - INFO - Start  /home/.../ora191000_2101/jdk/bin/java -jar /home/.../ora191000_2101/rdbms/admin/autoupgrade.jar -config /home/.../log/upgrade_19c/
MYDATABASE/config_file_deploy_20220525_1155.cfg -mode deploy.

2022/05/25 13:37:41 - INFO -  Enable_triggers : OH ==> /home/logiciel/oracle/ora191000_2101

 

... End of the deploy

                         Postupgrade tasks

2022/05/25 15:37:41 - INFO -  Enable_triggers : OH ==> /home/.../ora191000_2101

2022/05/25 15:37:42 - INFO -  Enable_Unified_Audit : OH ==> /home/.../ora191000_2101



2022/05/25 15:37:45 - INFO -  Restore Point was created by autoupgrade.jar ...
      AUTOUPGRADE_9212_
MYDATABASE121020,25/05/2022 12:25:55

 

 

2022/05/25 15:37:46 - INFO - TDE is configured:

the parameters wallet_root and tde_configuration will be modified to /home/logiciel/oracle/…/MYDATABASE/wallets and KEYSTORE_CONFIGURATION=FILE,  OH ==> /home/logiciel/oracle/ora19600_2001

 

  Stop-Start Instance ...

2022/05/25 15:37:47 - INFO -  Update Time Zone Root-Seed and all PDBs



2022/05/25 15:40:25 - INFO -  Check_dba_registry ...  OH ==> /home/.../ora19100_2001

 

Oracle Application Express               4.2.5.00.08                              VALID
Oracle Label Security                    19.0.0.0.0                               VALID
Spatial                                  19.0.0.0.0                               VALID
Oracle Multimedia                        19.0.0.0.0                               VALID
Oracle Text                              19.0.0.0.0                               VALID
Oracle Workspace Manager                 19.0.0.0.0                               VALID
Oracle XML Database                      19.0.0.0.0                               VALID
Oracle Database Catalog Views            19.0.0.0.0                               VALID
Oracle Database Packages and Types       19.0.0.0.0                               VALID
JServer JAVA Virtual Machine             19.0.0.0.0                               VALID
Oracle XDK                               19.0.0.0.0                               VALID
Oracle Database Java Packages            19.0.0.0.0                               VALID
OLAP Analytic Workspace                  19.0.0.0.0                               VALID
Oracle OLAP API                          19.0.0.0.0                               VALID
Oracle Real Application Clusters         19.0.0.0.0                               OPTION OFF

 

 
2022/05/25 15:40:25 - INFO -  TZ_VERSION ....
        32