逻辑standby配置

接上:

我的逻辑standby配置:

http://wayw009.spaces.live.com/blog/cns!73fba3701ddca1f5!322.entry

otn上logical standby: http://blog.oracle.com.cn/html/11/t-26311.html

1. 验证是否存在表没有主键

select owner,table_name from dba_logstdby_not_unique

where (owner,table_name) not in

(select distinct owner,table_name from dba_logstdby_unsupported)

and bad_column='Y';

2. 验证

SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,

SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG

FROM V$DATABASE;

PK_ UI_

--- ---

NO NO

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

再验证

SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,

SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG

FROM V$DATABASE;

PK_LOG UI_LOG

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

YES YES

3. 为primary database添加部分参数

alter system set log_archive_dest_3='location=/opt/oracle/standby_redo valid_for=(standby_logfiles,standby_role) db_unique_name=primary' scope=both;

alter system set log_archive_dest_state_3=enable scope=both;

alter system set UNDO_RETENTION=3600 scope=both;

4. alter database recover managed standby database cancel;

(这个应该在备份数据库里面执行)

(记住:在build之前一定要先cancel 物理standby,否则执行"alter database recover to logical standby cncimps"时会总是没有返回)

5. 在primary database上建立logminer字典

exec dbms_logstdby.build;

6. alter database recover to logical standby cncimps;

(这个应该在备份数据库里面执行)

我这里原来db_name就是cncimps,所以这样后db_name没变,也就不用再重新修改pasword文件了(如果和原来的不一致,那么就需要重建password文件)

7. standby也这么修改:

alter system set log_archive_dest_3='location=/opt/oracle/standby_redo valid_for=(standby_logfiles,standby_role) db_unique_name=standby' scope=both;

alter system set log_archive_dest_state_3=enable scope=both;

alter system set UNDO_RETENTION=3600 scope=both;

8. shutdown immediate (shutdown standby)

9. startup mount

alter database open resetlogs;

数据库已更改

(11) 开始应用redo

如果之前没有使用standby redo log file,这里会出错

SQL> alter database start logical standby apply immediate;
alter database start logical standby apply immediate
*第1行出现错误

ORA-16239: 由于没有备用重做日志, IMMEDIATE选项不可用

10. 添加redo log

alter database add standby logfile group 4 '/opt/oracle/oradata/cncimps/standbyredo04.log' size 50m;

alter database add standby logfile group 5 '/opt/oracle/oradata/cncimps/standbyredo05.log' size 50m;

alter database add standby logfile group 6 '/opt/oracle/oradata/cncimps/standbyredo06.log' size 50m;

alter database add standby logfile group 7 '/opt/oracle/oradata/cncimps/standbyredo07.log' size 50m;

11. 添加temp数据文件

alter session disable guard;

alter tablespace temp add tempfile '/opt/oracle/oradata/cncimps/temp03.dbf' size 500M reuse;

alter session enable guard;

12. 测试

可以用这个来归档当前日志

alter system archive log current;

在测试的时候,好像要花费很长时间来应用日志

如果应用日志,备库里的alert文件会有下面的相关信息

Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable

Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable

Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable

LSP2 started with pid=29, OS id=18066

Wed Jul 25 09:57:26 2007

LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_386_607103080.dbf

Wed Jul 25 09:57:26 2007

LOGMINER: Turning ON Log Auto Delete

LOGSTDBY Apply process P005 started with pid=32 OS id=18072

LOGSTDBY Apply process P004 started with pid=31 OS id=18070

LOGSTDBY Apply process P006 started with pid=33 OS id=18074

LOGSTDBY Apply process P007 started with pid=34 OS id=18076

LOGSTDBY Apply process P008 started with pid=35 OS id=18078

LOGSTDBY Analyzer process P003 started with pid=30 OS id=18068

Wed Jul 25 09:57:28 2007

LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_386_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_387_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_387_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_388_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_388_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: Begin mining logfile: /opt/oracle/archivelog/1_389_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: End mining logfile: /opt/oracle/archivelog/1_389_607103080.dbf

Wed Jul 25 09:57:28 2007

LOGMINER: Begin mining logfile: /opt/oracle/oradata/cncimps/standbyredo05.log

Wed Jul 25 09:57:28 2007

Thread 1 advanced to log sequence 3

Current log# 2 seq# 3 mem# 0: /opt/oracle/oradata/cncimps/redo02.log

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-927886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7916042/viewspace-927886/