Oracle

How to check Database Size, Used and Free Space in a Database

 

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, 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
/

Output like as

DB SİZE : 221 GB
USED : 132 GB
FREE : 89 GB

Advertisements
Standard
ASM

RESIZING DATAFILE IN ASM

  • Re-sizing  datafiles in ASM is very simple.

+ASM oracle@akyaka:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 26 14:53:56 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name from v$datafile;

NAME
——————————————————————————–

+DBA/sritam01/datafile/system.260.932399649
+DBA/sritam01/datafile/sysaux.261.932399653
+DBA/sritam01/datafile/undotbs1.262.932399655
+DBA/sritam01/datafile/users.264.932399665
+DBA/sritam01/datafile/cw_data.266.933506593
+DBA/sritam01/datafile/cw_index.267.933506607
+DBA/sritam01/datafile/tools.268.933506617
+DBA/sritam01/datafile/xcom_data.269.933506639
+DBA/sritam01/datafile/xcom_indx.270.933506647
+DBA/sritam01/datafile/xcom_lobs.271.933506659
+DBA/sritam01/datafile/cw_data.272.933506675

NAME
——————————————————————————–
+DBA/sritam01/datafile/cw_data.273.933506679
+DBA/sritam01/datafile/dbmain_data.274.950891839
+DBA/sritam01/datafile/cw_data.275.966863495
+DBA/sritam01/datafile/cw_data.276.966863579
+DBA/sritam01/datafile/cw_data.277.966863661

16 rows selected.

the following example shows how to extend a datafile to the table_space_name tablespace in the +DBA ASM group.

1. Identify the datafiles for the tablespace you want to increase.

SQL> select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name = ‘SYSTEM‘ order by file_name;

FILE_NAME
——————————————————————————–
MB
———-
+CWBAU/sritam01/datafile/system.260.932399649
30000

Extened the Datafile

SQL> alter database datafile ‘+DBA/sritam01/datafile/system.260.932399649’ RESIZE 30001M;

Database altered.

SQL> select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name = ‘SYSTEM’ order by file_name;

FILE_NAME
——————————————————————————–
MB
———-
+DBA/sritam01/datafile/system.260.932399649
30001

 

SQL>

Standard
Oracle

VirtualBox UDEV SCSI Rules Configuration In Oracle Linux 6 ( Install Oracle Grid on VM )

Oracle grid 11g r2, i added three disk on oracle vm.

like as :
Ekran Resmi 2018-04-14 20.08.28
Identify the Disks (/sbin/scsi_id)

[root@erman ~]# /sbin/scsi_id -g -u -d /dev/sdb
1ATA_VBOX_HARDDISK_VB08a6b549-f300066c
[root@erman ~]# /sbin/scsi_id -g -u -d /dev/sdc
1ATA_VBOX_HARDDISK_VBe062438c-ccbaf371
[root@erman ~]# /sbin/scsi_id -g -u -d /dev/sdd
1ATA_VBOX_HARDDISK_VBfcd96dfe-06d07a8b

Create the “/etc/udev/rules.d/99-oracle-asmdevices.rules” file.
[root@erman ~]#
KERNEL==”sd?1″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”1ATA_VBOX_HARDDISK_VB08a6b549-f300066c“, NAME=”asm-disk1“, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sd?1″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”1ATA_VBOX_HARDDISK_VBe062438c-ccbaf371“, NAME=”asm-disk2“, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
KERNEL==”sd?1″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”1ATA_VBOX_HARDDISK_VBfcd96dfe-06d07a8b“, NAME=”asm-disk3“, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″
Load Updated Block Device Partitions (/sbin/partprobe)
[root@erman ~]# /sbin/partprobe /dev/sdb
[root@erman ~]# /sbin/partprobe /dev/sdc
[root@erman ~]# /sbin/partprobe /dev/sdd
[root@erman ~]#
Show oracle asm disks.
[root@erman dev]# ls -la asm-disk*
brw-rw—-. 1 oracle dba 8, 17 Apr 14 16:44 asm-disk1
brw-rw—-. 1 oracle dba 8, 33 Apr 14 16:43 asm-disk2
brw-rw—-. 1 oracle dba 8, 33 Apr 14 16:43 asm-disk3
[root@erman dev]#

Ekran Resmi 2018-04-14 19.46.53

you can continue setup.

br erman.

Standard
ASM

INS-30510 reported while installing GridInfrastructure for Cluster or Single DB

Hello,

For storing Voting disks in ASM,we require minimum of three disks or three failgroups for Normal Redundancy diskgroup.

The number of voting files you can store in a particular Oracle ASM disk group depends upon the redundancy of the disk group.

ASM puts each voting disk in its own failure group within the disk group.

solutions: change Oracle asm disk group redudancy.

A normal redundancy disk group must contain at least two failure groups but if you are storing your voting disks on Oracle ASM, then a normal redundancy disk group must contain at least three failure groups.

Ekran Resmi 2018-04-14 17.33.34

BR. erman.

Standard
Linux

Linux Unix d.bin command

Hello, Some useful Oracle RAC Database commands in daily use

  • Background Processes
fraudms80:/dev#cd
fraudms80:/home/oracle#ps -ef |grep d.bin
oracle 7536914 1 0 10:17:18 - 0:00 /oracle/product/11.2.0.4/grid/bin/ohasd.bin reboot
oracle 12452116 1 0 10:17:38 - 0:00 /oracle/product/11.2.0.4/grid/bin/evmd.bin
oracle 14287240 1 0 10:17:38 - 0:00 /oracle/product/11.2.0.4/grid/bin/oraagent.bin
oracle 23200164 12452116 0 10:17:39 - 0:00 /oracle/product/11.2.0.4/grid/bin/evmlogger.bin -o /oracle/product/11.2.0.4/grid/evm/log/evmlogger.info -l /oracle/product/11.2.0.4/grid/evm/log/evmlogger.log
fraudms80:/home/oracle#

 

Standard
AIX

AIX server : The file access permissions do not allow the specified action Error Solutions !

Hello, my server version AIX fraudms80 2 7 00CC51B74C00 while copy folder, creating a new folder or etc. is returning:

The file access permissions do not allow the specified action Error. 

the file is being copied with the expected permissions so why is AIX giving me this error?

Look for the dot “.” directory, ownership and permissions?

fraudms80:/home/oracle#ls -lart
total 16
drwxr-xr-x 2 500 dba 256 Feb 26 14:30 .
-rwxr----- 1 500 dba 254 Feb 26 14:30 .profile
drwxr-xr-x 17 bin bin 4096 Mar 20 14:54 ..

If you are a system administrator and have access to the NFS mount check/change the permission again (it should be dynamic), and if you NOT, then you can notify them saying the same.

-rw-r--r-- 1 root system 3603 Dec 08 2008 README_nmon12e.txt
drwxr-xr-x 10 oracle dba 4096 Sep 03 2011 jdk1.6.0_27
drwxr-xr-x 3 oracle dba 256 Dec 23 2011 .java
-rwx------ 1 oracle dba 275 Dec 23 2011 .profile
drwxr-xr-x 3 oracle dba 256 Dec 23 2011 .oracle

home directory should be as follows for Oracle Database .

now we changing folder owner in the /home.

Like chmod -R 777 somefolder and someotherfolder.
Make sure, while chmod’ing to use -R flag (recursive)

 fraudms80:/oracle#ls -la
total 8
drwxr-xr-x 4 oracle dba 256 Apr 12 10:52 .
drwxr-xr-x 31 root system 4096 Apr 05 14:11 ..
drwxr-xr-x 2 root system 256 Feb 26 16:04 lost+found
drwxrwxrwx 4 oracle dba 256 Apr 12 10:59 setup
fraudms80:/oracle#

trying;
fraudms80:/home/oracle#cp .profile .profile_asm
fraudms80:/home/oracle#

yes, problem is solved.

BR.

erman

 

Standard
Oracle

Memory management in Oracle database

 

SQL> COLUMN component FORMAT A30

SELECT  component, current_size, min_size, max_size
FROM    v$memory_dynamic_components
WHERE   current_size != 0;SQL> SQL>   2    3

COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool                       771751936  318767104  771751936
large pool                         67108864   67108864  603979776
java pool                          50331648   50331648   50331648
streams pool                       33554432          0   33554432
SGA Target                       2030043136 1862270976 2030043136
DEFAULT buffer cache             1073741824  687865856 1291845632
PGA Target                       1191182336 1191182336 1358954496

7 rows selected.

SQL>  show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3G
memory_target                        big integer 3G
shared_memory_address                integer     0
SQL>  SELECT component, current_size, min_size, max_size, last_oper_type FROM v$memory_dynamic_components;

COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE LAST_OPER_TYP
------------------------------ ------------ ---------- ---------- -------------
shared pool                       771751936  318767104  771751936 GROW
large pool                         67108864   67108864  603979776 SHRINK
java pool                          50331648   50331648   50331648 STATIC
streams pool                       33554432          0   33554432 GROW
SGA Target                       2030043136 1862270976 2030043136 GROW
DEFAULT buffer cache             1073741824  687865856 1291845632 SHRINK
KEEP buffer cache                         0          0          0 STATIC
RECYCLE buffer cache                      0          0          0 STATIC
DEFAULT 2K buffer cache                   0          0          0 STATIC
DEFAULT 4K buffer cache                   0          0          0 STATIC
DEFAULT 8K buffer cache                   0          0          0 STATIC

COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE LAST_OPER_TYP
------------------------------ ------------ ---------- ---------- -------------
DEFAULT 16K buffer cache                  0          0          0 STATIC
DEFAULT 32K buffer cache                  0          0          0 STATIC
Shared IO Pool                            0          0          0 STATIC
PGA Target                       1191182336 1191182336 1358954496 SHRINK
ASM Buffer Cache                          0          0          0 STATIC

16 rows selected.


SQL> COLUMN component FORMAT A30
select * from v$pgastat;SQL>

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
aggregate PGA target parameter                                   1191182336
bytes

aggregate PGA auto target                                         945358848
bytes

global memory bound                                               119111680
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
total PGA inuse                                                   140760064
bytes

total PGA allocated                                               173752320
bytes

maximum PGA allocated                                            1341917184
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
total freeable PGA memory                                          18874368
bytes

process count                                                            40


max processes count                                                      68



NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
PGA memory freed back to OS                                      1.4235E+10
bytes

total PGA used for auto workareas                                         0
bytes

maximum PGA used for auto workareas                               622905344
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
total PGA used for manual workareas                                       0
bytes

maximum PGA used for manual workareas                               2713600
bytes

over allocation count                                                     0



NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
bytes processed                                                  4.8075E+10
bytes

extra bytes read/written                                         1.4650E+10
bytes

cache hit percentage                                                  76.64
percent


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
recompute count (total)                                              483134



19 rows selected.

SQL>



Standard