s_radhak
asked on
ORA-03113 when creating database
When I tried installing Oracle 8i personal edition on Windows 2000, installation worked fine , but when it starts creating the database , I get the ORA-03113 End-of-file on communication channel error. The sample database is also not created.
When I tried installing Oracle 8i on Windows NT we did not have any problems.
Any help is appreciated.
Thank you.
When I tried installing Oracle 8i on Windows NT we did not have any problems.
Any help is appreciated.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am using Windows 2000 OS. I am not sure if it has Service pack 6. As far as I know it just has SP 1.
Service pack 1 for Windows 2000 server is also called SP6 for NT. This could also be the issue of not enough Memory!
Thanks
Thanks
Check the following page
http://www.microsoft.com/windows2000/downloads/recommended/sp1/default.asp
Here is what Oracle Says about this issue on Windows NT
"The Database Configuration Assistant does not ask for the internal password because it is not required. Then when the database configuration assistant runs oradim to create the service, it uses the syntax
D:\Oracle\Ora81\bin\oradim -new -sid SD03 -startmode manual -pfile
D:\Oracle\admin\sd03\pfile \init.ora Because the -intpwd parameter to create the internal password is left out, the password file is not created.
The database configuration assistant is then able to login as internal without a password, but the database creation fails with the ORA-3113 errors. The reason is that the init.ora file has remote_login_passwordfile= exclusive, requiring a password file that was not created.
To see if this is the problem, next time the error occurs do this:
Remove the user from the ORA_DBA group and then logoff and log back on. Then the database configuration assistant will prompt for a password and use it in the oradim statement or save the options to a script. Then before running the scripts to create the database, either remove remote_login_passwordfile= exclusive from the init.ora, or edit the batch file so that the oradim line has the -intpwd parameter:
D:\Oracle\Ora81\bin\oradim -new -sid SD03 -intpwd ORACLE -startmode manual -pfile D:\Oracle\admin\sd03\pfile \init.ora "
http://www.microsoft.com/windows2000/downloads/recommended/sp1/default.asp
Here is what Oracle Says about this issue on Windows NT
"The Database Configuration Assistant does not ask for the internal password because it is not required. Then when the database configuration assistant runs oradim to create the service, it uses the syntax
D:\Oracle\Ora81\bin\oradim
D:\Oracle\admin\sd03\pfile
The database configuration assistant is then able to login as internal without a password, but the database creation fails with the ORA-3113 errors. The reason is that the init.ora file has remote_login_passwordfile=
To see if this is the problem, next time the error occurs do this:
Remove the user from the ORA_DBA group and then logoff and log back on. Then the database configuration assistant will prompt for a password and use it in the oradim statement or save the options to a script. Then before running the scripts to create the database, either remove remote_login_passwordfile=
D:\Oracle\Ora81\bin\oradim
ASKER
I downloaded SP 1 for Windows 2000.
Then installed Oracle 8i. During installation, when it was initialising the database it came up with
ORA-12571: TNS packet writer failure.
I clicked on Ignore and the program was not responding atall.
It came up with a list saying
1. Net8 Config Assistant - Succeeded
2. Oracle Database Config Assistant - Failed
3. Starting Oracle HTTP Service - Succeeded
It says one or more tools have failed. It is recommended but not required that these tols succeed for this installation.
Then I went into Database Cinfiguration Assistant and tried to create a Database. When it is 90% complete it pops up with the error ORA-03113.
I even tried your suggestion about script file and batch file, but the problem here is I cannot even find the init.ora. It never gets created.
Thanks for all your help.
Then installed Oracle 8i. During installation, when it was initialising the database it came up with
ORA-12571: TNS packet writer failure.
I clicked on Ignore and the program was not responding atall.
It came up with a list saying
1. Net8 Config Assistant - Succeeded
2. Oracle Database Config Assistant - Failed
3. Starting Oracle HTTP Service - Succeeded
It says one or more tools have failed. It is recommended but not required that these tols succeed for this installation.
Then I went into Database Cinfiguration Assistant and tried to create a Database. When it is 90% complete it pops up with the error ORA-03113.
I even tried your suggestion about script file and batch file, but the problem here is I cannot even find the init.ora. It never gets created.
Thanks for all your help.
Let's bypass the database configuration agent. Following is how you create a database. Let me do more research on why database configuration agent.
Let's say you Installed Oracle Binaries in
d:/oracle/bin
and are creating a database named WINNER
Create the parameter file (initwinner.ora)
Create a new "initwinner.ora" for your new database and
Copy the "initwinner.ora" file to the following directory
under your $ORACLE_HOME (this is the default for your database)
(parameter file is an ascii file and if you cut and paste following
it could be used for your winner database)
d:\oracle\dbs\initwinner.o ra
---- cut here for sample init.ora parameter file -----
db_block_size = 8192
db_files = 1000
# Memory parameters
# Shared pool size should be, in general, equal to approximately 2%
# of the total physical memory.
shared_pool_size = 20971520 #SMALL (20M)
#shared_pool_size = 41943040 #MEDIUM (40M)
#shared_pool_size = 83886080 #LARGE (80M)
# The numbers of buffers should be approxiately equal to 2% of the
# total physical memory. The actual parameters value can be
# calculated as: (2% of physical memory) / db_block_size
db_block_buffers = 8500 #SMALL
#db_block_buffers = 5000 #MEDIUM
#db_block_buffers = 10000 #LARGE
hash_area_size = 1048576 #SMALL (8M)
sort_area_size = 1048576 #SMALL (1M)
optimizer_mode = choose
db_file_multiblock_read_co unt = 16
hash_multiblock_io_count = 8
sort_multiblock_read_count = 8
log_checkpoint_interval = 5000
dml_locks = 125
log_buffer = 512
open_cursors = 400
db_name = winner
control_files = (d:\oracle\winner\control1 .ora,
d:\oracle\winner\control2. ora)
rollback_segments = (r01,r02,r03,r04)
processes = 35
timed_statistics = true
global_names = false
nls_date_format="YYYY-MM-D D"
log_archive_start=true
log_archive_dest=d:\oracle \winner\ar ch
background_dump_dest=d:\or acle\winne r\bdump
user_dump_dest=d:\oracle\w inner\udum p
core_dump_dest=d:\oracle\w inner\cdum p
---- cut here ----
2. Append the "listener.ora" to contain your new database information.
V8.0:
d:\oracle\bin\net80\admin\ listener.o ra
in the SID_LIST_LISTENER section add:
(SID_DESC=(SID_NAME=winner ))
run lsnrctl (in d:\oracle\bin\lsnrctl.exe) and type reload at the > prompt
CREATING THE INSTANCE AND SERVICES 8.0
========================== ========== ==
1. Using the command line version of Instance Manager to create the new NT
Services.
a. Change to the "d:\oracle\bin" directory.
b. Type in the following command on one line:
ORADIM80 -new -sid winner -intpwd nothing -startmode AUTO
-pfile d:\oracle\dbs\initwinner.o ra
You can check for errors on "d:\oracle\RDBMS80\ORADIM8 0.LOG".
2. Verify that the services were created.
a. Open the Control Panel and double click on the Services icon.
b. Scroll down to the Oracle Services and verify that
"OracleServicewinner" and "OracleStartwinner" services exist, and
that the "OracleServicewinner" has been started.
c. Click on the Close button to exit the Services applications.
CREATING THE DATABASE 8.0
=========================
From a Command Prompt
3. Type in the following:
C:\>SET ORACLE_SID=winner
C:\>SVRMGR30
SVRMGR>CONNECT INTERNAL/ORACLE
You should get a message that you are "connected".
If not, you will need to delete the instance and start again.
To delete an instance, use the command line version of Instance
Manager (ex. ORADIM80 -delete -sid <new_sid>).
4. Startup the new instance by typing the following:
STARTUP NOMOUNT PFILE=d:\oracle\dbs\initwi nner.ora
You should get a message that the instance was STARTED.
5. Now you can run your CREATE DATABASE script.
create database "winner"
maxinstances 8
maxlogfiles 32
maxdatafiles 1022
character set "US7ASCII"
national character set "US7ASCII"
datafile
'd:\path\to\oradata\winner \system01. dbf' size 100M
logfile
'd:\path\to\oradata\winner \redo0101. log' size 50M,
'd:\path\to\oradata\winner \redo0201. log' size 50M,
'd:\path\to\oradata\winner \redo0301. log' size 50M;
6. Now run the following script to create temporary and rollback tablespaces, etc.
---cut here ----
spool winner_creation.log
---- The database should already be started up at this point with:
connect internal
create rollback segment r0 tablespace system
storage (initial 16k next 16k minextents 2 maxextents 20);
alter rollback segment r0 online;
create tablespace rbs datafile
'd:\path\to\oradata\winner \rbs01.dbf ' size 50M
default storage (
initial 50K
next 50K
pctincrease 0
minextents 2
);
create tablespace temp datafile
'd:\path\to\oradata\winner \temp01.db f' size 50M
default storage (
initial 2M
next 2M
pctincrease 0
);
create rollback segment r01 tablespace rbs;
create rollback segment r02 tablespace rbs;
create rollback segment r03 tablespace rbs;
create rollback segment r04 tablespace rbs;
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r0 offline;
drop rollback segment r0;
alter user sys temporary tablespace temp;
alter user system temporary tablespace temp;
@%ORACLE_HOME%\rdbms80\adm in\catalog .sql
@%ORACLE_HOME%\rdbms80\adm in\catproc .sql
spool off
----- cut here --------
7. From the same svrmgr prompt connect as the user SYSTEM password being MANAGER
@%ORACLE_HOME%\rdbms80\adm in\pupbld. sql
Once these scripts have finished running, your new database should be fully operational.
Let's say you Installed Oracle Binaries in
d:/oracle/bin
and are creating a database named WINNER
Create the parameter file (initwinner.ora)
Create a new "initwinner.ora" for your new database and
Copy the "initwinner.ora" file to the following directory
under your $ORACLE_HOME (this is the default for your database)
(parameter file is an ascii file and if you cut and paste following
it could be used for your winner database)
d:\oracle\dbs\initwinner.o
---- cut here for sample init.ora parameter file -----
db_block_size = 8192
db_files = 1000
# Memory parameters
# Shared pool size should be, in general, equal to approximately 2%
# of the total physical memory.
shared_pool_size = 20971520 #SMALL (20M)
#shared_pool_size = 41943040 #MEDIUM (40M)
#shared_pool_size = 83886080 #LARGE (80M)
# The numbers of buffers should be approxiately equal to 2% of the
# total physical memory. The actual parameters value can be
# calculated as: (2% of physical memory) / db_block_size
db_block_buffers = 8500 #SMALL
#db_block_buffers = 5000 #MEDIUM
#db_block_buffers = 10000 #LARGE
hash_area_size = 1048576 #SMALL (8M)
sort_area_size = 1048576 #SMALL (1M)
optimizer_mode = choose
db_file_multiblock_read_co
hash_multiblock_io_count = 8
sort_multiblock_read_count
log_checkpoint_interval = 5000
dml_locks = 125
log_buffer = 512
open_cursors = 400
db_name = winner
control_files = (d:\oracle\winner\control1
d:\oracle\winner\control2.
rollback_segments = (r01,r02,r03,r04)
processes = 35
timed_statistics = true
global_names = false
nls_date_format="YYYY-MM-D
log_archive_start=true
log_archive_dest=d:\oracle
background_dump_dest=d:\or
user_dump_dest=d:\oracle\w
core_dump_dest=d:\oracle\w
---- cut here ----
2. Append the "listener.ora" to contain your new database information.
V8.0:
d:\oracle\bin\net80\admin\
in the SID_LIST_LISTENER section add:
(SID_DESC=(SID_NAME=winner
run lsnrctl (in d:\oracle\bin\lsnrctl.exe)
CREATING THE INSTANCE AND SERVICES 8.0
==========================
1. Using the command line version of Instance Manager to create the new NT
Services.
a. Change to the "d:\oracle\bin" directory.
b. Type in the following command on one line:
ORADIM80 -new -sid winner -intpwd nothing -startmode AUTO
-pfile d:\oracle\dbs\initwinner.o
You can check for errors on "d:\oracle\RDBMS80\ORADIM8
2. Verify that the services were created.
a. Open the Control Panel and double click on the Services icon.
b. Scroll down to the Oracle Services and verify that
"OracleServicewinner" and "OracleStartwinner" services exist, and
that the "OracleServicewinner" has been started.
c. Click on the Close button to exit the Services applications.
CREATING THE DATABASE 8.0
=========================
From a Command Prompt
3. Type in the following:
C:\>SET ORACLE_SID=winner
C:\>SVRMGR30
SVRMGR>CONNECT INTERNAL/ORACLE
You should get a message that you are "connected".
If not, you will need to delete the instance and start again.
To delete an instance, use the command line version of Instance
Manager (ex. ORADIM80 -delete -sid <new_sid>).
4. Startup the new instance by typing the following:
STARTUP NOMOUNT PFILE=d:\oracle\dbs\initwi
You should get a message that the instance was STARTED.
5. Now you can run your CREATE DATABASE script.
create database "winner"
maxinstances 8
maxlogfiles 32
maxdatafiles 1022
character set "US7ASCII"
national character set "US7ASCII"
datafile
'd:\path\to\oradata\winner
logfile
'd:\path\to\oradata\winner
'd:\path\to\oradata\winner
'd:\path\to\oradata\winner
6. Now run the following script to create temporary and rollback tablespaces, etc.
---cut here ----
spool winner_creation.log
---- The database should already be started up at this point with:
connect internal
create rollback segment r0 tablespace system
storage (initial 16k next 16k minextents 2 maxextents 20);
alter rollback segment r0 online;
create tablespace rbs datafile
'd:\path\to\oradata\winner
default storage (
initial 50K
next 50K
pctincrease 0
minextents 2
);
create tablespace temp datafile
'd:\path\to\oradata\winner
default storage (
initial 2M
next 2M
pctincrease 0
);
create rollback segment r01 tablespace rbs;
create rollback segment r02 tablespace rbs;
create rollback segment r03 tablespace rbs;
create rollback segment r04 tablespace rbs;
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r0 offline;
drop rollback segment r0;
alter user sys temporary tablespace temp;
alter user system temporary tablespace temp;
@%ORACLE_HOME%\rdbms80\adm
@%ORACLE_HOME%\rdbms80\adm
spool off
----- cut here --------
7. From the same svrmgr prompt connect as the user SYSTEM password being MANAGER
@%ORACLE_HOME%\rdbms80\adm
Once these scripts have finished running, your new database should be fully operational.
BTW. the init.ora file will be inityourdatabasename.ora
yourdatabasename's default is ORCL so check fro initorcl.ora
yourdatabasename's default is ORCL so check fro initorcl.ora
ASKER
Hi,
I tried your suggestion, but could not complete the process 'cos some of the utilities were not installed properly. We have finally installed Oracle on the server and everything seems to work fine now. We use the client version of Oracle on our m/c.
Thanks for the help.
I tried your suggestion, but could not complete the process 'cos some of the utilities were not installed properly. We have finally installed Oracle on the server and everything seems to work fine now. We use the client version of Oracle on our m/c.
Thanks for the help.
Oracle Enterprise Manager release 2.1 is not supported on Windows 2000. After certification is complete, a patch, if necessary, will be released that includes Windows 2000 support.