RMAN Recovery Script

col "Restore Command" for a100 col "Applied Logs" for a100 col "Catalog Logs" for a100 col "Recover Command" for a80 select ' restore archivelog from logseq ' || applied_arc.startNo ||
' until logseq ' || catalog_arc.endNo || ' thread=' ||
catalog_arc.thread# || ';' "Restore Command" from --(select thread#,max(sequence#) + 1 startNo from gv$archived_log
where applied='YES' group by thread#) applied_arc, (select thread#,max(sequence#) startNo from gv$archived_log where
applied='YES' group by thread#) applied_arc, (select thread#, max(sequence#) endNo from v$backup_archivelog_details
group by thread#) catalog_arc where applied_arc.thread# = catalog_arc.thread#; prompt '=========== Archive Log Info =============' select distinct 'Thread ' || thread# || ': last applied archive log '
|| sequence# || ' at ' || to_char(next_time, 'MON/DD/YYYY HH24:MI:SS')
|| ' next change# ' || next_change# "Applied Logs" from v$archived_log where thread# || '_' || sequence# in (select thread# || '_' || max(sequence#) from v$archived_log where
applied='YES' group by thread#) --and applied='YES' ; select 'Thread ' || thread# || ': last cataloged archive log '
|| sequence# || ' at ' || to_char(next_time, 'MON/DD/YYYY HH24:MI:SS')
|| ' next change# ' || next_change# "Catalog Logs" from v$backup_archivelog_details where thread# || '_' || sequence# in (select thread# || '_' || max(sequence#) from
v$backup_archivelog_details group by thread#) ; prompt '=========== recover point ================' --select 'recover database until sequence ' || seq# || ' thread '
|| thread# || ' delete archivelog maxsize 4000g; ' Content select 'set until sequence ' || seq# || ' thread ' || thread#
|| '; ' || chr(13)|| chr(10) || 'recover database
delete archivelog maxsize 4000g; ' "Recover Command" from ( select * from ( select thread#, sequence# + 1 seq#, next_change# from ( select * from v$backup_archivelog_details where thread# || '_' || sequence# in (select thread# || '_' || max(sequence#) from
v$backup_archivelog_details group by thread#) ) order by next_change# ) where rownum = 1 ) ; https://weidongzhou.wordpress.com/2014/09/20/
script-to-identify-the-restore-and-recover-point-for-archive-logs/

ETA for long running Rollback

set serveroutput on
DECLARE
  type t_undoblocks is table of number index by varchar2(100);
  type t_ublk is table of number index by varchar2(100);
  v_undoblocks t_undoblocks;
  v_ublk t_ublk;
  v_eta number;
  v_sleep number := 3;
BEGIN
  for r in (SELECT cast(b.XID as varchar2(100)) xid, b.used_urec FROM v$transaction b)
  LOOP
     v_ublk(r.xid) := r.used_urec;
  end loop;
  dbms_output.put_line('Checking if SMON is recovering any transactions');
  for r in (select cast(XID as varchar2(100)) xid, state,undoblocksdone,undoblockstotal,RCVSERVERS from V$FAST_START_TRANSACTIONS where state<>'RECOVERED')
  LOOP
    v_undoblocks(r.xid) := r.undoblocksdone;
    dbms_output.put_line(rpad('TransactionID',25) || rpad('state',15) || rpad('recover_servers',20) || rpad('undo_blocks_total',20) || rpad('undo_blocks_done',20));
    dbms_output.put_line(rpad(r.XID,25) || rpad(r.state,25) || rpad(to_char(r.RCVSERVERS),20) || rpad(to_char(r.undoblockstotal),20) || rpad(to_char(r.undoblocksdone),20));
  end loop;

  dbms_output.put_line(chr(10) ||'Sleep '||v_sleep||' seconds to check again...');
  dbms_lock.sleep(v_sleep);

  for r in (select cast(XID as varchar2(100)) xid, state,undoblocksdone,undoblockstotal,RCVSERVERS from V$FAST_START_TRANSACTIONS where state<>'RECOVERED')
  LOOP
    if v_undoblocks.exists(r.xid) then
       if r.undoblocksdone > v_undoblocks(r.xid) then
         v_eta := round((r.undoblockstotal-r.undoblocksdone)*v_sleep/60/(r.undoblocksdone-v_undoblocks(r.xid)),1);
         dbms_output.put_line('SMON is rolling back '||r.xid||'...'||r.undoblocksdone||' out of '||r.undoblockstotal||' blocks are done...ETA
is '||v_eta||' minutes');
       else
         dbms_output.put_line('SMON is rolling back '||r.xid||'...'||r.undoblocksdone||' out of '||r.undoblockstotal||' blocks are done...ETA
is unknown, pls try again');
       end if;
    end if;
  end loop;

  dbms_output.put_line(chr(10) ||'Checking if any transaction is rolling back by itself');
  for r in (SELECT a.sid, cast(b.XID as varchar2(100)) xid, b.used_urec FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr)
  LOOP
      if v_ublk.exists(r.xid) then
         if v_ublk(r.xid) > r.used_urec THEN
            v_eta := round(r.used_urec * v_sleep/60/(v_ublk(r.xid) - r.used_urec), 1);
            dbms_output.put_line('SID,XID : '||r.sid||','||r.xid||' is rolling back...'||r.used_urec||' blocks to go...ETA is '||v_eta||' minutes');
         end if;
      end if;
  end loop;
end;
/
OUTPUT
------------------
Checking if SMON is recovering any transactions

TransactionID                        state          recover_servers     undo_blocks_total   undo_blocks_done
000A0006077F6G56         RECOVERING               0         4093224                            117092

Sleep 3 seconds to check again...
SMON is rolling back 000A0006077F6G56...117278 out of 4093224 blocks are done...ETA
is 1068.8 minutes

Checking if any transaction is rolling back by itself

PL/SQL procedure successfully completed.

Solution :
======

How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1) SQL> select pid, program from v$process where program like '%SMON%'; PID PROGRAM ---------- ------------------------------------------------ 30 oracle@acxdb02.bncollege.com (SMON) SQL> oradebug setorapid 30 Oracle pid: 30, Unix process pid: 25461, image: oracle@node02.com (SMON) SQL> oradebug event 10513 trace name context forever, level 2 Statement processed. SQL> select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS); no rows selected SQL> alter system set fast_start_parallel_rollback=false; System altered. SQL> oradebug setorapid 30 Oracle pid: 30, Unix process pid: 25461, image: oracle@node02.com (SMON) SQL> oradebug event 10513 trace name context off Statement processed.

Reference : https://alexzeng.wordpress.com/2011/09/11/how-to-check-rollback-transaction/

RAC Rolling Restart during maintenance activity

1. Check the server status . 

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl status server -f
NAME=node1
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

NAME=node2
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

2. Check your cluster name :

root@node1 ~]# /data01/app/11204/grid_11204/bin/cemutlo -n
node-cluster
[root@node1 ~]#

3. Check status of all nodeapps :
----------------------------------------

[oracle@node1 ~]$ srvctl status nodeapps
VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP node2-vip is enabled
VIP node2-vip is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

Check scan and scan listener status :
============================

[oracle@node1 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node node2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node node1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node node1
[oracle@node1 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node1
[oracle@node1 ~]$

Check the status of CRS on specific node:
===================================================
[grid@node1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[grid@node1 ~]$ crsctl check cluster
===========================================
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[grid@node1 ~]$ crsctl check cluster -all
**************************************************************
node1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
node2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

[oracle@node1 ~]$ srvctl status database -d racdb -v
Instance racdb1 is running on node node1. Instance status: Open.
Instance racdb2 is running on node node2. Instance status: Open.

How to check the status of complete clusterware stack on all nodes:
=======================================================================

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_NEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.FRA.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRNEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRVOTE.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCR_VOTE.dg
               ONLINE  OFFLINE      node1                                      
               ONLINE  OFFLINE      node2                                      
ora.asm
               ONLINE  ONLINE       node1                    Started            
               ONLINE  ONLINE       node2                    Started            
ora.gsd
               OFFLINE OFFLINE      node1                                      
               OFFLINE OFFLINE      node2                                      
ora.net1.network
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.ons
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.registry.acfs
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                                      
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node1                                      
ora.cvu
      1        ONLINE  ONLINE       node2                                      
ora.node1.vip
      1        ONLINE  ONLINE       node1                                      
ora.node2.vip
      1        ONLINE  ONLINE       node2                                      
ora.oc4j
      1        ONLINE  OFFLINE                                                  
ora.racdb.db
      1        ONLINE  ONLINE       node1                    Open              
      2        ONLINE  ONLINE       node2                    Open              
ora.scan1.vip
      1        ONLINE  ONLINE       node2                                      
ora.scan2.vip
      1        ONLINE  ONLINE       node1                                      
ora.scan3.vip
      1        ONLINE  ONLINE       node1                            


[oracle@node1 ~]$ ps -ef | grep d.bin
root      2579     1  1 02:07 ?        00:00:19 /data01/app/11204/grid_11204/bin/ohasd.bin reboot
grid      2857     1  0 02:07 ?        00:00:00 /data01/app/11204/grid_11204/bin/mdnsd.bin
grid      2868     1  0 02:07 ?        00:00:02 /data01/app/11204/grid_11204/bin/gpnpd.bin
grid      2878     1  0 02:07 ?        00:00:14 /data01/app/11204/grid_11204/bin/gipcd.bin
root      2892     1  2 02:07 ?        00:00:44 /data01/app/11204/grid_11204/bin/osysmond.bin
grid      2948     1  0 02:07 ?        00:00:14 /data01/app/11204/grid_11204/bin/ocssd.bin
root      3145     1  0 02:08 ?        00:00:07 /data01/app/11204/grid_11204/bin/octssd.bin reboot
grid      3177     1  0 02:08 ?        00:00:05 /data01/app/11204/grid_11204/bin/evmd.bin
root      4426     1  1 02:14 ?        00:00:13 /data01/app/11204/grid_11204/bin/crsd.bin reboot
oracle    9034  8298  0 02:37 pts/2    00:00:00 grep d.bin
[oracle@node1 ~]$

How to stop crs on specific node:
===================================
[oracle@node1 ~]$ srvctl stop instance -d racdb -i racdb1

[oracle@node1 ~]$ srvctl status  database -d racdb -v
Instance racdb1 is not running on node node1
Instance racdb2 is running on node node2. Instance status: Open.

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.OCRNEW.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.OCRVOTE.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'node1'
CRS-2673: Attempting to stop 'ora.DATA_NEW.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'node1'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'node1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.node1.vip' on 'node1'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'node1'
CRS-2677: Stop of 'ora.node1.vip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.node1.vip' on 'node2'
CRS-2677: Stop of 'ora.scan2.vip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'node2'
CRS-2677: Stop of 'ora.scan3.vip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'node2'
CRS-2676: Start of 'ora.node1.vip' on 'node2' succeeded
CRS-2676: Start of 'ora.scan2.vip' on 'node2' succeeded
CRS-2676: Start of 'ora.scan3.vip' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'node2'
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'node2'
CRS-2677: Stop of 'ora.registry.acfs' on 'node1' succeeded
CRS-2677: Stop of 'ora.OCRNEW.dg' on 'node1' succeeded
CRS-2677: Stop of 'ora.DATA_NEW.dg' on 'node1' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'node1' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'node2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'node2' succeeded
CRS-2677: Stop of 'ora.OCRVOTE.dg' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'node1'
CRS-2677: Stop of 'ora.ons' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'node1'
CRS-2677: Stop of 'ora.net1.network' on 'node1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'node1' has completed
CRS-2677: Stop of 'ora.crsd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'node1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2673: Attempting to stop 'ora.evmd' on 'node1'
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2677: Stop of 'ora.evmd' on 'node1' succeeded
CRS-2677: Stop of 'ora.crf' on 'node1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'node1' succeeded
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@node2 ~]# /data01/app/11204/grid_11204/bin/crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_NEW.dg
               ONLINE  ONLINE       node2                                      
ora.FRA.dg
               ONLINE  ONLINE       node2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       node2                                      
ora.OCRNEW.dg
               ONLINE  ONLINE       node2                                      
ora.OCRVOTE.dg
               ONLINE  ONLINE       node2                                      
ora.OCR_VOTE.dg
               ONLINE  OFFLINE      node2                                      
ora.asm
               ONLINE  ONLINE       node2                    Started            
ora.gsd
               OFFLINE OFFLINE      node2                                      
ora.net1.network
               ONLINE  ONLINE       node2                                      
ora.ons
               ONLINE  ONLINE       node2                                      
ora.registry.acfs
               ONLINE  ONLINE       node2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.cvu
      1        ONLINE  ONLINE       node2                                      
ora.node1.vip
      1        ONLINE  INTERMEDIATE node2                    FAILED OVER        
ora.node2.vip
      1        ONLINE  ONLINE       node2                                      
ora.oc4j
      1        ONLINE  ONLINE       node2                                      
ora.racdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown  
      2        ONLINE  ONLINE       node2                    Open              
ora.scan1.vip
      1        ONLINE  ONLINE       node2                                      
ora.scan2.vip
      1        ONLINE  ONLINE       node2                                      
ora.scan3.vip
      1        ONLINE  ONLINE       node2                                

[oracle@node2 ~]$ srvctl status database -d racdb -v
Instance racdb1 is not running on node node1
Instance racdb2 is running on node node2. Instance status: Open.

[oracle@node2 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node node2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node node2
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node node2

[oracle@node2 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node2

======================================================================
                   COMPLETE THE MAINTENANCE ACTIVITY ON NODE1 AND
                                         RESTART RESOURCES ON NODE1 
======================================================================

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@node1 ~]# ps -ef | grep d.bin
root     14259     1  1 03:11 ?        00:00:02 /data01/app/11204/grid_11204/bin/ohasd.bin reboot
grid     14430     1  0 03:11 ?        00:00:00 /data01/app/11204/grid_11204/bin/mdnsd.bin
grid     14441     1  0 03:11 ?        00:00:00 /data01/app/11204/grid_11204/bin/gpnpd.bin
grid     14452     1  1 03:11 ?        00:00:01 /data01/app/11204/grid_11204/bin/gipcd.bin
root     14466     1  1 03:11 ?        00:00:01 /data01/app/11204/grid_11204/bin/osysmond.bin
grid     14511     1  1 03:11 ?        00:00:01 /data01/app/11204/grid_11204/bin/ocssd.bin
root     14627     1  0 03:11 ?        00:00:00 /data01/app/11204/grid_11204/bin/octssd.bin reboot
grid     14647     1  0 03:11 ?        00:00:00 /data01/app/11204/grid_11204/bin/evmd.bin
root     14911     1  2 03:12 ?        00:00:00 /data01/app/11204/grid_11204/bin/crsd.bin reboot
root     15215 10009  0 03:13 pts/2    00:00:00 grep d.bin

[oracle@node1 ~]$ srvctl start instance -d racdb -i racdb1

[oracle@node1 ~]$ srvctl status database -d racdb -v
Instance racdb1 is running on node node1. Instance status: Open.
Instance racdb2 is running on node node2. Instance status: Open.
[oracle@node1 ~]$

[oracle@node1 ~]$ /data01/app/11204/grid_11204/bin/crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_NEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.FRA.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRNEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRVOTE.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCR_VOTE.dg
               ONLINE  OFFLINE      node1                                      
               ONLINE  OFFLINE      node2                                      
ora.asm
               ONLINE  ONLINE       node1                    Started            
               ONLINE  ONLINE       node2                    Started            
ora.gsd
               OFFLINE OFFLINE      node1                                      
               OFFLINE OFFLINE      node2                                      
ora.net1.network
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.ons
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.registry.acfs
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                                      
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.cvu
      1        ONLINE  ONLINE       node2                                      
ora.node1.vip
      1        ONLINE  ONLINE       node1                                      
ora.node2.vip
      1        ONLINE  ONLINE       node2                                      
ora.oc4j
      1        ONLINE  ONLINE       node2                                      
ora.racdb.db
      1        ONLINE  ONLINE       node1                    Open              
      2        ONLINE  ONLINE       node2                    Open              
ora.scan1.vip
      1        ONLINE  ONLINE       node1                                      
ora.scan2.vip
      1        ONLINE  ONLINE       node2                                      
ora.scan3.vip
      1        ONLINE  ONLINE       node2                                      
[oracle@node1 ~]$

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

Follow same process on node2:

[oracle@node2 ~]$ srvctl stop instance -d racdb -i racdb2

[root@node2 ~]# /data01/app/11204/grid_11204/bin/crsctl stop crs

======================================================================
                   COMPLETE THE MAINTENANCE ACTIVITY ON NODE2 AND
                                         RESTART RESOURCES ON NODE2
======================================================================


[root@node2 ~]# /data01/app/11204/grid_11204/bin/crsctl start crs
[oracle@node2 ~]$ srvctl start instance -d racdb -i racdb2

======================================================================
If you want to stop both nodes at same time ( Non-Rolling)  . You can use below steps :
=====================================================================

[oracle@node1 ~]$ srvctl stop database -d racdb

[oracle@node1 ~]$ srvctl status database -d racdb -v
Instance racdb1 is not running on node node1
Instance racdb2 is not running on node node2
[oracle@node1 ~]$
[oracle@node1 ~]$ /data01/app/11204/grid_11204/bin/crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_NEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.FRA.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRNEW.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCRVOTE.dg
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.OCR_VOTE.dg
               ONLINE  OFFLINE      node1                                      
               ONLINE  OFFLINE      node2                                      
ora.asm
               ONLINE  ONLINE       node1                    Started            
               ONLINE  ONLINE       node2                    Started            
ora.gsd
               OFFLINE OFFLINE      node1                                      
               OFFLINE OFFLINE      node2                                      
ora.net1.network
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.ons
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
ora.registry.acfs
               ONLINE  ONLINE       node1                                      
               ONLINE  ONLINE       node2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                                      
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                                      
ora.cvu
      1        ONLINE  ONLINE       node2                                      
ora.node1.vip
      1        ONLINE  ONLINE       node1                                      
ora.node2.vip
      1        ONLINE  ONLINE       node2                                      
ora.oc4j
      1        ONLINE  ONLINE       node2                                      
ora.racdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown  
      2        OFFLINE OFFLINE                               Instance Shutdown  
ora.scan1.vip
      1        ONLINE  ONLINE       node1                                      
ora.scan2.vip
      1        ONLINE  ONLINE       node2                                      
ora.scan3.vip
      1        ONLINE  ONLINE       node2                      

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.OCRNEW.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.OCRVOTE.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'node1'
CRS-2673: Attempting to stop 'ora.DATA_NEW.dg' on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'node1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.node1.vip' on 'node1'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'node1'
CRS-2677: Stop of 'ora.FRA.dg' on 'node1' succeeded
CRS-2677: Stop of 'ora.DATA_NEW.dg' on 'node1' succeeded
CRS-2677: Stop of 'ora.OCRNEW.dg' on 'node1' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'node1' succeeded
CRS-2677: Stop of 'ora.node1.vip' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.crsd' on 'node2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node2'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.OCRNEW.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.OCRVOTE.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA_NEW.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.oc4j' on 'node2'
CRS-2673: Attempting to stop 'ora.cvu' on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'node2'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.node2.vip' on 'node2'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'node2'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'node2'
CRS-2677: Stop of 'ora.cvu' on 'node2' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'node2' succeeded
CRS-2677: Stop of 'ora.node2.vip' on 'node2' succeeded
CRS-2677: Stop of 'ora.OCRNEW.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.DATA_NEW.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'node2' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'node1' succeeded
CRS-2677: Stop of 'ora.OCRVOTE.dg' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'node1'
CRS-2677: Stop of 'ora.ons' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'node1'
CRS-2677: Stop of 'ora.net1.network' on 'node1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'node1' has completed
CRS-2677: Stop of 'ora.crsd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2673: Attempting to stop 'ora.evmd' on 'node1'
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2677: Stop of 'ora.OCRVOTE.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'node1' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'node2'
CRS-2677: Stop of 'ora.ons' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'node2'
CRS-2677: Stop of 'ora.net1.network' on 'node2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'node2' has completed
CRS-2677: Stop of 'ora.crsd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node2'
CRS-2673: Attempting to stop 'ora.evmd' on 'node2'
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.evmd' on 'node2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node2' succeeded
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node2'
CRS-2677: Stop of 'ora.cssd' on 'node2' succeeded

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl check has
CRS-4638: Oracle High Availability Services is online

[root@node1 ~]# ps -ef | grep d.bin
root     14259     1  0 03:11 ?        00:00:10 /data01/app/11204/grid_11204/bin/ohasd.bin reboot
grid     14430     1  0 03:11 ?        00:00:00 /data01/app/11204/grid_11204/bin/mdnsd.bin
grid     14441     1  0 03:11 ?        00:00:01 /data01/app/11204/grid_11204/bin/gpnpd.bin
grid     14452     1  0 03:11 ?        00:00:08 /data01/app/11204/grid_11204/bin/gipcd.bin
root     14466     1  1 03:11 ?        00:00:19 /data01/app/11204/grid_11204/bin/osysmond.bin

[root@node1 ~]# /data01/app/11204/grid_11204/bin/crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node1'
CRS-2673: Attempting to stop 'ora.crf' on 'node1'
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2677: Stop of 'ora.crf' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.drivers.acfs' on 'node1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

NODE 2 :

[root@node2 ~]# /data01/app/11204/grid_11204/bin/crsctl check has
CRS-4638: Oracle High Availability Services is online
[root@node2 ~]# ps -ef | grep d.bin
root      7169     1  0 02:59 ?        00:00:14 /data01/app/11204/grid_11204/bin/ohasd.bin reboot
grid      7303     1  0 02:59 ?        00:00:00 /data01/app/11204/grid_11204/bin/mdnsd.bin
grid      7315     1  0 02:59 ?        00:00:01 /data01/app/11204/grid_11204/bin/gpnpd.bin
grid      7326     1  0 02:59 ?        00:00:10 /data01/app/11204/grid_11204/bin/gipcd.bin
root      7340     1  1 02:59 ?        00:00:38 /data01/app/11204/grid_11204/bin/osysmond.bin
root     14636  5007  0 03:41 pts/0    00:00:00 grep d.bin

[root@node2 ~]# /data01/app/11204/grid_11204/bin/crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node2'
CRS-2673: Attempting to stop 'ora.crf' on 'node2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node2'
CRS-2677: Stop of 'ora.drivers.acfs' on 'node2' succeeded
CRS-2677: Stop of 'ora.crf' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node2'
CRS-2677: Stop of 'ora.mdnsd' on 'node2' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node2'
CRS-2677: Stop of 'ora.gpnpd' on 'node2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@node2 ~]#

Send a logfile as attachment using mailx

$ uuencode test.log test.log |mailx -s "Send a logfile as attachment using mailx " xyz@gmail.com

Debug CRS not coming up on BOOT

# su - <crs-user> -c "<CRS_HOME>/bin/crsctl check boot"
# echo $?

i.e.

# su - crsuser -c "/ocw/crs/bin/crsctl check boot"
# echo $?
0

The Block Change Tracking File

When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.

The block change tracking file is maintained in the directory defined by the db_create_file_dest parameter, if it is configured. You can also define the location of the block change tracking file via the using file clause of the alter database enable block change tracking command. You can rename the block change tracking file with the alter database rename file command, just as you would with any normal database file. Here is an example of turning on block change tracking, using the using file clause to define where the block change tracking file should be:

Alter database enable block change tacking
using file '/u01/oracle/RMAN/blocktrack/my_db_tracking.chg';
The block change tracking file size depends on svereal considerations, including the number of enabled redo threads, the size of the database, and the number of RMAN backups for which change data needs to be stored. The formula looks like this:
R=((T * 2)+ B) * (S/250000)
where:
R = The size of the block change tracking file, in bytes

T = The number of enabled redo threads

S = The size of the database, in bytes

B = The number of incremental backups for which RMAN needs to store change tracking data

• To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

• For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

Restoring previous version of stats

Scenario : Performance of certain queries has deteriorated suddenly after collecting fresh statistics . You want to revert the stats to previous values .

Solution : Use the dbms_stats.restore_stats procedure to revert to an older set of optimizer statistics .

1. Check the retention of stats for your database

SQL > select dbms_stats.get_Stats_history_retention from dual

GET_STATS_HISTORY_RETENTION
--------------------------------------------------
                              31

2. Check how far you can go to restore the stats :

SQL> select dbms_stats.get_stats_history_availability from dual ;

GET_STATS_HISTORY_AVAILABLITY
--------------------------------------------------
17-APR-2016 03:05:26.7180000000 AM

3. exec dbms_stats.restore_schema_stats(ownername=>'SUMIT' , as_of_timestamp = > '15-May-2016 01.30.30.211212121 PM' , no_invalidate=> false ) ;

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database?

Estimate The Size Of Tables

The CREATE_TABLE_COST procedure of the DBMS_SPACE package helps to estimate the size of the table segment ,

This procedure is used in capacity planning to determine the size of the table given various attributes , The CREATE_TABLE_COST procedure has two versions :

• The first version takes the average row size of the table as argument and outputs the table size.
• The second version takes the column information of the table as argument and outputs the table size.

Both Versions also Requires the following input :

•  the expected number of rows
•  pct_free setting for the table
•  the tablespace name where the table would be created.

Version 1
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   avg_row_size       IN NUMBER,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   colinfos           IN CREATE_TABLE_COST_COLUMNS,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

The output of the procedure contains used_bytes and alloc_bytes :

•  The used_bytes : represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
•  The alloc_bytes : represent the size of the table segment when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

Example for using Version 1:

This methode requires creating the table first with some sample data to calculate the average row size of the table ,

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';

AVG_ROW_LEN
-----------
         68

Drop table test;

set serveroutput on 

DECLARE 
 ub NUMBER; 
 ab NUMBER; 
BEGIN 
  DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab); 
  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab)); 
END; 
/ 

Used Bytes      =  8036352
Allocated Bytes =  8388608

which is around 8 MB , now lets create and populate the actual table.

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

     BYTES SEGMENT_TYPE
---------- ------------------
  9437184 TABLE

which is around 9 MB

Example for using Version 2:

This example estimate a Table size with one column NUMBER (10) , two columns VARCHAR2 (30) and one column date , with pct_free=10 and row_count=100000 :

set serveroutput on 

DECLARE 
 ub NUMBER; 
 ab NUMBER; 
 cl sys.create_table_cost_columns; 
BEGIN 
  cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10), 
        sys.create_table_cost_colinfo('CHAR',30), 
        sys.create_table_cost_colinfo('CHAR',30), 
        sys.create_table_cost_colinfo('DATE',NULL)); 

  DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab); 

  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab)); 
END; 
/ 

Used Bytes      = 9314304
Allocated Bytes = 9437184

which is around 9 MB , now lets create and populate the actual table.  

Note : we changed VARCHAR2 to CHAR to get the maximum possible estimation .

  

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

     BYTES SEGMENT_TYPE
---------- ------------------
  9437184 TABLE

which is around 9 MB

You may notice that version 2 of The CREATE_TABLE_COST procedure is more accurate than version 1 , which is expected because version 2 estimates the size based on the table structure which includes the column data types and length , plus the storage attributes .

Estimate The Size Of Indexes

The CREATE_INDEX_COST Procedure of the DBMS_SPACE package helps to estimate the size of creating an index on an existing table.

The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Usage Notes :

•     The table on which the index is created must already exist.
•     The computation of the index size depends on statistics gathered on the segment.
•     It is imperative that the table must have been analyzed recently.
•     In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

The output of the procedure contains used_bytes and alloc_bytes :

•  The used_bytes : how much space is for the index data
•  The alloc_bytes : how much space is allocated within the tablespace for the index segment.

Estimate Index Size example :

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

set serveroutput on 

declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index test_indx on test (a,b) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   

Used Bytes      = 3800000
Allocated Bytes = 6291456

which is around 6 MB , now lets create the actual index.

create index test_indx on test (a,b) tablespace users

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';

     BYTES SEGMENT_TYPE
---------- ------------------
   6291456 INDEX

which is around 6 MB 

 Reference oracle support (Doc ID 1585326.1)

How to find OMS repository database information

1. Check in the file emgc.properties for the parameter EM_REPOS_CONNECTDESCRIPTOR . 

EM_REPOS_CONNECTDESCRIPTOR=(DESCRIPTION\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=hostname-scan.abc.com)(PORT\=1521))(CONNECT_DATA\=(SERVER\=DEDICATED)(SERVICE_NAME\=dbname_service)))

2. emctl config oms -list_repos_details

C:\oracle\product\mw\oms\BIN>emctl config oms -list_repos_details
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Repository Connect Descriptor : (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname-scan.abc.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbname_service)))
Repository User : sysman

Why odd number of voting disk

Simple Majority Rule, According to Oracle – “An absolute majority of voting disks configured (more than half) must be available and responsive at all times for Oracle Clusterware to operate.” Which means to survive from loss of ‘N’ voting disks, you must configure atleast ‘2N+1′ voting disks.

OR we can say a node must be able to see trun(n/2 + 1) voting disks to survive as a part of cluster . 

• When you have 1 voting disk and it goes bad, the cluster stops functioning.
• When you have 2 and 1 goes bad, as per simple majority rule cluster stop functioning 
• When you have 3 and 1 goes bad, the cluster runs fine . 
• When you have 3 and 2 go bad, the cluster stops .
• When you have 4 and 1 goes bad, cluster run fine . 
• When you have 4 and 2 go bad, the same, because the nodes can only access half, not > half.
• So you see 4 voting disks have the same fault tolerance as 3, but you waste 1 disk, without gaining anything.
• The recommendation for odd number of voting disks helps save a little on hardware requirement.