DG

DATAGUARD 配置过程:
alter database force logging; -----将数据库置为强制且志模式

在主库上创建standby logfile:

alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redostd01.log' size 100m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redostd02.log' size 100m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redostd03.log' size 100m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redostd04.log' size 100m;

SELECT group#,type,member from v$logfile where type='STANDBY';
SELECT GROUP#,DBID,THREAD#,SEQUENCE#,STATUS from v$standby_log;

主库修改参数

DB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_2='SERVICE=SBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='SBDB','PROD1'
LOG_FILE_NAME_CONVERT= 'SBDB','PROD1'
STANDBY_FILE_MANAGEMENT=AUTO

从库上配置参数

DB_UNIQUE_NAME=SBDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,PROD1)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_2='SERVICE=PROD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB'
LOG_FILE_NAME_CONVERT= 'PROD1','SBDB'
STANDBY_FILE_MANAGEMENT=AUTO

在主库上执行:

rman target sys/oracle@PROD1 auxiliary sys/oracle@SBDB
duplicate target database for standby from active database dorecover nofilenamecheck;  --11g才有

开启备库的日志应用:

recover managed standby database disconnect;     ------12C以后的命令

--告诉物理备库开始应用
alter database recover managed standby database using current logfile disconnect from session;

--取消备库的日志应用
alter database recover managed standby database cancel;   

--查看没有应用的归档日志的情况
select APPLIED,SEQUENCE# from v$archived_log; 
   
--查看归档日志的情况
select sequence#,name,applied from v$archived_log;   
--查看备库的日志文件
select * from v$standby_log;   

查看数据库的情况:
select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS from v$database;

alter database commit to switchover to primary;  --将物理备库切成主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY ;  --切换成物理备库

10/10 22:46
data guard:

备库:物理备库，逻辑备库，snapshot
目前基本都是物理备库。

data guard的模式:

1. maximum availablility (确保数据万无一失，零数据丢失)在这种模式下，
   实在是不能将数据写到备库上了这种模式转换成maximum performance，目前生产环境基本都是这种模式。

2.maximum performance 保证了最大的性能，但是有一定的数据丢失的可能性，主库不会管备库是否收到数据。

3.maximum protection 如果 数据传不到备库，主库将停止，生产环境基本不用。

DG相关的后台进程:
ARCH:把归档发给备库
RES: 接受日志
FAL:一解决REDO的间隔GAP
MRP:日志被应用，恢复的过程
LNS: LOG传送

DG的正确启库和关库的流程:
启库:
1.启动主库，开启监听
2.启动备库，开启监听

--告诉物理备库开始应用归档日志，开启实时日志应用REAL-TIME APPLY
--在备库执行
alter database recover managed standby database using current logfile disconnect from session;

检查主库和备库，分别在主库和备库上执行:
select process,sequence#,status,delay_mins from vdatabase ;

停库:
备库上的操作:
1、取消备库上的日志应用
alter database recover managed standby database cancel;
2、关闭备库
shutdown immedate
主库上的操作:
关闭主库
shutdown immedate

10/22 21:32
ADG switchover过程:

select open_mode,database_role from v$database;
select process,sequence#,status,delay_mins from v$managed_standby;

主库执行:

select switchover_status,from v$database;
alter database commit to switchover to physical standby with session shutdown;
shutdown immedate;
startup mount;

备库执行:

select switchover_status from v$database;
alter database commit to switchover to primary with session shutdown;
alter database open;
select open_mode,database_role from v$database;

到以前的主库执行,既现在的备库:

select open_mode,database_role from v$database;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

更改保护模式

alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;

开启主库归档传输:

alter system set log_archive_dest_state_2='enable';

关闭主库归档传输:

alter system set log_archive_dest_state_2='defer';

查询GAP:

select thread#,low_sequence#,high_sequence# from v$archive_gap;
select * from v$archive_dest_status;

su - oracle
sqlplus / as sysdba
startup

备库软件装好,启动监听 可以不建库

137.5 137.6

--主库操作
select open_mode from v$database;
lsnrctl status
lsnrctl start

--备库操作
lsnrctl start --备库启动监听
desc vdatabase; ==yes

--主库操作
show parameter spfile;
strings xxxx.
show parameter log_archive_config
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter FAL_SERVER
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT

--备库操作
sqlplus / as sysdba
shutdown immediate
startup nomount
lsnrctl status --数据库启动到nomount状态,监听是开启的

--主库操作
rman target sys/oracle@PROD1 auxiliary sys/oracle@SBDB
duplicate target database for standby from active database dorecover nofilenamecheck;
exit
sqlplus / as sysdba

select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS from v$database;

--备库操作
sqlplus / as sysdba
select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS from vmanaged_standby; --备库上的归档进程

--主库操作
select process,sequence#,status,delay_mins from v$managed_standby;

--在备库执行
alter database recover managed standby database using current logfile disconnect from session;
select process,sequence#,status,delay_mins from vdatabase

alter database recover managed standby database cancel; ----取消备库的日志应用
alter database open read only;
select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS from vdatabase; --read only with apply

--主库操作
create table kkk(id int);
insert into kkk values(1);
--从库
desc kkk;
select * from kkk;
--主库操作
drop table kkk;

--从库
lsnrctl start
ps -ef | grep lsnr
ps -ef | grep oracle --没启动库
sqlplus / as sysdba

切换过程
--主库切换成备库
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
start mount;

--备库执行
select switchover status from vdatabase; --PRIMARY

到以前的主库执行,既现在的备库:

select open_mode,database_role from v$database; --mounted
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

--备库执行,查看状态 ARCH(归档进程),RFS(接收日志进程),MRP0(应用日志进程)
select process,sequence#,status,delay_mins from v$managed_standby;

--主库执行,查看状态,ARCH(归档进程),LNS(发送日志进程)
select process,sequence#,status,delay_mins from v$managed_standby;

--从库查询,Gap, 延迟,处理方法,把主库日志拷贝到从库,在从库注册
select thread#,low_sequence#,high_sequence# from v$archive_gap;