Bank is not OPEN

[ramonmeloo2]

Galera is the following .. Mucho very little with Oracle, but in the company I work, something happened that the bank is no longer in 'Open' mode.

When I try to place, after the message:

Select all

ORA-01157: cannot identify/lock data file 12 -  see DBWR trace file. 
ORA-01110: data file 12: 'F:\ORACLE\ORADATA\PRODUCAO\SIG_DADOS_01.DBF'

I can leave the bench 'Mounted', but it does not open.

What could have happened?

I have a backup (.dmp), but this .dbf file I believe it is corrupted because the bank is not opening.

I believe this happened after he tried to back up and there was no space on the server. Can someone help me?

Environment is a Windows 2000 Server server with Oracle9i

Thank you !!

[madmax]

Hello Ramon,

face this error and why Oracle can not find the Datafile or he is being used or is corrupted indeed.

Makes the following 1st How and Windows restarts the server and try to get Start on the bench to see if it turned.

but

Select all

ALTER SYSTEM CHECK DATAFILES

If no FO is not to change its Datafile

Select all

 alter database rename file '+ DG1/testdb/datafile/TS1.256.739201677' para '/ u01/datafiles/ts1.dbf';

If you do not solve hope you have backed up.

I hope I have helped.

[ramonmeloo2]

Oops, thanks for the answer.
is the following, the file is exactly on this path he showed (so I think it's corrupted).

I have restarted the server several times and it does not return.

When I open the Enterprise Manager Console, I can only connect to the Launch Standalone option. When I'm going to connect with the bank, I can only put sysdba (in connect as), no use and no password.

Oh I can leave as: Shutdown, Started (Nomant) and Started (Mounted). If I click Open, he made this mistake I showed.

If I go to SQLPlus (with the Bank in Shutdown) in DOS, the following appears:

Select all

C:\Documents and Settings\Administrator>sqlplus /nolog 
 
SQL*Plus: Release 9.2.0.6.0 - Production on Seg Mai 21 10:54:07 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
SQL> conn usuario/senha@producao 
ERROR: 
ORA-01034: ORACLE not available 
ORA-27101: shared memory realm does not exist

If I try to connect directly, the following error:

Select all

C:\Documents and Settings\Administrator.SRH>sqlplus "/ as sysdba" 
 
SQL*Plus: Release 9.2.0.6.0 - Production on Seg Mai 21 10:56:31 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
ERROR: 
ORA-12560: TNS:erro de adaptador de protocolo

I can not perform these commands that you passed me because Says I'm not connected ..

Thanks face ..

[madmax]

Wait for aí on the part

This error showing when you connect with sys by the

Select all

ORA-27101: shared memory realm does not exist

and because there is not enough memory for Oracle Work, review the memory of this server and try to connect with SYS by DOS and run the command that I passed:

Select all

ALTER SYSTEM CHECK DATAFILES 

Anything posts aí.

ABS.

[ramonmeloo2]

Poise, apparently the memory of the server is all free .. very strange ..
and this protocol adapter error?

Select all

C:\Documents and Settings\Administrator.SRH>sqlplus "/ as sysdba" 
 
SQL*Plus: Release 9.2.0.6.0 - Production on Seg Mai 21 11:38:45 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
ERROR: 
ORA-12560: TNS:erro de adaptador de protocolo

Excuse the expensive ignorance ..
Where are the Oracle server memory configuration files? Type, is there any file that specifies the amount of memory that this server will use for this Oracle bank?

[madmax]

Ramon

the memory configuration and made in the bank installation but you can change by configuring the following SGA parameters following link that can help
http://en.glufke.net/oracle/viewtopic.php?t=4306

Referring to error ORA-12560: TNS: Protocol adapter error

Select all

ORA-12560: TNS: protocol adapter error 
 
Cause: A generic protocol adapter error occurred. 
 
Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and re-execute the operation. Turn off tracing when the operation is complete. 

More link on this: [[2] http://www.dba-oracle.com/t_ora_12560_t ... _error.htm
I hope I helped you.

ABS

[ramonmeloo2]

Dude, I managed to run your command. I increased his memory também .. it was like this:

Select all

C:\Documents and Settings\Administrator.SRH>sqlplus /nolog 
 
SQL*Plus: Release 9.2.0.6.0 - Production on Seg Mai 21 15:17:28 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
SQL> conn user_srhsig@producao/srhsig 
ERROR: 
ORA-01034: ORACLE not available 
ORA-27101: shared memory realm does not exist 
 
 
SQL> conn / as sysdba 
Conectado a uma instΓncia inativa. 
 
 
SQL> startup 
InstΓncia ORACLE iniciada. 
 
Total System Global Area  529605220 bytes 
Fixed Size                   455268 bytes 
Variable Size             419430400 bytes 
Database Buffers          109051904 bytes 
Redo Buffers                 667648 bytes 
Banco de dados montado. 
ORA-01157: nπo Θ possφvel identificar/bloquear arquivo de dados 12 - consulte 
arquivo de anßlise DBWR 
ORA-01110: 12 do arquivo de dados: 
'F:\ORACLE\ORADATA\PRODUCAO\SIG_DADOS_01.DBF' 
 
 
SQL>  ALTER SYSTEM CHECK DATAFILES; 
 
Sistema alterado.

The bank can not climb at all. I read in some forums that this memory error can be configuration in oracle_home or oracle_sid ... I put it like this:

Select all

SET ORACLE_SID=producao 
SET ORACLE_HOME=c:\oracle\ora92

Is that right?

The memory is free, the server is using only this .. Set the memory (pool_size, etc) everything is ok.

What exactly does this dbf file mean ?? With this file that I have backup (.dmp) can I restore the bank?

Thanks for the forle expensive ...

[madmax]

Ramon

answering your question.

What exactly does this dbf file mean ??
A: DBF is Oracle Datafile and where Tablespace is saved, tables, index

If you have the backup files (.dmp), yes you can recover the bank and just do an import using these backup (.dmp).

ABS.

[ramonmeloo2]

I have this updated .dmp.
So with this file I can recover DBF? It generates a new .dbf file from this .dmp?

When I try the Recovery option, he says I have to access the Oracle Management Server. But I can not access it. I'm creating a new OEM repository to be able to see if I can access. I read that I needed to be done, because there is not this service. I'll test this and put the answer ... thanks !!

[ramonmeloo2]

Dude, I'm trying to import this .dmp anyway and it does not work .. Get this error now:

Select all

C:\Documents and Settings\Administrator>imp 'SYS/system@PROD2 as sysdba' fil 
e=G:\producao_full.dmp log=G:\teste.log full=y 
 
Import: Release 9.2.0.6.0 - Production on Qua Mai 23 10:55:26 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
 
Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production 
With the Partitioning, OLAP and Oracle Data Mining options 
JServer Release 9.2.0.6.0 - Production 
 
Arquivo de exportaτπo criado por EXPORT:V09.02.00 via caminho convencional 
 
AdvertΩncia: os objetos foram exportados por SYSTEM; nπo por vocΩ 
 
importaτπo realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16 
IMP-00017: a instruτπo a seguir falhou com o erro 4068 ORACLE: 
 "BEGIN  SYS.DBMS_REPCAT_MIG.PRE_IMPORT; END;" 
IMP-00003: Erro Oracle: 4068 encontrado 
ORA-04068: estado atual dos pacotes  foi descartado 
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" contΘm erros 
ORA-06508: PL/SQL: nπo foi localizada a unidade de programa que estß sendo chama 
da 
ORA-06512: em "SYS.DBMS_REPCAT_MIG", line 116 
ORA-06512: em line 1 
IMP-00000: Importaτπo encerrada com Ωxito

I need to create something with the same name From the previous bank (production) ?? I already researched and nothing: |

[madmax]

Ramon,
this backup and valid?

Parace that SYS objects are with errors

Try the import with Y = Ignore, is that an option

] Another option and run the following scripts that you have in $ oracle_home / rdbms / admin
1) catreP.sql (removes related replication objects)
2) catrep.sql (creates replication objects related)
3) UTLRP.SQL (Invalid Recompile PL / SQL Modules).

See the links:
https://forums.oracle.com/forums/thread ... ID=4332603 http://www.dbasupport.com/forums/archiv ... 32513.html

I hope this helps
ABS.

[ramonmeloo2]

Dude, this problem I had solved .. it was bad ..

The problem q is giving in the import is that the default db_block_size is 4096 and the backup is 8192.

For this I have to create the bank manually, is it?

Dude, I'vê tried everything here and I can not get this import. I was able to create a bank 8192, but of the various errors at the time of import (non-existent user, language, etc.).

I have to have the list of all users before importing? Does not matter the users from the DMP?
Yes, my .dmp é valid ..

Another thing, as it error in this

Select all

F:\ORACLE\ORADATA\PRODUCAO\SIG_DADOS_01.DBF

is possible I only restore this in this bank ? Or will I really have to create a new BD and import everything? I can not import because the bank does not get open.

or I know it, make a copy of the bank's structure (picking the users, something like not to these import errors) .. ????

Thank you very much your help, thanks !!

[madmax]

Ramon,

has already attempted to increase the data_block_size with this command:

Select all

SQL>alter system set db_block_size=8k scope=spfile;

then try to import.

Face and easier to use the assent and make a new instance and create your bank in it, FAPOIS I make a straight backup plan that you will need.

Here my DMP and always by Schema and not full of the bank with this I can migrate from bank without problems.

ABS ..

[ramonmeloo2]

I'm going to try this guy .. because I had read that I could not change the size of the block after creating the bank .. Oh now it was giving this error: ORA-01552: Cannot Use System Rollback Segment for non-system tablespace '<My Tablespace > '

I'll try to create by the assistant and I'll come back here to talk .. thanks !!!

[ramonmeloo2]

And about this error, I saw some things about it. The tablespace undo was already created, even so it does not allow ..

I will create a bank by the wizard and try to change the size of the block.

[ramonmeloo2]

Dude, how do I import schema by schema ??

é using that touser and fromuser ??

type, when I put full, it recovers users and some other data .. but from the muito key error (does not insert as a certain table on account that is related to another, and this other I should have been imported before .. understood?) ..

and when I put fromuser and touser, he says soon that import was closed .. I need to use the fromuser / touser for all My tablespaces? Still a little confused with this .. a syntax like this is correct:

Select all

imp 'system/srhsig@PROD as sysdba' file=G:\producao_full.dmp log=imp.log fromuser=srhsig touser=srhsig  commit=y ignore=y

Is this ??

srhsig é my main schema .. but this soon a mistake (not specifies, error 1) .. and then for import .. it does not matter anything ..

When I put this:

Select all

imp 'system/srhsig@PROD as sysdba' file=G:\producao_full.dmp log=G:\teste.log full=y ignore=y

It takes a 2 hour p performing backup, but keeps these key mistakes (the errors that were giving It was at Schema SH). This schema is standard ??? He said that the key was violated (there showed sh.xxxfk ..) .. something like this ...

[ramonmeloo2]

Dude, the command used to hold backup was:


This is the file I have ... What complication to get kkkkkk ...

to create the bacno now by the assistant (database configuration assistant) .. it creates all the tablespaces that I need (the q had in the old bench), I have no problem with some users (mdsys, Ctxsys, etc.) .. but I can not import without errors .. complicated = \\

[ramonmeloo2]

Select all

C:\Documents and Settings\Administrator.SRH>imp 'system/system@PRODUCAO as sysdba' file=G:\producao_full.dmp log=imp.log fromuser=srhsig touser=srhsig buffer=50000000 commit=y ignore=y 
 
Import: Release 9.2.0.6.0 - Production on Qua Mai 30 10:17:18 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
 
Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production 
With the Partitioning, OLAP and Oracle Data Mining options 
JServer Release 9.2.0.6.0 - Production 
 
Arquivo de exportaτπo criado por EXPORT:V09.02.00 via caminho convencional 
 
AdvertΩncia: os objetos foram exportados por SYSTEM; não por você 
 
importaτπo realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16 
. importando objetos de SRHSIG para SRHSIG 
IMP-00009: fim anormal do arquivo de exportaτπo 
C:\Documents and Settings\Administrator.SRH>

porque is giving this errone: ??? ||||||||

[ramonmeloo2]

After that I try with User SYSTEM so, the same error continues:

Select all

C:\Documents and Settings\Administrator.SRH>imp file=g:\producao_full.dmp log=g:\imp.log fromuser=srhsig touser=srhsig buffer=50000000 commit=y ignore=y 
 
Import: Release 9.2.0.6.0 - Production on Qua Mai 30 10:31:01 2012 
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 
Nome do usußrio: SYSTEM 
Senha: 
 
Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production 
With the Partitioning, OLAP and Oracle Data Mining options 
JServer Release 9.2.0.6.0 - Production 
 
Arquivo de exportaτπo criado por EXPORT:V09.02.00 via caminho convencional 
importaτπo realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16 
. importando objetos de SRHSIG para SRHSIG 
IMP-00009: fim anormal do arquivo de exportaτπo 
C:\Documents and Settings\Administrator.SRH>

[ramonmeloo2]

Somebody????? : |

[madmax]

Ramon.

I'm sorry to tell you, but I think your DMP is invalid my friend.

If not a simple Imp Full he goes something wrong in this file.

So in the next use by Schema and not Full of the Bank.

ABS.

[ramonmeloo2]

é man, it seems that the dmp is corrupted even .. to see here how to solve .. thanks brother !!

[madmax]

Ramon

attempts to make the EXP thus

Select all

exp user/senha@BANCO grants=y log=C:\backups\USER.log file=C:\backups\user.DMP

then the imp Thus:


]


I hope I have helped.

ABS ..

[oraclewoman]

Hi Ramon, how are you?

managed to solve the problem?

OracleWoman