Oracle 11.2: Restaurer un tablespace après un DROP

Les nouveautés RMAN sont souvent parmi les plus utiles de chaque version. Oracle 11.2 ne déroge pas à la règle avec la possibilité de lancer une commande DUPLICATE sans se connecter à la TARGET et les améliorations liées au Tablespace Point In Time Recovery ou TSPITR. Ce sont ces nouvelles possibilités gérées automatiquement à partir d’Oracle 11.2 que je vous propose d’illustrer dans cet article…

Pour reproduire cet exemple, vous procèderez en 4 étapes:

• Dans un premier temps, vous allez configurer une base de données en mode archivelog, un tablespace LMT et une sauvegarde
• Dans un deuxième temps, supprimez le tablespace que vous pourrez restaurez par la suite
• Ensuite, vous déterminerez, avec logminer, le SCN de la commande DROP TABLESPACE
• Enfin, vous effectuerez une commande RMAN RECOVER pour restaurer le tablespace juste avant la commande DROP

Plutôt facile ? Et bien, en effet ; il ne vous reste plus qu’à passer à la pratique.

Configurer votre base de données pour permettre le TSPITR

Il y a plusieurs restrictions pour effectuer un « Tablespace Point In Time Recovery »; référez-vous à la documentation associée pour bien comprendre les pré-requis et les limites associées. Les plus évidentes étant bien entendu qu’il vous faut un tablespace « Locally Managed » avec, si possible une table ou 2 pour vérifier que les données sont bien restaurées; il faut également que votre base de données soit en mode archivelogs et que vous ayez une sauvegarde. le script ci-dessous crée un tablespace X et une sauvegarde. Vous n’aurez aucun mal à l’adapter à votre configuration, si, par exemple, que vous utilisez OMF ou ASM:

sqlplus / as sysdba

create tablespace X
   datafile '/u01/app/oracle/oradata/BLACK/x01.dbf'
   size 50M;

create user demo
    identified by demo
    default tablespace users
    temporary tablespace temp;

grant connect, resource to demo;

create table demo.x (id number)
  tablespace x;

insert into demo.x values (1);
commit;

!mkdir -p /u01/app/oracle/oradata/BLACK/archivelogs

alter system set
   log_archive_dest_1='location=/u01/app/oracle/oradata/BLACK/archivelogs';

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /u01/app/oracle/oradata/BLACK/archivelogs
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence	       17

exit

rman target /

host 'mkdir -p /u01/app/oracle/backup/BLACK';

configure channel device type disk
   format '/u01/app/oracle/backup/BLACK/%U';

configure controlfile autobackup on;

configure controlfile autobackup
   format for device type disk
   to '/u01/app/oracle/backup/BLACK/%F';

backup database
   plus archivelog delete all input;

exit;

Supprimer le tablespace X

Vous l’aurez compris, pour cet exemple, vous devez supprimer le tablespace X. Là encore rien de bien complexe; on en profite pour vérifier que la table que contient le tablespace est supprimée :

sqlplus / as sysdba

col id format 99
select id from demo.x;

 ID
---
  1

drop tablespace x including contents and datafiles;
select id from demo.x;

select id from demo.x
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

Déterminer le SCN de la commande DROP TABLESPACE

Vous pourrez facilement déterminer l’heure et même l’archivelog ou le redolog correspondant à la commande DROP TABLESPACE en regardant le fichier alert.log. Pour enrichir un peu cet article, vous trouverez ci-dessous un script qui utilise LogMiner et qui vous donne avec précision le SCN de la commande. Pour cela il scrute la commande est dans les 10 derniers archivelogs de la base de données s’ils sont en ligne. Là encore, n’hésitez pas à modifier ce script pour vos besoins:

select thread#, max(sequence#)
   from v$archived_log
  group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
      1             37

declare
  xopts binary_integer;
  duplicate_logfile exception;
  pragma exception_init(duplicate_logfile,-1289);
begin
   xopts := dbms_logmnr.new;
  for i in (select min(member) name, group#
                  from v$logfile
                 group by group#) loop
     begin
        dbms_logmnr.add_logfile(
           logfilename => i.name,
           options => xopts);
     exception when duplicate_logfile then null;
     end;
     xopts := dbms_logmnr.addfile;
  end loop;

  for j in (select thread#, max(sequence#) sequence#
              from v$archived_log
             group by thread#) loop
     for i in (select name from v$archived_log
                where thread#=j.thread#
                  and sequence#>=j.sequence#-10
                  and deleted='NO') loop
        begin
           dbms_logmnr.add_logfile(
              logfilename => i.name,
              options => xopts);
        exception when duplicate_logfile then null;
        end;
     end loop;
  end loop;
end;
/

exec dbms_logmnr.start_logmnr( options => -
     dbms_logmnr.dict_from_online_catalog);

col previous_scn format 999999999999 new_value previous_scn
select sql_redo, scn-1 previous_scn
   from v$logmnr_contents
  where operation='DDL'
    and  lower(sql_redo) like '%drop%tablespace%'
  order by previous_scn;

prompt &&previous_scn
1047545

Restaurer le tablespace X

Comme attendu, restaurer le tablespace s’avère extrêmement simple : c’est tout l’objet de la modification de Oracle 11.2. Il suffit d’une seule commande RMAN RECOVER comme ci-dessous; la clause auxiliary destination permet de fixer la destination des fichiers de la base de données auxiliaire. Celle-ci est supprimée à la fin de l’opération :

exit

rman target /

host 'mkdir -p /u01/app/oracle/oradata/X';

recover tablespace x
  until scn 1047545
  auxiliary destination '/u01/app/oracle/oradata/X';

exit;

sqlplus / as sysdba

col id format 99
select id from demo.x;
*
ERROR at line 1:
ORA-00942: table or view does not exist

Vous attendiez-vous à retrouver votre table ? Non, ce n’est pas le cas ! Pourquoi ? Et bien parce que la commande drop tablespace including contents and datafiles supprime implicitement avec des ordres DDL le contenu du tablespace avant de le supprimer; pour vous en persuadez relancez le script LogMiner et changez la requête associées; voilà ce que je trouve dans mon cas :

col previous_scn format 999999999999 new_value previous_scn
select sql_redo, scn-1 previous_scn
   from v$logmnr_contents
  where operation='DDL'
    and  lower(sql_redo) like '%drop%tablespace%'
  order by previous_scn;

SQL_REDO
 --------------------------------------------------
 PREVIOUS_SCN
 -------------
 drop table "DEMO"."X" cascade constraints purge;
 1047524

 drop tablespace x including contents and datafiles;
 1047545

Il aurait donc fallut en fait revenir au SCN 1047424 pour retrouver la table demo.X.

D’un autre côté et comme vous pouvez le constater ci-dessous, RMAN abat un gros travail. Si le tablespace existait encore, il vérifierait qu’il est Self-Content pour utiliser les tablespace transportable; dans notre cas, il constate simplement que le tablespace a été supprimé mais effectue tout le process du tablespace transportable :

Starting recover at 10-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='axsd'

initialization parameters used for automatic instance:
db_name=BLACK
db_unique_name=axsd_tspitr_BLACK
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/oradata/X
log_archive_dest_1='location=/u01/app/oracle/oradata/X'
#No auxiliary parameter file used

starting up automatic instance BLACK

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created

List of tablespaces that have been dropped from the target database:
Tablespace x

contents of Memory Script:
{
# set requested point in time
set until  scn 1047545;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 10-SEP-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=59 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/BLACK/c-380670392-20090910-02
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/BLACK/c-380670392-20090910-02 tag=TAG20090910T010841
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/X/BLACK/controlfile/o1_mf_5bjfn7tp_.ctl
Finished restore at 10-SEP-09

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  scn 1047545;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  6 to
 "/u01/app/oracle/oradata/BLACK/x01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-SEP-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/BLACK/x01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/BLACK/0pkorpqm_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/BLACK/0pkorpqm_1_1 tag=TAG20090910T010734
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 10-SEP-09

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=697166053 file name=/u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_system_5bjfng3y_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=697166053 file name=/u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_undotbs1_5bjfng5c_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=697166053 file name=/u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_sysaux_5bjfng4s_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1047545;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "X", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 10-SEP-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/oradata/BLACK/archivelogs/1_38_696476410.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/BLACK/0qkorpso_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/BLACK/0qkorpso_1_1 tag=TAG20090910T010840
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/X/1_37_696476410.dbf thread=1 sequence=37
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/X/1_37_696476410.dbf RECID=29 STAMP=697166054
archived log file name=/u01/app/oracle/oradata/BLACK/archivelogs/1_38_696476410.dbf thread=1 sequence=38
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-SEP-09

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  X read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/X''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/X''";
}
executing Memory Script

sql statement: alter tablespace  X read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/X''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/X''

Performing export of metadata...
 EXPDP> Starting "SYS"."TSPITR_EXP_axsd":
 EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 EXPDP> Master table "SYS"."TSPITR_EXP_axsd" successfully loaded/unloaded
 EXPDP> ******************************************************************************
 EXPDP> Dump file set for SYS.TSPITR_EXP_axsd is:
 EXPDP>   /u01/app/oracle/oradata/X/tspitr_axsd_27997.dmp
 EXPDP> ******************************************************************************
 EXPDP> Datafiles required for transportable tablespace X:
 EXPDP>   /u01/app/oracle/oradata/BLACK/x01.dbf
 EXPDP> Job "SYS"."TSPITR_EXP_axsd" successfully completed at 01:14:50
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
 IMPDP> Master table "SYS"."TSPITR_IMP_axsd" successfully loaded/unloaded
 IMPDP> Starting "SYS"."TSPITR_IMP_axsd":
 IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 IMPDP> Job "SYS"."TSPITR_IMP_axsd" successfully completed at 01:15:04
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  X read write';
sql 'alter tablespace  X offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  X read write

sql statement: alter tablespace  X offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_temp_5bjfpcpz_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/onlinelog/o1_mf_3_5bjfp9qz_.log deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/onlinelog/o1_mf_2_5bjfp8pg_.log deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/onlinelog/o1_mf_1_5bjfp7rj_.log deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_sysaux_5bjfng4s_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_undotbs1_5bjfng5c_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/datafile/o1_mf_system_5bjfng3y_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/X/BLACK/controlfile/o1_mf_5bjfn7tp_.ctl deleted
Finished recover at 10-SEP-09

Supprimer la configuration

Vous apprécierez sans doute cette fonctionnalité si vous consolider vos bases de données pour plusieurs applications. En attendant, voici le script qui supprime l’ensemble du test de votre configuration :

sqlplus / as sysdba
drop user demo cascade;
drop tablespace x including contents and datafiles;
alter system reset
   log_archive_dest_1 scope=spfile sid='*';

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;
exit

rman target /
delete noprompt backup;
delete noprompt archivelog all;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
exit