Primary database
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;
Demo pfile
stand.__data_transfer_cache_size=0
stand.__db_cache_size=637534208
stand.__inmemory_ext_roarea=0
stand.__inmemory_ext_rwarea=0
stand.__java_pool_size=16777216
stand.__large_pool_size=33554432
stand.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=671088640
stand.__sga_target=989855744
stand.__shared_io_pool_size=50331648
stand.__shared_pool_size=234881024
stand.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/stand/control01.ctl','/u01/app/oracle/oradata/stand/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_unique_name='stand'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STAND'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcl,stand)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stand'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stand'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stand'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Directory standby
-----------------
/u01/app/oracle/fast_recovery_area/stand
/u01/app/oracle/admin/orcl/adump
/u01/app/oracle/oradata/stand
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;
Required IPC RDMAV_FORK_SAFE environment not set
C:\Users\Admin>adrci
adrci>show homes
adrci> set home diag\rdbms\stand\lan
adrci> show control
adrci> set control (SHORTP_POLICY=72);
adrci> purge -age 4320 -type trace
