To Check the Current workflow Mailer Configuration and logfiles

SQL>

col MEANING for a10;
col DECODE(FCQ.CONCURRENT_QUEUE_NAME) for a30;

col OS_PROCESS_ID for a10;

col LOGFILE_NAME for a50;

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;

Output :
======

EANING    P DECODE(FCQ.CONCURRENT_QUEUE_NA CONCURRENT_PROCESS_ID OS_PROCESS LOGFILE_NAME
---------- - ------------------------------ --------------------- ---------- --------------------------------------------------
Active     A maile r container                             692801 60228270   /test01/oracle/TEST/inst/apps/TEST_host/logs/appl/conc/log/FNDCPGSC692801.txt

Active     A listener container                            692802 63701744   /test01/oracle/TEST/inst/apps/TEST_host/logs/appl/conc/log/FNDCPGSC692802.txt

CRSCTL commands in Oracle 11g Release 2

CRSCTL commands in Oracle 11g Release 2

How to shutdown CRS on all nodes and Disable CRS as ROOT user:
-------------------------------------------------------------
#crsctl stop crs
#crsctl disable crs

How to Enable CRS and restart CRS on all nodes as ROOT user:
-----------------------------------------------------------
#crsctl enable crs
#crsctl start crs

How to check VIP status is ONLINE / OFFLINE:
----------------------------------------
$crs_stat or
$crsctl stat res -t ------> 11gr2

How to Check current Version of Clusterware:
-------------------------------------------
$crsctl query crs activeversion

$crsctl query crs softwareversion [node_name]

How to Start & Stop CRS and CSS:
-------------------------------
$crsctl start crs
$crsctl stop crs

#/etc/init.d/init.crs start
#/etc/init.d/init.crs stop

#/etc/init.d/init.cssd stop
#/etc/init.d/init.cssd start

How to Enable & Disable CRS:
---------------------------
$crsctl enable crs
$crsctl disable crs

#/etc/init.d/init.crs enable
#/etc/init.d/init.crs disable

How to Check current status of CRS:
----------------------------------
$crsctl check crs

$crsctl check cluster [-node node_name]

How to Check CSS, CRS and EVMD:
------------------------------
$crsctl check cssd

$crsctl check crsd

$crsctl check evmd

How to List the Voting disks currently used by CSS:
--------------------------------------------------
$crsctl check css votedisk

$crsctl query css votedisk

How to Add and Delete any voting disk:
-------------------------------------
$crsctl add css votedisk <PATH>

$crsctl delete css votedisk <PATH>

How to start clusterware resources:
----------------------------------
$crsctl start resources

$crsctl stop resources

RMAN Backup of Database

How to take RMAN backup with and without incremental level.

RMAN INCREMENTAL BACKUP LEVEL 0

$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>

RMAN INCREMENTAL BACKUP LEVEL 1

$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 1 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>


RMAN BACKUP OF DATABASE (WITHOUT USING LEVEL 0)

$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>

NOTE :-

If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ can not be considered as a parent backup from which you can take level 1 backup.


Incremental backup levels :-

Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;

Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;

Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;

A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.

TABLESPACE USED AND FREE BASED ON PERCENTAGE :

               without undo

select a.tablespace_name "TABLESPACE NAME",round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/sum(round(a.bytes/1024/1024)))*100,2) 
"PERCENTAGE USED" , round(nvl(b.free,0)/1024/1024) "FREE" from dba_data_files a, (select tablespace_name, sum(bytes) free from dba_free_space group by 
tablespace_name) b where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name not like 'UNDO%' group by a.tablespace_name,b.free having round
(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/ sum(round(a.bytes/1024/1024)))*100,2) > 90;

          with undo

select a.tablespace_name "TABLESPACE NAME",round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/sum(round(a.bytes/1024/1024)))*100,2) "PERCENTAGE USED" , round(nvl(b.free,0)/1024/1024) "FREE" from dba_data_files a, (select tablespace_name, sum(bytes) free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name(+) group bya.tablespace_name,b.free having round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/ sum(round(a.bytes/1024/1024)))*100,2) > 90;

O/P:

TABLESPACE NAME                PERCENTAGE USED       FREE
------------------------------ --------------- ----------
APPS_TS_MEDIA                            97.03       8824
APPS_TS_TX_DATA                          97.32       4612
APPS_TS_TX_IDX                           95.01       6011

BACKUP                                   96.98       7768

Restore Datafile Without Backup - Archivelog Mode ON

Datafile restoration without backup is only possible when archivelog is enabled during the time of datafile dropped. By using archivelog and redo logs we can restore datafile.

SQL> select log_mode from v$database;

                        LOG_MODE

                                ------------

                                ARCHIVELOG

• I have removed the emp.dbf datafile accidently

Oracle@test TEST$  rm emp.dbf

• When I try to insert a new data in employee table present in emp datafile it shows below error

SQL> insert into emp select * from emp;
insert into emp select * from emp
                           
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 6: '/d02/app/oracle/oradata/testdb/emp.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

• To recover the datafile we need to take it offline :

SQL> alter database datafile 6 offline;

• Create a new datafile with same old dropped datafile name/Path

SQL> alter database create datafile '/d02/app/oracle/oradata/testdb/emp.dbf ';

• Now recover the datafile

SQL> recover datafile 8;

• Bring the datafile online :

SQL> alter database datafile 6 online;

• Now insert the values and check

SQL> insert into emp select * from emp;

100 rows created.

To Find Used and Free Space in Datafiles :

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

To reduce/resize the tablespace :

We can resize the datafile like below

ALTER DATABASE DATAFILE '/a01/sysaux01.dbf' RESIZE 1024M;

of course if you already used some space above 1024M then you will get an error

ORA-03297: file contains used data beyond requested RESIZE value

We can use the below querry to find the minimum/least value to reduce a datafile.

SQL >
SELECT  CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.file_name = '/a01/sysaux01.dbf';

   /

                                               OR

SQL >
SELECT  file_name, tablespace_name , CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.tablespace_name = 'SYSAUX';

   /