Suite de l’article « Oracle 12C Multitenant Part 1 : export / import 11G sur une PDB 12C » : ici
Nous avons vu précédemment la création d’une pluggable database, et son alimentation via import Datapump. Attaquons nous maintenant à RMAN.
En 12c Enterprise, option Multitenant, Rman permet de sauvegarder 3 éléments distincts : La CDB,les PDBs ou les métadatas.
Dans cet article, nous allons étudier la partie Pluggable database, à mon sens la plus courante dans le monde de la production.
Prenons le cas d’un backup complet de notre CDB qui inclut donc toutes les PDBs :
Petit passage en archivelog avant toutes choses :
SQL> alter system set log_archive_dest='/oradata/CDB/arch' scope=both ;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 1.0503E+10 bytes
Fixed Size 5294664 bytes
Variable Size 5737809336 bytes
Database Buffers 4731174912 bytes
Redo Buffers 28258304 bytes
Database mounted.
SQL>alter database archivelog ;
Database altered.
SQL> alter database open;
Database altered.
SQL>alter pluggable database all open read write ;
Pluggable database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/CDB/arch
Oldest online log sequence 208
Next log sequence to archive 210
Current log sequence 210
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 MYPDB1 READ WRITE NO
Go pour un petit backup full
RMAN> sql 'alter system switch logfile';
using target database control file instead of recovery catalog
sql statement: alter system switch logfile
RMAN> backup database format '/oratmp/Backup/bkpfull_svg_DB_on%_%t%U.bkp' plus archivelog ;
Starting backup at 18-SEP-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=423 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=210 RECID=1 STAMP=890684562
input archived log thread=1 sequence=211 RECID=2 STAMP=890684585
input archived log thread=1 sequence=212 RECID=3 STAMP=890684599
input archived log thread=1 sequence=213 RECID=4 STAMP=890685090
input archived log thread=1 sequence=214 RECID=5 STAMP=890685099
..
..
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oracle/product/12.1.0.2/dbs/0gqhesqa_1_1 tag=TAG20150918T084810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-SEP-15
Starting backup at 18-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/CDB/undotbs01.dbf
input datafile file number=00001 name=/oradata/CDB/system01.dbf
input datafile file number=00003 name=/oradata/CDB/sysaux01.dbf
input datafile file number=00006 name=/oradata/CDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_svg_DB_on%_8907292970hqhesqh_1_1.bkp tag=TAG20150918T084817 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata/CDB/MYPDB1/tbs_mrsadmin_01.dbf
input datafile file number=00008 name=/oradata/CDB/MYPDB1/sysaux01.dbf
input datafile file number=00007 name=/oradata/CDB/MYPDB1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_svg_DB_on%_8907293040iqhesqo_1_1.bkp tag=TAG20150918T084817 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/oradata/CDB/MYPDB1/sysaux01.dbf
input datafile file number=00015 name=/oradata/CDB/MYPDB1/tbs_MYPDB1_01.dbf
input datafile file number=00013 name=/oradata/CDB/MYPDB1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_svg_DB_on%_8907293070jqhesqr_1_1.bkp tag=TAG20150918T084817 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/oradata/CDB/pdbseed/sysaux01.dbf
input datafile file number=00002 name=/oradata/CDB/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_svg_DB_on%_8907293100kqhesqu_1_1.bkp tag=TAG20150918T084817 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-SEP-15
Starting backup at 18-SEP-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=267 RECID=58 STAMP=890729313
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oracle/product/12.1.0.2/dbs/0lqhesr2_1_1 tag=TAG20150918T084833 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-SEP-15
Starting Control File and SPFILE Autobackup at 18-SEP-15
piece handle=/oracle/product/12.1.0.2/dbs/c-3858261253-20150918-00 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-15
Ok, nous allons maintenant créer une petite panne « utilisateur » sur la PDB MYPDB1 :
SQL> alter session set container=MYPDB1;
Session altered.
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
5296
SQL> delete from MRS_EXECUTED_BLOCKS;
5296 rows deleted
SQL> commit;
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
0
Là, nous avons une simple erreur utilisateur… Deux choix s’offrent principalement à nous :
– La restauration complète de la PDB
ou
– La restauration incomplète, ou encore restauration d’une ou plusieurs tables dans le cas de cet article.
Voyons voir tout d’abord la restauration complète de la PDB dans laquelle se trouve la table ci dessus.
Note : Bien sur pour ce type d’opération, la PDB se doit d’être CLOSE.
RMAN> sql 'alter pluggable database MYPDB1 close immediate';
using target database control file instead of recovery catalog
sql statement: alter pluggable database MYPDB1 close immediate
RMAN> run {
set until time "to_date('18-09-2015:08:50:00', 'DD-MM-YYYY:hh24:mi:ss')";
restore pluggable database MYPDB1;
recover pluggable database MYPDB1
AUXILIARY DESTINATION '/oratmp';
}2> 3> 4> 5> 6>
Note :
En PITR, si la flash_recovery n’est pas configurée, l’utilisation du paramètre « AUXILIARY DESTINATION » est obligatoire pour effectuer le Recover.
Starting restore at 18-SEP-15
using channel ORA_DISK_1
skipping datafile 13; already restored to file /oradata/CDB/MYPDB1/system01.dbf
skipping datafile 14; already restored to file /oradata/CDB/MYPDB1/sysaux01.dbf
skipping datafile 15; already restored to file /oradata/CDB/MYPDB1/tbs_MYPDB1_01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 18-SEP-15
Starting recover at 18-SEP-15
using channel ORA_DISK_1
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='twsh'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=twsh_pitr_MYPDB1_CDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/oratmp
log_archive_dest_1='location=/oratmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 654312056 bytes
Database Buffers 2013265920 bytes
Redo Buffers 13062144 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('18-09-2015:08:50:00', 'DD-MM-YYYY:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 18-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 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 /oracle/product/12.1.0.2/dbs/c-3858261253-20150918-00
channel ORA_AUX_DISK_1: piece handle=/oracle/product/12.1.0.2/dbs/c-3858261253-20150918-00 tag=TAG20150918T084835
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oratmp/CDB/controlfile/o1_mf_bzqgo9fp_.ctl
Finished restore at 18-SEP-15
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('18-09-2015:08:50:00', 'DD-MM-YYYY:hh24:mi:ss')";
# switch to valid datafilecopies
switch clone datafile 13 to datafilecopy
"/oradata/CDB/MYPDB1/system01.dbf";
switch clone datafile 14 to datafilecopy
"/oradata/CDB/MYPDB1/sysaux01.dbf";
switch clone datafile 15 to datafilecopy
"/oradata/CDB/MYPDB1/tbs_MYPDB1_01.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
datafile 13 switched to datafile copy
input datafile copy RECID=1 STAMP=890731086 file name=/oradata/CDB/MYPDB1/system01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=2 STAMP=890731086 file name=/oradata/CDB/MYPDB1/sysaux01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=3 STAMP=890731086 file name=/oradata/CDB/MYPDB1/tbs_MYPDB1_01.dbf
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18-SEP-15
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 /oratmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oratmp/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oratmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oratmp/CDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/bkpfull_svg_DB_on%_8907292970hqhesqh_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/bkpfull_svg_DB_on%_8907292970hqhesqh_1_1.bkp tag=TAG20150918T084817
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-SEP-15
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=890731102 file name=/oratmp/CDB/datafile/o1_mf_system_bzqgogyy_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=890731102 file name=/oratmp/CDB/datafile/o1_mf_undotbs1_bzqgogyx_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=890731102 file name=/oratmp/CDB/datafile/o1_mf_sysaux_bzqgogyz_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=890731102 file name=/oratmp/CDB/datafile/o1_mf_users_bzqgogz0_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('18-09-2015:08:50:00', 'DD-MM-YYYY:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone 'MYPDB1' "alter database datafile
13 online";
sql clone 'MYPDB1' "alter database datafile
14 online";
sql clone 'MYPDB1' "alter database datafile
15 online";
sql clone "alter database datafile 6 online";
# recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
'MYPDB1' delete archivelog;
sql clone 'alter database open read only';
plsql <>;
plsql <>;
# shutdown clone before import
shutdown clone abort
plsql <>>;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
sql statement: alter database datafile 15 online
sql statement: alter database datafile 6 online
Section Recover :
Starting recover at 18-SEP-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 267 is already on disk as file /oradata/CDB/arch/1_267_890666181.dbf
archived log for thread 1 with sequence 268 is already on disk as file /oradata/CDB/arch/1_268_890666181.dbf
archived log file name=/oradata/CDB/arch/1_267_890666181.dbf thread=1 sequence=267
archived log file name=/oradata/CDB/arch/1_268_890666181.dbf thread=1 sequence=268
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-15
sql statement: alter database open read only
Oracle instance shut down
Removing automatic instance
Automatic instance removed
auxiliary instance file /oratmp/CDB/datafile/o1_mf_sysaux_bzqgogyz_.dbf deleted
auxiliary instance file /oratmp/CDB/controlfile/o1_mf_bzqgo9fp_.ctl deleted
Finished recover at 18-SEP-15
Cette log est assez intéressante … On s’aperçoit qu’RMAN va créer une instance temporaire « clone » de l’instance CDB pour effectuer ONLINE, sans aucun arrêt de l’instance principale (imaginons une prod), un recover en PITR de notre PDB.
Cela veut dire aussi en langage « stockage » qu’il faut prévoir du disque sur un FS (soit unitairement comme l’exemple ci dessus, ou soit fixe pour une Flash_recovery), et cela peut monter vite en Go en fonction de la taille de notre PDB ..
RMAN> sql 'alter pluggable database MYPDB1 open resetlogs';
sql statement: alter pluggable database MYPDB1 open resetlogs
RMAN>exit;
/oratmp/CDB[CDB]:sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 18 09:20:33 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set lines 200
SQL> select DBID,NAME,OPEN_MODE,OPEN_TIME from v$pdbs;
DBID NAME OPEN_MODE OPEN_TIME
---------- ------------------------------ ---------- ---------------------------------------------------------------------------
548741631 PDB$SEED READ ONLY 17-SEP-15 07.56.27.384 PM +02:00
888812708 MYPDB1 READ WRITE 18-SEP-15 09.20.02.391 AM +02:00
SQL> alter session set container=MYPDB1 ;
Session altered.
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
5296
=> Ok, nous avons retrouvé nos datas, restauré uniquement notre PDB sans impact sur la dispo de l’instance CDB principale, et donc de facto sur les autres PDBs (dans le cas où nous en aurions eu plusieurs), mais nous avons quand même du générer une indisponibilité de la PDB cible.
Nous aurions pu donc passer par une restauration table uniquement, puisque RMAN (en 12c) permet la restauration d’une table directement !
Mais qu’allons nous faire de Datapump alors me direz vous ? Et bien le conserver, car il est toujours utile d’avoir un dump à coté au cas ou (par expérience, avec les joies de la robotique ..)
Note :
Attention, pour Datapump sur une PDB, il faut là encore utiliser un ALIAS TNS pour le réaliser, car un datapump full de la CDB ne permet en effet pas de réaliser un export des PDBs.
Vu que nous avons restauré la PDB, il faut re-simuler une panne, on refait donc un petit backup de notre PDB, avec une gestion des archivelogs car dans ce second exemple je vais utiliser le until sequence ..
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter system switch logfile ;
System altered.
SQL> exit
RMAN> run {
backup pluggable database MYPDB1 format '/oratmp/Backup/bkpfull_MYPDB1_on%_%t%U.bkp' ;
backup archivelog all format '/oratmp/Backup/bkpfull_MYPDB1_ARCH_%t%U.bkp' ;
}
Starting backup at 18-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/oradata/CDB/MYPDB1/sysaux01.dbf
input datafile file number=00015 name=/oradata/CDB/MYPDB1/tbs_MYPDB1_01.dbf
input datafile file number=00013 name=/oradata/CDB/MYPDB1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp tag=TAG20150918T101728 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-SEP-15
Starting backup at 18-SEP-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=313 RECID=104 STAMP=890732742
input archived log thread=1 sequence=314 RECID=105 STAMP=890732754
input archived log thread=1 sequence=315 RECID=106 STAMP=890732763
input archived log thread=1 sequence=316 RECID=107 STAMP=890732769
input archived log thread=1 sequence=317 RECID=108 STAMP=890732780
input archived log thread=1 sequence=318 RECID=109 STAMP=890732790
input archived log thread=1 sequence=319 RECID=110 STAMP=890732823
input archived log thread=1 sequence=320 RECID=111 STAMP=890732835
input archived log thread=1 sequence=321 RECID=112 STAMP=890732847
input archived log thread=1 sequence=322 RECID=113 STAMP=890732861
input archived log thread=1 sequence=323 RECID=114 STAMP=890732869
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_MYPDB1_ARCH_89073465222qhf21s_1_1.bkp tag=TAG20150918T101731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=253 RECID=44 STAMP=890727333
input archived log thread=1 sequence=254 RECID=45 STAMP=890727345
input archived log thread=1 sequence=255 RECID=46 STAMP=890727375
input archived log thread=1 sequence=256 RECID=47 STAMP=890727387
input archived log thread=1 sequence=257 RECID=48 STAMP=890727402
input archived log thread=1 sequence=258 RECID=49 STAMP=890727413
input archived log thread=1 sequence=259 RECID=50 STAMP=890727421
input archived log thread=1 sequence=260 RECID=51 STAMP=890727428
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_MYPDB1_ARCH_89073465923qhf223_1_1.bkp tag=TAG20150918T101731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=366 RECID=157 STAMP=890734079
input archived log thread=1 sequence=367 RECID=158 STAMP=890734088
input archived log thread=1 sequence=368 RECID=159 STAMP=890734101
input archived log thread=1 sequence=369 RECID=160 STAMP=890734116
input archived log thread=1 sequence=370 RECID=161 STAMP=890734551
input archived log thread=1 sequence=371 RECID=162 STAMP=890734589
input archived log thread=1 sequence=372 RECID=163 STAMP=890734610
input archived log thread=1 sequence=373 RECID=164 STAMP=890734651
channel ORA_DISK_1: starting piece 1 at 18-SEP-15
channel ORA_DISK_1: finished piece 1 at 18-SEP-15
piece handle=/oratmp/Backup/bkpfull_MYPDB1_ARCH_89073466624qhf22a_1_1.bkp tag=TAG20150918T101731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-SEP-15
Starting Control File and SPFILE Autobackup at 18-SEP-15
piece handle=/oracle/product/12.1.0.2/dbs/c-3858261253-20150918-08 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-15
RMAN> exit
-> On génère une petite archivelog histoire de nous arrêter à la précédente
/home/oracle[CDB]:sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 18 09:25:55 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter system switch logfile ;
System altered.
SQL> select sequence#,to_char(completion_time,'DD-MM-YYYY HH24:MI:SS') from v$archived_log where to_char(completion_time,'DD-MM-YYYY HH24:MI:SS') > '18-09-2015 10:10:00';
SEQUENCE# TO_CHAR(COMPLETION_
---------- -------------------
370 18-09-2015 10:15:51
371 18-09-2015 10:16:29
372 18-09-2015 10:16:50
373 18-09-2015 10:17:31
374 18-09-2015 10:18:41
SQL> alter session set container=MYPDB1 ;
Session altered.
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
5296
SQL> delete from "MYPDB1"."MRS_EXECUTED_BLOCKS";
5296 rows deleted.
SQL> commit;
Commit complete.
==> Lançons donc notre restauration de cette table à partir de RMAN, en s’arrêtant à la séquence 330 qui ne contient donc pas l’opération de delete ci dessus ..
Ici, pas besoin de faire un close de la pluggable database, la restauration n’étant pas complète et étant directement sur un objet précis. Par contre, il faut spécifier sur quelle PDB les tables concernées sont stockées :
Note :
J’ai volontairement fait un DELETE et non pas un DROP TABLE, car dans le cas d’un drop table, il n’est pas nécessaire de passer par une table temporaire via la génération d’un dump, comme je le fais ci dessous. En effet, RMAN ne permet pas de restaurer le contenu de la table sur elle même, il faut passer par une table temporaire sinon cela plante.
Bien sur vous me direz que si toutes les lignes ont été supprimées, on peut dropper la table et la restaurer entièrement… oui pourquoi pas ! Mais imaginons que seules quelques lignes soient impactées, nous pourrions faire du rattrapage à partir d’une table temporaire, et éviter ainsi d’avoir à restaurer l’intégralité de notre table.
RMAN> recover table MYPDB1.MRS_EXECUTED_BLOCKS of pluggable database MYPDB1
until sequence 374
auxiliary destination '/oratmp' ;
Starting recover at 18-SEP-15
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/18/2015 11:20:05
RMAN-05063: Cannot recover specified tables
RMAN-05112: table "MYPDB1"."MRS_EXECUTED_BLOCKS" already exists
Donc on fait :
RMAN> recover table MYPDB1.MRS_EXECUTED_BLOCKS of pluggable database MYPDB1
2> until sequence 374
3> auxiliary destination '/oratmp'
4> remap table MYPDB1.MRS_EXECUTED_BLOCKS:MRS_EXECUTED_BLOCKS_TMP;
Starting recover at 18-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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='zhkk'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=zhkk_pitr_MYPDB1_CDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/oratmp
log_archive_dest_1='location=/oratmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 654312056 bytes
Database Buffers 2013265920 bytes
Redo Buffers 13062144 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# 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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 18-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 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 /oracle/product/12.1.0.2/dbs/c-3858261253-20150918-07
channel ORA_AUX_DISK_1: piece handle=/oracle/product/12.1.0.2/dbs/c-3858261253-20150918-07 tag=TAG20150918T101630
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oratmp/CDB/controlfile/o1_mf_bzqo91sv_.ctl
Finished restore at 18-SEP-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 13 to new;
set newname for clone datafile 14 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 13, 14;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oratmp/CDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /oratmp/CDB/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 18-SEP-15
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 /oratmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oratmp/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oratmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/backup_full_on%_8907345731rqhf1vd_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/backup_full_on%_8907345731rqhf1vd_1_1.bkp tag=TAG20150918T101613
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 00013 to /oratmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /oratmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp tag=TAG20150918T101728
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 18-SEP-15
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=890737881 file name=/oratmp/CDB/datafile/o1_mf_system_bzqo975w_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=890737881 file name=/oratmp/CDB/datafile/o1_mf_undotbs1_bzqo975t_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=890737881 file name=/oratmp/CDB/datafile/o1_mf_sysaux_bzqo975w_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=9 STAMP=890737881 file name=/oratmp/CDB/datafile/o1_mf_system_bzqo9p7f_.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=890737881 file name=/oratmp/CDB/datafile/o1_mf_sysaux_bzqo9p7d_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone 'MYPDB1' "alter database datafile
13 online";
sql clone 'MYPDB1' "alter database datafile
14 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "MYPDB1":"SYSTEM", "MYPDB1":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
Starting recover at 18-SEP-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 371 is already on disk as file /oradata/CDB/arch/1_371_890666181.dbf
archived log for thread 1 with sequence 372 is already on disk as file /oradata/CDB/arch/1_372_890666181.dbf
archived log for thread 1 with sequence 373 is already on disk as file /oradata/CDB/arch/1_373_890666181.dbf
archived log file name=/oradata/CDB/arch/1_371_890666181.dbf thread=1 sequence=371
archived log file name=/oradata/CDB/arch/1_372_890666181.dbf thread=1 sequence=372
archived log file name=/oradata/CDB/arch/1_373_890666181.dbf thread=1 sequence=373
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-15
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database MYPDB1 open read only';
}
executing Memory Script
sql statement: alter pluggable database MYPDB1 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/oratmp/CDB/controlfile/o1_mf_bzqo91sv_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter system set control_files = ''/oratmp/CDB/controlfile/o1_mf_bzqo91sv_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 15 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 15;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 18-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
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 00015 to /oratmp/ZHKK_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp tag=TAG20150918T101728
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-SEP-15
datafile 15 switched to datafile copy
input datafile copy RECID=12 STAMP=890737926 file name=/oratmp/ZHKK_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_bzqoc5kb_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# online the datafiles restored or switched
sql clone 'MYPDB1' "alter database datafile
15 online";
# recover and open resetlogs
recover clone database tablespace "MYPDB1":"MYPDB1", "SYSTEM", "UNDOTBS1", "SYSAUX", "MYPDB1":"SYSTEM", "MYPDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 15 online
Starting recover at 18-SEP-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 373 is already on disk as file /oradata/CDB/arch/1_373_890666181.dbf
archived log file name=/oradata/CDB/arch/1_373_890666181.dbf thread=1 sequence=373
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-15
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database MYPDB1 open';
}
executing Memory Script
sql statement: alter pluggable database MYPDB1 open
Un petit Focus sur la partie « datapump » de RMAN :
contents of Memory Script:
{
# create directory for datapump import
sql 'MYPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/oratmp''";
# create directory for datapump export
sql clone 'MYPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/oratmp''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oratmp''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oratmp''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_zhkk_Avay":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 12 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "MYPDB1"."MRS_EXECUTED_BLOCKS" 9.598 MB 438313 rows
EXPDP> Master table "SYS"."TSPITR_EXP_zhkk_Avay" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_zhkk_Avay is:
EXPDP> /oratmp/tspitr_zhkk_58836.dmp
EXPDP> Job "SYS"."TSPITR_EXP_zhkk_Avay" successfully completed at Fri Sep 18 11:12:21 2015 elapsed 0 00:00:11
Export completed
Import avec le remap table :
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_zhkk_wCBw" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_zhkk_wCBw":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "MYPDB1"."MRS_EXECUTED_BLOCKS_TMP" 9.598 MB 438313 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_zhkk_wCBw" successfully completed at Fri Sep 18 11:12:24 2015 elapsed 0 00:00:01
Et maintenant, RMAN supprime son espace de travail :
Removing automatic instance
Automatic instance removed
auxiliary instance file /oratmp/CDB/datafile/o1_mf_temp_bzqo9tv9_.tmp deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_temp_bzqo9th7_.tmp deleted
auxiliary instance file /oratmp/ZHKK_PITR_MYPDB1_CDB/onlinelog/o1_mf_3_bzqoc7g4_.log deleted
auxiliary instance file /oratmp/ZHKK_PITR_MYPDB1_CDB/onlinelog/o1_mf_2_bzqoc7dz_.log deleted
auxiliary instance file /oratmp/ZHKK_PITR_MYPDB1_CDB/onlinelog/o1_mf_1_bzqoc7cq_.log deleted
auxiliary instance file /oratmp/ZHKK_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_bzqoc5kb_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_sysaux_bzqo9p7d_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_system_bzqo9p7f_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_sysaux_bzqo975w_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_undotbs1_bzqo975t_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_system_bzqo975w_.dbf deleted
auxiliary instance file /oratmp/CDB/controlfile/o1_mf_bzqo91sv_.ctl deleted
Finished recover at 18-SEP-15
On vérifie :
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
0
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS_TMP";
COUNT(*)
----------
5296
SQL> insert into "MYPDB1"."MRS_EXECUTED_BLOCKS" select * from "MYPDB1"."MRS_EXECUTED_BLOCKS_TMP" ;
5296 rows created.
SQL> commit ;
Commit complete.
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
5296
SQL> drop table "MYPDB1"."MRS_EXECUTED_BLOCKS_TMP";
Table dropped.
Comme je le mentionnais plus haut, dans le cas d’un drop direct d’une table, l’instruction de recover est plus simple :
SQL> alter session set container=MYPDB1;
Session altered.
SQL> drop table "MYPDB1"."MRS_EXECUTED_BLOCKS";
Table dropped.
La restauration complète de la table :
RMAN> recover table MYPDB1.MRS_EXECUTED_BLOCKS of pluggable database MYPDB1
until sequence 374
auxiliary destination '/oratmp' ;2> 3>
Starting recover at 18-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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='otsm'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=otsm_pitr_MYPDB1_CDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/oratmp
log_archive_dest_1='location=/oratmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 654312056 bytes
Database Buffers 2013265920 bytes
Redo Buffers 13062144 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# 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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 18-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 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 /oracle/product/12.1.0.2/dbs/c-3858261253-20150918-07
channel ORA_AUX_DISK_1: piece handle=/oracle/product/12.1.0.2/dbs/c-3858261253-20150918-07 tag=TAG20150918T101630
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oratmp/CDB/controlfile/o1_mf_bzqoxp4w_.ctl
Finished restore at 18-SEP-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 13 to new;
set newname for clone datafile 14 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 13, 14;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oratmp/CDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /oratmp/CDB/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 18-SEP-15
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 /oratmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oratmp/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oratmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/backup_full_on%_8907345731rqhf1vd_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/backup_full_on%_8907345731rqhf1vd_1_1.bkp tag=TAG20150918T101613
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 00013 to /oratmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /oratmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp tag=TAG20150918T101728
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 18-SEP-15
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=890738541 file name=/oratmp/CDB/datafile/o1_mf_system_bzqoxvhv_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=890738541 file name=/oratmp/CDB/datafile/o1_mf_undotbs1_bzqoxvhs_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=890738541 file name=/oratmp/CDB/datafile/o1_mf_sysaux_bzqoxvhv_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=9 STAMP=890738541 file name=/oratmp/CDB/datafile/o1_mf_system_bzqoybkd_.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=890738541 file name=/oratmp/CDB/datafile/o1_mf_sysaux_bzqoybkc_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone 'MYPDB1' "alter database datafile
13 online";
sql clone 'MYPDB1' "alter database datafile
14 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "MYPDB1":"SYSTEM", "MYPDB1":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
Starting recover at 18-SEP-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 371 is already on disk as file /oradata/CDB/arch/1_371_890666181.dbf
archived log for thread 1 with sequence 372 is already on disk as file /oradata/CDB/arch/1_372_890666181.dbf
archived log for thread 1 with sequence 373 is already on disk as file /oradata/CDB/arch/1_373_890666181.dbf
archived log file name=/oradata/CDB/arch/1_371_890666181.dbf thread=1 sequence=371
archived log file name=/oradata/CDB/arch/1_372_890666181.dbf thread=1 sequence=372
archived log file name=/oradata/CDB/arch/1_373_890666181.dbf thread=1 sequence=373
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-15
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database MYPDB1 open read only';
}
executing Memory Script
sql statement: alter pluggable database MYPDB1 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/oratmp/CDB/controlfile/o1_mf_bzqoxp4w_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter system set control_files = ''/oratmp/CDB/controlfile/o1_mf_bzqoxp4w_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 15 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 15;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 18-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
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 00015 to /oratmp/OTSM_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oratmp/Backup/bkpfull_MYPDB1_on%_89073464821qhf21o_1_1.bkp tag=TAG20150918T101728
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-SEP-15
datafile 15 switched to datafile copy
input datafile copy RECID=12 STAMP=890738587 file name=/oratmp/OTSM_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_bzqozsvz_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 374 thread 1;
# online the datafiles restored or switched
sql clone 'MYPDB1' "alter database datafile
15 online";
# recover and open resetlogs
recover clone database tablespace "MYPDB1":"MYPDB1", "SYSTEM", "UNDOTBS1", "SYSAUX", "MYPDB1":"SYSTEM", "MYPDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 15 online
Starting recover at 18-SEP-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 373 is already on disk as file /oradata/CDB/arch/1_373_890666181.dbf
archived log file name=/oradata/CDB/arch/1_373_890666181.dbf thread=1 sequence=373
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-SEP-15
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database MYPDB1 open';
}
executing Memory Script
sql statement: alter pluggable database MYPDB1 open
contents of Memory Script:
{
# create directory for datapump import
sql 'MYPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/oratmp''";
# create directory for datapump export
sql clone 'MYPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/oratmp''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oratmp''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oratmp''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_otsm_qbiz":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 320 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TRIGGER
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "MYPDB1"."MRS_EXECUTED_BLOCKS" 218.4 KB 5296 rows
EXPDP> Master table "SYS"."TSPITR_EXP_otsm_qbiz" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_otsm_qbiz is:
EXPDP> /oratmp/tspitr_otsm_34847.dmp
EXPDP> Job "SYS"."TSPITR_EXP_otsm_qbiz" successfully completed at Fri Sep 18 11:23:21 2015 elapsed 0 00:00:11
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_otsm_xfFz" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_otsm_xfFz":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "MYPDB1"."MRS_EXECUTED_BLOCKS" 218.4 KB 5296 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/TRIGGER
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_otsm_xfFz" successfully completed at Fri Sep 18 11:23:24 2015 elapsed 0 00:00:01
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /oratmp/CDB/datafile/o1_mf_temp_bzqoyh6m_.tmp deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_temp_bzqoygsp_.tmp deleted
auxiliary instance file /oratmp/OTSM_PITR_MYPDB1_CDB/onlinelog/o1_mf_3_bzqozvs0_.log deleted
auxiliary instance file /oratmp/OTSM_PITR_MYPDB1_CDB/onlinelog/o1_mf_2_bzqozvqz_.log deleted
auxiliary instance file /oratmp/OTSM_PITR_MYPDB1_CDB/onlinelog/o1_mf_1_bzqozvpt_.log deleted
auxiliary instance file /oratmp/OTSM_PITR_MYPDB1_CDB/datafile/o1_mf_aedifica_bzqozsvz_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_sysaux_bzqoybkc_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_system_bzqoybkd_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_sysaux_bzqoxvhv_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_undotbs1_bzqoxvhs_.dbf deleted
auxiliary instance file /oratmp/CDB/datafile/o1_mf_system_bzqoxvhv_.dbf deleted
auxiliary instance file /oratmp/CDB/controlfile/o1_mf_bzqoxp4w_.ctl deleted
auxiliary instance file tspitr_otsm_34847.dmp deleted
Finished recover at 18-SEP-15
Et voila, nous avons retrouvé notre table complète :
SQL> select count(*) from "MYPDB1"."MRS_EXECUTED_BLOCKS";
COUNT(*)
----------
5296
Il va de soi qu’il y a plein d’autres tests à faire avec notre ami RMAN, mais cela donne déja une bonne idée de ce que la version 12c permet de faire !
Micka
dbafaq.wordpress.com 