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

This article mainly explains "can the operation of deleting tablespaces in the database be restored?" the content in the article is simple and clear, and it is easy to learn and understand. let's study and learn "can the operation of deleting tablespaces in the database be restored?"

Once in a WeChat group, someone asked whether the following two operations could still be restored. And it's not open for filing. Then someone asked whether the database was flashback.

Drop user aaa cascade;drop tablespace asd including contents and datafiles

PS: he didn't specify that flashback is a flashback query? Flashback watch? Flash back to the database? Let's look at this problem one by one from the point of view that we don't understand the flashback feature (here we assume that there is only one table under this user).

The following is the whole analysis process:

Scenario 1. Flashback query

SQL > create table aaa.a1 (id number); Table created.SQL > insert into aaa.a1 values (3); 1 row created.SQL > commit;Commit complete.SQL > select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER----3575965SQL > drop user aaa cascade;User dropped.SQL > select * from aaa.a1 as of scn 3575965 Select * from aaa.a1 as of scn 3575965 * ERROR at line 1:ORA-00942: table or view does not exist

You can see that flashback queries are invalid. In fact, if you know something about flashback queries, you can eliminate them first. Because flashback queries are based on undo and undo is affected by ddl, drop operations do not use undo tablespaces, so undo-based flashback queries cannot retrieve data in this scenario.

Scenario 2. Flashback table (flashback table)

SQL > flashback table aaa.a1 to before drop;flashback table aaa.a1 to before drop*ERROR at line 1:ORA-01435: user does not exist

Drop user cascade will not put the table in the Recycle Bin, so no matter how we execute flashback table, it won't help.

Finally, let's try flashing back to the database to see if it can be a lifesaver. In fact, the prerequisite for flashing back to the database is to turn on archiving, so I'm sorry it won't work either.

Suppose we now open the flashback database (flashback database, including archiving, of course), then will the data we deleted by mistake be retrieved?

Scenario 3. Flashback database (1)

SQL > select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER----3574600SQL > drop user aaa cascade;User dropped.SQL > drop tablespace asd including contents and datafiles;Tablespace dropped.SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 889389056 bytesFixed Size 2258360 bytesVariable Size 574622280 bytesDatabase Buffers 306184192 bytesRedo Buffers 6324224 bytesDatabase mounted.SQL > flashback database to scn 3574600 / / flashback before misoperation Flashback complete.SQL > alter database open read only; / / Open Database altered.SQL > select * from aaa.a1;select * from aaa.a1 * ERROR at line 1:ORA-00376 in read only mode: file 10 cannot be read at this timeORA-01111: name for data file 10 is unknown-rename to correct fileORA-01110: data file 10:'/ u01cannot be read at this timeORA-01111 cannot be read at this timeORA-01111: name for data file 10 is unknown-rename to correct fileORA-01110: data file 10:'/ u01qapqqqoracleERROR at line 1:ORA-00376 11.2.0Compact 1DbThash DBAMED00010'

Here's how the flashback database works: flashbackdatabase is used to restore data in the database to a previous point in time, rather than media recovery. The drop tablespace including contents and datafiles here (the effect is the same with including datafile), the corresponding data file will be deleted. So even if the flashback database feature is turned on now, it won't help.

So what if, in the above case, I only executed the drop usercascade command instead of drop tablespace including datafile (in flashback database mode)?

Scenario 4. Flashback database (2)

SQL > select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER----3581891SQL > drop user db1 cascade;User dropped.SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 889389056 bytesFixed Size 2258360 bytesVariable Size 574622280 bytesDatabase Buffers 306184192 bytesRedo Buffers 6324224 bytesDatabase mounted.SQL > flashback database to scn 3581891 flashback complete.SQL > alter database open read only;Database altered.SQL > select * from db1.milktwo ID NAME- 33 kk2 hh Thank you for your reading. The above is the content of "can the operation of deleting tablespaces deleted by users in the database be recovered?" after the study of this article, I believe that you can still recover the operation of deleting tablespaces in the database. The specific use situation still needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.