Add Data file

 **************************************************

Database status

Tablespace Usage

ASM Disk Group Usage(RAC)

Backup

Filesystem Usage

Alert Log Check for ORA errors

Listener status

Cluster status (RAC)

****************************************************************

select name,open_mode from v$database;

startup time - 

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"

FROM   sys.v_$instance;

select status,count(*) from dba_objects where owner='STLMNT' group by status order by status;

select count(*),status from v$session group by status;

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance

FROM dual;

SET TERMOUT ON;

PROMPT 

PROMPT +------------------------------------------------------------------------+

PROMPT | Report   : Oracle RAC Instances                                        |

PROMPT | Instance : &current_instance                                           |

PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF

SET FEEDBACK    6

SET HEADING     ON

SET LINESIZE    180

SET PAGESIZE    50000

SET TERMOUT     ON

SET TIMING      OFF

SET TRIMOUT     ON

SET TRIMSPOOL   ON

SET VERIFY      OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name          FORMAT a13         HEAD 'Instance|Name / Number'

COLUMN thread#                FORMAT 99999999    HEAD 'Thread #'

COLUMN host_name              FORMAT a28         HEAD 'Host|Name'

COLUMN status                 FORMAT a6          HEAD 'Status'

COLUMN startup_time           FORMAT a20         HEAD 'Startup|Time'

COLUMN database_status        FORMAT a8          HEAD 'Database|Status'

COLUMN archiver               FORMAT a8          HEAD 'Archiver'

COLUMN logins                 FORMAT a10         HEAD 'Logins?'

COLUMN shutdown_pending       FORMAT a8          HEAD 'Shutdown|Pending?'

COLUMN active_state           FORMAT a6          HEAD 'Active|State'

COLUMN version                                   HEAD 'Version'

SELECT

    instance_name || ' (' || instance_number || ')' instance_name

  , thread#

  , host_name

  , status

  , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time

  , database_status

  , archiver

  , logins

  , shutdown_pending

  , active_state

  , version

FROM

    gv$instance

ORDER BY

    instance_number;

select count(*) from v$backup_corruption;

set pagesize 200

col TABLESPACE_NAME FORMAT A15

  select a.TABLESPACE_NAME,

           a.BYTES MB_USED,

           b.BYTES MB_FREE,

           round(((a.BYTES - b.BYTES)/a.BYTES)*100,2) percent_used

    from

           (

           select TABLESPACE_NAME,

                  sum(BYTES/1024/1024) bytes

           from   dba_data_files

           group  by TABLESPACE_NAME

           ) a,

           (

           select TABLESPACE_NAME ,

                  sum(BYTES/1024/1024) bytes from   dba_free_space

           group  by TABLESPACE_NAME

           ) b

   where   a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)

order by ((a.BYTES - b.BYTES)/a.BYTES) desc;

select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

col tablespace_name for a10

select TABLESPACE_NAME,FILE_ID,sum(BYTES_USED/1024/1024) used_mb,sum(BYTES_FREE/1024/1024) free_mb,

round(sum(BYTES_USED/1024/1024)/(sum(BYTES_FREE/1024/1024)+sum(BYTES_USED/1024/1024))*100,2) "%age" from v$temp_space_header group by TABLESPACE_NAME,FILE_ID;

select count(*) from dba_objects where status like 'INVALID';

SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,EXTENTS, MAX_EXTENTS FROM DBA_SEGMENTS WHERE MAX_EXTENTS - EXTENTS < 30 ORDER BY EXTENTS;

Select segment_name, owner, extents, max_extents from dba_segments where segment_type = 'TABLE' and (extents +1) >= max_extents;

col month for a5

select

  to_char(CREATION_TIME,'RRRR') year,

  sum(bytes)/1024/1024/1024 Bytes_in_GB 

from 

  v$datafile 

group by 

  to_char(CREATION_TIME,'RRRR'), 

order by 

  1;

########### ASM DISK GROUP  ##########

set lines 132

col name format a14

col PATH format a33

select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

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

select 100-(FREE_MB/TOTAL_MB)*100 as "Used % Safely Usable Area",  Name from v$asm_diskgroup_stat 

##########RMAN BACKUP CHECK ############

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';

SELECT START_TIME,END_TIME,STATUS,INPUT_TYPE FROM V$RMAN_BACKUP_JOB_DETAILS order by start_time;

###### ALert log location#####

show parameter back

! ps -ef |grep tns

 tail -1000 alert_TFCCAT.log |grep ORA-

*****************************************************************************************************************************************

SQL> select count(*),to_char(completion_time,'DD-MON-YYYY') from v$archived_log group by to_char(completion_time,'DD-MON-YYYY') order by 2;

*****************************************************************************************************

/oracle/crs/oracle/product/10/crs/bin

Avantgard

export ORACLE_HOME=/oracle/crs/oracle/product/10/crs/

export PATH=$PATH:/oracle/crs/oracle/product/10/crs/bin

crsctl check  crs

Monarch

export ORACLE_HOME=/u01/app/11.2.0/grid/

export PATH=$PATH:/u01/app/11.2.0/grid/bin/

crsctl stat res -t

*****************************************************************************************************

Database status

Tablespace Usage

Filesystem Usage

Alert Log Check for ORA errors

Backup 

Listener status

Cluster status (RAC)

ASM Disk Group Usage(RAC)

*****************************************************************************************************

To find ASM Diskgroup and Disks status

  

set lines 132

col name format a14

col PATH format a33

select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

GROUP_NUMBER NAME      STATE   TYPE  TOTAL_MB FREE_MB V

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

 1           OCR_VOTE MOUNTED EXTERN 152999    152603   Y

 2           DB_DATA  MOUNTED EXTERN 812000   810198  N

*****************************************************************************************************

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance

FROM dual;

SET TERMOUT ON;

PROMPT 

PROMPT +------------------------------------------------------------------------+

PROMPT | Report   : Oracle RAC Instances                                        |

PROMPT | Instance : &current_instance                                           |

PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF

SET FEEDBACK    6

SET HEADING     ON

SET LINESIZE    180

SET PAGESIZE    50000

SET TERMOUT     ON

SET TIMING      OFF

SET TRIMOUT     ON

SET TRIMSPOOL   ON

SET VERIFY      OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name          FORMAT a13         HEAD 'Instance|Name / Number'

COLUMN thread#                FORMAT 99999999    HEAD 'Thread #'

COLUMN host_name              FORMAT a28         HEAD 'Host|Name'

COLUMN status                 FORMAT a6          HEAD 'Status'

COLUMN startup_time           FORMAT a20         HEAD 'Startup|Time'

COLUMN database_status        FORMAT a8          HEAD 'Database|Status'

COLUMN archiver               FORMAT a8          HEAD 'Archiver'

COLUMN logins                 FORMAT a10         HEAD 'Logins?'

COLUMN shutdown_pending       FORMAT a8          HEAD 'Shutdown|Pending?'

COLUMN active_state           FORMAT a6          HEAD 'Active|State'

COLUMN version                                   HEAD 'Version'

SELECT

    instance_name || ' (' || instance_number || ')' instance_name

  , thread#

  , host_name

  , status

  , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time

  , database_status

  , archiver

  , logins

  , shutdown_pending

  , active_state

  , version

FROM

    gv$instance

ORDER BY

    instance_number;

****************************************************************************************************

eb1-db-svr       - PROD1

eb1-db-svr-bck   - PROD2

eb2-db-svr       - PROD1(Mounted),PREPROD1

eb2-db-svr-bck   - PREPROD2

col file_name format a60

select file_name,bytes/1024/1024/1024 GB,autoextensible from dba_data_files where tablespace_name like 'SCHNEIDERIDX';

alter database datafile '+DATA/preprod/datafile/schneideridx.306.800110763' resize 24G;