I. Understand what is database recovery When we use a database, the content of the database will always be reliable, correct, but due to the fault of computer systems (hardware failure, network fault, process failure and system failure) affects the database The system's operation affects the correctness of the data in the database, and even disrupts the database to lose all or part of the data in the database. Therefore, after the above fault occurs, it is desirable to reconstruct this complete database, which is called database recovery. The recovery process can be roughly divided into restore and recover processes.
Database recovery can be divided into the following categories:
1.1 Consistent recovery of instance failures
An example failure occurs when an example unexpectedly (such as power down, background process failure, etc.) or is expected to abort (issuing a shutdoum abort statement). Case Recovery The consistent state of transaction before returning the database to the fault. If an instance failure is discovered online, the media is recovered. In other cases Oracle starts instance recovery when it is started at the next database (assembled and opened). If necessary, from the assembly state to the open state, automatically excit the instance recovery, by the following processing:
(1) For the data that is not recorded in the recovery data file, it is rolled forward. This data is recorded in the online log,
Including the content recovery of the rollback segment.
(2) Rolling the uncommitted transaction, press step 1 to regenerate the operation specified by the rollback segment.
(3) Release the resources held by the firm during the fault.
(4) Solve any unresolved distributed transactions that are submitted in a fault.
1.2 Media fault or inconsistent recovery of file errors
The media fault is a fault when a file, some or disk of a file cannot be read or cannot be written. File error Generally, the unexpected error causes the file to be inconsistent with the file being deleted or accidentally. The database in this state is inconsistent, requiring DBA manual to perform the recovery of the database, which has two forms, which are decided to run the archive and backup mode of the database.
(1) Complete media recovery can restore all lost modifications. Under normal circumstances, there is a possibility of a backup of a database and the database is running in archiving and there is a available archive log. For different types of errors, there are different types of fully recovery available, which is determined to destroy the availability of files and databases.
(2) Incomplete media recovery is a medium recovery that is not possible when it is impossible or not required. Reconstructing the damaged database to restore the consistency of a transaction before the media failure or before the user's error. Incomplete media recovery has different types of use, which determines the case where incomplete media recovery is required, with the following types: revoke, time and modified incomplete recovery.
Cancel Recovery: In some cases, incomplete media recovery must be controlled, and DBA can revoke the operation of the specified point. Restore the revocation is destroyed in one or more log groups (online or archive), which cannot be used for recovery procedures, so media recovery must be controlled, so that the media recovery must be controlled to use the nearest, untreated log. The restore operation is suspended after the data file.
Based on time (TIME) and amended (SCN) recovery: If DBA wants to recover some specified point in the past, it is an ideal incomplete media recovery, which is generally restored before recovery to a particular operation. Before you accidentally delete a data table.
Chapter 2. Database Recovery Case Test Environment
2.1 Database Environment
All the following cases are passed through the test, the environment is:
OS: Windows 2000 Server
DB: Oracle 816
DBNAME: TEST
Data file: SQL> SELECT FILE #, Status, Enabled, Name from V $ datafile;
File # status enabled name
-------------------------------------------------- ----------------
1 System Read Write D: /oracle/oradata/test/system01.dbf
2 Online Read Write D: /oracle/oradata/test/rbs01.dbf
3 Online Read Write D: /oracle/oradata/test/USERS01.DBF
4 Online Read Write d: /oracle/oradata/test/temp01.dbf
5 Online Read Write D: /oracle/oradata/test/tools01.dbf
6 Online Read Write D: /oracle/oradata/test/indx01.dbf
Control file:
SQL> SELECT * FROM V $ ControlFile;
STATUS NAME
-------------------------------------------------- -------------------
D: /oracle/oradata/test/control01.ctl
D: /oracle/oradata/test/control02.ctl
D: /oracle/oradata/test/control03.ctl
Online log:
SQL> SELECT * FROM V $ logfile;
Group # status member
-------------------------------------------------- -------------------
1 Stale D: /oracle/oradata/test/redo01.log
2 D: /oracle/oradata/test/redo02.log
3 Stale D: /oracle/oradata/test/redo03.log
2.2 Database Backup Script
Cold backup script:
Rem script: coldbak.sql
Rem Creater: chenjiping
Rem Date: 5.8.2003
Rem Desc: Offline Full Backup Database
--Connect Database
CONNECT INTERNAL / Password;
--Shutdown Database
Shutdown immediate;
--Copy Data File
Xcopy D: / Oracle / ORADATA / TEST / *. DBF D: / Database / H / R;
- Copy Control File
! Xcopy D: / Oracle / ORADATA / TEST / *. CTL D: / Database / H / R;
--Copy log file
! Xcopy D: / Oracle / ORADATA / TEST / *. LOG D: / DATABASE / H / R;
--Startup Database
Startup;
Description:
1, the above scripts are backed up in the database close data file, online log, control files (in one
Record), if successful backup, all files are consistent;
2, no backup parameter file, parameter file can be backed up, no need to back up each time, only need to back up after changing the setting;
3. If the above command is not executed in successful, the backup will be invalid. If the connection database is unsuccessful, then the database is not successful, then the backup is invalid; 4, the cold backup suggestion under manual intervention.
Database OS thermal backup script
Rem script: hotbak.sql
Rem Creater: chenjiping
Rem Date: 5.8.2003
Rem Desc: Backup All Database DataFile in Archive
--Connect Database
CONNECT INTERNAL / Password;
--archive
Alter System Archive Log Current;
--Start
Alter TableSpace System Begin Backup;
! xcopy d: /oracle/oradata/test/system01.dbf d: / databak / h / r;
Alter TableSpace System End Backup;
Alter TableSpace RBS Begin Backup;
! xcopy d: /oracle/oradata/test/rbs01.dbf d: / databak / h / r;
Alter TableSpace RBS End Backup;
Alter TableSpace Uses Begin Backup;
! xcopy d: /oracle/oradata/test/Users01.dbf d: / databak / h / r;
Alter TableSpace Users End Backup;
Alter TableSpace Tools Begin Backup;
! xcopy d: /oracle/oradata/test/tools01.dbf d: / databak / h / r;
Alter TableSpace Tools End Backup;
Alter TableSpace Indx Begin Backup;
! xcopy d: /oracle/oradata/test/indx01.dbf d: / databak / h / r;
Alter TableSpace Indx End Backup;
--end
--Bak Control File
--binary
Alter Database Backup ControlFile To 'D: /DATABAK/Controlbinbak.000';
--ASCII
Alter Database Backup ControlFile to TRACE;
Alter System Archive Log Current;
Description:
1. The hot backup must be running under the database archive;
2, the above scripts can back up all data files in the database in the database (except for the temporary data file), there is no need to back up the online log;
3. The archive log requires at least all logs after a complete backup;
4. If the above command is not executed in successful, the backup is invalid. If the connection database is unsuccessful, the backup is invalid.
RMAN backups are only in the case where there is a recovery directory, and if the directory is not resume, the situation is roughly similar. The following is the script of the hot backup full backup of RMAN:
# Script: Bakup.rcv
# Creater: chenjiping
# Date: 5.8.2003
# Desc: Backup All Database DataFile in Archive with Rman
# Connect Database
Connect RCVCAT RMAN / RMAN @ BACK;
Connect Target InterNal / Virpure;
# Start backup databaserun {
ALLOCATE CHANNEL C1 TYPE DISK;
Backup full tag 'dbfull' format 'd: / backup / full% u_% s_% p' Database
Include Current ControlFile;
SQL 'ALTER System Archive Log Current';
Release channel C1;
}
# end
Description:
1. The database must run in archiving mode;
2, RMAN will automatically back up the data file, run reliable;
3, archive log additional backup processing, but at least the log is required to save a backup;
4, there is no need to use RMAN to do cold backup, the effect is not good.
The above-described test environment for recovery cases in the database is described above with some backup test scripts, and other backup scripts can evolve according to the above scripts or in the case.
Database automatic instances will not be described, only examples of media errors or recovery caused by human errors are illustrated.
The above includes testing verification on Windows Oracle816, which is slightly different from different operating systems.
Chapter 3. Learn about information related to recovery
1, understand the alarm log file
The alarm log file generally records the start / closing information, archive information, backup information, recovery information, common error information, some database modification records, etc. General order rules are
The path to the alarm log file is determined according to the initialization parameter background_dump_dest. For example, on my machine, this parameter value is D: / Oracle / Admin / Test / bdump, then you can find the file under the path.
2, background process tracking file
The path to the background process tracking file is consistent with the path of the alarm log file. In some cases, you can learn more about the information you need to recover through the background of the background trace file. If you need to recover in the database, there is often such a statement in the alarm log file:
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 1 - See Dbwr TRACE FILE
By prompted DBWR trace file, you can query more detailed information.
3, V $ RECOVER_FILE and V $ Recovery_log
This is two dynamic performance views, which can be viewed under mount. By these two views, you can learn more about the data file that needs to be recovered with the archive log that needs to be used.
Chapter 4. Database Recovery Case
4.1 Backup and recovery in non-archive mode
Backup solution: using OS cold backup
1. Connect the database and create a test table
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT);
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2. Backup database
SQL> @ coldbak.sql or svrmgrl @ colorbak.sql in DOS
3. Insert the record
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> Select * from test; a
-------------------
1
2
4. Turn off the database
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down.
5. Destroy one or more data files, such as deleting user01.dbf
C: /> del D: /oracle/oradata/test/users01.dbf
The simulation media is destroyed.
6. Restart the database, you will find the following error
SQL> Startup
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 3 - See Dbwr TRACE FILE
ORA-01110: Data file 3: 'D: /oracle/oradata/test/USERS01.DBF'
In the alarm file, there will be more detailed information
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 3 - See Dbwr TRACE FILE
ORA-01110: Data file 3: 'D: /oracle/oradata/test/USERS01.DBF'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
7. Copy backup to restore to the original position (Restore process)
C: /> Xcopy D: / Database /*.* D: / Oracle / ORADATA / TEST / H / R / S
8. Open the database and check the data
SQL> ALTABASE OPEN;
Database altered.
SQL> SELECT *.
A
---------------------------------------
1
Here you can find that the database is successful, but the data before the backup is lost before the crash.
Description:
1. The recovery scheme in the non-invasive mode is very small. In general, there can only be a recovery method, which is a complete recovery of the rear backup of the database, just need to copy the original backup (restore), no required
2. In this case, it can be fully restored to the backup point, but may be lost data, and the data before the backup and the collapse will be lost;
3, no matter how much data files are destroyed or online logs or control files, you can recover, because this recovery process is all cold backup files for RESTORE, and all files on this backup point are consistent, with the latest The database does not matter, it is like a previous "point" more than putting the database;
4. For non-invasive modes, the best way is to use OS's cold backup. It is recommended not to use RMAN to make cold backups. The effect is not good because RMAN does not back up online logs, and Restore can solve the problem. 5. If there is no backup Online logs, such as RMAN backups, you need to use the unfull recovery method to recreate the online log file.
4.2 Missing or damage a data file in archiving mode
4.2.1 OS Backup Solution
Damaged or lost a data file in the archive mode, if there is a corresponding backup of archive logs, recovery is still relatively simple, and you can do as little DOWN machine time, and can be completely recovered by the database.
1. Connect the database, create a test table and insert record
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT) TABLESPACE USERS;
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, backup database
SQL> @ hotbak.sql or svrmgrl @ hotbak.sql under DOS
3. Continue to insert record in the test table
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
4, turn off the database, simulate the lost data file
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down
C: /> del D: /oracle/oradata/test/users01.dbf
The simulation media is destroyed.
5, start the database error, offline this data file:
SQL> Startup
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 3 - See Dbwr TRACE FILE
ORA-01110: Data file 3: 'D: /oracle/oradata/test/USERS01.DBF'
You can also view alarm files (see a recovery case) or dynamic view V $ Recover_File
Such as SQL> SELECT * FROM V $ Recover_File;
File # Online Error Change # TIME
---------- ------------------------- ------ 3 Online 1013500 2003-05-07
Offline data file
SQL> ALTER DATABASE DATAFILE 3 OFFLINE DROP;
Database altered.
6. Open the database, copy backup (Restore), recover (recover) the data file, and online:
SQL> ALTABASE OPEN;
Database altered.
Copy backup from backup
Copy D: / DataBak / Users01.dbf d: / oracle / oradata / test;
Restore this data file
SQL> Recover DataFile 3;
ORA-00279: Change 1053698 Generated AT
05/07/2003
17:51:26
Needed for
Thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00304.arc
ORA-00280: Change 1053698 for thread 1 is in sequence # 304
Specify log: {
Auto
ORA-00279: Change 1053701 Generated AT
05/07/2003
17:51:39
Needed for
Thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00305.arc
ORA-00280: Change 1053701 for thread 1 is in sequence # 305
ORA-00278: log file 'd: /oracle/oradata/test/archive/testt001s00304.arc' no longer needed for this recovery log applies.
Media Recovery Complete.
Restore success, online data file
SQL> ALTER DATABASE DATAFILE 3 Online;
Database altered.
7, check the data of the database (completely recovery)
SQL> SELECT *.
A
--------------------------------
1
2
Description:
1
2. Backup can be backed up in a full backup database. For special circumstances, only specific data files can be backed up, such as only the user table space (generally for some of the specific frequent data files, can separately increase the backup frequency separately. );
3, if the damage is discovered during the recovery process, you can use a recovery method of a data file (step 5, you need to go offline by one by one, step 6 needs to separate the data files. Restore), you can also use the recovery method of the entire database;
4. If it is damaged by the system table space, this method cannot be used.
4.2.2 RMAN Backup Solution
RMAN can also be backed up, and backup and recovery methods will be easier to reliably than OS backups.
1. Connect the database to create a test table and insert the record SQL> Connect International / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT) TABLESPACE USERS;
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, backup database table space Users
C: /> rman
Recovery Manager: Release 8.1.6.0.0 - Production
Rman> Connect RCVCAT RMAN / RMAN @ BACK
RMAN-06008: Connected to Recovery Catalog Database
Rman> Connect Target InterNal / Virpure
RMAN-06005: Connected to Target Database: Test (dbid = 1788174720)
Rman> Run {
2> Allocate Channel C1 TYPE DISK;
3> Backup tag 'tsuser' format 'd: / backup / tsuser_% u_% s_% p'
4> TableSpace Users;
5> Release Channel C1;
6>}
RMAN-03022: Compiableing Command: Allocate
RMAN-03023: Executing Command: Allocate
RMAN-08030: Allocated Channel: C1
RMAN-08500: Channel C1: SID = 16 DevType = Disk
RMAN-03022: Compiableing Command: Backup
RMAN-03025: Performing Implicit Partial Resync of Recovery Catalog
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complete
RMAN-03023: Executing Command: Backup
RMAN-08008: Channel C1: Starting Full DataFile Backupset
RMAN-08502: set_count = 5 set_stamp = 494177612 CREATION_TIME =
16-MAY-03
RMAN-08010: Channel C1: Specifying DataFile (s) in Backupset
RMAN-08522: Input datafile fno = 00003 name = d: /oracle/oradata/test/user01.dbf
RMAN-08013: Channel C1: Piece 1 Created
RMAN-08503: Piece Handle = D: / Backup / TSUSER_05EN93AC_5_1 Comment = NONE
RMAN-08525: Backup Set Complete, ELAPSED TIME:
00:00:01
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complaters-03022: Compiableing Command: Release
RMAN-03023: Executing Command: Release
RMAN-08031: Released Channel: C1
Rman>
3. Continue to insert record in the test table
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
---------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> R
1 * ALTER SYSTEM SWITCH LOGFILE;
System altered.
4, turn off the database, simulate the lost data file
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down
C: /> Del D: /oracle/oradata/test/user01.dbf
5, start the database, check the error
SQL> Startup
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 3 - See Dbwr TRACE FILE
ORA-01110: Data File 3: 'D: /oracle/oradata/test/User01.dbf'
6, open the database first
SQL> ALTER DATABASE DATAFILE 3 OFFLINE DROP;
Database altered.
SQL> ALTABASE OPEN;
Database altered.
7, restore this table space
The recovery script can be a resume single data file
Run {
ALLOCATE CHANNEL C1 TYPE DISK;
Restore DataFile 3;
Recover datafile 3;
SQL 'ALTER DATABASE DATAFILE 3 Online';
Release channel C1;
}
It can also be, restore table space
Run {
ALLOCATE CHANNEL C1 TYPE DISK;
RESTORE TABLESPACE;
Recover TableSpace User;
SQL 'ALTER DATABASE DATAFILE 3 Online';
Release channel C1;
}
The process is as follows:
C: /> rman
Recovery Manager: Release 8.1.6.0.0 - Production
Rman> Connect RCVCAT RMAN / RMAN @ BACK
RMAN-06008: Connected to Recovery Catalog Database
RMAN> Connect Target InterNal / Virpureerman-06005: Connected to Target Database: Test (dbid = 1788174720)
Rman> Run {
2> Allocate Channel C1 TYPE DISK;
3> Restore DataFile 3;
4> Recover datafile 3;
5> SQL 'ALTER DATABASE DATAFILE 3 Online';
6> Release Channel C1;
7>}
// Output content length, omitted - editor
Rman>
8, check if the data is complete
SQL> ALTABASE OPEN;
Database altered.
SQL> SELECT *.
A
---------------------------------------
1
2
Description:
1. RMAN can also realize the recovery of single table space or data file, and the recovery process can be under Mount or Open mode if it is restored in the Open mode, and the DOWN machine time can be reduced;
2. If damage is a data file, it is recommended to restore it under the Open mode;
3, here you can see that when the RMAN is restored to the table space, the code is relatively simple, and the reliability of backup and recovery can be guaranteed, so it is recommended to use RMAN backup and recovery.
4.3 Lost multiple data files to implement the recovery of the entire database.
4.3.1 OS Backup Solution
Damage (loss) multiple data files in the OS backup archiving mode, and recover the entire database
1. Connect the database, create a test table and insert record
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT);
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, back up the database, back up the data file after the temporary data file
SQL> @ hotbak.sql or svrmgrl @ hotbak.sql under DOS
3. Continue to insert record in the test table
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
---------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
4, turn off the database, simulate the lost data file
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down
C: /> Del D: /oracle/oradata/test/system01.dbf
C: /> del D: /oracle/oradata/test/indx01.dbf
C: /> Del D: /oracle/oradata/test/tools01.dbf
C: /> DEL D: /Oracle/oradata/test/rbs01.dbf
Simulated media destruction (here deleted multiple data files) 5, start the database, check the error
SQL> Startup
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 1 - See Dbwr TRACE FILE
ORA-01110: Data file 1: 'd: /oracle/oradata/test/system01.dbf'
Details You can view alarm files
ORA-1157 Signalled During: Alter Database Open ...
Thu May 08
09:39:36
2003
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 1 - See Dbwr TRACE FILE
ORA-01110: Data file 1: 'd: /oracle/oradata/test/system01.dbf'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
Thu May 08
09:39:36
2003
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 2 - See Dbwr TRACE FILE
ORA-01110: Data file 2: 'D: /oracle/oradata/test/rbs01.dbf'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
Thu May 08
09:39:36
2003
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 5 - See Dbwr TRACE FILE
ORA-01110: Data file 5: 'D: /oracle/oradata/test/tools01.dbf'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
Thu May 08
09:39:36
2003
Errors in file d: /oracle/admin/test/bdump/testdbw0.trc:
ORA-01157: Cannot Identify / Lock Data File 6 - See Dbwr TRACE FILE
ORA-01110: Data file 6: 'D: /oracle/oradata/test/indx01.dbf'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: UNABLE TO OPEN FILEO / S-Error: (OS 2) The system cannot find the specified file.
You can see by query V $ recover_file
SQL> SELECT * FROM V $ Recover_File;
File # Online Error Change # TIME
---------- ------------------------- --------
1 Online File Not Found 0
2 Online File Not Found 0
5 Online File Not Found 0
6 Online File Not Found 0
There are four data files that need to be recovered
6, copy backup back to the original place (Restore), start recovery database (Recover)
RESTORE process:
C: /> COPY D: /DATABAK/SYSTEM01.DBF D: / Oracle / ORADATA / TEST /
C: /> COPY D: /DATABAK/TEST/indx01.dbf d: / oracle / ORADATA / TEST /
C: /> Copy D: /DATABAK/TEST / TOOLS01.DBF D: / Oracle / ORADATA / TEST /
C: /> COPY D: /DATABAK/TEST / RBS01.DBF.DBF D: / Oracle / ORADATA / TEST /
Recover process:
SQL> Recover data;
ORA-00279: Change 1073849 Generated AT
05/08/2003
08:58:35
Needed for thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00311.arc
ORA-00280: Change 1073849 for thread 1 is in sequence # 311
Specify log: {
Auto
ORA-00279: Change 1073856 Generated AT
05/08/2003
09:03:27
Needed for thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00312.arc
ORA-00280: Change 1073856 for thread 1 is in sequence # 312
ORA-00278: log file 'd: /oracle/oradata/test/archive/testt001s00311.arc' NO
Longer Needed for this Recovery
ORA-00279: Change 1073858 Generated AT
05/08/2003
09:11:43
Needed for thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00313.arc
ORA-00280: Change 1073858 for thread 1 is in sequence # 313
ORA-00278: log file 'D: /oracle/oradata/test/archive/testt001s00312.arc' NO
Longer Needed for this Recovery
ORA-00279: Change 1073870 Generated AT05 / 08/2003
09:11:46
Needed for thread 1
ORA-00289: Suggestion: D: /oracle/oradata/test/archive/testt001s00314.arc
ORA-00280: Change 1073870 for thread 1 is in sequence # 314
ORA-00278: log file 'd: /oracle/oradata/test/archive/testt001s00313.arc' NO
Longer Needed for this Recovery
Log Applied.
Media Recovery Complete.
7, open the database, check the data of the database (completely restore)
SQL> ALTABASE OPEN;
Database altered.
SQL> SELECT *.
A
---------------------------------------
1
2
Description:
1. As long as there is a backup and archive, you can realize full recovery of the database (no data);
2. Suitable for losing a large number of data files, or the recovery of databases including system data files;
3, the recovery process is performed under mount, if the recovery is successful, then open the database, the DOWN machine time may be longer.
4.3.2 RMAN Backup Solution
RMAN backup archive mode corruption (lost) multiple data files, recovering the entire database
1. Connect the database, create a test table and insert record
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT);
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, backup database
DOS under C:> / Rman cmdfile = Bakup.rcv msglog = backup.log;
The following is Backup.log content.
Recovery Manager: Release 8.1.6.0.0 - Production
Rman> # script: bakup.rcv
2> # Creater: chenjiping
3> # Date: 5.8.2003
4> # Desc: Backup All Database DataFile in Archive with Rman
5>
6> #Connect Database
7> Connect RCVCAT RMAN / RMAN @ BACK;
8> Connect Target InterNal / Virpure;
9>
10> #start Backup Database
11> Run {
12> Allocate Channel C1 TYPE DISK;
13> Backup full tag 'dbfull' format 'D: / backup / full% u_% s_% p' Database
14> Include Current ControlFile;
15> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
16> Release Channel C1;
17>}
18> # end19>
RMAN-06008: Connected to Recovery Catalog Database
RMAN-06005: Connected to Target Database: Test (dbid = 1788174720)
RMAN-03022: Compiableing Command: Allocate
RMAN-03023: Executing Command: Allocate
RMAN-08030: Allocated Channel: C1
RMAN-08500: Channel C1: SID = 15 DevType = Disk
RMAN-03022: Compiableing Command: Backup
RMAN-03023: Executing Command: Backup
RMAN-08008: Channel C1: Starting Full DataFile Backupset
RMAN-08502: set_count = 4 set_stamp = 494074368 cree =
15-May-03
RMAN-08010: Channel C1: Specifying DataFile (s) in Backupset
RMAN-08522: INPUT DATAFILE FNO = 00002 Name = D: /oracle/oradata/test/rbs01.dbf
RMAN-08522: Input datafile fno = 00001 name = d: /oracle/oradata/test/system01.dbf
RMAN-08011: Including Current ControlFile in Backupset
RMAN-08522: Input datafile fno = 00005 name = d: /oracle/oradata/test/tools01.dbf
RMAN-08522: Input datafile fno = 00004 name = d: /oracle/oradata/test/temp01.dbf
RMAN-08522: Input datafile fno = 00006 name = d: /oracle/oradata/test/indx01.dbf
RMAN-08522: Input datafile fno = 00003 name = d: /oracle/oradata/test/user01.dbf
RMAN-08013: Channel C1: Piece 1 Created
RMAN-08503: Piece Handle = D: / backup / full04en5ug0_4_1 comment = None
RMAN-08525: Backup Set Complete, ELAPSED TIME:
00:01:16
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complete
RMAN-03022: Compiableing Command: SQL
RMAN-06162: SQL Statement: ALTER SYSTEM ARCHIVE LOG CURRENT
RMAN-03023: Executing Command: SQL
Rman-03022: Compiableing Command: Release
RMAN-03023: Executing Command: Release
RMAN-08031: Released Channel: C1
Recovery Manager Complete.
Here, it means that the backup is successful. 3. Continue to insert record in the test table
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
---------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
4, turn off the database, simulate the lost data file
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down
C: /> Del D: /oracle/oradata/test/system01.dbf
C: /> del D: /oracle/oradata/test/indx01.dbf
C: /> Del D: /oracle/oradata/test/tools01.dbf
C: /> DEL D: /Oracle/oradata/test/rbs01.dbf
5, start the database, check the error
SQL> Startup
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 1 - See Dbwr TRACE FILE
ORA-01110: Data file 1: 'd: /oracle/oradata/test/system01.dbf'
Query V $ Recover_File
SQL> SELECT * FROM V $ Recover_File;
File # Online Error Change # TIME
---------- ------------------------- --------
1 Online File Not Found 0
2 Online File Not Found 0
5 Online File Not Found 0
6 Online File Not Found 0
You can know that there are four data files that need to be recovered.
6, recovery with RMAN
C: /> rman
Recovery Manager: Release 8.1.6.0.0 - Production
Rman> Connect RCVCAT RMAN / RMAN @ BACK
RMAN-06008: Connected to Recovery Catalog Database
Rman> Connect Target InterNal / Virpure
RMAN-06005: Connected to Target Database: Test (dbid = 1788174720)
Rman> Run {
2> Allocate Channel C1 TYPE DISK;
3> Restore Database; 4> Recover data;
5> SQL 'ALTER DATABASE OPEN';
6> Release Channel C1;
7>}
RMAN-03022: Compiableing Command: Allocate
RMAN-03023: Executing Command: Allocate
RMAN-08030: Allocated Channel: C1
RMAN-08500: Channel C1: SID = 17 DevType = Disk
RMAN-03022: Compiableing Command: Restore
RMAN-03025: Performing Implicit Partial Resync of Recovery Catalog
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complete
RMAN-03022: Compiableing Command: IRESTORE
RMAN-03023: Executing Command: IRESTORE
RMAN-08016: Channel C1: Starting DataFile Backupset Restore
RMAN-08502: set_count = 4 set_stamp = 494074368 cree =
15-May-03
RMAN-08089: Channel C1: Specifying DataFile (s) to restore from backup set
RMAN-08523: Restoring DataFile 00001 to D: /oracle/oradata/test/system01.dbf
RMAN-08523: Restoring DataFile 00002 to D: /oracle/oradata/test/rbs01.dbf
RMAN-08523: Restoring DataFile 00003 to D: /oracle/oradata/test/user01.dbf
RMAN-08523: Restoring DataFile 00004 to D: /oracle/oradata/test/temp01.dbf
RMAN-08523: Restoring DataFile 00005 to D: /oracle/oradata/test/tools01.dbf
RMAN-08523: Restoring DataFile 00006 to D: /oracle/oradata/test/indx01.dbf
RMAN-08023: Channel C1: Restored Backup Piece 1
RMAN-08511: Piece Handle = D: / backup / full04en5ug0_4_1 tag = dbfull params = null
RMAN-08024: Channel C1: Restore Complete
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complete
RMAN-03022: Compiableing Command: Recover
RMAN-03022: Compiableing Command: Recover (1)
RMAN-03022: Compiling Command: Recover (2)
RMAN-03022: Compiling Command: Recover (3) RMAN-03023: Executing Command: Recover (3)
RMAN-08054: Starting Media Recovery
RMAN-03022: Compiling Command: Recover (4)
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 327 IS ALREADY ON Disk As File D: /oacle/oradata/test/archive/testt001s00327.arc
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 328 IS ALREADY ON Disk As File D: /oracle/oradata/test/archive/testt001s00328.arc
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 329 IS ALREADY ON Disk As File D: /oracle/oradata/test/archive/testt001s00329.arc
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 330 IS Already on Disk As File D: /oracle/oradata/test/archive/testt001s00330.arc
RMAN-03023: Executing Command: Recover (4)
RMAN-08515: ArchiveLog filename = d: /oracle/oradata/test/archive/testt001s00327.arc thread = 1 sequence = 327
RMAN-08515: ArchiveLog filename = d: /oracle/oradata/test/archive/testt001s00328.arc thread = 1 sequence = 328
RMAN-08055: Media Recovery Complete
RMAN-03022: Compiableing Command: SQL
RMAN-06162: SQL Statement: Alter Database Open
RMAN-03023: Executing Command: SQL
Rman-03022: Compiableing Command: Release
RMAN-03023: Executing Command: Release
RMAN-08031: Released Channel: C1
Rman>
7, check the data of the database (completely recovery)
SQL> SELECT *.
A
---------------------------------------
1
2
Description:
1. As long as there is backup and archiving, RMAN can also realize full recovery of the database (no loss data);
2. Recovery with the OS backup database, suitable for losing a large number of data files, or the recovery of databases including system data files;
3, the target database is performed in mount, if the recovery is successful, then open the database;
4, RMAN's backup and recovery commands are relatively simple and reliable. It is recommended to use RMAN to back up the database.
4.4 Incomplete recovery case
4.4.1 Time-based recovery under OS backup
Incomplete recovery can be divided into time-based recovery, based on change recovery and cancellation based recovery, the incomplete recovery process is described based on time recovery as an example.
Time-based recovery can not completely restore a certain time before the current time, for some misoperation, such as deleting a data table, can be reverted to the table's deletion time before the table is deleted before the table is derived before The official environment avoids one person-oriented error. 1. Connect the database, create a test table and insert a record:
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT);
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, back up the database, here is best to back up all data files, including temporary data files:
SQL> @ hotbak.sql or svrmgrl @ hotbak.sql under DOS
Or cold backup can also
3, delete the test table, assume the time before the deletion is T1, before deleting, easy to test, continue to insert data and apply it to return
files.
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
---------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
Statement processed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Statement processed.
SQL> SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24: MI: SS') from Dual;
TO_CHAR (sysdate, 'yy
-------------------
2003-05-21
14:43:01
SQL> Drop Table Test;
Table Dropped.
4, ready to return to time t1, retrieve the deleted table, first turn off the database:
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down.
5, copy all the data files just backed up
C: /> Copy D: / Database / *. DBF D: / Oracle / ORADATA / TEST /
6, start to MOUNT
SQL> Startup Mount;
Oracle Instance Started.
Total System Global Area 102020364 BYtes
Fixed Size 70924 bytes
Variable size 85487616 bytes
Database buffers 16384000 BYTES
Redo buffers 77824 bytes
Database mounted.
7, start the database to T1 time
SQL> Recover Database Until Time '2003-05-
21: 14: 43: 01
"
ORA-00279: Change 30944 Generated AT
05/21/2003
14:40:06
Needed for thread 1
ORA-00289: Suggest: d: /oracle/oradata/test/archive/testt001s00191.arcora-00280: Change 30944 for thread 1 is in sequence # 191
Specify log: {
Auto
Log Applied.
Media Recovery Complete.
8, open the database, check the data
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SELECT *.
A
---------------------------------------
1
2
Description:
1, not fully recovering all data, cold backup can also, because the recovery process is restored from the backup point, if the timestamp of one of the data files is greater than the time point to recover, then restore It is impossible to succeed;
2, there are three ways of incomplete recovery, the process is the same, only the recover command is different, here is an example of time-based recovery;
3, after the incomplete recovery, you must open the database in the way in RESETLOGS, it is recommended to make a full backup immediately, because ResetLogs are reused, it is difficult to recover before;
4, the above is before deleting time, but in practical applications, it is difficult to know the previous actual time before deleting, but can be used in general time, or can use the analysis log file (logmnr) to achieve accurate time;
5. Generally, this incomplete recovery is used on the backup machine after the test machine, and then the error-deleted table back and euro production system is exported.
4.4.2 Changed recovery under RMAN backup
With OS backups illustrate a time-based recovery, now use RMAN to indicate a change-based recovery
1. Connect the database, create a test table and insert record
SQL> Connect Internal / Password As Sysdba;
Connected.
SQL> CREATE TABLE TEST (A INT);
Table created
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> commit;
Commit completion
2, backup database
C: /> rman
Recovery Manager: Release 8.1.6.0.0 - Production
Rman> Connect RCVCAT RMAN / RMAN @ BACK
RMAN-06008: Connected to Recovery Catalog Database
Rman> Connect Target InterNal / Virpure
RMAN-06005: Connected to Target Database: Test (dbid = 874705288)
Rman> Run {
2> Allocate Channel C1 TYPE DISK;
3> Backup full tag 'dbfull' format 'D: / backup / full% u_% s_% p' Database
4> Include Current ControlFile;
5> SQL 'ALTER System Archive Log Current';
6> Release Channel C1;
7>}
// Screen output content length, omission - editing
RMAN> 3, delete test tables, before deleting, easy to test, continue to insert data and apply to archive, and get the SCN number before deleting.
SQL> INSERT INTO TEST VALUES (2);
1 row inserted
SQL> commit;
Commit completion
SQL> SELECT *.
A
---------------------------------------
1
2
SQL> ALTER SYSTEM SWITCH LOGFILE;
Statement processed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Statement processed.
SQL> SELECT MAX (KTuxescnw * Power (2, 32) KTUXESCNB) SCN from x $ ktuxe;
SCN
------------
31014
SQL> Drop Table Test;
Table Dropped.
4. Ready to return to SCN 31014, first turn off the database, then start to mount
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down.
SQL> Startup Mount;
5, start to return to change point SCN 31014
Rman> Run {
2> Allocate Channel C1 TYPE DISK;
3> Restore Database;
4> Recover Database Until SCN 31014;
5> SQL 'ALTER DATABASE OPEN RESETLOGS'
6> Release Channel C1;
7>}
RMAN-03022: Compiableing Command: Allocate
RMAN-03023: Executing Command: Allocate
RMAN-08030: Allocated Channel: C1
RMAN-08500: Channel C1: SID = 10 DevType = Disk
RMAN-03022: Compiableing Command: Restore
RMAN-03022: Compiableing Command: IRESTORE
RMAN-03023: Executing Command: IRESTORE
RMAN-08016: Channel C1: Starting DataFile Backupset Restore
RMAN-08502: set_count = 1 set_stamp = 494613682 creation_time =
21-May-03
RMAN-08089: Channel C1: Specifying DataFile (s) to restore from backup set
RMAN-08523: Restoring DataFile 00001 to D: /oracle/oradata/test/system01.dbf
RMAN-08523: Restoring DataFile 00002 to D: /oracle/oradata/test/rbs01.dbf
RMAN-08523: Restoring DataFile 00003 to D: /oracle/oradata/test/USERS01.DBF
RMAN-08523: Restoring DataFile 00004 to D: /oracle/oradata/test/temp01.dbfrman-08523: Restoring DataFile 00005 to D: /oracle/oradata/test/tools01.dbf
RMAN-08523: Restoring DataFile 00006 to D: /oracle/oradata/test/indx01.dbf
RMAN-08023: Channel C1: Restored Backup Piece 1
RMAN-08511: Piece Handle = D: / backup / full01enmd5i_1_1 tag = dbfull params = null
RMAN-08024: Channel C1: Restore Complete
RMAN-03023: Executing Command: Partial Resync
RMAN-08003: Starting Partial Resync of Recovery Catalog
RMAN-08005: Partial Resync Complete
RMAN-03022: Compiableing Command: Recover
RMAN-03022: Compiableing Command: Recover (1)
RMAN-03022: Compiling Command: Recover (2)
RMAN-03022: Compiling Command: Recover (3)
RMAN-03023: Executing Command: Recover (3)
RMAN-08054: Starting Media Recovery
RMAN-03022: Compiling Command: Recover (4)
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 191 IS Already on Disk As File D: / ORACL
E / ORADATA / TEST / Archive / Testt001S00191.ARC
RMAN-06050: ArchiveLog Thread 1 SEQUENCE 192 IS ALREADY ON Disk As File D: / ORACL
E / ORADATA / TEST / ARCHIVE / TESTT001S00192.ARC
RMAN-03023: Executing Command: Recover (4)
RMAN-08515: ArchiveLog filename = d: /oracle/oradata/test/archive/testt001s00191.ar
C thread = 1 sequence = 191
RMAN-08515: ArchiveLog FileName = D: /oracle/oradata/test/archive/testt001s00192.arc
Thread = 1 SEQUENCE = 192
RMAN-08055: Media Recovery Complete
RMAN-03022: Compiableing Command: SQL
RMAN-06162: SQL Statement: Alter Database Open ResetLogs
RMAN-03023: Executing Command: SQL
Rman-03022: Compiableing Command: Release
RMAN-03023: Executing Command: Release
RMAN-08031: Released Channel: C1
6, check data
Database altered.
SQL> SELECT *.
A
---------------------------------------
1
2
It can be seen that the table still exists.
Description:
1, RMAN can also achieve incomplete recovery, the method is easier and reliable than the method of backup of OS backup;
2, RMAN can be based on time, based on change and log sequences based on incomplete recovery based on log sequences, can specify which log sequence restored to, such as
Run {
Allocate CHANNEL CH1 TYPE DISK;
Allocate CHANNEL CH2 TYPE 'SBT_TAPE';
Set Until LogSeq 1234 Thread 1;
Restore ControlFile To '$ Oracle_Home / DBS / CF1.f';
Replicate ControlFile from '$ Oracle_Home / DBS / CF1.F';
ALTER DATABASE MOUNT;
Restore Database;
Recover data;
SQL "ALTABASE OPEN RESETLOGS";
}
3. Like all incomplete recovery, you must restore all backup data files in mount, need resetlogs;
4, change based recovery is more reliable than time-based recovery, but may also be more complicated, need to know which change number (SCN) needs to be restored, in normal production, there are many ways to obtain SCN, such as querying the Database Dictionary Table (V $ archived_log or v $ log_history), or analyzing archiving and online log (logmnr), etc.
Chapter 5 Other Recovery Cases
5.1 Damage to the recovery method of online log
5.1.1 Damage Non Current Online Log
Everyone is clear, online logs are classified into current online logs and non-current online logs, and the damage to the current online log is relatively simple, and the problem can be solved by the CLEAR command.
1. Start the database, encounter ORA-00312 or ORA-00313 errors, such as
ORA-00313: Open Failed for MEMBERS OF LOG GROUP 1 of Thread 1
ORA-00312: Online log 1 thread 1: 'D: /oacle/oradata/test/redo01.log'
From here we know the data file of the log group 1 is corrupted.
See more detailed information from the alarm file
2, View v $ log view
SQL> Select Group #, sequence #, archived, status from v $ log;
Group # sequence # Archived Status
---------- --------------------------------
1 1 Yes inactive
2 2 Yes inactive
3 3 No Current
It can be known that the group is non-current state and has been archived.
3, rebuild the log file with the clear command
SQL> ALTER DATABASE CLOGFILE GROUP 1;
If the log group has not been filed, you need to use it.
SQL> ALTER DATABASE CLEAR UnarchiveD logfile group 1;
4, open the database, re-back up the database
SQL> ALTABASE OPEN;
Description:
1. If damaged is a non-current online log file, only CLEAR can reconstruct the log file, but if the database is archived, the log is not archived, it is necessary to force CLEAR; 2. It is recommended that Clear, especially forced Clear is a full backup of a database;
3, this method is suitable for archiving and non-archive databases.
5.1.2 Damaged Current Online Log
There are two cases of damage to the current log in archiving mode.
First, the database is normal shutdown, there is no unresolved transaction in the log file requires instance recovery, and the current log group can be reconstructed directly with ALTER DATABASE CLEAR UNACHIVED LOGFILE GROUP N.
Second, it is an active affairs in the log group. The database needs to be recovered, and the log group needs to synchronize, there are two remedies:
A. The best way is to ensure the consistency of the database by incomplete recovery, but this approach requires that in the archive, and has available backups.
B. By mandatory recovery, it may cause the database inconsistent.
The following is used to illustrate these two recovery methods:
5.1.2.1 Recovery by backup
1. Open the database, you will encounter a similar error
ORA-00313: Open Failed for MEMBERS OF LOG GROUP 1 of Thread 1
ORA-00312: Online log 1 thread 1: 'D: /oacle/oradata/test/redo01.log'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file
2, check V $ log, found that it is current log
SQL> Select Group #, sequence #, archived, status from v $ log;
Group # sequence # Archived Status
----------------------------------------
1 1 No Current
2 2 Yes inactive
3 3 Yes inactive
3, found Clear unsuccessful
SQL> ALTER DATABASE CLEAR UnarchiveD logfile group 1;
Alter Database Clear UnarchiveD logfile group 1
*
Error At Line 1:
ORA-01624: LOG 1 Needed for Crash Recovery Of Thread 1
ORA-00312: Online log 1 thread 1: 'D: /oacle/oradata/test/redo01.log'
4, copy the full backup of a valid database, does not completely restore the database:
You can use the Until SCN to restore or use unsil cnacel to recover
Recover Database Until Cancel
First choose Auto, try to recover the archive log that you can use, and then re-
Recover Database Until Cancel
This time I enter Cancel, complete the incomplete recovery, that is, the recovery twice.
Such as:
SQL> Recover Database Until Cancel;
Auto
......
SQL> Recover Database Until Cancel;
Cancel;
5. Open the database with ALTER DATABASE Open RESETLOGS. Description:
1. The database recovered database is consistent incomplete recovery, which will lose transaction data in the current online log;
2, this method is suitable for archiving the database and has a fully backed up of available databases;
3. After the recovery is successful, remember to make a full backup of a database;
4. It is recommended that the online log file must implement the mirror phase on different disks, avoid this happening, because any data is lost is not allowed for production.
5.1.2.2 For mandatory recovery if there is no backup
1. Open the database, you will encounter a similar error
ORA-00313: Open Failed for MEMBERS OF LOG GROUP 1 of Thread 1
ORA-00312: Online log 1 thread 1: 'D: /oacle/oradata/test/redo01.log'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file
2, check V $ log, found that it is current log
SQL> Select Group #, sequence #, archived, status from v $ log;
Group # sequence # Archived Status
---------- --------------------------------
1 1 No Current
2 2 Yes inactive
3 3 Yes inactive
3, found Clear unsuccessful
SQL> ALTER DATABASE CLEAR UnarchiveD logfile group 1;
Alter Database Clear UnarchiveD logfile group 1
*
Error At Line 1:
ORA-01624: LOG 1 Needed for Crash Recovery Of Thread 1
ORA-00312: Online log 1 thread 1: 'D: /oacle/oradata/test/redo01.log'
4, drop the database down
SQL> Shutdown Immediate
5, add the following parameters in INIT
_ALLOW_RESETLOGS_CORRUPTION = True
6, restart the database, use the Until Cancel recovery
SQL> Recover Database Until Cancel;
Cancel
If an error, no longer pay attention, issue
SQL> ALTER DATABASE OPEN RESETLOGS;
7, after the database is opened, execute a Full Export immediately
8, shutdown database, remove _ALL_RESETLOGS_CORRUPT parameters
9, rebuild library
10, IMPORT and complete recovery
11. It is recommended to execute Analyze Table ... Validate structure cascade;
Description:
1. This recovery method is a recovery method after a way. In general, it is recommended not to use because the method may cause inconsistencies of the database;
2, the method also loses data, but the loss of data does not have more data from the previous method, mainly the submitted or not submitted data of the data file;
3. After the suggestion is successful, strictly implement the above 7 to 11 steps, complete the inspection and analysis of the database;
4, all the full backups of the database after all are completed;
5. It is recommended that the online log file must implement the mirror phase on different disks, avoid this happening, because any data is lost is not allowed for production. 5.2 Recovery method for damage control file
5.2.1 Damage to a single control file
Damaged a single control file is relatively easy to recover, because the general database system, the control file is not one, and all control files are mirrors, as long as copying a good control file replaces the change control file.
1. Control file is damaged, the most typical is to start the database error, not mount database
SQL> Startup
ORA-00205: Error In Identifying ControlFile, Check Alert log for more info
View the alarm log file, there is the following information
Alter Database Mount
Mon May 26
11:59:52
2003
ORA-00202: ControlFile: 'D: /oracle/oradata/chen/control01.ctl'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
2, stop the database:
SQL> Shutdown Immediate
3, copy a good control file replace the change control file or modify the control file parameters in the init.ora, cancel this bad control file.
4, restart the data:
SQL> Startup
Description:
1. The loss of a single control file is relatively simple, because all control files in the database are mirrors, only simple
Copy a good one;
2. It is recommended that the mirror control file is on different disks;
3, it is recommended to make a backup of the control file, long-term retaining a text backup of the control file generated by the ALTER DATABASE BACKUP Control File to TRACE.
5.2.2 Damaged All Control Files
Damage to multiple control files, or artificially deleted all control files, can no longer solve the problem by controlling the replication of the file, this time you need to re-establish the control file.
At the same time, Alter Database Backup Control File to TRACE can generate a text backup of a control file.
The following is the step of recovering the control file in detail:
1, turn off the database
SQL> Shutdown Immediate;
2. Delete all control files, the loss of the simulation control file
3, start the database, there is an error, can not start under mount
SQL> Startup
ORA-00205: Error In Identifying ControlFile, Check Alert log for more info
View the alarm log file, there is the following information
Alter Database Mount
Mon May 26
11:53:15
2003
ORA-00202: ControlFile: 'D: /oracle/oradata/chen/control01.ctl'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open file
O / S-Error: (OS 2) The system cannot find the specified file.
4, turn off the database
SQL> Shutdown Immediate;
5. Run the script of the Create Control File under Internal or Sys, pay attention to the path of the online log or data file, or modify the script generated when the ALTER DATABASE BACKUP Control File to TRACE is backup control file, go down the extra comment can. Startup Nomount
Create ControlFile Reuse Database "Test" NoSetlogs NoarchiveLog
MaxLogfiles 32
MaxLogmembers 2
MaxDatafiles 254
MaxInstances 1
MaxLoghistory 226
Logfile
Group 1 'D: /oracle/oradata/test/redo01.log' size 1m,
Group 2 'd: /oracle/oradata/test/redo02.log' size 1m,
Group 3 'D: /oracle/oradata/test/redo03.log' size 1m
DataFile
'D: /oracle/oradata/test/system01.dbf',
'D: /oracle/oradata/test/rbs01.dbf',
'D: /oracle/oradata/test/Users01.dbf',
'D: /oracle/oradata/test/temp01.dbf',
'D: /oracle/oradata/test/tools01.dbf',
'D: /oracle/oradata/test/indx01.dbf'
Character set zhs16gbk;
- Recovery Is Required if any of the datafiles area restackups,
- or if the last shutdown was not normal or immediate.
Recover Database
- IF The last shutdown was not normal or immediate
--Noarchive
- Recover Database Until Cancelusing Backup ControlFile
--archive
- Recover Database Using Backup ControlFile Until Cancel
- Database can now be open name.
ALTER DATABASE OPEN
--IF Recover Database Until Cancel
- RALTER DATABASE OPEN RESETLOGS;
6. If there is no error, the database will start to the OPEN state.
Description:
1. Reconstruction Control File is used to restore the damage of all data files, and you need to pay attention to the correctness of its writing, and ensure that all data files and online logs are included;
2, often there is a case because a disk is corrupted, we cannot recover (store) data files to this disk, so when you go to another disk, we must recreate the control file to identify this new This method can also be used here to recover.
5.3 Recovery method of damage to the rollback data file
The loss or damage of a data file in the split segment table space causes the database to not recognize it, and an error of ORA-1157, ORA-1110 occurs when the database is started, or an error of the system level, such as ORA-7360. ORA-1116, ORA-1110 errors, or errors of the operating system level, such as ORA-7368. Thanks to the hard work of Coolyl, most of the contents of the rollback segment is taken from his article in ITPUB.
5.3.1 Damaged data files, but the database is in the OPEN state
If you find that the data file with a rollback segment is lost or damaged, the database at this time is in the open state and runs, don't turn off the database, because when most cases are opened, it is closed. It is good to solve the problem.
There are two cases:
A. It is OFFLINE Loss or damaged data file, then recover from a backup, performing media recovery to keep consistency. But this situation requires the database to be archived.
B. It is the entire rollback segment table space where Offline is missing or damaged, and then deletes the entire rollback segment table space and rebuilt, but you have to kill the user's process that has been activated in the rollback segment. Of of OFFLINE.
Usually the first case is simple to implement, but more user transactions will be wrong and roll back.
Concrete step of A:
1, offline loss or damaged data file
ALTER DATABASE DATAFILE '
2, recover from a valid backup.
3, do the following query:
Select v1.group #, member, sequence #
From v $ log v1, v $ logfile v2
WHERE V1.Group # = v2.group #;
This will list all of your Redolog files and the Sequence Numbers they represent.
4. Restore data files.
Recover datafile '
5, I am sure that you have applied all the Redolog files until the prompt information "Media Recovery Complete" appears.
6, the online data file.
Alter Database DataFile '
B Details:
1. Offline exists all returns in the split segment of the returning or damaged data file.
Alter rollback segment
2. Detect the status of the rollback segment.
Select segment_name, Status from dba_rollback_segs
WHERE TABLESPACE_NAME = '
3, delete all OFFLINE back rolls
Drop rollback segment
4, handle the rollback segments of those online status.
Re-execute the second step
If you have already performed the return segment status of OFFLINE operation is still Online, you will show that there is an active transaction in this returning segment. You have to query
Select segment_name, XACTS Active_TX, V.status
From V $ ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '
If there is no return result, it is demonstrated that all rollback segments in the split segment table space in the presence or damaged data file have been OFFLINE, then re-execute the second step, the third step. If the query is returned, the status should be "pending offline". Then look at the active_tx column, if the value is 0, it indicates that there is no unprocessed transaction in this returned segment, soon will be OFFLINE, then wait After it is OFFLINE to re-execute 2, 3 steps, step up to sixth step. Continue to the fifth step if the value is greater than 0.
5. Forced the rollback section of the event.
Active transaction should be submitted or rolled, perform the following query to see which users take up the rollback segment:
Select S.SID, S.Serial #, S.USERNAME, R.NAME "ROLLBACK"
From V $ Session S, V $ TRANSACTION T, V $ ROLLNAME R
WHERE R.NAME IN ('
'
And S. Taddr = T.Addr and T.Xidusn = R.USN;
It is best to contact those User let them go back rolling or submit a transaction. If you can't do it, you can only enforce the process.
Alter System Kill Session '
After killing the process, I will automatically clear those transactions for a while, and then go back to the second step to continue to query.
6, delete the rollback segment.
DROP TABLESPACE
7, rebuild the roll band and online them.
Description:
1. If the database is in the Open state, you can solve the problem directly in the Open state, there is no need to stop the database, add DOWN machine time;
2. Whether it is the recovery method above, it is not a normal recovery, and it does not cause inconsistencies or errors.
5.3.2 Database is closed, but there is no active transaction in the data file
In this case, the easiest way is OFFLINE DROP to drop this bad or lost data file, then open the database in the restriced mode and then delete and rebuild the rollback segment table space containing the corrupted file.
Specific steps are as follows:
1. Determine the database is normal. The method is to view the Alert file, and finally see if there is the following information:
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
If there is, it will prove that the database is normal, otherwise this method cannot be recovered.
2, modify the init parameter file, remove the rollback segment of the rollback segment table space contained in Rollback_SEGMENTS, if you can't determine which return segments are bad, simple ways to comment out the entire rollback_segments.
3, go to the Mount database with the restricted mode.
Startup Restrict Mount
4, OFFLINE DROP falls the bad data file
Alter Database DataFile '
5, open the database
ALTER DATABASE OPEN
If you see the following information "Statement Processed", jump to step 7, if you see the error message of ORA-604, ORA-376, and ORA-1110, continue step 6. 6, normal closing the database, then comment out of Rollback_SEGMENTS in the init file, and join the implied parameters
_Corrupted_rollback_segments = (
Then open the database in a restrical mode
Startup restrict
7, delete the rollback segment table space that contains damage to the file.
DROP TABLESPACE
8. Rebuild a segment table space, remember to put the rollback segment after the creation is ONLINE.
9, re-enable databases available to all users.
ALTER System Disable Restricted Session;
10, then close the database, modify the init file, if you start just comment, you can remove the comment, if you add an implied parameter, comment it off, and add all the rollback segments in Rollback_Segments.
11, start the database normally:
Startup
Description:
1. Prerequisite for this method is that the database is normal to close (not abort);
2. This method is a normal method and does not cause data errors.
5.3.3 Database is closed, there are active transactions in the data file, and there is no backup available.
Generally, this reason is caused by Shutdown Abort or other reasons for anomalous shutdown (such as power outage).
1. Open a transaction
SQL> SET Transaction Use Rollback Segment RBS0;
Transaction set.
SQL> INSERT INTO TEST (A) VALUES (1);
1 row created.
2, abnormal shutdown
SQL> Shutdown Abort;
Oracle Instance Shut Down.
3. Delete a data file for RBS
C:>
Del
D: / Oracle / ORADATA / CHEN / RBS01.
4. Modify INIT
Rollback_segments = (system)
Add_corrupted_rollback_segments = (RBS0, RBS1, RBS2 ...)
5, SQL> Startup Mount
6, SQL> ALTER DATABASE DATAFILE 'D: /ORACLE/Ordata/t8i/rbs01.dbf' offline Drop;
The database has been changed.
7, SQL> Recover Database;
Complete media recovery.
8, SQL> ALTER DATABASE OPEN;
The database has been changed.
9, SQL> SELECT * FROM V $ ROLLNAME;
USN Name
---- -------
0 system
10, SQL> Select segment_name, tablespace_name, status from dba_rollback_segs;
Segment_name tablespace_name status
------------------------------------------------- ---
System System Onliners0 RBS Needs Recovery
RBS1 RBS NEEDS Recovery
RBS2 RBS NEEDS Recovery
11, SQL> Drop Rollback Segment RBS0;
The rejection period has been discarded.
SQL> Drop Rollback Segment RBS1;
The rejection period has been discarded.
SQL> Drop Rollback Segment RBS2;
The rejection period has been discarded.
12, SQL> Select segment_name, tablespace_name, Status from dba_rollback_segs;
Segment_name tablespace_name status
-------------------------------------
System System Online, SYSTEM SYSTEM ONLINE
13, SQL> Drop TableSpace Rbs Including CONTENTS;
The table space has been discarded.
14. Rebuild a new back rollometer space and rollback segment, and online.
15, SQL> Shutdown Abort
16, modify the init
Rollback_SEGMENTS = (RBS0, RBS1, RBS2)
Remove _corrupted_rollback_segments = (RBS0, RBS1, RBS2).
17, SQL> STARTUP
Description:
1. This method is to use the method that is not available. If there is a backup, it is recommended to recover from the backup;
2, this method restored the database that may cause data errors in the database;
3. After the recovery is successful, the EXP / IMP data is recommended and the database is checked.
5.3.4 Database is closed, there is an active transaction in the data file, recovered from backup
1. Restore damaged data files from a valid backup.
2, mount database.
3, do the following query:
SELECT FILE #, Name, Status from V $ datafile;
If you find that the file to be recovered is OFFLINE status, you must first Online it:
Alter Database DataFile '
4, do the following query
Select v1.group #, member, sequence #, first_change #
From v $ log v1, v $ logfile v2
WHERE V1.Group # = v2.group #;
This will list Sequence and First Change Numbers represented by the RedLog file.
5. If the database is non-archiving, do the following query:
SELECT FILE #, Change # from v $ recover_file;
If the Change # is greater than the minimum Redolog file, the data file can be recovered, remember to apply all the Redolog files all over again when the log is applied.
If the Change # is less than the first_change # of the smallest Redolog file, the data file cannot be recovered. At this time, you have to recover the database from a valid full backup. If there is no full backup, then you can only put it. The database is enforced to an inconsistent state to go to the EXP output, and then re-use the library import data, because the recovery of this way Oracle does not recommend the user yourself, so I will not explain it in detail. 6. Restore data files:
Recover datafile '
7, I am sure that you have applied all the Redolog files until the prompt information "Media Recovery Complete" appears.
8, open the database.
Description:
1. This method requires that it is made in the manner of archiving, and it is a recommended way;
2, this method does not cause the database's error.
5.4 Recovery method for damage to temporary data files
The recovery of the temporary data file is relatively simple, because other useful data is not involved in the temporary file, so after deleting.
1. Close the database:
SQL> Shutdown Immediate
2. Delete the temporary data file, the simulation media failed;
3, start the database, detect file errors;
4, offline this data file:
SQL> ALTER DATABASE DATAFILE 'file name full name' OFFLINE DROP;
5, open the database
SQL> ALTER DATABASE OPEN
6, delete this temporary table space
SQL> DROP TABLESPACE TEMP (or other temporary table space name);
7, recreate the table space and reassign it to the user.
Description:
1. The temporary data file is an unbaustive file, and the permanent data is not saved. You can delete the rebuild at any time, and do not affect the data security of the database;
2. If it is re-established, don't forget to reassign it to the user.
Chapter VI. Common Recovery Misunderstandings
1. You can do not need to back up, only the archive can perform the forward recovery of the database.
A: This is not possible before Oracle 9i. I have never heard of other databases. The main idea of incomplete recovery is to use the backup before the incomplete point, plus the archive log, and restore the incomplete recovery point. There is a flashback characteristic in 9i, and the use of this feature is also a lot of limitations.
2, do not fully recover only one backup data file that needs to be recovered
A: Not fully resumes need to copy all data files, preferably include temporary data files, otherwise additional processing, if there is an SCN of a data file to be greater than the incomplete recovery point, then this recovery will be failed.
3, using the RMAN directory and the target database can be very good in the same database.
A: Use the recovery directory and the target database in the same database, there will be a large restoration limit, such as the damage of the system data file of the database, the database cannot be Open at all, then RMAN will not connect to the recovery directory, there is no existence back to normal.
Chapter 7. Summary
Here we repeatedly demonstrate the recovery plan in many cases. Through these demonstrations, we should master the following:
1. Use OS and RMAN to make a variety of conventional backups and recovery.
2. Familiar with unconventional backups and recovery methods without backup or simple.
