Oracle Data Guard 12c Windows CDB21 primary & CDB22 physical standby
----set both databases to archivelog mode------
----CDB21 primary
$CDB21>
SQL>SELECT force_logging FROM v$database;
SQL>alter database force logging;
SQL>SELECT force_logging FROM v$database;
SQL>select group#, thread#, bytes from v$log;
---- recommended one more than redo log
SQL>
	alter database add standby logfile thread 1 ('E:\APP\ORACLE\ORADATA\CDB21\standby_log_1.log') size 50M;
	alter database add standby logfile thread 1 ('E:\APP\ORACLE\ORADATA\CDB21\standby_log_2.log') size 50M;
	alter database add standby logfile thread 1 ('E:\APP\ORACLE\ORADATA\CDB21\standby_log_3.log') size 50M;
	alter database add standby logfile thread 1 ('E:\APP\ORACLE\ORADATA\CDB21\standby_log_4.log') size 50M;
SQL>select group#, thread#, bytes  from v$standby_log;
SQL>alter user sysdg identified by oracle123 account unlock;
SQL>grant sysdg to c##jasonw identified by oracle123 container=all;
SQL>!clear

--SQL>alter system set log_archive_dest_1='Location=USE_DB_RECOVERY_FILE_DEST valid_for(ALL_LOGFILES, ALL_ROLES) db_unique_name=CDB21' scope=both;
--SQL>alter system set log_archive_dest_1='Location=C:\app\oracle\recovery_area valid_for(ALL_LOGFILES, ALL_ROLES) db_unique_name=CDB21' scope=both;
--SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

alter system set log_archive_config='DG_CONFIG=(CDB21,CDB22)' scope=both;
--alter system set log_archive_dest=' ' scope=both; --cannot use with DB_RECOVERY_FILE_DEST
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB21' scope=both;
alter system set log_archive_dest_2='SERVICE=CDB22 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB22' scope=both;
--alter system set log_archive_dest_3='SERVICE=CDB22 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB22' scope=both;

SQL>alter system set log_archive_dest_state_1='enable' scope=both;
SQL>alter system set log_archive_max_processes=4 scope=both;
SQL>alter system set log_archive_config='dg_config=(CDB21, CDB22)' scope=both;
SQL>alter system set standby_file_management='auto' scope=both;

SQL>show pdbs;
SQL>alter system set fal_server='CDB22' scope=both;
SQL>alter system set fal_client=CDB21 scope=both;
SQL>alter system set archive_lag_target=180 scope=both;
SQL>quit
----here below CDB22
$CDB22>mkdir -p C:\app\oracle\admin\CDB21\adump
$CDB22>mkdir -p C:\app\oracle\admin\CDB21\
$CDB22>mkdir -p E:\app\oracle\oradata\CDB22\C21P1
$CDB22>mkdir -p E:\app\oracle\oradata\CDB22\pdbseed
....
$CDB22>mkdir -p E:\app\oracle\recovery_area\CDB21
$CDB22>vi C:\app\oracle\product\12.1.0\dbhome_1\database\initCDB22.ora
DB_NAME='CDB22'
DB_DOMAIN=''
:wq!
------------even better CDB22 pfile-------------------------------
###########################################
# Database Identification
###########################################
db_domain=""
db_name="CDB22"
fal_client='CDB22'
fal_server='CDB21'
log_archive_config='DG_CONFIG=(CDB21,CDB22)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB22'
log_archive_dest_2='SERVICE=CDB21 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB21'
log_archive_dest_3=' '
log_archive_max_processes=4
standby_file_management=auto
archive_lag_target=1800
-------------------------------------------------
$CDB22>vi /etc/oratab
CDB22:oracle_home ....:y
grid_home .....
$CDB22> . oraenv
CDB22
$CDB22>sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile="C:\app\oracle\product\12.1.0\dbhome_1\database\INITcdb22.ora";
SQL>quit
$CDB22>
SET ORACLE_SID=CDB22
echo %ORACLE_SID%

----make sure password files are the same password for active duplicate
$CDB22>rman target sys@CDB21 auxiliary sys@CDB22
$CDB22>rman target sys/xxxxxxxx@CDB21 auxiliary sys/xxxxxxxx@CDB22
target password
auxiliary password
???? allocate channel
???? allocate auxiliary channel

RMAN>
run {
duplicate target database for standby from active database
	spfile
	PARAMETER_VALUE_CONVERT 'CDB21', 'CDB22'
        set db_unique_name='CDB22'
        SET DB_FILE_NAME_CONVERT 'CDB21\C21P1', 'CDB22\C22P1', 'CDB21', 'CDB22', 'C21P1', 'C22P1'
        SET LOG_FILE_NAME_CONVERT 'CDB21', 'CDB22'
	    set fal_server='CDB21'
		nofilenamecheck;
}

	
sql 'alter database recover managed standby database disconnect;
RMAN>
----spfile init parameters are duplicated over and need to change
$CDB21>sqlplus / as sysdba
SQL>alter system set log_archive_dest_2='SERVICE=CDB22 REOPEN=15 ASYNC valid_for=(ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=CDB22' scope=both;
SQL>alter system set log_archive_dest_2='SERVICE=CDB22 NOAFFIRM ASYNC NET_TIMEOUT=60 DELAY=180 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=CDB22' scope=both;
SQL>!clear
$CDB22>
alter system set log_archive_config='DG_CONFIG=(CDB21,CDB22)' scope=both;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB22' scope=both;
alter system set log_archive_dest_2='SERVICE=CDB21 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB21' scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set standby_file_management='auto' scope=both;
alter system set archive_lag_target=1800 scope=both;
---- by RMAN duplicate ----alter database mount standby database;
alter database recover managed standby database disconnect from session;
$CDB22>
RMAN>quit
RMAN>!clear
CDB21>
select max(sequence#) from v$archived_log where applied='YES';
CDB22>
select max(sequence#) from v$archived_log where applied='YES';
CDB21>
SQL>ALTER SYSTEM SWITCH LOGFILE; ----asynchronorous, fast but ARCH process could fail, at risk
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT; ----synchronous, slow, waits for ACK, safer
CDB21>
select max(sequence#) from v$archived_log where applied='YES';
CDB22>
select max(sequence#) from v$archived_log where applied='YES';
----check both CDB21 & CDB22 ----
select db_unique_name,database_role,open_mode from v$database;
select name from v$datafile;
select group#,type,member from v$logfile;
SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS FROM V$MANAGED_STANDBY;
---- stop redo apply ----
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
---- start real-time redo apply ----
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

---- post DG config ----
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES';
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
alter system switch logfile;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
---- on standby ----
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
---- if to start ---- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;
SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;
---- on standby ----
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';
select db_name,
	hostname,
	al.thrd,
	almax,
	lhmax,
	applied_time,
	to_char(almax - lhmax),
	log_age_minutes,
       dg_stat_applylag_sec,
       dg_stat_transportlag_sec
	from
	(select name db_name from v$database),
	(select upper(substr(host_name,1,(decode(instr(host_name,'.'),0,length(host_name),
 	(instr(host_name,'.')-1))))) hostname
 	from v$instance),
	(select thread# thrd, max(sequence#) almax
 	from v$archived_log
 	where resetlogs_change#=(select resetlogs_change# from v$database)
 	group by thread#) al,
	(select thread# thrd, max(sequence#) lhmax, max(first_time) applied_time
 	from v$log_history
 	where first_time=(select max(first_time) from v$log_history)
 	group by thread#) lh,
	(select thread# thrd, round(to_number(sysdate - max(first_time))*24*60) log_age_minutes from v$log_history group by thread#) la,
       (select to_number(substr(value,instr(value,':',1,2)+1,length(value))) + 60 * to_number(substr(value,instr(value,':',1,1)+1,2)) dg_stat_applylag_sec from v$dataguard_stats where name ='apply lag'),
       (select to_number(substr(value,instr(value,':',1,2)+1,length(value))) + 60 * to_number(substr(value,instr(value,':',1,1)+1,2)) dg_stat_transportlag_sec from v$dataguard_stats where name ='transport lag')
	where al.thrd = lh.thrd
	and la.thrd(+)=lh.thrd;

	----DEFER has a bug 12.1 that archivelog is not deleted-----
	SQL>alter system set log_archive_dest_state_2 = ENABLE;