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

It is believed that many inexperienced people do not know what to do about how to analyze the cases and suggestions of mistakenly deleting the database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Case sharing

Erroneous deletion of data table

When I took over all the databases of a department, I missed one, and no one told me, so I didn't know this database existed. One day, a programmer mistakenly pressed a button to delete a large amount of data. When he found me, he found that the database was not archived and there was no backup. As a result, the programmers filled up the data for a few days, and the bonus was lost directly.

Mistakenly delete a user

I have just started DBA, but I have already made a mistake that I will never forget. Originally, I was going to delete a user of the test environment, but because there were multiple windows on the desktop, as a result, drop user XXX cascade directly gave a user of the formal environment to drop. As soon as I pressed enter, I felt strange. I thought I wouldn't! There is no hurry, but fortunately, the information of this user is synchronized from another server, otherwise it is dead. In the future, I'm used to seeing which DB is on that server first, and don't get it wrong.

Mistakenly delete table data

In the past, the company had a script written by a programmer and an implementer to execute it. There was no backup or archive of delete * from xxx; commit; in the script. As a result, all the personnel of our company went out, holding notebooks and desktops, to all the government units in a district and county in Beijing to record personnel information for a week. It is still fresh in my memory.

Erroneous deletion of data table

The version exported from the test environment contains drop sentences, but it is done directly in the production environment without looking at it. All of a sudden, the material list is gone, and the entire production line is stopped. Later, it was restored and the data was lost for a long time. Lesson: the script you execute must be checked carefully.

Reference and suggestion

1. Constrain or disable specific DDL operations through triggers

For high-risk database DDL operations such as TRUNCATE, you can consider disabling it through triggers to prevent unauthorized operations from damaging the data. Many negligent data disasters come from Truncate, a system-level rm command that is destructive and DDL cannot be backed back, even if it is too late to find out. Therefore, we suggest that users should consider using DDL triggers to disable dangerous operations such as Truncate for security purposes.

two。 Authorization based on the principle of minimum authority

Excessive authorization is to plant security risks for the database. In order to avoid the security risks caused by excessive authorization, the principle of minimum authorization must be followed when users are authorized.

3. Clarify the responsibilities of users

Different database users should be clear about the scope of work that can be used. If ordinary user identity should be used, DBA user identity should never be used. Errors can only be avoided if the authority is commensurate. Even users with administrator responsibilities should follow the habit of performing different tasks in different identities, such as the use of SYS and SYSTEM users should be distinguished and defined.

4. Backup before any data corruption

Before the truncation and deletion of the data table, backup should be carried out to form a habit of backup so as to avoid being caught off guard after misoperation.

5. Rename instead of delete operation

Whether operating system-level or database-level deletion operations, try to rename instead of deletion, such as renaming data tables, renaming data files, and then delete completely after a period of observation and confirmation. The Recycle Bin function introduced in Oracle10g is to change the DROP operation we performed to rename for protection. When we find an error, we can retrieve it through the Recycle Bin, but note that the time of saving the object in the Recycle Bin is related to space. If there is not enough storage space, the object will be automatically released. It is very helpful for us to learn from the idea of the Recycle Bin in management.

6. Try to buy enough time

Don't underestimate any simple maintenance operation, because an accident can significantly extend your maintenance time. Therefore, you should try to buy enough time, including making adequate preparations, speeding up the implementation of unimportant steps, and reducing unnecessary time consumption. the more time you have, the more time you will spend dealing with possible failures.

7. Review your clipboard

Many errors are caused by pasting the contents of the clipboard, so be vigilant when you are ready to paste content you can't see into a window or command line. On Windows, there are many clipboard enhancement tools that can help us record and display the contents of the clipboard. Review your clipboard to make sure it contains what you expect.

8. Never execute a script that you haven't read carefully.

For DBA, if you have never read a script carefully, don't execute it. An error in the script can lead to a serious data disaster. We have encountered a case in which all data files were deleted due to a variable error in the script, and the lesson was painful. If you really can't audit the contents of the script, back up your data before doing anything important.

After reading the above, do you know how to carry out the analysis of misdeleted cases and suggestions in the database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.