分类目录归档：ORA-xxxxx

ORA-1502问题分析解决

发表于 2011 年 12 月 2 日 由 惜分飞

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查询清楚，不要按照规定或者惯性思维办事。

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

ORA-09968, ORA-01102 When Starting a Database

发表于 2011 年 11 月 23 日 由 惜分飞

一、网友错误
pub上网友遇到一个问题

Tue Nov 22 10:31:19 2011
ALTER DATABASE   MOUNT
Tue Nov 22 10:31:19 2011
sculkget: failed to lock /u01/app/oracle/product/10.2.01/db_1/dbs/lkORCL exclusive
sculkget: lock held by PID: 26308
Tue Nov 22 10:31:19 2011
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26308
Tue Nov 22 10:31:19 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

我给的建议是重启数据库解决，其实重启数据库是关闭了当前开启的实例，然后开启报错的实例，所以重启成功

二、错误重现

Tue Nov 22 10:31:19 2011
ALTER DATABASE   MOUNT
Tue Nov 22 10:31:19 2011
sculkget: failed to lock /u01/app/oracle/product/10.2.01/db_1/dbs/lkORCL exclusive
sculkget: lock held by PID: 26308
Tue Nov 22 10:31:19 2011
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26308
Tue Nov 22 10:31:19 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...




[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 23 09:07:21 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter name ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /opt/oracle/oradata/test/contr
                                                 ol01.ctl
SQL> create pfile='/tmp/t_pfile' from spfile;

File created.

SQL> exit
Disconnected from 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@ECP-UC-DB1 ~]$ vi /tmp/t_pfile 

*.__db_cache_size=67108864
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=117440512
*.__streams_pool_size=8388608
*.archive_lag_target=0
*.audit_file_dest='/opt/oracle/admin/test/adump'
*.background_dump_dest='/opt/oracle/admin/test/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/oracle/oradata/test/control01.ctl'
*.core_dump_dest='/opt/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/oracle/oradata/test/archivelog'
*.open_cursors=1000
*.pga_aggregate_target=66060288
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/test/udump'
~
~

"/tmp/t_pfile" 28L, 1043C written
[oracle@ECP-UC-DB1 ~]$ export ORACLE_SID=tt1
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 23 09:10:47 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='/tmp/t_pfile' mount;
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             134219808 bytes
Database Buffers           67108864 bytes
Redo Buffers                6303744 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


SQL> exit
Disconnected from 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@ECP-UC-DB1 ~]$ more /opt/oracle/admin/test/bdump/alert_tt1.log 
Wed Nov 23 09:11:26 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
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.4.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 117440512
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 8388608
  sga_target               = 209715200
  control_files            = /opt/oracle/oradata/test/control01.ctl
  db_block_size            = 8192
  __db_cache_size          = 67108864
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = location=/opt/oracle/oradata/test/archivelog
  archive_lag_target       = 0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /opt/oracle/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  dispatchers              = (PROTOCOL=TCP) (SERVICE=testXDB)
  job_queue_processes      = 10
  background_dump_dest     = /opt/oracle/admin/test/bdump
  user_dump_dest           = /opt/oracle/admin/test/udump
  core_dump_dest           = /opt/oracle/admin/test/cdump
  audit_file_dest          = /opt/oracle/admin/test/adump
  db_name                  = test
  open_cursors             = 1000
  pga_aggregate_target     = 66060288
PMON started with pid=2, OS id=28086
PSP0 started with pid=3, OS id=28088
MMAN started with pid=4, OS id=28090
DBW0 started with pid=5, OS id=28092
LGWR started with pid=6, OS id=28094
CKPT started with pid=7, OS id=28096
SMON started with pid=8, OS id=28098
RECO started with pid=9, OS id=28100
CJQ0 started with pid=10, OS id=28102
MMON started with pid=11, OS id=28104
Wed Nov 23 09:11:28 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=28106
Wed Nov 23 09:11:28 2011
starting up 1 shared server(s) ...
Wed Nov 23 09:11:28 2011
ALTER DATABASE   MOUNT
Wed Nov 23 09:11:28 2011
sculkget: failed to lock /opt/oracle/product/10.2.0/db_1/dbs/lkTEST exclusive
sculkget: lock held by PID: 12339
Wed Nov 23 09:11:28 2011
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12339
Wed Nov 23 09:11:28 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

通过这个试验，再现了网友的ORA-09968, ORA-01102错误

三、MOS解释
ORA-09968, ORA-01102 When Starting a Database

发表在 ORA-xxxxx | 评论关闭

ORA-19693: backup piece %s already included

发表于 2011 年 11 月 13 日 由 惜分飞

一、问题现象

RMAN> restore database;

启动 restore 于 13-11月-11
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00002 还原到 E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF
通道 ORA_DISK_1: 将数据文件 00003 还原到 E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF
通道 ORA_DISK_1: 将数据文件 00004 还原到 E:\ORACLE\ORADATA\XFF\USERS01.DBF
通道 ORA_DISK_1: 将数据文件 00005 还原到 E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF
通道 ORA_DISK_1: 将数据文件 00006 还原到 E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF
通道 ORA_DISK_1: 将数据文件 00008 还原到 E:\ORACLE\ORADATA\XFF\P_TEST01.DBF
通道 ORA_DISK_1: 将数据文件 00009 还原到 E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF
通道 ORA_DISK_1: 将数据文件 00010 还原到 E:\ORACLE\ORADATA\XFF\P101.DBF
通道 ORA_DISK_1: 将数据文件 00011 还原到 E:\ORACLE\ORADATA\XFF\P201.DBF
通道 ORA_DISK_1: 将数据文件 00012 还原到 E:\ORACLE\ORADATA\XFF\P301.DBF
通道 ORA_DISK_1: 将数据文件 00015 还原到 E:\ORACLE\ORADATA\XFF\OGG01.DBF
通道 ORA_DISK_1: 将数据文件 00016 还原到 E:\ORACLE\ORADATA\XFF\SPOT01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 11/13/2011 18:19:31 上) 失败
ORA-19693: 已包括备份片段 F:\RMANBACKUP\9_12_0GMMD2KI_1_1

RMAN> list backup summary;


备份列表
===============
关键字     TY LV S 设备类型 完成时间   段数 副本数 压缩标记
------- -- -- - ----------- ---------- ------- ------- ---------- ---
6       B  F  A DISK        12-9月 -11 1       2       NO         TAG20110912T215425
7       B  F  A DISK        12-9月 -11 1       1       NO         TAG20110912T215425
8       B  F  A DISK        12-9月 -11 1       1       NO         TAG20110912T220120

19693, 00000, "backup piece %s already included"
// *Cause:  This backup piece was already specified for inclusion in the
//          restore conversation. A restore conversation may process only
//          a single instance of a backup piece.
// *Action: Remove the specified duplicate backup piece in restore steps
//          and restart the conversation.

二、解决办法
1、查找出副本数大于1的备份集
2、重命名对应的备份集
3、使用crosscheck backup检测出无效的备份集
4、使用delete noprompt expired backup删除无效备份集
5、使用CATALOG START WITH重新添加刚刚重命名的备份集

三、问题原因
Bug 4483368: SEVERAL RMAN CATALOG START WITH CREATES DUPLICATE CATALOG ENTRIES

After several "RMAN  catalog start with "we have several copies of the same backuppiece 
in our catalog which we can see with "list backup;" and also "list backup summary;".
However, when I now try to restore with that controlfile instead of a catalog 
I receive  ORA-19693 backup piece "/opt/oracle/admin/mnt/HERMES2/bkp/..." 
is already included.
发表在 ORA-xxxxx, rman备份/恢复 | 标签为 | 评论关闭