ORA-600 ktfbhget-4

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-600 ktfbhget-4

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户和我们反馈,他们数据库现在使用异常,查询dba_data_files报ORA-600 ktfbhget-4

*** 2019-09-21 13:56:42.944
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktfbhget-4], [384], [16], [], [], [], [], []
Current SQL statement for this session:
select tablespace_name from dba_data_files
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1028F23AC ?
ksfdmp+0018          bl       01FD7F4C             
kgerinv+00e8         bl       _ptrgl               
kgesinv+0020         bl       kgerinv              000000000 ? 000000000 ?
                                                   FFFFFFFFFFF8920 ? 000400002 ?
                                                   000000000 ?
ksesin+005c          bl       kgesinv              7000001637868E8 ? 110359FD8 ?
                                                   000000100 ? 000007FFF ?
                                                   000007FFF ?
ktfbhget+047c        bl       ksesin               1029B1D18 ? 200000002 ?
                                                   000000000 ? 000000180 ?
                                                   000000000 ? 000000010 ?
                                                   000000008 ? 000000000 ?
ktfbhcf+03c0         bl       ktfbhget             FFFFFFFFFFF8DA0 ? 1103589D8 ?
                                                   201035A57C ? 1C610005A40 ?
qerfxFetch+0c94      bl       01FD7AC4             
qerjoFetch+037c      bl       _ptrgl               
rwsfcd+0060          bl       _ptrgl               
qersoFetch+0108      bl       _ptrgl               
qerjoFetch+037c      bl       _ptrgl               
qerjoFetch+037c      bl       _ptrgl               
rwsfcd+0060          bl       _ptrgl               
qeruaFetch+0140      bl       01FD7AC4             
qervwFetch+008c      bl       01FD7AC4             
opifch2+0c68         bl       01FD7AC4             
opiall0+1244         bl       opifch2              7000001626B2F28 ? 100000001 ?
                                                   FFFFFFFFFFFA3D8 ?
kpoal8+0a68          bl       opiall0              5E1000C818 ? 2200000014 ?
                                                   FFFFFFFFFFFAAD8 ? 000000000 ?
                                                   FFFFFFFFFFFAA28 ? 11028C2C8 ?
                                                   080000000 ? 4000000007FFF ?
opiodr+08e8          bl       _ptrgl               
ttcpip+0c54          bl       _ptrgl               
opitsk+0c28          bl       ttcpip               11000C818 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0798          bl       opitsk               000000000 ? 000000000 ?
opiodr+08e8          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101F5E80 ?
                                                   FFFFFFFFFFFF4C0 ? 0A000FD98 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 400000000 ?
                                                   FFFFFFFFFFFF4C0 ?
main+0138            bl       01FD758C             
__start+0098         bl       main                 000000000 ? 000000000 ?

通过询问知道由于file#=17最初由于裸设备权限异常,导致无法访问,执行了delete from file$ where file#=17,然后遭遇异常又人工插入了一条file#=17的记录到file$中,但是由于不知道具体值,可能是由于部分值插入错误引起现在问题.
20190927225858


通过分析file$表定义

create table file$                                             /* file table */
( file#         number not null,                   /* file identifier number */
  status$       number not null,                      /* status (see KTS.H): */
                                               /* 1 = INVALID, 2 = AVAILABLE */
  blocks        number not null,                   /* size of file in blocks */
                                           /* zero for bitmapped tablespaces */
  ts#           number,                         /* tablespace that owns file */
  relfile#      number,                              /* relative file number */
  maxextend     number,                                 /* maximum file size */
  inc           number,                                  /* increment amount */
  crscnwrp      number,                                 /* creation SCN wrap */
  crscnbas      number,                                 /* creation SCN base */
  ownerinstance varchar("M_IDEN"),                    /* Owner instance name */
  spare1        number,      /* tablespace-relative DBA of space file header */
                                   /* NULL for dictionary-mapped tablespaces */
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)

通过dump文件头(文件创建大小/SCN等),结合一些计算和经验值,获取到相关值重新插入正确值,一切恢复正常

delete from file$ where file#=17 and ts#=384;
insert into sys.file$ (FILE#, STATUS$, BLOCKS, TS#, RELFILE#, MAXEXTEND, INC,
 CRSCNWRP, CRSCNBAS, OWNERINSTANCE, SPARE1, SPARE2, SPARE3, SPARE4)
values (17,2,256000,384,17,0,0,3087,1631091037,null,71303170, null, null, null);
commit;

再次提醒,file$中记录请勿人工修改,以前写过相关casefile$ 删除记录恢复(delete file$ recovery)

rman备份到win共享目录

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:rman备份到win共享目录

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在win环境中数据库备份到异地,相对来说没有linux的nfs方便(可能nfs使用多了比较熟悉),以前写过一篇文章(windows rman自动备份并传输到远程服务器处理方法),通过本地备份,然后拷贝到远程共享目录实现,相对来说该方案比较繁琐,这次尝试直接备份到共享目录
1. 服务配置
20190926221926


oracle数据库服务和监听服务配置使用此账户的方式登录(而不是默认的本地系统账号)

2.在目标服务器中配置共享
20190926222029

主要两台win服务器登录用户名和密码需要一致,最好也是数据库安装用户

3.数据库备份脚本
20190926222130

备份脚本路径使用\\方式而不能使用别名盘符

4.数据库备份计划任务
20190926222951

运行任务时请使用下列用户选择ORA_DBA

通过sql获取asm别名实际文件名

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:通过sql获取asm别名实际文件名

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

asm的别名机制带来了一些便利的同时有些时候也引入了一些麻烦,比如某些工具无法很好的识别别名,闲着写了sql直接获取别名和实际数据文件对应关系
直接查询数据文件
1-3


通过asmcmd中的ls命令查看

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语句实现

获取实际数据文件
1-2


通过sql语句快速实现把数据文件路径中的别名转换为实际存储路径(omf方式存储)

oracle active dataguard修改密码备库延迟生效

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题: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.