最佳实践：11GRAC到11G单实例物理DG配置参考v1.0

主库基本环境

主库文件路径环境

主库HOSTS文件：

 备库环境设计：

 /u01/app/oracle/oradata/orcldg/datafile                    #备库数据文件存放路径

 /u01/app/oracle/oradata/orcldg/onlinelog                   #备库联机日志文件文件存放路径

 /u01/app/oracle/oradata/orcldg/tempfile                    #备库临时表空间文件存放路径

 /u01/app/oracle/oradata/orcldg/controlfile                   #备库控制文件存放路径

 /u01/app/oracle/oradata/orcldg/archivelog                   #备库归档文件存放路径

 /u01/app/oracle/admin/orcldg/adump                       #备库相关日志存放路径

 /u01/app/oracle/admin/orcldg/dpdump                     #备库相关日志存放路径

 /u01/app/oracle/admin/orcldg/hdump                      #备库相关日志存放路径

 /u01/app/oracle/admin/orcldg/pfile                         #备库相关日志存放路径

 /u01/app/oracle/fast_recover_area                          #备库闪回空间放路径

备库名称：

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 –omount

设置RAC1节点数据库archivelog

sqlplus as sysdba

alter database archivelog;

alter databae open;

启动RAC2节点实例

srvctl start instance –d orcl –I orcl2 –oopen

确认是否为归档开启

sqlplus as sysdba

archive log list

2、主库force logging模式开启

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

SelectOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,GUARD_STATUS,FORCE_LOGGINGfrom v$database;

在节点RAC1上执行：

SQL> alter database force logging;

3、主库创建standby redo log文件

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

col member format a50;

selecta.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b wherea.group#=b.group#;

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

alter database add standby logfile thread 1group 11 ('+DATA') size 50m;

alter database add standby logfile thread 1group 12 ('+DATA') size 50m;

alter database add standby logfile thread 1group 13 ('+DATA') size 50m;

alter database add standby logfile thread 2group 14 ('+DATA') size 50m;

alter database add standby logfile thread 2group 15 ('+DATA') size 50m;

alter database add standby logfile thread 2group 16 ('+DATA') size 50m;

创建完查看日志组

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

4、备库创建目录

[oracle@oracle ~]$ cd $ORACLE_BASE

[oracle@oracle oracle]$ mkdir -poradata/orcldg/datafile

[oracle@oracle oracle]$ mkdir -poradata/orcldg/onlinelog

[oracle@oracle oracle]$ mkdir -poradata/orcldg/tempfile

[oracle@oracle oracle]$ mkdir -poradata/orcldg/controlfile

[oracle@oracle oracle]$ mkdir -poradata/orcldg/archivelog

[oracle@oracle oracle]$ cd$ORACLE_BASE

[oracle@oracle oracle]$ ls

cfgtoollogs  checkpoints diag  oradata  product

[oracle@oracle oracle]$ mkdir -padmin/orcldg/adump

[oracle@oracle oracle]$ mkdir -padmin/orcldg/dpdump

[oracle@oracle oracle]$ mkdir -padmin/orcldg/hdump

[oracle@oracle oracle]$ mkdir -padmin/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要有所区别，DB_UNIQUE_NAME需要用到DG_CONFIG和LOG_ARCHIVE_CONFIG中，

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

alter system setlog_archive_cOnfig='dg_cOnfig=(orcl,orcldg)' sid='*';

alter system setlog_archive_dest_1='location=+DATA valid_for=(all_logfiles,all_roles) db_unique_name=orcl'sid='*';

alter system setlog_archive_dest_2='service=orcldg lgwr async noaffirmvalid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=spfilesid='*';

alter system setstandby_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 setlog_archive_max_processes=10 scope=both;

alter system set db_file_name_cOnvert='+DATA/orcl/datafile','/u01/app/oracle/oradata/orcldg/datafile','+DATA/orcl/tempfile','/u01/app/oracle/oradata/orcldg/tempfile'scope=spfile sid='*';

alter system setlog_file_name_cOnvert='+DATA/orcl/onlinelog','/u01/app/oracle/oradata/orcldg/onlinelog'scope=spfile sid='*';

关闭数据库使参数生效

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

在备库重命名口令文件

[oracle@oracle admin]$ cd$ORACLE_HOME/dbs

[oracle@oracle dbs]$ ls

init.ora  orapworcl1 pfileorcl.ora

[oracle@oracle dbs]$ mv orapworcl1orapworcl

在备库创建参数文件，修改后内容如下：

[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'

*.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/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

*.log_archive_dest_2='service=orclLGWR 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 assysdba

SQL*Plus: Release 11.2.0.4.0Production on Fri Jan 11 15:52:21 2019

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                  222302184bytes

Database Buffers           83886080 bytes

Redo Buffers                    4718592bytes

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

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

复制数据库到备库

duplicate target database for standbyfrom active database nofilenamecheck;

以下是输出

[oracle@oracle admin]$ rman targetsys/oracle@orcl auxiliary sys/oracle@orcldg

Recovery Manager: Release 11.2.0.4.0- Production on Fri Jan 11 17:08:33 2019

Copyright (c) 1982, 2011, Oracleand/or its affiliates.  All rightsreserved.

connected to target database: ORCL(DBID=1525049921)

connected to auxiliary database: ORCL(not mounted)

RMAN> duplicate target databasefor standby from active database nofilenamecheck;

Starting Duplicate Db at 11-JAN-19

using target database control fileinstead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 devicetype=DISK

contents of Memory Script:

{

  backup as copy reuse

  targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' auxiliaryformat

 '/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=orcl1device 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 datafilecopy

copying standby control file

output filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl1.ftag=TAG20190111T170841 RECID=2 STAMP=997290522

channel ORA_DISK_1: datafile copycomplete, 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: copiedcontrol 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 mountstandby 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 datafilecopy

input datafile file number=00001name=+DATA/orcl/datafile/system.256.997269101

output filename=/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:16

channel ORA_DISK_1: starting datafilecopy

input datafile file number=00002name=+DATA/orcl/datafile/sysaux.257.997269101

output filename=/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafilecopy

input datafile file number=00006name=+DATA/orcl/datafile/hr.273.997270893

output filename=/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafilecopy

input datafile file number=00003name=+DATA/orcl/datafile/undotbs1.258.997269103

output filename=/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafilecopy

input datafile file number=00005name=+DATA/orcl/datafile/undotbs2.267.997269211

output filename=/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafilecopy

input datafile file number=00004name=+DATA/orcl/datafile/users.259.997269103

output filename=/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103tag=TAG20190111T170850

channel ORA_DISK_1: datafile copycomplete, elapsed time: 00:00:01

Finished backup at 11-JAN-19

sql statement: alter system archivelog current

contents of Memory Script:

{

  switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2STAMP=997290566 filename=/u01/app/oracle/oradata/orcldg/datafile/system.256.997269101

datafile 2 switched to datafile copy

input datafile copy RECID=3STAMP=997290566 filename=/u01/app/oracle/oradata/orcldg/datafile/sysaux.257.997269101

datafile 3 switched to datafile copy

input datafile copy RECID=4STAMP=997290566 filename=/u01/app/oracle/oradata/orcldg/datafile/undotbs1.258.997269103

datafile 4 switched to datafile copy

input datafile copy RECID=5STAMP=997290566 file name=/u01/app/oracle/oradata/orcldg/datafile/users.259.997269103

datafile 5 switched to datafile copy

input datafile copy RECID=6STAMP=997290566 filename=/u01/app/oracle/oradata/orcldg/datafile/undotbs2.267.997269211

datafile 6 switched to datafile copy

input datafile copy RECID=7STAMP=997290566 filename=/u01/app/oracle/oradata/orcldg/datafile/hr.273.997270893

Finished Duplicate Db at 11-JAN-19

RMAN>

恢复完结果

selectDBID,NAME,OPEN_MODE,DATABASE_ROLE from v$database;

alter database recover managedstandby database disconnect from session;          #启用DATAGUARD，不实时应用数据

10、同步验证

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

11、同步管理

停止同步进程

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE CANCEL;

主备库延时同步

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBYDATABASE DELAY 30 DISCONNECT FROM SESSION;

使用LGWR同步（实时应用日志）

alter database recover managedstandby database using current logfile disconnect from session;

12、同步验证日志查看

在主库上进行日志切换

ALTER SESSION SETnls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time,next_time FROM v$archived_log  ORDER BYsequence#;

ALTER SYSTEM SWITCH LOGFILE;

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

ALTER SESSION SETnls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time,next_time, applied FROM v$archived_log  ORDERBY sequence#;

13、多种模式切换

查看当前保护模式

SELECT protection_mode FROMv$database;

多种模式如何切换

-- Maximum Availability.

ALTER SYSTEM SETLOG_ARCHIVE_DEST_2='SERVICE=orcldg AFFIRM SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';

ALTER DATABASE SET STANDBY DATABASETO MAXIMIZE AVAILABILITY;

-- Maximum Performance.

ALTER SYSTEM SETLOG_ARCHIVE_DEST_2='SERVICE=orcldg NOAFFIRM ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';

ALTER DATABASE SET STANDBY DATABASE TOMAXIMIZE PERFORMANCE;

-- Maximum Protection.

ALTER SYSTEM SETLOG_ARCHIVE_DEST_2='SERVICE=orcldg AFFIRM SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE SET STANDBY DATABASETO MAXIMIZE PROTECTION;

ALTER DATABASE OPEN;

14、主备切换

-- Convert primary database tostandby

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVERTO STANDBY;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database asstandby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBYDATABASE;

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE DISCONNECT FROM SESSION;

在备库上执行

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

15、强制切换

当主库不可用时，从库可以被激活成主库使用，注意数据库运行的保护模式，如果是最大保护模式的情况下是不会有数据丢失，如果是最大性能模式执行该切换可能会丢失数据！！

需要在从库上执行如下SQL语句。打开从库使用。

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBYDATABASE;

Alter database open;

16、实施注意事项！！

1、主库和备库时间一定要一致，稳妥的情部是一定要部署NTP同步。

2、主库RAC的两个节点都需要修改tnsnames.ora以保证传归档及redo可达

3、密码文件要一致，sys的密码要一致，如果主库修改了一定要及时更新密码文件及密码

4、12c-RAC环境密码文件默认存放在ASM里，需要手动创建一个传到备库

5、其中参数文件写的参数是按照旧版本10G的参数写的，也可以支持11G、12C的环境。按照官方文档可以简写。