搭建记录：duplicate搭建oracle rac到单实例搭建物理dg-m6米乐安卓版下载

以前一直也用这个文档来在小型的生产环境搭建adg，经过了多轮的改版，之前上墨天轮上发布过6.0的版本
经过多次检查发现还是有修改的空间，经过一部分校正，再发布第7版。如果有错误还请大家帮忙指出，我再进行修改。

目录

修定记录 1

duplicate搭建rac到单实例adg（11g） 2

0、测试环境信息 2

1、主库归档 3

2、主库force logging模式开启 3

3、主库创建standby redo log 文件 3

4、备库创建目录 4

5、参数修改 5

6、创建备库参数文件及口令文件 5

7、主库监听相关创建 7

8、备库监听相关创建 7

9、rman复制数据库到备库，进行同步 8

10、同步验证 13

11、同步管理 13

12、同步验证日志查看 14

13、多种模式切换 15

14、主备切换 15

15、强制切换（会丢数据，极端环境操作） 16

16、快照数据库 16

16.1将物理备库切换为快照数据库 16

16.2将快照数据库切回物理备库 17

17、实施注意事项！！ 18

0、测试环境信息

主库基本环境

主库文件路径环境

主库hosts文件：

192.168.56.10 rac1
192.168.56.11 rac2
192.168.56.12 rac1-vip
192.168.56.13 rac2-vip
192.168.56.14 rac-scan
10.10.10.10 rac1-priv
10.10.10.11 rac2-priv
192.168.56.20 oracle

备库环境设计：

备库相关名称：
db_unique_name=orcldg
db_name=orcl
instance_name=orcldg

1、主库归档开启

在rac1节点执行

sqlplus / as sysdba
alter system set log_archive_dest_1=’location= data’;
exit

停库immediate

srvctl stop database -d orcl -o immediate;

启动rac1节点实例到mount模式

srvctl start database -d orcl -n rac1 -o mount

设置rac1节点数据库archivelog

sqlplus / as sysdba
alter database archivelog;
alter databae open;

启动rac2节点实例

srvctl start instance -d orcl -i orcl2 -o open

确认是否为归档开启

sqlplus / as sysdba
archive log list

2、主库force logging模式开启

在节点rac1执行，查看数据库的模式：

select log_mode,open_mode,protection_mode,database_role,switchover_status,guard_status,force_logging from v$database;

在节点rac1上执行：

alter database force logging;

3、主库创建standby redo log 文件

主库rac1节点sqlplus执行查看现有日志组情况

col member format a50;
select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;

每个 thread 都需要创建，standby redo log 比 redo log 多一组,大小相同

alter database add standby logfile thread 1 group 11 (' data') size 50m;
alter database add standby logfile thread 1 group 12 (' data') size 50m;
alter database add standby logfile thread 1 group 13 (' data') size 50m;
alter database add standby logfile thread 1 group 14 (' data') size 50m;
alter database add standby logfile thread 1 group 15 (' data') size 50m;
alter database add standby logfile thread 2 group 21 (' data') size 50m;
alter database add standby logfile thread 2 group 22 (' data') size 50m;
alter database add standby logfile thread 2 group 23 (' data') size 50m;
alter database add standby logfile thread 2 group 24 (' data') size 50m;
alter database add standby logfile thread 2 group 25 (' data') size 50m;

创建完查看日志组

select group#,type,member from v$logfile order by 2;

4、备库创建目录

[oracle@oracle ~]$ cd $oracle_base
[oracle@oracle oracle]$ mkdir -p oradata/orcldg/datafile
[oracle@oracle oracle]$ mkdir -p oradata/orcldg/onlinelog
[oracle@oracle oracle]$ mkdir -p oradata/orcldg/tempfile
[oracle@oracle oracle]$ mkdir -p oradata/orcldg/controlfile
[oracle@oracle oracle]$ mkdir -p oradata/orcldg/archivelog
[oracle@oracle oracle]$ cd $oracle_base
[oracle@oracle oracle]$ ls
cfgtoollogs checkpoints diag oradata product
[oracle@oracle oracle]$ mkdir -p admin/orcldg/adump
[oracle@oracle oracle]$ mkdir -p admin/orcldg/dpdump
[oracle@oracle oracle]$ mkdir -p admin/orcldg/hdump
[oracle@oracle oracle]$ mkdir -p admin/orcldg/pfile
[oracle@oracle oracle]$ mkdir -p /u01/app/oracle/fast_recover_area

5、参数修改

show parameter db_name

show parameter db_unique_name

db_name备库要与主库保持一致，

db_unique_name要有所区别，不能一样，

参数dg_config和log_archive_config中会用到db_unique_name

log_archive_dest_2里的service=后面跟的是服务名，就是写到tnsnames.ora里的

主库修改参数如下，在rac1节点执行

alter system set log_archive_config='dg_config=(orcl,orcldg)' sid='*';
alter system set log_archive_dest_1='location= data valid_for=(all_logfiles,all_roles) db_unique_name=orcl' sid='*';
alter system set log_archive_dest_2='service=orcldg async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='orcl' scope=both sid='*';
alter system set fal_server='orcldg' scope=both sid='*';
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg/datafile',' data/orcl/datafile','/u01/app/oracle/oradata/orcldg/tempfile',' data/orcl/tempfile' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg/onlinelog',' data/orcl/onlinelog' scope=spfile sid='*';

关闭数据库使参数生效

（这里关库重启主要是为了让db_file_name_convert和log_file_name_convert立即生效！这两个参数如果这里不配置也可以！等需要的时候再配置。如果配置了该参数没有立即重启库，以后可能会出现1个实例崩溃后无法正常启动，提示参数不致！！！！）

srvctl stop database -d orcl -o immediate;

启动数据库实例

srvctl start database -d orcl -o open;

6、创建备库参数文件及口令文件

在主库rac1节点执行，拷贝口令文件及参数文件到备库

[oracle@rac1 ~]$ cd $oracle_home/dbs
[oracle@rac1dbs]$scp orapworcl1 oracle@oracle:/u01/app/oracle/product/11.2.0/db_1/dbs

主库两个节点分别拷贝自己的口令文件到对方的dbs目录下

在备库重命名口令文件

[oracle@oracle admin]$ cd $oracle_home/dbs
[oracle@oracle dbs]$ ls
init.ora orapworcl1 pfileorcl.ora
[oracle@oracle dbs]$ mv orapworcl1 orapworcl

在备库创建参数文件，注意如下事项：

注意备库的参数文件中db_files:

参数要与生产的进行一下比较，要多于生产库的文件数量

注意db_file_name_convert参数:

如果原库的数据文件在多个路径下且有重名的数据文件情况下，此时注意修改该参数值不要在同一个文件夹下！！

修改后内容如下：

[oracle@oracle dbs]$ touch initorcldg.ora
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcldg/controlfile/control01.ctl','/u01/app/oracle/oradata/orcldg/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert=' data/orcl/datafile','/u01/app/oracle/oradata/orcldg/datafile',' data/orcl/tempfile','/u01/app/oracle/oradata/orcldg/tempfile'
*.db_files=1024
*.log_file_name_convert= ' data/orcl/onlinelog','/u01/app/oracle/oradata/orcldg/onlinelog'
*.db_name='orcl'
*.db_recovery_file_dest_size=6005194752
*.db_recovery_file_dest='/u01/app/oracle/fast_recover_area'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='orcldg'
*.fal_server='orcl'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
*.db_unique_name='orcldg'
*.service_names='orcldg'
*.undo_tablespace='undotbs1'
*.memory_target=300m

7、主库监听相关创建

tnsnames.ora文件

orcl =
(description =
(address = (protocol = tcp)(host = rac-scan)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
orcldg =
(description =
(address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = orcldg)
)
)

8、备库监听相关创建

listener.ora文件

listener =
(description_list =
(description =
(address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
)
)
sid_list_listener =
(sid_list =
(sid_desc =
(oracle_home = /u01/app/oracle/product/11.2.0/db_1)
(sid_name = orcldg)
)
)

tnsnames.ora文件

orcl =
(description =
(address = (protocol = tcp)(host = 192.168.56.10)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
orcldg =
(description =
(address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = orcldg)
)
)

9、rman复制数据库到备库，进行同步

备库操作，启动实例至nomount状态

[oracle@oracle dbs]$ cd ~
[oracle@oracle ~]$ echo $oracle_sid
orcldg
[oracle@oracle ~]$ sqlplus / as sysdba
sql*plus: release 11.2.0.4.0 production on fri jan 11 15:52:21 2019
米乐app官网下载 copyright (c) 1982, 2013, oracle. all rights reserved.
connected to an idle instance.
sql> create spfile from pfile;
file created.
sql> startup nomount;
oracle instance started.
total system global area 313159680 bytes
fixed size 2252824 bytes
variable size 222302184 bytes
database buffers 83886080 bytes
redo buffers 4718592 bytes

使用tnsping测试orcl及orcldg是否可达

rman target sys/oracle@orcl auxiliary sys/oracle@orcldg

复制数据库到备库，默认按如下执行没有并行，

duplicate target database for standby from active database nofilenamecheck;

如果需要打开并行多通道，需要按如下执行

run 
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

以下是不开关行的输出

[oracle@oracle admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcldg
recovery manager: release 11.2.0.4.0 - production on fri jan 11 17:08:33 2019
米乐app官网下载 copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved.
connected to target database: orcl (dbid=1525049921)
connected to auxiliary database: orcl (not mounted)
rman> duplicate target database for standby from active database nofilenamecheck;
starting duplicate db at 11-jan-19
using target database control file instead of recovery catalog
allocated channel: ora_aux_disk_1
channel ora_aux_disk_1: sid=19 device type=disk
contents of memory script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcldg' ;
}
executing memory script
starting backup at 11-jan-19
allocated channel: ora_disk_1
channel ora_disk_1: sid=73 instance=orcl1 device type=disk
finished backup at 11-jan-19
contents of memory script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcldg/controlfile/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orcldg/controlfile/control02.ctl' from
'/u01/app/oracle/oradata/orcldg/controlfile/control01.ctl';
}
executing memory script
starting backup at 11-jan-19
using channel ora_disk_1
channel ora_disk_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl1.f tag=tag20190111t170841 recid=2 stamp=997290522
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01
finished backup at 11-jan-19
starting restore at 11-jan-19
using channel ora_aux_disk_1
channel ora_aux_disk_1: copied control file copy
finished restore at 11-jan-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/orcldg/tempfile/temp.266.997269191";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211";
set newname for datafile 6 to
"/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211" datafile
6 auxiliary format
"/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893" ;
sql 'alter system archive log current';
}
executing memory script
executing command: set newname
renamed tempfile 1 to /u01/app/oracle/oradata/orcldg/tempfile/temp.266.997269191 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
starting backup at 11-jan-19
using channel ora_disk_1
channel ora_disk_1: starting datafile copy
input datafile file number=00001 name= data/orcl/datafile/system.256.997269101
output file name=/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:16
channel ora_disk_1: starting datafile copy
input datafile file number=00002 name= data/orcl/datafile/sysaux.257.997269101
output file name=/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:07
channel ora_disk_1: starting datafile copy
input datafile file number=00006 name= data/orcl/datafile/hr.273.997270893
output file name=/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:03
channel ora_disk_1: starting datafile copy
input datafile file number=00003 name= data/orcl/datafile/undotbs1.258.997269103
output file name=/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01
channel ora_disk_1: starting datafile copy
input datafile file number=00005 name= data/orcl/datafile/undotbs2.267.997269211
output file name=/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01
channel ora_disk_1: starting datafile copy
input datafile file number=00004 name= data/orcl/datafile/users.259.997269103
output file name=/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103 tag=tag20190111t170850
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01
finished backup at 11-jan-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=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101
datafile 2 switched to datafile copy
input datafile copy recid=3 stamp=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101
datafile 3 switched to datafile copy
input datafile copy recid=4 stamp=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103
datafile 4 switched to datafile copy
input datafile copy recid=5 stamp=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103
datafile 5 switched to datafile copy
input datafile copy recid=6 stamp=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211
datafile 6 switched to datafile copy
input datafile copy recid=7 stamp=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893
finished duplicate db at 11-jan-19
rman>

恢复完结果

---查看备库情况
select dbid,name,open_mode,database_role from v$database;
--mount模式启动同步（非adg）
alter database recover managed standby database disconnect from session;

10、同步验证

使用scott用户创建表测试，登录备库查看，同时观察告警日志跟踪

例如create table dept2023 as select * from dept;

11、同步管理

停止同步进程
alter database recover managed standby database cancel;
主备库延时同步
--先停止同步
alter database recover managed standby database cancel;
--启动延迟同步
alter database recover managed standby database delay 30 disconnect from session;
使用lgwr同步（adg实时）
--如果mount模式，要先open
alter database open
alter database recover managed standby database using current logfile disconnect from session;

12、同步验证日志查看

在主库上进行日志切换

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sequence#, first_time, next_time from v$archived_log order by sequence#;
alter system switch logfile;

在从库上执行如下语句查看日志是否同步切换到最新。

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

查看切换状态，是否有gap或目标不可达的情况发生

select switchover_status from v$database;

如果返回to standby或session active表示无问题。

13、多种模式切换

查看当前保护模式

select protection_mode from v$database;

多种模式如何切换

-- maximum availability.
alter system set log_archive_dest_2='service=orcldg affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
alter database set standby database to maximize availability;
-- maximum performance.
alter system set log_archive_dest_2='service=orcldg noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
alter database set standby database to maximize performance;
-- maximum protection.
alter system set log_archive_dest_2='service=orcldg affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
shutdown immediate;
startup mount;
alter database set standby database to maximize protection;
alter database open;

14、主备切换

a)在主库检查切换

select switchover_status from v$database;

如果该列值为"to standby"则表示primary 数据库支持转换为standby 角色，否则的话你就需要重新检查一下data guard 配置，比如看看log_archive_dest_n 之类参数值是否正确有效等等。

b)主库切换成备库并关闭

connect / as sysdba
alter database commit to switchover to standby;

建议直接执行下面这个，上面的有可能会话关不掉

alter database commit to switchover to physical standby with session shutdown;

关闭数据库

shutdown immediate;

c)备库切换成主库

在备库上执行

connect / as sysdba
alter database commit to switchover to primary;

建议直接执行下面这个

alter database commit to switchover to primary with session shutdown;
shutdown immediate;

再次打开变成主库

startup;

d)原来的主库以备库方式启动

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

15、强制切换（会丢数据，极端环境操作）

当主库不可用时，从库可以被激活成主库使用，需要在从库上执行如下sql语句。打开从库使用。

alter database recover managed standby database finish;
alter database activate standby database;
alter database open;

16、快照数据库

在dataguard中，可以将standby备库切换为snapshot快照数据库，在切换为snapshot数据库后，备库将置于可读写的模式。可用于模拟业务功能测试。在使用完成之后，可以将快照数据库切换为物理备库。在此期间，备库可以接受主库传输过来的日志，但是不能应用日志，需要处于物理备库的时候才可以应用。

16.1将物理备库切换为快照数据库

1.备库配置快速恢复区

alter system set db_recovery_file_dest_size=10g;
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recover_area';

2.备库关闭redo apply应用

--在关闭之前，备库的开启状态为：read only with apply

--关闭redo apply后，备库的开启状态为：read only

alter database recover managed standby database cancel;

3.切换为快照数据库

--执行转换为快照数据库后，备库的开启状态为：mounted

alter database convert to snapshot standby;

--开启备库后，数据库开启状态为：read write

alter database open;

查看状态：

sql> select open_mode,database_role,protection_mode,protection_level from v$database;
open_mode database_role protection_mode protection_level
-------------------- ---------------- -------------------- --------------------
read write snapshot standby maximum availability maximum availability

在快照数据库上进行读写操作,此时不仅可以读，还可以写入数据

sql> select * from scott.dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
sql> insert into scott.dept values(50,'secadmin','china');
1 row created.
sql> commit;
commit complete.
sql> select * from scott.dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
50 secadmin china

16.2将快照数据库切回物理备库

1.关闭备库，将备库启动到mount状态

shutdown immediate
start mount

2.执行切回命令

alter database convert to physical standby;

3.再次关闭备库，启动到open状态，开启实时应用

--关闭备库

shutdown immediate

--启动数据库到open状态

startup

--开始备库实时应用

alter database recover managed standby database using current logfile disconnect;

4.查看备库状态

sql> select name,open_mode,database_role,protection_mode from v$database;
name open_mode database_role protection_mode
--------- -------------------- ---------------- --------------------
adgdb read only with apply physical standby maximum availability

5.这个时候，再回去查看scott表信息，发现处于snapshot数据库时insert的数据已经不存在

sql> select * from scott.dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston

17、实施注意事项！！

• 主库和备库时间建议要一致，稳妥的情况是一定要部署ntp同步。
• 主库rac的两个节点都需要修改tnsnames.ora以保证传归档及redo可达
• 密码文件要一致，sys的密码要一致，最好校验一下密码文件的md5值，确认一模一样，如果主库修改了一定要及时更新密码文件及密码
• 主备库环境变量，一定要仔细核对，oracle_base与oracle_home别有出错，例如oracle_base的值最后加/，设定oracle_home变量时又引用oracle_base多加个/，会引起启动的实例监听无法连接。
• 
  

1. 也欢迎关注我的公众号【徐sir的it之路】，一起学习！
   ————————————————————————————
   公众号：徐sir的it之路
   csdn ：https://blog.csdn.net/xxddxhyz?type=blog
   墨天轮：https://www.modb.pro/u/3605
   pgfans：https://www.pgfans.cn/user/home?userid=5568
   ————————————————————————————