Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Possible to perform operations nologging without breaking standby database recovery?

Posted on 2007-11-13
9
Medium Priority
?
1,148 Views
Last Modified: 2013-12-19
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!
0
Comment
Question by:kcorbinakc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 672 total points
ID: 20277124
You may want to look at creating temporary table that are only valid for as long as session is active.

http://www.felipecruz.com/oracle-temporary-tables.php

0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20282029
Since you are implementing a web application, why not use cookies?

Package to check into:  OWA_COOKIE
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20282046
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 9

Assisted Solution

by:joebednarz
joebednarz earned 664 total points
ID: 20282074
Strike that... reverse!  UTL_HTTP is older, OWA_COOKIE is newer.  OWA_COOKIE is a bit simpler to use also...
0
 

Author Comment

by:kcorbinakc
ID: 20283276
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...
0
 
LVL 9

Assisted Solution

by:techji
techji earned 664 total points
ID: 20326072
Well you can do FORCE LOGGING at tablespace level instead of database level. And then create a scrap tablespace with NOLOGGING set and keep these temp tables there.
0
 

Author Comment

by:kcorbinakc
ID: 20350302
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question