Full Recovery-media recovery

Recovery phase

1. Corrupted or missing files are restored from backup

2, from the archive and online log files necessary to change the data, the rollback block is also generated at this time, this is called roll forward or restore the cache

3, the database may contain submitted, non-committed change data

4. Rollback block is used to rollback any uncommitted data, this is called rollback or transaction recovery

5. The database is now in the recovery state

No open archive Mode recovery

A, the benefits:

Simple execution with low error rate

Recovery time is the time it takes to restore all the files

B, adverse:

Data loss, must be manually applied

The entire database is restored to the last point in time when the backup was closed

Backup non-archive recovery with redo log files

Sql>shutdown ABORT

To restore data:

unix:cp/backup/*/databases/db01/oradata

Nt:copy D:\disk1\backup\*.* D:\disk1\data\

Copy complete, open instance

Conn/as SYSDBA

STARTUP

Backup non-archive recovery without redo log files

Close the database

Shutdown immediate

restoring data files and control files

Recovery of data Files cp/db01/backup/*dbf/oradata/*

Control File Recovery Cp/db01/backup/*.ctl/oradta

No online logs are backed up and online logs cannot be applied

Recover database until Cancel

sql> shutdown immediate; The database is closed. The database has been uninstalled. The ORACLE routine has been closed. Sql> startup Mount;oracle Routine has been started. Total System Global area  612368384 bytesfixed size                  1250428 bytesvariable size             239078276 bytesdatabase Buffers          364904448 bytesredo buffers                7135232 bytes database loaded. sql> ALTER DATABASE Open;alter database open* 1th Row error: ORA-00338: Log 1 (for thread 1) than control file update ORA-00312: Online log 1 thread 1: ' D:\ORAD Ata\orcl\redo01. LOG ' sql> recover database until cancel, complete media recovery. sql> alter Open;alter database open* 1th Row error: ORA-01589: To open the database you must use the Resetlogs or Noresetlogs option sql> alter D Atabase Open Restlogs;alter Database open restlogs                    * 1th row error: ORA-02288: Invalid open mode sql> ALTER DATABASE open RESETL OGS; the database has changed.

　　

View data dictionary-which file needs to be restored

V$recover_file decide which file needs to be recovered V$archived_log lists all archived logs V$recovery_log all archived logs need to be restored sql> create tablespace TBS datafile '/ u01/test01.dbf ' size 100M; table space created. #在归档模式下alter database datafile '/u01/test01.dbf ' offline; #不在归档下alter database datafile '/u01/test01.dbf ' offline DROP; S Ql> select * from V$recover_file;  file# ONLINE Online_sta ERROR change# time-----------------------------------------------------------------5 OFFLINE OFFLINE 1093567 May-April -15error column There are two possible null errors if the recovery does not require the OFFLINE normalchange# column to return the SCN from the recovery start example illustrates: Archive mode sql> alter system Switch logfile; the system has changed. sql> alter system switch logfile; sql> alter system switch logfile; I only have 3 log groups, and the oldest ones will be replaced.   In view v$recovery_logsql> select * from V$recovery_log;         thread# sequence# time Archive_name---------------------------------------------------------------- 1 3 February-April -15 D:\FLASH_RECOVERY_AREA\ORCL\AR chivelog\2015_04_06\o1_mf_1_                               3_      Bl4wzx0z_. Arc Description: V$log before sequence=3 has been replaced, can only be archived in the recovery sql> ALTER DATABASE datafile 7 Online;alter database datafile 7 online* 1th line error Error: ORA-01113: File 7 requires media recovery ORA-01110: Data file 7: ' D:\ORADATA\ORCL\GULL01. DBF ' sql> recover datafile 7;ora-00279: Change 1129093 (generated on 04/02/2015 21:26:50) is required for thread 1 ORA-00289: Recommendation: D:\FLASH_RECOVERY _area\orcl\archivelog\2015_04_06\o1_mf_1_3_%u_. ARCORA-00280: Change 1129093 (for thread 1) to specify the log in the sequence #3: {<ret>=suggested | filename | AUTO | Cancel}auto The log that has been applied. Complete media recovery. sql> ALTER DATABASE datafile 7 online; Note: You can also use the direct use of recover automatic datafile 7, and again the archive backup of the other directory, you can use the location of the specified archive to restore sql> recover automatic datafile 7;o RA-00279: Change 1143303 (generated on 04/06/2015 20:11:52) is required for thread 1 ORA-00289: recommended: D:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_04_ 06\o1_mf_1_6_%u_. ARCORA-00280: Change 1143303 (for thread 1) in the sequence #6 ORA-00278: This recovery no longer requires log files ' D:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_04_06\O1_ Mf_1_6_bl4y290z_. ARC ' ORA-00308: Unable to open archive log ' D:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_04_06\O1_MF_1_6_bl4y290z_. ARC ' ORA-27041: Cannot open file OSD-04002: Cannot open file O/s-error: (OS 2) The system cannot find the file specified. Specify log: {<ret>=suggested | filename | AUTO | cancel}filenameora-00308: Unable to open archive log ' filename ' ORA-27041: Cannot open file OSD-04002: Cannot open file O/s-error: (OS 2) The system cannot find the file specified. Specify log: {<ret>=suggested | filename | AUTO | Cancel}d:\flash_recovery_area\orcl\archivelog\o1_mf_1_6_bl4y290z_. The log that arc has applied. Complete media recovery. Sql>

　

The V$datafile_header can be checkpoint_change# to V$archived_log. first_change# and next_change#, determine the need to restore those archives

Use of the RECOVER command

A, in the Mount state

recover [AUTOMATIC] database or recover [AUTOMATIC] datafile '/u01/test01.dbf ';

B. Recovery under Database Open

recover [AUTOMATIC] tablespace users or recover [AUTOMATIC] datafile 3;

Automatic automatically applies archive logs and redo log files, and does not use automatic only the redo log is applied

Change directories for normal data files (not for system use)

sql> ALTER DATABASE datafile 7 offline; sql> ALTER DATABASE Rename file ' D:\ORADATA\ORCL\GULL01. DBF ' to ' D:\ORADATA\GULL01. DBF '  2  ; The specified directory for copying files in the operating system sql> recover datafile 7; Complete media recovery. sql> ALTER DATABASE datafile 7 online;

Recovery of System files

The system data file cannot be taken offline while using the process

sql> ALTER DATABASE datafile 1 offline;alter database datafile 1 offline* 1th row error: ORA-01541: System tablespace cannot be taken offline; If necessary, please close

You need to change the path of the system file systems, only in the Mount state.

sql> shutdown immediate; The database is closed. The database has been uninstalled. The ORACLE routine has been closed. Operating system data File copy sql> startup Mount;oracle routine has been started. Total System Global area  612368384 bytesfixed size                  1250428 bytesvariable size             234883972 bytesdatabase Buffers          369098752 bytesredo buffers                7135232 bytes database loaded. sql> ALTER DATABASE datafile 1 offline; sql> ALTER DATABASE Rename file ' D:\ORADATA\ORCL\SYSTEM01. DBF ' to ' D:\ORADATA\SYSTEM01. DBF '; the database has changed. sql> ALTER DATABASE datafile 1 oNline; sql> ALTER DATABASE open; Sql>

Data file loss without backup of recovery data file

such as database shutdown state, the database Mount state, so that the data file (no backup) OFFLINE, open the database, users can only use this table space.

If the database is in the open state, immediately offline the corresponding table space state

Sql> alter Tablespace Gull OFFLINE IMMEDIATE; table space has changed. Sql> select * from V$recover_file;     file# ONLINE  online_ ERROR                   change# time------------------------------------------------------------------ --         7 OFFLINE OFFLINE FILE not FOUND                0         8 OFFLINE OFFLINE                         1147508 June-April -15sql> ALTER DATABASE Create DAT Afile ' D:\ORADATA\GULL01. DBF '; the database has changed. Sql> select * from V$recover_file;     file# ONLINE  online_ ERROR                   change# time------------------------------------------------------------------ --         7 OFFLINE OFFLINE UNKNOWN ERROR            579936 March-March -15         8 OFFLINE OFFLINE                         1147508 June-April- 15 Archive files need to be in order to recover sql> recover automatic tablespace Gull; tablespace online alter tablespace Gull Online;

　　

Recovery of read-only table spaces

1, the table space before the backup is Read-only, now or read-only.

This recovery does not require any redo log

Sql> alter Tablespace Gull Read only; Tablespace has changed. GULL01 Backup of tablespace data file sql> alter system switch logfile; sql> alter system switch logfile; sql> alter system switch logfile; Replace the previously backed-up gull01 directly with no need to do any recovery

2, table space is now Read-wirte, backup is through read-only, need to apply redo and archive

sql> create Tablespace Gull datafile ' d:\oradata\gull01.dbf ' size 20M; table space created. Sql> alter Tablespace Gull Read only; Tablespace has changed. Backing up data files gull01sql> alter Tablespace Gull Read write; tablespace has changed. Sql> Conn Gull/oracle is connected. Sql> CREATE TABLE test0408 as select *from dba_objects; Sql> alter system checkpoint; Sql> alter system checkpoint; Sql> alter system checkpoint; Sql> alter SYSTEM switch;alter SYSTEM switch * 1th line error: ORA-01900: Requires LOGFILE keyword sql> alter system Switch logfile; the system has changed. sql> alter system switch logfile; sql> alter system switch logfile; sql> ALTER DATABASE datafile 7 Online;alter database datafile 7 online* 1th Row error: ORA-01113: File 7 requires media recovery ORA-01110: Data file 7: ' D:\ORADATA\GULL01. DBF ' sql> recover automatic datafile 7; Complete media recovery. sql> ALTER DATABASE datafile 7 online;  Sql> Select COUNT (*) from test0408; COUNT (*)----------50365sql> 

3, now for the read-only, before the Read-write

The corresponding file should be changed to offline, in the recovery

Sql> CREATE TABLE test040801 as select *from dba_objects; Sql> alter Tablespace Gull Read only; Tablespace has changed.                                         Sql> CREATE TABLE test040802 as select *from dba_objects;create table test040802 as select *from dba_objects * Error on line 1th: ORA-01647: tablespace ' Gull ' is read-only and cannot be allocated in space sql> alter Tablespace Gull Read Write;al ter tablespace Gull Read write* 1th line error: ORA-01122: Database file 7 validation failed ORA-01110: Data file 7: ' D:\ORADATA\GULL01. DBF ' ORA-01208: Data file is old version-cannot access current version sql> recover datafile 7;ora-00283: Recovery session canceled due to error ORA-01124: Unable to recover data file 7-file in use or in recovery ORA -01110: Data file 7: ' D:\ORADATA\GULL01. DBF ' sql> ALTER DATABASE datafile 7 offline; sql> recover automatic datafile 7; Complete media recovery. Sql> alter Tablespace Gull Read Write;alter tablespace Gull read write* 1th line error: Ora-01135:dml/query access to file 7 is offline ORA -01110: Data file 7: ' D:\ORADATA\GULL01. DBF ' sql> ALTER DATABASE datafile 7 online; Sql> alter Tablespace Gull read write, table space has changed. Sql> Select COUNT (*) from TEst040801; COUNT (*)----------50366sql>

　　

Full Recovery-media recovery