Rely constraint and ROWID Tips
来源:互联网 发布:网络爬虫视频网盘教程 编辑:程序博客网 时间:2024/05/16 08:32
Relying on Unique Identification of Rows in Data Guard Tables
In the physical standby database, the recovery is achieved using the rowid of segments. Since a logical standby database is not structurally exact as the primary database, the rowid between the primary and the Oracle instance will not be the same. For a logical standby database, the SQL apply service must use some other mechanism to uniquely identify a row in tables.
Usually, the primary key and non-null unique indexes are the best available data structure that can be used to identify a row in a table. In general, it is good practice for a database design to have a primary key or unique index on tables; however, this may not always be the case in any database.
Although having a primary key or unique index on tables will assist in recovery on a logical standby site, it is not an absolute requirement for logical Oracle instance setup. The SQL apply service can use other scalar columns from the tables to determine the uniqueness of rows.
Normally, when a row is updated in a database, its old and new values, along with the rowid, are written in the redo log. The redo log does not contain any information about the primary key or unique key for that row. Since a logical standby database depends on such an identifier to uniquely identify a row, Oracle introduced a Supplemental Logging mechanism for logical standby databases.
ROWID and supplemental logging
When the supplemental logging is active on a database, the redo logs contain other columns from tables to uniquely identify a row. If the table has a primary key or unique index defined, the only columns involved in the primary key or unique index will be registered in the redo logs along with the actual column(s) that has changed.
If the table does not have any primary keys or unique index defined, Oracle will write all scalar columns from the table to identify the row. This may significantly increase the size of redo logs and will impact the log apply services on the logical standby site.
One way to resolve this issue is to define a disabled primary key RELY constraint on those tables that do not have a primary key/unique index defined. Creating a disabled primary key will not add any extra overhead in maintaining the primary key on the primary database, yet it will minimize the information required in redo logs for logical Oracle instance recovery.
WARNING – Before creating the Primary Key RELY constraint, it is vital that the DBA understand the application data very well. Alternatively, the application developers can help with constraints definitions.
Putting a database in Supplemental Logging mode is a requirement for a logical standby database. Even if all the tables in the database have a primary key defined, supplemental logging will have to be activated so that the primary key columns will be written to the redo logs along with the updated columns.
- Rely constraint and ROWID Tips
- rely constraint
- physical rowid and logical rowid
- ubuntu lock and rely on
- ROWNUM and ROWID
- Oracle rowid and urowid
- rowid and rownum
- [tips]关于rowid的一些内容
- [tips]关于rowid的一些内容
- [tips]关于rowid的一些内容
- oracle rowid and postgresql ctid
- Rowid
- ROWID
- rowid
- ROWID
- ROWID
- ROWID
- ROWID
- 【足迹C++primer】31、初识泛型算法
- calling lua from c in linux
- 非常精简的Linux线程池实现(一)——使用互斥锁和条件变量
- 设计模式 之 抽象工厂
- spring bean 初始化
- Rely constraint and ROWID Tips
- 用VBA,把OUTLOOK 2010里指定天数之前的邮件,转移到另外的文件夹里
- 0001/1000
- 使用者身份切换 su
- linux syslog日志的使用
- 安装linux系统的两大神器yum and apt-get
- 码农提高工作效率
- linux下修改环境变量问题
- 信息学习25