一.前提条件
1.数据库已经安装完
2.主备库的数据库版本一致
3.主库一节点配置
[root@node1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Use for oracle11g RAC
#Public ip
192.168.6.2 node1
192.168.6.3 node2
#Virtual ip
192.168.6.4 node1-vip
192.168.6.5 node2-vip
#Private ip
172.16.6.2 node1-priv
172.16.6.3 node2-priv
#Scan ip
192.168.6.6 scan-ip
主库二节点配置
[root@node2 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Use for oracle11g RAC
#Public ip
192.168.6.2 node1
192.168.6.3 node2
#Virtual ip
192.168.6.4 node1-vip
192.168.6.5 node2-vip
#Private ip
172.16.6.2 node1-priv
172.16.6.3 node2-priv
#Scan ip
192.168.6.6 scan-ip
备库配置
[oracle@node3 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.6.7 node3 oracle
二.主库开启数据库归档
startup mount
alter database archivelog;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_dest_1='LOCATION=+fra_arc' scope=spfile sid='*';
alter database open;
三.主库启用强制日志模式
alter database force logging;
四.主库添加standby_log;
1.先查看本地日志组
SYS@orcl1> select thread#,group#,members,bytes/1024/1024 from v$log;
THREAD# GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------- ---------------
1 1 2 50
1 2 2 50
2 3 2 50
2 4 2 50
2.主库添加6组standby log文件（一般比主库多一组）
SYS@orcl1> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m;
SYS@orcl1> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;
3.主库查看添加的日志组
SYS@orcl1> select group#,thread#,bytes/1024/1024,status from v$standby_log;
GROUP# THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
5 1 50 UNASSIGNED
6 1 50 UNASSIGNED
7 1 50 UNASSIGNED
8 2 50 UNASSIGNED
9 2 50 UNASSIGNED
10 2 50 UNASSIGNED
五.修改主库参数
1.修改主库参数
SYS@orcl1> alter system set log_archive_config='DG_CONFIG=(orcl,orclstd)' scope=both sid='*';
SYS@orcl1> alter system set log_archive_dest_1='LOCATION=+fra_arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';
SYS@orcl1> alter system set log_archive_dest_2='SERVICE=orclstd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd' scope=both sid='*';
SYS@orcl1> alter system set log_archive_dest_state_1=enable scope=both sid='*';
SYS@orcl1> alter system set log_archive_dest_state_2=enable scope=both sid='*';
SYS@orcl1> alter system set db_file_name_convert='/u02/oradata/orclstd','+DATA/orcl/datafile','/u02/oradata/orclstd','+DATA/orcl/tempfile' scope=spfile sid='*';
SYS@orcl1> alter system set log_file_name_convert='/u02/oradata/orclstd','+DATA/orcl/onlinelog','/u02/oradata/orclstd','+fra_arc/orcl/onlinelog' scope=spfile sid='*';
SYS@orcl1> alter system set standby_file_management=AUTO scope=both sid='*';
SYS@orcl1> alter system set fal_server='orclstd' scope=both sid='*';
2.查看参数
SYS@orcl1> select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');
NAME VALUE
db_file_name_convert /u02/oradata/orclstd, +DATA/orcl/datafile, /u02/oradata/orclstd, +DATA/orcl/tempfile
log_file_name_convert /u02/oradata/orclstd, +DATA/orcl/onlinelog, /u02/oradata/orclstd, +FRA_ARC/orcl/onlinelog
log_archive_dest_1 LOCATION=+FRA_ARC/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl
log_archive_dest_2 SERVICE=orclstd LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_server orclstd
log_archive_config DG_CONFIG=(orcl,orclstd)
log_archive_format %t_%s_%r.arc
log_archive_max_processes 8
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name orcl
db_unique_name ORCL
六.主库配置监听
1.修改hosts文件
一节点
[root@node1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Use for oracle11g RAC
#Public ip
192.168.6.2 node1
192.168.6.3 node2
#Virtual ip
192.168.6.4 node1-vip
192.168.6.5 node2-vip
#Private ip
172.16.6.2 node1-priv
172.16.6.3 node2-priv
#Scan ip
192.168.6.6 scan-ip
#添加备库解析
192.168.6.7 node3 oracle
二节点
[root@node2 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Use for oracle11g RAC
#Public ip
192.168.6.2 node1
192.168.6.3 node2
#Virtual ip
192.168.6.4 node1-vip
192.168.6.5 node2-vip
#Private ip
172.16.6.2 node1-priv
172.16.6.3 node2-priv
#Scan ip
192.168.6.6 scan-ip
#添加备库解析
192.168.6.7 node3 oracle
一节点
grid用户下操作
[grid@node1 ~]$ vim $ORACLE_HOME/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
#添加如下配置信息，ORACLE_HOME指的是oracle用户下的ORACLE_HOME绝对路径
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl1)
)
)
oracle用户下操作
[oracle@node1 ~] vim $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#添加备库解析信息
orclstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
二节点
grid用户下操作
[grid@node1 ~]$ vim $ORACLE_HOME/network/admin/listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
#添加如下配置信息，ORACLE_HOME指的是oracle用户下的ORACLE_HOME绝对路径
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl2)
)
)
oracle用户下操作
[oracle@node2 ~] vim $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#添加备库解析信息
orclstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
八.主库密码拷贝
服务器双节点密码拷贝
scp orapworcl1 node2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
scp orapworcl2 node1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
九.修改备库host文件
[oracle@node3 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.6.7 node3 oracle
192.168.6.6 scan-ip oracle
十.配置备库监听,并启动监听
1.编辑listener.ota
[oracle@node3 admin]$ vim listener.ota
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orclstd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
2.编辑tnsnames.ora
[oracle@node3 admin]$ vim tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orclstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
3.启动监听
lsnrctl start
十一.配置备库密码文件
1.拷贝主库密码文件到备库
scp orapworcl1 node3:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
2.修改密码文件
mv orapworcl1 orapworcstd
十二.创建备库pfile
vim initorclstd.ora
*.audit_file_dest='/u01/app/oracle/admin/orclstd/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oradata/orclstd/control01.ctl','/u01/app/oracle/fast_recovery_area/orclstd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orclstd'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclstdXDB)'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orclstd,orcl)'
*.log_archive_dest_1='LOCATION=/u02/oradata/orclstd_archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd'
#*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl OPTIONAL PROPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA/orcl/onlinelog','/u02/oradata/orclstd','+FRA_ARC/orcl/onlinelog','/u02/oradata/orclstd'
*.db_file_name_convert='+DATA/orcl/datafile','/u02/oradata/orclstd','+DATA/orcl/tempfile','/u02/oradata/orclstd'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.sga_target=2147483648
*.service_names='orcl'
十三.oracle用户下创建相应的目录
mkdir -p /u01/oracle/app/admin/orclstd/adump
mkdir -p /u02/oradata/orclstd
mkdir -p /u01/app/oracle/fast_recovery_area/orclstd
十四.启动备库到nomount
startup nomount
十五.主库一节点登录rman连接主备库
rman target sys/newcapec@orcl auxiliary sys/newcapec@orclstd
duplicate target database for standby from active database nofilenamecheck;
十六.打开备库
alter database open
十七.修改主备库rman策略
主库：
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION on;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
备库：
CONFIGURE BACKUP OPTIMIZATION on;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
十八.
开启redo应用(实时同步)commit
自Oracle Database 12.1起，USING CURRENT LOGFILE子句已弃用
11.2.0.4
alter database recover managed standby database using current logfile disconnect from session;日志实时应用
alter database recover managed standby database using current logfile disconnect;日志实时应用
12.2.0.1
alter database recover managed standby database disconnect;
十九.添加dg_apply_log.sql
CREATE OR REPLACE TRIGGER dg_apply_log
after startup on database
BEGIN
DECLARE
database_role varchar(20);
BEGIN
select database_role into database_role from v$database;
/* dbms_output.put_line('database_role');*/
IF database_role = 'PHYSICAL STANDBY'
THEN
EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect from session';
DBMS_OUTPUT.put_line('Physical standby database real time apply set complete!');
ELSE
DBMS_OUTPUT.put_line(database_role);
END IF;
END;
END dg_apply_log;
/
最后修改时间：2020-05-29 11:17:13
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
