redhat+ oracle 11.2.0.4 RAC 搭建 dataguard
知识改变命运,技术就是要分享,有问题随时联系,免费答疑,欢迎联系!
redhat+ oracle 11.2.0.4 RAC 搭建 dataguard
1.安装环境
主机名 | OS | DB | SID | db_name | db_unique_name |
rac1 | Redhat7 | 11.2.0.4 | orcl1 | orcl | orcl_rac |
rac1 | Redhat7 | 11.2.0.4 | orcl2 | orcl | orcl_rac |
orcldg | Redhat7 | 11.2.0.4 | orcl | orcl | orcl_dg |
备注:orcl1是一套oracle RAC 集群中的一台服务器
2.安装前配置
主库:192.168.2.15
备库:192.168.2.180
主库:安装oracle 软件,并创建oracle 数据库
备库:仅安装oracle软件
主库:/u01/app/oracle/product/11.2.0.4/db_1
备库:/u01/app/oracle/product/11.2.0.4/dbhome_1
数据库实例规划
主库:
实例名:orcl1数据库名orcl 数据库唯一名:orcl_rac
备库:
实例名:orcl数据库名 orcl 数据库唯一名:orcl_dg
Oracle Dataguard 要求主库和备库的数据库名字一样,即db_name要一样,
使用db_unique_name名字区分
3.安装过程
主库:
Select log_mode from v$database;
主库:
SQL>select force_logging from v$database;
FORCE_
------
NO
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>startup mount;
ORACLE 例程已经启动。
Total System Global Area 1119043584 bytes
Fixed Size????????????????? 2280496 bytes
Variable Size???????????? 738198480 bytes
Database Buffers???????? ?369098752 bytes
Redo Buffers??????????????? 9465856 bytes
数据库装载完毕。
SQL>alter database force logging;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL>select force_logging from v$database;
FORCE_
------
YES
Force_logging 为YES 代表数据库是强制日志模式。
主库:
增加standby 日志组(正常比online 日志多一组)
alter database add standby logfile thread 1 group 5 '+DATA/orcl/onlinelog/group5_01.log' size 50M;
alter database add standby logfile thread 1 group 6 '+DATA/orcl/onlinelog/group6_01.log' size 50M;
alter database add standby logfile thread 1 group 7 '+DATA/orcl/onlinelog/group7_01.log' size 50M;
alter database add standby logfile thread 2 group 8 '+DATA/orcl/onlinelog/group8_01.log' size 50M;
alter database add standby logfile thread 2 group 9 '+DATA/orcl/onlinelog/group9_01.log' size 50M;
alter database add standby logfile thread 2 group 10 '+DATA/orcl/onlinelog/group10_01.log' size 50M;
--查询standby log 命令
SELECT bytes,GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
--删除standby 的命令
alter database drop standby logfile group 4;
使用netca配置监听。
下图是监听配置后的结果,监听正常。
主库:RAC 不用单独配置监听
备库:配置DG的时候,备份的数据库还无法open,所有要配置静态监听
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
? (SID_LIST =
??? (SID_DESC =
????? (GLOBAL_DBNAME = orcl_dg)
????? (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
????? (SID_NAME = orcl)
??? )
? )
LISTENER =
? (DESCRIPTION_LIST =
??? (DESCRIPTION =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.180)(PORT = 1521))
??? )
??? (DESCRIPTION =
????? (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
??? )
? )
ADR_BASE_LISTENER = /u01/app/oracle/product/11.2.0.4/dbhome_1
启动监听:lsnrctl start
ORCL_PRI ?为主库的连接信息,orcl_std为备库的连接信息
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
主库:
ORCL_PRI ?=
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.15)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVICE_NAME = orcl_rac)
??? )
? )
ORCL_STD =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.180)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVICE_NAME = orcl_dg)
??? )
? )
备库:
ORCL_PRI ?=
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.15)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVICE_NAME = orcl_rac)
??? )
? )
ORCL_STD =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.180)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVICE_NAME = orcl_dg)
??? )
? )
确认主库和备库是否联通
主备库:tnsping ORCL_PRI
??????? tnsping ORCL_STD
主库:
创建pfile ,修改pfile,生成spfile,并以spfile 启动数据库
alter system set? DB_UNIQUE_NAME=orcl_rac scope=spfile sid='*';
alter system set? LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_rac,orcl_dg)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST? VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_rac' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl_std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_dg' scope=both sid='*';
#standby
alter system set FAL_SERVER=orcl_std scope=both sid='*';
alter system set fal_client=orcl1 scope=both sid='orcl1';
alter system set fal_client=orcl2 scope=both sid='orcl2';
alter system set DB_FILE_NAME_CONVERT='/data/oradata/','+DATA/orcl/datafile/','/data/oradata/', '+DATA/orcl/tempfile/' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/data/oradata/','+DATA/orcl/onlinelog/' scope=spfile sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
备库:
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl'
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.db_recovery_file_dest='/data/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=462107443200
*.undo_tablespace='UNDOTBS1'
*.open_cursors=300
*.pga_aggregate_target=200M
*.processes=1500
*.sessions=1655
*.sga_target=420M
*.db_unique_name='orcl_dg'
*.log_archive_config='DG_CONFIG=(orcl_dg,orcl_rac)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_dg'
*.log_archive_dest_2='SERVICE=orcl_pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_pri'
*.log_file_name_convert='+DATA/orcl/onlinelog/','/data/oradata/'
*.db_file_name_convert='+DATA/orcl/datafile/','/data/oradata/','+DATA/orcl/tempfile/','/data/oradata/'
*.fal_client='ORCL_STD'
*.fal_server='ORCL1,ORCL2'
*.standby_file_management='AUTO'
创建相应的路径:
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /data/oradata/
mkdir -p /u01/app/oracle
mkdir -p /data/oradata/fast_recovery_area
将主库A的密码文件复制到备库和 主库B
scp orapworcl1 192.168.2.180:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
mv orapworcl1 orapworcl
scp orapworcl1 192.168.2.14:/u01/app/oracle/product/11.2.0.4/db_1/dbs
export ORACLE_SID=orcl
Sql> create spfile from pfile='/tmp/pfile.txt';
主库:使用RMAN 复制主库的控制文件和数据文件到备库
rman target sys/oracle@orcl_pri auxiliary sys/oracle@orcl_std
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
备库:
Alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
4.同步数据测试
主库:创建表
SQL> Create table t_test as select * from dba_users;
Table created.
SQL> Select count(1) from t_test;
? COUNT(1)
----------
?????? 35
备库:
SQL> Select count(1) from t_test;
? COUNT(1)
----------
?????? 35
主库的数据已经同步到备库。
切换测试
主库:
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
alter?database?commit?to?switchover?to?physical?standby?WITH?SESSION?SHUTDOWN;
startup mount ;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
备库:
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
shutdown immediate;
startup mount;
将数据库切换为主库:
alter?database?commit?to?switchover?to?primary;
查看数据库角色:
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
alter database open;
主库:
备库:
数据正常同步
6.DG常用管理命令
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;
-----由shutdown模式切换到只读模式-------
startup nomount;
alter database mount standby database;
alter database open read only;
-----由应用日志模式切换到只读模式-------
alter database recover managed standby database cancel;?-- 取消日志应用
alter database open read only;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;?-- 启动归档日志应用
or
alter database recover managed standby database using current logfile disconnect from session; -- 启动实时日志应用
6.4.1 主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup ;
alter database recover managed standby database disconnect from session;
or
alter database recover managed standby database using current logfile disconnect from session;
6.4.2 从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup;
alter system switch logfile;
alter database recover automatic standby database;
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
set linesize 200
col name format a100
select sequence#,name,applied from v$archived_log;
select database_role, protection_mode, protection_level from v$database;
常用切换步骤
主库和备库之间角色切换
主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup ;
alter database recover managed standby database disconnect from session;
or
alter database recover managed standby database using current logfile disconnect from session;
从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup;
alter system switch logfile;
停止应用恢复模式
alter database recover managed standby database finish;
转换standbydb为primary db
alter database commit to switchover to primary;
重启数据库,恢复正常业务
shutdown immediate
startup
1:备库停止日志应用
alter database recover managed standby database cancel;
2:主库重启到mount
shutdown immediate;
startup mount;
alter system set log_archive_dest_2='SERVICE=orcl_dgtd lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl_dgtandby'
3:主库,备库操作更改为最大保护
alter database set standby database to maximize PROTECTION;
alter database open;
4:备库开始应用日志
alter database recover managed standby database using current logfile disconnect from session;
1:备库取消日志应用
备库:
alter database recover managed standby database cancel;
2:主库调整模式
主库:
alter database set standby database to maximize performance;
3:备库开始日志应用
alter database recover managed standby database using current logfile disconnect from session;
4:查看数据库角色(主备)
select database_role, protection_mode, protection_level from v$database;
1:备库取消日志应用
备库:
alter database recover managed standby database cancel;
2:主库调整模式
主库:
alter database set standby database to maximize availability;
3:备库开始日志应用
alter database recover managed standby database using current logfile disconnect from session;
4:查看数据库角色(主备)
select database_role, protection_mode, protection_level from v$database;
1:备库停止日志应用
alter database recover managed standby database cancel;
2:主库,备库操作更改为最大保护
alter database set standby database to maximize PROTECTION;
3:备库开始应用日志
alter database recover managed standby database using current logfile disconnect from session;
联系我们
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!