Home » RDBMS Server » Backup & Recovery » Disable RedoLog for a single user? (Oracle 10g)
Disable RedoLog for a single user? [message #586482] Thu, 06 June 2013 15:16 Go to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Hi,

I would like all operations on all tables of a SINGLE user not to generate arhive redo logs.

I see this was not possible six years ago:
http://dbaforums.org/oracle/lofiversion/index.php?t6523.html

Did the Oracle got smarter? Is it possible to do this now?
Re: Disable RedoLog for a single user? [message #586483 is a reply to message #586482] Thu, 06 June 2013 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Smarter? It'd be dumber!
Explain your real problem and need, maybe we can help you to find a solution.

Regards
Michel
Re: Disable RedoLog for a single user? [message #586545 is a reply to message #586483] Fri, 07 June 2013 05:53 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Quote:
Smarter? It'd be dumber!

Personally I don't care, but the client thinks Oracle is dumb with redo logs management.

Quote:
Explain your real problem and need, maybe we can help you to find a solution.


Production DB has ~30 users that hold tables dedicated to the transactional system.
Production DB has one user that holds tables dedicated to the data warehouse, which is refreshed every night.
Warehouse scripts are doing full refresh (drop all/insert all) and take about 5 hours.
The scripts, transforming the data from transactional schemas into the warehouse, are spamming redo logs with lots of trash data (40GB).
The client sees no point in having the redo archive grow that big every time.
The client says this has no advantages, only causes a slower database restoration time in case the redo log has to be put in action, also performance of warehouse scripts is slowed down.
The client wants to restore transactional data as fast as possible and warehouse data to be refreshed at night.
As far as I know disabling Redo Log for a single user is not possible within a single database, and it has been tried to move the warehouse into another DB, but the performance of data transformation scripts gets much slower then.
Sad

Re: Disable RedoLog for a single user? [message #586547 is a reply to message #586545] Fri, 07 June 2013 06:07 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
The client wants to restore transactional data as fast as possible and warehouse data to be refreshed at night.

Did you consider use of materialized views?
Re: Disable RedoLog for a single user? [message #586568 is a reply to message #586545] Fri, 07 June 2013 08:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you not set the DW tables to NOLOGGING, and populate them with a direct load? That will avoid redo and undo generation.
Or if for some reason you cannot do a direct load into the DW tables, then do your intermediate processing on global temporary tables (which will generate undo (in the current release) but not redo) and when the data is prepared, direct load from them into the DW tables?
Re: Disable RedoLog for a single user? [message #586575 is a reply to message #586545] Fri, 07 June 2013 09:32 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Buchas wrote on Fri, 07 June 2013 06:53
. . . E t c . . . to the data warehouse, which is refreshed every night.
Warehouse scripts are doing full refresh (drop all/insert all) and take about 5 hours.
. . .

This means the DW refresh is designed extremely inefficient.
Listen to Littlefoot and Watson and re-design the process.
And perhaps the DW itself needs to be re-designed?

[Updated on: Fri, 07 June 2013 10:28] by Moderator

Report message to a moderator

Re: Disable RedoLog for a single user? [message #586581 is a reply to message #586545] Fri, 07 June 2013 10:31 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... In addition, to all the above, why transactional and DWH are in the same database?
DWH should be in NOARCHIVEDLOG mode (as there should be no transaction), all tables in NOLOGGING mode and... (see John's post).
Your architecture is wrong.

Regards
Michel
Previous Topic: Database hang due to ArchiveLog full
Next Topic: Oracle 10 g Query
Goto Forum:
  


Current Time: Thu Mar 28 05:47:01 CDT 2024