0、测试环境信息
主库基本环境
主机 | CPU | 内存 | ORACLE_HOME | 实例名 | Db_name | db_unique_name |
RAC1 | 1 | 4 | /u01/app/oracle/product/11.2.0/dbhome_1 | orcl1 | orcl | orcl |
RAC2 | 1 | 4 | /u01/app/oracle/product/11.2.0/dbhome_1 | orcl2 | orcl | orcl |
主库文件路径环境
主机 | 数据文件路径 | 控制文件 | 归档日志文件 |
RAC1 | +DATA/orcl/datafile +DATA/orcl/tempfile | +DATA/orcl/controlfile | +DATA/orcl/archivelog |
RAC2 |
主库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的环境。按照官方文档可以简写。
本文分享自微信公众号 - IT攻城狮的学习笔记，如有侵权，请联系 service001@enmotech.com 删除。
