创建逻辑备用库(Logical Standby Database)

关注

原创最新推荐文章于 2023-02-07 11:40:54 发布 1.5k 阅读 AI 写同款· GEO 优化 ›

创建 Logical Standby Database 创建 Logical Standby Database[@more@]一、确认主库包含逻辑STANDBY支持的表和数据类型1、逻辑STANDBY支持的数据类型：CHARNCHARVARCHAR2 and VARCHARNVAR... 阅读详情

下面的是Logical Standby Database的配置步骤.

(下面用到的一些文件的位置都是临时性的,还得做一定的修改)

将主数据库置为FORCE LOGGING模式.在主数据库创建之后做如下操作:

SQL>ALTER DATABASE FORCE LOGGING;

确认主数据库是归档的并定义好本地归档.如下:

SQL >ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=e:/oracle/oradata/orcl/archive  MANDATORY' SCOPE=BOTH;

确认LOG_PARALLELISM初始化参数值,Logical Standby Database要求此参数的值为1,是个缺省值.如果查出来的参数值为1则不需要做修改,如果不是1值则要用ALTER SYSTEM SET命令做修改.具体操作如下:

SQL>SHOW PARAMETER LOG_PARAMETER------查看参数的当前值

SQL>ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;

修改完以后要关闭数据库并重新启动此参数才有效.

SQL>SHUTDOWN IMMEDIATE

SQL>STARTUP

确认支持的数据类型和表:

在建立Logical Standby Database时,有些数据类型和表是支持的而有些是不支持的.

支持的数据类型有:

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

不支持的数据类型有:

NCLOB

LONG

LONG RAW

BFILE

ROWID

UROWID

user-defined types

object types REFs

varrays

nested tables

不支持的对象类型有:

User-defined tables and sequences in the SYS schema

Tables with unsupported datatypes

Tables using data segment compression

Index-organized tables

确认主数据是否包含不支持的对象可以查询数据字典表DBA_LOGSTDBY_UNSUPPORTED:

SQL>SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

2> ORDER BY OWNER,TABLE_NAME;

用以下的方式查看上面所得出的表的字段名和数据类型:

SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED

2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

Logical Standby Database会过滤的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

确保主数据库中的表行是可以唯一确认的(即有表中有主键)

找出没有主键的表:

SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE

2> 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 REPCAT$_RUNTIME_PARMS Y

SYSTEM MVIEW$_ADV_INDEX N

选择出来看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

2> 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

确认主数据库可以补足日志(Supplemental Logging)

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ---

NO NO (NO值表示此时主数据库是不支持补足日志的)

使主数据库可以补足日志

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;(这条语句将有关由主数据修改的行唯一信息可以通过日志传到备用数据库中并做Log Apply)

在主数据库中做新的日志切换.因为在上一步做完Enable Supplemental Logging之后.当前日志可能包含了有补足的日志和没有补足的日志,而逻辑备用数据库的Log Apply是不支持两者一起的日志的.所以在创建逻辑备用数据库之前要确保要从主数据库传到备用数据库的所有日志都只包含补足日志

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;

确认一下主数据库此时支持补足日志

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ---

YES YES (YES值表示此时主数据库是支持补足日志的)

SUPPLEMENTAL_LOG_DATA_PK:支持补足日志,其信息是primary key

SUPPLEMENTAL_LOG_DATA_UI:支持补足日志,其信息是unique index

在主数据库创建一个交替的表空间.并用DBMS_LOGMNR_D.SET_TABLESPACE将某些表放到这个表空间中.因为在逻辑备用数据库中有一些表是属于SYS和SYSTEM用户的,而这些是放在表空间的.这些表可能在一段时间后记录猛增.为了使SYSTEM表空间不会被用满而导致数据库DOWN机,建立这个交替的表空间来存放这些表.

SQL>CREATE TABLESPACE logmnrts DATAFILE '/disk1/oracle/dbs/logmnrts.dbf'

2> SIZE 25M AUTOEXTEND ON MAXSIZE 2048M

3>SEGMENT SPACE MANAGEMENT AUTO;

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');

(上面一步EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');可以在以后的维护中再做相应的维护)

创建逻辑备用数据库

确认主数据库的数据文件和日志文件

在主数据库中通过查询V$DATAFILE获得数据文件的位置

SQL> select name from v$datafile;

NAME

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

E:/ORACLE/ORA92/ORCL/SYSTEM01.DBF

E:/ORACLE/ORA92/ORCL/UNDOTBS01.DBF

E:/ORACLE/ORA92/ORCL/EXAMPLE01.DBF

E:/ORACLE/ORA92/ORCL/INDX01.DBF

E:/ORACLE/ORA92/ORCL/TOOLS01.DBF

E:/ORACLE/ORA92/ORCL/USERS01.DBF

E:/ORACLE/ORA92/ORCL/LOGMNRTS.DBF

E:/ORACLE/ORA92/ORCL/XDB01.DBF

在主数据库中通过查询V$LOGFILE获得日志文件的位置

SQL> col member format a35

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

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

1 ONLINE E:/ORACLE/ORA92/ORCL/REDO01.LOG

2 ONLINE E:/ORACLE/ORA92/ORCL/REDO02.LOG

3 ONLINE E:/ORACLE/ORA92/ORCL/REDO03.LOG

对主数据库做一次冷备份(全备份,拷贝所有需要的文件:数据文件,控制文件,参数文件)具体做法如下:

关闭数据库:SQL>SHUTDOWN IMMEDIATE

将前面得到的数据文件拷贝到一个临时的位置 F:/ORACLE

启动数据库到MOUNT状态下:SQL>STARTUP MOUNT

为逻辑备用数据库创建一个备份的控制文件:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO

2> 'f:/oracle/bkcontrol.ora';

将主数据库放在restricted session模式下(在打开数据库时以免用户对数据库进行DML和DDL操作): SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

建立Logminer字典(Logical Standby Database在分解REDO LOG为SQL语句时需要Logminer工具,而这个工具在使用之前需要建立Logminer字典):

SQL> ALTER DATABASE OPEN;

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

取消主数据库的restricted session模式使用户可以执行DML和DDL语句:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

查看最近的归档日志(在后面的逻辑备用数据库的创建过程中需要用到)

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;(对当前日志做归档)

SQL> SELECT NAME FROM V$ARCHIVED_LOG

2> WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

3> WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST= 'NO'));

NAME

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

E:/ORACLE/ORADATA/ORCL/ARCHIVE/1_19.DBF

上面的查询其中WHERE子句的DICTIONARY_BEGIN指明是要找出新建的字典, STANDBY_DEST指明是本地归档目录而不是远程归档.

为备用数据库创建准备初始化参数:

SQL> CREATE PFILE='f:/oracle/initstdby.ora' FROM SPFILE;

将前面三步所得到的数据文件,控制文件和参数文件从主数据库的服务器拷贝到备用数据库的服务器.(拷贝到f:/oracle目录)

在备用数据库服务器修改初始化参数(刚拷贝过来的初始化参数文件)修改后的值大概如下:

*.aq_tm_processes=1

*.background_dump_dest='e:/oracle/admin/standby/bdump'

*.compatible='9.2.0.0.0'

*.control_files='e:/oracle/oradata/controlfile/bkcontrol.ora'

*.core_dump_dest='e:/oracle/admin/standbyl/cdump'

*.db_block_size=16384

*.db_cache_size=137363456

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fast_start_mttr_target=300

*.hash_join_enabled=TRUE

*.instance_name='standby'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=27262976

*.log_archive_dest_1='LOCATION=e:/oracle/oradata/archive MANDATORY'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_format='arch%d_%s.dbf'

*.log_archive_start=true

*.open_cursors=300

*.pga_aggregate_target=80000000

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=45088768

*.sort_area_size=524288

*.sql_trace=FALSE

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='e:/oracle/admin/standby/udump'

*.workarea_size_policy='AUTO'

*.lock_name_space=STANDBY

*.standby_file_management='AUTO'

*.standby_archive_dest='e:/oracle/oradata/stdarch'

*.log_parallelism=1

在备用数据库一端创建一个新的实例.如下操作:

c:/>oradim –new –sid standby –startmode auto

在主数据配置listener.ora,tnsnames.ora和sqlnet.ora文件.配置完之后分别如下:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = e:/oracle/ora92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = e:/oracle/ora92)

(SID_NAME = orcl)

)

tnsnames.ora文件为:

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

sqlnet.ora文件为:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (HOSTNAME,TNSNAMES, ONAMES)

在备用数据库配置listener.ora,tnsnames.ora和sqlnet.ora.配置后的文件内容分别如下:

其中配置sqlnet.ora文件中的参数sqlnet.expire_time是enable死连接侦测

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

(ADDRESS_LIST =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl2)

(ORACLE_HOME = e:/oracle/ora92)

(SID_NAME = ORCL)

)

(SID_DESC =

(PROGRAM = extproc)

(SID_NAME = PLSExtProc)

(ORACLE_HOME = e:/oracle/ora92)

)

tnsnames.ora文件为:

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

sqlnet.ora文件为:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (HOSTNAME,TNSNAMES, ONAMES)

SQLNET.EXPIRE_TIME=2

在主数据库和备用数据库用lsnrctl start和lsnrctl stop启动和关闭监听器.然后在主数据库用tnsping standby和在备用数据库用tnsping orcl试看两个库是否是通的.

用初始化参数文件启动数据库到MOUNT状态下.

SQL> STARTUP MOUNT PFILE= f:/oracle/admin/standby/pfile/initstdby.ora;

Rename数据文件.如果拷贝过来的数据文件的位置与原来在主库中的位置不一样.而我们现在使用的是主库备份过来的控制文件,所以要将数据文件rename到新的位置.做如下操作(如果位置是一样的可以不做操作):

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/SYSTEM01.DBF'

2> TO 'f:/oracle/standby/system01.dbf';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/UNDOTBS01.DBF '

2> TO 'f:/oracle/standby/ UNDOTBS01.DBF ';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/EXAMPLE01.DBF'

2> TO 'f:/oracle/standby/ EXAMPLE01.DBF';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/INDX01.DBF'

2> TO 'f:/oracle/standby/ INDX01.DBF';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/TOOLS01.DBF'

2> TO 'f:/oracle/standby/ TOOLS01.DBF ';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/USERS01.DBF'

2> TO 'f:/oracle/standby/ USERS01.DBF';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/XDB01.DBF'

2> TO 'f:/oracle/standby/ XDB01.DBF';

SQL> ALTER DATABASE RENAME FILE 'E:/ORACLE/ORA92/ORCL/LOGMNRTS.DBF'

2> TO 'f:/oracle/standby/ LOGMNRTS.DBF';

Rename日志文件.这一步只是将控制文件中日志文件的位置信息进行更改,此时并没有真正的日志文件生成,也没有从主库拷贝日志文件到备用库.做如下:

SQL> ALTER DATABASE RENAME FILE ' e:/oracle/ora92/orcl/redo01.log '

2> TO ' e:/oracle/radata/redo01.log';

SQL> ALTER DATABASE RENAME FILE ' e:/oracle/ora92/orcl/redo02.log '

2> TO ' e:/oracle/radata /redo02.log';

SQL> ALTER DATABASE RENAME FILE ' e:/oracle/ora92/orcl/redo03.log '

2> TO ' e:/oracle/radata/redo03.log';

在备用数据库打开DATA GUARD(使用户不能在Logical Standby Database做更新操作),做如下:

SQL> ALTER DATABASE GUARD ALL;

SQL> ALTER DATABASE OPEN RESETLOGS;

重设Logical Standby Database的数据库名(这一步的目的性暂时没完全搞清楚)

使用DBNEWID(nid)工具来重设数据库名.要先关闭数据库并把数据库启动为MOUNT状态:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT PFILE= f:/oracle/initstdby.ora;

用DBNEWID工具,操作如下(这个是在命令行下操作):

nid TARGET=SYS/password@orcl DBNAME=standby

然后再关闭数据库:SQL>SHUTDOWN IMMEDIATE

改变初始化参数: db_name=standby

启动数据库为MOUNT状态:SQL>STARTUP MOUNT;

为备用数据库创建SERVER PARAMETER FILE:

SQL>CREATE SPFILE FROM PFILE= f:/oracle/initstdby.ora;

关闭数据库然后用SPFILE重启动数据库:

SQL>SHUTDOWN IMMEDIATE

SQL>STARTUP MOUNT

SQL>ALTER DATABASE OPEN RESETLOGS;

给备用数据库创建一个新的临时文件(属于临时表空间)

先查看备用库中是否有临时文件,如果没有就直接创建,如果有要删除重新创建.(因为从主库的冷备份拷贝过来的临时文件在备用库中是不能用的.所以得重建)相应操作如下:

SQL> SELECT * FROM V$TEMPFILE;-------查看是否有临时文件

no rows selected

SQL> ALTER DATABASE TEMPFILE 'tempfilename' DROP;------删除重建前的临时文件

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE

2> CONTENTS ='TEMPORARY';--------查看临时文件所属的临时表空间名

TABLESPACE_NAME

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

TEMP

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE

2> 'f:/oracle/standby/temp01.dbf'

3> SIZE 40M REUSE;--------给这个临时表空间重建一个临时文件

注册归档日志和启动SQL Apply Services

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE

2> ' e:/oracle/oradata/orcl/arc0004.001';------将最近的归档日志注册到Logical Standby Database.这里的e:/oracle/oradata/stdarch/ ARCH1069826922_21.DBF是在前面得到的.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;-----启动redo logs apply,用执行SQL语句的方式来apply redo logs.就是SQL Apply Services.到于INITIAL是第一次启动SQL Apply Services时要写的.后面的可以直接用如下操作

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;----停止

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;----启动

在主数据库配置远程归档.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=payroll3' SCOPE=BOTH;

--------配置远程归档目录

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

--------配置这个归档目录的状态为可用

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;----对当前日志进行归档,启动远程归档.

检查Logical Standby Database

在建立好Logical Standby Database后,Log Transport Service 和 Log Apply Service服务也都启动.需要检查一下日志是否正由主数据库传到备用数据库并apply.要做到这个目的,用下面的步骤来做.

检查日志是否已经被注册到Logical Standby Database中.连接到备用数据库并查询DBA_LOGSTDBY_LOG视图.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

Session altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END

2> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC

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

22 23-11月-04 14:00:17 23-11月-04 14:26:58 NO NO

1 rows selected.

连接到主数据库并归档部分日志.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

再查询一下DBA_LOGSTDBY_LOG视图.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

Session altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END

2 FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC

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

22 23-11月-04 14:00:17 23-11月-04 14:26:58 NO NO

23 23-11月-04 14:26:58 23-11月-04 14:33:33 NO NO

2 rows selected.

(可以看到多出来一个新的归档日志)

检查日志中的数据是否Apply到逻辑备用数据库中

在逻辑备用数据库中查询DBA_LOGSTDBY_STATS视图来检查日志中的数据是否正被正确地Appled.

SQL> COLUMN NAME FORMAT A30

SQL> COLUMN VALUE FORMAT A30

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

NAME VALUE

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

coordinator state INITIALIZING

在上面的输出中,coordinator进程正在初始化,这表明Log Apply Service正准备Apply SQL.但是日志中的数据还没有开始被Applied到逻辑备用数据库中.

查V$LOGSTDBY看当前的SQL Apply活动状态.连接到逻辑备用数据库中,查询V$LOGSTDBY视图.

SQL> COLUMN STATUS FORMAT A50

SQL> COLUMN TYPE FORMAT A12

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

TYPE HIGH_SCN STATUS

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

COORDINATOR ORA-16115: loading Log Miner dictionary data

READER ORA-16127: stalled waiting for additional transact

ions to be applied

BUILDER ORA-16117: processing

PREPARER ORA-16116: no work available

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

TYPE HIGH_SCN STATUS

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

COORDINATOR ORA-16126: loading table or sequence object number

READER ORA-16116: no work available

BUILDER ORA-16116: no work available

PREPARER ORA-16116: no work available

如果Redo Data开始Apply到备用数据库中时,在V$LOGSTDBY视图中coordinator 进程的状态会显示applying

检查日志中的数据是否Apply完成可以在逻辑备用数据库中从DBA_LOGSTDBY_PROCESS视图中获得.

SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

APPLIED_SCN NEWEST_SCN

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

180702 180702

其中,如果APPLIED_SCN和NEWEST_SCN两列的值是一样的,表示coordinator process进程已经Apply完成. 日志中的数据也Apply完成.

在LOG_ARCHIVE_DEST_n 初始参数后指定DELAY=n (minutes) 的意思是由primary database传到standby database的redo log data将在n minutes之后才开始Apply 到standby database. Default是NODELAY,如果指定了DELAY而没有指定值的话则缺省为30分钟.