Oracle 11.2.0_Phy-DataGuard_CentOS7.2

来源：互联网发布：淘宝客服接待用语编辑：程序博客网时间：2024/06/16 17:29

介绍

背景：近期有一套系统需要做物理DG，新购置一台数据库服务。具体如下：

IP地址主机名称Oracle服务名用途172.16.1.173ora11gprixcky系统主库（在线运行中）172.16.1.172slave2.clouderastdxcky系统备库（仅需装Oracle软件即可）

1、配置流程梳理

主库强记日志
主库创建standby日志组
主、备配置修改initSID.ora
主、备配置静态监听和客户端TNS
从主库拷贝口令文件到备库
主库启动到mount，备库启动到nomount
主库开启归档，依据参数文件中配置，创建相关路径并授权
利用RMAN将主库duplicate到备库
备、主开库

2、搭建步骤

Oracle 11.2.0.3在CentOS7.2上安装过程中需注意一点

编辑…/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk
修改：
$(MK_EMAGENT_NMECTL)为如下（追加" -lnnz11"）

主库强制记日志

SQL>alter database force logging;

SQL> select force_logging from v$database;
FOR
------
YES

主库创建standby日志组

查看日志文件大小和路径

SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/ora11g/11g/oradata/xcky/redo03.log
/home/ora11g/11g/oradata/xcky/redo02.log
/home/ora11g/11g/oradata/xcky/redo01.log

创建与online rego日志大小相同的stangby日志，组数为四组，比online redo多一组即可

SQL> alter database add standby logfile group 4 '/home/ora11g/11g/oradata/xcky/stdredo01.log' size 52428800;
Database altered.

SQL> alter database add standby logfile group 5 '/home/ora11g/11g/oradata/xcky/stdredo02.log' size 52428800;
Database altered.
SQL> alter database add standby logfile group 6 '/home/ora11g/11g/oradata/xcky/stdredo03.log' size 52428800;
Database altered.

SQL> alter database add standby logfile group 7 '/home/ora11g/11g/oradata/xcky/stdredo04.log' size 52428800;

Database altered.

主、备配置修改initSID.ora

主库：

一般库是由spfile参数文件启动，确认下，spfile有路径值说明是由spfile启动：

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ----------------
spfile string/home/ora11g/11g/product/11.2.
0/dbhome_1/dbs/spfilexcky.ora

SQL>create pfile from spfile; ---由当前spfile生成可编辑的pfile参数文件。

主库编辑initxcky.ora文件，追加如下内容：

db_unique_name='prixcky'
log_archive_config='dg_config=(prixcky,stdxcky)'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=prixcky'
log_archive_dest_2='service=stdxcky LGWR async valid_for=(online_logfiles,primary_role) db_unique_name=stdxcky'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_max_processes=30
fal_client='prixcky'
fal_server='stdxcky'
standby_file_management='AUTO'

log_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/')
db_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/','/opt/oradata/','/opt/oradata/')

将原参数文件拷贝到备库，做好对应配置的修改：

db_unique_name='stdxcky'
log_archive_config='dg_config=(prixcky,stdxcky)'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=stdxcky'
log_archive_dest_2='service=prixcky LGWR async valid_for=(online_logfiles,primary_role) db_unique_name=prixcky'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_max_processes=30
fal_client='stdxcky'
fal_server='prixcky'

standby_file_management='AUTO'

SQL> select name from v$datafile;
NAME
--------------------------------------------------
/home/ora11g/11g/oradata/xcky/system01.dbf
/home/ora11g/11g/oradata/xcky/sysaux01.dbf
/home/ora11g/11g/oradata/xcky/undotbs01.dbf
/home/ora11g/11g/oradata/xcky/users01.dbf
/opt/oradata/PHOTO_INFO.dbf
/opt/oradata/XCKY_INFO.dbf
/opt/oradata/BUNCH_INFO.dbf
/opt/oradata/TRANSFER_INFO.dbf
/opt/oradata/OTHER_INFO.dbf
/opt/oradata/BULLETIN_INFO.dbf
/opt/oradata/SYSTEM_INFO.dbf
/opt/oradata/INDEX_INFO.dbf
/opt/oradata/VIEW_INFO.dbf
/opt/oradata/VIEW_LOG.dbf
/opt/oradata/XCKY.dbf

数据文件路径有/opt/oradata 和/home/ora11g/11g/oradata/xcky，如果备库与此相同，首先在备库创建这些目录并授权，同时也需要指定db_file_name_convert参数。

SQL> select member from v$logfile;
MEMBER
--------------------------------------------
/home/ora11g/11g/oradata/xcky/redo03.log
/home/ora11g/11g/oradata/xcky/redo02.log
/home/ora11g/11g/oradata/xcky/redo01.log

SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------------------------------
control_files string/home/ora11g/11g/oradata/xcky/control01.ctl, /home/ora11g/11g/oradata/xcky/control02.ctl

以上是日志文件、数据文件、控制文件的目录路径，需要根据各自类型的文件路径，指定主、备库间文件路径转换参数；

log_file_name_convert用于指定日志文件路径目录转换；

db_file_name_convert用于指定数据文件、控制文件目录转换；以主前备后的方式对应。

如果对应错误，duplicate时会导致如下报错：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/30/2017 20:05:10
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/30/2017 20:05:08
ORA-17628: Oracle error 19505 returned by remote Oracle server

解决方案就是将不对应的路径纠正对应关系，重新duplicate。

使用scp将口令文件拷贝到固定路径下（安装备库软件时候，本次安装路径环境变量与主库是相同的）

[oracle@ora11g dbs]$ ls
！ hc_xcky.dat init.ora lkPRIXCKY lkXCKY orapwxcky snapcf_xcky.f spfilexcky.ora
[oracle@ora11g dbs]$ scp orapwxcky slave2.cloudera:$ORACLE_HOME/dbs
oracle@slave2.cloudera's password:
orapwxcky 100% 1536 1.5KB/s 00:00

主、备路静态监听和客户端TNS配置

listener.ora配置：

主库：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prixcky)
(ORACLE_HOME = /home/ora11g/11g/product/11.2.0/dbhome_1)
(SID_NAME = xcky)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

备库：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdxcky)
(ORACLE_HOME = /home/ora11g/11g/product/11.2.0/dbhome_1)
(SID_NAME = xcky)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave2.cloudera)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

tnsnames.ora配置（主、备）：

prixcky =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prixcky)
)
)

stdxcky =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave2.cloudera)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdxcky)
)
)

主库启动到mount，备库无控制文件，只能启动到nomount

主库开启归档，依据参数文件中配置，备库创建相关路径并授权

*.audit_file_dest='/home/ora11g/11g/admin/xcky/adump'
*.control_files='/home/ora11g/11g/oradata/xcky/control01.ctl','/home/ora11g/11g/oradata/xcky/control02.ctl'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=stdxcky'

这些路径目录在备库是不存在的，需要手工创建。

测试，确保使用服务名能够连通主、备库

[oracle@ora11g dbs]$ sqlplus sys/hisign@prixcky as sysdba

[oracle@ora11g ~]$ sqlplus sys/hisign@stdxcky as sysdba

利用RMAN将主库duplicate到备库

$rman target sys/hisign@primary auxiliary sys/hisign@stdxcky

如果没有配置静态监听，那么这个过程连接auxiliary库时候会提示报错信息：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

因为备库无法mount，pmon进程无法启动，不会注册动态监听。

RMAN>duplicate target database for standby from active database nofilenamecheck;

执行如下：

[oracle@slave2 ~]$ rman target sys/hisign@prixcky auxiliary sys/hisign@stdxcky
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Aug 30 20:30:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: XCKY (DBID=283416601, not open)
connected to auxiliary database: XCKY (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 30-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=156 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/orapwxcky' auxiliary format
'/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/orapwxcky' ;
}
executing Memory Script

Starting backup at 30-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=172 device type=DISK
Finished backup at 30-AUG-17

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/ora11g/11g/oradata/xcky/control01.ctl';
restore clone controlfile to '/home/ora11g/11g/oradata/xcky/control02.ctl' from
'/home/ora11g/11g/oradata/xcky/control01.ctl';
}
executing Memory Script

Starting backup at 30-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/snapcf_xcky.f tag=TAG20170830T203028 RECID=2 STAMP=953411428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-AUG-17

Starting restore at 30-AUG-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 30-AUG-17

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file /home/ora11g/11g/oradata/xcky/system01.dbf for datafile 1 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/sysaux01.dbf for datafile 2 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/undotbs01.dbf for datafile 3 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/users01.dbf for datafile 4 with checkpoint SCN of 2403114

contents of Memory Script:
{
set newname for tempfile 1 to
"/home/ora11g/11g/oradata/xcky/temp01.dbf";
set newname for tempfile 2 to
"/opt/oradata/XCKYTMP.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/ora11g/11g/oradata/xcky/system01.dbf";
set newname for datafile 2 to
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf";
set newname for datafile 3 to
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf";
set newname for datafile 4 to
"/home/ora11g/11g/oradata/xcky/users01.dbf";
set newname for datafile 5 to
"/opt/oradata/PHOTO_INFO.dbf";
set newname for datafile 6 to
"/opt/oradata/XCKY_INFO.dbf";
set newname for datafile 7 to
"/opt/oradata/BUNCH_INFO.dbf";
set newname for datafile 8 to
"/opt/oradata/TRANSFER_INFO.dbf";
set newname for datafile 9 to
"/opt/oradata/OTHER_INFO.dbf";
set newname for datafile 10 to
"/opt/oradata/BULLETIN_INFO.dbf";
set newname for datafile 11 to
"/opt/oradata/SYSTEM_INFO.dbf";
set newname for datafile 12 to
"/opt/oradata/INDEX_INFO.dbf";
set newname for datafile 13 to
"/opt/oradata/VIEW_INFO.dbf";
set newname for datafile 14 to
"/opt/oradata/VIEW_LOG.dbf";
set newname for datafile 15 to
"/opt/oradata/XCKY.dbf";
backup as copy reuse
datafile 5 auxiliary format
"/opt/oradata/PHOTO_INFO.dbf" datafile
6 auxiliary format
"/opt/oradata/XCKY_INFO.dbf" datafile
7 auxiliary format
"/opt/oradata/BUNCH_INFO.dbf" datafile
8 auxiliary format
"/opt/oradata/TRANSFER_INFO.dbf" datafile
9 auxiliary format
"/opt/oradata/OTHER_INFO.dbf" datafile
10 auxiliary format
"/opt/oradata/BULLETIN_INFO.dbf" datafile
11 auxiliary format
"/opt/oradata/SYSTEM_INFO.dbf" datafile
12 auxiliary format
"/opt/oradata/INDEX_INFO.dbf" datafile
13 auxiliary format
"/opt/oradata/VIEW_INFO.dbf" datafile
14 auxiliary format
"/opt/oradata/VIEW_LOG.dbf" datafile
15 auxiliary format
"/opt/oradata/XCKY.dbf" ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/ora11g/11g/oradata/xcky/temp01.dbf in control file
renamed tempfile 2 to /opt/oradata/XCKYTMP.dbf in control file

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 30-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/opt/oradata/BUNCH_INFO.dbf
output file name=/opt/oradata/BUNCH_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/opt/oradata/OTHER_INFO.dbf
output file name=/opt/oradata/OTHER_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/opt/oradata/BULLETIN_INFO.dbf
output file name=/opt/oradata/BULLETIN_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/opt/oradata/SYSTEM_INFO.dbf
output file name=/opt/oradata/SYSTEM_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oradata/PHOTO_INFO.dbf
output file name=/opt/oradata/PHOTO_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/opt/oradata/INDEX_INFO.dbf
output file name=/opt/oradata/INDEX_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/opt/oradata/VIEW_INFO.dbf
output file name=/opt/oradata/VIEW_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oradata/XCKY_INFO.dbf
output file name=/opt/oradata/XCKY_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/opt/oradata/TRANSFER_INFO.dbf
output file name=/opt/oradata/TRANSFER_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/opt/oradata/VIEW_LOG.dbf
output file name=/opt/oradata/VIEW_LOG.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/opt/oradata/XCKY.dbf
output file name=/opt/oradata/XCKY.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-AUG-17

contents of Memory Script:
{
catalog clone datafilecopy "/home/ora11g/11g/oradata/xcky/system01.dbf",
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf",
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf",
"/home/ora11g/11g/oradata/xcky/users01.dbf";
switch clone datafile 1 to datafilecopy
"/home/ora11g/11g/oradata/xcky/system01.dbf";
switch clone datafile 2 to datafilecopy
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf";
switch clone datafile 3 to datafilecopy
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf";
switch clone datafile 4 to datafilecopy
"/home/ora11g/11g/oradata/xcky/users01.dbf";
switch clone datafile all;
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/system01.dbf RECID=2 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/sysaux01.dbf RECID=3 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/undotbs01.dbf RECID=4 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/users01.dbf RECID=5 STAMP=953411462

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=953411462 file name=/opt/oradata/PHOTO_INFO.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=953411462 file name=/opt/oradata/XCKY_INFO.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=953411462 file name=/opt/oradata/BUNCH_INFO.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=953411462 file name=/opt/oradata/TRANSFER_INFO.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=953411462 file name=/opt/oradata/OTHER_INFO.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=953411462 file name=/opt/oradata/BULLETIN_INFO.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=953411462 file name=/opt/oradata/SYSTEM_INFO.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=13 STAMP=953411462 file name=/opt/oradata/INDEX_INFO.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=14 STAMP=953411462 file name=/opt/oradata/VIEW_INFO.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=15 STAMP=953411462 file name=/opt/oradata/VIEW_LOG.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=16 STAMP=953411462 file name=/opt/oradata/XCKY.dbf
Finished Duplicate Db at 30-AUG-17

备库自动mount，开备库

SQL> select status from v$instance;
STATUS
------------
MOUNTED

SQL> alter database open;
Database altered.

SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
应用主库传过来的日志，保证数据一致性。
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

开主库

SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PRIMARY

SQL> alter database open;
Database altered.

SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
默认为最大性能模式
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

3、DG维护

解决备库不能及时应用主库日志

查看哪些从主库传输过来的日志被apply
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
80 YES
......
94 YES
95 YES
96 YES
97 IN-MEMORY
18 rows selected

正常情况，日志会在内存中直接应用传输过来的日志；如果备库重启，就不会及时应用日志，导致主备数据不一致：

解决方案：

备库执行命令重新应用未被应用的日志

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

此时，就开启了RTA(Real Time Apply)

SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS CLIENT_P STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH ARCH CLOSING 1 111 1 1
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 90 1 466
ARCH ARCH CLOSING 1 93 1 17
ARCH ARCH CLOSING 1 112 1 2
ARCH ARCH CLOSING 1 95 1 308
ARCH ARCH CLOSING 1 113 1 1
ARCH ARCH CLOSING 1 114 1 2
ARCH ARCH CLOSING 1 100 1 7
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 115 1 1
ARCH ARCH CLOSING 1 116 1 2
ARCH ARCH CLOSING 1 102 1 1
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 103 1 2
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 104 1 2
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 105 1 1
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 106 1 2
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 107 1 1
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 108 1 2
ARCH ARCH CLOSING 1 109 1 1
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 110 1 2
RFS ARCH IDLE 0 0 0 0
RFS UNKNOWN IDLE 0 0 0 0
RFS UNKNOWN IDLE 0 0 0 0
RFS UNKNOWN IDLE 0 0 0 0
RFS LGWR IDLE 1 117 200 1
MRP0 N/A APPLYING_LOG1 117 200 102400

36 rows selected.

SQL> select dest_id,recovery_mode from v$archive_dest_status
DEST_ID RECOVERY_MODE
---------- ---------------------------
1 MANAGED REAL TIME APPLY

我配置的是log_archive_dest_1，应该看为1的recovery_mode。

SQL> show parameter archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string dg_config=(prixcky,stdxcky)
log_archive_dest_1 string location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=stdxcky

阅读全文

0 0