Database security, integrity, concurrent control, and recovery
In order to ensure the security and reliability of the database data and the right and effective, DBMS must provide a unified data protection function. Data protection is also data control, mainly including the security, integrity, concurrent control, and recovery of the database.
First, the security of the database
The security of the database refers to the protection database to prevent unauthorized use of data leaks, changes or destruction. There is this problem in the computer system and stored in a large amount of data in the database system, sharing many users, making security issues more prominent.
In a general computer system, security measures are set at first level.
Password technology can be adopted in DB storage. When the physical storage device is stolen, it functions. Two controls are available in the Database System: User ID and Identification, Data Access Control.
In the Oracle multi-user database system, the security machine is used as the following work:
l Prevent unauthorized data inventory;
l Prevent an unauthorized access to mode objects;
l Control disk use;
l Control system resource use;
l Auditing user action.
Database security can be divided into two categories: system security and data security.
System security refers to the mechanism of access and use of the system-level control database, including:
l The combination of valid username / password;
l Is a user authorized to connect to a database;
l The number of disk space available for user objects;
l Users' resource limit;
l Whether the database audit is effective;
l Which system operations can be implemented.
Data security refers to the mechanism of access and use of an object-level control database, including:
l Which users can access a specified mode object and which operation types allowed on an object.
An arbitrary access control is provided on the Oracle server, which is a method based on privileged information access. The user must access an object must have the corresponding privilege to give the user. Authorized users can authorize it to other users, for this reason, this security type is arbitrary.
Oracle uses the following mechanisms to manage database security:
l Database user and mode;
l privilege;
l role;
l Storage settings and spatial shares;
l resource limit;
l Audit.
1. Database Access Control
The method of Oracle protection information uses any access control to control the access of all users to named objects. User access is privileged for access. A privilege is a license for accessing a naming object, which is a specified format.
Oracle uses a variety of different mechanisms to manage database security, with two mechanisms: mode and user. The pattern is a collection of pattern objects, and the mode objects are like tables, views, procedures, and packages, etc. The first database has a set of patterns.
Each Oracle database has a set of legitimate users, accessing a database, running a database application and using the user connected to a database defining the user. When a database user is established, the user creates a corresponding mode, the mode name is the same as the username. Once the user connects a database, the user can access all objects in the corresponding mode, and one user is only linked to the mode of the same name, so users and patterns are similar.
The user's access rights are controlled by the settings of the user security domain. When establishing a new user or change a user, the security administrator has the following decisions to the user security domain:
l is a database system or a user authorization information by an operating system.
l Set the user's default table space and temporary table space.
l Lists the user-saving tablespace and the spatial share can be used in the tablespace.
l Set the environmental file that the user resource limit, which specifies the total amount of system resources available for users. l Specify the privileges and roles of the user to access the corresponding object.
Each user has a security domain. It is a set of features that determine the following:
l Users available privileges;
l The share of the table space available;
l System resource limitations of users.
1) User identification:
In order to prevent the use of unauthorized database users, Oracle provides two confirmation methods
Operating system confirmation and corresponding Oracle database confirmation.
If the operating system allows, Oracle can use the information maintained by the operating system to identify the user. The advantages of identifying users by operating systems are:
l Users can more easily connect to Oracle and do not need to specify the username and password.
l Controls for users' authorized controls in the operating system, Oracle does not need to store and manage user passwords. However, the username still has to maintain in the database.
l corresponds to user names and operating system audit trails in the database.
User confirmation of Oracle Database Method: Oracle uses information stored in the database to identify a user attempting to pick up the database, this authentication method is only used when the operating system cannot be used for database user authentication. Perform user authentication when a user uses an ORACLE database. Each user has a password when it is created, and the user password is used to connect to the database connection to prevent unauthorized use of the database. The user's password is stored in the database data dictionary in a password, and the user can modify its password at any time.
2) Users' table space settings and quotas
Several settings for the use of table spaces:
l User's default table space;
l Use the temporary table space of the user;
l The space usage quota in the database table space.
3) User resource limitation and environmental files
The total amount of various system resources available for users is part of the user security domain. Use explicitly set resource restrictions; security administrators prevent users from consuming valuable system resources without controlling. Resource restrictions are managed by environmental files. An environmental file is a set of resource restrictions that have been named. In addition, Oracle provides security administrators to deliver enable or enable selection of environmental file resource restrictions.
Oracle can limit the use of several types of system resources, each resource can be controlled on session level, call level, or both. In the session level: Every time the user is connected to a database, establish a session. Each session is restricted on the computer that consumes CPU time and memory on the computer of the SQL statement. Several resource limits for Oracle can be set on the session level. If the session-level resource restriction is exceeded, the current statement is aborted (roll back) and returns the information that the session limit has been reached. At this point, all previously executed statements are not affected, and only other operations will be made by COMMIT, ROLLBACK, or the connection to the database.
In the call level: When executed in the SQL statement, handling this statement, there is a few steps, in order to prevent excessive calling system, Oracle can set several resource restrictions in the call level. If the resource restriction of the call level is exceeded, the statement processing is stopped, and the statement is rolled back and returns an error. However, the statements of the current transaction have not been affected, and the user session continues to connect.
Have the following resource restrictions:
l In order to prevent uncontrolled use of CPU time, Oracle can limit the CPU time each Oracle call and the CPU used in the Oracle call during a session, in 0.01 seconds.
l In order to prevent excessive I / O, Oracle can limit the number of logical data blocks read for each call and each session.
l Oracle also provides several other resource restrictions in the session level.
The number of parallel sessions of each user;
The restriction of session free time, if the time between the Oracle calls of a session reaches the idle time, the current transaction is rolled back, the session is aborted, and the session resource is returned to the system;
Each session can disappear limit, if the restriction of the captive time is exceeded during a session, the current transaction is rolled back, the session is deleted, and the resource of the session is released;
The amount of special SGA space for each session.
User environment file:
The user environment file is a nameset that specifies the resource restriction, which can be valid for the Oracle database. The use of user environment files can easily manage resource restrictions. To use user environment files, first you should classify users in the database, decide how many user environment files are needed in the database. Before establishing an environmental file, determine the value of each resource limit. For example, a class of users typically do not perform a large number of logical data block read, which can set the corresponding value of the logical-reads-pre-session and Logical-Reads-PER-CALL. The best way to determine the appropriate resource restriction of an environment file of a user in many cases is to collect historical information used for each resource.
2. Privileges and roles
1) Privilege: Privilege is to perform a special type of SQL statement or the power to access another user. There are two types of privileges: system privileges and object privileges.
System privilege: Yes, performing a special action or performing a special action on an object type. Oracle has more than 60 different system privileges, each system allows users to perform a special database operation or a class of database operations.
System privileges can be licensed to users or roles, general, system privilege managers and application developers, end users do not need these related functions. Authorization to a user's system privilege and have the system privilege to authorize other users or roles. Converse, You can recycle system privileges from those authorized users or roles.
Object privilege: The right to perform special actions on the specified table, view, sequence, process, function, or package. For different types of objects, there are different types of object privileges. For some mode objects, such as aggregation, indexing, triggers, database chains are not related to object privileges, they are controlled by system privileges.
For objects contained in a mode in a user name, the user automatically has all object privileges, ie the object in the mode has all object privileges. The holders of these objects can authorize any object privileges on these objects to other users. If the subject contains the Grant Option authorization, the subject can also authorize its rights to other users.
2) Roles: Named groups for related privileges, can be licensed to users and roles. ORACEL is more easily privileged with roles. Have the following advantages:
l Reduce privilege management, do not explicitly authorize the same privilege group to several users, just give this privilege group to the role, then authorize the role to each user.
l Dynamic privilege management, if a set of privileges need to change, just modify the privilege of the role, all security domains that give all users to the role will automatically reflect the modifications to the role.
l Select availability of privileges, authorized to give the user to enable (available) or unable (not available).
l Apply an artistic that when a user performs an application by a username, the database application can query the dictionary, which will automatically select the role or not.
l Special application security, role use can be protected by password protection, application provides the correct password usage role enable, achieving dedicated application security. Because users don't know their password, the role cannot be enabled.
Generally, establish a role to serve two purposes: administrate privileges for database application management privileges for user groups. The corresponding role is called the application role and the user role.
The application role is all privileges required to grant a database application. An application role can give other roles or specify users. One application can have several different roles, each role of different privilege groups can perform different data access when using applications. User roles are established for a set of database users with public privilege requirements. User privilege management is controlled by the application role or privilege authorization to the user role, and then authorizes the user role to the corresponding user.
Database roles include the following features:
l A role can grant system privilege or object privilege.
l A role can be authorized to other roles, but cannot be looped.
l Any role can be authorized to any database user.
l You can be enabled or unable to authorize each of the users. A user's security domain contains only privileges that currently enable all roles of the user.
l An indirect authorization role (the role of authorization to another role) can explicitly or can't make it.
In a database, each role name must be unique. The role name is different from the user, and the role is not included in any mode, so the user who creates a role is deleted without affecting the role.
Oracle In order to provide compatibility with previous versions, Oracle predefined the following roles: Connent, Resource, DBA, Exp-Full-Database, and Imp-Full-Database.
3. Audit
The audit is a monitoring and record of the selected user action, usually used in:
l Review suspicious activities. For example, the data is deleted by the unauthorized user, at which time the security administrator can determine all connections to the database, and the audit is successfully or unsea-deleted for all tables of the database.
l Monitor and collect data about the specified database activity. For example: What is modified to collect, how many logical I / O is executed.
Oracle supports three audit types:
l Statement Audit, audit of some type of SQL statement, no structure or object.
l Privilege Audit, the use of system privileges to perform the corresponding action.
l Object Audit, audit of specified statements on a special mode object.
Oracle's audit options are limited to the following aspects:
l The success of the audit statement, unsuccessful execution, or both.
l Each user session audit statement is executed once or a statement is performed once each time.
l Audit on all users or the active user.
When the audit of the database is enable, audit records are generated during the statement execution phase. The audit record contains information with an audit operation, the operation of the user, the date and time of operation. The audit record can exist data dictionary tables (called audit records) or operating system audit records. The database audit record is in the SYS mode AUD $ table.
Second, data integrity
It refers to the correctness and compatibility of data. The integrity of data is to prevent data inventory from informing data, prevent error information input and output, ie data to comply with a set of predefined rules determined by DBA or application developers. Oracle's data integrity of tables for relational databases has the following types:
l When the row inserted or modified the table is allowed to contain columns, a column containing a null value is called empty and non-empty rules.
l The only column rule, allowing the value of the table line to be inserted or modified to the value on the column.
l Reference integrity rules, customization model definition
l Users to define the rules for complex integrity.
Oracle allows definition and implementation of each type of data integrity rule that can be defined by integrity constraints and database triggers.
Integrity constraints are illustrative methods for defining a rule of the table.
The database trigger is to use a non-explanatory method to implement integrity rules, which utilizes a database trigger (stored database procedure) to define and implement any type of integrity rule.
1. Integrity constraint
Oracle uses an integrity constraint mechanism to prevent invalid data to enter the database's base table, if any DML execution result destroys the integrity constraint, the statement is rolled back and returns an error. The integrity constraints achieved by Oracle fully complies with ANSI X3.135-1989 and ISO9075-1989 standards. Use the integrity constraint to implement the data integrity rules with the following advantages:
l When you define or change a table, you don't need programming, it is easy to write programs and eliminate procedural errors, which are controlled by Oracle. So the illustrative integrity is better than the application code and database trigger.
l The integrity constraint defined by the table is stored in the data dictionary, so the data entered by any application must comply with the integrity constraint associated with the table.
l has the greatest development capabilities. When the transaction rules implemented by integrity constraints, the administrator only needs to change the definition of integrity constraints, all applications automatically comply with the modified constraints.
l Since the integrity constraints are stored in the data dictionary, the database application can use this information, and then feedback immediately before the SQL statement is executed or by Oracle check.
l Because the semantics of integrity constraints are clearly defined, performance optimization can be achieved for each specified instruction rule.
l Since the integrity constraints can be temporarily unable to prevent the overhead of restraint retrieval when loading a large amount of data. When the database is completed, the integrity constraint can be easily enabled, any new row that destroy integrity constraints is listed in the exception table.
Oracle's DBA and Application Starters Enter the value of the values of the columns. The integrity constraints available:
l NOT NULL Constraint: If the value of a column of the table is not allowed to be empty, you must specify the Not Null constraint in this column.
l UNIQUE code constraint: When the two lines not allowed on the column or group listed on the table, the UniQue code integrity constraint is required to specify the unique code on the column or group. Columns or group columns in UNIQUE code constraints are called unique codes. All unique integrity constraints are implemented with an index method.
l Primary Key Constraint: Each table in the database can have a Primary Key constraint. The column or group column included in the Primary Key integrity constraint, each table can have a master code. Oracle uses the index to implement the Primary Key constraint.
l Foreign Key Constraint (confiferential reference constraints): Table in relational databases can be associated with public columns, which controls the relationship between columns that must be maintained. The columns included in the reference integrity constraint definition are called a foreign code. The unique code or codes in the table referenced by the outer code are called a reference code. A table containing a foreign code is a subtray or a slave table. A representation of a dual-pro-table or a reference table is referred to by the sub-table. If each row of the table, the value of the external code must match a value in the main code, then specify reference integrity constraints.
l CHECK Constraint: The table of each row must be true or unknown to a specified condition, you need to specify Check integrity constraints on one or column groups. This statement is rolled out if the CHECK constraint is calculated when a DML statement is issued.
2. Database trigger
Oracle allows the definition process that these processes are implicitly executed when the relevant form is INSERT, UPDATE, or DELETE statement. These processes are called database triggers. The trigger is similar to the stored process, which can include SQL statements and PL / SQL statements that can be called other stored procedures. Process and trigger differences in calling methods: The process is explicitly executed by the user or app; the trigger is implicitly triggered by Oracle (INSERT, UPDATE, DELETE). A database application can implicantly trigger multiple triggers stored in the database.
In many cases, the trigger supplements the standard function of Oracle and provides a highly dedicated database management system. General trigger for:
l Automatically generates the export value. l Prevent invalid affairs.
l Implement complex security audits.
l Implement the reference integrity of the cross-knot in the distributed database.
l Implement complex transaction rules.
l Provide transparent event records.
l Provides advanced audits.
l Maintain the synchronized table copy.
l Collect statistics on the table access.
Note: In the Oracle environment, you can use Oracle tool SQL * Forms to also define, store, and execute triggers, which are part of the application developed by SQL * Forms, which is different from the database trigger defined on the table. The database trigger is defined on the table, stored in the related database, which will cause the database trigger to execute when IMSERT, UPDATE, and DELETE statement, whether it is to send these statements. The SQL * Forms trigger is the composition of the SQL * Forms application, which is only excited when specifying the trigger point in the specified SQL * Forms application.
A trigger consists of three parts: trigger event or statement, trigger limit, and trigger actions. Trigger events or statements refer to the SQL statement of the excitation trigger, which can be the INSERT, UNPDATE, or DELETE statement of a specified table. The trigger limit is a Boolean expression that the Boolean expression must be true when the trigger is activated. The trigger is a process, which is a PL / SQL block. When the trigger statement is issued, the trigger limit is calculated as true, the process is executed.
3. Concurrent control
The database is a shared resource that can be shared by multiple applications. These programs can run serial serial, but in many cases, since the amount of data involved in the application may be large, it often involves the exchange of input / output. To effectively utilize database resources, you may run multiple processes or multiple processes of a program in parallel, which is a parallel operation of the database. In multi-user database environments, multiple user programs can access databases in parallel, if the concurrent operation is not controlled, incorrect data, or disrupt the consistency of database data.
Example: In the flight ticket ticket, there are two booking staff (T1, T2) for a motor ticket for a route (a), the operation process is shown in the figure:
A in the database
1
1
1
1
0
0
T1
READ A
A: = a-1
Write a
T2
READ A
A: = a-1
Write a
T1 A
1
1
0
0
0
0
T2 workspace A
1
1
0
0
0
First T1 read A, then T2 is also read. Then T1 reduces the A 1 in its workspace, and T2 also takes the same action, and they have 0 values, and finally write the 0 value back to the database. There is no illegal operation in this process, but there is actually one ticket. This situation is called the database of the database, which is due to parallel operation. The so-called inconsistency is actually caused by the data in the processing program workspace and the data in the database is inconsistent. If the handler does not modify the data in the database, it will never cause any inconsistency. On the other hand, if there is no parallel operation, this temporary inconsistency will not cause any problem. Data is inconsistent to be caused by two factors: one is the modification of the data, and the other is in parallel operation. Therefore, in order to maintain the consistency of the database, parallel operation must be controlled. The most common measures are blocking the data.
1) Types of inconsistent databases
l inconsistent
During a transaction, other modifications of other submitted or not submitted transactions are obvious, so that the data set returned by the query is not consistent with any point.
l can not read
Within a transaction, two identical queries will return different data, which is caused by the query noted changes to other transactions. l Read dirty data
If the transaction T1 modifies a value (a), then the transaction T2 reads this value, after which T1 is revoked for some reason to revoke the value of the value, the value of T2 read is dirty.
l Lost change
A modification of another transaction is rewritten in a transaction, such as the above flight ticket ticket.
l Destructive DDL operation
When a user modifies the data of a table, another user changes or deletes the table at the same time.
1) Block
Some data blockers are generally used in multi-user databases to solve data consistency and integrity issues in concurrent operations. Blocking is a mechanism to prevent destructive interference between users from accessing the same resource, which means that the data is incorrectly modified or incorrectly changing the data structure.
Use two blockade in multi-user databases: row it (dedicated) blockade and shared blockade. It blocks the sharing of relevant resources, if a transaction blocks a resource, only this transaction can change the resource until it is released. The shared blocking allows the relevant resources to be shared, several users can read the same data at the same time, and several transactions can get shared blockers on the same resource. Shared blocking is compared to seal it to block a higher data parallelism.
There will be a deadlock after using a blockade in a multi-user system, causing some transactions that cannot continue to work. A deadlock can occur when two or more users wait for each other to be sealed.
2) Oracle variety of consistency models.
Oracle provides data concurrency and data integrity using transactions and blocking mechanisms. All blocks acquired by the statement within a transaction are held during the transaction to prevent other parallel transactions from destructive interference. The modification of a transaction SQL statement is visible in the transaction started after it is submitted. All blocks acquired by the statement in a transaction are released in the transaction.
Oracle provides read consistency in two different levels: statement level read consistency and transaction-level consistency. ORCLE always implements the statement-level read consistency to ensure that the data returned by a single query is consistent with the query start time. So a query will never see any modifications made by other transactions submitted during the execution of the query. In order to implement statement level reading consistency, when the query enters the execution phase, the data submitted so far when the SCN is gazing, and any modifications submitted after the statement execution starts, the query will not be seen.
Oracle allows you to select transaction-level read-consistency, which guarantees data in all queries in the same transaction
4) Blocking mechanism
Oracle automatically uses different blockade types to control data from parallel access to prevent destructive interference between users. Oracle automatically blocks a resource for a transaction to prevent other transactions to block it to the same resource. Automatically release it when some event appears or transaction no longer needs this resource.
Oracle divides the blockade into the following categories:
l Data blockade: Data blockade protection table data, guarantees the integrity of data when multiple users are parallel. Data blockade prevents disruptive interference from the conflict of DML and DDL operations. DML operations can get data blockade at two levels: Specify line blockade and entire table blocking, and the table block is also required when the conflict is prevented. When the row is to be modified, the transaction gets the data blockade in this line. The table blockade can have the following way: row sharing, row, sharing blockade, shared rows it and row it.
l DDL blockade (dictionary blockade)
DDL blocking protection mode objects (such as tables) definitions, DDL operations will affect objects, and a DDL statement implicitly submits a transaction. When any DDL transaction needs to automatically get a dictionary blockade by Oracle, the user cannot explicitly request DDL blockade. The modified or reference mode object is blocked during DDL operation.
l Internal blocking: protects the internal database and memory structure, which is invisible to the user.
5) Handmade data blockade
The following conditions allow for the use of the selection instead of the Oracle default blockade mechanism:
l Application requires transaction-level reading or repeatable read.
l Application requires a transaction to a resource to access it. In order to continue its statement, there is no need to have other transactions to complete.
Oracle automatic blockade can be replaced at secondary: transaction grade system level.
l Transaction level: Transaction containing the following SQL statements replace the Oracle default blockade: lock table command, select ... for update command, a set transactin command with the read only option. The blockade obtained by these statements is released after the transaction is submitted or returned.
l System level: The instance can be started with a non-default blockade by adjusting the initialization parameters Serializable and Reo-Locking. The default value of the two ginseng data is:
Serializable = false
ORW-LOCKING = always
4. Database backup and recovery
When we use a database, always hope that the content of the database is reliable, correct, but due to the fault of computer systems (hardware failure, software failure, network fault, process failure and system failure) affects the operation of the database system, affecting the database The correctness of the data, even disrupts the database, so that all or part of the data in the database is lost. Therefore, after the above fault occurs, it is desirable to re-establish a complete database, which is called database recovery. The recovery subsystem is an important part of the database management system. Recovery processing changes with the structure of the fault type that occurs.
1) Restore the structure used by the database
Oracle Database Use several structures to protect data on possible failures: database backup, log, rollback segment, and control files.
The database backup is composed of an operating system backup that makes up the physical file of the Oracle database. Database recovery is performed when the media is fault, and the desired data file or control file is restored by the backup file.
Logs, every Oracle database instance is provided, and all modifications made in the database are recorded. The log of an instance consists of at least two log files. When the instance failure or media failure is restored, the change in the database log is applied to the data file, modify the time of the database data to the fault. Database logs consist of two parts: online logs and archive logs.
Each running Oracle database instance has an online log, which works with the Oracle background process LGWR and immediately records all modifications made by this instance. Online log consists of two or more expected files, in a loop mode.
The archive log is an optional, an Oracle Database Instance Once the online log is filled, an archive file of the online log can be formed. The archived online log file is uniquely identified and synthesized.
Rollback segments are used to store the old values of the values modified by the ongoing transaction (uncommitted transaction), which is used to undo any non-commit changes during database recovery.
Control files, typically used to store the physical structure of the database. Some status information in the control file is used to guide Oracle during instance recovery and media recovery.
2) Online log
Each instance of an Oracle database has a associated online log. A online log consists of multiple online log files. The online log file fills in the log entry, and the data recorded by the log entry is used to reconstruct all modifications made to the database. The background process LGWR is written in a loop manner. When the current online log file is full, LGWR is written to the next available online log file. You can use it when the checkpoint of the last available online log file is completed. If the archive is not implemented, a filled online log file is completed when the checkpoint containing the online log file is completed, and the file can be used after being archived. At any time, there is only one online log file to be written to the storage log entry, which is called an active or current online log file, and other online log files are inactive online log files.
ORCLE ends writing a online log file and start writing to another online log file. The log switch is fully filled with the current online log file, and you must continue to write to the next online log file, or you can also enforce the log switch by the DBA. Each log file appears, each online log file is assigned to a new log serial number. If the online log file is archived, it contains its log serial number in the archive log file. Oracle Background Process DBWR (Database Write) Write all modified database buffers in the SGA to data files, such events are called a checkpoint. Check points are achieved for the following reasons:
l Checkpoint to ensure that the data segment blocks that are often changed in memory are written to the data file every other time. Since DBWR uses the most recent use algorithm, the frequently modified data segment block will never use the minimum number of blocks, if the checkpoint does not appear, it never writes to disk.
l Since all database modifications that have been recorded until checkpoints, the log item that is prior to checkpoints is no longer needed to be applied to the data file when the instance is restored, so the checkpoint can speed up the instance recovery.
Although the checkpoint has some overhead, Oracle does not stop the activities and does not affect the current transaction. Since DBWR constantly writes a database buffer to disk, a checkpoint does not have to write a number of data blocks at a time. A checkpoint guarantees that all modified data blocks since the previous checkpoint have been written to the disk. Check points whether or not the online log file is archive, it always appears. If archive is implemented, the checkpoint must be completed before the LGWR reuses the online log file, and the filled online log file must be archived.
Checkpoints can appear on all data files of the database (called database checkpoints), or you can appear on the specified data file. Here's how do you have a checkpoint and what happens:
l A database checkpoint automatically appears at each log switch. If the previous database checkpoint is being processed, the checkpoint implemented by the log switch is better than the current checkpoint.
l Initialize the reference data log-checkpoint-interval settings implemented database checkpoints, and implement a database checkpoint after the predetermined log block is filled (since the last database checkpoint). Another parameter log-checkpoint-timeout can be set to implement a database checkpoint after the number of seconds after the start of the last database checkpoint. This option is useful to use a very large log file, which increases the checkpoint between the beginning of the log. The database checkpoint initiated by the initialization parameter is only started after the previous checkpoint is completed.
l When the backup is started in the line table, only the checkpoint constituting the data file is implemented, and the checkpoint is still in progress any checkpoint.
l When the DBA offlines a table space, only the online file constituting the table space is implemented.
l When the DBA is turned off in normal or immediately, the Oracle implements a database checkpoint before the instance is turned off, and the checkpoint is overwhelmed. Any run checkpoint.
l DBA may request a database checkpoint, which is overwhelming any run checkpoint.
Checkpoint mechanism: When the checkpoint appears, checkpoint the background process remembers the location of the next day of the online file, and notifies the database write the background process to write data files to the Database buffer modified in the SGA. . Then modify the header of all control files and data files by CKPT, reflecting the last checkpoint. When the checkpoint does not occur, DBWR will only write only the least least used database buffer to disk when needed, and prepare buffers for new data.
Mirror icon online log file: In order to secure an online log file image of the instance to its online log file Oracle provides mirror icon feature. When there is a mirror icon online log file, the LGWR simultaneously writes the same log information to a plurality of the same online log files. The log file is divided into groups, and the log files in each group are called members, all members in each group simultaneously active, assigned by LGWR to the same log serial number. If you use a mirror online log, you can create an online log file group, and each member of the group requires the same size. Mechanism of mirror online log: LGWR is always looking for all members of the group, written to all members of a group, and then transform all members of the next group, written in parallel.
Each database instance has its own online log group, which can be a mirror or not, called an online log clue of an instance. In a typical configuration, a database instance accesss an Oracle database, so only one clue exists. However, in running the Oracle Parallel Server, two or more instances are paid in parallel, in which case each instance has its own clues.
3) Archive log
Oracle To archive the filled online log file group, you have to establish an archive log, or weigh offline log. It has the following uses for database reserve and recovery:
l Database backups and online and archive log files, which can ensure that all submitted transactions can be restored in the operating system or disk failure.
l When the database is open and the normal system is used, if the archive log is permanent, the online backup can be made and used.
If the user database requires no data in any disk failure event, the archived log must exist. Archive Filled Online log files may require DBA to perform additional management operations.
Archive mechanism: Decisive to archive settings, the mechanism for archive the online log group can be automatically archived by the Oracle Background Process Arch or by the user process. When the log group becomes inactive, the log switch indicates when the next group is completed, and the Arch can archive a group to access any or all of the members of the group to complete the archive group. After the online log file archive can be reused for LGWR. When archiving, you must specify an archive target to point to a storage device, which is different from a device with data files, online log files, and control files, ideal, permanently move the archive log file to offline storage devices, such as tape.
The database can run in two different ways: noarchiveLog mode or ArchiveLog mode. The database cannot be filed in the NOARCHIVELOG mode. In the database control file indicated that the filled group does not need to archive, one is completed by the constitution of the log switch, and the group can be reused by LGWR. In this way, only the database instance failure can be protected, and the medium (disk) fault cannot be protected. Using information stored in the online log, instance failure recovery can be implemented.
If the database is in the ArchiveLog mode, the archive of the online log can be implemented. The log file group that is filled in the control file cannot be reused before archiving. Once the composition is inactive, the process of performing the archive can immediately use the group.
At the start of the instance, the Arch process can be activated by the parameter log-archive-start setting, otherwise the ARCH process cannot be started at the instance startup. However, DBA can initiate or stop automatically archive in relying on it. Once the online log file group is changed, the ARCH process automatically archives it.
If the database is running in the ArchiveLog mode, the DBA can manually archive the unacceptable log file group, regardless of the automatic archiving or not.
4) Database Reserve
Regardless of the backup or recovery mode, database data files, log files, and control files for Oracle databases are absolutely needed, which is the policy part of protecting media faults. The operating system is backup with complete rear preparation and part of the backup L complete backup: a full backup will constitute all database files of the Oracle database, an operating system backup of online log files and control files. A complete reserve is performed after the database is properly closed, and cannot be performed after the instance is faulty. At this point, all files constituting the database are closed and consistent with the current point. You cannot perform full backup when the database is opened. It is useful in any type of media recovery mode by a fully retrofed data file.
l Partial backup
Partial rear for any operating system backup other than completely, can be performed under the database open or off. Such as a single table space reserve, a single data file backup and control file backup. Some backups are only available to the archivelog mode, because the archive logs existing, the data file can be restored by some of the backup. The restoration is consistent with the restoration of the restoration.
5) Database recovery
l Recovery of instance failures
An example failure occurs when an example unexpectedly (such as power down, background process failure, etc.) or is expected to abort (issuing a shutdoum abort statement), which requires instance recovery. Case Recovery is consistent with transactions before recovering a database. If an instance failure is discovered online, the media is recovered. In other cases Oracle starts instance recovery when it is started at the next database (assembled and opened). If necessary, from the assembly state to the open state, automatically excit the instance recovery, by the following processing:
(1) For the data that is not recorded in the recovery data file, it is rolled forward. This data is recorded in the online log, including the content recovery of the retrieval segment.
(2) Rolling the uncommitted transaction, press step 1 to regenerate the operation specified by the rollback segment.
(3) Release the resource held in the event of a transaction during the fault.
(4) Solve any unresolved distribution transactions that are submitted in a fault.
l Media fault recovery
The media fault is a fault when a file, some or a disk of a file cannot be read or cannot be written. There are two forms in the recovery of media failures, which is determined in the archive of the database.
l If the database is running, its online log is only reused but cannot archive, and the media is restored to use the latest complete backup simple recovery. Working in full backup must be re-active.
l If the database is running, its online log is archived, the recovery of the media fault is an actual recovery process, and the reconstructed database is restored to a specified transaction in front of the media failure.
Regardless of which form, the recovery of media faults always resumes the entire database to a transaction before the fault. If the database is running in ArchiveLog, there are different types of media recovery: full media recovery and incomplete media recovery.
Complete media recovery can restore all lost modifications. It may only be possible when all necessary logs are available. There are different types of full media recovery available, which is determined to destroy the availability of files and databases. example:
l Turn off the recovery of the database. When the database can be assembled, it is closed, and it is completely not working properly. At this time, full media recovery of all or single destroyed data files can be performed.
l Open the recovery of offline table spaces for the database. When the database is open, the fully medium recovery can be processed. The uncompassed database table space is available online, and the damaged space is offline, all of its data files are used as the unit of recovery.
l Open the recovery of a single data file in the offline interval of the database. When the database is open, the fully medium recovery can be processed. The unshabable database table space is available online, and the damaged table space is offline, and the specified data file that is damaged by the table space can be recovered. l Restore the full media using the backup control file. When all copy of the control file is damaged due to disk failure, media recovery can be recovered without losing data.
Incomplete media recovery is a media recovery that is not possible in full media recovery or not required. Reconstructing the damaged database to restore the consistency of a transaction before the media failure or before the user's error. Incomplete media recovery has different types of use, which determines the case where incomplete media recovery is required, with the following types: revoke, time and modified incomplete recovery.
Based on withdrawal recovery: In some cases, incomplete media recovery must be controlled, DBA can revoke the operation of the specified point. Restore the revocation is destroyed in one or more log groups (online or archive), which cannot be used for recovery procedures, so media recovery must be controlled, so that the media recovery must be controlled to use the nearest, untreated log. The restore operation is suspended after the data file.
Based on time and modified recovery: If DBA wants to return to a specified point in the past, it is ideal for incomplete media. Can be used in the following cases:
l When the user accidentally deletes a table, and notice the estimation time of the error submit, the DBA can immediately turn off the database to restore it to the user error.
l Due to the system failure, a part of an online log file is broken, so the active log file is suddenly unused, and the instance is aborted, and the media recovery is required. In recovery, the current online log file can be used, and DBA uses time-based recovery, once there is a timely online log, the recovery process is stopped after being applied to the data file.
In both cases, the endpoint of incomplete media recovery can be specified by the time point or system modification number (SCN).
