Oracle Architecture and User management

Database architecture
Defined:
The composition of the database, the working process, the composition and management mechanism of the data in the database.

Composition
instances, user processes, server processes, database files,

Other files (parameter files, password files, archive files).

Oracle's concept:
Program Global Zone (PGA):
Defined:
A piece of memory that the server process uses to contain data and control information,

PGA is a non-shared memory that is allocated when the server process is started or created

(The PGA assignment is also required for operations such as sequencing, connection, etc.) during system operation.

View by command:

Sql> Show Parameter Area_size

Oracle instances:
A collection of background processes and memory structures.

System global Area (SGA):
Defined:
When the instance starts, Oracle allocates a block of memory for the instance, which is called the SGA.
is a huge memory space that will allocate an SGA,

The system global zone and starts some of the Oracle's daemon Processes (daemons).

Composition
Shared pool:
Where some "program" data is cached.

Data buffers:
After data is written to disk or read from disk,

This data is stored in the block buffer cache.

Log buffers:
The data needs to be written to the online redo log, before it is written to disk

To temporarily cache this data in the redo buffer. Improve access speed.

Java Pool:
Running Java code in the data will be used.

Candidates
For large memory allocations. The shared pool is not processed.

View:
sql> Select pool,sum (bytes) bytes from V$sgastat group by pool;

Memory =SGA+PGA in Oracle
There can be only one SGA in an instance, and all server processes and background processes share an SGA.

Database:
A collection of data,
A set of operating system files that physically refer to the storage of database information.

Physical Structure:
A set of operating system files (3 files) that comprise the database:

                     data files, control files, redo log files.

  Logical Structure:
      logical storage structure for database data (table space, segments) and schema objects (tables, views, etc.).


  SQL statement execution:
     1, compile, grammar, semantic check.
     2, Shared pool:
            Library cache:
                  Cache the correct SQL statement.
            Dictionary cache (data dictionary cache):
                  cache permissions and object data and properties.

        prevent shared pools from being insufficient, using FIFO rules:

The SQL statement that is executed after the execution overwrites the SQL statement that was executed first.

3. Data Buffer cache:
The data block is transferred into memory from disk or storage, and buffer cache is present.

4. Redo Log buffer (redo log buffers):
After performing additions and deletions, the changed data is first written to the redo log buffer,

The data before the change is placed in the redo log buffer.

Finally, the changed data is written to the data buffer.

Ensure that the transaction needs to be committed or withdrawn at the time of data recovery.

View:
Sql> Show parameter shared_pool_size;
//Display 0 indicates automation management.

Sql> Select Name,bytes/1024/1024| | ' MB ' from V$sgainfo;
//View the size of the shared pool.

Sql> Set Timing on
//Start time.

Sql> select * from Scott.emp;
//Query the Scott.emp table.

Sql> Show parameter db_cache_size;
the size of the//query buffer.

Oracle Process:
User process:
The database user requests the connection server to start.

Server process:
Started when a user establishes a session and connects to an instance.

Background process:
Starts when the instance is started.


Background process (5 processes):
DBWN Process:
Data write process (DBWR)
Writes dirty data from the buffer cache to the data file in the hard disk.

Dirty data:
The modified data is stored in the memory buffer, and the transaction is not committed.

Modified data that has not yet been written to the hard drive data file.


Sql> col description for A40;
Sql> Select Paddr,name,description from v$bgprocess where name like ' dbw% ';

Process monitoring Process (Pmon):
Manages user sessions and performs the appropriate actions when a problem occurs in a session.

System monitoring Process (Smon):
When the instance starts, the Smon process checks the synchronization information of all data files with the control file

The recorded checkpoint information is consistent, and the instance recovery operation is initiated with inconsistencies.

The CKPT process is used for the Smon process.


Log write Process (LGWR):
Only one.
The LGWR process is triggered when a user commits a transaction.
The LGWR process is triggered when the One-third space in the red log buffer space is exhausted.

Before the dirty data is written to the disk, the LGWR process writes the log information to the log file first.

Perform such operations every 3 seconds.

LGWR process cannot have multiple, log sequence is serial write, cannot do parallel write,

Otherwise, data recovery cannot be done.

$ ps-elf | grep Ora_ |grep LGWR
//View the LGWR process.

Sql> Select Paddr,name,description from v$bgprocess where name like ' lg% ';


Checkpoint process (CKPT):
The mechanism for ensuring data integrity is to check that all data files and control file headers are consistent.
The CKPT process is responsible for updating the header information of the data file and control file, and saving the checkpoint information,

Ensure synchronization of database log files and data files.

Principle:
When recovering a database, you only need to find the last checkpoint saved by the CKPT.

You can determine the starting position of the recovered data in the log file based on it,

The log information after the re-execution.


Sql> select Paddr,name,description from v$bgprocess;
//query all background processes.

Sql> SELECT * from v$bgprocess where paddr< > ' 00 ';
//Retrieving paddr is a 00 process.

Sql> SELECT * from v$bgprocess where paddr= ' 00 ';


Oracle's storage structure
Physical Structure:
Main documents:
Data files:
A file that physically stores database data. dbf.

Characteristics:
Each data file is associated with only one database.
A table space can contain one or more data files.
A database file belongs to only one table space.

Redo Log Files:
The extension is. log, which records all changes to the data,

and provides a data recovery mechanism,

Ensure that the database is re-recovered after a system crash and other unexpected occurrences.

Control files:
extension. CTL, is a binary file,

Includes the name and location of the data file and redo log file.

                is the file that is necessary for the database to start and run.

      Other files:
           parameter file, Archive log files, password files.


  Logical Structure:
       includes table spaces, segments, extents, blocks, patterns, and so on.

      tablespace:
          Permanent table space, The temporary tablespace, which revokes the table space.

      system tablespace:
           System,sysaux,users and Temp.


Oracle User Management
 sys:
      super users in Oracle, all data dictionaries and views in the database are stored in the SYS mode.

SYS users can only log on to the system in Sysoper or SYSDBA roles.

SYSTEM:
The default database administrator in Oracle, with DBA authority.

SCOTT:
A demo user of the Oracle database, created when the database is installed.



To access the database: You must pass Oracle Authentication (2 methods):
Configuring an application requires that each individual that needs to be accessed has a different database account.
Configure the application itself to connect to the database as a public user and to process the application-level permissions internally.

Attention:
Within the database, you must create one or more users that allow operational data.

Sql> select * from Dba_users;
//View all users in the database. An account with DBA authority is allowed.

Sql> select * from All_users;
//View all users that you can manage.

Sql> select * from User_users;
//view current user information.

This article is from the "Enron Smile" blog, please be sure to keep this source http://liyuanjie.blog.51cto.com/12877605/1963955

Oracle Architecture and User management