--Restore the entire database
Run {
Shutdown immediate;
startup Mount;
Restore database;
Recover database;
ALTER DATABASE open;
}
--Restore Table space users
Run {
SQL ' alter tablespace users offline ';
Restore tablespace users;
Recover tablespace users;
SQL ' alter tablespace data online ';
}
--Recover data files
Applies to all tablespace data files
run{
Shutdown immediate;
startup Mount;
Restore datafile '/u01/app/oracle/oradata/data01.dbf ';
Recover datafile '/u01/app/oracle/oradata/data01.dbf ';
ALTER DATABASE open;
}
Non-system Tablespace data files can also be directly in the open state of restore and recover
Run {
SQL ' ALTER DATABASE datafile 6 offline ';
Restore DataFile 6;
Recover DataFile 6;
SQL ' ALTER DATABASE datafile 6 online ';
}
--Temporary table space corruption
Re-establish a temporary table space
sql> Create temporary tablespace temp1
Tempfile '/u01/app/oracle/oradata/test10g/temp101.dbf ' size 200M;
Set the built-in TEMP1 table space to the default temporary tablespace for the database
sql> ALTER DATABASE default temporary tablespace Temp1;
Drop the table space of the old temp
sql> Drop Tablespace temp including contents and datafiles;
--All control file corruption
Run {
Shutdown immediate;
Startup Nomount;
Set dbid=1187100180;
Restore Controlfile from '/u01/backup/20131202_test11g_64.bak ';
ALTER DATABASE Mount;
Recover database;
ALTER DATABASE open resetlogs;
}
Make a full backup of the database once you open it
--spfile damage
Run {
Shutdown immediate;
Startup Nomount;
Set dbid=1176041295;
Restore SPFile from '/u01/backup/20131202_test11g_64.bak ';
Shutdown immediate;
Startup
}
--Non-current log member corruption
Sql> Startup no Error
Sql> select Status,member from V$logfile;
sql> ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/test11g/redolog3a.log ';
sql> ALTER DATABASE Add logfile member '/u01/app/oracle/oradata/test11g/redolog3a.log ' to group 1;
--Current Log member corruption
Sql> Startup no Error
[EMAIL protected] test11g> ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log ';
ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log '
*
ERROR at line 1:
Ora-01609:log 1 is the current log for thread 1-cannot drop members
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01.log '
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log '
[EMAIL protected] test11g> alter system switch logfile;
System altered.
[EMAIL protected] test11g> ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log ';
Database altered.
[EMAIL protected] test11g> ALTER DATABASE add logfile member '/u01/app/oracle/oradata/test11g/redo01a.log ' to group 1;
Database altered.
--Spare
Sql> ALTER DATABASE clear logfile Group 1;
ALTER DATABASE clear logfile Group 1
*
ERROR at line 1:
Ora-00350:log 1 of Instance ORCL (thread 1) needs to be archived
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log '
The reason for the failure is that group 1 has not been archived and needs to be added "unarchived" parameters;
Sql> ALTER DATABASE Clear unarchived logfile Group 1;
Database altered.
sql> ALTER DATABASE open;
Database altered.
--Non-current log group corruption
Sql> Startup
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/TEST11G/TEST11G/TRACE/TEST11G_LGWR_16823.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/test11g/redolog3a.log '
Ora-27037:unable to obtain file status
Linux error:2: No such file or directory
Additional Information:3
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/test11g/redolog3.log '
Ora-27037:unable to obtain file status
Linux error:2: No such file or directory
sql> startup Mount;
Sql> ALTER DATABASE clear logfile Group 3;
sql> ALTER DATABASE open;
--Current Log group corruption
[Email protected] test11g> Startup
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/TEST11G/TEST11G/TRACE/TEST11G_ORA_20114.TRC:
Ora-00313:open failed for members of log Group 1 of thread
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01.log '
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log '
[Email protected] test11g> startup mount
[EMAIL protected] test11g> ALTER DATABASE drop logfile Group 1;
ALTER DATABASE drop logfile Group 1
*
ERROR at line 1:
Ora-01623:log 1 is the current log for instance test11g (thread 1)-Cannot drop
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01.log '
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log '
[Email protected] test11g> recover database until cancel;
Media recovery complete.
[EMAIL protected] test11g> ALTER DATABASE open resetlogs;
Database altered.
Do an Rman full backup
--Recover archived logs
Rman> Run
2> {
3> SET ARCHIVELOG DESTINATION to '/u02/archive ';
4> restore Archivelog sequence between and 67;
5>}
--incomplete recovery (until scn/time/sequence)
Under Mount
Set until time ' MAR 21 2005 06:00:00 '
Set until SCN 1000
Set until sequence 9923
rman> startup Mount;
Rman> Run
2> {
3> set until Time "to_date (' 20131129 11:29:05 ', ' yyyymmdd hh24:mi:ss ')";
4> Restore Database;
5> Recover database;
6>}
rman> ALTER DATABASE open resetlogs;
Do an Rman full backup
Or
Rman> Run {
2> restore database from tag=tag20131209t153042;
3> recover database from tag=tag20131209t153042;
4>}
Do an Rman full backup
If there is Rman data file backup and control file backup, but lost archive log, recover will be error
[EMAIL protected] test11g> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-01194:file 2 needs more recovery to be consistent
Ora-01110:data file 2: '/U01/APP/ORACLE/ORADATA/TEST11G/SYSAUX01.DBF '
rman> startup Nomout;
rman> restore Controlfile from '/u01/backup/20131209_test11g_93.bak ';
Rman> ALTER DATABASE Mount;
rman> restore database from tag=tag20131209t153042;
1, if there is a partial archive
At this point we can only use the logs from the Lost archive log for recovery.
rman> recover database until sequence=2 thread=1;
rman> ALTER DATABASE open resetlogs;
Or
[Email protected] test11g> recover database until cancel using Backup controlfile;
First auto, then Cancel.
2. If not archived
Mount State Execution:
[EMAIL protected] test11g> alter system set "_allow_resetlogs_corruption" =true scope=spfile;
System altered.
[Email protected] test11g> startup force;
ORACLE instance started.
Total System Global area 301322240 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database buffers 41943040 bytes
Redo buffers 6381568 bytes
Database mounted.
Ora-01589:must use Resetlogs or noresetlogs option for database open
[EMAIL protected] test11g> ALTER DATABASE open resetlogs;
Database altered.
[EMAIL protected] test11g> alter system reset "_allow_resetlogs_corruption" scope=spfile;
System altered.
[Email protected] test11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[Email protected] test11g> Startup
ORACLE instance started.
Total System Global area 301322240 bytes
Fixed Size 1336092 bytes
Variable Size 251661540 bytes
Database buffers 41943040 bytes
Redo buffers 6381568 bytes
Database mounted.
Database opened.
Oracle Rman Various recovery