ADG 升级 [11.2.0.4 -> 19.3]
目标
ORACLE ADG 环境下将 11.2.0.4 升级到 19.3
思路
停快速故障轉移功能;主库暂停归档传输;备库安装新版本软件,备库启动到mount;主库安装软件,主库升级数据库,主库启用归档传输,备库开启日志恢复。
注意
19.3新版软件安装到一个新目录下，注意copy之前的spfile、密码文件、network file、dg_broker_config文件（监听配置文件的静态监听部分需要修改），备库只升级软件版本。数据库升级是通过主库升级后通过应用主库归档完成升级和同步的。
概要步骤
1.停快速故障轉移功能
2.主库暂停归档向备库传输
3.备库在新目录下安装新版本软件
4.备库启动到mount状态
5.主库安装软件，升级数据库
6.主库启用归档向备库传输
7.检查备库、主库升级情况
8.修改 compatible 参数(主备)
9.ADG环境DB重启
10.切换到最大可用模式
具体步骤
1.停快速故障轉移功能
[oracle@mppay2 ~]$ dgmgrl sys/123456
DGMGRL> show configuration
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
DGMGRL> show configuration;
Configuration - my_dg_cfg
Protection Mode: MaxAvailability
Databases:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
2.主库暂停归档向备库传输
DGMGRL> show database mppay1
Database - mppay1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
edit configuration set protection mode as MaxPerformance;
edit database 'mppay1' set state='TRANSPORT-OFF';
edit database 'mppay1' set property LogXptMode ='ASYNC';
edit database 'mppay2' set property LogXptMode ='ASYNC';
show database 'mppay1';
#主機
SQL> show parameter dg_broker;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1MPPAY1.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2MPPAY1.dat
dg_broker_start boolean TRUE
#備機
SQL> show parameter dg_broker;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1MPPAY2.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2MPPAY2.dat
dg_broker_start boolean TRUE
3.备库在新目录下安装新版本软件
3.1 关闭数据库，监听。
[oracle@mppay2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@mppay2 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
[oracle@mppay2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> exit
[oracle@mppay2 ~]$ lsnrctl stop
3.2 在新目录下安装Oracle 19.3 软件(only oftware)
--编辑oracle环境变量，修改ORACLE_HOME为新的目录
vim /home/oracle/.bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
[oracle@mppay2 ~]$ mkdir /u01/app/oracle/product/19.3.0/dbhome_1 -p
[oracle@mppay2 oracle193]$ pwd
/home/oracle/setup/oracle193
[oracle@mppay2 oracle193]$ ls
LINUX.X64_193000_db_home.zip
unzip -d /u01/app/oracle/product/19.3.0/dbhome_1 LINUX.X64_193000_db_home.zip
cd /u01/app/oracle/product/19.3.0/dbhome_1
[oracle@mppay2 dbhome_1]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@mppay2 dbhome_1]$ ./runInstaller
--安裝時路徑選擇 新的 ORACLE_HOME
3.3 copy 配置文件
[oracle@oracle2 admin]$ cp /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/*.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/
#修改 監聽文件中 $ORACLE_HOME 文件路徑
--修正listener.ora 及 tnsnames.ora 中ORACLE_HOME
cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileMPPAYUAT.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1MPPAY2.dat /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2MPPAY2.dat /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwMPPAYUAT /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
4.备库启动到mount状态
[oracle@mppay2 ~]$ which lsnrctl
[oracle@mppay2~]$ lsnrctl start
[oracle@mppay2~]$ sqlplus / as sysdba
SQL> startup mount;
5.主库安装软件，升级数据库
5.1 备份数据库
--略
5.2 在新目录下安装Oracle 19.3 软件(only oftware)
--编辑oracle用户环境变量，修改ORACLE_HOME新目录
vim .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
--安裝時選擇新的 ORACLE_HOME ,注:监听不需要配置
5.3 Run preupgrade.jar
[oracle@mppay1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
[oracle@mppay1 dbhome_1]$ java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
------------------------------------------------------------------------------------
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-08-11T06:23:57
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: MPPAYUAT
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: No Patch Bundle applied
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 11
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Enterprise Manager Repository [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 111 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
2. Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.
The database has an Enterprise Manager Database Control repository.
Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.
3. Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
2 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
4. Backup the existing ACLs and their assignments for reference. Use the new
DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views to administer
network privileges after upgrade.
The database contains network ACLs with privileges that will be migrated
to a new format in 12c.
Network access control list (ACL) privileges in 11g will be migrated to a
new format in 12c. As part of the migration, new DBMS_NETWORK_ACL_ADMIN
interfaces and dictionary views are provided, and privileges in the
existing ACLs will be converted to the new format with new ACL names. The
old ACL names, DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views may
continue to be used but are deprecated and their use is discouraged. For
further information, refer to My Oracle Support note number 2078710.1.
5. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 3.2.1.00.10. Upgrade APEX to at least
version 18.2.0.00.12.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
6. Fix the metadata for the DBMS_JOB jobs listed below before upgrading the
database to ensure these jobs can be properly re-created, or drop them if
no longer needed. For reference, check the section "Summary of DBMS_JOB
Subprograms" in the Oracle Database PL/SQL Packages and Types Reference
documentation.
83 has invalid interval expresion: TRUNC(SYSDATE + 1) + ?14*60?/(24*60)
There are jobs created using DBMS_JOB package with invalid metadata.
These jobs cannot be re-created successfully during database upgrade
process.
Starting with Oracle Database 19c, jobs created and managed through
DBMS_JOB package in previous database versions will be re-created using
Oracle Scheduler architecture. Jobs not successfully re-created may not
function properly after upgrade.
7. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
owner of the trigger or drop and re-create the trigger with a user that
was granted directly with such. You can list those triggers using: SELECT
OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
There is one or more database triggers whose owner does not have the
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
8. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
9. Synchronize your standby databases before database upgrade.
The standby database is not currently synchronized with its associated
primary database.
To keep data in the source primary database synchronized with its
associated standby databases, all standby databases must be synchronized
before database upgrade. See My Oracle Support Note 2064281.1 for
details.
10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
Database Oracle home to remove both EXF and RUL.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
Starting with Oracle Database release 12.1, the Expression Filter (EXF)
and Database Rules Manager (RUL) features are desupported, and are
removed during the upgrade process. This step can be manually performed
before the upgrade to reduce downtime.
11. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database MPPAYUAT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
12. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 11 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
13. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
14. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
15. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database MPPAYUAT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-08-11T14:23:58
5.4 Perform Pre-Upgrade Actions
# 1) Increase the processes parameter.
sqlplus / as sysdba <<EOF
alter system set processes=1000 scope=spfile;
shutdown immediate;
startup;
exit;
EOF
# 2) Remove EM DB Console config.
cp $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin/emremove.sql
sqlplus / as sysdba <<EOF
SET ECHO ON;
SET SERVEROUTPUT ON;
@$ORACLE_HOME/rdbms/admin/emremove.sql
exit;
EOF
# 3) Remove OLAP catalog.
sqlplus / as sysdba <<EOF
@$ORACLE_HOME/olap/admin/catnoamd.sql
exit;
EOF
# 4) Ignoring the APEX upgrade. This is a junk test instance. Normally APEX would be at latest version.
# 5) Included in AUTOFIXUP.
# 6) This will be unnecessary after removal of EM repository above.
# 7) Included in AUTOFIXUP.
# Recompile invalid objects.
sqlplus / as sysdba <<EOF
@$ORACLE_HOME/rdbms/admin/utlrp.sql
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
exit;
EOF
# Run preupgrade-fixups.sql script.
sqlplus / as sysdba <<EOF
@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/preupgrade_fixups.sql
exit;
EOF
5.5 关闭数据库，监听
[oracle@mppay1 ~]$ lsnrctl stop
[oracle@mppay1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@mppay1 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
[oracle@mppay1 ~]$
sqlplus / as sysdba <<EOF
shutdown immediate;
exit;
EOF
5.6 copy 配置文件
cp $ORACLE_HOME/network/admin/*.ora $ORACLE_BASE/product/19.3.0/dbhome_1/network/admin
# Add this to $ORACLE_BASE/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
# Need to correct password versions and remove this.
cat >> $ORACLE_BASE/product/19.3.0/dbhome_1/network/admin/sqlnet.ora <<EOF
# This should be temporary while you deal with old passwords.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
EOF
cp $ORACLE_BASE/product/11.2.0.4/dbhome_1/dbs/orapwMPPAYUAT $ORACLE_BASE/product/19.3.0/dbhome_1/dbs/
cp $ORACLE_BASE/product/11.2.0.4/dbhome_1/dbs/spfileMPPAYUAT.ora $ORACLE_BASE/product/19.3.0/dbhome_1/dbs/
cp $ORACLE_BASE/product/11.2.0.4/dbhome_1/dbs/dr1MPPAY1.dat $ORACLE_BASE/product/19.3.0/dbhome_1/dbs/
cp $ORACLE_BASE/product/11.2.0.4/dbhome_1/dbs/dr2MPPAY1.dat $ORACLE_BASE/product/19.3.0/dbhome_1/dbs/
#Switch to the 19c listener (修改到新的ORACLE_HOME上)
vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
[oracle@mppay1 ~]$ which lsnrctl
/u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl
[oracle@mppay1 ~]$ lsnrctl start
5.7 升级数据库
[oracle@mppay1 dbs]$ source /home/oracle/.bash_profile
[oracle@mppay1 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@mppay1 ~]$ which sqlplus
/u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus
sqlplus / as sysdba <<EOF
startup upgrade;
exit;
EOF
5.8 運行升級
[oracle@mppay1 ~]$ $ORACLE_HOME/bin/dbupgrade
--------------------------------------------------------------------------------------------------------
Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0/dbhome_1]
/u01/app/oracle/product/19.3.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.3.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0/dbhome_1]
Analyzing file /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20200811065232]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200811065232/catupgrd_catcon_707.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200811065232/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200811065232/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 16
Database Name = MPPAY1
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243/catupgrd_catcon_707.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243]
Parallel SQL Process Count = 4
Components in [MPPAY1]
Installed [APEX CATALOG CATJAVA CATPROC JAVAVM ORDIM OWM XDB XML]
Not Installed [APS CONTEXT DV EM MGW ODM OLS RAC SDO WK XOQ]
------------------------------------------------------
Phases [0-107] Start Time:[2020_08_11 06:52:58]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [MPPAY1] Files:1 Time: 89s
*************** Catalog Core SQL ***************
Serial Phase #:1 [MPPAY1] Files:5 Time: 32s
Restart Phase #:2 [MPPAY1] Files:1 Time: 4s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [MPPAY1] Files:19 Time: 9s
Restart Phase #:4 [MPPAY1] Files:1 Time: 4s
************* Catalog Final Scripts ************
Serial Phase #:5 [MPPAY1] Files:7 Time: 13s
...
***************** Post Upgrade *****************
Serial Phase #:103 [MPPAY1] Files:1 Time: 28s
**************** Summary report ****************
Serial Phase #:104 [MPPAY1] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [MPPAY1] Files:1 Time: 2s
Serial Phase #:106 [MPPAY1] Files:1 Time: 0s
Serial Phase #:107 [MPPAY1] Files:1 Time: 28s
------------------------------------------------------
Phases [0-107] End Time:[2020_08_11 07:21:45]
------------------------------------------------------
Grand Total Time: 1728s
LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/MPPAY1/upgrade20200811065243/upg_summary.log
Grand Total Upgrade Time: [0d:0h:28m:48s]
5.9 再進行升級後的操作
sqlplus / as sysdba <<EOF
startup
exit;
EOF
# 11) Time zone file.
sqlplus / as sysdba <<EOF
-- Check current settings.
SELECT * FROM v$timezone_file;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
-- Begin upgrade to the latest version.
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
SHUTDOWN IMMEDIATE;
STARTUP;
-- Do the upgrade.
SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
-- Check new settings.
SELECT * FROM v$timezone_file;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
exit;
EOF
# 12) Ignored
# 13) AUTOFIXUP
# 14) Gather fixed object stats.
sqlplus / as sysdba <<EOF
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exit;
EOF
# AUTOFIXUP
sqlplus / as sysdba <<EOF
@/u01/app/oracle/cfgtoollogs/MPPAY1/preupgrade/postupgrade_fixups.sql
exit;
EOF
[oracle@mppay1 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter comp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
compatible string 11.2.0.4.0
db_index_compression_inheritance string NONE
inmemory_prefer_xmem_memcompress string
nls_comp string BINARY
noncdb_compatible boolean FALSE
plsql_v2_compatibility boolean FALSE
[oracle@XAG109 ~]$ cat /etc/oratab
5.10查看Broker配置
[oracle@mppay1 ~]$ which dgmgrl
/u01/app/oracle/product/19.3.0/dbhome_1/bin/dgmgrl
[oracle@mppay1 ~]$ dgmgrl sys/123456
#如果為如下disabled，則先enable
DGMGRL> show configuration
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Members:
mppay1 - Primary database
mppay2 - Physical standby database (disabled)
#ENABLE
DGMGRL> ENABLE DATABASE mppay2;
DGMGRL> show configuration;
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Members:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)
6.主库启用归档向备库传输
DGMGRL> show database mppay1;
Database - mppay1
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
DGMGRL> edit database 'mppay1' set state='TRANSPORT-ON';
DGMGRL> show database 'mppay1'
Database - mppay1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
--查看备库日志应用情况
DGMGRL> show database 'mppay2';
Database - mppay2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 294.00 KByte/s
Real Time Query: OFF
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
#1小时后
DGMGRL> show database 'mppay2';
Database - DG23
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 728.00 KByte/s
Real Time Query: OFF
Instance(s):
DG
Database Status:
SUCCESS
5.11 DGMGRL 显示主备正常 open 备库 (如上查看配置）
SQL> alter database open;
DGMGRL> show database 'mppay2';
Database - mppay2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 22 seconds (computed 0 seconds ago)
Average Apply Rate: 292.00 KByte/s
Real Time Query: ON
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Members:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 35 seconds ago)
7.检查备库、主库升级情况
SQL>
set linesize 150
set pagesize 9999
col comp_name format a40
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
Oracle XDK 19.0.0.0.0 UPGRADED
Oracle Database Java Packages 19.0.0.0.0 UPGRADED
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Workspace Manager 19.0.0.0.0 UPGRADED
Oracle XML Database 19.0.0.0.0 UPGRADED
Oracle Multimedia 19.0.0.0.0 UPGRADED
Oracle Application Express 3.2.1.00.10 INVALID
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
7936
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
0
SQL> select * from utl_recomp_errors;
no rows selected
SQL> select ACTION_TIME,ACTION,VERSION from registry$history;
ACTION_TIME ACTION VERSION
--------------------------------------------------------------------------- ------------------------------ ---------
10-AUG-20 04.57.19.675583 PM VIEW INVALIDATE
10-AUG-20 04.57.19.699783 PM UPGRADE 11.2.0.4.0
BOOTSTRAP 19
11-AUG-20 07.17.23.735536 AM RU_APPLY 19.0.0.0.0
11-AUG-20 07.20.47.682513 AM UPGRADE 19.0.0.0.0
8.修改 compatible 参数(主备)
-- 切记这个参数已修改，此次升级操作就无法回退，一定要在应用经过测试之后修改。
SQL> show parameter comp
NAME TYPE VALUE
------------------------------------
cell_offload_compaction string ADAPTIVE
compatible string 11.2.0.0.0
nls_comp string BINARY
plsql_v2_compatibility boolean FALSE
SQL> alter system set compatible='19.0.0' scope=spfile;
- ADG环境DB重启
關閉主機DB
SHUTDOWN IMMEDIATE;
關閉監聽
lsnrctl stop
關閉備機DB
SHUTDOWN IMMEDIATE;
關閉備機監聽
lsnrctl stop
啟動備機監聽
lsnrctl start
啟動備機DB
startup;
啟動主機監聽
lsnrctl start
啟動主機DB
startup;
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL> select substr(t.FILE_NAME,1,INSTR(t.FILE_NAME,'/',-1,1)) as file_path,count(1) as icount
from dba_data_files t group by substr(t.FILE_NAME,1,INSTR(t.FILE_NAME,'/',-1,1));
FILE_PATH ICOUNT
----------------------------------------------------- -----------------------------
/u01/app/oracle/oradata/MPPAYUAT/ 5
/u02/oradata/MPPAYUAT/ 110
- 切换到最大可用模式
[oracle@mppay1 ~]$ dgmgrl sys/123456
DGMGRL> show configuration;
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Members:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 11 seconds ago)
DGMGRL> edit database 'mppay1' set property LogXptMode ='SYNC';
DGMGRL> edit database 'mppay2' set property LogXptMode ='SYNC';
DGMGRL> edit configuration set protection mode as MaxAvailability;
启动数据库到mount状态出现如下问题，则如下处理
SQL> startup mount;
ORA-00845: MEMORY_TARGET not supported on this system
[root@XAG110 ~]# df -h | grep shm
tmpfs 1.5G 709M 788M 48% /dev/shm
[root@XAG110 ~]# cat /etc/fstab | grep tmpfs
[root@XAG110 ~]# mount -o remount,size=4G /dev/shm
[root@XAG110 ~]# df -h | grep shm
tmpfs 4.0G 709M 3.4G 18% /dev/shm
SQL> startup mount;
Database mounted.
网友评论