|
|
|
|
|
|
|
Re: No Redo for DML? [message #163635 is a reply to message #163593] |
Fri, 17 March 2006 10:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
feroze
Messages: 144 Registered: September 2005 Location: chicago
|
Senior Member |
|
|
Hi
Mahesh Sir i was trying to run this
set autotrace traceonly stat
select count(*) from emp;
i got this error, what am i missing here
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report
1 row selected.
SP2-0612: Error generating AUTOTRACE report
SP2-0612: Error generating AUTOTRACE STATISTICS report
Version is Oracle9i Enterprise Edition Release 9.2.0.5.0.
Thanks
|
|
|
|
Re: No Redo for DML? [message #163790 is a reply to message #163593] |
Mon, 20 March 2006 00:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
aciolac
Messages: 242 Registered: February 2006
|
Senior Member |
|
|
About comments of Mahesh Rajendran on my words:the initial question was How can I disable redo generation for DML statements. I think that I am responded very correct - impossible to stop redo generation. Not so? Please see much attent all thread.
|
|
|
|
Re: No Redo for DML? [message #471757 is a reply to message #163549] |
Tue, 17 August 2010 03:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ahmedfahmy80
Messages: 5 Registered: August 2010 Location: Egypt
|
Junior Member |
|
|
Hi,
Simply you can disable log generation by setting the following parameter to TRUE.
_disable_logging=TRUE
But it is not recommended to play with hidden parameters as it is not supported by Oracle Metalink.
|
|
|
|
Re: No Redo for DML? [message #471806 is a reply to message #163549] |
Tue, 17 August 2010 06:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ahmedfahmy80
Messages: 5 Registered: August 2010 Location: Egypt
|
Junior Member |
|
|
if you think a little bit in my reply, you will find that this parameter can be used while DB up & running. so you can use it for a specific job and once you finish you can return it back to its value.
Best Scenatio to use such parameter in import operations using impdp.
and please be polit in your reply done say such words "stupid" because you dont know who am i and what's my experience to even reply me.
|
|
|
Re: No Redo for DML? [message #471807 is a reply to message #471806] |
Tue, 17 August 2010 07:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Quote:Simply you can disable log generation by setting the following parameter to TRUE.
_disable_logging=TRUE
Apart from being a very dangerous suggestion, this argument is still not totally true.
Even with the parameter set, redo information will still be generated (written to buffer) and only disk writes will not occur.
[Updated on: Tue, 17 August 2010 07:14] Report message to a moderator
|
|
|
|
|
Re: No Redo for DML? [message #471811 is a reply to message #471807] |
Tue, 17 August 2010 07:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ahmedfahmy80
Messages: 5 Registered: August 2010 Location: Egypt
|
Junior Member |
|
|
You can test what i'm saying by doing the following query....
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
if any change in size has been detected then there is an entry has been logged into redo buffer. otherwise you successfully bypass the redo buffer. Do it and let me know what do u think.
|
|
|
Re: No Redo for DML? [message #471812 is a reply to message #471810] |
Tue, 17 August 2010 07:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I can't keep quiet about this - the subject matter is so dangerous that it must be dealt with.
If _disable_logging is set, then no redo is written to the online logs. This means that if the instance crashes, it is not posssible for SMON to recover it and open the database: you must perform an incomplete recovery, up to the point at which you disabled logging. Some people may say "don't worry: set _allow_resetlogs_corruption, and then you can open your database." This is even worse, because if the crash occured during a data dictionary update, you will now have a corrupted data dictionary as well as corrupted user data.
I do not believe that any responsible DBA would ever dream of using these parameters.
Over and out.
|
|
|
|
|
Re: No Redo for DML? [message #472478 is a reply to message #471812] |
Fri, 20 August 2010 22:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
John Watson wrote on Tue, 17 August 2010 19:36I can't keep quiet about this - the subject matter is so dangerous that it must be dealt with.
If _disable_logging is set, then no redo is written to the online logs. This means that if the instance crashes, it is not posssible for SMON to recover it and open the database: you must perform an incomplete recovery, up to the point at which you disabled logging. Some people may say "don't worry: set _allow_resetlogs_corruption, and then you can open your database." This is even worse, because if the crash occured during a data dictionary update, you will now have a corrupted data dictionary as well as corrupted user data.
I do not believe that any responsible DBA would ever dream of using these parameters.
Over and out.
Well, this is the best answer in this topic to ahmedfahmy80.
|
|
|
Re: No Redo for DML? [message #472557 is a reply to message #163549] |
Sun, 22 August 2010 02:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ahmedfahmy80
Messages: 5 Registered: August 2010 Location: Egypt
|
Junior Member |
|
|
Thx for your reply. However i'm the DBA that i'm using this point that it is because we have some testing regions not using archive mode. In addition to i'm using that parameter in certain circumstances, such as import and export as we are using the import process as one of our processes. So i dont care about recover something i dont need it in real life with those testing regions. I hope everyone now understand what i meant and know that in some situations we can use it under certain conditions...
Regards,
Ahmed Fahmy
|
|
|
Re: No Redo for DML? [message #472559 is a reply to message #472557] |
Sun, 22 August 2010 02:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:So i dont care about recover something i dont need it in real life with those testing regions.
And you are ready to restore your database if you screw the dictionary and lose all modifications since then.
I advice you to take a full backup before doing such thing and do it in restricted mode (without other user) then you will lose nothing but (down)time.
Regards
Michel
[Updated on: Sun, 22 August 2010 02:41] Report message to a moderator
|
|
|
Re: No Redo for DML? [message #472598 is a reply to message #472557] |
Sun, 22 August 2010 23:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
ahmedfahmy80 wrote on Sun, 22 August 2010 14:27Thx for your reply. However i'm the DBA that i'm using this point that it is because we have some testing regions not using archive mode. In addition to i'm using that parameter in certain circumstances, such as import and export as we are using the import process as one of our processes. So i dont care about recover something i dont need it in real life with those testing regions. I hope everyone now understand what i meant and know that in some situations we can use it under certain conditions...
Regards,
Ahmed Fahmy
Well, I think, you're not only the DBA at here.
Your system is not our system, your situation is not our situation, your point, working, ..etc.. are not our at all. You can use the hidden parameter, you can issue some plan, however, you can not or at least, you may not use this in production db, also your system.
OK, you disable this parameter, would you like to answer me some questions:
1- If DB crash, how do you restore?
1.1. You can use dumpfile to restore, but, how do you rebuild internal code? For example: Your app connect to DB by using jdbc, your app was deployed by java, but, in some package, one function was used to declare some way to connect the other db by db_link, submit the variable by using literal string (of course, this is not well of coding, but, some coders often do not pay attention to this), how do you restore this to original?
1.2. The SYS user is not always to use, except maintenance Database, however, some java classes in SYS has revoked by schema user's java other class (or call them), example: xxxx_Code_Base_ .. If some coders in your system submit some thing in this _Code_Base of Sys, such as example, how do you restore internal code?
2- If your boss suggest limit time to restore/rebuild DB, an example, 2 hours to restore 1TB, and you must open all of system after 2 hours, how do you do?
Regard!
|
|
|
Re: No Redo for DML? [message #473004 is a reply to message #472598] |
Wed, 25 August 2010 06:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Why in the name of all that's holy would you use a test DB configured differently from production? Unless that's the sole test, but you're using blatantly dangerous commands which would (or at least should) never be allowed into a real environment. What would that prove? What validity would the testing have? Why worry about removing redo if its going to have to happen in prod anyway? Not using archive log is no reason to remove redo!
I'm not saying its never valid to use these (never say never and they're obviously there for a reason) but your description sounds like you probably shouldnt be doing it.
[Updated on: Wed, 25 August 2010 06:18] Report message to a moderator
|
|
|