10gR1Stream问题不少，熟悉的介绍下经验！

zhchtao323

hsdfghgfhgfhgf

zhchtao323

fdghfhfh

teddyboy

设置checkpoint_retension_time不就好了吗?

zhouwf0726

感谢楼上的回复！我在一楼就说了，目前我们版本是10104。


查找资料有参数控制以上信息保留时间。却发现是10gR2的。

dbms_capture_adm.alter_capture(
capture_name IN VARCHAR2,
rule_set_name IN VARCHAR2 DEFAULT NULL,
remove_rule_set IN BOOLEAN DEFAULT FALSE,
start_scn IN NUMBER DEFAULT NULL,
use_database_link IN BOOLEAN DEFAULT NULL,
first_scn IN NUMBER DEFAULT NULL,
negative_rule_set_name IN VARCHAR2 DEFAULT NULL,
remove_negative_rule_set IN BOOLEAN DEFAULT FALSE,
capture_user IN VARCHAR2 DEFAULT NULL,
checkpoint_retention_time IN NUMBER DEFAULT NULL);

在我们的数据库DESC dbms_capture_adm发现没有checkpoint_retention_time

SQL> DESC SYS.dbms_capture_adm
PROCEDURE ABORT_GLOBAL_INSTANTIATION
PROCEDURE ABORT_SCHEMA_INSTANTIATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
PROCEDURE ABORT_TABLE_INSTANTIATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
PROCEDURE ALTER_CAPTURE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CAPTURE_NAME VARCHAR2 IN
RULE_SET_NAME VARCHAR2 IN DEFAULT
REMOVE_RULE_SET BOOLEAN IN DEFAULT
START_SCN NUMBER IN DEFAULT
USE_DATABASE_LINK BOOLEAN IN DEFAULT
FIRST_SCN NUMBER IN DEFAULT
NEGATIVE_RULE_SET_NAME VARCHAR2 IN DEFAULT
REMOVE_NEGATIVE_RULE_SET BOOLEAN IN DEFAULT
CAPTURE_USER VARCHAR2 IN DEFAULT
PROCEDURE BUILD
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FIRST_SCN NUMBER OUT
PROCEDURE CREATE_CAPTURE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
CAPTURE_NAME VARCHAR2 IN
RULE_SET_NAME VARCHAR2 IN DEFAULT
START_SCN NUMBER IN DEFAULT
SOURCE_DATABASE VARCHAR2 IN DEFAULT
USE_DATABASE_LINK BOOLEAN IN DEFAULT
FIRST_SCN NUMBER IN DEFAULT
LOGFILE_ASSIGNMENT VARCHAR2 IN DEFAULT
NEGATIVE_RULE_SET_NAME VARCHAR2 IN DEFAULT
CAPTURE_USER VARCHAR2 IN DEFAULT
PROCEDURE DROP_CAPTURE
Argument Name Type In/Out Default?




看看大家有没有碰到过这个问题的？多谢！

zhouwf0726

[B]看文档中说，可以重置FIRST_SCN来PURGE数据字典，有人这么做过吗？谢谢！[/B]


A New First SCN Value and Purged LogMiner Dictionary Information
When you reset the first SCN value for an existing capture process, Oracle automatically purges LogMiner data dictionary information prior to the new first SCN setting. If the start SCN for a capture process corresponds to information that has been purged, then Oracle automatically resets the start SCN to the same value as the first SCN. However, if the start SCN is higher than the new first SCN setting, then the start SCN remains unchanged.

Figure 2-4 shows how Oracle automatically purges LogMiner data dictionary information prior to a new first SCN setting, and how the start SCN is not changed if it is higher than the new first SCN setting.

Figure 2-4 Start SCN Higher Than Reset First SCN

Text description of the illustration strms038.gif

Given this example, if the first SCN is reset again to a value higher than the start SCN value for a capture process, then the start SCN no longer corresponds to existing information in the LogMiner data dictionary. Figure 2-5 shows how Oracle resets the start SCN automatically if it is lower than a new first SCN setting.

Figure 2-5 Start SCN Lower Than Reset First SCN

Text description of the illustration strms039.gif

As you can see, the first SCN and start SCN for a capture process may continually increase over time, and, as the first SCN moves forward, it may no longer correspond to an SCN established by the DBMS_CAPTURE_ADM.BUILD procedure.

See Also:
"First SCN and Start SCN"
"Setting the Start SCN for an Existing Capture Process"
The DBMS_CAPTURE_ADM.ALTER_CAPTURE procedure in the PL/SQL Packages and Types Reference for information about altering a capture process

zhouwf0726

先找个测试环境按照如下操作做一下，如果可行，以后定期去处理。


自己整理了个测试脚本：

select 'DECLARE
   app_scn NUMBER;
   req_scn NUMBER;' || '
BEGIN
SELECT APPLIED_SCN,REQUIRED_CHECKPOINT_SCN  INTO app_scn,req_scn FROM dba_capture WHERE capture_name=''&original_capture'';' || '
DBMS_cAPTURE_ADM.ALTER_CAPTURE(
        capture_name => ''&temp_capture'',
        start_scn => acked_scn,
        first_scn => acked_scn);' ||  '
END;' || '
/'
from dual;


以下是文档说明：

Setting the First SCN for an Existing Capture Process
You can set the first SCN for an existing capture process using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. The specified first SCN must meet the following requirements:

It must be greater than the current first SCN for the capture process.
It must be less than or equal to the current applied SCN for the capture process. However, this requirement does not apply if the current applied SCN for the capture process is zero.
It must be less than or equal to the required checkpoint SCN for the capture process.

You can determine the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN
   FROM DBA_CAPTURE;


When you reset a first SCN for a capture process, information below the new first SCN setting is purged from the LogMiner dictionary for the capture process automatically. Therefore, after the first SCN is reset for a capture process, the start SCN for the capture process cannot be set lower than the new first SCN. Also, redo log files prior that contain information prior to the new first SCN setting will never be needed by the capture process.

For example, the following procedure sets the first SCN for a capture process named strm01_capture to 351232.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    first_scn    => 351232);
END;
/


--------------------------------------------------------------------------------
Note:
If the specified first SCN is higher than the current start SCN for the capture process, then the start SCN is set automatically to the new value of the first SCN.
If you need to capture changes in the redo log from a point in time in the past, then you can create a new capture process and specify a first SCN that corresponds to a previous data dictionary build in the redo log. The BUILD procedure in the DBMS_CAPTURE_ADM package performs a data dictionary build in the redo log.
You can query the DBA_LOGMNR_PURGED_LOG data dictionary view to determine which redo log files will never be needed by any capture process.

--------------------------------------------------------------------------------


See Also:
"SCN Values Relating to a Capture Process"
"The LogMiner Data Dictionary for a Capture Process"
"First SCN and Start SCN Specifications During Capture Process Creation"
"Displaying the Redo Log Files That Will Never Be Needed by Any Capture Process"


Setting the Start SCN for an Existing Capture Process
You can set the start SCN for an existing capture process using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. Typically, you reset the start SCN for a capture process if point-in-time recovery must be performed on one of the destination databases that receive changes from the capture process.

The specified start SCN must be greater than or equal to the first SCN for the capture process. You can determine the first SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN FROM DBA_CAPTURE;


Also, when you reset a start SCN for a capture process, make sure the required redo log files are available to the capture process.

For example, the following procedure sets the start SCN for a capture process named strm01_capture to 750338.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    start_scn    => 750338);
END;
/


See Also:
"SCN Values Relating to a Capture Process"
Oracle Streams Replication Administrator's Guide for information about performing database point-in-time recovery on a destination database in a Streams environment

zhouwf0726

SQL> select count(*) from system.LOGMNR_RESTART_CKPT$;

  COUNT(*)
----------
  41518673

SQL> exec dbms_capture_adm.stop_capture('STRM_CAPTURE');

PL/SQL procedure successfully completed.

SQL> exec dbms_capture_adm.alter_capture(capture_name=>'STRM_CAPTURE',start_scn=>1216512333,first_scn=>1216512333);

select count(*) from system.LOGMNR_RESTART_CKPT$   
--41518673
--39779569
--37199569
--33239569
--31509569
--29579569
--28574569
--27674569
--26072069
--25729569
--25702069
--25674569
--25634569
--25614569
--25534569
--25514569
--25502069
--25494569
--25482069
--25472069
--25464569
--25454569
--25444569
--25422069
--25389569
--25377069
--25359569
--25347069
--25337069
--25317069
--25304569
--25292069

执行到一定程度，速度超慢，几乎是1万条已提交。已经持续了5个多小时，没办法，在线nlogging rebuild index速度恢复正常。

--24907069
--24459569
--20869569
--19657069
--17964569

teddyboy

很占undo表空间
升级吧

teddyboy

查一下dba_capture_parameters看是不是 _checkpoint_retension_time 有可能隐藏了

zhouwf0726

最初由 teddyboy 发布
[B]查一下dba_capture_parameters看是不是 _checkpoint_retension_time 有可能隐藏了 [/B]

以前看过，没有找到，接下来准备升级！