oracle dg安装测试维护

在我的日常运维当中还没有dg库，趁着最近不是很忙，自己搭建个DG库测试下，我的环境是centos6.5磁盘目录一致，数据库版本是11gR2-11.0.0.4

首先安装两个虚拟机，设置好网络，也可以安装好一个再克隆过去，然后安装数据库软件不安装实例，下面是我做实验时的笔记，欢迎大家评论交流。

1.配置主库静态监听及tns
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ortest)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = ortest)
)
)

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1522))
)
)

创建静态监听我试了好久才成功。大家可以直接拷贝这块的，根据自己的主机名修改，注意下缩进。
tns
PUBLIC =
(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1522))
)
利用netmgr创建主库tns
TNSSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ortest)
)
)

TNAMASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ortest)
)
)

2.配置主库参数
--查看force_logging 是否打开
select name,force_logging from v$database;

alter database force logging;

alter system set db_unique_name=ortest scope=spfile;
alter system set db_name=ortest scope=spfile;
alter system set instance_name=ortest scope=spfile;
alter system set fal_client='tnsmaster' scope=both;
alter system set fal_server='tnsstandby' scope=both;
alter system SET log_archive_config='DG_CONFIG=(ortest,dgstandby)' scope=both;
alter system SET log_archive_dest_1='LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ortest' scope=both;
alter system SET log_archive_dest_2='SERVICE=tnsstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgstandby' scope=both;
ALTER SYSTEM SET db_file_name_convert='/oracle/app/oradata/ortest','/oracle/app/oradata/ortest' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/oracle/archive','/oracle/archive' scope=spfile;
alter system set standby_file_management='auto' scope=both;

show parameter log_arc --查看归档的文件格式log_archive_format
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
有一个小插曲我将归档日志文件名写错了，数据库必须包括 '%t %s %r的文件名，重启数据库时报错ORA-19905: log_archive_format must contain %s, %t and %r
create spfile from pfile ='/oracle/app/admin/ortest/pfile/init.ora.013202017522';
startup后可以正常启动数据库，但是之前设置的一些参数丢失了，该文件是建库时候的参数，重复上述的一些命令即可。

3.备库参数设置
拷贝主库的密码文件scp orapwortest 192.168.1.132:/oracle/app/product/11.2.0/dbhome_1/dbs
修改密码文件名orapwddgstandby： mv orapwortest orapwddgstandby
修改环境变量 vi .bash_profile 设置SID=dgstandby 使参数生效 source .bash_profile
创建参数文件 cd $ORACLE_HOME/dbs touch initdgstandby.ora 插入一行 db_name=ortest
启动数据库到nomount
创建静态监听复制主库的TNS到备库测试监听
主库备库分别执行：tnsping tnsmaster sqlplus sys/orcl@tnsmaster as sysdba
tnsping tnsstandby sqlplus sys/orcl@tnsstandby as sysdba

4.使用RMAN创建物理standby
必须使用TNS来登录RMAN
rman target sys/orcl@tnsmaster
connect auxiliary sys/orcl@tnsstandby
run{
allocate channel c1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby nofilenamecheck from active database
dorecover
spfile
set memory_target='5033164800'
set instance_name='dgstandby'
set db_unique_name='dgstandby'
set log_archive_config='DG_CONFIG=(ortest,dgstandby)'
SET AUDIT_FILE_DEST='/oracle/app/admin/ortest/adump'
set control_files='/oracle/app/oradata/ortest/control01.ctl','/oracle/app/fast_recovery_area/ortest/control02.ctl'
set log_archive_dest_1='LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgstandby'
set log_archive_dest_2='SERVICE=tnsmaster LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ortest'
SET db_file_name_convert='/oracle/app/oradata/ortest','/oracle/app/oradata/ortest'
SET log_file_name_convert='/oracle/archive','/oracle/archive'
set standby_file_management='auto'
set fal_client='tnsstandby'
set fal_server='tnsmaster';
release channel c1;
release channel stby;
}

--设置SGA大小 ?该如何分配，备库跟主库一致即可，本次测试时设置小了，导致备库启动不了，删除备库ORACLE_HOME/dbs/spfile，重新启动备库到nomount状态，并重复上面的操作
上述操作讲主库的数据文件创建到备库了，但是备库temp文件未有

5.添加standby日志--需要添加多少呢，数据库日志组+1（如本备库从主库拷贝过来的是3组日志组，本次添加standby日志组则添加4组）
alter database add standby logfile group 4 '/oracle/app/oradata/ortest/standby04.rdo' size 50m;
alter database add standby logfile group 5 '/oracle/app/oradata/ortest/standby05.rdo' size 50m;
alter database add standby logfile group 6 '/oracle/app/oradata/ortest/standby06.rdo' size 50m;
alter database add standby logfile group 7 '/oracle/app/oradata/ortest/standby07.rdo' size 50m;

select *from v$log;
select * from v$standby_log;

6.查看主备库的状态
select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;--数据库状态： READ ONLY WITH APPLY 实时应用，该状态时才会同步数据
select *from v$managed_standby;
select group#,thread#,sequence#,bytes,archived,status from v$standby_log;
select * from v$archive_dest_status;
select message from v$dataguard_status;
select dest_id,sequence#,applied,status from v$archived_log where dest_id=2 order by 2 asc;

7.开启同步
停止redo应用
alter database recover managed standby database cancel;
开启redo应用（日志切换才会同步）
alter database recover managed standby database disconnect from session;
开启redo应用（实时同步，commit即可同步）
alter database recover managed standby database using current logfile disconnect;
测试打开备库
备库上上面的语句执行

8.DG应用日常维护
启动数据库：
1）主备库都在关闭状态，主备都要启动
a.启动备库监听
b.启动备库
c.启动主库监听
d.启动主库

2）主库正常运行，启动备库
a.启动备库监听
b.启动备库

关闭数据库
1）主库正常运行，只关闭备库
alter database recover managed standby database cancel;
shutdown immediate
lsnrctl stop
lsnrctl stop listener1

2)主库备库都要关闭：先关主库后关备库
a)关闭主库
shutdown immediate
lsnrctl stop
b)关闭备库
alter database recover managed standby database cancel;
shutdown immediate
lsnrctl stop
lsnrctl stop listener1

注意：备库应用了归档日志才能删除
configure archivelog deletion policy to applied on standby--rman下设置主库的参数，11G可以不设置，如下测试

先关闭备库日志应用，关闭数据库，关闭监听注意两个监听都关闭，在主库切换归档，T表插入几条数据，切换归档，这会主库备库会有归档日志差异
rman下
list archivelog all;
delet archivelog all;
crosscheck archivelog all;
delet archivelog all;--该步骤不会删除未应用的归档日志
然后启动备库监听，启动备库数据库，打开日志应用，此时归档已同步，并且主库插入的数据更新到备库了。

介绍：DG的三种模式
1最大保护模式
2最大可用性 commit的应用
3最大性能，日志归档或者commit。本次试验是该模式

三种模式的切换顺序，必须按此顺序
最大性能》最高可用》最高保护
最高保护》最高可用》最高性能