Reviewr
priyaramnan, September 25, 2002 - 2:44 am UTC
greetings,
In our organization we linux server in which oracle ver 8 is installed.
now in the init.ora prameter file i have given as
utl_file_dir = /home/priya
from my machine i have mapped to the linux home directory as "L:".My
machine operating system is win 95
from my machine i have created a procedure to write into a text file in the
L:\priya.The procedure is given below
Create or replace PROCEDURE write_file IS
l_file UTL_FILE.FILE_TYPE;
BEGIN
l_file := utl_file.fopen('l:\priya', 'test.txt', 'w');
UTL_FILE.PUT_LINE(l_file, 'this is a test');
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
END;
Now when i execute this i get an error as
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "DEPOSITS.WRITE_FILE", line 4
ORA-06512: at line 1
i even tried
utl_file_dir = *
i get the same error
how to proceed further??.(i checked for the case sensitivity)
i checked the v$parameter table but the value is null for the utl_file_dir=/home/priya.
I don't understand why??
becoz once i changed the parameter file i even restarted the instance.
i am in badly need of the answer.
Thanxs in advance.
September 25, 2002 - 8:17 am UTC
server is running on linux.
you are using windoze file names.
think about it. Server is running on linux. plsql runs in the server. server sees /home/priya - NOT L:\priya.
use the directory name the SERVER where the code is running would see.
If utl_file_dir is not set (you need NULL) then you messed up your init.ora file in some way. Look for the LAST utl_file_dir entry -- that is the one that counts -- in the init.ora file.
Reviewer
priyaramnan2k2, September 26, 2002 - 3:58 am UTC
Thanxs Mr.Tom it worked well.
A reader, August 27, 2004 - 12:02 pm UTC
Tom,
We have a backup machine tied to the primary. Do any changes to init.ora propagate to the standby machine? I am turning audit_trail=db and changing java_pool_size and large_pool_size values. What happens when replication is turned off, turned on, and when it is changed from off to on? Also, when auditing is on on the primary does it need to be turned on the secondary? Is it necessary? Also, when to start&stop the secondary? Should it be done immediately after primary? Please clarify.
August 27, 2004 - 12:59 pm UTC
you have to maintain the init.ora's in a standby configuration
but you say "replication"? that's not a backup tool.
sooo, cannot really comment as to what to do, since I don't know what you are doing.
A reader, August 27, 2004 - 1:11 pm UTC
Tom,
Replication is done by dataguard. The standby machine acts as a backup to primary. As far as I know here, standby gets archived logs from primary. Depending on some actions, replication is turned off and on. Last month they performed something on the standby machine. They turned replication off. That stopped archive log files from flowing. Sorry about my inexpertise but as far as I know standby is a photocopy of primary.
Now, we decided to implement auditing in primary machine. Also we decided to increase large_pool_size and java_pool_size.
Please clarify in a normal primary-standby scenario does the above three changes require changes in standby done manually??
Any help will be appreciated
August 27, 2004 - 1:15 pm UTC
you would need to keep the init.ora's as "in sync" as you wanted them to be. the other database is in managed recovery mode, it won't need to have the parameter changes take effect so you never need shut it down.
A reader, August 27, 2004 - 1:21 pm UTC
Thanks Tom,
That means init.ora in standby needs to be changed manually to make in sync. And it doesn't need to be restarted.
August 27, 2004 - 1:32 pm UTC
correct.
A reader, September 17, 2004 - 7:19 pm UTC
Tom,
responding to 2nd comment from below, the database btw is in both read only and managed recovery modes. In that case, setting audit_trail=db would cause a problem?
If the audit_trail=db, then, at startup I noticed a problem. It wouldn't start in read only mode??
September 17, 2004 - 8:25 pm UTC
if db is opened read only, audit-trail will be a problem, yes. it is hard to audit to the database if the database is read only after all.
A reader, September 17, 2004 - 9:01 pm UTC
Tom,
How to open d/b so that it could perform in either of the modes? The database should be queried always and it also gets archive logs from primary? In that case, again, could the audit_trail be set to db? Please clarify. Thanks
September 17, 2004 - 9:10 pm UTC
think about it
db = read only
audit_trail = db => write to database
db = read only
think about it.
A reader, September 17, 2004 - 9:15 pm UTC
Tom,
ok, i want the audit_trail set to db, have both options: 1. ddl, dml to db. 2. archive logging. Then during database start up, after mounting it, if it complains "database not open", what should i use?
September 18, 2004 - 10:15 am UTC
er?
sorry you are not making too much sense on this.
if database says "i'm not open" after mounting, it is correct, you need to alter database open;
but if this is a physical standby, as you seem to indicate -- your options 1 and 2 above are mutually exclusive
A reader, September 18, 2004 - 7:38 pm UTC
Tom,
Thank you. I found this in documentation:
The following statement opens a database in read-only mode:
ALTER DATABASE OPEN READ ONLY;
You can also open a database in read-write mode as follows:
ALTER DATABASE OPEN READ WRITE;
In order to meet all my requirements, I think I just have to use "ALTER DATABASE OPEN READ WRITE;" That permits auditing, also as you said they are mutually exclusive, managed recovery is also permitted.
Please correct me!
September 19, 2004 - 10:19 am UTC
if you open the database read write, you will not have a physical standby instance anymore.
a physical standby can either be
a) opened READ ONLY (period, nothing else, no write, even temp must be super specially configured)
b) in managed recovery mode.
really -- two modes, period. no writing, none.
A reader, September 19, 2004 - 4:16 pm UTC
Tom,
Thanks for the response. I am sure, however, there was a new instance under peration. The new database instance(schema) was made/loaded few days back and a lot of things are being performed. Inserts, deletes etc ....
The normal instance/schema ( please clear my confusion here) is entered using sqlplus schema_name1@database_name
The test instance is used as sqlplus test@database_name
Then, in that case how were opertaions performed? If it was read only and managed recovery
September 19, 2004 - 5:10 pm UTC
you lost me. no idea what you are asking here. "what operations"
A reader, September 19, 2004 - 5:18 pm UTC
Tom,
The confusion is this: There are two instances runninng ... test instance, regular instance. using the test instance, testing was performed. AFAIK, inserts, deletes etc ...
Now, the whole database is down. It has to be brought up. When testing is carried out, archive logs are not brought.
A couple of days back, os reboot occured, so database reboot is occuring -- hence how to start?
Another question is how is each instance affected while starting the database? How does the read-only/managed recovery concept apply to the instances?
Please clarify
September 19, 2004 - 5:37 pm UTC
sorry -- no idea what state your system is in, what you were doing, how you were using it.
if you were using standby - there would have been "production", and there would have been "standby"
the words "test and regular", they do not come into play in a physical standby configuration. there would be no "test" instance there. the physical standby is either in managed recovery, or read only (for reporting). It would not be a "test" instance.
contact support, maybe you can relate to them what you had, what you did, what you now have and what you might be able to "save"
A reader, September 19, 2004 - 8:31 pm UTC
Tom,
Thanks very much for the clarifications.
few questions ...
1) Auditing is not impossible on standby machine. Please comment (true or false)
2) What's the defference between bringing a database up, bringing an instance up? How many instances can exist?
3) What are tar and itar? You mention them always
4) "Contact support". Do you suggest "pick up the phone -- call oracle"? Who should do that?
September 19, 2004 - 9:54 pm UTC
1) not into the database, *think about it*
2) you do not bring up a database. you bring up an instance, it then mounts and opens a database. a database may be mounted and opened by many instances simultaneously (RAC), but an instance will only ever mount and open a single database in its life (you have to destroy the instance and restart it with a new set of parameters -- instances are transient things)
3) support.
4) the person who ordered the software -- who bought it, they would have your support information. typically the DBA would know.
A reader, September 19, 2004 - 10:42 pm UTC
Thanks Tom,
One last question,
If I open the database in READ WRITE mode, could it still switch to managed_recovery when needed?
In other words, is READ ONLY the only mode in order to have it function in read and managed_recovery modes?
Thanks for all the help.
September 20, 2004 - 7:57 am UTC
NO, i don't know how to say it any clearer than:
a physical standby will either be
a) in managed recovery mode
b) open READONLY
period, thats it, nothing else, no other changes, thats all she wrote, finito.
I meet same problem but My test server is Windows XP SP2
Nguyen Thanh Trung, January 12, 2005 - 11:00 pm UTC
I meet same problem but My test server is Windows XP SP2
January 13, 2005 - 8:47 am UTC
what "same problem"?!?