DB-hub Technology Oracle Lab – DB+ASM+Grid 12c upgrade to 19c
Oracle Lab – DB+ASM+Grid 12c upgrade to 19c
1.实验环境准备
1.1.升级路径
能够直接升级到 Oracle 19c 的数据库最小版本
源数据库 目标数据库
11.2.0.4 19c
12.1.0.2 19c
12.2.0.1 19c
18.1 19c
以下的数据库版本需要间接升级
源数据库 升级路径 目标数据库
12.1.0.1 12.1.0.2/12.2.0.1 19c
11.2.0.1/11.2.0.2/11.2.0.3 11.2.0.4 19c
11.1.0.6/11.1.0.7 11.2.0.4 19c
10.2.0.2, 10.2.0.3,
10.2.0.4, 10.2.0.5 11.2.0.4/12.1.0.2 19c
10.1.0.5 11.2.0.4/12.1.0.2 19c
9.2.0.8 或更低版本 11.2.0.4 19c
对于任何多步骤的升级，因为必须要升级两次，所以需要运行 preupgrade 脚本两次：首先，对于中间升级版本运行脚本一次，之后，对于最终升级到的版本运行脚本一次。比如，如果要升级的数据库是Oracle Database 10g，那么按照下面的步骤:
- 按照 Oracle Database Upgrade Guide 12c Release 1 (12.1) 的步骤升级 10.2.0.5 到 12.1.0.2，包括为 12.1.0.2 运行 pre-upgrade 脚本。
- 直接升级 Oracle Database 12c release 1 (12.1.0.2) 到 Oracle Database 19c。按照Oracle Database Upgrade Guide的说明以及本文档，包括为 19c 运行 preupgrade 脚本。
如果您打算使用Data Pump export/import来升级，那么这个限制就不存在了。
比如： -
如果您要升级的数据库当前是 11.2.0.2 或者 11.1.0.7，那么您必须先要升级到 Oracle Database 11g release 2 (11.2.0.4)。
- 如果您要升级的数据库当前是 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 或者 10.1.0.5，那么您先要升级到版本 11.2. 或者 12.1
- 如果您要升级的数据库当前是 9.2.0.8, 那么您必须先要升级到一个中间版本:
- 从 9.2.0.8 升级到 11.2.0.4，之后再从11.2升级到19c。
1.2.19c版本的变化
1)对 DBMS_JOB 的支持
Oracle继续支持DBMS_JOB包。但是，您必须赋予提交 DBMS_JOB jobs 的用户以 CREATE JOB 的权限。
Oracle Scheduler 替代了 DBMS_JOB package。尽管仍然支持 DBMS_JOB 以实现向后兼容，但 Oracle 强烈建议您从 DBMS_JOB 切换到 Oracle Scheduler。
- 在DBMS_JOB中的每个作业的 19c 升级期间，将使用DBMS_SCHEDULER创建相应的条目
- 旧的DBMS_JOB接口仍然有效。 但是使用它将总是在 scheduler 中创建相应的条目
- preupgrade.jar 中的升级前检查会检查是否存在不一致或其它问题。
2)不再支持 Oracle Multimedia
Oracle Database 19c 中不再支持 Oracle Multimedia 功能，此功能已从 19c 中被移除。
作为图像处理和转换的替代方案，Oracle建议您将多媒体内容存储在 SecureFiles LOB 中，并且使用第三方产品，比如 Piction。 ORDIM 组件仍然可以在 registry 看到，并处于 VALID 状态。Oracle Multimedia 的对象和 packages 也仍然保留在数据库中。但是，这些对象和 packages 已不再起作用；如果尝试使用它们，则会引发异常。 Oracle Locator 不受 Oracle 多媒体支持的影响。
3)不再支持 Oracle Streams
从 Oracle Database 19c（19.1）开始，不再支持 Oracle Streams 功能。 Oracle GoldenGate 是 Oracle 数据库的复制解决方案。
请注意，Oracle Database Advanced Queuing 并未被弃用，Oracle Database 19c 完全支持 Oracle Database Advanced Queuing。 Oracle Streams 不支持 Oracle Database 12c (12.1) 及以后版本新加入的功能，比如 multitenant architecture, LONG VARCHAR, 以及其它功能。 Oracle Streams复制功能已被GoldenGate取代。
如果使用了 Oracle Streams，则 Preupgrade check “STREAMS_SETUP” 将发出警告。 要删除 Oracle Streams，则请参阅对应版本的 Oracle documentation，Oracle Streams Concepts and Administration Guide 中的 “Removing an Oracle Streams Configuration” 部分。
1.3.虚拟机
+DATA：5GB
+FRA :10GB
主机 :60GB
内存 :4G
1.4.数据库
源数据库：（参考相关实验安装源数据库）
DB Name: OTTER
DB Unique Name: OTTER_PR, OTTER_DR
Host Name: dg3, dg4
IP Address: 192.168.1.183, 184
Database Version: 12.2.0.1
Grid 12.2.0.1
目标数据库：
DB Name: OTTER
DB Unique Name: OTTER_PR, OTTER_DR
Host Name: dg3, dg4
IP Address: 192.168.1.183, 184
Database Version: 19.3
Grid 19.3
1.5.Prepare Directory
execute on dg3 and dg4
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/19.3/db
GRID_HOME=/oracle/app/oracle/product/19.3/grid
cd /
mkdir -p /oracle/app/oracle/product/19.3/db
mkdir -p /oracle/app/oracle/product/19.3/grid
1.6.oracle profile
execute on dg3 and dg4
export GRID_HOME=$ORACLE_BASE/product/19.3/grid
1.7.download 19c grid software
execute on dg3 and dg4
Unzip the download software to the GRID_HOME
unzip /home/oracle/LINUX.X64_193000_grid_home.zip -d $GRID_HOME
rm /home/oracle/LINUX.X64_193000_grid_home.zip
2.Upgrade Grid
2.1.defer log transfer
Stop Primary log transfer to Standby.
Execute the following steps on Stanby and Primary.
Upgrade Standby first, then Primary.
alter system set log_archive_dest_state_2=defer scope=both;
2.2.shutdown database and ASM
export ORACLE_HOME=ORACLE_BASE/product/12.2.0.1/db
sqlplus / as sysdba
shutdown immediate
exit
export ORACLE_HOME=ORACLE_BASE/product/12.2.0.1/grid
export ORACLE_SID=+ASM
sqlplus / as sysasm
shutdown immediate
exit
2.3.Dry Run for Upgrade
export GRID_HOME=ORACLE_BASE/product/19.3/gridGRID_HOME/gridSetup.sh dryRunForUpgrade
检查日志，修复错误
2.4.Start Upgrade
$GRID_HOME/gridSetup.sh
2.5.Management Options
2.6.Installation Location
2.7.Root Script Execution
2.8.Prerequisite Checks
2.8.1.patching – ocmrf option
As per Metalink Doc ID 2161861.1
This enhancement to OPatch exists in 12.2.0.1.5 release and later. The option -ocmrf is used to provide OPatch the OCM responses during a silent install. Since OCM is no longer packaged with OPatch, the -ocmrf is no longer needed on the command line.
As per Metalink Doc ID 1591616.1
Note: as latest opatch doesn’t contain OCM anymore, the option “-ocmrf” is unnecessary if latest opatch is being used, refer to the following for details:
note 2161861.1 – OPatch: Behavior Changes starting in OPatch 12.2.0.1.5 and 11.2.0.3.14 releases
So this is expected behavior.
Simply use following CLI for applying the patch
# opatchauto apply <UNZIPPED_PATCH_LOCATION>/24412235
2.8.2.OPATCHAUTO-72046
[oracle@dg4:/home/oracle/28553832]opatchauto apply
OPATCHAUTO-72046: Invalid wallet parameters.
OPATCHAUTO-72046: The wallet path or wallet password provided is not valid.
OPATCHAUTO-72046: Please provide valid wallet information.
opatchauto bootstrapping failed with error code 46.
CAUSE
opatchauto command is not being run as root user. Opatchauto for Grid PSUs should always be run as root user.
refer to patch readme file:
- The utility(opatchauto) must be executed by an operating system (OS) user with root privileges, and it must be executed on each node in the cluster if the GI home or Oracle RAC database home is in non-shared storage. The utility should not be run in parallel on the cluster nodes.
For example from the README for BUG 22646084 – GRID INFRASTRUCTURE PATCH SET UPDATE 12.1.0.2.160419 (APR2016)
https://updates.oracle.com/Orion/Services/download?type=readme&aru=20043186#BGBIHIHC
Patch 22646084 – Oracle Grid Infrastructure Patch Set Update 12.1.0.2.160419 (Apr2016)
Platform: Linux x86-64, Solaris SPARC, Solaris x86-64, IBM AIX, Linux on IBM System z
Released: April 19, 2016
Section 2, “Patch Installation and Deinstallation”
Add the directory containing the opatchauto to the $PATH environment variable.
For example:
# export PATH=$PATH:<GI_HOME>/OPatch
To patch the GI home and all Oracle RAC database homes of the same version:
# opatchauto apply <UNZIPPED_PATCH_LOCATION>/22646084 -ocmrf <ocm response file> <<<============NOTE root
2.8.3.Apply patch 28553832
cd /oracle/app/oracle/product/12.2.0.1/grid/OPatch
[oracle@dg4:/oracle/app/oracle/product/12.2.0.1/grid/OPatch]sudo ./opatchauto apply /home/oracle/28553832
System initialization log file is /oracle/app/oracle/product/12.2.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2021-02-18_09-48-35PM.log.
Session log file is /oracle/app/oracle/product/12.2.0.1/grid/cfgtoollogs/opatchauto/opatchauto2021-02-18_09-48-38PM.log
The id for this session is VRFT
[init:init] Executing OPatchAutoBinaryAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Executing OPatch prereq operations to verify patch applicability on SIHA Home........
[init:init] OPatchAutoBinaryAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
[init:init] Executing SIHAPrereqAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Executing prereq operations before applying on SIHA Home........
[init:init] SIHAPrereqAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
[shutdown:shutdown] Executing SIHAShutDownAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Performing prepatch operations on SIHA Home........
Prepatch operation log file location: /oracle/app/oracle/crsdata/dg4/crsconfig/hapatch_2021-02-18_09-48-57PM.log
[shutdown:shutdown] SIHAShutDownAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
[offline:binary-patching] Executing OPatchAutoBinaryAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Start applying binary patches on SIHA Home........
[offline:binary-patching] OPatchAutoBinaryAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
[startup:startup] Executing SIHAStartupAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Performing postpatch operations on SIHA Home........
Postpatch operation log file location: /oracle/app/oracle/crsdata/dg4/crsconfig/hapatch_2021-02-18_09-49-51PM.log
[startup:startup] SIHAStartupAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
[finalize:finalize] Executing OracleHomeLSInventoryGrepAction action on home /oracle/app/oracle/product/12.2.0.1/grid
Verifying patches applied on SIHA Home.
[finalize:finalize] OracleHomeLSInventoryGrepAction action completed on home /oracle/app/oracle/product/12.2.0.1/grid successfully
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:dg4
SIHA Home:/oracle/app/oracle/product/12.2.0.1/grid
Summary:
==Following patches were SUCCESSFULLY applied:
Patch: /home/oracle/28553832/28553832
Log: /oracle/app/oracle/product/12.2.0.1/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-02-18_21-49-12PM_1.log
2.8.4.Apply patch 27006180
DO NOT need to apply this patch, it’s incluede in 28553832.
[oracle@dg4:/oracle/app/oracle/product/12.2.0.1/grid/OPatch]sudo ./opatchauto apply /home/oracle/Patch/27006180
System initialization log file is /oracle/app/oracle/product/12.2.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2021-02-05_10-43-33PM.log.
Session log file is /oracle/app/oracle/product/12.2.0.1/grid/cfgtoollogs/opatchauto/opatchauto2021-02-05_10-43-37PM.log
The id for this session is WKLK
[init:init] Executing OPatchAutoBinaryAction action on home /oracle/app/oracle/product/12.2.0.1/db
Executing OPatch prereq operations to verify patch applicability on SIDB Home........
[init:init] OPatchAutoBinaryAction action completed on home /oracle/app/oracle/product/12.2.0.1/db with failure
Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:
Patch Target : poc12d->/oracle/app/oracle/product/12.2.0.1/db Type[sidb]
Details: [
---------------------------Patching Failed---------------------------------
Command execution failed during patching in home: /oracle/app/oracle/product/12.2.0.1/db, host: poc12d.
Command failed: /oracle/app/oracle/product/12.2.0.1/db/OPatch/opatchauto apply /home/oracle/Patch/27006180 -oh /oracle/app/oracle/product/12.2.0.1/db -target_type oracle_database -binary -invPtrLoc /oracle/app/oracle/product/12.2.0.1/grid/oraInst.loc -persistresult /oracle/app/oracle/product/12.2.0.1/db/OPatch/auto/dbsessioninfo/sessionresult_analyze_poc12d_SLOTH.ser -analyze -online
Command failure output:
==Following patches FAILED in analysis for apply:
Patch: /home/oracle/Patch/27006180/27006180
Log: /oracle/app/oracle/product/12.2.0.1/db/cfgtoollogs/opatchauto/core/opatch/opatch2021-02-05_22-43-41PM_1.log
Reason: Failed during Analysis: /home/oracle/Patch/27006180/27006180 is not applicable to the oracle home /oracle/app/oracle/product/12.2.0.1/db
After fixing the cause of failure Run opatchauto resume with session id "WKLK"
]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.
opatchauto failed with error code 42
2.9.Prerequisite Checks Again
Ignore All: Physical Memory
2.10.Summary
2.11.Install Product
2.12.Finish
The response file for this session can be found at:
/oracle/app/oracle/product/19.3/grid/install/response/grid_2021-02-05_11-01-54PM.rsp
You can find the log of this install session at:
/oracle/app/oraInventory/logs/GridSetupActions2021-02-05_11-01-54PM/gridSetupActions2021-02-05_11-01-54PM.log
You can find the log of this install session at:
/oracle/app/oraInventory/logs/UpdateNodeList2021-02-05_11-01-54PM.log
You can find the log of this install session at:
/oracle/app/oraInventory/logs/UpdateNodeList2021-02-05_11-01-54PM.log
For your reference:
Case 1: “root.sh” never ran on this cluster, then as grid user, execute GRID_HOME/deinstall/deinstall
Case 2: “root.sh” already ran, then follow the step below – please keep in mind that you will need wait till each step finishes successfully before move to next one:
Step 1: As root, run “GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force” on all node, except the last one.
Step 2: As root, run “GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode” on last node. This command will zero out OCR and VD disk also.
Step 3: As grid user, run “GRID_HOME/deinstall/deinstall” on any node.
2.13.Startup databases
After Primary host upgrade finished, start up both primary and standby database.
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db
export ORACLE_SID=OTTER
sqlplus / as sysdba
startup
2.14.Start log transfer
alter system set log_archive_dest_state_2=enable scope=both;
Stanby start real time log apply:
alter database recover managed standby database using current logfile disconnect from session;
check primary and standby was sync!
3.备库升级准备
3.1.备库安装19c数据库软件
参考19c数据库安装实验
cd /oracle/home
export ORACLE_HOME=/oracle/app/oracle/product/19.3/db
unzip LINUX.X64_193000_db_home.zip -d ORACLE_HOME
rm –f LINUX.X64_193000_db_home.zipORACLE_HOME/runInstaller
3.2.拷贝旧的文件到新的目录
拷贝旧版本的spfile,Password file和network file(tnsnames.ora listener.ora sqlnet.ora)等到新的目录：
--network file（tnsnames.ora listener.ora sqlnet.ora）
cp /oracle/app/oracle/product/12.2.0.1/db/network/admin/*.ora /oracle/app/oracle/product/19.3/db/network/admin/
--PFILE
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/initOTTER.ora /oracle/app/oracle/product/19.3/db/dbs/
--SPFILE
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/spfileOTTER.ora /oracle/app/oracle/product/19.3/db/dbs/
--Password File
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/orapwOTTER /oracle/app/oracle/product/19.3/db/dbs/
3.3.暂停主库向备库传输归档日志
在主库中运行：
alter system set log_archive_dest_state_2=defer scope=both;
3.4.关闭备库和Listener
lsnrctl stop
lsnrctl stop listener_dg
sqlplus / as sysdba
shutdown immediate;
3.5.修改profile
export ORACLE_HOME=$ORACLE_BASE/product/19.3/db
关闭当前的Putty, 重新打开Putty
3.6.备库启动到mount状态
使用19c软件启动备库到mount状态：
sqlplus / as sysdba
SQL> startup mount
3.7.修改并启动listener
修正DG listener.ora 中静态监听内容（ORACLE_HOME）
SID_LIST_LISTENER=
(SID_DESC=(GLOBAL_DBNAME = OTTER)(ORACLE_HOME = /oracle/app/oracle/product/19.3/db)(SID_NAME = OTTER)
)
停止旧的Listener启动新的Listener。
3.8.启动主库向备库传输归档日志
在主库中运行：
alter system set log_archive_dest_state_2=enable scope=both;
alter system switch logfile;
select max(sequence#) from v$archived_log;
备库中运行：
alter database recover managed standby database using current logfile disconnect from session;
select max(sequence#) from v$archived_log;
检查备库和主库是sync状态。
4.主库升级准备
4.1.备份数据库
Option1:
创建 guaranteed flashback restore point
对源库做备份，冷备份或热备份都可以。
To create restore point:
create restore point before_upgrade guarantee flashback database;
refrence:
-- drop restore point
drop restorepoint before_upgrade;
-- list restore point
rman target /
RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
1791083 GUARANTEED 19-FEB-21 BEFORE_UPGRADE
-- or select vrestore_point
col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from Vrestore_point;
NAME GUARANTEE_ TIME
-------------------- ---------- -------------------------------
BEFORE_UPGRADE YES 19-FEB-21 12.26.30.000000000 AM
-- roll back to restore point
-- falshback database need to be on "mount" status
SQL> flashback database to restore point before_upgrade;
-- might need to: alter database open resetlogs;
Option2:
Full database backup:
replace BK_PATH with your backup directory.
rman target /
RMAN> run
{
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
BACKUP DATABASE FORMAT 'BK_PATH/db_%d_%T_%U.bak' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'BK_PATH/cntrl_%s_%p_%s.bak';
BACKUP ARCHIVELOG ALL FORMAT 'BK_PATH/arc_%t_%s.bak' delete all input;
BACKUP SPFILE FORMAT 'BK_PATH/spf_%d_%U.bak'
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
4.3.运行preupgrade.jar
Preupgrade 脚本说明：
#Earlier_release_Oracle_home/jdk/bin/java -jar #New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/db
cd /oracle/app/oracle/product/19.3/db/rdbms/admin
$ORACLE_HOME/jdk/bin/java -jar preupgrade.jar
==================
PREUPGRADE SUMMARY
==================
/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/preupgrade.log
/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/preupgrade_fixups.sql
/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2021-02-19T00:30:04
4.4.运行preupgrade fixups script
SQL> @/oracle/app/oracle/cfgtoollogs/OTTER_PR/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-02-19 00:29:53
For Source Database: OTTER
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- ---------------------------
1. min_recovery_area_size NO Manual fixup required.
2. parameter_min_val NO Manual fixup recommended.
3. dictionary_stats YES None.
4. tablespaces_info NO Informational only.
Further action is optional.
5. sync_standby_db NO Informational only.
Further action is optional.
6. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
4.5.失效的组件及对象
查询失效的组件及对象
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
如果发现了任何无效组件或者对象，则执行下面的操作以使数据库中的无效对象变为有效：
运行 $ORACLE_HOME/rdbms/admin/utlrp.sql 编译数据库中的无效对象, 可以多次执行utlrp.sql脚本编译无效对象。
SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
-----------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-02-07 12:06:59
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
---------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-02-07 12:07:01
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Starting validation 12:07:04
...Database user "SYS", database schema "APEX_050000", user# "88" 12:07:04
...270 packages
...263 package bodies
...465 tables
...8 functions
...16 procedures
...4 sequences
...497 triggers
...1582 indexes
...255 views
...0 libraries
...14 types
...5 type bodies
...0 operators
...0 index types
...Begin key object existence check 12:07:06
...Completed key object existence check 12:07:06
...Setting DBMS Registry 12:07:06
...Setting DBMS Registry Complete 12:07:06
...Exiting validate 12:07:06
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
4.6.SYS 和 SYSTEM 表空间
确保用户 SYS 和 SYSTEM 的默认表空间为 ‘SYSTEM’。
必须在 ‘SYSTEM’ 表空间中有足够的空间，或者将 extents 设置为 unlimited。
col USERNAME for a20
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
USERNAME DEFAULT_TABLESPACE
-------------------- ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
如果 DEFAULT_TABLESPACE 是 SYSTEM 表空间以外的其他 tablespace，请使用以下命令将用户 SYS 和 SYSTEM 的默认表空间修改为 SYSTEM：
alter user SYS default tablespace SYSTEM;
alter user SYSTEM default tablespace SYSTEM;
4.7.收集优化器统计信息以减少数据库升级停机时间
Gather DICTIONARY STATS
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
4.8.确认升级前物化视图刷新都已经完成
用下面的语句检查当前是否有物化视图正在刷新。在升级数据库前, 需要确认所有的物化视图都已经完成了刷新。
检查物化视图日志的大小，如果物化视图日志的行数非零，那么刷新物化视图。
检查 direct loader 日志以及 PMOP 日志（分区维护操作日志），如果 direct loader log 或者 PMOP 日志非空，那么刷新日志显示的物化视图。
SELECT o.name FROM sys.obj￥ o, sys.user￥ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/
4.9.检查数据库组件状态
set linesize 500
set pages 500
col COMP_NAME for a50
col STATUS for a10
col VERSION for a25
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME STATUS VERSION
-------------------------------------------------- ---------- ----------
JServer JAVA Virtual Machine VALID 12.2.0.1.0
OLAP Analytic Workspace VALID 12.2.0.1.0
Oracle Database Catalog Views VALID 12.2.0.1.0
Oracle Database Java Packages VALID 12.2.0.1.0
Oracle Database Packages and Types VALID 12.2.0.1.0
Oracle Database Vault VALID 12.2.0.1.0
Oracle Label Security VALID 12.2.0.1.0
Oracle Multimedia VALID 12.2.0.1.0
Oracle OLAP API VALID 12.2.0.1.0
Oracle Real Application Clusters OPTION OFF 12.2.0.1.0
Oracle Text VALID 12.2.0.1.0
Oracle Workspace Manager VALID 12.2.0.1.0
Oracle XDK VALID 12.2.0.1.0
Oracle XML Database VALID 12.2.0.1.0
Spatial VALID 12.2.0.1.0
15 rows selected.
4.10.检查时区版本
col VERSION for 999999999
select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
Oracle Database 19c 自带的 time zone 文件版本是 32
– Case 1 源数据库的 Timezone 版本等于或者小于 32。
如果源数据库的 Timezone 版本小于 32，那么不需要打 DST 补丁到源数据库 Home 或者目标 Home。
– Case 2 源数据库的 Timezone 版本高于 32。
如果源数据库的 Timezone 版本高于 32，升级前必须打补丁把目标 19c $ORACLE_HOME 升级到源库的 Timezone 版本一致。
4.11.确保没有数据文件需要介质恢复或处于备份的状态
执行下面的语句检查备份的状态:
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
确保没有文件需要介质恢复:
SELECT * FROM v$recover_file;
4.12.清空回收站
PURGE DBA_RECYCLEBIN;
4.13.Outstanding Distributed Transactions
- 执行下面的语句:
SQL> Select * from dba_2pc_pending;
- 如果上一步中的查询返回任何行，则执行以下语句:
SQL> select local_tran_id FROM dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;
4.14.Oracle Database Vault
如果目标Oracle数据库版本为12.2或更高版本，则可以在不禁用Oracle Database Vault的情况下进行升级。如果在源Oracle数据库发行版中启用了Oracle Database Vault，则可以在不先禁用Oracle Database Vault的情况下将Oracle数据库升级到Oracle Database 18c及更高版本。升级后，如果源Oracle数据库版本是Oracle Database 12c第1版（12.1）或更高版本，则使用与升级之前相同的设置启用Oracle Database Vault。
例如，如果源数据库是Oracle数据库版本12.1，并且在该版本中禁用了Oracle Database Vault，则在升级后它将保持禁用状态
如果源Oracle Database Database 12.1数据库在升级之前已启用Oracle Database Vault，则升级后将启用Oracle Database Vault。
如果在升级之前手动禁用Oracle Database Vault，则必须在升级后手动启用Oracle Database Vault。
4.15.Data Guard Broker配置文件
升级到Oracle Database 19c及更高版本后，要保留降级到早期版本的功能，必须备份Data Guard broker 配置文件。
在Oracle Database 19c之前的版本中，Oracle Data Guard broker 的属性在Oracle Data Guard broker 配置文件中维护，并且可以使用DGMGRL 命令进行修改。 但是，从Oracle Database 19c开始，这些数据库设置不再存储在 broker 配置文件中。 作为此更改的结果，尽管您可以继续使用DGMGRL修改这些属性，但您修改的值不再存储在Oracle Data Guard broker 配置文件中。 相反，DGMGRL命令直接修改这些Oracle Data Guard Broker 属性映射到的Oracle数据库初始化参数。
由于对属性设置的管理方式进行了此更改，因此，如果使用Oracle Data Guard broker，则Oracle建议您在开始升级之前将早期版本的Oracle Data Guard broker 配置文件导出到安全的备份位置。 如果在升级之前未备份Oracle Data Guard broker配置文件，则在升级之后，您无法降级到早期版本并保留先前为Oracle Data Guard选择的属性设置。
4.16.密码状态为 EXPIRED 的用户
在开始升级之前，请确定是否要对密码处于EXPIRED状态且其帐户处于LOCKED状态的默认Oracle数据库帐户使用密码身份验证。
在升级到 Oracle Database 19c 之后，默认的 Oracle 账号（没有设置密码并且处于 EXPIRED 和 LOCKED 状态）会被置为 NO AUTHENTICATION 状态。
由于此新功能，这些默认账号会变为 schema-only 帐户，并无法使用密码验证。此功能的好处是管理员不再需要定期修改这些Oracle默认账号的密码。此功能还可以降低未授权者使用默认密码侵入这些帐户的安全风险。
如果要在升级期间阻止将这些Oracle帐户设置为仅 schema-only 帐户，则必须在开始升级之前为该帐户设置有效的强密码，或者在升级后为这些帐户设置有效的强密码， 或者在升级前解锁帐户。
升级后，管理员还可以为仅 schema-only 启用密码身份验证。 但是，为了更好的安全性，Oracle建议您将这些帐户保留为 schema-only 账号。
4.17.Transparent Encryption Oracle 钱包
如果使用了带 Oracle 钱包的 Transparent Data Encryption (TDE)，那么拷贝 thesqlnet.ora 和 wallet 文件到新的Oracle home。在升级前需要手工拷贝 sqlnet.ora 和 wallet 文件。
- 以授权用户身份登录。
- 手工拷贝 sqlnet.ora，wallet 文件以及 ewallet.p12，到新的 Oracle home。
打开数据库 wallet
例如:
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN
4.18.密码大小写敏感
从 Oracle Database 12c release 2 (12.2) 开始，默认的基于密码验证的协议排除了大小写不敏感的 10g 版本的密码。默认的SQLNET.ORA文件中参数SQLNET.ALLOWED_LOGON_VERSION_SERVER被设置成了 12 （排他模式）。
为了安全起见，Oracle建议使用大小写敏感的密码验证。这是默认的设置。但是在升级数据库的时候可以短暂的关闭大小写敏感的密码验证。在升级后，可以再决定是否启用大小写敏感的密码验证。
在升级前，Oracle建议您检查是否新的密码验证会影响您的应用。可以做下面的检查：
- 检查是否有用户使用了 10g 大小写不敏感的密码验证方式。
- 检查是否使用了尚未安装 CPUOct2012 补丁的11.2.0.3或者更早版本的客户端，或者应用了这个补丁但尚未启用大小写敏感的密码版本。
- 确认您并未设置SEC_CASE_SENSITIVE_LOGON成FALSE。设置SEC_CASE_SENSITIVE_LOGON为FALSE就无法启用大小写敏感的密码版本了（11G和12C的密码版本）
4.19.Network Utility 包
执行 preupgrade 脚本后，检查 preupgrade 日志
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the Database Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 has dependent objects.
执行下面的语句
SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN
('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
在升级测试中，确保使用新的访问控制。在升级后确保这些包是可用的，在升级后，根据源库的使用情况赋予正确的权限。
4.20.只读表空间
以 -T 参数使用 Parallel Upgrade Utility 可以在升级时把用户表空间置为只读。 因为数据库可以读取之前版本创建的数据文件 header， 所以在升级时我们不需要做额外的操作。当升级完成后，表空间被置为读写时，文件 header 会自动被更新。如果升级失败，无法把表空间重新 online，那么检查升级日志。日志中包含把表空间重新 online 的语句。可以在数据库中或者每个pdb里手工执行来 online 表空间。
4.21.客户的计划作业以及 cron job
对于Oracle发起的 job，可以使用 DBMS_JOB, DBMS_SCHEDULER 来停掉。
对于 OS 发起的 cron jobs 需要 Unix 管理员来停掉 cron jobs。
参考:
Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB
Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification
4.22.不支持的参数
删除不支持的初始化参数并调整已弃用的初始化参数。 在新版本中，某些参数不再被支持，有些参数已经被废弃。 从启动新Oracle数据库实例的任何参数文件中删除所有不支持的参数。 不受支持的参数可能会导致新的Oracle数据库版本出错。
Pre-Upgrade Information Tool 会在“不推荐使用的参数”和“不支持的参数”部分显示任何已弃用的参数和不支持的参数。
4.23.CLUSTER_DATABASE
如果要升级的是集群数据库，那么需要在升级前修改参数 CLUSTER_DATABASE 为 FALSE 并在升级后改回 TRUE。
ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
4.24.触发器
禁用所有自定义的 before/after DDL 类型的触发器，完成升级后再启用它们。
4.25. Data security roles
在 11g 数据库上定义的 Data security roles 不能自动转换成 ORAS。 所以在升级前，需要删除所有在 11g 数据库上定义的 data security roles。升级后可以使用 Analytic Workspace Manager 19c 重新定义 data security roles。
如果从 11g 升级到 19c 之前未删除 data security roles，那么所有的 data security policies 以及 data security role 都会在 19c 上失效。
5.升级
5.1.stop listener and copy tns files
stop listener
lsnrctl stop
lsnrctl stop listener_dg
拷贝TNS 文件到新的 Oracle Home:
cp /oracle/app/oracle/product/12.2.0.1/db/network/admin/*.ora /oracle/app/oracle/product/19.3/db/network/admin
5.2.shutdown database
SQL> shutdown immediate;
5.3.copy password 文件
拷贝password 文件到新的 Oracle home:
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/orapwOTTER /oracle/app/oracle/product/19.3/db/dbs/
5.4.copy parameter files
拷贝参数文件到新的 Oracle Home:
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/initOTTER.ora /oracle/app/oracle/product/19.3/db/dbs/
cp /oracle/app/oracle/product/12.2.0.1/db/dbs/spfileOTTER.ora /oracle/app/oracle/product/19.3/db/dbs/
从参数文件中删除所有废弃的参数。在新的版本的数据库里有一些参数已经被废弃。从要启动新版本的数据库的参数文件中删除所有被废弃的参数，否则会在启动时产生错误。同时，修改那些在新版本里格式已经被改变的参数。
5.5.cluster数据库
如果要升级的是集群数据库，那么需要在升级前修改参数 CLUSTER_DATABASE 为 FALSE 。
5.6.修改profile
export ORACLE_HOME=$ORACLE_BASE/product/19.3/db
关闭当前的Putty, 重新打开Putty
5.7.修改并启动listener
修正 listener.ora 中静态监听内容（ORACLE_HOME）
SID_LIST_LISTENER=
(SID_DESC=(GLOBAL_DBNAME = OTTER)(ORACLE_HOME = /oracle/app/oracle/product/19.3/db)(SID_NAME = OTTER)
)
停止旧的Listener启动新的Listener。
lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-FEB-2021 22:17:51
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/19.3/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /oracle/app/oracle/product/19.3/db/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/dg3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 19-FEB-2021 22:17:51
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/dg3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1521)))
The listener supports no services
The command completed successfully
lsnrctl start listener_dg
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-FEB-2021 22:18:08
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/19.3/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /oracle/app/oracle/product/19.3/db/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/dg3/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1525)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias listener_dg
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 19-FEB-2021 22:18:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/dg3/listener_dg/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1525)))
Services Summary...
Service "OTTER" has 1 instance(s).
Instance "OTTER", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
tnsping OTTER_DR
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-FEB-2021 22:19:38
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/19.3/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg4)(PORT = 1525))) (CONNECT_DATA = (SERVICE_NAME = OTTER)))
OK (10 msec)
C:\>sqlplus peacock/peacock@OTTER_PR
SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 8 21:19:10 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Feb 08 2021 16:01:12 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> select count(*) from obj;
COUNT(*)
----------
7
SQL> select * from test;
ID NAME
---------- ----------
10 Tom Cruese
20 MikeJordan
SQL>
5.8.Startup DB to upgrade mode
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 19 00:54:53 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 1040187392 bytes
Database Buffers 603979776 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from vdatabase,vinstance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
OTTER READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
exit
5.9.Run dbupgrade
cd /home/oracle
ls -l $ORACLE_HOME/bin/dbupgrade
-rwxr-x--- 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
nohup $ORACLE_HOME/bin/dbupgrade &
[1] 57079
[oracle@poc12d:/oracle/app/oracle/product/19.3/db/bin]jobs -l
[1]+ 57079 Running nohup ./dbupgrade &
ps -ef | grep -i catctl.pl | grep -v grep
oracle 57085 57079 1 00:56 pts/0 00:00:00 /oracle/app/oracle/product/19.3/db/perl/bin/perl -I/oracle/app/oracle/product/19.3/db/perl/lib /oracle/app/oracle/product/19.3/db/rdbms/admin/catctl.pl /oracle/app/oracle/product/19.3/db/rdbms/admin/catupgrd.sql
cd $ORACLE_HOME/cfgtoollogs/OTTER_PR
ls
upgrade20210219005710
cd upgrade20210219005710
ls -ltr
total 5072
-rw------- 1 oracle oinstall 546 Feb 19 00:57 catupgrd_catcon_57085.lst
-rw-r--r-- 1 oracle asmadmin 4526 Feb 19 00:57 catupgrd_20210219005719_57085.ora
-rw-r--r-- 1 oracle oinstall 656 Feb 19 00:58 catupgrd_catcon_kill_sess_57085_ALL.sql
-rw------- 1 oracle oinstall 259161 Feb 19 00:58 catupgrd3.log
-rw------- 1 oracle oinstall 405518 Feb 19 00:58 catupgrd1.log
-rw------- 1 oracle oinstall 200483 Feb 19 00:58 catupgrd2.log
-rw------- 1 oracle oinstall 3138950 Feb 19 00:58 catupgrd0.log
tail -f catupgrd3.log
cd /home/oracle
tail -f nohub.out
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [SLOTH] Files:1 Time: 2s
Serial Phase #:106 [SLOTH] Files:1 Time: 0s
Serial Phase #:107 [SLOTH] Files:1 Time: 68s
------------------------------------------------------
Phases [0-107] End Time:[2021_02_08 16:56:59]
------------------------------------------------------
Grand Total Time: 1425s
LOG FILES: (/oracle/app/oracle/product/19.3/db/cfgtoollogs/SLOTH/upgrade20210208163259/catupgrd*.log)
Upgrade Summary Report Located in:
/oracle/app/oracle/product/19.3/db/cfgtoollogs/SLOTH/upgrade20210208163259/upg_summary.log
Grand Total Upgrade Time: [0d:0h:23m:45s]
5.10.问题
dbupgrade hung:
Restart Phase #:47 [OTTER_PR] Files:1 Time: 2s
************* Final Catproc scripts ************
Serial Phase #:48 [OTTER_PR] Files:1 Time: 10s
Restart Phase #:49 [OTTER_PR] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [OTTER_PR] Files:1
SQL> set linesize 500
SQL> col EVENT for a40
select sid, serial#, sql_id, event,p1,p2,p3 from vsession
where status='ACTIVE' and type='USER' and sid not in
(select sid from vmystat);
SID SERIAL# SQL_ID EVENT P1 P2 P3
---------- ---------- ------------- ---------------------------------------- ---------- ---------- ----------
141 41525 812k63c3m70pu log file switch (archiving needed) 0 0 0
alter system kill session '141,41525' immediate;
--cause:
col name for a15
SQL> SELECT name, SPACE_LIMIT/1024/1024/1024 AS SPACE, SPACE_USED/1024/1024/1024 AS USED, SPACE_RECLAIMABLE/1024/1024/1024 AS RECLAIMABLE FRO
NAME SPACE USED RECLAIMABLE
--------------- ---------- ---------- -----------
+FRA 2.9296875 2.88964844 0
db_recovery_size filled up.
alter system set db_flashback_retention_target=240;
alter system set db_recovery_file_dest_size=4500m;
6.升级后步骤
6.1.bounce database
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 8 20:04:11 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from vdatabase,vinstance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
SLOTH READ WRITE NO 19.0.0.0.0 OPEN
6.2.检查数据库组件状态
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME STATUS VERSION
-------------------------------------------------- ---------- -----------
JServer JAVA Virtual Machine UPGRADED 19.0.0.0.0
Oracle Application Express VALID 5.0.4.00.1
Oracle Database Catalog Views UPGRADED 19.0.0.0.0
Oracle Database Java Packages UPGRADED 19.0.0.0.0
Oracle Database Packages and Types UPGRADED 19.0.0.0.0
Oracle Real Application Clusters OPTION OFF 19.0.0.0.0
Oracle Text UPGRADED 19.0.0.0.0
Oracle Workspace Manager UPGRADED 19.0.0.0.0
Oracle XDK UPGRADED 19.0.0.0.0
Oracle XML Database UPGRADED 19.0.0.0.0
10 rows selected.
6.3.compile invalid objects
运行 utlrp.sql 以编译无效对象
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2407
SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');
COUNT(*)
----------
698
SQL> @/oracle/app/oracle/product/19.3/db/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-02-08 20:13:10
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-02-08 20:16:48
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
6.4.Run postupgrade_fixups.sql
SQL> @/oracle/app/oracle/cfgtoollogs/SLOTH/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-02-07 12:01:04
For Source Database: SLOTH
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
4. old_time_zones_exist NO Manual fixup recommended.
5. dir_symlinks YES None.
6. post_dictionary YES None.
7. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
6.5.Run Post-Upgrade Status Tool
这个脚本会确认是否所有的问题都已经被解决。
Note: utluNNNs.sql is replaced by utlusts.sql in 19c version
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.
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Enter value for 1:
Oracle Database Release 19 Post-Upgrade Status Tool 02-08-2021 20:21:1
Database Name: SLOTH
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:13:20
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:00:56
Oracle XDK VALID 19.3.0.0.0 00:00:53
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:11
Oracle Text VALID 19.3.0.0.0 00:00:29
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:32
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:01:35
Datapatch 00:00:58
Final Actions 00:01:05
Post Upgrade 00:00:11
Post Compile 00:03:38
Total Upgrade Time: 00:25:00
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.
6.6.CLUSTER_DATABASE
对于 Oracle RAC 环境，把 CLUSTER_DATABASE 改回 TRUE，之后运行 19c home 的 srvctl 命令来升级数据库配置。比如:
ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
$ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME
6.7.Upgrade Time Zone
如果 Pre-Upgrade Information Tool 要求我们在升级数据库后升级 time zone 文件，那么使用 DBMS_DST PL/SQL package 来升级 RDBMS DST（timezone）版本
关于最新的 latest DST patch 请参考 Note 412160.1.
以下脚本随Oracle Database 18c及以上版本一起提供:
- $ORACLE_HOME/rdbms/admin/utltz_countstats.sql
脚本使用统计信息提供在数据库中TIMESTAMP WITH TIME ZONE数据的数量。 无需重启。 - ORACLE_HOME/rdbms/admin/utltz_countstar.sql
脚本使用COUNT（*）查询每个具有TSTZ列的表来计算数据库中的TIMESTAMP WITH TIME ZONE数据的数量。 使用DBMS_DST包或utlz_upg_check.sql和utlz_upg_apply.sql脚本时，此脚本非常有用。 - ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
时区升级检查脚本 - ?/rdbms/admin/utltz_upg_apply.sql
时区应用脚本。 警告：此脚本将重新启动数据库并调整时区数据。
[oracle@poc12d:/oracle/app/oracle/product/19.3/db/bin]cd $ORACLE_HOME/rdbms/admin
[oracle@poc12d:/oracle/app/oracle/product/19.3/db/rdbms/admin]ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql
-rw-r--r-- 1 oracle oinstall 8317 Feb 24 2017 utltz_countstats.sql
-rw-r--r-- 1 oracle oinstall 7423 Feb 24 2017 utltz_countstar.sql
-rw-r--r-- 1 oracle oinstall 33684 Sep 8 2017 utltz_upg_check.sql
-rw-r--r-- 1 oracle oinstall 21526 Sep 8 2017 utltz_upg_apply.sql
SQL> SELECT version FROM vtimezone_file;
VERSION
----------
26
SQL> @/oracle/app/oracle/product/19.3/db/rdbms/admin/utltz_upg_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL> @/oracle/app/oracle/product/19.3/db/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL>
SQL> SELECT version FROM vtimezone_file;
VERSION
----------
32
1 row selected.
SQL>
6.8.Run Post-Upgrade Status Tool again
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
6.9.Run catuppst.sql
Please refer to: Database Upgrade Guid Release 12.2
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.
SQL> @/oracle/app/oracle/product/19.3/db/rdbms/admin/catuppst.sql
Session altered.
Session altered.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
System altered.
PL/SQL procedure successfully completed.
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2021-02-08 20:50:59
DBUA_TIMESTAMP DBRESTART FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP DBRESTART NONE 2021-02-08 20:50:59
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2021-02-08 20:50:59
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2021-02-08 20:50:59
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP POSTUP_BGN NONE 2021-02-08 20:50:59
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2021-02-08 20:50:59
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP CATREQ_BGN NONE 2021-02-08 20:50:59
1 row selected.
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2021-02-08 20:50:59
DBUA_TIMESTAMP CATREQ_END FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP CATREQ_END NONE 2021-02-08 20:50:59
1 row selected.
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping table OBJMIG
catuppst: Dropping table USERMIG
catuppst: Dropping table COLMIG
catuppst: Dropping table CLUMIG
catuppst: Dropping table CONMIG
catuppst: Dropping table BOOTSTRAPMIG
catuppst: Dropping table TABMIG
catuppst: Dropping table TSMIG
catuppst: Dropping table INDMIG
catuppst: Dropping table ICOLMIG
catuppst: Dropping table LOBMIG
catuppst: Dropping table COLTYPEMIG
catuppst: Dropping table SUBCOLTYPEMIG
catuppst: Dropping table NTABMIG
catuppst: Dropping table REFCONMIG
catuppst: Dropping table OPQTYPEMIG
catuppst: Dropping table ICOLDEPMIG
catuppst: Dropping table VIEWTRCOLMIG
catuppst: Dropping table ATTRCOLMIG
catuppst: Dropping table TYPE_MISCMIG
catuppst: Dropping table LIBRARYMIG
catuppst: Dropping table ASSEMBLYMIG
catuppst: Dropping table TSQMIG
catuppst: Dropping table FETMIG
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2021-02-08 20:50:59
DBUA_TIMESTAMP POSTUP_END FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP POSTUP_END NONE 2021-02-08 20:50:59
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2021-02-08 20:50:59
DBUA_TIMESTAMP CATUPPST FINISHED 2021-02-08 20:50:59
DBUA_TIMESTAMP CATUPPST NONE 2021-02-08 20:50:59
1 row selected.
Session altered.
SQL>
6.10.Run postupgrade_fixups.sql again
SQL> @/oracle/app/oracle/cfgtoollogs/SLOTH/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
1 row selected.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-02-07 12:01:04
For Source Database: SLOTH
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
4. old_time_zones_exist YES None.
5. dir_symlinks YES None.
6. post_dictionary YES None.
7. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
6.11.check invalid objects again
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
1 row selected.
6.12.drop restore point
col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE_ TIME
-------------------- ---------- -----------------------------------
BEFORE_UPGRADE YES 19-FEB-21 12.26.30.000000000 AM
1 row selected.
SQL> drop restore point BEFORE_UPGRADE;
Restore point dropped.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
no rows selecte
6.13.Verify DBA_REGISTRY
col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- -------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
APEX Oracle Application Express 5.0.4.00.12 VALID
10 rows selected.
6.14.Password File
Password file orapw$SID automatically copied during upgrade process.
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
6.15.oratab
change on Primary and Standby:
SLOTH:/oracle/app/oracle/product/12.2.0.1/db:N
to:
SLOTH:/oracle/app/oracle/product/19.3/db:N
6.16.Upgrade Recovery Catalog
如果使用的recovery catalog版本低于rman客户端的版本，我们必须升级它。可以通过命令 UPGRADE CATALOG 来升级 Recovery catalog。
关于具体的步骤信息，请参照Oracle文档中的 “Upgrading the Recovery Catalog” 部分
6.17.fully sync standby database
You might face ORA-39700 while opening your standby database. The reason of this error would be that you tried to STARTUP OPEN the standby database from the newer version RDBMS home. To avoid this error, DO NOT open the standby database until it is fully in sync with the primary database. To do this, first you would need to open the database in mount mode and then enable managed recovery. After enabling the managed recovery, archived logs should start applying on the database and eventually database should have the same version as primary. Once completely synchronized, now you can open your standby database and use it as active dataguard.
问题：MRP0不启动
Stop the managed recovery and start the manual recovery
SQL>recover managed standby database cancel;
SQL>recover automatic standby database;
bounce database, startup to open read only mode.
start real time apply again:
alter database recover managed standby database using current logfile disconnect from session
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
run this on both primary and standby:
SQL> select count(*) from dba_objects;
COUNT(*)
----------
72419
6.18.COMPATIBALE parameter
坑：stanby必须和primary一致，log才能应用，否则：
*** 2021-02-19T22:03:06.777766-05:00
krsu_wallet_pki_connect: OCISessionBegin failed. Error -1
krsu_dump_oci_emsg: Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
可以在stanby完全同步后再改此参数。
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.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ---------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ---------
compatible string 19.0.0
noncdb_compatible boolean FALSE