标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 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-600 kcratr_nab_less_than_odr 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 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
分类目录归档:ORA-xxxxx
创建视图提示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问题
ORA-1502问题分析解决
1、出现ORA-1502
接到开发报告,我们报表程序出现SQLCODE=[-1502]错误提示,sql执行不成功,根据这个提示,我猜想是ORA-1502错误,查询ora-1502错误
[oracle@node1 ~]$ oerr ora 1502 01502, 00000, "index '%s.%s' or partition of such index is in unusable state" // MERGE: 1489 RENUMBERED TO 1502 // *Cause: An attempt has been made to access an index or index partition // that has been marked unusable by a direct load or by a DDL // operation // *Action: DROP the specified index, or REBUILD the specified index, or // REBUILD the unusable index partition
根据这个提示,错误的原因是因为有index编程unusable state
--查询分区index是否有UNUSABLE的index
SQL> col index_owner for a20
SQL> col index_name for a30
SQL> col partition_name for a20
SQL> SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME
2 FROM DBA_IND_PARTITIONS
3 WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
4 AND STATUS = 'UNUSABLE';
INDEX_OWNER INDEX_NAME PARTITION_NAME
-------------------- ------------------------------ --------------------
STAT_YDZJ IND_STAT_DAY_COMPANY_DAY P_201111
STAT_YDZJ IND_STAT_DAY_COMPANY_MOBILE P_201111
STAT_YDZJ IND_STAT_DAY_COMPANY_AREA P_201111
--查询无效全局index和普通index
SQL> SELECT OWNER, a.index_name
2 FROM Dba_Indexes a
3 WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
4 AND ROWNUM<2;
OWNER INDEX_NAME
------------------------------ ------------------------------
STAT_YDZJ PK_ND_STAT_DAY_COMPANY
2、解决问题
批量生产sql语句,重建这些unusable index
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生成语句后,让开发测试程序,反馈工作正常
3、问题分析
我昨天对STAT_DAY_COMPANY表添加了分区(SPLIT拆分MAXVALUE),本以为该表的所有index都是local index不知道为何有了一个全局index(公司规定所有分区表都只能建立local index),所以没有做相关查询,导致这次出现了index无效。但是为什么还有三个local index也变成了unusable,这个估计是大批量append插入数据导致。而开发那边正好是批量append插入数据到STAT_DAY_COMPANY表中,导致出现了ORA-1502错误
建议:处理分区表时,要对index查询清楚,不要按照规定或者惯性思维办事。

加我微信(17813235971)
加我QQ(107644445)

