|
|
Oracle 2011. 8. 11. 15:19
| 수정 날짜 29-APR-2010 유형 PROBLEM 상태 PUBLISHED |
|
Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
SymptomsDatapump export from the client side completes with errors. Example:
expdp system/password full=y dumpfile=full_exp.dmp logfile=full_explog
...
. . exported "CALYPSO"."KICKOFF_CFG" 0 KB 0 rows
. . exported "CALYPSO"."LE_AGR_CHILD" 0 KB 0 rows
. . exported "CALYPSO"."LE_ATTR_CODE" 0 KB 0 rows
UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1
However, reviewing the logfile shows that the "job successfully completed"
CauseThis issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK
SolutionThe expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS.
Therefore, if the export logfile says "job successfully completed", the dump file generated by the job should be fine.
You can simply ignore the errors, since the dump file is still valid for an import.
In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn't. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well.
.
With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It's impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn't address other possible failures that look very similar on the client side.
.
Relying on information in the log file is one way to verify that the job actually completed successfully. Problems like this one became much more intermittent by the 10.2.0.4 release and are rarely, if ever, seen in 11.1 or later.
ReferencesBUG:5969934 - EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK
|
 관련 자료
|
출처 : Oracle Support
Data Pump에 생각보다 많은 Bug들이 있다. 하지만, 성능상으로는 이런 Bug들을 감수할만큼 효율적이니 잘 피해서 사용해보자.
Oracle 2011. 8. 11. 11:43
버그 속성
| 유형 |
B - Defect |
제품 버전에서 수정됨 |
11.1 |
| 중요도 |
2 - Severe Loss of Service |
제품 버전 |
10.2.0.2.0 |
| 상태 |
80 - Development to Q/A |
플랫폼 |
212 - IBM AIX on POWER Systems (64-bit) |
| 생성 날짜 |
12-Jul-2006 |
플랫폼 버전 |
- |
| 업데이트 날짜 |
16-Mar-2011 |
기본 버그 |
- |
| 데이터베이스 버전 |
10.2.0.2.0 |
|
|
| 영향을 받는 플랫폼 |
Generic |
|
|
| 제품 소스 |
Oracle |
|
|
관련 제품
| 라인 |
Oracle Database Products |
제품군 |
Oracle Database |
| 영역 |
Oracle Database |
제품 |
5 - Oracle Server - Enterprise Edition |
Hdr: 5387030 10.2.0.2.0 RDBMS 10.2.0.2.0 TXN MGMT LOCAL PRODID-5 PORTID-212
Abstract: AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
*** 07/12/06 08:56 am REQUEST TEXT ***
So when:
a) not using GUARANTEED retention at undo tablespace level and
b) not using auto-extensible data files (fixed sized data file). then the
tuned_undoretention stays high to satisfy the maxquerylen at that time and
causes out of undo space error. The tuned_undoretention value did not return
to
a lower value even after 36 hours of quiet time (not a single query was
submitted).
Then, we changed undo tablespace's data files to be auto_extensible, but did
not allow it to actually extend any further. The max size was set to the
current allocated size.
With this change the tuned_undoretention returned to the set value for und
o_retention parameter immediately after the long running query completed.
This is same behavior we have seen in 10g R1 without the need for
auto_extensible data files.
And that is the difference in 10g R2 from R1. This change forces the bogus
use
of auto_extensible option.
I will upload a text file for you to review in support of what I mentioned
here.
*** 07/12/06 08:56 am BUSINESS NEED ***
It is causes confusion and misleading to customer needs.
So, this workaround is not a big deal, but 10g R2 is not backward compatible
to 10g R1 in this respect. And is not clearly documented as such. If there is
no chance to change this to match what 10g R1did, then I think a Metalink
Note to that effect will clear things up for all who may run into this
issue.
*** 07/12/06 08:57 am RESPONSE ***
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_TBS
SQL>
SQL> alter session set nls_date_format='mm/dd/yy hh24:mi:ss';
Session altered.
SQL> set lines 132
SQL> get f1.sql
1 select begin_time
2 , end_time
3 , undoblks
4 , tuned_undoretention
5 , maxquerylen
6 , maxqueryid
7* from v$undostat
SQL> /
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
07/05/06 14:32:23 07/05/06 14:37:25 1 900
0
07/05/06 14:22:23 07/05/06 14:32:23 3 900
0
07/05/06 14:12:23 07/05/06 14:22:23 4 900
0
07/05/06 14:02:23 07/05/06 14:12:23 2 900
0
07/05/06 13:52:23 07/05/06 14:02:23 50 900
0
07/05/06 13:42:23 07/05/06 13:52:23 5 900
0
07/05/06 13:32:23 07/05/06 13:42:23 2 900
0
07/05/06 13:22:23 07/05/06 13:32:23 4 900
0
07/05/06 13:12:23 07/05/06 13:22:23 6 900
0
07/05/06 13:02:23 07/05/06 13:12:23 0 900
0
07/05/06 12:52:23 07/05/06 13:02:23 63 900
0
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
07/05/06 12:42:23 07/05/06 12:52:23 265 1800
962 271b023vduant
07/05/06 12:32:23 07/05/06 12:42:23 449 1201
362 271b023vduant
07/05/06 12:22:23 07/05/06 12:32:23 404 2948
2108 271b023vduant
07/05/06 12:12:23 07/05/06 12:22:23 372 2348
1506 271b023vduant
07/05/06 12:02:23 07/05/06 12:12:23 353 1747
906 271b023vduant
07/05/06 11:52:23 07/05/06 12:02:23 513 1421
582 271b023vduant
07/05/06 11:42:23 07/05/06 11:52:23 169 1005
164 fby3wmzn3hzvw
07/05/06 11:32:23 07/05/06 11:42:23 3 900
0
07/05/06 11:22:23 07/05/06 11:32:23 2 900
0
07/05/06 11:12:23 07/05/06 11:22:23 11 900
0
07/05/06 11:02:23 07/05/06 11:12:23 4 900
0
*** 07/12/06 08:58 am RESPONSE ***
SQL> get f2.sql
1 select tablespace_name
2 , retention
3 from dba_tablespaces
4* where tablespace_name = 'UNDO_TBS'
SQL> /
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDO_TBS NOGUARANTEE
SQL> col file_name for a45
SQL>
SQL> get f3.sql
SQL> get f3.sql
1 select file_name
2 , autoextensible
3 , bytes/1048576 MB
4 , increment_by
5 , maxbytes/1048576 Max_MB
6 from dba_data_files
7* where tablespace_name = 'UNDO_TBS'
SQL> /
FILE_NAME AUT MB INCREMENT_BY
MAX_MB
--------------------------------------------- --- ---------- ------------
----------
/u10/oradata/VZCT/undo_tbs_02.dbf YES 3000 128
3000
/u11/oradata/VZCT/undo_tbs_01.dbf YES 2000 128
2000
/u13/oradata/VZCT/undo_tbs_03.dbf YES 1000 128
1000
*** 07/26/06 02:54 pm *** (CHG: Sta->11 Asg->NEW OWNER)
*** 07/27/06 12:09 pm ***
*** 07/28/06 02:22 pm ***
*** 07/28/06 02:22 pm ***
*** 08/16/06 10:17 am ***
*** 08/16/06 02:36 pm *** ESCALATED
*** 08/16/06 02:36 pm ***
*** 08/21/06 10:24 am ***
*** 08/21/06 02:17 pm ***
*** 08/21/06 02:17 pm ***
*** 08/21/06 04:07 pm *** (CHG: Sta->30)
*** 08/21/06 04:07 pm ***
*** 09/18/06 09:12 am *** (CHG: Sta->16)
*** 09/18/06 09:12 am ***
*** 09/18/06 09:49 am *** (CHG: Sta->11)
*** 09/18/06 01:15 pm *** (CHG: Sta->30)
*** 09/18/06 01:15 pm ***
*** 09/19/06 08:13 am *** (CHG: Sta->16)
*** 09/19/06 08:13 am ***
*** 09/19/06 10:56 am *** (CHG: Sta->30)
*** 09/19/06 10:56 am ***
*** 09/25/06 08:25 am *** (CHG: Sta->11)
*** 09/25/06 08:25 am ***
*** 09/26/06 06:30 am ***
*** 09/26/06 02:33 pm ***
*** 09/26/06 02:39 pm ***
*** 09/26/06 02:43 pm *** (CHG: Sta->30)
*** 09/26/06 02:43 pm ***
*** 09/28/06 04:12 pm ***
TAR: 5337754.992
----
PROBLEM:
--------
1) Unused Undo segments are not being release, originally the database was
running on 10.1 without problem, but after upgrade to release 10.2.0.2 the
problem started.
2) UNDO_RETENTION parameter was correctly set = 900
3) Undo Tablespace was fixed sized, in other word the Undo tablespace was not
enabled with Autoextensible option for the associated datafiles.
4) Also, the Undo tablespace was never enabled with the guaranty retention
option.
5) So, after upgrade to 10.2 the problem is the TUNED_UNDORETENTION stayed
very high causing UNDO_TABLESPACE run out of disk space and it is not
releasing the unused undo segments.
6) The Oracle Undo Advisor is always asking to increase the Undo tablespace
(e.g. 17032 MB) which is not acceptable.
7) TUNED_UNDORETENTION column (v$undostat) is always increasing and never
returning back to 900 as on 10gR1:
=============================================================
SQL> get f1.sql
1 select begin_time, end_time, undoblks, tuned_undoretention, maxquerylen,
maxqueryid
2* from v$undostat
SQL> /
BEGIN_TIME END_TIME UNDOBLKS TUNED_UNDORETENTION
MAXQUERYLEN MAXQUERYID
----------------- ----------------- ---------- -------------------
----------- -------------
04/15/06 12:37:38 04/15/06 12:40:00 1443 61233
36031 gf37j3d3q2h15
04/15/06 12:27:38 04/15/06 12:37:38 6285 61261
36031 gf37j3d3q2h15
04/15/06 12:17:38 04/15/06 12:27:38 5450 60206
35430 gf37j3d3q2h15
04/15/06 12:07:38 04/15/06 12:17:38 6348 60110
34830 gf37j3d3q2h15
04/15/06 11:57:38 04/15/06 12:07:38 5005 60094
34229 gf37j3d3q2h15
04/15/06 11:47:38 04/15/06 11:57:38 5440 59954
33628 gf37j3d3q2h15
04/15/06 11:37:38 04/15/06 11:47:38 6662 58994
33028 gf37j3d3q2h15
04/15/06 11:27:38 04/15/06 11:37:38 5197 58995
32427 gf37j3d3q2h15
04/15/06 11:17:38 04/15/06 11:27:38 5597 59184
31826 gf37j3d3q2h15
04/15/06 11:07:38 04/15/06 11:17:38 6035 59943
31225 gf37j3d3q2h15
04/15/06 06:47:38 04/15/06 06:57:38 5185 56068
15607 gf37j3d3q2h15
04/15/06 06:37:38 04/15/06 06:47:38 5002 54669
15007 gf37j3d3q2h15
04/15/06 06:27:38 04/15/06 06:37:38 4905 54491
14406 gf37j3d3q2h15
04/13/06 21:57:38 04/13/06 22:07:38 3474 345600
213 0mdbrtbvdd9gu
04/13/06 21:47:38 04/13/06 21:57:38 25 345600
0
04/13/06 21:37:38 04/13/06 21:47:38 22 345600
0
04/13/06 21:27:38 04/13/06 21:37:38 96 345600
0
236 rows selected.
=============================================================
*** 09/28/06 04:12 pm ***
DIAGNOSTIC ANALYSIS:
--------------------
1) This behavior was diagnosed through the v$undostat view (OWC session)
2) Please check the undo_retention_too_much.txt log file.
WORKAROUND:
-----------
None.
RELATED BUGS:
-------------
None
REPRODUCIBILITY:
----------------
It is always reproducible in the customers environment.
TEST CASE:
----------
None
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
Analyst: Esteban Bernal
AIM: ebernalus
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 09/28/06 04:13 pm *** (CHG: Sta->16)
*** 09/28/06 04:19 pm ***
*** 09/28/06 04:31 pm *** (CHG: Sta->11)
*** 09/28/06 08:10 pm *** (CHG: Sta->30)
*** 09/28/06 08:10 pm ***
*** 09/29/06 06:34 am *** (CHG: Sta->11)
*** 09/29/06 06:34 am ***
*** 09/29/06 05:05 pm *** (CHG: Sta->30)
*** 09/29/06 05:05 pm ***
*** 10/02/06 02:21 pm *** (CHG: Sta->11)
*** 10/02/06 02:21 pm ***
*** 10/02/06 04:29 pm *** (CHG: Sta->30)
*** 10/02/06 04:29 pm ***
*** 10/03/06 10:59 am *** (CHG: Sta->11)
*** 10/03/06 10:59 am ***
*** 10/03/06 11:19 am ***
*** 10/03/06 12:27 pm ***
*** 10/03/06 01:52 pm ***
*** 10/03/06 01:52 pm ***
*** 10/03/06 04:00 pm ***
*** 10/06/06 11:56 am ***
*** 10/06/06 12:13 pm ***
*** 10/06/06 12:13 pm ***
*** 10/09/06 06:11 am ***
*** 10/10/06 08:42 am ***
*** 10/12/06 06:04 am ***
*** 10/16/06 11:15 am ***
*** 10/18/06 12:01 pm ***
*** 10/23/06 07:59 am ***
*** 10/23/06 10:58 am ***
*** 10/26/06 04:43 pm ***
*** 10/26/06 06:28 pm ***
*** 11/01/06 12:19 pm ***
*** 11/09/06 03:54 pm ***
*** 11/13/06 03:52 pm ***
*** 11/14/06 11:19 am ***
*** 11/15/06 08:43 am ***
*** 11/15/06 08:43 am ***
*** 11/17/06 02:57 pm *** (CHG: Sta->30)
*** 11/17/06 02:57 pm ***
*** 11/17/06 04:09 pm ***
*** 11/27/06 11:04 am ***
*** 11/27/06 11:05 am *** (CHG: Sta->11)
*** 11/27/06 11:05 am ***
*** 11/29/06 09:45 am ***
*** 11/30/06 06:52 am ***
*** 11/30/06 06:56 am ***
*** 11/30/06 07:36 am ***
*** 11/30/06 12:36 pm ***
*** 12/04/06 03:26 pm ***
*** 12/04/06 05:21 pm ***
*** 12/05/06 02:15 pm ***
*** 12/05/06 02:16 pm *** (CHG: Sta->30)
*** 12/05/06 02:49 pm ***
*** 12/05/06 02:50 pm *** (CHG: Sta->11)
*** 12/05/06 02:50 pm ***
*** 12/05/06 03:52 pm *** (CHG: Sta->30)
*** 12/05/06 03:52 pm ***
*** 12/06/06 07:35 am *** (CHG: Sta->11)
*** 12/06/06 07:35 am ***
*** 12/06/06 08:25 am *** (CHG: Sta->30)
*** 12/06/06 08:25 am ***
*** 12/06/06 03:12 pm ***
*** 12/06/06 03:15 pm *** (CHG: Sta->11)
*** 12/06/06 03:15 pm ***
*** 12/06/06 04:09 pm ***
*** 12/06/06 04:34 pm ***
*** 12/07/06 10:05 am ***
*** 12/07/06 05:47 pm *** (CHG: Sta->30)
*** 12/07/06 05:47 pm ***
*** 12/08/06 07:22 am *** (CHG: Sta->11)
*** 12/08/06 07:23 am ***
*** 12/08/06 10:20 am *** (CHG: Sta->30)
*** 12/08/06 10:20 am ***
*** 12/08/06 12:09 pm *** (CHG: Sta->11)
*** 12/08/06 12:09 pm ***
*** 12/11/06 04:29 pm ***
*** 12/12/06 07:22 am ***
*** 12/14/06 11:37 am ***
*** 12/21/06 04:56 pm ***
*** 12/22/06 04:06 pm ***
*** 01/02/07 06:18 am ***
*** 01/02/07 04:25 pm ***
*** 01/03/07 03:48 pm ***
*** 01/03/07 04:53 pm ***
*** 01/04/07 04:22 pm ***
*** 01/08/07 06:49 am ***
*** 01/11/07 04:01 pm ***
*** 01/12/07 12:28 pm ***
*** 01/12/07 11:02 pm ***
REDISCOVERY INFORMATION:
Look for:
- System Managed Undo
undo_management=auto in init.ora
- Fixed size undo tablespace
select autoextensible from dba_data_files
where tablespace_name='<current_undo_ts>'
returns "No" for all the undo ts datafiles.
- The undo tablespace is already sized such that
it always has more than enough space to store
all the undo generated within the undo_retention
time, and the in-use undo space never exceeds the
undo tablespace warning alert threshold (see
below for the query to show the thresholds).
- The tablespace threshold alerts recommend that
the DBA add more space to the undo tablespace:
select creation_time, metric_value, message_type,
reason, suggested_action
from dba_outstanding_alerts
where object_name='<current_undo_ts>';
returns a suggested action of:
"Add space to the tablespace"
Or, this recommendation has been reported in the
past but the condition has now cleared:
select creation_time, metric_value, message_type,
reason, suggested_action, resolution
from dba_alert_history
where object_name='<current_undo_ts>';
- The undo tablespace in-use space exceeded the
warning alert threshold at some point in time:
To see the warning alert percentage threshold:
select object_type, object_name,
warning_value, critical_value
from dba_thresholds
where object_type='TABLESPACE';
To see the (current) undo tablespace percent of
space in-use:
select
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name='<current_undo_ts>'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='<current_undo_ts>')
"PCT_INUSE"
from dual;
WORKAROUND:
There are 3 possible alternate workarounds (any one of
these should resolve the problem of the alerts triggering
unnecessarily):
1. Set the autoextend and maxsize attribute of each datafile
in the undo ts so it is autoextensible and its maxsize is
equal to its current size so the undo tablespace now has
the autoextend attribute but does not autoend:
alter database datafile '<datafile_flename>'
autoextend on maxsize <current_size>;
With this setting, v$undostat.tuned_undoretention is not
calculated based on a percentage of the undo tablespace
size, instead v$undostat.tuned_undoretention is set to
the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
2. Set the following hidden parameter:
in init.ora: _smu_debug_mode=33554432
or use: alter system set "_smu_debug_mode" = 33554432;
With this setting, v$undostat.tuned_undoretention is not
calculated based on a percentage of the fixed size undo
tablespace, instead v$undostat.tuned_undoretention is
set to the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
3. Set the following hidden parameter:
in init.ora: _undo_autotune = false
or use: alter system set "_undo_autotune" = false;
With this setting, v$undostat (and therefore
v$undostat.tuned_undoretention) is not maintained and
and the undo_retention used is the one specified in
init.ora
RELEASE NOTES:
]]In 10.2, for a non-autoextend SMU undo tablespace which
]]is already adequately sized to handle the transaction
]]workload for the specified undo_retention time, the
]]tablespace threshold alert mechanism may still recommend
]]that the DBA add more space to the undo tablespace.
*** 01/12/07 11:20 pm ***
*** 01/12/07 11:20 pm *** (CHG: Sta->80)
*** 01/15/07 03:53 am ***
*** 01/16/07 04:21 am ***
*** 01/16/07 06:28 am ***
*** 01/16/07 08:24 am ***
*** 01/16/07 11:18 am ***
*** 01/16/07 02:06 pm ***
*** 01/16/07 02:07 pm ***
*** 01/16/07 02:07 pm ***
*** 01/16/07 03:56 pm ***
*** 01/16/07 10:24 pm ***
*** 01/17/07 06:42 am ***
*** 01/17/07 08:45 am ***
*** 01/17/07 11:22 am ***
*** 01/17/07 07:17 pm ***
*** 01/17/07 07:19 pm ***
*** 01/19/07 11:25 am ***
*** 01/19/07 12:40 pm ***
*** 01/19/07 02:10 pm ***
*** 01/23/07 07:50 am ***
*** 01/23/07 01:01 pm *** (CHG: Sta->16)
*** 01/23/07 01:01 pm ***
*** 01/23/07 04:36 pm *** (CHG: Sta->30)
*** 01/23/07 04:36 pm ***
*** 01/24/07 08:07 am ***
*** 01/24/07 08:57 am ***
*** 01/24/07 08:58 am *** (CHG: Sta->11)
*** 01/24/07 08:58 am ***
*** 01/24/07 02:21 pm ***
*** 01/24/07 03:32 pm ***
*** 01/25/07 02:44 pm ***
*** 01/25/07 02:44 pm *** (CHG: Sta->80)
*** 01/25/07 02:44 pm ***
*** 02/20/07 05:57 am ***
*** 02/21/07 02:55 am ***
*** 02/23/07 05:04 am ***
*** 03/05/07 07:18 am ***
*** 03/05/07 07:23 am ***
*** 03/06/07 12:13 am ***
*** 03/12/07 07:14 am ***
*** 04/11/07 08:11 pm ***
*** 04/11/07 08:13 pm ***
*** 04/13/07 04:19 am ***
*** 04/13/07 04:20 am ***
*** 04/13/07 04:23 am ***
*** 04/26/07 05:03 pm ***
*** 04/26/07 05:30 pm ***
*** 05/02/07 08:14 am ***
*** 05/03/07 12:40 am ***
*** 05/03/07 12:42 am ***
*** 07/05/07 04:19 pm ***
*** 07/06/07 05:24 pm ***
*** 07/12/07 05:21 pm ***
*** 09/14/07 02:22 pm ***
*** 09/17/07 11:45 pm ***
*** 09/17/07 11:47 pm ***
*** 09/18/07 10:29 am ***
*** 09/28/07 04:02 pm ***
*** 10/09/07 03:41 pm ***
*** 10/09/07 03:43 pm ***
*** 10/18/07 02:12 pm ***
*** 10/18/07 10:10 pm ***
*** 10/18/07 10:12 pm ***
*** 11/21/07 08:54 am ***
*** 11/21/07 01:01 pm ***
*** 11/21/07 01:01 pm ***
*** 11/30/07 02:59 am ***
*** 11/30/07 12:09 pm ***
*** 12/02/07 12:50 am ***
*** 12/11/07 05:30 am ***
*** 12/11/07 09:25 am ***
*** 02/10/08 10:04 pm ***
*** 04/08/08 12:55 pm ***
*** 04/09/08 12:51 am ***
*** 04/17/08 03:49 am ***
*** 04/17/08 03:49 am ***
*** 04/17/08 04:06 am ***
*** 04/17/08 04:06 am ***
*** 04/17/08 10:57 pm ***
*** 04/17/08 11:08 pm ***
*** 04/17/08 11:08 pm ***
*** 04/18/08 03:55 am ***
*** 04/18/08 03:55 am ***
*** 04/18/08 07:55 am ***
*** 04/18/08 02:01 pm ***
*** 04/18/08 02:01 pm ***
*** 04/18/08 02:33 pm ***
*** 04/21/08 12:58 am ***
*** 04/24/08 04:40 am ***
*** 04/24/08 04:49 am ***
*** 04/24/08 04:54 am ***
*** 04/28/08 05:53 am ***
*** 04/28/08 11:43 am ***
*** 04/28/08 12:32 pm *** (CHG: Sta->11)
*** 04/28/08 12:32 pm ***
*** 04/28/08 01:13 pm ***
*** 04/28/08 01:13 pm *** (CHG: Sta->80)
*** 04/28/08 01:13 pm ***
*** 04/28/08 11:03 pm ***
*** 05/05/08 06:32 am ***
*** 05/19/08 03:41 am ***
*** 05/19/08 06:47 am ***
*** 05/19/08 06:47 am ***
*** 05/21/08 11:44 pm ***
*** 05/22/08 10:41 am ***
*** 05/22/08 06:58 pm ***
*** 05/22/08 06:58 pm ***
*** 06/12/08 05:16 am ***
64-bit) for base bug 7145055
*** 06/12/08 06:35 am ***
*** 06/12/08 06:39 am ***
bug 7145055
*** 06/12/08 06:43 am ***
*** 06/12/08 06:46 am ***
base bug 7145055
*** 07/21/08 01:36 am ***
base bug 7117498
*** 07/25/08 04:54 pm ***
*** 08/08/08 02:17 am ***
base bug 7285182
*** 08/11/08 12:47 pm ***
*** 08/19/08 07:34 am ***
*** 08/20/08 06:16 pm ***
*** 08/22/08 06:39 am ***
7342997
*** 08/29/08 02:40 pm ***
*** 09/18/08 04:37 am ***
*** 10/02/08 11:57 pm ***
*** 12/03/08 01:24 am ***
bug 5471994 bug 4367986 bug 6085625 bug 5648872 bug 5863277 bug 5177766
bug 5254759 bug 6452485
*** 01/15/09 06:03 am ***
*** 01/27/09 11:23 pm ***
*** 02/25/09 10:59 pm ***
*** 03/24/09 02:03 am ***
bug 5476873 bug 5599293 bug 5922239
*** 03/27/09 12:19 pm ***
*** 04/09/09 03:23 am ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:57 pm ***
*** 04/21/09 08:58 pm ***
*** 04/21/09 08:58 pm ***
*** 06/02/09 09:08 am ***
bug 5476873 bug 5599293 bug 5922239 bug 5081798 bug 5057695
*** 06/23/09 04:27 am ***
bug 5476873 bug 5599293 bug 5922239 bug 5010657 bug 6859515
*** 07/09/09 09:16 am ***
*** 09/08/09 01:46 am ***
*** 01/28/10 09:21 am ***
*** 01/28/10 01:21 pm ***
*** 01/29/10 08:31 am ***
*** 01/29/10 08:32 am ***
*** 01/30/10 01:58 am ***
*** 02/03/10 03:09 pm ***
*** 02/03/10 03:09 pm ***
*** 04/02/10 08:30 am ***
*** 07/01/10 05:38 pm ***
*** 08/30/10 03:03 pm ***
*** 09/15/10 01:08 am ***
*** 11/09/10 07:39 am ***
*** 12/15/10 02:05 pm ***
*** 12/19/10 11:21 pm ***
*** 12/20/10 03:34 am ***
*** 12/21/10 10:47 pm ***
*** 12/21/10 10:48 pm ***
*** 12/29/10 07:14 am ***
*** 01/03/11 10:53 pm ***
*** 01/04/11 01:08 am ***
*** 01/11/11 12:19 am ***
*** 01/26/11 02:47 am ***
*** 03/03/11 09:15 am ***
*** 03/16/11 05:26 pm ***
Oracle 2011. 8. 11. 11:39
| Subject: |
Automatic Tuning of Undo_retention Causes Space Problems |
| |
Doc ID: |
Note:420525.1 |
Type: |
PROBLEM |
| |
Last Revision Date: |
26-MAY-2008 |
Status: |
PUBLISHED |
Applies to:Oracle Server - Enterprise Edition - Version: 10.2 to 11.0
This problem can occur on any platform.
Symptoms
Look for:
1.) System Managed Undo
undo_management=auto in init.ora file
2.) Fixed size undo tablespace
SQL> select autoextensible from dba_data_files where tablespace_name='<current_undo_tablespace >'
returns "No" for all the undo tablespace datafiles.
3.) The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds).
4.) The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace:
SQL> select creation_time, metric_value, message_type,reason, suggested_action from dba_outstanding_alerts where object_name='<current_undo_ts>';
returns a suggested action of: "Add space to the tablespace"
Or,
This recommendation has been reported in the past but the condition has now cleared:
SQL> select creation_time, metric_value, message_type, reason, suggested_action, resolution from dba_alert_history where object_name='<current_undo_ts>';
5.) The undo tablespace in-use space exceeded the warning alert threshold at some point in time:
To see the warning alert percentage threshold:
SQL> select object_type, object_name, warning_value, critical_value from dba_thresholds where object_type='TABLESPACE';
To see the (current) undo tablespace percent of space in-use:
SQL> select
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name='<current_undo_ts>'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='<current_undo_ts>')
"PCT_INUSE"
from dual;
CauseThis is due to Bug 5387030 that is fixed in 11.1 version of RDBMS server.
Solution
Apply the patch for the Bug 5387030. Check metalink for patch availability for your platform and RDBMS version.
Workaround
There are 3 possible alternate workarounds (any one of these should resolve the problem of the alerts triggering unnecessarily):
1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend:
SQL> alter database datafile '<datafile_flename>'
autoextend on maxsize <current_size>;
With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.
2.) Set the following hidden parameter in init.ora file:
_smu_debug_mode=33554432
or
SQL> Alter system set "_smu_debug_mode" = 33554432;
With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.
3.) Set the following hidden parameter in init.ora:
_undo_autotune = false
or
SQL> Alter system set "_undo_autotune" = false;
With this setting, v$undostat (and therefore v$undostat.tuned_undoretention) is not maintained and and the undo_retention used is the one specified in init.ora file.
ReferencesBug 5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
출처 : Oracle Support
alter system set "_IN_MEMORY_UNDO"=false;
Alter system set "_undo_autotune" = false;
Oracle 2011. 8. 11. 11:30
| 수정 날짜 20-MAY-2010 유형 PROBLEM 상태 PUBLISHED |
|
Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
SymptomsRunning an expdp on a certain table usually takes an hour.
However, it now runs for over a day. The problem appears to be with one table (the largest in the database).
When the expdp is started the first table that it processes has an estimated process time of over 24hrs.
When the same export is run from two other servers, it shows the elapsed time for the same table as around 20 minutes
ERROR
-----------------------
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
ORA-31693 : Table data object "MERIDIAN"."LINEPAST" failed to load/unload and is being skipped due to error:
ORA-02354 : error in exporting/importing data
ORA-01555 : snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small
The table does not have any LOB columns.
Increasing the undo_retention parameter did not help.
ChangesMost of the rows in the table were recently updated.
CauseUpdating the rows in the table have caused high fragmentation (chained/migrated rows), which increased significantly the time to perform the export of this table.
Solution1.Recreate the fragmented table through :
SQL> create table <new_table_name> as (select * from <fragmented_table);
then drop the fragmented table.
You may re-name the <new_table_name> to the old table name.
2. Perform the expdp job. (this time it ended in few minutes)
You may also refer to Note 746778.1 How to Identify, Avoid and Eliminate Chained and Migrated Rows ?
ReferencesNOTE:746778.1 - How to Identify, Avoid and Eliminate Chained and Migrated Rows ?
|
관련 자료
출처 : oracle support
('' ) NOTE:746778.1의 내용 |
Cachesys 2011. 3. 25. 10:33
Intersystems 한국지사에서 근무하시던 배영민 실장님께서 몇 년 전부터 작업하시던 Cachesys에 대한
한글 교재가 아쉽게도 책으로 출간되지는 못하고, PDF 파일로 배포를 해주시네요.
필요하신 분들은 첨부파일을 받아서 공부해보시기 바랍니다.
Cachesys DBMS Version 정보가 보이지는 않는데요. 제가 보기에는 2007 Version 이상인듯 합니다.
그 이하 Version을 사용하셔서 개념이 크게 다르진 않으니 도움이 될 듯 합니다.
www.intersystems.com
June.Seo@Intersystems.com
Oracle 2011. 2. 22. 17:52
PL/SQL을 이용하여 생성한 Package, Procedure, Function 등 다양한 OBJECTS와 Tables간의
Dependency 정보를 확인하기 위해서 어떻게 해야 할까??
개발할 때 마다, 이를 관리하기 위한 정보를 생성해??
아니다. Oracle Dictionary 중에서 "USER_DEPENDENCIES"를 조회하면 된다.
USER_OBJECTS나 USER_TABLES 정보와 잘 Join해서 검색한다면 보다 다양한 형태로 원하는 Dependency
정보를 확인해볼 수 있을 것입니다.
그러면 이쯤에서 하나 더....
Package나 Procedure, Function등을 개발하고, 이를 Input Parameter를 Application 개발자에게 문서로
전달해야 한다면 어떻게 할 것인가??
Source를 일일이 하나씩 열어서 문서로 옮겨 적어??
Oh~~ No!!
눈치 빠른 분이라면 이미 아시겠지만, 이 또한 Dictionary 중에서 USER_ARGUMENTS 정보를 이용하면 된다.
Oracle로 개발하고, 이를 문서화하고자 한다면 일단 Dictionary에 있는 정보를 잘 참고해보도록 하자.
정말 존재하지 않는 Data를 문서화 하려고 한다면야 어쩔 수 없겠지만, 활용할 수 있는 정보들이 있음에도
불구하고 일일이 백지에 Typing으로 입력하려면 그야 말로 "머리가 나쁘면 손발이 고생한다"는 속담을
상기시켜주는 꼴이다.
제발 이렇게 시간 낭비하지 말자. 개발만 해도 부족한 시간 아니던가....
Oracle 2011. 2. 18. 10:45
With the REF_CURSOR you can return a recordset/cursor from a stored procedure.
There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.
The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:
create or replace
package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
The pl/sql procedure that returns a ref-cursor looks like this:
/** until Oracle 9 */
create or replace
procedure test( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.
/** From Oracle 9 */
create or replace
procedure test( p_deptno IN number,
p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
/* Strong type */
create or replace
procedure test( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.STRONG REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Selecting the ref_cursor from JDBC
To get the cursor from Java you can use the following JDBC-code:
public void method() throws SQLException{
Connection conn = getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
int deptno = 10;
Object temp;
try{
cstmt = conn.prepareCall("begin test(?,?); end;");
cstmt.setInt(1, deptno);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
while (rs.next()){
for (int j=0;j< columnCount;j++){
temp = rs.getObject(j+1);
}
}
} finally {
if (!rs==null){
rs.close();
}
if (!stmt==null){
stmt.close();
}
if (!conn==null){
conn.close();
}
}
}
Calling ref-cursor from pl/sql
create or replace procedure test_call is
c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;
r_emp c_emp%rowtype;
begin
test(10,c_cursor);
loop
fetch c_cursor into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.name);
end loop;
close c_cursor;
end test_call;
출처 : http://www.oradev.com/ref_cursor.jsp
Oracle 2011. 2. 18. 10:31
Oracle9iR2 Documents에서 SQL Reference 문서를 찾은 후, UPDATE 구문에 대한 설명을
확인해보시면, Update 구문이 아래와 같은 구조를 같는다고 설명이 되어 있습니다.
오른쪽 하단에 보시면 Returning_Clause라고 존재하죠.
하지만, 실제로는 많이 사용되지도 않을 뿐더러, 각 교육기관을 통해서 배울 때, 대부분 알고 있듯이
UPDATE table_name SET update_contents WHERE where_clause;
로만 보아왔고, 사용하셨을 것입니다.
Returning_Clause를 잘 사용하면 도움이 될 수 있으니 일단 알고 넘어가도록 하겠습니다.
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3; 위의 SQL문을 확인해보면 아래와 같습니다.
last_name이 'Jones'인 사람을 찾아서 jod_id와 salary, department_id 값을 변경해주고,
bnd1, bnd2, bnd3의 변수에 salary*0.25, last_name, department_id 값을 Return 하도록 작성된
것입니다.
흔히 이와 같은 형태를 구현할 때, 아래와 같이 2개의 SQL문을 작성하여 처리하는 경우가 많습니다.
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones';
SELECT salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd
FROM employees
WHERE last_name = 'Jones';
하지만, 동일한 Table에 2번을 접근해야 한다는 점에서 비효율적이죠.
이럴 때, 앞서 제시한 예제 구문처럼 Returning Clause를 이용한다면 보다 효율적으로 작업을 진행할
수 있을듯 합니다.
단, 아래와 같은 경우에는 Returning Clause를 사용할 수 없으니 숙지하시기 바랍니다.
(생각해보면 당연하게 받아 들여질 수 있는 내용입니다.)
- Specify the
returning_clause for a multitable insert.
- Use this clause with parallel DML or with remote objects.
- Retrieve
LONG types with this clause.
- Specify this clause for a view on which an
INSTEAD OF trigger has been defined.
Oracle 2010. 12. 15. 17:57
ㅇ 환경적 요소
Oracle 9iR2이 설치된 DB 서버 한개와 Oracle 10gR2이 설치된 DB 서버 한개가 각각 물리적으로 떨어져서
구성되어 있는 환경에서 DB Link를 이용하여 양쪽 DB 서버에 존재하는 Table에 접근하여 Data를 읽어가도록
하기 위해 진행된 상황
ㅇ 증상
Oracle9i의 A Table와 Oracle10g의 B Table을 조인할 경우, Query 수행 시 App에서 응답없음 상태가
되는 문제가 발생함.
ㅇ 분석
Oracle9i의 A Table은 전체 약 300만건의 Data가 존재하고, Join시 사용될 Key값에 대해서 PK가 설정되어
있는 상태이다.
Oracle10g의 B Table은 전체 약 400건의 Data가 존재하고, Join시 사용될 Key값에 대해서 PK가 설정되어
있는 상태이다.
Query문 예시) select a.key, a.value, b.name
from t_b b, t_a@db_link a
where b.key = a.key
Test Case 1) Oracle9i에 있는 t_a Table에 대해서 단일 Table Query 수행시 정상적인 실행계획을 가지고
결과값이 바로 나타남.
Test Case 2) Oracle10g에 있는 t_b Table에 대해서 단일 Table Query 수행시 정상적인 실행계획을 가지고
결과 값이 바로 나타남
Test Case 3) 예시문에 제시된 Query문 수행시, 30s가 지나도 결과값이 안나타남.
==> 이를 실행 계획을 확인해보니, t_b Table을 Index Full Scan을 하고,
t_a Table은 Remote ~~ 라고 표시되어 실행계획이 확인이 안됨.
이렇게 두 Table을 읽은 후에, Hash Join으로 처리함.
Hash로 실행계획이 풀리는 것을 보니, Oracle 9i의 t_a Table이 DB Link를 통해서
읽어오는 과정에서 Full Table Scan으로 풀려지는 것을 의심함.
==> 예시문에 제시된 Query문에 각종 Index Hint를 적용해도 해결이 안됨.
Test Case 4) Oracle10g쪽 DB Server에서 Oracle9i의 t_a Table을 읽을 때, Where 절을 이용해서
검색해본 바, 상수값을 입력하여 조회시에는 정상적으로 실행계획이 타는 것처럼
결과 값이 바로 나타남. 하지만, 상수값 대신 Oracle10g의 t_b Table의 값을 Where절에서
In을 이용해서 적용하면 Test Case 3과 동일한 증상이 나타남.
ㅇ 원인
A query with an in-subquery where the outer query is remote mapped may produce a poor execution plan
Oracle Support에서 원인을 찾아본 바, 이와 같이 DB Link의 Bug가 발생한다고 함.
ㅇ 해결방법
Oracle Support에서는 DB Patch를 시행하라고 권고하였으나, 이는 서비스 DB에서 바로 시행할 수 있는
것이 아니기에, Oracle 9i의 t_a Table을 Oracle9i에 존재하는 임의의 t_c Table과 Index Hint를 이용하여
무조건 Index를 타도록 Query문을 작성한 후, 이를 View로 생성했다.
그리고 나서 Oracle10g쪽에서 이 View를 이용해서 Join을 수행하도록 하였더니, 내가 원하는 형태로
실행 계획이 풀리면서 결과가 정상적으로 나타남.
그동안 DB Link를 너무 무심하게 가져다 사용한 것 같다는 생각이 드는 사건이었습니다. 이번 사건을 계기로
DB Link에 대해서 알아본 바, 생각보다 많은 Bug들이 존재한다는 점이 충격적으로 다가오는군요.
내가 잘 알고 있다고, 잘 사용해왔다고 생각했던 기술들이 나의 뒷통수를 후려치는 순간입니다.
"돌다리도 두들겨 건너라."라는 옛 속담처럼, 확인에 확인을 하는 습관만이 삽질과 야근으로부터
해방시켜줄 수 있는 방안이 아닐까 합니다.
Happening 2010. 11. 5. 13:10
" 쿠룻키루"라는 컴팩트한 수동식 문서 분쇄기가 일본에서 생산되고 있네요.
문서 분쇄기하면 대부분 기업에서 사용하는 덩치 큰 기기이고, 사용시 꽤나 시끄러운 소음을 유발하는데 비해서,
수동식이라 조용하고, 보관이 용이할 듯 합니다.
가정에서도 문서 분쇄기가 있었으면 할 때가 있는데...
이 정도면 하나쯤 구매해서 비치해 두는 것도 괜찮을듯 하네요.
단점이라면, 사무실에서 사용하는 문서 분쇄기에 비하면 사용할 수 있는 종이의 폭이 좁다는 것.
정보 출처 : http://blog.nikkeibp.co.jp/nd/news/2010/09/205998.shtml
|
EXPDP_ORA-1555 Err 해결방법.docx
invalid-file