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

18.7. Performing Complete user-managed Media Recovery
After a consistent backup, restoring the database to the current SCN is the best result. The ability to recover an entire database. Restores a single table space. or recover data files. 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
The ability to recover all corrupted data files in one operation. It is also possible to recover each corrupted data file separately from the operation.

18.7.1.1, Preparing for Closed Database Recovery
(1) Close the database and check for failed media devices
(2) Assume that the problem that caused the media failure is temporary, assuming that the data is not corrupted (for example, the disk or controller is out of power), there is no need for media recovery: just start the database. Start the operation again.

Assuming that it cannot be repaired, proceed to the following steps

18.7.1.2, Restoring Backups of the damaged or Missing Files
(1) Infer which data files need to be restored
(2) Find a recent backup of the corrupted data file. Restore only corrupted data files: Do not restore data files that are not corrupted or redo log files regardless of what. Assume that there are no backups whatsoever. Only one data file (with archive) can be created
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) Run recover database,recover tablespace xxx,recover datafile ' xxx ' and other statements
(5) If you do not voluntarily recover, you must accept or reject each indicated log. Assuming that you are actively recovering, the database itself actively applies the logs.

(6) After the media is restored, the database returns: Media recovery complete.

(7) ALTER DATABASE open

18.7.2, performing datafile Recovery in an Open Database
Media failure occurs when the database is open. cannot be written when an error is returned. The general tablespace is simply a corrupted data file that is offline and cannot be queried when an error is returned, and the general tablespace is simply a corrupted data file that is not offline.
When the database is open, the recovery process cannot be used as a full media restore of the system table space.

Assume that the data file for the system tablespace is corrupted, and the database shuts itself down voluntarily.

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 including the corrupted data file offline.

(2) Assume that the problem that caused the media to fail is temporary, assuming that the data is not corrupted (for example. Disk or controller power-down), no media recovery required: Just start the database, and again start the operation. Assuming that it cannot be repaired, proceed to the following steps

18.7.2.2, Restoring Backups of the inaccessible datafiles
(1) Infer which data files need to be restored
(2) Find a recent backup of the corrupted data file.

Restore only corrupted data files: Do not restore data files that are not corrupted or redo log files regardless of what. Suppose there is no backup, only one data file (with archive) can be created
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) Run recover database,recover tablespace xxx,recover datafile ' xxx ' and other statements
(2) If you do not voluntarily recover, you must accept or reject each indicated log. Assuming that you are actively recovering, the database itself actively applies the logs.
(3) After the media is restored, 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 "file name" for A40;
sql> col "tablespace name" for A10
Sql> set linesize
sql>
Select
  ts.name "tablespace name"
 , DF.F ile# "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 file name 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

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