Scripts to find USED,FREE and total DataBase sizes

--> Script to find Total DB size:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024/1024 data_size
         from dba_data_files ) a,
     ( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
         from dba_temp_files ) b,
     ( select sum(bytes)/1024/1024/1024 redo_size
         from sys.v_$log ) c;


--> Script to find Total and Free DB sizes:

col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 /1024) || ' GB' "Database Size"
,      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
/

--> Script to find USED DB size:

SELECT round(SUM(bytes)/1024/1024/1024) "GB" FROM dba_segments;

--> Script to report true free space within the used portion of the TEMPFILE:

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

RC:crs services are not coming up after removal OS user - CRS-0184: Cannot communicate with the CRS daemon after reboot

Steps performed in CRS startup issue and its resolution after OS user (ORATEST) removal:
--> Error message: the message lists the serverpool, listener and data type entries for user “ORATEST”
[SYSTEM.CRSD.TYPES.ora!database!type.TYPE_ACL.CONFIG]
ORATEXT : DEFAULT=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~FLAGS=_CONFIG~ID=1078~TYPE=_STRING~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr]
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost1_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=never~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost1.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

..
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost2!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost2_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=restore~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost2.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}


--> The commands to stop and delete the server pool associated with ORATEST.

[root@myhost1 ~]# cd $GRID_HOME/bin
./crsctl stop resource ora.ORATEST.db
./crsctl delete resource ora.ORATEST.db
./crsctl delete serverpool ora.ORATEST
./crsctl getperm serverpool ora.ORATEST
./crsctl status serverpool

--> Commands to remove the  ORATEST listeners
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost1
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost2
[root@myhost1 bin]# ./srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): myhost1,myhost2

--> Commands executed during action to delete data type

1. Check database type
# ./crsctl status type |grep database
TYPE_NAME=ora.database.type

2. Check/Remove database
cd $ORACLE_HOME/bin
$srvctl config database -d ORATEST
$srvctl remove database -d db_name

3. Delete database type resource
cd $GRID_HOME/bin
# ./crsctl delete type ora.database.type
# ./crsctl status type |grep database
/d01/crstest/product/11.2.0/bin/crsctl status type  ora.database.type -p | grep DEFAULT_VALUE=owner
4. Get ocrdump With ROOT user
cd $GRID_HOME/bin
./ocrdump /tmp/ocrdump_after.log
# cat /tmp/ocrdump_after.log|grep -i ORATEST
5. Add database
Syntax --
$ ./srvctl add database -d <db_name> -p <Spfile> -o<Oracle_home>
$ ./srvctl add instance -d <db_name> -i <instance_name> -n <node>

Commands --
./srvctl add database -d VAL -o /d01/oraval/product/10.2.0
./srvctl add instance -d VAL -i VAL1 -n myhost1
./srvctl add instance -d VAL -i VAL2 -n myhost2

6. Get ocrdump.
./ocrdump /tmp/ocrdump_withdb.log
7. Stop/Start CRS (As OS user already deleted)
[root@myhost2 bin]# id ORATEST
id: ORATEST: No such user
#crsctl stop crs
#crsctl start crs
8. Stop crs/reboot node
#crsctl stop crs
9. Reboot nodes.
10. Check CRS services:
[root@myhost1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       myhost1
               ONLINE  ONLINE       myhost2
ora.asm
               OFFLINE OFFLINE      myhost1
               OFFLINE OFFLINE      myhost2
ora.gsd
               OFFLINE OFFLINE      myhost1
               OFFLINE OFFLINE      myhost2
ora.net1.network
               ONLINE  ONLINE       myhost1
               ONLINE  ONLINE       myhost2
ora.ons
               ONLINE  ONLINE       myhost1
               ONLINE  ONLINE       myhost2
ora.registry.acfs
               OFFLINE OFFLINE      myhost1
               OFFLINE OFFLINE      myhost2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       myhost2
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       myhost1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       myhost1
ora.VAL.VAL1.inst
      1        ONLINE  ONLINE       myhost1
ora.VAL.VAL2.inst
      1        ONLINE  ONLINE       myhost2
ora.VAL.db
      1        ONLINE  ONLINE       myhost1
ora.cvu
      1        ONLINE  ONLINE       myhost1
ora.oc4j
      1        ONLINE  ONLINE       myhost1
ora.myhost1.LISTENER_VAL_myhost1.lsnr
      1        ONLINE  ONLINE       myhost1
ora.myhost1.vip
      1        ONLINE  ONLINE       myhost1
ora.myhost2.LISTENER_VAL_myhost2.lsnr
      1        ONLINE  ONLINE       myhost2
ora.myhost2.vip
      1        ONLINE  ONLINE       myhost2
ora.scan1.vip
      1        ONLINE  ONLINE       myhost2
ora.scan2.vip
      1        ONLINE  ONLINE       myhost1
ora.scan3.vip
      1        ONLINE  ONLINE       myhost1