Know more about _in_memory_undo

set parameter _in_memory_undo = FALSE to disable IMU

Workaround: Disable IMU (set _in_memory_undo=FALSE)

PLEASE NOTE: This bug applies to single instance databases and not RAC as IMU is not enabled in RAC.

注意在RAC系统中IMU是不可用的,所以也就不必要去设置_in_memory_undo=FALSE

The workaround will prevent the problem, but will not fix it.

Note: _in_memory_undo is a dynamic parameter for 10g with values of TRUE or FALSE. It specifies whether there should be in memory undo for transactions. Setting this value to FALSE will disable this feature. This will cause excess redo generation.

_in_memory_undo is applicable when

compatibility >= 10.0
undo_management = AUTO
cluster_database = FALSE

Running IMU transactions may generate out-of-order redo records

Disabling in memory undo (_in_memory_undo=false)
can help to eliminate “In memory undo latch” contention
but there may still be “undo global data” latch contention
as that latch is used regardless of the setting of
_in_memory_undo. The fix for this bug can help reduce
contention on both latches.

Know more about Oracle Nologging

The NOLOGGING clause doesn’t prevent redo on all operations, but rather only on a subset. I searched the documentation for examples of this…

http://st-doc.us.oracle.com/11/112/server.112/e16541/parallel007.htm?term=nologging+generate+redo#VLDBG1536

[NO]LOGGING Clause

The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The NOLOGGING attribute is not specified at the INSERT statement level but is
instead specified when using the ALTER or CREATE statement for a table, partition, index, or tablespace.

When a table or index has NOLOGGING set, neither parallel nor serial direct-path INSERT operations generate redo logs. Processes running with the NOLOGGING option set run faster because no redo is generated. However, after a NOLOGGING operation against a table,
partition, or index, if a media failure occurs before a backup is performed, then all tables, partitions, and indexes that have been modified might be corrupted.

Direct-path INSERT operations (except for dictionary updates) never generate redo logs if the NOLOGGING clause is used. The NOLOGGING attribute does not affect undo, only redo. To be precise, NOLOGGING allows the direct-path INSERT operation to generate a negligible
amount of redo (range-invalidation redo, as opposed to full image redo).

But I did find an Ask Tom article which is more explicit about what operations generate redo despite the NOLOGGING clause:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

This has a nice table of operations with either No/Archive and No/Logging specified, and you can see redo is generated most of the time.

So to sum up the NOLOGGING clause only works with certain operations and we cannot expect all REDO to be completely halted.

Another item to consider is whether the Indexes on the tables were created with NOLOGGING or not… This is covered in Index generates high redo, although it is in NOLOGGING (Doc ID 1235234.1), so please reveiw that note to see if there are some indexes that can be recreated to reduce redo further.

Would It affect RAC clusterware and database If we adjust OS time/Clock?

Question:

在RAC环境中节点之间的OS操作系统时钟一致是clusterware能够稳定运行的重要因素之一,但是如果我们确实有调整OS时间的需求,那么是否真的会影响到RAC的正常运行呢? 具体的影响是如何的呢?  又需要注意哪些方面的因素呢?

Answer:

RAC: Frequently Asked Questions (Doc ID 220970.1)

Does Oracle RAC work with NTP (Network Time Protocol)?
YES! NTP and Oracle RAC are compatible, as a matter of fact, it is recommended to setup NTP in an Oracle RAC cluster, for Oracle 9i Database, Oracle Database 10g, and Oracle Database 11g Release 1.


Keep the following points in mind:

# Minor changes in time (in the seconds range) are harmless for Oracle RAC and the Oracle Clusterware. If you intend on making large time changes it is best to shutdown the instances and the entire Oracle Clusterware stack on that node to avoid a false eviction, especially if you are using the Oracle RAC 10g low-brownout patches, which allow really low misscount settings.

# Backup/recovery aspect of large time changes are documented in Note: 77370.1, basically you can’t use RECOVER DATABASE UNTIL TIME to reach the second recovery point, It is possible to overcome with RECOVER DATABASE UNTIL CANCEL or UNTIL CHANGE. If you are doing complete recovery (most of the times) then this is not an issue since the Oracle recovery code uses SCN (System Change Numbers) to advance in the redo/archive logs. The SCN numbers never go back in time (unless a reset-logs operation is performed), there is always an association of an SCN to a human readable timestamp (which may change forward or backwards), hence the issue with recovery until point in time vs. until SCN/Cancel.

# If DBMS_SCHEDULER is in usage it will be affected by time changes, as it’s using actual clock rather than SCN.

# On platforms with OPROCD get fix for <> “OPROCD REBOOTS NODE WHEN TIME IS SET BACK BY XNTPD”

# If NTP is not configured correctly (using -x flag), and diagwait not set to 13 Note: 559365.1 10.2/11.1 RAC systems can be rebooted due to OPROCD, during a leap second event, see Note: 759143.1.
# Daylight saving time adjustments do not affect the system clock, only the displayed time, hence have no impact on the Oracle software.

Apart from these issues, the Oracle RDBMS server is immuned to time changes, i.e. will not affect transaction/read consistency operations.

Also please refer to note:
Dates & Calendars – Frequently Asked Questions (Doc ID 227334.1)

So please perform time changes in small amount using date command only.  Doing it precisely will  be difficult manually. Therefore using ntpd with -x option could be better solution for this case as well.

In general step in not more than 3 seconds when tuning time backward should be fine.

Know more about LOCK_SGA Parameter

Can you kindly define and explain of  lock_sga  parameter?each flatform(HP, IBM, SUN) recommended value of both parameter
and why it is recommended like that value.

 

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.

Each platform has its own recommendations and support over the parameter value. Hence you would see differences in the recommendations.

+ lock_sga Only locks the entire SGA into physical memory.

+ It can be set to TRUE as long as you want to lock the entire SGA in the physical memeory and your OS supports it. As for as i know it works in all the platforms.

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space.

NOTE:: This parameter is ignored on platforms that do not support it.

+ Windows For sure doesn’t support this parameter.

+ AIX Supports it.

+ LOCK_SGA is not supported on Solaris.

+ On Hp-UX if you have mlock (OS privilege) then LOCK_SGA is not required

+ Linux also supports this parameter

You can also review

Note 577898.1 – ORA-27102 Received At Startup When LOCK_SGA Is Set Although Enough Memory Is Available.

 

lock_sga: Default value: false.
If set to true, the entire SGA will be locked into physical memory (preventing it from being paged out). This may cause problems on systems with insufficient physical memory.

The parameters (and recommendations) are Unix generic.

 

The lock_sga parameter (which default AND recommended value is false): If you have sufficient memory on the server, you may consider setting it to true. (Forcing the SGA to be locked in physical memory).
On the other hand, if lock_sga is set to true, and you do not have suffficient memory, the SGA will stay in memory, and force other processe, like server processes to swap, and hence cause performance degradation.

For both the hidden and normal configuration parameters, we do not recommend deviations from the default values, without having detailed information on your specific configruation.

If you look at all Oracle products, options, features and parameters, there are billions of combinations. We do not have a complete catalog over each recommended values for each combiantion.

Know Oracle Process OS Scheduled Priority

Can you kindly define and explain of this parameter?

_os_sched_high_priority

 

Regarding _os_sched_high_priority :

Setting LMS priority automatically via _os_sched_high_priority
It has been proven that performance is increased when LMS processes are running in the real time priority class, as opposed to the timesharing class. The parameter _os_sched_high_priority was introduced in Oracle 10g Release 2, and it allows LMS processes to be automatically configured in the real time priority class at instance startup. This feature basically obsoletes the need to manually set the real time priority for the LMS processes using a C program or the renice command.

On Unix platforms, process priority changes are normally executed under the root privilege, and the LMS process priority changes are implemented through an executable � oradism � owned by root with the setuid bit set.

The default value for _os_sched_high_priority in 10.2.0.1 is 1. With _os_sched_high_priority = 1, it means that all LMS processes are set to priority 1 in the real time class. We suggest to always set _os_sched_high_priority=1 in the init.ora, regardless of the default, and only toggle the behavior by restoring the original privileges and permissions on the oradism executable. Other possible values for this parameter are:

� If set to 0, LMS processes are not prioritized at startup, they are kept at the timesharing class as in earlier releases.

� Values higher than 1 give LMS higher priority in the real time class. Those settings are not recommended because they might cause starvation for other Oracle processes, leading to performance degradation.

If LMS processes remain in the timesharing class at instance startup, despite _os_sched_high_priority set to 1, then ownership and/or privileges for oradism may not have been set properly. To correct / verify these settings:

$ ls -l oradism

-rwsr-sr-x 1 root dba 15871 Jun 13 10:32 oradism

# chown root:dba oradism

# chmod 6755 oradism

Note about Linux process priorities:

SCHED_OTHER
static prio 0 : shows dynamic priorities with ‘ps’, ranging from 59-99

SCHED_RR / SCHED_FIFO
static prio 1 -> shows as priority 58 with ‘ps’
static prio 11 -> shows as priority 48 with ‘ps’
static prio 59 -> shows as priority 0 with ‘ps’
static prio 60 -> shows as priority -1 with ‘ps’
static prio 99 -> shows as priority -40 with ‘ps’ (highest RT priority)

On Linux, the LMS process priority can be seen via �ps �efl | grep lms�:

$ ps -efl|grep lms

0 S spommere 2201 1 0 58 0 – 285308 schedu 14:39 ? 00:00:00 ora_lms0_appsu01

0 S spommere 2205 1 0 58 0 – 285308 schedu 14:39 ? 00:00:00 ora_lms1_appsu01

In the above example, the LMS processes are running in the real-time class.

In the example below, the change in process prioritization failed, and the process priority remained at 75, because the permissions on the oradism executable were incorrectly set.

$ ps -efl | grep lms

0 S spommere 2002 1 1 75 0 – 285307 schedu 14:38 ? 00:00:00 ora_lms0_appsu01

0 S spommere 2006 1 1 75 0 – 285308 schedu 14:38 ? 00:00:00 ora_lms1_appsu01

 

Reference: internal Note 341974.1

 

Unpublished note 433105.1: ‘LMS Real Time Priority in RAC 10g Release 2 – Things to Consider Before Changing’.

_os_sched_high_priority: Default value is 1.
Setting it to 0 means that all scheduling class manipulation will be disabled.
The parameter is mainly ised in RAC systems, where you may want specific procecces like LMS to run at a higher priority.

Any underscore (hidden) parameters (like _os_sched_high_priority) should not be set unless specificly instructed by Support either via a Service Request or via a MetaLink Note. In all other cases, it’s default value should be used.
(You may see examples of notes by querying in MetaLink with the word _os_sched_high_priority).

查询v$lock缓慢和direct path write temp等待

v$lock是常用的enqueue lock队列锁动态性能视图,不管是用户自己部署的监控脚本也好、还是enterprise manager都多少会使用到该V$LOCK视图, 但是在10g中遇到了v$lock查询缓慢的问题, 例如下面的查询会等待较多direct path write temp等待事件:

 

 

select count(*) from v$lock; 

  COUNT(*) 
---------- 
       163 

Elapsed: 00:00:60.90 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2384831130 

-------------------------------------------------------------------------------------- 
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |            |     1 |    50 |     1 (100)| 00:00:01 | 
|   1 |  SORT AGGREGATE         |            |     1 |    50 |            |          | 
|*  2 |   HASH JOIN             |            |     1 |    50 |     1 (100)| 00:00:01 | 
|   3 |    MERGE JOIN CARTESIAN |            |   100 |  3800 |     0   (0)| 00:00:01 | 
|*  4 |     FIXED TABLE FULL    | X$KSUSE    |     1 |    19 |     0   (0)| 00:00:01 | 
|   5 |     BUFFER SORT         |            |   100 |  1900 |     0   (0)| 00:00:01 | 
|   6 |      FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 | 
|   7 |    VIEW                 | GV$_LOCK   |    10 |   120 |     0   (0)| 00:00:01 | 
|   8 |     UNION-ALL           |            |       |       |            |          | 
|*  9 |      FILTER             |            |       |       |            |          | 
|  10 |       VIEW              | GV$_LOCK1  |     2 |    24 |     0   (0)| 00:00:01 | 
|  11 |        UNION-ALL        |            |       |       |            |          | 
|* 12 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    64 |     0   (0)| 00:00:01 | 
|* 13 |         FIXED TABLE FULL| X$KSQEQ    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 14 |      FIXED TABLE FULL   | X$KTADM    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 15 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    64 |     0   (0)| 00:00:01 | 
|* 16 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    64 |     0   (0)| 00:00:01 | 
|* 17 |      FIXED TABLE FULL   | X$KTATL    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 21 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    64 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------

direct path write temp
direct path write temp
direct path write temp
................

 

 

显然仅返回100多条记录的v$LOCK视图的查询不该这么慢,也不该由SORT或HASH造成大量的临时空间使用, 究其根本还是FIXED TABLE即X$的内部表上的统计信息不准确导致的执行计划使用,通过使用RULE HINT可以马上获得较好的性能:

 

 

select /*+ RULE */ count(*) from v$LOCK;

  COUNT(*) 
---------- 
       190 

Elapsed: 00:00:00.18

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2026431807 

------------------------------------------------- 
| Id  | Operation                  | Name       | 
------------------------------------------------- 
|   0 | SELECT STATEMENT           |            | 
|   1 |  SORT AGGREGATE            |            | 
|   2 |   MERGE JOIN               |            | 
|   3 |    SORT JOIN               |            | 
|   4 |     MERGE JOIN             |            | 
|   5 |      SORT JOIN             |            | 
|   6 |       FIXED TABLE FULL     | X$KSQRS    | 
|*  7 |      SORT JOIN             |            | 
|   8 |       VIEW                 | GV$_LOCK   | 
|   9 |        UNION-ALL           |            | 
|* 10 |         FILTER             |            | 
|  11 |          VIEW              | GV$_LOCK1  | 
|  12 |           UNION-ALL        |            | 
|* 13 |            FIXED TABLE FULL| X$KDNSSF   | 
|* 14 |            FIXED TABLE FULL| X$KSQEQ    | 
|* 15 |         FIXED TABLE FULL   | X$KTADM    | 
|* 16 |         FIXED TABLE FULL   | X$KTATRFIL | 
|* 17 |         FIXED TABLE FULL   | X$KTATRFSL | 
|* 18 |         FIXED TABLE FULL   | X$KTATL    | 
|* 19 |         FIXED TABLE FULL   | X$KTSTUSC  | 
|* 20 |         FIXED TABLE FULL   | X$KTSTUSS  | 
|* 21 |         FIXED TABLE FULL   | X$KTSTUSG  | 
|* 22 |         FIXED TABLE FULL   | X$KTCXB    | 
|* 23 |    SORT JOIN               |            | 
|* 24 |     FIXED TABLE FULL       | X$KSUSE    | 
-------------------------------------------------

 

 

针对上述问题考虑为FIXED TABLE收集统计信息,可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS标准存储过程,特别是对于版本升级上来的数据库,特别需要考虑执行该存储过程更新FIXED TABLE STATISTICS:

 

 

SQL> set timing on;
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:01:24.87

 

 

GATHER_FIXED_OBJECTS_STATS

 

Create fixed table statistics
Directly after catupgrd.sql has been completed
This will speed up processing for recompilation with utlrp.sql
Create fixed table statistics again after a week with regular production workload
This task should be done only a few times per year

ORA-600 [17003]错误一例

一套AIX 上的10.2.0.4系统运行catupgrd.sql脚本时出现ORA-600 [17003]错误, 详细的日志如下:

 

ALTER TYPE lcr$_row_record ADD MEMBER FUNCTION
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17003], [0x70000008E6DA8C8], [1],
[1], [], [], [], []

1. Provide a list of invalid objects taken before upgrade was initially attempted.

spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> ‘VALID’;
spool off

2. Provide output of select obj# ,name from obj$ where name in (‘LCR$_ROW_LIST’,’LCR$_ROW_RECORD’);

3. Provide an output of :
select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status
psta, P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID
and do.object_ID in (select object_id from dba_objects where
OBJECT_NAME=’LCR$_ROW_LIST’)
/

4. Run same SQL in step 3 in another database of the same version – 10.1.0.4

select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status
psta, P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID
and do.object_ID in (select object_id from dba_objects where
OBJECT_NAME=’AQ$_REG_INFO’)
/

Compare and verify that this object has as many parent entries in dependency$ as they
are in a fresh Database of the same version. If they are not, alter
compile this object,and using same query above to verify that parent rows are created.

5. To verify timestamp discrepancy, run SQL

spool &spoolfilename
set pagesize 10000
column d_name format a20
column p_name format a20
select do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,’DD-MON-YYYY HH24:MI:SS’) “P_Timestamp”,
to_char(po.stime ,’DD-MON-YYYY HH24:MI:SS’) “STIME”,
decode(sign(po.stime-p_timestamp),0,’SAME’,’*DIFFER*’) X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;

SQL> select obj# ,name from obj$ where name in (‘LCR$_ROW_LIST’,’LCR$_ROW_RECORD’);

OBJ# NAME
———- ——————————
5738 LCR$_ROW_RECORD
5776 LCR$_ROW_RECORD
256054 LCR$_ROW_LIST

SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status
2 3 psta, P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
4 where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID
5 and do.object_ID in (select object_id from dba_objects where
6 OBJECT_NAME=’LCR$_ROW_LIST’)
7 /

no rows selected

SQL> spool timestamp
SQL> set pagesize 10000
column d_name format a20
column p_name format a20
select do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
SQL> to_char(p_timestamp,’DD-MON-YYYY HH24:MI:SS’) “P_Timestamp”,
to_char(po.stime ,’DD-MON-YYYY HH24:MI:SS’) “STIME”,
decode(sign(po.stime-p_timestamp),0,’SAME’,’*DIFFER*’) X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
SQL> and do.status=1 /*dependent is valid*/
2 3 4 5 6 7 8 and po.status=1 /*parent is valid*/
9 10 11 and po.stime!=p_timestamp /*parent timestamp not match*/
12 order by 2,1;

no rows selected

SQL> spool off

Errors in file ora_594098.trc:
ORA-07445: exception encountered: core dump [kgghstfel+0074] [SIGSEGV]
[Address not mapped to object] [0x204000000208] [] []

1. Install 10.2.0.1, install 10.2.0.4
2. run utlu102i.sql, check output.
3. change Oracle Home 10.1 to 10.2
4. startup upgrade and run catupgrd.sql

SQL> set lines 200
SQL> col comp_name format a50
SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
————————————————– —————————— ———————————
Oracle XML Database 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
Oracle Real Application Clusters 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID

If the above query shows that everything is valid and is in proper version,
it means, that we would need to correct the problem with lcr$_row_record before upgrade.

I see that one component is invalid ‘Database packages and types’. To resolve this, please perform the following:

SQL> shutdown immediate
SQL> startup upgrade
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> shutdown immediate
SQL> startup
SQL> set lines 200
SQL> col comp_name format a50
SQL> select comp_name,version,status from dba_registry;

Recompiled also get ORA-00600: internal error code, arguments: [17003]

SQL> alter type LCR$_ROW_RECORD compile;
alter type LCR$_ROW_RECORD compile
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17003], [0x70000007C3A0378], [1],
[1], [], [], [], []

select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status psta, P_TIMESTAMP
from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID =
select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’);

SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
2 D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status psta, P_TIMESTAMP
3 from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
4 where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID =
5 (select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’);

(select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’)
*
ERROR at line 5:
ORA-01427: single-row subquery returns more than one row

SQL> set lines 200
SQL> col comp_name format a50
SQL> select comp_name,version,status from dba_registry;

SQL> col object_name format a20
SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status psta, P_TIMESTAMP
from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID in
(select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’);

 

MOS Bug Info:

 

Hdr: 6611530 10.2.0.2.0 RDBMS 10.2.0.2.0 AQ PRODID-5 PORTID-197 ORA-600
Abstract: ORA-600 17003

PROBLEM:
——–
Customer was trying to apply the 10.2.0.3 patchset and kept getting ora-600
17003 errors running catproc.  They tested the running of catalog, catproc
and utlrp in the 10.2.0.2 database version before upgrade and got the same
error.  The errors are reproducing trying to compile at least these 2
objects:
ALTER PACKAGE “SYS”.”DBMS_AQADM_SYS” COMPILE BODY REUSE SETTINGS
ALTER TYPE “SYS”.”LCR$_ROW_RECORD” COMPILE SPECIFICATION REUSE SETTINGS

This issue started in SR 6535356.994 and since the issue reproduces before
upgrade it was determined that this isn’t an upgrade issue.

DIAGNOSTIC ANALYSIS:
——————–
Have reviewed alert.log and trace file.
Had customer run hcheck – output will be uploaded.

In the trace files, I couldn’t find the handle for the second argument of the
ora-600 17003 error, so can not determine the object.

WORKAROUND:
———–
none known

RELATED BUGS:
————-
Looks exactly like Bug 5857558.  Was going to try the generic fix in this bug
but cannot determine the object because cannot find the handle in the trace
files.

REPRODUCIBILITY:
—————-
It is reproducible everytime they run catproc.  catalog runs fine.

TEST CASE:
———-
none

STACK TRACE:
————
ksedst
ksedmp
ksfdmp
kgeriv
kgeasi
kglget
kglgob
kgldpo0
kgldpo
kgldon
pl_don
ptgxtn
ptg_nd
phdbte
phncrr_check_remote_refs
phncee_check_extra_errors
phnr_resolve
ph2exp
ph2ext
ph2osa
ph2of1
ph2exp
ph2ext
ph2osa
ph2of1

SUPPORTING INFORMATION:
———————–

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-

DIAL-IN INFORMATION:
——————–

IMPACT DATE:
————

The query returned no rows:
SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status
psta, P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po where
D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID = (select
object_id from dba_objects where OBJECT_NAME=’AQ$_SRVNTFN_MESSAGE’);
  2    3    4    5  
/

no rows selected

Therefore, I have asked him to send up the files needed to set-up the system
tablespace in-house.  Do you need any other tablespaces?

Customer did the following:

1.    Recovered the database back to 10.2.0.2 before we ran catalog and catproc.
2. Performed the steps for dictionary fix as following ( he had tested this
out ? please see dev.log with the test he did before this ):
(1) update obj$ set status=5 where obj#=(select object_id from dba_objects
where OBJECT_NAME=’AQ$_SRVNTFN_MESSAGE’);
     commit;
     select obj#, name, status from obj$ where obj# = (select object_id from
dba_objects where OBJECT_NAME=’AQ$_SRVNTFN_MESSAGE’);
.
(2) FLUSH the shared pool(or bounce the DB), to reflect this changed status
in cache as well.
.
(3) alter type AQ$_SRVNTFN_MESSAGE compile;
.
(4) Check required dependency$ rows are recreated.
     select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp, po.status
psta, P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po where
D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID = (select
object_id from dba_objects where OBJECT_NAME=’AQ$_SRVNTFN_MESSAGE’);
.
(5) ALTER PACKAGE “SYS”.”DBMS_AQADM_SYS” COMPILE BODY REUSE SETTINGS;
     select obj#, name, status from obj$ where obj# = (select object_id from
dba_objects where OBJECT_NAME=’DBMS_AQADM_SYS’ and OBJECT_TYPE in (‘PACKAGE
BODY’));
3. Executed catalog
4. Executed catproc and encountered Ora-600 errors:
    First error:
       UPDATE SYS.AQ_SRVNTFN_TABLE tab
           *
ERROR at line 1:
ORA-81: address range [0x60000000000DBDB0, 0x60000000000DBDB4) is not
readable
ORA-600: internal error code, arguments: [kksfbc-reparse-infinite-loop],
[0x9FFFFFFFBE9CED88], [], [], [], [], [], []

     Second error:
Warning: Type created with compilation errors.

Errors for TYPE LCR$_ROW_RECORD:

LINE/COL ERROR
——– —————————————————————–
0/0      ORA-81: address range [0x60000000000DBD80, 0x60000000000DBD84)
         is not readable
         ORA-600: internal error code, arguments: [17003],
         [0xC0000004F1DD89D0], [1], [1], [], [], [], []

Invalids after utlrp:

SYS                STREAMS$_EVALUATION_CONTEXT      EVALUATION CONTEXT
INVALID
                   DBMS_STREAMS                     PACKAGE            
INVALID
                   DBMS_STREAMS_DATAPUMP            PACKAGE BODY       
INVALID
                   DBMS_STREAMS                     PACKAGE BODY       
INVALID
                   DBMS_LOGREP_IMP                  PACKAGE BODY       
INVALID
                   LCR$_ROW_RECORD                  TYPE               
INVALID
                   LCR$_ROW_RECORD                  TYPE BODY          
INVALID
                   AQ$_AQ_SRVNTFN_TABLE_F           VIEW               
INVALID
                   AQ$AQ_SRVNTFN_TABLE              VIEW               
INVALID

I then asked him to up the following parameters which did no good (didn?t
think they would but wanted to try):
shared_pool_size greater then 800M
large_pool_size greater then 250M
java_pool_size greater then 250M
db_cache_size greater then 500M
compatible set to 10.2.0.0
pga_aggregate_target greater then 1000M

Then had him do the following:
SQL> select distinct o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.type#=13 and o.obj#=d.p_obj# ;

  2    3  
NAME                           STIME     P_TIMESTA
—————————— ——— ———
AQ$_SRVNTFN_MESSAGE            08-NOV-07 11-NOV-06

SQL> startup restrict
SQL> conn / as sysdba

SQL> update dependency$ d set p_timestamp = (select stime from obj$ p where
d.p_obj#=p.obj#)
           where d.p_obj# in (select obj# from obj$ where type#=13) and
d.p_timestamp != (select stime
           from obj$ o where d.p_obj#=o.obj#);

ENSURE BY THE PREVIOUS STATEMENT that only 1 rows are updated.

——— THIS RETURNED 3 rows therefore he rolled back and this is where we
are now.

/upload/bug6611530 directory:
Dev.log ? gives the outputs after the dictionary patch was done
alert_oasc1[1].log.11082007.txt ? the alert.log after the dictionary patch
was done and the migration was then attempted
oasc1_ora_3475.trc – The first trace file in the series

You can see in the trace file the following 2 ora errors:
ORA-600: internal error code, arguments: [kksfbc-reparse-infinite-loop],
[0x9FFFFFFFBE9CED88], [], [], [], [], [], []
AND
ORA-600: internal error code, arguments: [17003], [0xC0000004F1DD89D0],
[1], [1], [], [], [], []
—- where I still cannot find the handle in the trace file.

The ora-600 kksfbc-reparse-infinite-loop occured after the data dictionary
patching so I included it here for clarity.  Will work on that one after this
is fixed (if it can be).

Here is the results of the requested query.  Since this object has both an
OBJECT_TYPE of TYPE and TYPE BODY there are 2 queries that were run:

SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
  2  D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp,
po.status psta,
  3   P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
  4  where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID =
  5  (select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’
and OBJECT_TYPE = ‘TYPE’);

    D_OBJ#
———-
OBJECT_NAME
——————————————————————————

DTYP                DSTA    D_TIMESTA     ORDER#     P_OBJ#
——————- ——- ——— ———- ———-
OBJECT_NAME
——————————————————————————

PTYP                PSTA    P_TIMESTA
——————- ——- ———
   1462820
LCR$_ROW_RECORD
TYPE                INVALID 09-NOV-07          2    3320201
LCR$_ROW_LIST
TYPE                VALID   11-NOV-06

   1462820
LCR$_ROW_RECORD
TYPE                INVALID 09-NOV-07          1        309
STANDARD
PACKAGE             VALID   18-APR-03

   1462820
LCR$_ROW_RECORD
TYPE                INVALID 09-NOV-07          0    1462817
LCR_ROW_LIB
LIBRARY             VALID   15-NOV-03

SQL>
SQL> select D_OBJ#, do.object_name, do.object_type dtyp, do.status dsta,
  2  D_TIMESTAMP, ORDER#, P_OBJ#, po.object_name, po.object_type ptyp,
po.status psta,
  3   P_TIMESTAMP from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
  4  where D_OBJ# = do.object_ID and P_OBJ#= po.object_ID and do.object_ID =
  5  (select object_id from dba_objects where OBJECT_NAME=’LCR$_ROW_RECORD’
and OBJECT_TYPE = ‘TYPE BODY’);

    D_OBJ#
———-
OBJECT_NAME
——————————————————————————

DTYP                DSTA    D_TIMESTA     ORDER#     P_OBJ#
——————- ——- ——— ———- ———-
OBJECT_NAME
——————————————————————————

PTYP                PSTA    P_TIMESTA
——————- ——- ———
   1462858
LCR$_ROW_RECORD
TYPE BODY           INVALID 09-NOV-07          0    1462820
LCR$_ROW_RECORD
TYPE                INVALID 09-NOV-07

 

 

可以尝试在startup upgrade模式下重新运行catproc.sql脚本来解决组件失效的问题(catproc.sql is to try validate the registry and that should resolve the issue)。

职业生涯的一个中转站

00年代的最后一个月,总算没有辜负这三年来对于oracle的学习;在一个月的无尽等待不断反思中,祝贺信终于来了:

Dear Xiang Bing,

Congratulations! on the successful completion of the Oracle DBA 10g Certified Master practicum.

You are now a member of an elite group of Oracle professionals.  You will receive your Oracle DBA 10g Certified Master fulfillment kit that includes a congratulations letter, OCM certificate, OCM ID card, and denim OCM shirt to the mailing address mentioned in your Oracle DBA 10g OCM Hands-on course requirement Form.

OCM, Hello World!

10年代是我们80后的年代!!

使用logminer日志挖掘技术

使用logminer日志挖掘技术

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

[gview file=”https://www.askmaclean.com/wp-content/uploads/2014/11/less11_Flashback.pdf”]

使用Oracle闪回flashback技术

使用Oracle闪回flashback技术

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

[gview file=”https://www.askmaclean.com/wp-content/uploads/2014/11/less11_Flashback.pdf”]

沪ICP备14014813号

沪公网安备 31010802001379号