昨天的logic standby 事故处理：
夜里12点左右 2台logic standby 全部停止日志apply 查看logic standby 相关进程发现:
1.select spid, type, status from v$logstdby_process no rows returns
2.2012-03-12 23:36:16 update “PROD_DATA2″.”PM_SUPPLIER” set xxx…..
ORA-01403: no data found
报出如下错误，这种错误一般是由于standby 端的表数据与primary端出现了不一致导致的，最大的麻烦莫过于这张表是一个LOB字段的表
通过数据的对比，发现源表与目标表 已经出现了很大的不一致，初步判断是由于skip操作导致的 transaction 丢失导致的数据异常
准备使用DBMS_LOGSTDBY.INSTANTIATE_TABLE 进行表的重新同步：
INSTANTIATE_TABLE Procedure
This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter. If the table already exists in the logical standby database, it will be dropped and re-created based on the table definition at the primary database. This procedure only brings over the data associated with the table, and not the associated indexes and constraints.
Use the INSTANTIATE_TABLE procedure to:
Add a table to a standby database.
Re-create a table in a standby database.
Syntax
DBMS_LOGSTDBY.INSTANTIATE_TABLE (
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
dblink IN VARCHAR2);
Parameters
Table 56-5 INSTANTIATE_TABLE Procedure Parameters
Parameter Description
schema_name
Name of the schema
table_name
Name of the table to be created or re-created in the standby database
dblink
Name of the database link account that has privileges to read and lock the table in the primary database
SQL*Plus: Release 10.2.0.5.0 – Production on Wed Mar 14 00:42:21 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> alter session set db_file_multiblock_read_count = 128;
Session altered.
SQL> alter session set sort_area_size =804857600;
Session altered.
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL> exec DBMS_LOGSTDBY.INSTANTIATE_TABLE(‘PROD_DATA2′,’PM_SUPPLIER’,’dblinkprimary’);
ERROR at line 1:
ORA-20001: ORA-31693: Table data object “PROD_DATA2″.”PM_SUPPLIER” failed to
load/unload and is being skipped due to error:
ORA-12161: TNS:internal error: partial data received
ORA-02063: preceding from
ORA-06512: at “SYS.KUPW$WORKER”, line 113
ORA-02063: preceding from
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old
ORA-02063: preceding 2 lines from DBLINKPRIMARY
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snaps
ORA-06512: at “SYS.DBMS_LOGSTDBY”, line 664
ORA-06512: at line 1
报出如下错误 这种错误一般是由于LOB 回滚段不足所导致的：
关于LOB的undo做如下说明:
RETENTION – time-based: this specifies how long older versions are to be retained.
PCTVERSION – space-based: this specifies what percentage of the LOB segment is to be used to hold older versions.
Points to Note:
RETENTION is a keyword in the LOB column definition. No value can be specified for RETENTION. The RETENTION value is implicit. If a LOB is created with database compatibility set to 9.2.0.0 or higher, undo_management=TRUE and PCTVERSION is not explicitly specified, time-based retention is used. The LOB RETENTION value is always equal to the value of the UNDO_RETENTION database instance parameter.
You cannot specify both PCTVERSION and RETENTION.
PCTVERSION is applicable only to LOB chunks/pages allocated in LOBSEGMENTS. Other LOB related data in the table column and the LOBINDEX segment use regular undo mechanism.
PCTVERSION=0: the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause“snapshot too old” errors.
PCTVERSION=100: the space allocated by older versions of LOB data can never be reused by other transactions. LOB data storage space is never reclaimed and it always increases.
RETENTION and PCTVERSION can be altered Recommendation Time-based retention using the RETENTION keyword is preferred. A high value for RETENTION or PCTVERSION may be needed to avoid “snapshot too old” errors in environments with high concurrent read/write LOB access.
查看”PROD_DATA2″.”PM_SUPPLIER”这张表的LOB属性：
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL> select bitand(flags,32) from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where
OWNER=’PROD_DATA2′ and OBJECT_NAME=’PM_SUPPLIER’); 2
BITAND(FLAGS,32)
—————-
0
SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name = ‘PM_SUPPLIER’;
TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
—————————— —————————— ———- ———-
PM_SUPPLIER SYS_LOB0000017052C00041$$ 10
修改为90
alter table prod_data2.PM_SUPPLIER modify lob(PRODUCT_DESCRIPTION) (pctversion 90);
sys@item> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name = ‘PM_SUPPLIER’; 2
TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
—————————— —————————— ———- ———-
PM_SUPPLIER SYS_LOB0000017052C00041$$ 90
重新开启同步，继续报错：
ERROR at line 1:
ORA-20001: ORA-39082: Object type
TRIGGER:”PROD_DATA2″.”TRIG_PRODUCT_SUPPLIER_INSERT” created with compilation
warnings
ORA-06512: at “SYS.DBMS_LOGSTDBY”, line 664
ORA-06512: at line 1
使用 DBMS_LOGSTDBY.INSTANTIATE_TABLE同步时 所依赖的对象会一并同步过来，这个trigger 在源端我们已经禁用了，直接drop掉这个trigger继续同步：
SQL> alter session set db_file_multiblock_read_count = 128;
Session altered.
SQL> alter session set sort_area_size =804857600;
Session altered.
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL> exec DBMS_LOGSTDBY.INSTANTIATE_TABLE(‘PROD_DATA2′,’PM_SUPPLIER’,’dblinkprimary’);
PL/SQL procedure successfully completed.
经历了漫长的等待终于同步成功，由于此前失败了好几次，总共大概花了7个小时，可见LOB表对于logic standby的影响，一旦出现问题，同步花费的时间将大大的超过普通表，一张记录相同的普通表 使用DBMS_LOGSTDBY.INSTANTIATE_TABLE同步大概只需要10分钟左右，相比较而言差了几百倍之多！
同步完成后 进度很快追了上来：
SPID TYPE STATUS
———— ————— —————————————————————————————————-
29989 COORDINATOR ORA-16116: no work available
29940 READER ORA-16116: no work available
29942 BUILDER ORA-16116: no work available
29944 PREPARER ORA-16116: no work available
29946 PREPARER ORA-16116: no work available
29948 PREPARER ORA-16116: no work available
29952 ANALYZER ORA-16116: no work available
29954 APPLIER ORA-16116: no work available
29956 APPLIER ORA-16116: no work available
29958 APPLIER ORA-16116: no work available
29960 APPLIER ORA-16116: no work available
29962 APPLIER ORA-16116: no work available
29964 APPLIER ORA-16116: no work available
29966 APPLIER ORA-16116: no work available
29968 APPLIER ORA-16116: no work available
29970 APPLIER ORA-16116: no work available
29972 APPLIER ORA-16116: no work available
29974 APPLIER ORA-16116: no work available
29976 APPLIER ORA-16116: no work available
19 rows selected.
APPLIED_SCN APPLIED_TIME RESTART_SCN RESTART_TIME LATEST_SCN LATEST_TIME MINING_SCN MINING_TIME
———– ——————- ———– ——————- ———- ——————- ———- ——————-
3.5694E+10 2012-03-13 06:31:33 3.5686E+10 2012-03-12 19:07:22 3.5694E+10 2012-03-13 06:31:56 3.5694E+10 2012-03-13 06:31:34
终于处理完了这次故障，仰头一看 东方已经微微既白。对于大型的OLTP系统LOB表的存在无疑增加的很多维护的难度，对于要求性极高的系统，LOB字段建议不要存放在数据库系统，无论对于DG还是后期的例如OGG的同步 都是一个极大的隐患。
How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary [ID 422826.1]
Modified 24-FEB-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
References
Applies to:
Oracle Server – Enterprise Edition – Version: 9.0.1.4 to 9.2.0.8
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 9.0.1.4 to 9.2.0.8
Goal
The Purpose of this Note Is To Show How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary.
The Following Example shows the issue.
Create a LOB specifying PCTVERSION = 5 and create another one specifying RETENTION.
When query DBA_LOBS, would not expect to see default PCTVERSION = 10 when RETENTION was specified.
SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);
Table created.
SQL> CREATE TABLE lobretention
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) retention);
Table created.
SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in (‘LOBPCTVERSION’, ‘LOBRETENTION’);
TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
—————– ————————— ———- ———
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800
LOBRETENTION SYS_LOB0000096864C00001$$ 10 10800
> />see that both PCTVERSION and RETENTION columns have values
As You can see, would expect table named LOBRETENTION to clearly show 0 for PCTVERSION since RETENTION was specified. Likewise, LOBPCTVERSION should show only PCTVERSION. So when query DBA_LOBS down the road would have no indication which is truly active.
Also if You Change the Storage Parameters the Dictionary does not change.
> />
SQL> alter table lobretention modify lob(lobLoc) (pctversion 5);
Table altered.
SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name = ‘LOBRETENTION’;
TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
—————– ————————— ———- ———
LOBRETENTION SYS_LOB0000096864C00001$$ 5 10800
see the change in value under PCTVERSION, but both PCTVERSION and RETENTION columns are populated.
Solution
This Issue Identified as Bug 3844939 and its fixed in 10.2 and higher
There is Work Around to Identify Which Truly Active Value PCTVERSION OR RETENTION , by Running The Following Query :
select bitand(flags,32) from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where and OBJECT_NAME=’‘);
OWNER=
if the output is 0 then its PCTVERSION , if its 32 then its RETENTION
Or
create or replace view DBA_LOB_RETENTION
(OWNER, TABLE_NAME, COLUMN_NAME, PCTVERSION, RETENTION)
as
select u.name, o.name,
decode(bitand(c.property, 1), 1, ac.name, c.name),
decode(bitand(l.flags, 32), 0, l.pctversion$, to_number(NULL)),
decode(bitand(l.flags, 32), 32, l.retention, to_number(NULL))
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.lob$ l,
sys.user$ u
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj#
and c.intcol# = l.intcol#
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)
and bitand(c.property,32768) != 32768 /* not unused column */
;
select * from DBA_LOB_RETENTION where TABLE_NAME=’‘ ;
The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.
You Cannot Specify Both PCTVERSION and RETENTION for LOBs Creation.
Also You Cannot Specify RETENTION if the Database is Running in Manual Undo Mode.
PCTVERSION :
Specify the maximum percentage of overall LOB storage space to be used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
RETENTION :
If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION. NOTE: RETENTION will remain constant in DBA_LOBS even after subsequent changes to UNDO_RETENTION. However, LOB undo should take into affect changes to the parameter UNDO_RETENTION over time.
References
BUG:3844939 – DBA_LOBS COLUMNS RETENTION AND PCTVERSION CONTAIN WRONG VALUES
BUG:4231990 – NOT POSSIBLE TO IDENTIFY VERSION RETENTION STRATEGY OF A LOB SEGMENT
NOTE:415845.1 – Lob Segment Size Increases in Dictionary Managed Tablespace: Freelist Blocks Are Not Used
Related
Products
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition
Keywords
DATA DICTIONARY; UNDO_RETENTION; LOB
Back to top
The space is reserved by PCTVERSION or RETENTION. The docs note:
RETENTION – time-based: this specifies how long older versions are to be retained.
PCTVERSION – space-based: this specifies what percentage of the LOB segment is to be used to hold older versions.
Points to Note:
RETENTION is a keyword in the LOB column definition. No value can be specified for RETENTION. The RETENTION value is implicit. If a LOB is created with database compatibility set to 9.2.0.0 or higher, undo_management=TRUE and PCTVERSION is not explicitly specified, time-based retention is used. The LOB RETENTION value is always equal to the value of the UNDO_RETENTION database instance parameter.
You cannot specify both PCTVERSION and RETENTION.
PCTVERSION is applicable only to LOB chunks/pages allocated in LOBSEGMENTS. Other LOB related data in the table column and the LOBINDEX segment use regular undo mechanism.
PCTVERSION=0: the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause“snapshot too old” errors.
PCTVERSION=100: the space allocated by older versions of LOB data can never be reused by other transactions. LOB data storage space is never reclaimed and it always increases.
RETENTION and PCTVERSION can be altered Recommendation Time-based retention using the RETENTION keyword is preferred. A high value for RETENTION or PCTVERSION may be needed to avoid “snapshot too old” errors in environments with high concurrent read/write LOB access.