Por Joel Perez , Mahir M. Quluzade (OCE) & Sebastián D'Alessandro (OCE)
Publicado en Mayo 2015
Índice:
Veamos el tercero de los ejemplos para duplicar una base de datos con RMAN en modo activo.
Antes de continuar con la lectura le aconsejamos leer la (parte I) de esta serie para estar seguro de comprender la teoría relacionada con estos artículos. Si preparó su host destino como se indica en los 7 pasos descritos en la (parte 1), entonces podrá duplicar la base de datos utilizando RMAN.
Los “backups multisection” de RMAN brindan la posibilidad de llevar a cabo resguardos de manera más rápida realizando backup de datafiles muy grandes en modo paralelo. Múltiples piezas de backup (“backup pieces”) son creadas escribiendo cada una de ellas por un canal separado. A partir de Oracle Database 12c Release 1 (12.1), también se puede utilizar “multisection backup sets” para transferir los archivos de origen requeridos para la duplicación activa de base de datos (“active database duplication”).
Se utiliza la clausula SECTION SIZE en el comando DUPLICATE para crear los backupset en “multisection” que pueden ser utilizados para la “active database duplication”. El siguiente comando crea backup sets en multisección, con un tamaño de 400MB por pieza (“backup piece”).
En el siguiente ejemplo estamos trabajando inicialmente como base de datos origen con la CDB llamada “prmcdb” que será duplicada en la CDB llamada “dupcdb”como ocurre en la tercera parte de esta serie de artículos. En este caso usamos el comando DUPLICATE de RMAN con las siguientes opciones:
Opción de duplicación | CDB |
Tipo de duplicación | Active Database Duplication |
Método de duplicación | Pull Based |
Tipo de backup utilizado | Backupsets |
Paralelismo | SECTION SIZE 400M |
Número de “Target Channels” | 1 |
Número de “Auxiliary Channels” | 2 |
A continuación se muestra el comando DUPLICATE de RMAN para este caso:
RMAN>run
{
allocate channel c1 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
duplicate target database to dupcdb
from active database
spfile
parameter_value_convert ='prmcdb','dupcdb'
set db_file_name_convert ='prmcdb','dupcdb'
set log_file_name_convert ='prmcdb','dupcdb'
section size 400M;
}
Descripción del código:
Como podemos ver en el código de arriba, utilizamos la opción SECTION SIZE 400M dentro del comando RMAN DUPLICATE.Esto significa que RMAN utilizará backup sets para la duplicación de la base de datos origen y además creará múltiples piezas de backup (“backup pieces”) de alrededor de 400M.
En este caso el número de “target channels” (1) es menor que el de “auxiliary channels” (2) por tal motivo la ejecución de active database duplication se realizará utilizando el método Pull-Based. También utilizamos para este caso los parámetros parameter_value_convert y db{log}_file_name_convert en el comando duplicate. El uso de estos parámetros está explicado en la primera parte (parte 1) de esta serie de artículos.
Veamos este código en acción:
[oracle@oel62-ora12c-prm ~]$ rman
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Apr 8 12:50:13 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/*****@prmcdb
connected to target database: PRMCDB (DBID=2508276746)
RMAN> connect auxiliary sys/*****@dupcdb
connected to auxiliary database: DUPCDB (not mounted)
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Apr 8 17:20:01 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> run
2> {
3> allocate channel c1 device type disk;
4>
5> allocate auxiliary channel a1 device type disk;
6> allocate auxiliary channel a2 device type disk;
7>
8> duplicate target database to dupcdb
9> from active database
10> spfile
11> parameter_value_convert='prmcdb','dupcdb'
12> set db_file_name_convert='prmcdb','dupcdb'
13> set log_file_name_convert='prmcdb','dupcdb'
14> section size 400M;
15> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=43 device type=DISK
allocated channel: a1
channel a1: SID=20 device type=DISK
allocated channel: a2
channel a2: SID=21 device type=DISK
Starting Duplicate Db at 08-APR-14
current log archived
contents of Memory Script:
{
restore clone from service 'prmcdb' spfile to
'/u01/app/oracle/product/12.1.0/dbhome/dbs/spfiledupcdb.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome/dbs/spfiledupcdb.ora''";
}
executing Memory Script
Starting restore at 08-APR-14
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/dbhome/dbs/spfiledupcdb.ora
channel a1: restore complete, elapsed time: 00:00:01
Finished restore at 08-APR-14
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome/dbs/spfiledupcdb.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPCDB'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/dupcdb/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/dupcdb/control01.ctl'', ''/u01/app/oracle/fra/dupcdb/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=dupcdbXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''prmcdb'', ''dupcdb'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''prmcdb'', ''dupcdb'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPCDB'' comment= ''duplicate'' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/dupcdb/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/dupcdb/control01.ctl'', ''/u01/app/oracle/fra/dupcdb/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=dupcdbXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''prmcdb'', ''dupcdb'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''prmcdb'', ''dupcdb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1219260416 bytes
Fixed Size 2287768 bytes
Variable Size 838862696 bytes
Database Buffers 369098752 bytes
Redo Buffers 9011200 bytes
allocated channel: a1
channel a1: SID=19 device type=DISK
allocated channel: a2
channel a2: SID=20 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PRMCDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPCDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'prmcdb' primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PRMCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1219260416 bytes
Fixed Size 2287768 bytes
Variable Size 838862696 bytes
Database Buffers 369098752 bytes
Redo Buffers 9011200 bytes
allocated channel: a1
channel a1: SID=7 device type=DISK
allocated channel: a2
channel a2: SID=20 device type=DISK
Starting restore at 08-APR-14
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/dupcdb/control01.ctl
output file name=/u01/app/oracle/fra/dupcdb/control02.ctl
Finished restore at 08-APR-14
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/u01/app/oracle/oradata/dupcdb/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/dupcdb/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/dupcdb/undotbs01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/dupcdb/pdbseed/system01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/dupcdb/users01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/dupcdb/pdbseed/sysaux01.dbf";
set newname for datafile 8 to
"/u01/app/oracle/oradata/dupcdb/prmpdb01/system01.dbf";
set newname for datafile 9 to
"/u01/app/oracle/oradata/dupcdb/prmpdb01/sysaux01.dbf";
set newname for datafile 10 to
"/u01/app/oracle/oradata/dupcdb/prmpdb01/prmpdb01_users01.dbf";
set newname for datafile 28 to
"/u01/app/oracle/oradata/dupcdb/prmpdb02/system01.dbf";
set newname for datafile 29 to
"/u01/app/oracle/oradata/dupcdb/prmpdb02/sysaux01.dbf";
set newname for datafile 30 to
"/u01/app/oracle/oradata/dupcdb/prmpdb02/prmpdb02_users01.dbf";
restore
from service 'prmcdb' section size
400 m clone database
;
sql 'alter system archive log current';
}
executing Memory Script
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-APR-14
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/dupcdb/system01.dbf
channel a1: restoring section 1 of 2
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00003 to /u01/app/oracle/oradata/dupcdb/sysaux01.dbf
channel a2: restoring section 1 of 2
channel a1: restore complete, elapsed time: 00:01:05
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/dupcdb/system01.dbf
channel a1: restoring section 2 of 2
channel a2: restore complete, elapsed time: 00:01:05
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00003 to /u01/app/oracle/oradata/dupcdb/sysaux01.dbf
channel a2: restoring section 2 of 2
channel a1: restore complete, elapsed time: 00:01:15
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00004 to /u01/app/oracle/oradata/dupcdb/undotbs01.dbf
channel a1: restoring section 1 of 1
channel a2: restore complete, elapsed time: 00:01:16
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00005 to /u01/app/oracle/oradata/dupcdb/pdbseed/system01.dbf
channel a2: restoring section 1 of 1
channel a1: restore complete, elapsed time: 00:00:26
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00006 to /u01/app/oracle/oradata/dupcdb/users01.dbf
channel a1: restoring section 1 of 1
channel a1: restore complete, elapsed time: 00:00:03
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00007 to /u01/app/oracle/oradata/dupcdb/pdbseed/sysaux01.dbf
channel a1: restoring section 1 of 2
channel a2: restore complete, elapsed time: 00:01:06
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00007 to /u01/app/oracle/oradata/dupcdb/pdbseed/sysaux01.dbf
channel a2: restoring section 2 of 2
channel a2: restore complete, elapsed time: 00:00:55
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00008 to /u01/app/oracle/oradata/dupcdb/prmpdb01/system01.dbf
channel a2: restoring section 1 of 1
channel a1: restore complete, elapsed time: 00:01:38
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00009 to /u01/app/oracle/oradata/dupcdb/prmpdb01/sysaux01.dbf
channel a1: restoring section 1 of 2
channel a2: restore complete, elapsed time: 00:01:03
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00009 to /u01/app/oracle/oradata/dupcdb/prmpdb01/sysaux01.dbf
channel a2: restoring section 2 of 2
channel a1: restore complete, elapsed time: 00:01:31
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00010 to /u01/app/oracle/oradata/dupcdb/prmpdb01/prmpdb01_users01.dbf
channel a1: restoring section 1 of 1
channel a1: restore complete, elapsed time: 00:00:03
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00028 to /u01/app/oracle/oradata/dupcdb/prmpdb02/system01.dbf
channel a1: restoring section 1 of 1
channel a2: restore complete, elapsed time: 00:00:53
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00029 to /u01/app/oracle/oradata/dupcdb/prmpdb02/sysaux01.dbf
channel a2: restoring section 1 of 2
channel a1: restore complete, elapsed time: 00:01:01
channel a1: starting datafile backup set restore
channel a1: using network backup set from service prmcdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00029 to /u01/app/oracle/oradata/dupcdb/prmpdb02/sysaux01.dbf
channel a1: restoring section 2 of 2
channel a2: restore complete, elapsed time: 00:01:20
channel a2: starting datafile backup set restore
channel a2: using network backup set from service prmcdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00030 to /u01/app/oracle/oradata/dupcdb/prmpdb02/prmpdb02_users01.dbf
channel a2: restoring section 1 of 1
channel a2: restore complete, elapsed time: 00:00:08
channel a1: restore complete, elapsed time: 00:00:51
Finished restore at 08-APR-14
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'prmcdb'
archivelog from scn 2028233;
switch clone datafile all;
}
executing Memory Script
Starting restore at 08-APR-14
channel a1: starting archived log restore to default destination
channel a1: using network backup set from service prmcdb
channel a1: restoring archived log
archived log thread=1 sequence=67
channel a2: starting archived log restore to default destination
channel a2: using network backup set from service prmcdb
channel a2: restoring archived log
archived log thread=1 sequence=68
channel a1: restore complete, elapsed time: 00:00:03
channel a2: restore complete, elapsed time: 00:00:03
Finished restore at 08-APR-14
datafile 1 switched to datafile copy
input datafile copy RECID=27 STAMP=844363740 file name=/u01/app/oracle/oradata/dupcdb/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=28 STAMP=844363740 file name=/u01/app/oracle/oradata/dupcdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=29 STAMP=844363741 file name=/u01/app/oracle/oradata/dupcdb/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=844363742 file name=/u01/app/oracle/oradata/dupcdb/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=31 STAMP=844363742 file name=/u01/app/oracle/oradata/dupcdb/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=32 STAMP=844363743 file name=/u01/app/oracle/oradata/dupcdb/pdbseed/sysaux01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=33 STAMP=844363744 file name=/u01/app/oracle/oradata/dupcdb/prmpdb01/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=34 STAMP=844363744 file name=/u01/app/oracle/oradata/dupcdb/prmpdb01/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=35 STAMP=844363745 file name=/u01/app/oracle/oradata/dupcdb/prmpdb01/prmpdb01_users01.dbf
datafile 28 switched to datafile copy
input datafile copy RECID=36 STAMP=844363745 file name=/u01/app/oracle/oradata/dupcdb/prmpdb02/system01.dbf
datafile 29 switched to datafile copy
input datafile copy RECID=37 STAMP=844363746 file name=/u01/app/oracle/oradata/dupcdb/prmpdb02/sysaux01.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=38 STAMP=844363746 file name=/u01/app/oracle/oradata/dupcdb/prmpdb02/prmpdb02_users01.dbf
contents of Memory Script:
{
set until scn 2028681;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 08-APR-14
starting media recovery
archived log for thread 1 with sequence 67 is already on disk as file /u01/app/oracle/fra/DUPCDB/archivelog/2014_04_08/o1_mf_1_67_9n7tw914_.arc
archived log for thread 1 with sequence 68 is already on disk as file /u01/app/oracle/fra/DUPCDB/archivelog/2014_04_08/o1_mf_1_68_9n7tw9fy_.arc
Oracle instance started
Total System Global Area 1219260416 bytes
Fixed Size 2287768 bytes
Variable Size 838862696 bytes
Database Buffers 369098752 bytes
Redo Buffers 9011200 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPCDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPCDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/08/2014 17:29:42
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 66 and starting SCN of 2027471 found to restore
Recovery Manager complete.
Nota Importante: Como se observa en la última salida, recibimos un error. Este es un bug de la versión Oracle Database 12c (12.1.0.1) y fue solucionado con el patch set 12.1.0.2. Como probablemente ya sepa, la versión de Oracle Database 12c Release 1 (12.1.0.2) fue liberada el 24 de Julio de 2014.
Usted puede probar este caso con versión 12c Realese 1 (12.1.0.2) sin que se produzca el error.
Joel Pérez es un experto DBA (Oracle ACE Director, OCM Cloud Admin. & OCM11g) con más de 15 años de experiencia real en el mundo de tecnología Oracle, especializado en diseño e implementación de soluciones de: Cloud, Alta disponibilidad, Recuperación contra desastres, Upgrades, Replicación y toda área relacionada con bases de datos Oracle. Consultor Internacional con trabajos, conferencias y actividades relacionadas en más de 50 países alrededor del mundo. Habitual Orador en eventos Oracle alrededor del mundo como: OTN LAD, OTN MENA, OTN APAC y más. Joel se ha caracterizado siempre por ser un pionero en materia de tecnología Oracle desde los inicios de su carrera siendo el primer latinoamericano en ser nombrado "OTN Expert" en el año 2003, uno de los primeros Oracle ACE en el programa ACE en el año 2004, unos de los primeros OCP Cloud en el mercado global en el año 2013 y como uno de los mayores logros de su carrera, recientemente en el 2014 fue honorificado como uno de los primeros "OCM Database Cloud Administrator" del mundo. En la actualidad Joel Pérez esta radicado en el continente de Asia con base en Beijing, China llevando a cabo operaciones profesionales como “Chief Technologist & MAA, HA Arquitect” para Yunhe Enmo (Beijing) Technology Co. Ltd.
http://education.oracle.com/education/otn/JoelPerez.htm
Mahir M. Quluzade es un Senior DBA con mas de 10 anos de experiencia en bases de datos Oracle con especial foco en "High Availability" & "Disaster Recovery Solutions (RAC, Data Guard, RMAN,…)". Mahir actualmente trabaja en el "Central Bank of the Republic of Azerbaijan". El es OCP DBA. Mahir es frecuente orador en el "Azerbaijan Oracle User Group (AZEROUG)".
Sebastián D'Alessandro es un Senior DBA con más de 12 años de experiencia en tecnología Oracle, focalizado principalmente en seguridad de base de datos, soluciones de alta disponibilidad, disaster recovery y virtualización. Actualmente desarrolla su actividad como consultor e instructor de manera independiente.
Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.