Useful commands dataguard

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.