Oracle数据库
ADG搭建实施报告
2023年 6月
修改历史
日期 | 版本号 | 作者 | 修改说明 | 更改请求号 |
注释:“变更请求号”为文档正式发布后需要变更时的编号。
审阅
姓名 | 日期 | 备注 |
分发
公司 | 姓名 | 日期 | 备注 |
目录
2.3.4 remote_login_passwordfile配置
1概述
本文档为RAC TO 单节点的数据库ADG搭建实施手册。
2实施步骤
2.1 基本信息
(如下是idmdb主、备库基本信息)主库使用两节点RAC,备库使用单节点进行搭建ADG,具体信息如下表格:
DB类型 | 主机名 | DB_NAME | DB_UNIQUE_NAME | INSTANCE | ASM DG |
主库 | CRPLDAPDB01 | Idmdb | Idmdb | Idmdb 1 | DATA, +DATA/arch |
CRPLDAPDB02 | Idmdb2 | ||||
备库 | dg-ldapdb | Idmdb | dg_ldapdb | dg_ldapdb |
主机 | 灾备IP | 灾备VIP |
CRPLDAPDB01 | 10.59.1.42 | 10.59.1.41 |
CRPLDAPDB02 | 10.59.1.44 | 10.59.1.43 |
dg-ldapdb | 10.61.0.230 |
2.2 准备工作
备注:无特殊说明所有操作均在备库服务器上
2.2.1 RMAN备份主库
备注:主库端操作
- 查看数据库大小
- 编写脚本进行全库备份
SQL> SELECT SUM(DS.BYTES)/1024/1024/1024 "SIZE(G)" FROM DBA_SEGMENTS ds; SIZE(G) ---------- 134.760681 |
idmdb1:/backup/fullback20200103@CRPLDAPDB01> vi fullbak.sh ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=idmdb1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM export NLS_LANG=AMERICAN_CHINA.AL32UTF8 TMPDIR=/var/tmp; export TMPDIR NLS_DATE_FORMAT="YYYY/MM/DD hh24:mi:ss"; export NLS_DATE_FORMAT TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN DISPLAY LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH PATH=$PATH:$ORACLE_HOME/bin:/usr/sbin; export PATH PS1=[`hostname`:$ORACLE_SID:\$PWD$] BACKUP_DIR=/backup/fullback20200103 rman target / log $BACKUP_DIR/rman_db_full_$(date +%Y%m%d).log<<EOF run{ allocate channel c1 device type disk; allocate channel c2 device type disk; allocate channel c3 device type disk; CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET; configure channel device type disk maxpiecesize 4G; BACKUP FORMAT='/backup/fullback20200103/data_%U_%T.dbf' DATABASE; BACKUP SPFILE FORMAT '/backup/fullback20200103/spfile_%U_%T.ora'; backup current controlfile for standby format'/backup/fullback20200103/ctl_stand_con.ctl'; release channel c1; release channel c2; release channel c3; } EOF |
idmdb1:/backup/fullback20200103@CRPLDAPDB01>chmod 755 fullbak.sh idmdb1:/backup/fullback20200103@CRPLDAPDB01> nohup sh fullbak.sh > fullbak.log & |
2.2.2备份文件传输至备库
备注:主库端操作
将全库备份通过灾备复制网络传输至备库服务器的/home/oracle/backup目录中。
2.2.3添加tnsnames.ora文件信息
- 配置主备端三个节点的tnsnames.ora,添加如下信息:
IDMDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.45)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.42)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.44)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =idmdb) ) ) dg_ldapdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.61.0.230)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =dg_ldapdb) ) ) |
#以上已添加可通过lsnrctl status查看状态
2.2.4配置静态监听
LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=10.61.0.230)(PORT=1521)))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=dg_ldapdb) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=dg_ldapdb))) |
2.2.5 拷贝密码文件
- 在备库端将主库传输过来的口令文件拷贝到$ORACLE_HOME/dbs下并重命名:
scp orapwidmdb1 oracle@10.61.0.230:$ORACLE_HOME/dbs/orapwdg_ldapdb |
2.2.6配置hosts
vi /etc/hosts 10.61.0.230 dg-ldapdb |
2.3主库检查
备注:主库端操作
2.3.1 是否安装相关组件
SQL> SELECT * FROM V$OPTION WHERE PARAMETER in ('Oracle Data Guard', 'Advanced Compression'); PARAMETER VALUE ------------------------------- ---------- Oracle Data Guard TRUE Advanced Compression TRUE |
2.3.2 FORCE_LOGGING模式
select force_logging from v$database; (开启:alter database force logging;) |
2.3.3 归档模式
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/arch Oldest online log sequence 3545 Next log sequence to archive 3547 Current log sequence 3547 |
2.3.4 remote_login_passwordfile配置
show parameter remote_login_passwordfile (remote_login_passwordfile应为EXCLUSIVE) |
2.4 修改参数文件
2.4.1 修改主库参数
备注:主库端操作
修改主库参数
alter system set log_archive_config='dg_config=( idmdb, dg_ldapdb)' scope=both sid='*'; alter system set log_archive_dest_state_2='defer' scope=both sid='*'; #等备库实例启动,再开启enabled alter system set log_archive_dest_2='service=dg_ldapdb LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dg_ldapdb' sid='*' scope=both; alter system set fal_client=' idmdb' scope=both sid='*'; alter system set fal_server=' dg_ldapdb' scope=both sid='*'; alter system set standby_file_management=AUTO scope=both sid='*'; alter system set log_archive_max_processes = 8 scope=both sid='*'; |
2.4.2 修改备库参数
[oracle@dg-ldapdb dbs]$ cat initdg_ldapdb.ora *.aq_tm_processes=1 *.audit_file_dest='/u01/app/oracle/admin/dg_ldapdb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/dg_ldapdb/current01.ctl','/u01/app/oracle/oradata/dg_ldapdb/current02.ctl'#Restore Controlfile *.db_block_size=8192 *.db_cache_size=6453M *.db_create_file_dest='/u01/app/oracle/oradata/dg_ldapdb' *.db_domain='' *.db_name='idmdb' *.db_unique_name='dg_ldapdb' *.db_recovery_file_dest='/u01/app/oracle/flashback' *.db_recovery_file_dest_size=10g *.dg_broker_start=FALSE *.diagnostic_dest='/u01/app/oracle' #*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg_ldapdbXDB)' *.fal_client='dg_ldapdb' *.fal_server='idmdb' *.java_jit_enabled=TRUE *.job_queue_processes=1000 *.large_pool_size=698M *.log_archive_config='dg_config=(idmdb,dg_ldapdb)' *.log_archive_dest_1='location=/arch' *.log_archive_dest_2='service=idmdb LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=idmdb' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=8 *.log_archive_min_succeed_dest=1 *.log_archive_trace=0 *.nls_territory='CHINA' *.open_cursors=1000 *.pga_aggregate_target=2g *.processes=1200 *.query_rewrite_enabled='TRUE' *.query_rewrite_integrity='TRUSTED' *.remote_login_passwordfile='exclusive' *.sessions=1325 *.sga_max_size=5g *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' *.db_file_name_convert='+DATA/idmdb','/u01/app/oracle/oradata/dg_ldapdb' *.log_file_name_convert='+DATA/idmdb,'/u01/app/oracle/oradata/dg_ldapdb' |
Mkdir -p /u01/app/oracle/admin/dg_ldapdb/adump mkdir -p /u01/app/oracle/oradata/dg_ldapdb mkdir -p /arch mkdir -p /u01/app/oracle/flashback mkdir -p /u01/app/oracle/oradata/dg_ldapdb/datafile mkdir -p /u01/app/oracle/oradata/dg_ldapdb/onlinelog |
2.5 创建并恢复备库实例
2.5.1 恢复参数文件
sqlplus / as sysdba SQL> create spfile from pfile; SQL> startup nomount #检查数据库是否使用spfile启动及参数: show parameter spfile show parameter name show parameter standby show parameter cluster show parameter listerner show parameter fal show parameter manage show parameter convert show parameter log |
2.5.2 恢复控制文件
rman target / RMAN> restore standby controlfile from '/home/oracle/backup/fullback20200103/ctl_stand_con.ctl'; RMAN> alter database mount; |
2.5.3 恢复数据库
- restore database
RMAN> catalog start with '/home/oracle/backup/fullback20200103'; RMAN> crosscheck backupset; RMAN> delete noprompt expired backup; RMAN> restore database; |
2.6创建standby redo
2.6.1 查看主库日志文件大小
备注:主/备库端操作
col thread# for 99 col member for 99 col bytes for 99999999 col status for a10 col type for a20 set linesize 200 set pagesize 100 SELECT L.GROUP#, L.THREAD#, LF.MEMBER, L.BYTES/1024/1024, L.STATUS, LF.TYPE FROM V$LOG L, V$LOGFILE LF WHERE L.GROUP# = LF.GROUP#; |
2.6.2 创建standby日志组
备注:若数据库已经有standby log的信息,主备库可不用重新删除添加。即忽略该步骤。以下为主备库均无standby log情况下操作。
- 添加主备库端两节点的standby日志组
主库: alter database add standby logfile thread 1 group 13 ('+DATA','+DATA') size 200m; alter database add standby logfile thread 2 group 14 ('+DATA','+DATA') size 200m; 备库: alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby07a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby07b.log') size 200m; alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby08a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby08b.log') size 200m; alter database add standby logfile thread 1 group 9 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby09a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby09b.log') size 200m; alter database add standby logfile thread 2 group 10 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby10a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby10b.log') size 200m; alter database add standby logfile thread 2 group 11 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby11a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby11b.log') size 200m; alter database add standby logfile thread 2 group 12 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby12a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby12b.log') size 200m; alter database add standby logfile thread 1 group 13 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby13a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby13b.log') size 200m; alter database add standby logfile thread 2 group 14 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby14a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby14b.log') size 200m; |
2.6.3 备库介质恢复
alter database recover managed standby database using current logfile disconnect from session nodelay; |
2.6.4 开启日志投递
备注:主库端操作
alter system set log_archive_dest_state_3='enable' scope=both sid='*'; alter system switch logfile; |
2.7同步状态检查
2.7.1 查看备库警告日志
tail -100f alersid.log
2.7.2 mrp进程状态检查
select inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%'; |
2.7.3 外部归档文件处理
注意:外部存档日志文件空间,如空间不足请及时删除(文档 ID 1617965.1)
SQL>select * from V$FLASH_RECOVERY_AREA_USAGE; RMAN>delete foreign archivelog all; |
2.7.4 日志接收与同步检查
主库: select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1; 备库: -- 检查备库已经接收到的 sequence# 号 select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1; -- 检查备库已经应用到的 sequence# 号 select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1; |
2.7.5 延迟查询
select name,value from v$dataguard_stats; |
2.8 DG_BLOCK配置
2.8.1 配置监听
主库: vi /u01/app/11.2.0/grid_1/network/admin/listener.ora --Primary Node1 SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=idmdb1)(GLOBAL_DBNAME=idmdb__DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1))) --Primary Node2 SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=idmdb2)(GLOBAL_DBNAME=idmdb__DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1))) 备库: LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=10.61.0.230)(PORT=1521)))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=dg_ldapdb) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=dg_ldapdb)) (SID_DESC= (GLOBAL_DBNAME=dg_ldapdb_DGMGRL) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=dg_ldapdb))) |
2.8.2 修改配置参数
主库: ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+data/IDMDB/DATAGUARDCONFIG/dgb_config1.ora' SCOPE=BOTH sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+data/IDMDB/DATAGUARDCONFIG2/dgb_config1.ora' SCOPE=BOTH sid='*'; alter system set dg_broker_start=true scope=both sid='*'; 备库: alter system set dg_broker_start=true scope=both sid='*'; |
2.8.3 配置DG_BLOCK
SQL> dgmgrl sys/oracle 在dgmgrl下创建配置: DGMGRL> CREATE CONFIGURATION idmdb AS PRIMARY DATABASE IS 'idmdb' CONNECT IDENTIFIER IS 'idmdb'; DGMGRL> ADD DATABASE 'dg_ldapdb' AS CONNECT IDENTIFIER IS 'dg_ldapdb'; DGMGRL> SHOW CONFIGURATION; Configuration - idmdb Protection Mode: MaxPerformance Databases: idmdb - Primary database dg_ldapdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database verbose idmdb; Database - idmdb Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): idmdb1 idmdb2 Properties: DGConnectIdentifier = 'idmdb' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS DGMGRL> SHOW DATABASE VERBOSE dg_ldapdb; Database - dg_ldapdb Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 647.00 KByte/s Real Time Query: ON Instance(s): dg_ldapdb Properties: DGConnectIdentifier = 'dg_ldapdb' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'auto' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '+DATA/idmdb, /u01/app/oracle/oradata/dg_ldapdb' LogFileNameConvert = '+DATA/idmdb,/u01/app/oracle/oradata/dg_ldapdb' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'dg_ldapdb' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-ldapdb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg_ldapdb_DGMGRL)(INSTANCE_NAME=dg_ldapdb)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/arch' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS |