command to check Database Size

command to check DB size:
SQL> SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
   "total_size in GB"
    FROM (SELECT SUM (bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a,
    (SELECT NVL (SUM (bytes), 0) / 1024 / 1024/1024 temp_size
    FROM dba_temp_files) b,
    (SELECT SUM (bytes) / 1024 / 1024/1024 redo_size FROM sys.v_$log) c,
    (SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024/1024
    controlfile_size
    FROM v$controlfile) d;

total_size in GB
----------------
       14.192627
Alternative,
select 
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Size in GB
----------
 14.192627


SQL>

=============================

SQL> SELECT SUBSTR (a.file_id, 1, 3) FILE#, SUBSTR (file_name, 1, 80) file_name,
    a.BYTES / 1024 / 1024 tot_bytes, b.free_bytes / 1024 / 1024 free
    FROM dba_data_files a,
    (SELECT file_id, SUM (BYTES) free_bytes
    FROM dba_free_space
    GROUP BY file_id) b
    WHERE a.file_id = b.file_id(+) AND a.tablespace_name = '&tablespace_name'
    ORDER BY 2;
Enter value for tablespace_name: DEV_OCS
old   7: WHERE a.file_id = b.file_id(+) AND a.tablespace_name = '&tablespace_name'
new   7: WHERE a.file_id = b.file_id(+) AND a.tablespace_name = 'DEV_OCS'

FILE#
------------
FILE_NAME
--------------------------------------------------------------------------------
 TOT_BYTES       FREE
---------- ----------
11
/u01/app/oracle/oradata/gmkp11/DEV_ocs.dbf
      1700    80.5625

12
/u01/app/oracle/oradata/gmkp11/DEV_ocs01.dbf
      5120    1766.25

FILE#
------------
FILE_NAME
--------------------------------------------------------------------------------
 TOT_BYTES       FREE
---------- ----------
====================================
To check used space in database:

SQL> select sum(bytes)/1024/1024/1024 from dba_Segments;

SUM(BYTES)/1024/1024/1024
-------------------------
               9.09033203

How to freeze EBS system date

EBS application frozen steps:

1.Stop application services

2.Login to database server as sys user with sysdba privileges and fire below commands ,

SQL>select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; (to check current system date)

SQL>ALTER SYSTEM SET FIXED_DATE = '20014-05-01';

SQL>startup force

SQL>select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; (to check whether system date being constant)

3.Start all EBS application services and check application for system date.

command to know tablespace usage for storage management

command to know tablespace usage:
SQL>select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name;

command to know only segment usage:
SQL> select bytes totalusedspace, tablespace_name,segment_name from dba_segments where segment_name='<segment name>';

SQL> select bytes totalusedspace, tablespace_name,segment_name from dba_segments where segment_name='I_OBJ1';

TOTALUSEDSPACE TABLESPACE_NAME
-------------- ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
      10551296 SYSTEM
I_OBJ1

Command to know tablespace usage along with index_name,segment_name:(with tablespacename prompt)

select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free",di.index_name "INDEX_NAME",tu.segment_name "SEGMENT_NAME" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name,segment_name from dba_segments group by tablespace_name , segment_name) tu,(select tablespace_name,index_name  from dba_indexes)di where df.tablespace_name = tu.tablespace_name and di.tablespace_name=tu.tablespace_name and df.tablespace_name=’&TablespaceName’;

Command to know tablespace usage along with index_name,segment_name:(with index_name prompt)

select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free",di.index_name "INDEX_NAME",tu.segment_name "SEGMENT_NAME" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name,segment_name from dba_segments group by tablespace_name , segment_name) tu,(select tablespace_name,index_name  from dba_indexes)di where df.tablespace_name = tu.tablespace_name and di.tablespace_name=tu.tablespace_name and di.index_name=’&Index_Name’;

Command to know tablespace usage along with index_name,segment_name:(with segment_name prompt)

select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free",di.index_name "INDEX_NAME",tu.segment_name "SEGMENT_NAME" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name,segment_name from dba_segments group by tablespace_name , segment_name) tu,(select tablespace_name,index_name  from dba_indexes)di where df.tablespace_name = tu.tablespace_name and di.tablespace_name=tu.tablespace_name and tu.segment_name=’&Segment_Name’;
command to make output alignment:
SQL>set wrap off
SQL> set linesize 3000
Command to know tablespace usage along with index_name,segment_name:(with table_name prompt):

select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free", tu.segment_name "SEGMENT_NAME",di.table_name "TABLE_NAME" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name,segment_name from dba_segments group by tablespace_name , segment_name) tu,(select tablespace_name,table_name, index_name  from dba_indexes)di where df.tablespace_name = tu.tablespace_name and di.tablespace_name=tu.tablespace_name and di.table_name='&Table_Name';

AD Utilities in R12

AD Utilities in R12

AD Utilities are a group of tools designed to install, upgrade, maintain, and patch applications.

ADDBCTL - is a utility to start/stop the database
ADDLNCTL- is used to start/stop the listener control
ADSTRTAL- is a utility for starting all the application in Application tier
ADCMCTL - is used for starting/stopping  the concurrent manager
ADSTPALL - is a utility to stop all the process in Application tier
ADADMIN - is used to perform maintenance/administration tasks on an Oracle Apps Instance.
ADCTRL -Utility to determine status of worker
ADRELINK -Used to relink apps executables with product libraries
ADMRGPCH -Merge different patches & create single patch .
ADCLONE -Utility to clone Insatnce from Test to Prod or PROD to TEST
ADCONFIG -To configure different components like changing port number or to increase number of JVM
ADIDENT -utility to find version of a file
ADLICMGR -To license a product in applications
ADSPLICE -You add a product in application

admrgpch apply

 Merge Patches using admrgpch

Admrgph utility is used to merge two or more patches in oracle apps. The advantage of merging patches is that it reduces downtime as the repetitive task of compiling invalid database objects,  generating forms and reports,jar files etc.

How to use Admrgpch to merge patches

Download the  patches in /pathto directory. Now create 2 subdirectory in /pathto say source -which contains  the unzipped patches to be merged and dest -which contains the merged patch. Please note that both source and dest should be created as immediate child of same parent directory say /pathto. Now you can execute the following command to merge the patches.

admrgpch -s <source> -d <dest> -merge_name <mergename>

For example->

admrgpch -s </pathto/source> -d </pathto/dest> -merge_name <mergedpatch>

Please make sure the the merge path log file "admrgpch.log" does not contain any error. If the  above command to merge patches completes successfully then it displays the following->

Executing the merge of the patch drivers
-- Processing patch: /pathto/source/5708576
-- Done processing patch: /pathto/source/5708576

-- Processing patch: /pathto/source/4428060
-- Done processing patch: /pathto/source/4428060

Copying files...

5% complete. Copied 269 files of 5373...
10% complete. Copied 538 files of 5373...
15% complete. Copied 806 files of 5373...
20% complete. Copied 1075 files of 5373...
25% complete. Copied 1344 files of 5373...
30% complete. Copied 1612 files of 5373...
35% complete. Copied 1881 files of 5373...
40% complete. Copied 2150 files of 5373...
45% complete. Copied 2418 files of 5373...
50% complete. Copied 2687 files of 5373...
55% complete. Copied 2956 files of 5373...
60% complete. Copied 3224 files of 5373...
65% complete. Copied 3493 files of 5373...
70% complete. Copied 3762 files of 5373...
75% complete. Copied 4030 files of 5373...
80% complete. Copied 4299 files of 5373...
85% complete. Copied 4568 files of 5373...
90% complete. Copied 4836 files of 5373...
95% complete. Copied 5105 files of 5373...
100% complete. Copied 5373 files of 5373...

Character-set converting files...

  2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log

Now go to the destination merge patch directory say "dest". You can see that the admrgpch already created a driver with name "u_<mergename>.drv" say u_mergedpatch.drv. Now apply the merged patch as a single patch using  adpatch. So you have to give this driver name u_<mergename>.drv" when prompted.

Restrictions of admrgpch ->

It will not merge patches of different releases,platform,different parallel modes. Also do not use admrgpch to merge AD and Non-AD patches ad AD patches will change the patch utility itself.