OraFAQ Forum: Backup & Recovery » Flash Recovery ->SYSTEM.DBF

Home » RDBMS Server » Backup & Recovery » Flash Recovery ->SYSTEM.DBF (Oracle 11.2.0.2.0 Beta)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Flash Recovery ->SYSTEM.DBF [message #562352]

Tue, 31 July 2012 12:19

drumboy
Messages: 5
Registered: July 2012
Location: Hungary

Junior Member

Hi!

How can I repair the C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF file?

Drumboy

sqlplus

SQL*Plus: Release 11.2.0.2.0 Beta on K. J˙l. 31 18:43:26 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

SQL> startup;
ORACLE instance started.

Total System Global Area 577511424 bytes
Fixed Size 1385040 bytes
Variable Size 369102256 bytes
Database Buffers 201326592 bytes
Redo Buffers 5697536 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS 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: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 14038427 generated at 07/31/2012 11:20:36 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\RDBMS\ARC0000000450_0773153579.0001

ORA-00280: change 14038427 for thread 1 is in sequence #450

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

SQL> select l.status, member
2 from v$logfile inner join v$log l using (group#);

STATUS MEMBER
---------------- ----------------------------------------
UNUSED C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AR
EA\XE\ONLINELOG\O1_MF_1_7KO9RW0Z_.LOG

CURRENT C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AR
EA\XE\ONLINELOG\O1_MF_2_7KO9RWX9_.LOG

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562354 is a reply to message #562352]

Tue, 31 July 2012 12:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What exactly led up to these happenings?

Is/was DB previously had Archive Mode enabled?

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562357 is a reply to message #562354]

Tue, 31 July 2012 13:16

drumboy
Messages: 5
Registered: July 2012
Location: Hungary

Junior Member

Dear BlackSwan!

This morning I can't contact to Database, and I try it step by step.
I don't know... I didn't turn of the flash recovery.

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562358 is a reply to message #562357]

Tue, 31 July 2012 13:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This morning I can't contact to Database, and I try it step by step.
Possible fatal actions

>I don't know... I didn't turn of the flash recovery.
I did not ask about Flash Recovery.
I asked if this DB had Archive Mode enabled.

post excerpt from alert_SID.log (or V11 equivalent) starting with when DB operated without any error

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562359 is a reply to message #562352]

Tue, 31 July 2012 13:56

drumboy
Messages: 5
Registered: July 2012
Location: Hungary

Junior Member

c:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace\alert_xe.log
...
...
Mon Jul 30 10:49:15 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =35
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta.
Using parameter settings in server-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA
System parameters with non-default values:
processes = 200
sessions = 322
sga_max_size = 552M
spfile = "C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"
nls_language = "HUNGARIAN"
nls_territory = "HUNGARY"
nls_date_language = "HUNGARIAN"
nls_date_format = "YYYY.MM.DD"
nls_currency = "Ft"
nls_numeric_characters = ". "
nls_iso_currency = "HUNGARY"
nls_time_format = "HH24:MI:SSXFF"
nls_timestamp_format = "YYYY.MM.DD HH24:MI:SSXFF"
nls_time_tz_format = "HH24:MI:SSXFF TZH:TZM"
nls_timestamp_tz_format = "YYYY.MM.DD HH24:MI:SSXFF TZH:TZM"
nls_dual_currency = "Ft"
sga_target = 552M
control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
compatible = "11.2.0.0.0"
db_recovery_file_dest = "C:\oraclexe\app\oracle\flash_recovery_area"
db_recovery_file_dest_size= 10G
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
shared_servers = 4
job_queue_processes = 4
cursor_sharing = "EXACT"
audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
optimizer_features_enable= "9.2.0"
db_name = "XE"
open_cursors = 300
os_authent_prefix = ""
optimizer_mode = "CHOOSE"
_b_tree_bitmap_plans = FALSE
optimizer_index_cost_adj = 10
pga_aggregate_target = 90M
diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
Mon Jul 30 10:49:56 2012
PMON started with pid=2, OS id=3684
Mon Jul 30 10:49:56 2012
PSP0 started with pid=3, OS id=3692
Mon Jul 30 10:49:58 2012
VKTM started with pid=4, OS id=3844 at elevated priority
Mon Jul 30 10:49:58 2012
GEN0 started with pid=5, OS id=3848
Mon Jul 30 10:49:58 2012
DIAG started with pid=6, OS id=3852
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Mon Jul 30 10:49:58 2012
DIA0 started with pid=8, OS id=3860
Mon Jul 30 10:49:58 2012
MMAN started with pid=9, OS id=3864
Mon Jul 30 10:49:58 2012
DBW0 started with pid=10, OS id=3868
Mon Jul 30 10:49:58 2012
LGWR started with pid=11, OS id=3872
Mon Jul 30 10:49:58 2012
DBRM started with pid=7, OS id=3856
Mon Jul 30 10:49:58 2012
CKPT started with pid=12, OS id=3876
Mon Jul 30 10:49:58 2012
SMON started with pid=13, OS id=3880
Mon Jul 30 10:49:58 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
ORACLE_BASE from environment = C:\oraclexe\app\oracle
Mon Jul 30 10:49:58 2012
RECO started with pid=14, OS id=3884
Mon Jul 30 10:49:58 2012
MMON started with pid=15, OS id=3888
Mon Jul 30 10:49:58 2012
MMNL started with pid=16, OS id=3892
Mon Jul 30 10:50:04 2012
alter database mount exclusive
Mon Jul 30 10:50:09 2012
Successful mount of redo thread 1, with mount id 2671093660
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 600 KB redo, 77 data blocks need recovery
Started redo application at
Thread 1: logseq 444, block 74988
Recovery of Online Redo Log: Thread 1 Group 2 Seq 444 Reading mem 0
Mem# 0: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_7KO9RWX9_.LOG
Completed redo application of 0.27MB
Completed crash recovery at
Thread 1: logseq 444, block 76189, scn 13895719
77 data blocks read, 77 data blocks written, 600 redo k-bytes read
Thread 1 advanced to log sequence 445 (thread open)
Thread 1 opened at log sequence 445
Current log# 1 seq# 445 mem# 0: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_7KO9RW0Z_.LOG
Successful open of redo thread 1
Mon Jul 30 10:50:15 2012
SMON: enabling cache recovery
Mon Jul 30 10:50:20 2012
[4056] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:117656 end:118750 diff:1094 (10 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Opening with Resource Manager plan: INTERNAL_PLAN_XE
Mon Jul 30 10:50:21 2012
Starting background process VKRM
Mon Jul 30 10:50:21 2012
VKRM started with pid=23, OS id=1892
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jul 30 10:50:23 2012
QMNC started with pid=24, OS id=1936
Completed: alter database open
Mon Jul 30 10:50:40 2012
Starting background process CJQ0
Mon Jul 30 10:50:40 2012
CJQ0 started with pid=33, OS id=3668
Mon Jul 30 10:50:48 2012
db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jul 30 10:55:30 2012
Starting background process SMCO
Mon Jul 30 10:55:30 2012
SMCO started with pid=30, OS id=2344

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562365 is a reply to message #562359]

Tue, 31 July 2012 14:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

How is it that posted excerpt shows NONE of the activity in original post?

What happened between Mon Jul 30 10:55:30 2012 & NOW?

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562366 is a reply to message #562365]

Tue, 31 July 2012 14:37

drumboy
Messages: 5
Registered: July 2012
Location: Hungary

Junior Member

Tue Jul 31 19:01:57 2012
Starting ORACLE instance (normal)
Tue Jul 31 19:02:20 2012
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...
Tue Jul 31 20:38:30 2012
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 4
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Tue Jul 31 20:38:33 2012
Stopping background process VKTM
Tue Jul 31 20:38:35 2012
Instance shutdown complete
Tue Jul 31 20:38:37 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\oraclexe\app\oracle\product\11.2.0\server\RDBMS
Autotune of undo retention is turned on.
IMODE=BR
ILAT =35
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta.
Using parameter settings in server-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA
System parameters with non-default values:
processes = 200
sessions = 322
sga_max_size = 552M
spfile = "C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"
nls_language = "HUNGARIAN"
nls_territory = "HUNGARY"
nls_date_language = "HUNGARIAN"
nls_date_format = "YYYY.MM.DD"
nls_currency = "Ft"
nls_numeric_characters = ". "
nls_iso_currency = "HUNGARY"
nls_time_format = "HH24:MI:SSXFF"
nls_timestamp_format = "YYYY.MM.DD HH24:MI:SSXFF"
nls_time_tz_format = "HH24:MI:SSXFF TZH:TZM"
nls_timestamp_tz_format = "YYYY.MM.DD HH24:MI:SSXFF TZH:TZM"
nls_dual_currency = "Ft"
sga_target = 552M
control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
compatible = "11.2.0.0.0"
db_recovery_file_dest = ""
db_recovery_file_dest_size= 10G
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
shared_servers = 4
job_queue_processes = 4
cursor_sharing = "EXACT"
audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
optimizer_features_enable= "9.2.0"
db_name = "XE"
open_cursors = 300
os_authent_prefix = ""
optimizer_mode = "CHOOSE"
_b_tree_bitmap_plans = FALSE
optimizer_index_cost_adj = 10
pga_aggregate_target = 90M
diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
Tue Jul 31 20:38:51 2012
PMON started with pid=2, OS id=2820
Tue Jul 31 20:38:51 2012
PSP0 started with pid=3, OS id=2252
Tue Jul 31 20:38:52 2012
VKTM started with pid=4, OS id=2620 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Tue Jul 31 20:38:52 2012
GEN0 started with pid=5, OS id=2808
Tue Jul 31 20:38:52 2012
DIAG started with pid=6, OS id=3924
Tue Jul 31 20:38:52 2012
DBRM started with pid=7, OS id=3652
Tue Jul 31 20:38:52 2012
DIA0 started with pid=8, OS id=1924
Tue Jul 31 20:38:52 2012
MMAN started with pid=9, OS id=3748
Tue Jul 31 20:38:52 2012
DBW0 started with pid=10, OS id=856
Tue Jul 31 20:38:52 2012
LGWR started with pid=11, OS id=1592
Tue Jul 31 20:38:52 2012
CKPT started with pid=12, OS id=640
Tue Jul 31 20:38:52 2012
SMON started with pid=13, OS id=3460
Tue Jul 31 20:38:52 2012
RECO started with pid=14, OS id=1128
Tue Jul 31 20:38:52 2012
MMON started with pid=15, OS id=1720
Tue Jul 31 20:38:52 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
ORACLE_BASE from environment = C:\oraclexe\app\oracle
Tue Jul 31 20:38:52 2012
ALTER DATABASE MOUNT
Tue Jul 31 20:38:52 2012
MMNL started with pid=16, OS id=1724
Successful mount of redo thread 1, with mount id 2671263516
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Tue Jul 31 20:38:56 2012
ALTER DATABASE OPEN
Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_3856.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: ALTER DATABASE OPEN...
Tue Jul 31 20:55:44 2012
License high water mark = 2
USER (ospid: 2668): terminating the instance
Instance terminated by USER, pid = 2668
Tue Jul 31 20:55:48 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\oraclexe\app\oracle\product\11.2.0\server\RDBMS
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta.
Using parameter settings in client-side pfile C:\DOCUME~1\USER\LOCALS~1\TEMP\RMAN_DUMMY.ORA on machine 5128C978A31B483
System parameters with non-default values:
sga_target = 272M
db_name = "xe"
Tue Jul 31 20:56:00 2012
PMON started with pid=2, OS id=4008
Tue Jul 31 20:56:00 2012
PSP0 started with pid=3, OS id=2536
Tue Jul 31 20:56:01 2012
VKTM started with pid=4, OS id=3208 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Tue Jul 31 20:56:01 2012
GEN0 started with pid=5, OS id=3704
Tue Jul 31 20:56:01 2012
DIAG started with pid=6, OS id=184
Tue Jul 31 20:56:01 2012
DBRM started with pid=7, OS id=3592
Tue Jul 31 20:56:01 2012
DIA0 started with pid=8, OS id=2432
Tue Jul 31 20:56:01 2012
MMAN started with pid=9, OS id=2456
Tue Jul 31 20:56:01 2012
DBW0 started with pid=10, OS id=3436
Tue Jul 31 20:56:01 2012
LGWR started with pid=11, OS id=2920
Tue Jul 31 20:56:01 2012
CKPT started with pid=12, OS id=3240
Tue Jul 31 20:56:01 2012
SMON started with pid=13, OS id=2828
Tue Jul 31 20:56:01 2012
RECO started with pid=14, OS id=3300
Tue Jul 31 20:56:01 2012
MMON started with pid=15, OS id=160
Tue Jul 31 20:56:01 2012
MMNL started with pid=16, OS id=3964
Tue Jul 31 20:56:01 2012
ORACLE_BASE from environment = C:\oraclexe\app\oracle
Tue Jul 31 20:57:56 2012
Starting ORACLE instance (normal)

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562375 is a reply to message #562366]

Tue, 31 July 2012 22:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Completed: alter database open
>Mon Jul 30 10:50:40 2012

above existed in first excerpt & show clean DB open
Below existed in second excerpt & shows a problem opening DB
*NOTE* more than 24 hours elapsed between the two excerpts

>Tue Jul 31 19:01:57 2012
>Starting ORACLE instance (normal)
>Tue Jul 31 19:02:20 2012
>alter database open resetlogs
>ORA-1194 signalled during: alter database open resetlogs...
>Tue Jul 31 20:38:30 2012
>Shutting down instance (immediate)

farther down the second excerpt we see the following:

>Completed: ALTER DATABASE MOUNT
>Tue Jul 31 20:38:56 2012
>ALTER DATABASE OPEN
>Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_3856.trc:
>ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
>ORA-1589 signalled during: ALTER DATABASE OPEN...
>Tue Jul 31 20:55:44 2012
>License high water mark = 2
>USER (ospid: 2668): terminating the instance
>Instance terminated by USER, pid = 2668
>Tue Jul 31 20:55:48 2012
>Starting ORACLE instance (normal)

NOTICE the gap of about 17 minutes in records above
It appears that this is when REDO logs were applied to make file consistent.

I don't see any problem so I am not sure why this thread was started.

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562388 is a reply to message #562375]

Wed, 01 August 2012 03:42

drumboy
Messages: 5
Registered: July 2012
Location: Hungary

Junior Member

Dear BlackSwan!

First of all, thank your help!
I'm not expert in oracle, so I uploaded the full alert log, maybe you can see some other problem.

www.drumboy.hu/alert_xe.log

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562725 is a reply to message #562388]

Fri, 03 August 2012 18:09

raceone
Messages: 3
Registered: July 2012

Junior Member

Before I give any advice please answer a couple of questions.

1. What did you do, or what happened, that you are where you are ... give a detailed explanation.
2. Why not drop it and just do a reinstall?
3. Please post the relevant sections of the alert log for us to review (and if you do not know how to format a listing read the FAQ ...

Report message to a moderator

Re: Flash Recovery ->SYSTEM.DBF [message #562736 is a reply to message #562725]

Sat, 04 August 2012 00:27

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you think Blackswan asked?
What do you think the previous posts contain?
what do you think the url (with "alert_xe.log") OP posted contain?

Regards
Michel

[Updated on: Sat, 04 August 2012 00:46]

Report message to a moderator

Previous Topic:	Check the DB Size exclude particular table "A"
Next Topic:	Rman error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 21:16:05 CST 2024