| Recovery Problem [message #162211] |
Thu, 09 March 2006 03:26  |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
HI all,
I ve one problem.
I am working on windows OS.
Oracle database version is 9.2.0.1.0
Now i did some txns as scott and crashed SYSTEM datafile as follows and while i am trying
to recover the database it is showing the below last error. I dont 've Archive log no 29
with me.
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination d:\archive
Oldest online log sequence 29
Current log sequence 31
SQL>
Next i shutdown the database and went to mount state and made the database to run in
ARCHIVELOG mode.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\archive
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
SQL> alter database open;
// Now i logged in as scott and performed some txns say insert
SQL> alter table emp drop primary key;
Table altered.
SQL> insert into emp select * from emp;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
448
//AS "sys" user
SQL>select username,default_tablespace from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT SYSTEM
// now i shutdown the database and crashed SYSTEM DATAFILE
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\archive
Oldest online log sequence 32
Next log sequence to archive 34
Current log sequence 34
SQL>
//and now i crashed SYSTEM datafile.i.e i physically removed that datafile at OS level.
//and tried to startup and it stoped at mount stage reporting an error.
//now gain shut the database
//restored the SYSTEM.DBF from my cold backup and went to mount stage and tried to recover
//the database
C:\Documents and Settings\maheshv>sqlplus/nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 9 14:51:35 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database recover automatic;
alter database recover automatic
*
ERROR at line 1:
ORA-00279: change 226836 generated at 03/09/2006 12:35:07 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00029.001
ORA-00280: change 226836 for thread 1 is in sequence #29
ORA-00278: log file 'D:\ARCHIVE\ARC00029.001' no longer needed for this recovery
ORA-00308: cannot open archived log 'D:\ARCHIVE\ARC00029.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
|
|
|
|
|
|
| Re: Recovery Problem [message #162255 is a reply to message #162212] |
Thu, 09 March 2006 07:18  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Frank, you said:
| Quote: |
I dont 've Archive log no 29
with me.
|
In this case go for point in time recovery.
Read some docs about that & then try.
--Girish
|
|
|
|
| Re: Recovery Problem [message #162406 is a reply to message #162211] |
Fri, 10 March 2006 01:36 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
Try this cmds:
SQL> startup mount
SQL> recover database using backup until cancel;
cancel
SQL> alter database open resetlogs;
Alex zeng|Skype Me: hans9zeng
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
