dataguard配置及数据测试

最新推荐文章于 2021-09-20 17:03:12 发布

珠穆朗玛峰上的白莲花

最新推荐文章于 2021-09-20 17:03:12 发布

阅读量681

点赞数

分类专栏： DataGuard 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/weixin_41761624/article/details/108486166

版权

DataGuard 专栏收录该内容

1 篇文章

订阅专栏

1、环境

1.1 基本配置

CentOS release 6.9 (Final)

oracle 11.2.0.4

主库	备库
主机名db1	主机名db2
192.168.84.13	192.168.84.12
ORACLE_SID=icdc	ORACLE_SID=aux1
ORACLE_BASE=/opt/app/oracle	ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1	ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1

1.2 准备就绪

1、主库：安装数据库软件，安装dbca，安装netca

2、从库：安装数据库软件，不安装dbca，安装netca

2、安装dataguard

2.1 主库打开强制日志模式

select force_logging from v$database;

alter database force logging;

2.2 主库要打开归档模式

archive log list;

如果没有打开，执行以下操作：

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2.3 修改主库的本地归档路径

mkdir -p /home/oracle/arc_icdc_dest1

alter system set log_archive_dest_1='location=/home/oracle/arc_icdc_dest1/ valid_for=(online_logfiles,primary_role) db_unique_name=icdc';

激活主库的本地归档路径

alter system set log_archive_dest_state_1='enable';

2.4 主库要启用远程归档路径

从库的唯一服务名：aux1srv

从库的实例名（SID）:aux1

alter system set log_archive_dest_2='service=aux1srv valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

激活远程归档路径

alter system set log_archive_dest_state_2='enable';

2.5 主库要打开dataguard开关

alter system set log_archive_config='dg_config=(icdc,aux1)';

2.6 为从库准备口令文件

[oracle@db1dbs]$scp orapwicdc root@192.168.84.12:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwaux1

[root@db2 ~]# chown -R oracle.oinstall /opt/app/oracle/product/11.2.0/dbhome_1/dbs/

2.7 为从库准备参数文件

查看主库的数据文件路径：SQL> select name from v$datafile;

将以下文件保存在新建的从库里边

[oracle@db2 dbs]$ vim initaux1.ora

*.audit_file_dest='/opt/app/oracle/admin/aux1/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/home/oracle/oradata/aux1/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='icdc' #主备都不变的名字

*.db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/opt/app/oracle'

*.log_archive_config='dg_config=(icdc,aux1)'

*.log_archive_max_processes=5

*.memory_target=1560281088

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

db_unique_name='aux1'

log_archive_dest_3='location=/home/oracle/arc_aux1_dest3/ valid_for=(standby_logfiles,standby_role) db_unique_name=aux1'

log_archive_dest_state_3='enable'

standby_file_management=auto

db_file_name_convert='/home/oracle/oradata/icdc','/home/oracle/oradata/aux1'

log_file_name_convert='/home/oracle/oradata/icdc','/home/oracle/oradata/aux1'

2.8 在从库中准备相关的目录

mkdir -p /opt/app/oracle/admin/aux1/adump

mkdir -p /home/oracle/oradata/aux1

mkdir -p /opt/app/oracle/fast_recovery_area

mkdir -p /home/oracle/arc_aux1_dest3

2.9 在从库创建spfile,启动实例到nomount

[oracle@db2 dbs]$ export ORACLE_SID=aux1

[oracle@db2 dbs]$ sqlplus / as sysdba

SQL> startup nomount

2.10 监听程序配置并重启

从库配置监听程序

[oracle@db2 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = aux1.example.com)

(ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = aux1)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.84.12)(PORT = 1521))

)

主库配置监听程序

[oracle@db1 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = icdc.example.com)

(ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = icdc)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.84.13)(PORT = 1521))

)

[oracle@db1 admin]$ cat tnsnames.ora

ICDC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = icdc)

)

aux1srv =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.84.12)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = aux1.example.com)

)

2.11 在主库测试监听配置

[oracle@db1 ~]$ sqlplus sys/hongjun123@aux1srv as sysdba

SQL> show parameter name

2.12 在主库启动rman复制从库

从库的由来：它是由rman复制主库出来的，不是自己创建的

rman target / auxiliary sys/hongjun123@aux1srv

RMAN> duplicate target database for standby from active database;

结束之后:

2.13 查看当前从库状态

SQL> select status from v$instance;

STATUS

------------

MOUNTED

2.14 在主从库都添加standby log

SQL> select * from v$logfile;

SQL> select * from v$log;

根据以上连个命令确定log日志的路径及大小，必须要和主库一样大

alter database add standby logfile '/home/oracle/oradata/aux1/redo04.log' size 52428800;

alter database add standby logfile '/home/oracle/oradata/aux1/redo05.log' size 52428800;

alter database add standby logfile '/home/oracle/oradata/aux1/redo06.log' size 52428800;

2.15 在主库切换日志

alter system switch logfile;

启动管理恢复后再检查

select sequence#,applied from v$archived_log;

2.16 在从库启动和关闭管理恢复

启动：

实时同步数据

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

延迟数据同步

alter database recover managed standby database disconnect from session;

关闭：

SQL> alter database recover managed standby database cancel;

2.17 从库的最终状态

Mount状态，启动管理恢复

2.18 简单测试主从是否成功

最简单的方法，备库查看最新的归档日志是否apply，如果applied是yes则正常同步
主库sqlplus>alter system switch logfile;
备库sqlplus>select SEQUENCE#,applied,FIRST_TIME,NEXT_TIME from v$archived_log order by 1 desc;