« Oracle 11g : Analizando segmentos con DBVerifyMonitorear o no monitorear , he allí el problema »

Oracle11g : Paso a paso como utilizar el comando DUPLICATE ACTIVE DATABASE de RMAN



Esta es la situación :

JEFE : Debes generar una copia de la base de datos productiva que esta en Oracle11g desde la máquina A a la máquina B
DBA : Ok, sencillo , cuando comenzamos
JEFE : Ahora, la base de datos sólo pesa 4TB
DBA : Mmmm , que grande... bueno, donde dejamos el respaldo, en la máquina A o en la B?
JEFE : En ninguna
DBA : 0.o!!! de que me habla?
JEFE : En ninguna máquina, pues no hay espacio
DBA : ¿Nada de espacio?
JEFE : Nada.... sólo 1GB
DBA : Mmm , bueno, hacemos begin backup de los datafiles y los trasladamos de a poco
JEFE : Los datafiles, pesan mínimo 20GB
DBA : ¿Dónde firmo mi carta de renuncia?

Para que no suceda lo anterior, simplemente utilizamos un comando genial de Oracle11g, llamado el DUPLICATE ACTIVE DATABASE, que permite realizar la copia de una base de datos y generarla en la máquina que deseemos, sin necesidad de pasar por un respaldo.

Esto lo hace extremadamente poderoso y rápido.

A continuación los pasos.




1.- Se debe generar un listener, el cual contenga las entradas de la instancia nueva y claro, el origen de donde sacaremos los datos.

LISTER11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g.inmotion.cl)(PORT = 1521))
)

SID_LIST_LISTER11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = copia)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = copia)
)
)

2.- Al momento de levantar el listener, debe estar proporcionando disponibilidad a ambos servicios

[oracle@oracle11g admin]$ lsnrctl start LISTER11G

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 21-AUG-2009 12:00:03

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle11g/lister11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.inmotion.cl)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11g.inmotion.cl)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTER11G
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 21-AUG-2009 12:00:03
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle11g/lister11g/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.inmotion.cl)(PORT=1521)))
Services Summary...
Service "copia" has 1 instance(s).
Instance "copia", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle11g admin]$

3.- Se debe añadir la siguiente entrada al archivo tnsnames.ora

copia =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g.inmotion.cl)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = copia)

)
)

4.- Necesitamos generar un archivo de inicialización pfile, el cual contenga sólo un parámetro, el db_name, el resto de los parámetros serán seteados con el DUPLICATE ACTIVE DATABASE

[oracle@oracle11g dbs]$ ls -ltr initcopia.ora
-rw-r--r-- 1 oracle oinstall 14 Aug 21 12:02 initcopia.ora
[oracle@oracle11g dbs]$ more initcopia.ora
db_name=copia

5. -Se debe generar un archivo de password, dado que el DUPLICATE ACTIVE DATABASE se conecta mediante SYSDBA a la instancia remota. Como observación , la password debe ser exactamente la misma , entre la instancia de origen y la de destino.

[oracle@oracle11g dbs]$ orapwd file=orapwcopia password=oracle entries=5
[oracle@oracle11g dbs]$ ls -ltr *copia*
-rw-r--r-- 1 oracle oinstall 14 Aug 21 12:02 initcopia.ora
-rw-r----- 1 oracle oinstall 2048 Aug 21 12:06 orapwcopia
[oracle@oracle11g dbs]$

6.- Se debe iniciar la instancia auxiliar en estado NOMOUNT , en esta instancia es donde quedarán los datos de la primaria

[oracle@oracle11g dbs]$ export ORACLE_SID=copia
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 21 12:07:52 2009

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 2564096 bytes
SQL>

7.- Nos conectamos a RMAN , con la instancia primaria , la base de datos debe estar abierta

[oracle@oracle11g dbs]$ rman target sys/oracle@orcl

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Aug 21 12:09:22 2009

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

connected to target database: ORCL (DBID=1218194307)

RMAN>

8.- Nos conectamos a través de RMAN a la instancia auxiliar

RMAN> connect auxiliary sys/oracle@copia

connected to auxiliary database: COPIA (not mounted)

RMAN>

Se debe recordar que esta entrada (@copia) fue creada en el punto 3

9.- Se deben generar todos los directorios nuevos que vamos a utilizar en nuestra nueva instancia

10.- Ahora podemos ejecutar nuestro comando DUPLICATE DATABASE mediante RMAN , de la siguiente forma

run {
set newname for datafile '/u01/app/oracle/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/copia1/users01.dbf';
set newname for datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' to '/u01/app/oracle/oradata/copia2/undotbs01.dbf';
set newname for datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/copia3/sysaux01.dbf';
set newname for datafile '/u01/app/oracle/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata/copia4/system01.dbf';

duplicate target database to copia
from active database
db_file_name_convert '/u01/app/oracle/oradata/orcl' , '/u01/app/oracle/oradata/copia'
spfile parameter_value_convert = '/u01/app/oracle/admin/orcl' , '/u01/app/oracle/admin/copia'
set log_file_name_convert = '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/copia'
set audit_file_dest='/u01/app/oracle/admin/copia/adump'
set log_archive_dest_1=''
set memory_target='183001600'
set dispatchers='(PROTOCOL=TCP) (SERVICE=copiaXDB)'
set control_files='/u01/app/oracle/oradata/copia1/control01.ctl','/u01/app/oracle/oradata/copia2/control02.ctl','/u01/app/oracle/oradata/copia3/control03.ctl'
set db_recovery_file_dest_size = '2294967296';
}

11.- Mediante el anterior script, se comienza a generar la copia de la base de datos.

executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting Duplicate Db at 22-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=97 device type=DISK

contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/product/11.1.0/db_1/dbs/spfileorcl.ora' auxiliary format '/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora''";
}
executing Memory Script

Starting backup at 22-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
Finished backup at 22-AUG-09

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora''

contents of Memory Script:
{
sql clone "alter system set db_name = ''COPIA'' comment= ''duplicate'' scope=spfile";
sql clone "alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl'', ''/u01/app/oracle/oradata/copia'' comment= '''' scope=spfile";
sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/copia/adump'' comment= '''' scope=spfile";
sql clone "alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile";
sql clone "alter system set memory_target = 183001600 comment= '''' scope=spfile";
sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=copiaXDB)'' comment= '''' scope=spfile";
sql clone "alter system set control_files = ''/u01/app/oracle/oradata/copia1/control01.ctl'', ''/u01/app/oracle/oradata/copia2/control02.ctl'', ''/u01/app/oracle/oradata/copia3/control03.ctl'' comment= '''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size = 2294967296 comment= '''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set db_name = ''COPIA'' comment= ''duplicate'' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl'', ''/u01/app/oracle/oradata/copia'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/copia/adump'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile
sql statement: alter system set memory_target = 183001600 comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=copiaXDB)'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/copia1/control01.ctl'', ''/u01/app/oracle/oradata/copia2/control02.ctl'', ''/u01/app/oracle/oradata/copia3/control03.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 2294967296 comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 184127488 bytes

Fixed Size 1298752 bytes
Variable Size 142610112 bytes
Database Buffers 37748736 bytes
Redo Buffers 2469888 bytes

contents of Memory Script:
{
set newname for datafile 1 to "/u01/app/oracle/oradata/copia4/system01.dbf";
set newname for datafile 2 to "/u01/app/oracle/oradata/copia3/sysaux01.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/copia2/undotbs01.dbf";
set newname for datafile 4 to "/u01/app/oracle/oradata/copia1/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/copia4/system01.dbf" datafile auxiliary format
"/u01/app/oracle/oradata/copia3/sysaux01.dbf" datafile auxiliary format
"/u01/app/oracle/oradata/copia2/undotbs01.dbf" datafile auxiliary format
"/u01/app/oracle/oradata/copia1/users01.dbf" ;
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

Starting backup at 22-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/copia4/system01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/copia1/users01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 22-AUG-09

sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "COPIA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/copia/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/copia/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/copia/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/copia4/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_25_690413581.dbf" auxiliary format
"/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_%u_.arc" archivelog like
"/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_22/o1_mf_1_25_59154osg_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 22-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=25 RECID=19 STAMP=695595606
output file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=25 RECID=20 STAMP=695595606
output file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
Finished backup at 22-AUG-09

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0fkn9m61_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0gkn9m6i_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0fkn9m61_.arc
File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0gkn9m6i_.arc

datafile 4 switched to datafile copy
input datafile copy RECID=1 STAMP=695595667 file name=/u01/app/oracle/oradata/copia1/users01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=695595668 file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=695595668 file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf

contents of Memory Script:
{
set until scn 1066933;
recover clone database delete archivelog ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 22-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc thread=1 sequence=25
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-AUG-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 184127488 bytes

Fixed Size 1298752 bytes
Variable Size 146804416 bytes
Database Buffers 33554432 bytes
Redo Buffers 2469888 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "COPIA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/copia/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/copia/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/copia/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/copia4/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile 1 to "/u01/app/oracle/oradata/copia/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/copia3/sysaux01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/copia2/undotbs01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/copia1/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/copia/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf RECID=1 STAMP=695595722

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf RECID=2 STAMP=695595723

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/copia1/users01.dbf RECID=3 STAMP=695595724

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=695595724 file name=/u01/app/oracle/oradata/copia1/users01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=695595723 file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=695595722 file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 22-AUG-09

RMAN>

Documentación sobre DUPLICATE ACTIVE DATABASE

by Ligarius
24.08.09. 11:31:00. 2640 words, 27082 views. Categories: Oracle 11g ,