oracleADG搭建

整体规划

主库单实例>>>>>>>>备库单实例

主库操作
1.主库开启归档
检查归档模式

archive log list

开启归档

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

2.主库开启强日志

select force_logging from v$database;

开启强日志

alter database force logging;

3.主库增加standbylog
查看当前日志组大小，位置。

set linesize 200
set pagesize 1000
col group# format 99
col (a.bytes)/1024/1024 format 99
col a.thread# format 9 
col member format a50 
col a.status format a20
select a.group#,(a.bytes)/1024/1024,a.thread#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

整理standby

alter database add standby logfile  group 4 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 5 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 6 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 7 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;

4.主库修改参数

alter system set standby_file_management=MANUAL scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,dgiscdb)' scope=both ;alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog  valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both;alter system set log_archive_dest_2='service=dgiscdb LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dgiscdb' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;alter system set fal_server='dgiscdb' scope=both ;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/u01/app/oracle/oradata/dgiscdb','/u01/app/oracle/oradata/orcl'scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/dgiscdb','/u01/app/oracle/oradata/orcl' scope=spfile;
alter system set standby_file_management=AUTO scope=both sid='*';

5.主库配置静态监听和tns
配置静态监听

SID_LIST_LISTENER_ORCL =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl)     (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)     (SID_NAME = orcl)    )  )LISTENER_ORCL =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    ) )

配置tns

orcl =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)	  (SID = orcl)    )  )dgiscdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.1113)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = dgiscdb)          (SID = dgiscdb)    )  )

**

备库操作

**
1.备库配置静态监听
配置静态监听

cd $ORACLE_HOME/network/admin

SID_LIST_LISTENER_ORCL =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = dgiscdb)     (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)     (SID_NAME = dgiscdb)    )  )LISTENER_ORCL =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.113)(PORT = 1523))    ) )

配置tns

orcl =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)	  (SID = orcl1)    )  )dgiscdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.113)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = dgiscdb)          (SID = dgiscdb)    )  )

2.将主库密码文件传至备库

cd $ORACLE_HOME/dbs
scp -P 22 192.168.172.112:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl .

3.测试主备库监听和tnsname可用性

tnsping orcl
tnsping dgiscdb
sqlplus  sys/oracle@orcl as sysdba
sqlplus  sys/oracle@dgiscdb as sysdba

4创建审计目录

mkdir -p /u01/app/oracle/admin/dgiscdb/adump

5.备库准备脚本(复制数据库)

1）touch /home/oracle/standby_init.oracat >>/home/oracle/standby_init.ora<<ADB_NAME=orclDB_UNIQUE_NAME=dgiscdbDB_BLOCK_SIZE=8192db_create_file_dest='/u01/app/oracle/oradata/dgiscdb'  sga_target = '1G'log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'control_files='/u01/app/oracle/oradata/dgiscdb/dgsicdbcontrol.ctl' A

touch /home/oracle/init.shchmod +x  /home/oracle/init.shcat >>/home/oracle/init.sh<<Asqlplus "/ as sysdba" << !shutdown abortstartup nomount pfile='/home/oracle/standby_init.ora'connect  sys/oracle@orcl as sysdbaconnect  sys/oracle@dgiscdb as sysdba!rman target sys/oracle@orcl auxiliary sys/oracle@dgiscdb  << !run {allocate channel ch001 type disk;allocate channel ch002 type disk;allocate auxiliary channel ch003 type disk;duplicate target database for standby from active databasespfile  parameter_value_convert 'orcl','dgiscdb'  set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dgiscdb','/home/oracle/isc.dbf','/u01/app/oracle/oradata/dgiscdb'  set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dgiscdb'  set db_name='orcl'  set db_unique_name='dgiscdb'  set instance_name='dgiscdb'  set standby_file_management='AUTO'  set log_archive_dest_2='service=orcl LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=orcl'  set sga_target = '1024M'  set db_create_online_log_dest_1 = '/u01/app/oracle/oradata/dgiscdb'  set instance_number = '1'  set control_files='/u01/app/oracle/oradata/dgiscdb/dgiscdbcontrol.ctl'  set fal_server='orcl';release channel ch001;release channel ch002;release channel ch003;}!A

6.备库增加standby log（如果主库创建了standbyredolog那么会自动传递到备库的）

alter database add standby logfile  group 4 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 5 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 6 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
alter database add standby logfile  group 7 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;

7）此时备库在mount状态，等待追归档然后open

alter database open;

发现的问题，如果备库归档目录有文件时会有如下错误

ADG维护常用sql
停止日志应用

alter database recover managed standby database cancel;

开启日志应用

alter database recover managed standby database using current logfile disconnect from session;

时刻监控alert日志
1.查看是否为ADG模式

select 'Using Active Data Guard' ADG from v$MANAGED_STANDBY M, v$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';

2.查看主备日志序列号

select thread#,max(sequence#) from gv$archived_log group by thread#; 

3.查看为什么归档没有同步到standby端

set line 400
col DEST_NAME for a20
col DESTINATION for a10
select dest_id,dest_name,status,fail_date ,ERROR from V$ARCHIVE_DEST where rownum <4;

4.查看主备角色

select database_role,open_mode from v$database;
select sequence#,applied from v$archived_log order by 1;

时时观察alert日志。
5.断档处理（将主库归档拷贝至备库）

alter database register physical logfile '日志文件';