Several recovery methods for Oracle database online redo logs being deleted

The online redo log of the Oracle database contains an operational record of all the data in the database, and we can do a lot of things with redo logs, such as log mining.

Sometimes, for a variety of reasons, our online log was mistakenly deleted or accidentally damaged, how should we restore it, in fact, very simple, see the following:

We delete the log by deleting the online log in case of accidental deletion:

[[Email protected] orcl]$ RM redo*[[email protected] orcl]$ ls-l Redo*ls: Unable to access redo*: No file or directory [[email protected] orcl]$ s Qlplus/as sysdbasql> Startup Mountoracle Routine has been started .... The database is loaded.

Because we are only missing the online redo log, the database can be booted into Mount state, the Mount state of the database will only open the control file, and will not verify the status of each data file, the validation action will be in the open phase.

sql> ALTER DATABASE open;alter database open* 1th line error: ORA-03113: File end of communication channel process id:4607 session id:125 serial number: 5

Open the database, the error will be, and the database will be forcibly closed

Here we try to open the database using the Resetlogs method:

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 resetlogs; the database has changed.

Resetlogs opening a database must be available after the database is not fully recovered, and you must use the resetlogs or noresetlogs option after incomplete recovery

In addition to this method, we can also open the database by clearing the LogFile method, as follows:

Start the database to Mount State first

Query V$log View:

sql> select * from v$log;    group#    thread#   SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS  arc status       first_change# first_time  next_change#  next_time---------- ---------- ---------- ---------- ---------- ----------  --- ---------------- ------------- ------------------- ------------ ----------- -------- 1    1       1  134217728    5121 NO  CURRENT      984719 2015-09-16  16:04:30   2.8147e+14 3    1        0  134217728   5121 yes unused   0            0 2    1       0  134217728    5121 YES UNUSED   0           0

If the archived field is yes, we can

ALTER DATABASE Clear logfile command is cleared and, if no, we can forcibly clear sql> ALTER DATABASE CLE by ALTER DATABASE clear unarchived logfile AR logfile Group 2; the database has changed. Sql> ALTER DATABASE clear logfile Group 3; Sql> ALTER DATABASE Clear unarchived logfile Group 1;alter database clear unarchived logfile Group 1* 1th error occurred: ORA-0162 4: Log 1 is required for emergency Recovery instance ORCL (thread 1) ORA-00312: Online log 1 thread 1: '/app/oradata/orcl/redo01.log '

But since group 1 is the current online log, plus I was using the shutdown abort to shut down the database

Data file status inconsistent, need to use the current log for instance recovery, so the Purge log command cannot be cleared

If the database file is in the same state, then we can open the database by the ALTER databases opening command, but if we encounter such inconsistency, we also need to open the database through Resetlogs, as follows:

sql> recover database until cancel;ora-00279:  Change  984722  (in  09/16/ 2015 16:04:43  generation)   for thread  1  is required ora-00289:  recommendations:  /app/archivelog/orcl_1_1_ 890582670.dbfora-00280:  change  984722  (for thread  1)   Specify logs in sequence   #1  :  {<ret>= suggested | filename | auto | cancel}autoora-00308: cannot open  archived log  '/app/archivelog/orcl_1_1_890582670.dbf ' Ora-27037: unable to obtain  file statusLinux-x86_64 Error: 2: No such file or  directoryadditional information: 3ora-00308: cannot open archived log  '/app /ARCHIVELOG/ORCL_1_1_890582670.DBF ' ora-27037: unable to obtain file statuslinux-x86_ 64 error: 2: no such file or directoryadditional information:  3ora-01547: warning: recover&nbsP;succeeded but open resetlogs would get error belowora-01194: file  1 needs more recovery to be consistentORA-01110: data file  1:  '/app/oradata/orcl/system01.dbf ' sql> recover database until cancel   ora-00279:  Change  984722  (in  09/16/2015 16:04:43  build)   for Threads  1  is required ora-00289:  recommended: /app/archivelog/orcl_1_1_890582670.dbfora-00280:  change  984722  (for threading  1)   Specify the log in sequence   #1  :  {<ret>=suggested | filename | auto |  cancel}cancelora-01547: warning: recover succeeded but open resetlogs  would get error belowORA-01194: file 1 needs more recovery  to be consistentora-01110: data file 1:  '/app/oradata/orcl/system01.dbf ' ora-01112:  Media Recovery is not started Sql> alter database open resetlogs;alter database open resetlogs*  1  line error: O ra-01194:  file  1  need more recovery to maintain consistency ora-01110:  data files  1:  '/app/oradata/orcl/system01.dbf '

However, we find that incomplete recovery is a failure, and it is not possible to open the database through resetlogs at this time, so we can only open the database by applying the implied parameters to make the state inconsistent by the implied parameters as follows:

sql> create pfile= '/home/oracle/p2.ora ' from spfile; add pfile inside *._allow_resetlogs_corruption=trueecho "*._allow_ Resetlogs_corruption=true ">>p2.ora then opens the database through our new pfile to mount State:sql> startup Mount Pfile= '/home/oracle/ P2.ora ' ORACLE routine has been started. Total System Global area 334036992 bytesfixed size 2253024 bytesvariable size 171970336 bytesdatabase buffers 155189 248 Bytesredo buffers 4624384 bytes database loaded. Then open the database by Resetlogs method sql> ALTER DATABASE open resetlogs;

Since we started with our temporary pfile, we have to complete the final step and restart the database

Well, the database is open, but because our database is recovering from an exception, it may be problematic, so it is recommended that you do a backup to prevent data loss.

This article is from the "Database Road" blog, make sure to keep this source http://dbaway.blog.51cto.com/7099215/1695387

Several recovery methods for Oracle database online redo logs being deleted