ORA-00600[4194]故障解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

联系：手机/微信(+86 17813235971) QQ(107644445)

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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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问题分析解决

联系：手机/微信(+86 17813235971) QQ(107644445)

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

ORA-09968, ORA-01102 When Starting a Database

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-09968, ORA-01102 When Starting a Database

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

一、网友错误
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-19693: backup piece %s already included

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-19693: backup piece %s already included

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

一、问题现象

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-600 [12235]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 [12235]

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

今天早上例行对各个区域数据库服务器进行检查，发现内蒙古电信的数据库服务器出现ORA-600 [12235]错误
一、错误现象

alert_txzldb.log日志：
Sun Nov  6 09:34:57 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_8253.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
txzldb_ora_8253.trc内容：
Oracle program name: oracle@database.localdomain
*** 2011-11-06 09:34:57.530
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+269         call     ksedst()+0           0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksfdmp()+14          call     ksedmp()+0           3 ? BFFFECA0 ? 98584A4 ?
                                                   AD58F60 ? 3 ? A4B929C ?
kgeriv()+188         call     ksfdmp()+0           AD58F60 ? 3 ?
kgesiv()+113         call     kgeriv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ?
ksesic0()+39         call     kgesiv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ? 2FCB ? 0 ?
                                                   BFFFED0C ?
opirip()+519         call     ksesic0()+0          2FCB ? AD5903C ? BFFFF6AC ?
                                                   FFFFFFFF ? BFFFF814 ? 1 ?
opidrv()+462         call     opirip()+0           32 ? 0 ? 0 ?
sou2o()+25           call     opidrv()+0           32 ? 0 ? 0 ?
main()+355           call     sou2o()+0            BFFFF814 ? 32 ? 0 ? 0 ?
                                                   BFFFF840 ? 0 ?
__libc_start_main()  call     main()+0             1 ? BFFFF894 ? BFFFF89C ?
+161                                               96DFD4 ? 1 ? 8208E40 ?
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (ksedmp()+269 -> ksedst()+0) ==========
Dump of memory from 0xBFFFEB64 to 0xBFFFEC64
BFFFEB60          BFFFEC64 0820B6F8 00000000      [d..... .....]
BFFFEB70 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
………………

二、数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

三、mos信息
根据mos的建议，这个错误可以忽略，不用过多关注

ORA-600 [12235] "Oracle process has no purpose in life !" [ID 33174.1]
ERROR:
  ORA-600 [12235] [a] [b] [/c] [d] [e]
VERSIONS:
  versions 7.0 to 9.2
DESCRIPTION:
  This error shows up when Oracle detects an Oracle defunct process.
  When an Oracle process starts up, it reads data from the SGA that defines
  what type of process it should become.
  If the process does not locate any valid customization data, it reports
  ORA-600 [12235] and exits.
  On a heavily loaded system, ORA-600 [12235] may be a symptom that the server
  process was too slow in starting. That is, the process that initiated the
  new server may timeout waiting for the new process to start and abandon the
  new server request.  In the new server process it is possible that the new
  server reaches the code to customize its operation before the os request to
  kill the process is actioned.  The messages "ksbsrv: No startup
  acknowledgement from forked process ..." and "Timed out trying to start
  shared server ..." may be reported to trace and alert files.
SUGGESTIONS:
  Ignore the error.
  One of the most common reasons for this error to be reported is that
  someone typed 'oracle' manually at the OS prompt.
  If this error is reported regularly and appears not to be explained
  by comments made in this note, contact Oracle Support Services.

四、错误重现
根据mos中的讲述，在系统级别执行oracle，也会出现该错误提示，使用oracle重现结果

[oracle@database ~]$ oracle
[oracle@database ~]$ oracle
[oracle@database ~]$ tail  $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Mon Nov  7 11:10:25 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_27108.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Mon Nov  7 11:10:27 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_1281.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []

win平台登录sqlplus报ora-01031错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：win平台登录sqlplus报ora-01031错误解决

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

今天一朋友告诉我，他的win平台的oracle不能在本地使用sqlplus / as sysdba登录数据库，提示ora-01031的错误，他说是administrator用户，应该不会出现权限不足的情况。我的登录上去一看，果真是这样的情况：

01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.
根据这个错误提示，我的第一反应就是当前的用户不属于ora_dba用户组，通过计算机管理–>本地用户和组–>administrator用户属性–>隶属于中只有一个administrators，果然没有ora_dba组

接下来的事情，就是添加ora_dba组到administrator用户中
点击刚刚隶属于下面的添加–>高级–>立即查找–选择ora_dba–点击确定–>再点击选择组中的确定–>点击用户属性的确定


添加把ora_dba添加到administrator用户所属组中，再尝试登录

ok，登录成功了，看来在win系统中，要想使用sqlplus / as sysdba 登录数据库，必须要隶属于ora_dba组，就算administrators组也不能越俎代庖。

ORA-00845: MEMORY_TARGET not supported on this system

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00845: MEMORY_TARGET not supported on this system

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

在Oracle 11g中如果采用AMM内存管理，那么当MEMORY_TARGET的值大于/dev/shm的时候，就会报ORA-00845: MEMORY_TARGET not supported on this system错误，解决办法增加/dev/shm大小，在redhat系列系统中，/dev/shm的默认值是系统总内存的一半

1、错误重现

SQL>SELECT *  FROM V$VERSION;
 
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 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>show parameter memory;
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SQL>alter system set memory_max_target=800m;
alter system set memory_max_target=800m
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
 
SQL>alter system set memory_max_target=800m scope=spfile;
 
System altered.
 
SQL>alter system set memory_target=800m scope=spfile; 
 
System altered.
 
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:01:18 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL>startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL>!oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

2、修改/dev/shm大小

[root@xifenfei ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_xifenfei-lv_root
                       17G   13G  3.9G  77% /
tmpfs                 590M     0  590M   0% /dev/shm
/dev/sda1             485M   30M  430M   7% /boot
[root@xifenfei ~]# mount -o size=900M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
[root@xifenfei ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_xifenfei-lv_root
                       17G   13G  3.9G  77% /
tmpfs                 900M     0  900M   0% /dev/shm
/dev/sda1             485M   30M  430M   7% /boot
[root@xifenfei ~]# vi /etc/fstab 
 
 
#
# /etc/fstab
# Created by anaconda on Sat Nov  5 02:49:30 2011
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_xifenfei-lv_root /                       ext4    defaults        1 1
UUID=7ace6c04-d232-43ac-9ef5-70ea92fe49bd /boot                   ext4    defaults        1 2
/dev/mapper/vg_xifenfei-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=900M        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

3、启动数据库验证

[oracle@xifenfei admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:03:51 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
sys@XFF>show parameter memory;
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 800M
memory_target                        big integer 800M
shared_memory_address                integer     0

4、官方解释
Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer. If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.

5、解决问题建议
5.1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size.

5.2. If configuring AMM is not possible due to lack of space on /dev/shm mount point, you can configure ASMM instead of AMM, i.e. set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET instead of MEMORY_TARGET.

SP2-1503 SP2-0152 错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：SP2-1503 SP2-0152 错误解决

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

一、现场描述
服务器上有Oracle 10g环境变量分别为
Oracle 10g环境变量

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=ecp
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:$ORACLE_BASE:$ORACLE_HOME
export PATH=${PATH}:$ORACLE_HOME/bin:$ORA_CRS_HOME:$ORA_CRS_HOME/bin
export PATH=${PATH}:$LD_LIBRARY_PATH:$CLASSPATH:$ORACLE_PATH
 
现在在上面Oracle 11g，在shell中执行下面命令修改环境变量
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=ora11g
然后安装数据库，安装过程一切顺利，安装完成，当使用sqlplus的时候报如下错误：
[oracle@node1 tmp]$ sqlplus /nolog
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
 
二、问题原因
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$ export ORACLE_SID=ora11g
[oracle@node1 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 31 17:04:17 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
因为系统的环境变量中的PATH没有修改过来，直接使用sqlplus的时候，还是会调用Oracle 10g中的sqlplus，而此时ORACLE_HOME与其不匹配，导致出现上面错误
 
三、证明猜想，试验如下
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$  env|grep ORACLE
ORACLE_PATH=/opt/oracle/common/oracle/sql:.:/opt/oracle/product/10.2.0/db_1/rdbms/admin
ORACLE_SID=ecp
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
说明：修改ORACLE_HOME，指向Oracle 11g的目录，然后执行sqlplus(该程序属于10g)，报同样错误

四、补充说明
1、如果在一台服务器上安装不同版本数据库，建议使用不用用户安装，这样可以减少很多管理上没必要的麻烦，提高工作效率
2、sqlplus程序需要和ORACLE_HOME向对应，不然就会出现SP2-1503   SP2-0152错误
3、本次排错中学习到知识，如果要执行一个shell文件修改环境变量，可以使用source
4、如果必须使用同一个用户安装，那么建议新安装的oracle版本建一个shell文件，然后使用source shell_filename，修改环境变量（特别注意PATH)