分类目录归档：数据库

随着硬件生命周期,数据库等保要求等,不少客户需要把数据库从11.2.0.4版本升级到19c.对于这样的客户的核心生产库升级,一般考虑到回退情况,大部分会选择异机进行升级,提供一个完整版测试
在源库(11.2.0.4)中做升级之前检查

--触发器检查（禁用和启用脚本生成）
SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' DISABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';


SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' ENABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

--检查时区信息
select * from  v$timezone_file;
select  distinct owner from  dba_tab_columns where 
DATA_TYPE='TIMESTAMP(6) WITH TIME ZONE';

--物化视图刷新检查
SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

--无效index检查
set linesize 400
select owner,index_name from dba_indexes where status in ('INVALID','UNUSABLE') ;
select index_owner,index_name,partition_name,status from dba_ind_partitions where status in ('INVALID','UNUSABLE') ;

--清空审计表和所属表空间检查
truncate table aud$;
SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';

--清理19c中无法升级组件
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@olspreupgrade.sql
@emremove.sql
@catnoamd.sql
@catnoexf.sql
@$ORACLE_HOME/apex/apxremov.sql
@?/rdbms/admin/utlprp.sql 32
select owner,object_type from dba_objects where object_name =upper('htmldb_system');
drop package htmldb_system;
drop public synonym htmldb_system;

--检查数据文件不处于备份状态
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; 

--清空回收站
PURGE DBA_RECYCLEBIN;

--检查 SYS 及 SYSTEM默认表空间
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

--检查组件状态
set pagesize 500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

--记录无效对象
create table system.invalid_obj_10g_beforeup tablespace sysaux as select substr(object_name,1,40) object_name,
substr(owner,1,15) owner, object_type from  dba_objects where status='INVALID' order by owner,object_type;

--禁用Block Change Tracking
SELECT filename, status, bytes FROM v$block_change_tracking;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

--执行 preupgrade 脚本
$ORACLE_HOME/jdk/bin/java -jar /u01/xff/preupgrade.jar FILE TEXT DIR /tmp
@/tmp/preupgrade_fixups.sql

--执行dbupgdiag.sql脚本
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@dbupgdiag.sql

rman备份还原数据库

--在11.2.0.4库备份操作
backup   filesperset = 5 as compressed backupset  database format
  '/u01/rmanback/full_%T_%U.rman';
sql 'alter system archive log current';
sql 'alter system archive log current';
Backup filesperset = 10 as compressed backupset archivelog all format 
  '/u01/rmanback/arch_%T_%U.rman' not backed up delete input;
backup  format '/u01/rmanback/ctl_%T_%U.rman' current controlfile;

--在19c库还原操作
SQL>startup nomount pfile='/tmp/pfile'
RMAN> restore controlfile from '/u01/rmanback/ctl_20251003_0a459rsp_1_1.rman';
RMAN> alter database mount;
RMAN>  catalog start with '/u01/orabak/';
RMAN> restore database;
RMAN> recover database;   --可以增量追加归档
SQL> alter database open resetlogs upgrade;

正式升级操作（19c环境）

startup pfile='/u01/xff/pfile.upgrade'  mount;
alter database open upgrade;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
cd $ORACLE_HOME/bin
./dbupgrade

sqlplus / as sysdba
startup
@?/rdbms/admin/utlusts.sql TEXT
@?/rdbms/admin/utlrp.sql 32
@?/rdbms/admin/utlusts.sql TEXT

set pagesize500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

升级完成后操作

--执行postupgrade_fixups
@/tmp/postupgrade_fixups.sql

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


--升级时区
cd $ORACLE_HOME/rdbms/admin
@utltz_countstats.sql
@utltz_countstar.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

主要日志

--升级操作日志
[oracle@oracledb:/u01/app/oracle/product/19c/db/bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19c/db/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]


/u01/app/oracle/product/19c/db/rdbms/admin/orahome = [/u01/app/oracle/product/19c/db]
/u01/app/oracle/product/19c/db/bin/orabasehome = [/u01/app/oracle/product/19c/db]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/db]

Analyzing file /u01/app/oracle/product/19c/db/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20251003103800]

catcon::set_log_file_base_path: ALL catcon-related output will be written to
  [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd*.log] 
  files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_*.lst]
   files for spool files, if any


Number of Cpus        = 4
Database Name         = orcl
DataBase Version      = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to 
  [/u01/xxx/upgrade20251003103801/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd*.log]
   files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd_*.lst] 
   files for spool files, if any


Log file directory = [/u01/xxx/upgrade20251003103801]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX DV EM MGW ODM OLS RAC WK]
DataBase Version      = 11.2.0.4.0

------------------------------------------------------
Phases [0-107]         Start Time:[2025_10_03 10:38:01]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 30s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 18s
Restart  Phase #:2    [orcl] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 9s
Restart  Phase #:4    [orcl] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:7    Time: 7s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 5s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 4s
Restart  Phase #:8    [orcl] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:70   Time: 9s
Restart  Phase #:10   [orcl] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 25s
Restart  Phase #:12   [orcl] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:95   Time: 2s
Restart  Phase #:14   [orcl] Files:1    Time: 0s
Parallel Phase #:15   [orcl] Files:122  Time: 4s
Restart  Phase #:16   [orcl] Files:1    Time: 0s
Serial   Phase #:17   [orcl] Files:25   Time: 1s
Restart  Phase #:18   [orcl] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:32   Time: 6s
Restart  Phase #:20   [orcl] Files:1    Time: 0s
Serial   Phase #:21   [orcl] Files:3    Time: 4s
Restart  Phase #:22   [orcl] Files:1    Time: 1s
Parallel Phase #:23   [orcl] Files:25   Time: 79s
Restart  Phase #:24   [orcl] Files:1    Time: 0s
Parallel Phase #:25   [orcl] Files:12   Time: 49s
Restart  Phase #:26   [orcl] Files:1    Time: 0s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:4    Time: 1s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 0s
Restart  Phase #:32   [orcl] Files:1    Time: 0s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:297  Time: 9s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 0s
Serial   Phase #:38   [orcl] Files:10   Time: 2s
Restart  Phase #:39   [orcl] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 21s
Restart  Phase #:41   [orcl] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 50s
Restart  Phase #:43   [orcl] Files:1    Time: 1s
Parallel Phase #:44   [orcl] Files:11   Time: 3s
Restart  Phase #:45   [orcl] Files:1    Time: 0s
Parallel Phase #:46   [orcl] Files:3    Time: 0s
Restart  Phase #:47   [orcl] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 5s
Restart  Phase #:49   [orcl] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 8s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 0s
Restart  Phase #:52   [orcl] Files:1    Time: 0s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl] Files:2    Time: 161s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl] Files:1    Time: 0s
Serial   Phase #:56   [orcl] Files:3    Time: 9s
Serial   Phase #:57   [orcl] Files:3    Time: 1s
Parallel Phase #:58   [orcl] Files:10   Time: 1s
Parallel Phase #:59   [orcl] Files:25   Time: 2s
Serial   Phase #:60   [orcl] Files:4    Time: 3s
Serial   Phase #:61   [orcl] Files:1    Time: 0s
Serial   Phase #:62   [orcl] Files:32   Time: 1s
Serial   Phase #:63   [orcl] Files:1    Time: 0s
Parallel Phase #:64   [orcl] Files:6    Time: 4s
Serial   Phase #:65   [orcl] Files:2    Time: 8s
Serial   Phase #:66   [orcl] Files:3    Time: 29s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl] Files:1    Time: 0s
Serial   Phase #:69   [orcl] Files:1    Time: 0s
Parallel Phase #:70   [orcl] Files:2    Time: 14s
Restart  Phase #:71   [orcl] Files:1    Time: 0s
Parallel Phase #:72   [orcl] Files:2    Time: 0s
Serial   Phase #:73   [orcl] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl] Files:1    Time: 0s
Serial   Phase #:76   [orcl] Files:1    Time: 16s
Serial   Phase #:77   [orcl] Files:2    Time: 0s
Restart  Phase #:78   [orcl] Files:1    Time: 0s
Serial   Phase #:79   [orcl] Files:1    Time: 8s
Restart  Phase #:80   [orcl] Files:1    Time: 0s
Parallel Phase #:81   [orcl] Files:3    Time: 10s
Restart  Phase #:82   [orcl] Files:1    Time: 0s
Serial   Phase #:83   [orcl] Files:1    Time: 2s
Restart  Phase #:84   [orcl] Files:1    Time: 0s
Serial   Phase #:85   [orcl] Files:1    Time: 4s
Restart  Phase #:86   [orcl] Files:1    Time: 0s
Parallel Phase #:87   [orcl] Files:4    Time: 28s
Restart  Phase #:88   [orcl] Files:1    Time: 0s
Serial   Phase #:89   [orcl] Files:1    Time: 0s
Restart  Phase #:90   [orcl] Files:1    Time: 0s
Serial   Phase #:91   [orcl] Files:2    Time: 4s
Restart  Phase #:92   [orcl] Files:1    Time: 0s
Serial   Phase #:93   [orcl] Files:1    Time: 0s
Restart  Phase #:94   [orcl] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl] Files:1    Time: 6s
Restart  Phase #:96   [orcl] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl] Files:1    Time: 1s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl] Files:1    Time: 22s
*******************   Migration   ******************
Serial   Phase #:99   [orcl] Files:1    Time: 15s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl] Files:1    Time: 0s
Serial   Phase #:101  [orcl] Files:1    Time: 0s
Serial   Phase #:102  [orcl] Files:1    Time: 35s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl] Files:1    Time: 14s
****************   Summary report   ****************
Serial   Phase #:104  [orcl] Files:1    Time: 0s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl] Files:1    Time: 0s
Serial   Phase #:106  [orcl] Files:1    Time: 0s
Serial   Phase #:107  [orcl] Files:1     Time: 50s

------------------------------------------------------
Phases [0-107]         End Time:[2025_10_03 10:51:23]
------------------------------------------------------

Grand Total Time: 802s 

 LOG FILES: (/u01/xxx/upgrade20251003103801/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/xxx/upgrade20251003103801/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:13m:22s]


--组件状态
SQL>@?/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:53:4
Database Name: ORCL

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

Oracle Server                          UPGRADED     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine           UPGRADED     19.16.0.0.0  00:01:28
Oracle XDK                             UPGRADED     19.16.0.0.0  00:00:16
Oracle Database Java Packages          UPGRADED     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                UPGRADED     19.16.0.0.0  00:00:04
Oracle Text                            UPGRADED     19.16.0.0.0  00:00:18
Oracle Workspace Manager               UPGRADED     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                    UPGRADED     19.16.0.0.0  00:00:58
Oracle Multimedia                      UPGRADED     19.16.0.0.0  00:00:13
Spatial                                UPGRADED     19.16.0.0.0  00:01:11
Oracle OLAP API                        UPGRADED     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:11:54

Database 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.

--编译无效对象
SQL>@?/rdbms/admin/utlrp.sql 32

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2025-10-03 10:53:48
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>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2025-10-03 10:55:35
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

--编译之后组件状态正常
SQL>@?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:55:3
Database Name: ORCL

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

Oracle Server                             VALID     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine              VALID     19.16.0.0.0  00:01:28
Oracle XDK                                VALID     19.16.0.0.0  00:00:16
Oracle Database Java Packages             VALID     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                   VALID     19.16.0.0.0  00:00:04
Oracle Text                               VALID     19.16.0.0.0  00:00:18
Oracle Workspace Manager                  VALID     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                       VALID     19.16.0.0.0  00:00:58
Oracle Multimedia                         VALID     19.16.0.0.0  00:00:13
Spatial                                   VALID     19.16.0.0.0  00:01:11
Oracle OLAP API                           VALID     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12
Post Compile                                                     00:01:47

Total Upgrade Time: 00:13:41

Database 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.

--时区升级之后结果
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


3 rows selected.

具体参考:Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单 (Doc ID 2577572.1)