Oracle11gR2 Dataguard搭建

首页 > 代码库 > Oracle11gR2 Dataguard搭建

2024-07-31 19:00:06 221人阅读

数据库版本：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;

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 CONNECTED

ARCH CLOSING

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搭建

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Oracle11gR2 Dataguard搭建

Oracle11gR2 Dataguard搭建

看完仍有疑问？有类似问题直接问程序猿