Database Details

I got the task to create a database for my HR department in cluster environment.

SQL> select name from v$database;

           NAME

            ---------

           HRLIVE

SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;

             INST_ID     COUNT(*)

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

                    1              3

                    2              3

SQL> select name from v$datafile;

NAME

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

+DATA/hrlive/datafile/system.286.867076971

+DATA/hrlive/datafile/sysaux.279.867076971

+DATA/hrlive/datafile/users.285.867076971

+DATA/hrlive/datafile/users.289.867607927

+DATA/hrlive/datafile/undotbs01.287.867689853

+DATA/hrlive/datafile/undotbs02.290.867690103

6 rows selected.

SQL> select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB

            from  (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space

            from dba_free_space group by tablespace_name) a,  (select tablespace_name,   

            sum(bytes)/1024/1024/1024 as tbs_size   from dba_data_files group by tablespace_name

            UNION

            select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size

            from dba_temp_files

           group by tablespace_name ) b

           where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEGB     FREEGB

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

SYSAUX                                              2                  1.5

USERS                                                20                 11.1

SYSTEM                                              2                  1.2

UNDOTBS01                                       5                     5

UNDOTBS02                                       5                     5

TEMP                                                   5

6 rows selected.

SQL> select name from v$controlfile;

NAME

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

+DATA/hrlive/controlfile/current.284.867077073

+RECO/hrlive/controlfile/current.474.867077073

SQL> select name from v$tempfile;

NAME

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

+DATA/hrlive/tempfile/temp.283.867077081

 SQL> select member from v$logfile;

MEMBER

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

+DATA/hrlive/onlinelog/group_1.276.867359185

+RECO/hrlive/onlinelog/group_1.456.867359185

+DATA/hrlive/onlinelog/group_2.277.867359245

+RECO/hrlive/onlinelog/group_2.362.867359245

+DATA/hrlive/onlinelog/group_3.280.867359337

+RECO/hrlive/onlinelog/group_3.450.867359339

+DATA/hrlive/onlinelog/group_4.281.867359409

+RECO/hrlive/onlinelog/group_4.332.867359409

8 rows selected.

SQL> select count(*) from gv$session where status='ACTIVE';

  COUNT(*)

----------

        97

SQL>  select count(*) from gv$session where status='INACTIVE';

  COUNT(*)

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

         3

SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)

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

    14.5908089

SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';

        GB

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

7.64480158

SQL> select name,floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used / 1024 / 1024) "Used              MB"  from v$recovery_file_dest;

NAME                                                            Size MB    Used MB

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

+RECO                                                             15000       5510

SQL> archive log list;

Database log mode                         Archive Mode

Automatic archival                         Enabled

Archive destination                        USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence            89

Next log sequence to archive         90

Current log sequence                     90

.
Happy Learning...