Help - Search - Members - Calendar
Full Version: NoLogging in table
Oracle DBA Forums > Oracle > Oracle Forum
Francisco Riccio
Hi everyone,

I am doing a test with the option of nologging in a Oracle 10.2.0.1 running on Linux Red Hat of 32 bits.

I create a table TEST on a new schema and in a new tablespace.
The database and the new tablespace don't have the option of force logging, but the tablespace have the field logging in Y (default).

Ok, then take a backup of my database with RMAN (backup as compressed backupset database plus archivelog).

Then i run the following query:

set serveroutput on
declare
f_inicio number:=0;
begin
f_inicio:=DBMS_UTILITY.get_time;
for c in 1..80000
loop
insert /*+ APPEND */ into friccio.TEST values ('TEST');
end loop;
commit;
DBMS_OUTPUT.put_line('Tiempo: '||to_char((DBMS_UTILITY.get_time - f_inicio)/100)||' segundos.');
end;
/

The table has only 1 field of char(2000)

And it generate 3 archives redo logs of 50 MB (Only i am connected in the test database).

Then i reviewed the archives generated with Log Minner and i didn't see entries of INSERT for table TEST. (I attach the image)

Then I take the current scn (select current_scn from v$database);

And I do a restore and recover the database until this scn value and then i do a select to my table TEST and it gives me the data (80000 records).

So my questions are:

1. Just i do 80000 cicles in my for where each cicle write 2000 bytes => 80000 x 2000 bytes = 152.59MB so it just generates 3 archives redo logs since run my pl/sql script.
So my question is: Why it generate 3 archives redo logs if my table is in nologging and i use hint /*append*/, my database hasn't the option force logging enable and my tablespace are in logging. Even in the log minner i don't see entries of insert.

2. Why can i recover the table with all its data, if Oracle says that with notlogging you can't recover with archives redo logs the objects with nologging.

3. I can verify that really with nologging is faster than logging, in my test it was 3 times faster than logging.

Please, i want to listen some answers.

Regards.

Francisco.





SteveC
No comment about what you screenshot shows because there is no way to know if you have used logminer correctly (created a dictionary, added files to be examined, etc.).

Insert /*+ append */ into table.... will ignore the hint.

Insert /*+ append */ into table xxx select x from whatever.... will use the hint.

But thanks for spamming this question on other web sites.
JGarmany
You obviously are logging the changes. Many features in the oracle database require logging.
They only way rman could restore the rows is if they were logged.

Appending rows has no impact on logging, just how the rows are inserted into the table.

http://www.dba-oracle.com/t_logminer.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.