标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,770)
- DB2 (22)
- MySQL (77)
- Oracle (1,611)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (592)
- Oracle安装升级 (98)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (39)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (22)
-
最近发表
- Oracle 19c 202507补丁(RUs+OJVM)-19.28
- 2025年的Oracle 8.0.5数据库恢复
- ORA-600 kokiasg1故障分析(obj$中核心字典序列全部被恶意删除)
- ORA-00756 ORA-10567故障数据0丢失恢复
- 数据库文件变成32k故障恢复
- tcp连接过多导致监听TNS-12532 TNS-12560 TNS-00502错误
- 文件系统格式化MySQL数据库恢复
- .sstop勒索加密数据库恢复
- 解决一次硬件恢复之后数据文件0kb的故障恢复case
- Error in invoking target ‘libasmclntsh19.ohso libasmperl19.ohso client_sharedlib’问题处理
- ORA-01171: datafile N going offline due to error advancing checkpoint
- linux环境oracle数据库被文件系统勒索加密为.babyk扩展名溯源
- ORA-600 ksvworkmsgalloc: bad reaper
- ORA-600 krccfl_chunk故障处理
- Oracle Recovery Tools恢复案例总结—202505
- ORA-600 kddummy_blkchk 数据库循环重启
- 记录一次asm disk加入到vg通过恢复直接open库的案例
- CHECKDB 发现了 N 个分配错误和 M 个一致性错误
- 达梦数据库dm.ctl文件异常恢复
- Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
分类目录归档:ORA-xxxxx
ORA-00600[4194]故障解决
朋友数据库因为断电,导致数据库正常启动片刻之后,自动down掉
一、alert日志
Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.1.0. System parameters with non-default values: processes = 150 __shared_pool_size = 58720256 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 4194304 nls_date_format = yyyy-mm-dd hh24:mi:ss sga_target = 335544320 control_files = /u02/ezhou/control01.ctl db_block_size = 8192 compatible = 10.2.0.1.0 log_archive_dest = /u02/arch log_archive_max_processes= 10 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=ezhouXDB) job_queue_processes = 10 background_dump_dest = /u01/pp/oracle/admin/ezhou/bdump user_dump_dest = /u01/pp/oracle/admin/ezhou/udump core_dump_dest = /u01/pp/oracle/admin/ezhou/cdump audit_file_dest = /u01/pp/oracle/admin/ezhou/adump db_name = ezhou open_cursors = 400 sql_trace = TRUE pga_aggregate_target = 94371840 MMAN started with pid=4, OS id=5539 PMON started with pid=2, OS id=5535 DBW0 started with pid=5, OS id=5541 LGWR started with pid=6, OS id=5543 SMON started with pid=8, OS id=5547 CJQ0 started with pid=10, OS id=5577 RECO started with pid=9, OS id=5575 Sat Dec 10 17:15:40 2011 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))' MMNL started with pid=12, OS id=5581 MMON started with pid=11, OS id=5579 Sat Dec 10 17:15:40 2011 starting up 1 shared server(s) ... PSP0 started with pid=3, OS id=5537 CKPT started with pid=7, OS id=5545 Sat Dec 10 17:15:42 2011 ALTER DATABASE MOUNT Sat Dec 10 17:15:46 2011 Setting recovery target incarnation to 3 Sat Dec 10 17:15:47 2011 Successful mount of redo thread 1, with mount id 4055654398 Sat Dec 10 17:15:47 2011 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Sat Dec 10 17:15:47 2011 ALTER DATABASE OPEN Sat Dec 10 17:15:47 2011 Beginning crash recovery of 1 threads Sat Dec 10 17:15:47 2011 Started redo scan Sat Dec 10 17:15:48 2011 Completed redo scan 319 redo blocks read, 98 data blocks need recovery Sat Dec 10 17:15:50 2011 Started redo application at Thread 1: logseq 24, block 3 Sat Dec 10 17:15:50 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo03.log Sat Dec 10 17:15:50 2011 Completed redo application Sat Dec 10 17:15:51 2011 Completed crash recovery at Thread 1: logseq 24, block 322, scn 6168722 98 data blocks read, 98 data blocks written, 319 redo blocks read Sat Dec 10 17:15:51 2011 LGWR: STARTING ARCH PROCESSES ARC1 started with pid=17, OS id=5645 ARC0 started with pid=16, OS id=5643 ARC3 started with pid=19, OS id=5649 ARC4 started with pid=20, OS id=5651 ARC2 started with pid=18, OS id=5647 ARC6 started with pid=22, OS id=5655 ARC7 started with pid=23, OS id=5657 ARC5 started with pid=21, OS id=5653 ARC8 started with pid=24, OS id=5659 Sat Dec 10 17:15:52 2011 ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC3: Archival started ARC4: Archival started ARC5: Archival started ARC6: Archival started ARC7: Archival started ARC8: Archival started ARC9: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC9 started with pid=25, OS id=5661 Sat Dec 10 17:15:52 2011 Thread 1 advanced to log sequence 25 Sat Dec 10 17:15:53 2011 ARC2: STARTING ARCH PROCESSES Sat Dec 10 17:15:53 2011 ARC6: Becoming the 'no FAL' ARCH ARC6: Becoming the 'no SRL' ARCH Sat Dec 10 17:15:53 2011 ARC3: Becoming the heartbeat ARCH Sat Dec 10 17:15:53 2011 Thread 1 opened at log sequence 25 Current log# 1 seq# 25 mem# 0: /u02/ezhou/redo01.log Current log# 1 seq# 25 mem# 1: /u02/ezhou/redo01a.rdo Successful open of redo thread 1 Sat Dec 10 17:15:53 2011 SMON: enabling cache recovery Sat Dec 10 17:15:54 2011 ARCa: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE ARCa started with pid=26, OS id=5663 Sat Dec 10 17:15:57 2011 Successfully onlined Undo Tablespace 1. Sat Dec 10 17:15:57 2011 SMON: enabling tx recovery Sat Dec 10 17:15:57 2011 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=27, OS id=5666 Sat Dec 10 17:16:13 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:17 2011 Completed: ALTER DATABASE OPEN Sat Dec 10 17:16:27 2011 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:27 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery stopped at EOT rba 25.126.16 Block recovery completed at rba 25.126.16, scn 0.6168829 Doing block recovery for file 2 block 73 Block recovery from logseq 25, block 68 to scn 6168786 Sat Dec 10 17:16:28 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.69.16, scn 0.6168789 Sat Dec 10 17:16:28 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc: ORA-01595: error freeing extent (2) of rollback segment (5)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:30 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:37 2011 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:37 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Doing block recovery for file 2 block 73 Block recovery from logseq 25, block 68 to scn 6168841 Sat Dec 10 17:16:37 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.149.16, scn 0.6168843 Sat Dec 10 17:16:37 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc: ORA-12012: error on auto execute of job 8886 ORA-00607: Internal error occurred while making a change to a data block Sat Dec 10 17:16:41 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:42 2011 DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:42 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Sat Dec 10 17:16:43 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:46 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:17:46 2011 DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:17:46 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Sat Dec 10 17:17:48 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:17:49 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] PMON: terminating instance due to error 472 Instance terminated by PMON, pid = 5535
二、MOS记录
ERROR: ORA-600 [4194] [a] [b] VERSIONS: versions 6.0 to 10.1 DESCRIPTION: A mismatch has been detected between Redo records and rollback (Undo) records. We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. ARGUMENTS: Arg [a] Maximum Undo record number in Undo block Arg [b] Undo record number from Redo block
三、解决办法
1、修改参数
undo_management= MANUAL
undo_tablespace= SYSTEM
2、打开数据库,删除当前undo空间,重建新undo空间
3、修改参数
undo_management= AUTO
undo_tablespace= UNDOTBSNEW
4、重新启动数据库
创建视图提示ORA-01031
1、重现问题
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select table_name from user_tables where table_name in('X_T','DEPT'); TABLE_NAME ------------------------------ X_T SQL> create view v_xff1 2 as 3 select * from X_T; View created. SQL> create view V_XFF AS 2 SELECT * FROM SCOTT.DEPT; SELECT * FROM SCOTT.DEPT * ERROR at line 2: ORA-01031: insufficient privileges SQL> SELECT COUNT(*) FROM SCOTT.DEPT; COUNT(*) ---------- 4
通过上面的试验证明:
1)在同一个schema下,有查询权限,就可以创建视图
2)在不同schema下,即使有了查询权限,创建视图,还是会提示ORA-01031
2、查看相关权限情况
SQL> select * from SESSION_PRIVS where 2 PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW'); PRIVILEGE ---------------------------------------- SELECT ANY TABLE CREATE VIEW CREATE ANY VIEW SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN( 2 SELECT PRIVILEGE 3 FROM ROLE_SYS_PRIVS 4 WHERE ROLE IN(SELECT * FROM SESSION_ROLES)); no rows selected
通过上面权限查询得出:用户所具有的select 其他用户表的权限是用过role授权
3、单独授于select权限
SQL> conn / as sysdba Connected. SQL> grant select on SCOTT.DEPT to xff; Grant succeeded. SQL> conn xff/xifenfei Connected. SQL> create view V_XFF AS 2 SELECT * FROM SCOTT.DEPT; View created. SQL> select view_name from user_views; VIEW_NAME ------------------------------ V_XFF V_XFF1
4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
1、检查alert日志发现错误
Wed Nov 30 13:36:47 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:49 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:50 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:51 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:53 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:54 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:55 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:58 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:37:12 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 22:00:09 2011 ………… Wed Nov 30 22:00:15 2011 GATHER_STATS_JOB encountered errors. Check the trace file. Wed Nov 30 22:00:15 2011 Errors in file /opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc: ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state
2、查看trace文件
/opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/oracle/product/10.2.0/db_1 System name: Linux Node name: localhost.localdomain Release: 2.6.18-92.el5 Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008 Machine: x86_64 Instance name: ora9i Redo thread mounted by this instance: 1 Oracle process number: 42 Unix process pid: 21372, image: oracle@localhost.localdomain (J001) *** 2011-11-30 22:00:15.782 *** ACTION NAME:(GATHER_STATS_JOB) 2011-11-30 22:00:15.763 *** MODULE NAME:(DBMS_SCHEDULER) 2011-11-30 22:00:15.763 *** SERVICE NAME:(SYS$USERS) 2011-11-30 22:00:15.763 *** SESSION ID:(532.60095) 2011-11-30 22:00:15.763 ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state *** 2011-11-30 22:00:15.782 GATHER_STATS_JOB: GATHER_TABLE_STATS('"VAS"','"TAB_XN_CALLLOG_ANALYSIS"','""', ...) ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state
3、日志初步结论
通过alert日志,感觉应该是对分区表操作,导致”VAS”.”XN_CALLLOG_ANALYSIS_PK”索引变成了unusable state,然后在数据库自动收集统计信息的时候报错(最大可能是全局index导致)
4、验证猜测是否正确
SQL> SELECT owner,index_name,table_name,status FROM DBA_indexes 2 WHERE index_name='XN_CALLLOG_ANALYSIS_PK' AND owner='VAS'; OWNER INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- VAS XN_CALLLOG_ANALYSIS_PK TAB_XN_CALLLOG_ANALYSIS UNUSABLE
5、解决相关问题问题
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || 'REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;' FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC') AND STATUS = 'UNUSABLE' UNION ALL SELECT 'alter index ' ||OWNER || '.' || A.INDEX_NAME || ' REBUILD online nologging;' FROM DBA_INDEXES A WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC') AND STATUS = 'UNUSABLE';
执行生成sql,解决相关index unusable问题