oracle11gR2Dataguard搭建

页面导航:首页 > 数据库 > oracle > oracle11gR2Dataguard搭建

oracle11gR2Dataguard搭建

来源: 作者: 时间:2016-02-16 09:27 【

数据库版本:Oracle Database 11g Enterprise Edition Release 11 2 0 1 0主库ip:192 168 133 133 instance_name:orcl service_name:pdb 主机名:dg1备库ip:192 168 133 134
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 主库ip:192.168.133.133 instance_name:orcl service_name:pdb 主机名:dg1 备库ip:192.168.133.134 instance_name:orcl service_name:sdb 主机名:dg2 (注意,在上进行操作,网络连接方式最好选择host only,这样内部网络才会稳定,主库备库间的通信才更加正常)

--修改主备库hosts文件: 主库: [[email protected] ~]# cat /etc/hosts 127.0.0.1 dg1 localhost : :1 localhost6.localdomain6 localhost6 192.168.133.133 dg1 192.168.133.134 dg2
备库: [[email protected] ~]# cat /etc/hosts 127.0.0.1 dg2 localhost : :1 localhost6.localdomain6 localhost6 192.168.133.133 dg1 192.168.133.134 dg2

--看看防火墙是否关闭: [[email protected] ~]# /etc/init.d/iptables status
Firewall is stopped.

--搭建DG前的准备工作: 开启logging和archivelog mode SQL>alter database force logging; 检查: SQL>select force_logging from v$database;(应为YES) 检查数据库是否处于归档模式: SQL>select log_mode from v$database; 如果数据库没有处于归档模式,那么将数据库shutdow immediate;重启到mount,执行 SQL>alter database archivelog; SQL>alter database open; SQL>archive log list;

--配置主备库的监听和网络服务名: 主库: listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pdb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)) )
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora: PDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.133)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb) ) )
SDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sdb) ) )
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
备库: listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = sdb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521)) )
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora: PDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.133)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb) ) )
SDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sdb) ) )

--启动主备库的监听: lsnrctl start

--创建备库的口令文件: 如果主库中有就直接使用scp命令拷贝到备库所在服务器相应目录中,如果没有就使用orapwd命令创建:
orapwd file=xxx password=xxx entries=xx

--创建主备库的pfile,添加相应的参数: 主库: SQL>create pfile from spfile; [[email protected] dbs]$ vi initorcl.ora 添加如下内容: *.instance_name=orcl *.db_unique_name=pdb *.log_archive_config='dg_config=(pdb,sdb)' *.fal_server=sdb *.fal_client=pdb *.log_archive_dest_2='service=sdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sdb' *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.STANDBY_FILE_MANAGEMENT=AUTO
如果主备库的数据文件及日志文件不在同一个目录,则需要添加如下两个参数文件,路径为先远程后本地:

*.DB_FILE_NAME_CONVERT='/u01/oradata/sdb/','/u01/oradata/pdb/'

*.LOG_FILE_NAME_CONVERT='/u01/oradata/sdb/','/u01/oradata/pdb/'


备库: 使用scp命令将主库的pfile拷贝到备库所在服务器相同目录下,并修改如下参数: [[email protected] dbs]$ vi initorcl.ora *.instance_name=orcl *.db_unique_name=sdb *.log_archive_config='dg_config=(pdb,sdb)' *.fal_server=pdb *.fal_client=sdb *.log_archive_dest_2='service=pdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdb' *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.STANDBY_FILE_MANAGEMENT=AUTO

--创建备库相应的目录(以下是我的路径): 数据文件和日志文件目录/u01/app/oracle/oradata/orcl/ 闪回区目录:/u01/app/oracle/flash_recovery_area/orcl 警报日志文件目录:/u01/app/oracle/diag/rdbms/sdb/orcl/trace 归档日志文件目录:/ss/archivelog/orcl

--备库:以pfile创建spfile并启动数据库到nomount: SQL>startrup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' SQL>create spfile from pfile; SQL>shutdown immediate; SQL>startup nomount

--检测主备库的网络连通性: 主库: SQL> conn [email protected] as sysdba
Connected.
SQL> conn [email protected] as sysdba
Connected. 备库: SQL> conn [email protected] as sysdba
Connected.
SQL> conn [email protected] as sysdba
Connected.

--使用rman备份数据库: 备份当前控制文件: RMAN>backup format '/ss/backup/controlfile_%U' current controlfile for standby; 备份数据文件和归档日志文件: RMAN>backup format '/ss/backup/db_%U' database plus archivelog;

--把备份集拷贝到和主库一样的目录/ss/backup/下: scp /ss/backup/* :/ss/backup/

--physical standby database的创建 --上面的步骤中我们已经将备库启动到nomount了,打开rman,使用rman的rman duplicate创建备用数据库: [[email protected] ~]$rman target [email protected] auxiliary / RMAN>duplicate target database for standby nofilenamecheck; 备用数据库完成之后,数据库会自动开启到mount: SQL>select status from v$instance; (如果主备目录不同:duplicate target database for standby; 如果主备目录相同:duplicate target database for standby nofilenamecheck;)


--修改备库为恢复管理模式,使备库可以应用主库的redo数据,实现同步: SQL>alter database recover managed standby database disconnect from session; 或 SQL>alter database recover managed standby database using current logfile disconnect from session;

--检查是否有错误信息出现: SQL>select error from v$archive_dest where target='STANDBY';

--检查备库日志应用情况: SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 30 01-NOV-14 01-NOV-14 YES 31 01-NOV-14 01-NOV-14 YES 32 01-NOV-14 02-NOV-14 YES 33 02-NOV-14 02-NOV-14 YES 34 02-NOV-14 02-NOV-14 YES 35 02-NOV-14 02-NOV-14 YES 36 02-NOV-14 02-NOV-14 YES 37 02-NOV-14 02-NOV-14 YES
--在主库切换日志,在备库中查看日志应用情况: SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 30 01-NOV-14 01-NOV-14 YES 31 01-NOV-14 01-NOV-14 YES 32 01-NOV-14 02-NOV-14 YES 33 02-NOV-14 02-NOV-14 YES 34 02-NOV-14 02-NOV-14 YES 35 02-NOV-14 02-NOV-14 YES 36 02-NOV-14 02-NOV-14 YES 37 02-NOV-14 02-NOV-14 YES 38 02-NOV-14 03-NOV-14 IN-MEMORY

--为主备库分别创建standby日志文件(至少比redo log多一组): 取消备库日志应用: SQL>alter database recover managed standby database cancel; 主库: SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 50m; SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER ---------- ------- -------------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log 4 /u01/app/oracle/oradata/orcl/stdredo01.log 5 /u01/app/oracle/oradata/orcl/stdredo02.log 6 /u01/app/oracle/oradata/orcl/stdredo03.log 7 /u01/app/oracle/oradata/orcl/stdredo04.log 7 rows selected.
备库: SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 50m; SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 50m; SQL> select group#,status,member from v$logfile; SQL> select group#,status,member from v$logfile; GROUP# STATUS MEMBER ---------- ------- -------------------------------------------------------------------------------- 3 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_3_b5bt92v2_.log 2 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_2_b5bt90kf_.log 1 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_1_b5bt8yfc_.log 4 /u01/app/oracle/oradata/orcl/stdredo01.log 5 /u01/app/oracle/oradata/orcl/stdredo02.log 6 /u01/app/oracle/oradata/orcl/stdredo03.log 7 /u01/app/oracle/oradata/orcl/stdredo04.log 7 rows selected.

--启用备库日志应用: SQL>alter database recover managed standby database disconnect from session;
--在备库中确定相关进程是否全部启动: SQL> select process,status from v$managed_standby;
PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING ARCH CONNECTED ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE MRP0 WAIT_FOR_LOG RFS IDLE
9 rows selected.

--同步验证: --查看备库是否存在日志断点: SQL> select * from v$archive_gap;
no rows selected 如果存在日志断点,就要到主库中找到这些日志: SQL> select sequence#,name from v$archived_log t1,v$archive_gap t2 where t1.thread#=t2.thread#; 找到这些日志文件之后,将这些文件拷贝到备库,并注册到备库进行应用: SQL>alter database register logfile 'xxx';
主库: SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 10m autoextend off;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl/users02.dbf /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/users03.dbf /u01/app/oracle/oradata/orcl/users04.dbf /u01/app/oracle/oradata/orcl/test02.dbf 10 rows selected.
备库: 查看备库警报日志文件: Datafile #10: '/u01/app/oracle/oradata/orcl/test02.dbf'
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl/users02.dbf /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/users03.dbf /u01/app/oracle/oradata/orcl/users04.dbf /u01/app/oracle/oradata/orcl/test02.dbf
10 rows selected.
--修改主库的保护模式和保护级别: --查看主库保护模式和保护级别: SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
如果不是处于maximum availability高可用模式,就关闭主库重启到mount,修改为高可用模式: SQL>shutdown immediate; SQL>alter database mount SQL>alter database set standby database to maximize availability; (alter database set standby database to maximize {protection | availability | performance } ) SQL>select protection_mode,protection_level from v$database; SQL>alter database open; 只需修改主库,会自动应用同步到备库。

至此,Datagurad已经搭建成功。




Tags:

相关文章

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <