分类目录归档：Oracle

update user$报ORA-01031错误

发表于 2023 年 4 月 11 日由惜分飞

在oracle的有些版本中,为了安全不允许用户直接update USER$表

[oracle@dbserver1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 11 12:51:55 2023
Version 19.16.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI';
UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI'
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> show user; 
USER is "SYS"
SQL> create table t_user$ as select * from user$;

Table created.

SQL> UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI';
UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI'
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create user test identified by oracle;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/oracle
Connected.

SQL> update sys.user$ SET NAME='XFF' WHERE NAME='XIFENFEI';
update sys.user$ SET NAME='XFF' WHERE NAME='XIFENFEI'
           *
ERROR at line 1:
ORA-01031: insufficient privileges

官方说明:CANNOT UPDATE SYS.USER$ AS SYS AFTER APPLYING OCTOBER 2020 PSU/RU, ORA-01031 IS OBTAINED (Doc ID 2746319.1)

发表在 Oracle | 标签为 update user$ ORA-01031 | 评论关闭

删除ora.asmgroup资源offline记录

发表于 2023 年 4 月 11 日由惜分飞

采用了fix asm之后,查看集群状态的时候会有一个ora.asmgroup相关是offline状态,可以通过srvctl modify asm -count 2命令强制把asm count设置为2从而就不会有offline的资源存在

[grid@dbserver1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.chad
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.net1.network
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.ons
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      dbserver1                STABLE
               OFFLINE OFFLINE      dbserver2                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.SYSDG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                Started,STABLE
      2        ONLINE  ONLINE       dbserver2                Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver2.vip
      1        ONLINE  ONLINE       dbserver2                STABLE
ora.xff.db
      1        ONLINE  ONLINE       dbserver1                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       dbserver2                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
--------------------------------------------------------------------------------
[grid@dbserver1 ~]$ srvctl modify asm -count 2
[grid@dbserver1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.chad
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.net1.network
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.ons
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      dbserver1                STABLE
               OFFLINE OFFLINE      dbserver2                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.SYSDG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                Started,STABLE
      2        ONLINE  ONLINE       dbserver2                Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.cvu
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver2.vip
      1        ONLINE  ONLINE       dbserver2                STABLE
ora.xff.db
      1        ONLINE  ONLINE       dbserver1                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       dbserver2                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
--------------------------------------------------------------------------------
[grid@dbserver1 ~]$

发表在 Oracle RAC | 标签为 ora.asmgroup offline删除 | 评论关闭

清理类似SYS$SYS.KUPC$C_2_20230411115109_0服务

发表于 2023 年 4 月 11 日由惜分飞

查看监听发现大量类似SYS$SYS.KUPC$C_2_20230411115109_0 服务

[grid@dbserver1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2023 13:05:47

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-SEP-2022 23:57:17
Uptime                    215 days 13 hr. 8 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/19c/grid/network/log/listener_scan1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.9.9)(PORT=11521)))
Services Summary...
Service "SYS$SYS.KUPC$C_2_20230411115109_0.XFF" has 1 instance(s).
  Instance "elcdb2", status READY, has 1 handler(s) for this service...
Service "SYS$SYS.KUPC$S_2_20230411115109_0.XFF" has 1 instance(s).
  Instance "elcdb2", status READY, has 1 handler(s) for this service...
Service "elcdb" has 2 instance(s).
  Instance "elcdb1", status READY, has 1 handler(s) for this service...
  Instance "elcdb2", status READY, has 1 handler(s) for this service...
Service "elcdbXDB" has 2 instance(s).
  Instance "elcdb1", status READY, has 1 handler(s) for this service...
  Instance "elcdb2", status READY, has 1 handler(s) for this service...

清理这种服务

SQL> select 'exec DBMS_AQADM.STOP_QUEUE(queue_name=>'''||name||''');' as reservice from v$services where name like '%KUPC%';

RESERVICE
--------------------------------------------------------------------------------
exec DBMS_AQADM.STOP_QUEUE(queue_name=>'SYS.KUPC$C_2_20230411115109_0');
exec DBMS_AQADM.STOP_QUEUE(queue_name=>'SYS.KUPC$S_2_20230411115109_0');

SQL> exec DBMS_AQADM.STOP_QUEUE(queue_name=>'SYS.KUPC$C_2_20230411115109_0');
exec DBMS_AQADM.STOP_QUEUE(queue_name=>'SYS.KUPC$S_2_20230411115109_0');

PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
SQL> select 'exec DBMS_AQADM.DROP_QUEUE(queue_name=>'''||name||''');' as reservice from v$services where name like '%KUPC%';

RESERVICE
--------------------------------------------------------------------------------
exec DBMS_AQADM.DROP_QUEUE(queue_name=>'SYS.KUPC$C_2_20230411115109_0');
exec DBMS_AQADM.DROP_QUEUE(queue_name=>'SYS.KUPC$S_2_20230411115109_0');

SQL> 
SQL> exec DBMS_AQADM.DROP_QUEUE(queue_name=>'SYS.KUPC$C_2_20230411115109_0');
exec DBMS_AQADM.DROP_QUEUE(queue_name=>'SYS.KUPC$S_2_20230411115109_0');
PL/SQL procedure successfully completed.

SQL> 

PL/SQL procedure successfully completed.

SQL>

再次查看服务确认已经被清理

[grid@dbserver1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-APR-2023 13:08:37

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-SEP-2022 23:57:17
Uptime                    215 days 13 hr. 11 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/19c/grid/network/log/listener_scan1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.9.9)(PORT=11521)))
Services Summary...
Service "elcdb" has 2 instance(s).
  Instance "elcdb1", status READY, has 1 handler(s) for this service...
  Instance "elcdb2", status READY, has 1 handler(s) for this service...
Service "elcdbXDB" has 2 instance(s).
  Instance "elcdb1", status READY, has 1 handler(s) for this service...
  Instance "elcdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@dbserver1 ~]$

发表在 Oracle | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

update user$报ORA-01031错误

删除ora.asmgroup资源offline记录

清理类似SYS$SYS.KUPC$C_2_20230411115109_0服务

文章分类

最近发表

分类目录归档：Oracle

update user$报ORA-01031错误

删除ora.asmgroup资源offline记录

清理类似SYS$SYS.KUPC$C_2_20230411115109_0服务

标签云

文章分类

最近发表