(BOT) Oracle study notes - Architecture Liu Chuan author : Title:Oracle Architecture Date:06-03-03 E-Mail : [email]xsp_cn@yahoo.com.cn[/email] Oracle8I Architecture One operates as an example of the Oracle database (Instance). Examples database system is the only marking Oracle_SID used in the operating system, parameters used in Oracle INSTANCE_NAME said. First to start the database server in the overall distribution system (SGA), which constitute memory structure. The OS then start the process of a number of permanent memory, the process of structural components of Oracle. Architecture, including physical, logical structure, the structure of memory structures and processes. : The database is used by the physical structure of the database system posed by the document. Physical document consists of an Oracle database data files, and log files and document control parameters such as paper documents and distribution Database File database for the storage of data : documents, such as Table, indexing data. Visit data, a system to read data from a data file and the data stored in the memory buffer. To reduce the total number of IO operation, increased performance, data has been stored in memory, the background process DBWR to decide how to incorporate the data corresponding to the document. Information : All of the data files stored in the data file name, size, the corresponding table space information, it automatically extended. Select tablespace_name, bytes, blocks, autoextensible, file_name from dba_data_files; Usually the size of data files or blocks can be used bytes byte count, said the number of data blocks. China : 8i BLOCKS=8192bytes data blocks, the data block size of 8k. In Oracle data block size, the smallest database as a unit, reading the data when the data blocks for reading. DB_BLOCK_SIZE parameters determined by the size of data blocks in the parameters documented. General Physics piece is a multiple operating system, in multiples of 512 bytes. 9i system and acquiescence Table Space DB_BLOCK_SIZE identifying data block size, and other non-acquiescence table space using parameters BLOCKSIZE said. General Data for 4K blocks, 8K, 16k, 32K size Quick access to the data size : select * from v$parameter where name="_ftn1" 'db_block_size'; Show parameter db_block_size; See Parameter File Information : Once added to the database data files will not be deleted unless deleted table space. Generally speaking, a multi-table database space, a table space containing one or more data files. Dynamic data can also change the paper size, the revised data file size : alter database datafile 'd:\oracle\oradata\oradbsp\gsm01.dbf' resize 1000M Inquiries can understand the data dictionary table space DBA_FREE_SPACE spare capacity. Automatically extend when the space End data files, it will automatically expand space Autoextend attribute to decide whether to expand, said next expansion of the size of each automatically extended by the maximum size maxsize decision or unlimited, acquiescence system can be set up to automatically extend all data files. Alter Database Datafile above parameters can be used to amend the order. Alter TAbleSPACE ADD order to increase the use of new data files, increasing autoextend attribute. Usually need to create data files automatically extended attribute increased maxsize closed or restricted. Table space information : Table space by one or more data files can be passed DBA_TABLESPACES inquiries. Table space for the storage parameters are : initial_extent (initial district size) \next_extent (next allocation table size) \min_extents (the district) have \maxtents (the largest number of districts) and Pct_increase (next growth percentage) These parameters are stored in table provides space for the storage and space the size of the entity. Query : select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE from dba_tablespaces; Table information storage parameters : Select initial_extent, next_extent, min_extents, max_extents, pct_increase from user_tables; Log files : for recording information in the database changes, all changes to the database's information has been recorded in the log. Default log of the database created for three teams, each member. Switching log for that group. To ensure the security of the database so the members can log on the mirror, when a breach does not affect the normal operation of the database. Alter database add logfile 'path\filename' group 1// increased by a member of the first log. Log by the number of members of the parameters maxlogmembers restrictions. Switch : When a log log file members of the group was also a full log of all the data, the system automatically switched to a log file group. Logfile; alter system switch manually switching : Inquiries can log log : select * from the state v$log;// Group members can select * from v$logfile;// Document Log checkpoints (Checkpoint) : CKPT short, Oracle is a background process called check-points or checkpoints. Check points to ensure that all changes have been made to the database are included in the database buffer. Log_checkpoint_timeout parameters of a checkpoint by the time spacing parameters log_checkpoint_inteval decided to implement an inspection needs to fill the number of log files faster. V$log data dictionary to check that the current system has been changed its SCN (System Change Number), which are also known as checkpoints, at the system used checkpoint_change# said that the database data files, control documents, log files simultaneously basis. Checkpoint_change# stored in the log file, database file and document control. In switching log files, inspection points at the same time change. SCN will issue immediate change in the database operation, will change the results into data files, documents and database head control to ensure that all documents database synchronization. Increased log and delete groups : Alter database add logfile group 4 ( 'd:\oracle\oradata\oradbsp\redo04.log,' 'e:\oradata\oradbsp\redo04.log') size 1m Alter database dorp logfile gorup 4; Increase membership and delete log Alter database add logfile member 'filename' reuse to group 4; Alter database drop logfile member 'filename'; Control is a binary file documents : document control, data used to describe the physical structure. A database only needs a control document. Control the content of the document include : 1. DB_NAME unique identifier database and the database name. The database is set up parameters to be included in binary mode control documents. DBID logo has been included in the database only document control Select dbid, name from v$database; 2. Canada data creation time Select name, created, checkpoint_change# from v$database; 3. Designation : all data files and log files in the data file name and path, the log file name and path. 4. The synchronization information is also required to restore the database stored in the control of documentation, information that is simultaneously its checkpont_change# When changes in the structure of the database, the control system automatically changes the information in the documents, the database was created three acquiescence to control document, the name and path parameters paper, mirror the relationship between the three documents generally stored in different physical disk Select * from v$controlfile; Mirror paper manual control steps : 1). Closed Database 2). Complex document control system to designated trails 3). Complex document will be added to the control system parameters document 4) the resumption of the Secretary Renewal control documents : alter database backup controlfile to trace and then control the reconstruction udump catalog documents script. Can rebuild All the orders database structure changes will lead to changes in document control. Laws were also recorded in the database structure information ORACLE tracking documents, and tracking information into the background by Oracle Automatic Storage, tracking document, entitled alert_SID.log Contents of the document tracking and storage background_dest parameters can be used to determine the parameters of documents. $oraclHome/admin/DB_NAME/bdump Acquiescence in the catalog. User defined parameters tracked by USER_DUMP_DEST Table space, including the structure of logic : (Tablespaces) \ (segments) \ (Extents) \ Data Express (Data Blocks) and object model. Table space is the most logical, logical data faster is a small admission. Table space by the number of logical database constituted. Table space in physics posed by the various data files. Storage is a logical target of an occupied one of the entities, including data, the index of \ \ temporary rollback of the above. Of the increase is achieved by increasing the number of distribution space, and at least one distribution, the unit is an integral multiple of data quickly. IO Data Express is the smallest unit database, the data is usually fast integer multiples OS. Table space and the number of types of inquiries : Select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE from dba_tablespaces; 4003rd changes the size of data files Alter database datafile 'd:\oracle\oradata\oradbsp\gsm01.dbf' resize 1000M 4003rd Table space Offline Alter tablespace employee OFFLINE; 4003rd see Table space state Select tablespace_name, status from dba_tablespaces; Oracle : according to the classification of the data storage performance distinction of four categories : \ data and the index of temporary and rollback segment. Table-based database for the storage of data for storage, indexing of the index data for the classification of temporary; For the rollback of the rollback. Table regression exclusive space : a rollback of the table space. If RBS.RBS Table space physics from various data files, logical expression of a number of regression See regression of the 4003rd Select from dba_rollback_segs; SEGMENT_NAME.TABLESPACE_NAME 4003rd set up for the RBS table space or Read ONLY state OFFLINE Alter rollback of the rollback segment rbs1 offline;// installed Offline Alter tablespace rbs offline;, 4003rd create table space RBS Offline Provisional main categories of users : Table space may be required for total temporary space, the user temporary table space can be specified by the DBA to create users, create temporary increase in temporary table space option 4003rd adding temporary table space, increasing TempOraRY option Create tablespace userTemp datafile 'd : \oracle\oradata\oradbsp\userTemp01.dbf 'size 200M 1000M Temporary; autoextend on next IEMG maxSize 4003rd users set up temporary table space Alter user test temporary tablespace userTemp; 4003rd establishment of a "provisional" Table space Create temporary tablespace TempTableSpace tempfile 'd : \oracle\oradata\oradbsp\tempfile.dbf 'size 200M autoextend on next IEMG maxSize 1000M; V$tempfile;// detect select * from "temporary" Spatial Information Table : Table of storage space, the designated types of logical structure. By paragraph (extents). \ \ Index of the data into the regression of the above and temporary. According to the above : It used to store data, storage of data in the data table space 4003rd acquiescence table space set up users Alter user test default tablespace testtablespace; 4003rd acquiescence see Table space users The data base of gradual change with the increase of data, of the increase is achieved by increasing the number of Index : for data storage, indexing, creating an index, table space in the automatic indexing of the creation of a 4003rd the index, while the index table, as well as an index, as shown in the table listing Select a.table_name, a.index_name, a.uniqueness, b.column_name From user_indexes a, b user_ind_columns Where a.index_name=b.index_name and a.table_name=b.table_name and a.table_name= 'YHDA_ONE' Order by a.table_type, a.table_name, a.index_name; For the storage of user data back to retire before changes : the value of each user in insert\update\delete time, the system must provide users with a rollback of 4003rd rollback of information inquiries Select SEGMENT_NAME, OWNER, or TABLESPACE_NAME from dba_rollback_segs; Select SEGMENT_NAME, OWNER, TABLESPACE_NAME from dba_segments where segment_type= 'ROLLBACK'; Ranking : by the use of the temporary order, in the interim users automatically create a temporary table space, scheduling after temporary removal of the automatic system used acquiescence temporary table space for users to use Ranking Provisional users exclusive access of the 4003rd Select username, temporary_tablespace from dba_users; Broken down by region : disk, each allocated at least one area of increase is achieved by the District, which is composed of a row of data quickly. , Each the size of the entire data several times faster. 4003rd access to data storage parameters of the Select * from all_tables where owner= 'TEST'; Or Select a.initial_extent. A.next_extent. A.min_extents. A.max_extents. A.pct_increase Karlsson from user_tables DB_BLOCK_SIZE determined by the parameters : faster data, 9i acquiescence table space for the system parameters DB_BLOCK_SIZE faster data set size, the use of non-acquiescence faster data set size parameters BLOCKSIZE Targets include : the base model, view, the aggregation, indexing and sequencing, synonyms, etc. Links Memory : Memory Architecture Oracle database system into the overall district SGA\ procedures PGA\ overall ranking district Sort Area District, a large pool Large Pool and JAVA pool. Buffer zones : the whole system including data, log buffer, and shared pool. System memory whole area is the largest database server, a regional occupation. 1). Buffer : revised data, insert data were stored in the data buffer, when changes are completed, the data was written into the document. Use at least recently used LRU principle. Db_block_buffers size parameters determined by the parameters of the document Select * from v$parameter where name="_ftn1" 'db_block_buffers' Hits : hit Ratio=1- (data document the amount of time - the total time from the buffer-the amount of buffer contains LOB) / total reading 2). Buffer memory : log database for the revised information. When the log buffer quantities of log data, from log to log into the process LGWR log file data into the group. Log buffer size parameters set LOG_BUFFER Log_buffer parameter or parameters to check documents show log_buffe 3). SQL\pl/sql procedures for sharing pool : grammatical analysis and translation, and the implementation of the memory region. Sharing pool comprising buffer and the data dictionary and users overall buffer zone three parts. Executive Secretary buffer containing SQL code, the implementation plan; Data Dictionary from the buffer containing the data dictionary table, and the definition of competence shown Shared Pool mainly for the SQL query syntax analysis procedures and translation. If the stored procedure or operation of a large number of packages will increase the size of sharing pool Select * from v$parameter where name="_ftn1" 'shared_pool_size' The size of the overall system can be used to detect orders sga; show : Select * from v$sga; Oracle9i use of dynamic memory management, can change the dynamic online and share data buffer pool size. 9i use of the rapid developments in the size DB_CACHE_SIZE In the whole process : the process of connecting to Oracle database users and the creation of a conversation, automatic assignment by the Oracle. PGA major variable when programming for users with storage array. Ranking : provides for the classification requirements of the SQL memory space and system memory region for the use of specialized data classification, as part of the space sorting area. Sorting into memory, and disk sorting area. If enough memory, disk use Oracle provisional classification table space. Sort_Area_Size district for the establishment of order size. Show parameter sort_area_size; Tai Chi : shared pool for the restoration of management tools for database backup RMAN. By parameters large_pool_size Show parameter large_pool_size; Java pool established by the parameters java_pool_size The following structure : WIN process is the kind of comprehensive services, a number of process was under UNIX. Database started, start the case, the system automatically allocate SGA whole area and started a number of background processes. Oracle example of a process known as the memory regions and background. Example : examples of multi-process, multi-process, the process was divided into user process, the server process and background process. Each user has a link to the process. Permanent memory of the background process : USER (user process) \Dnnn\Listener (monitoring process) \DBWR (reading and writing process) \LGWR (log process) \ARCH (archiving process) \pmon (process monitoring) \SMON (Surveillance) CHPT (checkpoints) \RECO (restoration process), OS database in the process of total occupation of process parameters by the restrictions. Select * from v$parameter where name like '%process%'; Therefore, to increase the number of process parameters documents, but also to amend the Linux kernel parameters SEMMNS, said the distribution system will be able to process. Check user conversation information : Select * from v$process p, v$session's where p.ADDR=s.PADDR; Select p.SPID, p.USERNAME, p.BACKGROUND, s.SCHEMANAME, s.MACHINE, s.OSUSER From v$process p,'s v$session Where p.PID=s.SID; Conversation ID : sid v$session said, speaking serial# No. SER said. They are not the only form of a conversation. SCHEMANAME database users, and OSUSER, who hosts MACHINE V$process process : SPID said its operating system, OS process ID. BACKGROUND expressed similar process, said customers use the process for background BAC process. Background : The main structure of the process include : data into the process DBWR, LGWR log into the process, the inspection process CKPT point, RMON monitoring systems, process control LANs, archiving process ARCH and restoring RECO process, the process LCKn blockade, Dnnn scheduling process, the server process Snnn, SNP and other process operations. Background process can be obtained V$bgprocess 1. DBWR checkpoint : there, the process can get DBWR LGWR for write operations. A maximum of 10 can be activated DBWR data into the process, the process of DBW0 name, DBW1, DBW2 :. . DBW9. Db_writer_processes revision is the value, the range of 1-10 2. LGWR : log into the process, log log data from the disk buffer into the log file group. The database system will log all changes in information storage buffer, the buffer zone by LGWR will log the information into the disk. Subscription orders issued commit to the data was written into the log buffer. Services were submitted, the system has been given a change in its SCN, check points. 3. LANs : System Monitoring, the process to start the implementation of the examples examples restored, and is no longer responsible for the temporary use of. 4. PMON : process control, failure of the process when the user process to resume implementation process for the release of the liquidation process of memory and the use of resources. PMON was awakened with the law 5. ARCH : archiving process, which when fully log in the log information into the disk or tape medium for the failure to restore the database. Database archiving mode operation in the log before switching from ARCH process will log information into the disk or tape. ARCH 10 archiving process can be started up, the name of ARC0-ARC9, the document is revised parameters, additional parameters LOG_ARCHIVE_MAX_PROCESSES 6. CKPT : checkpoint process, which is responsible for all the data files on the system and the synchronous control of the documents in its first amendment. Database to ensure synchronization. Log_check_point_timeout decided to produce a testing point for the time interval, and every time we have a new test point and its SCN. Log_checkpoint_internal parameters of the implementation of a rapid test needs to fill the number of log files. The decision checkpoint several conditions : 1). Each log switching 2). After the designated time, the time Log_check_point_timeout 3). Express was written into the pre-defined number of log disk, automatically produce an inspection point. Log_checkpoint_internal decided by the parameters. 4) Database closed. 5). Table space when OFFLINE 7. RECO : restoration process, which for the distributed database system, RECO automatically maintain consistency of the data in a distributed environment. 8. LCKn : locking process for the next Oracle Parallel Server 9. Dnnn : scheduling process, which exists in the server multi-threaded architecture, the process for users to connect to the server process. 10. SNP : work process through DBMS_JOB automatic operation of the scheduled operation of the database package, and for distributed database, the database automatically renewable between snapshots. JOB_QUEUE_PROCESS process for the establishment of the number of operating parameters, JOB_QUEUE_INTERNAL time interval for the process of setting up operations. Background document tracking system for background information : automatic tracking process all the orders were revised database structures to track the background process, such as ALTER Database, Create TableSpace other. Alert_SID.log acquiescence acquiescence tracking paper trails : $ORACE_HOME\admin\DB_NAME\bdump\SIDALERT.log name. Background_demp_dest document tracking parameters determined by the path. User_dump_dest parameters set by the user and document tracking, document tracking users : SID_*.trc. TRACE TKPROF order to be collated document format : C:\ "tkprof Usage : tkprof tracefile outputfile [explain= [table=]] [sys= [sort=]]]] [insert= [print= Alter Session Set SQL_TRACE=TRUE online orders can be installed, set up after the automatic system of tracking user information document. DDL exclusive lock : lock wait (Exclusive DDL) from the main phrase DROP TABLE, ALTER TABLE cause. Sharing (Shared DDL) Create Procedure sentence caused mainly by lock. Bank exclusive lock that was locked outside firms, in which other firms may revise normal. If the facts were presented or regression, the locks will be untied Inquiries lock other users of the user process Select a.USERNAME, a.SID, a.SERIAL#, b.ID1 From v$session a, b v$lock Where b.ID1 in (select distinct e.id21 From v$session d, e v$lock Where d.LOCKWAIT=e.KADDR) And a.SID=b.SID And b.REQUEST=0; Alter : unlocking system mandatory session kill 'SID, SERIVAL#'; Good article, Mt. Very good for the landlord, benefited from the country The landlord must have experience of Oracle database management, and has also offered to share with you! Learning to the landlord. . . . I ah no one roof This Haotie not precision, I am sorry landlord die !~~ Collection, I would like to thank the !~~~ Ding. . Thank you share Even now, with a good thing Laughter Ding Ah, yes. Thank you. Yes. . . . . . Ding Yes, it is worth the top that although I do not understand. . Share [color=blue] Information : Once added to the database data files will not be deleted unless deleted table space. [/color] Data files can be deleted from the table inside the room, in spite of relatively trouble Thx. Yes ah Dingding Top two! ! I do not quite your heart! Save Top first, after a look :) [ Closed window ]