How to Find Out the size of your Oracle Database

As an Oracle DBA this is the most important interview question and at any time you may face an issue where you need to find out the size of the Oracle Database. In detail, here you will get to know the actual size of the database that comprises of only data files when no redo and temp are generated. In second case, you may asked to find out the overall database size that contains all the data files, temp files and the redo logs (free+used space). Third, is the size occupied by data in this database or you can say  Database usage details which you can get using dba_segments. 

To Find the Actual size of the Database in GB

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

To Find the Size Occupied by Data in the Database 

Gives the size occupied by data in this database or Database usage details.

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

To Find the Overall/Total Database Size

Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.  

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Read more ...

Schema and Non-Schema Objects in Oracle Database

Oracle database contains schema objects like views, tables, triggers etc., and several other types of objects which are also stored in the database but are not contained in a schema.

A schema is a collection of logical structures of data, or schema objects which is owned by a database user and has the same name as that of the user. Schema objects can be created and manipulated with SQL and include the following types of objects:

Types of Schema Objects

Schema objects are logical data storage structures which do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle Database stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles.

• Tables and index-organized tables
• Constraints
• Views
• Database links
• Database triggers
• Dimensions
• External procedure librarie
• Indexes and indextypes
• Java classes, Java resources, and Java sources
• Materialized views and materialized view logs
• Object tables, object types, and object views
• Operators
• Sequences
• Stored functions, procedures, and packages
• Synonym
• Tables and index-organized tables
• Clusters 

Types of NON-SCHEMA Objects

There are several other types of objects which are also stored in the database but are not contained in a schema are:

• Contexts
• Directories
• Parameter files (PFILEs) and server parameter files (SPFILEs)
• Profile
• Roles
• Rollback segments
• Tablespaces
• User

 For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle Database allocates for the object within the tablespace's datafiles.

Read more ...

How To Find DBID in NOMOUNT State

Oracle Database identifier in short DBID is an internal, unique identifier for an Oracle database. Database administrator must note down the DBID in safe place, so that any miss-happening to the database could be easily identified and recovered. In case it is required to recover SPFILE or control file from autobackup, such as disaster recovery, you will need to set DBID. So lets see how to get DBID in NOMOUNT State.

Why DBID is important? 

• It is an unique identifier for a database.
• In case of backup and recovery RMAN distinguishes databases by DBID.
• When DBID of a database is changed, all previous backups and archived logs of the database become unusable.
• After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their log sequence to 1 
• You should make a backup of the whole database immediately after changing the DBID.

Let's take an example of getting it in nomount state:

First shut down the database using shut immediate command

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now startup database in nomount state

SQL> startup nomount
ORACLE instance started.
Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             402657268 bytes
Database Buffers          197132288 bytes
Redo Buffers                5640192 bytes

You can also set tracefile identifier for easily identification of tracefile.

SQL> alter session set tracefile_identifier=orahow;
Session altered.

Now, dump first ten block of datafile, because each block header contains dbid information.

SQL> alter system dump datafile 'D:\app\SantoshTiwari\oradata\TEST11\USERS01.DBF'
  2  block min 1 block max 10;
System altered.

Now find the location of Trace file.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\santoshtiwari\diag\rdbm
                                                 s\test11\test11\trace

Now search for Db ID inside the trace file. In Linux you can use cat command with grep to find it:

cat filename | grep Db id

Here you can see the dump here:

Start dump data block from file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\USERS01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3561501508=0xd4483344, Db Name='TEST11'
Activation ID=0=0x0
Control Seq=3522=0xdc2, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA


In simple you can also get it using v$database:

SQL> select name, dbid from v$database;

NAME            DBID

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

TEST11    3561501508

 DBID is also displayed by the RMAN client when it starts up and connects to your database. Typical output follows:

SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 6 19:59:06 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

Read more ...

How To Check Alert Log File in Oracle 11g

Each database has an alert log file, which contains a chronological(sequential order in which they occurred) log of database messages and errors. Oracle will automatically create a new alert log file whenever the old one is deleted.

The alert log includes the following errors and log messages:

• All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)
• Administrative operations such as DDL statements and the SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
• Several messages and errors relating to the functions of shared server and dispatcher processes
• Errors during the automatic refresh of a materialized view

How to Find the Location of alert log file in Oracle 11g

SQL> select name from v$database;

NAME
---------
TEST11

alert.log file is written to the directory specified by the background_dump_dest parameter. To view alert log, first you need to find it's location using show parameter command.

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace

You can also get the same result using this sql:

SQL> show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace

 Copy the above value to find the exact name and location of alert log using ls command:
                                              

[oracle@orahow ~]$ ls -ltr /u01/orahow/diag/rdbms/test11/TEST11/trace/al*
-rw-r----- 1 oracle dba 1901154 Nov 24 04:00 //u01/orahow/diag/rdbms/test11/TEST11/trace/alert_TEST11.log

To view alert log you can use tail command with options and filename.

[oracle@orahow ~]$ tail -20f /u01/orahow/diag/rdbms/test11/TEST11/trace/ alert_TEST11.log

In the following output you will see the last 20 lines of the alert log, but if you want to see more number of lines (ex: 100 line) you can use 100f like that.

Read more ...

How to Make a File Write Protected in Linux

In Linux there is an additional file attribute which prevent files and folders from being deleted accidentally. Flag which make a file write/delete protected even from root is called immutable flag. Linux administrator must set this flag for various configuration files on the production servers so that no one could delete/temper with these files.

When you want to delete file/folder having immutable flag set, you will encounter with an error:
rm: cannot remove 'file-name': Operation not permitted.

Making file write protected in Linux

To set this flag we use plus (+) sign with chattr command and to unset this flag we use minus (-) sign.

Syntax:

To set this flag on files you can use the following command:

chattr +i filename

To unset or remove the attribute you can use the following command:

 chattr -i filename 

To set this flag on directories only you need to specify the directory name:

 chattr +i directory name 

chattr -i directory name 

How to search files having immutable flag set?
To accomplish this we use lsattr command pipe with the grep command.

lsattr -R | grep +i

Example:

Creating file having name san.txt and setting flag on it.
[oracle@orahow ~]$ vi san.txt

Initially check the file permission:
[oracle@orahow ~]$ ls -l san.txt
-rw-r--r-- 1 oracle dba 19 Nov 18 05:56 san.txt

 [oracle@orahow ~]$ chattr +i san.txt

chattr: Operation not permitted while setting flags on san.txt


To set this flag you need to login as a root user:

[root@orahow oracle]# chattr +i san.txt

[root@orahow oracle]# lsattr san.txt
----i-------- san.txt 

Now try to delete the file having immutable flag set:

[root@orahow oracle]# rm san.txt

rm: remove write-protected regular file `san.txt'? y

rm: cannot remove `san.txt': Operation not permitted

To remove this file you need to unset this flag:

[root@orahow oracle]# chattr -i san.txt

[root@orahow oracle]# lsattr san.txt

------------- san.txt

[root@orahow oracle]# rm san.txt
rm: remove regular file `san.txt'? y
[root@orahow oracle]# cat san.txt
cat: san.txt: No such file or directory

Making a directory write protected in Linux:

[root@orahow oracle]# mkdir san
[root@orahow oracle]# ls -ldr san

drwxr-xr-x 2 root root 4096 Nov 18 07:36 san

[root@orahow oracle]# chattr +i san

Now if you try to remove the directory it will throw a message: operation not permitted. Before deleting this folder you need to unset this flag.

[root@orahow oracle]# rmdir san
rmdir: san: Operation not permitted

[root@orahow oracle]# chattr -i san

[root@orahow oracle]# rmdir san

[root@orahow oracle]# ls -ldr san

ls: san: No such file or directory

Read more ...