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