Oracle Advanced Training Course 1st learning notes revised the page number in this course notes corresponds to Oracle9i Database Management Basics II ed 1.1 vol.1.pdf
Guide: http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx Author: jackyang (JackYang.sh@gmail.com) Time: 2006-10-10 p151 restore (Restore) and recovery (recovery) Difference: Restore, just copy back the backup file back. Recovery not only copies the backup file back, but also restores the backup time to the restored data. Objective of p153 backup and recovery MTBF (average fault interval) MTTR (average recovery time) Minimized data loss p154 fault category: Statement fault, which usually requires no DBA intervention, p156 user process faults, which usually do not require DBA intervention. p158 user errors usually require DBA intervention. p160 network failure routine faults usually require DBA intervention. p162 media faults usually require DBA intervention, the p165 user session/user process establishes a connection first, and the Oracle server starts the session after verifying the user. Instance)
Oracle instance is a method of accessing the Oracle database. An Instance = a group of background processes + an SGA group of background processes have at least five processes, one of which is program monitor (pmon ), used to monitor user processes. SGA is a global system zone and essentially a memory structure. A group of background processes are used to manage databases. The memory structure of Oracle consists of the system global zone (SGA) and program global zone (PGA. Differences between the two: SGA is allocated when the Oracle routine is started; PGA is allocated when the server process is started; SGA is used to store database information shared by database processes (including data and control information about Oracle servers ). It is actually a large block of shared memory. The memory structure of SGA includes: Shared Pool, high-speed cache of database buffer, redo log buffer, other structures, large shared pool, Java pool, and command for viewing SGA memory allocation: SQL> show SGA; transaction (TSN) Transaction rollback/undo restore transaction the difference between p160drop table, truncate, and delete is handled by pmon. Drop table deletes table, including defining truncate to delete data, cannot be rollback, releasing space. When the data volume is large, the execution speed is fast. Delete only deletes data and can be rolled back without releasing space. When the data volume is large, the execution speed is slow. p161 uses the "time point recovery" function to restore the deleted table, but the new data after the time point is gone. Dbpitr data recovery based on time points tspitr tablespace recovery based on time points, a small range. Routine fault Oracle running mode Shared Mode crash recovery routine fault recovery medium fault Use Backup Recovery
38 is the fault point, 32 is the sequence number (Log File number) of the redo log, and 32 to 37 is the archived redo log file; p167 defines the backup and recovery policies. Creating backup and recovery policies is not a matter of one person, but related to the entire company and management. It is closely related to business requirements and the decision of the boss. The operation requires testing and verifying the backup on the cloned dB, simulating the fault, and restoring the database. OLTP (online transaction process) Data Warehouse is used for DSS (Decision Support System) p173 disaster recovery (disaster recovery), which is much higher than backup requirements. Many Oracle built-in disaster recovery uard mechanisms are used for disaster recovery. It is actually a remote archiving mechanism. P179 reviews the concepts mentioned in Oracle9i Database Management Foundation I and focuses on backup and recovery requirements. The SGA (system global area) system global area query execution plan is used to optimize the performance of SQL statements in Oracle. Similarly, SQL statements must be defined in the same format. They must contain no more or less spaces and be case-insensitive. Redo log buffer (redo log buffer) for retransmission, database recovery online redo log file check point (ckpt) to ensure that the system submission number (SCN) of data file and control file is consistent. P179oracle basic components overview. The figure above shows the instance (routine), logical part; composed of the memory structure (SGA) and background processes; the figure below shows the Oracle (physical) database, physical part; the Oracle Server includes an oracle routine and an Oracle database. The relationship between Oracle routines and Oracle databases. An Oracle routine always opens one and only opens one Oracle database. P180 Memory Structures and descriptions of various background processes. The memory structure (SGA) includes: Database buffer high-speed buffer, log buffer, large shared pool, shared pool, and Java pool. Background processes include: Database writer (dbwn), log writer (lgwr), system monitor (SMON), Process Monitor (pmon), checkpoint (ckpt), archive program (arcn ). P181oracle databases are generally used to store and retrieve relevant information. Contains three types of files: data files, redo log files, and control files. Other Oracle server files include parameter files, password files, and archived redo log files. Oracle uses multi-process to improve concurrency. P181 when oracle is working in dedicated server mode, a user process is directed to a server process. When oracle is working in Shared Server mode, multiple User processes share one server process. The initialization parameter file is used to initialize the instance. Most parameters have default values. P181the standard view provided by the Oracle server to obtain information about databases and routines: V $ SGA, V $ instance, V $ process, V $ bgprocess, V $ database, and V $ datafile p182 large shared pools use the large_pool_size parameter to set the size of large shared pools. Determine how to use a large shared pool and query v $ sgastat. The condition pool = 'large pool 'p184dbwn is only responsible for writing dirty data (gray data) in the database buffer to the data file, make the two sides consistent. Dirty data (gray data) is a modified data that can be configured with multiple dbwn to improve performance, but does not work for a single processor system. The checkpoint process will notify dbwn and lgwrdbwn to execute each other for a long time. The large data volume lgwr is executed every moment, and the small data volume commit is used to make permanent changes to the database; the lgwr is executed during commit, dbwn does not execute, so the execution speed of commit is very fast. The P185 tablespace creation policy p187 has two redo log groups of the same size and is divided into two hard disks. P188 dynamic attempts related to redo logs include: V $ log, V $ logfile, and V $ log_history p189. When 3rd redo log groups are used up and recycled to 1st redo log groups, check Point and archive (in archive mode ). Archiving is a synchronization operation, so you need to wait. Non-archive mode: Generally, two groups of archive modes are used. Generally, three groups of each group must have at least two redo days to members. Each member is located on a different disk to prevent faults. P190 the method for locating the redo log file to add a member to the redo log group after removing the Member from the redo log group p192 Checkpoint Check Point, dirty data in the data synchronization buffer is written to the data file. The three types of check points are related to the version. After version 9.2, there are all three types of check points. Full checkpoint incremental checkpoint part of the checkpoint alter table space begin backup only backup the table space. The control file is very important, so it should be placed on two hard disks respectively. Reset log enables the sequence number of the log file to start from 1. If archiving is too late, the lgwr process will wait. Since rollback writes uncommitted data to a data file, rollback is required after rollback. MTTR only indicates the rollback time, excluding the restoration time. If the check point is frequent, the recovery time is short, but the normal running speed of the database is reduced. Check Point can be automated or customized. P196 provides a dynamic view of the location and name of the control file: three methods for creating a database: V $ parameter and V $ controlfile. 1. when installing Oracle, follow the steps below to create a database 2. use dbca to create a database. the first two methods for manually creating databases using create database are suitable for small and medium databases. The last method is suitable for medium and large databases because many parameters can be set.
In
Oracle10g
To manually create a database in: See chapter 4 "create database" in Oracle9i database management basics I ed 1.1 vol.1.pdf ".
Guide:
Http://blog.csdn.net/magus_yang/archive/2006/10/10/1328283.aspx
1. assume that the installation directory of Oracle10g is D:/ora10g. the SID of the database created is db3 2. create the following directory: D:/ora10g/oradata/db3 D:/ora10g/oradata/db3/udump D:/ora10g/oradata/db3/cdump D: /ora10g/oradata/db3/bdump D:/backup/db3d:/backup/db3/arc archive D:/backup/db3/hot standby D: /backup/db3/cold backup 3. copy the initialization parameter file initdb1.ora to D:/ora10g/database/initdb3.ora and modify the initdb3.ora file. The content to be modified is: Change db1 to db3; change all routes; Use uppercase letters for several names. The modified content of the initdb3.ora file is as follows: background_dump_dest = 'd: /ora10g/oradata/db3/bdump 'compatible = '10. 1.0.2.0 'control _ FILES = ('d:/ora10g/oradata/db3/con1.ctl ', 'd:/ora10g/oradata/db3/con2.ctl') core_dump_dest = 'd: /ora10g/oradata/db3/cdump 'db _ name = 'db3' # dispatchers = '(Protocol = TCP) (Service = gdb1) 'remote _ login_passwordfile = 'clusive 'sessions = 20undo_management = 'auto' undo _ tablespace = 'Undotbs 'user _ dump_dest = 'd:/ora10g/oradata/db3/udump 'Log _ archive_dest_1 = 'location = E: /backup/db3/arc'log _ archive_format = '% S _ % T _ % R. arc 'service _ names = 'db3' instance _ name = 'db3' where: log_archive_dest is the archive object, log_archive_format is the archive format, and the initialization parameter file naming rule is initsid. ora 4. modify credb10g. the content to be modified in the SQL file is: Change db1 to db3; change all processes; and change credb10g. the modified SQL file is as follows: Create Database db3maxinstances 8 maxloghistory 1 maxlogfiles 16 maxlogmembers 3 maxda Tafiles 100 logfile group 1 ('d:/ora10g/oradata/db3/redo01a. log', 'd:/ora10g/oradata/db3/redo01b. log ') Size 10 m, group 2 ('d:/ora10g/oradata/db3/redo02a. log', 'd:/ora10g/oradata/db3/redo02b. log ') Size 10 mdatafile 'd:/ora10g/oradata/db3/system01.dbf' size 220 m autoextend on next 10 m extent management localsysaux datafile 'd: /ora10g/oradata/db3/sysaux01.dbf 'size 45 m autoextend on next 10 mdefault tempora Ry tablespace temptempfile 'd:/ora10g/oradata/db3/temp. DBF 'size 25 mautoextend on next 10 Mundo tablespace undotbs datafile 'd:/ora10g/oradata/db3/undotbs. DBF 'size 30 m Character Set zhs16gbknational Character Set al16utf16user sys identified by ora123user system identified by ora123; among them, sysaux is a new auxiliary tablespace of 10 Gb. 5. Set the environment variable D:/> set oracle_sid = db3 Sid must be capitalized, and spaces are not allowed on both sides of the equal sign. If you do not need to set this environment variable offline through network connection, you need to set this environment variable. If you need to switch between 9i and 10g environment variables, you can create a batch file setenv. bat. The file content is as follows: Set ORACLE_HOME = D:/ora9iset Path = D:/ora9i/bin; % PATH % 6. this step is required to create the Oracle service process windows, but Linux does not need to perform this step d:/> oradim-New-Sid db3-intpwd ora123-startmode auto instance created, where: ora123 is the initial password-startmode auto option. After creation, choose Control Panel> Administrative Tools> services to check whether the Oracle service process is successfully created, the service process name should be oracleservicedb3. In addition, a password file is automatically generated under the database directory of the Oracle installation directory. 7. D:/> sqlplus/nologsql> connect sys/ora123 as sysdbasql> startup nomountoracle instance started. At this time, SGA has been created. 8. Create Database SQL> @ credb10gdatabase created if the credb10g. SQL file is not in the current directory, you need to bring the absolute path. At this time, only the internal table of the data dictionary is created (this is the ancestor of all tables), and the view of the data dictionary has not yet been created. 9. SQL> @? /Rdbms/admin/CATALOG; generate the data dictionary view? # Indicates that the output of the ". SQL" command is PL/SQL procedure successfully completed by default after the catalog of the Oracle installation directory. The following statement can verify whether the database is successfully created. SQL> select name from V $ controlfile; SQL> select * from V $ log; 10. SQL> @? /Rdbms/admin/catproc; Generate System packages in dB, including the built-in PL/SQL package, which is very important and time-consuming.
