Can't create datafile on slave server

[boraovali]

Hello,

We have dbvisit software which works on Oracle Database 11g R1 servers. When I create a new tablespace with datafiles on master server, Dbvisit on second server crashes. The log file at below, could you help me to solve the problem.

Thanks.

20100125 04:28:19 main::cmn_mail_logs-> mail_log: 201001251615 - ERROR at line 1:ORA-01119: error in creating database file 'a?'ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directory ADD_DATAFILE
Dbvisit terminated.
Return code = 433

[Arjen Visser]

Thank you for your question.

Is this new datafile in a new directory on the standby server? Dbvisit cannot create directories, it can only create the datafile.

If the directory does not exists, then create the directory and run Dbvisit again on the standby server.

As Dbvisit was not able to add the datafile, you may have to manually add the datafile on the standby database. To do this:

Run the following SQL on the standby database:
SQL> select name, file# from v$datafile where status = 'RECOVER';

This will give you the internal name of the datafile on the standby database.
Example, this may give you the name: /oracle/testdev/product/10.2.0.1/dbs/UNNAMED00012

Then you can rename the datafile to what you like with the following SQL:

SQL> alter database create datafile
'/oracle/testdev/product/10.2.0.1/dbs/UNNAMED00012'
as
'/oradata/testdev/dbs/ucsc_dev01.dbf';

Where /oradata/testdev/dbs/ucsc_dev01.dbf
is the file name for your standby database.

Note:
If the following statement does not return any rows:
SQL> select name, file# from v$datafile where status = 'RECOVER';

Then the rename SQL command is as follows:
SQL> alter database create datafile
'/oradata/testdev/dbs/ucsc_dev01.dbf'
as
'/oradata/testdev/dbs/ucsc_dev01.dbf';

Dbvisit can now be run again on the standby server to continue to update the standby database.

Please note:
Dbvisit has a current limitation that only one data file can be added per Dbvisit run. Add the following SQL to the standby database (for Oracle version 10g and higher) to ensure multiple datafiles can be added to the standby database:
SQL> alter system set STANDBY_FILE_MANAGEMENT = auto;

[boraovali]

Sorry for delayed answer. I fixed the problem with renaming the datafile name, however still we can't create datafile with correct name.

The folder where I create datafiles is our default location of all datafiles so I didn't create a new folder. I think Dbvisit may have permission problem about file creating on O/S level however all permissions looks fine.

[Arjen Visser]

Are you able to provide more details? Can you paste the output and the trace file here?

Thanks