Virtualbox4.3搭建oracle11gR2_64之dataguard完整案例

  1. 云栖社区>
  2. 博客>
  3. 正文

Virtualbox4.3搭建oracle11gR2_64之dataguard完整案例

青夜之衫 2017-12-04 22:23:00 浏览1621
展开阅读全文

RACDATAGUARDSTREAM被认为是Oracle的高可用的三架马车.三架马车各有所侧重:

(1)RAC主要解决了单节点故障,实现负载均衡,也实现了高性能。常用于24x7 的商业应用。

(2)DATAGUARD用冗余的方式实现高可用的,解决容灾。

(3)STREAM更加丰富,主要是应用层面的数据共享。

------->>本篇小编主要模拟在VirtualBox下搭建基于oracle11gR2_64的dataguard

==========================================================================================================================================================================
ORACLE 11G 搭建DATAGUARD步骤
==========================================================================================================================================================================

1 安装环境(VirtualBox4.0.3)

在主机1上安装数据库软件,并建监听和实例,在主机2上安装数据库软件,并建监听,但不建实例。

主机1

主机2

操作系统

Redhat5 64位

Redhat5 64位

主机名

dawn.com

dg.com

IP

192.168.11.110

192.168.11.112

数据库软件版本

oracle 11.2.0.1.0

oracle 11.2.0.1.0

ORACLE_BASE

/oracle/app/oracle/

/oracle/app/oracle/

ORACLE_HOME

$ORACLE_BASE/product/11.2.0/db_1

$ORACLE_BASE/product/11.2.0/db_1

ORACLE_SID

primary

standby

闪回区

2G

2G

归档

开启

开启

2 主数据库配置

2.1 设置数据库强制归档

sqlplus / as sysdba

SQL> ALTER DATABASE FORCE LOGGING;

SQL> select force_logging from v$database;

FOR

---

YES

2.2 添加STANDBY日志文件

SQL>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/priamry/stdbyredo01.log') SIZE 50m;

SQL>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/priamry/stdbyredo02.log') SIZE 50m;

SQL>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/priamry/stdbyredo03.log') SIZE 50m;

SQL> select * from v$logfile order by 1;

2.3 修改参数文件

2.3.1 生成pfile

SQL>create pfile from spfile;

SQL>shutdown immediate;

2.3.2 修改pfile

vi $ORACLE_HOME/dbs/initprimary.ora

在最后添加如下内容:

*.audit_file_dest='/u01/app/oracle/admin/primary/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/primary/control01.ctl','/u01/app/oracle/flash_recovery_area/primary/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='primary'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

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

*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/arch'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=842006528

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name=primary

*.fal_server='standby'

*.fal_client='primary'

*.standby_file_management=auto

*.db_file_name_convert='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/primary/'

*.log_file_name_convert='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/primary/'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_2='service=standby LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role)'

2.3.3 生成spfile

SQL> create spfile from pfile;

File created.

2.4 修改监听配置文件

cs4yKIAiCIAjCvSYeixAEQRAE4b4lAhVBEARBEO5

2.5 修改TNS配置文件

1nIQQQgghaleCvOHjRfxiFc60KVqclXUzMSGEEEI

2.6 重启监听服务

lsnrctl stop

lsnrctl start

2.7 配置最大可用模式

SQL> startup

ORACLE instance started

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> exit

2.8 备份数据库

我在这里模拟试验,数据文件较小较少,没用rman简化处理,用的全是手工处理,主要想对相关细节有较深入的了解,后续有机会会尝试用rman简化搭建,生产环境搭建肯定用rman.

---------->>下面是相关脚本(方法好像有点土):

--datafile

scp *.dbf oracle@192.168.11.112:/u01/app/oracle/oradata

--controlfile

scp *.dbf oracle@192.168.11.112:/u01/app/oracle/oradata/arch

--pfile

scp initprimary.ora oracle@192.168.11.112:/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora

--tnsnames.ora

scp tnsnames.ora oracle@192.168.11.112://u01/app/oracle/product/11.2.0/db_1/network/admin/

--orapwdprimary

scp orapwdprimary oracle@192.168.11.112:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdstandby

3 备数据库配置

3.1 建立相应的文件目录

包括dump文件目录,闪回区,数据文件目录,可以通过 show parameter dest命令查看(这里都是通过oracle用户创建)

mkdir -p /u01/app/oracle/admin/standby/adump;

mkdir -p /u01/app/oracle/diag/rdbms/standby/standby/trace

mkdir -p /u01/app/oracle/flash_recovery_area

mkdir -p /u01/app/oracle/oradata/standby

3.2 修改监听配置文件

修改ip地址

8DDQw9i4k3Pe8AAAAASUVORK5CYII=

3.3 修改TNS配置文件

修改后的tnsnames.ora文件内容:


3.4 重启监听服务

lsnrctl stop

lsnrctl start

3.5 主备库互相tnsping(只列主库,从库也OK)


3.6 修改参数文件

[oracle@dg2 admin]$ sqlplus / as sysdba

SQL> shutdown immediate;

vi $ORACLE_HOME/dbs/initstandby.ora

其中标红的部分都是需要调整和备库一致的:


SQL> create spfile from pfile;

3.7 启动数据库

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

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

3.8 验证

主库备库均执行如下命令:

SQL> archive log list;

主库

KCIqxVExMPAAAAABJRU5ErkJggg==

备库

m2hf7sHi7Bg30wvflPCebtLlveZcQomt+dpU4DN6

如果最后一行数字相同,说明配置成功

3.9 启用备用库

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora';

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 532678648 bytes

Database Buffers 301989888 bytes

Redo Buffers 2396160 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

3.10 主库切换日志,观察从库日志:

SQL> alter system switch logfile;

System altered.


3.11 主库进行数据修改,备库以只读方式查询测试:

SQL> create table lyl_dept as select * from scott.dept;

Table created.

SQL> alter system switch logfile;

System altered.

---------------------------------->>

SQL> select * from lyl_dept;

select * from lyl_dept

*

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database recover managred standby database cancel;

alter database recover managred standby database cancel

*

ERROR at line 1:

ORA-00905: missing keyword

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from lyl_dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> alter database recover managed standby database disconnect from session;

Database altered.

3.12 把主库切换到备库

----主节点

SQL> alter database commit to switchover to physical standby;

alter database commit to switchover to physical standby

*

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> startup nomount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 532678648 bytes

Database Buffers 301989888 bytes

Redo Buffers 2396160 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

--备库

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

standby OPEN

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 557844472 bytes

Database Buffers 276824064 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

SQL> show user;

USER is "SYS"

SQL> select * from lyl_dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> delete from lyl_dept where deptno=10;

1 row deleted.

SQL> commit;

Commit complete.

-----顺利完成切换,并可在备库进行正常更新操作.

3.13 遇到的问题及解决办法

其实搭建dg不是很难,搭建过程中主要是由于没注意细节,导致在一些环节浪费了不少时间,主要有:

A.创建备库相关目录时名称与主库混淆(比如审计用的adump创建成主库形式的);

B.没相应授权(比如cp到备库中的密码文件由于没有授权导致主备库间不能redo和archivelog传递),查看备库alert日志发现如下内容:

------------------------->>

CnXdMJ9NefjwIf8DSk0y7GiUwP0AAAAASUVORK5C

--------->>也就直接导致了备库的日志序列一直是0,如下示: