Database Versus Instance
The database is the data on disk, stored in operating system files (or possibly, under Unix, in raw files). The instance is composed of the System Global Area (SGA) memory and the background processes. The instance is STARTed using Oracle Server Manager or Oracle Enterprise Manager (OEM). The database is then MOUNTed on the instance and is finally OPENed. The users CONNECT to the instance in order to access the data in the database. Figure 1.1 shows the basic components of the Oracle database and instance.
The Oracle database and the Oracle instance
The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system
ORACLE_SID is instance. An instance is a background processes that processes or operates a database.
check $ORACLE_HOME/dbs/init.ora for instance_name parameter.
Check /etc/oratab file (or depending on Oracle version /var/opt/oracle/oratab) for a list of instances on the server.
Set the ORACLE_SID environment variable examples:
export ORACLE_SID=orcl
ORACLE_SID=TSH2; export ORACLE_SID
System Global Area (SGA)
The System Global Area (SGA) is the shared memory area used by the instance to store information that must be shared between the database and user processes. The main components of the SGA include the database buffer cache, shared pool, and redo log buffers. The database buffer cache contains actual copies of data blocks from the database. When you update a table, the information is first modified in the data buffer and is later written to disk. The shared pool contains the SQL and PL/SQL statements being executed; every SQL statement executed is stored in the shared pool, which allows Oracle to reuse all the information it has generated about the statement. This reuse produces significant improvements in processing speeds for users in an online transaction processing (OLTP) environment who are executing the same SQL statements. The redo log buffer collects the redo entries for the online redo logs prior to their being flushed to disk.
https://www.bnl.gov/phobos/Detectors/Computing/Orant/doc/network.804/a48385/apa.htm
TNSNAMES.ORA
This file contains a list of the service names and addresses of network destinations. A client (or a server that is part of a distributed database) needs this file to tell it where it can make connections.
Note: This file is not necessary if Oracle Names is used.
Note: This file should be generated and modified by Oracle Network Manager. Do not edit it manually.
SQLNET.ORA
This file contains optional diagnostic parameters, client information about Oracle Names, and may contain other optional parameters such as native naming or security (encryption and checksumming) parameters.
Note: SQLNET.ORA may contain node-specific parameters. Unless you are using Oracle Names and the Dynamic Discovery Option, you should create it with Network Manager. You may edit the SQLNET.ORA file for an individual client by using the SQLNET.ORA Editor, which is described in the Oracle Network Products Troubleshooting Guide.
Server Configuration Files
each server machine needs a LISTENER.ORA file to identify and control the behavior of the listeners that listen for the databases on the machine.
LISTENER.ORA
This file includes service names and addresses of all listeners on a machine, the system identifiers (SIDs) of the databases they listen for, and various control parameters used by the Listener Control Utility.
Note:Unless you are using Oracle Names and the Dynamic Discovery Option, this file should be generated and modified by the Oracle Network Manager. You should not edit it manually.
Note: LISTENER.ORA and TNSNAMES.ORA contain some similar information. The address of the server in TNSNAMES.ORA is the same as the address of the listener for a server in LISTENER.ORA. Similarly, the address in the TNSNAMES.ORA file includes the SID which is required (as SID_NAME) in the LISTENER.ORA file. Figure A - 1 shows the similarities between these files for a single server.
Figure A - 1. Similarities Between TNSNAMES.ORA and LISTENER.ORA
An example of tnsnames.ora:
names.default_domain = world
names.preferred_servers =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom)
(PROTOCOL = TCP)
(Host = vance.world)
(Port = 1522)
)
)
Connect to an oracle database as sys:
sqlplus sys/passwd@<YOUR_SERVICE_NAME> as sysdba
or
sqlplus sys/passwd@host1:PORT/<DATABASE_SERVICE_NAME>
No Comments