【12c】DataGuard物理备用数据库（DUPLICATE方式）搭建详细教程
1 软件环境
- Oracle Linux 6.9
- Oracle 12.1.0.2
- 主库：sz.oracle.com，IP：192.168.1.102
- 备库：sh.oracle.com，IP：192.168.1.103
2 主数据库配置
2.1 检查并设置数据库
1）首先，主数据库必须处于强制日志（Force Logging）模式下运行，这是要防止主数据库中存在直接的数据修改而不记录日志的行为。
检查强制日志模式如下：
SQL> select dbid,name,force_logging from v$database;DBID NAME FORCE_LOGGING---------- --------- ---------------------------------------1565839253 ORCL NO启动强制日志模式如下：
SQL> alter database force logging;Database altered.取消强制日志模式如下：
SQL> alter database no force logging;Database altered.2）其次，检查主数据库的日志运行模式，Data Guard要求主数据库必须在归档模式下运行，此模式下数据库可以连续完整的保存事务日志。
SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 11Current log sequence 13如果数据库处在非归档模式，则需要做如下调整：
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 1241513984 bytesFixed Size 2923872 bytesVariable Size 452985504 bytesDatabase Buffers 771751936 bytesRedo Buffers 13852672 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 11Next log sequence to archive 13Current log sequence 133）最后，要检查数据库实例的口令文件，Data Guard环境下的每个数据库必须强制使用口令文件，并且备用数据库口令文件中SYS用户的口令必须和主数据库SYS用户的口令完全一致。若没有，使用orapwd命令创建口令文件。
[oracle@sz ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl-rw-r----- 1 oracle oinstall 7680 Apr 18 13:41 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl2.2 设置必要的主数据库参数
1）主备库架构
按照如下所示的内容构建Data Guard环境，假设一个主库在深圳，备库在上海。
| DG ROLE | DB_NAME | INSTANCE_NAME | DB_UNIQUE_NAME | TNS_NAME |
| 主库 | orcl | orcl | orcl | shzhen |
| 备库 | orcl | orcldg | orcldg | shhai |
2）主库配置参数
orcl.__data_transfer_cache_size=0orcl.__db_cache_size=687865856orcl.__java_pool_size=16777216orcl.__large_pool_size=33554432orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=671088640orcl.__sga_target=973078528orcl.__shared_io_pool_size=0orcl.__shared_pool_size=218103808orcl.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=4560m*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.local_listener='LISTENER_ORCL'*.log_archive_format='%t_%s_%r.dbf'*.memory_target=1560m*.open_cursors=300*.processes=300*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'#############################The following is adding for DG by Alen Liu 2020-04-19####################################DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DESTVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcl'LOG_ARCHIVE_DEST_2='SERVICE=shhai LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orcldg'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESTANDBY_FILE_MANAGEMENT=AUTOFAL_SERVER=orcldgFAL_CLIENT=orclDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','/u01/app/oracle/oradata/orcl'LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','/u01/app/oracle/oradata/orcl'#############################The following is adding for DG by Alen Liu 2020-04-19####################################3）设置网络配置
[oracle@sz admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.SHHAI =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcldg)))LISTENER_ORCL =(ADDRESS = (PROTOCOL = TCP)(HOST = sz.oracle.com)(PORT = 1521))SHZHEN =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))ORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sz.oracle.com)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))3 备用数据库配置
3.1 配置密码文件
将主库的密码文件拷贝至备库，并修改名称。
1）主库传送文件
[oracle@sz dbs]$ scp orapworcl oracle@192.168.56.103:$ORACLE_HOME/dbs/orapworcldgoracle@192.168.56.103's password:orapworcl 100% 7680 7.5KB/s 00:002）备库检查文件
[oracle@sh ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcldg-rw-r----- 1 oracle oinstall 7680 Apr 19 17:28 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcldg3.2 设置网络配置
将主库的设置好的网络文件拷贝至备库。
1）主库传送文件
[oracle@sz admin]$ scp tnsnames.ora oracle@192.168.56.103:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/oracle@192.168.56.103's password:tnsnames.ora 100% 809 0.8KB/s 00:002）备库检查文件
[oracle@sh ~]$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.SHHAI =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcldg)))LISTENER_ORCL =(ADDRESS = (PROTOCOL = TCP)(HOST = sh.oracle.com)(PORT = 1521))SHZHEN =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))ORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sz.oracle.com)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))3.3 设置初始化参数文件
1）将主库的pfile传送至备库，并做适当调整。
[oracle@sz dbs]$ scp initorcl.ora oracle@192.168.56.103:$ORACLE_HOME/dbs/initorcldg.oraThe authenticity of host '192.168.56.103 (192.168.56.103)' can't be established.RSA key fingerprint is bd:22:31:30:20:8f:b1:12:3a:0c:dd:db:5b:f0:8f:12.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.168.56.103' (RSA) to the list of known hosts.oracle@192.168.56.103's password:initorcl.ora 100% 1869 1.8KB/s 00:002）检查备库文件
[oracle@sh dbs]$ cat initorcldg.oraorcl.__data_transfer_cache_size=0orcl.__db_cache_size=687865856orcl.__java_pool_size=16777216orcl.__large_pool_size=33554432orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=671088640orcl.__sga_target=973078528orcl.__shared_io_pool_size=0orcl.__shared_pool_size=218103808orcl.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=4560m*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.local_listener='LISTENER_ORCL'*.log_archive_format='%t_%s_%r.dbf'*.memory_target=1560m*.open_cursors=300*.processes=300*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'#############################The following is adding for DG by Alen Liu 2017-09-11####################################DB_UNIQUE_NAME=orcldgLOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DESTVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcldg'LOG_ARCHIVE_DEST_2='SERVICE=shzhen LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orcl'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESTANDBY_FILE_MANAGEMENT=AUTOFAL_SERVER=orclFAL_CLIENT=orcldgDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg'LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg'#############################The following is adding for DG by Alen Liu 2017-09-11####################################4 Data Guard搭建
4.1 主库使用新创建的配置文件
SQL> startupORACLE instance started.Total System Global Area 1644167168 bytesFixed Size 2925024 bytesVariable Size 1056968224 bytesDatabase Buffers 570425344 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened.SQL> create spfile from pfile;File created.4.2 备库启动至NOMOUNT状态
SQL> startup nomountORACLE instance started.Total System Global Area 1644167168 bytesFixed Size 2925024 bytesVariable Size 1056968224 bytesDatabase Buffers 570425344 bytesRedo Buffers 13848576 bytesSQL> create spfile from pfile;File created.4.3 启动DUPLICATE数据库复制
[oracle@sh ~]$ rman target sys/system@shzhen auxiliary sys/system@shhaiRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 19 17:58:55 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;Starting Duplicate Db at 19-APR-20using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=23 device type=DISKcurrent log archivedcontents of Memory Script:{backup as copy reusetargetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl' auxiliary format'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcldg' ;}executing Memory ScriptStarting backup at 19-APR-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=46 device type=DISKFinished backup at 19-APR-20contents of Memory Script:{sql clone "create spfile from memory";shutdown clone immediate;startup clone nomount;restore clone from service 'shzhen' standby controlfile;}executing Memory Scriptsql statement: create spfile from memoryOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 1644167168 bytesFixed Size 2925024 bytesVariable Size 1073745440 bytesDatabase Buffers 553648128 bytesRedo Buffers 13848576 bytesStarting restore at 19-APR-20allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=22 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/fast_recovery_area/ORCLDG/controlfile/o1_mf_h9r8963y_.ctlFinished restore at 19-APR-20contents of Memory Script:{sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{set newname for tempfile 1 to"/u01/app/oracle/oradata/orcldg/temp01.dbf";switch clone tempfile all;set newname for datafile 1 to"/u01/app/oracle/oradata/orcldg/system01.dbf";set newname for datafile 3 to"/u01/app/oracle/oradata/orcldg/sysaux01.dbf";set newname for datafile 4 to"/u01/app/oracle/oradata/orcldg/undotbs01.dbf";set newname for datafile 5 to"/u01/app/oracle/oradata/orcldg/example01.dbf";set newname for datafile 6 to"/u01/app/oracle/oradata/orcldg/users01.dbf";restorefrom service 'shzhen' clone database;sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/orcldg/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 19-APR-20using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcldg/system01.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcldg/sysaux01.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcldg/undotbs01.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcldg/example01.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcldg/users01.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 19-APR-20sql statement: alter system archive log currentcurrent log archivedcontents of Memory Script:{restore clone force from service 'shzhen'archivelog from scn 1792397;switch clone datafile all;}executing Memory ScriptStarting restore at 19-APR-20using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting archived log restore to default destinationchannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: restoring archived logarchived log thread=1 sequence=12channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_AUX_DISK_1: starting archived log restore to default destinationchannel ORA_AUX_DISK_1: using network backup set from service shzhenchannel ORA_AUX_DISK_1: restoring archived logarchived log thread=1 sequence=13channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 19-APR-20datafile 1 switched to datafile copyinput datafile copy RECID=1 STAMP=1038160949 file name=/u01/app/oracle/oradata/orcldg/system01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=1038160949 file name=/u01/app/oracle/oradata/orcldg/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=1038160949 file name=/u01/app/oracle/oradata/orcldg/undotbs01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=4 STAMP=1038160949 file name=/u01/app/oracle/oradata/orcldg/example01.dbfdatafile 6 switched to datafile copyinput datafile copy RECID=5 STAMP=1038160949 file name=/u01/app/oracle/oradata/orcldg/users01.dbfcontents of Memory Script:{set until scn 1793777;recoverstandbyclone databasedelete archivelog;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 19-APR-20using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_12_h9r8fm03_.arcarchived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_13_h9r8fn6x_.arcarchived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_12_h9r8fm03_.arc thread=1 sequence=12archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_13_h9r8fn6x_.arc thread=1 sequence=13media recovery complete, elapsed time: 00:00:01Finished recover at 19-APR-20Finished Duplicate Db at 19-APR-204.4 启动备库，并查看其状态
SQL> alter database open;Database altered.SQL> select name,open_mode,protection_mode,database_role from v$database;NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE--------- -------------------- -------------------- ----------------ORCL READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY4.5 备库启动Redo应用
SQL> alter database recover managed standby database disconnect from session;Database altered.4.6 主库创建测试数据
SQL> create table scott.test(id number,name varchar2(100));Table created.SQL> insert into scott.test values(1,'Alen');1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.4.7 备库验证
SQL> select * from scott.test;ID NAME---------- ----------------------------------------------------------------------------------------------------1 Alen告警日志：
alter database recover managed standby database disconnect from sessionSun Apr 19 18:15:44 2020WARNING: There are no standby redo logs.Standby redo logs should be configured for real time apply. Real time apply will be ignored.Sun Apr 19 18:15:44 2020Attempt to start background Managed Standby Recovery process (orcldg)Starting background process MRP0Sun Apr 19 18:15:44 2020MRP0 started with pid=36, OS id=812Sun Apr 19 18:15:44 2020MRP0: Background Managed Standby Recovery process started (orcldg)Sun Apr 19 18:15:49 2020Serial Media Recovery startedManaged Standby Recovery not using Real Time ApplySun Apr 19 18:15:49 2020Waiting for all non-current ORLs to be archived...Sun Apr 19 18:15:49 2020All non-current ORLs have been archived.Sun Apr 19 18:15:49 2020Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_14_h9r8pmo1_.arcMedia Recovery Waiting for thread 1 sequence 15 (in transit)Completed: alter database recover managed standby database disconnect from sessionSun Apr 19 18:16:33 2020Archived Log entry 4 added for thread 1 sequence 15 rlc 1038157520 ID 0x5d570e4d dest 2:RFS[1]: No standby redo logfiles createdRFS[1]: Opened log for thread 1 sequence 16 dbid 1565938253 branch 1038157520Sun Apr 19 18:16:34 2020Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2020_04_19/o1_mf_1_15_h9r8rz29_.arcResize operation completed for file# 3, old size 675840K, new size 706560KMedia Recovery Waiting for thread 1 sequence 16 (in transit)显示没有备库standby redolog，创建即可。
WARNING: There are no standby redo logs.
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;Database altered.SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;Database altered.SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;Database altered.SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;Database altered.至此，基于DUPLICATE构建物理备用数据库完成。
No comments:
Post a Comment