分类目录归档: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-xxxxx, Oracle备份恢复 | 标签为 , | 评论关闭

创建视图提示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

发表在 ORA-xxxxx | 标签为 | 评论关闭

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问题

发表在 ORA-xxxxx | 标签为 | 评论关闭