前天第一次现场实施，扑街。对DataGuard一知半解就敢上生产环境没出大事我也是命大。回来好好反思了一下。阅读官方文档了解了好多参数。网上好多DG教程都不讲参数，在这里详细码一遍。
第一步：网络互通
这个不用多讲了，主机之间通信必须会设置的。
第二步：备份主库
在生产环境中操作一定要谨慎，确保备份的完整性和可用性非常重要，这样一旦发生失误操作也可以使用rman还原。
RMAN> backup database plus archivelog;
查看备份信息：
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.14G DISK 00:00:30 27-MAR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190327T012737
Piece Name: /u01/app/oracle/fast_recovery_area/EASSJ/backupset/2019_03_27/o1_mf_nnndf_TAG20190327T012737_g9pdzspr_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 994759 27-MAR-19 /u01/app/oracle/oradata/eassj/system01.dbf
2 Full 994759 27-MAR-19 /u01/app/oracle/oradata/eassj/sysaux01.dbf
3 Full 994759 27-MAR-19 /u01/app/oracle/oradata/eassj/undotbs01.dbf
4 Full 994759 27-MAR-19 /u01/app/oracle/oradata/eassj/users01.dbf
5 Full 994759 27-MAR-19 /u01/app/oracle/oradata/eassj/example01.dbf
RMAN>
第三步：创建静态监听
为什么需要静态监听呢？动态监听不行吗？在ocm考试的时候，老师要求必须掌握手动配置DataGuard。因为有传言说用GC搭建DG会失败。其实只要操作规范化是不会出现问题的。但没有GC手动命令行搭建也是很有必要的。百度了一下答案，原因是11g的体系中不支持未mount的数据库通过监听被其他数据库连接。但是也有蹊径，就是在服务名里加入（UR=A)这个选项。我上次搭建异构平台的dataguard这样是可以解决的。但在同平台版本下可以用rman连接成功，却会在执行过程中报错。所以老老实实的配置吧。
主库：eassj 192.168.220.22 主机名：c1
备库：eassj 192.168.220.24 主机名：c2
首先在主库上创建：
（有条件直接netmgr就完事了，因为格式的一点点不规范oracle有时候就不会识别）我是在别的库netmgr然后复制了listener.ora文件里的结构。
c1：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = eassj)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = eassj)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = c1)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
c2：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = eassj)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = eassj)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = c2)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
创完以后记得开启
第四步：创建服务名
c1：
EASSJ_C1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = c1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = eassj)
)
)
EASSJ_C2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = c2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = eassj)
)
)
c2：
EASSJ_C1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = c1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = eassj)
)
)
EASSJ_C2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = c2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = eassj)
)
)
第五步：传送密钥文件
c1：
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ll
total 9540
-rw-rw----. 1 oracle oinstall 1544 Mar 27 01:17 hc_eassj.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Mar 27 01:15 lkEASSJ
-rw-r-----. 1 oracle oinstall 1536 Mar 27 01:17 orapweassj
-rw-r-----. 1 oracle oinstall 9748480 Mar 27 01:28 snapcf_eassj.f
-rw-r-----. 1 oracle oinstall 2560 Mar 27 01:18 spfileeassj.ora
[oracle@localhost dbs]$ scp -p orapweassj c2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
The authenticity of host 'c2 (192.168.220.24)' can't be established.
RSA key fingerprint is 0d:da:6b:97:dc:73:76:60:29:6d:55:f6:fe:72:dc:b7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'c2,192.168.220.24' (RSA) to the list of known hosts.
oracle@c2's password:
orapweassj 100% 1536 1.5KB/s 00:00
[oracle@localhost dbs]$
c2：
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ll
total 8
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Mar 27 01:17 orapweassj
[oracle@localhost dbs]$
第六步：创建临时init初始化参数文件
[oracle@localhost dbs]$ vi initeassj.ora
[oracle@localhost dbs]$ cat initeassj.ora
DB_NAME=eassj
[oracle@localhost dbs]$
第七步：创建辅助日志
关于辅助日志的理解可以参考这篇文章：理解standby redo log
非常重要的是，这一步创建的辅助日志数量应遵循以下两点：
1.个数遵循 nx+1（n为日志组数，x为节点数）
2文件大小遵循和主库日志大小一致。
在第二点上我曾吃过很深的教训。刚到公司第二天老大就让我搭建一个异构平台的DG。搭建是搭建出来了，但是却一直无法实现实时同步。在每次开启的时候都提示 "不兼容的介质” 由于问题提示不是显式的，找了很久答案，最后才发现是辅助日志的大小和主库日志大小不一致导致的。浪费时间！头疼。而且很多教学资源都未提到这一点。今天在看官方文档的时候发现在官方文档里是有的。所以以后还是要多啃官方文档。（坑爹，有日语都没汉语，英文看的头大）。
SQL> alter database add standby logfile group 7('/u01/app/oracle/oradata/eassj/sredo07.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 8('/u01/app/oracle/oradata/eassj/sredo08.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 9('/u01/app/oracle/oradata/eassj/sredo09.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 10('/u01/app/oracle/oradata/eassj/sredo10.log') size 50m;
Database altered.
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
7 0 0 YES UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
10 0 0 YES UNASSIGNED
第八步：修改主库参数
注意有关shared_server、dispatcher的参数需要清空。local_listener参数如果存在，需要清除。
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(eassj,eassj_st)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=eassj_c2 lgwr SYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=eassj_st';
- LOG_ARCHIVE_CONFIG
该参数定义了DG配置中可用的DB_UNIQUE_NAME参数值列表。与目标参数DB_UNIQUE_NAME的值结合使用时，DG以它们来实现两个数据库之间连接的安全性检查工作。只要不指定SEND和RECEIVE属性，这个参数就是动态的，这两个属性是旧参数REMOTE_ARCHIVE_ENABLE遗留下来的，已经不再需要，因此就不要再使用了。
在实际使用时，你只需要将其他数据库的唯一名称添加到配置就可以了，当前数据库的唯一名会根据场景自动添加；不过为了清晰期间，并且在所有的数据库中保持该参数的一致性，还是会将当前数据库的唯一名称明确的添加上去。对于名称的配置顺序没有要求，该参数在有RAC的环境中是必须要配置的，应该始终使用该参数。
在本例当中，我们主库的db_unique_name为eassj，准备创建的备库db_unique_name为eassj_st。
- LOG_ARCHIVE_DEST_2
官方文档中的解释：
LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination boston.
可以看到，该参数只有当数据库角色为主库时才会有作用。在备库上也设置此参数的目的是为今后的故障切换做准备。此目标将重做数据传输到远程物理备用目标。需要注意的是：
-
SERVICE=eassj_c2中的eassj_c2应是tnsnames文件中存在的、指向备库的服务名。
-
SYNC\ASYNC 指定日志是同步传输还是异步传输。这个参数的设置参考的是备库的高可用模式。一般的，有：
- 最大保护：同步
- 最大性能：异步
- 最高可用：能同步就不异步，不能同步时才会异步。
本例当中需要开启最大可用模式，所以这里设置为：SYNC
- VALID_FOR
VALID_FOR属性由2部分组成:archive_source(online_logfile,standby_logfile,all_logfiles)和database_role(primary_role,standby_role,all_role).
online_logfile: 表示归档联机重做日志
standby_logfile:表示归档备用数据库的重做日志/接受来自主库的重做日志
all_logfiles: online_logfile && standby_logfile
primary_role: 仅当数据库角色为主库时候生效
standby_role: 仅当数据库角色为备库时候生效
all_role: 任意角色均生效
*.log_archive_dest_1='location=/u01/app/oracle/archive_log'
*.log_archive_dest_2='service=DG lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG'
比如主备库都设置如上
主库就会使用位置1来进行归档(而且默认 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=主库的DB_UNIQUE_NAME)，
使用位置2来进行归档（其实就是通过lgwr async传输到tns中别名为DG，DB_UNIQUE_NAME=DG的备库的/u01/app/oracle/archive_log）
备库只是被动的使用位置1来接受来自主库的归档日志，如alert中会出现的一句话Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archive_log
位置2由于设置为PRIMARY_ROLE而自己是standby则不会生效
一旦发生切换
原来的主库的位置2就不起作用了，原来的备库的位置2就起作用了
如果主备都把位置2设置为ALL_LOGFILES,ALL_ROLES，备库还要给主库发归档日志，那就矛盾了。
- DB_UNIQUE_NAME
官方文档的解释:
Specify a unique name for each database. This name stays with the database and does not change, even if the primary and standby databases reverse roles.
为每个数据库指定唯一的名称。即使主数据库和备用数据库反向角色，此名称仍保留在数据库中，并且不会更改。
该参数的作用在于，假如创建了同db_name的主备库（如本例），db_unique_name能够帮助你识别实例。此参数不可重复，否则会报出：已存在此hash值 的错误。
c1：
SQL> alter database force logging;
关于这一步的说明参考文章：实验说明为什么DataGuard需要设置force logging
本文不再赘述。
设置参数后需要重启实例。
第九步：检查主库是否archive mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
第十步：为备库创建相应的文件夹
[oracle@localhost oracle]$ mkdir -p /u01/app/oracle/oradata/eassj
[oracle@localhost oracle]$ mkdir -p /u01/app/oracle/admin/eassj/{a,dp}dump
[oracle@localhost oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area
第十一步：备库启动到startup nomount状态
不启怎么rman连。
c2：
[oracle@localhost ~]$ . .bash_profile
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 27 19:07:11 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 229683200 bytes
Fixed Size 2251936 bytes
Variable Size 171967328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5132288 bytes
SQL>
第十二步：主库服务器连接到rman
[oracle@localhost ~]$ rman target sys/oracle@eassj_c1 auxiliary sys/oracle@eassj_c2
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 27 19:09:15 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: EASSJ (DBID=3630113189)
connected to auxiliary database: EASSJ (not mounted)
第十三步：编辑脚本
RUN {
ALLOCATE CHANNEL D1 TYPE DISK;
ALLOCATE CHANNEL D2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX1 TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE nofilenamecheck
SPFILE
PARAMETER_VALUE_CONVERT 'eassj','eassj'
SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
SET DB_UNIQUE_NAME='eassj_st'
SET
CONTROL_FILES='/u01/app/oracle/oradata/eassj/control01.ctl'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(eassj,eassj_st)'
SET LOG_ARCHIVE_DEST_2='SERVICE=eassj_c1 lgwr SYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=eassj'
SET FAL_SERVER='eassj_c1'
SET
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/eassj/','/u01/app/oracle/oradata/eassj'
SET
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/eassj','/u01/app/oracle/oradata/eassj'
SET STANDBY_FILE_MANAGEMENT='AUTO';
}
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE nofilenamecheck
在这一行语句中，nofilenamecheck这个参数的作用是不检查文件名。
如果副本数据库与目标库不在同一台机器上，并且副本数据库的在线日志文件路径与目标库相同，则运行duplicate命令时必须指定NOFILENAMECHECK参数以避免冲突提示。晕了吧，异机操作路径相同还必需指定NOFILENAMECHECK。此处oracle表现的很傻，它不知道你要恢复的路径是在另一台机器上，它只是认为要恢复到的路径怎么跟目标数据库表现的一样呢？会不会是要覆盖目标数据库啊，为了避免这种情形，于是它就报错。所以一旦异机恢复，并且路径相同，那么你必须通过指定NOFILENAMECHECK来避免oracle的自动识别。
此处在官方文档中也有提及：
If you have a standby database on the same system as the primary database, you must use a different directory structure. Otherwise, the standby database attempts to overwrite the primary database files.
- PARAMETER_VALUE_CONVERT
PARAMETER_VALUE_CONVERT 'eassj','eassj'
参数转换。
- DB_UNIQUE_NAME
SET DB_UNIQUE_NAME='eassj_st'
指定数据库唯一名，必须和主库名还有其他备库名不一致。
- FAL_SERVER
SET FAL_SERVER='eassj_c1'
引用官方文档：
Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files. See Section 5.8.
FAL_SERVER FAL(Fetch Archive Log)功能相比9iR1时的DG已经有了很大的进步。它只用于物理备库，配置它能够使得物理备库在发现问题时，从DG配置中的一个数据库(主库或备库)中获取缺失的归档日志文件，有时我们又成它为被动间隔处理(reactive gap resolution)，不过FAL技术在之前的三个版本中得到了极大的增强以至于现在几乎不需要再定义FAL参数了。伴随着9iR2版本引入的主动间隔处理(proactive gap resolution)技术的使用，几乎物理或逻辑备库上任何类型的间隔请求都可以由主库上的ping进程来处理了。
RMAN> RUN {
ALLOCATE CHANNEL D1 TYPE DISK;
ALLOCATE CHANNEL D2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX1 TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE nofilenamecheck
SPFILE
PARAMETER_VALUE_CONVERT 'eassj','eassj'
SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
SET DB_UNIQUE_NAME='eassj_st'
SET
CONTROL_FILES='/u01/app/oracle/oradata/eassj/control01.ctl'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(eassj,eassj_st)'
SET LOG_ARCHIVE_DEST_2='SERVICE=eassj_c1 lgwr SYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=eassj'
SET FAL_SERVER='eassj_c1'
SET
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/eassj/','/u01/app/oracle/oradata/eassj'
SET
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/eassj','/u01/app/oracle/oradata/eassj'
SET STANDBY_FILE_MANAGEMENT='AUTO';
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
using target database control file instead of recovery catalog
allocated channel: D1
channel D1: SID=24 device type=DISK
allocated channel: D2
channel D2: SID=18 device type=DISK
allocated channel: AUX1
channel AUX1: SID=10 device type=DISK
Starting Duplicate Db at 27-MAR-19
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapweassj' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapweassj' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileeassj.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileeassj.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileeassj.ora''";
}
executing Memory Script
Starting backup at 27-MAR-19
Finished backup at 27-MAR-19
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileeassj.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/eassj/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=eassjXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''/u01/app/oracle/fast_recovery_area'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''eassj_st'' comment=
'''' scope=spfile";
sql clone "alter system set CONTROL_FILES =
''/u01/app/oracle/oradata/eassj/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_CONFIG =
''DG_CONFIG=(eassj,eassj_st)'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_2 =
''SERVICE=eassj_c1 lgwr SYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=eassj'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_SERVER =
''eassj_c1'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u01/app/oracle/oradata/eassj/'', ''/u01/app/oracle/oradata/eassj'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_FILE_NAME_CONVERT =
''/u01/app/oracle/oradata/eassj'', ''/u01/app/oracle/oradata/eassj'' comment=
'''' scope=spfile";
sql clone "alter system set STANDBY_FILE_MANAGEMENT =
''AUTO'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/eassj/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=eassjXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''eassj_st'' comment= '''' scope=spfile
sql statement: alter system set CONTROL_FILES = ''/u01/app/oracle/oradata/eassj/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set LOG_ARCHIVE_CONFIG = ''DG_CONFIG=(eassj,eassj_st)'' comment= '''' scope=spfile
sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''SERVICE=eassj_c1 lgwr SYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=eassj'' comment= '''' scope=spfile
sql statement: alter system set FAL_SERVER = ''eassj_c1'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/eassj/'', ''/u01/app/oracle/oradata/eassj'' comment= '''' scope=spfile
sql statement: alter system set LOG_FILE_NAME_CONVERT = ''/u01/app/oracle/oradata/eassj'', ''/u01/app/oracle/oradata/eassj'' comment= '''' scope=spfile
sql statement: alter system set STANDBY_FILE_MANAGEMENT = ''AUTO'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1169149952 bytes
Fixed Size 2252624 bytes
Variable Size 738197680 bytes
Database Buffers 419430400 bytes
Redo Buffers 9269248 bytes
allocated channel: AUX1
channel AUX1: SID=133 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/eassj/control01.ctl';
}
executing Memory Script
Starting backup at 27-MAR-19
channel D1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_eassj.f tag=TAG20190327T234822 RECID=2 STAMP=1004053702
channel D1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-MAR-19
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
"/u01/app/oracle/oradata/eassjtemp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/eassjsystem01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/eassjsysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/eassjundotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/eassjusers01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/eassjexample01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/eassjsystem01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/eassjsysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/eassjundotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/eassjusers01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/eassjexample01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/eassjtemp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 27-MAR-19
channel D1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/eassj/system01.dbf
channel D2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/eassj/sysaux01.dbf
output file name=/u01/app/oracle/oradata/eassjsysaux01.dbf tag=TAG20190327T234831
channel D2: datafile copy complete, elapsed time: 00:02:26
channel D2: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/eassj/example01.dbf
output file name=/u01/app/oracle/oradata/eassjsystem01.dbf tag=TAG20190327T234831
channel D1: datafile copy complete, elapsed time: 00:03:31
channel D1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/eassj/undotbs01.dbf
output file name=/u01/app/oracle/oradata/eassjexample01.dbf tag=TAG20190327T234831
channel D2: datafile copy complete, elapsed time: 00:01:30
channel D2: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/eassj/users01.dbf
output file name=/u01/app/oracle/oradata/eassjusers01.dbf tag=TAG20190327T234831
channel D2: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/eassjundotbs01.dbf tag=TAG20190327T234831
channel D1: datafile copy complete, elapsed time: 00:00:51
Finished backup at 27-MAR-19
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=2 STAMP=1004053973 file name=/u01/app/oracle/oradata/eassjsystem01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1004053973 file name=/u01/app/oracle/oradata/eassjsysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1004053973 file name=/u01/app/oracle/oradata/eassjundotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1004053973 file name=/u01/app/oracle/oradata/eassjusers01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1004053973 file name=/u01/app/oracle/oradata/eassjexample01.dbf
Finished Duplicate Db at 27-MAR-19
released channel: D1
released channel: D2
released channel: AUX1
执行完成。
第十四步：验证
c2：
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 28 00:10:08 2019
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> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string /u01/app/oracle/oradata/eassj/
, /u01/app/oracle/oradata/eass
j
db_name string eassj
db_unique_name string eassj_st
global_names boolean FALSE
instance_name string eassj
lock_name_space string
log_file_name_convert string /u01/app/oracle/oradata/eassj,
/u01/app/oracle/oradata/eassj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processor_group_name string
service_names string eassj_st
SQL> alter database open;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
c1：
SQL> create table zmx (i int);
Table created.
c2：
SQL> desc zmx;
Name Null? Type
----------------------------------------- -------- ----------------------------
I NUMBER(38)
可以看到不用切换日志，数据也是实时同步的。
c2：
NAME VALUE DATUM_TIME
-------------------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 03/28/2019 00:29:04
apply lag +00 00:00:00 03/28/2019 00:29:04
apply finish time +00 00:00:00.000
estimated startup time 10
延时为0，主备库已实现实时同步。
第十五步：数据库的主备切换
c1：
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PRIMARY MAXIMUM PERFORMANCE TO STANDBY
c2：
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
可以看到，主库的状态是可以转换为备库，而备库状态则是NOT ALLOWED，这是因为主库没有发起转换的请求。
c1：
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PHYSICAL STANDBY MAXIMUM PERFORMANCE TO PRIMARY
c2：
这台机器需要mount，因为它即将转换为主库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
//如果你没有开启应用日志模式，那你的状态将持续为NOT ALLOWED。
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PHYSICAL STANDBY MAXIMUM PERFORMANCE TO PRIMARY
现在，两台主机都处于TO PRIMARY的状态，此时需要在c2上运行这条语句。
c2：
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PRIMARY MAXIMUM PERFORMANCE NOT ALLOWED
c1：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
新的备库上开启应用日志模式。
c2：
开启数据库
SQL> alter database open;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PRIMARY MAXIMUM PERFORMANCE RESOLVABLE GAP
SQL> /
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PRIMARY MAXIMUM PERFORMANCE SESSIONS ACTIVE
SQL> create table lay (i int);
Table created.
看到状态从RESOLVABLE GAP到SESSIONS ACTIVE，这说明切换已经完成。建表验证
c1：
SQL> desc lay
Name Null? Type
----------------------------------------- -------- ----------------------------
I NUMBER(38)
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------- -------------------- ------------------------------
transport lag +00 00:00:00 03/28/2019 01:00:56
apply lag +00 00:00:00 03/28/2019 01:00:56
apply finish time +00 00:00:00.000
estimated startup time 46
同步更新！
第十六步：切换至最大可用模式
c2：（现为主库）
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PRIMARY MAXIMUM AVAILABILITY TO STANDBY
c1：
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EASSJ PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED
3554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
