Start/Stop Application Services without Weblogic & Nodemanager in R12.2

In R12.2 EBS instance, if you wants to stop/start all application services excluding Weblogic admin service and nodemanager services use below command.

cd $ADMIN_SCRIPTS_HOME

./adstpall.sh -skipNM -skipAdmin

./adstrtal.sh -skipNM -skipAdmin

Purge Log Files and Trace Files

find /ebiz/oracle11.2.0/rdbms/audit/ -name 'ora*.aud' -mtime +30 -exec ls -lrt  {} \;
find /ebiz/oracle11.2.0/rdbms/audit/ -name 'ora*.aud' -mtime +30 -exec rm -f  {} \;

find /ebiz/oracle11.2.0/admin/TEST_hostname/bdump -name 'test*.trc' -mtime +30 -exec ls -lrt  {} \;
find /ebiz/oracle11.2.0/admin/TEST_hostname/bdump -name 'test*.trc' -mtime +30 -exec rm -f  {} \;

Find Nodemanager Running Process

ps -ef | grep -v grep | grep -i weblogic.NodeManager

Find Oracle Home for PMON Process

$ ps -ef | grep pmon

ora1024   262366        1   0   Mar 23      -  0:12 ora_pmon_mysid

ORACLE_SID is mysid

$ ls -l /proc/262366/cwd

lr-x------   2 ora1024  dba  0 Mar 23 19:31 cwd -> /data/opt/app/product/10.2.0.4/db_1/dbs/

ORACLE_HOME is /data/opt/app/product/10.2.0.4/db_1

Oracle Data Guard Redo Apply & Monitoring – Complete DBA Guide

Oracle Data Guard is one of the most critical High Availability (HA) and Disaster Recovery (DR) solutions used in Oracle environments. For DBAs, continuously monitoring redo generation, transport, and apply is essential to ensure data consistency between Primary and Standby databases.

This blog provides a complete, practical checklist with ready-to-use SQL commands, explanations, and troubleshooting steps to help Oracle DBAs efficiently manage Data Guard environments. This guide is especially useful for Oracle Apps DBA, Core DBA, and RAC administrators.

---

1. Check Last Applied Redo Log (Standby)

This query helps identify the latest redo log sequence applied on the standby database.

SELECT THREAD#, MAX(SEQUENCE#) AS LAST_APPLIED_LOG
FROM V$LOG_HISTORY
GROUP BY THREAD#;

Why this matters:

• Confirms whether redo apply is progressing

• Helps identify apply lag

---

2. Identify Unapplied Archive Logs

Use this query to check archived logs that are received but not yet applied on standby.

SELECT THREAD#, SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'NO';

--If many logs show APPLIED = NO, investigate transport or MRP issues.

---

3. Force Log Switch (Primary Database)

Triggering a log switch is useful to test redo shipping and apply.

RAC Environment

ALTER SYSTEM ARCHIVE LOG CURRENT;

Non-RAC Environment

ALTER SYSTEM SWITCH LOGFILE;

Tip: Always perform this on the Primary database.

---

4. Check Managed Recovery Process (MRP) Status

This query confirms whether the redo apply process (MRP) is running properly.

SELECT PROCESS, STATUS, SEQUENCE#
FROM V$MANAGED_STANDBY;

Look for:

• MRP0 process

• STATUS should be APPLYING_LOG or WAIT_FOR_LOG

---

5. View Archive Apply History

Displays applied status for all archived logs.

SELECT SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Useful for auditing redo apply behavior over time.

---

6. Start Redo Apply on Standby

Use the following commands to start managed recovery.

Real-Time Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Archive Log Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

---

7. Fix ORA-01153: Incompatible Media Recovery

This error occurs when a recovery process is already active.

Resolution Steps

-- Step 1: Cancel existing recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Step 2: Restart recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

This resolves most redo apply conflicts.

---

8. Stop Redo Apply (Standby)

To gracefully stop managed recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Useful during maintenance or troubleshooting.

---

9. Verify Data Guard Configuration

Check overall database role and protection mode.

SELECT NAME,
       DB_UNIQUE_NAME,
       OPEN_MODE,
       DATABASE_ROLE,
       PROTECTION_MODE
FROM V$DATABASE;

Ensures the database is operating in the expected role.

---

10. Check Applied Redo Logs (Standby)

Latest Applied Sequence

SELECT MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES';

Thread-wise Apply Status

SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
GROUP BY THREAD#;

Helpful in RAC environments where multiple threads exist.

---

11. Primary Database – Last Generated Redo

Displays the latest redo log generated on the primary database.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT val.thread#,
       MAX(val.sequence#) AS last_primary_seq_generated,
       MAX(val.first_time) KEEP (DENSE_RANK LAST ORDER BY val.sequence#)
           AS last_generated_timestamp
FROM v$archived_log val,
     v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY val.thread#
ORDER BY val.thread#;

---

12. Standby Database – Last Received Redo

Shows the latest redo log received by the standby database.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT val.thread#,
       MAX(val.sequence#) AS last_received_sequence,
       MAX(val.first_time) KEEP (DENSE_RANK LAST ORDER BY val.sequence#)
           AS last_received_timestamp
FROM v$archived_log val,
     v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY val.thread#
ORDER BY val.thread#;

---

13. Standby Database – Last Applied Redo

Displays the most recent redo log applied on standby.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT val.thread#,
       MAX(val.sequence#) AS last_standby_seq_applied,
       MAX(val.first_time) KEEP (DENSE_RANK LAST ORDER BY val.sequence#)
           AS last_applied_timestamp
FROM v$archived_log val,
     v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
  AND val.applied IN ('YES','IN-MEMORY')
GROUP BY val.thread#
ORDER BY val.thread#;

---

14. Check Current SCN and Timestamp

Used to verify database synchronization at SCN level.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT TO_CHAR(CURRENT_SCN, 'FM999999999999999') AS current_scn,
       SCN_TO_TIMESTAMP(CURRENT_SCN) AS current_scn_timestamp
FROM V$DATABASE;

---

15. Check Switchover Status

Before performing a switchover, verify readiness.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Expected values:

• TO STANDBY

• TO PRIMARY

---

16. General Redo Apply Monitoring

Quick queries for daily monitoring:

SELECT SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG;

SELECT MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES';

---

• If you found this guide useful, share it with fellow DBAs and bookmark it for daily operations.

---

🔔 Follow for more Oracle DBA tips, EBS administration guides, and real-world troubleshooting scenarios.

Find Data Guard Parameters

set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
               'log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert',
                     'log_file_name_convert', 'standby_file_management');

Find Data file Size

select file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE,MAXBYTES from dba_data_files where tablespace_name like '%UNDO%';

Find Archive Log file Creation Date

select sequence#, substr(name,1,96),creator, to_char(first_time,'DD-MON HH24:MI'), to_char(completion_time,'DD-MON HH24:MI') 
from v$archived_log where first_time > sysdate-100 and name is not null order by 1;

Find the Tablespace size in putty

select nvl(b.tablespace_name,nvl(a.tablespace_name,'NA')) " Tablespace" 
         , kbytes_alloc/1024 "Allocated" 
         , (kbytes_alloc-nvl(kbytes_free,0))/1024 "used" 
         , nvl(kbytes_free,0) /1024 "free" 
from (select sum(bytes)/1024 Kbytes_free 
                  , max(bytes)/1024 largest 
                  , tablespace_name 
         from  dba_free_space 
         group by tablespace_name) a ,
       (select sum(bytes)/1024 Kbytes_alloc 
                 ,tablespace_name 
        from  dba_data_files 
        group by tablespace_name) b 
where a.tablespace_name (+) = b.tablespace_name 
and b.tablespace_name like '%' 
order by 4 ;

Find the Database Growth by Monthly Basis

select to_char(creation_time, 'YYYY Month') "Month",
    sum(bytes)/1024/1024 "Growth in MB"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'YYYY Month');