You Asked

Hi Team,
We plan to deploy 2 Oracle labs whose datafiles are resident on NFS (or NAS). And we met a strange situation about CREATING pfile/datafile to NFS(or NAS) on Windows Server:
1) on Windows, we failed to CREATE pfile(or tablespace) to NFS(or NAS), an error ORA-09201 (or ORA-01119) returned. I believe that the configuration of NFS(or NAS) is correctly, because we could exp(or spool) to NFS(or NAS) sucessfully.
2) on Linux, we could CREATE pfile(or tablespace) successfully, and so are exp(or spool), everything is alright.
Both the version of database is Oracle 12.2.0, is there any difference about CREATE command between Windows and Linux?
I've tried lower database version, the outcome are totally identical.

Here's my testment(let us take NFS as an example):
#for Windows Server 2008R2 (could not CREATE PFILE or CREATE TABLESPACE)

X:\>mount

Local    Remote                                 Properties
-------------------------------------------------------------------------------
x:       \\190.160.2.31\volume1\DATA            UID=0, GID=0
                                                rsize=131072, wsize=131072
                                                mount=soft, timeout=10.0
                                                retry=1, locking=yes
                                                fileaccess=755, lang=GB2312-80
                                                casesensitive=no
                                                sec=sys

X:\>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 9 09:39:04 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pfile='x:\pfile.ora' from spfile;
create pfile='x:\pfile.ora' from spfile
*
ERROR at line 1:
ORA-09210: sftopn: error opening file
OSD-04002: ╬?╖?┤≥┐?╬─╝■
O/S-Error: (OS 3) ╧╡═│╒╥▓╗╡╜╓╕╢?╡─┬╖╛╢íú


SQL> create tablespace tbs_test datafile 'x:\tbs_test01.dbf' size 1m;
create tablespace tbs_test datafile 'x:\tbs_test01.dbf' size 1m
*
ERROR at line 1:
ORA-01119: error in creating database file 'x:\tbs_test01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: ╬?╖?┤≥┐?╬─╝■
O/S-Error: (OS 3) ╧╡═│╒╥▓╗╡╜╓╕╢?╡─┬╖╛╢íú

--Well, it seems the NFS is not correctly configure, but this conclusion was defeated by the following testment:

SQL> spool x:\spool_log.txt
SQL> select count(*) from user_objects;

  COUNT(*)
----------
     51775

SQL> spool off;
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64
bit Production
X:\>exp SYSTEM/ORa23c92_123 file=x:\t_test.dmp tables=t_test

Export: Release 12.2.0.1.0 - Production on Fri Jun 9 09:46:33 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                         T_TEST          9 rows exported
Export terminated successfully without warnings.

X:\>dir
 Volume in drive X has no label.
 Volume Serial Number is 1221-969B

 Directory of X:\

2017/06/09  09:48    <DIR>          .
2017/06/09  09:48    <DIR>          ..
2017/06/08  15:09    <DIR>          #recycle
2017/06/09  09:07            67,584 T.DMP
2017/06/09  09:40               308 spool_log.txt            -- the *spool* doc
2017/06/09  09:46             4,096 t_test.dmp               -- the *exp* doc
               3 File(s)         72,336 bytes
               3 Dir(s)  10,746,468,270,080 bytes free

X:\>

-- I could finished the spool or export operation, thus I believe the NFS configuration is quite correctly.

#for Oracle Enterprise Linux 6.8 (successfully finished)

[oracle@mesdb ora]$ df -h
df: `/root/.gvfs': Permission denied
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_mesdb-LogVol03
                      264G   24G  227G  10% /
tmpfs                 868M  4.7M  863M   1% /dev/shm
/dev/sda1             190M   81M   96M  46% /boot
/dev/mapper/vg_mesdb-LogVol01
                      7.8G  194M  7.2G   3% /tmp
/dev/mapper/vg_mesdb-LogVol02
                       16G  249M   15G   2% /var
/dev/mapper/vg_data-lv_oradata
                      985G   65G  870G   7% /oradata
/dev/mapper/vg_data-lv_arch
                      197G   24G  164G  13% /archlog
/dev/mapper/vg_data-lv_backup
                      246G   60M  234G   1% /backup
/dev/sr0              3.8G  3.8G     0 100% /mnt
190.160.2.31:/volume1/DATA/
                       11T  710G  9.8T   7% /ora
[oracle@mesdb ora]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 9 09:53:46 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create tablespace tbs_test datafile '/ora/tbs_test01.dbf' size 1m;

Tablespace created.

SQL> create pfile='/ora/pfile.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@mesdb ora]$ ll
total 1120
-rwxrwxrwx 1 1024 users    1260 Jun  9  2017 pfile.ora           -------created sucessfully
drwxrwxrwx 1 root root      180 Jun  8 15:09 #recycle
-rwxrwxrwx 1 1024 users     308 Jun  9 09:40 spool_log.txt
-rw-r----- 1 1024 users 1056768 Jun  9  2017 tbs_test01.dbf      -------created sucessfully
-rwxrwxrwx 1 1027 users   67584 Jun  9 09:07 T.DMP
-rwxrwxrwx 1 1024 users    4096 Jun  9 09:46 t_test.dmp

Weired, isn't it?
Is it possible to use NFS (or NAS) on Windows as the carrier of datafiles? If we really want to use NFS (or NAS) on our lab.

and Connor said...

"O/S-Error: (OS 3)" is cannot find the file

C:\Users\hamcdc>net helpmsg 3

The system cannot find the path specified.

So the *database* cannot see X: drive. This is most likely a permissions issue - you need to see the user that the database *service* is using, and then ensure that user account has access to the X: drive.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joe Huang, June 09, 2017 - 3:01 am UTC

Thanks for your quick reply, Connor,
What makes me confused was that the *spool* command (inside sqlplus) and *exp* command (outside sqlplus) could finished sucessfully. But the *CREATE* failed.
As you said, I should try to check the permissions issue again and again.

June 11, 2017 - 6:56 am UTC

You run sqlldr, and you are in effect doing:

- connect to database
- database session gives data back to *your* sqlldr program, which is logged in as *you* and inherits *your* OS rights.

That's very different 'create pfile' which is

- connect to database
- database session *directly* wants to write the file

Problem Solved

Joe Huang, June 09, 2017 - 8:12 am UTC

For Windows NFS (or NAS), we should use FULL PATH rather than MAPPING TAG.
I mean in this case:
--WRONG WAY

SQL> create tablespace tbs_2 datafile '<b>X:</b>\tbs_2.dbf' size 1m;

--RIGHT WAY

SQL> create tablespace tbs_2 datafile '<b>//190.160.2.31/volume1/DATA</b>/tbs_2.dbf' size 1m;

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Problem Solved

More to Explore

Live SQL

Dev Gym

Failed to CREATE tablespace or pfile to NFS (or NAS who was believed correctly configured) on Windows

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Problem Solved

More to Explore

Live SQL

Dev Gym