Switchover oracle database 19c
Oracle 19c-Step by Step Manual Switchover
PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.
Use full commands you may need throw the process.
-- shutdown Applications which are running and connection to the DB.
-- backup crontab
-- select database_role from v$database; #check DB role
-- select name, open_mode, database_role from v$database; # above or
this command
-- ALTER SYSTEM SWITCH LOGFILE;
-- SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- check --
-- alter system set log_archive_dest_state_2='ENABLE' scope=both;
-- select process, status, sequence# from v$managed_standby; #check the MRP0
or MRP process is running
-- ALTER DATABASE RECOVER managed
standby database cancel; #Cancel managed recovery operations.
-- alter database recover managed standby database using current logfile
disconnect from session; # online redolog apply via redologs
-- ALTER DATABASE RECOVER managed
standby database parallel 10
disconnect; #logapply via arcive log
-- lsnrctl start LISTENER
-- Always see the logfile when you start DB to check whether the
parameters are correct or not.
-- NOTE: when you add tempfiles in the Primary database, new tempfiles
will not added automatically in the Physical Standby database.
-- The tempfile will NOT automatically be created in the physical
standby.
Following parameters are
related to the Primary and Standby database. Verify the parameter before you
start with Switch Over.
show parameter
service_names;
show parameter
instance_name;
show parameter
log_archive_dest_2;
show parameter
fal_server;
show parameter
fal_client;
show parameter
db_file_name_convert;
show parameter
log_file_name_convert;
select name,
open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
EDUDB READ WRITE PRIMARY
select name,
open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
EDUDB MOUNTED PHYSICAL STANDBY
STEP 1
Starting with Switchover.
Run the following steps on
Primary:
Alter system switch
logfile;
select
switchover_status from v$database;
alter database commit to switchover to physical standby with session
shutdown;
shutdown immediate;
startup mount;
STEP 2
Run this on standby:
select
switchover_status from v$database;
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup
NOTE:
You can stop listener
and modify it and start it again, If need it. Make sure that you have correct
entry in tnsname.ora. Better if you test with command tnsping.
STEP 3
Execute following
command on Standby:
alter database
recover managed standby database using current logfile disconnect from session
;
select
name,db_unique_name,database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
---------
------------------------------ ----------------
EDUDB EDUDB02 PHYSICAL STANDBY
select process, status,
sequence# from v$managed_standby;
PROCESS STATUS
SEQUENCE#
---------
------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 274
RFS IDLE 0
RFS IDLE 0
MRP0
WAIT_FOR_LOG 274
At this Point, we are done with switchover:
Extra steps:
STEP 4
Check whether the standby is in sync:
On both Primary and
Standby:
On Primary:
SELECT ARCH.THREAD#
"Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD#
,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#
,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence
Applied
----------
---------------------- ---------------------
1 273 273
1 273 273
select max(sequence#)
from v$log_history;
MAX(SEQUENCE#)
--------------
273
On Standby:
SELECT ARCH.THREAD#
"Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD#
,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#
,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence
Applied
----------
---------------------- ---------------------
1 273 273
select max(sequence#)
from v$log_history;
MAX(SEQUENCE#)
--------------
273
SELECT THREAD#
"Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2
3 4
Thread Last Sequence Generated
----------
-----------------------
1
273
Trouble shooting standard check:
1)
Make sure the
parameter is enabled on both Primary and Standby.
--alter system set
log_archive_dest_state_2=defer;
alter system set
log_archive_dest_state_2=enable;
2)
-- required for jdbc
connection. You can set the value anyway it doesn't harm
SQL> alter system
set instance_name='edudb' scope=spfile;
3)
Test connection with tnsping
on both direction.
4)
-- check the
service_name parameter before and edit the value as follow. You can alter
multiple service_name as follow, if required.
alter system set
service_names=' edudb01',' edudb01.th.com',' edudb02'
,' edudb02.th.com' scope = both;
No comments:
Post a Comment