环境：
| 主机ip | dg角色 | db_name | db_unique_name |
| 192.168.56.110 | primary | SHPD | SHPD |
| 192.168.56.111 | standby | SHPD | SHST |
主机必要条件check
select NAME,LOG_MODE,FORCE_LOGGING from v$database; alter database force logging;
前提
-
SHPD和SHST都已安装好oracle软件，配置好环境变量，listener.ora，tnsnames.ora
-
SHPD数据库已创建好，启动到open，SHST启动到nomount
-
目录结构同clone db
-
init.ora也使用clonedb的，db_unique_name需要修改，其余的下面在具体配置
tnsname.ora内容：
shpd = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.110)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SHPD) ) ) shst = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.111)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SHST) ) )
配置主库参数
--参数check
col name format a30;
col value format a200;
set linesize 1000;
set pagesize 1000;
select name,value from v$parameter
where name in('fal_server',
'fal_client',
'standby_file_management',
'db_file_name_convert',
'log_file_name_convert',
'db_name',
'db_unique_name',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_config')
order by name;
--主备db和log路径转换
select 'datafile',name from v$datafile
union all
select 'tempfile',name from v$tempfile
union all
select 'logifle',member from V$LOGFILE where TYPE='ONLINE';
ALTER SYSTEM SET db_file_name_convert='/u01/oradata/SHST/datafile','/u01/oradata/SHPD/datafile','/u01/oradata/SHST/tempfile','/u01/oradata/SHPD/tempfile' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/u01/oradata/SHST/onlinefile','/u01/oradata/SHPD/onlinefile' scope=spfile;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(SHPD,SHST)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/SHPD/arch valid_for=(all_logfiles,all_roles) db_unique_name=SHPD' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=SHST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SHST' SCOPE=BOTH;
ALTER SYSTEM SET fal_server='SHST' SCOPE=BOTH;
ALTER SYSTEM SET fal_client='SHPD' SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
配置从库参数
ALTER SYSTEM SET db_unique_name='SHST' scope=spfile; ALTER SYSTEM SET db_name='SHPD' scope=spfile; ALTER SYSTEM SET db_file_name_convert='/u01/oradata/SHPD/datafile','/u01/oradata/SHST/datafile','/u01/oradata/SHPD/tempfile','/u01/oradata/SHST/tempfile' scope=spfile; ALTER SYSTEM SET log_file_name_convert='/u01/oradata/SHPD/onlinefile','/u01/oradata/SHST/onlinefile' scope=spfile; ALTER SYSTEM SET log_archive_config='DG_CONFIG=(SHPD,SHST)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/SHST/arch valid_for=(all_logfiles,all_roles) db_unique_name=SHST' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_2='SERVICE=SHPD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SHPD' SCOPE=BOTH; ALTER SYSTEM SET fal_server='SHPD' SCOPE=BOTH; ALTER SYSTEM SET fal_client='SHST' SCOPE=BOTH; ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
添加日志文件
根据primary的online redo log大小和组数，按规则添加Standby online log，添加的group的编号最好有预留，方便扩展
select * from V$LOGFILE;
select * from v$log;
select * from V$STANDBY_LOG;
/*主库添加Standby online log*/
alter database add standby logfile
group 11 '/u01/oradata/SHPD/onlinefile/standbyredo04.log' size 50M,
group 12 '/u01/oradata/SHPD/onlinefile/standbyredo05.log' size 50M,
group 13 '/u01/oradata/SHPD/onlinefile/standbyredo06.log' size 50M,
group 14 '/u01/oradata/SHPD/onlinefile/standbyredo07.log' size 50M;
/*备库添加Standby online log*/
alter database add standby logfile group 10('/u01/app/oracle/oradata/intedb/standby_redo10.log') size 50M;
alter database add standby logfile group 11('/u01/app/oracle/oradata/intedb/standby_redo11.log') size 50M;
alter database add standby logfile group 12('/u01/app/oracle/oradata/intedb/standby_redo12.log') size 50M;
alter database add standby logfile group 13('/u01/app/oracle/oradata/intedb/standby_redo13.log') size 50M;
alter database add standby logfile group 14('/u01/app/oracle/oradata/intedb/standby_redo14.log') size 50M;
备库同步主库数据
生成standby controlfile，并复制到standby目录（忽略）
--ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';
--cp /tmp/control01.ctl /tmp/control02.ctl
--scp /tmp/control01.ctl /tmp/control02.ctl oracle@192.168.56.111:/u01/oradata/SHST/controlfile/Standby数据恢复，Standby上执行
##Method1 在线还原数据，需要配置配置静态监听，STANDBY上执行
rman target sys@SHPD auxiliary sys@SHST duplicate database for standby from active database nofilenamecheck;
##Method2利用备份集，有target连接
先备份，duplicate复制库
rman target sys@SHPD auxiliary / duplicate target database for standby;
##Method3利用备份集，无target连接
先备份，duplicate复制库
rman auxiliary / duplicate database for standby backup location '/data/backup' nofilenamecheck;
--查看DB状态 select instance_name,status from v$instance; --打开Standby数据库 alter database open read only; --开启实时日志应用，启用Real-Time Apply；开启MRP进程，停止redo应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; --关闭MRP进程（停止应用日志），开启redo应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --使用arch同步模式 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --验证测试data guard ALTER SYSTEM ARCHIVE LOG CURRENT; select name,controlfile_type,open_mode,protection_mode, protection_level,database_role,SWITCHOVER_STATUS,db_unique_name, guard_status,PRIMARY_DB_UNIQUE_NAME from v$database; OPEN_MODE ------------------------------------------------------------ READ ONLY WITH APPLY SELECT * FROM v$dataguard_stats; SELECT NAME,applied FROM v$archived_log order by COMPLETION_TIME desc; select * from V$ARCHIVE_DEST; select * from v$archive_dest_status; SELECT * FROM v$standby_log; Note：测试的时候出现了不是实时应用日志，需要手动归档日志后才同步，这一般是standby或primary上的onlinelog或standby log有问题，解决办法是重建日志或scp日志 /*日志传输状态的监控，主库执行，Method1*/ --第一步：在主库执行如下语句获得最新归档序列号 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; --第二步：在主库执行确认最新归档的日志是否已经传输至备库 SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; /*日志传输状态的监控，主库执行，Method2*/ select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log where standby_dest='YES'; select 'Primary :' "DB Role",thread#,max(sequence#) from v$archived_log where standby_dest='NO' group by thread# union select 'Standby :' "DB Role",thread#,max(sequence#) from v$archived_log where standby_dest='YES' and applied='YES' group by thread# order by thread#;