暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

ADG干货

原创 刘宁 2021-11-04
2113

ORACLE 11G DATAGUARD 搭建(RMAN duplicate方式相同目录结构)


1.准备ORACLE主、备库环境

本次使用

ORACLE 11.2.0.4 数据库软件

RHEL rhel6.2 操作系统

1.1修改主、备库hosts文件

vi /etc/hosts

172.20.0.7 liu
172.20.0.8 liudg

1.2环境变量

主库环境变量

vi .bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LANG=en_US; export LANG
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

备库环境变量

vi .bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_SID=orcl; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LANG=en_US; export LANG
ORACLE_UNQNAME=orcldg; export ORACLE_UNQNAME
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

1.3主备库环境准备

主库安装数据库软件、建库、建监听
./runInstaller ****安装数据库软件****
NETCA ****建监听****
DBCA ****建库****
备库安装数据库软件、建监听 ./runInstaller ****安装数据库软件**** NETCA ****建监听****

2.进行dataguard配置

2.1主库开启归档

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;

2.2主库开启强制日志

alter database force logging

(在mount模式下执行,效率更快,可以开启归档时执行)

2.3主库开启flashback日志

select flashback_on from v$database;
Alter database flashback on;
alter database open;

2.4修改tnsnames.ora 文件

cd $ORACLE_HOME/network/admin

***主备同步需要***

ORCL =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.7)(PORT = 1521))
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcl)
    )
)
ORCLDG =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.8)(PORT = 1521))
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcl)
    )
)

2.5修改监听为静态注册

主库:

SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (SID_NAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.7)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)
ADR_BASE_LISTENER = /u01/app/oracle

备库:
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (SID_NAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
        )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.8)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)
ADR_BASE_LISTENER = /u01/app/oracle

重启主备监听:

lsnrctl stop
lsnrctl start
主备检测tnsnames
tnsping orcldg
tnsping orcl

2.6.修改主库参数,增加standby 联机日志

sqlplus / as sysdba

增加以下内容

alter system set DB_UNIQUE_NAME='orcl' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;
alter system set log_archive_dest_2='SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=spfile;
alter system set log_archive_dest_state_1='enable' scope=spfile;
alter system set log_archive_dest_state_2='enable' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCL/datafile','/u01/app/oracle/oradata/ORCLDG/datafile';
alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCL/onlinelog/','/u01/app/oracle/oradata/ORCLDG/onlinelog/';
alter system set fal_server='orcldg';
alter system set fal_client='orcl';
alter system set standby_file_management='AUTO' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo01.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo03.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo04.log' size 50M;
重启主库
shutdown immediate;
startup;

2.7生成主库pfile文件

create pfile='/home/oracle/pfile.ora' from spfile;

2.8传输相应文件到备库

1) 主库密码文件

scp -r $ORACLE_HOME/dbs/orapworcl liudg:$ORACLE_HOME/dbs/ 

2) 主库pfile文件

scp -r /home/oracle/pfile.ora liudg:/home/oracle/

2.9修改备库pfile文件

(:orclorcldg互换即可/log_file_name_convertdb_file_name_convert无需变化)

3.rman duplication方式恢复备库

3.1创建备库相应目录结构

mkdir -p /u01/app/oracle/admin/orcldg/adump
mkdir -p /u01/app/oracle/oradata/ORCLDG/controlfile/
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDG/controlfile/
mkdir -p /u01/app/oracle/diag/rdbms/orcldg/orcl/cdump
mkdir -p /u01/app/oracle/oradata/ORCLDG/datafile
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/oradata/ORCLDG/onlinelog/standby
(注:据当前环境目录修改)

3.2启动备库到nomount状态

[oracle@orcldg ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
SQL>

3.3主库连接辅助库

[oracle@liu ~]$ rman target sys/ln202902@orcl auxiliary sys/ln202902@orcldg
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 10 14:32:33 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1615274987)
connected to auxiliary database: ORCL (not mounted)
RMAN>

3.4duplicate复制数据库

RMAN> duplicate target database for standby nofilenamecheck from active database;
nofilenamecheck: 相同目录结构使用此参数,结构不同使用该参数避免目录检查。
RMAN> duplicate target database for standby from active database;

4.备库开启standby实时恢复

SQL>alter database recover managed standby database disconnect from session using current logfile;

6.primary库归档,查看dataguard数据同步是否成功

SQL> select name,archived,applied,sequence# from v$archived_log;
SQL> alter system archive log current;
SQL> select name,archived,applied,sequence# from v$archived_log;
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;

7.启停

7.1关闭

先主库,后备库。
主库执行: shutdown immediate; lsnrctl stop 备库执行: alter database recover managed standby database cancel; 关闭实时同步 shutdown immediate; lsnrctl stop

7.2启动

先备库,后主库。
备库执行: lsnrctl start Startup nomount; alter database mount standby database; alter database recover managed standby database using current logfile disconnect from session; 主库执行: lsnrctl start startup

8. 保护性切换

1.主库启动到mount状态
2.主库中执行如下SQL语句
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
3.查询保护模式语句
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

9. 主备切换

 
主库:
SQL> select switchover_status from v$database;
1. 如果switchover_status为TO_STANDBY说明可以转换
直接转换:
alter database commit to switchover to physical standby;
2. 如果switchover_status为SESSIONS ACTIVE 则关闭会话
SQL>alter database commit to switchover to physical standby with session shutdown;
3. startup #启动
4. alter database recover managed standby database using current logfile disconnect from session; #启同步
5.select name,open_mode,switchover_status,database_role from v$database; #查看状态

备库:
SQL> select switchover_status from v$database;
1. 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库
SQL>alter database commit to switchover to primary
2. 如果switchover_status为SESSION ACTIVE 就应该断开活动会话
SQL>alter database commit to switchover to primary with session shutdown;
3. 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能,检查主库
4.alter database open;
5.select name,open_mode,switchover_status,database_role from v$database; 

10. 灾难恢复

手动

 
旧备库切新主库:
alter database recover managed standby database finish force;
alter database commit to switchover to primary with session shutdown;
alter database open;
select name,log_mode ,open_mode ,database_role ,protection_mode ,switchover_status from v$database;
SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; #查询进主库的SCN
新主库切旧备库: shutdown immediate startup mount flashback database to scn 新主库SCN; ALTER DATABASE CONVERT TO PHYSICAL STANDBY; #转换physical standby database shutdown immediate; startup; select name,open_mode,switchover_status,database_role from v$database; #查看当前状态

alter database recovr managed standby database using current logfile disconnect from session; #开启MRP
select name,open_mode,switchover_status,database_role from v$database;


检查:
archive log list;
此刻是主备正常的
旧主库恢复后,就可以将角色转回
新主库状态:


alter database commit to switchover to pysical standby; #转回备库
startup;
select name,open_mode,switchover_status,database_role from v$database;
新备库状态:

此刻
alter database commit to switchover to primary with session shutdown ;
alter database open;

OK

broker

1.主备库listener.ora静态注册中添加如下:

 
SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (SID_NAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
)
    (SID_DESC =
    (GLOBAL_DBNAME = orcl_DGMGRL) #DB_UNIQUE_NAME.DGMGRL
        (SID_NAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
    )
)

2.开启broker

show parameter dg_broker_start;

alter system set dg_broker_start=true scope=spfile;

3. dgmgrl


到这儿就算是成功了,然后现在试试一条命令的转换主备

接下来要配置成功fast_start failover 需要满足以下5项条件.

1. dataguard 的配置要么是maxAvailability模式要么是maxPerformance模式.

2.dataguard的配置为maxAvailability模式时,fast-start failover的目标standby数据库的log传送模式必须设置为Sync.

3. dataguard的配置为maxPerformance模式时,fast-start failover的目标standby数据库的log传送模式必须设置为Async.

4. 主库与fast-start failover的目标standby数据库都必须激活flashback功能.

5. 当配置了多standby数据库时,在主库的配置属性FastStartFailoverTarget指定目标standby 数据库.

操作:略

配置完毕后

开启observer

DGMGRL> start observer(nohup dgmgrl -silent sys/oracle@orcl "start observer" &)

DGMGRL> enable fast_start failover

ok成功

最后修改时间:2021-12-06 14:49:03
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论