Skip to content

rman_switchback_from_fra

Jump to bottom
PhilippeLeroux edited this page Oct 8, 2018 · 3 revisions

Table of contents

Image copy, switchback avec une base OMF

Mon db_create_file_dest vaut '/u02/database'.

La commande backup as copy datafile 16 format 'XX'; échoue si le nom du fichier est OMF.

Ne pas passer de format ne fait rien il faut utiliser la clause to destination à la place.

Astuce trouvée ici

Corruption d'un datafile

Lister les datafiles.
oracle@srvr2dbfs01:R2DBFS:db> sqlplus /@syspdb666 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 23 11:18:34 2017

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@pdb666> @lstbs

PDB name       FILE_ID TABLESPACE_NAME                          MB STATUS
--------- ------------ ------------------------------ ------------ ---------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
PDB666              13 SYSTEM                                  250 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_system_dyvckr6m_.dbf

					14 SYSAUX                                  350 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_sysaux_dyvckr90_.dbf

					15 UNDOTBS1                                100 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_undotbs1_dyvckr93_.dbf

					16 DEMOS                                 1 300 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvckr99_.dbf




4 lignes selectionnees.
Corruption du datafile 16, celui du tablespace DEMOS
oracle@srvr2dbfs01:R2DBFS:db> dd of=/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvckr99_.dbf if=/dev/zero bs=8192 count=4000
4000+0 records in
4000+0 records out
32768000 bytes (33 MB) copied, 0.0191436 s, 1.7 GB/s
Lecture de la table region du schéma hr 
oracle@srvr2dbfs01:R2DBFS:db> sqlplus hr/HR@pdb666_oci

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 23 11:23:11 2017

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Heure de la derniere connexion reussie : Lun. Oct.  23 2017 10:41:28 +02:00

Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

hr@pdb666> select count(*) from regions;
select count(*) from regions
	   *
ERREUR a la ligne 1 :
ORA-01578: bloc de donnees ORACLE altere (fichier # 16, bloc # 139)
ORA-01110: fichier de donnees 16 :
'/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvckr99_.dbf'

La visualisation de l'alerlog montre l'étendu des dégâts.

Restauration du tablespace DEMOS

Switch du tablespace DEMOS dans la FRA
oracle@srvr2dbfs01:R2DBFS:db> rman target /@syspdb666

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 23 11:26:35 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: R2DBFS:PDB666 (DBID=3748971997)

RMAN> alter tablespace demos offline;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/23/2017 11:26:53
ORA-01122: echec de controle de verification pour le fichier BdD 16
ORA-01110: fichier de donnees 16 : '/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvckr99_.dbf'
ORA-01210: l'en-tete du fichier de donnees a un support altere

RMAN> alter database datafile 16 offline;

Statement processed

RMAN> switch datafile 16 to copy;

datafile 16 switched to datafile copy "/u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvcq911_.dbf"

RMAN> recover datafile 16;

Starting recover at 23-OCT-2017 11:28:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00019: /u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvcq911_.dbf
channel ORA_DISK_1: reading from backup piece /u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/backupset/2017_10_23/o1_mf_nnnd1_INCR_FOR_IMG_COPY_dyvdnqd9_.bkp
channel ORA_DISK_1: piece handle=/u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/backupset/2017_10_23/o1_mf_nnnd1_INCR_FOR_IMG_COPY_dyvdnqd9_.bkp tag=INCR_FOR_IMG_COPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-OCT-2017 11:28:30

A ce stade le tablespace DEMOS est mis online et est disponible pour les utilisateurs, sauf qu'il réside dans la FRA ce qui n'est pas forcément souhaitable sur le long terme.

Switch du tablespace DEMOS dans le répertoire des DATA.

Il faut passer le tablespace offline, puis :

RMAN> backup as copy datafile 16 to destination '/u02/database/';

Starting backup at 23-OCT-2017 11:33:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvcq911_.dbf
output file name=/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvfvd88_.dbf tag=TAG20171023T113329 RECID=23 STAMP=958131244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 23-OCT-2017 11:34:05

RMAN> alter tablespace demos online;

Statement processed

Tous les datafiles sont de nouveau dans /u02/database.

La table region du schéma hr est de nouveau lisible
oracle@srvr2dbfs01:R2DBFS:db> sqlplus hr/HR@pdb666_oci

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 23 11:38:51 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Heure de la derniere connexion reussie : Lun. Oct.  23 2017 11:23:11 +02:00

Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

hr@pdb666> select count(*) from regions;

    COUNT(*)
------------
           4

1 ligne selectionnee.
Visualisation des datafiles
oracle@srvr2dbfs01:R2DBFS:db> sqlplus /@syspdb666 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 23 11:39:59 2017

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@pdb666> @lstbs

PDB name       FILE_ID TABLESPACE_NAME                          MB STATUS
--------- ------------ ------------------------------ ------------ ---------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
PDB666              13 SYSTEM                                  250 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_system_dyvckr6m_.dbf

					14 SYSAUX                                  350 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_sysaux_dyvckr90_.dbf

					15 UNDOTBS1                                100 AVAILABLE
/u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_undotbs1_dyvckr93_.dbf

					16 DEMOS                                 1 300 AVAILABLE
/u03/recovery/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/o1_mf_demos_dyvcq911_.dbf




4 lignes selectionnees.

Le nom du datafile a changé de o1_mf_demos_dyvckr99_.dbf à o1_mf_demos_dyvcq911_.dbf, mais il est toujours présent sur le disque :

oracle@srvr2dbfs01:R2DBFS:db> cd /u02/database/R2DBFS/5C33B5EBEFEE1A95E05364F0FAC0C30D/datafile/
oracle@srvr2dbfs01:R2DBFS:datafile> ll
total 2080128
-rw-rw---- 1 oracle oinstall   67117056 Oct 23 11:17 o1_mf_temp_dyvckr96_.dbf
-rw-rw---- 1 oracle oinstall   32768000 Oct 23 11:21 o1_mf_demos_dyvckr99_.dbf
-rw-rw---- 1 oracle oinstall 1363156992 Oct 23 11:34 o1_mf_demos_dyvfvd88_.dbf
-rw-rw---- 1 oracle oinstall  367009792 Oct 23 11:41 o1_mf_sysaux_dyvckr90_.dbf
-rw-rw---- 1 oracle oinstall  104865792 Oct 23 11:55 o1_mf_undotbs1_dyvckr93_.dbf
-rw-rw---- 1 oracle oinstall  262152192 Oct 23 11:56 o1_mf_system_dyvckr6m_.dbf
oracle@srvr2dbfs01:R2DBFS:datafile> ll *demo*
-rw-rw---- 1 oracle oinstall   32768000 Oct 23 11:21 o1_mf_demos_dyvckr99_.dbf
-rw-rw---- 1 oracle oinstall 1363156992 Oct 23 11:34 o1_mf_demos_dyvfvd88_.dbf

Il faut donc le supprimer :

oracle@srvr2dbfs01:R2DBFS:datafile> rm o1_mf_demos_dyvckr99_.dbf

Sauvegarde de la base :

oracle@srvr2dbfs01:R2DBFS:db> ./image_copy_backup.sh 
12h11> cd $HOME/plescripts/db/rman

# =========================================================================================================
12h11> sqlplus -s sys/Oracle12 as sysdba
[ output skipped ]

# =========================================================================================================
# Espace disque avant backup :
Filesystem                       Size  Used Avail Use% Mounted on
/dev/mapper/vgorcl-lvorcl         10G  7.4G  2.7G  74% /u01
/dev/mapper/vgoradata-lvoradata   16G  8.2G  7.6G  52% /u02
/dev/mapper/vgorafra-lvorafra     16G  9.6G  6.2G  61% /u03

# Espace disque après backup :
12h12> df -h /u01 /u02 /u03
Filesystem                       Size  Used Avail Use% Mounted on
/dev/mapper/vgorcl-lvorcl         10G  7.4G  2.7G  74% /u01
/dev/mapper/vgoradata-lvoradata   16G  8.2G  7.6G  52% /u02
/dev/mapper/vgorafra-lvorafra     16G  9.7G  6.1G  62% /u03

# script image_copy_backup.sh running time : 1mn00s

Résumer des commandes rman pour effectuer la restauration d'un datafile

alter database datafile 16 offline;

switch datafile 16 to copy;

recover datafile 16;

alter database datafile 16 online;

Plus tard lors d'une période adaptée :

alter database datafile 16 offline;

backup as copy datafile 16 to destination '/u02/database/';

switch datafile 16 to copy;

recover datafile 16;

alter tablespace demos online;

Puis penser à supprimer l'ancien fichier.

Clone this wiki locally