DG的搭建记录是怎样的

DG的搭建记录是怎样的，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

单实例DG搭建手册

规划

ip

db_name

db_unique_name

sid

tns

192.168.10.103

test

test

test

test

192.168.10.104

test

dgdb

dgdb

dgdb

配置监听

主库listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_DG =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = test)

(ORACLE_HOME = /u01/app/oracle/product/11.2)

      (SID_NAME = test)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lzl)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

备库listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_DG =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = dgdb)

(ORACLE_HOME = /u01/app/oracle/product/11.2)

      (SID_NAME = dgdb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

(DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = l2l)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

主备库的tnsnames.ora

test =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lzl)(PORT = 1521))

)

    (CONNECT_DATA =

      (SID = test)

      (SERVER = DEDICATED)

    )

  )

dgdb =

  (DESCRIPTION =

    (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = l2l)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = dgdb)

      (SERVER = DEDICATED)

    )

  )

主库准备

更改日志模式

防止使用no logging子句造成主备库数据不一致

SQL> alter database force logging

  2  /

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

是否归档

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

更改log_archive_config

SQL>  alter system set log_archive_config=‘DG_CONFIG=(test,dgdb) scope=both ;

System altered.

SQL> show parameter archive_config

NAME     TYPE VALUE

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

log_archive_config     string DG_CONFIG=(test,dgdb)

SQL> 

配置DG参数

alter system set log_archive_dest_1=location=/home/oracle/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test scope=both;

alter system set log_archive_dest_2 =SERVICE=dgdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb scope=both;

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

数据文件自动添加

alter system set standby_file_management=auto scope=both;

alter system set fal_server=dgdb scope=both;

alter system set fal_client=test scope=both;

1800s自动切换redo log

alter system set archive_lag_target=1800;

文件位置调整

alter system set db_file_name_convert=/u01/app/oracle/oradata/test/,/u01/app/oracle/oradata/dgdb/ scope=spfile;

alter system set log_file_name_convert=/u01/app/oracle/oradata/logfile/,/u01/app/oracle/oradata/logfile/ scope=spfile;

2.密码文件

使用以前的密码文件或者直接创建一个密码文件

[oracle@lzl ~]$ orapwd password=oracle file=/home/oracle/orapwd.ora force=y ignorecase=y

[oracle@lzl ~]$ ls

orapwd.ora

3.备份数据库

backup database

4.生成pfile

create pfile=/home/oracle/initdgdb.ora from spfile;

修改参数

*.db_unique_name=dgdb

*.fal_client=dgdb

*.fal_server=test

*.service_names=dgdb

*.log_archive_dest_1=location=/home/oracle VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgdb

*.log_archive_dest_2=SERVICE=dgdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test

db_name应与主库保持一致

6.生成stanby controlfile

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

Database altered.

备库准备

备库是一个只有数据库软件的环境

1.拷贝stanby controlfile，pfile，orapwd 文件到备库

控制文件拷贝到pfile指定的目录中

密码文件更改sid到/oracle/home/dbs中

[oracle@l2l ~]$ ls

control01.ctl  initdgdb.ora  orapwd.ora

2.创建pfile中的目录

审计目录、控制文件目录、数据文件目录等

3.启动备库到mount阶段

[oracle@l2l ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 27 17:04:07 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/home/oracle/initdgdb.ora;

ORACLE instance started.

Total System Global Area 1048059904 bytes

Fixed Size    2235000 bytes

Variable Size  310379912 bytes

Database Buffers  729808896 bytes

Redo Buffers    5636096 bytes

SQL> alter database mount;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

4.rman数据恢复

使用rman将主库的备份应用于备库

RMAN> catalog start with ‘/home/oracle/liu/’

RMAN> restore database；

5.创建standby redo log files，数量至少是主库的两倍

standby redo log 与redo log是很相似的，但是standby redo log是用来在standby库上接收主库的redo data的。

备库上不会使用online redo log

为了方便任意切换，最好是主备库都创建standby redo log files

SQL> alter database add standby logfile group 11 (/u01/app/oracle/oradata/test/dg_redo11.log ) size 50M;

Database altered.

SQL> alter database add standby logfile group 12 (/u01/app/oracle/oradata/test/dg_redo12.log ) size 50M;

Database altered.

SQL> alter database add standby logfile group 13 (/u01/app/oracle/oradata/test/dg_redo13.log ) size 50M;

Database altered.

SQL> alter database add standby logfile group 14 (/u01/app/oracle/oradata/test/dg_redo14.log ) size 50M;

Database altered.

SQL> alter database add standby logfile group 15 (/u01/app/oracle/oradata/test/dg_redo15.log ) size 50M;

Database altered.

SQL> alter database add standby logfile group 16 (/u01/app/oracle/oradata/test/dg_redo16.log ) size 50M;

Database altered.

查看v$standby_log

SQL> select group# from v$standby_log;

    GROUP#

----------

10

11

12

13

14

15

6 rows selected.

SQL> select group# from v$log;

    GROUP#

----------

1

3

2

查看文件还是需要v$logfile

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/test/redo03.log

/u01/app/oracle/oradata/test/redo02.log

/u01/app/oracle/oradata/test/redo01.log

/u01/app/oracle/oradata/test/dg_redo11.log

/u01/app/oracle/oradata/test/dg_redo12.log

/u01/app/oracle/oradata/test/dg_redo13.log

/u01/app/oracle/oradata/test/dg_redo14.log

/u01/app/oracle/oradata/test/dg_redo15.log

/u01/app/oracle/oradata/test/dg_redo16.log

9 rows selected.

建立主备库DG关系

备库

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

Database altered.

告警日志信息：

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (dgdb)

Sat Jul 28 19:28:48 2017

MRP0 started with pid=25, OS id=3264

MRP0: Background Managed Standby Recovery process started (dgdb)

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 100

Completed:  alter database recover managed standby database using current logfile disconnect from session

查看备库角色

SQL>  select PROTECTION_MODE ,PROTECTION_LEVEL ,GUARD_STATUS,DATABASE_ROLE,to_char(current_scn) from v$database;

PROTECTION_MODE      PROTECTION_LEVEL  GUARD_S DATABASE_ROLE

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

TO_CHAR(CURRENT_SCN)

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NONE  PHYSICAL STANDBY

2481074

主库检查

备库检查

SQL>  select PROTECTION_MODE ,PROTECTION_LEVEL ,GUARD_STATUS,DATABASE_ROLE,to_char(current_scn) from v$database;

PROTECTION_MODE      PROTECTION_LEVEL  GUARD_S DATABASE_ROLE

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

TO_CHAR(CURRENT_SCN)

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

MAXIMUM AVAILABILITY RESYNCHRONIZATION  NONE  PRIMARY

2482972

看完上述内容，你们掌握DG的搭建记录是怎样的的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！