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

Data Guard Commands

Last applied log:
-------------------
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

--- select thread#,sequence#,applied from v$archived_log where applied='NO';

Switching log in RAC environment:
--------------------------------
alter system archive log current;

MRP Process checking:
---------------------
select process,status,sequence# from v$managed_standby;

Archive applied history:
------------------------
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

apllying redolog:
----------------
sql> alter database recover managed standby database using current logfile disconnect;
sql> alter database recover managed standby database disconnect from session;

ORA-01153: an incompatible media recovery is active
-----------------------------------------------------
first> alter database recover managed standby database cancel;
second> alter database recover managed standby database using current logfile disconnect;

disconect the redo apply:
------------------------
alter database recover managed standby database cancel;

to check all:
-------------
select name, db_unique_name,open_mode, database_role, protection_mode from v$database;

Applied log checking:
--------------------
select THREAD#,sequence# from v$archived_log where applied='YES';
select max(sequence#) from v$archived_log where applied='YES';
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

Primary:
--------
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;


Standby:(Received)
------------------
 select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Standby:(Applied)
----------------
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;

Switchover status:
----------------
select switchover_status from v$database;


General:
select sequence#,applied from v$archived_log;

select max(sequence#) from v$archived_log where applied='YES';

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;