Oracle notes (1), installation and architecture

I. Installation
Key points during installation: Global Database Name, system identifier (SID, Instance name), server parameter file (SPFILEXXX (Instance name). ORA ).
Listener Configuration: The parameter file (listener. ora, tnsnames. ora) in the server \ NETWORK \ ADMIN folder ).

Ii. Oracle Architecture

1. Instance
An instance is a collection of background processes and memory structures. You must start an instance to access data in the database. One instance can only open and use one database.
When the instance is started, a global system zone (SGA) is allocated and a series of background processes are started. Each background process executes different tasks.

2. Databases
An Oracle Database physically refers to a group of operating system files (data files) and logically refers to the logical relationship after the database is created.

3. Session
A single connection between the user and the Oracle server during a session. It is created when a connection is established and closed when the connection is disconnected.
When a database user creates multiple connections to the server at the same time, multiple parallel sessions are created for the user.

4. Memory
The Oracle memory structure contains the following two memory zones:
System global zone (SGA): This memory zone is allocated when the instance is started.
Program global zone (PGA): This memory zone is allocated when the server process starts.

A. System Global zone (SGA, System Global Area)
Used to store database information and shared by server processes.
It can be divided:

● Shared Pool [operation sharing]
The Shared pool includes the database cache area (Shared SQL & PL/SQL) and data dictionary cache.
A. database cache zone: including the recently executed SQL interpretation version, PL/SQL process, function, and other program unit compilation versions. This makes it easier for users to share calls, save costs, and increase process speed.
B. Data Dictionary cache: storing frequently used data dictionaries (tables, columns, indexes, permissions, and other information) in the memory can reduce performance conflicts caused by SQL statements.

● Data Buffer Cache [Record Sharing]
The main function is to buffer the data blocks that need to be read or accessed, so as to reduce the read and write operations on the disk too frequently. All online users can share the region. When processing a query, the process first checks whether the required data blocks exist in the memory, improving the reading speed.

● Log Buffer (Redo Log Buffer)
This area stores all the information about recorded data operations in this buffer, and at the right time, the LGWR process writes them to the log files on the disk.

● Large shared area (Large Pool)
Optional features of Oracle, mainly used for multithreading.

B. Program Global Area)
PGA is a dedicated memory area allocated to a single user process in the memory of the database server. It is private to the user process and cannot be shared.
It is automatically allocated when a user connects to the database and creates a session, and is automatically released after the session ends.

5. Process
User process: starts when a database user requests to connect to the Oracle server.
Server process: it is enabled when a user establishes a session connection to process one or more user process requests.
Background process: starts an Oracle instance at startup to improve system efficiency and coordinate multiple users.
Data Writing Process (DBWR): writes block buffer data to data files on the disk.
Log write process (LGWR): writes information about the redo log buffer to the redo log file.
System Monitoring (SMON): If the database is shut down abnormally, such as a sudden power failure. Then, when the database is started again, the SMON process performs necessary data repair operations.
Process Monitoring (PMON): cleans up resources when an Oracle process fails and is used to recover Failed User processes.
Checkpoint Process (CKPT): updates the database status information in control files and data files whenever changes in the buffer zone are permanently recorded in the database to ensure Database Synchronization.
Archive process (ARCH): archives the full log groups during each log switch. Archive and non-archive log groups are supported (default ).

6. database logical structure
Databases, tablespaces, segments, partitions, and data blocks)

7. Physical database structure
Data File: used to store all data (*. DBF). Each database has a set of data files, and only one data file can correspond to one database.
LOG File: records all operations performed on the database (*. LOG). It is mainly used for database fault recovery.
Control File: records the control information (*. CTL) of all database files ).

Syntax View:
C: \> sqlplus "sys/test1234 as sysdba"

SQL> DESC v $ controlfile
SQL> SELECT status, name FROM v $ controlfile

SQL> DESC v $ datafile
SQL> SELECT file #, status, name FROM v $ datafile

SQL> DESC v $ logfile
SQL> SELECT member FROM v $ controlfile

Ps. content reference:
Oracle.10G. Database System tutorial. taught by the Chinese Emy of Sciences Training Instructor
Oracle study Note 2, Oracle architecture http://www.lurenjia.net/article.asp? Id = 189
Architecture http://www.it-waibao.com/IToutsourcing/support/database/Oracle/catalogTreeID100_folderID520_infoID10452.html for Oracle 9i Database Server