11 | 12 | 2017
Latest Articles
Popular Articles

Administration

Discovery of physical layout (database files, temp files, control files redo log files )

User Rating:  / 0
PoorBest 

Phyiscal Database Discovery

 

The Oracle Database is not thin air. There is a physical layer. The database is somewhere, let' s take a look where files are located on disk.


With this one I list where the data files are located, how big the actual file is, whether the file is auto extensible and what the maximum file size is about.


SQL> set pagesize 9999
SQL> set linesize 300
SQL> column file_name format a50
SQL> column tablespace_name format a25
SQL> select file_id,file_name,tablespace_name,round(bytes/(1024*1024),2) "ACTUAL SIZE",autoextensible "AUTO",round(maxbytes/(1024*1024),2) "MAX SIZE" from dba_data_files order by tablespace_name;

     FILE_ID FILE_NAME                                TABLESPACE_NAME       ACTUAL SIZE AUT       MAX SIZE
---------------- -------------------------------------------------- ------------------------- ----------------
           5 /opt/oradata/SILVER/user_data_01.dbf           USER_DATA              8192 YES           7168
           2 /opt/oradata/SILVER/sysaux01.dbf               SYSAUX                  700 YES       32767.98
           1 /opt/oradata/SILVER/system01.dbf               SYSTEM                  710 YES       32767.98
           6 /opt/oradata/SILVER/tuning_01.dbf              TS_TUNING               100 NO               0
           7 /opt/oradata/SILVER/ts_user_1.dbf              TS_USER_1               100 NO               0
           8 /opt/oradata/SILVER/ts_user_2.dbf              TS_USER_2               100 NO               0
           3 /opt/oradata/SILVER/undotbs01.dbf              UNDOTBS1               1704 YES           2048
           4 /opt/oradata/SILVER/users01.dbf                USERS                  4086 YES           8192

8 rows selected.

 

Note we can also query v$datafile in order to retrieve similar information.


With this one I list where the temp files are located, how big the actual file is, whether the file is auto extensible and what the maximum file size is about.

 

SQL> select file_id,file_name,tablespace_name,round(bytes/(1024*1024),2) "ACTUAL SIZE",autoextensible "AUTO",round(maxbytes/(1024*1024),2) "MAX SIZE" from dba_temp_files order by tablespace_name;    

FILE_ID      FILE_NAME                              TABLESPACE_NAME          ACTUAL SIZE AUT       MAX SIZE
---------------- -------------------------------------------------- ------------------------- ----------------
           1 /opt/oradata/SILVER/temp01.dbf                    TEMP                  1024 NO          0

 

Note we can also query v$tempfile in order to retrieve similar information.

Every Oracle database has at least 2 redo log groups. Every group has at least 1 member. With this one I list where the redo log files are located.
Note redo log files can be online or standby. Standby redo logs are only to be used for Data Guard purposes.

 

SQL> column member format a30
SQL> select group#,member,type from v$logfile;
     

GROUP#           MEMBER                         TYPE
---------------- ------------------------------ -------
           1     /opt/oradata/SILVER/redo01.log ONLINE
           2     /opt/oradata/SILVER/redo02.log ONLINE
           3     /opt/oradata/SILVER/redo03.log ONLINE

 

Every Oracle Database has at least 1 control file. Preferrable more than 1.
With this one I list where the controlfile are located.

 

SQL> column name format a50
SQL> select * from v$controlfile;

STATUS    NAME                                             IS_       BLOCK_SIZE   FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------------- ----------------
         /opt/oradata/SILVER/control01.ctl                 NO         16384          608
         /opt/flash_recovery_area/SILVER/control02.ctl     NO         16384          608

 

Where the controlfiles of a database are can also be found in the server parameter file also the spfile called.
With this one I list whether a server parameter file is used for the startup of the current database instance and if so where the server parameter file is located.
By default the server parameter file is located in the $ORACLE_HOME/dbs directory on Unix systems or in the %ORACLE_HOME%\database directory on Windows systems.

 

SQL> show parameter spfile

NAME                       TYPE                    VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                     string                  /opt/oracle/product/11.2.0/dbhome_1/dbs/spfileSILVER.ora

 

If we want that the database can be accessed with the SYSDBA or the SYSOPER privilege from a remote destinatiion. Not on the database server itself we cannot use OS authentication but we use password file authentication.
By default the password file is located in the $ORACLE_HOME/dbs directory on Unix systems or in the %ORACLE_HOME%\database directory on Windows systems.
The name of the password file is something like PWD$INSTANCE_NAME

With this one we can query who is able to connect as sysdba or as sysoper

 

SQL> select * from v$pwfile_users;

USERNAME                       SYSDBA SYSOPER  SYSASM
------------------------------ ------ -------  ------
SYS                              TRUE    TRUE   FALSE

 

In order to be able to access the database with password file authentication the instance initialization parameter remote_login_passwordfile must have been set to exclusive. ( which is the default )

 

SQL> show parameter remote_login_passwordfile

NAME                         TYPE          VALUE

remote_login_passwordfile  string      EXCLUSIVE

 

If we think about a database we think about tables, indexes, stored procedures, packages, functions, triggers, sequences.
Tables, indexes and lob segments are at the physical layer segments stored in tablespaces.
A tablespace of which the contents are UNDO or PERMANENT has at least 1 but can span multiple data files.
A tablespace of which the contents are TEMPORARY has at least 1 but can span multiple temp files.

With this one we list the tablespaces as well their content


SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME           CONTENTS
------------------------- ---------
SYSTEM                    PERMANENT
SYSAUX                    PERMANENT
UNDOTBS1                  UNDO
TEMP                      TEMPORARY
USERS                     PERMANENT
USER_DATA                 PERMANENT
TS_TUNING                 PERMANENT
TS_USER_1                 PERMANENT
TS_USER_2                 PERMANENT

9 rows selected.

 

Note we can also query v$tablespace in order to retrieve similar information.


Sometimes we wonder whether a tablespace is almost full, whether we have to add a datafile or not. You may want to use my used space script for this purpose.


Information related to tables can be retrieved from the views DBA_TABLES, ALL_TABLES or USER_TABLES.
Information related to indexes can be retrieved from the views DBA_INDEXES, ALL_INDEXES or USER_INDEXES.
Information related to triggers can be retrieved from the views DBA_TRIGGERS, ALL_TRIGGERS or USER_TRIGGERS.
Information related to sequences can be retrieved from the views DBA_SEQUENCES, ALL_SEQUENCES or USER_SEQUENCES.
Information related to synonyms can be retrieved from the views DBA_SYNONYMS, ALL_SYNONYMS or USER_SYNONYMS.
Information related to pl/sql code can be retrieved from the views DBA_SOURCE, ALL_SOURCE or USER_SOURCE.
Information related to views can be retrieved from the views DBA_VIEWS, ALL_VIEWS or USER_VIEWS

Off course there are other objects like database links, user types, ... The views DBA_OBJECTS, ALL_OBJECTS, USERS_OBJECTS can be queried.