Three major functions of undo: 1. provide consistent read. 2. roll back the transaction. 3. Check whether the tablespace belongs to temporary, undo, or permanent when the instance recovers? Selecttablespace_name, contents, statusfromdba_tablespaceswheretablespace_namelike % UNDO %; 1. Manage Undo tablespaces -- CREATE a new Undo tablespace
Three major functions of undo: 1. provide consistent read. 2. roll back the transaction. 3. Check whether the tablespace belongs to temporary, undo, or permanent when the instance recovers? Select tablespace_name, contents, status from dba_tablespaces where tablespace_name like '% UNDO %'; 1. Manage Undo tablespace -- CREATE a new Undo tablespace
Three major functions of undo: 1. provide consistent read. 2. roll back the transaction. 3. instance recovery
Check whether the tablespace belongs to temporary, undo, or permanent?Select tablespace_name, contents, status from dba_tablespaces where tablespace_name like '% UNDO % ';
1. manage Undo tablespaces -- CREATE a new Undo tablespace create undo tablespace UNDOTBS2datafile '/paic/g2bh8060/dev/xqd/oradata/hd02ntlf/comment 'size 500 m autoextend on retention noguarantee; the default value is NOGUARANTEE-add the file alter tablespace undotbs1 add datafile '/u01/oracle/undo01.dbf' size 500 m autoextend on for the Undo tablespace; alter tablespace undotbs1 add datafile '+ DATA_MIDG' size 500 m autoextend on; query and change whether the undo tablespace retention guaranteeselect ta Blespace_name, RETENTION from dba_tablespaces where tablespace_name like '% UNDO %'; alter database UNDOTBS01 retention guarantee | NOGUARANTEE; change the default tablespace show parameter tablespace system set undo_tablespace = undotbs2; undo tablespace Delete drop tablespace undotbs1 including contents and datafiles; undo tablespace rename alter tablespace undotbs2 rename to undotbs1; recreate undo tablespace 1. because of the creation of the delay segment, You can first cancel the parameter; show parameter segmentalter s Ystem set deferred_segment_creation = false; 2. create undotemp tablespace 3. alter system set undo_tablespace = undotemp; (if the latency section creation feature is enabled, the system will prompt that the tablespace does not exist.) 4. drop the old undotbs1. The old tablespace is now in the pending offline state. Wait for the offline tablespace to wait until all the transactions on it are completed before the offline tablespace is actually in the offline state. ORA-30013: undo tablespace 'undotbs1' is currently in use5. the new rename is old.
2. Understand UNDOUndo-related parameters: undo_management AUTO -- after 9i, undo_management uses autoundo_tablespace UNDOTBS2 -- you can dynamically specify the system undo tablespace undo_retention 900 -- unit: seconds. The default value is 900undo_retention for select operations, we generally want to flash back to query data within three hours. The undo block in the transaction is active, and the block after the transaction ends is inactive. The inactive undo block will become expired after the specified time of undo_retention. For example, if the longest query of a database is within five minutes, set "undo_retention" to "300", then the possibility of this select statement encountering 01555 snapshot being too old is relatively small, but it is not guaranteed. Alter system set undo_retention = 10800 SCOPE = BOTH; we use the undo_management parameter to control which method to use. If it is SET to auto, the UNDO tablespace is used. At this time, an UNDO tablespace must be specified. If it is set to manual, the system uses the rollback segment method to store undo information after startup. If undo_management is not specified, the system starts in manual mode by default. Even if parameters in auto mode are set, these parameters are ignored. If the RETENTIONGUARANTEE clause is configured in the database, the RETENTIONGUARANTEE clause ensures that the retained revocation is ensured. If the Undo tablespace is too small to meet all the active transactions that use it, the following situation occurs: 1. If the Undo tablespace is used up by 85%, oracle will release an automatic tablespace warning 2. When the Undo tablespace is used up by 97%, Oracle will release an automatic tablespace severe warning 3. All DML statements will not be allowed, in addition, a space exceeding error is received. 4. DDL statements allow continued execution. Therefore, the undo tablespace in the PA database is NOGUARANTEE, regardless of the production or test database. In the case of RETENTIONGUARANTEE, the undo in the undo_retention seconds cannot be overwritten. Therefore, if the expired and freed blocks are almost lost and the Undo tablespace cannot be automatically expanded, the above error will be reported. The function of the undo_retention parameter can be used as a line to determine whether to ensure that the undo block is not overwritten in the case of RETENTIONGUARANTEE. If no retention is set, for example, in the case of PA, is used as the demarcation line for priority coverage. Therefore, there are only two priorities. Any online side is randomly overwritten without priority. The extend size allocated to the tablespace In the Undo tablespace. @ Tsfree: select d. tablespace_name, space "SUM_SPACE (M)", blocks sum_blocks, space-nvl (free_space, 0) "USED_SPACE (M)", round (1-nvl (free_space, 0) /space) * 100, 2) "USED_RATE (%)", free_space "FREE_SPACE (M)" from (select tablespace_name, round (sum (bytes)/(1024*1024 ), (2) space, sum (blocks) blocks from dba_data_files group by tablespace_name) d, (select tablespace_name, round (sum (bytes)/(102) 4*1024), 2) free_space from dba_free_space group by tablespace_name) f where d. tablespace_name = f. tablespace_name (+) order by 1; in USED_SPACE (M), all of them have been allocated to the tablespace. There is an undo block in four minutes. Select tablespace_name, status, sum (bytes)/1024/1024 mb from dba_undo_extents group by tablespace_name, status; active: indicates that the transaction using the undo has no commit or rollbackUNEXPIRED (inactive): indicates that there are no active transactions on the undo, And the undo in this status can be overwritten by other transactions. Expired: indicates that the duration of the undo block's inactive state exceeds the time specified by undo_retention. freed: indicates that the content of the undo block is empty and has never been used. Apply for Undo tablespace for one transaction: 1. If the undo tablespace can be automatically expanded, it will be automatically expanded first. 2. If no extension is available, use the free zone. 3. If there is no free zone, find the expired undo zone. 4. If there is no expired, look for unexpired. At this time, the undo_retention time has not been reached for these areas, so the flashback query cannot be guaranteed. 5. If no, an error is reported, and the active zone will not be used. In Retention Guarantee mode, the undo tablespace requested by a transaction takes only the, 3 points above. After, an error is reported if the Undo space cannot be applied. That is to say, the undo block within the undo_retention period will not be overwritten.
Perform derivative operations to the database. When batch DML operations are not allowed to commit for a long time, add several files to the Undo tablespace and enable automatic scaling to maintain monitoring.
