一、配置方案
环境说明
| 角色 | 主库 | 主库 | 备库 |
|---|---|---|---|
| 主机名 | rac01 | rac02 | racdg |
| 公网IP | 10.10.10.141 | 10.10.10.143 | 10.10.10.191 |
| VIP | 10.10.10.142 | 10.10.10.144 | - |
| SCAN | 10.10.10.140 | 10.10.10.140 | - |
| INSTANCE_NAME | orcl1 | orcl2 | orcl |
| DB_NAME | orcl | orcl | orcl |
| SERVICE_NAME | orcl | orcl | orcl |
| DB_UNIQUE_NAME | orcl | orcl | orcldg |
| 存储模式 | ASM | ASM | 文件系统 |
文件系统规划
| 主库 | 备库 |
|---|---|
| +DATADG/orcl/datafile | /oradata/datafile |
| +DATADG/orcl/tempfile | /oradata/tempfile |
| +ARCDG/orcl/onlinelog, +DATADG/orcl/onlinelog | /oradata/onlinelog |
| +ARCDG/ | /oradata/arch |
操作步骤
1. 主库上设置 Force Logging 模式
查看数据库的模式：
SQL> select LOG_MODE, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, GUARD_STATUS, FORCE_LOGGING from v$database;
在主库上开启强制日志模式：
SQL> alter database force logging;SQL> select force_logging from v$database;
FOR
---
YES
--如果没有开始归档模式需要开启归档模式
srvctl stop database -d orcl -o immediate
srvctl start instance -d orcl -i orcl1 -o mount
alter database archivelog;
alter database open;
srvctl start instance -d orcl -i orcl2
2. RMAN备份主库
在rac节点上执行 RMAN 备份：
$rman target /
RMAN> run {allocate channel c1 type disk;allocate channel c2 type disk;backup as compressed backupset database format='/oracle/backup/%T_%d_%s_%p.bak';sql 'alter system archive log current';backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p.bak';backup format='/oracle/backup/%T_%d_%s_%p.ctl' current controlfile;release channel c1;release channel c2;
}
传递备份至备库：
$ scp /oracle/backup/20210418* oracle@10.10.10.191:/oradata/backup
3. 主库上监听和 TNS 配置
配置主库监听：对于 RAC 环境在grid用户下的$GRID_HOME/network/admin/listener.ora
su - grid
$cd $GRID_HOME/network/admin/
$vi listener.ora
示例配置：
##节点 1
[grid@rac01 admin]$ cat listener.oralistener.ora Network Configuration File:
/u01/app/11.2.0/grid/network/admin/listener.ora Generated by Oracle
configuration tools.ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ONSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)(SID_NAME = orcl1)))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)))ADR_BASE_LISTENER = /u01/app/gridENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ONLISTENER_SCAN1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)))ADR_BASE_LISTENER_SCAN1 = /u01/app/grid##节点 2，对应修改即可。
[grid@rac02 admin]$ cat listener.oralistener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.oraGenerated by Oracle configuration tools.ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ONSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)(SID_NAME = orcl2)))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)))ADR_BASE_LISTENER = /u01/app/gridENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ONLISTENER_SCAN1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)))ADR_BASE_LISTENER_SCAN1 = /u01/app/grid-- 这里写的 Oracle 用户的 ORACLE_HOME，否则连接时会报错:ORA-01031: insufficient privileges
注意在 Oracle 11gR2 的 RAC 环境下，监听是在 grid 用户下配置的。所以这里可以用 grid 用户连接，并修改。 最后重新加载监听。
将备库的 TNS 添加到主库两节点的 tnsnames.ora 中：
$cd $ORACLE_HOME/network/admin
$vi tnsnames.ora
示例配置：
ORCL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.141)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.143)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))ORCLDG =
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.191)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl))
)
4. 主库上修改参数
在节点一和节点二上设置以下参数：
SQL> alter system set fal_server='orcldg';
SQL> alter system set fal_client='orcl1' scope=both sid='orcl1'; --不打算使用FSFO功能可以不配
SQL> alter system set fal_client='orcl2' scope=both sid='orcl2'; --不打算使用FSFO功能可以不配
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=both sid='*';
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(all_logfiles,all_roles) db_unique_name=orcl' scope=both sid='*';
SQL> alter system set log_archive_dest_2='SERVICE=orcldg lgwr ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both;
SQL> alter system set log_archive_max_processes=10 scope=both;
SQL> alter system set db_file_name_convert='/oradata/datafile', '+DATADG/orcl/datafile', 'oradata/tempfile', '+DATADG/orcl/tempfile' scope=spfile;
SQL> alter system set log_file_name_convert='/oradata/onlinelog','+ARCDG/orcl/onlinelog','/oradata/onlinelog','+DATADG/orcl/onlinelog' scope=spfile;
SQL> alter system set standby_file_management='AUTO' scope=both;
SQL> alter system set log_archive_dest_state_1='enable';
SQL> alter system set log_archive_dest_state_2='enable';
SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
5. 备库存储和文件系统创建
在备库上新建各个数据文件的存放位置：
$ mkdir -p /oradata/datafile # 数据文件
$ mkdir -p /oradata/tempfile # 临时文件
$ mkdir -p /oradata/onlinelog # redo 日志
$ mkdir -p /oradata/controlfile # 控制文件
$ mkdir -p /oradata/arch # 归档文件
手动建立以下目录，保持与主库目录一致：
$ cd $ORACLE_BASE
$ mkdir -p /u01/app/oracle/oradata/orcl
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area
6. 口令文件复制
将主库的口令文件复制到备库相同目录下，并改名为 orapworcldg：
$ scp /oracle/app/oracle/db/dbs/orapworcl oracle@10.10.10.191:/u01/app/oracle/product/11.2.0/db_1/dbs
$ mv orapworcl orapworcldg
7. 创建和修改备库 pfile 参数文件
在主库上生成 pfile：
SQL> create pfile from spfile;
传递至备库并重命名：
$ cd $ORACLE_HOME/dbs
$ scp initorcl.ora oracle@10.10.10.191: /u01/app/oracle/product/11.2.0/db_1/dbs
$ vi initorcldg.ora
修改相关参数：
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/controlfile/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name=orcl
*.db_unique_name=orcldg
*.service_names=orcl
*.db_recovery_file_dest_size=209715200000
*.open_cursors=300
*.pga_aggregate_target=70061654016
*.processes=1500
*.sessions=1655
*.sga_target=38386270208
*.log_archive_format='%t_%s_%r.arc'
*.db_recovery_file_dest='/oradata/arch'
*.db_file_name_convert='+DATADG/orcl/datafile','/oradata/datafile','+DATADG/orcl/tempfile','/oradata/tempfile'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl, orcldg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_file_name_convert='+ARCDG/orcl/onlinelog','/oradata/onlinelog','+DATADG/orcl/onlinelog','/oradata/onlinelog'
*.remote_login_passwordfile='exclusive'
*.standby_file_management=auto
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='orcl1','orcl2'
*.fal_client='orcldg'
启动备库并创建 SPFILE：
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcldg.ora' nomount;
SQL> create spfile from pfile;
SQL> shutdown immediate;
8. 备库上监听和 TNS 配置
配置监听器：
$cd $ORACLE_HOME/network/admin
$vi listener.ora
示例配置：
LISTENER =
(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.191)(PORT = 1521)))
)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)(SID_NAME = orcl)))ADR_BASE_LISTENER = /u01/app/oracle
配置 TNS：
$vi tnsnames.ora
示例配置：
ORCL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.141)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.143)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))ORCLDG =
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.191)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl))
)
开启监听：
$ lsnrctl start
检查监听和 TNS：
$ tnsping orcldg
$ tnsping orcl
9. RMAN恢复备库
恢复控制文件并注册备份集：
-- 备库启动到nomount
SQL> startup nomount;
-- 进入rman恢复控制文件，并注册备份集
rman target /
RMAN> restore standby controlfile from '/oradata/backup/20210418_ORCL_4020_1.ctl';
RMAN> alter database mount;
RMAN> CATALOG START WITH '/oradata/backup/';
RMAN> CROSSCHECK BACKUP;
恢复数据库：
示例：批量重命名数据文件和临时文件
-- 生成重命名数据文件的 RMAN命令
SELECT 'set newname for datafile ' || t.file_id || ' to ''' || '/oradata/datafile/' || SUBSTR(t.file_name, INSTR(t.file_name, '/', -1) + 1) || '''; '
FROM dba_data_files t;-- 生成重命名临时文件的 RMAN命令
SELECT 'set newname for tempfile ' || t.file_id || ' to ''' || '/oradata/datafile/' || SUBSTR(t.file_name, INSTR(t.file_name, '/', -1) + 1) || '''; '
FROM dba_temp_files t;-- 生成重命名日志文件的 SQL 语句（如果需要）
SELECT 'ALTER DATABASE RENAME FILE ''' || a.MEMBER || ''' TO ''' || '/oradata/onlinelog/' || SUBSTR(a.MEMBER, INSTR(a.MEMBER, '/', -1) + 1) || ''';'
FROM v$logfile a;
执行恢复：
RMAN> run {set newname for datafile 1 to '/oradata/datafile/system01.dbf';set newname for datafile 2 to '/oradata/datafile/sysaux01.dbf';set newname for datafile 3 to '/oradata/datafile/undotbs01.dbf';set newname for datafile 4 to '/oradata/datafile/undotbs02.dbf';set newname for datafile 5 to '/oradata/datafile/users01.dbf';set newname for datafile 6 to '/oradata/datafile/test01.dbf';set newname for tempfile 1 to '/oradata/tempfile/temp01.dbf';restore database;switch datafile all;switch tempfile all;
}recover database;
如果恢复中有差异可以从主库中复制归档日志到备库并注册：
$su – grid
$asmcmd
ASMCMD> cp +ARCDG/orcl/AR*/2021_04_18/thread_* /oracle/backup
$scp /oracle/backup/thread_* oracle@10.10.10.191:/oradata/arch
注册归档日志并恢复数据库：
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3741.2001.973785643';
...
SQL> recover managed standby database disconnect from session;
SQL> recover managed standby database cancel;
10. 在主库中建立 Standby Logfile
RAC 每个 Redo Thread 都需要创建对应的 Standby Redo Log。 创建原则和单实例一样，包括日志文件大小相等，日志组数量要多 1 组。
-- 主库查询日志文件大小及路径
SQL> set lines 120
SQL> col member for a50
SQL> select a.thread#,
a.group#,a.bytes/1024/1024 size(MB),b.member
from v$log a,v$logfile b where a.group#=b.group#;
在rac节点一或节点二上执行：
SQL> alter database add standby logfile thread 1 group 11('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 1 group 12('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 1 group 13('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 1 group 14('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 1 group 15('+ARCDG', '+DATADG') size 512M;
...
SQL> alter database add standby logfile thread 2 group 16('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 2 group 17('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 2 group 18('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 2 group 19('+ARCDG', '+DATADG') size 512M;
SQL> alter database add standby logfile thread 2 group 20('+ARCDG', '+DATADG') size 512M;
...
11. 在备库中建立 Standby Logfile
alter database add standby logfile group 11 ('/oradata/onlinelog/standby_redo11a.dbf', '/oradata/onlinelog/standby_redo11b.dbf') size 512m;
alter database add standby logfile group 12 ('/oradata/onlinelog/standby_redo12a.dbf', '/oradata/onlinelog/standby_redo12b.dbf') size 512m;
alter database add standby logfile group 13 ('/oradata/onlinelog/standby_redo13a.dbf', '/oradata/onlinelog/standby_redo13b.dbf') size 512m;
alter database add standby logfile group 14 ('/oradata/onlinelog/standby_redo14a.dbf', '/oradata/onlinelog/standby_redo14b.dbf') size 512m;
alter database add standby logfile group 15 ('/oradata/onlinelog/standby_redo15a.dbf', '/oradata/onlinelog/standby_redo15b.dbf') size 512m;
...
12. 备库打开数据库并实时应用 Redo
SQL> alter database open;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLYSQL> alter database recover managed standby database using current logfile disconnect from session;
二、完成后验证
主库检查
SQL> select thread#, sequence#, status from v$log;
SQL> select process, sequence#, status from v$managed_standby;
备库检查
SQL> SELECT PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
验证同步
-- rac节点1 执行
SQL> create table test1 as select * from dba_users;
Table created.SQL> alter system switch logfile;
System altered.-- 节点2 执行
SQL> create table test2 as select * from dba_users;
Table created.SQL> alter system switch logfile;
System altered.-- 备库查询
SQL> select count(*) from test1;COUNT(*)
----------31SQL> select count(*) from test2; COUNT(*)
----------31SQL> select THREAD#,sequence#,applied from v$archived_log order by 1,2;THREAD# SEQUENCE# APPLIED
---------- ---------- ---------1 72 NO1 73 YES1 74 YES2 56 YES2 57 NO
三、问题汇总
3.1 备库未打补丁处理
- 传递与主库一致的补丁。
- 关闭所有 Oracle 相关进程。
- 应用补丁。
3.2 Temp 文件错误处理
- 恢复时
set newname路径错误。 - 将 tempfile 下线并新建正确路径的 tempfile。
- 删除错误的 tempfile。
3.3 归档删除脚本部署
- 创建归档删除脚本并定期自动删除归档文件。
总结
至此，Oracle RAC集群到单机的dataguard搭建完成。
![[2024年3月10日]第15届蓝桥杯青少组stema选拔赛C++中高级（第二子卷、编程题（2））](https://i-blog.csdnimg.cn/direct/bdd46687e4094e809431e3956c9e6aa6.png)
