Me interesa mostrar en este post todos los ajustes necesarios si creamos las VM siguiendo mi post anterior, usando Vagrant, Ansible y VirtualBox en Windows 10.
Lo que sigue son los pasos conocidos para configurar una base standby usando ASM a partir de una single instance, con un par de ajustes propios de este ambiente. Con un poco de tiempo extra, todo esto puede agregarse a un playbook e incluirlo en los pasos que ejecuta Ansible.
Hay que tener presente que Dataguard es una opción de Oracle Enterprise Edition, y no es posible usarlo en edición Standard (SE).
Repasando la configuración que tenemos:
- hosts: ora12c-si-asm-oel681/2
- IPs: 172.16.56.30 y 172.16.56.31
- ORACLE_SID: orclcdb
- usan ASM y PDBs
- no tienen configurado archivelog
- los usuarios tienen contraseñas por defecto del playbook ansible
Esto se puede ver con una rápida validación después de creadas las VM en el post anterior:
D:\GitHub\vagrant-vbox-si-asm>vagrant ssh ora12c-si-asm-oel681
Last login: Wed Jan 24 21:22:11 2018 from 10.0.2.2
----------------------------------------
Welcome to ora12c-si-asm-oel681
OracleLinux 6.8 x86_64
FQDN: ora12c-si-asm-oel681.internal.lab
IP: 10.0.2.15
Processor: Intel(R) Core(TM) i7-4600M CPU @ 2.90GHz
#CPU's: 1
Memory: 3254 MB
Kernel: 4.1.12-37.4.1.el6uek.x86_64
----------------------------------------
[vagrant@ora12c-si-asm-oel681 ~]$ sudo su - oracle
[oracle@ora12c-si-asm-oel681 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 47G 2.4G 43G 6% /
tmpfs 1.6G 1.1G 580M 65% /dev/shm
/dev/mapper/vgora-lvora
74G 18G 53G 26% /u01
/dev/sr0 56M 56M 0 100% /mnt
[oracle@ora12c-si-asm-oel681 ~]$ ps -eaf | grep pmon
oracle 15488 1 0 15:46 ? 00:00:05 ora_pmon_orclcdb
oracle 24473 1 0 14:03 ? 00:00:05 asm_pmon_+ASM
oracle 31671 31653 0 22:57 pts/0 00:00:00 grep pmon
[oracle@ora12c-si-asm-oel681 ~]$ tail /etc/oratab
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/oracle/12.1.0.2/grid:N # line added by Agent
orclcdb:/u01/app/oracle/12.1.0.2/db1:N # line added by Agent
[oracle@ora12c-si-asm-oel681 ~]$ . oraenv
ORACLE_SID = [orcldb] ? orclcdb
The Oracle base has been set to /u01/app/oracle
[oracle@ora12c-si-asm-oel681 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 22:58:15 2018
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
22:58:23 SYS @ orclcdb:>set lines 180 pages 180
22:58:25 SYS @ orclcdb:>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
22:58:25 SYS @ orclcdb:>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
22:58:27 SYS @ orclcdb:>alter session set container=orcl;
Session altered.
22:58:40 SYS @ orclcdb:>select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/system.269.966268047
+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/sysaux.270.966268047
+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/users.272.966268171
22:59:28 SYS @ orclcdb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12c-si-asm-oel681 ~]$ ifconfig | grep "inet "
inet addr:10.0.2.15 Bcast:10.0.2.255 Mask:255.255.255.0
inet addr:192.168.9.70 Bcast:192.168.9.255 Mask:255.255.255.0
inet addr:172.16.56.30 Bcast:172.16.56.255 Mask:255.255.255.0
inet addr:127.0.0.1 Mask:255.0.0.0
[oracle@ora12c-si-asm-oel681 ~]$ ssh 172.16.56.31
oracle@172.16.56.31's password:
----------------------------------------
Welcome to ora12c-si-asm-oel682
OracleLinux 6.8 x86_64
FQDN: ora12c-si-asm-oel682.internal.lab
IP: 10.0.2.15
Processor: Intel(R) Core(TM) i7-4600M CPU @ 2.90GHz
#CPU's: 1
Memory: 3254 MB
Kernel: 4.1.12-37.4.1.el6uek.x86_64
----------------------------------------
[oracle@ora12c-si-asm-oel682 ~]$
[oracle@ora12c-si-asm-oel682 ~]$ ps -eaf | grep pmon
oracle 7285 1 0 15:29 ? 00:00:05 ora_pmon_orclcdb
oracle 19530 19514 0 23:11 pts/0 00:00:00 grep pmon
oracle 21172 1 0 14:03 ? 00:00:05 asm_pmon_+ASM
Me interesa configurar una de ellas como standby, digamos en ora12c-si-asm-oel682.
Este es un resumen de los pasos que vamos a seguir, usando RMAN duplicate para la copia inicial de la base, y Dataguard Broker para configurar la replicación hacia la base standby.
Agrego un paso extra (6) para evitar un error al usar RMAN en el siguiente.
Esto es particular de este ambiente de pruebas, no es algo necesario siempre.
1) ajustar la base primaria:
a) habilitar force_logging
b) habilitar archivado
c) agregar standby redo logs
d) habilitar DataGuard Broker
2) agregar entradas en tnsnames.ora de ambos servidores
3) agregar una entrada al listener.ora en el servidor standby
4) parar la base corriendo en la standby
5) iniciar la base standby con un archivo de arranque ajustado (init.ora)
6) crear alias en ASM para el spfile en la primaria
7) crear la base standby usando RMAN duplicate from active
8) habilitar standby usando DataGuard Broker
Y estos son los detalles de aplicar estos pasos.
Al final incluyo como referencia los dos problemas que encontré y son evitados al seguir los pasos anteriores.
1) Ajustes en la base primaria
force_logging y archivado son necesario en la base primaria para configurar una standby.
Agregar standby redo logs es algo recomendado para estar preparados para un cambio de rol, pero no es obligatorio.
Yo lo agrego para que no se muestren alertas al final al usar Dataguard Broker
[oracle@ora12c-si-asm-oel681 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 23:12:13 2018
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
23:12:13 SYS @ orclcdb:>alter database force logging;
Database altered.
23:12:25 SYS @ orclcdb:>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
23:13:08 SYS @ orclcdb:>alter system set dg_broker_start=true;
System altered.
23:13:20 SYS @ orclcdb:>set lines 180 pages 180
23:13:24 SYS @ orclcdb:>select group#, thread#, bytes/1024/1024 from v$log order by 1;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 100
2 1 100
3 1 100
4 1 100
23:14:50 SYS @ orclcdb:>alter database add standby logfile ('+DATA','+FRA') size 100M;
Database altered.
23:14:55 SYS @ orclcdb:>alter database add standby logfile ('+DATA','+FRA') size 100M;
Database altered.
23:14:57 SYS @ orclcdb:>alter database add standby logfile ('+DATA','+FRA') size 100M;
Database altered.
23:14:59 SYS @ orclcdb:>alter database add standby logfile ('+DATA','+FRA') size 100M;
Database altered.
23:15:08 SYS @ orclcdb:>col member for a50
23:15:12 SYS @ orclcdb:>select * from v$logfile order by type, group#;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
1 ONLINE +DATA/ORCLCDB/ONLINELOG/group_1.261.966265055 NO 0
1 ONLINE +FRA/ORCLCDB/ONLINELOG/group_1.257.966265067 YES 0
2 ONLINE +DATA/ORCLCDB/ONLINELOG/group_2.262.966303565 NO 0
2 ONLINE +FRA/ORCLCDB/ONLINELOG/group_2.258.966303569 NO 0
7 ONLINE +DATA/ORCLCDB/ONLINELOG/group_7.276.966303573 NO 0
7 ONLINE +FRA/ORCLCDB/ONLINELOG/group_7.265.966303577 NO 0
8 ONLINE +FRA/ORCLCDB/ONLINELOG/group_8.266.966303587 NO 0
8 ONLINE +DATA/ORCLCDB/ONLINELOG/group_8.277.966303583 NO 0
3 STANDBY +DATA/ORCLCDB/ONLINELOG/group_3.263.966307683 NO 0
3 STANDBY +FRA/ORCLCDB/ONLINELOG/group_3.268.966307685 NO 0
4 STANDBY +FRA/ORCLCDB/ONLINELOG/group_4.262.966303491 NO 0
4 STANDBY +DATA/ORCLCDB/ONLINELOG/group_4.273.966303489 NO 0
5 STANDBY +DATA/ORCLCDB/ONLINELOG/group_5.275.966303495 NO 0
5 STANDBY +FRA/ORCLCDB/ONLINELOG/group_5.264.966303499 NO 0
6 STANDBY +DATA/ORCLCDB/ONLINELOG/group_6.274.966303325 NO 0
6 STANDBY +FRA/ORCLCDB/ONLINELOG/group_6.263.966303329 NO 0
16 rows selected.
23:15:28 SYS @ orclcdb:>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
23:15:57 SYS @ orclcdb:>startup mount;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
23:16:19 SYS @ orclcdb:>alter database archivelog;
Database altered.
23:16:21 SYS @ orclcdb:>startup force;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
23:17:03 SYS @ orclcdb:>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
23:17:05 SYS @ orclcdb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Notar que es una práctica recomendada crear standby redo logs con un grupo más de los usados en la primaria.
En este ejemplo no es realmente necesario ya que no se va a poner nunca en producción, pero hay que tenerlo presente si van a seguir un procedimiento similar en producción.
2) agregar entradas en tnsnames.ora de ambos servidores
En 12c todo debería funcionar con Easy Connect, pero en mis pruebas no siempre anduvo bien, así que por simplicidad configuramos las entradas en los archivos tnsnames.ora
En primaria:
[oracle@ora12c-si-asm-oel681 ~]$ vi /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
[oracle@ora12c-si-asm-oel681 ~]$ cat /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
primary,ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel681.internal.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdb)
)
)
standby,orclstb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel682)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstb)
)
)
En standby:
[oracle@ora12c-si-asm-oel682 admin]$ vi /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
[oracle@ora12c-si-asm-oel682 admin]$ cat /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/12.1.0.2/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_orclcdb =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel682)(PORT = 1521))
primary,ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel681.internal.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdb)
)
)
standby,orclstb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel682)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstb)
)
)
3) agregar una entrada al listener.ora en el servidor standby
[oracle@ora12c-si-asm-oel682 oracle]$ vi /u01/app/oracle/12.1.0.2/grid/network/admin/listener.ora
[oracle@ora12c-si-asm-oel682 oracle]$ cat /u01/app/oracle/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel682.internal.lab)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
# ncalero: static entry for standby:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclstb)
(ORACLE_HOME = /u01/app/oracle/12.1.0.2/db1)
(SID_NAME = orclcdb)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
[oracle@ora12c-si-asm-oel682 oracle]$ /u01/app/oracle/12.1.0.2/grid/bin/lsnrctl reload listener
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2018 23:29:27
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@ora12c-si-asm-oel682 oracle]$
[oracle@ora12c-si-asm-oel682 oracle]$ tail /etc/oratab
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/oracle/12.1.0.2/grid:N # line added by Agent
orclcdb:/u01/app/oracle/12.1.0.2/db1:N # line added by Agent
4) parar la base corriendo en la standby
Esta VM tiene una base igual que la primaria y no la necesitamos.
Podemos borrarla y de paso liberar espacio de ASM, o solo detenerla y continuar con este ejemplo, ya que el espacio en disco alcanza.
[oracle@ora12c-si-asm-oel682 dbs]$ . oraenv
ORACLE_SID = [orclcdb] ?
[oracle@ora12c-si-asm-oel682 dbs]$ srvctl status database -d orclcdb
Database is running.
[oracle@ora12c-si-asm-oel682 dbs]$ srvctl stop database -d orclcdb
[oracle@ora12c-si-asm-oel682 dbs]$
Solo para evitar problemas con la configuración en el futuro, borro la base configurada en clusterware, ya que la configuración cambió:
[oracle@ora12c-si-asm-oel682 dbs]$ srvctl remove database -d orclcdb
Remove the database orclcdb? (y/[n]) y
[oracle@ora12c-si-asm-oel682 dbs]$
5) iniciar la base standby con un archivo de arranque ajustado (init.ora)
Vamos a quitar el spfile que usaba la base anterior, y crear un archivo de parámetros mínimo para la nueva standby para poder levantarla en modo nomount.
Luego cuando ejecutamos el comando RMAN duplicate vamos a poner todos los parámetros necesarios para la standby.
[oracle@ora12c-si-asm-oel682 dbs]$ . oraenv
ORACLE_SID = [orclcdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c-si-asm-oel682 oracle]$ cd $ORACLE_HOME
[oracle@ora12c-si-asm-oel682 db1]$ pwd
/u01/app/oracle/12.1.0.2/db1
[oracle@ora12c-si-asm-oel682 db1]$ cd dbs
[oracle@ora12c-si-asm-oel682 dbs]$ mv spfileorclcdb.ora spfileorclcdb-bad.ora
[oracle@ora12c-si-asm-oel682 dbs]$ echo '*.db_name=orclcdb' > initorclcdb.ora
[oracle@ora12c-si-asm-oel682 dbs]$ cat initorclcdb.ora
*.db_name=orclcdb
[oracle@ora12c-si-asm-oel682 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 23:47:34 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
23:47:35 SYS @ orclcdb:>startup nomount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 2922712 bytes
Variable Size 159385384 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
23:47:41 SYS @ orclcdb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ora12c-si-asm-oel682 dbs]$
[oracle@ora12c-si-asm-oel682 dbs]$ ps -eaf | grep pmon
oracle 21258 1 0 13:43 ? 00:00:01 asm_pmon_+ASM
oracle 25855 1 0 23:47 ? 00:00:00 ora_pmon_orclcdb
oracle 25907 23932 0 23:48 pts/1 00:00:00 grep pmon
[oracle@ora12c-si-asm-oel682 dbs]$
6) crear alias en ASM para el spfile en la primaria
[oracle@ora12c-si-asm-oel681 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 25 00:43:22 2018
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
00:43:22 SYS @ orclcdb:>show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ORCLCDB/PARAMETERFILE/sp
file.268.966266805
00:43:24 SYS @ orclcdb:> exit
[oracle@ora12c-si-asm-oel681 ~]$ cd $ORACLE_HOME/dbs
[oracle@ora12c-si-asm-oel681 dbs]$ ls -lrt
total 24
-rw-r--r--. 1 oracle oinstall 2992 Feb 3 2012 init.ora
-rw-r-----. 1 oracle dba 24 Jan 24 14:49 lkORCLCDB
-rw-r-----. 1 oracle oinstall 7680 Jan 24 15:26 orapworclcdb
-rw-r-----. 1 oracle oinstall 41 Jan 24 15:45 initorclcdb.ora
-rw-rw----. 1 oracle dba 1544 Jan 25 00:49 hc_orclcdb.dat
[oracle@ora12c-si-asm-oel681 dbs]$ cat initorclcdb.ora
SPFILE='+DATA/orclcdb/spfileorclcdb.ora'
[oracle@ora12c-si-asm-oel681 dbs]$ . oraenv
ORACLE_SID = [orclcdb] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c-si-asm-oel681 dbs]$ asmcmd
ASMCMD> cd data/orclcdb
ASMCMD> ls spfileorclcdb.ora
ASMCMD-8002: entry 'spfileorclcdb.ora' does not exist in directory '+data/orclcdb/'
ASMCMD> cd parameterfile
ASMCMD> ls
spfile.268.966266805
ASMCMD> cd ..
ASMCMD> mkalias parameterfile/spfile.268.966266805 spfileorclcdb.ora
ASMCMD> ls
63871D5DE1F02321E0534609A8C0357B/
6387BBFEF9833D92E0534609A8C0BF8E/
CONTROLFILE/
DATAFILE/
FD9AC20F64D244D7E043B6A9E80A2F2F/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileorclcdb.ora
ASMCMD> exit
7) crear la base standby usando RMAN duplicate from active
Antes de lanzar RMAN, validamos conectivitidad SQL*Net a ambas bases desde la primaria:
Ahora sí, lanzamos el backup:
[oracle@ora12c-si-asm-oel681 ~]$ . oraenv
ORACLE_SID = [+ASM] ? orclcdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c-si-asm-oel681 ~]$ tnsping orclcdb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-JAN-2018 01:26:03
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel681.internal.lab)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb)))
OK (20 msec)
[oracle@ora12c-si-asm-oel681 ~]$ tnsping orclstb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-JAN-2018 01:26:06
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-si-asm-oel682)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstb)))
OK (10 msec)
[oracle@ora12c-si-asm-oel681 ~]$ sqlplus sys/Oracle123@orclstb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 01:26:17 2018
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
01:26:17 SYS @ orclstb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate auxiliary channel aux type disk;
6> duplicate target database for standby from active database nofilenamecheck spfile
7> set log_archive_max_processes='8'
8> set memory_target='0'
9> set sga_target='768M'
10> set pga_aggregate_target='256M'
11> set db_unique_name='orclstb'
12> set db_create_file_dest='+DATA'
13> set DB_CREATE_ONLINE_LOG_DEST_1='+DATA'
14> set DB_RECOVERY_FILE_DEST='+FRA'
15> set DB_RECOVERY_FILE_DEST_SIZE='10G'
16> set standby_file_management='AUTO';
17> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=76 device type=DISK
allocated channel: c2
channel c2: SID=57 device type=DISK
allocated channel: aux
channel aux: SID=1 device type=DISK
Starting Duplicate Db at 25-JAN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' targetfile
'+DATA/orclcdb/spfileorclcdb.ora' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora''";
}
executing Memory Script
Starting backup at 25-JAN-18
Finished backup at 25-JAN-18
sql statement: alter system set spfile= ''/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora''
contents of Memory Script:
{
sql clone "alter system set log_archive_max_processes =
8 comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
0 comment=
'''' scope=spfile";
sql clone "alter system set sga_target =
768M comment=
'''' scope=spfile";
sql clone "alter system set pga_aggregate_target =
256M comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''orclstb'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
sql clone "alter system set DB_CREATE_ONLINE_LOG_DEST_1 =
''+DATA'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''+FRA'' comment=
'''' scope=spfile";
sql clone "alter system set DB_RECOVERY_FILE_DEST_SIZE =
10G comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set log_archive_max_processes = 8 comment= '''' scope=spfile
sql statement: alter system set memory_target = 0 comment= '''' scope=spfile
sql statement: alter system set sga_target = 768M comment= '''' scope=spfile
sql statement: alter system set pga_aggregate_target = 256M comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''orclstb'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set DB_CREATE_ONLINE_LOG_DEST_1 = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+FRA'' comment= '''' scope=spfile
sql statement: alter system set DB_RECOVERY_FILE_DEST_SIZE = 10G comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
allocated channel: aux
channel aux: SID=28 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/ORCLSTB/CONTROLFILE/current.289.967913439'', ''+FRA/ORCLSTB/CONTROLFILE/current.261.967913439'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/ORCLSTB/CONTROLFILE/current.288.967913439';
restore clone primary controlfile to '+FRA/ORCLSTB/CONTROLFILE/current.275.967913439' from
'+DATA/ORCLSTB/CONTROLFILE/current.288.967913439';
sql clone "alter system set control_files =
''+DATA/ORCLSTB/CONTROLFILE/current.288.967913439'', ''+FRA/ORCLSTB/CONTROLFILE/current.275.967913439'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/ORCLSTB/CONTROLFILE/current.289.967913439'', ''+FRA/ORCLSTB/CONTROLFILE/current.261.967913439'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 25-JAN-18
channel c1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/12.1.0.2/db1/dbs/snapcf_orclcdb.f tag=TAG20180125T034620
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 25-JAN-18
Starting restore at 25-JAN-18
channel aux: copied control file copy
Finished restore at 25-JAN-18
sql statement: alter system set control_files = ''+DATA/ORCLSTB/CONTROLFILE/current.288.967913439'', ''+FRA/ORCLSTB/CONTROLFILE/current.275.967913439'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
allocated channel: aux
channel aux: SID=28 device type=DISK
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 6 auxiliary format new
datafile 7 auxiliary format new
datafile 8 auxiliary format new
datafile 9 auxiliary format new
datafile 10 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
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 backup at 25-JAN-18
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/ORCLCDB/DATAFILE/system.257.966264763
channel c2: starting datafile copy
input datafile file number=00003 name=+DATA/ORCLCDB/DATAFILE/sysaux.256.966264621
output file name=+DATA/ORCLSTB/DATAFILE/sysaux.286.967913491 tag=TAG20180125T034709
channel c2: datafile copy complete, elapsed time: 00:01:46
channel c2: starting datafile copy
input datafile file number=00009 name=+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/sysaux.270.966268047
output file name=+DATA/ORCLSTB/DATAFILE/system.287.967913491 tag=TAG20180125T034709
channel c1: datafile copy complete, elapsed time: 00:01:50
channel c1: starting datafile copy
input datafile file number=00007 name=+DATA/ORCLCDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.265.966265243
output file name=+DATA/ORCLSTB/63871D5DE1F02321E0534609A8C0357B/DATAFILE/sysaux.284.967913601 tag=TAG20180125T034709
channel c1: datafile copy complete, elapsed time: 00:01:26
channel c1: starting datafile copy
input datafile file number=00005 name=+DATA/ORCLCDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.266.966265245
output file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/sysaux.285.967913599 tag=TAG20180125T034709
channel c2: datafile copy complete, elapsed time: 00:01:28
channel c2: starting datafile copy
input datafile file number=00008 name=+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/system.269.966268047
output file name=+DATA/ORCLSTB/63871D5DE1F02321E0534609A8C0357B/DATAFILE/system.279.967913687 tag=TAG20180125T034709
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA/ORCLCDB/DATAFILE/undotbs1.259.966264923
output file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/system.278.967913687 tag=TAG20180125T034709
channel c2: datafile copy complete, elapsed time: 00:00:46
channel c2: starting datafile copy
input datafile file number=00006 name=+DATA/ORCLCDB/DATAFILE/users.258.966264919
output file name=+DATA/ORCLSTB/DATAFILE/users.277.967913735 tag=TAG20180125T034709
channel c2: datafile copy complete, elapsed time: 00:00:07
channel c2: starting datafile copy
input datafile file number=00010 name=+DATA/ORCLCDB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/users.272.966268171
output file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/users.282.967913743 tag=TAG20180125T034709
channel c2: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/ORCLSTB/DATAFILE/undotbs1.280.967913733 tag=TAG20180125T034709
channel c1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 25-JAN-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=967913751 file name=+DATA/ORCLSTB/DATAFILE/system.287.967913491
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=967913751 file name=+DATA/ORCLSTB/DATAFILE/sysaux.286.967913491
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=967913752 file name=+DATA/ORCLSTB/DATAFILE/undotbs1.280.967913733
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=967913752 file name=+DATA/ORCLSTB/63871D5DE1F02321E0534609A8C0357B/DATAFILE/system.279.967913687
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=967913752 file name=+DATA/ORCLSTB/DATAFILE/users.277.967913735
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=967913753 file name=+DATA/ORCLSTB/63871D5DE1F02321E0534609A8C0357B/DATAFILE/sysaux.284.967913601
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=967913753 file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/system.278.967913687
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=967913754 file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/sysaux.285.967913599
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=967913754 file name=+DATA/ORCLSTB/6387BBFEF9833D92E0534609A8C0BF8E/DATAFILE/users.282.967913743
Finished Duplicate Db at 25-JAN-18
released channel: c1
released channel: c2
released channel: aux
RMAN> exit
Recovery Manager complete.
Podemos ver el espacio usado en ASM del servidor standby por la nueva base:
[oracle@ora12c-si-asm-oel682 ~]$ . oraenv
ORACLE_SID = [orclcdb] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c-si-asm-oel682 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 4194304 10236 10140 0 10140 0 N CRS/
MOUNTED EXTERN N 512 4096 4194304 20472 16472 0 16472 0 N DATA/
MOUNTED EXTERN N 512 4096 4194304 30708 29536 0 29536 0 N FRA/
ASMCMD>
8) Configurar standby usando DataGuard Broker
[oracle@ora12c-si-asm-oel681 admin]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> create configuration orcl_dg as primary database is orclcdb connect identifier is orclcdb;
Configuration "orcl_dg" created with primary database "orclcdb"
DGMGRL> add database orclstb as connect identifier is orclstb maintained as physical;
Database "orclstb" added
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxPerformance
Members:
orclcdb - Primary database
orclstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 50 seconds ago)
DGMGRL> show database orclstb
Database - orclstb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 46 seconds ago)
Apply Lag: 0 seconds (computed 46 seconds ago)
Average Apply Rate: 456.00 KByte/s
Real Time Query: OFF
Instance(s):
orclcdb
Database Warning(s):
ORA-16857: standby disconnected from redo source for longer than specified threshold
Database Status:
WARNING
DGMGRL> show database orclstb
Database - orclstb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 minute 22 seconds (computed 9 seconds ago)
Apply Lag: 1 minute 22 seconds (computed 9 seconds ago)
Average Apply Rate: 456.00 KByte/s
Real Time Query: OFF
Instance(s):
orclcdb
Database Status:
SUCCESS
DGMGRL> exit
Como vemos le toma unos segundos activar la configuración hasta que no se reportan más errores.
Validaciones
En la base primaria podemos ver que el envío de logs está funcionando:
[oracle@ora12c-si-asm-oel681 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 25 04:05:41 2018
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
04:05:41 SYS @ orclcdb:>set lines 180 pages 180
04:07:09 SYS @ orclcdb:>col dest_name for a20
04:07:11 SYS @ orclcdb:>select dest_id, dest_name, status, db_unique_name, database_mode,recovery_mode, archived_seq#,error, gap_status
04:07:11 2 from V$ARCHIVE_DEST_STATUS
04:07:11 3 where status !='INACTIVE';
DEST_ID DEST_NAME STATUS DB_UNIQUE_NAME DATABASE_MODE RECOVERY_MODE ARCHIVED_SEQ#
---------- -------------------- --------- ------------------------------ --------------- ----------------------- -------------
ERROR GAP_STATUS
----------------------------------------------------------------- ------------------------
1 LOG_ARCHIVE_DEST_1 VALID NONE OPEN IDLE 17
2 LOG_ARCHIVE_DEST_2 VALID orclstb MOUNTED-STANDBY MANAGED REAL TIME APPLY 17
NO GAP
04:07:12 SYS @ orclcdb:>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 18
Current log sequence 18
04:07:21 SYS @ orclcdb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12c-si-asm-oel681 admin]$
Y en la standby podemos ver que se reciben y aplican:
[oracle@ora12c-si-asm-oel681 admin]$ ssh ora12c-si-asm-oel682
oracle@ora12c-si-asm-oel682's password:
Last login: Mon Feb 12 17:04:14 2018 from ora12c-si-asm-oel681.internal.lab
----------------------------------------
Welcome to ora12c-si-asm-oel682
OracleLinux 6.8 x86_64
FQDN: ora12c-si-asm-oel682.internal.lab
IP: 10.0.2.15
Processor: Intel(R) Core(TM) i7-4600M CPU @ 2.90GHz
#CPU's: 1
Memory: 3254 MB
Kernel: 4.1.12-37.4.1.el6uek.x86_64
----------------------------------------
[oracle@ora12c-si-asm-oel682 ~]$ . oraenv
ORACLE_SID = [oracle] ? orclcdb
The Oracle base has been set to /u01/app/oracle
[oracle@ora12c-si-asm-oel682 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 12 17:11:54 2018
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
17:11:17 SYS @ orclcdb:>set lines 180 pages 180
17:11:54 SYS @ orclcdb:>SELECT name,open_mode, DATABASE_ROLE, PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- -------------------- --------------------
ORCLCDB MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
17:12:23 SYS @ orclcdb:>select inst_id, pid, process, thread#, sequence#, status from gv$managed_standby where process='MRP0';
INST_ID PID PROCESS THREAD# SEQUENCE# STATUS
---------- ------------------------ --------- ---------- ---------- ------------
1 27307 MRP0 1 18 WAIT_FOR_LOG
17:12:30 SYS @ orclcdb:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12c-si-asm-oel682 ~]$ cd /u01/app/oracle/diag/rdbms/orclstb/orclcdb/trace/
[oracle@ora12c-si-asm-oel682 trace]$ ls -lrt | tail
-rw-r-----. 1 oracle dba 34895 Feb 12 17:07 drcorclcdb.log
-rw-r-----. 1 oracle dba 3335 Feb 12 17:07 orclcdb_rfs_26965.trm
-rw-r-----. 1 oracle dba 24927 Feb 12 17:07 orclcdb_rfs_26965.trc
-rw-r-----. 1 oracle dba 82 Feb 12 17:07 orclcdb_rfs_27314.trm
-rw-r-----. 1 oracle dba 1074 Feb 12 17:07 orclcdb_rfs_27314.trc
-rw-r-----. 1 oracle dba 229 Feb 12 17:07 orclcdb_mrp0_27307.trm
-rw-r-----. 1 oracle dba 3774 Feb 12 17:07 orclcdb_mrp0_27307.trc
-rw-r-----. 1 oracle dba 348822 Feb 12 17:08 alert_orclcdb.log
-rw-r-----. 1 oracle dba 2556 Feb 12 17:08 orclcdb_rsm0_26912.trm
-rw-r-----. 1 oracle dba 13241 Feb 12 17:08 orclcdb_rsm0_26912.trc
[oracle@ora12c-si-asm-oel682 trace]$ date
Mon Feb 12 17:12:59 CET 2018
[oracle@ora12c-si-asm-oel682 trace]$ tail alert_orclcdb.log
Mon Feb 12 17:07:54 2018
Media Recovery Log +FRA/ORCLSTB/ARCHIVELOG/2018_02_12/thread_1_seq_14.276.967913829
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Mon Feb 12 17:07:55 2018
Media Recovery Log +FRA/ORCLSTB/ARCHIVELOG/2018_02_12/thread_1_seq_15.277.967913829
Mon Feb 12 17:07:56 2018
Media Recovery Log +FRA/ORCLSTB/ARCHIVELOG/2018_02_12/thread_1_seq_16.278.967913831
Mon Feb 12 17:07:58 2018
Media Recovery Log +FRA/ORCLSTB/ARCHIVELOG/2018_02_12/thread_1_seq_17.279.967914469
Media Recovery Waiting for thread 1 sequence 18 (in transit)
[oracle@ora12c-si-asm-oel682 trace]$
Problemas encontrados
a) Si no creamos el alias en ASM (o ajustamos el nombre del spfile en el init, es otra solución posible), vamos a tener este error al intentar tomar un respaldo de la base primaria:
[oracle@ora12c-si-asm-oel681 ~]$ rman target sys/Oracle123 auxiliary sys/Oracle123@orclstb
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jan 24 23:49:57 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2737326422)
connected to auxiliary database: ORCLCDB (not mounted)
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate auxiliary channel aux type disk;
d6> uplicate target database for standby from active database nofilenamecheck spfile
7> set log_archive_max_processes='8'
8> set db_unique_name='orclstb'
9> set standby_file_management='AUTO';
10> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=57 device type=DISK
allocated channel: c2
channel c2: SID=82 device type=DISK
allocated channel: aux
channel aux: SID=22 device type=DISK
Starting Duplicate Db at 24-JAN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' targetfile
'+DATA/orclcdb/spfileorclcdb.ora' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora''";
}
executing Memory Script
Starting backup at 24-JAN-18
released channel: c1
released channel: c2
released channel: aux
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/24/2018 23:51:46
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c2 channel at 01/24/2018 23:51:46
ORA-19505: failed to identify file "+DATA/orclcdb/spfileorclcdb.ora"
ORA-15173: entry 'spfileorclcdb.ora' does not exist in directory 'orclcdb'
RMAN> exit
Recovery Manager complete.
b) Si no agregamos los parametros de memoria al usar RMAN, vamos a tener este error al intentar crear la copia:
[oracle@ora12c-si-asm-oel681 ~]$ rman target sys/Oracle123 auxiliary sys/Oracle123@orclstb
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jan 25 00:55:33 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2737326422)
connected to auxiliary database: ORCLCDB (not mounted)
RMAN> run
2> {
3> allocate channel c1 type disk;
a4> llocate channel c2 type disk;
5> allocate auxiliary channel aux type disk;
6> duplicate target database for standby from active database nofilenamecheck spfile
7> set log_archive_max_processes='8'
8> set db_unique_name='orclstb'
9> set standby_file_management='AUTO';
10> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=51 device type=DISK
allocated channel: c2
channel c2: SID=59 device type=DISK
allocated channel: aux
channel aux: SID=1 device type=DISK
Starting Duplicate Db at 25-JAN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/orapworclcdb' targetfile
'+DATA/orclcdb/spfileorclcdb.ora' auxiliary format
'/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora''";
}
executing Memory Script
Starting backup at 25-JAN-18
Finished backup at 25-JAN-18
sql statement: alter system set spfile= ''/u01/app/oracle/12.1.0.2/db1/dbs/spfileorclcdb.ora''
contents of Memory Script:
{
sql clone "alter system set log_archive_max_processes =
8 comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''orclstb'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set log_archive_max_processes = 8 comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''orclstb'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/25/2018 00:57:53
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00845: MEMORY_TARGET not supported on this system
RMAN>
Conclusiones
Crear una configuración primary/standby en Oracle es un procedimiento conocido, y muy simple usando Dataguard broker.
Hay varias formas de crear la copia inicial de la base de datos, y en este artículo vimos como hacerlo usando RMAN duplicate from active.
Lo más importante aquí es mostrar los pasos adicionales necesarios para completar esta configuración si creamos las bases de forma automática usando Ansible, Vagrant y VirtualBox, como describí en el post anterior.
Un saludo.
