ADG干货

原创刘宁 2021-11-04

2319

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文件

(注:orcl和orcldg互换即可/log_file_name_convert、db_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;

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

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者