Nologging/Unrecoverable operations
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/appindex01.dbf
/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/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