Converting a physical standby database into snapshot standby database
1) To check gap
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#
select name,open_mode,database_role,controlfile_type from v$database;
select flashback_on from v$database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database flashback on;
select flashback_on from v$database;
show parameter recovery
select name from v$recovery_file_dest;
show parameter db_recovery_file_dest_size
check recovery desk and recovery desk size parameter
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
select name,open_mode,database_role,controlfile_type from v$database;
select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
shutdown immediate ---
startup ------
select name,open_mode,database_role,controlfile_type from v$database;
Converting a snapshot standby database into physical standby
Revert back
--------------
select name,open_mode,database_role,controlfile_type from v$database;
shutdown immediate -
startup mount -
select name,open_mode,database_role,controlfile_type from v$database;
alter database convert to physical standby;
SQL> alter database convert to physical standby;
Database altered.
SQL> select name,open_mode,database_role,controlfile_type from v$database;
select name,open_mode,database_role,controlfile_type from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
select name,open_mode,database_role,controlfile_type from v$database;
shutdown immediate;
startup mount
select name,open_mode,database_role,controlfile_type from v$database;
select flashback_on from v$database;
alter database flashback off --
select flashback_on from v$database;
ALTER DATABASE RECOVER managed standby database disconnect from session;
select name,open_mode,database_role,controlfile_type from v$database;
==============Standby Database Switchover and Switchback=============
Overview
Check in db "dba_jobs" - stop those jobs in DC servers
Verify that each database is properly configured for the role
Verify that there are no redo transport errors or redo gaps at the standby database
Verify target of your switchover is synchronized or not
Check at D.R site Redo is received or not and applied
Ensure temporary files exist on the standby database that match the temporary files on the primary database
Ensure Flashback is on both D.C and D.R
Initiate the switchover on the primary database
Shut down and then mount the former primary database.
Verify that the switchover target is ready to be switched to the primary role.
Switch the target physical standby database role to the primary role.
Open the new primary database.
Start Redo Apply on the new physical standby database.
==============
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 in read only 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 from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';
SELECT db_unique_name, open_mode, database_role FROM v$database;
SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
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#
/
ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
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
8) check crontab and scheduler if any state and rman backup will scheduled during activity if yes disable it
Implementation
--------------
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
--------------------------------
select SWITCHOVER_STATUS from V$DATABASE;
select name,database_role,switchover_status from v$database;
!ps -ef|grep rman
SELECT * From dba_jobs_running;
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;
alter database commit to switchover to physical standby with session shutdown
shut abort
startup mount;
or
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY 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
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
select name, open_mode, db_unique_name, database_role from v$database;
==================================
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;
2) 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 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';
2) ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
3) shut immediate
4) startup
or
alter database open
select database_role from v$database;
select name, open_mode, database_role from v$database;
===============================================startup script===========
$ cat startup.sh
export ORACLE_SID=testdbs3
sqlplus / as sysdba <<EOF
startup mount;
alter database recover managed standby database disconnect from session ;
EOF
export ORACLE_SID=testdbs2
sqlplus / as sysdba <<EOF
startup mount;
alter database recover managed standby database disconnect from session ;
EOF
export ORACLE_SID=testdbs1
sqlplus / as sysdba <<EOF
startup mount;
alter database recover managed standby database disconnect from session ;
EOF
lsnrctl start LISTENER_TESTDB_STB
ps -ef|grep pmon
ps -ef|grep tns
No comments:
Post a Comment