Oracle11g搭建DataGuard及主备切换方法（抄录琼杰笔记）_oracle dataguard

---

前言

提示：琼杰笔记的这篇文章是要付费的希望有能力的去支持一下：

官方的这篇文章我是付过费的但是官方网站却不能注册账号，又害怕浏览器一关丢失了付费信息所以还是抄录一份。

---

一、DataGuard 工作原理

Data Gurad 通过冗余数据来提供数据保护，Data Gurad 通过日志同步机制保证冗余数据和主数之前的同步，这种同步可以是实时，延时，同步，异步多种形式。Data Gurad 常用于异地容灾和小企业的高可用性方案，虽然可以在Standby 机器上执行只读查询，从而分散Primary 数据库的性能压力，但是Data Gurad 决不是性能解决方案。

Stream 是以Oracle Advanced Queue为基础实现的数据同步，提供了多种级别的灵活配置，并且Oracle 提供了丰富的API等开发支持，Stream 更适用在应用层面的数据共享。

在Data Gurad 环境中，至少有两个数据库，一个处于Open 状态对外提供服务，这个数据库叫作Primary Database。第二个处于恢复状态，叫作Standby Database。运行时primary Database 对外提供服务，用户在Primary Database 上进行操作，操作被记录在联机日志和归档日志中，这些日志通过网络传递给Standby Database。这个日志会在Standby Database 上重演，从而实现Primary Database 和Standby Database 的数据同步。

Oracle Data Gurad 对这一过程进一步的优化设计，使得日志的传递，恢复工作更加自动化，智能化，并且提供一系列参数和命令简化了DBA工作。

如果是可预见因素需要关闭Primary Database，比如软硬件升级，可以把Standby Database 切换为Primary Database 继续对外服务，这样即减少了服务停止时间，并且数据不会丢失。如果异常原因导致Primary Database 不可用，也可以把Standby Database 强制切换为Primary Database继续对外服务，这时数据损失成都和配置的数据保护级别有关系。因此Primary 和Standby 只是一个角色概念，并不固定在某个数据库中。

DG架构可以按照功能分成3个部分：

1） 日志发送（Redo Send）
2） 日志接收（Redo Receive）
3） 日志应用（Redo Apply）

DataGuard 数据保护模式

Data Guard 允许定义3钟数据保护模式，分别是最大保护（Maximum Protection），最大可用（Maximum Availability）和最大性能（Maximum Performance）。

1. 最大保护（Maximum Protection）

这种模式能够确保绝无数据丢失。要实现这一步当然是有代价的，它要求所有的事务在提交前其REDO不仅被写入到本地的Online Redologs，还要同时写入到Standby数据库的Standby Redologs，并确认REDO数据至少在一个Standby数据库中可用（如果有多个的话），然后才会在Primary数据库上提交。如果出现了什么故障导致Standby数据库不可用的话（比如网络中断），Primary数据库会被Shutdown，以防止数据丢失。

使用这种方式要求Standby Database 必须配置Standby Redo Log，而Primary Database必须使用LGWR，SYNC，AFFIRM 方式归档到Standby Database.

2. 最高可用性（Maximum availability）

这种模式在不影响Primary数据库可用前提下，提供最高级别的数据保护策略。其实现方式与最大保护模式类似，也是要求本地事务在提交前必须至少写入一台Standby数据库的Standby Redologs中，不过与最大保护模式不同的是，如果出现故障导致Standby数据库无法访问，Primary数据库并不会被Shutdown，而是自动转为最高性能模式，等Standby数据库恢复正常之后，Primary数据库又会自动转换成最高可用性模式。

这种方式虽然会尽量避免数据丢失，但不能绝对保证数据完全一致。这种方式要求Standby Database 必须配置Standby Redo Log，而Primary Database必须使用LGWR，SYNC，AFFIRM 方式归档到Standby Database.

3. 最高性能（Maximum performance）

缺省模式。这种模式在不影响Primary数据库性能前提下，提供最高级别的数据保护策略。事务可以随时提交，当前Primary数据库的REDO数据至少需要写入一个Standby数据库，不过这种写入可以是不同步的。如果网络条件理想的话，这种模式能够提供类似最高可用性的数据保护，而仅对Primary数据库的性能有轻微影响。这也是创建Standby数据库时，系统的默认保护模式。

这种方式可以使用LGWR ASYNC 或者 ARCH 进程实现，Standby Database也不要求使用Standby Redo Log。

二、搭建环境

三、主库配置

1、开启归档模式，参考：oracle开启关闭归档以及归档空间满的处理方法总结
2、开启强制日志模式：

alter database force logging;
1

查看

SQL> select name,log_mode,force_logging from v$database;

NAME LOG_MODE FOR
--------- ------------ ---
ORCL ARCHIVELOG YES
1
2
3
4
5

3、创建standby redolog日志组：
原则：

1：standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2：standby redo log日志文件组的个数依照下面的原则进行计算：
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
假如只有一个节点，这个节点有三组redolog，
所以Standby redo log组数>=(3+1)*1 == 4
所以至少需要创建4组Standby redo log
1
2
3
4
5
6

查看当前线程与日志组的对应关系及日志组的大小：

SQL> select thread#,group#,bytes/1024/1024 from v$log;

THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 50
1 2 50
1 3 50
1
2
3
4
5
6
7

如上，这里有三组redo log，所以至少需要创建4组Standby redo log，大小均为50M：

alter database add standby logfile group 4 ('/data/oracle/app/oracle/oradata/archivelog/redo_dg_04.log') size 50M;
alter database add standby logfile group 5 ('/data/oracle/app/oracle/oradata/archivelog/redo_dg_05.log') size 50M;
alter database add standby logfile group 6 ('/data/oracle/app/oracle/oradata/archivelog/redo_dg_06.log') size 50M;
alter database add standby logfile group 7 ('/data/oracle/app/oracle/oradata/archivelog/redo_dg_07.log') size 50M;
1
2
3
4

若删除组：

alter database drop standby logfile group x;
1

查看standy日志组的信息：

SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

GROUP# SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------
4 0 UNASSIGNED 50
5 0 UNASSIGNED 50
6 0 UNASSIGNED 50
7 0 UNASSIGNED 50
1
2
3
4
5
6
7
8

4、创建主库密码文件

su - oracle
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y
1
2

5、配置spfile文件：

查看spfile文件路径：

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
1
2
3
4
5
6

2.读入数据

代码如下（示例）：

data = pd.read_csv(
    'https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())
1
2
3

用spfile创建一个pfile,用于修改：

create pfile='/tmp/initorcl.ora' from spfile;
1

修改pfile文件
vim /tmp/initorcl.ora

orcl.__db_cache_size=637534208
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base=’/u01/app/oracle’
orcl.__pga_aggregate_target=671088640
orcl.__sga_target=989855744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=268435456
orcl.__streams_pool_size=16777216
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/fast_recovery_area/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=1653604352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.db_unique_name=’orclpr’
*.fal_client=’orclpr’
*.fal_server=’orcldg’
*.standby_file_management=’AUTO’
*.log_archive_config=’DG_CONFIG=(orclpr,orcldg)’
*.log_archive_dest_1=’location=/u01/app/oracle/oradata/orcl/archivelog’
*.log_archive_dest_2=’SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’

复制pfile文件到spfile:

shutdown immediate;
create spfile from pfile='/tmp/initorcl.ora';
startup;
1
2
3

#主库参数详解：

保持同一个Data Guard中所有的DB_NAME相同
DB_NAME=ora11g
 
 为一个数据库指定一个唯一的名称，该参数一经指定就不会发生改动除非DBA主动改动
DB_UNIQUE_NAME=ora11g_primary
 
初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志，并指定Data  Guard配置的惟一数据库名，默认值为SEND，RECEIVE，NODG_CONFIG。

当设置该参数为SEND时，会激活发送归档日志到远程位置；当设置该能数为NOSEND时，会禁止发送归档日志到远程位置；当设置该参数为RECEIVE时，会激活接收远程归档日志；当设置该参数

为NORECEIVE时，会禁止接收远程归档日志；当设置该参数为DG_CONFIG时，可以最多指定9个惟一数据库名；当设置该参数为NODG_CONFIG时，会禁止指定惟一数据库名。

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11g_primary ,ora11g_standby)'   
 
指定本地归档的路径
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_primary'
 
指定远端备库的归档路径
LOG_ARCHIVE_DEST_2='SERVICE=ora11g_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_standby'
 
LOG_ARCHIVE_DEST_n(从1到10)定义redo文件路径。该参数必须通过location或service指定归档文件路径。location表示本地路径，service通常是net service name,即接收redo数据的

standby数据库。

注意：每一个LOG_ARCHIVE_DEST_n都有一个对应的LOG_ARCHIVE_DEST_STATE_n参数，该参数拥有以下4个属性值：

ENABLE:默认值，表示允许传输服务

DEFER: 指定对应的log_archive_dest_n参数有效，但暂不使用

ALTERNATE:禁止传输，但是如果其他相关的目的地的连接通通失败，则它将变成enable

RESET:功能与DEFER类似，不过如果传输目的地之前有过错误，它会清除所有错误信息

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
 
远程登录设置独享模式(z注意保证主备库密码相同)
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
%s log sequence number
%S log sequence number, zero filled %t thread number, RAC的节点中设有THREAD参数， 就是这个值了
%T thread number, zero filled
 ------其中这前4项是可以用在 9I 中的
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

 Oracle 10g开始，log_archive_format初始化参数必须包含%s, %t 和 %r,以确保生成的归档重做日志名称唯一。
（原因：10g能做穿越resetlog的恢复，所以要加%r.而9i的不能做穿越resetlog的恢复，所以没有%r的参数）


LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
当Primary Database的某些日志没有发送到Standby Database 这时候发生归档裂缝(Archive Gap)
缺失的日志就是裂缝(GAP)。Data Guard 能够自动检测，解决归档裂缝，不需要DBA的介入。这需要配置
FAL_CLIENT,FAL_SERVER这两个参数(FAL:Fetch Archive Log)
从FAL这个名字可以看出，这个过程是 Standby Database 主动发起的取日志的过程,Standby Database 就是
FAL_CLIENT,它是从FAL_SERVER中取这些GAP。10g中，这个FAL_SERVER可以是Primary Database,也可以是其他的
Standby Database
 
FAL_CLIENT和FAL_SERVER两个参数都是Oracle Net NameFAL_CLIENT通过网络向FAL_SERVER发送请求,
FAL_SERVER通过网络向FAL_CLIENT发送缺失的日志
但是这两个连接不一定是一个连接。因此 FAL_CLIENT向FAL_SERVER发送请求时，会携带FAL_CLIENT的参数
值,用来告诉FAL_SERVER应该向哪里发送缺少的日志。这个参数也是一个Oracle Net Name 这个Name在FAL_SERVER
上定义的，用来指向FAL_CLIENT
FAL_SERVER=ora11g_standby
FAL_CLIENT=ora11g_primary
 
如果Primary数据库数据文件发生修改(如新建)则在standby数据库作相应修改
设为AUTO表示自动管理。设为MANUAL表示需要手工管理（注:重命名修改
是不会传到standby数据库)
STANDBY_FILE_MANAGEMENT=AUTO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

5、修改监听文件，添加静态监听：

vi $ORACLE_HOME/network/admin/listener.ora
1

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)


ADR_BASE_LISTENER = /u01/app/oracle

SAVE_CONFIG_ON_STOP_LISTENER = ON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

重启监听服务：

lsnrctl stop
lsnrctl start  或
lsnrctl reload
1
2
3

6、编辑网络服务名配置文件tnsnames.ora：

vi $ORACLE_HOME/network/admin/tnsnames.ora
1

orcldg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

orclpr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

tnsping测试：

[oracle@oracle11g dbs]$ tnsping orclpr

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-4月 -2019 14:30:09

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@oracle11g dbs]$ tnsping orcldg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-4月 -2019 14:30:11

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@oracle11g dbs]$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

四、备库配置

1、将主库中的密码文件、pfile文件、监听文件复制到备库中：

cd /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp orapworcl 172.16.8.11:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

scp /tmp/initorcl.ora 172.16.8.11:/tmp/

cd /data/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin
scp listener.ora 172.16.8.11:/data/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp tnsnames.ora 172.16.8.11:/data/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
1
2
3
4
5
6
7
8

2、配置spfile文件：
修改pfile文件：

vim /tmp/initorcl.ora
1

orcl.__db_cache_size=637534208
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base=’/u01/app/oracle’
orcl.__pga_aggregate_target=671088640
orcl.__sga_target=989855744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=268435456
orcl.__streams_pool_size=16777216
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/fast_recovery_area/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=1653604352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.db_unique_name=’orcldg’
*.fal_client=’orcldg’
*.fal_server=’orclpr’
*.standby_file_management=’AUTO’
*.log_archive_config=’DG_CONFIG=(orclpr,orcldg)’
*.log_archive_dest_1=’location=/u01/app/oracle/oradata/orcl/archivelog’
*.log_archive_dest_2=’SERVICE=orclpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’

vim /tmp/initorcl.ora

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

orcl.__db_cache_size=637534208
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'
orcl.__pga_aggregate_target=671088640
orcl.__sga_target=989855744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=268435456
orcl.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1653604352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='orcldg'
*.fal_client='orcldg'
*.fal_server='orclpr'
*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(orclpr,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/archivelog'
*.log_archive_dest_2='SERVICE=orclpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

复制pfile文件到spfile：

shutdown immediate;
create spfile from pfile='/tmp/initorcl.ora';

startup nomount;
1
2
3
4

3、修改监听文件：

vi $ORACLE_HOME/network/admin/listener.ora
1

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1) 
(SID_NAME = orcl)
)
)


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /data/oracle/app/oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

重启监听：

lsnrctl stop
lsnrctl start
1
2

4、tnsping测试：

[oracle@oracle11gstandby ~]$ tnsping orclpr

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-4月 -2019 14:21:44

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)
[oracle@oracle11gstandby ~]$ tnsping orcldg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-4月 -2019 14:21:47

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5、手工创建所需的目录：

su - oracle
mkdir -p /data/oracle/app/oracle/admin/orcl/adump
mkdir -p /data/oracle/app/oracle/admin/orcl/dbdump
mkdir -p /data/oracle/app/oracle/admin/orcl/pfile
mkdir -p /data/oracle/app/oracle/oradata/orcl
mkdir -p /data/oracle/app/oracle/fast_recovery_area/orcl
mkdir -p /data/oracle/app/oracle/oradata/orcl/archivelog
1
2
3
4
5
6
7

6、启动备库到nomount：

shutdown immediate;
startup nomount;
1
2

7、利用RMAN在备库上恢复主库：

rman target sys/password@orclpr auxiliary sys/password@orcldg

duplicate target database for standby from active database nofilenamecheck;
1
2
3

恢复过程如下：

[oracle@oracle11gstandby admin]$ rman target sys/password@orclpr auxiliary sys/password@orcldg

Recovery Manager: Release 11.2.0.4.0 - Production on 星期三 4月 10 15:52:38 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1532410718)
connected to auxiliary database: ORCL (not mounted)

RMAN> 
duplicate target database for standby from active database nofilenamecheck;
RMAN> 
duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 2019-04-10 15:53:04
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2275 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/data/oracle/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format 
'/data/oracle/app/oracle/product/11.2.0/db_1/dbs/orapworcl' ;
}
executing Memory Script

Starting backup at 2019-04-10 15:53:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
Finished backup at 2019-04-10 15:53:07

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/data/oracle/app/oracle/oradata/orcl/control01.ctl';
restore clone controlfile to '/data/oracle/app/oracle/fast_recovery_area/orcl/control02.ctl' from 
'/data/oracle/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 2019-04-10 15:53:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/data/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20190405T162324 RECID=1 STAMP=1004804604
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2019-04-10 15:53:08

Starting restore at 2019-04-10 15:53:08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2019-04-10 15:53:09

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

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to 
"/data/oracle/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/data/oracle/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 2 to 
"/data/oracle/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to 
"/data/oracle/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to 
"/data/oracle/app/oracle/oradata/orcl/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/data/oracle/app/oracle/oradata/orcl/system01.dbf" datafile 
2 auxiliary format 
"/data/oracle/app/oracle/oradata/orcl/sysaux01.dbf" datafile 
3 auxiliary format 
"/data/oracle/app/oracle/oradata/orcl/undotbs01.dbf" datafile 
4 auxiliary format 
"/data/oracle/app/oracle/oradata/orcl/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/oracle/app/oracle/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2019-04-10 15:53:16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/data/oracle/app/oracle/oradata/orcl/system01.dbf tag=TAG20190405T162333
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/data/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/data/oracle/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20190405T162333
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/data/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/data/oracle/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20190405T162333
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/data/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/data/oracle/app/oracle/oradata/orcl/users01.dbf tag=TAG20190405T162333
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2019-04-10 15:53:52

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1005234832 file name=/data/oracle/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1005234832 file name=/data/oracle/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1005234832 file name=/data/oracle/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1005234832 file name=/data/oracle/app/oracle/oradata/orcl/users01.dbf
Finished Duplicate Db at 2019-04-10 15:53:58

RMAN>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138

恢复完成！
过程中若报错如下：

DBGSQL:     TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;
DBGSQL:        sqlcode = 6550
DBGSQL:         B :fhdbi = 32767
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2018 15:22:28
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
1
2
3
4
5
6
7
8
9
10
11
12
13

说明使用了catalog,但是在连接的时候没有指定catalog，需要用下面的连接方式：

rman target sys/123.com@orclpr auxiliary sys/123.com@orcldg nocatalog
1

8、 登陆备库并查看数据库当前状态:

[oracle@ora11gs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 8 15:45:34 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
MOUNTED
---------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

RMAN恢复完直接就是mount状态。
9、备库启动日志应用：

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> set pagesize 100;
SQL>  select sequence#,applied from v$archived_log order by 1;

 SEQUENCE# APPLIED
---------- ---------
    76 YES
    77 YES
    78 YES
---------------------
1
2
3
4
5
6
7
8
9
10
11
12
13

10、分别查看主库和备库的归档序列号是否一致：
先在主库手动切换一下日志再查看：

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oradata/orcl/archivelog
Oldest online log sequence     78
Next log sequence to archive   80
Current log sequence           80
1
2
3
4
5
6
7
8
9
10
11

再在备库上查看：

 SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oradata/orcl/archivelog
Oldest online log sequence     78
Next log sequence to archive   0
Current log sequence           80
1
2
3
4
5
6
7

11、查看备库中各文件如下：

[root@ora11gs orcl]# ll
total 2001444
drwxr-xr-x. 2 oracle oinstall      4096 Nov  8 15:47 archivelog
-rw-r-----. 1 oracle oinstall   9781248 Nov  8 15:48 control01.ctl
-rw-r-----. 1 oracle oinstall  10493952 Nov  8 15:47 db_test11.dbf
-rw-r-----. 1 oracle oinstall   5251072 Nov  8 15:47 db_test12.dbf
-rw-r-----. 1 oracle oinstall  52436992 Nov  8 15:47 db_test1.dbf
-rw-r-----. 1 oracle oinstall  33562624 Nov  8 15:47 db_test.dbf
-rw-r-----. 1 oracle oinstall 328343552 Nov  8 15:47 example01.dbf
-rw-r-----. 1 oracle oinstall 692068352 Nov  8 15:47 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 786440192 Nov  8 15:47 system01.dbf
-rw-r-----. 1 oracle oinstall 125837312 Nov  8 15:47 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Nov  8 15:47 users01.dbf
[root@ora11gs orcl]# ll archivelog/
total 5848
-rw-r-----. 1 oracle oinstall 5109248 Nov  8 15:47 1_76_981729112.dbf
-rw-r-----. 1 oracle oinstall  530432 Nov  8 15:47 1_77_981729112.dbf
-rw-r-----. 1 oracle oinstall  344064 Nov  8 15:47 1_78_981729112.dbf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

至此，dataguard已部署完成，可以测试是否成功！

五、主备切换

Dataguard主、备库切换方法总结