DATABASE

***************DATABSE SIZE***************

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

***************TABLESPACE SIZE***************

set linesize 1200

set pagesize 1200

     column tablespace_name format a20 heading 'Tablespace'

     column sumb format 999,999,999

     column extents format 9999

     column bytes format 999,999,999,999

     column largest format 999,999,999,999

     column Tot_Size format 999,999 Heading 'Total| Size(Mb)'

     column Tot_Free format 999,999,999 heading 'Total Free(MB)'

     column Pct_Free format 999.99 heading '% Free'

     column Chunks_Free format 9999 heading 'No Of Ext.'

     column Max_Free format 999,999,999 heading 'Max Free(Kb)'

     set echo off

     PROMPT  FREE SPACE AVAILABLE IN TABLESPACES

     select a.tablespace_name,sum(a.tots/1048576) Tot_Size,

     sum(a.sumb/1048576) Tot_Free,

     sum(a.sumb)*100/sum(a.tots) Pct_Free,

     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free

     from

     (

     select tablespace_name,0 tots,sum(bytes) sumb,

     max(bytes) largest,count(*) chunks

     from dba_free_space a

     group by tablespace_name

     union

     select tablespace_name,sum(bytes) tots,0,0,0 from

      dba_data_files

     group by tablespace_name) a

     group by a.tablespace_name

order by pct_free;

select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='&TABLESPACE_NAME';

***************UNDO TABLESPACE***************

col allocated for 999,999.999

col free      for 999,999.999

col used      for 999,999.999

select

   ( select sum(bytes)/1024/1024/1024 from dba_data_files

       where tablespace_name like '%UND%' )  allocated,

    ( select sum(bytes)/1024/1024/1024 from dba_free_space

       where tablespace_name like '%UND%')  free,

    ( select sum(bytes)/1024/1024/1024 from dba_undo_extents

       where tablespace_name like '%UND%') USed

from dual

/

***************TEMP TABLESPACE***************

SELECT tablespace_name,SUM(bytes_used)/ 1024 / 1024/1024,SUM(bytes_free)/ 1024 / 1024/1024 FROM V$temp_space_header GROUP BY tablespace_name;

set lines 300

col FILE_NAME for a25;

select FILE_NAME,autoextensible,BYTES/1024/1024 from dba_temp_files where TABLESPACE_NAME like '%TEMP%';

TO CUTDOWN PATH ATTACHED WHEN CHANGING DIRECTORIES

PS1="`hostname --short`[`echo ${LOGNAME}`]_`echo ${ORACLE_SID}`> "; export PS1

Need to add this in .profile or bash_profile

***************PARAMTERS CROSSCHECKING***************

bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)

SQL> @/home/oracpaq1/bde_chk_cbo.sql

Spool file bde_chk_cbo_report.html has been generated.

SQL>

EBS Database Parameter Settings Analyzer (Doc ID 1953468.1)

***************DB PATCH***************

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <path>

$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"

select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION from DBA_REGISTRY_SQLPATCH order by BUNDLE_SERIES;

SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;

***************BACKUP***************

RMAN COLD

rman target / log=dbname_cold_backup.log << EOF

 RUN

 {

 ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '<path>/dbname/%d_COLD_%M%D%Y_%p_%s';

 ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '<path>/dbname/%d_COLD_%M%D%Y_%p_%s';

 ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '<path>/dbname/%d_COLD_%M%D%Y_%p_%s';

 ALLOCATE CHANNEL CH4 DEVICE TYPE DISK FORMAT '<path>/dbname/%d_COLD_%M%D%Y_%p_%s';

 BACKUP AS COMPRESSED BACKUPSET FULL DATABASE TAG dbname_cold_backup_dbfiles;

 BACKUP FORMAT '<path>/dbname/%d_%M_%D_%Y_%t.ctl' CURRENT CONTROLFILE TAG dbname_backup_ctlfile;

 }

 exit;

EOF

RMAN HOT

rman target / log=dbname_HOT.log << EOF

RUN

{

ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

ALLOCATE CHANNEL CH4 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

ALLOCATE CHANNEL CH5 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

ALLOCATE CHANNEL CH6 DEVICE TYPE DISK FORMAT '<path>/dbname/incr/%d_HOT_%M%D%Y_%p_%s';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL = 0 DATABASE TAG ${ORACLE_SID}_HOT_${TAG_STAMP};

BACKUP FORMAT '<path>/dbname/incr/%d_%M_%D_%Y_%t.ctl' CURRENT CONTROLFILE TAG '${ORACLE_SID}_HOT_${TAG_STAMP}';

BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '<path>/dbname/incr/%d_ARCH_%M%D%Y_%p_%s_%t'  TAG '${ORACLE_SID}_ARCH_${TAG_STAMP}';

}

exit;

EOF 

******LAST DDL TIME*******

select owner,object_type,object_name,to_char(created,'MM-DD-YY HH24:MI:SS'),to_char(LAST_DDL_TIME,'MM-DD-YY HH24:MI:SS') from dba_objects where to_char(LAST_DDL_TIME,'MM-DD-YY HH24:MI:SS') between '10-11-18 16:00:00' and '10-11-18 17:04:59' and owner not in 'SYS';

select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME from all_objects where status = 'INVALID' --and owner in (select ORACLE_USERNAME from apps.fnd_oracle_userid) and OBJECT_NAME  not in (select OBJECT_NAME from GEAE_DBA.GEAE_BASE_INVALID_OBJECTS) and object_type not in 'MATERIALIZED VIEW' order by LAST_DDL_TIME desc;