Home » RDBMS Server » Networking and Gateways » Not able to create tablespace (oracle 9.2.0.6.0 - red hat linux 4. 0)
Not able to create tablespace [message #296336] Fri, 25 January 2008 20:30 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Sir / Madam,

we have created one database...and it is getting connected well. Further we need to create tablespaces accdly. When we try to create the same we are getting the below error :


SQL>
SQL> CREATE TABLESPACE SA_DATA
2 DATAFILE
3 '/PROD_dbmain/database/SAPRD/data/SA_DATA_01.dbf' SIZE 6291456 K reuse ,
4 '/PROD_dbmain/database/SAPRD/data/SA_DATA_02.dbf' SIZE 6291456 K reuse,
5 '/PROD_dbdata/database/SAPRD/data/SA_DATA_03.dbf' SIZE 6291456 K ,
6 '/PROD_dbmain/database/SAPRD/data/SA_DATA_03.dbf' SIZE 2097152 K
7 LOGGING
8 ONLINE
9 PERMANENT
10 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256 K;
CREATE TABLESPACE SA_DATA
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel



when we checked in google further, it seems there are many reasons for this ORA error:


For example, ORA-3113 could be signaled for any of these scenarios:

for e.g.
- Server machine crashed
- Your server process was killed at O/S level
- Network problems
- Oracle internal errors / aborts on the server
- Client incorrectly handling multiple connections
- etc.. etc.. etc.. - a lot of possible causes !!

Now we need to further create the required tablespaces on urgent
basis and release the database to users.
Kindly cfm how to proceed further and adv us the area to be checked immediately.

Tks for your prompt reply.

rgds,
kesavan.
Re: Not able to create tablespace [message #296337 is a reply to message #296336] Fri, 25 January 2008 20:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Usually a trace file is created from an ORA-03113 failure.
It might/should contain additional clues.
Re: Not able to create tablespace [message #296346 is a reply to message #296336] Fri, 25 January 2008 23:47 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member


Kesavan,

It will be appreciated, if you can upload trace contents pertaining to error along with OS Version & Oracle version plus any other information which you think may help forum members in order to understand your situation in a better way.

Regards

Manjit Kumar [mkbhati]
Re: Not able to create tablespace [message #296408 is a reply to message #296336] Sat, 26 January 2008 11:55 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL> CREATE TABLESPACE SA_DATA
2 DATAFILE
3 '/PROD_dbmain/database/SAPRD/data/SA_DATA_01.dbf' SIZE 6291456 K reuse ,
4 '/PROD_dbmain/database/SAPRD/data/SA_DATA_02.dbf' SIZE 6291456 K reuse,
....

Are you reusing any existing datafile?
Re: Not able to create tablespace [message #296410 is a reply to message #296408] Sat, 26 January 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are you reusing any existing datafile?

It does not matter. You can use "reuse" even if the file does not exist.

Regards
Michel
Re: Not able to create tablespace [message #296452 is a reply to message #296346] Sun, 27 January 2008 10:00 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear sir,
i have attached my alert log file...and pls ref below details
oracle version: 9.2.0.0.0

o/s version:
Linux saprod 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux
***content from alert log file ***

create tablespace bsp_data
datafile '/PROD_dbdata/database/SAPRD/data/bsp_data01.dbf' size 1548288 k,
'/PROD_dbdata/database/SAPRD/data/BSP_DATA_02.DBF' SIZE 2097152 K online permanent
extent management local uniform size 64 k
Sun Jan 27 19:59:02 2008
Errors in file /PROD_dbsys/database/SAPRD/trace/bdump/saprd_pmon_6634.trc:
ORA-00471: DBWR process terminated with error
Sun Jan 27 19:59:02 2008
PMON: terminating instance due to error 471
Instance terminated by PMON, pid = 6634

*************************************

***********content of trace file************
[oracle9@saprod bdump]$ more saprd_pmon_6634.trc
/PROD_dbsys/database/SAPRD/trace/bdump/saprd_pmon_6634.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /oracle/oracle9/product/9.2.0
System name: Linux
Node name: saprd
Release: 2.6.9-42.ELsmp
Version: #1 SMP Wed Jul 12 23:27:17 EDT 2006
Machine: i686
Instance name: SAPRD
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 6634, image: oracle@saprd (PMON)

*** 2008-01-27 19:59:02.790
*** SESSION ID:(1.1) 2008-01-27 19:59:02.259
error 471 detected in background process
ORA-00471: DBWR process terminated with error
[oracle9@saprod bdump]$

**********************************************


****my init file content*****
[oracle9@saprod dbs]$ more initSAPRD.ora
*.background_dump_dest='/PROD_dbsys/database/SAPRD/trace/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/PROD_dbsys/database/SAPRD/ctrl/control01.ctl','/PROD_dbsys/database/SAPRD/ctrl/control02.ctl','/PROD_dbsys/
database/SAPRD/ctrl/control03.ctl'
*.core_dump_dest='/PROD_dbsys/database/SAPRD/trace/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SAPRD'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SAPRDXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='SAPRD'
*.java_pool_size=83886080
*.large_pool_size=8388608
#*.log_archive_dest='/PROD_arch/database/SAPRD/archive'
#*.log_archive_format='ARC_SAPRD%T_%S.arc'
#*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/PROD_dbsys/database/SAPRD/trace/udump'
*.utl_file_dir='*'
************************************

kindly help me on urgent basis.

rgds,
kesavan




Re: Not able to create tablespace [message #296457 is a reply to message #296452] Sun, 27 January 2008 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should be interesting is DBWR trace file.

Regards
Michel
Re: Not able to create tablespace [message #296576 is a reply to message #296336] Mon, 28 January 2008 05:33 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Kesawan thanks for uploading traces.Please check following :

(1) Have you set correct kernel parameters before creating database.
(2) Sufficient disk space / Quota is available as requested.
(3) You are not receiving any file system / memory related OS errors.
(4) If you can upload DBWR & LGWR traces containing errors.

There can be multiple reason for errors you have been receiving.

Regards

manjit Kumar [mkbhati]

Re: Not able to create tablespace [message #296635 is a reply to message #296576] Mon, 28 January 2008 08:26 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Manoj,

background processes are getting killed it seems, due to "out of memory".

we are not able to see the DBWR and LGWR trace files also...
only below file is getting created.

---------------------------------
[oracle9@saprod bdump]$ ls -ltr
total 28
-rw-r----- 1 oracle9 dba 681 Jan 28 19:39 saprd_pmon_12496.trc
-rw-r--r-- 1 oracle9 dba 23062 Jan 28 19:49 alert_SAPRD.log
[oracle9@saprod bdump]$ more saprd_pmon_12496.trc
/PROD_dbsys/database/SAPRD/trace/bdump/saprd_pmon_12496.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /oracle/oracle9/product/9.2.0
System name: Linux
Node name: saprod
Release: 2.6.9-42.ELsmp
Version: #1 SMP Wed Jul 12 23:27:17 EDT 2006
Machine: i686
Instance name: SAPRD
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 12496, image: oracle@saprod (PMON)

*** 2008-01-28 19:39:35.309
*** SESSION ID:(1.1) 2008-01-28 19:39:35.302
error 470 detected in background process
ORA-00470: LGWR process terminated with error
[oracle9@saprod bdump]$

-----------------------------------

one more thing,
if we create any tablespace in partition: /oracle/oracle9...then it is getting created...
if we tried to create any other partition, it is giving subject error.

fyi,
[oracle9@saprod oracle]$ ls -ltr
total 20
drwxrwxrwx 26 oracle9 dba 4096 Jan 28 19:57 oracle9
drwx------ 2 root root 16384 Jan 28 2008 lost+found

# cd /oracle/oracle9
# ls -ltr
drwxrwxrwx 7 oracle9 dba 4096 Mar 9 2004 Disk1
drwxrwxrwx 3 oracle9 dba 4096 Mar 9 2004 Disk2
drwxrwxrwx 3 oracle9 dba 4096 Mar 9 2004 Disk3
drwxrwxrwx 2 oracle9 dba 4096 May 10 2004 3006854
drwxrwxrwx 2 oracle9 dba 4096 Feb 23 2005 4198954
-r-xr-xr-x 1 oracle9 dba 10203 Dec 12 2005 libaio-devel-0.3.105-2.i386.rpm
-rwxrwxrwx 1 oracle9 dba 659577856 Jan 28 17:52 ship_9204_linux_disk1.cpio
-rwxrwxrwx 1 oracle9 dba 646914048 Jan 28 17:58 ship_9204_linux_disk2.cpio
-rwxrwxrwx 1 oracle9 dba 299722752 Jan 28 17:58 ship_9204_linux_disk3.cpio
-rwxrwxrwx 1 oracle9 dba 1670 Jan 28 18:01 p3006854_9204_LINUX.zip
-rwxrwxrwx 1 oracle9 dba 5802 Jan 28 18:01 p4198954_21_LINUX.zip
-rwxrwxrwx 1 oracle9 dba 191 Jan 28 18:05 bash_profile.orig
-rwxrwxrwx 1 oracle9 dba 735 Jan 28 18:07 bp
drwxrwxrwx 2 oracle9 dba 4096 Jan 28 18:17 Desktop
drwxrwxr-x 3 oracle9 dba 4096 Jan 28 18:33 product
drwxrwxr-x 6 oracle9 dba 4096 Jan 28 18:34 oui
drwxrwxr-x 4 oracle9 dba 4096 Jan 28 18:34 jre
drwxrwxr-x 6 oracle9 dba 4096 Jan 28 18:34 doc
drwxrwxr-x 12 oracle9 dba 4096 Jan 28 18:39 oraInventory
drwxr-xr-x 3 oracle9 dba 4096 Jan 28 18:42 admin
-rw-r--r-- 1 oracle9 dba 1090 Jan 28 18:45 initICPRD.ora
-rw-r----- 1 oracle9 dba 2147491840 Jan 28 19:51 ideals_data_01.dbf
-rw-r----- 1 oracle9 dba 4294975488 Jan 28 19:56 SA_DATA_01.dbf
[oracle9@saprod ~]$ pwd
/oracle/oracle9


i will provide my sysctl.conf content
--------------------------------------
kernel.core_uses_pid = 1
kernel.shmmax = 1073741824
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.sem = 5010 641280 5010 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000


my .bash_profile content
------------------------
-bash-3.00$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
umask 022
PATH=$PATH:$HOME/bin

export PATH
ORACLE_BASE=/home/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=ICPRD; export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib; export CLASSPATH

LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
-bash-3.00$

------------------

pls adv further....
thanks for your time with us.

Rgds,
kesavan.
Re: Not able to create tablespace [message #296642 is a reply to message #296635] Mon, 28 January 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 27 January 2008 18:26
What should be interesting is DBWR trace file.

And now LGWR.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Not able to create tablespace [message #296651 is a reply to message #296576] Mon, 28 January 2008 09:10 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear sir,

iam providing the answers for your questions:

(1) Have you set correct kernel parameters before creating database.
ans: sir, i have provided my sysctl.conf content, pls adv.

(2) Sufficient disk space / Quota is available as requested.
ans: there are enough spcae in the disk... 70gb
(3) You are not receiving any file system / memory related OS errors.
ans: no, we did not receive any abv kind of errors.
(4) If you can upload DBWR & LGWR traces containing errors.
in bdump folder, dbwr & lgwr trace files are not getting generated..only pmon files are available

pls adv.

rgds,
kesavan.
Re: Not able to create tablespace [message #296656 is a reply to message #296651] Mon, 28 January 2008 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you don't have the useful information to know what happened.

Regards
Michel
Re: Not able to create tablespace [message #296670 is a reply to message #296336] Mon, 28 January 2008 11:00 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
Are you able to do any DDL/DMLs in the database?
Previous Topic: Heterogeneous Database connections Oracle, SQL Server 2005 and MS Access
Next Topic: No Listener
Goto Forum:
  


Current Time: Fri Mar 29 05:36:51 CDT 2024