Shulou(Shulou.com)06/01 Report--

1. user process

Ps -elf | greporacleocl |grep-v grep

2. database process

Ps -elf |greporacleorcl

3. daemon

Ps -elf |grep oha

Large pools: In SGA, large pools are optional buffer areas that administrators can configure as needed for large-scale input/output and backup processes, such as large data operations, database backups and restores.

Java pool: Java pool is an optional buffer, but must be set up when installing java or java programs, it is used to compile Java language writing instructions.

Six core processes:

DBWn process: used to write dirty data in buffer cache to data file in hard disk.

What is dirty data, for example, I execute the following statement

SQL> update emp set sal=sal+100;

At this time, the modified data is stored in memory and not saved to the data file on the hard disk. These data are cached in the buffer cache, and the transaction has not been committed. In this way, the modified data that has not been written to the hard disk data file is called dirty data. In fact, at this time, open a session, use SCOTT user connection to check that the data in the emp table is unmodified data. So in oracle such an operation is asynchronous write, multiple processes can write at the same time, multiple accounts will have multiple DBWn processes, in fact, the process name DMW, followed by n is the process ID generated

SQL> update scott.emp set sal=sal+100;

Fourteen lines have been updated.

SQL>

LGWR: There is only one log writing process

The lgwr process is triggered when the user commits

The lgwr process is triggered when one-third of the red log buffer space is used up

Before writing dirty data to disk, the LGWR process writes log information to log files every 3 seconds.

LGWR process cannot have multiple, because the log is written sequentially, can not do parallel write, otherwise can not do data recovery.

CKPT process: Checking whether all data file and control file headers are consistent is a mechanism to ensure that all modified data blocks in the data buffer are written to the data file, that is, a mechanism to ensure data integrity. However, after the checkpoint is completed, the CKPT process is responsible for updating the header information of the data file and the control file, and saving the checkpoint information to ensure that the database log file and the data file are synchronized. The principle is that when the database is restored, just find the last checkpoint saved by CKPT, and you can determine the starting position of the restored data in the log file according to it, and then re-execute the log information.

SMON process: SMON process will be triggered if data file and control file headers are inconsistent. When an instance is started, SMON process checks whether synchronization information of all data files is consistent with checkpoint information recorded in control file, and if not, starts instance recovery operation. The CKPT process is intended for use by SMON processes.

PMON process: Process monitoring process, PMON process manages user sessions, this process will perform appropriate actions when a session has problems. For example, when a user's PC restarts while logged into the database, PMON can detect this problem and also collate any actions the user is doing.

1.1. Oracle storage structure

Data file related knowledge

To see how many data files are in the system, we can view them through dynamic data dictionary or static data dictionary: View data files through static data dictionary

View data files via dynamic data dictionary

The contents of a data file cannot be viewed directly, but must be viewed through a logical structure, namely a table space. A table space is a logical storage structure, whereas a data file is a physical structure.

How to view tablespaces

Tablespaces can also be viewed through dba_tablespaces

Data dictionary is a logical storage structure, and data file is a real storage structure, if there is a table space, but no data file is not possible, table space and data file are corresponding, if there is no data file, table space cannot exist independently, if only table space does not have data file is also not possible, just like if the computer does not have a hard disk, you can no longer logically see C disk D disk, where the hard disk is equivalent to a data file, Disk C is equivalent to table space. We can put multiple data files into a table space, and then store data in the table space, table space and data file are corresponding, a table space can be composed of one or more data files, but a data file can only be in a table space, how do we check the correspondence between table space and data file?

You can also view the size of the tablespaces

You can also create new data files in the table spaces described above. If you run out of table space, simply create the appropriate data file. The size of the table space is the sum of the sizes of the data files, and the final data files are stored in the hard disk, but we can store the data files in different hard disks, as long as they are in a table space, so that the hard disk size is limited to the files, and the data files in multiple hard disks can be formed into a table space.

So DBAs will store a lot of tables, views, indexes, synonyms and other objects in the table space.

Segment: For example, in the xx table space DBA created a table named test, this table space occupied space, we call it segment. According to the different objects stored in the segment, it is divided into data segment, index segment, fallback segment, temporary segment, etc.

You can view segment information

For segments, there is a smaller unit called a zone.

Zone (extent): Zone is the smallest unit of disk space allocation. Disk is divided into zones. At least one zone is allocated at a time. Zone is composed of continuous data blocks. A segment is mainly composed of one or more zones. When a segment is created, it contains at least one zone. When all the space in the segment has been used, the system automatically allocates a new zone for the segment. Zones cannot exist across data files, but can only exist in one data file.

A segment is composed of regions, and the segment must be an integer multiple of the region. If a table is 25MB and a region is 4MB in size, then the size of the segment is 28MB. If 3MB of data is added to the table, the space for this period can still accommodate the table. Now the size of the table is 28MB. If 1MB is added, then the space for the segment is 32 MB.

View the distribution of extents and segments for the EXAMPLE tablespace

The smallest unit of storage in an Oracle database is a block-8KB, in which data is stored. Data block is the smallest storage unit that oracle server can read or write. Oracle server manages the storage space of data files in units of data block. The value range of data block is 2-64KB, and its default size is related to oracle version.

View minimum storage unit for operating system

[root@orclsrv ~]# tune2fs -l /dev/sda1

These letters can also be viewed through OEM

Creating tablespaces and data files

SQL> create tablespace xx datafile '/u01/xx.dbf' size 100m autoextend on

2 next 5m maxsize 200m;

Tablespace created.

Change the name of the table space to yy

For administrative purposes, we want the name of the table space to match the name of the data file.

Solution:

1. Use operating system commands to make a copy of the data file

2. Put the table space offline

3. Then rename the data file

Set the table space offline

Rename data files

View modifications

Put Tablespace Online

Prompt to restore/u01/yy.dbf

Put Tablespace Online

How to expand the size of tablespaces

1. Change data file size directly

2. Add data files to tablespaces

If the partition in which the table space is located runs out of space

Delete Tablespace

Create temporary tablespaces

Create temporary tablespaces

Deleting temporary tablespaces is the same command as deleting tablespaces.

1.2. Oracle User Management

If you want to see if users exist in the oracle system, you can view the dba_users view

If sqlplus queries need to be preformatted

SQL> col username for a20

SQL> col account_status for a20

SQL> col default_tablespace for a20

SQL> set linesize 120

SQL> set pagesize 120

SQL> select username,account_status,default_tablespace from dba_users;

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE

-------------------- -------------------- --------------------

MGMT_VIEW OPEN SYSTEM

SYSOPENSYSTEM

SYSTEM OPENSYSTEM

DBSNMP OPENSYSAUX

SYSMAN OPENSYSAUX

SCOTTOPENUSERS

OUTLN EXPIRED & LOCKED SYSTEM

FLOWS_FILES EXPIRED & LOCKED SYSAUX

MDSYS EXPIRED & LOCKED SYSAUX

ORDSYS EXPIRED & LOCKED SYSAUX

EXFSYS EXPIRED & LOCKED SYSAUX

Oracle User Management---

To access the database, anyone must be a valid database user who can authenticate with oracle, and we can configure the application to require a different database account for each individual who needs access, or we can configure the application itself to connect to the database as a public user and handle application-level permissions internally. Either way, you need to create one or more users inside the database that allow you to manipulate the data.

1.3. Users and Schema

Syntax for creating users:

Create user username identified by password default tablespace quota size on tablespaces;

Note: Although the user name is written in lowercase when creating a user, it is written in uppercase when stored in the data dictionary, such as create user tom. If you must use lowercase, the user name needs to be enclosed in double quotes create user "tom"

The user created does not have any permissions, even the permission to log on to the database.

If a user's quota for a table space is not set, then even if the user has write permission to the table space, it cannot be written.

Examples:

View HR user status

Change HR user password to oracle

Manual setting expiration;

unlock user

Examples of grant and revoke permissions for oracle

Before creating users, create a table space xxx

Create a table space xxx

createtablespace xxx datafile '/u01/xxx.dbf' size 100m autoextend on next 10m maxsize unlimited;

Create user tom

create user tom identified by oracle;

query created user tom

Although created in lowercase tom, but stored in the data dictionary is uppercase, so queries also need to write uppercase

Remove tom users

Drop user tom;

create user"tom" identified by oracle;

Remove tom users

The correct way to delete should be:

drop user"tom";

Create user tom, the default table space is "XXX", and no quota is specified, so tom users cannot create objects on xxx table space

Results of using tom users to connect to the database

SQL>conn sys/oracle as sysdba

Drop user tom

Tom users are created specifying that the default table space is xxx and the disk quota for tom users is 10m

You can use the dba_ts_quotas view to view disk quotas per user

The tom user we just tested even has permission to create a session

1.4. Granting privileges to users 34

Grant session permissions to tom users

SQL>conn sys/oracle as sysdba

SQL> grant create session to tom;

Authorization successful.

Test the connection.

Test whether tom users can create tables

Grant tom user permission to create tables

Create aa table

Test tom user can insert record for table, can insert record.

1.5. revoke permissions

View tom user's current permissions

Revoke tom user's create table permission

Test tom users can view emp tables under SCOTT users

Connect to the database using sys users

Authorize tom user to emp table under select scott user

Connecting to a database using tom users

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.