Oracle Architecture and User management

This blog will be a preliminary introduction to Oracle's architecture, storage structure, memory structure, and process structure, so as to grasp its physical composition, file composition and various processes on a macro level, which can be very useful for further understanding.

First, ORALCE system structure

1. Overview

Oracle's architecture is the composition of the database, the work process, and the organization and management of the data in the database, to understand the architecture of the Oracle database, you must understand the main components of Oracle and important concepts. The Oracle architecture includes a range of components, including instances, user processes, service processes, data files, and other files, such as parameter files, password files, and archive log files. As shown in the following:

Instances and databases are a core part of the Oracle database architecture and are the two most important concepts, and the main task for DBAs is to maintain instances and databases.

1) Example

An instance is a collection of background processes and memory that must be started to access data in the database.

When Oracle starts, a system global Zone (SGA) is assigned and a series of Oracle background processes are started

An instance can only open and use one database

Oracle is divided into single-process and multi-process instances, and is now generally a multi-process instance, multi-process is multi-user, different users use different processes to execute different parts of Oracle.

2) database

A database is a collection of data that is treated as a logical unit.

The Oracle database consists of operating system files that provide the actual physical storage for database information

Oracle databases include logical and physical structures.

Physical Structure: refers to a set of operating system files that store database information, each with a logical structure and a physical structure. A physical structure is a set of operating system files that comprise a database, consisting mainly of 3 types of files: Data files, control files, and redo log files.

Logical Structure: refers to the logical storage structure of database data, including: Table space, data segment, table, view, etc.

Ii. Oracle Storage Architecture

Oracle's storage structure is divided into physical and logical structures, both of which are independent and interlinked.

1. Physical Structure:

The physical structure is the operating system physical file used after the Oracle database was created, divided into the following two types:

1) Main documents

data files: data files are physical storage database files, data file characteristics: Each data file is only associated with a database, a table space can contain one or more data files, a data file can only belong to one table space. The data file contains user or application data for the database, as well as metadata and data dictionaries.

Redo log file: It records all changes to the data and provides a data recovery mechanism that, if the database server crashes but does not lose any data files, the instance can use the information in these files to recover the database.

control File: when the database reads, the data file is located according to the information of the control file. The control file stores the name and location of the data file and the redo log file. A database of at least two control files, oracle11g by default includes 3 control files, each control file content is the same, you can avoid because of a control file corruption caused the database cannot be started. The control file records the following key information: the location and size of the data file, the location and size of the redo log file, the database name and creation time, and the log sequence number. Control files are critical to the database. Without these files, you cannot open the data file to access the data in the database.

2) Other documents

Parameter file: Used to define the configuration at instance startup

Password file: Allow Sysdba, Sysoper, and sysasm to connect remotely to the instance and perform administrative tasks

Archive log files: Using these files and database backups, you can recover lost data files. That is, the archive log can restore the restored data file

2. Oracle Logical Structure

Oracle's logical components include table spaces, segments, extents, blocks, and schemas.

1) Table Space

The tablespace is the largest logical unit in the database, and the SYSTEM and Sysaux tablespace are the table spaces that must exist when the database is created, as well as temp,users.

Each tablespace is made up of one or more data files, and a data file can be associated with only one table space.

The size of the tablespace equals the sum of all the data file sizes that make up the tablespace.

Table space and data files are relative, if there is no data file, table space can not exist independently, if only table space does not have a data file is not good, like if the computer does not have a hard disk, you can no longer logically see the C disk D, where the hard disk is the equivalent of data files, and the C disk equivalent is the table space. We can make multiple data files into a table space, and then store the data in the table space, the table space and data files are corresponding, a table space can be composed of one or more data files, but a data file can only be in one table space.

The role of Table spaces:

For different users to assign different table space, different schema objects are assigned different table spaces, which facilitates the management of user actions and schema objects.

Different data files can be created on different disks, which facilitates managing disk space, improving I/O performance, backing up and recovering data, and more.

System tablespace: Data that holds internal tables and data dictionaries for Oracle systems, such as indicate, column names, user names, and so on.

Sysaux table space: As a secondary tablespace for the system, it is used to store data used by various database tools such as Enterprise Manager, Staspack, Logminer, and so on. Used to store various patterns of object data, such as intelligent Proxy User Dbsnmp, data Mining user ODM and so on.

Users table space: As a table space for users, you can create various objects on this table space, such as creating tables, indexes, and so on.

Temp tablespace: A special table space for temporary data, for example: when sorting is required, the system will temporarily store the sorted data in that tablespace, and once the sorting process is complete, the space occupied by the sort data can be released, called a temporary tablespace.

The above table space we can not create, the system is installed automatically after the creation of, we may also create their own as needed.

The syntax for creating a tablespace is:

CREATE tablespace Tablespacename

datafile ' filename ' [SIZE integer [k| M]]

[Autoextend [off| On]];

Case one: Create an auto-growing tablespace tbs_work

If it cannot be created, the database does not start and is created after the startup command is executed.

If the space is not enough, you can adjust the table space size.

Method One: Change the size of the data file and indicate the location of the data file, using the Resize keyword to perform the resized table space.

Method Two: Add a new data file to the tablespace and automatically save the data file to the new data file when it is full.

Case two: Change the read and write state of the table space, only the read-only, write is read-write.

Delete Table space

Case three, want to see how many data files in the system

Case four, how to view table space

Case five, viewing the correspondence between tablespaces and data files

2) Paragraph

Gencun is a logical storage structure of a specified type in a tablespace.

Segment consists of a set of areas

Segments are divided into 4 categories: Data segment, index segment, fallback segment, temporary segment.

3) District

Zone is the smallest unit of disk space allocation. Disks are partitioned, allocating at least one zone at a time.

The area is made up of contiguous data blocks.

A segment consists of one or more zones, which contains at least one area when a segment is created.

Zones cannot exist across data files, only in one data file.

4) Data block

is the smallest data organizational unit and Management unit in the data

Data in Oracle data is stored in data blocks

The data block is the smallest storage unit that can be read or written by the Oracle server

The value range of the data block is 2K-64KB, and the default value is related to the Oracle version

5) mode

A schema is a collection of database objects. Include: tables, views, indexes, synonyms, sequences, procedures, packages, and so on.

When a user is created, Oracle automatically creates a pattern that is the same as the user name, so the pattern is also known as user mode.

After the user logs on, the default access is the number library object in the same mode as their own name.

Third, Oracle's memory structure

Memory is the first element that affects database performance, and the main content of Oracle memory storage is as follows:

Program code

About connected session information, including all currently active and inactive sessions

Information necessary to run a program, such as a query plan

Information that is communicated and shared between Oracle processes, such as locks

The memory of Oracle database can be divided into system global Area (SGA), program Global Zone (PGA), user Global Zone (UGA) according to the method of memory usage.

1. System global Area (SGA)

Database information is stored in the system global zone, shared by multiple database processes, when the database instance starts the SGA memory automatically allocated, SGA is the database occupies the server memory of the largest area, is also an important index affecting the performance of the database, SGA according to the role of different, divided into the following parts.

2. PGA

The PGA is not part of an instance and contains the data and control information required by a single server process or a single background process.

The PGA is automatically assigned when a user process connects to the database and creates a session

The memory required for each user process that is connected to the Oracle database is retained in the zone

When a user session ends, the PAG will release

3, UGA

User Global Zone (UGA) stores session state for user processes

UGA can be used as a part of the SGA or PGA. The exact location depends on how you connect to Oracle:

If you are connected through a shared server, UGA is included in the SAG

If connected via a proprietary server, the UGA is included in the PGA of the proprietary server

Case one: Verifying how the memory structure works (how is the SELECT statement executed?) ）

When the user executes the statement select * from Scott.emp, the working principle is divided into four steps, as follows:

The first step: compile, compile contains the syntax of the check and semantic check.

Step Two: If the compilation does not have a problem, it enters the shared pool, which is composed of the library cache and the data dictionary cache. The SQL statement will enter the library cache the first time, that is, the library cache will hold the correct SQL statements compiled, just the SQL statement. SQL statements that are not cached in the library cache require hard parsing (that is, the i/0 resource that consumes the device reads from the hard disk). Assuming that the SQL statement is in the library cache, no hard parsing is required and the data dictionary cache is then checked. Permissions and object data and attributes are cached in the data dictionary cache, all of which are dictionary The cache checks whether the user has access to the Scott.emp table, if there is permission to continue down, and if there is no permission, returns a result directly to the user. If a statement has never been executed, the statement is first cached in the library cache in the share pool, and the next time it is executed, the SQL statement is taken directly from the share pool. Storing SQL statements also requires space, share pool space is also limited, in order to prevent the share pool space is not enough, the first-in-one rule that executes after the SQL statement will overwrite the first executed SQL statement to free up space, of course, the larger the share pool the better.

Step three: The data buffer cache now has an HR user access to the Scott.emp table, and the role of the datastore cache is to transfer the block into memory from disk or storage and buffer cache. Summary: Buffer cache Caches the data itself, and the library cache caches the SQL statement itself.

Fourth step: Redo Log buffer< Redo the logging buffer > when the user performs insert,update,delete,create,alter and other operations, the data changes, these changed data are written to the data buffer (buffer Cache), the redo log buffer is written before the changed data is placed in the redo log cache to ensure that Oracle knows which transactions need to be committed and which needs to be recalled when the data is restored.

Information about the share pool can be viewed through Enterprise Manager

If the console does not open, you can execute the command to open:

You can view the shared pool size from the V$sgainfo

Test statement Execution Time:

Sql> Set Timing on

Sql> select * from scott.emp; Execute SQL statement for the first time

Time used: 00:00:00.05

Sql>/Time to execute the previous command again is 00:00:00.00

Time used: 00:00:00.00

The above test found that the cache increased the speed of the query.

IV. Structure of Oracle Processes

Oracle has 5 background processes that must be started or the DB instance cannot start successfully, namely the process monitoring (Pmon) process, the system monitoring (Smon) process, the data write (DBWR) process, the log write (LGWR) process, the checkpoint (CKPT) process.

CKPT process: Ensures that all modified chunks of data in the data buffer are written to the database file mechanism. Ensure synchronization of database log files and data files.

Related commands:

1. Viewing user processes and database processes

ps–elf | GREPORACLEOCL |grep–v grep

2. Guardian Process

Ps–elf |grep OHA

3. View the log process LGWR

Ps–elf |grep Ora_ | grep LGWR

V. Oracle User Management

When creating a new database, Oracle will be familiar with some default database users, such as Sys,system and Scott. SYS and system users are Oracle administrative users, and Scott users are a demonstration user of Oracle database, and the Scott database contains sample tables for test presentation (for learning communication)

1. Default database user

1) SYS

SYS user is a superuser in Oracle, and all data dictionaries and views in the database exist in the SYS pattern, and the data dictionary stores all the information used to manage database objects and is a very important system information in the Oracle database. SYS users are primarily used to maintain system information and manage instances. SYS users can only log on to the system in Sysoper or SYSDBA roles.

2) SYSTEM

The system user is the default database administrator in Oracle, and it has DBA authority. This user mode stores the internal tables and views used by Oracle management tools. Users, permissions, and storage of the database are typically managed through the system user. Creating a user table in system mode is not recommended. System cannot log on to the Sysoper and sysdba roles, only by default.

3) SCOTT

The Scott user is a model user of the Oracle database, which is typically created when the database is installed. The Scott user mode contains 4 sample tables, one of which is an EMP table that uses the users table space to store schema objects.

Typically, for security reasons, different access rights are required for tables of different uses, at which point a different user needs to be created, and the Create user command in Oracle is used for creating users, each with a default tablespace and a temporary table space. If not specified, Oracle sets the users as the default tablespace, and temp as the temp table space.

4) SYSDBA and Sysoper two system permissions differences

Sysdba and Sysoper belong to the system privilege, also known as administrative privilege, and have some administrative level permissions such as database turn-off. The specific permissions of SYSDBA and Sysoper are as follows:

"Sysoper" permissions, which are database operator permissions, include:

Open the database server

Shutting down the database server

Backing Up the database

Recovering a Database

Log Archive

Session Limit

"SYSDBA" permissions, that is, database administrator rights, permissions include:

Open the database server

Shutting down the database server

Back Up Database recovery database

Log Archive

Session Limit

Management functions

Create a database

"Sysdba" has the highest system privileges and is sys after logging in.

"Sysoper" is mainly used to start, close the database, Sysoper login after the user is public.

2. Creating a custom user

Steps:

Select User name and password

Identify the table spaces that users need to store objects

Determine the limit for each table space

Assigning default tablespace and temporary table space

Create user

Granting permissions and roles to users

Grammar:

CREATE User User

Identified by password

[DEFAULT tablespace tablespace]

[Temporary tablespace tablespace]

[QUOTA {integer [k| M] | Unlimited}on tablespace

[QUOTA {integer [k| M] | Unlimited}on tablespace] ...]

[PASSWORD EXPIRE]

Case 1: Create user Ydw

CREATE user Ydw--username YDW

Identified by pwd123--Password is martinpwd

Default Tablespace tbs_work--defaults to tbs_work for table space

Temporary tablespace temp-temp Table space

QUOTA UNLIMITED on Tbs_work--no restrictions on the use of the Tbs_work table space

PASSWORD EXPIRE; --Need to change password for first time login

3, modify YDW user's password is pwd123456

4. Delete User Ydw

5. Querying all users of Oracle

6. Unlocking the user

1) First check the status of the HR user is locked

Vi. Oracle Database Rights Management

Permission refers to the right to execute a specific command or access a database object

There are two types of permissions, system permissions and object permissions

System permissions allow users to perform certain database operations, such as creating a table that is a system privilege

Object permissions allow users to perform specific operations on database objects such as tables, views, sequences, and so on

A role is a combination of related permissions that can be granted to a role and then granted to a user to simplify rights management

1. Using System permissions

System permissions are the rights to perform certain system-level operations in a database, or to perform certain operations on a class of objects, such as the right to create a tablespace in a database, or the right to create a table, which are system permissions, as follows:

The common system permissions are as follows:

CREATE SESSION: Connecting to a Database

CREATE TABLE: Creating tables

CREATE VIEW: Creating views

Create SEQUENCE: Creating a sequence

2. Object permissions

Object permissions are the right to perform actions on a particular pattern object, which can only be set for the schema object, including: tables, views, sequences, stored procedures, and so on.

3. Authorization

Oracle database users have access to two ways

1) grant permissions directly to the user

2) grant permissions to the role, and then grant the role to one or more users

Using roles enables more efficient and convenient management of permissions, so database administrators typically use roles to grant permissions to users rather than directly to users.

The following types of predefined roles are available in Oracle for common systems:

1) Connect: Have connection database permissions

2) RESOURCE: Have permissions to create tables, triggers, procedures, etc.

3) DBA: Database administrator with highest administrative database permissions

The new user must grant a certain permission to perform the related database operation, authorizing the grant statement to cancel the authorization through the REVOKE statement

The syntax for authorization:

1) GRANT CONNECT to MARTIN;

Grant Connet role to Martin

2) GRANT Connect,resource to MARTIN;

At the same time Grant Connet,resource these 2 roles to Martin

3) GRANT SELECT on scott.emp to MARTIN;

Allow user Martin to query scott.emp table records

4) GRANT UPDATE on scott.emp to MARTIN;

Allow user Martin to update records in the Scott.emp table

5) GRANT all on scott.emp to MARTIN;

Allow user Martin to insert, delete, update, and query records in the Scott.emp table

4. The syntax for revoking permissions:

Revoke user-assigned permissions or roles by using the REVOKE command

1) REVOKE Connect,resource from MARTIN;

Undo the 2 roles of Connet,resource

2) REVOKE SELECT from Scott.emp to MARTIN;

Revoke Martin's permission to query the records of a scott.emp table

Case 1: Set permissions for a user

1) First set up user Ydw

2) login with YDW user

Prompt to change password for first logon

3) log back in with the new password and prompt for no permissions.

4) Grant the user the session (connect to database) permission

5) Re-login to Oracle

6) test YDW can create table, prompt insufficient permission

7) Grant users permission to create tables

8) Log in again using YDW, test to create table

The creation was successful.

9) test Insert data and query

10) View User-owned permissions

11) Revoke User's privileges

12) test user ydw can query scott.emp

Prompt does not have permission

13) Grant users the right to query scott.emp

14) test Query Scott.emp

The query was successful.

Oracle Architecture and User management