联系：手机/微信(+86 13429648788) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
asm的别名机制带来了一些便利的同时有些时候也引入了一些麻烦,比如某些工具无法很好的识别别名,闲着写了sql直接获取别名和实际数据文件对应关系
直接查询数据文件
ASMCMD> ls -l +MGMT/ora18c/test01.dbf Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE SEP 06 00:00:00 N test01.dbf => +MGMT/ORA18C/DATAFILE/TEST16K.274.1016183943
但是如果太多,这样一个个替换效率太低,通过sql语句实现
获取实际数据文件
联系：手机/微信(+86 13429648788) QQ(107644445)
标题：oracle active dataguard修改密码备库延迟生效
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在oracle dataguard环境中,当主库修改密码之后,备库不会立即生效,需要flush shared pool之后才会生效
主库创建用户并尝试登录
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:24:12 2019 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY SQL> create user xff identified by oracle; User created. SQL> grant dba to xff; Grant succeeded. SQL> conn xff/oracle Connected.
备库登录信息
[oracle@standby ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019 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> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY [oracle@standby ~]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:25:58 2019 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> conn xff/oracle Connected.
主库修改密码
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019 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> alter user xff identified by xff; User altered. SQL> conn xff/xff Connected.
备库尝试登录
SQL> conn xff/xff; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn xff/oracle Connected.
备库原密码可以登录修改之后的密码无法登录
备库刷新shared pool,新密码登录成功
SQL> conn / as sysdba Connected. SQL> alter system flush shared_pool; System altered. SQL> conn xff/oracle ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn xff/xff Connected.
联系：手机/微信(+86 13429648788) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有一个客户恢复请求,6个节点11.2.0.3 RAC,非归档模式,数据量近200T
SQL> recover database; ORA-00279: change 318472018583 generated at 05/04/2019 17:58:05 needed for thread 4 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch4_322810_870181839.dbf ORA-00280: change 318472018583 for thread 4 is in sequence #322810 Wed Aug 28 11:19:55 2019 ALTER DATABASE RECOVER DATABAE Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 14 Seq 552 Reading mem 0 Mem# 0: +REDO/xff/log2.ora Recovery of Online Redo Log: Thread 2 Group 15 Seq 126 Reading mem 0 Mem# 0: +REDO/xff/log3.ora Recovery of Online Redo Log: Thread 3 Group 18 Seq 122 Reading mem 0 Mem# 0: +REDO/xff/log6.ora ORA-279 signalled during: ALTER DATABASE RECOVER database ... Wed Aug 28 11:21:31 2019 ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL
数据库恢复需要thread 4 sequence #322810,查询redo信息
Wed Aug 28 12:40:15 2019 SMON: enabling tx recovery Database Characterset is ZHS16GBK Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_smon_51338.trc (incident=244209): ORA-00600: internal error code, arguments: [4137], [44.47.613406], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_244209/xff1_smon_51338_i244209.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Wed Aug 28 12:40:16 2019 ORACLE Instance xff1 (pid = 26) - Error 600 encountered while recovering transaction (44, 47). Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_smon_51338.trc: ORA-00600: internal error code, arguments: [4137], [44.47.613406], [0], [0], [], [], [], [], [], [], [], [] Wed Aug 28 12:40:20 2019 Exception[type: SIGSEGV,Address not mapped to object][ADDR:0x5122000000C8][PC:0xE1B4D3,ktugru()+87][flags:0x0,count:1] Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_p086_54066.trc (incident=245017): ORA-07445:exception encountered:core dump [ktugru()+87][SIGSEGV][ADDR:0x5122000000C8][Address not mapped to object] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_245017/xff1_p086_54066_i245017.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Aug 28 12:40:20 2019 Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_p000_53873.trc (incident=244305): ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_244305/xff1_p000_53873_i244305.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.
提示undo异常,屏蔽回滚段之后,数据库正常打开没有任何报错信息
Wed Aug 28 12:57:15 2019 SMON: enabling cache recovery Instance recovery: looking for dead threads Instance recovery: lock domain invalid but no dead threads [57676] Successfully onlined Undo Tablespace 22. Undo initialization finished serial:0 start:2386111306 end:2386112316 diff:1010 (10 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK Wed Aug 28 12:57:17 2019 minact-scn: Inst 1 is now the master inc#:2 mmon proc-id:57624 status:0x7 minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 No Resource Manager plan active Starting background process GTX0 Wed Aug 28 12:57:18 2019 GTX0 started with pid=45, OS id=57777 Starting background process RCBG Wed Aug 28 12:57:18 2019 RCBG started with pid=46, OS id=57779 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Wed Aug 28 12:57:19 2019 QMNC started with pid=47, OS id=57788 Completed: ALTER DATABASE OPEN
后续涉及创建新undo,删除老undo并处理一些类似,基本上恢复正常
联系：手机/微信(+86 13429648788) QQ(107644445)
标题：select default$ from col$ where rowid=:1 大量解析
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在一个12.1.0.2的库的awr中发现大量47r1y8yn34jmj语句的解析
联系：手机/微信(+86 13429648788) QQ(107644445)
标题：DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0
alert 日志报错
2019-08-14T11:30:15.112151+08:00 WARNING: too many parse errors, count=546 SQL hash=0x750004bb PARSE ERROR: ospid=11550, error=933 for statement: 2019-08-14T11:30:15.112224+08:00 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 2019-08-14T11:30:15.114628+08:00 ----- PL/SQL Call Stack ----- object line object handle number name 0xd0ba9890 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION 0x870ac548 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK 0x870ac548 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS 0xc91e5518 1 anonymous block WARNING: too many parse errors, count=646 SQL hash=0x750004bb PARSE ERROR: ospid=11550, error=933 for statement: 2019-08-14T11:30:15.298603+08:00 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 2019-08-14T11:30:15.298698+08:00 ----- PL/SQL Call Stack ----- object line object handle number name 0xd0ba9890 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION 0x870ac548 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK 0x870ac548 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS 0xc91e5518 1 anonymous block
这里比较明显由于DELETE FROM wri$_adv_sqlt_rtn_planWHERE这条sql语法不对，导致无法解析因此报了ORA-00933错误,通过人工执行
SQL> exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS(); PL/SQL procedure successfully completed.
后台alert日志重现该错误，证明该程序本身有问题，属于oracle bug范畴，查询mos发现相关Bug 26764561 : ORA-00933 IN SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
 |
|