Home » RDBMS Server » Backup & Recovery » ora-1194 and ora-1110 (Oracle 10g 10.2.0.1 Windows (32bit))
ora-1194 and ora-1110 [message #406160] Tue, 02 June 2009 10:37 Go to next message
marcossantos
Messages: 114
Registered: June 2008
Senior Member
Hi,
The database server is crash.
I have datafiles, controlfiles and redologs.
I dont have archive (the oracle was noarchivelog mode)
I have install the oracle in other machine.
And I execute the following steps:

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1098907648 bytes
Fixed Size                  1250092 bytes
Variable Size             587205844 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 geodbam
FAILOVERTESTE01
10.2.0.1.0        02-JUN-09 MOUNTED      NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


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: 'F:\ORACLE\GEODBAM\SYSTEM01.DBF'




Can someone help me?

Marcos Santos
Re: ora-1194 and ora-1110 [message #406168 is a reply to message #406160] Tue, 02 June 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
recover the database before opening.

Regards
Michel
Re: ora-1194 and ora-1110 [message #406176 is a reply to message #406168] Tue, 02 June 2009 12:28 Go to previous message
marcossantos
Messages: 114
Registered: June 2008
Senior Member
SQL> select substr(name, 1, 50), status from v$datafile;

SUBSTR(NAME,1,50)                                  STATUS                                                                                                                           
-------------------------------------------------- -------                                                                                                                          
F:\ORACLE\GEODBAM\SYSTEM01.DBF                     SYSTEM                                                                                                                           
F:\ORACLE\GEODBAM\UNDOTBS01.DBF                    RECOVER                                                                                                                          
F:\ORACLE\GEODBAM\SYSAUX01.DBF                     RECOVER                                                                                                                          
F:\ORACLE\GEODBAM\USERS01.DBF                      RECOVER                                                                                                                          
F:\ORACLE\GEODBAM\SDE01.DBF                        RECOVER                                                                                                                          
F:\ORACLE\GEODBAM\SISCOM01.DBF                     RECOVER                                                                                                                          
F:\ORACLE\GEODBAM\SDE02                            RECOVER                                                                                                                          

7 rows selected.

SQL> select
  2  substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,40)                        REC FUZ CHECKPOINT_CHANGE#                                                                                                                 
---------------------------------------- --- --- ------------------                                                                                                                 
F:\ORACLE\GEODBAM\SYSTEM01.DBF               YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\UNDOTBS01.DBF              YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\SYSAUX01.DBF               YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\USERS01.DBF                YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\SDE01.DBF                  YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\SISCOM01.DBF               YES           57612018                                                                                                                 
F:\ORACLE\GEODBAM\SDE02                      YES           57612018                                                                                                                 

7 rows selected.

SQL> select GROUP#,substr(member,1,60) from v$logfile;

          GROUP# SUBSTR(MEMBER,1,60)                                                                                                                                                
---------------- ------------------------------------------------------------                                                                                                       
               3 F:\ORACLE\GEODBAM\REDO03.LOG                                                                                                                                       
               2 F:\ORACLE\GEODBAM\REDO02.LOG                                                                                                                                       
               1 F:\ORACLE\GEODBAM\REDO01.LOG                                                                                                                                       

3 rows selected.

SQL> select
  2  * from v$recover_file;

           FILE# ONLINE  ONLINE_ ERROR                                                                      CHANGE# TIME                                                            
---------------- ------- ------- ----------------------------------------------------------------- ---------------- ---------                                                       
               1 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               2 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               3 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               4 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               5 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               6 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       
               7 ONLINE  ONLINE                                                                            57612018 19-MAY-09                                                       

7 rows selected.

SQL> select distinct status from v$backup;

STATUS                                                                                                                                                                              
------------------                                                                                                                                                                  
NOT ACTIVE                                                                                                                                                                          

1 row selected.

SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

      FILENUMBER           STATUS SCN                      SEQUENCE                                                                                                                 
---------------- ---------------- ---------------- ----------------                                                                                                                 
               1             8196 57612018                    13842                                                                                                                 
               2                4 57612018                    13842                                                                                                                 
               3                4 57612018                    13842                                                                                                                 
               4                4 57612018                    13842                                                                                                                 
               5                4 57612018                    13842                                                                                                                 
               6                4 57612018                    13842                                                                                                                 
               7                4 57612018                    13842                                                                                                                 

7 rows selected.

SQL> select distinct (fuzzy) from v$datafile_header;

FUZ                                                                                                                                                                                 
---                                                                                                                                                                                 
YES                                                                                                                                                                                 

1 row selected.

SQL> spool off


I try this.

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1098907648 bytes
Fixed Size                  1250092 bytes
Variable Size             587205844 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes
SQL> recover database;
ORA-01507: database not mounted


SQL>
SQL>
SQL> recover database until change 57612018;
ORA-01507: database not mounted


SQL> alter database mount;

Database altered.

SQL> recover database until change 57612018;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 57612018 generated at 05/19/2009 15:50:38 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\FLASH_RECOVERY_AREA\GEODBAM\ARCHIVELOG\2009_06_02\O1_MF_1_13842_%U_.ARC
ORA-00280: change 57612018 for thread 1 is in sequence #13842


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



I dont have archivelog files.

Thanks,

Marcos Santos
Previous Topic: alter tablespace begin backup
Next Topic: Recovery Catalog RMAN
Goto Forum:
  


Current Time: Sun Dec 11 04:12:47 CST 2016

Total time taken to generate the page: 0.14859 seconds