Oracle 19c自动升级实用程序（第3部分）

发布于：2021-02-08 15:55:20

0

346

0

Oracle Oracle 19c 自动升级 数据库

自动升级功能自动执行典型升级过程的每个步骤，并使我们能够在尽可能少的人为干预下执行升级。

AutoUpgrade基于一个配置文件，其中包含我们要升级的数据库的详细信息，我们还有一个AutoUpgrade作业管理器，它根据执行AutoUpgrade的阶段或模式来执行各种与升级相关的作业。

默认情况下，autoupgrade实用程序使用的autoupgrade.jar文件是Oracle 19c软件的一部分，位于$ ORACLE_HOME / rdbms / admin中–请注意，该文件在较低版本中不存在。我们必须从MOS注释（2485457.1）下载此文件，并且可以使用此实用程序自动执行从12c R2到18c的升级-不仅限于Oracle 19c。

在AutoUpgrade 19c第1部分中，我们以分析模式执行AutoUpgrade，对数据库执行只读检查，并返回一个报告，其中突出显示了数据库升级中可能出现的任何警告或潜在错误，并提供了一些建议。

在AutoUpgrade 19c第2部分中，我们在FIXUP模式下执行了AutoUpgrade，它不仅执行在Analyze模式下执行的检查，而且在完成这些检查之后，然后，在升级开始之前，AutoUpgrade执行修复早期版本源数据库所需的所有自动修复任务。

AutoUpgradeDEPLOY处理模式执行数据库的实际升级，以及执行第1部分和第2部分讨论的分析和修复阶段中执行的所有步骤。基本上在部署模式下，AutoUpgrade在数据库上运行从升级前源数据库分析到升级后检查的所有升级任务。

虽然我们之前已对两个数据库DB1和DB2执行了“分析”和“修复”阶段，但我们仅对其中一个数据库（DB1）进行了升级–因此，我们需要相应地修改/tmp/config.txt文件。

在部署模式下执行自动升级

[oracle@host02 prechecks]$ /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config.txt -mode deploy
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands

执行预检查

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|PRECHECKS|PREPARING|RUNNING|19/06/12 12:10|     N/A|12:10:04|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

运行修复作业

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|PREFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:10:41|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

排空阶段–复制钱包（如果存在）并关闭数据库

upg> lsj
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
| 100|    DB1|DRAIN|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:10:57|       |
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
Total jobs 1

启动数据库升级

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:11:17|Running|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
Total jobs 1

检查作业的状态

upg> status
---------------- Config -------------------
User configuration file    [/tmp/config.txt]
General logs location      [/u02/app/oracle/autoupgrade/new        # Top level logging directory (Required)/cfgtoollogs/upgrade/auto]
Mode                       [DEPLOY]
DB upg fatal errors        ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time          [1440] minutes
DB restore abort time      [120] minutes
DB drop GRP abort time     [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed         [1]
Total CDB being processed             [0]
Jobs finished successfully            [0]
Jobs finished/aborted                 [0]
jobs in progress                      [1]
------------ Resources ----------------
Threads in use                        [20]
JVM used memory                       [32] MB
CPU in use                            [13%]
Processes in use                      [18]

upg> tasks
+---+------------------+-------------+
| ID|              NAME|         Job#|
+---+------------------+-------------+
|  1|              main|      WAITING|
| 20|          jobs_mon|      WAITING|
| 21|           console|     RUNNABLE|
| 22|      queue_reader|      WAITING|
| 23|             cmd-0|      WAITING|
| 29|     job_manager-0|      WAITING|
| 31|        event_loop|TIMED_WAITING|
| 32|        bqueue-100|      WAITING|
|344|         exec_loop|      WAITING|
|350|       monitor_db1|TIMED_WAITING|
|351|        catctl_db1|      WAITING|
|352| abort_monitor_db1|TIMED_WAITING|
|353|        async_read|     RUNNABLE|
+---+------------------+-------------+

检查数据库升级的状态–注意%Upgraded值正在更改

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:11:44|8%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:19:38|19%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

升级现已完成–重新编译无效对象

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:51:24|90%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:51:24|90%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

运行升级后修复作业，如升级时区DST和重新启动数据库

upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
| 100|    DB1|POSTFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|13:00:40|Remaining 1/3|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1

upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|POSTFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|13:03:01|Loading DB info|
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
| 100|    DB1|POSTUPGRADE|EXECUTING|FINISHED|19/06/12 12:10|     N/A|13:03:58|RESTARTING_DB|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
Total jobs 1

upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 FOR DB1

[oracle@host02 prechecks]$


注意数据库db1的oratab文件条目已更新

[oracle@host02 2019_06_12]$ cat /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
db1:/u01/app/oracle/product/19.3.0/dbhome_1:N
db2:/u02/app/oracle/product/12.2.0/dbhome_1:N
[oracle@host02 2019_06_12]$

注意每个阶段的各种升级目录和日志文件

[oracle@host02 100]$ ls -l
total 744
-rwx------ 1 oracle oinstall 716018 Jun 12 13:04 autoupgrade_20190612.log
-rwx------ 1 oracle oinstall   8389 Jun 12 13:03 autoupgrade_20190612_user.log
-rwx------ 1 oracle oinstall      0 Jun 12 12:10 autoupgrade_err.log
drwx------ 2 oracle oinstall   4096 Jun 12 12:55 dbupgrade
drwx------ 2 oracle oinstall   4096 Jun 12 12:11 drain
drwx------ 2 oracle oinstall   4096 Jun 12 12:57 postchecks
drwx------ 2 oracle oinstall   4096 Jun 12 13:03 postfixups
drwx------ 2 oracle oinstall   4096 Jun 12 13:03 postupgrade
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 prechecks
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 prefixups
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 preupgrade
[oracle@host02 100]$

查看升级摘要报告

[oracle@host02 dbupgrade]$ pwd
/u02/app/oracle/autoupgrade/db1/db1/100/dbupgrade
[oracle@host02 dbupgrade]$ ls -l
total 71464
-rwx------ 1 oracle oinstall    12129 Jun 12 12:55 autoupgrade20190612121003db1.log
-rwx------ 1 oracle oinstall 49061634 Jun 12 12:55 catupgrd20190612121003db10.log
-rwx------ 1 oracle oinstall  8551109 Jun 12 12:45 catupgrd20190612121003db11.log
-rwx------ 1 oracle oinstall  6557311 Jun 12 12:45 catupgrd20190612121003db12.log
-rwx------ 1 oracle oinstall  8833106 Jun 12 12:45 catupgrd20190612121003db13.log
-rwx------ 1 oracle oinstall      532 Jun 12 12:11 catupgrd20190612121003db1_catcon_18422.lst
-rwx------ 1 oracle oinstall        0 Jun 12 12:40 catupgrd20190612121003db1_datapatch_upgrade.err
-rwx------ 1 oracle oinstall     1303 Jun 12 12:43 catupgrd20190612121003db1_datapatch_upgrade.log
-rwx------ 1 oracle oinstall    38515 Jun 12 12:46 catupgrd20190612121003db1_stderr.log
-rwx------ 1 oracle oinstall    31341 Jun 12 12:55 db1_autocompile20190612121003db10.log
-rwx------ 1 oracle oinstall      546 Jun 12 12:47 db1_autocompile20190612121003db1_catcon_25152.lst
-rwx------ 1 oracle oinstall     2070 Jun 12 12:55 db1_autocompile20190612121003db1_stderr.log
-rwx------ 1 oracle oinstall     4187 Jun 12 12:43 during_upgrade_pfile_catctl.ora
-rwx------ 1 oracle oinstall    32574 Jun 12 12:11 phase.log
-rwx------ 1 oracle oinstall     1728 Jun 12 12:55 upg_summary.log
-rwx------ 1 oracle oinstall       46 Jun 12 12:55 upg_summary_report.log
-rwx------ 1 oracle oinstall      423 Jun 12 12:55 upg_summary_report.pl
[oracle@host02 dbupgrade]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    06-12-2019 12:45:3
Database Name: DB1

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:14:19
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:01:17
Oracle XDK                             UPGRADED      19.3.0.0.0  00:01:03
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:13
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:16
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:06
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:19
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:35
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:00:40
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:01:40
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:00:45
Spatial                                UPGRADED      19.3.0.0.0  00:06:02
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:13
Datapatch                                                        00:03:50
Final Actions                                                    00:03:57
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:32:15

Database time zone version is 26. 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:0h:44m:25s]
[oracle@host02 dbupgrade]$

查看升级后修复日志文件

cat postfixups_db1.log 
...
...

temp/sqlsessend.sql][DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.333 INFO Executing SQL [@/u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql
] in [DB1, container:DB1] - ExecuteSql.sendSqlCmdToSqlPlus 
2019-06-12 13:02:57.383 INFO End Reading process Output Stream - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO Begin Closing File /u02/app/oracle/autoupgrade/new/db1/db1/temp/DB1/TimeZone/DB1.log - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO End Closing File /u02/app/oracle/autoupgrade/new/db1/db1/temp/DB1/TimeZone/DB1.log - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO Finished - ReadInputStream.run 
2019-06-12 13:02:57.384 INFO Complete [/u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql][DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO Looking for error in log file after /u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql execution on [DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO Closing sqlplus with exitValue 0 [DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO The Timezone upgrade has finished for [DB1] - old_time_zones_exist.NonCDBTimeZoneUpg 
2019-06-12 13:02:57.384 INFO Finished - old_time_zones_exist.NonCDBTimeZoneUpg 
2019-06-12 13:02:57.384 INFO Finished FIXUP [OLD_TIME_ZONES_EXIST][DB1][SUCCESSFUL] - DBUpgradeInspector$FixUpTrigger.executeFixUp

查看升级后日志文件

[oracle@host02 postupgrade]$ cat postupgrade.log 
2019-06-12 13:03:58.001 INFO Deserializing /u02/app/oracle/autoupgrade/new        # Top level logging directory (Required)/cfgtoollogs/upgrade/auto/config_files/dbstate_DB1 file from {1} - DBState.deserialize 
2019-06-12 13:03:58.031 INFO 
DataBase Name:db1
Sid Name     :db1
Source Home  :/u02/app/oracle/product/12.2.0/dbhome_1
Target Home  :/u01/app/oracle/product/19.3.0/dbhome_1
 - PostActions.2019-06-12 13:03:58.031 INFO Executing PostUpgrade - AutoUpgPostActions.runPostActions 
2019-06-12 13:03:58.039 INFO Starting - PostActions.runPostActions 
2019-06-12 13:03:58.042 INFO Starting - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.043 INFO Starting - Oratab.updateOraTab 
2019-06-12 13:03:58.043 INFO Begin Updating oratab /etc/oratab - Oratab.updateOraTab 
2019-06-12 13:03:58.045 INFO Updating oratab file /etc/oratab completed with success - Oratab.updateOraTab 
2019-06-12 13:03:58.053 INFO Starting - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.053 INFO Begin Copying network files - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.084 INFO File /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.084 INFO Copying/merging file listener.ora ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO IFILE /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora not found, skipping... - NetworkFiles.processIFile 
2019-06-12 13:03:58.085 INFO File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora.tmp does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO Copying/merging file /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora.tmp ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO File /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO Copying/merging file sqlnet.ora ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO End Copying network files - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.085 INFO Finished - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.096 INFO Starting - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.096 INFO Begin Copying Password File - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.100 INFO Copying password file from /u02/app/oracle/product/12.2.0/dbhome_1/dbs/orapwdb1 to /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwdb1 - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO Copying password file completed with success - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO End Copying Password File - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO Finished - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.110 INFO Resetting DB state - DBState.resetStateOnTarget 
2019-06-12 13:03:58.110 INFO Resetting the CONCURRENT DBMS_STATS preference for DB1 - ConcurrentStat.resetConcurrentValue 
2019-06-12 13:03:58.110 INFO The CONCURRENT DBMS_STATS preference for DB1 was already set to OFF. - ConcurrentStat.resetConcurrentValue 
2019-06-12 13:03:58.112 INFO Return status is SUCCESS - PostActions.writeStatusLog 
2019-06-12 13:03:58.121 INFO Update of oratab [DB1]
 [/etc/oratab] [SUCCESS] [None]

Network Files [DB1]
 [/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora] [SUCCESS] [None]
 [/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora] [SUCCESS] [None]
 [/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora] [SUCCESS] [None]

Copy of password file [DB1]
 [/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwdb1] [SUCCESS] [None]

Database State
 Resetting the database's state: [SUCCESS] [None]

 - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.122 INFO Finished - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.122 INFO Finished - PostActions.runPostActions 
2019-06-12 13:03:58.122 INFO No postupgrade user action defined - AutoUpgPostActions.runPostActions 
[oracle@host02 postupgrade]$

请注意由Autoupgrade自动创建的保证还原点

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME,PRESERVED from v$restore_point;

GUA
---
NAME
--------------------------------------------------------------------------------
TIME    PRE
--------------------------------------------------------------------------- ---
YES
AUTOUPGRADE_221145114461854_DB1
12-JUN-19 12.10.29.000000000 PM    YES

作者介绍

城南花已开

博客

31

获赞

1

浏览

11300

热门博客推荐