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
for c in 1..80000
insert /*+ APPEND */ into friccio.TEST values ('TEST');
DBMS_OUTPUT.put_line('Tiempo: '||to_char((DBMS_UTILITY.get_time - f_inicio)/100)||' segundos.');
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.