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

1. Overview of Oracle

-Oracle Database, or Oracle for short. Is an object-relational database management system (ORDBMS) of ORACLE (Oracle) in the United States. At present, it occupies a major share in the database market.

The history of Oracle: Ellison and two friends founded RSI in 1977; in the summer of 1979, RSI released the second edition of Oracle; in 1983, it released the third edition of Oracle and renamed it to Oracle in 1983 (meaning "the source of all wisdom"); in 1986, it became the fourth largest software company in the world; in June 1992, Oracle released version 7; in 1997, Oracle released version 8; in June 2001, Oracle released Oracle 9i. In September 2003, Oracle released Oracle 10g; on July 11, 2007, Oracle released Oracle 11g; in 2013, Oracle 12c released. (note: this series of topics uses Oracle 11g. )

-Why learn Oracle?

-excellent performance, a model in large databases; object-relational database management system (ORDBMS); widely used in management information, enterprise data processing, Internet and e-commerce and other fields.

-excellent performance in data security and data integrity control; data interoperability across operating systems and hardware platforms; support for multi-user, major transactions; good portability.

The installation tutorial of Oracle 11g will not be discussed in detail. Please refer to the https://jingyan.baidu.com/article/363872eccfb9266e4aa16f5d.html web page for details.

-uninstall Oracle 11g: first find the directory app\ Administrator\ product\ 11.2.0\ dbhome_1\ deinstall, click deinstall.bat (a command line window opens), and then follow the command line prompts step by step.

2. The architecture of Oracle database.

The so-called Oracle or Oracle database refers to the Oracle database management system. It consists of two parts, namely, Oracle Server consists of an Oracle instance and an Oracle database. (Oracle Server = Oracle Instance + Oracle Database. )

For people who are new to Oracle databases, the two concepts that are easy to confuse are Oracle instances and Oracle databases.

A, Oracles database

-A collection of related operating system files (that is, files stored on the computer's hard disk) (including control files, data files, online log files, parameter files, password files, etc.). These files are organized into a logical whole, that is, the Oracle database.

-A database is a collection of data that Oracle uses to store and manage related information.

-Oracle database must cooperate with in-memory instances in order to provide data management services.

B. Oracle instance

-memory structure (SGA, PGA) and a series of background processes (Background Process), which together are called an Oracle instance. Namely: Oracle Instance = SGA, PGA + Background Process.

-Oracle memory structure: includes system global area (SGA) and program global area (PGA). That is Oracle Memory Strutures = SGA + PGA. (SGA is shared by the server and background processes; PGA contains the data and control information of a single background process. Contrary to the SGA shared by several processes, PGA is an area used by only one process. PGA is assigned to be collected when the process is terminated when the process is created, that is, it is generated by the server process. )

-SGA: system global SGA,SGA = data buffer + redo log buffer + shared pool + large pool + Java pool + stream pool. The global area of the system is dynamic and is determined by the parameter SGA_MAX_SIZE.

-data buffer (Database buffer cache): stores the mirror image of the data blocks obtained from the data file. The size is determined by db_cache_size.

-redo log buffer (Redo log buffer): any changes to the database are recorded in this buffer in order, and then written to disk by the LGWR process, whose size is determined by LOG_BUFFER.

-shared pool (Shared pool): is the most critical memory fragment in SGA. The shared pool is mainly composed of library cache (shared SQL area and PL/SQL area) and data dictionary cache. Its function is to store frequently used sql. In the limited capacity, the database system decides when to release the sql in the shared pool according to certain algorithms. The size in the library cache is determined by shared_pool_size.

-data dictionary cache: stores the definition and permission information of data files, tables, indexes, columns, users and other data objects in the database. The size is determined by shared_pool_size and cannot be specified separately.

-Large pool: an optional area for large processes such as Oracle backup and restore operations, IO server processes, etc.

-Java pool: this program buffer is reserved for Java programs. There is no need to change the default size of the buffer if you do not use the Java program.

-PGA: memory reserved for each user process to connect to the ORACLE database. It is allocated when the process is created, released when the process ends, and can only be used by one process. PGA includes the following structures: sorting area, cursor state area, session information area, stack area. It is determined by the parameter pga_aggregate_target.

-background process: PMON-- > program monitoring process, SMON-- > system monitoring process, DBWR-- > database writing process, CKPT-- > checkpoint process.

-PMON (Program Monitoring process): clears the invalid user process and releases the resources used by the user process. For example, PMON will roll back the committed work, release the lock, and release the SGA resources allocated to the failed process.

-SMON (system monitoring process): check the consistency of the database, complete disaster recovery when startup fails, etc.

-DBWR (database writing process): responsible for writing modified data blocks from the database buffer cache to data files on disk.

-CKPT (checkpoint process): the working principle of DBWR/LGWR, resulting in data files, log files, control files inconsistent, the CKPT process is responsible for synchronizing data files, log files and control files, updating the header information of data files / control files, etc.

C. The relationship between Oracle instance and Oracle database

-an instance can load and open a database.

-A database can be loaded and opened by multiple instances.

-the relationship between the instance and the database is one-to-one or many-to-one.

3. Common commands in Oracle

A. Understand the meaning of the 7 services of Oracle

-Oracle ORCL VSS Writer Service:Oracle volume mapping copy writing service, VSS (Volumne Shadow Copy Service) enables storage devices (such as disks, arrays, etc.) to create high-fidelity point-in-time images, namely mapped copies (shadow copy). (optional start)

-OracleDBConsoleorcl:Oracle database console service. Orcl is the instance ID of Oracle. The default instance is orcl. Sam, an expert in oracle health inspection, says the service needs to be started when running Enterprise Manager (Enterprise Manager OEM). (optional start)

-OracleJobSchedulerORCL:Oracle job scheduling (timer) service. ORCL is the ID of the Oracle instance. (optional start)

-OracleMTSRecoveryService: server control. Sam, an expert in oracle health inspection, said the service allows the database to act as a resource manager for Microsoft transaction server MTS,COM/COM+ objects and transactions in a distributed environment. (optional start)

-part of the OracleOraDb11g_home1ClrAgent:Oracle Database .NET extension service. (optional start)

-OracleOraDb11g_home1TNSListener: listener service, which is needed only when the database needs remote access. (optional start)

-OracleServiceORCL: database service (database instance), which is the core service of Oracle. This service is the basis of database startup, and only when the service is started, the Oracle database can be started normally. (must be started)

B. The use of ORACLE tools

-four ways: SQL command line mode (using SQLPlus included in Oracle), browser mode, WEB-based enterprise manager EM, Sql Developer, PLSQL Developer tools.

-browser mode, the use of WEB-based enterprise manager EM: find the URL address of the Oracle Enterprise Management (Oracle Enterprise Manager) console and copy it to the browser: (it should be noted that some browsers cannot use secondary features). The address can be found in the readme.txt file in the\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ install directory.

C, SQL Plus commands commonly used in Oracle

-Connect: switch connection users, abbreviated to conn.

-Show user: displays the currently logged in user.

-Host: executes operating system commands.

-Spool: export records to text.

-Clear screen: clear the screen.

-Start d:\ test.sql: execute the SQL statement in the file system (Note: the start command is equivalent to @, that is, @ d:\ test.sql)

-Desc: displays the table structure.

-Show error: displays an error message.

-EXIT: quit.

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.