martes, 17 de diciembre de 2019

Crear una Standby usando RMAN Duplicate from Active Database

IMPLEMENTANDO PHYSICAL STANDBY USANDO RMAN DUPLICATE FROM ACTIVE DATABASE

A continuación detallaré el paso a paso para la implementación de un Physical Standby Database, usando RMAN DUPLICATE DATABASE FROM ACTIVE DATABASE. Este proceso  clonará a través de la red, un ambiente productivo y en base a este generará un physical standby, sin necesidad de tener un backup RMAN previamente generado.
En el presente ejemplo, el origen se encuentra en filesystem y el destino será un Physical Standby sobreASM.
Información de la BD Primaria(origen):
Host: SERVER-PROD
DB_NAME = IRIS
DB_UNIQUE_NAME=IRIS
Primary Database
OS versión-> redhat 6.7
Servidor -> SERVER-PROD
Instancia -> IRIS
ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system: Filesystem
Información de la BD Standby(destino):
Host: SERVER-CONT
DB_NAME = IRIS
DB_UNIQUE_NAME = IRISSBY
Standby Database
OS versión-> redhat 7.2
Servidor-> server-conting
Instancia->IRISSBY
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system : ASM (+DG_DATA12C_02)

PRIMARY: Todos los siguientes pasos se ejecutarán en el entorno primario

1)Verificar la BD se encuentre en modo archivelog

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivedata/archivelogs/
Oldest online log sequence 5231
Next log sequence to archive 5234
Current log sequence
2) verificar la BD origen se encuentre en modo force_logging
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
En caso de ser requerido activar de la siguiente manera :
SQL> alter database force logging;
Database altered.
3.-Crear standby redo logfiles
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog1.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog2.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog3.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog4.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog5.rdo' size 500M;
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000
SQL> select group#, thread#, bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
———-      ———-      —————
1                  1                   500
2                  1                  500
3                  1                  500
4                  1                  500
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000
Quedando de la siguiente manera:
select GROUP#,member from v$logfile
 GROUP#       MEMBER
-------- ------------------------------------------------
 4        /oradata1/IRIS/onlinelog/o1_mf_4_cxjbnd5q_.log
 4        /oradata2/IRIS/onlinelog/o1_mf_4_cxjbndhw_.log
 3        /oradata1/IRIS/onlinelog/o1_mf_3_cxjbnbom_.log
 3        /oradata3/IRIS/onlinelog/o1_mf_3_cxjbncbd_.log
 2        /oradata2/IRIS/onlinelog/o1_mf_2_cxjbn9g5_.log
 2        /oradata3/IRIS/onlinelog/o1_mf_2_cxjbn9r6_.log
 1        /oradata1/IRIS/onlinelog/o1_mf_1_cxjbn7lk_.log
 1        /oradata2/IRIS/onlinelog/o1_mf_1_cxjbn7wl_.log
 5        /oradata1/IRIS/onlinelog/slog1.rdo
 6        /oradata1/IRIS/onlinelog/slog2.rdo
 7        /oradata1/IRIS/onlinelog/slog3.rdo
 8        /oradata1/IRIS/onlinelog/slog4.rdo
 9        /oradata1/IRIS/onlinelog/slog5.rdo
4.-Verificar parametros necesarios para armar el dataguard:
DB_NAME:
SQL> show parameter db_name
 NAME             TYPE               VALUE
 ------------ ----------------- ---------------
 db_name          string              IRIS
DB_UNIQUE_NAME:
SQL> show parameter db_unique_name
  NAME               TYPE         VALUE
 ---------------- ------------- ------------
  db_unique_name     string        IRIS
5.-Configurar ARCHIVE_LOG_CONFIG para enviar redo logs de origen a destino usando DB_UNIQUE_NAME.
Antes:
SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                    TYPE       VALUE
 ------------------- ----------- ------------
 log_archive_config    string
Cambio:
SQL> alter system set log_archive_config='DG_CONFIG=(IRIS,IRISSBY)';
 System altered.
Después:
SQL> show parameter log_archive_config

NAME                   TYPE               VALUE
 ------------------- ------------   -------------------------
 log_archive_config    string        DG_CONFIG=(IRIS,IRISSBY)
6.-Habilitar LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2
Antes:
SQL> show parameter log_archive_dest_1
NAME                           TYPE         VALUE
--------------------------- ----------- -----------------------------------
log_archive_dest_1            string     LOCATION=/archivedata/archivelogs/
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
Cambio:
SQL> alter system set log_archive_dest_1='LOCATION=/archivedata/archivelogs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRIS';

System altered.
despues:
SQL> show parameter log_archive_dest_1

NAME                    TYPE                VALUE
-------------------- ----------   ----------------------------------------
log_archive_dest_1     string     LOCATION=/archivedata/archive-
                                  logs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
                                  DB_UNIQUE_NAME=IRIS
                                                                          
log_archive_dest_10     string
log_archive_dest_11     string
log_archive_dest_12     string
log_archive_dest_13     string
log_archive_dest_14     string
log_archive_dest_15     string
log_archive_dest_16     string
log_archive_dest_17     string
log_archive_dest_18     string
log_archive_dest_19     string
–Seteamos el transporte de redos en el log_archive_dest_2
Antes:
SQL> show parameter log_archive_dest_2
NAME                          TYPE           VALUE
 ----------------------  -------------- ----------------
 log_archive_dest_2           string
 log_archive_dest_20          string
 log_archive_dest_21          string
 log_archive_dest_22          string
 log_archive_dest_23          string
 log_archive_dest_24          string
 log_archive_dest_25          string
 log_archive_dest_26          string
 log_archive_dest_27          string
 log_archive_dest_28          string
 log_archive_dest_29          string
Cambio:
SQL> alter system set log_archive_dest_2='SERVICE=IRISSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=IRISSBY';
 System altered.
Después:
SQL> show parameter log_archive_dest_2
NAME                   TYPE            VALUE
------------------- --------- ----------------------------------
log_archive_dest_2   string   SERVICE=IRISSBY LGWR ASYNC VALID_FOR                                                  
                              =(ONLINE_LOGFILES,PRIMARY_ROLE) 
                              DB_UNIQUE_NAME=IRISSBY
 log_archive_dest_20 string
 log_archive_dest_21 string
7.-Configurar FAL_SERVER y FAL_CLIENT
FAL_SERVER=Fetch archivelog al destino – Primary
FAL_CLIENT=Cliente que recibe los archivelogs – Standby
FAL_SERVER:
SQL> alter system set fal_server=IRIS;
System altered.
SQL> show parameter fal_server
NAME                TYPE                 VALUE
 -------------- ------------------ ------------------
 fal_server           string             IRIS
FAL_CLIENT:
SQL> alter system set fal_client=IRISSBY;
System altered.
SQL> show parameter fal_client
 NAME                   TYPE             VALUE
 -------------- ----------------  ---------------------
 fal_client           string          IRISSBY
8.-Habilitar replicación de SO.
Para que el standby tambien replique operaciones del SO como adición o borrado de archivos(por ejemplo cuando agregas o borras datafiles en PROD,estas operaciones sean replicadas al standby), el parametro standby_file_management debe ser seteado a AUTO.
alter system set standby_file_management=auto;
SQL> show parameter standby_file_management
NAME                         TYPE             VALUE
 -----------------------  --------------- ------------------
 standby_file_management     string            AUTO
9.-Habilitar el login remoto
alter system set remote_loging_passwordfile=exclusive;
SQL> show parameter password
NAME                            TYPE            VALUE
-------------------------- ---------------- ----------------
remote_login_passwordfile     string            EXCLUSIVE
10.-Configurar conectividad de red:
La Instancia primaria debe estar registrada estaticamente con su propio listener
--Listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = IRIS)
 (ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1)
 (SID_NAME = IRIS)
 )
 )
Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en ambos servidores Primario y Standby.
-bash-4.2$ cat tnsnames.ora
IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )

STANDBY: Todos los siguientes pasos se ejecutarán en el entorno standby.

11.-Configuraciones de red en standby
Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en servidor standby.
IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )
–Registrar la BD standby staticamente en el listener del servidor standby

[grid@fcrac06 admin]$ cat listener.ora
LISTENER_EXT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_EXT)))) # line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=IRISSBY)
(SID_NAME=IRISSBY)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=() # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_EXT=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_EXT=SUBNET # line added by Agent
validar:
 [oracle@fcrac06 admin]$ lsnrctl status | grep IRIS
 Service "IRISSBY" has 1 instance(s).
 Instance "IRISSBY", status UNKNOWN, has 1 handler(s) for this service...
12.-Crear las rutas a nivel de SO que alojaran los archivos de BD.
/u01/app/oracle/admin/IRISSBY/
[oracle@ocm2 ~]$ mkdir oradata
[oracle@ocm2 ~]$ mkdir oradata/pritst
[oracle@ocm2 ~]$ mkdir oradata/pritst/arch
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/adump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/bdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/dpdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/pfile
13.- Registrar en el oratab del servidor standby
 vi /etc/oratab
 IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate
[oracle@fcrac06 admin]$ cat /etc/oratab | grep IRIS
IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate
[oracle@fcrac06 admin]$
14.-Crear pfile basico en $ORACLE_HOME/dbs del standby
[oracle@fcrac06 dbs]$ cat initIRISSBY.ora
DB_NAME=IRIS
DB_UNIQUE_NAME=IRISSBY
[oracle@fcrac06 dbs]$
15.-copiar el passwordfile de origen a standby
scp oracle@ocm1:$ORACLE_HOME/dbs/orapwIRIS $ORACLE_HOME/dbs
[oracle@fcrac06 dbs]$ ls -ltr
total 10220
-rw-r–r– 1 oracle oinstall 2992 Jun 26 2017 init.ora
-rw-r—– 1 oracle oinstall 7680 Apr 8 01:47 orapwIRIS —
-rw-r—– 1 oracle oinstall 7680 Apr 8 08:40 orapwIRISSBY
-rw-r—– 1 oracle asmadmin 10403840 Apr 8 10:38 cntrlIRISSBY.dbf
-rw-rw—- 1 oracle asmadmin 1544 Apr 8 10:46 hc_IRISSBY.dat
-rw-r–r– 1 oracle oinstall 1238 Apr 8 10:53 initIRIS.ora.old
-rw-r–r– 1 oracle oinstall 36 Apr 8 13:16 initIRISSBY.ora
16.-Usando el pfile simple subir el standby hasta nomount.
[oracle@fcrac06 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 8 13:32:20 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> !pwd
/u01/app/oracle/product/12.1.0/dbhome_1/dbs
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initIRISSBY.ora';
 ORACLE instance started.

Total System Global Area 2466250752 bytes
 Fixed Size 2927240 bytes
 Variable Size 2097153400 bytes
 Database Buffers 318767104 bytes
 Redo Buffers 47403008 bytes
17.-Preparar script para duplicate
[oracle@fcrac06 ~]$ cat dupstby.cmd
 run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit
18.-En el servidor standby conectarse al primario y auxiliay(standby) a traves de RMAN y ejecutar el duplicate database

[oracle@fcrac06 ~]$ rman target sys/oracle@IRIS
Recovery Manager: Release 12.1.0.2.0 – Production on Sun Apr 8 13:52:27 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: IRIS (DBID=1678477687)
RMAN>

RMAN> connect auxiliary sys/oracle@IRISSBY
connected to auxiliary database: IRIS (not mounted)

RMAN>
run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA12C_02'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit
19.-Proceso de duplicate inicia:
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 100931731456 bytes
Fixed Size 7656992 bytes
Variable Size 13958646240 bytes
Database Buffers 86704652288 bytes
Redo Buffers 260775936 bytes
allocated channel: aux
channel aux: SID=2401 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/oradata/IRISSBY/controlfile/control01.ctl’;
}
executing Memory Script
Starting backup at 08-APR-18
channel c1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_IRIS.f tag=TAG20180408T165819
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-APR-18
contents of Memory Script:
20.-Iniciar Active Dataguard
—Activar proceso de Sincronización MRP.
SQL> select status from v$instance;
 STATUS
 ------------
 MOUNTED
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
 --------------------
 READ ONLY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
21.-Validación final.
Finalmente generamos 3 archivelogs en el origen y validamos sean aplicados en standby
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
 SELECT sequence#, first_time, next_time, applied, thread# FROM v$archived_log ORDER BY 1,2;
 SEQUENCE#   FIRST_TIME           NEXT_TIME           APPLIED  THREAD#
 ---------- -------------------- -------------------- -------  -------
 5262       08/04/2018 17:18:36  08/04/2018 17:20:20  YES        1
 5263       08/04/2018 17:20:20  08/04/2018 17:21:14  YES        1
 5264       08/04/2018 17:21:14  08/04/2018 17:22:56  YES        1
 5265       08/04/2018 17:22:56  08/04/2018 17:23:15  YES        1
 5266       08/04/2018 17:23:15  08/04/2018 17:23:18  IN-MEMORY  1
como se puede apreciar nuestro nuevo physical standby se encuentra terminado y totalmente funcional!!!.
-

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)

RMAN - Cuando no se encuentra un archive log --- RMAN-06726: could not locate archived log

  Lo importante en todo esto, es estar dentro del catalogo correspondiente, sino el crosscheck NO FNCIONA !!   oracle backup of logfiles is ...