Oracle backup and recovery Basics

Oracle backup and recovery Basics
I. oracle flash back technology the oracle flash back technology began to provide flashback queue operations in 9i, which was greatly improved in 10 Gb, added the flashback table, flashbackdrop, flashback version query, flashback transaction query, and flashback database. added the flashback data archive function to 11 GB. Note: All other flash back operations except flashback database are based on the content in the undo segment. Therefore, due to the undo_retenton parameter and to enable the flashback feature, you must enable automatic undo tablespace management. As follows:

SQL> showparameter undo_NAME                                 TYPE        VALUE----------------------------------------------- ------------------------------_optimizer_undo_cost_change          string      11.1.0.6undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS1SQL>

 

1) flashbacktable flashback operations can be performed based on the time point or the specific scn. The following operations are performed based on the time point. Note: This process relies entirely on undo. Note the settings of undo_retention and undo_management. Select timestamp_to_scn (to_date ('2017-12-24 18:01:27 ', 'yyyy-mm-ddhh24: mi: ss ')); flashback to test9 to scn xxxxxx; Note: If you are prompted to enable row movement during the Flashback table operation, run the following command: Altertable test9 enable row movement; 2) flashbackdrop when we use drop table to delete a table, the table is deleted in form. If the oracle recycle bin is enabled, the table will be placed in the recycle bin. Run the following command to check whether the recycle bin is Enabled. So where is the oracle recycle bin? In fact, it is a logical space divided in the user's tablespace, and the data it can store depends on the user's tablespace. (Of course, this function is invalid for sys/system users) when space is insufficient, Oracle will overwrite the objects in Recycle Bin in the first-in-first-out order. You can also manually delete the space occupied by the Recycle Bin. 1 ). purge tablespace tablespace_name: used to clear the Recycle Bin 2) of the tablespace ). purge tablespace tablespace_name useruser_name: clears the objects of the specified user in the Recycle Bin of the specified tablespace. 3 ). purge recyclebin: Delete the objects in the current user's Recycle Bin. 4 ). purge dba_recyclebin: delete objects in all users' Recycle Bin. This Command requires sysdba permission 5 ). drop table table_name purge: delete objects permanently and cannot be restored using Flashback instead of in Recycle Bin. 6). Purge index recycle_bin_object_name: When you want to release the space of the Recycle bin and restore the table, you can release the space occupied by the index of the object to relieve the space pressure. The index can be rebuilt as follows: the above operation is actually a data transaction in a user data dictionary. Of course, we can use user-recyclebin to view more detailed information, such as dropscn and operation. Think: if the two I deleted indicate the same, how can we restore them. The answer is to query the transaction through ject_name, and then recover the retrieved content, or orscn Based on the deletion time. 3) flashback queue Flashback is a feature provided by ORACLE since 9i. It uses oracle to query consistent versions in 9i, this feature is also called Flashback Query. Oracle adopts multi-VERSION Query consistency. The image before undo data is used to ensure query consistency, or a record set for query is constructed through undo, in this process, you are not allowed to wait until the transaction is committed or the transaction is rolled back. There are many ways to build this query record set in Oracle, so the simplest is to use asof timestamp or as of scn. My operation case is as follows: Method 1:

SQL> select count (*) from test9; COUNT (*) ---------- 6SQL> delete from test9 where rownum <3; two rows have been deleted. SQL> commit; submitted completely. SQL> select count (*) from test9; COUNT (*) ---------- 4SQL> select count (*) from test9 as of timestampsysdate-3/1440; COUNT (*) ---------- 6SQL> insert into test9 select * from test9 as oftimestamp sysdate-3/1440; 6 rows have been created. SQL> select count (*) from test9; COUNT (*) ---------- 10

 

We can see that this flash back operation is more suitable for statements such as delete from table. Of course, we can use this technology based on scn, so there is a smon_scn_time in the system, we can query through the following command: Select scn, to_char (time_dp, 'yyyy-mm-ddhh24: mi: ss') from smon_scn_time; the scn and time are synchronized every 5 minutes. We can see the specific operation information. Note that this query can be time-based or scn-based. Starttime indicates the time when a record is executed, and endtime indicates the time when the record is replaced. In addition, we can perform queries based on a period of time or scn. Of course, this function is also limited by the undo tablespace settings. 4) flashbackversion query the flash query shows that it can only see the operation changes at a certain time point, so flashback version query is the operation changes within a period of time. For example, Selectempno, versions_operation, versions_starttime, versions_endtime, versions_xid fromtest9 versions between timestamp minvalue and maxvalue. Pay attention to the shoddy ORA_ROWSCN contents. When you need to record ORA_ROWSCN for each modification, you can use rowdependencies. 5) flashbacktransaction query to implement the same function. This function depends on a flashback_transaction_query view and then modifies the transaction. My operations are as follows:

SQL> select * from test9; empno ename job mgr hiredate sal comm deptno ready --------- ---------- -------------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20 7777 allen salesman 7698 20-2 month-81 1600 300 30SQL> update test9 set empno = 3333 where empno = 7369;

 

1 row updated. SQL> commit; submitted completely.

SQL>select versions_operation,versions_xid from test9 versions between timestampminvalue and maxvalue;VVERSIONS_XID-----------------U05001600200E0000I 05001A001E0E0000SQL>select xid,undo_sql from flashback_transaction_query wherexid='05001600200E0000';XID----------------UNDO_SQL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------05001600200E0000update"SCOTT"."TEST9" set "EMPNO" = '7369' where ROWID= 'AAASE1AAEAAAABeAAA';05001600200E0000SQL>

 

Then we can cancel and restore a transaction. 6) The flashbackdatabase is different from other flashback operations, so the flashbackdatabase is based on the flashback log. We can use the flashback log to flash the entire database back to the previous state. Note: database restart is required during this process. You can configure relevant parameters and then start this function. By default, this function is disabled. After this function is enabled, an rvwr log writing process is added to the background process. Note: This feature requires the database archive mode to be enabled. Disable this function as follows: Enable this function as follows: You can also view the following information in the alarm log: db_recovery_file_dest_size of 5120 MB is 43.82% used. we can see that our zone uses 43.82%. When the peak value is reached, rman must be used for deletion. The following operation is performed: The db_flashback_retention_target parameter is also provided. Common views include: V $ flashback_recovery_area_usage, v $ database, v $ flashback_database_log, v $ flashback_database_logfile, v $ flashback_database_stat