暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 关于使用DBUA对oracle11.2.0.4升级19c中断的后续处理

关于使用DBUA对oracle11.2.0.4升级19c中断的后续处理

原创 敖子🍖 2024-10-18
253

使用DBUA对Oracle11.2.0.4进行19c升级时进程异常中断的后续处理，在dbua进程进行数据库upgrade升级的时候，DBUA工具因为突然异常退出导致升级程序中断,很多步骤没有跑完，然后sqlplus /  as sysdba进入空闲实例启动数据库的时候提示报错

SQL> startup
ORACLE instance started.

Total System Global Area 1593831936 bytes
Fixed Size 8897024 bytes
Variable Size 922746880 bytes
Database Buffers 654311424 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 15589
Session ID: 4 Serial number: 39325



并且trc文件提示

*** 2024-10-16T14:00:53.022277+08:00
*** SESSION ID:(4.39325) 2024-10-16T14:00:53.022367+08:00
*** CLIENT ID:() 2024-10-16T14:00:53.022450+08:00
*** SERVICE NAME:(SYS$USERS) 2024-10-16T14:00:53.022489+08:00
*** MODULE NAME:(sqlplus@oracle11g (TNS V1-V3)) 2024-10-16T14:00:53.022525+08:00
*** ACTION NAME:() 2024-10-16T14:00:53.022561+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2024-10-16T14:00:53.022595+08:00

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
<error barrier> at 0x7ffd1d700e18 placed dbsdrv.c@4959
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
<error barrier> at 0x7ffd1d700e18 placed dbsdrv.c@4959
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier

*** 2024-10-16T14:00:54.142242+08:00
2024-10-16T14:00:54.141906+08:00
Incident 253556 created, dump file: /u01/app/oracle/diag/rdbms/oracle11g/oracle11g/incident/incdir_253556/oracle11g_ora_15589_i253556.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier

kgefec: fatal error 1092

*** 2024-10-16T14:00:58.757459+08:00
USER(prelim) (ospid: 15589): terminating the instance due to ORA error 704


通过mos文档知道大致流程

原数据库的版本是11.2.0.4，现在目标数据库的版本是升级的19.0.0.0，需要对其进行使用并行升级实用程序catctl.pl来调用catupgrd.sql进行补充升级

SQL> startup mount

ORACLE instance started.

Total System Global Area 1593831936 bytes
Fixed Size 8897024 bytes
Variable Size 922746880 bytes
Database Buffers 654311424 bytes
Redo Buffers 7876608 bytes
Database mounted.

打开为upgrade模式

SQL> alter database open upgrade;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE


执行catupgrd.sql进行补充升级

su - ooracle

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql


等待漫长的运行时间，将近五个小时


执行完毕后关闭数据库重新启动open，做升级后续操作

1.执行dbupgdiag.sql检查19C数据库情况

su - oracle

sqlplus / as sysdba

SQL> @/u01/app/oracle/cfgtoollogs/oracle11g/preupgrade/postupgrade_fixups.sql

Session altered.


需要根据脚本执行的结果提示待手动配置的升级项

2.检查数据字典

2.1检查数据字典状态

 su - oracle

sqlplus / as sysdba

spool /tmp/regInvalid.out
set echo on
-- query registry
set lines 80 pages 100
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)
comp_name,substr(version,1,10) version,status
from dba_registry order by modified;

2.2更新数据字典

$ORACLE_HOME/rdbms/admin/catproc.sql       //运行服务器端所需要的PL/SQL脚本
$ORACLE_HOME/rdbms/admin/utlrp.sql           //刷新数据库其他功能的表和视图，编译无效对象


3.修改COMPATIBLE参数

SQL> show parameter COMPATIBLE;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
noncdb_compatible boolean FALSE

SQL>ALTER SYSTEM SET COMPATIBLE="19.0.0" SCOPE=SPFILE;

shutdown immediate
startup



4.Time Zone更新

SQL> SELECT * FROM v$timezone_file;

FILENAME                  VERSION       CON_ID
-------------------- ---------- ----------
timezlrg_14.dat           14                0

等于14：这已经是11g需要的版本，但是需要升级到19c环境，version=32

4.1升级Time Zone

查找时区文件

find $ORACLE_HOME -name 'zoneinfo'

cd /u01/app/oracle/product/19.3.0/dbhome_1/oracore/zoneinfo

 ll
total 33312
drwxr-xr-x 2 oracle oinstall 4096 Apr 17 2019 big
drwxr-xr-x 2 oracle oinstall 4096 Apr 17 2019 little
-rw-r--r-- 1 oracle oinstall 59574 Jun 14 2018 readme.txt
-rw-r--r-- 1 oracle oinstall 52931 Jun 14 2018 timezdif.csv
-rw-r--r-- 1 oracle oinstall 792894 Jun 18 2014 timezlrg_10.dat
-rw-r--r-- 1 oracle oinstall 787272 Aug 10 2016 timezlrg_11.dat
-rw-r--r-- 1 oracle oinstall 785621 Aug 10 2016 timezlrg_12.dat
-rw-r--r-- 1 oracle oinstall 782475 Aug 10 2016 timezlrg_13.dat
-rw-r--r-- 1 oracle oinstall 791430 Dec 18 2012 timezlrg_14.dat
-rw-r--r-- 1 oracle oinstall 791476 Aug 10 2016 timezlrg_15.dat
-rw-r--r-- 1 oracle oinstall 800913 Aug 10 2016 timezlrg_16.dat
-rw-r--r-- 1 oracle oinstall 779003 Aug 10 2016 timezlrg_17.dat
-rw-r--r-- 1 oracle oinstall 781669 Jun 22 2016 timezlrg_18.dat
-rw-r--r-- 1 oracle oinstall 785786 Aug 10 2016 timezlrg_19.dat
-rw-r--r-- 1 oracle oinstall 493675 Aug 10 2016 timezlrg_1.dat
-rw-r--r-- 1 oracle oinstall 789973 Aug 10 2016 timezlrg_20.dat
-rw-r--r-- 1 oracle oinstall 782517 Aug 10 2016 timezlrg_21.dat
-rw-r--r-- 1 oracle oinstall 784497 Aug 10 2016 timezlrg_22.dat
-rw-r--r-- 1 oracle oinstall 788897 Aug 10 2016 timezlrg_23.dat
-rw-r--r-- 1 oracle oinstall 777086 Aug 10 2016 timezlrg_24.dat
-rw-r--r-- 1 oracle oinstall 780468 Jun 22 2016 timezlrg_25.dat
-rw-r--r-- 1 oracle oinstall 785474 Aug 10 2016 timezlrg_26.dat
-rw-r--r-- 1 oracle oinstall 785408 Jul 25 2016 timezlrg_27.dat
-rw-r--r-- 1 oracle oinstall 782585 Sep 28 2016 timezlrg_28.dat
-rw-r--r-- 1 oracle oinstall 788462 Dec 5 2016 timezlrg_29.dat
-rw-r--r-- 1 oracle oinstall 507957 Aug 10 2016 timezlrg_2.dat
-rw-r--r-- 1 oracle oinstall 785841 May 3 2017 timezlrg_30.dat
-rw-r--r-- 1 oracle oinstall 786708 Nov 6 2017 timezlrg_31.dat
-rw-r--r-- 1 oracle oinstall 786909 Jun 20 2018 timezlrg_32.dat
-rw-r--r-- 1 oracle oinstall 527717 Aug 10 2016 timezlrg_3.dat
-rw-r--r-- 1 oracle oinstall 531137 Aug 10 2016 timezlrg_4.dat
-rw-r--r-- 1 oracle oinstall 587487 Aug 10 2016 timezlrg_5.dat
-rw-r--r-- 1 oracle oinstall 586750 Aug 10 2016 timezlrg_6.dat
-rw-r--r-- 1 oracle oinstall 601242 Aug 10 2016 timezlrg_7.dat
-rw-r--r-- 1 oracle oinstall 616723 Dec 9 2015 timezlrg_8.dat
-rw-r--r-- 1 oracle oinstall 801410 Aug 10 2016 timezlrg_9.dat
-rw-r--r-- 1 oracle oinstall 345637 Jun 22 2016 timezone_10.dat
-rw-r--r-- 1 oracle oinstall 345356 Dec 9 2015 timezone_11.dat
-rw-r--r-- 1 oracle oinstall 345024 Aug 10 2016 timezone_12.dat
-rw-r--r-- 1 oracle oinstall 344425 Dec 18 2012 timezone_13.dat
-rw-r--r-- 1 oracle oinstall 344448 Dec 9 2015 timezone_14.dat
-rw-r--r-- 1 oracle oinstall 344448 Aug 10 2016 timezone_15.dat
-rw-r--r-- 1 oracle oinstall 343044 Aug 10 2016 timezone_16.dat
-rw-r--r-- 1 oracle oinstall 341718 Dec 9 2015 timezone_17.dat
-rw-r--r-- 1 oracle oinstall 341718 Aug 10 2016 timezone_18.dat
-rw-r--r-- 1 oracle oinstall 343167 Aug 10 2016 timezone_19.dat
-rw-r--r-- 1 oracle oinstall 274427 Aug 10 2016 timezone_1.dat
-rw-r--r-- 1 oracle oinstall 343167 Aug 10 2016 timezone_20.dat
-rw-r--r-- 1 oracle oinstall 343249 Aug 10 2016 timezone_21.dat
-rw-r--r-- 1 oracle oinstall 343594 Jun 22 2016 timezone_22.dat
-rw-r--r-- 1 oracle oinstall 343768 Aug 10 2016 timezone_23.dat
-rw-r--r-- 1 oracle oinstall 340192 Aug 10 2016 timezone_24.dat
-rw-r--r-- 1 oracle oinstall 340197 Aug 10 2016 timezone_25.dat
-rw-r--r-- 1 oracle oinstall 342602 Aug 10 2016 timezone_26.dat
-rw-r--r-- 1 oracle oinstall 342602 Jul 25 2016 timezone_27.dat
-rw-r--r-- 1 oracle oinstall 341401 Sep 28 2016 timezone_28.dat
-rw-r--r-- 1 oracle oinstall 341401 Dec 5 2016 timezone_29.dat
-rw-r--r-- 1 oracle oinstall 274900 Dec 18 2012 timezone_2.dat
-rw-r--r-- 1 oracle oinstall 340884 May 3 2017 timezone_30.dat
-rw-r--r-- 1 oracle oinstall 340892 Nov 6 2017 timezone_31.dat
-rw-r--r-- 1 oracle oinstall 340869 Jun 20 2018 timezone_32.dat
-rw-r--r-- 1 oracle oinstall 286651 Aug 10 2016 timezone_3.dat
-rw-r--r-- 1 oracle oinstall 286264 Dec 18 2012 timezone_4.dat
-rw-r--r-- 1 oracle oinstall 286310 Aug 10 2016 timezone_5.dat
-rw-r--r-- 1 oracle oinstall 286217 Aug 10 2016 timezone_6.dat
-rw-r--r-- 1 oracle oinstall 286815 Aug 10 2016 timezone_7.dat
-rw-r--r-- 1 oracle oinstall 302100 Dec 9 2015 timezone_8.dat
-rw-r--r-- 1 oracle oinstall 351525 Aug 10 2016 timezone_9.dat

如果没有timezone_32.dat,需要对其进行补丁


4.2 时区升级准备

SQL> exec DBMS_DST.BEGIN_PREPARE(32);

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME
----------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
32

DST_UPGRADE_STATE
PREPARE


BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
6 END;
7
8 /

PL/SQL procedure successfully completed.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME
----------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


4.3Time Zone升级

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1593831936 bytes
Fixed Size 8897024 bytes
Variable Size 939524096 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE

SQL> set serveroutput on
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> alter session set "_with_subquery"=materialize;
Session altered.


将timezone version升级到32：
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(32);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
2 3 4 ORDER BY PROPERTY_NAME;

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

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
UPGRADE


SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER
--------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME UPG
-------------------------------------------------------------------------------------------------------------------------------- ---
GSMADMIN_INTERNAL
AQ$_CHANGE_LOG_QUEUE_TABLE_L YES

MDSYS
SDO_DIAG_MESSAGES_TABLE YES

GSMADMIN_INTERNAL
AQ$_CHANGE_LOG_QUEUE_TABLE_S YES


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1593831936 bytes
Fixed Size 8897024 bytes
Variable Size 939524096 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> alter session set "_with_subquery"=materialize;
Session altered.

执行timezone升级：
SQL> set serveroutput on
SQL> VAR numfail number

BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
2 3 4 5 6 7 8 error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
11 /
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
2 3 4 5 /
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

确认升级成功：
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
2 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


SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0

SQL>


5.监听配置

5.1 配置监听

cp /u01/app/oracle/product/11.2.0/db_1/network/admin/*  /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/    #把11g目录下的监听文件cp到19c路径


lsnrctl stop

cd /u01/app/oracle/product

rm -rf 11.2.0/       #删除原11g oracle_home目录

export DISPLAY=192.168.171.1:0.0

netca       #图形化配置监听

删除原11g LISTENER 新增19c LISTENER 我这里监听名还是LISTENER 端口还是为1521

SQL> show parameter local_listener;

NAME TYPE                             VALUE
------------------------------------ ------
local_listener                            string


SQL> alter system set local_listener='' scope=both;      #将LOCAL_LISTENER设置为空，动态监听默认注册1521端口。

System altered.

lsnrctl start 或 lsnrctl reload


服务名 实例名等未作修改，所以还是为oracle11g ，后续可以做修改

5.2 配置数据库和监听自启动(可选)

修改oratab文件

vi /etc/oratab

oracle11g:/u01/app/oracle/product/19.3.0/dbhome_1:N   #将N修改为Y

oracle11g:/u01/app/oracle/product/19.3.0/dbhome_1:Y


修改 /etc/rc.local文件

su - root

vi /etc/rc.local

touch /var/lock/subsys/local

su oracle -lc "/u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl start"
su oracle -lc /u01/app/oracle/product/19.3.0/dbhome_1/bin/dbstart


修改dbstart文件

su - oracle

vi $ORACLE_HOME/bin/dbstart

ORACLE_HOME_LISTNER=$1--------->改为ORACLE_HOME_LISTNER=$ORACLE_HOME

reboot 重启操作系统测试




6.数据测试

升级前的用户，表空间 ，表，视图等数据完好存在



所有操作暂时结束~目前使用正常，如果有一些未发现的问题，在做处理。over~

oracle墨力计划dbua
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论