首页 > 代码库 > Oracle11gR2 Dataguard搭建

Oracle11gR2 Dataguard搭建

数据库版本: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文件:
主库:
[root@dg1 ~]# cat /etc/hosts
127.0.0.1                         dg1 localhost
: :1                                   localhost6.localdomain6 localhost6
192.168.133.133             dg1
192.168.133.134             dg2
 
备库:
[root@dg2 ~]# cat /etc/hosts
127.0.0.1                        dg2 localhost
: :1                                  localhost6.localdomain6 localhost6
192.168.133.133            dg1
192.168.133.134            dg2
 
 
--看看防火墙是否关闭:
[root@dg1 ~]# /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 =http://www.mamicode.com/
      (SERVICE_NAME = pdb)
    )
  )
 
SDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (SERVICE_NAME = sdb)
    )
  )
 
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (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 =http://www.mamicode.com/
      (SERVICE_NAME = pdb)
    )
  )
 
SDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (SERVICE_NAME = sdb)
    )
  )
 
 
--启动主备库的监听:
lsnrctl start
 
 
--创建备库的口令文件:
如果主库中有就直接使用scp命令拷贝到备库所在服务器相应目录中,如果没有就使用orapwd命令创建:
orapwd  file=xxx   password=xxx  entries=xx
 
 
--创建主备库的pfile,添加相应的参数:
主库:
SQL>create pfile from spfile;
[oracle@dg1 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拷贝到备库所在服务器相同目录下,并修改如下参数:
[oracle@dg2 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 sys/oracle@pdb as sysdba 
Connected. 
SQL> conn sys/oracle@sdb as sysdba 
Connected.
备库:
SQL> conn sys/oracle@sdb as sysdba 
Connected. 
SQL> conn sys/oracle@pdb 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/*   oralce@192.168.133.134:/ss/backup/
 
 
--physical standby database的创建
--上面的步骤中我们已经将备库启动到nomount了,打开rman,使用rman的rman duplicate创建备用数据库:
[oracle@dg2 ~]$rman target sys/oracle@pdb 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已经搭建成功。

 

Oracle11gR2 Dataguard搭建