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

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

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

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

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;

MAX(SEQUENCE#)

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

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

网页题目：DG如何搭建
本文URL：https://www.cdcxhl.com/article32/pscesc.html

成都网站建设公司_创新互联，为您提供网站营销、标签优化、外贸网站建设、定制网站、虚拟主机、手机网站建设

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容