| |
Oracle database online redo log contains a record of all operations of the data in the database, we can use the redo logs do many operations, such as log mining.
Sometimes, for whatever reason, our online log is accidentally deleted or accidental damage people out, how do we recover it, is actually very simple, look at the following elements:
We remove the online logs log simulation accidentally deleted situation:
[Oracle @ test orcl] $ rm redo *
[Oracle @ test orcl] $ ls -l redo *
ls: can not access the redo *: No such file or directory
[Oracle @ test orcl] $ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
. . .
Database loading is completed.
Because we are just missing online redo logs, the database can start to mount state, the state will only open the database mount the control file, and will not go to check the status of each data file, verification operation will be carried out in the open stage .
SQL> alter database open;
alter database open
*
Line 1 Error:
ORA-03113: end of the communication channel file
Process ID: 4607
Session ID: 125 SEQ ID NO: 5
Open the database, it will be given, and the database will be forced to close
Here we use resetlogs Attempts to open the database:
SQL> recover database until cancel;
Complete media recovery.
SQL> alter database open;
alter database open
*
Line 1 Error:
ORA-01589: To open a database, you must use RESETLOGS option or NORESETLOGS
SQL> alter database open resetlogs;
Database has changed.
resetlogs open database must only be used after the database is not fully recovered, and after incomplete recovery must use RESETLOGS option or NORESETLOGS
In addition to this method, we can open the database by clearing the logfile method, as follows:
First, start to mount the database state
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 ARCHIVED field is YES, then we can
alter database clear logfile command to clear, if it is No, then we can
alter database clear unarchived logfile forcibly cleared
SQL> alter database clear logfile group 2;
Database has changed.
SQL> alter database clear logfile group 3;
Database has changed.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
Line 1 Error:
ORA-01624: log 1 is an emergency restore instance orcl (thread 1) required
ORA-00312: online log 1 thread 1: '/app/oradata/orcl/redo01.log'
However, because group 1 is the current online log, shutdown plus I was using before the abort to shut down the database
Data files in an inconsistent state, you need to use the current log for instance recovery, it can not be cleared by clearing the log command
If the database file is in a consistent state, so that here we can open command to open the database by alter database, but if you encounter such a situation inconsistent, but also open the database through resetlogs, as follows:
SQL> recover database until cancel;
ORA-00279: Change 984 722 (generated at 09/16/2015 16:04:43) is required for the thread 1
ORA-00289: Recommendation: /app/archivelog/orcl_1_1_890582670.dbf
ORA-00280: Change 984 722 (for thread 1) in the sequence # 1
Specify the log: { = suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: can not open archived log '/app/archivelog/orcl_1_1_890582670.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: can not open archived log '/app/archivelog/orcl_1_1_890582670.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/app/oradata/orcl/system01.dbf'
SQL> recover database until cancel
ORA-00279: Change 984 722 (generated at 09/16/2015 16:04:43) is required for the thread 1
ORA-00289: Recommendation: /app/archivelog/orcl_1_1_890582670.dbf
ORA-00280: Change 984 722 (for thread 1) in the sequence # 1
Specify the log: { = suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/app/oradata/orcl/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
Line 1 Error:
ORA-01194: file 1 needs more recovery to maintain consistency
ORA-01110: data file 1: '/app/oradata/orcl/system01.dbf'
However, we found incomplete recovery failed, this time by resetlogs open the database is not possible, then we can only through the application of hidden parameters, by making the implicit parameter to open a database in an inconsistent state, as follows:
SQL> create pfile = '/ home / oracle / p2.ora' from spfile;
In pfile which increased * ._ allow_resetlogs_corruption = TRUE
echo "* ._ allow_resetlogs_corruption = TRUE" >> p2.ora
Then open the database to mount state through our new pfile:
SQL> startup mount pfile = '/ home / oracle / p2.ora'
ORACLE instance started.
Total System Global Area 334036992 bytes
Fixed Size 2253024 bytes
Variable Size 171970336 bytes
Database Buffers 155189248 bytes
Redo Buffers 4624384 bytes
Database loading is completed.
Then open the database by resetlogs method
SQL> alter database open resetlogs;
Database has changed.
Because we use our temporary generated pfile to start, it is also the final step, you can restart the database
Well, the database is open, but because our database recover from exceptional cases, there may be a problem, it is proposed to make a backup to prevent data loss. |
|
|
|