月归档:八月 2020

数据库升级遭遇ORA-04063: package body “SYS.DBMS_SQLTUNE”

数据库从11.2.0.2升级到11.2.0.4,遇到一下错误

Oracle Database 11.2 Post-Upgrade Status Tool           08-21-2020 20:43:36
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.   ORA-04063: package body "SYS.DBMS_SQLTUNE" 有错误
.   ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_SQLTUNE" 的程序单元
.   ORA-06512: 在 line 8
.                                       INVALID      11.2.0.4.0  00:07:28
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:01:45
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:20
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:35
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:57
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:12
Oracle Enterprise Manager
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 6
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.                                         VALID      11.2.0.4.0  00:00:24
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:16
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:33
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:01:21
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:05
Oracle Multimedia
.   ORA-04063: package body "SYS.DBMS_SQLTUNE" 有错误
.   ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_SQLTUNE" 的程序单元
.   ORA-06512: 在 "SYS.UTL_RECOMP", line 865
.   ORA-06512: 在 "SYS.UTL_RECOMP", line 878
.   ORA-06512: 在 line 1
.                                         VALID      11.2.0.4.0  00:00:53
Spatial
.                                         VALID      11.2.0.4.0  00:03:25
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:08
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:18:38

PL/SQL 过程已成功完成。

查询mos发现类似解决方案

SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'DBMS_STATS';

OWNER                          OBJECT_NAME
                                        OBJECT_TYPE         STATUS
------------------------------ -----------------------------------------------------------------------------------------
--------------------------------------- ------------------- -------
SYS                            DBMS_STATS
                                        PACKAGE             VALID
SYS                            DBMS_STATS
                                        PACKAGE BODY        VALID
PUBLIC                         DBMS_STATS
                                        SYNONYM             VALID

SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'DBMS_SQLTUNE'
  2  /

OWNER                          OBJECT_NAME
                                        OBJECT_TYPE         STATUS
------------------------------ -----------------------------------------------------------------------------------------
--------------------------------------- ------------------- -------
SYS                            DBMS_SQLTUNE
                                        PACKAGE             VALID
SYS                            DBMS_SQLTUNE
                                        PACKAGE BODY        INVALID
PUBLIC                         DBMS_SQLTUNE
                                        SYNONYM             INVALID

SQL> alter package DBMS_SQLTUNE compile body;

警告: 更改的包体带有编译错误。

SQL> show error;
PACKAGE BODY DBMS_SQLTUNE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: 内部错误 [phdite:node kind]
10210/5  PL/SQL: ORA-01775: 同义词的循环链
10210/5  PL/SQL: SQL Statement ignored
13917/7  PL/SQL: Statement ignored
16995/5  PL/SQL: SQL Statement ignored
17060/12 PL/SQL: ORA-01775: 同义词的循环链
17657/7  PL/SQL: Statement ignored
17660/31 PLS-00231: 函数 'SQLERRM' 不能在 SQL 中使用
19718/5  PL/SQL: SQL Statement ignored
19718/12 PL/SQL: ORA-01775: 同义词的循环链
19782/5  PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
19782/17 PLS-00231: 函数 'BUILD_SQL_DETAIL_XML' 不能在 SQL 中使用
SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'XMLCONCAT';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
PUBLIC
XMLCONCAT
SYNONYM             VALID


SQL> drop public synonym XMLCONCAT;

同义词已删除。

SQL>  alter package DBMS_SQLTUNE compile body;

程序包体已变更。

重新跑升级脚本catupgrd.sql,一切正常

Oracle Database 11.2 Post-Upgrade Status Tool           08-21-2020 21:08:31
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:06:44
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:00
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:00
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:00
Oracle OLAP API
.                                         VALID      11.2.0.4.0
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:00:00
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:00
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:00:00
Spatial
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Rules Manager
.                                         VALID      11.2.0.4.0
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:06:51

PL/SQL 过程已成功完成。

参考解决方案:Upgrade From 10G To 11G Fails On Dbms_sqltune : ORA-04063: Package Body “SYS.DBMS_SQLTUNE” Has Errors (Doc ID 1271490.1)
DBMS_STATS , DBMS_SQLTUNE_INTERNAL , DBMS_SQLTUNE Package Can Not be Recompiled during/After Upgrade reporting “ORA-04063″ , “ORA-06508″ . “ORA-06512″ (Doc ID 761961.1)

发表在 Oracle安装升级 | 标签为 , , | 评论关闭

Oracle Recovery Tools恢复MISSING00000文件故障

前几天介绍了自己开发的Oracle Recovery Tools工具,今天有客户遇到故障,数据库由于断电无法正常启动,第三方对其进行了重建控制文件操作,但是有两个数据文件没有加入到重建控制文件脚本中,当数据库被resetlogs方式打开之后出现如下效果
20200808142132


20200808142248

数据库已经被多次resetlogs打开,这两个丢失的文件无法正常加载进去,通过Oracle Recovery Tools进行处理
20200808143542

这里可以明显看到,resetlogs scn和checkpoint scn均不一样,常规方法肯定无法处理.
20200808143640

直接在恢复库只读情况下把文件加入到数据库中
20200808142722

通过Oracle Recovery Tools点击鼠标就可以快速的解决这类问题,省去的大量的bbed处理的烦劳(在win上要去找bbed软件,要去考虑各种偏移量,修改目标值等)

发表在 非常规恢复 | 标签为 , | 评论关闭

重命名sys用户引起数据库启动报ORA-01092 ORA-00600 kokasgi1错误

有客户和我反馈,他们为了防止有人使用sys用户进行登录,对sys用户进行了重命名,具体操作语句为:

update user$ set name='THISISSYS' where user#=0;

然后重启数据库发现数据库无法正常启动,报ORA-01092 ORA-00600 kokasgi1错误

[oracle@ecs-d75e-0618923 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 7 14:40:28 2020

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6881345536 bytes
Fixed Size                  2214056 bytes
Variable Size            4227860312 bytes
Database Buffers         2617245696 bytes
Redo Buffers               34025472 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],[], [], [], [], []
Process ID: 6269
Session ID: 530 Serial number: 3

以前写过处理过类似问题,参见:ORA-600 kokasgi1故障恢复,通过特殊处理,数据库open之后,对其user#=0的用户修改为SYS.

[oracle@ecs-d75e-0618923 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 7 14:52:09 2020

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6881345536 bytes
Fixed Size                  2214056 bytes
Variable Size            4227860312 bytes
Database Buffers         2617245696 bytes
Redo Buffers               34025472 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> select name from user$ where user#=0;

NAME
------------------------------
THISISSYS

SQL> update user$ set name='SYS' where user#=0;

1 row updated.

SQL> commit;

Commit complete.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6881345536 bytes
Fixed Size                  2214056 bytes
Variable Size            4227860312 bytes
Database Buffers         2617245696 bytes
Redo Buffers               34025472 bytes
Database mounted.
Database altered.

至此恢复完成,再次sys是oracle内部默认的超级用户,系统很多调用在程序中写死的sys.,对其用户不能进行重命名操作.

发表在 非常规恢复 | 标签为 , , | 评论关闭