5. Sqlnet
The "sqlnet.ora" file contains client side network configuration parameters located at "$ORACLE_HOME/network/admin" or "$ORACLE_HOME/net80/admin" directory on the client.
This file will also be present on the server if client style connections are used on the server itself.
Here is an example of an "sqlnet.ora" file.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH set to specify the order of the naming methods used for client name resolution lookups
Tnsnames Set to resolve a net service name through the tnsnames.ora file on the clientLdap Set to resolve a database service name, net service name, or net service alias through a directory server.ezconnect or hostname Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service namecds Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.Nis Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.
If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
NONE for no authentication methods. A valid username and password can be used to access the database.
ALL for all authentication methods
NTS for Windows NT native authentication
6. Control file
Control file is a small binary file that records the physical structure of the database. The control file also includes:
-The database name
-Names and locations of associated datafiles and redo log files
-The timestamp of the database creation
-The current log sequence number
-Checkpoint information
The control file must be available for writing by the Oracle Database server whenever the database is open.
It is being read/written in Mount stage.
Without the control file, the database cannot be mounted.
The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. More than one copy of contolfile is recommended
select * from v$controlfile;
7. Data files
Datafiles are physical files stored on disk which consists of User, System, Undo segments.
Data file constitutes the actual database & it holds almost 90% of the actual database size
Database datafiles are only written to by the DBWR processes that we introduced you to earlier.
These database datafiles are associated with Oracle “Tablespaces”, which are “logical” containers for tables and indexes.
To get DB file details:
select * from v$datafile; <Used generally when db is in mount/open stage>
select * from dba_data_files; <Used generally when db is in open stage>
To get free space in dbfiles:
select dt.tablespace_name,round(sum(df.bytes)/1024/1024/1024,0) "Free_GB"
from dba_free_space df,dba_tablespaces dt
where df.tablespace_name=dt.tablespace_name(+)
and df.tablespace_name not in (select tablespace_name from dba_temp_files)
group by dt.tablespace_name
order by 1;
To get allocated space in dbfiles:
select dt.tablespace_name,round(sum(bytes)/1024/1024/1024,0) "GB"
from dba_data_files df,dba_tablespaces dt
where df.tablespace_name=dt.tablespace_name(+)
and df.tablespace_name not in (select tablespace_name from dba_temp_files)
group by dt.tablespace_name
order by 1;
DB files are integral part of database recorded in contolfile, loss of any online db file lead to db crash until it is taken offline or replaced with valid backup followed by recovery
8. Temp file
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.
For example, if you join two large tables, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.
Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
To get temp file details:
select * from v$tempfile; <Used generally when db is in mount/open stage>
select * from dba_temp_files; <Used generally when db is in open stage>
To get free space in Temp tablespace :
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
select TABLESPACE_NAME,round(sum(FREE_SPACE)/1024/1024,0) "FREE_MB" from dba_temp_free_space group by tablespace_name; <11g onwards>
As temp files are not recorded in control file, absence of temp file can be tolerated but SYSTEM tablespace will act as TEMP tablespace
9. Online Redo Log File
Online redo log holds redo entry for every sql statement processed in database written by LGWR sequentially in available redo log file groups
Redo log plays important role in DB/Instance recovery post DB crash to identify the status of the transaction (committed/uncommitted)
Each DB holds minimum two Online redo log file group , one as a current redo log being written & other for archiving
After each log switch a new unique sequential number has been assigned to every redo thread
A log switch is the point at which Oracle ends writing to current online redo log file and begins writing to next available.. Manually log switch can happen using “alter system switch logfile;”
Single instance DB contains single thread with minimum two groups & a member
In RAC, number of thread equivalent to number of instances running in cluster with each thread representing/Used by an instance
Redo log Members helps in Multiplexing the redo log files to safe guard against damage/loss of the disk
Multiple members under a same group/thread hold the duplicate copy of the redo file
Each member of a group can be spanned across multiple disk controllers so that failure of a single disk would not lead to loss of a complete redo group
10. Archive Log
Archive log files are historical image of online redo log file.
When database in ARCHIVELOG Mode, before a redo log file gets overwritten content of that redo log copied to archive log files in the archive destination specified by init parameter log_archive_dest & in the format specified by log_archive_format
Archive log file name formed by sequence#, thread# & reset log id (10g onwards)
Archive log files can be backed using RMAN & required for recovery
Archive log list provide details of the archive mode, current /archived redo sequence & location of the archive destination
Thats It !!!
No comments:
Post a Comment