Oracle 10g R2 之DataGuard物理standby 配置-白红宇

Oracle 10g R2 之DataGuard物理standby 配置

阅读量：6878 次

发布时间：2019-06-26

本文共 8610 字，大约阅读时间需要 28 分钟。

的简单介绍

DATA GUARD的最主要的功能是冗灾。当然根据配置的不同，DATA GUARD具备以下特点：高可用、性能提升、数据保护以及故障恢复等。

DATA GUARD可以分为物理STANDBY和逻辑STANDBY两种。二者的最大差别在于，物理STANDBY应用的是主库的归档日志，而逻辑STANDBY应用的是主库的归档日志中提取的SQL语句。由于二者这一点的区别，决定了物理STANDBY无论从逻辑结构和物理结构都是和主库保持一致，而逻辑STANDBY则只需保证逻辑结构一致，且逻辑STANDBY在应用SQL语句的时候，数据库可以处于打开的状态。

如果从DATA GUARD的保护模式分，可以分为三种不同的保护模式：

保护最大化：这种模式的配置可以保证主库和备库的同步，任何情况下主库的损毁都不会导致已提交数据的丢失。如果主库和备库之间的网络出现问题，或者备库本身出现问题，都会导致主库停止数据处理。

可用最大化：这种模式和上面一种类似，也是会保证主库和备库的同步，区别在于，当网络或备库不可用时，主库仍然可以继续处理。

性能最大化：主库和备库是异步的。这种模式可能在主库出现损毁时，丢失一部分数据。但是这种模式对主库负荷最小，因此具有最好的性能。

关于DATA GUARD 的介绍请参考Oracle 官方网站，本文主要是介绍如何配置物理 STANDBY。

主库：

[主机名-dg1.andylhz.com,IP地址-192.168.1.254，实例名-dg，唯一数据库名-dgp，NET服务名-dgp]

备库：

[主机名-dg2.andylhz.com,IP地址-192.168.1.253，实例名-dg，唯一数据库名-dgs，NET服务名-dgs]

因为在安装数据的是一开始就启用了归档，在这里就省略此步骤，不过作为文档就有必要写出操作步骤如下：

SQL>archive log list 看是否启用归档？

如果没有就关闭数据库，并启动到mount的状态，

SQL>startup mount；

SQL>alter database archivelog; 启用归档功能。

SQL>archive log list；确认结果如图：

备用联机日志文件’

SQL> alter database add standby logfile group 4

('/u01/app/oracle/oradata/dg/redo04.log') size 50m;

SQL> alter database add standby logfile group 5

('/u01/app/oracle/oradata/dg/redo05.log') size 50m;

SQL> alter database add standby logfile group 6

('/u01/app/oracle/oradata/dg/redo06.log') size 50m;

SQL> create pfile from spfile;

文件创建以后可以在$ORACLE_HOME/dbs下找到initdg.ora

vi initdg.ora #标记红色部分为天加的参数

dg.__db_cache_size=83886080

dg.__java_pool_size=4194304

dg.__large_pool_size=4194304

dg.__shared_pool_size=71303168

dg.__streams_pool_size=0

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

*.background_dump_dest='/u01/app/oracle/admin/dg/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/dg/control01.ctl','/u01/app/oracle/oradata/dg/control02.ctl','/u01/app/oracle/oradata/dg/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/dg/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='dg'

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

*.db_recovery_file_dest_size=2147483648

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

*.job_queue_processes=10

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

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/dg/udump'

*.DB_UNIQUE_NAME='dgp'

*.log_archive_config='DG_CONFIG=(dgp,dgs)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/dg/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgp'

*.log_archive_dest_2='SERVICE=dgs arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgs'

#standby角色转换的参数

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='dgs'

*.FAL_CLIENT='dgp'

修改完后保存退出。

关闭主数据库

再用sys 用户连接到数据库，根据修改过的pfile 文件创建 spfile 文件

SQL> shutdown immediate;

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

六、建立备用库的控制文件

SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';

七、配置主数据库listener及tnsnames

[oracle@dg1 ~]$ cat /u01/app/oracle/10g/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/10g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/10g)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = dg)

(ORACLE_HOME = /u01/app/oracle/10g)

(SID_NAME = dg)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.andylhz.com)(PORT = 1521))

#(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.andylhz.com)(PORT = 1521))

#在进行switchover 的时候需要将此设置更改

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

[oracle@dg1 ~]$ cat /u01/app/oracle/10g/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/10g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/10g)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = dg)

(ORACLE_HOME = /u01/app/oracle/10g)

(SID_NAME = dg)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.andylhz.com)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

[oracle@dg1 ~]$ cat /u01/app/oracle/10g/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/10g/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DGP =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.andylhz.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg)

)

DGS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.andylhz.com)(PORT = 1521))

#(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.andylhz.com)(PORT = 1521))

#在进行switchover 的时候需要将此设置更改

)

(CONNECT_DATA =

(SERVICE_NAME = dg)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

我在另外的备机上的oracle软件的安装和主库安装位置是一样的。

所以将主库的数据文件和standby控制文件，参数文件initdg.ora 和listener tnsnames 文件，密码文件等复制到备库的相应位置：如下

[oracle@dg1 ~]$ cd /u01/app/oracle/

[oracle@dg1 oracle]$ ll

total 16

drwxr-xr-x 55 oracle oinstall 4096 Jan 23 02:33 10g

drwxr-x--- 3 oracle oinstall 4096 Jan 23 02:05 admin

drwxr-x--- 3 oracle oinstall 4096 Jan 23 02:06 flash_recovery_area

drwxr-x--- 3 oracle oinstall 4096 Jan 23 02:05 oradata

将standby 控制文件重命名control01.ctl control02.ctl control03.ctl

[oracle@dg2 oradata]$ cd /u01/app/oracle/

[oracle@dg2 oracle]$ ll

total 16

drwxr-xr-x 54 oracle oinstall 4096 Jan 15 16:52 10g

drwxr-x--- 3 oracle oinstall 4096 Jan 23 02:48 admin

drwxr-x--- 4 oracle oinstall 4096 Jan 23 03:17 flash_recovery_area

drwxr-x--- 3 oracle oinstall 4096 Jan 23 02:48 oradata

、tnsnames文件

[oracle@dg2 oradata]$ vi /u01/app/oracle/10g/dbs/initdg.ora #标记红色部分为天加的参数

dg.__db_cache_size=83886080

dg.__java_pool_size=4194304

dg.__large_pool_size=4194304

dg.__shared_pool_size=71303168

dg.__streams_pool_size=0

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

*.background_dump_dest='/u01/app/oracle/admin/dg/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/dg/control01.ctl','/u01/app/oracle/oradata/dg/control02.ctl','/u01/app/oracle/oradata/dg/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/dg/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='dg'

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

*.db_recovery_file_dest_size=2147483648

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

*.job_queue_processes=10

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

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/dg/udump'

*.DB_UNIQUE_NAME='dgs'

*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/dg/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'

*.log_archive_dest_2='SERVICE=dgp arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgp'

#standby角色转换的参数

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='dgp'

*.FAL_CLIENT='dgs'

然后保存退出：

用sys用户登录备库后创建spfile

SQL> create pfile from spfile;

File created.

注：备库的listener 和tnsnames 文件中的参数是一样的，只是主机名/IP需要做修改即可。

启动主库监听：lsnrctl start

[oracle@dg2 oradata]$ tnsping dgp

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 23-JAN-2012 05:18:45

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.andylhz.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg)))

OK (10 msec)

启动备库监听: lsnrctl start

[oracle@dg1 dg]$ tnsping dgs

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 23-JAN-2012 05:29:10

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg2.andylhz.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg)))

OK (20 msec)

SQL> startup mount;#备库以mount方式打开

启动redolog应用：

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

SQL> startup； #主库正常打开

切换日志文件：

SQL> alter system switch logfile;

主库日志显示

备库日志

备库成功应用了主库的归档日志

转载地址：http://uzrfl.baihongyu.com/

你可能感兴趣的文章