Oracle单机版11.2.0.1到11.2.0.4手动升级指南
一、前言
Oracle 11.2.0.1 版本在实际生产应用中存在多个已知问题，其中最典型的便是 DBLink SCN 不同步 等 Bug，严重影响系统稳定性和数据一致性。为了解决这些问题，提升数据库的稳定性与兼容性，我们决定对一套运行在 Linux 环境下的 Oracle 单机数据库（11.2.0.1） 进行版本升级，目标版本为 11.2.0.4。
本次文档将详细记录整个升级过程，包括前期准备、操作步骤、注意事项以及升级后的验证操作，旨在为有类似需求的同仁提供可参考的实施方案与实践经验。
二、环境说明
在Liunx操作系统环境下，对本机单机数据库从Oracle 11.2.0.1升级到11.2.0.4版本
| 操作系统版本 | 数据库版本 | 安装目录 |
|---|---|---|
| CentOS 7.6 | Oracle 11.2.0.1 | /app/oracle/product/11.2.0 |
| CentOS 7.6 | Oracle 11.2.0.4 | /app/oracle/product/11.2.0.4/db_1 |
三、原数据库明细
3.1 确认SPFILE位置
[oracle@qxdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 26 14:57:41 2025
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/11.2.0/dbs
/spfilecsqxcs.ora
SQL>
3.2 查看数据库名，安装平台，安装组件
SQL> col platform_name for a30
SQL> select name,open_mode,platform_name from v$database;
NAME OPEN_MODE PLATFORM_NAME
--------- -------------------- ------------------------------
CSQXCS READ WRITE Linux x86 64-bit
SQL>
SQL> col comp_name for a40
SQL> col version for a15
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- --------------- ----------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID
OLAP Catalog 11.2.0.1.0 VALID
Spatial 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
COMP_NAME VERSION STATUS
---------------------------------------- --------------- ----------------------
Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 VALID
Oracle OLAP API 11.2.0.1.0 VALID
18 rows selected.
SQL>
3.3 数据库版本信息
SQL> select instance_name,version from v$instance;
INSTANCE_NAME VERSION
---------------- ---------------
csqxcs 11.2.0.1.0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> col product for a20
SQL> col version for a12
SQL> col status for a20
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
-------------------- ------------ --------------------
NLSRTL 11.2.0.1.0 Production
Oracle Database 11g 11.2.0.1.0 64bit Production
Enterprise Edition
PL/SQL 11.2.0.1.0 Production
TNS for Linux: 11.2.0.1.0 Production
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL>
3.4 控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/csqxcs/control01.ctl
/app/oracle/flash_recovery_area/csqxcs/control02.ctl
SQL>
3.5 在线日志文件
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/csqxcs/redo03.log
/oradata/csqxcs/redo02.log
/oradata/csqxcs/redo01.log
/oradata/csqxcs/standby11.log
/oradata/csqxcs/standby12.log
/oradata/csqxcs/standby13.log
/oradata/csqxcs/standby14.log
7 rows selected.
SQL>
3.6 数据文件
SQL> set lines 200
SQL> col file_name for a70
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------- ------------------------------
/oradata/csqxcs/users01.dbf USERS
/oradata/csqxcs/undotbs01.dbf UNDOTBS1
/oradata/csqxcs/sysaux01.dbf SYSAUX
/oradata/csqxcs/system01.dbf SYSTEM
/oradata/csqxcs/users02.dbf USERS
/oradata/csqxcs/users03.dbf USERS
/app/oracle/product/11.2.0/dbs/D:appAdministratororadataorclUSER03.dbf USERS
7 rows selected.
SQL>
3.7 归档模式
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /oraarch Oldest online log sequence 49323 Next log sequence to archive 49325 Current log sequence 49325 SQL>
3.8 用户状态
SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MED_GKCS OPEN
SPMQXUSER OPEN
MED_DEV OPEN
CMSSELECT OPEN
TSFIN OPEN
CSQXDIS OPEN
TMPUSER OPEN
CMS_DJ OPEN
RPA_MED OPEN
CMSQX_EBS_HN OPEN
DTPCMS OPEN
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CSQX_DSF_SAVE OPEN
DISCSQX OPEN
MED_SELECT OPEN
SJZTUSER OPEN
SYSTEM OPEN
SYS OPEN
SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
APEX_PUBLIC_USER EXPIRED & LOCKED
DIP EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
XS$NULL EXPIRED & LOCKED
ORACLE_OCM EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OWBSYS EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
SYSMAN EXPIRED & LOCKED
ANONYMOUS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ORDDATA EXPIRED & LOCKED
OWBSYS_AUDIT EXPIRED & LOCKED
APEX_030200 EXPIRED & LOCKED
APPQOSSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
FLOWS_FILES EXPIRED & LOCKED
MGMT_VIEW EXPIRED & LOCKED
OUTLN EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
DBSNMP EXPIRED & LOCKED
45 rows selected.
SQL>
3.9 失效对象记录
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-07-26 15:17:13
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 2025-07-26 15:17:16
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). 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 errors 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>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV CMS_OUTPT_WMSINBOUD_V VIEW INVALID
MED_DEV ERS_CSTGEN_V VIEW INVALID
MED_DEV FIN_AR_CHALKMANSELLERCST_V VIEW INVALID
MED_DEV JD_INPT_AP_FORE_TEMP_V VIEW INVALID
MED_DEV JD_INPT_AR_OFFWDR_TEMP_V VIEW INVALID
MED_DEV KPI_SALEXAM_TARGET_V VIEW INVALID
MED_DEV NC_INPT_AP_CHALKUP_GS_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AP_CHALKUP_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AP_FORE_GS_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AP_FORE_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AP_OFFWDR_GS_TEMP_V VIEW INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV NC_INPT_AP_OFFWDR_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AR_OFFWDR_GS_TEMP_V VIEW INVALID
MED_DEV NC_INPT_AR_OFFWDR_TEMP_V VIEW INVALID
MED_DEV SCM_SELLER_STRUCT_V VIEW INVALID
MED_DEV SCM_STK_MONITORING_MAIN_GAN VIEW INVALID
MED_DEV STM_INPT_CARD_V VIEW INVALID
MED_DEV WEB_CLIENTS_V VIEW INVALID
MED_DEV YK_INPT_AP_OFFPLANWDR_TEMP_V VIEW INVALID
MED_DEV YK_INPT_AP_OFFWDR_TEMP_V VIEW INVALID
MED_DEV SCM_PURDIS_PAYCST_V VIEW INVALID
MED_DEV ERP_BILL_MAIN_VIEW VIEW INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV VIEW_JZFP_LWG VIEW INVALID
MED_DEV ERP_BILL_INFO_VIEW VIEW INVALID
MED_DEV SEND_PRODUCT_INFO VIEW INVALID
MED_DEV OLD_PURBILL_HDR_V VIEW INVALID
MED_DEV OLD_SALBILL_HDR_V VIEW INVALID
MED_DEV ERP_BILL_MAIN_VIEW_20150112BAK VIEW INVALID
MED_DEV SEND_PRODUCT_INFO_20150112BAK VIEW INVALID
MED_DEV ERP_BILL_INFO_VIEW_20150112BAK VIEW INVALID
MED_DEV CMS_OUTPT_WMSOUTBOUND_V VIEW INVALID
MED_DEV INPT_GXS_SALBILL_ALL_V VIEW INVALID
MED_DEV INPT_GXS_SALBILL_V VIEW INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV JD_INPT_AP_FORE_V VIEW INVALID
MED_DEV JD_INPT_AR_OFFWDR_V VIEW INVALID
MED_DEV NC_INPT_AP_CHALKUP_GS_V VIEW INVALID
MED_DEV NC_INPT_AP_CHALKUP_V VIEW INVALID
MED_DEV NC_INPT_AP_FORE_GS_V VIEW INVALID
MED_DEV NC_INPT_AP_PUR_OFFWDR_GS_V VIEW INVALID
MED_DEV NC_INPT_AP_PUR_OFFWDR_V VIEW INVALID
MED_DEV NC_INPT_AR_SAL_OFFWDR_GS_V VIEW INVALID
MED_DEV NC_INPT_AR_SAL_OFFWDR_V VIEW INVALID
MED_DEV SCM_BUY_BILL_PRO_V VIEW INVALID
MED_DEV SCM_LOT_STOCK_DITCH_PRO_V VIEW INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV SCM_LOT_STOCK_PRO_V VIEW INVALID
MED_DEV SCM_SAL_BILL_PRO_V VIEW INVALID
MED_DEV USER_SCM_BILL_RPT_FLOWS_LRTF_V VIEW INVALID
MED_DEV USER_SCM_BILL_RPT_FLOWS_LRT_V VIEW INVALID
MED_DEV USER_SCM_BILL_RPT_FLOWS_V VIEW INVALID
MED_DEV YK_INPT_AP_OFFPLANWDR_V VIEW INVALID
MED_DEV YK_INPT_AP_OFFWDR_V VIEW INVALID
MED_DEV USER_SCM_BILL_RPT_FLOWS_F_V VIEW INVALID
MED_DEV USER_SCM_BILL_RPT_FLOWS_V_TSYP VIEW INVALID
MED_DEV SCM_SALBILL_ELECINV_V VIEW INVALID
MED_DEV STM_INPT_SALSTREAM_V VIEW INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV P_PUB_WAREDICT_OWNERID_AUTO PROCEDURE INVALID
MED_DEV P_SCM_LOT_STOCK_DITCH PROCEDURE INVALID
MED_DEV P_SCM_SAL_CHANGE_PL PROCEDURE INVALID
MED_DEV P_SUPPLY_CHAIN_LOG PROCEDURE INVALID
MED_DEV P_YT2 PROCEDURE INVALID
MED_DEV P_YT3 PROCEDURE INVALID
MED_DEV P_YT4 PROCEDURE INVALID
MED_DEV P_YT5 PROCEDURE INVALID
MED_DEV P_YT_CLOSE_ASN PROCEDURE INVALID
MED_DEV P_YT_TABCNT PROCEDURE INVALID
MED_DEV SCM_CHECK_CSTCARD PROCEDURE INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV TOOL_QUERYTABLEROWS PROCEDURE INVALID
MED_DEV WEB_IMP_CSTSOWARE PROCEDURE INVALID
MED_DEV FIN_SAL_NEWINVOICENO PROCEDURE INVALID
MED_DEV LRT_SCM_PURBILL_SPD PROCEDURE INVALID
MED_DEV PRC_LDCX_BMXS PROCEDURE INVALID
MED_DEV PRC_LDCX_LSMY PROCEDURE INVALID
MED_DEV P_EXEC_PROCEDURES PROCEDURE INVALID
MED_DEV P_EXPTRANS PROCEDURE INVALID
MED_DEV P_EXPTRANS_BOXBILL PROCEDURE INVALID
MED_DEV P_EXPTRANS_INVOICE PROCEDURE INVALID
MED_DEV P_EXPTRANS_PURBILL PROCEDURE INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV P_EXPTRANS_PURINVOICE PROCEDURE INVALID
MED_DEV P_EXPTRANS_SALBILL PROCEDURE INVALID
MED_DEV P_EXPTRANS_SALINVOICE PROCEDURE INVALID
MED_DEV P_EXPTRANS_WMSINBOUD PROCEDURE INVALID
MED_DEV P_EXPTRANS_WMSOUTBOUND PROCEDURE INVALID
MED_DEV P_HSJ_KPI_OLDTONEW PROCEDURE INVALID
MED_DEV P_IMPTRANS PROCEDURE INVALID
MED_DEV P_IMPTRANS_ERS_CSTGEN PROCEDURE INVALID
MED_DEV P_IMPTRANS_INPT_APPLYBILL PROCEDURE INVALID
MED_DEV P_IMPTRANS_INPT_PURAPPLYBILL PROCEDURE INVALID
MED_DEV P_IMPTRANS_INPT_SALAPPLYBILL PROCEDURE INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV P_INI_LOT_STOCK PROCEDURE INVALID
MED_DEV P_INI_LOT_STOCK_1 PROCEDURE INVALID
MED_DEV P_INI_LOT_STOCK_SY PROCEDURE INVALID
MED_DEV P_INI_LOT_STOCK_WJ PROCEDURE INVALID
MED_DEV P_INPT_ITEM_MASTER PROCEDURE INVALID
MED_DEV P_INPT_OUTPT2HZJK PROCEDURE INVALID
MED_DEV P_INPT_STORE_MASTER PROCEDURE INVALID
MED_DEV P_INPT_VENDOR_MASTER PROCEDURE INVALID
MED_DEV F_GET_EMP_LOGICDEPT FUNCTION INVALID
MED_DEV F_GET_ISINPTDECIMAL FUNCTION INVALID
MED_DEV F_GET_SALE_TASK FUNCTION INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV F_GET_SALGIF_INF FUNCTION INVALID
MED_DEV F_SCM_GETQTY_NEW FUNCTION INVALID
MED_DEV USER_F_GET_SALESTATE_XJ FUNCTION INVALID
MED_DEV USER_GS PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS1 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS2 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS3 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS4 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS5 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS6 PACKAGE BODY INVALID
MED_DEV ZTC_INI_KITS7 PACKAGE BODY INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV INS_UPD_PUB_WAREDICT_WHS TRIGGER INVALID
MED_DEV TD_BPM_STOCKSTATUS_DEF TRIGGER INVALID
MED_DEV TD_BPM_LOCK_DEF TRIGGER INVALID
MED_DEV TD_PUB_BANK TRIGGER INVALID
MED_DEV TD_PUB_CLIENTS TRIGGER INVALID
MED_DEV TD_PUB_CLIENTS_INVTITLE TRIGGER INVALID
MED_DEV TD_PUB_CLIENTS_KIND TRIGGER INVALID
MED_DEV TD_PUB_CLIENTS_OWNER TRIGGER INVALID
MED_DEV TD_PUB_CURRENCY TRIGGER INVALID
MED_DEV TD_PUB_DEPT TRIGGER INVALID
MED_DEV TD_PUB_EMP TRIGGER INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV TD_PUB_LOCN TRIGGER INVALID
MED_DEV TD_PUB_STORE_MASTER TRIGGER INVALID
MED_DEV TD_PUB_WAREDICT TRIGGER INVALID
MED_DEV TD_PUB_WAREDICT_KIND TRIGGER INVALID
MED_DEV TD_PUB_WAREDICT_OWNER TRIGGER INVALID
MED_DEV TD_PUB_WAREDICT_PRONO TRIGGER INVALID
MED_DEV TD_SCM_BUYERCST_LINKMAN TRIGGER INVALID
MED_DEV TD_SCM_PAYMODE TRIGGER INVALID
MED_DEV TD_SCM_TRANSMODE TRIGGER INVALID
MED_DEV TD_SYS_CODE TRIGGER INVALID
MED_DEV TD_WMS_TSK_WORK_GRPAREA TRIGGER INVALID
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --------------------
MED_DEV TRG_SCM_SALBILL_HDR_BALANCE TRIGGER INVALID
MED_DEV TRG_SCM_SALBILL_HDR_DISCOUNT TRIGGER INVALID
MED_DEV INS_UPD_PUB_STORE_MASTER TRIGGER INVALID
135 rows selected.
SQL>
四、备份原数据库
使用rman对数据库整库进行level 0备份，同时备份归档日志和控制文件，spfile文件
[oracle@qxdb ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 26 15:26:06 2025
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CSQXCS (DBID=913750087)
RMAN> backup as compressed backupset tag full_backup_level0 incremental level 0 format '/backup/level0_%T_%U.bak' database;
Starting backup at 2025-07-26 15:27:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1913 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/csqxcs/users02.dbf
input datafile file number=00006 name=/oradata/csqxcs/users03.dbf
input datafile file number=00003 name=/oradata/csqxcs/undotbs01.dbf
input datafile file number=00001 name=/oradata/csqxcs/system01.dbf
input datafile file number=00004 name=/oradata/csqxcs/users01.dbf
input datafile file number=00007 name=/app/oracle/product/11.2.0/dbs/D:appAdministratororadataorclUSER03.dbf
input datafile file number=00002 name=/oradata/csqxcs/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:27:20
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:45:15
piece handle=/backup/level0_20250726_le3vhquo_1_1.bak tag=FULL_BACKUP_LEVEL0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:17:55
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:45:16
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:45:17
piece handle=/backup/level0_20250726_lf3vhs0b_1_1.bak tag=FULL_BACKUP_LEVEL0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-07-26 15:45:17
RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current
RMAN> backup as compressed backupset format '/backup/archlog_%T_%U.bak' archivelog all;
Starting backup at 2025-07-26 15:47:35
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49212 RECID=55601 STAMP=1207361425
input archived log thread=1 sequence=49213 RECID=55602 STAMP=1207371601
input archived log thread=1 sequence=49214 RECID=55603 STAMP=1207374003
input archived log thread=1 sequence=49215 RECID=55604 STAMP=1207375011
input archived log thread=1 sequence=49216 RECID=55605 STAMP=1207376517
input archived log thread=1 sequence=49217 RECID=55606 STAMP=1207386909
input archived log thread=1 sequence=49218 RECID=55607 STAMP=1207388715
input archived log thread=1 sequence=49219 RECID=55608 STAMP=1207389602
input archived log thread=1 sequence=49220 RECID=55609 STAMP=1207389603
input archived log thread=1 sequence=49221 RECID=55610 STAMP=1207389605
input archived log thread=1 sequence=49222 RECID=55611 STAMP=1207389608
input archived log thread=1 sequence=49223 RECID=55612 STAMP=1207389609
input archived log thread=1 sequence=49224 RECID=55613 STAMP=1207389611
input archived log thread=1 sequence=49225 RECID=55614 STAMP=1207389614
input archived log thread=1 sequence=49226 RECID=55615 STAMP=1207389615
input archived log thread=1 sequence=49227 RECID=55616 STAMP=1207389617
input archived log thread=1 sequence=49228 RECID=55617 STAMP=1207389620
input archived log thread=1 sequence=49229 RECID=55618 STAMP=1207389623
input archived log thread=1 sequence=49230 RECID=55619 STAMP=1207389624
input archived log thread=1 sequence=49231 RECID=55620 STAMP=1207389626
input archived log thread=1 sequence=49232 RECID=55621 STAMP=1207389629
input archived log thread=1 sequence=49233 RECID=55622 STAMP=1207389636
input archived log thread=1 sequence=49234 RECID=55623 STAMP=1207390842
input archived log thread=1 sequence=49235 RECID=55624 STAMP=1207392081
input archived log thread=1 sequence=49236 RECID=55625 STAMP=1207393233
input archived log thread=1 sequence=49237 RECID=55626 STAMP=1207395135
input archived log thread=1 sequence=49238 RECID=55627 STAMP=1207402164
input archived log thread=1 sequence=49239 RECID=55628 STAMP=1207402229
input archived log thread=1 sequence=49240 RECID=55629 STAMP=1207402233
input archived log thread=1 sequence=49241 RECID=55630 STAMP=1207402239
input archived log thread=1 sequence=49242 RECID=55631 STAMP=1207402248
input archived log thread=1 sequence=49243 RECID=55632 STAMP=1207402269
input archived log thread=1 sequence=49244 RECID=55633 STAMP=1207402287
input archived log thread=1 sequence=49245 RECID=55634 STAMP=1207402308
input archived log thread=1 sequence=49246 RECID=55635 STAMP=1207402326
input archived log thread=1 sequence=49247 RECID=55636 STAMP=1207402332
input archived log thread=1 sequence=49248 RECID=55637 STAMP=1207402336
input archived log thread=1 sequence=49249 RECID=55638 STAMP=1207402350
input archived log thread=1 sequence=49250 RECID=55639 STAMP=1207402369
input archived log thread=1 sequence=49251 RECID=55640 STAMP=1207404033
input archived log thread=1 sequence=49252 RECID=55641 STAMP=1207406934
input archived log thread=1 sequence=49253 RECID=55642 STAMP=1207408371
input archived log thread=1 sequence=49254 RECID=55643 STAMP=1207410711
input archived log thread=1 sequence=49255 RECID=55644 STAMP=1207412157
input archived log thread=1 sequence=49256 RECID=55645 STAMP=1207414989
input archived log thread=1 sequence=49257 RECID=55646 STAMP=1207422749
input archived log thread=1 sequence=49258 RECID=55647 STAMP=1207422750
input archived log thread=1 sequence=49259 RECID=55648 STAMP=1207428699
input archived log thread=1 sequence=49260 RECID=55649 STAMP=1207443681
input archived log thread=1 sequence=49261 RECID=55650 STAMP=1207443915
input archived log thread=1 sequence=49262 RECID=55651 STAMP=1207443920
input archived log thread=1 sequence=49263 RECID=55652 STAMP=1207443927
input archived log thread=1 sequence=49264 RECID=55653 STAMP=1207443947
input archived log thread=1 sequence=49265 RECID=55654 STAMP=1207443953
input archived log thread=1 sequence=49266 RECID=55655 STAMP=1207443960
input archived log thread=1 sequence=49267 RECID=55656 STAMP=1207444155
input archived log thread=1 sequence=49268 RECID=55657 STAMP=1207444324
input archived log thread=1 sequence=49269 RECID=55658 STAMP=1207447202
input archived log thread=1 sequence=49270 RECID=55659 STAMP=1207447204
input archived log thread=1 sequence=49271 RECID=55660 STAMP=1207447205
input archived log thread=1 sequence=49272 RECID=55661 STAMP=1207447209
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:47:35
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:48:30
piece handle=/backup/archlog_20250726_lg3vhs4n_1_1.bak tag=TAG20250726T154735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49273 RECID=55662 STAMP=1207447212
input archived log thread=1 sequence=49274 RECID=55663 STAMP=1207447215
input archived log thread=1 sequence=49275 RECID=55664 STAMP=1207447218
input archived log thread=1 sequence=49276 RECID=55665 STAMP=1207447221
input archived log thread=1 sequence=49277 RECID=55666 STAMP=1207447224
input archived log thread=1 sequence=49278 RECID=55667 STAMP=1207447228
input archived log thread=1 sequence=49279 RECID=55668 STAMP=1207447231
input archived log thread=1 sequence=49280 RECID=55669 STAMP=1207447234
input archived log thread=1 sequence=49281 RECID=55670 STAMP=1207447237
input archived log thread=1 sequence=49282 RECID=55671 STAMP=1207447240
input archived log thread=1 sequence=49283 RECID=55672 STAMP=1207447243
input archived log thread=1 sequence=49284 RECID=55673 STAMP=1207447247
input archived log thread=1 sequence=49285 RECID=55674 STAMP=1207447250
input archived log thread=1 sequence=49286 RECID=55675 STAMP=1207447253
input archived log thread=1 sequence=49287 RECID=55676 STAMP=1207447256
input archived log thread=1 sequence=49288 RECID=55677 STAMP=1207447259
input archived log thread=1 sequence=49289 RECID=55678 STAMP=1207447263
input archived log thread=1 sequence=49290 RECID=55679 STAMP=1207447266
input archived log thread=1 sequence=49291 RECID=55680 STAMP=1207447269
input archived log thread=1 sequence=49292 RECID=55681 STAMP=1207447272
input archived log thread=1 sequence=49293 RECID=55682 STAMP=1207447275
input archived log thread=1 sequence=49294 RECID=55683 STAMP=1207447278
input archived log thread=1 sequence=49295 RECID=55684 STAMP=1207447282
input archived log thread=1 sequence=49296 RECID=55685 STAMP=1207447285
input archived log thread=1 sequence=49297 RECID=55686 STAMP=1207447288
input archived log thread=1 sequence=49298 RECID=55687 STAMP=1207447291
input archived log thread=1 sequence=49299 RECID=55688 STAMP=1207447294
input archived log thread=1 sequence=49300 RECID=55689 STAMP=1207447297
input archived log thread=1 sequence=49301 RECID=55690 STAMP=1207447301
input archived log thread=1 sequence=49302 RECID=55691 STAMP=1207447304
input archived log thread=1 sequence=49303 RECID=55692 STAMP=1207447307
input archived log thread=1 sequence=49304 RECID=55693 STAMP=1207447310
input archived log thread=1 sequence=49305 RECID=55694 STAMP=1207447313
input archived log thread=1 sequence=49306 RECID=55695 STAMP=1207447316
input archived log thread=1 sequence=49307 RECID=55696 STAMP=1207447320
input archived log thread=1 sequence=49308 RECID=55697 STAMP=1207447323
input archived log thread=1 sequence=49309 RECID=55698 STAMP=1207447326
input archived log thread=1 sequence=49310 RECID=55699 STAMP=1207447329
input archived log thread=1 sequence=49311 RECID=55700 STAMP=1207447332
input archived log thread=1 sequence=49312 RECID=55701 STAMP=1207447338
input archived log thread=1 sequence=49313 RECID=55702 STAMP=1207458002
input archived log thread=1 sequence=49314 RECID=55703 STAMP=1207458513
input archived log thread=1 sequence=49315 RECID=55704 STAMP=1207461363
input archived log thread=1 sequence=49316 RECID=55705 STAMP=1207461417
input archived log thread=1 sequence=49317 RECID=55706 STAMP=1207476003
input archived log thread=1 sequence=49318 RECID=55707 STAMP=1207476005
input archived log thread=1 sequence=49319 RECID=55708 STAMP=1207476007
input archived log thread=1 sequence=49320 RECID=55709 STAMP=1207476010
input archived log thread=1 sequence=49321 RECID=55710 STAMP=1207476021
input archived log thread=1 sequence=49322 RECID=55711 STAMP=1207476078
input archived log thread=1 sequence=49323 RECID=55712 STAMP=1207478874
input archived log thread=1 sequence=49324 RECID=55713 STAMP=1207487352
input archived log thread=1 sequence=49325 RECID=55714 STAMP=1207495035
input archived log thread=1 sequence=49326 RECID=55715 STAMP=1207496814
input archived log thread=1 sequence=49327 RECID=55716 STAMP=1207496855
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:48:30
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:49:25
piece handle=/backup/archlog_20250726_lh3vhs6e_1_1.bak tag=TAG20250726T154735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 2025-07-26 15:49:25
RMAN> backup current controlfile format '/backup/controlfile_%T_%U.bak';
Starting backup at 2025-07-26 15:49:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:49:48
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:49:49
piece handle=/backup/controlfile_20250726_li3vhs8r_1_1.bak tag=TAG20250726T154947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-07-26 15:49:49
RMAN> backup spfile format '/backup/spfile_%T_%U.bak';
Starting backup at 2025-07-26 15:50:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2025-07-26 15:50:11
channel ORA_DISK_1: finished piece 1 at 2025-07-26 15:50:12
piece handle=/backup/spfile_20250726_lj3vhs9j_1_1.bak tag=TAG20250726T155011 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-07-26 15:50:12
RMAN> exit
Recovery Manager complete.
[oracle@qxdb ~]$ ls -lh /backup/*.bak
-rw-r----- 1 oracle oinstall 606M Jul 26 15:48 /backup/archlog_20250726_lg3vhs4n_1_1.bak
-rw-r----- 1 oracle oinstall 610M Jul 26 15:49 /backup/archlog_20250726_lh3vhs6e_1_1.bak
-rw-r----- 1 oracle oinstall 12M Jul 26 15:49 /backup/controlfile_20250726_li3vhs8r_1_1.bak
-rw-r----- 1 oracle oinstall 12G Jul 26 15:45 /backup/level0_20250726_le3vhquo_1_1.bak
-rw-r----- 1 oracle oinstall 1.4M Jul 26 15:45 /backup/level0_20250726_lf3vhs0b_1_1.bak
-rw-r----- 1 oracle oinstall 96K Jul 26 15:50 /backup/spfile_20250726_lj3vhs9j_1_1.bak
[oracle@qxdb ~]$
五、静默安装数据软件
把11.2.0.4安装包上传到/db-update 目录下，解压后进行静默安装数据库软件11.2.0.4
[root@qxdb backup]# su - oracle
Last login: Sat Jul 26 15:32:03 CST 2025 on pts/3
[oracle@qxdb ~]$ cd /db-update/
[oracle@qxdb db-update]$ ls -lh
total 2.4G
-rw-r--r-- 1 oracle oinstall 1.3G Jul 21 15:50 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1.1G Jul 21 15:49 p13390677_112040_Linux-x86-64_2of7.zip
[oracle@qxdb db-update]$ unzip -q p13390677_112040_Linux-x86-64_1of7.zip
[oracle@qxdb db-update]$ unzip -q p13390677_112040_Linux-x86-64_2of7.zip
[oracle@qxdb db-update]$ cd database/
[oracle@qxdb database]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_SID=csqxcs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11.2.0
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/bin/:$PATH
export PATH=$PATH:/usr/bin:/bin:/usr/bin/x11:/usr/local/bin
export PATH=$PATH:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/lib64:/lib:$ORACLE_HOME/rdbms/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
[oracle@qxdb database]$ cat > db.rsp << "EOF"
> oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
> oracle.install.option=INSTALL_DB_SWONLY
> ORACLE_HOSTNAME=oracle11g
> UNIX_GROUP_NAME=oinstall
> INVENTORY_LOCATION=/app/oracle/oraInventory
> SELECTED_LANGUAGES=en,zh_CN
> ORACLE_HOME=/app/oracle/product/11.2.0.4/db_1
> ORACLE_BASE=/app/oracle
> oracle.install.db.InstallEdition=EE
> oracle.install.db.DBA_GROUP=dba
> oracle.install.db.OPER_GROUP=oper
> DECLINE_SECURITY_UPDATES=true
> oracle.installer.autoupdates.option=SKIP_UPDATES
> EOF
[oracle@qxdb database]$ pwd
/db-update/database
[oracle@qxdb database]$ cat db.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=oracle11g
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/app/oracle/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/app/oracle/product/11.2.0.4/db_1
ORACLE_BASE=/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES
[oracle@qxdb database]$ mkdir -p /app/oracle/product/11.2.0.4/db_1
[oracle@qxdb database]$ ./runInstaller -silent -force -showProgress -ignoreSysPrereqs -waitForCompletion -responseFile /db-update/database/db.rsp -ignorePrereq
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 792434 MB Passed
Checking swap space: must be greater than 150 MB. Actual 32734 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2025-07-26_03-41-30PM. Please wait ...[WARNING] [INS-35342] The specified group for Database Operator (OSOPER) may not be defined in the system.
CAUSE: The OS group you specified as the OSOPER group may not be configured on the server, or in the Network Information Service (NIS).
ACTION: Check the group name you selected, and specify a different group, or create the group on the server or in the Network Information Service.
You can find the log of this install session at:
/app/oraInventory/logs/installActions2025-07-26_03-41-30PM.log
Prepare in progress.
.................................................. 9% Done.
Prepare successful.
Copy files in progress.
.................................................. 14% Done.
.................................................. 20% Done.
.................................................. 26% Done.
.................................................. 31% Done.
.................................................. 36% Done.
.................................................. 41% Done.
.................................................. 47% Done.
.................................................. 52% Done.
.................................................. 57% Done.
.................................................. 63% Done.
.................................................. 68% Done.
.................................................. 73% Done.
.................................................. 78% Done.
.................................................. 83% Done.
..............................
Copy files successful.
Link binaries in progress.
..........
Link binaries successful.
Setup files in progress.
.................................................. 88% Done.
.................................................. 94% Done.
Setup files successful.
The installation of Oracle Database 11g was successful.
Please check '/app/oraInventory/logs/silentInstall2025-07-26_03-41-30PM.log' for more details.
Execute Root Scripts in progress.
As a root user, execute the following script(s):
1. /app/oracle/product/11.2.0.4/db_1/root.sh
.................................................. 100% Done.
Execute Root Scripts successful.
Successfully Setup Software.
[oracle@qxdb database]$
六、关闭原数据库
[oracle@qxdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 26 15:56:06 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@qxdb ~]$
七、关闭原监听
[oracle@qxdb ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JUL-2025 15:57:21
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.168.135)(PORT=1521)))
The command completed successfully
[oracle@qxdb ~]$
八、环境变量调整
8.1 启动文件和监听配置
把原dbs目录中spfile和密码文件等复制到新安装的ORACLE_HOME目录下，tnsnames.ora、listener.ora监听文件也一样复制到新安装的ORACLE_HOME目录下
[oracle@qxdb ~]$ cd $ORACLE_HOME [oracle@qxdb 11.2.0]$ cp -r dbs /app/oracle/product/11.2.0.4/db_1/ [oracle@qxdb 11.2.0]$ cd network/admin/ [oracle@qxdb 11.2.0]$ cd network/admin/ [oracle@qxdb admin]$ ll total 12 -rw-r--r-- 1 oracle oinstall 297 Jul 19 08:25 listener.ora drwxr-xr-x. 2 oracle oinstall 64 Jun 19 2022 samples -rw-r--r--. 1 oracle oinstall 187 May 8 2007 shrept.lst -rw-r--r-- 1 oracle oinstall 365 Jul 19 08:26 tnsnames.ora [oracle@qxdb admin]$ cp listener.ora tnsnames.ora /app/oracle/product/11.2.0.4/db_1/network/admin/ [oracle@qxdb admin]$
8.2 环境变量11204.env
创建11.2.0.4的环境变量，并应用11.2.0.4的数据库环境变量
[oracle@qxdb admin]$ cd ~
[oracle@qxdb ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_SID=csqxcs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11.2.0
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/bin/:$PATH
export PATH=$PATH:/usr/bin:/bin:/usr/bin/x11:/usr/local/bin
export PATH=$PATH:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/lib64:/lib:$ORACLE_HOME/rdbms/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
[oracle@qxdb ~]$ cat > 11204.env << "EOF"
> export PATH
> export ORACLE_SID=csqxcs
> export ORACLE_BASE=/app/oracle
> export ORACLE_HOME=/app/oracle/product/11.2.0.4/db_1
> export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
> export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
> export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
> export PATH=$ORACLE_HOME/bin/:$PATH
> export PATH=$PATH:/usr/bin:/bin:/usr/bin/x11:/usr/local/bin
> export PATH=$PATH:$ORACLE_BASE/common/oracle/bin
> export ORACLE_TERM=xterm
> export TNS_ADMIN=$ORACLE_HOME/network/admin
> export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/lib64:/lib:$ORACLE_HOME/rdbms/lib
> export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
> EOF
[oracle@qxdb ~]$ . 11204.env
[oracle@qxdb ~]$ echo $ORACLE_HOME
/app/oracle/product/11.2.0.4/db_1
[oracle@qxdb ~]$ echo $ORACLE_SID
csqxcs
[oracle@qxdb ~]$
注意我们升级成功后把11204.env文件替换成.bash_profile，使登录生效
九、数据库升级
9.1 启动到升级状态
把数据库启动到升级状态
[oracle@qxdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 26 16:09:19 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 5.3848E+10 bytes
Fixed Size 2265984 bytes
Variable Size 3.9326E+10 bytes
Database Buffers 1.4496E+10 bytes
Redo Buffers 24682496 bytes
Database mounted.
Database opened.
SQL>
9.2 执行utlu112i.sql脚本
对数据库进行升级前的校验，对ERROR和WARNING进行修复。这里我们对WARNING进行忽略，继续进行升级。
SQL> spool upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 07-26-2025 16:12:13
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: CSQXCS
--> version: 11.2.0.1.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 5229 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 1533 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER MED_DEV has 135 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Sync standby database prior to upgrade.
WARNING: --> Your recycle bin contains 29 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL>
9.3 执行catupgrd.sql脚本
执行该脚本对数据库数据字典等进行版本升级。请耐心等待升级较长时间，可以在关注告警文件是否有异常。
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "<procedure/function name>", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC> a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot
DOC> be run with the Oracle Database Vault option set to TRUE since
DOC> AS SYSDBA connections are restricted.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC> the server without the Database Vault option, and restart the server
DOC> using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if Database Vault is installed in the database but the Oracle
DOC> Label Security option is FALSE. To successfully upgrade Oracle
DOC> Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC> relink the server with the OLS option (but without the Oracle Database
DOC> Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if bootstrap migration is in progress and logminer clients
DOC> require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC> Run utlmmig.sql
DOC> then (if needed)
DOC> restart the database using UPGRADE and
DOC> rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old oracle home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original ORACLE_HOME and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if (1) the old release uses a time
DOC> zone file version newer than the one shipped with the new oracle
DOC> release and (2) the new oracle home has not been patched yet:
DOC>
DOC> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Patch new ORACLE_HOME to the same time zone file version as used
DOC> in the old ORACLE_HOME.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC> size 70M reuse
DOC> extent management local
DOC> segment space management auto
DOC> online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
COMP_TIMESTAMP UPGRD__BGN 2025-07-26 16:16:48 2460883 58608
old 3: NOT IN (&&utlip_tabcol)
new 3: NOT IN (SELECT platform_id FROM registry$database)
No errors.
No errors.
DOC>#######################################################################
DOC>#######################################################################
DOC> The following check_edition_exists procedure may result in this error:
DOC>
DOC> ERROR at line 1:
DOC> ORA-20000: Editioning view exists for non-edition enabled schema
DOC> ORA-06512: at "SYS.CHECK_EDITION_EXISTS", line 21
DOC> ORA-06512: at line 2
DOC>
DOC> if there exists non-edition enabled schemas that have editioning
DOC> views. One of the following corrective actions must be taken before
DOC> the upgrade will proceed.
DOC>
DOC> 1. Drop these editioning views.
DOC> 2. Edition enable the schemas using the alter user statement.
DOC> 3. Replace the editioning views with regular views.
DOC>
DOC> Perform a "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT" and take
DOC> a corrective action described above.
DOC>
DOC> Restriction is for:
DOC> 1) When source database is 11.2.0.1 and is being upgraded to 11.2.0.2.
DOC> 2) To identify the particular schema/views run the pre-upgrade script
DOC> /rdbms/admin/utlu112i.sql in normal mode on the source database.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error and terminate the SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
COMP_TIMESTAMP CATALG_BGN 2025-07-26 16:16:56 2460883 58616
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
SCHEMA_URL VARCHAR2(700)
LOCAL VARCHAR2(3)
SCHEMA XMLTYPE
INT_OBJNAME VARCHAR2(4000)
QUAL_SCHEMA_URL VARCHAR2(767)
HIER_TYPE VARCHAR2(11)
BINARY VARCHAR2(3)
SCHEMA_ID RAW(16)
HIDDEN VARCHAR2(3)
No errors.
DOC>
。。。省略
.
Oracle Database 11.2 Post-Upgrade Status Tool 07-26-2025 16:25:55
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:03:13
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:01:37
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:11
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:04
OLAP Catalog
. VALID 11.2.0.4.0 00:00:14
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:05
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:00:45
Oracle XDK
. VALID 11.2.0.4.0 00:00:13
Oracle Text
. VALID 11.2.0.4.0 00:00:07
Oracle XML Database
. VALID 11.2.0.4.0 00:00:50
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:05
Oracle Multimedia
. VALID 11.2.0.4.0 00:00:44
Spatial
. VALID 11.2.0.4.0 00:00:43
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:02
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:02
Oracle Application Express
. VALID 3.2.1.00.10
Final Actions
. 00:00:04
Total Upgrade Time: 00:09:07
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@qxdb ~]$
十、启动数据库
10.1 数据库启动到可读写状态
[oracle@qxdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 26 16:35:19 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 5.3848E+10 bytes
Fixed Size 2265984 bytes
Variable Size 3.9326E+10 bytes
Database Buffers 1.4496E+10 bytes
Redo Buffers 24682496 bytes
Database mounted.
Database opened.
SQL>
10.2 编译失效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-07-26 16:57:18
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 2025-07-26 16:57:34
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
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 errors 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>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select count(1) from dba_objects where status='INVALID';
COUNT(1)
----------
135
SQL>
10.3 检查安装组件状态
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql . Oracle Database 11.2 Post-Upgrade Status Tool 07-26-2025 17:00:05 . Component Current Version Elapsed Time Name Status Number HH:MM:SS . Oracle Server . VALID 11.2.0.4.0 00:03:13 JServer JAVA Virtual Machine . VALID 11.2.0.4.0 00:01:37 Oracle Workspace Manager . VALID 11.2.0.4.0 00:00:11 OLAP Analytic Workspace . VALID 11.2.0.4.0 00:00:04 OLAP Catalog . VALID 11.2.0.4.0 00:00:14 Oracle OLAP API . VALID 11.2.0.4.0 00:00:05 Oracle Enterprise Manager . VALID 11.2.0.4.0 00:00:45 Oracle XDK . VALID 11.2.0.4.0 00:00:13 Oracle Text . VALID 11.2.0.4.0 00:00:07 Oracle XML Database . VALID 11.2.0.4.0 00:00:50 Oracle Database Java Packages . VALID 11.2.0.4.0 00:00:05 Oracle Multimedia . VALID 11.2.0.4.0 00:00:44 Spatial . VALID 11.2.0.4.0 00:00:43 Oracle Expression Filter . VALID 11.2.0.4.0 00:00:02 Oracle Rules Manager . VALID 11.2.0.4.0 00:00:02 Oracle Application Express . VALID 3.2.1.00.10 Final Actions . 00:00:04 Total Upgrade Time: 00:09:07 PL/SQL procedure successfully completed. SQL>
十一、升级后检查
11.1 确认SPFILE位置
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/11.2.0.4/d
b_1/dbs/spfilecsqxcs.ora
SQL>
11.2 变更compatible参数
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/11.2.0.4/d
b_1/dbs/spfilecsqxcs.ora
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
System altered.
SQL>
11.3 确认数据库版本
SQL> select instance_name,version from v$instance;
INSTANCE_NAME VERSION
---------------- -----------------
csqxcs 11.2.0.4.0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> col product for a20
SQL> col version for a12
SQL> col status for a20
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
-------------------- ------------ --------------------
NLSRTL 11.2.0.4.0 Production
Oracle Database 11g 11.2.0.4.0 64bit Production
Enterprise Edition
PL/SQL 11.2.0.4.0 Production
TNS for Linux: 11.2.0.4.0 Production
SQL>
11.4 控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/csqxcs/control01.ctl
/app/oracle/flash_recovery_area/csqxcs/control02.ctl
SQL>
11.5 数据文件
SQL> set lines 200
SQL> col file_name for a70
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------- ------------------------------
/oradata/csqxcs/users01.dbf USERS
/oradata/csqxcs/undotbs01.dbf UNDOTBS1
/oradata/csqxcs/sysaux01.dbf SYSAUX
/oradata/csqxcs/system01.dbf SYSTEM
/oradata/csqxcs/users02.dbf USERS
/oradata/csqxcs/users03.dbf USERS
/app/oracle/product/11.2.0/dbs/D:appAdministratororadataorclUSER03.dbf USERS
7 rows selected.
SQL>
11.6 归档模式
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oraarch Oldest online log sequence 49401 Next log sequence to archive 49403 Current log sequence 49403 SQL>
11.7 用户状态
SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MED_GKCS OPEN
SPMQXUSER OPEN
MED_DEV OPEN
CMSSELECT OPEN
TSFIN OPEN
CSQXDIS OPEN
TMPUSER OPEN
CMS_DJ OPEN
RPA_MED OPEN
CMSQX_EBS_HN OPEN
DTPCMS OPEN
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CSQX_DSF_SAVE OPEN
DISCSQX OPEN
MED_SELECT OPEN
SJZTUSER OPEN
SYSTEM OPEN
SYS OPEN
SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
APEX_PUBLIC_USER EXPIRED & LOCKED
DIP EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
XS$NULL EXPIRED & LOCKED
ORACLE_OCM EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OWBSYS EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
SYSMAN EXPIRED & LOCKED
ANONYMOUS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ORDDATA EXPIRED & LOCKED
OWBSYS_AUDIT EXPIRED & LOCKED
APEX_030200 EXPIRED & LOCKED
APPQOSSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
FLOWS_FILES EXPIRED & LOCKED
MGMT_VIEW EXPIRED & LOCKED
OUTLN EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
DBSNMP EXPIRED & LOCKED
45 rows selected.
SQL>
十二、重启数据库
由于我们发现这有一个数据文件不太规范这里我们重启数据库移动一下数据文件的位置。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@qxdb ~]$ cp /app/oracle/product/11.2.0/dbs/D:appAdministratororadataorclUSER03.dbf /oradata/csqxcs/users04.dbf
[oracle@qxdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 26 17:13:53 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5.3848E+10 bytes
Fixed Size 2265984 bytes
Variable Size 3.8923E+10 bytes
Database Buffers 1.4898E+10 bytes
Redo Buffers 24682496 bytes
Database mounted.
SQL> alter database rename file '/app/oracle/product/11.2.0/dbs/D:appAdministratororadataorclUSER03.dbf' to '/oradata/csqxcs/users04.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/csqxcs/system01.dbf
/oradata/csqxcs/sysaux01.dbf
/oradata/csqxcs/undotbs01.dbf
/oradata/csqxcs/users01.dbf
/oradata/csqxcs/users02.dbf
/oradata/csqxcs/users03.dbf
/oradata/csqxcs/users04.dbf
7 rows selected.
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@qxdb ~]$
十三、启动监听
[oracle@qxdb ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JUL-2025 17:17:34 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /app/oracle/product/11.2.0.4/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora Log messages written to /app/oracle/diag/tnslsnr/qxdb/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.168.135)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.168.135)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 26-JUL-2025 17:17:34 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora Listener Log File /app/oracle/diag/tnslsnr/qxdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.168.135)(PORT=1521))) Services Summary... Service "csqxcs" has 1 instance(s). Instance "csqxcs", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@qxdb ~]$
至此，我们已经完成了11.2.0.1到11.2.0.4的单机版本数据库升级 ，启动应用服务验证即可。
最后修改时间：2025-07-28 09:57:36
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
