Re: Question: Yet more RMAN frustration...

BD schrieb:
> Hi, all.
>
> I'm working through learning RMAN under 8.1.7.4 (yes, I know) running
> on AIX 5.2.0. I am not using a recovery catalog.
>
> I'm making progress, but am at a bit of an impasse. Here is what I am
> attempting to do:
> I want to take a backup of a tablespace; change data in a table in that
> tablespace; apply a restore; confirm that the data is back to its
> original values. Simple.
>
> But for all attempts I've made, and in the notable absence of error
> messages, the restore makes no difference - the changes I made to the
> table are not 'corrected' by the restore.
> Here is my process - I apologize in advance for being dense, but I'd
> really appreciate someone helping to identify my error or lack of
> understanding:
>
>
> STEP 1: CONFIRM THE TABLESPACE FOR THE TABLE TO BE CHANGED:
> CTD3> select owner, table_name, tablespace_name from sys.all_tables
> where upper (table_name) like '%TBLUSERLOG';
>
> OWNER TABLE_NAME TABLESPACE_NAME
> -------- ------------ ------------------------------
> APP1 TBLUSERLOG SMALL_TABLES_2
>
> STEP 2: CHECK THE STATUS OF ARCHIVE LOGGING
> CTD3> archive log list;
> Database log mode Archive Mode
> Automatic archival Enabled
> Archive destination /fs01/app/oracle/product/8.1.7/dbs/arch
> Oldest online log sequence 239
> Next log sequence to archive 241
> Current log sequence 241
>
>
> STEP 3: ASCERTAIN THE CURRENT VALUE OF THE COLUMN TO BE CHANGED:
> CTD3> select distinct (fstrchecksum) from app1.tbluserlog;
> FSTRCHECKSUM
> ------------------------------
> TEST456
>
> STEP 4: BACK UP THE TABLESPACE:
> rman nocatalog target rman/rman_at_CTD3
> Recovery Manager: Release 8.1.7.4.0 - Production
>
> RMAN-06005: connected to target database: CTD3 (DBID=2897029717)
> RMAN-06009: using target database controlfile instead of recovery
> catalog
> run {allocate channel d1 type disk format
> '/rs01/oradata/bkup02/%U';backup tablespace small_tables_2;}
> RMAN-08503: piece handle=/rs01/oradata/bkup02/0igr5fnf_1_1 comment=NONE
> RMAN-08525: backup set complete, elapsed time: 00:00:35
> RMAN-08031: released channel: d1
>
>
> STEP 5: CHANGE THE DATA FROM SQLPLUS:
> update app1.tbluserlog set fstrchecksum = 'TEST789';
> 23343 rows updated.
>
> commit;
> Commit complete.
>
>
> STEP 6: CONFIRM THE CHANGE:
> select distinct (fstrchecksum) from app1.tbluserlog;
> FSTRCHECKSUM
> ------------------------------
> TEST789
>
>
> STEP 7: RESTORE FROM THE BACKUP JUST TAKEN
> run {sql 'alter tablespace small_tables_2 offline';
> allocate channel ch1 type disk;
> restore tablespace small_tables_2;
> switch datafile all;
> recover tablespace small_tables_2;
> sql 'alter tablespace small_tables_2 online';}
>
> RMAN-08511: piece handle=/rs01/oradata/bkup02/0igr5fnf_1_1 tag=null
> params=NULL
> RMAN-08024: channel ch1: restore complete
> RMAN-03022: compiling command: switch
> RMAN-03022: compiling command: recover
> RMAN-03022: compiling command: recover(1)
> RMAN-03022: compiling command: recover(2)
> RMAN-03022: compiling command: recover(3)
> RMAN-03023: executing command: recover(3)
> RMAN-08054: starting media recovery
> RMAN-08055: media recovery complete
> RMAN-03022: compiling command: recover(4)
> RMAN-03022: compiling command: sql
> RMAN-06162: sql statement: alter tablespace small_tables_2 online
> RMAN-03023: executing command: sql
> RMAN-08031: released channel: ch1
>
>
> STEP 8: CONFIRM THAT THE VALUE IN THE COLUMN IS BACK TO ITS ORIGINAL
> STATE:
> select distinct (fstrchecksum) from app1.tbluserlog;
> FSTRCHECKSUM
> ------------------------------
> TEST789 *** Not the original value!! ***
>
>
> I am sure this concerns some behavior of RMAN concerning which it will
> apply first when doing a restore, but at this point I've been at it all
> day and haven't gotten any joy.
>
> Thanks much for any insights!!
>
> BD
>

What you intend to do is called TSPITR. The main problem is, that you can not throw a single tablespace in the past - in that case the database become inconsistent. You can recover until time in the past, but in that case - the whole database, and open it with resetlogs. So you would lose all the changes ,done after that point in time. To achieve this goal Oracle can do the trick - make incomplete recovery of desired tablespace in the separately ( called auxiliary ) database.

Best regards

Maxim Received on Wed Aug 03 2005 - 15:53:31 CDT