Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the processing methods after mistakenly deleting database data". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Stop the library and start mount

2.flashback database to timestamp to_timestamp ('2019-09-07 09-07 09: 49) 47-7-09, 47-5, 54, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4

3.alter database open read only

4. Check whether the data meets the requirements, expdp export

5. Stop the library and start mount

6.recover database

Alter database open

Under the test environment, you can operate under a simple test, but I am very afraid that if there is a problem with recover, it is not the problem of this table. The whole database goes back to 47 minutes and 54 seconds, which has a great impact. I am very resistant to this plan, but the impact is too great and needs to be restored as soon as possible. However, after repeatedly communicating with customers about the risk of the program, coupled with the fact that about 30 million of the data had been recovered, the customer agreed to adopt a low-risk off-machine recovery scheme, which was slightly relieved.

The recovery of the different machine is quite smooth. We recovered the database from the complete database, and the archive was also pulled out from the backup. Then recover specified the time, and recover,recover checked the number of rows once a second. The final data volume was about 33 million, and the failure came to an end.

I think my fault accounted for a large part of this failure. I was not very familiar with flashback and mistakenly thought that the command flashback table depended on flashback database on. In fact, the command is completely dependent on undo, and flashback query is the same thing, for a technology you are not familiar with, you must not be self-righteous in the production environment. Any change operation can only be used by customers after sufficient testing. This failure has benefited me a lot, and I will bear in mind that similar incidents will not happen again.

After the matter is over, immediately prepare to turn on flashback archive technology that does not rely on undo flashback query table. The granularity of this flashback archive is a table, it can flashback the data of specified retention time for query, and does not rely on undo table space. If you want to achieve real-time backtracking of business tables, then flashback archive can meet the requirements. Flashback database is not very practical and is generally used in the standby database. The simple steps of flashback archive are posted below. You can refer to:

Flashback archive Test:

Prerequisites for enabling flashback archive:

Undo Management is AOTU

Tablespace must be ASSM

1. Create flashback archive tablespace

Create tablespace fba datafile'+ DATADG' size 10g

two。 Create a flashback archive region

Create flashback archive default fba1 tablespace fba retention 2 day; / / specifies the default flashback area of the database when created

Query:

Select flashback_archive_name name, status from dba_flashback_archive

Select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,FLASHBACK_ARCHIVE#,RETENTION_IN_DAYS,CREATE_TIME,LAST_PURGE_TIME,STATUS from dba_flashback_archive

3. Try to modify the retention time:

Alter flashback archive fba1 modify retention 1 day

4. Clear all information in flashback archive:

Alter flashback archive fba1 purge all

Clear the message from 1 day ago:

Alter flashback archive fba1 purge before timestamp (systimestamp-interval'1' day)

5. Create test data

Test1 2936192 384M

Test2 2936192 384M

6. Put test2 in flashback archive.

Alter table test2 flashback archive fba1

Query:

SELECT table_name,archive_table_name,status from dba_flashback_archive_tables

7. All delete dropped around 18:07.

Delete from test1

Delete from test2; occupies 1000m of table space.

8. Do something else to make sure the undo is empty

9. Query attempt:

Test1 has been unable to query:

Select count (*) from test1 as of timestamp to_timestamp ('2019-09-18 18-18-01-01-18-01-01-18-12-12-18-18-18-01-01-18-12-12-18-18-18-01-01-18-18-18-18-01-01-18-18-18-18-01-18-18-18-18-01-18-18-18-01-18-18-18-18-18-01-18

*

ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 10 with name "_ SYSSMU10_1251597811 $" too small

Test2 can query data at any time after it is enabled:

SQL > select / * + parallel (16) / count () from test2 as of timestamp to_timestamp ('2019-09-18, 1818, 18, 18, 18, 12, 20, 12, 12, 12, 12, 12, 12, 12, 12, 12, 18, 18, 18, 18, 18, 12, 12, 12, 12, 12, 12, 12, 12, 18, 18, 18, 18, 18, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 14, 12, 14, 12, 12, 12, 12, 12, 14, 14, 14, 14, 12, 6, 6, 6, 6, 6, 6, 3, 2, 3, 4, 4, 3, 4, 4, 3, 4, 4, 3, 4, 3, 2, 3, 2, 3, 2, 3, 2, 3,

This is the end of the content of "what are the processing methods after mistakenly deleting database data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.