Example of Incomplete recovery based on time points

Example of Incomplete recovery based on time points

When it comes to Incomplete recovery, there are generally three scenarios: Incomplete recovery based on time points, Incomplete recovery based on scn, and incomplete recovery based on cancel.
In all three cases, Incomplete recovery is adopted, and incomplete recovery is caused by such errors in the process of full recovery. There are countless errors, such as archiving and loss of redo damage, control File loss, backup problems, manual errors, and so on.
We can give an incomplete recovery case. In fact, there are still some points worth summarizing and learning in the actual operation process.
Step 1 prepare basic data. Currently, we can see that there is only one new_recover table on the tablespace data.

SQL> select owner, segment_name, segment_type from dba_segments where tablespace_name = 'data ';

OWNER SEGMENT_NAME SEGMENT_TYPE
TEST NEW_RECOVER TABLE
It contains some data.

SQL> select count (*) from test. new_recover;

COUNT (*)
----------
4667
Step 2: Start hot backup. To understand the entire process, we can manually complete this incomplete recovery.
Use the following statement to generate a dynamic SQL statement for Hot Backup
Select 'alter tablespace' | tablespace_name | 'in in backup; 'from dba_tablespaces where l
Ogging = 'logging ';
Copy the physical file to the specified Backup Directory.
After the copy is complete, use the following statement to declare that the hot backup has been completed
Select 'alter tablespace' | tablespace_name | 'end backup; 'from dba_tablespaces where l
Ogging = 'logging ';
Step 3: delete the tablespace data, stop the database, and try to restore it.
Drop tablespace data including contents and datafiles;
Shut immediate
After deletion, Do not worry that you did not write down the timestamp. In fact, there will be a record in the database log.
Sun Jul 26 19:29:37 2015
Drop tablespace data including contents and datafiles
Deleted file/u02/ora11g/oradata/TEST/data01.dbf
Completed: drop tablespace data including contents and datafiles
Sun Jul 26 19:29:54 2015
Step 4: try to restore the data file
We can restore the data file from the hot backup path to the data file path.
Startup mount
! Cp xxxx/hot_backup/*. dbf/u02/ora11g/oradata/TEST

In step 5, we can try to start the Restoration Based on the time point. The Restoration Based on the time point is incomplete because the data changes after the time point will be lost.

SQL> recover database until time '2017-07-26 19:29:37 ';
Media recovery complete.
The recovery process will soon be completed. At this time, the deleted data file is not reflected in the control file, nor can it be seen in v $ datafile.
SQL> Select name from v $ datafile;

NAME
--------------------------------------------------------------------------------
/U02/ora11g/oradata/TEST/system01.dbf
/U02/ora11g/oradata/TEST/sysaux01.dbf
/U02/ora11g/oradata/TEST/undotbs01.dbf
/U02/ora11g/oradata/TEST/testdata. dbf
Step 2: Use resetlogs to open the database.

SQL> alter database open resetlogs;

Database altered.

In this case, you can view v $ datafile and find a new file. But we can see from the name that we are a lost file, but we don't know what is going on, we will recover it.
SQL> select name from v $ datafile;
NAME
--------------------------------------------------------------------------------
/U02/ora11g/oradata/TEST/system01.dbf
/U02/ora11g/oradata/TEST/sysaux01.dbf
/U02/ora11g/oradata/TEST/undotbs01.dbf
/U02/ora11g/product/11.2.0/dbhome_1/dbs/MISSING00004
/U02/ora11g/oradata/TEST/testdata. dbf
In this case, try ls-l to check whether the file exists and find that the file does not exist.
SQL>! Ls-l/u02/ora11g/product/11.2.0/dbhome_1/dbs/MISSING00004
Ls:/u02/ora11g/product/11.2.0/dbhome_1/dbs/MISSING00004: No such file or directory
Let's first rename the file.

Alter database rename file '/u02/ora11g/product/11.2.0/dbhome_1/dbs/MISSING00004' to '/ora11g/oradata/TEST/data01.dbf ';

Database altered.
Step 7: Restore the data file.
You may be prompted to select the recovery method when restoring. We should select auto

SQL> recover datafile '/u02/ora11g/oradata/TEST/data01.dbf ';
ORA-00279: change 970750 generated at 07/26/2015 19:26:36 needed for thread 1
ORA-00289: suggestion:
/U02/ora11g/product/11.2.0/dbhome_1/dbs/arch1_886076275.dbf
ORA-00280: change 970750 for thread 1 is in sequence #1

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
Auto
Log applied.
Media recovery complete.
After the restoration is complete, check v $ recover_file to see if other data files need to be restored.

SQL> select * from v $ recover_file;

No rows selected
After recovery, check the table space status, which is online, but not yet.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS ONLINE
TEMP ONLINE
DATA ONLINE
TESTDATA ONLINE

This is because we still have a problem checking the data.

SQL> select count (*) from test. new_recover;
Select count (*) from test. new_recover
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/data01.dbf'

In step 8, we set the tablespace to Online, and the entire recovery process is complete.
SQL> alter tablespace data online;

Tablespace altered.

SQL> select count (*) from test. new_recover;

COUNT (*)
----------
4667

A lot of Ora errors were encountered throughout the Incomplete recovery process. Some errors were resolved from one problem to another, leading to unrecoverable results. In any case, Oracle has done a lot of work to ensure data recovery.