Pages

Sunday, December 24, 2017

Get Oracle and Server Information

===========================
General
===========================
Get Oracle and OS information

===========================
Oracle
===========================


-- Instance Name
SELECT instance_name FROM V$INSTANCE;

-- Instance Number
SELECT instance_number FROM V$INSTANCE;

-- Oracle Version
SELECT version from V$INSTANCE;

-- Oracle Edition
SELECT banner FROM V$VERSION;

--Total Size of Datafiles
SELECT ROUND(SUM(maxbytes) /1024/1024/1024) AS GB 
  FROM DBA_DATA_FILES;

--Total Size of used space
SELECT ROUND(SUM(user_bytes) /1024/1024/1024) AS GB 
  FROM DBA_DATA_FILES;

--Memory settings
COL name FOR A30

COL value FOR A30
SELECT name, value FROM V$PARAMETER WHERE name like '%memo%';

--SGA Size
SELECT name, value FROM V$PARAMETER WHERE name = 'sga_max_size';

-- Location of oracle alert.log
SELECT name, value FROM V$PARAMETER 
 WHERE name like 'background_dump_dest'; 

-- Location of archive files
SELECT name, value FROM V$PARAMETER 
 WHERE name like 'log_archive_dest_1'; 

--ARCHIVEMODE/NONARCHIVEMOD
SELECT log_mode from V$DATABASE;

--To get Host OS Details from DB
SELECT DBMS_UTILITY.port_string from DUAL; 
x86_64/Linux 2.4

===========================
OS Server
===========================
--To get Host OS Details from Linux
less /etc/redhat-release
Red Hat Enterprise Linux Server release 5.10 (Tikanga)

less /proc/version 
Linux version 2.6.18-371.3.1.el5 (mockbuild@x86-028.build.eng.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-54)) #1 SMP Mon Nov 11 03:23:58 EST 2013

uname  -r
2.6.18-371.3.1.el5

name -a
Linux idn-pte-2-aps-1 2.6.18-371.3.1.el5 #1 SMP Mon Nov 11 03:23:58 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

--To get CPU info
-- processor model
less /proc/cpuinfo | grep vendor | uniq
GenuineIntel

--Number of processors
less /proc/cpuinfo | grep processor | wc -l
16

--Number of cores
less /proc/cpuinfo | grep "cpu core"
4*16