In order to do this, there are 5 steps that Oracle does. From the manual pages:
- Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.
- 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.
- 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.
- (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.
- (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:
while for the "application part":
In order to calculate the total space, I wrote this query:
select sum(bytes)/1024/1024/1024 gbytes from (
SELECT round(sum(bytes)/1024/1024/1024,2) gbytes
FROM (
-- SYS%
SELECT bytes FROM dba_data_files WHERE tablespace_name in ('SYSTEM','SYSAUX')
UNION ALL
-- UNDO
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
-- tables's datafiles
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
-- lob's datafiles
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 >= (
-- Archived log
SELECT input_bytes FROM v$rman_backup_job_details
WHERE end_time <= to_date('&pitr','dd/mm/yyyy hh24:mi:ss')
AND end_time > (SELECT max(end_time) FROM v$rman_backup_job_details WHERE input_type='DB FULL')
);
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:
- Restore the core part
- Restore the application part
- 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 round(sum(bytes)/1024/1024/1024,2) gbytes
FROM (
-- SYS%
SELECT bytes FROM dba_data_files WHERE tablespace_name in ('SYSTEM','SYSAUX')
UNION ALL
-- UNDO
SELECT bytes FROM dba_data_files ddf JOIN dba_tablespaces dt
ON (ddf.tablespace_name=dt.tablespace_name) WHERE dt.contents= 'UNDO'
UNION ALL
-- tables's datafiles
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
-- lob's datafiles
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
-- Archived log
SELECT input_bytes FROM v$rman_backup_job_details
WHERE end_time <= to_date('&pitr','dd/mm/yyyy hh24:mi:ss')
AND 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
Update
2017/Nov/11 - Modified the select statement in order to correct some of bugs.
Nessun commento:
Posta un commento