Oracle Database Management restore Data

• Description of DML and restored data generation • Monitoring and Management of restored data • Description of differences between restored data and redone data • Configuration of restoration retention time • guaranteed restoration retention time • Use of Restoration Guidance 1, data manipulationYou can use dml SQL statements to manipulate or modify data: INSERT, UPDATE, DELETE, and MERGE. These statements are executed as part of the transaction processing. The transaction processing starts with the first successful DML statement and ends with the COMMIT or ROLLBACK command. Transaction processing is always committed. The entire transaction is rolled back unless the SAVEPOINT clause is used to roll back to a specific storage point. In addition to the explicit COMMIT or ROLLBACK commands, you can also implicitly execute these commands. For example, if a process or system fails, a rollback may occur. After executing a DDL command such as create table, it may be submitted. Note • Use the MERGE command to execute a combination of DML commands to MERGE data from one table to another. • Submission always occurs before and after DML commands are executed. 2. Restore dataRestoring data is: • Original data copies before modification • captured for each transaction that changes data • at least retained until transaction processing ends • supported: -rollback operation-read consistency query-Flashback query, flashback transaction processing, and flashback table-restore from failed transaction processingWhen a process changes the data in the database, Oracle DB will save the old value (restore data ). Store data as is before data modification. If restored data is captured, uncommitted data can be rolled back. Restore data is supported. Restoration can also be used for "reverse" (FLASHBACK) transaction processing and tables. In, provide the same results as the data at the start of the query. To successfully complete the read consistency query, the original information must still exist as the restoration information. If the raw data is no longer available, the system will receive the "snapshot too old" error. As long as the restoration information is retained, Oracle DB can rebuild the data to meet the read consistency query requirements. Purposefully search for data of a certain version that exists at a time in the past. As long as the Restoration Information in the past time still exists, the flash query can be completed successfully. Use the restoration function to create a compensation transaction for rollback and related transaction processing. You can use the function to restore a table to a specific time point. Restoring data can also be used. If the user session ends abnormally before the user decides to commit or roll back the transaction processing (possibly due to network errors or client faults), the transaction processing will fail. When the instance crashes or the shutdown abort command is issued, transaction processing may also fail. If the transaction fails to be processed, select the safest action, that is, let Oracle DB cancel all the changes made by the user to restore the original data. The restoration information of all transaction processing will be kept at least until the transaction processing ends, that is, when you end the transaction processing using one of the following methods: • The user restores the transaction processing (rollback transaction processing ). • End Transaction Processing (commit transaction processing ). • The user executes DDL statements, such as CREATE, DROP, RENAME, or ALTER statements. If the current transaction processing contains any DML statement, the database first submits the transaction processing, then executes and submits DDL in the new transaction processing. • Exceptional termination of user sessions (rollback transaction processing ). • The user session is terminated and exited normally (committed for transaction processing ). The amount of restored data retained and its retention time depend on the amount of database activity and database configuration. Note: Oracle flash back transaction processing uses online redo logs to mine the corresponding restored SQL statements used for execution. If the start time of transaction processing is not provided in the flash back transaction processing call, only the restored data is used as the human time boundary to determine the start time of redo mining for the target transaction processing. 3. process and restore Data• Each transaction is only allocated to one restoration segment. • A restoration segment can serve multiple transactions at the same time. The restoration segments are allocated at the beginning of transaction processing. During the entire transaction process, the original (before the change) value is copied to the restoration segment when the data is changed. By selecting the dynamic performance view of V $ TRANSACTION, you can view which TRANSACTION processing is allocated to which restoration segments. Recovery segments are dedicated segments automatically created by instances to support transaction processing. Like all segments, a restoration segment consists of partitions and data blocks. The restoration segment can automatically grow and contract as needed. It acts as the cyclic storage buffer for the allocated transaction processing. The transactions service fills the areas in the Recovery Section until the transaction is processed or occupies all the space. If more space is required after the partition is filled, transaction processing will obtain the space in the next partition of the segment. After all the zones are occupied, the Service Department automatically transfers back to the first zone or requests to allocate a new area for the restored section. Note: Parallel DML and DDL operations can actually cause transaction processing to use multiple restoration segments. 4. Store Restoration InformationThe restored information is stored in the restored segments, and the restored segments are stored in the restored tablespace. Restore tablespace: • used only to restore segments • contains special recovery considerations • can only be associated with a single instance • Requires any specified time, A given instance can only have one tablespace that is currently writable and restored.In a specific form of tablespace, this form of tablespace is called "you cannot create other segment types in the restored tablespace.") the installation process can automatically create a "small file" to restore the tablespace. You can also create a "large file" to restore the tablespace. However, in the OLTP environment where a large number of short transactions are processed at the same time, contention will occur on the file header. Restoring tablespaces stored in multiple data files can solve this potential problem. Although the database may have many tablespaces to restore, for any instance in the database, only one tablespace can be restored at a time specified as its current tablespace. Restore segments that are always owned by SYS will be automatically created. Because the restoration segment acts as a circular buffer, each segment contains at least two zones. The default maximum number of zones depends on the size of the database block, but this value is very large (the block size is 8 KB and the time zone is 32,765 ). Restoring a tablespace is a permanent, locally managed tablespace (with automatic partition allocation ). They are automatically managed by the database. Because data must be restored before it can be recovered from a failed transaction (for example, a failed transaction may fail when the instance crashes. 5. Restore and redo dataRestoring data is similar to redoing data at the beginning, but they have different functions. If yes, and. If a change is lost for some reason, you need to redo the data if you need to execute the change again. Restoring block changes can also be written into redo logs. The commit process needs to verify whether the changes made in the transaction processing have been written into the redo log file. This redo log file is permanently stored on the disk, rather than in the memory. In addition, redo log files are often multiplexing. Therefore, the disk has multiple copies of redo data. Although the changes may not have been written into the data file of the actual storage table block, it is sufficient to ensure Database Consistency as long as they are written to the persistent redo log. Assume that a power failure occurs before the submitted changes are reflected in the data file. Because the transaction processing has been committed, this will not cause any problems. When the system is started again, any redo records in the data file are not reflected when the system is rolled forward and powered off. 6. Manage RestorationAutomatic Recovery Management: • Fully Automatic Management of restored data and space in dedicated recovery tablespaces • used for all sessions • Self-optimized in AUTOEXTEND tablespaces, to meet the needs of long-running queries • perform self-optimization in a fixed-size tablespace to achieve the best retention time for DBA tasks that support flashback operations: • configure restoration retention time • Change the restored tablespace to a fixed size • Avoid space errors and "too old snapshots" ErrorsOracle DB provides the "auto-Restore management" function, which is a completely automatic mechanism for managing the restored information and space in the dedicated tablespace for all sessions. The system automatically optimizes itself to provide the best retention time for information restoration. More accurately, for automatically expanded tablespaces, the restoration retention time is optimized to the duration of the activity query that is slightly longer than the longest running time. For restored tablespaces of a fixed size, the database will be dynamically optimized to obtain the best retention time. Auto-Restore management is the default function in Oracle Database 11g (and later. Manual restoration management is also supported to be backward compatible with Oracle8i and earlier versions. However, in this mode, DBA needs to perform more interactive operations. In the manual restoration management mode, you can use the rollback segment instead of the tablespace to manage the restored space. Note: Oracle strongly recommends auto-Restore management. Although Oracle DB automatically manages and restores data and space by default. The restoration management function is available. 7. Configure the restoration retention timeUNDO_RETENTION specifies how long (in seconds) The submitted restore information will be retained ). This parameter must be set only in the following cases: • The AUTOEXTEND option is enabled for the restored tablespace• Set the LOB restoration retention time• Ensure the retention timeThe UNDO_RETENTION initialization parameter specifies the minimum threshold (in seconds) of the Restoration retention time ). You can set the shortest restoration and retention time of the automatically expanded restored tablespace to the expected maximum time required for the flashback operation. For auto-expanded restored tablespaces, the system retains restored data at least at the time specified by this parameter, and automatically adjusts the restoration retention time to meet the query restoration requirements. However, the retention time of this automatic optimization may not be enough to perform the flash back operation. For restored tablespaces of a fixed size, the system automatically adjusts to the best possible restoration retention time based on the restored tablespace size and usage history ;. Therefore, the UNDO_RETENTION settings are used in all the three cases listed above during Automatic restore management. This parameter is ignored in cases other than the three types. There are three types of Restoration Information: • uncommitted restoration information (activity): currently running transactions are supported. This type of information is required if you want to roll back the transaction or fail to process the transaction. It will never overwrite uncommitted Restoration Information. • Committed Restoration Information (not expired): This information is no longer required for running transactions, but is still used to meet the restoration retention interval. This is also called "unexpired" to restore information. When the active transaction fails to be processed because of a lack of space, the committed restoration information is retained. • Expired Restoration Information (expired): no longer needed to support running transactions. When the active transaction processing requires space, it overwrites the expired Restoration Information.Ensure that the retention time will change this behavior.. Retention guarantee is a tablespace attribute rather than an initialization parameter. This attribute can only be changed using SQL command line statements. The syntax for changing the restored TABLESPACE to ensure the RETENTION time is: SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; to return the restored TABLESPACE with a guaranteed RETENTION time to its general settings, run the following command: SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE ;. An attempt to set the RETENTION time for a non-restored TABLESPACE produces the following ERROR: SQL> ALTER TABLESPACE example RETENTION GUARANTEE; ERROR at line 1: ORA-30044: 'retention 'can only specified for undo tablespace 9. Change the restored tablespace to a fixed size.Cause: workflow: 1. normal workload. 2. Minimum size required to create a self-optimization mechanism. 3. You can use the restoration guide to calculate the size required to meet future growth. 4. You can choose to change the restored tablespace to a fixed size.The restored tablespace may be changed to a fixed size for two reasons: (recovery is expected in the future) or. If you decide to change the restored tablespace to a fixed size, You must select a large enough size to avoid the following two types of errors: Oracle recommends that you run properly at full load, increase the size of the restored tablespace to the minimum size required. Statistics automatically collected include. For systems that do not execute the flash back operation and do not plan to perform queries with a longer running time in the future, we recommend that you calculate the minimum restored tablespace size based on these statistics. For query and flash back operations with longer running time, you can use it to enter the length of the Restoration Period. 10. General Restoration InformationIn Enterprise Manager, select "Server> Automatic restore Management )". There are two pages: General and System Activity )". At the top of the General page, you can see the restoration retention time settings and information about the restored tablespace In the instance. 11. Instructions on restorationIn the middle of the General Undo page, you can access the restoration guide. It can be used to estimate the size of the restored tablespace required to meet the specified restoration retention time. The analysis area of this Guide displays the tablespace size required to support the retention time. You can click a point on the graph to view the tablespace size required during the selected period. Click the Edit Undo Tablespace button, and then click Edit in the Datafile section to change the restored Tablespace to a fixed size.Scenario: Modify the restoration retention time and calculate the size of the restored tablespace to support the required 48-hour retention time.A) Click the General tab to return to the General Automatic recovery Management page. B) In the "Undo Advisor (restoration guide)" section, select "Specified manually to allow for longer duration queries or flashback (manually specify to allow query or flashback for a longer period of time )". C) Enter 48 hours in the Duration field and click Run Analysis. The size of the restored tablespace displayed in the result is very close to that of the suggested restored tablespace. This size applies to most workloads, but we recommend that you set the size of the restored tablespace to three times the minimum size. That is to say, you should change the size of the restored tablespace to 372 MB. 12. view system activities System activity during the selected period is displayed at the top of the page. Below this section, there are three figures: 1. restore table space usage: displays the table space size by number of days (in MB) 2. restore retention time automatic optimization: view the optimized restore retention time by number of days (in minutes) 3. recovery generation rate: display the restoration generation rate by the number of days (in KB/s)