Start/Stop Application Services Without Concurrent

./adstrtal.sh -nodbchk

./adstpall.sh -nodbchk

Find the size of the SCHEMA/USER?

select sum(bytes/1024/1024)"size" from dba_segments where owner='&owner';

Find the log file for active workflow mailer and workflow agent listener Container

select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,’WFMLRSVC’,’maile r container’,’WFALSNRSVC’,’listener container’,fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code=’A’
and fl.lookup_type=’CP_PROCESS_STATUS_CODE’ and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in(‘WFMLRSVC’,’WFALSNRSVC’)
order by fcp.logfile_name;

Start/Stop Individual Components in Opmn

./opmnctl startproc ias-component=coreapplication_obis1

./opmnctl stopproc ias-component=coreapplication_obis1

Rolling back an Autoconfig session

Each execution of Autoconfig creates a rollback script in case you need to revert to previous configuration settings.

Tier                                       Directory
Application    $APPL_TOP/admin/<CONTEXT_NAME>/out/<MMDDhhmm>
Database       $ORACLE_HOME/appsutil/out/<CONTEXT_NAME>/<MMDDhhmm>


To roll back an autoconfig session, run: restore.sh script

Database User Create/Alter

Create User:

SQL> CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLESPACE <tablespace name>
TEMPORARY TABLESPACE <tablespace name>
QUOTA <quota amount> ON <tablespace name>;

You then need to grant appropriate roles to the user for example, session, connect, resource:

SQL> grant connect,resource to <username>;

Change Password:

SQL> alter user <username> identified by <newpassword>;

Unlock User:

SQL> alter user <username> account unlock;

Add tablespace / datafile

Increase the size of the datafile:

SQL> alter database datafile '/ebiz/oracle/test/db/apps_st/data/system04.dbf' autoextend on maxsize 1G;

SQL> alter database datafile '/ebiz/oracle/test/db/apps_st/data/system04.dbf' resize 3G;

Add an additional datafile:

SQL> alter tablespace system add datafile '/ebiz/oracle/test/db/apps_st/data/system05.dbf' size 2G;

Find Users connected to the Database

To find how many users are on the database issue the following:

SQL> SELECT username FROM v$session;

Show what users are running:

SQL> SELECT a.sid
, a.serial#
, b.sql_text
FROM v$session a
, v$sqlarea b
WHERE a.sql_address=b.address AND a.username = '<username>';

Find Database Users with DBA Privilege

 If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.


SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Find Tablespace size including Autoextend Values

Using this below sql query, you can find the tablespace size including autoextend values which is enabled for datafiles.

WITH my_ddf AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   my_ddf.tablespace_name,
         ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) totfree,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (my_ddf.mysize)
                 * 100
               ) perc
    FROM my_ddf, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE my_ddf.file_id = dfs.file_id(+)
         AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
GROUP BY my_ddf.tablespace_name
ORDER BY 6 DESC

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;