dropdown menu

EXTRA - ORACLE BASICS

Oracle Basics

Oracle Server It is an Oracle instance + an Oracle database
Oracle Instance It consists of memory and process structures to provide access to the database
Oracle Database It consists of data-, control- and redo log files

Oracle has changed the database naming convention starting with Oracle 12.2. Oracle database 18c (year 2018) is the full release of 12.2.0.2. The recommended database product to target would be 19c as it offers a greater duration of support by Oracle to March 2026.

Beginning with release 12.2.0.2, new releases will be annual. The version will be the last two digits of the release year. The release originally
planned as 12.2.0.2 will now be release 18 (for 2018), and the release originally planned as 12.2.0.3 will be release 19. Releases 18 and 19 will be treated as under the umbrella of 12.2 for Lifetime Support purposes.

Instead of Patch Sets, Patch Set Updates, and Database Bundle Patches, the new releases will be maintained with Release Updates (RU) and Release Update Revisions (RUR).

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

Instance overview

 

SGA (System Global Area)
The SGA is an area of memory allocated when an Oracle Instance starts up, it consists of a Shared Pool, Database Buffer Cache, Redo log buffer cache etc. The SGA's size and function are controlled by parameters in init.ora or spfile.

PGA (Program Global Area)
PGA is a reserved memory for each user process that connects to an Oracle database. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

USER PROCESS --> connection established (session)--> SERVER PROCESS --> ORACLE INSTANCE
When a user connects to the Oracle server a user process is created. After the connection is established a server process is started (PGA) which interacts with the Oracle instance during this session. In a dedicated server configuration, one server process is spawned for each connected user process. In a shared server configuration, user processes are distributed among a pre-defined number of server processes.

BACKGROUND PROCESSES:
Database Writer (DBWn): writes dirty blocks (blocks which have been modified) in the DB. buffer cache to data files.
Log Writer (LGWR): writes from the redo log buffer cache to the redo log file (every 3 seconda, after commit...)
Archiver (ARCn): backs up (archives) the filled online redo log files before they can be reused again
System Monitor (SMON): when the DB. is reopened after a failure, SMON does recovery (uses redo log files to update the database files)
Process Monitor (PMON): when a process fails PMON does clean up (rolling back transactions, releasing locks...)

Log Switch
When an online redo log file is filled up, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The archiver process (ARCn) initiates backing up (archiving) the filled log files at every log switch. It automatically archives the online redo log before the log can be reused.

Checkpoint (CKPT)
An event called checkpoint occurs when the DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data, to the data files. At checkpoint the checkpoint number is written into the data file headers and into the control files. Because all the databse changes up to the checkpoint have been recorded in the datafiles, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required.

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

Database overview


The Oracle database architecture includes logical and physical structures that make up the database:
- physical structure: control files, online redo log files, data files
- logical structure: tablespaces, segments, extents, data blocks

Pysical structure:
Control file: during DB creation a control file is created which contains the name of the DB, location of redo log files, timestamp....
Redo log files: they record all changes made to data and provide a recovery mechanism. Usually they are in /oracle/SID directory.
Data Files: Each tablespace consists of one or more files called data files. These are physical files which contain the data in the DB.

Logical structure:
Tablespace: (one or more) datafailes can be grouped logically into tablespaces (like a vg which can have multiple disks)
Table: the information is stored in tables, which consist of rows and columns (like an lv in the vg)



Data Blocks, Extents, Segments:
Oracle data blocks are the smallest units of storage that the Oracle server can allocate. One data block corresponds to one or more operating system blocks allocated  from an existing data file. Above the database blocks are the extents. An extent is a specific number of data blocks that is allocated to store information. When more space is needed it is allocated by extents. (Like we add one or more PPs to an LV)

In a tablespace above extents the data is grouped logically into segments. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. Oracle allocates space for segments in extents. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk. The segments also can span files, but the individual extents cannot. A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace. Each segment is made up of one or more extents.


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

PFILE, SPFILE

Oracle parameters are stored in parameter files (pfile, spfile). During start up the parameter file is used to set up Oracle parameters. PFILE is a text file and if it is modified, the instance must be shut down and restarted in order to make the new values effective. (/oracle/SID/920_64/dbs/initSID.ora) SPFILE is a binary file and it is maintained by the Oracle server. The ALTER SYSTEM COMMAND is used to change the value of instance parameters. An SPFILE is created from an initSID.ora file (PFILE) using the CREATE SPFILE command. (/oracle/SID/102_64/dbs/spfileSID.ora) By default, if you do not specify PFILE in your STARTUP command, Oracle will use server parameter file (SPFILE).

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

Database Start/Stop

Starting an instance includes the following tasks:
-reading the initialization file in the following order: spfileSID.ora, if not found then spfile.ora, initSID.ora
-allocating the SGA
-starting the background processes
-opening the alertSID.log

STARTUP command: STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER] [database] |MOUNT |NOMOUNT]
NOMOUNT creates the SGA and starts up the background processes but does not provide access to the db
MOUNT mounts the database for certain DBA activities but does not provide user access to the database
OPEN enables users to access the database

PFILE=file enables a nondefault parameterfile to be used to configure the instance
FORCE aborts the running instance before performing a normal startup
RESTRICT enables only users with RESTRICTED SESSION privilege to access the database
RECOVER begins media recovery when the database starts


It is also possible to open the database in READ WRITE mode, in READ ONLY mode or in restricted mode (RESTRICTED SESSION)

SHUTDOWN command:
NORMAL          no new connections, wait for user disconnect, db and redo buffers written to  disk, db dismounts
TRANSACTIONAL   no new transaction, transaction ends client disconnect, shutdown immediately (no recovery needed)
IMMEDIATE       no wait to finish, Oracle rolls back, disconnects clients and dismounts db (no recovery needed)
ABORT           no wait to finish, no rollback.....instance recovery neeeded, which occurs automatically

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

RMAN

The acronym RMAN stands for Oracle's Recovery Manager, with an emphasis on the word Recovery. Backups are worthless if you can't use them to restore lost data! RMAN is Oracle's recommended standard for database backups for any sized organization. The RMAN utility is an executable file, and its task can be automated by scripts using its command-line version.

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

Relinking Executables

You can relink the product executables manually by using the relink shell script located in the $ORACLE_HOME/bin directory. You must relink the product executables
every time you apply an operating system patch or after an operating system upgrade. Before relinking executables, you must shut down all executables that run in the Oracle home directory that you are relinking. In addition, shut down applications linked with Oracle shared libraries. The relink script does not take any arguments.
Depending on the products that have been installed in the Oracle home directory, the relink script relinks all Oracle product executables.

To relink product executables, run the following command:
$ relink

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

oraenv and coraenv

The oraenv and coraenv scripts are created during installation. These scripts set environment variables based on the contents of the oratab file and provide a central means of updating all user accounts with database changes and a mechanism for switching between databases specified in the oratab file.

The oraenv or coraenv script is usually called from the user’s shell startup file (for example, .profile or.login). It sets the ORACLE_SID and ORACLE_HOME environment variables and includes the $ORACLE_HOME/bin directory in the PATH environment variable setting. When switching between databases, users can run the oraenv or coraenv script to set these environment variables.

coraenv script: % source /usr/local/bin/coraenv
oraenv script: $ . /usr/local/bin/oraenv

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

Listener

There is a special process called listener, whose responsibility is to listen for incoming connection requests.
There are 3 operating system configuration files under $ORACLE_HOME/network/admin:
listener.ora configures the listener
tnsnames.ora contains a list of service names
sqlnet.ora conatins client side informations (i.e: client domain...)

lsnrctl with this can start/stop the listener

aix11:orap44 2> lsnrctl
...
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               spawn
change_password     quit                exit

Oracle recommends that you reserve a port for the listener in the /etc/services file of each Oracle Net Services node on the network. The default port is 1521. The entry lists the listener name and the port number. For example: oraclelistener 1521/tcp

In this example, oraclelistener is the name of the listener as defined in the listener.ora file. Reserve multiple ports if you intend to start multiple listeners.
If you intend to use Secure Sockets Layer, then you should define a port for TCP/IP with Secure Sockets Layer in the /etc/services file. Oracle recommends a value of
2484. For example: oraclelistenerssl 2484/tcps

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

No comments: