Problem of archive log

[michael_nh]

Hi,

I have lost my control file,so I need recover system.

After I input command archive log list,system gave me the following message:

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/admin/oracle8/arch
Oldest online log sequence 67
Next log sequence to archive 69
Current log sequence 69

when I recover database,system can't find file /app/oracle/admin/oracle8/arch/arch_1_69.arc
and give me the following error messsage,I can only find the last file is arch_1_68.arc.

ORA-00279: change 765190 generated at 07/04/2002 10:25:53 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_69.arc
ORA-00280: change 765190 for thread 1 is in sequence #69
ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_68.arc' no longer
needed for this recovery


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

ORA-00308: cannot open archived log
'/app/oracle/admin/oracle8/arch/arch_1_69.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

How can I recover my database,please help me .

Thanks,
Michael Ni

[pnrdba]

See,
Its asking for archive log with sequence number 69, and in the out put of archivelog list observe that , current log sequence is also 69. So just cancel the recovery and open your database with resetlogs option. Becuase you lost your control file, and has to recover using backup control file.

thanks

[michael_nh]

Hi Pnrdba,

I have try before you teach me, could you tell me where I make a mistake.

SQL> startup
ORACLE instance started.

Total System Global Area 56012784 bytes
Fixed Size 69616 bytes
Variable Size 38993920 bytes
Database Buffers 16777216 bytes
Redo Buffers 172032 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

copy backup control file

SQL> startup mount
ORACLE instance started.

Total System Global Area 56012784 bytes
Fixed Size 69616 bytes
Variable Size 38993920 bytes
Database Buffers 16777216 bytes
Redo Buffers 172032 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 704948 generated at 07/03/2002 12:40:11 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_62.arc
ORA-00280: change 704948 for thread 1 is in sequence #62


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

ORA-00279: change 725005 generated at 07/04/2002 13:04:06 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_63.arc
ORA-00280: change 725005 for thread 1 is in sequence #63
ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_62.arc' no longer
needed for this recovery
.
.
.
.
.
ORA-00279: change 725067 generated at 07/04/2002 13:04:47 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_67.arc
ORA-00280: change 725067 for thread 1 is in sequence #67
ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_66.arc' no longer
needed for this recovery


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

ORA-00279: change 725068 generated at 07/04/2002 13:04:47 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_68.arc
ORA-00280: change 725068 for thread 1 is in sequence #68
ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_67.arc' no longer
needed for this recovery


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

ORA-00308: cannot open archived log
'/app/oracle/admin/oracle8/arch/arch_1_68.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/oradata/oracle8/system01.dbf'


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/admin/oracle8/arch
Oldest online log sequence 60
Next log sequence to archive 60
Current log sequence 62

[oracle@oradb arch]$ ls
arch_1_60.arc arch_1_62.arc arch_1_64.arc arch_1_66.arc
arch_1_61.arc arch_1_63.arc arch_1_65.arc arch_1_67.arc

I don't know how to do next, I will grateful you if you can help me.

Regards,
Michael Ni

[oravijay]

Hi,

The main problem in recovering with a backup controlfile is that you have to open your database using RESETLOGS.

Instead, write a script for re-creating the controlfile and then do a recovery.

HTH.
Vijay.

[michael_nh]

Why I can't find arch_1_69.arc ?
I have use command archive log all,but it still can't generate file arch_1_67.arc

Regards,
Michael Ni

[alapps]

Since you are missing log 69 you can recover up to and including log 68


Your statement below should be changed:

OLD:
SQL> recover database using backup controlfile;

NEW:
SQL> recover database using backup controlfile UNTIL CANCEL;

when it asks for log 69... type in CANCEL

Open the database with the resetlogs option.

This will perform an incomplete recovery through log 68 (all that is available)..

Hope this helps...

[michael_nh]

Hi Alapps,

I have done as you teach me,but still can't recover database,can you tell me other ways?

SQL> recover database using backup controlfile until cancel
ORA-00279: change 725062 generated at 07/04/2002 15:30:19 needed for thread 1
ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_68.arc
ORA-00280: change 725062 for thread 1 is in sequence #68


Specify 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: '/data/oradata/oracle8/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/oradata/oracle8/system01.dbf'

Best regards,
Michael Ni

[pnrdba]

After u said "alter database recover automatic using backup controlfile until cancel" Then it asks for
Specify log: {=suggested | filename | AUTO | CANCEL}

Here, TYPE CANCEL, and then
Open database with resetlogs Option.

This will definitely work.

Thanks

[michael_nh]

Hi Pnrdba,

Alapps has told me which you just told me, I have tried but failed, you can see the forward reply which has result of performance.

Regards,
Michael Ni

[alapps]

michael_nh,

Since you have attempted a full recovery (UNTIL CANCEL not specified), you need to restore from the last backup, perform the incomplete recovery (recover automatic using backup controlfile until cancel), open the database with resetlogs and you should be "Back in Business"...