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的搭建记录是怎样的的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/203981.html