Friday, 16 June 2023

Oracle Standby Switch over and Switch Back activity step by step

 Overview steps:


Step:1 Check the db name, open mode, database role of the primary and standby databases.

Step:2 Select switchover status on primary & standby db.

Step:3 Check that there is no active users connected to the databases.

Step:4 Switch the current online redo log file on primary database and verify that it has been applied in the standby database.

Step:5 Connect with primary database and initiate the switchover.

Step:6 Bounce the primary db and check the switchover status.

Step:7 Then convert the physical standby into primary db.(stop the MRP process)

Step:8 Open the new standby db recovery mode .

Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.

Step:1 Check the db name, open mode, database role of the primary and standby databases.


Precheck

---------------


SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;

set lines 2000

set pages 2000

set owner for a13

set username for a12

col host for a50

col username for a15

col DB_LINK for a30

col CREATED for a30


select * from dba_db_links ;

/

select owner,DB_LINK from dba_db_links;


set lines 300 pages 1000

col OWNER for a30

col JOB_NAME for a30

SELECT OWNER, JOB_NAME, START_DATE,NEXT_RUN_DATE,STATE FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';


col JOB_ACTION for a20

 col START_DATE for a20

 col LAST_START_DATE for a20

 col LAST_RUN_DURATION for a20

 col NEXT_RUN_DATE for a30

select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,STATE from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';


col JOB_ACTION for a20

 col START_DATE for a20

 col LAST_START_DATE for a20

 col LAST_RUN_DURATION for a20

 col NEXT_RUN_DATE for a30

select JOB_NAME,JOB_ACTION,LAST_RUN_DURATION,NEXT_RUN_DATE,STATE from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';


SELECT db_unique_name, open_mode, database_role FROM v$database;


column destination format a35 wrap

column process format a7

column archiver format a8

column ID format 99

column mid format 99

select dest_id "ID",destination,status,target,  schedule,process,mountid  mid from v$archive_dest order by dest_id;

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

select * from V$DATAGUARD_CONFIG

SELECT FS_FAILOVER_STATUS "FSFO STATUS",FS_FAILOVER_CURRENT_TARGET TARGET,FS_FAILOVER_THRESHOLD THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" FROM  V$DATABASE;


SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;  

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT fs_failover_observer_present, fs_failover_observer_host FROM v$database;


set lines 100 pages 999

col member format a70

select st.group#,st.sequence#,ceil(st.bytes / 1048576) mb,lf.member

from v$standby_log st,v$logfile lf

where st.group# = lf.group#

/


SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 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; 


3) show parameter log_archive_config

        show parameter dg

       ## SELECT MESSAGE FROM V$DATAGUARD_STATUS;


4) select name,flashback_on from v$database;

5) select name from v$tempfile;

6) select value from v$diag_info where name ='Diag Trace';

7) show parameter recovery;

show parameter job

show parameter aq


select name,bytes/1024/1024/1024 from v$tempfile;

8) check crontab and scheduler if any state and rman backup will scheduled during activity if yes disable it



Implementation

--------------

  1) Ensure temporary files exist on the standby database that match the temporary files on the primary database

  2) Ensure Flashback is on both D.C and D.R

  

run command on primary database(production) fist we need to run primary database

compatible               = "11.2.0"

  log_archive_dest_1       = "LOCATION=/db1/orafra/svfe VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=svfe"

  log_archive_dest_2       = "SERVICE=svfedr LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=svfedr"

  log_archive_dest_state_1 = "ENABLE"


 alter system switch logfile


switchover primary to standby(Initiate the switchover on the primary database)

--------------------------------


select name,flashback_on from v$database;

select name,open_mode,database_role,controlfile_type from v$database;

select name,database_role,switchover_status from v$database;


NAME      DATABASE_ROLE    SWITCHOVER_STATUS

--------- ---------------- --------------------

EASYDB      PRIMARY          TO STANDBY


SQL>


!ps -ef|grep rman


@up

@dr

select name,flashback_on from v$database;

alter database flashback on;

select name,flashback_on from v$database;


SELECT * From dba_jobs_running;

select * from dba_scheduler_running_jobs;


col OWNER for a30

col JOB_NAME for a30

SELECT OWNER, JOB_NAME, START_DATE,NEXT_RUN_DATE,STATE FROM DBA_SCHEDULER_JOBS

WHERE ENABLED='TRUE';


SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


show parameter job

show parameter aq

SQL> 

SQL> alter system set job_queue_processes=0 ;


System altered.

SQL> alter system set aq_tm_processes=0 ;


System altered.

show parameter job

show parameter aq


select name,database_role,switchover_status from v$database;


select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session  where username <>'SYS';

select name,database_role,switchover_status from v$database;

alter database commit to switchover to physical standby with session shutdown  -------

eg


SQL>  select name,database_role,switchover_status from v$database;


NAME            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- ---------------- --------------------

EASYDB            PRIMARY          TO STANDBY


SQL> alter database commit to switchover to physical standby with session shutdown; 


Database altered.

Shut down and then mount the former primary database.

-------------------------------------------

shut immediate--

startup mount --

or

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;


select name,open_mode,database_role,controlfile_type from v$database;

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;


Archived Log entry 2278737 added for T-1.S-1136245 ID 0x109a137e LAD:1

Completed: ALTER DATABASE   MOUNT

2022-05-07T02:09:27.821104+04:00

ARC7 started with pid=42, OS id=941

Starting background process ARC8

2022-05-07T02:09:27.834239+04:00

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY *

Completed Successfully

   ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY *

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

2022-05-07T02:09:27.861561+04:00


wait for switch over from standy to primary (Start Redo Apply on the new physical standby database once switch over compleleted on standby database)


------------------------------------------

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION


select name, open_mode, db_unique_name, database_role from v$database;

select name,open_mode,database_role,controlfile_type from v$database;

@dr

==================================

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

After completing activity on primary database (production)

convert standby database to primary database

1) SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;


@up

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;


2)select name,flashback_on from v$database;


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


alter database flashback on;

select name,flashback_on from v$database;


3) select name from v$tempfile;

4) select value from v$diag_info where name ='Diag Trace';

5) show parameter recovery;

6) check crontab and scheduler if any state and rman backup will scheduled during activity if yes disable it

1)select name, switchover_status from v$database;

if require older version

 Cancel the MRP process

SQL> alter database recover managed standby database cancel;


Terminate the current switchover to Standby that never completed fully.  

select max(sequence#) from v$archived_log where applied='YES';


Implmentation --    


select name from v$tempfile;


A) Verify that the switchover target is ready to be switched to the primary role.

  select name, switchover_status from v$database;


  NAME      SWITCHOVER_STATUS

  --------- --------------------

  EASYDB      TO PRIMARY


B) Switch the target physical standby database role to the primary role.


  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;


C) Open the new primary database.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit


 startup

select name,open_mode,database_role,controlfile_type from v$database;

@dr

@up


D) Start Redo Apply on the new physical standby database.


===Real Time Example==============




set pagesize 24 linesize 87

col name format a12

col STATUS format a12

col WRL_PARAMETER format a40

select name, STATUS, WRL_PARAMETER from v$database,v$encryption_wallet;

EOF


$ORACLE_HOME/bin/lsnrctl start LSNR_EASYDB

$ORACLE_HOME/bin/lsnrctl start BPCE

$ORACLE_HOME/bin/lsnrctl start LSNR_DP


oracle@EASYDBSERVER01:/oraEASYDB/EASYDB/admin/startup_scripts[EASYDB] >



10. Check the status using @up from /home/oracle path.


Make sure DB_ROLE is PHYSICAL STANDBY.


NAME      INSTANCE_NAME    OPEN_MODE            HOST_NAME       DB_ROLE              DATABASE_STATUS   STATUS     LOGINS     UP TIME

--------- ---------------- -------------------- --------------- -------------------- ----------------- ---------- ---------- -------------------------

EASYDB      EASYDB             MOUNTED             EASYDBSERVER01        PHYSICAL STANDBY     ACTIVE            MOUNTED    ALLOWED    21-MAY-2021 07:12:14




alter system set job_queue_processes=1000 scope=both;

alter system set aq_tm_processes=10 scope=both;


===========================================================================================================================


Go to STANDBY DATABASE server:--


oracle@EASYDBSERVER101:~[EASYDB] >. ./EASYDB_12c.env

oracle@EASYDBSERVER101:~[EASYDB] >echo $ORACLE_SID

EASYDB

oracle@EASYDBSERVER101:~[EASYDB] >



11. Check switchover status in STANDBY DATABASE.


    select name, switchover_status from v$database; ---


    This should return "TO PRIMARY" or "SESSIONS ACTIVE" , Do not proceed if there is any other status{Like Not allowed}


12. in STANDBY DATABASE


recover managed standby database cancel; --

select switchover_status from v$database



show parameter aq_tm_processes

show parameter job_queue_processes



alter system set job_queue_processes=0 scope=both;

alter system set aq_tm_processes=0 scope=both;


select name,open_mode,database_role,switchover_status from v$database;



======================




    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; ----



set the EASYDB env 


select name,,open_mode, database_role,switchover_status from v$database;


13. in STANDBY DATABASE Bring up the DB  in read write.Go to below path.


    cd /oraEASYDB/EASYDB/admin/startup_scripts


    sh start_EASYDB_as_PRIMARY.sh


oracle@EASYDBSERVER101:/oraEASYDB/EASYDB/admin/startup_scripts[EASYDB] >cat start_EASYDB_as_PRIMARY.sh

export ORACLE_HOME=/oraEASYDB/EASYDB/12.2.0.1/EASYDB_home1

export ORACLE_SID=EASYDB

export TZ="MUT-4:00"

export TNS_ADMIN=/oraEASYDB/EASYDB/admin/startup_scripts

$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF

STARTUP MOUNT;

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "EASYDB12345678";

ALTER DATABASE OPEN;


set pagesize 24 linesize 87

col name format a12

col STATUS format a12

col WRL_PARAMETER format a40

select name, STATUS, WRL_PARAMETER from v$database,v$encryption_wallet;


EOF


-$ORACLE_HOME/bin/lsnrctl start LSNR_EASYDB

-$ORACLE_HOME/bin/lsnrctl start BPCE

-$ORACLE_HOME/bin/lsnrctl start LSNR_DP


oracle@EASYDBSERVER101:/oraEASYDB/EASYDB/admin/startup_scripts[EASYDB] >



14.Check the status using up.sql. DATABASE_ROLE should be PRIMARY.


NAME      INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            HOST_NAME                      DATABASE_STATUS   LOGINS     UP TIME

--------- ---------------- ---------------- -------------------- ------------------------------ ----------------- ---------- -------------------------

EASYDB      EASYDB             PRIMARY          READ WRITE           EASYDBSERVER101                       ACTIVE            ALLOWED    10-MAR-2021 21:32:30



15. Ensure that aq_tm_processes and job_queue_processes are set to required value ( refer PRODUCTION values in step 3).


    show parameter aq_tm_processes

    show parameter job_queue_processes


SQL> show parameter aq_tm_processes


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes                      integer     10

job_queue_processes                  integer     1000


alter system set aq_tm_processes=10;

alter system set job_queue_processes=1000;



16. in old PRODUCTION (current STANDBY DATABASE) enable recovery.


    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


    show parameter aq_tm_processes

    show parameter job_queue_processes


17. Ensure reverse replication is working fine


    alter system switch logfile;

    alter system switch logfile;

    alter system switch logfile;

    alter system switch logfile;



No comments:

Post a Comment