DG的搭建记录是怎样的

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

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