oracle dg

68 阅读 0 评论 45 点赞

我是靠谱客的博主瘦瘦鸭子，最近开发中收集的这篇文章主要介绍oracle dg，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

安装主库（软件+实例）：
1、shutdown immediate;
2、startup mount; /启动归档强制日志闪回
alter database force logging;
alter database archivelog;
alter database flashback on;
3、创建备库日志文件:

alter database add standby logfile group 7 ('D:appAdministratororadatalisdbREDO07.LOG') size 200M;
alter database add standby logfile group 8 ('D:appAdministratororadatalisdbREDO08.LOG') size 200M;
alter database add standby logfile group 9 ('D:appAdministratororadatalisdbREDO09.LOG') size 200M;
alter database add standby logfile group 10 ('D:appAdministratororadatalisdbREDO010.LOG') size 200M;
alter database add standby logfile group 11 ('D:appAdministratororadatalisdbREDO011.LOG') size 200M;
alter database add standby logfile group 12 ('D:appAdministratororadatalisdbREDO012.LOG') size 200M;
alter database add standby logfile group 13 ('D:appAdministratororadatalisdbREDO013.LOG') size 200M;
4、修改spfile文件
   1.alter system set db_unique_name='primary' scope=spfile;
   2.alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
   3.alter system set log_archive_dest_1='location=D:arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
   4.alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
   5.alter system set log_archive_dest_state_1='enable' scope=spfile;
   6.alter system set log_archive_dest_state_2='enable' scope=spfile;
   7.alter system set fal_server='standby' scope=spfile;
   8.alter system set fal_client='primary' scope=spfile;
   9.alter system set archive_lag_target=1800 scope=spfile;
   10.alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
   11.alter system set standby_file_management=auto scope=spfile;
   12.alter system set db_file_name_convert='standby','primary' scope=spfile;
   13.alter system set log_file_name_convert='standby','primary' scope=spfile;
5、重启数据库
alter database open;
shutdown immediate;
startup;
6、创建pfile
create pfile from spfile;
7、创建密码文件
pwd.ora
8、配置主库监听文件

# listener.ora Network Configuration File: d:appAdministratorproduct11.2.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = d:appAdministratorproduct11.2.0dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:d:appAdministratorproduct11.2.0dbhome_1binoraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = lisdb)
(ORACLE_HOME = d:appAdministratorproduct11.2.0dbhome_1)
(SID_NAME = lisdb)
)

)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.242.50)(PORT = 1521))
)
)

ADR_BASE_LISTENER = d:appAdministrator

9、配置TNS文件

# tnsnames.ora Network Configuration File: d:appAdministratorproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.

LISDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.242.50)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = lisdb)
)
)

primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.242.50)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = lisdb)
)
)

standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.242.51)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = lisdb)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

10、测试监听
lsnrctl stop
lsnrctl start

11、重启数据库
shutdown immediate--startup
tnsping primary
sys@primary

12、关闭主库。

13、安装备库，只安装软件（目录与主库保持一致）

14、复制文件
admin
cfgtoollogs
diag
arch
fast_recovery_area
密码文件
监听文件和tns文件修改listener.ora中ip为备库ip

15、备机新建实例
Oradim -new -sid LISDB
拷贝 initlisdb.ora到备库并修改内容
*.db_file_name_convert='primary','standby'
*.db_unique_name='standby'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_dest_1='location=D:arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_file_name_convert='primary','standby'

16、备注nomount
startup nomount pfile='d:appAdministratorproduct11.2.0dbhome_1databaseINITlisdb.ORA'

17、主库操作
startup
rman target /
Backup full database format='e:oracledbtmpFOR_STANDBY_%u%p%s.RMN' include current controlfile for standby;
RMAN >sql'alter system archive log current';
复制备份文件

RMAN>connect auxiliary sys/XHLis123@standby
RMAN>duplicate target database for standby nofilenamecheck;

18、备库启动standby
alter database mount standby database;
alter database recover managed standby database disconnect from session; //归档模式

dg验证：
主库：select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;
alter system switch logfile;

switchover 主库切换到物理备库

select switchover_status from v$database;

to_standby 或 sessions_active可以切换

日常操作：
关闭dg流程
1、备库停止日志应用 alter database recover managed standby database cancel ;
2、关闭主库 shutdown immediate
3、关闭备库 shutdown immediate

启动dg流程
1、启动备库
startup nomount
alter database mount standby database;
2、启动主库
startup
3、开启备库日志应用
alter database recover managed standby database using current logfile disconnect from session; //redo模式

启动只读模式
备库 startup nomount
备库 alter database mount standby database;
主库 startup
备库 alter database open read only;
备库 alter database recover managed standby database using current logfile disconnect from session;

切换回管理恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; -- 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;

主库和备库之间角色切换
select switchover_status from v$database;
to_standby 或 sessions_active可以切换
1、主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
2、从库切换为主库
alter database commit to switchover to primary;
alter database commit to switchover to primary with session shutdown; //有会话
shutdown immediate;
startup
alter system switch logfile;
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;