startup mount;
alter database archivelog;
archive log list;
show parameter db_recovery_file
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
alter system set db_recovery_file_dest_size=5g scope=both;
alter database force logging;
alter system switch logfile;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog4.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog5.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog6.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog7.log' size 200M;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both
alter system set log_archive_dest_2='service=stand async valid_for=(online_logfile,primary_role) db_unique_name=stand' scope=both;
alter system set standby_file_management=auto;
alter system set log_archive_config='DG_CONFIG=(orcl,stand)';
alter system set fal_client=orcl;
alter system set fal_server=stand;
scp orapworcl oracle@192.168.1.81:/u01/app/oracle/product/12.2.0/db_1/dbs/orapwstand
create pfile='/home/oracle/initstand.ora' from spfile;
startup pfile=initstand.ora nomount
rman target sys/sys123@orcl auxiliary sys/sys123@stand
duplicate target database for standby from active database dorecover NOFILENAMECHECK;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
select open_mode, controlfile_type from v$database;
alter database open read only;
alter database recover managed standby database cancel;
select * from v$logfile;
Performance mode change step
==============================================================
In Primary
alter system set log_archive_dest_2='service=stand affirm lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=stand' scope=both;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
alter database open;
In Standby
alter system set log_archive_dest_2='service=orcl affirm lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=both;
SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;
Performance Check
select SOURCE_DBID,name,value,unit,TIME_COMPUTED,DATUM_TIME,con_id from v$dataguard_stats
select thread#,max(sequence#) from v$archived_log group by thread#;
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# order by 1;
select * from v$dataguard_status;
SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_last_file,
a.applied_seq stdby_last_file, CASE WHEN b.last_seq - a.applied_seq > 2 THEN '=>' ELSE to_char(b.last_seq - a.applied_seq) END archive_difference, TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
FROM (SELECT resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY resetlogs_id, thread#) a,
(SELECT resetlogs_id, thread#, MAX (sequence#) last_seq
FROM v$archived_log
GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;
select max(sequence#),thread#, applied from v$archived_log group by thread#, applied;
Errors in file /u01/app/oracle/diag/rdbms/stand/orcl/trace/orcl_ora_5913.trc:
ORA-00313: open failed for members of log group 7 of thread 0
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/orcl/sbylog7.log'
ORA-27037: unable to obtain file status
alter system set standby_file_management=manual;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter system set standby_file_management=auto;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
Switchover Operation Procedure:
Status cheque:
select name,open_mode,database_role from v$database;
select switchover_status from v$database;
On primary : alter database commit to switchover to standby;
shutdown immediate
startup nomount
alter database mount standby database alter database recover managed standby database disconnect from session;
On standby : alter database commit to switchover to primary;
