oracle 11g 主从复制技术ADG实施手册主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是实时的业务数据库,从数据库的作用和使用场合一般有几个:一是作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作;二是可在从数据库作备份、数据统计等工作,这样不影响主数据库的性能,三、实现读写分离; 一、环境介绍操作系统版本:RHEL LINUX 6.7(64位) 数据库版本:ORACLE 11GR2 11.2.0.4(64位) IP地址规划: 主数据库 192.168.11.12 SID:pri db_name:pri db_unique_name:pri 主库主机名:ora11g 备份数据库 192.168.11.13 SID:std db_name:pri db_unique_name:std 备库主机名:ora11g-dg 版本统一、数据库统一、防火墙关闭 二、安装oracle此安装省略(参考:https://www./database/201712/702060.html) 主库在安装完软件后需要进行DBCA建库操作,推荐不要启用归档模式,会节省时间,备库端不要建库!可以开启监听程序 三、开始搭建Dataguard1:将数据库改为强制日志模式 (此步骤只在主库上做) 查看当前是否强制日志模式: SYS@pri> select name,log_mode,force_logging from v$database; NAME LOG_MODE FOR ——— ———— — PRI NOARCHIVELOG NO SYS@pri> alter database force logging; Database altered. SYS@pri> select name,log_mode,force_logging from v$database; NAME LOG_MODE FOR ——— ———— — PRI NOARCHIVELOG YES 2:创建密码文件 (此步骤只在主库上做,一般数据库都) 将主库的密码文件copy给备库,并重命名 [oracle@pri dbs]scporapwpri192.168.11.13: 3:创建standby redolog日志组 (此步骤只在主库上做) 原则: 1:standby redo log的文件大小与primary 数据库online redo log 文件大小相同 2:standby redo log日志文件组的个数依照下面的原则进行计算: Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数 假如只有一个节点,这个节点有三组redolog, 所以Standby redo log组数>=(3+1)*1 == 4 所以至少需要创建4组Standby redo log 由于已经存在group1-3,,所以group号只能从4开始 SYS@pri> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/standbylog/std_redo04.log’ size 50m; alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/standbylog/std_redo05.log’ size 50m; alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/standbylog/std_redo06.log’ size 50m; alter database add standby logfile group 7 ‘/u01/app/oracle/oradata/standbylog/std_redo07.log’ size 50m; 4:修改主库的pfile参数文件 (此步骤只在主库上做) 用spfile创建一个pfile,用于修改: SYS@pri> create pfile from spfile; 修改主库的pfile: 以下内容是需要新增加的: *.db_unique_name=’pri’ DG主库和备库的db_name必须一致,db_unique_name不一致 *.log_archive_config=’dg_config=(pri,std)’ pri主数据库SID,std备份数据库SID *.log_archive_dest_1=’location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri’ 主数据库的归档日志路径和SID *.log_archive_dest_2=’service=std valid_for=(online_logfiles,primary_role) db_unique_name=std’ 备份数据库的SID *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server=’std’ 备份数据库的SID *.fal_client=’pri’ 主数据库的SID *.db_file_name_convert=’/u01/app/oracle/oradata/std’,’/u01/app/oracle/oradata/pri’ 第一个目录是备份数据库数据文件路径(备份服务器上有此目录),第二个是主数据库数据文件路径 *.log_file_name_convert=’/u01/app/oracle/oradata/std’,’/u01/app/oracle/oradata/pri’ 第一个目录是备份数据库数据文件路径(备份服务器上有此目录),第二个是主数据库数据文件路径 *.standby_file_management=’auto’ 修改完毕,保存退出 手工创建/u01/app/oracle/arch: [oracle@pri dbs]$ mkdir –p /u01/app/oracle/arch 5:用修改过的pfile重新创建一个spfile,用于重启数据库 (此步骤只在主库上做) 关闭数据库: SYS@pri> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 用修改过的pfile重新创建一个spfile: SYS@pri> create spfile from pfile; 此时把数据库改为归档模式: (如果当初建库时选择了启用归档,则此步骤忽略) 由于当前数据库已关闭,首先需要把数据库启动到mount状态 SYS@pri> startup mount; ORACLE instance started. Database mounted. SYS@pri> alter database archivelog; 启用归档模式 Database altered. SYS@pri> alter database open; OPEN数据库 Database altered. SYS@pri> archive log list; 查看是否启用归档模式 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 如上,归档路径已经改为/u01/app/oracle/arch,证明对pfile的修改已生效 查看当前数据库是否使用spfile启动: SYS@pri> show parameter spfile; NAME TYPE VALUE spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora 如上,若能看到spfile的路径,则证明数据库是使用spfile启动的,若没有值,则说明是用pfile启动的。 确认数据库已经启用归档模式和强制日志模式: SYS@pri> select name,log_mode,force_logging from v$database; NAME LOG_MODE FOR ——— ——————- ——- PRI ARCHIVELOG YES 6:修改监听文件,添加静态监听 (主库、备库都要做) 主库: [oracle@pri ~]cdORACLE_HOME/network/admin [oracle@pri admin]$ vim listener.ora 添加的内容如下部分: # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
) SID_LIST_LISTENER = (SID_LIST =
) ADR_BASE_LISTENER = /u01/app/oracle 备库: [oracle@pri ~]cdORACLE_HOME/network/admin [oracle@pri admin]$ vim listener.ora 添加的内容如下部分: # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.13)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
) SID_LIST_LISTENER = (SID_LIST =
) ADR_BASE_LISTENER = /u01/app/oracle 使新增加的监听生效: (主库和备库端都要做) [oracle@pri admin]$ lsnrctl stop [oracle@pri admin]$ lsnrctl start 确认新增加的静态监听有效: 主库: [oracle@pri ~]$ lsnrctl status ……………………………………(N行省略) Services Summary… Service “pri” has 2 instance(s). Instance “pri”, status UNKNOWN, has 1 handler(s) for this service… Instance “pri”, status READY, has 1 handler(s) for this service… Service “priXDB” has 1 instance(s). Instance “pri”, status READY, has 1 handler(s) for this service… The command completed successfully 备库: [oracle@std ~]$ lsnrctl status ……………………………………(N行省略) Services Summary… Service “std” has 2 instance(s). Instance “std”, status UNKNOWN, has 1 handler(s) for this service… The command completed successfully 如上,静态监听添加成功 7:编辑网络服务名配置文件tnsnames.ora (主库和备库端都要做) [oracle@ora11g admin]$ pwd /u01/app/oracle/product/11.2.0/db_1/network/admin [oracle@ora11g admin]$ ls listener.ora samples tnsnames.ora listener.ora_bak shrept.lst tnsnames.ora_bak [oracle@ora11g admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. pri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri))
) std = (DESCRIPTION =
) 保证主库和备库的tnsnames.ora文件中的内容完全相同,可以把修改后的文件直接传给备库。 [oracle@ora11g admin]scptnsnames.ora192.168.11.13:ORACLE_HOME/network/admin tnsnames.ora 100% 925 0.9KB/s 00:00 配置完后,确保在任意一端上都能tnsping通对方: [oracle@pri admin]$ tnsping std [oracle@std admin]$ tnsping pri 8:在备库端,修改pfile参数文件 (只在备库端做) 首先,在主库端把pfile拷贝给备库端的$ORACLE_HOME/dbs目录下,并重命名: [oracle@ora11g-dg ~]cdORACLE_HOME/dbs [oracle@ora11g-dg dbs]$ ls hc_std.dat init.ora initstd.ora lkSTD orapwstd spfilestd.ora [oracle@ora11g dbs]scpinitpri.ora192.168.11.13:ORACLE_HOME/dbs/initstd.ora initpri.ora 100% 1497 1.5KB/s 00:00 然后在备库端进行修改: [oracle@ora11g-db ~]cdORACLE_HOME/dbs [oracle@ora11g-dg dbs]$ more initstd.ora pri.__db_cache_size=318767104 pri.__java_pool_size=4194304 pri.__large_pool_size=4194304 pri.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment pri.__pga_aggregate_target=335544320 pri.__sga_target=503316480 pri.__shared_io_pool_size=0 pri.__shared_pool_size=163577856 pri.__streams_pool_size=0 *.audit_file_dest=’/u01/app/oracle/admin/std/adump’ *.audit_trail=’db’ *.compatible=’11.2.0.0.0’ *.control_files=’/u01/app/oracle/oradata/std/control01.ctl’,’/u01/app/oracle/oradata/std/control02.ctl’ *.db_block_size=8192 *.db_domain=” *.db_name=’pri’ DG主库和备库的db_name必须一致,db_unique_name不一致 *.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’ *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest=’/u01/app/oracle’ *.dispatchers=’(PROTOCOL=TCP) (SERVICE=stdXDB)’ *.log_archive_format=’%t_%s_%r.dbf’ *.memory_target=836763648 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile=’EXCLUSIVE’ *.undo_tablespace=’UNDOTBS1’ 以下需要手工添加: *.db_unique_name=’std’ *.log_archive_config=’dg_config=(pri,std)’ *.log_archive_dest_1=’location=/u01/app/oracle/arch valid_for=(all_logfiles,all_ roles) db_unique_name=std’ *.log_archive_dest_2=’service=pri valid_for=(online_logfiles,primary_role) db _unique_name=pri’ *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server=’pri’ *.fal_client=’std’ *.db_file_name_convert=’/u01/app/oracle/oradata/pri’,’/u01/app/oracle/oradata /std’ *.log_file_name_convert=’/u01/app/oracle/oradata/pri’,’/u01/app/oracle/oradat a/std’ *.standby_file_management=’auto’ 修改完毕,保存退出 注意:整个搭建过程最需要留意的就是主库和备库的PFILE配置,建议修改完后仔细对照主备库PFILE的区别 9:在备库端手工创建所需的目录 (备库端做,不提前创建的话恢复时会报错!) mkdir -pv /u01/app/oracle/admin/std/adump mkdir -pv /u01/app/oracle/diag/rdbms/std/std/trace mkdir -pv /u01/app/oracle/arch mkdir -pv /u01/app/oracle/oradata/std mkdir -pv /u01/app/oracle/oradata/standbylog mkdir -pv /u01/app/oracle/flash_recovery_area 10:用修改后的pfile创建一个spfile,用于启动数据库 (备库端做) [oracle@std ~]$ sqlplus / as sysdba Connected to an idle instance. SYS@std> create spfile from pfile; File created. 将数据库启动到nomount状态: SYS@std> startup nomount; ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 482348376 bytes Database Buffers 352321536 bytes Redo Buffers 2379776 bytes SYS@std> 11:利用RMAN在备库上恢复主库 (备库端做) [oracle@std ~]$ rman target sys/oracle@pri auxiliary sys/oracle@std Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 15 16:39:28 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRI (DBID=775616459) connected to auxiliary database: PRI (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; 这条命令可以直接恢复数据文件,standby控制文件,standby日志组,非常霸道 Starting Duplicate Db at 16-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=134 device type=DISK contents of Memory Script: { backup as copy reuse targetfile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapwpri’ auxiliary format ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstd’ ; } executing Memory Script Starting backup at 16-MAR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=140 device type=DISK Finished backup at 16-MAR-16 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/std/control01.ctl’; } executing Memory Script Starting backup at 16-MAR-16 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/db_1/dbs/snapcf_pri.f tag=TAG20160316T110737 RECID=2 STAMP=906635257 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16 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/std/temp01.dbf”; switch clone tempfile all; set newname for datafile 1 to “/u01/app/oracle/oradata/std/system01.dbf”; set newname for datafile 2 to “/u01/app/oracle/oradata/std/sysaux01.dbf”; set newname for datafile 3 to “/u01/app/oracle/oradata/std/undotbs01.dbf”; set newname for datafile 4 to “/u01/app/oracle/oradata/std/users01.dbf”; backup as copy reuse datafile 1 auxiliary format “/u01/app/oracle/oradata/std/system01.dbf” datafile 2 auxiliary format “/u01/app/oracle/oradata/std/sysaux01.dbf” datafile 3 auxiliary format “/u01/app/oracle/oradata/std/undotbs01.dbf” datafile 4 auxiliary format “/u01/app/oracle/oradata/std/users01.dbf” ; sql ‘alter system archive log current’; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/std/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 16-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/pri/system01.dbf output file name=/u01/app/oracle/oradata/std/system01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:38 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/pri/sysaux01.dbf output file name=/u01/app/oracle/oradata/std/sysaux01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:09 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/pri/undotbs01.dbf output file name=/u01/app/oracle/oradata/std/undotbs01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/pri/users01.dbf output file name=/u01/app/oracle/oradata/std/users01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16 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=906635463 file name=/u01/app/oracle/oradata/std/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=906635463 file name=/u01/app/oracle/oradata/std/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=906635463 file name=/u01/app/oracle/oradata/std/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=906635463 file name=/u01/app/oracle/oradata/std/users01.dbf Finished Duplicate Db at 16-MAR-16 RMAN> 恢复数据库结束 12:尝试开启备库 登陆并查看数据库当前状态: [oracle@std ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 14 16:41:50 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@std> startup SYS@std> select status from v$instance; STATUS MOUNTED (RMAN恢复完直接就是mount状态) 13:备库启动日志应用(启用备库前确认归档日志是否都已拷贝) SYS@std> alter database recover managed standby database disconnect from session; Database altered. (停止日志应用的命令是:alter database recover managed standby database cancel;) 查看日志应用情况: SYS@std> set pagesize 100 SYS@std> select sequence#,applied from v$archived_log order by 1; SEQUENCE# APPLIED 8 YES 9 YES 10 YES 如上,如果发现有个NO的,也是正常的,说明该日志在主库上还没有归档,可以在主库上运行alter system switch logfile;命令来进行日志切换,再到备库查看日志应用情况 14:分别查看主库和备库的归档序列号是否一致: 先在主库手动切换一下日志: SYS@pri> alter system switch logfile; System altered. 然后查看主库: SYS@pri> archive log list; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 备库: SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 0 Current log sequence 24 结果完全一致,至此,DataGuard的搭建成功! 15:检查命令 查看standby启动的DG进程 SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS ARCH ARCH 23 CLOSING ARCH ARCH 0 CONNECTED //归档进程 ARCH ARCH 21 CLOSING ARCH ARCH 0 CONNECTED RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 24 IDLE //归档传输进程 RFS UNKNOWN 0 IDLE MRP0 N/A 24 APPLYING_LOG //日志应用进程 9 rows selected. 查看数据库的保护模式: SQL> select database_role,protection_mode,protection_level,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE standby 端查看,也是一样的。SQL> select database_role,protection_mode,protection_level,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED 查看DG的日志信息 SQL> select * from v$dataguard_status; Open Read Only standby数据库并且开启实时日志应用 SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1188511744 bytes Fixed Size 1364228 bytes Variable Size 754978556 bytes Database Buffers 419430400 bytes Redo Buffers 12738560 bytes Database mounted. Database opened. SQL> select database_role,protection_mode,protection_level,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE 7 rows selected. SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE MRP0 N/A 27 APPLYING_LOG 8 rows selected. SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS ARCH ARCH 19 CLOSING ARCH ARCH 20 CLOSING ARCH ARCH 0 CONNECTED ARCH ARCH 21 CLOSING MRP0 N/A 22 WAIT_FOR_LOG RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 22 IDLE 9 rows selected. |
|