Four full recovery scenarios in archive Mode

Four full recovery scenarios in archive Mode

In data backup and recovery, rman is basically used, but from the internal principle of the database, for media recovery, there are actually two things to do: restore and recover.
Restore is a copy similar to a physical file, while recover restores Data Based on scn in the database background.
In the archive mode, the following four scenarios can be used for full recovery. Of course, the premise is that there is a backup.
We can manually complete the Backup recovery process without relying on rman. Because the manual process is not complicated.
Manual Backup recovery means hot backup. If the archive is not enabled, the following error is reported.
SQL> alter tablespace data begin backup;
Alter tablespace data begin backup
*
ERROR at line 1:
ORA-01123: cannot start online backup; media recovery not enabled
When archive Hu is enabled, we can use dynamic SQL to generate Hot Standby statements. We filtered the temp tablespace because it is not required.
Select 'alter tablespace' | tablespace_name | 'in in backup; 'from dba_tablespaces where logging = 'logging'
Alter tablespace SYSTEM begin backup;
Alter tablespace SYSAUX begin backup;
Alter tablespace UNDOTBS begin backup;
Alter tablespace DATA begin backup;
Alter tablespace TESTDATA begin backup;

Then copy the physical file to a specified directory.
End backup is used to complete hot backup. This process is common and simple.

With backup, let's take a look at four full recovery scenarios. We can all destroy them manually.
The first is the data open state, where common data files are damaged.
Assume that the table test under the test user is stored on the tablespace data.

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

COUNT (*)
----------
0
We will destroy it manually.
SQL>! Rm/u02/ora11g/oradata/TEST/data02.dbf
Then, make a global check point. At this time, an error is reported for the previously accessible table.

SQL> alTer system checkpoint;

System altered.

SQL> select count (*) from test. test;
Select count (*) from test. test
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/data02.dbf'
We can try the offline tablespace, but the offline fails because the data file is lost.
SQL> alter tablespace data offline;
Alter tablespace data offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline-cannot do a normal offline
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/data02.dbf'
You need to use offline immediate instead of writing the checkpoint.
SQL> alter tablespace data offline immediate;

Tablespace altered.

At this time, we can find the corresponding files from the hot backup to restore them.
SQL>! Cp/u02/ora11g/oradata/hot_bak/data02.dbf/u02/ora11g/oradata/TEST

Then it is restored.
SQL> recover tablespace data;
Media recovery complete.
Set the tablespace to online after recovery.
SQL> alter tablespace data online;

Tablespace altered.

At this time, the table can be accessed again.
SQL> select count (*) from test. test;

COUNT (*)
----------
0

In the second scenario, files such as system files and undo tablespaces are damaged when the database is shut down.
We delete several system data files.
[Ora11g @ oel1 TEST] $ rm system01.dbf
[Ora11g @ oel1 TEST] $ rm sysaux01.dbf
[Ora11g @ oel1 TEST] $ rm undotbs01.dbf
Then, an error will be reported when the database is started.
SQL> startup
Oracle instance started.

Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 125832984 bytes
Database Buffers 75497472 bytes
Redo Buffers 6569984 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1-see DBWR trace file
ORA-01110: data file 1: '/u02/ora11g/oradata/TEST/system01.dbf'

The problem is obvious at this time. After a simple check, the system data file does not exist.
At this time, the system file is directly copied from the hot standby.
SQL>! Cp/u02/ora11g/oradata/hot_bak/system01.dbf/u02/ora11g/oradata/TEST
Then you can directly restore the data file.
SQL> recover datafile 1;
Media recovery complete.
After that, you can start the database and find several other data files are lost. The restoration and restoration methods are similar.

SQL>! Cp/u02/ora11g/oradata/hot_bak/sysaux01.dbf/u02/ora11g/oradata/TEST

SQL>! Cp/u02/ora11g/oradata/hot_bak/undo */u02/ora11g/oradata/TEST

SQL> recover datafile 2;
Media recovery complete.
SQL> recover datafile 3;
Media recovery complete.

SQL> alter database open;

Database altered.

The third scenario is to delete common data files when the database is stopped. There are still some differences in the operation at this time.
We still destroy it manually.
[Ora11g @ oel1 TEST] $ rm data02.dbf
Then, an error will be reported when the database is started.

SQL> startup
ORACLE instance started.

Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 125832984 bytes
Database Buffers 75497472 bytes
Redo Buffers 6569984 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4-see DBWR trace file
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/data02.dbf'

After a simple check, we will find that the corresponding tablespace is DATA.

SQL> select name from v $ tablespace where ts # in (select ts # from v $ datafile where file # = 4 );

NAME
------------------------------
DATA
At this time, because the database is in the mount stage, it cannot perform the offline operation, and the data file can be restored directly for data recovery.
SQL>! Cp/u02/ora11g/oradata/hot_bak/data02.dbf/u02/ora11g/oradata/TEST
SQL> recover tablespace data;
Media recovery complete.

SQL> alter database open;

Database altered.

The fourth scenario is the database open stage, where new data files are corrupted.
In this case, we can create a data file.

SQL> create tablespace testdat datafile '/u02/ora11g/oradata/TEST/testdata. dbf' size 5 M;

Tablespace created.
SQL> conn test/test
Connected.
SQL> create table testdat tablespace testdat as select * from all_objects;

Table created.
Then we immediately delete the data file. At this time, the data file is not in the backup set and cannot be restored.
SQL>! Rm/u02/ora11g/oradata/TEST/testdata. dbf

Refresh the buffer cache, and the table will be inaccessible instantly.
SQL> conn/as sysdba
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> select count (*) from test. testdat
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u02/ora11g/oradata/TEST/testdata. dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
If there is no backup at this time, we cannot recover it directly.
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 5-file is in use or recovery
ORA-01110: data file 5: '/u02/ora11g/oradata/TEST/testdata. dbf'
First, we need to give the corresponding tablespace to offline
SQL> alter tablespace testdat offline immediate;

Tablespace altered.

Then try to create an empty data file to restore
SQL> alter database create datafile '/u02/ora11g/oradata/TEST/testdata. dbf ';

Database altered.

Restore data files
SQL> recover datafile 5;
Media recovery complete.

SQL> alter tablespace testdat online;

Tablespace altered.

After the restoration is completed, the table can be accessed again.
SQL> select count (*) from test. testdat;

COUNT (*)
----------
5877