Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problems writing to directories in Oracle 21c Windows

user8005280Oct 12 2021

It looks like, from my perspective, that there is a bug with directories on Oracle 21c on Windows.
This new database version was just released in the last few days.
I am upgrading from Oracle 19c.
Here is the test case. This is just a simple block of PL/SQL code. It opens a text file and writes the current date and time into the file. MYDIRECTORY is any directory created with the CREATE DIRECTORY statement. If you run this block of code more than once, it will simply overwrite the file and create it again. So each time you run the block of code, you get a new timestamp in your file.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('MYDIRECTORY', 'test.txt', 'w');
  utl_file.put_line(f, to_char(sysdate, 'YYYY.MM.HH24.MI.SS'));
  utl_file.fclose(f);
end;
/

Now the above block of code either works or doesn't work, depending on where you create your directory.
On Oracle 21c you must create both a container database as well as a pluggable database. There is no option for stand-alone database as with Oracle 19c and prior versions.
I have run all my tests on the container database (not on the plug), and running SYS AS SYSDBA.
If you create the directory on the C: (where the Oracle base is installed), then there is no problem. The above block of code will run without error.
If you create the directory ON ANY OTHER DRIVE IN YOUR SYSTEM, the above block of code will fail.
So here is what happens when it works... Logged into the container SYS AS SYSDBA.
First, in the operating system, while logged in as the primary administrative user, create the folder on the C drive. From the command prompt, at the root...
image.pngThen connecting SYS AS SYSDBA, we will create the directory
image.pngdrop directory MYDIRECTORY;
create or replace directory MYDIRECTORY as 'C:\AAA\';
grant read, write on directory MYDIRECTORY to SYS;
grant read, write on directory MYDIRECTORY to IMP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to EXP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to DATAPUMP_EXP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to DATAPUMP_IMP_FULL_DATABASE;
There is no problem with any of that. Note that a couple of those grants are redundant. I just want to make sure that SYS definitely has the right to write to that directory.
Now we run our test block. No errors.
image.pngWe check for the file in the folder.
image.pngThe file is present and there is no error.
Now we try to do the same thing ON ANY OTHER DRIVE IN THE SYSTEM.
I have tested this on two different SATA drives, as well as an NMVE raid array, as well as a thumb drive. It fails on all of them. Here I am going through the thumb drive so that other people can easily replicate the test. First I format the thumb drive. It ends up as drive D:
image.pngimage.pngClearly there is nothing special about this disk. It has just been formatted. It should have all default properties. I create the folder as before.
image.pngFrom Windows explorer, I double check permission on the folder. "Everyone" has full rights. There should be no trouble writing to this disk.
image.pngNow, as before, while logged in SYS AS SYSDBA, drop and re-create the directory. Now using drive D although everything else is the same.
image.pngFinally run my test code.
image.pngAnd that will be the same error regardless of what drive you run that on. Any other drive other than C: will fail. Permission don't matter. You can grant everyone full write privileges, it won't matter. You can assign ownership of the drive root as well as the AAA subfolder, to the same user as the database was installed as, and it won't matter. Nothing you do will fix it. The directory simply will not write. I have tried dozens of variations.
This problem did not occur on Oracle 19c.
Any help is appreciated. Thank you,

Comments

KanchDev
Hi Jumbo,

You can consider an incremental rman backup from production database and recover DR database.

This method is available from 10g. otherwise you need to recreate the Standby database.


Cheers,
Kanchana.
CKPT
If the DR is not functioning from last 6 months, According to database size you can rebuild standby database.

if the data difference is not so huge, You can perform incremental roll forward.

For 10g, refer this below link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC
Shivananda Rao
As said, you can recreate the standby database if the size of the production database is quite less. If not, then make use of the incremental or rollforwarding method.

Refer this MOS ID *Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]*
SITI
Hi SRP,

DB status:

1.*No ASM storage.*

2.*oracle 9i.*

3.*Archive logs deleted on both Primary and Standby.*

my question:

1.without recreate standby.*is their any method to recover the standby database?*

Regards,
Jumbo.
Shivananda Rao
Since you have lost the archives on the primary database, it is not possible for you to recover your standby database.

The only option is to recreate it fully (if the size of the primary database is small) or to go with the roll forwarding method.
Ganadeva
Hi,

Try this for 9i

Rolling a Standby Forward using an RMAN Incremental Backup in 9i [ID 290817.1]

Please let us know if this works...

Regards,
Ganadeva
SITI
thank you for all.
1 - 7

Post Details

Added on Oct 12 2021
17 comments
3,948 views