kcorbinakc
asked on
Possible to perform operations nologging without breaking standby database recovery?
I have a web application that runs on Oracle 10g in a primary/physical standby scenario. As a result, force_logging is turned on for the entire database.
The application uses a table for storing the users 'temporary' information such as last screen, open items, etc. This is not critical data so we don't care if it's lost in the event of database failure but this table is written to every time the user moves to a different area of the application generating a large amount of redo, as well as slowing the application down (I think) substantially.
Is there a way to create a temporary table/tablespace or something that won't generate redo (or even undo) for storing this data in that won't break the standby if I have to activate it? Also, it needs to not be affected by the force_logging parameter.
Ideas? I know this is something that many other Oracle users must have encountered, but I can't figure out a way to do it.
Thanks!
The application uses a table for storing the users 'temporary' information such as last screen, open items, etc. This is not critical data so we don't care if it's lost in the event of database failure but this table is written to every time the user moves to a different area of the application generating a large amount of redo, as well as slowing the application down (I think) substantially.
Is there a way to create a temporary table/tablespace or something that won't generate redo (or even undo) for storing this data in that won't break the standby if I have to activate it? Also, it needs to not be affected by the force_logging parameter.
Ideas? I know this is something that many other Oracle users must have encountered, but I can't figure out a way to do it.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry... older version. After 9i, look at UTL_HTTP.
Here is a really good link plus some programming examples: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_http.htm#1012389
Here is a really good link plus some programming examples: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_http.htm#1012389
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I should have been more specific as to what we are doing: this is a web app, but not based on Oracle's application server. We actually use Apache and mod_perl for the FE and just Oracle database for the BE.
The temporary table suggestion is closer to what I'm looking for and I'm going to investigate it further. The problem I'm seeing so far, is that I need more persistence than the table only existing while the Oracle session is active, but I love the fact that it's handled like the Temporary Tablespace and doesn't generate redo (from what I understand) and while it's data is not persistent, the table definition is.
If there was a way to have a similar behavior, but not restrict the data access/persistence to a single Oracle session...
The temporary table suggestion is closer to what I'm looking for and I'm going to investigate it further. The problem I'm seeing so far, is that I need more persistence than the table only existing while the Oracle session is active, but I love the fact that it's handled like the Temporary Tablespace and doesn't generate redo (from what I understand) and while it's data is not persistent, the table definition is.
If there was a way to have a similar behavior, but not restrict the data access/persistence to a single Oracle session...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The problem is Data Guard requires force_logging to be set at the database level, effectively ignoring any nologging operations at any level.
Another thought I had was to maybe set a trigger that copies the data from a permanent table into a temporary table when a session is created. Then, before the session is ended, copy the data back to the permanent table. The problem though is that because connections to the application server aren't persistent to a single web server, let alone a single apache child, there's no persistence with the connection to a particular Oracle session. So, this couldn't really be made to work in practice.
Another thought I had was to maybe set a trigger that copies the data from a permanent table into a temporary table when a session is created. Then, before the session is ended, copy the data back to the permanent table. The problem though is that because connections to the application server aren't persistent to a single web server, let alone a single apache child, there's no persistence with the connection to a particular Oracle session. So, this couldn't really be made to work in practice.
Package to check into: OWA_COOKIE