Objective: Perform switchover in heterogenous dataguard setup in 19c
Precheck Steps:
1. Version oracle between primary and DG should be same
select * from v$version;
2. Ensure you use spfile
sho parameter pfile;
3. Value for broker start init ora should be true
sho parameter DG_BROKER_START
4. Esnure DG_BROKER_CONFIG_FILEn => parameter is set to a correct location (for a RAC, the file location is shared, ensure one file is in +DATA and other in +FRA)
sho parameter dg_broker_config_file
5. Ensure tnsnames.ora contains the tns entries for all the DBs involved
tnsping ORACL19C
tnsping ORACL19CSB1
step 1 to 5 output:
6. Ensure a DBConnectIdentifer is properly set to allow all the DBs can reach among themselves and among all the instances.
dgmgrl
show database verbose <dbname>;
Output:
7. Ensure SID_LIST_LISTENER entry is there to let DGMGRL has access to restart the instance.The StaticConnectIdentifier should be in sync with the SID_LIST_LISTENER entry. (we can use netmgr to do it - open netmgr, click on listener and expand it, go select Database service, and then add database to enter the relevant details
lsnrctl status
8. Verify primary is in archivelog mode
archive log list
9. Ensure compatible parameter is equal to or greater than 10.2.0.1.0
sho parameter compatible
10. Ensure the primary/standby init ora parameter files are set properly.
sho parameter DB_NAME
sho parameter DB_UNIQUE_NAME
sho parameter LOG_ARCHIVE_CONFIG
sho parameter CONTROL_FILES
sho parameter LOG_ARCHIVE_DEST_1
sho parameter LOG_ARCHIVE_DEST_2
sho parameter REMOTE_LOGIN_PASSWORDFILE
sho parameter LOG_ARCHIVE_FORMAT
sho parameter FAL_SERVER
sho parameter STANDBY_FILE_MANAGEMENT
11. dgmgrl ->"Show configuration verbose" to check the health of the broker configuration
12. dgmgrl ->"Show database verbose" for primary and standby to ensure the transport - ON and apply -ON are active in a active configuration
13. Do a archivelog switch and verify if that gets transported and applied smoothly
alter system archive log current; -- to switch all the threads in one go.
####Actual switchover Task:
14. Comment out or cancel any scheduled jobs in primary/standby (like backup):
No jobs
13:52:06 SQL> !crontab -l
no crontab for oracle
13:55:38 SQL>
15. Ensure brok_val note checks are complete well in advance, just run through it once , if time permits.
> the prechecks are done
The precheck step 1 to 13 should be finished
16. Ensure the static connect id for dgmgrl is set in listener.ora file
We should have already verified this by now
>> the <dbname>_dgmgrl isnt set for now in sby where it showed up in the dgmgrl show command. Primary it didnt show the _dgmgrl value.
17. LOCAL_LISTENER parameter resolves to the listeners of the local host properly
>> primary all set but not in sby (since listener is default values)
18. Set the log_archive_max_process value to 4
create pfile=<> from spfile;
sho parameter log_Archive_max_process
19. Set the log_file_name_convert value relevently to ensure the online redolog gets cleared
sho parameter log_file_name_convert
>>> OMF is used in both source and target
20. Manually clear the online log in case needed
SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF
WHERE L.GROUP# = LF.GROUP# AND L.STATUS
NOT IN ('UNUSED','CLEARING','CLEARING_CURRENT');
ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>;
>>> NA
21. Verify that there are no large gaps
SELECT THREAD#, SEQUENCE# FROM V$THREAD;
22. Validate it in standby
SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
23. Temp TS/file presence verification
SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE
FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;
24. Turn off the apply lag/delay
dgmgrl > SHOW DATABASE <standby-db_unique_name> DELAYMINS;
DGMGRL> EDIT DATABASE <standby-db_unique_name> SET PROPERTY 'DELAYMINS'='0';
sby:
DelayMins = '0'
no need to edit this.
25. Check jobs running and stop them/disable them
SELECT * FROM DBA_JOBS_RUNNING;
SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED
FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';
SHOW PARAMETER job_queue_processes;
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID='*';
EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );
Prmry:
14:02:20 SQL> SELECT * FROM DBA_JOBS_RUNNING;
no rows selected
Elapsed: 00:00:00.08
14:03:39 SQL>
26. Stop any middle tier as apply
opmnctl stopall
>> None
27. Enable log archive tracing in both primary and standby
28. Tail alert log of both primary and standby
>> done
29. Additional fallback option (create flash back point)
30.Ensure sys passwords for both primary and sys are OK. By trying to connect to sys@<TnsEntry>
31. Switchover (Switchover to standby)
DGMGRL> CONNECT SYS/password@primary <ensure @primary
DGMGRL> SWITCHOVER TO <standby database name>;
Post switchover Task:
32. Adjust the lag time
DGMGRL> EDIT DATABASE <standby-db-unique-name> SET PROPERTY 'DELAYMINS'='<saved_value>'; ->default 0 (hence leave as is)
>>no lag added
33. Edit the logarchive trace andd put it to its previous value
34. Edit the job_queue_processes
SQL> ALTER SYSTEM SET job_queue_processes=<value saved> scope=both sid='*';
>> no need to revert, since we didnt change it
35. Enable any disabled job
EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name captured>);
>> none, we didnt change it.
36. Drop the flash back point
DROP RESTORE POINT SWITCHOVER_START_GRP;
new prmry:
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;
Restore point dropped.
SQL> select * from v$restore_point;
no rows selected
SQL>
37. Ensure to reverse the rman settings manually
=================> So we finished the switchover of heterogenous oracle dataguard.
Thanks :)
