Nologging/Unrecoverable operations

From: Vinni <healdv_at_gmail.com>
Date: Tue, 5 Oct 2010 06:50:24 -0700 (PDT)
Message-ID: <36b11fdf-b155-4c53-8017-d67634021f5b_at_n26g2000yqh.googlegroups.com>



I folks, I decided to experiment creating an unrecoverable operation however I seem to have failed somehow and cannot work out why yet!

The database is Oracle 9.2.0.8 in a Solaris environment.

Ok here is my experiment:

   select force_logging from v$database

NO

--so we have no force logging

select name, unrecoverable_time from v$datafile order by

unrecoverable_time desc nulls last NAME 	UNRECOVERABLE_TIME

/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf

so I had an uncrecoverable operation at 10:34:56 woohoo

ok

the time is now:

select sysdate from dual

05/10/2010 13:08:05

Ok so its time to go make some uncrecoverable operations!

create table vintest (id number, name varchar2(14)) tablespace users nologging --create table
select * from dba_tables where table_name='VINTEST' --check it exists and double check it is in the correct tablespace

OK the create table was ddl and I said NOLOGGING so I should have an unrecoverable operation:

select name, unrecoverable_time from v$datafile order by unrecoverable_time desc nulls last
NAME
UNRECOVERABLE_TIME
/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf

still nothing yet!

ok so im going to insert some values into the new table, commit and then truncate and see if ive managed to do an unrecoverable operation yet!

insert into vintest values (1,'test')
insert into vintest values (1,'test')
insert into vintest values (1,'test')
insert into vintest values (1,'test')
insert into vintest values (1,'test')
insert into vintest values (1,'test')

commit;

select * from vintest

ID 	NAME
1	test
1	test
1	test
1	test
1	test
1	test



truncate table vintest;

select name, unrecoverable_time from v$datafile order by unrecoverable_time desc nulls last
 NAME
UNRECOVERABLE_TIME
/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf

select sysdate from dual
05/10/2010 13:15:15

Why am I not managing to create unrecoverable operations to experiment with ? I have also checked that I am not forcelogging at the tablespace level and i've also tried alter database no forcelogging just to be doubly sure and i'm still not managing to make any!

If someone could tell me what I am not doing that would be great. I've tried creating indexes with nologging, and CTAS nologging still no luck. Received on Tue Oct 05 2010 - 08:50:24 CDT

Original text of this message