1. 环境准备
主库 | 备库 | |
---|---|---|
操作系统 | Linux | Linux |
服务器名称 | primarynode | standbynode |
IP地址规划 | 192.168.186.130 | 192.168.186.131 |
--------------- | ----------------------- | ---------------- |
数据库版本 | 11.2.0.4 | 11.2.0.4 |
db_name | sfxt | sfxt |
db_unique_name | sfxt_bf | sfxt_bf |
instance_name | sfxt | sfxt_bf |
service_name | sfxt | sfxt |
数据库安装 | 安装数据库软件+创建监听+安装数据库 | 安装数据库软件+创建监听 |
2.环境配置
1.修改节点名称:
vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 ::1 localhost localhost.localdomain localhost6 192.168.186.130 primarynode 192.168.186.131 standbynode
2. 主库环境变量配置
PATH=$PATH:$HOME/bin ORACLE_SID=sfxt; export ORACLE_SID ORACLE_BASE=/data/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH export TEMP=/tmp export TMPDIR=/tmp umask 022 export PATH
3. 从库环境变量配置
PATH=$PATH:$HOME/bin ORACLE_SID=sfxt_bf; export ORACLE_SID ORACLE_BASE=/data/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH export TEMP=/tmp export TMPDIR=/tmp umask 022 export PATH
3. 主库配置
3.1 启用强制记录日志功能
- 查询是否启用强制记录日志
select force_logging from v$database;
- 如果未启用,则使用下面语句来开启强制记录日志
alter database force logging; --强制记录日志
3.2 启用归档
- 查询是否启用归档
archive log list
--或者
select log_mode from v$database;
- 如果未启用归档,可以按照如下步骤开启归档
[](javascript:void(0)😉
----Oracle 11g数据库归档模式开启
--STEP1:以sysdba角色登陆数据库
sqlplus / as sysdba
--STEP2:干净的关闭数据库
shutdown immediate
--STEP3:将数据库启动到mount状态
startup mount
--STEP4:开启归档
alter database archivelog;
--STEP5:打开数据库
alter database open
[](javascript:void(0)😉
3.3 主库参数配置
- db_unique_name
SQL> alter system set db_unique_name = 'sfxt' scope=spfile;
- log_archive_config
SQL> alter system set log_archive_config='DG_CONFIG=(sfxt,sfxt_bf)' scope=spfile;
- log_archive_dest_1
SQL> alter system set log_archive_dest_1='LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sfxt' scope=both;
- log_archive_dest_2
SQL> alter system set log_archive_dest_2='SERVICE=tnssfxt_bf LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sfxt_bf' scope=spfile;
- log_archive_dest_state_1
alter system set log_archive_dest_state_1 = ENABLE;
- log_archive_dest_state_2
alter system set log_archive_dest_state_2 = ENABLE;
- db_file_name_convert 查看数据文件的位置:
SQL> select name from v$datafile;
- 如果主库与备库数据文件位置不相同,则需要使用db_file_name_convert来转换。
SQL> alter system set db_file_name_convert='/data/oracle/oradata/sfxt_bf','/data/oracle/oradata/sfxt' scope=spfile;
- log_file_name_convert 查看在线日志文件的位置:
SQL> select member from v$logfile;
- 如果主库与备库在线日志文件位置不相同,则需要使用log_file_name_convert来转换。
SQL> alter system set log_file_name_convert='/data/oracle/oradata/sfxt_bf','/data/oracle/oradata/sfxt' scope=spfile;
- standby_file_management
SQL> alter system set standby_file_management=auto scope=spfile;
- fal_client
SQL> alter system set fal_client='tnssfx' scope=both;
- fal_server
SQL> alter system set fal_server='tnssfxt_bf' scope=both;
3.4 主库静态监听配置
[oracle@primarynode admin]$ vim listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=sfxt) (SID_NAME = sfxt) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) ) ADR_BASE_LISTENER = /u01/oracle
3.5 主库tnsnames.ora文件配置
vim tnsnames.ora
tnssfxt = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.130)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sfxt) ) ) tnssfxt_bf = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.131)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sfxt) ) )
4. 备库配置
4.1 从主库复制密码文件
登陆到主库,将密码文件复制到备库
[oracle@primarynode ~]$ cd $ORACLE_HOME/dbs [oracle@primarynode dbs]$ scp orapwsfxt 192.168.186.131:/data/oracle/product/11.2.0/db_1/dbs
登陆到备库,修改密码文件的名称
mv orapwsfxt orapwsfxt_bf
4.2 初始化参数文件
-
在主库生成初始化参数文件
SQL> create pfile from spfile;
-
拷贝主库的参数文件到备库并重命名
在主库上执行复制操作
[oracle@primarynode dbs]$ scp initsfxt.ora 192.168.186.131:/data/oracle/product/11.2.0/db_1/dbs
在备库上执行重命名操作
[oracle@standbynode dbs]$ mv initsfxt.ora initsfxt_bf.ora
-
修改备库的参数文件内容
db_name='sfxt' db_unique_name='sfxt_bf' audit_file_dest='/data/oracle/admin/sfxt_bf/adump' compatible='11.2.0.4.0' control_files='/data/oracle/oradata/sfxt_bf/control01.ctl','/data/oracle/flash_recovery_area/sfxt_bf/control02.ctl' log_archive_config='DG_CONFIG=(sfxt,sfxt_bf)' log_archive_dest_1='LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sfxt_bf' log_archive_dest_2='SERVICE=tnssfxt LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sfxt' db_file_name_convert='/data/oracle/oradata/sfxt','/data/oracle/oradata/sfxt_bf' log_file_name_convert='/u01/app/oracle/oradata/sfxt','/u01/app/oracle/oradata/sfxt_bf' fal_client='tnssfxt_bf' fal_server='tnssfxt' standby_file_management='AUTO'
-
新建需要的目录
mkdir -p /data/oracle/admin/sfxt_bf/adump mkdir -p /data/oracle/flash_recovery_area/sfxt_bf
-
将控制文件复制过来
主库创建控制文件
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'sfxt_bf.ctl';
复制到从库中
scp oracle@192.168.186.130:/data/oracle/product/11.2.0/db_1/dbs/sfxt_bf.ctl /data/oracle/oradata/sfxt_bf/control01.ctl
4.3 使用pfile文件创建spfile文件
登陆到idle数据库
sqlplus / as sysdba
创建spfile
create spfile from pfile
启动数据库到nomount状态
startup nomount
4.4 配置静态监听
[oracle@standbynode admin]$ vim listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=sfxt) (SID_NAME = sfxt_bf) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) ) ADR_BASE_LISTENER = /data/oracle
4.5 配置tnsnames.ora文件,直接把主库的拷贝过来即可
4.6 测试网络连通性
在主库与备库上均执行,确保可以正常访问
tnsping tnssfxt tnsping tnssfxt_bf
在主库:
sqlplus sys/sys123@tnssfxt as sysdba sqlplus sys/sys123@tnssfxt_bf as sysdba
4.7 参数检查(可选)
1.db_unique_name :2个节点需要不一样 2.compatible :主库与备库兼容性需一致 3.log_archive_config : 配置主库与备库的db_unique_name 4.log_archive_dest_1,2 :归档日志的路径 5.log_archive_dest_state_2 : enable -- 启用log_archive_dest_2 defer --禁用log_archive_dest_2 6.db_file_name_convert :数据文件转换路径 7.log_file_name_convert :日志文件转换路径 8.standby_file_management :设置为auto 9.log_archive_format :日志文件格式,两边需一致
5. 使用duplicate创建物理standby
5.1 连接到主库和备库
备库端执行:
## 一定要加nocatalog,否则在执行duplicate时会报错 [oracle@primarynode ~]$ rman target sys/sys123@sfxt auxiliary sys/sys123@sfxt_bf nocatalog
5.2 使用duplicate复制数据库
RMAN> duplicate target database for standby from active database nofilenamecheck;
6. 添加主库和备库的standby日志组
- standby日志组个数:配置为redo日志组个数+1
- 在主库与备库都添加standby日志组。主库可以不添加,但是如果后期发生主备切换,还是要添加,所以最好一次性添加。
- 只查询standby日志组: select * from v$standby_log ;
6.1 添加主库的standby日志组
alter database add standby logfile group 4 ('/data/oracle/oradata/sfxt/stredo04.log') size 50M;
alter database add standby logfile group 5 ('/data/oracle/oradata/sfxt/stredo05.log') size 50M;
alter database add standby logfile group 6 ('/data/oracle/oradata/sfxt/stredo06.log') size 50M;
alter database add standby logfile group 7 ('/data/oracle/oradata/sfxt/stredo07.log') size 50M;
6.2 添加备库的standby日志组
alter database add standby logfile group 4 ('/data/oracle/oradata/sfxt_bf/stredo04.log') size 50M;
alter database add standby logfile group 5 ('/data/oracle/oradata/sfxt_bf/stredo05.log') size 50M;
alter database add standby logfile group 6 ('/data/oracle/oradata/sfxt_bf/stredo06.log') size 50M;
alter database add standby logfile group 7 ('/data/oracle/oradata/sfxt_bf/stredo07.log') size 50M;
7. 开始同步数据库(在备库上执行)
alter database open;
--方式一:开启实时同步
alter database recover managed standby database using current logfile disconnect from session;
--或简写为:
alter database recover managed standby database using current logfile disconnect;
--方式二:开启同步(日志切换时才同步)
alter database recover managed standby database disconnect from session;
8.检查是否执行成功
8.1 主库状态查看
SQL> select open_mode, --数据库打开模式,如果实时同步,则为:read only with apply,取消同步则为:read only
database_role, --数据库角色,是主库还是备库
protection_mode, --保护模式
protection_level --保护级别
from v$database;
SQL> select open_mode,database_role,protection_mode,protection_level from v$database;
OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
8.2 备库状态查看
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