创建 Logical Standby Database

创建 Logical Standby Database[@more@]

一、确认主库包含逻辑STANDBY支持的表和数据类型

1、 逻辑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

2、逻辑STANDBY不支持的数据类型：

NCLOB
LONG RAW
BFILE
ROWID
UROWID

3、逻辑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来查询：
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

4、逻辑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

确认主数据库是否包含不支持的对象能够查询数据字典表dba_logstdby_unsupported:
SQL> select distinct owner,table_name from dba_logstdby_unsupported
order by owner,table_name;

用以下的方式查看上面所得出的表的字段名和数据类型:
SQL> select column_name,data_type from dba_logstdby_unsupported
where owner=oe and table_name = customers;

二、 确认主库中所有表都具备唯一约束

逻辑STANDBY需要确保主库中每一行每一列的更新都能被日志应用到逻辑备用库里面，因此最好主库中的表都具备唯一约束。可以通过视图DBA_LOGSTDBY_NOT_UNIQUE来查找主库中不具备唯一约束的表。

找出没有主键的表:
SQL> select owner, table_name, bad_column from dba_logstdby_not_unique
where table_name not in (select table_name from dba_logstdby_unsupported);

选择出来看bad_column字段值.假如此值为n表示这个表关于没有主键字段列有足够的列信息传到备用数据库中.假如此值为y表示信息不够必须对这个表加一个disable rely constraint以使关于这个表的log能够apply到逻辑备用库中.
选择出来只有system用户下的表repcat$_runtime_parms需要做修改,然后查看这个表的结构.然后做修改如下:
SQL> desc system.repcat$_runtime_parms
SQL> alter table system.repcat$_runtime_parms add primary key (runtime_parm_id,parameter_name) rely disable;

修改完以后再做上面的查询以确认没有表需要再做修改:
SQL> select owner, table_name, bad_column from dba_logstdby_not_unique
where table_name not in (select table_name from dba_logstdby_unsupported);
owner table_name b
------------------------------ ------------------------------ -
outln ol$hints n
outln ol$nodes n
system mview$_adv_basetable n
system mview$_adv_sqldepend n
system mview$_adv_filterinstance n
system def$_origin n
system repcat$_snapgroup n
system repcat$_ddl n
system repcat$_resolution_statistics n
system mview$_adv_index n

如果表不具备唯一约束的话，建议创建一个RELY的唯一约束：
SQL> 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

三、 主库其他检查事项

1、 确保主库是归档模式

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oraclelog
Oldest online log sequence 12800
Next log sequence to archive 12804
Current log sequence 12804

2、 log_parallelism参数必须设置为1：
SQL> show parameter log_parallelism
NAME TYPE VALUE
---------------- ----------- -------
log_parallelism integer 1

3、 创建一个logminer所用的表空间：

创建逻辑STANDBY对象的默认表空间是SYSTEM表空间，为了减少对系统表空间的影响，我们创建一个专门的表空间用于逻辑STANDBY：
SQL> CREATE TABLESPACE logminer DATAFILE ‘d:/oracle/oradata/xue9/logminer01.dbf’ size 50M;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘logminer’);

四、 逻辑STANDBY配置主库端操作：

1、用rman全库迁移。

2、MOUNT主库，生成备用库控制文件：
SQL> startup mount
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.

Sql> ALTER DATABASE BACKUP CONTROLFILE TO 'd:/oracle/oradata/standby/control01.ctl';
Database altered.

4、 打开主库，执行DBMS_LOGSTDBY.BUILD生成逻辑STANDBY所需LOGMINER字典信息：建立logminer字典(logical standby database在分解redo log为sql语句时需要logminer工具,而这个工具在使用之前需要建立logminer字典):

SQL> alter database open;
Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.

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

SQL> alter system switch logfile;
System altered.

SQL> select name from v$archived_log where dictionary_begin='yes' and standby_dest='no';

6、 生成STANDBY初始化参数文件：

SQL> create pfile=’d:/init.ora’ from spfile

五、 逻辑STANDBY配置：

1、 更改初始化参数文件：

注意更改control_files、background_dump_dest等指定的相应目录，这些目录必须预先创建好，注意添加以下几个参数：
standby_archive_dest：指定备用库的归档路径
parallel_max_servers：设置为9，必须设置，因为STANDBY日志恢复里程会启动相应的并行进程：
LOGMINER: Fetch Slave process P003 started with pid=17 OS id=3632
LOGMINER: Apply Slave process P004 started with pid=18 OS id=1888
LOGMINER: Apply Slave process P005 started with pid=19 OS id=2292
LOGMINER: Apply Slave process P006 started with pid=20 OS id=3336
LOGMINER: Apply Slave process P007 started with pid=21 OS id=3824
LOGMINER: Apply Slave process P008 started with pid=22 OS id=1788

instance_name：如果是同机配置STANDBY的话，必须指定和主库不同的实例名

更改后的参数文件如下：
*.aq_tm_processes=1
*.background_dump_dest='$ORACLE_BASE'
*.compatible='10.2.0.3.0'
*.control_files='$ORACLE_BASE/oradata/STANDBY/control01.ctl','$ORACLE_BASE/oradata/STANDBY/control02.ctl','$ORACLE_BASE/oradata/STANDBY/control03.ctl'
*.core_dump_dest='$ORACLE_BASE /admin/STANDBY/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='gpicdb0'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='gpicdb0'
*.java_pool_size=8388608
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='location=/orarach'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=12582912
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=20971520
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=5400
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='$ORACLE_BASE /admin/STANDBY/udump'
*.STANDBY_ARCHIVE_DEST='$ORACLE_BASE /oradata/STANDBY/archive'
*.LOG_ARCHIVE_FORMAT=ARC%S.%T
*.lock_name_space=STANDBY
*.standby_file_management='AUTO'
*.parallel_max_servers=9
*.log_parallelism=1

3、将备用库的控制文件按参数文件中的设置COPY 3份并更名，MOUNT数据库，更改数据文件和日志文件和路径

4、更改数据库名字并启动数据库（这步可做可不做）：

5、STANDBY创建临时文件，如果v$tempfile有记录主库的临时文件信息，必须先offline drop掉后再创建。这步必须要做，要不STANDBY将无法配置成功。

idle> select * from v$tempfile;
no rows selected
Elapsed: 00:00:00.01
idle> alter tablespace temp add tempfile 'd:/oracle/oradata/standby/temp01.dbf' size 50M reuse;
Tablespace altered

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

idle> ALTER DATABASE REGISTER LOGICAL LOGFILE 'D:/oracle/oradata/STANDBY/archive
/ARC00094.001';
Database altered

７、启动STANDBY日志应用模式：

idle> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
Database altered

六、 配置listener.ora和tnsnames.ora：

listener.ora添加STANDBY的配置信息：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = //oracle/app/oracle10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = gpicdb0)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = gpicdb0)
)
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = standby)
)
(SID_DESC =
(GLOBAL_DBNAME = XUE9)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = gpicdb0)
)
)

Tnsnames.ora添加STANDBY的连接字：
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.250.128.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =gpicdb0)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.250.128.24)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)

配置完成后重启监听：lsnrctl reload

七、 主库配置STANDBY的归档信息：

SQL> alter system set log_archive_dest_2='service=standby lgwr';
SQL> alter system set log_archive_dest_state_2=enable;

至此，逻辑STANDBY配置完成。

八、 测试日志应用：

主库端操作：
SQL> create user roby identified by roby;
SQL> grant dba to roby;
SQL> alter user roby default tablespace users;
SQL> alter system switch logfile
SQL> conn roby/roby
Connected.

SQL> create table t(a int);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select * from t;
A
----------
1
1
1
1
Elapsed: 00:00:00.00

备用库端检查：

Alert log记录的信息：

代码:
LOGSTDBY stmt: create user roby identified by VALUES '3937FA8E626D1ADE'
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: grant dba to roby
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: alter user roby default tablespace users
Thu Jun 21 13:58:15 2007
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: create table t(a int)
Thu Jun 21 14:05:16 2007
LOGSTDBY event: ORA-16204: DDL successfully applied

SQLPLUS登录进去，可以看到创建的表已经成功同步了：
idle> conn roby/roby
Connected.

idle> select * from t;
A
----------
1
1
1
1

启用STANDBY保护模式并打开数据库：
idle> ALTER DATABASE GUARD ALL;
Database altered.

idle> ALTER DATABASE OPEN RESETLOGS;
Database altered．

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

转载于:http://blog.itpub.net/21102096/viewspace-1018974/