問題描述
填寫問題的基礎信息。
| 系統名稱 | Oracle11.2.0.4遷移升級Oracle19.3 |
| IP地址 | |
| 操作系統 | Centos7.5 |
| 數據庫 | Oracle11.2.0.4遷移升級Oracle19.3 |
癥狀表現
問題的癥狀表現如下
需要將單機的Oracle11.2.0.4環境升級到Oracle19.3.0RAC環境，采用遷移升級的方式：
| 數據庫版本 | IP地址 | |
| 源端 | Oracle11.2.0.4 單機 | 192.168.123.10 |
| 目標 | Oracle19.3.0 RAC | 192.168.123.3/4 |
處理過程
處理過程推薦按照時間以列表形式，將處理過程時間點，處理內容。
1、下載最近的Autoupgrade工具，AutoUpgrade 工具 (Doc ID 3010002.1)：
📎autoupgrade.jar
2、目標庫&源端-配置JAVA環境變量，直接使用19c安裝時候JDK:
#目標端
[oracle@ora19c1 bin]$ ./java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ora19c1 bin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/jdk/bin#配置/etc/profile
export JAVA_HOME=/u01/app/oracle/product/19.3.0/db_1/jdk
export JRE_HOME=/u01/app/oracle/product/19.3.0/db_1/jdk/jre
export PATH=$PATH:$JAVA_HOME/bin#驗證
[root@ora19c1 ~]# source /etc/profile
[root@ora19c1 ~]# java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[root@ora19c1 ~]# su - oracle
Last login: Tue Jun 24 18:36:49 CST 2025 on pts/0
[oracle@ora19c1 ~]$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ora19c1 ~]$ java -jar autoupgrade.jar -version
build.version 25.3.250509
build.date 2025/05/09 02:53:51 +0000
build.hash 3110a3d32
build.hash_date 2025/05/05 19:43:04 +0000
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v25.3, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1[oracle@ora19c1 upgrade]$ scp -r $ORACLE_HOME/jdk 192.168.123.10:/home/oracle#源端：
[root@ora11g ~]# vi /etc/profileexport JAVA_HOME=/home/oracle/jdk
export JRE_HOME=/home/oracle/jdk/jre
export PATH=$PATH:$JAVA_HOME/bin3、源端-創建升級配置文件并檢查升級要求
[oracle@ora11g upgrade]$ java -jar autoupgrade.jar -create_sample_file config
Created sample configuration file /home/oracle/upgrade/sample_config.cfg#Global configurations
#Autoupgrade's global directory, ...
#temp files created and other ...
#send here
global.autoupg_log_dir=/home/oracle/upgrade#
# Database number 1
#
upg1.dbname=orcl
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/11.2.0/db_1
upg1.target_home=/u01/app/oracle/product/19.3.0/db_1
upg1.sid=orcl
upg1.log_dir=/home/oracle/upgrade
upg1.upgrade_node=ora11g
upg1.target_version=19
upg1.restoration=no#進行檢查
[oracle@ora11g upgrade]$ java -jar autoupgrade.jar -config ./sample_config.cfg -mode analyze
AutoUpgrade 25.3.250509 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]Jobs finished [1]
Jobs failed [0]Please check the summary report at:
/home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.log#顯示檢查結果正常
[oracle@ora11g upgrade]$ more /home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================Autoupgrade Summary Report
==========================================
[Date] Tue Jun 24 16:05:29 CST 2025
[Number of Jobs] 1
==========================================
[Job ID] 100
==========================================
[DB Name] orcl
[Version Before Upgrade] 11.2.0.4.0
[Version After Upgrade] 19
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2025-06-24 16:05:16
[Duration] 0:00:12
[Log Directory] /home/oracle/upgrade/orcl/100/prechecks
[Detail] /home/oracle/upgrade/orcl/100/prechecks/orcl_preupgrade.logCheck passed and no manual intervention needed
------------------------------------------#查看進一步的orcl_preupgrade.log發現有一些waring和建議：
BEFORE UPGRADE
==============REQUIRED ACTIONS================NoneRECOMMENDED ACTIONS===================1. (AUTOFIXUP) Remove OLAP Catalog by running the 11.2.0.4.0 SQL script$ORACLE_HOME/olap/admin/catnoamd.sql script.Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) isdesupported and will be automatically marked as OPTION OFF during thedatabase upgrade if present. Oracle recommends removing OLAP Catalog(OLAP AMD) before database upgrade. This step can be manually performedbefore the upgrade to reduce downtime.The OLAP Catalog component, AMD, exists in the database.2. (AUTOFIXUP) Connect to the database as SYS to drop all Data Pump AdvancedQueuing (AQ) tables prior to upgrading. Check MOS note 2789059.1 fordetails.The database needs to be free of Data Pump Advanced Queuing (AQ) tablesin order for Data Pump AQ message types to be re-created during thedatabase upgrade.There exists at least one Data Pump Advanced Queuing (AQ) table in theSYS schema which might prevent Data Pump AQ message types from gettingre-created.3. (AUTOFIXUP) Remove the EM repository.- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target19 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 dbconsoleStep 2: Connect to the database using the SYS account AS SYSDBASET ECHO ON;SET SERVEROUTPUT ON;@emremove.sqlWithout the set echo and serveroutput commands, you will not be able tofollow the progress of the script.Starting with Oracle Database 12c, the local Enterprise Manager DatabaseControl does not exist anymore. The repository will be removed from yourdatabase during the upgrade. This step can be manually performed beforethe upgrade to reduce downtime.The database has an Enterprise Manager Database Control repository.4. Make sure that all the MVs are refreshed and sys.sumdelta$ becomes emptybefore doing upgrade, unless you have strong business reasons not to doso. You can use dbms_mview.refresh() to refresh the MVs except thosestale ones to be kept due to business need. If there are any stale MVsdepending on changes in sys.sumdelta$, do not truncate it, because doingso will cause wrong results after refresh. Refer to the Materialized Viewsection in MOS Note 2380601.1 for more details.Oracle recommends that all materialized views (MV's) are refreshed beforeupgrading the database because this will clear the MV logs and thesumdelta$ table and may reduce the upgrade time. If you choose to notrefresh some MVs, the change data for those MV's will be carried throughthe UPGRADE process. After UPGRADE, you can refresh the MV's and MVincremental refresh should work in normal cases.There are one or more materialized views in either stale or invalidstate, or which are currently being refreshed.5. (AUTOFIXUP) Update NUMERIC INITIALIZATION PARAMETERS to meet estimatedminimums. This action may be done now or when starting the database inupgrade mode using the 19 ORACLE HOME.The database upgrade process requires certain initialization parametersto meet minimum values. The Oracle upgrade process itself has minimumvalues which may be higher and are marked with an asterisk. Afterupgrading, those asterisked parameter values may be reset if needed.Parameter Currently 19 minimum--------- --------- ------------------*sga_target 624951296 10024386566. Upgrade Oracle Application Express (APEX) manually before or after thedatabase upgrade.Starting with Oracle Database Release 18, APEX is not upgradedautomatically as part of the database upgrade. Refer to My Oracle SupportNote 1088970.1 for information about APEX installation and upgrades.Refer to MOS Note 1344948.1 for the minimum APEX version supported foryour target database release. Unsupported versions of APEX will be in anINVALID state when its database dependencies are not in sync with theupgraded database.The database contains APEX. APEX must be upgraded either before or afterthe database is upgraded7. Review below list of parameters set in memory only and for the ones thatare intended to be permanent:1. Save these settings in respective SPFILE.2. Run AutoUpgrade in ANALYZE mode so that parameters can get reflectedin interim pfiles created by the tool.The parameters with values that are in memory only are:Instance Parameter Memory Value Parameter File Value---------- ------------ ----------------- --------------------orcl sga_target 624951296 622854144 orcl sessions 1222 885 For database initialization parameter values that are not in thedatabase's initialization parameter file (pfile/spfile), note that thevalues in memory only will be lost on database shutdown in the currentOracle home prior to upgrading. Hence, these values will not be used inthe database upgrade unless they are recorded in the parameter file.Found at least one parameter with a value in memory that is not in thedatabase's initialization parameter file.8. (AUTOFIXUP) Gather stale data dictionary statistics prior to databaseupgrade in off-peak time using:EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;Dictionary statistics help the Oracle optimizer find efficient SQLexecution plans and are essential for proper upgrade timing. Oraclerecommends gathering dictionary statistics in the last 24 hours beforedatabase upgrade.For information on managing optimizer statistics, refer to the 11.2.0.4Oracle Database Performance Tuning Guide.Dictionary statistics do not exist or are stale (not up-to-date).9. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to theowner of the trigger or drop and re-create the trigger with a user thatwas granted directly with such. You can list those triggers using: SELECTOWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERETRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROMDBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').The creation of database triggers must be done by users granted withADMINISTER DATABASE TRIGGER privilege. Privilege must have been granteddirectly.There is one or more database triggers whose owner does not have theright privilege on the database.10. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade usingthe command:EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;Gathering statistics on fixed objects, if none have been gathered yet, isrecommended prior to upgrading.For information on managing optimizer statistics, refer to the 11.2.0.4Oracle Database Performance Tuning Guide.None of the fixed object tables have had stats collected.#執行自動fixup
[oracle@ora11g upgrade]$ java -jar autoupgrade.jar -config ./sample_config.cfg -mode fixups
AutoUpgrade 25.3.250509 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> WARNING: Target home entry is not available. This could be because is not specified in the configuration file or the specified path does not exist, this may lead AutoUpgrade to not be able to run the fixups for certain checks which need the target Oracle home presence.upg>
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------+
| 101| orcl|PREFIXUPS|EXECUTING|RUNNING| 16:09:39| 7s ago|Executing fixups|
+----+-------+---------+---------+-------+----------+-------+----------------+
Total jobs 1upg> status -job 101
DetailsJob No 101Oracle SID orclStart Time 25/06/24 16:09:39Elapsed (min): 0End time: N/ALogfilesLogs Base: /home/oracle/upgrade/orclJob logs: /home/oracle/upgrade/orcl/101Stage logs: /home/oracle/upgrade/orcl/101/prefixupsTimeZone: /home/oracle/upgrade/orcl/tempRemote Dirs: StagesSETUP <1 minDISPATCH <1 minPRECHECKS <1 minPREFIXUPS ~0 min (RUNNING)Stage-Progress Per Container+--------+---------+|Database|PREFIXUPS|+--------+---------+| orcl| 8 % |+--------+---------+upg> status -job 101
DetailsJob No 101Oracle SID orclStart Time 25/06/24 16:09:39Elapsed (min): 2End time: N/ALogfilesLogs Base: /home/oracle/upgrade/orclJob logs: /home/oracle/upgrade/orcl/101Stage logs: /home/oracle/upgrade/orcl/101/prefixupsTimeZone: /home/oracle/upgrade/orcl/tempRemote Dirs: StagesSETUP <1 minDISPATCH <1 minPRECHECKS <1 minPREFIXUPS ~1 min (RUNNING)Stage-Progress Per Container+--------+---------+|Database|PREFIXUPS|+--------+---------+| orcl| 8 % |+--------+---------+upg> lsj
+----+-------+---------+---------+-------+----------+--------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+--------+----------------+
| 101| orcl|PREFIXUPS|EXECUTING|RUNNING| 16:09:39|117s ago|Executing fixups|
+----+-------+---------+---------+-------+----------+--------+----------------+
Total jobs 1upg> exit
There is 1 job in progress. if you exit it will stop
Are you sure you wish to leave? [y|N] Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]Jobs finished [1]
Jobs failed [0]Please check the summary report at:
/home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.log#驗證自動fixup成功
[oracle@ora11g upgrade]$ more /home/oracle/upgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================Autoupgrade Summary Report
==========================================
[Date] Tue Jun 24 16:14:30 CST 2025
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] orcl
[Version Before Upgrade] 11.2.0.4.0
[Version After Upgrade] 19
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2025-06-24 16:09:39
[Duration] 0:00:09
[Log Directory] /home/oracle/upgrade/orcl/101/prechecks
[Detail] /home/oracle/upgrade/orcl/101/prechecks/orcl_preupgrade.logCheck passed and no manual intervention needed
------------------------------------------
[Stage Name] PREFIXUPS
[Status] SUCCESS
[Start Time] 2025-06-24 16:09:49
[Duration] 0:03:49
[Log Directory] /home/oracle/upgrade/orcl/101/prefixups
[Detail] /home/oracle/upgrade/orcl/101/prefixups/prefixups.html
------------------------------------------
4、源庫-使用rman備份原有生產數據庫：
[oracle@ora11g bak]$ mkdir /home/oracle/rman
[oracle@ora11g bak]$ rman target /RMAN>
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset database format '/home/oracle/rman/db_full_%d_%T_%p_%u.bak';
sql 'alter system archive log current';
backup as compressed backupset archivelog all not backed up 1 times format '/home/oracle/rman/arc_%d_%U.bak';
copy current controlfile to '/home/oracle/rman/cf_%d_id-%I_%u.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}[oracle@ora11g rman]$ cd /home/oracle/rman
[oracle@ora11g rman]$ ls -l
total 332992
-rw-r----- 1 oracle oinstall 7602176 Jun 24 14:35 ARCH043sqni0_1_1.bkp
-rw-r----- 1 oracle oinstall 9830400 Jun 24 14:35 c-1732109901-20250624-00
-rw-r----- 1 oracle oinstall 9830400 Jun 24 14:35 c-1732109901-20250624-01
-rw-r----- 1 oracle oinstall 9797632 Jun 24 14:35 control_ctl
-rw-r----- 1 oracle oinstall 303923200 Jun 24 14:35 DB013sqnh3_1_1.bkp
5、目標-恢復rman備份
#先啟動一個dummy實例
[oracle@ora19c1 ~]$ export ORACLE_SID=orcl1
[oracle@ora19c1 ~]$ rman target /Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 24 14:37:46 2025
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to target database (not started)RMAN> startup nomount;startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.3.0/db_1/dbs/initorcl1.ora'starting Oracle instance without parameter file for retrieval of spfile
Oracle instance startedTotal System Global Area 1073737800 bytesFixed Size 8904776 bytes
Variable Size 276824064 bytes
Database Buffers 780140544 bytes
Redo Buffers 7868416 bytes#恢復spfile文件
RMAN> restore spfile from '/home/oracle/rman/c-1732109901-20250624-01';Starting restore at 24-JUN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=296 device type=DISKchannel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/rman/c-1732109901-20250624-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 24-JUN-25#檢查spfile文件
[oracle@ora19c1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ora19c1 dbs]$ ls
hc_orcl1.dat init.ora spfileorcl1.ora
[oracle@ora19c1 dbs]$ ls -l
total 12
-rw-rw---- 1 oracle asmadmin 1544 Jun 24 14:38 hc_orcl1.dat
-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r----- 1 oracle asmadmin 2560 Jun 24 14:39 spfileorcl1.ora#創建pfile文件并修改相關配置*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='+data/orcl/control01.ctl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=+data'
*.pga_aggregate_target=207618048
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_target=622854144
*.db_name='orcl'
*.cluster_database=false
*.db_create_online_log_dest_1='+data'#啟動
SQL> startup force nomount pfile='/home/oracle/pfile19.ora';
ORACLE instance started.Total System Global Area 624950312 bytes
Fixed Size 8899624 bytes
Variable Size 364904448 bytes
Database Buffers 243269632 bytes
Redo Buffers 7876608 bytes#使用spfile文件啟動到nomount
SQL> create spfile from pfile='/home/oracle/pfile19.ora';File created.SQL> startup force nomount;
ORACLE instance started.Total System Global Area 7516189792 bytes
Fixed Size 8914016 bytes
Variable Size 1241513984 bytes
Database Buffers 6257901568 bytes
Redo Buffers 7860224 bytes#恢復控制文件RMAN> restore controlfile from '/home/oracle/rman/c-1732109901-20250624-01';#mount數據庫并恢復數據文件sql 'alter database mount';
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
SET NEWNAME FOR DATABASE TO '+DATA';
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
};#修改redo 路徑并clear，根據提前clear 可以減少割接時open resetlogs 時間。alter database rename file '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_n5c0yj5m_.log' to '+DATA/orcl/onlinelog/ora_redo01.log';
alter database rename file '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_n5c0yj38_.log' to '+DATA/orcl/onlinelog/ora_redo02.log';
alter database rename file '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_n5c0yj0f_.log' to '+DATA/orcl/onlinelog/ora_redo03.log';alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;在割接開始之前可以多次執行備份歸檔，新庫recover 操作，以減少割接時歸檔傳輸及應用時間
archbak.sh
#!/bin/bashrman target / <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
sql 'alter system archive log current';
backup as compressed backupset archivelog all not backed up 1 times format '/home/oracle/rman/arc_%d_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
exit;
EOFNEW: catalog 備份集，recover database
rman target /
catalog start with '/home/oracle/rman/';
recover database;6、目標-以upgrade的方式打開數據庫
alter database open resetlogs upgrade;
7、目標-執行autoupgrade創建配置文件
[oracle@ora19c1 upgrade]$ java -jar autoupgrade.jar -create_sample_file config
Created sample configuration file /home/oracle/upgrade/sample_config.cfg#Global configurations
#Autoupgrade's global directory, ...
#temp files created and other ...
#send here
global.autoupg_log_dir=/home/oracle/upgrade#
# Database number 1
#
upg1.dbname=orcl
upg1.start_time=NOW
upg1.source_home=/tmp --隨意寫一個/tmp
upg1.target_home=/u01/app/oracle/product/19.3.0/db_1
upg1.sid=orcl1
upg1.log_dir=/home/oracle/upgrade
upg1.upgrade_node=ora19c1
upg1.target_version=19
upg1.restoration=no8、目標-執行升級操作
#執行升級java -jar autoupgrade.jar -config ./sample_config.cfg -mode upgrade[oracle@ora19c1 upgrade]$ java -jar autoupgrade.jar -config ./sample_config.cfg -mode upgrade
AutoUpgrade 25.3.250509 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+----------+-------+-----------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-----------+
| 100| orcl1|DBUPGRADE|EXECUTING|RUNNING| 16:21:47|10s ago|0%Upgraded |
+----+-------+---------+---------+-------+----------+-------+-----------+
Total jobs 1upg> status -job 100
DetailsJob No 100Oracle SID orcl1Start Time 25/06/24 16:21:47Elapsed (min): 0End time: N/ALogfilesLogs Base: /home/oracle/upgrade/orcl1Job logs: /home/oracle/upgrade/orcl1/100Stage logs: /home/oracle/upgrade/orcl1/100/dbupgradeTimeZone: /home/oracle/upgrade/orcl1/tempRemote Dirs: StagesSETUP <1 minDBUPGRADE ~0 min (RUNNING)POSTCHECKS POSTFIXUPS SYSUPDATES Stage-Progress Per Container+--------+---------+|Database|DBUPGRADE|+--------+---------+| orcl1| 0 % |+--------+---------+
9、目標-在sqlnet文件中添加兼容參數
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=810、目標-檢查所有組件
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status
from dba_registry
order by modified;
11、目標-轉換為集群數據庫并添加集群資源
#pfile文件修改如下：
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/orcl/control01.ctl'
*.db_create_online_log_dest_1='+data'
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=+data'
*.pga_aggregate_target=734003200
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_target=1073741824
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.instance_name=orcl1
orcl2.instance_name=orcl2
orcl1.thread=1
orcl2.thread=2#使用pfile啟動startup pfile='/home/oracle/pfile19.ora';#添加thread2的日志組alter database add logfile thread 2 group 10 '+data' size 50m ;
alter database add logfile thread 2 group 11 '+data' size 50m ;
alter database add logfile thread 2 group 12 '+data' size 50m ;SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------1 1 136 52428800 512 1 NO CURRENT 2820730 25-JUN-25 1.8447E+19 02 1 134 52428800 512 1 YES INACTIVE 2618323 25-JUN-25 2719492 25-JUN-25 03 1 135 52428800 512 1 YES INACTIVE 2719492 25-JUN-25 2820730 25-JUN-25 010 2 0 52428800 512 1 YES UNUSED 0 0 011 2 0 52428800 512 1 YES UNUSED 0 0 012 2 0 52428800 512 1 YES UNUSED 0 0 06 rows selected.#添加undotbs2表空間SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' size 50m; Tablespace created.#啟用線程2SQL> alter database enable public thread 2;Database altered.#創建spfile文件到data磁盤組
SQL> create spfile='+data' from pfile='/home/oracle/pfile19.ora'; File created.#創建節點上的init參數文件[oracle@ora19c1 dbs]$ pwd
/u01/app/oracle/product/19.3.0/db_1/dbs
[oracle@ora19c1 dbs]$ cat initorcl1.ora
spfile='+data/orcl/PARAMETERFILE/spfile.717.1204731345'#使用一節點啟動spfile并執行腳本
SQL> startup force
ORACLE instance started.Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 390070272 bytes
Database Buffers 666894336 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.
SQL> show parameter pfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/PARAMETERFILE/spfile.717.1204731345
SQL> @?/rdbms/admin/catclust.sql #添加集群資源srvctl add database -d orcl -o $ORACLE_HOME
srvctl add instance -d orcl -n ora19c1 -i orcl1
srvctl add instance -d orcl -n ora19c2 -i orcl2
srvctl config database -d orclDatabase unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/19.3.0/db_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: orcl1,orcl2
Configured nodes: ora19c1,ora19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managedsrvctl modify database -d orcl -a DATA
srvctl modify database -d orcl -p '+data/orcl/PARAMETERFILE/spfile.717.1204731345'Database unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/19.3.0/db_1
Oracle user: oracle
Spfile: +data/orcl/PARAMETERFILE/spfile.717.1204731345
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: orcl1,orcl2
Configured nodes: ora19c1,ora19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed#使用grid統一啟動[grid@ora19c1 ~]$ srvctl start database -d orcl#創建密碼文件（或者復制過來）
問題列表
問題原因如下
1、第一次升級的時候由于虛擬機內存不足報錯如下：
2、第二次添加內存和cpu為8c8G，并調整sga=7G，pga=1G，再次升級報錯如下：
#pfile19c.ora*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='+data/orcl/control01.ctl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=+data'
*.pga_aggregate_target=1G
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_target=7G
*.db_name='orcl'
*.cluster_database=false
*.db_create_online_log_dest_1='+data'
查看mos找到一個類似的bug：
📎Oracle 19c autoupgrade fails with java.sql.SQLException.pdf
然后我也沒有改參數，嘗試使用resum job -100再次運行job竟然成功了。
時區文件也正常：
決定再升級一次試試，先刪除upgrade下的文件夾，然后重新恢復數據庫至upgrade狀態：
再次rman恢復后升級發現沒有任何報錯了，順利升級成功：
[oracle@ora19c1 upgrade]$ more /home/oracle/upgrade/orcl1/100/dbupgrade/upg_summary.logOracle Database Release 19 Post-Upgrade Status Tool 06-25-2025 11:39:5
Database Name: ORCLComponent Current Full Elapsed Time
Name Status Version HH:MM:SSOracle Server UPGRADED 19.3.0.0.0 00:09:51
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:02:11
Oracle XDK UPGRADED 19.3.0.0.0 00:00:17
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:05
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:05
OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
Oracle Text UPGRADED 19.3.0.0.0 00:00:19
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:24
Oracle Real Application Clusters VALID 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:01:34
Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:34
Spatial UPGRADED 19.3.0.0.0 00:02:44
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:09
Datapatch 00:02:07
Final Actions 00:02:42
Post Upgrade 00:00:45Total Upgrade Time: 00:23:20Database time zone version is 14. 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:27m:34s]
升級過程中的日志：
📎orcl1.7z
參考
問題解決如下
📎AutoUpgrade ?具.pdf
📎數據庫 升級 降級 兼容性矩陣.pdf
