How to find a Schema size in Oracle

SELECT sum(bytes) / (1024 * 1024 * 1024) size_in_gb
  FROM dba_segments
 WHERE owner = 'SCHEMA_NAME';

How to find an Index size

select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='INDEX' and segment_name='your_index_name';

How to find a table size

select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='TABLE' and segment_name='your_table_name';

Script to see Database Size

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0


SELECT (SELECT Sum(bytes / 1048576)
        FROM   dba_data_files) "Data Mb",
       (SELECT Nvl(Sum(bytes / 1048576), 0)
        FROM   dba_temp_files) "Temp Mb",
       (SELECT Sum(bytes / 1048576) * Max(members)
        FROM   v$log)          "Redo Mb",
       (SELECT Sum(bytes / 1048576)
        FROM   dba_data_files)
       + (SELECT Nvl(Sum(bytes / 1048576), 0)
          FROM   dba_temp_files)
       + (SELECT Sum(bytes / 1048576) * Max(members)
          FROM   v$log)        "Total Mb"
FROM   dual; 

Active session information

    SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
    FROM V$Session
    WHERE
    Status='ACTIVE' AND
    UserName IS NOT NULL;

Multiplexing Controlfile through RMAN in RAC

Login to the first node

column is_recovery_dest_file format a25
column name format a60
set linesize 160
select status, name, is_recovery_dest_file from v$controlfile;


check the control file status and names using the above quey,


i my case i had


STATUS  NAME                                                         IS_RECOVERY_DEST_FILE
------- ------------------------------------------------------------ -------------------------
        +DATA_RAJ/rajesh/controlfile/current.986.790135655           NO

hence ,

login in to sqlplus , and alter the system as below with your new place pointed to

alter system set control_files = '+DATA_RAJ/rajesh/controlfile/current.986.790135655','+RECO_RAJ' scope=spfile sid='*';


in my case i have the data and reco groups created on the asm, hence trying to place the controlfiles in to two different disk groups.


once alterd the system , shutdown the instance using srvctl


srvctl stop database -d xxx

after then , login to sql prmpt and startup the instance in no mount stage , once the db came up on nomount stage exit from the sql prompt and login in rman prompt

example

rman

connect  target

restore controlfile from '+DATA_RAJ/rajesh/controlfile/current.986.790135655';


it will restore/multiplex our control file to the existing and our new path. once the restore complete, shutdown the instances and startup normal

it should come up with the two controlfiles,

issues i was facing here were, one of the instance spfile was not on the shared drive hence i was failing on the below command

alter system set control_files = '+DATA_RAJ/rajesh/controlfile/current.986.790135655','+RECO_RAJ' scope=spfile sid='*';


as spfile could not be modifiable etc ... after which i have mounted the spfile on the shared drive and followed the same

Good Luck !!


Thanks

How to backup oracle home

Follow the steps to backup oracle home


say if u have the oracle home like /opt/oracle/product/11.2.0/ then the binaries , and let say u want to have the home backup taken to some backup directory

cd to the home directory

cd /opt/oracle/product/11.2.0

and then issue

tar -cvf - . |compress > /backup/11.2.0.tar.Z


if u want to untar it

u use tar -xvf