Configuração - Active Dataguard em instância única com grid infrastructure e PDB’s - Oracle 12c (12.2.0.1)

Por José Mario Barduchi Oracle Associate
Publicado em Abril 2018

Revisado por Lorenzo Mota


Introdução

Todos sabem que a continuidade dos negócios hoje é um dos pontos mais importantes para as empresas. Por isso são oferecidos vários produtos e soluções que garantam essa continuidade.

Claro, nenhuma solução usada isoladamente vai garantir 100% de disponibilidade, mas um conjunto de soluções pode te garantir noites tranquilas de sono.

Hoje vamos falar de uma dessas soluções. O Active Dataguard.

Entre suas principais vantagens, podemos destacar:

  • Nenhuma perda de dados em qualquer instance
  • Detecção de corrupção de dados e reparação automática
  • Direcionamento de workload de leituras para a base standby
  • Rolling upgrades e Standby-first patching usando o standby físico
  • Proteção de perda de dados em qualquer uma das instances
  • Backup's incrementais a partir do standby
  • Load balance e gerenciamento de serviços entre as bases replicadas

 

Hoje vamos demonstrar a criação de um active Dataguard 12c (12.2.0.1) com o GRID Infrastructure instalado e uma instance com alguns pdb's criados.


Importante: Neste artigo iremos replicar todos os PDBs criados em nosso ambiente.

Porém, na versão 12.2.0.1 já é possível replicar apenas alguns PDBs utilizando o parâmetro ENABLED_PDBS_ON_STANDBY no STANDBY ou já criando o PDB com a opção “STANDBYS” no CREATE PLUGGABLE DATABASE.

Segundo o manual "Oracle Data Guard Concepts and Administration":

"In Oracle Database 12c Release 1 (12.1), you could only specify whether a PDB was created and recovered in all (ALL) standbys or in no (NONE) standbys when adding a PDB to the primary database. As of Oracle Database 12c Release 2 (12.2.0.1), you can specify a subset of PDBs to be replicated on a physical standby of a multitenant container database (CDB), instead of having to choose either all PDBs or none. To do so, use the ENABLED_PDBS_ON_STANDBY initialization parameter to specify a list of PDBs or use the enhanced STANDBYS qualifier on the CREATE PLUGGABLE DATABASE statement, or both. PDBs that are not enabled on a standby CDB can remain disabled (true SUBSET Standby) or they can be enabled at a later date when all the required files are available at the standby CDB.”

Mas isso vamos abordar em outro artigo.



Retomando as configurações, vamos começar pelo TNS.

===============================================================
=== LISTENER E TNSNAMES
===============================================================


1) Nas máquinas envolvidas (todas elas), prepare o listener para o DG Broker.

No servidor de produção:

################################################################
#### Adicionar as entradas <GLOBAL_NAME>, <GLOBAL_NAME>_DGMGRL,
#### <GLOBAL_NAME>_DGB para já deixar o LISTENER preparado para
#### o DGBroker.
################################################################

  SID_LIST_LISTENER=
    (SID_LIST=
	(SID_DESC =
	  (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME = PLSExtProc)
	  (PROGRAM = extproc)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thor)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thor)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thor_DGMGRL)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thor)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thor_DGB)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thor)
	)
)


No servidor do Active Dataguard:

################################################################
#### Adicionar as entradas <GLOBAL_NAME>, <GLOBAL_NAME>_DGMGRL,
#### <GLOBAL_NAME>_DGB para já deixar o LISTENER preparado para
#### o DGBroker.
################################################################

SID_LIST_LISTENER=
  (SID_LIST=
	(SID_DESC =
	  (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME = PLSExtProc)
	  (PROGRAM = extproc)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thordg)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thordg)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thordg_DGMGRL)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thordg)
	)
	(SID_DESC=
	  (GLOBAL_DBNAME=thordg_DGB)
	  (ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)
	  (SID_NAME=thordg)
	)
)


Vamos executar o bounce do listener.

srvctl stop listener
srvctl start listener
  
  

Nos dois servidores, vamos criar uma entrada no tnsnames.ora para as instances de origem e destino.

################################################################
#### Acessos Primary/Active DataGuard
####
#### TNSPROD           => TNS apontando para o produção
#### TNSDG                => TNS apontando para o DG
################################################################


TNSPROD =
(description =
  (address_list =
  (address = (protocol = tcp)(host = 192.168.56.2)(port = 1521))
)
  (connect_data =
     (server = dedicated)
     (service_name = thor)
  )(UR=A)
)

TNSDG =
(description =
  (address_list =
  (address = (protocol = tcp)(host = 192.168.56.4)(port = 1521))
)
  (connect_data =
     (server = dedicated)
     (service_name = thordg)
  )(UR=A)
)


################################################################
#### Fim das configuracoes do Primary/DG
################################################################


-- Testar as conexões

SQL> conn sys/aaa123@TNSPROD as sysdba
SQL> conn sys/aaa123@TNSDG as sysdba
  
  

================================================================
== VERIFICAÇÕES E CINFIGURAÇÕES DE MODO ARCHIVE E FORCE LOGIN
================================================================   

2) Verificar se o CDB e os pdb's estão com archives, force logging e flasback on

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
  
  

A instance de origem deve estar com force logging, archive log mode e flashback database enable.

SQL> select INST_ID,  force_logging from GV$DATABASE;

  INST_ID      FORCE_LOGGING
  ----------   ---------------------------------------
           1   NO

SQL> col pdb_name forma a20
SQL> select PDB_ID,PDB_NAME,CON_ID,STATUS,LOGGING,FORCE_LOGGING from dba_pdbs ORDER BY PDB_NAME;



SQL>  archive log list;


3) Enable forca login e o flashback database

SQL> alter database force logging;
	Database altered.

SQL> alter database flashback on;
	Database altered.

SQL> select INST_ID,FORCE_LOGGING,FLASHBACK_ON from GV$DATABASE;
    
  INST_ID     FORCE_LOGGING   FLASHBACK_ON
  ----------  --------------  -------------------------
  1           YES             YES



4) Criar os standby redologs.

  COL GROUP#      FOR  99     HEADING 'Group'       JUSTIFY CENTER
  COL THREAD#     FOR  99     HEADING 'Thread'      JUSTIFY CENTER
  COL SEQUENCE#   FOR  999999 HEADING 'Seq.'        JUSTIFY  CENTER
  COL TYPE        FOR  A10    HEADING 'Type'        JUSTIFY CENTER     
  COL MEMBER      FOR  A50    HEADING 'Logfile'     JUSTIFY CENTER
  COL MBYTES      FOR  99,999,999 HEADING 'Tam(MB)'  JUSTIFY  CENTER
  COL STATUS      FOR  A15    HEADING 'Status Group' JUSTIFY  CENTER
  COL STATUS_FILE FOR  A15    HEADING 'Status File'  JUSTIFY CENTER


SELECT
	 lf.GROUP#,
	 lg.THREAD#,
	 lg.SEQUENCE#,
	 lf.TYPE,
	 lf.MEMBER,
	 (lg.BYTES/1024/1024) MBYTES,
	 lf.STATUS,
	 lg.STATUS STATUS_FILE
FROM
	 v$logfile lf
JOIN v$log lg on lg.GROUP# = lf.GROUP#
UNION ALL
SELECT
		lf.GROUP#,
		lg.THREAD#,
		lg.SEQUENCE#,
		lf.TYPE,
		lf.MEMBER,
		(lg.BYTES/1024/1024) MBYTES,
		lf.STATUS,
		lg.STATUS STATUS_FILE
FROM
	 v$logfile lf
JOIN v$standby_log lg on lg.GROUP# = lf.GROUP#
ORDER BY
	TYPE,
	GROUP#,
	THREAD#,
	SEQUENCE#;	

    
Importante:    
Criar os redos do StandBy com o mesmo tamanho dos redos da produção.
Criar sempre um redo a mais que a produção. Neste exemplo, criaremos 4 grupos.

SQL> alter database add standby logfile thread 1
     ('+DATA','+DATA') size 200M;
SQL> alter database add standby logfile thread 1
     ('+DATA','+DATA') size 200M;
SQL> alter database add standby logfile thread 1
     ('+DATA','+DATA') size 200M;
SQL> alter database add standby logfile thread 1
     ('+DATA','+DATA') size 200M;


E vamos verificar novamente.

 

5) Vamos agora para os parâmetros iniciais.

-- Especifica uma lista de até 9 nomes de banco de dados único
-- (definidos pelo DB_UNIQUE_NAME) para todos os bancos de
-- dados na configuração DataGuard. Ou seja, são as máquinas que
-- fazem parte do conjunto Primary/DG

SQL> alter system set
     LOG_ARCHIVE_CONFIG='DG_CONFIG=(thor,thordg)' 
     scope = spfile;
  
  

-- Geração de archives local enquanto a instance é primary.   
-- LOCATION=USE_DB_RECOVERY_FILE_DEST ==> Irá usar o mesmo
-- diretório (ou DiskGroup) da FRA (parâmetro
-- db_recovery_file_dest)
-- VALID_FOR=(ALL_LOGFILES,ALL_ROLES) ==> Especifica se os dados
-- de redo serão escritos para algum destino, com base nos
-- seguintes fatores:               
--   Se a instance está atualmente em execução como primary
--   Se os redos, os standby redos ou ambos estão sendo
--   arquivados no banco de dados do destino
-- DB_UNIQUE_NAME=                   ==> DB_UNIQUE_NAME da PRD

SQL> alter system set 
     LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST 
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=thor' 
     scope = spfile;
     

-- Ativando o state no instance de origem.     ALTER SYSTEM SET

SQL> ALTER SYSTEM SET 
     LOG_ARCHIVE_DEST_STATE_1=enable scope=spfile;		
SQL> ALTER SYSTEM SET 
     LOG_ARCHIVE_DEST_STATE_2=defer scope=spfile
     

-- Parameter for Swith - Failover -  Esses parâmetros atuam quando a instance primary sofre um switchover

SQL> alter system set 
     log_archive_max_processes=8 scope=both sid='*';
SQL> alter system set 
     FAL_SERVER='TNSDG' scope = spfile;

SQL> alter system set 
     STANDBY_FILE_MANAGEMENT=AUTO scope = spfile;


Importante: enable_pluggable_database deve estar setado como true.

NAME                           TYPE       VALUE
---------------------------    ---------  -----------
enable_pluggable_database      boolean    TRUE


-- Restart do database para assumir os novos valores

SQL> shutdown immediate
SQL> startup

SQL> set pages 120 lines 1000
SQL> col name for a30
SQL> col value forma a100
SQL> select name, value
	  from v$parameter
	  where name in
 	  ('db_name','db_unique_name','log_archive_config',
	   'log_archive_dest_1','log_archive_dest_2',
	   'log_archive_dest_state_1','log_archive_dest_state_2',
	   'remote_login_passwordfile', 'log_archive_format', 
      'log_archive_max_processes','fal_server',  
      'db_file_name_convert', 'log_file_name_convert',  
      'standby_file_management'
     );
 
  


6) Criando o DG através do RMAN

-- Source

sqlplus / as sysdba
create pfile='/tmp/initthor.ora' from spfile;
  
  

-- Copia para o destino

cd $ORACLE_HOME/dbs
scp -rv /tmp/initthor.ora oracle@192.168.56.4: 
/oracle/app/oracle/product/12.2.0.1/dbhome_1/initthordg.ora
scp -rv $ORACLE_HOME/dbs/orapwthor oracle@192.168.56.4: 
/oracle/app/oracle/product/12.2.0.1/dbhome_1/orapwthordg

=============================================================
== No servidor do Active dataguard:
=============================================================
mkdir -p /oracle/app/oracle/admin/thordg/adump



7) Editar o novo init

vim $ORACLE_HOME/dbs/initthordg.ora

*.db_name='thor'
*.db_unique_name=thordg
*.enable_pluggable_database=true
*.fal_server='TNSPROD'
*.log_archive_config='DG_CONFIG=(thor,thordg)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
DB_UNIQUE_NAME=thordg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_max_processes=8
*.standby_file_management='AUTO'

Importante: Corrigir também todos os parâmetros que se referencie a base SOURCE. Alterar para o nome do DG.



8) Criando o spfile

sqlplus / as sysdba
startup nomount
create spfile from pfile;
	
SQL> shutdown immediate
SQL> startup nomount



9) Duplicate

-- Conecta na Prod com auxiliary o DG

$ rman target sys/aaa123@TNSPROD auxiliary sys/aaa123@TNSDG

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jan 16 11:34:06 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: THOR (DBID=412756195)
connected to auxiliary database: THOR (not mounted)
	
RMAN>DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;


-- Log

Starting Duplicate Db at 16-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=40 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile  '/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwthor' auxiliary format 
 '/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwthordg'   ;
}
executing Memory Script

Starting backup at 16-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
Finished backup at 16-JAN-18

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''+DATA/THORDG/CONTROLFILE/current.321.965561697'', 
''+DATA/THORDG/CONTROLFILE/current.322.965561697'' comment= ''Set by RMAN'' scope=spfile";
   restore clone from service  'TNSPROD' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   
''+DATA/THORDG/CONTROLFILE/current.321.965561697'', 
''+DATA/THORDG/CONTROLFILE/current.322.965561697'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 16-JAN-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/THORDG/CONTROLFILE/current.325.965561699
output file name=+DATA/THORDG/CONTROLFILE/current.326.965561699
Finished restore at 16-JAN-18

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;
   set newname for clone tempfile  4 to new;
   set newname for clone tempfile  5 to new;
   set newname for clone tempfile  6 to new;
   set newname for clone tempfile  7 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;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;
   set newname for clone datafile  17 to new;
   set newname for clone datafile  18 to new;
   set newname for clone datafile  19 to new;
   set newname for clone datafile  20 to new;
   set newname for clone datafile  21 to new;
   set newname for clone datafile  22 to new;
   set newname for clone datafile  23 to new;
   set newname for clone datafile  24 to new;
   set newname for clone datafile  25 to new;
   set newname for clone datafile  26 to new;
   set newname for clone datafile  27 to new;
   set newname for clone datafile  28 to new;
   restore
   from  nonsparse   from service 
 'TNSPROD'   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

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
renamed tempfile 4 to +DATA in control file
renamed tempfile 5 to +DATA in control file
renamed tempfile 6 to +DATA in control file
renamed tempfile 7 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
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
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 16-JAN-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00018 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00021 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00022 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00023 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00024 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00025 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00026 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00027 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00028 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 16-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=31 STAMP=965561819 
file name=+DATA/THORDG/DATAFILE/system.327.965561709
datafile 3 switched to datafile copy
input datafile copy RECID=32 STAMP=965561819 
file name=+DATA/THORDG/DATAFILE/sysaux.328.965561725
datafile 4 switched to datafile copy
input datafile copy RECID=33 STAMP=965561819 
file name=+DATA/THORDG/DATAFILE/undotbs1.329.965561731
datafile 5 switched to datafile copy
input datafile copy RECID=34 STAMP=965561819 
file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/system.330.965561733
datafile 6 switched to datafile copy
input datafile copy RECID=35 STAMP=965561819 
file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/sysaux.331.965561737
datafile 7 switched to datafile copy
input datafile copy RECID=36 STAMP=965561819 
file name=+DATA/THORDG/DATAFILE/users.332.965561739
datafile 8 switched to datafile copy
input datafile copy RECID=37 STAMP=965561819 
file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/undotbs1.333.965561741
datafile 9 switched to datafile copy
input datafile copy RECID=38 STAMP=965561820 
file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/system.334.965561743
datafile 10 switched to datafile copy
input datafile copy RECID=39 STAMP=965561820 
file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/sysaux.335.965561747
datafile 11 switched to datafile copy
input datafile copy RECID=40 STAMP=965561821 
file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/undotbs1.336.965561753
datafile 12 switched to datafile copy
input datafile copy RECID=41 STAMP=965561821 
file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/users.337.965561755
datafile 13 switched to datafile copy
input datafile copy RECID=42 STAMP=965561821 
file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/system.338.965561757
datafile 14 switched to datafile copy
input datafile copy RECID=43 STAMP=965561822 
file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/sysaux.339.965561761
datafile 15 switched to datafile copy
input datafile copy RECID=44 STAMP=965561822 
file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/undotbs1.340.965561767
datafile 16 switched to datafile copy
input datafile copy RECID=45 STAMP=965561822 
file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/users.341.965561769
datafile 17 switched to datafile copy
input datafile copy RECID=46 STAMP=965561822 
file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/system.342.965561771
datafile 18 switched to datafile copy
input datafile copy RECID=47 STAMP=965561822 
file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/sysaux.343.965561773
datafile 19 switched to datafile copy
input datafile copy RECID=48 STAMP=965561822 
file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/undotbs1.344.965561781
datafile 20 switched to datafile copy
input datafile copy RECID=49 STAMP=965561822 
file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/users.345.965561783
datafile 21 switched to datafile copy
input datafile copy RECID=50 STAMP=965561823 
file name=+DATA/THORDG/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/system.346.965561785
datafile 22 switched to datafile copy
input datafile copy RECID=51 STAMP=965561823 
file name=+DATA/THORDG/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/sysaux.347.965561787
datafile 23 switched to datafile copy
input datafile copy RECID=52 STAMP=965561823 
file name=+DATA/THORDG/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/undotbs1.348.965561795
datafile 24 switched to datafile copy
input datafile copy RECID=53 STAMP=965561823 
file name=+DATA/THORDG/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/users.349.965561797
datafile 25 switched to datafile copy
input datafile copy RECID=54 STAMP=965561823 
file name=+DATA/THORDG/62D405A2F8C03AB1E0530238A8C04E05/DATAFILE/system.350.965561797
datafile 26 switched to datafile copy
input datafile copy RECID=55 STAMP=965561824 
file name=+DATA/THORDG/62D405A2F8C03AB1E0530238A8C04E05/DATAFILE/sysaux.351.965561801
datafile 27 switched to datafile copy
input datafile copy RECID=56 STAMP=965561824 
file name=+DATA/THORDG/62D405A2F8C03AB1E0530238A8C04E05/DATAFILE/undotbs1.352.965561809
datafile 28 switched to datafile copy
input datafile copy RECID=57 STAMP=965561824 
file name=+DATA/THORDG/62D405A2F8C03AB1E0530238A8C04E05/DATAFILE/users.353.965561811
Finished Duplicate Db at 16-JAN-18


10) Corrigir o nome dos control_files.

SQL>  show parameter control_files

SQL> alter system set  
     control_files=
     "'+DATA/THORDG/CONTROLFILE/current.325.965561699',
      '+DATA/THORDG/CONTROLFILE/current.326.965561699'" 
     scope=spfile;


Habilitar o flashback do DG.

SQL> ALTER DATABASE FLASHBACK ON;



11) Conferência

SQL> set pages 120 lines 10000
SQL> COLUMN SWITCHOVER_STATUS FORMAT A20
SQL> COLUMN INSTANCE    FORMAT A10
SQL> SELECT INST_ID, DATABASE_ROLE, DB_UNIQUE_NAME  INSTANCE, OPEN_MODE, FLASHBACK_ON, 
PROTECTION_MODE, PROTECTION_LEVEL,  SWITCHOVER_STATUS FROM GV$DATABASE;
  
  


12) Checagem dos parâmetros no DG

SQL> set pages 120 lines 1000
SQL> col name for a30
SQL> col value forma a100
SQL> select name, value
     from v$parameter
     where name in 
           ('db_name','db_unique_name','log_archive_config',
		     'log_archive_dest_1','log_archive_dest_2',
		     'log_archive_dest_state_1',
            'log_archive_dest_state_2',
		     'remote_login_passwordfile', 'log_archive_format', 
            'log_archive_max_processes',
		     'fal_server','db_file_name_convert', 
            'log_file_name_convert', 'standby_file_management'
           );




13) Habilitando o Data Guard com o Broker

-- SOURCE

sqlplus / as sysdba
alter system set dg_broker_start=true scope=both;
  
  

-- DG

sqlplus / as sysdba
alter system set dg_broker_start=true scope=both;
  
  


14) Vamos agora configurar DGBroker

-- SOURCE

dgmgrl sys/aaa123@TNSPROD
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Jan 16 11:55:19 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "thor"
Connected as SYSDBA.

DGMGRL> create configuration 'DR_THOR' as primary database is thor connect identifier
is TNSPROD;Configuration "DR_THOR" created with primary database "thor"
	
DGMGRL> add database thordg as connect identifier is TNSDG maintained as physical;
Database "thordg" added
	
DGMGRL> enable configuration;
Enabled.



15) Setando o StaticConnectIdentifier para evitar erros no Switchover.

DGMGRL> edit database thor set property  
StaticConnectIdentifier="single122:1521/thor_dgmgrl";
DGMGRL> edit database thordg set property  
StaticConnectIdentifier="single122dg:1521/thordg_dgmgrl";
  
  

16) Verificando as configurações

DGMGRL> show configuration verbose
Configuration - DR_THOR
  Protection Mode: MaxPerformance
  Members:
  thor   - Primary database
  thordg - Physical standby database 

  Properties:
	  FastStartFailoverThreshold      = '30'
	  OperationTimeout                = '30'
	  TraceLevel                      = 'USER'
	  FastStartFailoverLagLimit       = '30'
	  CommunicationTimeout            = '180'
	  ObserverReconnect               = '0'
	  FastStartFailoverAutoReinstate  = 'TRUE'
	  FastStartFailoverPmyShutdown    = 'TRUE'
	  BystandersFollowRoleChange      = 'ALL'
	  ObserverOverride                = 'FALSE'
	  ExternalDestination1            = ''
	  ExternalDestination2            = ''
	  PrimaryLostWriteAction          = 'CONTINUE'
	  ConfigurationWideServiceName    = 'thor_CFG'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


-- Database SOURCE

DGMGRL> show database thor;
Database - thor

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
	thor
Database Status:
SUCCESS


-- Database DG

DGMGRL> show database thordg;
Database - thordg

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 101.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
	thordg

Database Status:
SUCCESS


17) Alterando o modo de proteção

DGMGRL> EDIT DATABASE thor SET PROPERTY  'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'THORDG' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
  

-- Vamos deixar nos DG em Maximum Availability (AFFIRM, SYNC)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
  
  

Importante:
Você pode alterar estes parâmetros com ALTER SYSTEM SET?
Não, pois se fizermos via alter system o broker não vai registrar.

DGMGRL> show configuration verbose
Configuration - DR_THOR
  Protection Mode: MaxAvailability
  Members:
  thor   - Primary database
  thordg - Physical standby database 

  Properties:
	  FastStartFailoverThreshold      = '30'
	  OperationTimeout                = '30'
	  TraceLevel                      = 'USER'
	  FastStartFailoverLagLimit       = '30'
	  CommunicationTimeout            = '180'
	  ObserverReconnect               = '0'
	  FastStartFailoverAutoReinstate  = 'TRUE'
	  FastStartFailoverPmyShutdown    = 'TRUE'
	  BystandersFollowRoleChange      = 'ALL'
	  ObserverOverride                = 'FALSE'
	  ExternalDestination1            = ''
	  ExternalDestination2            = ''
	  PrimaryLostWriteAction          = 'CONTINUE'
	  ConfigurationWideServiceName    = 'thor_CFG'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


18) Agora vamos fazer alguns testes de replicação. Lembrando que ainda não estamos com o Active Dataguard habilitado.

-- SOURCE
-- Antes do Switch

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" 
FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#         LAST_APPLIED_LOG
----------      ----------------
         1                    11


-- DG
-- Antes do Switch

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" 
FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#         LAST_APPLIED_LOG
----------      ----------------
         1                    11


-- Gerar archives na produção e verificar se foram aplicados no DG via ALERT e via SQL

SQL> alter system switch logfile;

-- SOURCE
-- Depois do Switch

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" 
FROM V$LOG_HISTORY GROUP BY THREAD#;


THREAD#         LAST_APPLIED_LOG
----------      ----------------
         1                    15


-- DG
-- Depois do Switch

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" 
FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD#         LAST_APPLIED_LOG
----------      ----------------
         1                    15

SELECT 
	ARCH.THREAD# "Thread", 
	ARCH.SEQUENCE# "Last Sequence Received", 
	APPL.SEQUENCE# "Last Sequence Applied", 
	(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
	(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN 
	(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
	(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN 
	(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
   WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;	
	

Thread  Last Sequence Received  Last Sequence Applied  Difference
------  ----------------------  ---------------------  ----------
     1                     15                      15           0


19) Verificações adicionais

COLUMN SWITCHOVER_STATUS FORMAT A20
COLUMN INSTANCE    FORMAT A10	
SELECT INST_ID, DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, 
FLASHBACK_ON, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM GV$DATABASE;

Importante:

  • Em “OPEN_MODE”  é onde nos mostra que o Active Dataguard não está habilitado ainda. Quando estiver, estará "Read Only" no lugar de "Mounted".
  • Precisa deixar o DG com o mesmo PROTECTION_MODE que a PROD.



====================================================
== ACTIVE DATAGUARD
====================================================

20) Habilitando o Active DataGuard

Lembrete: Um standby físico com active dataguard tem que ser aberto em modo open e depois colocado em modo recover. Um standby físico sem o active fica em modo mount e depois em modo recover. Neste momento, não se aplica mais os archives e sim os redos standby's diretamente.

-- Cancele o recover na máquina de destino

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;


-- No DG, "abra" a base

ALTER  DATABASE OPEN;


-- No DG, inicie o RECOVER com o banco de dados aberto para leitura.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING  CURRENT LOGFILE DISCONNECT;


-- Verificar

COLUMN SWITCHOVER_STATUS FORMAT A20
COLUMN INSTANCE     FORMAT A10
SELECT INST_ID, DATABASE_ROLE, DB_UNIQUE_NAME  INSTANCE, OPEN_MODE, FLASHBACK_ON, 
PROTECTION_MODE, PROTECTION_LEVEL,  SWITCHOVER_STATUS FROM GV$DATABASE;
  
  



Executar as mesmas verificações feitas no 18.


Importante: Após habilitarmos o uso do ACTIVE no CDB, precisamos "abrir" os pdb's para que fiquem acessiveis também.

SQL>  show pdbs


SQL> ALTER PLUGGABLE DATABASE all OPEN;
Pluggable database altered.
SQL> show pdbs



Agora basta fazer testes criando tabelas e executando operações nos pdb’s para verificar a replicação acontecendo.

No próximo artigo iremos fazer a validate para o switchover, o switchover para a instance do DG e ainda realizar mais testes de replicação dos PDB’s como a criação de novos pdb’s e exclusão de pdb’s no source.



José Mario Barduchi é DBA/ATG Oracle a mais de 18 anos tendo atuado em diversos projetos de implantação e administração de grandes ambientes. É Formado em Engenharia de Software pela Universidade Metodista de São Paulo, pós-graduado em Banco de Dados Oracle pela FIAP. É certificado OCE RAC 11g, OCP 10g e 11g. Já trabalhou em consultorias Oracle Partner e atualmente é DBA de uma grande rede de hospitais e professor de alta disponibilidade em curso de pós-graduação. Em Novembro/2017 foi nomeado Oracle ACE Associate. É um dos organizadores dos eventos "DBA Brasil". Disponibiliza seus artigos em seu blog: http://www.oradicas.com.br

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.