Oracle uses handles for non-Backup Recovery in specific scenarios

Oracle uses handles for non-Backup Recovery in specific scenarios

In dba's work, backup is the foundation of all work. If there is no backup, it would be difficult to recover the data easily. If the business data requirements are high, resulting in data loss or damage, it would be a major accident. It is also important to use rman backup or make a complete system-level backup. If there is no backup in a specific scenario, it would be lucky if it can be recovered.

When a data file in the database is deleted by mistake, some work can be done if the database has not been restarted. Because the corresponding handle of the file has not been released. We can find an image backup to restore data. It must be noted that such recovery is not necessarily a full data recovery. If there are opened transactions when the data file is deleted, these transactions have also been committed.

-------------------------------------- Recommended reading --------------------------------------

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption

-------------------------------------- Split line --------------------------------------

Before deleting a data file, let's take a look at the data files in the test environment.

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------------------------------------------------------------------------
SYSTEM/u03/ora11g/oradata/TEST01/system01.dbf
SYSAUX/u03/ora11g/oradata/TEST01/sysaux01.dbf
UNDOTBS/u03/ora11g/oradata/TEST01/undotbs01.dbf
TEST_DATA1/u02/ora11g/testdata1.dbf
POOL_DATA/u03/ora11g/oradata/TEST01/pool_data03.dbf
POOL_DATA/u03/ora11g/oradata/TEST01/pool_data01.dbf
POOL_DATA/u03/ora11g/oradata/TEST01/pool_data02.dbf
POOL_DATA/u03/ora11g/oradata/TEST01/pool_data04.dbf
POOL_DATA/u03/ora11g/oradata/TEST01/pool_data05.dbf
POOL_DATA/u01/ora11g/pool_data06.dbf
POOL_DATA/u01/ora11g/pool_data07.dbf
11 rows selected.
Create a new tablespace and data file,

SQL> create tablespace test_delete datafile '/u01/ora11g/test_delete.dbf' size 10 M;
Tablespace created.

SQL> create user test_delete identified by test_delete default tablespace test_delete quota unlimited on test_delete;
User created.
Create a user and add some data to the user.
Grant connect, resource to test_delete;
Conn test_delete/test_delete
Create table test as select * from all_objects;
Create index test_ind on test (object_id );
Create table test1 as select * from test where rownum <100;
Update test1 set object_name = 'a ';
Note that we have not performed the commit operation for the last update statement, so the data may not have been written into the data file yet. From the transaction point of view, this update is not complete yet.
Let's see if we can recover all the data, including uncommitted transaction data.

Before deletion, you can simply perform a check.

SQL> select count (*) from test;
COUNT (*)
----------
5660

SQL> select count (*) from test1 where object_name = 'a ';
COUNT (*)
----------
99
Start to manually delete data files

[Ora11g @ rac1 fd] $ rm/u01/ora11g/test_delete.dbf
After the deletion, A create operation is performed successfully.

SQL> create table test3 as select * from test1;
Table created.
Continue to try an Update, and finally get the expected Ora error.
Update test set object_id = 1
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/u01/ora11g/test_delete.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
At this time, we began to consider using a handle to view the corresponding data files,
First, use ps to get the process number corresponding to dbw.

[Ora11g @ rac1 proc] $ ps-ef | grep ora_dbw
Ora11g 938 1 0 Nov20? 00:00:07 ora_dbw0_TEST01
Ora11g 7819 5794 0 00:00:00 pts/0 grep ora_dbw

Then check in/proc/938/fd
[Ora11g @ rac1 proc] $ ll/proc/938/fd
Total 0
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 0->/dev/null
L-wx ------ 1 ora11g dba 64 Nov 21 05:36 1->/dev/null
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 10->/dev/zero
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 11->/dev/zero
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 12->/u03/ora11g/product/11.2.0/dbhome_1/dbs/hc_TEST01.dat
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 13->/u03/ora11g/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
Lr-x ------ 1 ora11g dba 64 Nov 21 14->/proc/938/fd
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 15->/dev/zero
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 16->/u03/ora11g/product/11.2.0/dbhome_1/dbs/hc_TEST01.dat
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 17->/u03/ora11g/product/11.2.0/dbhome_1/dbs/lkTEST01
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 18->/u03/ora11g/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
Lrwx ------ 1 ora11g dba 64 Nov 21 19-> socket: [1434598]
L-wx ------ 1 ora11g dba 64 Nov 21 05:36 2->/dev/null
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 256->/u03/ora11g/oradata/TEST01/control01.ctl
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 257->/u03/ora11g/oradata/TEST01/control02.ctl
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 258->/u03/ora11g/oradata/TEST01/system01.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 259->/u03/ora11g/oradata/TEST01/sysaux01.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 260->/u03/ora11g/oradata/TEST01/undotbs01.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 261->/u02/ora11g/testdata1.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 262->/u03/ora11g/oradata/TEST01/pool_data03.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 263->/u03/ora11g/oradata/TEST01/pool_data01.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 264->/u03/ora11g/oradata/TEST01/pool_data02.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 265->/u03/ora11g/oradata/TEST01/pool_data04.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 266->/u03/ora11g/oradata/TEST01/pool_data05.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 267->/u01/ora11g/pool_data06.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 268->/u01/ora11g/pool_data07.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 269->/u03/ora11g/oradata/TEST01/temp01.dbf
Lrwx ------ 1 ora11g dba 64 Nov 21 05:55 270->/u01/ora11g/test_delete.dbf (deleted)
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 3->/dev/null
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 4->/dev/null
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 5->/dev/null
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 6->/dev/null
Lrwx ------ 1 ora11g dba 64 Nov 21 05:36 7->/u03/ora11g/product/11.2.0/dbhome_1/dbs/hc_TEST01.dat
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 8->/dev/null
Lr-x ------ 1 ora11g dba 64 Nov 21 05:36 9->/dev/null

You can see the handle information. The status of the deleted data file has been reflected in/proc/xx/fd.
In this case, manually copy the data file to the target directory.

[Ora11g @ rac1 fd] $ cp 270/u01/ora11g/test_delete.dbf
[Ora11g @ rac1 fd] $
After the copy is complete, use the test user for some simple verification. The operation to create a new table is successful.

SQL> conn test_delete/test_delete
Connected.
SQL> create table test4 as select * from cat;
Table created.
Let's take a look at the situation before the data file is restored.
SQL> select count (*) from test; -- there is no problem with the data, and the number of items is the same as expected.
COUNT (*)
----------
5660

SQL> select count (*) from test1 where object_name = 'a'; -- the transaction has been committed. It indicates that the changed data has been written into the data file. Expected Value: 0.
COUNT (*)
----------
99

Then we try to restore the data file.

SQL> conn/as sysdba
Connected.
SQL> alter database datafile '/u01/ora11g/test_delete.dbf' offline;
Database altered.
SQL> recover datafile '/u01/ora11g/test_delete.dbf ';
Media recovery complete.
SQL> alter database datafile '/u01/ora11g/test_delete.dbf' online;
Database altered.
To see how the data file is restored.

SQL> select count (*) from test; -- there is no problem with the data, and the number of items is the same as expected.
COUNT (*)
----------
5660

SQL> select count (*) from test1 where object_name = 'a'; -- the transaction has been committed. It indicates that the changed data has been written into the data file. Expected Value: 0.
COUNT (*)
----------
99

Restart the database to see if the database can be started or stopped normally. Data changes.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 255852824 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.

SQL> conn test_delete
Enter password:
Connected.
SQL> select count (*) from test; -- there is no problem with the data, and the number of items is the same as expected.
COUNT (*)
----------
5660

SQL> select count (*) from test1 where object_name = 'a'; -- the transaction has been committed. It indicates that the changed data has been written into the data file. Expected Value: 0.
COUNT (*)
----------
99

So the above example also shows that backup is more important than everything, and this recovery still requires luck, but to a certain extent, it is better than nothing. In addition, this recovery also requires luck. If the database is stopped at the beginning, it will be powerless.