Oracle clears daily backup data and recovers tables accidentally deleted

Oracle clears daily backup data and recovers tables accidentally deleted

I. Clean up data tables for routine backup

1. Background: The system tablespace usage has exceeded 90%. As a result, a related junk data is cleared.

2. You need to clear the table list for queries: some tables are often backed up during routine database operations. Consult with the former system administrator. During table backup, they generally add a date to the original table or use bak for identification. Therefore

SELECT owner, num_rows * avg_row_len, table_name FROM DBA_TABLES WHERE TABLE_NAME LIKE '% BAK %' AND REGEXP_LIKE (TABLE_NAME, '/d + ') AND (TABLE_NAME not like't _ IM_INVENTORY % 'AND TABLE_NAME not like 'vt %' AND TABLE_NAME not like 'ncm % 'AND TABLE_NAME not like't _ HR_SCHM %' AND TABLE_NAME NOT LIKE 'sys % 'and owner = 'jingya ');

Query the space occupied by the relevant potential tables. After other potential tables are excluded, the total space occupied is close to 100 GB.

3. Test: In the test system, tables that have been preliminarily determined are deleted to test related services. No problem.

4. Formal operation: Follow the company's change process to operate the formal system;

　Ii. Restore tables accidentally deleted

After completing the first job, the business runs for two days without failure. On the third day, the financial report says that the financial consolidated report will automatically offset the item to generate one fewer entry. After the analysis, it is determined that a data table used by the report is accidentally deleted. The table needs to be restored.

The recovery code is as follows:

-- 1 query the drop-down tables by mistake from the flash back: select object_name, original_name, partition_name, type, ts_name, createtime, droptime from recyclebin where original_name = 't_ CSL_ITEMDATAENTRY001 '; -- 2 restore to flashback table "BIN $ AP6V8ohdAaLgU8CoZGEBog = $0" to before drop; -- 3 query data select count (*) from T_CSL_ITEMDATAENTRY001

Confirm the problem.