Thanks for the question, Prince.

Asked: August 30, 2002 - 9:25 pm UTC

Last updated: August 04, 2005 - 9:43 am UTC

Version: 8.1.7.4

Viewed 1000+ times

You Asked

I take the backup os the tablespace TSIX at T0.

I create an index with NOlogging at time T1 on TSIX.

Alter the index for logging at T2

Say at T3, I have a failure and I want to recover TSIX

Assume, I did not take the TSIX backup after the index creation.

Now, Can I be able to recover the TSIX as of T3? Can I drop the index, while doing the recovery?

In summary, how do I recover TSIX to Point T3, from the backup which was taken at T0?

Thanks,

and Tom said...

You cannot recover the index in TSIX as your backup predates its existence and you have no redo log with which to recreate it.

You'll restore TSIX, recover it -- but the index will be invalid, you'll drop and recreate the index.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Tom!

Prince., August 31, 2002 - 3:39 pm UTC

My main quetion was, Can I recover the TSIX or would it error out when it comes to the point of recovering the index and fails to recover the TSIX further.

Anyway, your answer suggests that I can recover the TSIX but not the index, which is absoluttlely fine.

August 31, 2002 - 4:26 pm UTC

That is correct.

faster when creating index with nologging?

A reader, February 06, 2004 - 11:02 am UTC

Hi Tom,

just want to know if create index with nologging on noarchive log database, no redo generation? 0r in noarchive log database, nologging and logging when building index are the same (in terms of redo generation)? If yes, nologging option in creating index should only be
"valid" in archivelog database? Am I right?

David

February 07, 2004 - 1:24 pm UTC

easy to see....


ops$tkyte@ORA9IR2> select log_mode from v$database;
 
LOG_MODE
------------
NOARCHIVELOG
 

ops$tkyte@ORA9IR2> set verify off
ops$tkyte@ORA9IR2> column value new_val V

ops$tkyte@ORA9IR2> select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE
------------------------------ ----------
redo size                         3619828
 
ops$tkyte@ORA9IR2> create table t NOLOGGING as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.name, b.value, b.value-&V diff from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         3690384      70556
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE
------------------------------ ----------
redo size                         3724888
 
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.name, b.value, b.value-&V diff from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         3795492      70604

<b>so, for create table -- no difference.... but....</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(owner,object_type,object_name);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.name, b.value, b.value-&V diff from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         5519780    1724288
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(object_name,object_type,owner) NOLOGGING;
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.name, b.value, b.value-&V diff from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size'
  3  /
 
NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         5559596      39816
 
<b>for create index, big difference....</b>

Nologging

Yogesh, July 23, 2004 - 6:10 am UTC

I have created the table with "Create as select nologging parallel (degree 4)", as well created some indexes with nologging and parallel (degree 4), Now if I don't change the option back to logging for table & index, will it continue to skip redo log for any changes made after the table / index creation?

Lets assume that above operation was performed at T0 time. I enabled the logging option at time T1 .. so the changes made between T1-T0 time are not logged anywhere ? Is there any way to perform recovery in this scenario?

Another sub question ... How the parallel clause specified with degree 4 at the time of creation will effect the day to day operation on the tables / indexes? Will it be using multiple instances to rebuild the indexes / tables, when any changes are made?

July 23, 2004 - 8:59 am UTC

only for the few operations that are "nologging" -- like alter move, alter rebuild, direct path inserts.

for normal insert/update/deletes --logging happens "as usual"

is there any way to perform recovery ? sure, at time T0 you wold have backed up the affected datafiles -- meaning before using nologging, you need to coordinate with your DBA - work together.

the parallel clause will affect query plans and bulk operations -- you might turn that "off" after building if you don't want parallel operations day to day.

Parallel

Yogesh, July 23, 2004 - 10:33 am UTC

If I keep parallel enabled, Can it cause any problem?

July 23, 2004 - 3:40 pm UTC

did you want them parallel enabled? if not, yes, it'll cause a problem in that you did not want them to be so.

you tell me -- do you want them parallel enabled or not?

Parallel

Yogesh, July 26, 2004 - 4:52 am UTC

before rebuilding, they were noparallel. But if parallel is going to give me some performace improvement then I'll go for that.

July 26, 2004 - 7:27 am UTC

one word:

"benchmark"

well, two words

"test, benchmark"

why it always goes parallel 4

A reader, August 04, 2005 - 8:02 am UTC

Hi

I have ran this DDL statement

CREATE INDEX "PK_STA_DET" ON "STA_DET" ("IDINTRA" , "N_DETALLE" ) PCTFREE 5 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 314572800 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "ANSI_IDX_P" NOLOGGING
PARALLEL (DEGREE 2 INSTANCES 1);

I issue DEGREE 2 and expecting see two parallel slaves but I see 4 how so?

August 04, 2005 - 9:43 am UTC

you probably have 2 readers of data and 2 sorters of data.

degree 2 is for each operation that is decides to perform.

so, it is parallel 2, two reads -> two sorts -> one coordinator.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks Tom!

faster when creating index with nologging?

Nologging

Parallel

Parallel

why it always goes parallel 4

More to Explore

Backup/Recovery

Index creation with nologging and recovery

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks Tom!

faster when creating index with nologging?

Nologging

Parallel

Parallel

why it always goes parallel 4

More to Explore

Backup/Recovery