Treinamentos Oracle - Nerv Informática

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...)
  

Mensagemem Qua, 03 Fev 2010 3:42 pm

_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.
pauloaleo
Localização: SÃO BERNARDO DO CAMPO - SP

Mensagemem Qua, 03 Fev 2010 3:57 pm

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,
diegolenhardt
Localização: Recife

Mensagemem Qui, 04 Fev 2010 12:55 pm

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...

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...
Fernando Ribeiro
Localização: Sao Paulo - SP

Fernando Ribeiro - System Administrator

Mensagemem Qui, 04 Fev 2010 1:01 pm

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,

=[
diegolenhardt
Localização: Recife

Mensagemem Sex, 05 Fev 2010 10:17 am

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

Abraços e muito obrigado!!!

Cordialmente,

Fernando Ribeiro - 9634-0854
Fernando Ribeiro
Localização: Sao Paulo - SP

Fernando Ribeiro - System Administrator

Mensagemem Sex, 05 Fev 2010 10:24 am

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:

FULL=Y

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, :)
diegolenhardt
Localização: Recife

Mensagemem Qui, 11 Fev 2010 11:30 am

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)
pedroedu
Localização: Sao Luis

OCP 10g
pedro.lemos@live.com
(98)88178604/(98)84027368


Anterior

Voltar para DBA Geral

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante