【DATAGUARD】DG系列之RACtoONE物理备库的搭建
说明：Oracle11.2.0.4 RAC/RedHat6.3_x64 à Oracle11.2.0.4/Oracle Linux 6.6
主数据库：racdb 备库：phydb
1、 网络配置：
主库是通过SCAN IP进行网络访问，顾可不做相关监听配置，下面为备库静态监听配置文件：
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = phydb) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0) (SID_NAME = phydb) ) ) |
注意：为了确保能够在Oracle Data Guard配置中的任意节点上，发生角色转换或者故障转移后恢复原来的状态，必须为每个数据库实例在各自的节点上创建一个静态监听器项。详情可参考《【DATAGUARD】DG系列之DG代理（broker)的搭建和应用》，后边文章会说明相关配置。
2、 配置TNS，每个节点都需配置，本次测试主要针对RAC To ONE，暂不考虑RAC to RAC相关方面问题：
RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (SERVICE_NAME = racdb) ) ) PHYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oeloracle)(PORT = 1521)) (SERVICE_NAME = phydb) ) ) |
3、 修改数据库为归档模式，打开强制日志记录
--归档不做相关说明，下面是打开强制日志记录 SQL> alter database force logging; |
4、 备库创建密码文件
#orapwd file=$ORACLE_HOME/dbs/orapwphydb password=oracle
5、 拷贝参数文件，修改，并启动
create pfile=’/tmp/initphydb.ora’ from spfile; --启动数据库到nomount模式 |
6、 修改初始化参数，修改完成后重启各实例
主库参数：
alter system set db_unique_name='racdb' scope=spfile sid='*'; alter system set standby_file_management=AUTO scope=both; alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*'; alter system set db_file_name_convert='/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile' scope=spfile sid='*'; alter system set log_file_name_convert='/oracle/app/oracle/archivelog_phy','+DATA/racdb/archivelog' scope=spfile sid='*'; alter system set log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB' scope=spfile sid='*'; alter system set log_archive_dest_2='SERVICE=phydb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phydb' scope=spfile sid='*'; alter system set log_archive_dest_state_2=enable scope=both sid='*'; alter system set fal_server='phydb' scope=spfile sid='*'; |
备库参数：
alter system set db_unique_name='phydb' scope=spfile sid='*'; alter system set standby_file_management=AUTO scope=both; alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*'; alter system set db_file_name_convert='+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile','/oracle/app/oracle/oradata/PHYDB' scope=spfile sid='*'; alter system set log_file_name_convert='+DATA/racdb/archivelog','/oracle/app/oracle/archivelog_phy' scope=spfile sid='*'; alter system set log_archive_dest_1='location=/oracle/app/oracle/archivelog_phy VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phydb' scope=spfile sid='*'; alter system set log_archive_dest_2='SERVICE=racdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb' scope=spfile sid='*'; alter system set log_archive_dest_state_2=enable scope=both; alter system set fal_server='racdb' scope=spfile sid='*'; |
7、 创建备用重做日志，在创建备用重做日志时，必须使它们的大小与在线重做日志相同，2为每个线程创建一个额外的备用重做日志组。
alter database add standby logfile thread 1 group 7 '+DATA' size 200m; alter database add standby logfile thread 2 group 8 '+DATA' size 200m; alter database add standby logfile thread 1 group 9 '+DATA' size 200m; alter database add standby logfile thread 2 group 10 '+DATA' size 200m; alter database add standby logfile thread 1 group 11 '+DATA' size 200m; alter database add standby logfile thread 2 group 12 '+DATA' size 200m; |
8、 使用duplicate属性创建备用数据库
[oracle@rac01 dbs]$ rman target sys/oracle@racdb auxiliary sys/oracle@phydb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 2 09:19:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=857055310) connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 2015-07-02 09:19:59 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=192 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format '/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ; } executing Memory Script
Starting backup at 2015-07-02 09:20:00 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 instance=racdb1 device type=DISK Finished backup at 2015-07-02 09:20:01
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oracle/app/oracle/oradata/PHYDB/control01.dbf'; restore clone controlfile to '/oracle/app/oracle/oradata/PHYDB/control02.dbf' from '/oracle/app/oracle/oradata/PHYDB/control01.dbf'; } executing Memory Script
Starting backup at 2015-07-02 09:20:01 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/oracle/app/oracle/product/11.2.0/dbs/snapcf_racdb1.f tag=TAG20150702T092001 RECID=7 STAMP=883992006 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 2015-07-02 09:20:08
Starting restore at 2015-07-02 09:20:08 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy Finished restore at 2015-07-02 09:20: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 "/oracle/app/oracle/oradata/PHYDB/temp.286.859438605"; switch clone tempfile all; set newname for datafile 1 to "/oracle/app/oracle/oradata/PHYDB/system.293.859438573"; set newname for datafile 2 to "/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589"; set newname for datafile 3 to "/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601"; set newname for datafile 4 to "/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615"; set newname for datafile 5 to "/oracle/app/oracle/oradata/PHYDB/users.278.859438619"; set newname for datafile 6 to "/oracle/app/oracle/oradata/PHYDB/test1.261.859451689"; set newname for datafile 7 to "/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697"; set newname for datafile 8 to "/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749"; set newname for datafile 11 to "/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf"; backup as copy reuse datafile 1 auxiliary format "/oracle/app/oracle/oradata/PHYDB/system.293.859438573" datafile 2 auxiliary format "/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589" datafile 3 auxiliary format "/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601" datafile 4 auxiliary format "/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615" datafile 5 auxiliary format "/oracle/app/oracle/oradata/PHYDB/users.278.859438619" datafile 6 auxiliary format "/oracle/app/oracle/oradata/PHYDB/test1.261.859451689" datafile 7 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697" datafile 8 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749" datafile 11 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/PHYDB/temp.286.859438605 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2015-07-02 09:20:16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.287.859438601 output file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/racdb/datafile/system.293.859438573 output file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.288.859438589 output file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/racdb/datafile/undotbs2.285.859438615 output file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DATA/racdb/datafile/test1.261.859451689 output file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=+DATA/racdb/datafile/firsoul.263.882093749 output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+DATA/racdb/datafile/firsoul.259.882071697 output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=+DATA/racdb/datafile/firsoul01.dbf output file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/racdb/datafile/users.278.859438619 output file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2015-07-02 09:23:14
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=7 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573 datafile 2 switched to datafile copy input datafile copy RECID=8 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589 datafile 3 switched to datafile copy input datafile copy RECID=9 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601 datafile 4 switched to datafile copy input datafile copy RECID=10 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615 datafile 5 switched to datafile copy input datafile copy RECID=11 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619 datafile 6 switched to datafile copy input datafile copy RECID=12 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689 datafile 7 switched to datafile copy input datafile copy RECID=13 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697 datafile 8 switched to datafile copy input datafile copy RECID=14 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749 datafile 11 switched to datafile copy input datafile copy RECID=15 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf Finished Duplicate Db at 2015-07-02 09:24:27 |
9、 在执行复制过程中，容易出现以下错误，主要还是针对密码文件，为了避免出现错误，建议使用scp或者其他命令将主库密码文件拷贝过去直接使用。
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 2015-07-02 09:17:42 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=192 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format '/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ; } executing Memory Script
Starting backup at 2015-07-02 09:17:44 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=55 instance=racdb2 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/02/2015 09:17:45 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/02/2015 09:17:45 ORA-19505: failed to identify file "/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
10、 只读方式打开数据库，这里忽略了read only，Oracle通过读取控制文件来确定它是否是备用数据库，这里它将自动置于只读模式。
SQL> alter database open; |
11、 启用实时查询，只读方式打开后，重启管理恢复，至此，Oracle ADG搭建完成：
SQL>alter database recover managed standby database using current logfile disconnect; |
12、 通过创建表空间、表等操作，查看adg实时性，测试均正常。
13、 下面几条adg管理命令
--把MRP作为前台进程启动，MRP进程也是启动会话的一部分，如果退出会话，MRP进程也就退出了。 --把MRP作为后台进程启动 --让日志实时应用 --推迟10分钟启动MRP alter database recover managed standby database delay 120 disconnect; --立即让备库与主库同步 alter database recover managed standby database nodelay; --停止MRP alter database recover managed standby database cancel; |
14、 主库和备库之间角色切换
1、主库切换为备库 alter database commit to switchover to physical standby; alter database commit to switchover to physical standby with session shutdown; shutdown immediate startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session;
2、 从库切换为主库 alter database commit to switchover to primary; shutdown immediate; startup |
15、 监视备用数据库
--查看dg整理情况 Select protection_mode,protection_level,database_role from v$database; --查看是否使用实时应用 select dest_id,recovery_mode from v$archive_dest_status; select name,value,time_computed from v$datagurad_stats; |
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞1
添加新评论1 条评论
2015-08-06 23:58