oracle 9i standby,Oracle 9i R2 配置 Logical Standby

最近因业务量越来大，正在考虑将数据库将OLTP和OLAP分离的方案。分案的基本如下

主库 负责OLTP 业务，对外以供一些即时的查询。例如：当天充值明细查询

备库 和主库进行同步，对外提供一些历史明细数据查询。例如：历史充值明细查询。从主库的归档日志中获取SQL语句，然后执行，从而实现和主库的同步。很多书例如pinner 的Oracle高可用那本都建议用Oracle10g 的 Logical Standby实现同步。但是对于我们来说升级到Oracle10g 代价比较大，系统割接比较麻烦。综合考虑，我们还是在Oracle9i 的基础上实现 Logical Standby

分析库 从备库中获取数据，生成汇总数据，进行数据分析，对外提供汇总报表查询功能。例如：充值汇总报表，酬金结算报表等等

现在将在Linux Red Hat 4 Update 5 上的案例和大家进行分享

1.主库设置成归档模式

并检查

2. 确认主库中所有表都具备唯一约束

逻辑STANDBY需要确保主库中每一行每一列的更新都能被日志应用到逻辑备用库里

面，因此最好主库中的表都具备唯一约束。可以通过视图DBA_LOGSTDBY_NOT_UNIQUE来查找主库中不具备唯一约束的表。如果表不具备唯一约束的话，建议创建一个RELY的唯一约束：ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

如果不具备唯一约束的话，那么ORACLE的supplemental logging特性可以自动产生用于标识主库中每一行更改的信息，使得逻辑STANDBY能够同步这些变更。

查看是否启用了supplemental logging特性：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ---

NO NO

如果未启用的话，那么开启supplemental logging特性：

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

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ---

YES YES

4. 确保主库是归档模式： archive log list

5. 主库log_parallelism参数必须设置为1：

show parameter log_parallelism

6. 主库 创建一个logminer所用的表空间,用于日志分析

CREATE TABLESPACE logminer DATAFILE '/u02/oradata/devdb1/logminer01.dbf' size 50M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

用 sys 用户 以SYSDBA登录

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logminer');

7. 执行DBMS_LOGSTDBY.BUILD生成逻辑STANDBY所需LOGMINER字典信息：

EXECUTE DBMS_LOGSTDBY.BUILD;

8. 归档当前日志，并记录包含创建逻辑STANDBY字典信息的最新归档日志，将最新归档COPY至STANDBY的相应归档目录：

alter system switch logfile;

查询到这个归档日志文件，并拷贝到备库的 standby_archive_dest 参数指定的目录中

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES' and STANDBY_DEST='NO';

9.主库 shutdown immediate

10.拷贝主库的表空间文件、Redo、控制文件拷贝到备库的对应目录(采用冷备份方式)，如果是做物理Standby就不用拷贝控制文件了

11.拷贝主库的参数文件到备库(这种情况在主库和备库硬件环境相似的情况下成立，如果差异比较大应重新调整参数文件)

12.备库生成密码文件

/u01/app/oracle/ora920/bin/orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwdevdb1 password=install

13.修改备用数据库参数文件，添加下列参数

utl_file_dir='/u01/app/oracle'

standby_archive_dest='/u02/oradata/devdb1/stdarch'

fal_server='PRIMARY'

fal_client='STANDBY'

standby_file_management='AUTO'

#设置为9，必须设置，因为STANDBY日志恢复里程会启动相应的并行进程：

parallel_max_servers=9

14.修改主库及备用数据库的TNSNAMES.ora添加下列内容

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.131)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = devdb1.freelynet.com)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.132)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = devdb1.freelynet.com)

)

)

主库备库启动 lsnrctl start

主库测试 TNS

tnsping primary

tnsping standby

备库测试 TNS

tnsping primary

tnsping standby

15. 启动备用数据库

startup mount pfile='/u01/app/oracle/admin/devdb1/scripts/initstandby.ora';

16. 启用STANDBY保护模式并打开数据库：

ALTER DATABASE GUARD ALL;

17、STANDBY创建一个新的默认临时表空间，然后Drop掉原来的那个，在按原来的样子重新建立一个，再把新建的这个表控制去除

目的只有一个：拷贝过来的临时表空间中有主库的临时文件信息，必须清除否则要不STANDBY将无法配置成功。

18、手工注册COPY过来的归档日志文件：

ALTER DATABASE REGISTER LOGICAL LOGFILE '/u02/oradata/devdb1/stdarch/1_48.dbf';

19. 启动STANDBY日志应用模式：

ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

监控 Alter 文件如果成功的话有下列输出

....

Wed May 7 17:10:22 2008

ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL

ALTER DATABASE START LOGICAL STANDBY APPLY

with optional part

INITIAL

Attempt to start background Logical Standby process

LSP0 started with pid=12, OS id=3957

Wed May 7 17:10:22 2008

Completed: ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL

Wed May 7 17:10:22 2008

LOGSTDBY event: ORA-16111: log mining and apply setting up

LOGMINER: reader process P000 started with pid=13 OS id=3959

LOGMINER: builder process P001 started with pid=14 OS id=3961

LOGMINER: preparer process P002 started with pid=15 OS id=3963

LOGMINER: Apply Slave process P004 started with pid=17 OS id=3967

LOGMINER: Apply Slave process P005 started with pid=18 OS id=3969

LOGMINER: Fetch Slave process P003 started with pid=16 OS id=3965

LOGMINER: Apply Slave process P007 started with pid=20 OS id=3973

LOGMINER: Apply Slave process P008 started with pid=21 OS id=3975

LOGMINER: Apply Slave process P006 started with pid=19 OS id=3971

然后 ps -ef |grep oracle 多了下列这些进程

oracle 3957 1 2 17:10 ? 00:00:28 ora_lsp0_devdb1

oracle 3959 1 0 17:10 ? 00:00:07 ora_p000_devdb1

oracle 3961 1 1 17:10 ? 00:00:19 ora_p001_devdb1

oracle 3963 1 0 17:10 ? 00:00:04 ora_p002_devdb1

oracle 3965 1 1 17:10 ? 00:00:23 ora_p003_devdb1

oracle 3967 1 0 17:10 ? 00:00:05 ora_p004_devdb1

oracle 3969 1 0 17:10 ? 00:00:00 ora_p005_devdb1

oracle 3971 1 0 17:10 ? 00:00:00 ora_p006_devdb1

oracle 3973 1 0 17:10 ? 00:00:00 ora_p007_devdb1

oracle 3975 1 0 17:10 ? 00:00:00 ora_p008_devdb1

20. 启动主库,设置归档路径

startup

#采用最大性能模式，在日志切换时将归档日志文件发送到备用库。

alter system set log_archive_dest_2='service=standby mandatory reopen=60';

21. 主库执行一下DDL操作和DML操作，但是有些DDL操作、DML操作和数据类型不能被Logical Standby 支持

逻辑STANDBY支持的数据类型：

CHAR

NCHAR

VARCHAR2 and VARCHAR

NVARCHAR2

NUMBER

DATE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

RAW

CLOB

BLOB

逻辑STANDBY不支持的数据类型：NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID

逻辑STANDBY不支持的表和序列：

Tables and sequences in the SYS schema

Tables with unsupported datatypes

Tables used to support functional indexes

Tables used to support materialized views

Global temporary tables

逻辑STANDBY不支持的表和列可以通过视图dba_logstdby_unsupported来查询：

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

逻辑STANDBY不支持的SQL语句操作：

ALTER DATABASE

ALTER SESSION

ALTER SNAPSHOT

ALTER SNAPSHOT LOG

ALTER SYSTEM SWITCH LOG

CREATE CONTROL FILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE PFILE FROM SPFILE

CREATE SCHEMA AUTHORIZATION

CREATE SNAPSHOT

CREATE SNAPSHOT LOG

CREATE SPFILE FROM PFILE

CREATE TABLE AS SELECT FROM A CLUSTER TABLE

DROP DATABASE LINK

DROP SNAPSHOT

DROP SNAPSHOT LOG

EXPLAIN

LOCK TABLE

RENAME

SET CONSTRAINTS

SET ROLE

SET TRANSACTION

22。测试如下：

在主库的 scott 用户下，

drop table emp;

create table t as select * from all_objects;

执行日志切换

alter system switch logfile;

主库主库 alter 日志输出

Thread 1 advanced to log sequence 53

Current log# 3 seq# 53 mem# 0: /u02/oradata/devdb1/redo03.log

Wed May 7 23:04:15 2008

ARC1: Evaluating archive log 2 thread 1 sequence 52

ARC1: Beginning to archive log 2 thread 1 sequence 52

Creating archive destination LOG_ARCHIVE_DEST_2: 'standby'

Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oradata/devdb1/archive/1_52.dbf'

ARC1: Completed archiving log 2 thread 1 sequence 52

备库 alter 日志输出

Wed May 7 17:41:38 2008

LOGSTDBY event: ORA-16204: DDL successfully applied

LOGSTDBY stmt: drop table EMP

LOGSTDBY event: ORA-16204: DDL successfully applied

LOGSTDBY stmt: create table t as select * from all_objects

登录 备库发现，备库里面 emp表已经消失，然后多了一个T表

备用数据库启动过程

startup open pfile='/u01/app/oracle/admin/devdb1/scripts/initstandby.ora';

启动 Logical Standby Apply

alter database start logical standby apply

停止 Logical Standby Apply

alter database stop logical standby apply;[@more@]