Physical
Standby DB
Logical Standby DB
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.
Some useful commands related
to the Physical Standby DB and for the logical Standby DB.
Physical
Standby DB:
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;
alter system set log_archive_dest_state_2='defer' scope=both;
select process, status, sequence# from
v$managed_standby; #check the MRP0
ALTER DATABASE RECOVER managed standby database cancel; #Cancel managed recovery operations.
alter database recover managed standby
database using current logfile disconnect from session ; # deprecated in 12c online redolog apply via redologs
# On Primary DB
ALTER SYSTEM SET log_archive_dest_2='SERVICE=kardbstb compression=DISABLE delay=180' SCOPE=BOTH;
# On Standby DB
# If you don't want delay. The delay parapetr on Primay will be ignored.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;
# General command
alter database recover managed standby database disconnect from session;
OR
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
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.
# Check Standby is in Sync:
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;
# You can check last log apply date and time.
SELECT
CASE
WHEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
THEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD HH24:MI:SS')
ELSE 'ERROR'
END AS last_apply_time
FROM V$ARCHIVED_LOG
WHERE DEST_ID = (SELECT MIN(DEST_ID) FROM V$ARCHIVE_DEST WHERE STATUS = 'VALID');
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.
aktive standby as primary:
NOTE: Standby DB need to run via this command "using archived logfile" otherwise i have seen error regarding online logs:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT
#!/bin/bash
. /users/oracle/set_home_env TSTSTB
sqlplus -s "sys/oracle as sysdba" <<EOF
spool /oracle19/support/log/start_db.log
shutdown abort;
startup nomount;
alter database mount standby database;
alter database activate standby database;
alter database open;
alter database flashback off;
archive log list;
select flashback_on from v\$database;
shutdown immediate;
startup mount;
alter database open;
select name, open_mode, database_role, switchover_status from v\$database;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
EOF
Standby using rman backup:
startup nomount
rman target /
RMAN> restore standby controlfile from '/fast_recovery_area/TSTDB/backup/full_backup/STA_CONTTSTDB.1.142.20210614.1075209588';
RMAN> sql 'alter database mount standby database';
RMAN> catalog start with '/fast_recovery_area/TSTDB/backup/full_backup';
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
restore database;
switch datafile all;
recover database;
}
Physical Standby Open as READ_ONLY backup via RMAN
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN;
rman target /
RMAN> BACKUP CONSISTENT DATABASE format '/fast_recovery_area/TSTDBSTB/backup/rman_%d_%T_%s_%p.rm';
Logical Standby DB:
To see standby redologs:
select group# ||' - '|| type ||' - '||
member from v$logfile;
On primary
alter system switch logfile;
set lines 200
set pages 100
set head on
alter session set nls_date_format='mm/dd/yy hh24:mi:ss' ;
select APPLIED_TIME,APPLIED_SCN,MINING_TIME,MINING_SCN ,LATEST_SCN,
SYSDATE from v$logstdby_progress ;
APPLIED_TIME APPLIED_SCN
MINING_TIME MINING_SCN LATEST_SCN
SYSDATE
-----------------
----------- ----------------- ---------- ---------- -----------------
12/07/11
10:52:54 3.8725E+10 12/07/11 10:52:54
3.8725E+10 3.8725E+10 12/07/11 10:53:04
On Standby.
We can check with the
following command the status of the Logical Standby DB. There should no be
"wait".
set lines 200
set pages 60
col type format a12
col status format a70
select
SID,LOGSTDBY_ID,SPID,TYPE,STATUS,HIGH_SCN from v$logstdby_process ;
SID LOGSTDBY_ID SPID TYPE STATUS H
---------- ----------- ------------ ------------
--------------------------------- ---
843 -1 21994 COORDINATOR ORA-16116: no work available 3.8
846 0 22003 READER ORA-16116: no work available 3.8
841 1 22005 BUILDER
ORA-16116: no work available
3.8
845 2 22007 PREPARER ORA-16116: no work available 3.8
833 3 22027 ANALYZER ORA-16116: no work available 3.8
832 4 22029 APPLIER ORA-16116: no work available 3.8
834 5 22031 APPLIER ORA-16116: no work available 3.8
835 6 22033 APPLIER ORA-16116: no work available 3.8
836 7 22035 APPLIER ORA-16116: no work available 3.8
838 8 22037 APPLIER ORA-16116: no work available 3.8
10 rows selected.
Check the status whether logs are applying or
waiting.
SQL> SELECT SESSION_ID, STATE FROM
V$LOGSTDBY_STATE;
If you get issue
during the failover. You execute the following command again.
set lines 200 set head off set pages 0 select 'alter system set
log_archive_dest_2=' || chr(39) || value
|| chr(39) || ' ; ' from v$parameter where name =
'log_archive_dest_2' ; select 'alter system set
log_archive_dest_state_2=' || chr(39) || 'defer' || chr(39) || ' ;' from dual ; select 'alter system set
log_archive_dest_state_2=' || chr(39) || 'enable' || chr(39) || ' ;' from dual ;
Check the parameter
setting and some useful information about: LCR = Logical Change Record
What are the current
Sql Apply settings and statistics?
set linesize 150
col VALUE format a50
col NAME format a40
SELECT SUBSTR(name,
1, 40) AS NAME, SUBSTR(value,1,32) AS VALUE FROM GV$LOGSTDBY_STATS;
OR
SELECT NAME, VALUE
FROM V$LOGSTDBY_STATS;
NAME VALUE
----------------------------------------
---------------------------
number of preparers 1
number of
appliers 5
maximum SGA for LCR
cache 30
parallel servers in
use 9
maximum events
recorded 100
preserve commit
order TRUE
transaction
consistency FULL
record skip
errors Y
record skip DDL Y
record applied
DDL N
record unsupported
operations N
coordinator
state APPLYING
transactions
ready 22970723
transactions
applied 22970322
....
....
....
How to register the Log file.
ALTER DATABASE
REGISTER LOGICAL LOGFILE '/log/arch/stb/ARCPRD2_1_15036_586878676.arc' ;
Check the standby whether it is in SYNC with primary,
Check the v$archived view on standby.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
Check the DB Role.
SQL> select
name,db_unique_name,database_role from v$database;
It is highly recommended to increase this value to avoid any issues in
case of a GAP.
SQL> SHOW
PARAMETER LOG_ARCHIVE_MAX_PROCESSES;
SQL> ALTER SYSTEM
SET LOG_ARCHIVE_MAX_PROCESSES=10;
How to active heapdump
trace.
Next time new generated trace file
will created with the error ora-4030 error.
alter system set
events '4030 trace name heapdump level 536870917;name errorstack level 3';
Deactivate the event.
alter system set
events '4030 trace name context off';
You can find the trace in udump, it will not be named 942trace but
inside the file, you should find an ORA-942.
alter system set
timed_statistics = true;
alter system set
statistics_level=all;
alter system set
max_dump_file_size='UNLIMITED';
alter system set
events '942 trace name errorstack level 3' ;
-- Please don't forget to
disable the event later
-- Also please reset
timed_statistics and statistics_level to their original value (you may to check
their values before changing them)
alter system set
events '942 trace name context off';
Your comments are most valuable and it would help me to come up with better posts.
