Problemas Oracle 10g (Socorro) - Erro ORA-0027 e ORA-00202

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Responder
pauloaleo
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 181
Registrado em: Qua, 09 Fev 2005 12:30 pm
Localização: SÃO BERNARDO DO CAMPO - SP

Selecionar tudo

_allow_resetlogs_corruption = true
_allow_read_only_corruption = true
_no_recovery_through_resetlogs=TRUE
estes parametros tem que ser colocados no init do banco.

Diluma , ajuda ai , onde fica o init do banco.
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Nesse link tem exatamente o que precisa fazer,
http://eduardolegatti.blogspot.com/2008 ... ption.html

como já havia passado,

no caso ele startou nomount, criou o pfile e montou com o pfile=c:\pfile

vai dar certo também,
Fernando Ribeiro
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 02 Fev 2010 9:58 am
Localização: são Paulo - SP
Contato:
Fernando Ribeiro - System Administrator

Boa tarde Pessoal.
Gostaria de agradecer a todos que me ajudaram na resolução do problema no Oracle 10g, agradecer ao Diego e principalmente ao Paulo, que me acompanhou passo a passo na resolução do problema...
Bem, irei postar o passo a passo dos comandos que o Paulo me auxiliou...

Selecionar tudo

SQL>   GROUP 2 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_
5PMF7TRW_.LOG'
SP2-0734: unknown command beginning "GROUP 2 'C..." - rest of line ignored.
SQL>
SQL> SIZE 50M,
SP2-0042: unknown command "SIZE 50M," - rest of line ignored.
SQL>   GROUP 3 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_3_
5PMF7VM1_.LOG'
SP2-0734: unknown command beginning "GROUP 3 'C..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>
SQL> SIZE 100M
SP2-0042: unknown command "SIZE 100M" - rest of line ignored.
SQL> -- STANDBY LOGFILE
SQL> DATAFILE
SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SQL>   'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
SP2-0734: unknown command beginning "'C:\ORACLE..." - rest of line ignored.
SQL>   'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
SP2-0734: unknown command beginning "'C:\ORACLE..." - rest of line ignored.
SQL>   'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
SP2-0734: unknown command beginning "'C:\ORACLE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   'C:\ORACLEXE\ORADATA\XE\USERS.DBF'
SP2-0734: unknown command beginning "'C:\ORACLE..." - rest of line ignored.
SQL> CHARACTER SET US7ASCII
SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.
SQL> ;
  1  CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8*   GROUP 1 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_
5PMF7SXS_.LOG'
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ
ction

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 16:32:43 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> ?
SP2-0171: HELP system not available.
SQL> !
SP2-0042: unknown command "!" - rest of line ignored.
SQL> CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG
  2  MAXLOGFILES 16
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 8
  6  MAXLOGHISTORY 292
  7  LOGFILE
  8  GROUP 1
  9  'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_5PMF7SXS_.
LOG'SIZE 50M,
 10  GROUP 2
 11  'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_5PMF7TRW_.
LOG'SIZE 50M,
 12  GROUP 3
 13  'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_3_5PMF7VM1_.
LOG' SIZE 100M
 14  -- STANDBY LOGFILE
 15  DATAFILE
 16  'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
 17  'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
 18  'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
 19  'C:\ORACLEXE\ORADATA\XE\USERS.DBF'
 20  CHARACTER SET US7ASCII;

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recovery database;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> shutdown abort;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ
ction

C:\Documents and Settings\Administrator>set Oracle_sid=XE

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 16:54:10 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1286820 bytes
Variable Size              83889500 bytes
Database Buffers          163577856 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select status v$database;
select status v$database
                       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select status v$instance;
select status v$instance
                       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ
ction

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 16:57:20 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 16:58:57 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1286820 bytes
Variable Size              83889500 bytes
Database Buffers          163577856 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
         3         ONLINE
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_3_5PMF7VM1_.LOG
YES

         2 STALE   ONLINE
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_5PMF7TRW_.LOG
YES

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---

         1 STALE   ONLINE
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_5PMF7SXS_.LOG
YES


SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         5 OFFLINE OFFLINE
FILE MISSING                                                               0


         6 OFFLINE OFFLINE
FILE MISSING                                                               0


     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------


SQL> select status from v$datafile_reader;
select status from v$datafile_reader
                   *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select status from v$datafile_header;

STATUS
-------
ONLINE
ONLINE
ONLINE
ONLINE
OFFLINE
OFFLINE

6 rows selected.

SQL> select file# from v$datafile_header;

     FILE#
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> select file# from v$recover_file;

     FILE#
----------
         5
         6

SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\MISSING00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\MISSING00005'


SQL>
Recuperamos muita coisa, porém ainda ficaram algumas tabelas perdidas... Mas é melhor alguma coisa do que nada né???
Se alguém souber de como recuperar essas outras tabelas perdidas... Por favor...
Abraços e obrigado a todos...
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Bom não é o intuito criticar mas,

se você tem informações importantes num banco de dados, faça SEMPRE backup,

um simples comando exp ou expdp teria em no maximo 1 hora recuperado tudo,

=[
Fernando Ribeiro
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 02 Fev 2010 9:58 am
Localização: são Paulo - SP
Contato:
Fernando Ribeiro - System Administrator

Teria uma maneira rapida e simples de efetuar o backup???
Sabe como???

Abraços e muito obrigado!!!

Cordialmente,

Fernando Ribeiro - 9634-0854
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Selecionar tudo

exp usuario/senha file=C:\banco.dmp log=C:\logExp.log statistics=none owner=USUARIO
ou ainda, se tiver varios schemas,

use o usuario SYSTEM pra conectar, e onde tem owner=USUARIO
mude pra: ai ira backupear todos os schemas do banco,

ai você pode fazer um arquivo .bat com isso, e agendar uma tarefa do windows para fazer o backup todo dia, :)
pedroedu
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 50
Registrado em: Qua, 10 Fev 2010 3:04 pm
Localização: são Luis
Oracle Database 10g/12c Administrator Certified Professional
Oracle Database 11g Security
ISFS Information Security Foundation based on ISO IEC 27001
ITIL® Foundation V3 in IT Service Management

Selecionar tudo

shutdown immediate
startup nomount
recria o controlfile com o trace ou apenas o substitui por um outro bom, porque com certeza a extensao clt esta oculta pelo windows, ai você da um recover database until cancel using backup controlfile, vai pedir os archives se tiver em archive log ai digita AUTO, se não aplicar e não abrir e for muiiito importante esse banco, arruma uma maneira de acesseo remoto q dou um jeito de abrir-lo sem te cobrar...so faz uma backup dele antes de comecar a mexer na recuperacao(essa seria a primeira providencia a ser tomada antes de tentar recuperar um bd com problema, porque as vezes a tentativa de recuperar bagunca o dicionario e o cabecalho dos arquivos de dados e controle)
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes