Oracle Architecture and User management

Before using Oracle, we must have a deep understanding of the architecture of Oracle, unlike the SQL Server architecture we learned before, so it's a whole new thing for us.

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 the files that are physically stored in the database, the characteristics of the data files: Each data file is associated with only one database, one table space can contain one or more data files, and a data file can belong to only one table space. The data file contains user or application data for the database, as well as metadata and data dictionaries.

Redo log files: It records all changes to the data and provides a data recovery mechanism that the instance can use to recover the database if the database server crashes, but no data files are lost.

? 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 an 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 table space is the largest logical unit in the database, and the SYSTEM and Sysaux table spaces are the table spaces that must exist when the database is created, as well as temp,users.
? Each table space 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 is equal to 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 the table space does not have data files, it is 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 spaces, different schema objects are assigned table spaces, which facilitates the management of user actions and schema objects.
? You can create different data files 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]];

2) Paragraph
A segment exists in a table space and is a logical storage structure of a specified type.
Section consists of a set of areas
The paragraph is divided into 4 categories: Data segment, index segment, fallback segment, temporary segment.

3) District
The. Zone is the smallest unit of disk space allocation. Disks are partitioned, allocating at least one zone at a time.
The zone is made up of contiguous data blocks.
The segment consists of one or more zones, which contain at least one area when the segment is created.
The. Zone 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 the Oracle server can read or write to
The value range of the data block is 2K-64KB, and the default value is related to the Oracle version

5) mode
? Mode 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.
When a 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.

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 in the zone that is connected to the Oracle database
? 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 connect 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:

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

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 limits
"SYSDBA" permissions, that is, database administrator rights, permissions include:
? Open the database server
? Shutting down the database server
Backup Database Recovery Database
? Log Archive
? Session limits
? Management features
? 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:
1) Select User name and password
2) identify the table space that the user needs to store the object
3) Determine the limit for each table space
4) allocating default tablespace and temporary table space
5) Create user
6) 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

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 is a system privilege
Object permissions allow users to perform specific actions 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

Oracle Architecture and User management