Organize various objects, concepts and relationships of Oracle (one article reading)

Time:2020-1-22

 

The following is my understanding of some Oracle database related concept objects that I collated (copied). If there is any omission, please correct it. As for the purpose of sorting out this article: the main thing is that the content on the Internet is too scattered, so sorting it out can deepen understanding and facilitate subsequent reference. As far as I understand, the following should be applicable to 10g, 11g and 12C. The updated version is not used.

Oracle database is a relational database management system. In many cases, we will make the system that carries our core data as a database server in general, but in a strict sense, Oracle database is composed of two parts:

Among them:

Database refers to some physical files that users store data,Database = physical structure + logical structure

An instance is a set of processes and memory structures that are initialized when the database is started,Instance = background process + memory

That’s why we usually sayShut down and start instances, load and unload databasesThat’s why. From the concept of instance and database, we can know that the instance is temporary. It is just a set of logically divided memory structure and process structure. It will disappear with the closing of the database. In fact, the database is a pile of physical files, which is permanent (unless the disk is damaged). Database and instance are usually one-to-one, which we call single instance architecture; of course, there are some complex distributed structures. A database can be used for multiple instances, such as Oracle’s RAC (please Google or Baidu), that is, cluster mode.

The following figure shows a more specific structure of an Oracle database service:

When the Oracle database is started on the computer server, it is said that an Oracle instance is started on the server. Oracle instance is a software mechanism to access and control database. It includes two parts: system global area (SGA) and Oracle process. SGA is a set of shared memory buffer allocated by the system for the instance, which is used to store the database instance and control information, so as to realize the management and operation of the data in the database.
Process is a very important concept in operating system. A process performs a set of operations to complete a specific task. For Oracle database management system, process is composed of user process, server process and background process.
When a user runs an application, the system establishes a user process for it. The server process processes the requests of the connected user process, which communicates with the user process and serves the Oracle requests of the connected user process.
In order to improve the system performance and realize multi-user function, Oracle also starts some background processes in the system background for database data operation.
The following details the related concepts of Oracle Database
 
1、 Oracle database instance
This part mainly refers to (plagiarism):
https://www.cnblogs.com/chengxiao/p/5904783.html
https://blog.csdn.net/huay_li/article/details/80545275
https://blog.csdn.net/oChangWen/article/details/51171992
 
1. Interaction process between users and Oracle Database
The following figure describes the general interaction process of the single instance architecture
(1) user and user process interaction
User process can be general client software, such as SQL plus, SQL developer of Oracle, or some drivers, which belong to user process.
(2) User process and server process interaction
The server process is sometimes called the foreground process. Of course, compared with the background process (database writer, log writer, etc. mentioned later), the main function of the server process is to process the request of the user process connected to the current instance, execute the SQL sent by the client and return the execution result. In the proprietary server structure, the user process and the server process are one-to-one, that is, when the listener listens to a request from the client, it will assign a corresponding server process to it. Another structure is the shared server. This structure is not a user process corresponding to a server process. It will be coordinated through the scheduler. As for the connection of the shared server, this paper will not describe it here.
(3) Server process and instance process interaction
(4) Instance and database process interaction
 
The above describes the general interaction process of database connection operation. Next, let’s take a look at the instance memory structure of Oracle
 
2. Instance memory structure
(because the database writer and log writer are very related and important to the memory structure, the memory structure and the two process structures will be described in coordination.)
The memory structure of Oracle instance consists of two parts: SGA (system global area) and PGA (user global area). SGA is a shared memory area and the largest memory area. It contains the data and control information of the instance. SGA is automatically allocated when the instance is started and retracted when the instance is closed. All data operations of the database should be performed through SGA; PGA is the memory area specific to the user session, and each session has a dedicated memory area on the server side, which is PGA. This paper mainly analyzes and describes SGA. The composition of SGA is as follows:
 
(1) Database buffer cache & database writer
Buffer cache is the working area used by Oracle to execute SQL. When updating data, the user session will not directly update the data on the disk. If this is allowed, the impact of frequent disk IO on system performance will be devastating. So,Actual processing flowThat’s true:
For a simple query statement – select ename, salary from EMP where name = ‘no two’; when the user submits the SQL statement, the SQL statement will be sent to the server by the corresponding user process (such as our common SQL developer). The listener will listen to the request, and Oracle will establish a corresponding server process for it, and then the server process will First, scan the buffer to see if there are any data blocks that contain key rows (“no two”). If there are any, it will be counted as a cache hit. Then the relevant rows will be transferred to PGA for further processing, and finally displayed to the user after formatting. If there are no hits, the server process will first copy the corresponding rows into the buffer, and then return them to the client.
DML (insert, update, delete) operation is the same. Add the user to send an update statement, and the service process still scans the buffer first. If the cache hits, the data will be updated directly and dirty. If the cache hits, the server process will copy the corresponding data block from the disk to the buffer first, and then update.
1) Dirty buffer
If the blocks stored in the buffer are inconsistent with those on the disk, the buffer is called “dirty buffer”, and the dirty buffer will eventually be written to the disk by the database writer (DBWn).
2) Database writer (DBWn)
Database writer is a background process of Oracle. The so-called background process is relative to the foreground process (server process). The “n” of DBWn means that an instance can have multiple database writers.
Role: in short, DBWn’s role is to write the dirty buffer from the database buffer cache to the data file on disk.
The memory area of database buffer cache and the database writer are relatively important concepts. Other database products like MySQL also have corresponding implementations, but the names are different. When you understand this, you should always be aware of itThe session does not update the disk data directly, session update, insert, delete, including query are all applied to the buffer first, and then DBWn will dump the dirty buffer to disk.  
3) Trigger conditions for DBWn to execute writing:
  DBWn is a relatively lazy process, which will write as little as possible, it performs writes in the following four cases:
a. A service process does not find a free block within the set time (there is no buffer available, so it has to write)
b. Too many dirty buffers
c. 3 seconds timeout (wake up automatically every 3 seconds)
d. When a checkpoint is encountered, that is, a checkpoint. The checkpoint is an Oracle event. When a checkpoint is encountered, DBWn will write. For example, when an instance is closed in an orderly manner, there will be checkpoints, and DBWn will write all the dirty buffers to the disk. It is easy to understand that the consistency of data files should be maintained.
4) Note:
From the above several write opportunities of DBWn, we can realize that the write of DBWn is not directly dependent on the update operation of the session. It does not write whenever there is a dirty buffer. And,There is no relationship between DBWn writing and commitDo not assume that the impact of the commit operation will flow into the disk in real time.
DBWn uses the lazy algorithm to write, the reason should be clear: frequent disk IO has a great pressure on the system. If DBWn writes to the disk actively, it will have a great impact on the system performance. To put it another way, if DBWn writes to the disk diligently, then the significance of the existence of data base buffer is not great.
Of course, when we talk about this, we may realize that DBWn is so lazy to dump data. If at a certain time, there are a large number of dirty buffers in the database buffer cache (in the production environment, this is normal), that is, a large number of uncommitted and committed data are still in memory, not persistent to disk, and then suddenly the system is powered off, In this case, is the data lost? Of course, the data will not be lost, which leads to the concept of redo log. Next, we will talk about the memory structure and background process of the corresponding redo log.
 
(2) Log buffer & log writer
When we perform some DML operations (insert, update, delete), the data block changes, and the generated change vector will be written to the redo log file. With these records, when the system suddenly goes down due to power failure and other factors, a large amount of dirty data in the database buffer cache has not yet been written to the data file. When the database is restarted, there will be an instance recovery process. In this process, redo the log records to keep the data consistent; or the database encounters physical damage, such as disk damage At this time, data can be recovered through Oracle’s backup recovery tool (such as RMAN). The principle is: extract the change record in the backup set application redo log file.
1) Log buffer
Log buffer is a relatively small memory area. It is a change vector used for short-term storage, which is to be written to the redo log file of the disk.
The significance of log buffer is still to reduce disk IO and user waiting time. Imagine how bad the experience would be if every user DML operation had to wait for redo records to be written to disk.
2) Log writer (LGWR)
Function: the log writer (LGWR) writes the contents of the log buffer to the redo log file on the online disk, freeing the log user buffer space. Compared with the database writer (DBWn), the log writer is much more diligent.
3) Trigger conditions for LGWR to write:
a. Write on commit
As mentioned earlier, DBWn writing has nothing to do with commit. If there is no record in the database during commit, the data is really lost. Oracle’s redo log exists to ensure data security. When users commit (called fast commit in Oracle): write the record in redo log buffer into the log file, and write a committed record Record), the session will be suspended first, waiting for LGWR to write these records to the redo log file on the disk, and then the user will be notified that the submission is complete. So,LGWR performs writes on commit to ensure that transactions are never lost.
b. 3 seconds timeout (wake up automatically every 3 seconds)
c. The occupancy rate of the log buffer is 1 / 3, or the number of logs exceeds 1m.
d. Dbwr process trigger: before DBWn writes the dirty buffer to the disk, check whether its related redo records are written to the online log file. If not, notify the LGWR process. Known as write ahead in Oracle, redo records are written to disk before data records. This write is for data rollback. It is possible for DBWn to write uncommitted transactions (refer to the above mentioned write timing). How to ensure transaction rollback? (Note: the full name of dbwr is database writer process, which belongs to one of Oracle background processes. In some places, it is also called DBWn. I think this is because of the number of dbwr processes. Dbwr processes can have up to 20 in an Oracle server. This article aggregates many articles. If you encounter dbwr and DBWn, please do the same noun processing.)
First of all, we need to know that in addition to the actual data, DBWn will also write the undo data (refer to Oracle flashback Technology). In short, transaction rollback needs to undo the data. Before writing the undo data, it will first write the log record (a bit around) for the undo data,If the user wants to roll back the transaction, he can apply these logs to construct the undo data, and then roll back.

 

We summarize the two most important memory areas and corresponding background processes:
Both database buffer buffer and log buffer are designed to improve performance and avoid frequent io. Compared with the database buffer cache, the log buffer is much smaller and cannot be automatically managed. To modify the log buffer, you need to restart the instance, and the database buffer cache can be automatically managed. In order to avoid frequent disk IO leading to system performance degradation, DBWn processes that act on database buffer cache will write as little as possible, and DBWn writes have nothing to do with commit operations;
The LGWR process, which acts on the log buffer, writes very actively. Generally, it dumps the redo log records to disk almost in real time. LGWR is one of the biggest bottlenecks in Oracle architecture. DML cannot be faster than LGWR can write change vectors to disk.
 
(3) Shared pool
The shared pool contains a shared SQL area and a data dictionary store. The shared SQL area contains the information used to execute a specific SQL statement. The data dictionary area is used to store the data dictionary, which is shared by all user processes.
It is the most complex SGA structure. It has many substructures. Let’s take a look at several common shared pool components:
1) Library cache: the memory area of library cache will cache the recently executed code according to the analyzed format. In this way, when the same SQL code is executed multiple times, code analysis will not be repeated, which can greatly improve the system performance.
2) Data dictionary cache: store the object definitions (table, view, synonym, index and other database objects) in Oracle, so that when analyzing SQL code, you don’t need to frequently read the data in the data dictionary on disk
3) PL / SQL area: cache stored procedures, functions, triggers and other database objects. These objects are stored in the data dictionary. By caching them in memory, performance can be improved when repeated calls.
 
(4) Dachi
Large pool is an optional memory area. As mentioned earlier, private server connection and shared server connection are used. If the database adopts the shared server connection mode, large pool is used; RMAN (Oracle’s advanced backup and recovery tool) also needs large pool for data backup.
 
(5) Java pool
Many options of Oracle are written in Java, and the Java pool is used as the heap space for instantiating Java objects
 
(6) Flow pool
The process of extracting change record from redo log and the process of applying change record will use flow pool (for example, if the instance is shut down abnormally due to power failure, Oracle will automatically perform the instance recovery process when it is restarted. In this process, two actions, redo log and application redo log, need to be extracted)
 
The common memory structures of Oracle are listed above. It should be noted that the memory areas listed above can be adjusted dynamically or managed automatically except that the log buffer is fixed and cannot be adjusted dynamically.
 
3. Instance process structure
There are three kinds of processes in Oracle: user process, service process and background process. The background process starts with the instance. They are mainly to maintain the stability of the database, which is equivalent to the managers and internal service personnel in an enterprise. They do not directly provide services to users.
The following is about Oracle’s more common background processes, not all of them. (DBWn and LGWR are more important, as we have learned before, we will not expand them here.) among them, DBWn and ARCN can set the number of databases when they are started. These processes can be more than one in the system.
(1) DBWn database write process: the governance process of database buffer. (refer to the description in “database buffer cache & database writer” above)
Under its governance, there are always a certain number of free buffers in the database buffer to ensure that the user process can always find free buffers for its use.
 
(2) LGWR log file writing process: (please refer to the description in “log buffer & log writer” above)
It is the governance process of the log buffer, which is responsible for writing the log entries in the log buffer to the log files in the disk. There is only one LGWR process per instance.
 
(3) SMON (system monitor): install and open the database, responsible for automatic instance recovery and recovery of sort tablespace.
Effect:
a. Responsible for instance recovery, roll forward to the instance closed state, and redo with the log process after the last checkpoint. This includes committed and uncommitted transactions. Open the database and roll back: roll back uncommitted transactions. (Oracle promises that the data will not be lost after commit. Now we can roughly understand how to achieve this commitment and the choice of balance between data security and database performance. )
b. Clean up temporary sections to free up space
Trigger condition: wake up regularly or actively by other transactions.
 
(4) PMON (process monitor): process monitor, which mainly monitors server processes. As mentioned earlier, in the proprietary server system mode, the user process and the server process are one-to-one. If a session is abnormal, PMON will destroy the corresponding server process, roll back uncommitted transactions, and reclaim the session proprietary PGA memory area.
Effect:
a. User process was found terminated abnormally and cleaned up. Release occupied resources. (cleans up locks that terminate users’ use abnormally)
b. Register an instance dynamically with the listener.
Trigger condition: wake up regularly, and other processes will also wake it up actively.
 
(5) CKPT (checkpoint process): CKPT is responsible for initiating checkpoint signals and manually setting the syntax of checkpoint:
    alter system checkpoint; 
Checkpoint can force DBWn to write to the dirty buffer. When the database crashes, because a large number of dirty buffers are not written to the data file, the instance recovery needs to be performed by SMON when it is restarted. The instance recovery needs to extract and apply the redo log record. The extraction location starts from the location initiated by the last checkpoint (the data before checkpoint has been forced to write to the data file The location is called RBA (redo byte address). CKPT will continuously update the location to the control file (to determine where the instance recovery needs to start to extract the log records).
Trigger condition: log switch triggers checkpoint.
 
(6) Mmon (manageability monitor) – the main process of AWR
Database self monitoring and self-tuning support process. During the operation of an instance, a large number of statistical data about the activity and performance of the instance will be collected, which will be collected into the SGA. Mmon regularly captures these statistical data from the SGA and writes them into the data dictionary for subsequent analysis of these snapshots. (by default, mmon collects snapshots every hour)
Effect:
a. Collect statistics necessary for AWR and write them to disk. 10g is saved in the sysaux tablespace.
b. Generate server — generated alarm
Trigger condition: write the statistics in the shared pool to disk every hour, or the share pool takes up more than 15%.
 
  (7)ARCn(archiver process)
Archive process. This process is optional. If the database is configured as archive mode, this process is required. Archiving is to keep the redo log file permanently(The production library is generally configured as an archive mode)To the archive log file. The archive log file has the same function as the redo log file, except that the redo log file will be rewritten continuously, while the archive log file keeps a complete history of data changes.
Function: in case of log switching, copy the full online log file to the archive directory.
Trigger condition: wake up by LGWR during log switching.
Setting: log? Archive? Max? Processes can set the number of arcns when Oracle starts.
 
(8) Manageability monitor light (mmnl): lightweight mmon
(9) Memory manager (MMAN): automatic content management
Function: check AWR performance information every minute, and determine the optimal distribution of SGA components based on the information.
Settings: Statistics? Level: Statistics level
SGA? Target: total size of SGA
(10) Job queue Coordinator (cjq0): database timing task
(11) Recover writer (rvwr): provides logging for Flashback Database. Write the front image of the data block to the log.
(12) Change starting writer (ctwr): it is used to track the change of data block, and record the address of data block to the change [tracking file]. The incremental backup of RMAN will use this file to determine which data blocks have changed and back up.
(13) Lckn blocking process: used for parallel server system, mainly for blocking between instances
 
4. summary:
At this point, we have a general understanding of the basic memory structure and process structure of Oracle. To see the completed process and memory interaction, we can connect the entire interaction process in series according to the previous understanding.
 
 
2、 Oracle database storage structure
This part mainly refers to (plagiarism):
https://blogs.oracle.com/database4cn/oracle-v6
https://www.cnblogs.com/chengxiao/p/5904783.html
 
According to the above description, Oracle database service consists of instance + database. As an example, let’s start with database objects (storage structure – logical structure and physical structure)
The storage structure of Oracle database can be divided into logical storage structure and physical storage structure.
Logical storage structure: internal to OracleOrganize and manage dataThe way.
Physical storage structure: the way of organizing and managing data outside Oracle (operating system) – realStore dataPlace.
 
1. Logical storage structure:
Oracle logically divides the saved data into small units for storage and maintenance. The higher level logical storage structure is composed of these basic small units.
Logical structure type by sizeFrom small to largeIt can be divided into:Block > extent > segment > tablespace
The figure is as follows:
We can use the following metaphor to describe the logical structure relationship:
  Block: a piece of white paper area: a book composed of white paper section: multiple books are put into a file bag table space: file cabinet (to store multiple file bags)
Here is a detailed introduction to each concept:
(1) Block:
  Oracle usedThe smallest unit of management storageIt is also the smallest logical storage structure. When Oracle databases are input and output, they are read and write in blocks. The size of the block is determined by the parameterDB_BLOCK_SIZEThe default value of DB block size is different for different Oracle versions. We suggestBlock size is an integral multiple of the operating system block size (1, 2, 4, etc.)。 In addition,The block size is determined when the database is created, and cannot be modified later。 Of course, Oracle alsoSupport different tablespaces with different block sizes。 Next, we will introduce the structure of the block.
1) Block header information area:
Block header: contains the general attribute information of the block, such as the physical address of the block and the type of segment to which the block belongs
Table directory: if the data stored in a block is table data, information about the tables contained in the block is saved in the table directory
Line directory: information about line records, such as ROWID
2) Storage area of block: mainly including free space and used space. Oracle mainly manages this part of space through the following two parameters
  • Pctfree parameter: Specifies the minimum proportion of free space that must be retained in the block. When the free storage space in the block is reduced to the proportion set by pctfree, Oracle marks the block as unavailable and the new data row cannot be added to the block.
  • Pctused parameter: set a percentage. When the used storage space in the block is reduced below this percentage, the block will be marked as available again.
Note: the above two parameters (pctfree, pctused) areCan be at the tablespace levelSet up,Or at the segment levelSet up. Segment level settings take precedence.
 
(2) Extent:
Logical storage structure one level higher than block,Composed of continuous blocksIt isOracle is in storageThe smallest unit of allocation and recycling (zone is the smallest unit of disk allocation)。 When creating a database object with independent segment structure, such as table, index, etc., Oracle will create a data segment for the database object and assign an “initial area” to the data segment. For the allocation method of subsequent areas, different allocation methods are adopted according to the different space management methods of the table, such as autoxtent, uniform size or setting the default storage clause when creating the table or table space. In addition, users can recycle unused areas in tables, indexes and other objects by executing the following commands:
 
 select * from dba_extents;
 select * from user_extents;
 
 
 alter table table_name deallocate unused;
(3) Segment:
It is composed of multiple zones, which can be continuous or discontinuous. When users create various objects with actual storage structure (objects with data), such as tables, indexes, etc., Oracle will create “segments” for these objects.Generally, an object has only one segment。 When creating a segment, you can specify parameters such as pctfree and pctused to control the storage space management mode of the block, or you can specify storage parameters such as initial, next and pctincrease to specify the allocation mode of the block. IfThese parameters are not specified for the segment, which will automatically inherit the corresponding parameters of the tablespace。 Different types of database objects have different types of segments:
  • Data segment: save the user’s data (records in the table). Each table has a corresponding data segment with the same name as the data table
  • Index segment: used to store index information of system and user (index entry in index)
  • Temporary segment: when performing queries and other operations, Oracle may need to use some temporary storage space to temporarily save the parsed query statements and the temporary data generated during the sorting process, such as the sorting of order by statements and some summaries.
  • Rollback segment: used to store the value of user data before modification. The rollback segment and transaction are one to many relationships. A transaction can only use one rollback segment, while a rollback segment can store the rollback data of one or more transactions.
  To sum up: every table created usually corresponds to a segment. If it is a partition table, then each partition is independent into segments. If you create an index on a table, there will be another corresponding index segment.
 
 select * from dba_segments;
 select * from user_segments;
(4) Tablespace:
The most advanced logical storage structure,An Oracle database is composed of multiple tablespaces, but a tablespace only belongs to one database. A tablespace can have multiple data files, but a data file can only belong to one tablespace.A table space is a disk area. It consists of one or more disk files. A table space can hold many tables, indexes or clusters. Each table space has a pre fabricated disk area called the initial extent. After this area is used up, the next area is used up until the table space is used up. At this time, the table space needs to be expanded to add data files or expand existing data files. When creating a database, some default tablespaces will be created automatically, such as system tablespaces, sysaux tablespaces, etc. By using tablespaces, Oracle combines all relevant logical structures and objects. WeStorage parameters can be specified at the tablespace level or at the segment level。 Below we list common table spaces and the information they store:
  • Data table space: or user table space, a common table space used to store user data.
  • SYSTEM tablespace: the default tablespace, which is used to save data dictionaries (a set of internal system tables and views for saving database information, and other objects for Oracle internal use), source code and resolution code of all PL / SQL programs, including storage procedures and functions, packages, database triggers, etc., and database objects (tables, views, sequences) Definition.
  • Rollback tablespace: used to store rollback segments. Each instance can only use one undo tablespace at most. There are four common functions of undo tablespace:
1) Transaction rollback: when we perform DML operation on data, if we undo the modification, the data will be recovered to the pre modification data through the pre modification data recorded in undo tablespace. In some stored procedures, when exceptions are caught by exception, there is often a “rollback”, which is also used to rollback and recover the data operation of the current transaction.
2) When the database is recovered, the database is shut down abnormally. When the database is restarted, the uncommitted transactions are backed off according to the records of undo tablespace to recover the database.
3)Read consistencyWhen the operation data of a session is not submitted, the consistency of the data accessed by others (that is, the unmodified appearance) is guaranteed through undo table space.
4) Flashback query, which is a very interesting feature. You can view the data at a certain time point, often, calculated, not often. It is mainly used for recovery of misoperation, data deletion, etc.
To sum up, these things do not need to be taken care of in daily life.
  • Temporary table space: stores temporary data generated during SQL execution. When creating an Oracle database, you can set a parameter called sort area size. The default value is 65536. This memory will be used first when sorting. If it is insufficient, temporary table space will be used.
The mode of tablespace, also known as state, can be set as online or offline, read / write or read-only
Tablespaces can be taken offline automatically or manually. For example, you can take a tablespace offline for maintenance or backup and recovery. When the database encounters some errors, it will automatically take a table space offline, for example, when the database write process (DBWn) tries to write data files many times but fails. When a user attempts to access a table in an offline tablespace, they receive an error.
 
* when installing Oracle, some default configurations of tablespace are as follows:
1) When Oracle Database 11g R2 uses the DBCA database management assistant to create a database, three permanent tablespaces (system, sysaux, users), one temporary tablespace (Temp) and one undo tablespace (undotbs1) will be created by default.
2) Oracle database has several default tablespaces, among which system and sysaux tablespaces are required for an Oracle database. systemMust tablespaceyesSYSTEM,SYSAUX,TEMP,UNDOTBS1
 
  Operation method and practice of various table spaces:
1) Query tablespace
  select * from dba_tablespaces; 
 
2) Create datasheet space
 CREATE TABLESPACE "ts_test"                      
     LOGGING                                     
     DATAFILE 'D:\app\oradata\orcl\ts_test.dbf' 
     SIZE 512M                                   
     AUTOEXTEND ON                               
     NEXT 1024K                                  
     MAXSIZE UNLIMITED                           
     EXTENT MANAGEMENT LOCAL                     
     SEGMENT SPACE MANAGEMENT AUTO ;             
 
3) Create a temporary tablespace
 CREATE TEMPORARY TABLESPACE tmp_ts_test
   TEMPFILE  'D:\app\oradata\orcl\tmp_ts_test.DBF'
   SIZE 100M;
  
4) Rollback segment tablespace
 create undo tablespace undo_ts_test
     datafile  'D:\app\oradata\orclundo_ts_test.DBF'
     size  100M;
5) Create a new user, and use the previously created tablespace TS? Test and temporary tablespace TMP? TS? Test as the default usage space for testuser users.
 
 CREATE USER testuser         
     PROFILE "DEFAULT"            
     IDENTIFIED BY "pwd123456"       
     default tablespace ts_test  
     temporary tablespace tmp_ts_test  
     QUOTA 500m ON ts_test    
     ACCOUNT UNLOCK;              
 
 
 Grant connect, resource to testuser;
 
 
 
 GRANT "DBA" TO "testuser" WITH ADMIN OPTION;   
In terms of Oracle’s security policy, a user’s password is valid for 180 days by default. We can directly set the validity to permanent.
  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
 
6) If the table space is insufficient, there are two ways:
First, add data file:
Alter tablespace ts'u test  
     Add datafile '...' (full path name of data file)
     SIZE 1000M;
Second, set the table space to auto expand:
Alter database datafile '....' (full path name of data file)  
     AUTOEXTEND on; (when creating a tablespace, you can add this keyword to indicate that the tablespace is automatically extended.)
 
7) Delete tablespace
 drop tablespace ts_test
   including contents and datafile;
If there is data in the table space, it cannot be deleted successfully without adding the including contents. Adding the and datafiles keyword can automatically delete the data files under Linux and UNIX, but it needs to be deleted manually under windows
 
8) Undo table space and temp table space must have been created when the database is built, but they can be created, and users can specify the new space.
9) Oracle can specify different temporary table spaces for different users, and can set different temporary table spaces (temporary table space groups) for different sessions of the same user, so as to reduce IO competition.
 
2. Oracle physical storage structure:
Oracle database is logically composed of one or more tablespaces. Each tablespace is physically composed of one or more data files, and each data file is composed of data blocks. So,Logically, the data is stored in the table space, but physically in the data file corresponding to the table space。 We can use this diagram to describe the relationship between data files and tablespaces.
Or use the following picture to understand:
(1) The physical file organization structure of the Oracle Database:
  Basic documents:
1) Control documents:
Although the control file is small, it plays an important role in storing the basic information of the database. It contains pointers to the rest of the database (including the location of redo log files, data files, archive log files, etc.), stores important serial numbers and timestamps, and stores details of RMAN backups. Once the control document is damaged, the practice will be terminated immediatelyIn general, the protection of control files adopts multiplexing mechanism, that is, redundant copies are in different physical locations
2) Redo log file
The function of redo log file is to store changes to data, and redo log stores a series of change vectors (including online redo log file and archive log file) applied to database in chronological order. The instance recovery and the backup recovery required by the disk damage automatically performed by SMON when the database is started will be applied to the redo log for corresponding data recovery
Redo log fileMultiplexing is also recommended. A database must have at least two sets of redo log files。 One group is for LGWR to write. The log file is of fixed size. It will be full soon in peak business hours. After it is full, it will switch to the second group. In the database configured as archive mode, the archive process (ARCN) will start to archive and backup the contents of the first group, such as write and archive in this cycle. It should be noted that LGWR is not allowed to overwrite the logs of the current group before the archiving process has finished archiving them.
3) Data file
The data file stores the actual data. It is a physical file that stores the data. It is a physical concept. DBWn writes the contents of the database buffer to such files, and the size and number of data files are unlimited. Oracle starts with 10g,Creating a database requires at least two data files, one for system table space, which is used to store data dictionaries; one for sysaux table space, which is used to store auxiliary data of some data dictionaries.
Data files are made up of Oracle blocks. This is the basic I / O unit of Oracle. It is a different concept from operating system block. Oracle block is larger than operating system block. Of course, this is due to some considerations of performance. But we consider such a case. When users use operating system commands to back up data files (assuming one Oracle block = eight operating system blocks), Four operating system blocks have been copied, and then the CPU is preempted by DBWn, and DBWn updates the Oracle block again. At this time, when the copy command gets CPU time to copy the remaining four blocks, the data of the whole Oracle block is inconsistent. Therefore, this is also the time to perform this backup (user’s own backup), which requires some additional processing, For example, the reason why tablespace is set as backup mode. Of course, there is no such problem when using RMAN. The backup mechanism of RMAN is sure to get data consistent blocks. (this content can be understood)
For the protection of data files, it is generally possible to make regular backups or use raid.
 
  External documents:
The so-called external files mean that these files are not strictly part of the Oracle database.
1) Instance parameter file
This file stores some parameter settings required by the database, such as the size of each memory area, the maximum number of processes allowed, the maximum number of sessions, the location of the control file, the name of the database, etc. parameter files are alsoFiles to load first when the instance starts
2) Password file
Generally known as the external password file.The general user name and password are stored in the data dictionary,Not in this fileIn some special scenarios, such as the instance has not been started, at this time, I may need to log in to the system as an administrator to perform some recovery or startup operations. However, at this time, the data dictionary does not exist because the instance has not been started. At this time, the external password file is required to verify the user’s identity.
3) Archive log files
ARCN will back up and archive the online redo log files to such files, and the archived log files retain the complete historical information of data changes.
 
3、 Starting process of Oracle Database
This part mainly refers to (plagiarism):
https://blog.csdn.net/huay_li/article/details/80545275
https://www.jianshu.com/p/4d388f148737
 
The startup of Oracle requires four states: shutdown, nomount, mount, open
  1. Database process details
  (1)startup nomount
Start the instance (do not load the database). In this process, the relevant parameters of the configuration file (SPFILE / pfile) will be read to create the instance, that is, a series of processes. After starting, the instance is in the started state.
  (2)alter database mount;
Load (Associate) the database. During this process, the instance reads the control file and associates it with the corresponding database. At this time, the instance is in the state of “moved”.
  (3)alter database open;
Open the database and connect the instance to the data file. At this time, other users can access the database for reading and writing operations. At this time, the instance status is open.
Therefore, the instance status in the process of opening is as follows:Deaded(idle)>>Started >> Mounted >> Open
 
  2. Practice of database startup process
Generally, multiple databases will be established on one machine, so how to start the instance you want to start? The answer is: through Oracle Sid control, Oracle sid is a unique identification of the database in the operating system. Before demonstrating the database startup process, we first set up Oracle SID, and then start the instance.
(1) The preparations are as follows:
1) You can set the database to be started without setting it. You can use the database that is started by default during installation
 C:\Users\anand>set oracle_sid=orcl
2) Establish a connection with the database (in fact, it can be used in this way)
C:\Users\anand>sqlplus / as sysdba
 SQL * Plus: release 11.2.0.1.0 production on Saturday 2 10:58:17 2018
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 Connect to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
(2) Test database startup process
1) Start instance (do not load database)
SQL> startup nomount
 The Oracle routine has started.
 
 Total System Global Area 1068937216 bytes
 Fixed Size                  2182592 bytes
 Variable Size             624951872 bytes
 Database Buffers          436207616 bytes
 Redo Buffers                5595136 bytes
2) Load (Associate) database
SQL> alter database mount;
 
 The database has changed.
3) Open database
SQL> alter database open;
 
 The database has changed.
4) validation
 SQL> select status from v$instance;
 
 STATUS
 ------------
 OPEN

 

4、 The relationship among Oracle users, schema, database, tablespace and data file
This part mainly refers to (plagiarism):
https://blog.csdn.net/kanon_lgt/article/details/4269135
 
Before entering the abstract concept, you can look at this metaphor on the Internet:
“We can think of database as a large warehouse. The warehouse is divided into many rooms. Schema is the room. A schema represents a room. Table can be seen as a bed in every schema. Table is put into every room and can’t be placed outside of the room. Isn’t it that there is no sleeping at night Home can be returned, and then a lot of items can be placed on the bed, just like a lot of columns and rows can be placed on the table. The basic unit of data storage in the database is table. In reality, the basic unit of items placed in each warehouse is the bed, and user is the owner of each schema (so schema contains objects, not users). User and schema are one-to-one corresponding, and each u The ser can only use its own schema (room) without special specification. If a user wants to use other schema (room) things, it depends on whether the user (owner) of that schema (room) has given you this permission, or whether the DBA of this warehouse has given you this permission. In other words, if you are the owner of a warehouse, then the use right of the warehouse and all the things in the warehouse are yours (including the room). You have full operation right. You can throw away the unused things from each room, or put some useful things into a room. You can also assign specific permissions to each user, that is, he can go to a room What can be done can only be seen (read only), or it can have all the control rights (R / W) like the master. It depends on the role corresponding to the user. “
 
The specific concepts of these Oracle databases have been introduced in the previous section. Here is a brief introduction:
(1) Database:
Oracle database is the physical storage of data. This includes (data file ora or DBF, control file, online log, parameter file). In fact, the concept of Oracle database is different from other databases. The database here is an operating system with only one database. It can be seen that Oracle has only one large database.
(2) Example:
An Oracle instance consists of a series of background processes and memory structures.A database can have n instances
  
 
Relationship between database and instance:
The concept of Oracle instance and Oracle database is quite confusing. In many concepts, including I used to think that an instance is equivalent to a database. In fact, this should beIn most cases, it can be considered that an instance corresponds to a databaseThe instance is an instance, and the data is some converted file combinations screened from the outside world (as you will know after learning the fourth generation database, if you have done bare metal files, you will understand the principle). The database is not directly open to the outside world, and the open rule is through the instance, which is a section of memory, which contains many different sections (on the main body) It can be divided into SGA and PGA). All our operations are given to the instance, rather than directly operating the database file system. Why do we need to have the intermediate process? Oracle considers security, buffering, efficiency, scalability and other issues; and why do we say that in most cases? Because Oracle also has the concept of cluster, you will be more interested in Oracle RAC after the real cluster is used Deeply understand the relationship between instance and database. It uses multiple instances distributed on different hosts to operate the database on the same bare metal file system, so at this time, the instance and database are not one-to-one, but many to one, but only one database can be used for an instance.
 
(3) User (user)
  The user is established under the instance。 Different instances can create users with the same name.
The specific method of creating users is as follows (write it again):
 
 CREATE USER testuser         
     PROFILE "DEFAULT"            
     IDENTIFIED BY "pwd123456"       
     default tablespace ts_test  
     temporary tablespace tmp_ts_test  
     QUOTA 500m ON ts_test    
     ACCOUNT UNLOCK;              
 
  * description of profile (use profile file to realize user password restriction and resource restriction)
Proflie is a named collection of password restrictions and resource restrictions. When establishing an Oracle database, Oracle will automatically create a profile named default. The initialized default does not have any password and resource restrictions. Using profile has the following main items:
a. When establishing a profile, if only part of the password or resource restriction options are set, other options will automatically use the default value (the corresponding option of default)
b. When creating a user, Oracle will automatically assign the default to the corresponding database user if the profile option is not specified
c. A user can only assign one profile. If you want to manage the user’s password and resources at the same time, you should specify both password and resource options when creating a profile
d. When profile is used to manage passwords, the password management option is always activated, but if profile is used to manage resources, the resource limit must be activated
  
  * about the practice of profile use
1) View the currently existing profile file
select distinct profile from dba_profiles;
 --View the limits of resources in the specified profile file:
 select resource_name,limit from dba_profiles where profile='DEFAULT';
2) Modify the resource options in the current profile file:
 alter profile default limit FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 3;
3) Create a new profile file:
 CREATE PROFILE lock_accout LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 10;
4) Let users use the new profile file:
 alter user testuser profile lock_accout;
5) View the profile file currently used by the user
select username,profile from dba_users;
6) Use profile file to restrict users’ use of resources;
 
 alter system set resource_limit=TRUE scope=memory;
 
 alter profile lock_accout limit cpu_per_session 5000;
7) Delete profile
 drop profile lock_accout;
8) Delete the profile and change the user profile using the current profile to default
 drop profile lock_accout cascade;
9) All profile related parameters are listed below for easy reference:
Failed? Login? Attempts: used to specify the maximum number of failed contact logins
 
Password “lock” time: used to specify the number of days the account is locked
Password? Life? Time: used to specify the password validity period
Password? Grace? Time: used to specify password grace period
Password “reuse” time: used to specify the password reuse time
Password? Reuse? Max; specifies the number of times a password needs to change before it can be reused
Password? Verify? Function; verify password (verify change value to verify? Function)
CPU per session: used to specify the maximum CPU time that can be consumed by each session
Logical read per session: Specifies the maximum number of logical reads for a session
Private? SGA: used to specify the maximum total private space that a session can allocate in the shared pool. Note that this option only uses the shared server mode
Composite? Limit: the total resource consumption (unit: Service Unit) used to specify the session
CPU per call: limits the maximum CPU time (in hundredths of a second) that can be consumed per call (parsing, executing, or extracting data)
Logical read per call: used to limit the maximum number of logical I / O per call
Sessions per user: Specifies the maximum number of concurrent sessions per user
Connect? Time: Specifies the maximum connection time for a session
Idle time: Specifies the maximum idle time of the session
  (4)schema:
      In Oracle, generally, a user corresponds to a schema. The schema name of the user is equal to the user name. As the default schema of the user, the user cannot create a schema, schema is created when users are created, and various table spaces of users can be specified (this is different from PostgreSQL, which can create schema and assign it to a user). By default, all database objects created by users currently connected to the database belong to this schema (without specifying the schema). For example, if user Scott connects to the database and then creates table test (id int not null) to create a table, then the table is created in Scott’s schema; but if the user creates table testuser.test (id int not null), then the The table is created in the testuser schema, provided that the permission is allowed (that is, the testuser gives Scott the permission to create the data table under his schema). From this point of view, schema is a logical concept.
But be sure to pay attention to one thing:Schema is not created when user is created, but only after the user creates the first object. Only when there is an object under user, the corresponding schema will exist, if there is no object under user, the schema does not exist;
 
(5) Tablespace:
Tablespace is a data store for managingLogical concept, the table space is only related to the data file (ORA or DBF file), and the data file is physical,A table space can contain multiple data files, and a data file can only belong to one table space.
 
(6) Data files (DBF, ora):
Data files are databasePhysical storage unit。 The data of the database is logically stored in the table space, but the real landing is in one or more data files. A table space can be composed of one or more data files, and a data file can only belong to one table space. Once a data file is added to a tablespace, it cannot be deleted. If you want to delete a data file, you can only delete the tablespace it belongs to.
Table data is put into a table space by the user, and this table space will randomly put these table data into one or more data files.

Because Oracle’s database is not a common concept, Oracle has users and table space to manage and store data. But tables are not queried by table space, but by users. Because different users can create a table with the same name in the same table space! Here is the user!
The following diagram may help to understand the relationship between these names:
 
Reference resources:
https://blog.csdn.net/kanon_lgt/article/details/4269135
https://blog.csdn.net/Huay_Li/article/details/80575538
https://blog.csdn.net/huay_li/article/details/80545275
https://blog.csdn.net/u013933870/article/details/51700979
https://blog.csdn.net/oChangWen/article/details/51171992
https://blogs.oracle.com/database4cn/oracle-v6
https://www.jianshu.com/p/4d388f148737
https://www.cnblogs.com/fnng/archive/2012/07/24/2607482.html
https://blog.51cto.com/tianzt/528873
https://wenku.baidu.com/view/047cd4621ed9ad51f01df24a?pcf=2