Performing user-managed Database-18.7, performing complete user-managed Media Recovery

18.7. Performing Complete user-managed Media Recovery
To complete a consistent backup, restoring the database to the current SCN is the best result. You can restore an entire database, restore a single tablespace, or recover a data file. Consistent recovery does not require resetlogs to open the database, and non-conformance recovery requires resetlogs to open the database. Backup and Recovery Basics provides information about media recovery.

18.7.1, performing Closed Database Recovery
You can recover all corrupted data files in one operation, or separate operations to recover each corrupted data file.

18.7.1.1, Preparing for Closed Database Recovery
(1) Close the database and check the media device that is having problems
(2) If the problem that caused the media failure is temporary, if the data is not corrupted (for example, the disk or controller is down), no media recovery is required: Just start the database and start the operation again. If you can't fix it, take the following steps

18.7.1.2, Restoring Backups of the damaged or Missing Files
(1) Determine which data files need to be restored
(2) Locate the most recent backup of the corrupted data file. Restore only corrupted data files: Do not restore data files that are not corrupted or any redo log files; only one data file (with archive) can be created if there is no backup
ALTER DATABASE create datafile ' xxx ' as ' xxx ' size xxx reuse
(3) Use the operating system command to restore the data file to the default location or to a new location.
Alter DATABASE rename file ' xxx ' to ' xxx ';

18.7.1.3, Recovering the Database
(1) Connect to the database with System administrator privileges, start the database to mount
(2) query v$datafile get data file name and status
(3) Data files that need to be recovered must be online, in addition to the offline normal table space or read-only table space
Select ' Alter dabase datafile ' | | name | | ' online; ' from V$datafile;
(4) Execute recover database,recover tablespace xxx,recover datafile ' xxx ' and other statements
(5) Without automatic recovery, each indicated log must be accepted or rejected. If automatically restored, the database automatically applies the logs.
(6) The media is restored and the database returns: Media recovery complete.
(7) ALTER DATABASE open

18.7.2, performing datafile Recovery in an Open Database
When the database is open, media failure can not be written when the error is returned, the general table space is only corrupted data file offline, can not be queried when the error is returned, the general tablespace is only the corrupted data file is not offline.
When the database is open, the recovery process cannot be used as a full media restore of the system table space. If the data file for the system tablespace is corrupted, the database shuts down automatically.

18.7.2.1, Preparing for Open Database Recovery
(1) The database is open and it is found that it needs to be restored, taking the table space that contains the corrupted data file offline.
(2) If the problem that caused the media failure is temporary, if the data is not corrupted (for example, the disk or controller is down), no media recovery is required: Just start the database and start the operation again. If you can't fix it, take the following steps

18.7.2.2, Restoring Backups of the inaccessible datafiles
(1) Determine which data files need to be restored
(2) Locate the most recent backup of the corrupted data file. Restore only corrupted data files: Do not restore data files that are not corrupted or any redo log files; only one data file (with archive) can be created if there is no backup
ALTER DATABASE create datafile ' xxx ' as ' xxx ' size xxx reuse
sql> ALTER DATABASE Create DataFile '/oracle/oradata/boss/testtbs04_01.dbf ' as '/oracle/oradata/boss/testtbs04_01. DBF ' size 10m reuse;
(3) Use the operating system command to restore the data file to the default location or to a new location.
Alter DATABASE rename file ' xxx ' to ' xxx ';

18.7.2.3, Recovering Offline tablespaces in an Open Database
(1) Execute recover database,recover tablespace xxx,recover datafile ' xxx ' and other statements
(2) Without automatic recovery, each indicated log must be accepted or rejected. If automatically restored, the database automatically applies the logs.
(3) The media is restored and the database returns: Media recovery complete.
sql> recover automatic tablespace testtbs04;
(4) ALTER DATABASE open

Simulation 1, create a table space Testtbs04, create a table, delete the corresponding data file, do a restore of the shutdown database
(1)
sql> Create Tablespace Testtbs04
2 datafile '/oracle/oradata/boss/testtbs04_01.dbf ' size 10m
3 autoextend on next 1m maxsize Unlimited
4 Logging
5 Extent Management Local Autoallocate
6 BlockSize 8k
7 Segment Space Management Auto
8 Flashback on;

(2)
Sql> CREATE TABLE test04 (ID number, name VARCHAR2 ()) tablespace testtbs04;
sql> INSERT INTO test04 values (1, ' xxxxx ');
sql> INSERT INTO test04 values (2, ' yyyyy ');
Sql> commit;

(3)
Sql> select group#,members,sequence#,archived,status,first_change# from V$log;

group# members sequence# ARC STATUS first_change#
---------- ---------- ---------- --- ---------------- -------------
1 1 0 YES UNUSED 0
2 1 0 YES UNUSED 0
3 1 1 NO current 697986

sql> alter system switch logfile;
sql> alter system switch logfile;
sql> alter system switch logfile;
Sql> select group#,members,sequence#,archived,status,first_change# from V$log;

group# members sequence# ARC STATUS first_change#
---------- ---------- ---------- --- ---------------- -------------
1 1 2 YES INACTIVE 707835
2 1 3 YES INACTIVE 707837
3 1 4 NO current 707840
(4)
$ RM-RF TESTTBS04_01.DBF

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

sql> startup Open;

sql> col "filename" for A40;
sql> col "Table space name" for A10
Sql> Set Linesize 150
Sql>
Select
Ts.name "Table space name"
, df.file# "File Number"
, df.checkpoint_change# "Checkpoint"
, df.name "file name"
, df.status "Online status"
, rf.error "Recovery Reason"
, rf.change# "System change Number"
, Rf.time
From V$tablespace ts,v$datafile Df,v$recover_file RF
where ts.ts#=df.ts# and df.file#=rf.file#
Order BY df.file#;

Sql> Select
2 Ts.name "Table space name"
3, df.file# "file Number"
4, df.checkpoint_change# "Checkpoint"
5, df.name "file name"
6, df.status "Online status"
7, Rf.error "Recovery Reason"
8, rf.change# "System change Number"
9, Rf.time
From V$tablespace ts,v$datafile Df,v$recover_file RF
One where ts.ts#=df.ts# and df.file#=rf.file#
Order BY df.file#;

Tablespace name file number Check point filename online recovery reason system change number time
---------- ---------- ---------- ---------------------------------------- ------- ------------------ ---------- ------ ------
TESTTBS02 8 652783/oracle/oradata/boss/testtbs02_01.dbf OFFLINE OFFLINE NORMAL 0
TESTTBS04 707840/oracle/oradata/boss/testtbs04_01.dbf ONLINE FILE not FOUND 0

(5)
sql> ALTER DATABASE Create DataFile '/oracle/oradata/boss/testtbs04_01.dbf ' as '/oracle/oradata/boss/testtbs04_01. DBF ' size 10m reuse;

Sql> Select File#,name,status,checkpoint_change#,recover from V$datafile_header where file#=10;

file# NAME STATUS checkpoint_change# REC
---------- ---------------------------------------- ------- ------------------ ---
10/ORACLE/ORADATA/BOSS/TESTTBS04_01.DBF ONLINE 707602 YES

(6)
sql> recover automatic tablespace testtbs04;
Media recovery complete.

sql> ALTER DATABASE open;

Sql> select * from test04;

        ID NAME
--------------------------------------------------
         1 xxxxx
         2 YYYYY