-
hi friends,
when starting my sql plus, oracle gives an error message
ORA-01092: ORACLE instance terminated. Disconnection forced. When i tried starting the database from svrmgrl, it tries starting the database but gave an end-of-communication channel error message. what is the solution ?
satishpc
-
-
oh, so sorry for that. should have specified earlier. it is oracle 8i enterprise edition running on NT
-
you have to stop the database from services and start from services, then if the database is still not up try again from svrmgrl.
I think you are experiencing problems with SGA, are you using MTS?
Is it 8i?
-
hi
i tried both throught the services and svrmgrl. but it doesnt work. when starting from svrmgrl, it give the end-of-communication file problem. will reducing the SGA help ? it is oracle 8i. i am not using MTS
waiting for ur reply
satish
-
check your alert.log see if the instance was initiated at all. I said SGA because when I faced this problem in NT was because large pool was full but this was because I was using MTS and my large pool size were too small.
When I start the db from svrmgrl I didnt get the error message you got, instead it simply says oracle not available and when i tried to shut it down it just hangs even with abort option however these shutdown and process were still recorded in alert.log I fixed it by shutting the db from services and start from either svrmgrl or services.
I cant think of anything since our situation might be different
-
HI,
try to decrease the size of SGA and normally check alert.log in the background_dump_dest.
-
hi,
here is my alert file. i think there might be a bad block. please interpret for me. this was working fine till yesterday.
i dont even know whether i got the right part. if anyone needs any more info, please feel free to ask. thanks .
satish
here it is
Tue Dec 12 20:02:10 2000
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Dec 12 20:02:11 2000
alter database mount
***
Corrupt block relative dba: 0x00000003 file=0. blocknum=3.
Bad check value found during controlfile block read
Data in bad block - type:21. format:2. rdba:0x00000003
last change scn:0xffff.00001790 seq:0x1 flg:0x04
consistancy value in tail 0x17901501
check value in block header: 0x7b75, calculated check value: 0x2
spare1:0x0, spare2:0x0, spare2:0x0
LGWR: terminating instance due to error 227
Instance terminated by LGWR, pid = 275
Tue Dec 12 20:07:08 2000
Starting ORACLE instance (force)
License high water mark = 5
Tue Dec 12 20:12:08 2000
Instance terminated by USER, pid = 105
Starting up ORACLE RDBMS Version: 8.1.6.0.0.
System parameters with non-default values:
processes = 59
shared_pool_size = 5728640
large_pool_size = 614400
java_pool_size = 20971520
control_files = D:\Oracle\oradata\finsati\control01.ctl, D:\Oracle\oradata\finsati\control03.ctl, D:\Oracle\oradata\finsati\control02.ctl
db_block_buffers = 2048
db_block_size = 8192
compatible = 8.1.0
log_archive_dest_1 = LOCATION=D:\Oracle\Ora81\RDBMS
log_buffer = 32768
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
db_files = 1024
db_file_multiblock_read_count= 8
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
global_names = TRUE
distributed_transactions = 500
instance_name = finsati
service_names = finsati
mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
open_links = 4
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = finsati
open_cursors = 100
os_authent_prefix =
query_rewrite_enabled = TRUE
job_queue_processes = 4
job_queue_interval = 10
parallel_max_servers = 5
background_dump_dest = D:\Oracle\admin\finsati\bdump
user_dump_dest = D:\Oracle\admin\finsati\udump
max_dump_file_size = 10240
oracle_trace_collection_name=
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
SNP0 started with pid=8
SNP1 started with pid=9
SNP2 started with pid=10
SNP3 started with pid=11
Tue Dec 12 20:12:12 2000
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
-
It seems you need to recreate the corrupted control file.
-
hi,
i am a bit new to this field. in fact i am just a makeshift DBA. can anyone please tell me how do i solve this problem. if i need to recreate this control file, how do i do it. can anyone give me a detailed explanation. i would surely appreciate any help.
satish
-
Well to start with you are using MTS since there are dispatchers and shared servers.
Secondly the controlfile must be multiplexed, find the other copy and copy it on the one with bad block (overwrite it), may be it´s better make a copy of the corrupted controlfile just in case.
If you dont have any copy you will have to recreate the controlfile manually. Who is the DBA there? Or the guy who was in charge of it, he should has a script generated bwith command
alter database backup controlfile to trace;
and with the script you can recreate the cntrolfile easily. If he hasnt got one then you will have to create it manually which is a bit trickier
-
hi guys,
i am the most Knowledgeable person in my co. as far as oracle is coincerned. and this is my first brush with such a problem. sad but true. i tried searching for backup files but i didnt find one. that might be because i didnt backup any cntrl file. can u give me any source where i can find out the prodedure to create one. or is it simply better to reinstall oracle. i have some data and will be losing only some of it. i will need a day or two to process that data. what is better please advice. thanks
satish
-
check your initSID.ora and look for parameter
control_files
This tells if your controlfile are multiplexed and where they are.If you have only one then create manually
It looks something like
CREATE CONTROLFILE REUSE DATABASE "DB_NAME_HERE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 50
MAXINSTANCES 1
MAXLOGHISTORY 112
LOGFILE
GROUP 1 (
'/oracle/oratest/redolog/rdoexp1a.log',
'/oracle/oratest/redolog/rdoexp1b.log'
) SIZE 10M,
GROUP 2 (
'/oracle/oratest/backup/rdoexp2a.log',
'/oracle/oratest/backup/rdoexp2b.log'
) SIZE 10M
DATAFILE
'/oracle/oratest/data/system.dbf',
'/oracle/oratest/data/data1.dbf',
'/oracle/oratest/data/temp.dbf',
'/oracle/oratest/data/rbs.dbf';
where datafiles are all the datafiles in your database with exact PATH, same with LOGFILES
-
hi,
here is what my init.ora says
control_files = "D:\Oracle\oradata\finsati\control01.ctl"
control_files = "D:\Oracle\oradata\finsati\control03.ctl"
control_files = "D:\Oracle\oradata\finsati\control02.ctl"
are these all files copy of each other or are they all different files. do i need to create all or any one particular control file ?
satish
-
I dont know which controlfile is corrupted so I suggest you before procede backup all three control files in other disk or directory.
Now I think we have to guess which file is corrupt, start with the first control file, delete it and substitute it with second one, after substitution rename it so it´s name is same as first control file. Start the database if you get same error this means that the second control file is corrupted. Just change the corrupted one with the good one and rename them respectively.
-
Take a full backup including control files, redo log files, data files, init.ora, config.ora files.
If control01 is corrupted, delete control01 then copy control02 to control01.
Then startup the instance.
-
hi pando,
tried that as well but it doesnt work. think i will go home and start fresh tomorrow morn. will get back to u with the results tom. bye and thanks a lot for your help.
satishpc
-
if that still doesnt work I dunno what can be wrong :o
Are you sure you have tried with all three control files? You must be very very unlucky to corrupt all of them! But then again since they are in same directory may be they share same OS Blocks....
-
for future
(Normally every DBA after that kind of failure considers at least 3 copies of control files on 3 different (physical) disks.)
Today I also had hard disk failure of developers DB but was lucky because it was not permanent - I managed to copy data files from this corrupt disk to another disk and after did what you was already advised (edit init.ora control_files to point to new location and 'alter database rename file ...') DB worked again. So just try this as well: NT will check CRC for you.
-
Use the create control file option. Pls see the Oracle ref. guide for more details.
-
hi everybody
now I have 2 copies of control files on same disk,suppose I add one in init.ora,then does that automatically gets created during db start or I will be placing a copy in the new location and then restart db.
all your suggestions helped alot,and regarding mts ,can anybody say is it recommended not to use mts after oracle 7.3 and if so why???
-
hi friends,
i tried to give the create controfile option in svrmgrl. but it says oracle not connected. i tried connecting as internal, sys as sysdba, etc. but the same problem persists. documentation says i need OSDBA granted, but how do i do it ?
satishpc
-
Hi,
try:
set oracle_sid='your sid'
svrmgrl
svrmgr>connect internal/'your pass'
Connected.
svrmgr>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
svrmgr>startup mount
Database mounted.
svrmgr>alter dabatase backup controlfile to 'your backup path'
Statement processed.
then the rest it's easy.
hope it helps
-
hi,
that is a problem. when i say startup mount or startup with anything else, it shows me all the buffer size, sga etc but after redo buffers, it shows me this error
ORA-03113: end-of-file on commumnication channel
and after that , if i give any command , it gives me the error
not connected to oracle
what can i do ? this is getting more & nore interesting . i think i will learn a lot thanks to u guys.
satish
-
did you try with all control files at all to see which one is corrupted?
If all of them are corrupted your only choice will be start the instance (startup nomount) and recreate the control file
-
hi,
yes i did try with all three control files. but none of them works. all three of them are of same size. does it mean all three are corrupt ? i tried recreating but that is the problem. when i connect to svrmgrl and say create control file, it gives me an error saying oracle not connected. what do i do ?
satishpc
-
did you issue startup nomount
or
you issued startup?
Of course they are same size, they are mirrored copies to prevent loss of any of them. Like your case (it would be very helpful if you have one control file that works isnt it :D )
You have to issue startup nomount in order to recreate a control file.
-
hi ,
tried that. works fine. great. now i am trying to create the control file. my old control files didnt work. i tried. besied, being of the same size, they are also last modified at the same time. what does that mean ? anyway, will write back as soon as i create the control file. thanks a lot.
satish
-
may use NT comp command to compare control files: they should be the same.
how it happened control files get lost? Are you sure now that your HW/OS is OK? I saw a case how control files where lost because of disk controler problem. Then HW problem had to be fixed first before proceeding.
-
it´s not lost, corruption of blocks.
Of course control files have same modified time, they are mirrored copies. Mirror means clone, if you look yourself at the mirror you see another you.... hope this helps
-
hi,
i gave the foll command
CREATE CONTROLFILE REUSE DATABASE FINSATI NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 50
MAXINSTANCES 1
MAXLOGHISTORY 112
datafile
'D:\Oracle\oradata\finsati\temp01.dbf' ,
'D:\Oracle\oradata\finsati\rbs01.dbf',
'D:\Oracle\oradata\finsati\indx01.dbf',
'D:\Oracle\oradata\finsati\tools01.dbf',
'D:\Oracle\oradata\finsati\dr01.dbf',
'D:\Oracle\oradata\finsati\System01.dbf',
'D:\Oracle\oradata\finsati\users01.dbf'
logfile 'D:\Oracle\oradata\finsati\redo01.log' size 1M,
'D:\Oracle\oradata\finsati\redo02.log' size 1M,
'D:\Oracle\oradata\finsati\redo03.log' size 1M resetlogs
it says control file created. now do i start the instance using
startup force
or should it be something else ? waiting for ur reply.
thanks a lot.
satish
-
There is smth wrong with your syntaxis, it should looks smth like follows
CREATE CONTROLFILE REUSE DATABASE FINSATI NORESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 50
MAXINSTANCES 1
MAXLOGHISTORY 112
datafile
'D:\Oracle\oradata\finsati\temp01.dbf' ,
'D:\Oracle\oradata\finsati\rbs01.dbf',
'D:\Oracle\oradata\finsati\indx01.dbf',
'D:\Oracle\oradata\finsati\tools01.dbf',
'D:\Oracle\oradata\finsati\dr01.dbf',
'D:\Oracle\oradata\finsati\System01.dbf',
'D:\Oracle\oradata\finsati\users01.dbf'
logfile
'D:\Oracle\oradata\finsati\redo01.log' size 1M,
'D:\Oracle\oradata\finsati\redo02.log' size 1M,
'D:\Oracle\oradata\finsati\redo03.log' size 1M;
after this is done
alter database open;
-
hi,
when i say alter database open, i get the foll error
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\FINSATI\SYSTEM01.DBF'
what do i do ?
satish
-
ops, try this although I am not sure if it would work
shutdown the database
startup mount
recover database;
alter database open;
are you in archive log or no archive log
-
hi pando,
thanks a lot. it works great. a few more questions to triuble u. i had a few tablespaces which had 2-3 more datafiles. i can add these tablespaces and datafiles now, right ? second, what diff does it make whether i am in archive or nonarchive mode ? thanks a lot for u r help. i did learn a lot today.
satish
-
hm what do you mean you have a few more files to add...? You didnt include all datafiles in your create control file statement? Well it is too late now to add because the file headers have been updated with new SCNs. You will get errors if you wanna add.
Archive log allows you to recover until point of failure (as long as you keep the archived logs)
With no archive log you can only recover until last cold backup, if your last cold backup is from 1 week ago then you would lose 1 week work.
-
hi ,
i had mentioned all the datafiles but it seems there was a datafile in d:\oracle\ora81\database. but i couldnt fond any datafile there. now in the dba studo, it is showing me that this datafile is missing but i am not getting the name of the file. just the path. whenever i am querying most of my tables, i am getting this error
ORA-00376: file 9 cannot be read at this time
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: 'D:\ORACLE\ORA81\DATABASE\MISSING00009'
what is the way out ? can i delete this datafile ?
satishpc
-
Well I think this is a bit trouble. You said most of your tables cant be queried then seems like it´s the main datafile that is missing and you said it´s not anywhere, so it was deleted accidently? If it was deleted then you will have to do a recovery, it can be a few situations, I doubt I can help remotely.
Do you have backups at all?
Your company should hire a DBA, really. Oracle is pretty complex since it offers so many options, features, and depending on situation you do a specific thing.
If the boss doesnt want to hire a DBA I would suggest using SQL Server.
-
hi pando,
thanks a lot. i guess i will manage from here onwards. actually my co. wants me to be the dba so i gues i woill have to start studying very hard. :D . i did learn a lot as this is the first time my comapny has encountered a serious database problem. earlier, they simply used to reinstall oracle and import the dump. but i guess i will have to put systems in place. for the time being my database working properly(hopefully). i will just have to import a few tables and process them.can u just tell me how to delete that datafile. right now it is offline. thanks once again. bye and have a nice time.
satishpc
-
try this
shutdown the database
startup mount
alter database datafile 'MISSINGwhatever' offline drop;
select * from v$datafile;
check the status column, see if it´s offline
alter database open;
drop tablespace XXXXX including contents;
where XXXX the tablespace that owns that datafile.