DG如何搭建

这篇文章主要为大家展示了“DG如何搭建”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“DG如何搭建”这篇文章吧。

一、主库

1归档模式

SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

2强制日志

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

YES

3添加standby日志

standby logfile的数量和大小均要与redo logfile相同

SQL> select thread#,group#,members,bytes/1024/1024 from v$log;

   THREAD#     GROUP#     MEMBERS BYTES/1024/1024

———- ———- ———- —————

     1        1           1          50

     1        2           1          50

     1        3           1          50

SQL> col MEMBER for a25

SQL> select * from v$logfile;

    GROUP# STATUS TYPE MEMBER             IS_

———- ——- ——- ————————- —

     3       ONLINE  /oradata/orcl/redo03.log  NO

     2       ONLINE  /oradata/orcl/redo02.log  NO

     1       ONLINE  /oradata/orcl/redo01.log  NO

从图中可以看到我们主库有三组大小为50M的redo logfile,故我们也需要创建同样数量和大小的standby logfile:

SQL> alter database add standby logfile group 11 ('/oradata/orcl/stb01.log')size 50m;

Database altered.

SQL> alter database add standby logfile group 12('/oradata/orcl/stb02.log')size 50m;

Database altered.

SQL> alter database add standby logfile group 13('/oradata/orcl/stb03.log')size 50m;

Database altered.

SQL> select group#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS

———- ———- ———- — ———-

    11        0           0 YES UNASSIGNED

    12        0           0 YES UNASSIGNED

    13        0           0 YES UNASSIGNED

4设置数据库口令文件的使用模式

查看remote_login_passwordfile的值是否EXCLUSIVE

SQL> show parameter remote_login_passwordfile

NAME                 TYPE     VALUE

———————————— ———– ——————————

remote_login_passwordfile         string     EXCLUSIVE

如果不是,执行以下命令进行设置,并且重启数据库,使其生效:

SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

SQL>shutdown immediate;

SQL>startup;

5参数(文件)设置

SQL> show parameter db_unique_name;

DG的搭建需要修改许多数据库的参数,并且部分参数主备库之间有点区别,需要在配置过程细心一点。

NAME                 TYPE     VALUE

———————————— ———– ——————————

db_unique_name                 string     orcl

SQL> alter system set log_archive_config='dg_config=(orcl,orcls)' scope=spfile;

System altered.

–其中dg_config填写的是主备库的db_unique_name。

修改归档文件位置

SQL> show parameter db_recovery_file_dest

NAME                 TYPE     VALUE

———————————— ———– ——————————

db_recovery_file_dest             string     /u01/app/oracle/fast_recovery_

                         area

db_recovery_file_dest_size         big integer 4182M

设置本地归档位置,参数涉及切换

alter system set log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;

alter system set log_archive_dest_2='SERVICE=orcls ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls' scope=spfile;

启用设置的日志路径

SQL>alter system set log_archive_dest_state_1=enable scope=spfile;

SQL>alter system set log_archive_dest_state_2=enable scope=spfile;

设置归档日志进程的最大数量(视实际情况调整):

SQL>alter system set log_archive_max_processes=30 scope=both;

设置standby库从哪个数据库获取归档日志(只对standby库有效,在主库上设置是为了在故障切换后,主库可以成为备库使用):

SQL>alter system set fal_server=orcls scope=both;

设置文件管理模式,此项设置为自动,不然在主库创建数据文件后,备库不会自动创建:

SQL>alter system set standby_file_management=auto scope=spfile;

启用OMF功能:

SQL> alter system set db_create_file_dest='/oradata/orcl' scope=spfile;

–如果主备库文件的存放路径不同,还需要设置以下两个参数(需要重启数据库生效):

SQL> alter system set db_file_name_convert='/data/oradata/orcls/datafile','/data/oradata/orcl/datafile','/data/oradata/orcls/tempfile','/data/oradata/orcl/tempfile' scope=spfile;

SQL> alter system set log_file_name_convert='/data/oradata/orcls/redo','/data/oradata/orcl/redo' scope=spfile;

这步路径的先后顺序在主备库上的设置是不一样的,大家要注意!

二、备库参数设置

完成了以上步骤后,通过以下命令生成一个pfile文件给备库使用:

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from spfile;

File created.

打开生成的文件,修改部分参数,具体如下:

–修改后

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/orcls/control01.ctl','/u01/app/oracle/fast_recovery_area/orcls/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oradata/orcls'

*.db_domain=''

*.db_name='orcl'

*.db_unique_name='ocrls'

*.db_recovery_file_dest_size=4385144832

*.db_recovery_file_dest=''

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

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

*.log_archive_config='dg_config=(orcl,orcls)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls'

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

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

c)密码文件配置

密码文件是创建DG不可缺少的一部分,主库的密码文件一般在$ORACLE_HOME/dbs,命名格式是:orapw+db_unique_name

如果不存在此文件,我们可以通过以下命令生成一个:

#su – oracle

$cd $ORACLE_HOME/dbs

$orapwdfile=orapwocrl password=oracle

我们将密码文件和刚才修改好的pfile一起拷贝到备库的$ORACLE_HOME/dbs目录下,并重命名密码文件的名字:

备库上修改密码文件名和参数文件

5.listener.ora与tnsnames.ora配置

这两个文件均在$ORACLE_HOME/network/admin目录下,如果没有,可以自行创建一下

a)备库配置

listener.ora内容如下:

LISTENER=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcls)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcls)

    )

   )

tnsnames.ora内容如下:

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcl)

    )

  )

orcls =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcls)

    )

  )

重启一下监听:

$lsnrctl stop

$lsnrctl start

b)主库配置

listener.ora内容如下:

LISTENER=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ocrls)

      (ORACLE_HOME =/u01/app/oracle/product/12.1.0/db_1)

      (SID_NAME = ocrls)

    )

   )

tnsnames.ora内容如下:

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcl)

    )

  )

orcls =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcls)

    )

  )

执行以下命令重启监听,使配置生效:

$lsnrctl stop

$lsnrctl start

做完以上配置后,在主备库上执行以下命令,确保两个主机之间网络相通:

$tnsping orcls

$tnsping orcls

6.目录创建

参数和网络配置好后,我们需要为备库dump文件创建相应的目录(对照主库$ORACLE_BASE/admin):

[oracle@node2 ~]$ echo $ORACLE_BASE

/u01/app/oracle

[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/adump

[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/dpdump

为数据库文件创建目录(就是之前db_file_name_convert和log_file_name_convert的目录)–/oradata

ocrls:/data/oradata/orls@standby>mkdir -p/data/oradata/ocrls/redo/

ocrls:/data/oradata/ocrls@standby>mkdir -p/data/oradata/ocrls/datafile/

ocrls:/data/oradata/ocrls@standby>mkdir -p /data/oradata/ocrls/control/

7.RMAN复制创建standby库

准备工作都完成了,那我们可以开始standby库的创建了。

注:以下操作在备库完成

a)文件复制

先,我们使用之前修改的pfile把备库启动到nomount状态,生成spfile:

$echo $ORACLE_SID (确认SID是否我们设置的)

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

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size            2257272 bytes

Variable Size          507514504 bytes

Database Buffers      264241152 bytes

Redo Buffers            2633728 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>exit

从spfile启动

SQL>STARTUP NOMOUNT

SQL> show parameter db_unique_name;

NAME                 TYPE     VALUE

———————————— ———– ——————————

db_unique_name                 string     orcls

SQL> show parameter name;

NAME                 TYPE     VALUE

———————————— ———– ——————————

cell_offloadgroup_name             string

db_file_name_convert             string

db_name                  string     orcl

db_unique_name                 string     orcls

global_names                 boolean     FALSE

instance_name                 string     orcls

lock_name_space              string

log_file_name_convert             string

processor_group_name             string

service_names                 string     orcls

SQL>

复制数据文件,在备库上操作

[oracle@node2 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcls

Recovery Manager: Release 11.2.0.4.0 – Production on Fri Jun 15 00:33:22 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1506854844)

connected to auxiliary database: ORCL (not mounted)

RMAN>

确认我们已经连接上主库和备库后,执行以下命令:

如果在RMAN恢复时不指定 nofilenamecheck 参数

则在数据文件相同文件名恢复时会出现RMAN-05501错误

RMAN> duplicate target database for standby from active database nofilenamecheck;

命令执行完后,可以看到主库在开始复制文件到备库中

复制完成后,打开数据库开启实时同步:

SQL>ALTER DATABASE ARCHIVELOG;

SQL>ALTER DATABASE OPEN;

SQL>ARCHIVE LOG LIST

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

查看数据库状态

登陆到主库

$sqlplus / as sysdba

SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PRIMARY

登录到备库:

$sqlplus / as sysdba

SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

检查归档日志是否能正常传输(日志的序号必须是一样的):

主库

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

     2 17-JUN-18 18-JUN-18 NO     YES

     3 18-JUN-18 18-JUN-18 NO     YES

     4 18-JUN-18 18-JUN-18 NO     YES

     5 18-JUN-18 18-JUN-18 NO     YES

     6 18-JUN-18 19-JUN-18 NO     YES

     7 19-JUN-18 19-JUN-18 NO     YES

     8 19-JUN-18 19-JUN-18 NO     YES

     9 19-JUN-18 19-JUN-18 NO     YES

    10 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 YES     YES

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

    12 19-JUN-18 19-JUN-18 NO     YES

    12 19-JUN-18 19-JUN-18 NO     YES

13 rows selected.

备库

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 IN-MEMORY YES

b)切换日志测试

主库

SQL> alter system switch logfile;

System altered.

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

     2 17-JUN-18 18-JUN-18 NO     YES

     3 18-JUN-18 18-JUN-18 NO     YES

     4 18-JUN-18 18-JUN-18 NO     YES

     5 18-JUN-18 18-JUN-18 NO     YES

     6 18-JUN-18 19-JUN-18 NO     YES

     7 19-JUN-18 19-JUN-18 NO     YES

     8 19-JUN-18 19-JUN-18 NO     YES

     9 19-JUN-18 19-JUN-18 NO     YES

    10 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 YES     YES

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

    12 19-JUN-18 19-JUN-18 NO     YES

    12 19-JUN-18 19-JUN-18 NO     YES

    13 19-JUN-18 19-JUN-18 NO     YES

    13 19-JUN-18 19-JUN-18 NO     YES

15 rows selected.

备库

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 IN-MEMORY YES

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

———- ——— ——— ——— —

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 YES     YES

    13 19-JUN-18 19-JUN-18 IN-MEMORY YES

SQL> select max(sequence#)from v$archived_log;

SQL> select max(sequence#)from v$archived_log;

MAX(SEQUENCE#)

————–

        13

以上是“DG如何搭建”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!

原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/205344.html

(0)
上一篇 2021年11月29日
下一篇 2021年11月29日

相关推荐

发表回复

登录后才能评论