(reprint) ora-00283/ora-01610 problems in the database

Source: Internet
Author: User

Here needs to thank Cotton candy to give selfless help, really thank him!
http://blog.itpub.net/67668/viewspace-353270/
The process can be referred to http://www.itpub.net/viewthread.php?tid=1010027&extra=&page=1

Below I will make a small summary of the handling of the problem, hoping to help
The main problems are: Resetlogs/noresetlogs and control documents (refer to Eygle's book)
First:
To figure out the difference between resetlogs and noresetlogs
Norestlogs, the SCN of the control file is the high SCN from the current log, while the SCN of the Resetlogs control file is from the data file.
I did not understand here, I noresetlogs to restore no problem, then asked the cotton candy to know why Noresetlogs can succeed, and Resetlogs no success.

Second:
How to get a script to create a control file is as follows:
Sql>alter database backup to trace;
Then the script that queries the trace file can query the relevant details
sql> SELECT a.value| | b.symbol| | c.instance_name| | ' _ora_ ' | | d.spid| | '. TRC ' Trace_file_name
2 from (SELECT VALUE from V$parameter WHERE name= ' user_dump_dest ') A,
3 (SELECT SUBSTR (value,-6,1) symbol from V$parameter WHERE name= ' user_dump_dest ') b,
4 (SELECT instance_name from v$instance) C,
5 (SELECT spid from v$session s,v$process p,v$mystat m
6 WHERE s.paddr=p.addr and S.sid=m.sid and m.statistic#=0) d
7/

Trace_file_name
---------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_5724.trc
In this ORCL_ORA_5724.TRC script, you can find two ways to create a control file noresetlogs/resetlogs.

Third: The process of simulating errors
Shutdown abort to close the database.
IV: Using Noresetlogs for control files and database recovery
1. Boot to the database for Nomount status
Sql>startup Nomount;
2. Start creating control files
CREATE controlfile Reuse DATABASE "ORCL" Noresetlogs ARCHIVELOG
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
Maxloghistory 292
LOGFILE
GROUP 1 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01. LOG ' SIZE 50M,
GROUP 2 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02. LOG ' SIZE 50M,
GROUP 3 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03. LOG ' SIZE 50M
--STANDBY LOGFILE
DataFile
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FTITEM10G01 ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EYGLE01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FTTEST01 ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZXF1. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZXF2. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TZX01. DBF '
CHARACTER SET ZHS16GBK
;
3.recover database; Recovering a Database
4. Open databases alter DATABASE open;
Because Noresetlogs is restored with the high SCN of the current log, there is basically no problem.

IV: Using Resetlogs for control files and database recovery, some of the problems that arise here
1.shutdown Abort
2. Database boot to Nomount state
3. Create a control file
CREATE controlfile Reuse DATABASE "ORCL" Resetlogs ARCHIVELOG
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
Maxloghistory 292
LOGFILE
GROUP 1 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01. LOG ' SIZE 50M,
GROUP 2 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02. LOG ' SIZE 50M,
GROUP 3 ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03. LOG ' SIZE 50M
--STANDBY LOGFILE
DataFile
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FTITEM10G01 ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EYGLE01. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FTTEST01 ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZXF1. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZXF2. DBF ',
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TZX01. DBF '
CHARACTER SET ZHS16GBK
;
4.alter database Mount; Boot database to mount state
5.sql> Recover database;
ORA-00283:Recovery session canceled because of an error
ORA-01610:UseBACKUP ControlfileRecovery of option must be completed
Because of the Resetlogs method, the error will appear as above
You need to restore it in the following way
sql> Recover database using Backup controlfile until cancel;
ORA-00279: Change 6432534 (generated on 06/22/2008 16:39:31) is required for thread 1
ORA-00289: Recommended:
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_06_22\O1_MF_1_
13_%u_. ARC
ORA-00280: Change 6432534 (for thread 1) in the sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}

Here are a few options
Suggested: On top ORA-00289: Suggest: .... Will press this file to recover.
FileName: Specify the log file yourself
Auto: Auto Select, but I personally feel like suggestted: (
Cancel: This can not be recovered from the media.

I am here to choose Auto and suggested no no no, reported error is as follows:
Specify log: {=suggested | filename | AUTO | CANCEL}
Auto
ORA-00308: Unable to open archive log ' e:oracleproduct10.2.0flash_recovery_areaorclarchivelog2008_06_22
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 2) The system cannot find the specified file.

Use the Cancel report for the following error:
ORA-01112: Media recovery is not started

After the above steps found:
What I'm asking for in the archive log here is that the log file does not exist
Specify log: {=suggested | filename | AUTO | CANCEL}
E:oracleproduct10.2.0flash_recovery_areaorclarchivelog2008_06_22o1_mf_1_9_45w1nqdp_. ARC
ORA-00310: Archive log contains sequence 9; Request Sequence 10
ORA-00334: Archive log:
' E:oracleproduct10.2.0flash_recovery_areaorclarchivelog2008_06_22o1_mf_1
_9_45w1nqdp_. ARC '

To view the log file:
SELECT * from V$log;
group# thread# sequence# BYTES members ARC STATUS
------ ---------- ---------- ---------- ---------- --- --------
1 1 0 52428800 1 YES UNUSED
3 1 0 52428800 1 YES current
2 1 0 52428800 1 YES UNUSED
Found here the understanding of the problem, because the use of resetlogs, so the log file is definitely unused, ah, here to thank the Cotton candy.

6. Take a closer look at Eygle's book when there's a word
"If the online logs are not corrupted, you can specify that the online log files perform recovery", which is also indicated by cotton candy:
"Oh, forget your is resetlogs, your online log is still in, put the path of the online log ah, such as d:oracleproduct10.2.0oradatatestredo01.log, you put all the logs to test the past, There is one that should be able to do recovery "
After another attempt, it finally worked.
sql> Recover database using Backup controlfile until cancel;
ORA-00279: Change 6432534 (generated on 06/22/2008 16:39:31) is required for thread 1
ORA-00289: Recommended:
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_06_22\O1_MF_1_
13_%u_. ARC
ORA-00280: Change 6432534 (for thread 1) in the sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03. LOG
ORA-00310: Archive log contains sequence 12; Request Sequence 13
ORA-00334: Archive log: ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03. LOG '


sql> Recover database using Backup controlfile until cancel;
ORA-00279: Change 6432534 (generated on 06/22/2008 16:39:31) is required for thread 1
ORA-00289: Recommended:
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_06_22\O1_MF_1_
13_%u_. ARC
ORA-00280: Change 6432534 (for thread 1) in the sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02. LOG
The log that was applied.
Complete media recovery.
sql> ALTER DATABASE open;
ALTER DATABASE Open
*
An error occurred on line 1th:
ORA-01589: To open a database you must use the Resetlogs or Noresetlogs option
sql> ALTER DATABASE open resetlogs;
The database has changed.

(reprint) The database appears ora-00283/ora-01610 problems

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.