Restoration Methods for deleting online redo logs of Oracle databases

Restoration Methods for deleting online redo logs of Oracle databases
The online redo log of the Oracle database contains Operation Records of all data in the database. We can use the redo log to perform many operations, such as log mining. Sometimes, for various reasons, our online logs are deleted by mistake or accidentally damaged. How can we restore them? In fact, it is very simple. See the following content: when an online log is deleted by mistake, we can simulate that the log is deleted by mistake: [oracle @ test orcl] $ rm redo * [oracle @ test orcl] $ ls-l redo * ls: unable to access redo *: the file or directory [oracle @ test orcl] $ sqlplus/as sysdbaSQL> startup mount ORACLE routine has been started .... The database has been loaded. Because we only lack online redo logs, the database can be started to the mount state. The mount State database only opens the control file and does not verify the status of each data file, the verification is performed in the open stage. SQL> alter database open; alter database open * 1st Line Error: ORA-03113: communication channel file end process ID: 4607 session ID: 125 serial number: 5 to open the database, it will report an error, in addition, the database will force the following method to open the database using resetlogs: SQL> recover database until cancel; to restore the media. SQL> alter database open; alter database open * 1st Line Error: ORA-01589: to open the database, you must use RESETLOGS or NORESETLOGS option SQL> alter database open resetlogs; the database has changed. The resetlogs option can be used only after the database is not completely recovered. Besides this method, the RESETLOGS or NORESETLOGS option must be used after the database is not completely recovered, you can also open the database by clearing the logfile, as follows: first, start the database to the mount status 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 16:04:30 2.8147E + 14 3 1 0 134217728 5121 yes unused 0 0 0 2 1 0 134217728 5121 yes unused 0 0 if the ARCHIVED column is YES, you can run the alter database clear logfile command to clear the database. If it is No, you can use alter database clear unarchived logfile to forcibly clear SQL> alter database clear logfile group 2; the database has been changed. SQL> alter database clear logfile group 3; the database has been changed. SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * 1st rows error: ORA-01624: Log 1 is an emergency recovery instance orcl (thread 1) required ORA-00312: Online log 1 thread 1: '/app/oradata/orcl/redo01.log' but since group 1 is the current online log, in addition, the statuses of database data files that I previously used shutdown abort To close are inconsistent and the current log needs to be used for instance recovery, therefore, the log clearing command cannot be used to clear the database files. If the database file status is consistent, we can use the alter database open command to open the database. However, if such inconsistency occurs, you also need to use resetlogs to open the database as follows:

SQL> recover database until cancel; ORA-00279: Change 984722 (generated at 16:04:43) ORA-00289 required for thread 1: Recommended:/app/archivelog/orcl_1_1_890582670.dbfORA-00280: change 984722 (for thread 1) Specify the log in sequence #1: {<RET> = suggested | filename | AUTO | CANCEL} AUTOORA-00308: cannot open archived log '/app/archivelog/orcl_00000000890582670.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_00000000890582670.dbf' ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but open resetlogs wowould 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 (generated at 16:04:43) ORA-00289 required for thread 1: recommended: /app/archivelog/orcl_1_1_890582670.dbfORA-00280: Change 984722 (for thread 1) Specify the log in sequence #1: {<RET> = suggested | filename | AUTO | CANCEL} CANCELORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/app/oradata/orcl/system01.dbf' ORA-01112: recovery SQL> alter database open resetlogs; alter database open resetlogs * 1st Line Error: ORA-01194: File 1 requires more recovery to maintain consistency ORA-01110: Data File 1: '/app/oradata/orcl/system01.dbf'

 

However, we found that incomplete Recovery fails. In this case, it is impossible to open a database through resetlogs. Therefore, we can only open a database with inconsistent statuses through implicit parameters of the application, SQL> create pfile = '/home/oracle/p2.ora' from spfile; add * To pfile *. _ allow_resetlogs_upload uption = TRUEecho "*. _ allow_resetlogs_upload uption = TRUE "> p2.ora then open the database to the mount state through the new pfile: SQL> startup mount pfile = '/home/oracle/p2.ora' the ORACLE routine has been started. Total System Global Area 334036992 bytesFixed Size 2253024 bytesVariable Size 171970336 bytesDatabase Buffers 155189248 bytesRedo Buffers 4624384 bytes database loaded. Then, use the resetlogs method to open the database SQL> alter database open resetlogs; the database has been changed. Because we started it with the pfile we generated temporarily, we had to complete the last step. Just restart the database. The database was opened, but our database was recovered from an exception, there may be problems, so we recommend that you back up data to prevent data loss.