Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, maggio 08, 2017

Recovering Table, Part 1 - Introduction

Recovery a single table/table partition is a 12c new feature.

In order to do this, there are 5 steps that Oracle does. From the manual pages:

  1. Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.
  2. Creates an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database. You can specify the location to which the recovered data files are stored in the auxiliary database.
  3. Creates a Data Pump export dump file that contains the recovered tables or table partitions. You can specify the name and the location of the export dump file used to store the metadata of the recovered tables or table partitions.
  4. (Optional) Imports the Data Pump export dump file into the target instance. You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility.
  5. (Optional) Renames the recovered tables or table partitions in the target database.

The step 4 is optional because you can avoid importing the table. In this case, you have to import it manually. The step 5 is optional because you can avoid renaming the imported table. In this case, the table will be overwritten.

In my example (it's a real production case) I made "import table" and "remap table".

The rman scrpit I used is:

run {
2> recover table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE
3> until time "to_date('02/05/2017 17:00:00','dd/mm/yyyy hh24:mi:ss')"
4> auxiliary destination '/FS_DATAPUMP_X_MIGRAZIONE/RECOVER_PCMSp'
5> remap table PCMS_ADM.PIANIFICAZIONE_MANUTENZIONE:PIANIFICAZIONE_MAN_BCK;
6> }

Because of step 2 (creates an auxiliary database), you have to calculate space needed before the table recovery. The recovery process is split into 2 logically phases:
  1. Restore the core part
  2. Restore the application part
For the "core part" you have to calculate:

  • SYSTEM tablespace size
  • SYSAUX tablespace size
  • UNDO tablespace size
  • TEMP tablespace size
  • archived log size

while for the "application part":

  • tablcespace size on which the table is located
  • (optionally) tablespace size on which the lob is located
  • archived log size

In order to calculate the total space, I wrote this query:

select sum(bytes)/1024/1024/1024 gbytes from (
select BYTES from dba_data_files where TABLESPACE_NAME in ('SYSTEM','SYSAUX')
union all
select BYTES from dba_data_files ddf join dba_tablespaces dt
on (ddf.tablespace_name=dt.tablespace_name)
where dt. CONTENTS= 'UNDO'
union all
select BYTES from dba_temp_files
union all
select BYTES from dba_data_files where tablespace_name in (select tablespace_name from dba_segments where owner='&&owner' and segment_name='&&tabname')
union all
select BYTES from dba_data_files where tablespace_name in (select tablespace_name from dba_segments
where (owner, segment_name) in (SELECT owner, segment_name FROM dba_lobs where owner = '&&owner' and segment_name='&tabname'))
union all
select output_bytes from  v$rman_backup_job_details where end_time >= (select max(end_time) from  v$rman_backup_job_details where input_type='DB FULL'));

In the next posts, I'm going to show the 2 steps of recovery table.

Recovering Table, Part 2 - The "Core" part
Recovering Table, Part 3 - The "Application" part
Recovering Table, Part 4 - Note

Nessun commento: