ORACLE DATAGUARD单实例配置
1.DataGuard所需环境配置
操作系统 Centos6.5x86-64(64位) Centos6.5x86-64(64位) ------------------------------------------------------------------------- 服务器名称 oracle oracle ------------------------------------------------------------------------- IP地址规划 192.168.11.66 192.168.11.67 ------------------------------------------------------------------------- 数据库版本 11.2.0.4 11.2.0.4 ------------------------------------------------------------------------- db_name primarydb primarydb ------------------------------------------------------------------------- db_unique_name primarydb standbydb ------------------------------------------------------------------------- instance_name primarydb standbydb ------------------------------------------------------------------------- service_name primarydb primarydb ------------------------------------------------------------------------- 需要操作 安装数据库软件+创建监听+安装数据库 安装数据库软件+创建监听
2.数据库软件、监听、数据库等配置略过,保证两台机子的hosts文件一模一样
vim /etc/hosts 127.0.0.1 localhost oracle 192.168.11.66 oracle 192.168.11.67 oracle
3.DataGuard主库配置
查看日志文件和数据文件路径
#数据文件 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/oracle/primarydb/system01.dbf /oradata/oracle/primarydb/sysaux01.dbf /oradata/oracle/primarydb/undotbs01.dbf /oradata/oracle/primarydb/users01.dbf #查看在线日志文件的位置,log_file_name_convert SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /oradata/oracle/primarydb/redo03.log /oradata/oracle/primarydb/redo02.log /oradata/oracle/primarydb/redo01.log
主库参数
#主库启用强制记录日志功能,查询是否启用强制记录日志 select force_logging from v$database; #如果未启用，则使用下面语句来开启强制记录日志 alter database force logging; alter database open; #主库参数设置 alter system set db_unique_name='primarydb' scope=spfile; alter system set log_archive_config='DG_CONFIG=(primarydb,standbydb)' scope=spfile; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primarydb' scope=both; alter system set log_archive_dest_2='SERVICE=standbydb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb' scope=both; alter system set fal_server='standbydb' scope=both sid='*'; alter system set fal_client='primarydb' scope=both sid='*'; alter system set db_file_name_convert='/oradata/data/standbydb/','/oradata/data/primarydb/' scope=spfile; alter system set log_file_name_convert='/oradata/data/standbydb/','/oradata/data/primarydb/' scope=spfile; #alter system set log_archive_dest_state_1 = ENABLE; #alter system set log_archive_dest_state_2 = ENABLE; alter system set standby_file_management=auto scope=spfile; #如果主库与备库数据文件位置不相同，则需要使用db_file_name_convert来转换。 alter system set log_file_name_convert='/oradata/oracle/standbydb','/oradata/oracle/primarydb' scope=spfile; alter system set db_file_name_convert='/oradata/oracle/standbydb','/oradata/oracle/primarydb' scope=spfile; #在主库添加日志组 alter database add standby logfile group 4 ('/oradata/data/primarydb/stredo04.log') size 50M; alter database add standby logfile group 5 ('/oradata/data/primarydb/stredo05.log') size 50M; alter database add standby logfile group 6 ('/oradata/data/primarydb/stredo06.log') size 50M; alter database add standby logfile group 7 ('/oradata/data/primarydb/stredo07.log') size 50M; --standby_file_management alter system set fal_client='primarydb' scope=both; --fal_client alter system set fal_server='standbydb' scope=both; #启用归档日志 archive log list; shutdown immediate; startup mount; alter database archivelog; alter system set db_recovery_file_dest='location=/oradata/oracle/archive' scope=both; alter system set db_recovery_file_dest_size=1024g; alter database open;
#创建备库所需要的pfile文件
create pfile from spfile
3.主库配置静态监听
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ) #静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=primarydb) (SID_NAME = primarydb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1) ) ) ADR_BASE_LISTENER = /u01/app/oracle
查看监听状态
[oracle@oracle:/oradata/oracle/primarydb]$lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-APR-2020 15:34:41 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 21-APR-2020 10:34:00 Uptime 0 days 5 hr. 0 min. 41 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) Services Summary... Service "primarydb" has 1 instance(s). Instance "primarydb", status READY, has 1 handler(s) for this service... Service "primarydbXDB" has 1 instance(s). Instance "primarydb", status READY, has 1 handler(s) for this service... The command completed successfully
主库tnsnames.ora文件配置
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. primarydb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.66)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primarydb) ) ) standbydb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.67)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standbydb) ) )
4.配库配置
#拷贝密码文件并重命名
[oracle@oracle:/u01/app/oracle/product/11.2.0.4/db_1/dbs]$scp orapwprimarydb oracle@192.168.11.67:/u01/app/oracle/product/11.2.0.4/db_1/dbs/ oracle@192.168.11.67 password: orapwprimarydb 100% 1536 1.5KB/s 00:00 [oracle@oracle:/u01/app/oracle/product/11.2.0.4/db_1/dbs]$mv orapwprimarydb orapwstandbydb #或者不拷贝密码文件，直接使用orapwd生成一个新的密码文件，密码需与主库一致 orapwd file=orapwadgdbstandby password='oracle'; #拷贝参数文件(pfile)并重命名 [oracle@oracle:/u01/app/oracle/product/11.2.0.4/db_1/dbs]$scp initprimarydb.ora oracle@192.168.11.67:/u01/app/oracle/product/11.2.0.4/db_1/dbs/ oracle@192.168.11.67s password: initprimarydb.ora 100% 1485 1.5KB/s 00:00 [oracle@standbynode dbs]$ mv initprimarydb.ora initstandbydb.ora
修改pfile参数文件
*.audit_file_dest='/u01/app/oracle/admin/standbydb/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oradata/oracle/standbydb/control01.ctl','/u01/app/oracle/fast_recovery_area/standbydb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='primarydb','standbydb' *.db_name='primarydb' *.db_unique_name='standbydb' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primarydbXDB)' *.fal_client='standbydb' *.fal_server='primarydb' *.log_archive_config='DG_CONFIG=(standbydb,primarydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standbydb' *.log_archive_dest_2='SERVICE=primarydb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primarydb' #*.log_archive_dest_state_1='ENABLE' #*.log_archive_dest_state_2='ENABLE' *.log_file_name_convert='/oradata/oracle/primarydb','/oradata/oracle/standbydb' *.db_file_name_convert='/oradata/oracle/primarydb','/oradata/oracle/standbydb' *.open_cursors=300 *.pga_aggregate_target=195035136 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=587202560 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
注意：里面涉及到路径的需要手动创
mkdir -p /u01/app/oracle/admin/standbydb/adump
mkdir -p /u01/app/oracle/fast_recovery_area/standbydb
mkdir -p /oradata/oracle/standbyd
使用pfile文件创建spfile文件,在备库上执行,并启动数据库到nomount状态
sqlplus / as sysdba SQL> create spfile from pfile; SQL> startup nomount;
配置备库监听,nomount状态下必须使用静态监听才能连接到实例，在没有实例的情况下配置了动态监听，监听则会是blocked的状态，所需这里需要静态监听
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=standbydb) (SID_NAME = standbydb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1) ) ) ADR_BASE_LISTENER = /u01/app/oracle
配置tnsnames.ora文件，直接把主库的拷贝过来即可
[oracle@oracle:/u01/app/oracle/product/11.2.0.4/db_1/network/admin]$scp tnsnames.ora oracle@192.168.11.67:/u01/app/oracle/product/11.2.0.4/db_1/network/admin/ # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. primarydb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.66)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primarydb) ) ) standbydb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.67)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standbydb) ) )
在主备库上测试网络是否正常
主库
[oracle@oracle:/u01/app/oracle/product/11.2.0/db_1/dbs]$tnsping primarydb TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-OCT-2020 12:09:58 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.66)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = primarydb))) OK (20 msec) [oracle@oracle:/u01/app/oracle/product/11.2.0/db_1/dbs]$tnsping standbydb TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-OCT-2020 12:10:08 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.67)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standbydb) (SERVER = DEDICATED)(UR=A))) OK (0 msec) [oracle@oracle:/u01/app/oracle/product/11.2.0/db_1/dbs]$
备库
[oracle@oracle:/]$tnsping primarydb TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-OCT-2020 12:11:49 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.66)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = primarydb))) OK (10 msec) [oracle@oracle:/]$tnsping standbydb TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-OCT-2020 12:11:55 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.67)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standbydb))) OK (0 msec)
测试主库是否能正常链接到备库
[oracle@oracle:/]$sqlplus sys/oracle@standbydb as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 31 13:08:19 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
注意：仔细检查你的pfile参数是否正确
--检查参数是否正确,这一步非常重要,直接影响到你备库能不能起来仔细检查,由于参数配置的较多，需要认真检查参数配置是否正确，主要检查这些参数 1.db_unique_name --:2个节点需要不一样 2.compatible --:主库与备库兼容性需一致 3.log_archive_config --:配置主库与备库的db_unique_name 4.log_archive_desc_1,2 --:归档日志的路径 5.log_archive_desc_state_2 --: 6.enable --启用log_archive_desc_2 7.defer --禁用log_archive_desc_2 8.db_file_name_convert --:数据文件转换路径 9.log_file_name_convert --:日志文件转换路径 10.standby_file_management --:设置为auto 11.log_archive_format --:日志文件格式，两边需一致
使用duplicate创建物理standby,连接到主库和备库,一定要加nocatalog，否则在执行duplicate时会报错。
[oracle@standbydb ~]$ rman target sys/oracle@primarydb auxiliary sys/oracle@standbydb nocatalog rman target sys/oracle@primarydb auxiliary sys/oracle@standbydb nocatalog RMAN> duplicate target database for standby from active database nofilenamecheck; --执行结果如下所示 Starting Duplicate Db at 2020-04-21 15:00:17 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwprimarydb' auxiliary format '/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwstandbydb' ; } executing Memory Script Starting backup at 2020-04-21 15:00:18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 device type=DISK Finished backup at 2020-04-21 15:00:19 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oradata/oracle/standbydb/control01.ctl'; restore clone controlfile to '/u01/app/oracle/fast_recovery_area/standbydb/control02.ctl' from '/oradata/oracle/standbydb/control01.ctl'; } executing Memory Script Starting backup at 2020-04-21 15:00:19 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.4/db_1/dbs/snapcf_primarydb.f tag=TAG20200421T150019 RECID=8 STAMP=1038322819 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2020-04-21 15:00:20 Starting restore at 2020-04-21 15:00:20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 2020-04-21 15:00:21 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 "/oradata/oracle/standbydb/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradata/oracle/standbydb/system01.dbf"; set newname for datafile 2 to "/oradata/oracle/standbydb/sysaux01.dbf"; set newname for datafile 3 to "/oradata/oracle/standbydb/undotbs01.dbf"; set newname for datafile 4 to "/oradata/oracle/standbydb/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/oradata/oracle/standbydb/system01.dbf" datafile 2 auxiliary format "/oradata/oracle/standbydb/sysaux01.dbf" datafile 3 auxiliary format "/oradata/oracle/standbydb/undotbs01.dbf" datafile 4 auxiliary format "/oradata/oracle/standbydb/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /oradata/oracle/standbydb/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 2020-04-21 15:00:29 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/oradata/oracle/primarydb/system01.dbf output file name=/oradata/oracle/standbydb/system01.dbf tag=TAG20200421T150029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/oradata/oracle/primarydb/sysaux01.dbf output file name=/oradata/oracle/standbydb/sysaux01.dbf tag=TAG20200421T150029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/oradata/oracle/primarydb/undotbs01.dbf output file name=/oradata/oracle/standbydb/undotbs01.dbf tag=TAG20200421T150029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/oradata/oracle/primarydb/users01.dbf output file name=/oradata/oracle/standbydb/users01.dbf tag=TAG20200421T150029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2020-04-21 15:01:13 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=8 STAMP=1038322874 file name=/oradata/oracle/standbydb/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=9 STAMP=1038322874 file name=/oradata/oracle/standbydb/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=10 STAMP=1038322874 file name=/oradata/oracle/standbydb/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=11 STAMP=1038322874 file name=/oradata/oracle/standbydb/users01.dbf Finished Duplicate Db at 2020-04-21 15:01:21
如果要使用Catalog需要创建备份目录和恢复目录，做全备然后进行恢复
开始同步数据库,在备库上执行
SQL> alter database open; --打开数据库 SQL> alter database recover managed standby database using current logfile disconnect from session; --开启实时同步 SQL> alter database recover managed standby database cancel; --停止同步 SQL> alter database recover managed standby database disconnect from session; --开启日志切换同步 --查看备库状态 SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE --在主库创建测试表,并插入数据 SQL> create table newings(a number,b varchar2(50)); Table created. SQL> insert into newings values(1,'aaaa'); SQL> insert into newings values(2,'bbbb'); SQL> insert into newings values(3,'cccc'); SQL> commit; --在standby数据库上查询newings表,数据是否一致 SQL> select * from newings; SID SNAME ---------- -------------------------------------------------- 1 aaaa 2 bbbb 3 cccc 3 rows selected. SQL>
DataGuard启动顺序
--1.先起备库 startup nomount alter database mount standby database; alter database open; alter database recover managed standby database using current logfile disconnect from session;
--启动监听
lsnrctl start --2.再起主库 startup;
--启动监听
lsnrctl start
DataGuard停止顺序
--1.先关主库 shutdown immediate; --2.再关备库 alter database recover managed standby database cancel; --停止同步 shutdown immediate;
