Oracle Architecture and User management

Oracle's architecture is the composition of the database, the working process, and the organization and management of the data in the database. 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:
Oracle Main components

.
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.
.
Oracle Storage Architecture
Oracle's storage structure is divided into physical and logical structures, both of which are independent and interlinked.

.

Physical Structure: The
Physical structure is the operating system physical file used after the Oracle database is created, divided into the following two types:
1) Main file
? data files: Data files are files that are physically stored in the database, Data file features: Each data file is associated with only one database, a tablespace 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 file: 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) Additional files
parameter file: Used to define the configuration
File: Allow Sysdba, Sysoper, and sysasm to connect to the instance remotely and perform administrative tasks
? Archive log files: Using these files and database backups, The lost data file can be recovered. That is, the archive log can restore the restored data file
.
Oracle Logical Structure The logical components of
Oracle include tablespaces, segments, extents, blocks, schemas, and so on.
. The
1) tablespace
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 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
table space 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 a data file, 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.
.
Table space:
Assigning different tablespaces to different users, assigning different table spaces to different schema objects, and facilitating the management of user actions and schema objects. The
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 tablespace: As a secondary tablespace for system, for storing 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, such as creating tables, indexes, and so on, on this table space.
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 after the sorting process is complete, the space occupied by the sort data can be released, known as 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
.

.

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.
.
Area
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.
.
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
.
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.

.
Case SIX: in XX table space DBA creates a table named Test, the space occupied by this table space, which we call a segment, the command to view the data segment

Show 5,940 Segments

.
Case Seven: View the distribution of the extents and segments of the example table space

.
Case eight: viewing blocks

.
Case nine, view operating system minimum storage unit

.

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.
.

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.

.

.

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
.

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.

.

.

Oracle Process Architecture

.

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:
Viewing user processes and database processes
ps–elf | GREPORACLEOCL |grep–v grep

.
Daemon process
Ps–elf |grep OHA

.
Viewing the log process LGWR

.

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)
.
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.
.
Create 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

.

语法：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

.
Modify the YDW user's password to pwd123456

.

Delete User Ydw

.
Querying all users of Oracle

.
Unlock Users
1) First check the status of the HR user is locked

.

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
.
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
.
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.

.
Authorized
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; 授予授予CONNET角色给MARTIN.2）GRANT CONNECT,RESOURCE TO MARTIN;同时授予CONNET，RESOURCE这2个角色给MARTIN.3）GRANT SELECT ON SCOTT.emp TO MARTIN;允许用户MARTIN查询 SCOTT.emp表的记录.4）GRANT UPDATE ON SCOTT.emp TO MARTIN; 允许用户MARTIN更新 SCOTT.emp表中的记录.5）GRANT ALL ON SCOTT.emp TO MARTIN;允许用户MARTIN插入、删除、更新和查询SCOTT.emp表中的记录

.
Syntax for revoking permissions:

使用REVOKE命令撤销用户分配的权限或角色.1）REVOKE CONNECT,RESOURCE FROM MARTIN; 撤销CONNET，RESOURCE这2个角色.2）REVOKE SELECT FROM SCOTT.emp TO MARTIN;撤销MARTIN查询 SCOTT.emp表的记录的权限

.

Case 1: Set permissions for a user
First set up user Ydw

.
Log in with YDW user

Prompt to change password for first logon
.
Log back in with the new password and prompt for no permissions.

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

.
Re-login to Oracle

.
Test YDW can create a table with insufficient permissions

.
Granting users permission to create tables

.
Log in again using YDW to test the ability to create tables

The creation was successful.
.
Test insert data, and query

.
View User-owned permissions

.
Revoke a user's permissions

.
Test user ydw can query scott.emp

Prompt does not have permission
.
Grant user permission to query scott.emp

.
Test Query Scott.emp

The query was successful.
.

Oracle Architecture and User management