Realocando Pluggable Database (PDB) em modo READ WRITE em ambiente Multi-Tenant com Real Application Cluster (RAC)

Por Y. V. Ravi Kumar , Rodrigo Mufalani Oracle ACE e Konstantin Kerekovski
Publicado en Junio 2017


Revisado por Marcelo Pivovar

Introdução:

O Oracle 12c R2 (12.2.0.1.0) agora permite que Pluggable databases (PDBs) sejam realocados enquanto estão em modo READ WRITE (abertos para uso). Essa realocação pode ser usada para migrar um PDB de um servidor ou um Real Application Cluster (RAC) em um outro cluster completamente diferente ou entre CDBs no mesmo servidor/cluster. Isso é incrível! Isso pode ser feito tanto com downtime ou sem downtime. Neste artigo, iremos através do método que exige um pequeno downtime – apenas o downtime requerido para baixar a aplicação e se reconectar ao PDB com uma conexão TNS atualizada.

Configuração

Origem

Operating System:           Oracle Enterprise Linux 6.8 (Santiago)
Database:                   Oracle Database 12c R2 (12.2.0.1.0)
Oracle Clusterware:         Oracle Clusterware 12c R2 (12.2.0.1.0)
Hostname:                   tstldb101,tstldb102
IP address:                 192.168.0.61,192.168.0.62
Cluster Name:               TSTLDB01
Container Database (CDB):   PRODCDB
Pluggable Database (PDB):   PRODPDB

Destino:

Operating System:         Oracle Enterprise Linux 6.6 (Santiago)
Database:                 Oracle Database 12c R2 (12.2.0.1.0)
Oracle Clusterware:       Oracle Clusterware 12c R2 (12.2.0.1.0)
Hostname:                 tstldb201,tstldb202
IP address:               192.168.0.68, 192.168.0.69
Cluster Name:             TSTLDB02
Container Database (CDB): DEVCDB

Cenário

Vamos considerar o cenário onde você, o DBA, foi encarregado de realocar um database de um datacenter para outro. Isso é um procedimento relativamente comum e migrações são atividades comuns para DBAs. Porém, isso consome muito planejamento e geralmente usamos EXPDP/IMPDP ou RMAN DUPLICATE, em outras palavras, tem que atenção aos detalhes. Com uma configuração bem simples, PDB RELOCATE irá deixar fazer isso com apenas dois comandos.

O que iremos precisar:

  • Entrada de TNS no cluster destinoque apontam para o CDB (PRODCDB origem)
  • Um usuário comum, com role de SYSDBA ou SYSOPER no CDB (PRODCDB origem).
  • Um Public DBLINK conectando o CDB (PROD2CDB destino) ao CDB (PRODCDB origem)com um usuário comum.
  • Dois containers databases da versão 12c R2 (12.2.0.1.0)rodando em modo ARCHIVELOG.

Ambiente atual

Primeiro, vamos fazer balanço do ambiente atual. Analisaremos os recursos do cluster em ambos RAC, de origem TSTLDB01, e também o RAC de destino TSTLDB02. Iremos inspecioanr os serviços que estão sendo ouvidos por cada LOCAL listener nos nodes do cluster.

Recursos do Cluster - TSTLDB01

ORACLE_SID = [prodcdb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@tstldb101 trace]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.ASMNET2LSNR_ASM.lsnr
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.DATA.dg
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.FRA.dg
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.MGMT.dg
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.OCR_VOTE.dg
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.chad
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.net1.network
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.ons
               ONLINE  ONLINE       tstldb101                STABLE
               ONLINE  ONLINE       tstldb102                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      tstldb101                STABLE
               OFFLINE OFFLINE      tstldb102                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tstldb102                STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       tstldb101                169.254.93.217 
192.168.10.61 
192.168.11.61,
STABLE
ora.asm
      1        ONLINE  ONLINE       tstldb101                Started,STABLE
      2        ONLINE  ONLINE       tstldb102                Started,STABLE
      3        OFFLINE OFFLINE                                STABLE
ora.cvu
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       tstldb101                Open,STABLE
ora.prodcdb.db
      1        ONLINE  ONLINE       tstldb101                Open,HOME=/u01/app/o
                                                             racle/product/12.2.0/dbhome_1,
STABLE
      2        ONLINE  ONLINE       tstldb102                Open,HOME=/u01/app/o
                                                             racle/product/12.2.0/dbhome_1,
STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tstldb102                STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.tstldb101.vip
      1        ONLINE  ONLINE       tstldb101                STABLE
ora.tstldb102.vip
      1        ONLINE  ONLINE       tstldb102                STABLE


Recursos do Cluster - TSTLDB02


[oracle@tstldb201 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.ASMNET2LSNR_ASM.lsnr
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.DATA.dg
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.FRA.dg
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.OCR_VOTE.dg
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.chad
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.net1.network
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.ons
               ONLINE  ONLINE       tstldb201                STABLE
               ONLINE  ONLINE       tstldb202                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      tstldb201              STABLE
               OFFLINE OFFLINE      tstldb202               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tstldb202                STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       tstldb201                169.254.73.83
192.168.10.63
192.168.11.63,
STABLE
ora.asm
      1        ONLINE  ONLINE       tstldb201                Started,STABLE
      2        ONLINE  ONLINE       tstldb202                Started,STABLE
ora.cvu
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       tstldb201                Open,STABLE
ora.prod2cdb.db
      1        ONLINE  ONLINE       tstldb201                Open,HOME=/u01/app/o
                                                             racle/product/12.2.0/dbhome_1,
STABLE
      2        ONLINE  ONLINE       tstldb202                Open,HOME=/u01/app/o
                                                             racle/product/12.2.0/dbhome_1,
STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tstldb202                STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.tstldb201.vip
      1        ONLINE  ONLINE       tstldb201                STABLE
ora.tstldb202.vip
1        ONLINE  ONLINE       tstldb202                STABLE
  

Configuração do TNS no TSTLDB02 (Cluster Destino)

Nós iremos precisar garantir que o arquivo TNSNAMES.ORA do cluster destino tem as entradas apropriadas para o CBD de origem no cluster TSTLDB01. O TNSNAMES.ora reside no diretório $ORACLE_HOME/network/admin. Iremos marcar as entradas de TNS relevantes.

[oracle@tstldb201 ~]$ cat /u01/app/oracle/product/12.2.0/
dbhome_1/network/admin/tnsnames.ora                        
# tnsnames.ora Network Configuration File: /u01/app/oracle
/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD2CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb02-scan)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod2cdb.world)
    )
  )
 PRODCDB =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb01-scan)     (PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = prodcdb.world)     )   )

PRODPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb01-scan)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodpdb.world)
    )
  )

[oracle@tstldb201 ~]$ ssh tstldb202 cat /u01/app/oracle/
product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/
product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD2CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb02-scan)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod2cdb.world)
    )
  )
 PRODCDB =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb01-scan)     (PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = prodcdb.world)     )   )

PRODPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tstldb01-scan)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodpdb.world)
)
  )

Criar um usuário comum no Container Database (CDB) de origem, PRODCDB.

Iremos logar no CDB de origem, PRODCDB no cluster TSTLDB01 e criar um usuario comumcom o privilégio de sistema SYSOPER e a role CONNECT.

[oracle@tstldb101 admin]$ . oraenv
ORACLE_SID = [oracle] ? prodcdb1
The Oracle base remains unchanged with value /u01/
app/oracle

[oracle@tstldb101 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 
14:48:36 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
 - 64bit Production

SQL>create user c##relocation_user identified by 
"database_123" container=ALL;
User created.

SQL>grant sysoper to c##relocation_user container=ALL;
Grant succeeded.

SQL>grant connect to c##relocation_user container=ALL;
Grant succeeded.

Criar um DBLINK do CDB de destino (PROD2CDB) para o CDB (PRODCDB) de origem

Agora, iremos logar no CDB de destino, PROD2CDB e criar o database que irá conectar ao CDB de origem PRODCDB com as credenciais do nosso usuário comum C##RELOCATION_USER. Um bom teste é fazer um select na dual no CDB de origem para certificarmos que nosso DBLINK está funcionando.

[oracle@tstldb201 ~]$ . oraenv
ORACLE_SID = [oracle] ? prod2cdb1
The Oracle base remains unchanged with value /u01/app/
oracle

[oracle@tstldb201 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 
14:51:26 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
 - 64bit Production

SQL> create public database link SOURCE 
connect to C##RELOCATION_USER 
identified by "database_123" 
using 'PRODCDB';

Database link created.

SQL> select 'x' from dual@source;

'
-
x

Vamos checar o STATUS dos PDBs em ambos de nossos containers antes de fazer o RELOCATE

Logon no Container Database - PRODCDB

SQL> select name from v$database;

NAME
---------------
PRODCDB

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   -------------
PRODPDB           NORMAL
PDB$SEED          NORMAL

SQL> select inst_id, name, open_mode from gv$containers
 order by 2,1;

INST_ID   NAME              OPEN_MODE
----------  ---------------   -------------------
1     CDB$ROOT          READ WRITE
2     CDB$ROOT          READ WRITE
1     PDB$SEED          READ ONLY
2     PDB$SEED          READ ONLY
1     PRODPDB           READ WRITE
2     PRODPDB           READ WRITE

6 rows selected.

Logon no Container Database - PROD2CDB

SQL>select name from v$database;

NAME
----------------
PROD2CDB

SQL>select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   --------------
PDB$SEED          NORMAL

SQL>select inst_id, name, open_mode from gv$containers 
order by 2,1;

INST_ID   NAME              OPEN_MODE
------------  ---------------   -------------------
1     CDB$ROOT          READ WRITE
2     CDB$ROOT          READ WRITE
1     PDB$SEED          READ ONLY
2     PDB$SEED          READ ONLY

Relocate do PDB

Iremos realocar o pluggable database PRODPDB no container database PRODCDB no RAC cluster TSTLDB01 para o container database PROD2CDB no cluster TSTLDB02.

Primeiro, nós iremos conectar ao PROD2CDB como SYSDBA

[oracle@tstldb201 ~]$ . oraenv
ORACLE_SID = [oracle] ? prod2cdb1
The Oracle base remains unchanged with value /u01/
app/oracle

[oracle@tstldb201 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 15 
15:06:26 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 
- 64bit Production

Agora iremos iniciar o comando RELOCATE. O comando RELOCATE é na verdade apenas umcomando CREATE PLUGGABLE DATABASE com as cláusulas especiais RELOCATE e FROM.

SQL> create pluggable database PRODPDB from PRODPDB@source 
relocate;
Pluggable database created.

Status dos PDBs depois do comando RELOCALTE

Você irá notar que o PDB não foi realocado ainda como CBD de origem (PRODCBD) ainda mostra o PRODPDB em modo READ WRITE and status normal. No PROD2CBD, o CBD está com status RELOCATING e está MOUNTED. A verdadeira mágica ocorre quando você abre o PDB no modo READ e WRITE.

Logonno Container Database - PRODCDB

SQL> select name from v$database;

NAME
---------------
PRODCDB

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   ------------
PRODPDB           NORMAL
PDB$SEED        NORMAL

SQL> select inst_id, name, open_mode from gv$containers 
order by 2,1;

INST_ID   NAME              OPEN_MODE
----------  ---------------   -------------------
1     CDB$ROOT          READ WRITE
2     CDB$ROOT          READ WRITE
1     PDB$SEED          READ ONLY
2     PDB$SEED          READ ONLY
1     PRODPDB           READ WRITE
2     PRODPDB           READ WRITE

6 rows selected.

Logonno Container Database - PROD2CDB

SQL> select name from v$database;

NAME
-----------------
PROD2CDB

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   -------------
PRODPDB           RELOCATING
PDB$SEED          NORMAL

SQL> select inst_id, name, open_mode from gv$containers 
order by 2,1;


INST_ID   NAME              OPEN_MODE
----------  -----------------   -------------------
1     CDB$ROOT          READ WRITE
2     CDB$ROOT          READ WRITE
1     PDB$SEED          READ ONLY
2     PDB$SEED          READ ONLY
1     PRODPDB           MOUNTED
2     PRODPDB           MOUNTED

6 rows selected.

Entradas no Alert Log

Nas entradas no Alert.log abaixo, você nota que o PDB foi clonado e atualizado com os dados de redolog do database de origem.Tudo isso ocorre através do database link (SOURCE)

PRODCDB

2017-03-15T15:08:06.551794-04:00
PRODPDB(3):Opatch XML is skipped for PDB PRODPDB (conid=3)
2017-03-15T15:08:07.838624-04:00
PRODPDB(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated

PROD2CDB

2017-03-15T15:08:03.697491-04:00
create pluggable database PRODPDB from PRODPDB@source relocate
2017-03-15T15:08:06.433008-04:00
Opatch validation is skipped for PDB PRODPDB (con_id=3)
2017-03-15T15:09:37.705395-04:00
PRODPDB(3):Endian type of dictionary set to little
2017-03-15T15:09:38.959056-04:00
****************************************************************
Pluggable Database PRODPDB with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000e1
****************************************************************
2017-03-15T15:09:48.454617-04:00
Applying media recovery for pdb-4099 from SCN 3090713 to SCN 
3091669
Remote log information: count-2
thr-2, seq-13, logfile-+FRA/PRODCDB/partial_archivelog/2017_03_15/
thread_2_seq_13.346.938704183, los-3053923, nxs-18446744073709551615
thr-1, seq-16, logfile-+FRA/PRODCDB/partial_archivelog/2017_03_15/
thread_1_seq_16.347.938704187, los-3054706, nxs-18446744073709551615
attach called for domid 3 (domuid: 0xb90f74f7, options: 0x0, pid: 
29136)
queued attach broadcast request 0x77e894c8
2017-03-15T15:09:48.550034-04:00
* allocate domain 3, valid ? 1
 all enqueues go to domain 0
2017-03-15T15:09:48.980915-04:00
PRODPDB(3):Media Recovery Start
2017-03-15T15:09:49.003091-04:00
PRODPDB(3):Serial Media Recovery started
2017-03-15T15:09:49.537619-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_1_seq_16.347.938704187
2017-03-15T15:09:49.944589-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_2_seq_13.346.938704183
2017-03-15T15:09:58.903184-04:00
PRODPDB(3):Incomplete Recovery applied until change 3091669 time
 03/15/2017 15:09:40
2017-03-15T15:09:58.911190-04:00
PRODPDB(3):Media Recovery Complete (prod2cdb1)
detach called for domid 3 (domuid: 0xb90f74f7, options: 0x0, pid: 29136)
queued detach broadcast request 0x77e89470
2017-03-15T15:09:59.061449-04:00
freeing rdom 3
Completed: create pluggable database PRODPDB from PRODPDB@source 
relocate

Finalizando o relocate

SQL> select name from v$database;

NAME
---------------
PROD2CDB

SQL> alter pluggable database prodpdb open instances=ALL;

Você pode finalizar o relocate abrindo os PDBs no CBD destino, PROD2CDB. Isso irá atualizar com as informações de redolog do CDB de origem através do dblink e irá fechar o PDB em sua localização original (PRODCBD) e eliminar qualquer arquivo OMF que o PDB estava usando.

Alert Log Entries - PRODCDB

2017-03-15T15:19:07.360593-04:00
PRODPDB(3):JIT: pid 2919 requesting stop
Pluggable database PRODPDB closed
PRODPDB(3):JIT: pid 2919 requesting stop
2017-03-15T15:19:09.793396-04:00
 Received detach msg from inst 2 for dom 3
2017-03-15T15:19:10.349020-04:00
PRODPDB(3):detach called for domid 3 (domuid: 0x5458d6ca, 
options: 0x0, pid: 2919)
PRODPDB(3):queued detach broadcast request 0x78e893c0
2017-03-15T15:19:10.459344-04:00
freeing rdom 3
2017-03-15T15:19:10.854399-04:00
Pluggable database PRODPDB closed
2017-03-15T15:19:52.483356-04:00
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/DATAFILE/users.282.938693337
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/DATAFILE/undo_2.281.938693331
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/TEMPFILE/temp.279.938693287
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/DATAFILE/undotbs1.280.938693251
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/DATAFILE/sysaux.278.938693251
Deleted Oracle managed file +DATA/PRODCDB/4AC823DE5A5258F2E05
33D00A8C0BEFA/DATAFILE/system.276.938693251

Alert Log Entries - PROD2CDB

2017-03-15T15:18:38.871444-04:00
alter pluggable database prodpdb open instances=ALL
2017-03-15T15:18:50.968299-04:00
Applying media recovery for pdb-4099 from SCN 3091669 to SCN 
3092670
Remote log information: count-2
thr-2, seq-13, logfile-+FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_2_seq_13.348.938704725, los-3053923, 
nxs-18446744073709551615
thr-1, seq-16, logfile-+FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_1_seq_16.349.938704729, los-3054706, 
nxs-18446744073709551615
PRODPDB(3):Media Recovery Start
2017-03-15T15:18:50.975030-04:00
PRODPDB(3):Serial Media Recovery started
2017-03-15T15:18:51.243310-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_1_seq_16.349.938704729
2017-03-15T15:18:51.722207-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_2_seq_13.348.938704725
2017-03-15T15:19:01.079830-04:00
PRODPDB(3):Incomplete Recovery applied until change 3092670 
time 03/15/2017 15:18:26
2017-03-15T15:19:01.088514-04:00
PRODPDB(3):Media Recovery Complete (prod2cdb1)
PRODPDB(3):Autotune of undo retention is turned on.
PRODPDB(3):This instance was first to open pluggable database 
PRODPDB (container=3)
PRODPDB(3):attach called for domid 3 (domuid: 0xb90f74f7, 
options: 0x0, pid: 28066)
PRODPDB(3):queued attach broadcast request 0x77e89418
2017-03-15T15:19:01.514209-04:00
* allocate domain 3, valid ? 1
 all enqueues go to domain 0
2017-03-15T15:19:02.472600-04:00
PRODPDB(3):Undo initialization finished serial:0 start:15311654 
end:15311654 diff:0 ms (0.0 seconds)
PRODPDB(3):Database Characterset for PRODPDB is AL32UTF8
2017-03-15T15:19:03.598878-04:00
PRODPDB(3):Opatch validation is skipped for PDB PRODPDB 
(con_id=0)
2017-03-15T15:19:05.277234-04:00
PRODPDB(3):Opening pdb with no Resource Manager plan active
PRODPDB(3):JIT: pid 28066 requesting stop
PRODPDB(3):detach called for domid 3 (domuid: 0xb90f74f7, 
options: 0x0, pid: 28066)
PRODPDB(3):queued detach broadcast request 0x77e893c0
2017-03-15T15:19:06.134410-04:00
kjbdomchklazy: domain 3 was put in lazy mode because of: enqueue
 locks [GONE], fusion locks [OPEN] and resource cache [FLUSHED].
freeing rdom 3
2017-03-15T15:19:16.524446-04:00
Applying media recovery for pdb-4099 from SCN 3092670 to SCN 
3092863
Remote log information: count-2
thr-2, seq-13, logfile-+FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_2_seq_13.350.938704751, los-3053923, 
nxs-18446744073709551615
thr-1, seq-16, logfile-+FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_1_seq_16.351.938704755, los-3054706, 
nxs-18446744073709551615
PRODPDB(3):Media Recovery Start
2017-03-15T15:19:16.529161-04:00
PRODPDB(3):Serial Media Recovery started
2017-03-15T15:19:16.806200-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_2_seq_13.350.938704751
2017-03-15T15:19:17.392338-04:00
PRODPDB(3):Media Recovery Log +FRA/PRODCDB/partial_archivelog/
2017_03_15/thread_1_seq_16.351.938704755
2017-03-15T15:19:46.431592-04:00
PRODPDB(3):Incomplete Recovery applied until change 3092863 
time 03/15/2017 15:19:10
2017-03-15T15:19:46.439875-04:00
PRODPDB(3):Media Recovery Complete (prod2cdb1)
PRODPDB(3):This instance was first to open pluggable database 
PRODPDB (container=3)
PRODPDB(3):attach called for domid 3 (domuid: 0xb90f74f7, 
options: 0x0, pid: 28066)
PRODPDB(3):queued attach broadcast request 0x77e89368
2017-03-15T15:19:46.931622-04:00
* allocate domain 3, valid ? 1
 all enqueues go to domain 0
2017-03-15T15:19:47.530464-04:00
PRODPDB(3):[28066] Successfully onlined Undo Tablespace 2.
PRODPDB(3):Undo initialization finished serial:0 start:15356545 
end:15356725 diff:180 ms (0.2 seconds)
PRODPDB(3):Database Characterset for PRODPDB is AL32UTF8
PRODPDB(3):detach called for domid 3 (domuid: 0xb90f74f7, 
options: 0x0, pid: 28066)
PRODPDB(3):queued detach broadcast request 0x77e89310
2017-03-15T15:19:48.219210-04:00
freeing rdom 3
2017-03-15T15:19:51.361051-04:00
PRODPDB(3):This instance was first to open pluggable database 
PRODPDB (container=3)
PRODPDB(3):attach called for domid 3 (domuid: 0xb90f74f7, 
options: 0x0, pid: 28066)
PRODPDB(3):queued attach broadcast request 0x77e892b8
2017-03-15T15:19:51.611954-04:00
* allocate domain 3, valid ? 1
 all enqueues go to domain 0
2017-03-15T15:19:52.389210-04:00
+-+PRODPDB(3):[28066] Successfully onlined Undo Tablespace 2.
PRODPDB(3):Undo initialization finished serial:0 start:15361246 
end:15361573 diff:327 ms (0.3 seconds)
Opatch validation is skipped for PDB PRODPDB (con_id=3)
PRODPDB(3):Deleting old file#65 from file$
PRODPDB(3):Deleting old file#66 from file$
PRODPDB(3):Deleting old file#67 from file$
PRODPDB(3):Deleting old file#68 from file$
PRODPDB(3):Deleting old file#69 from file$
PRODPDB(3):Adding new file#60 to file$(old file#65)
PRODPDB(3):Adding new file#61 to file$(old file#66)
PRODPDB(3):Adding new file#62 to file$(old file#67)
PRODPDB(3):Adding new file#63 to file$(old file#68)
PRODPDB(3):Adding new file#64 to file$(old file#69)
PRODPDB(3):Successfully created internal service prodpdb.
world at open
***********************************************************
Post plug operations are now complete.
Pluggable database PRODPDB with pdb id - 3 is now marked as 
NEW.
***********************************************************
PRODPDB(3):Pluggable database PRODPDB dictionary check beginning
PRODPDB(3):Pluggable Database PRODPDB Dictionary check complete
PRODPDB(3):Database Characterset for PRODPDB is AL32UTF8
PRODPDB(3):Opatch validation is skipped for PDB PRODPDB (con_id=0)
2017-03-15T15:19:55.401586-04:00
PRODPDB(3):JIT: pid 28066 requesting full stop
2017-03-15T15:19:59.061057-04:00
PRODPDB(3):JIT: pid 28066 requesting full stop
2017-03-15T15:20:03.703415-04:00
PRODPDB(3):Opening pdb with no Resource Manager plan active
2017-03-15T15:20:04.880406-04:00
Pluggable database PRODPDB opened read write
2017-03-15T15:20:09.578702-04:00
Completed: alter pluggable database prodpdb open instances=ALL

Status dos PDBs depois da abertura PRODPDB no CDB destino, PROD2CDB

Aqui, podemos ver claramente que o PDB não existe mais na origem, PRODCDB, e está completamente operacional no destino, PROD2CDB.

Logonno Container Database - PRODCDB

SQL> select name from v$database;

NAME
---------------
PRODCDB

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   -------------
PDB$SEED          NORMAL

SQL> select inst_id, name, open_mode from gv$containers 
order by 2,1;

INST_ID     NAME            OPEN_MODE
----------    ----------------  -------------------
1       CDB$ROOT          READ WRITE
2       CDB$ROOT          READ WRITE
1       PDB$SEED          READ ONLY
2       PDB$SEED  READ ONLY

Logon no Container Database - PROD2CDB

SQL> select name from v$database;

NAME
---------------
PROD2CDB

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME          STATUS
---------------   -------------
PRODPDB           NORMAL
PDB$SEED          NORMAL

SQL> select inst_id, name, open_mode from gv$containers 
order by 2,1;

INST_ID   NAME              OPEN_MODE
----------  -----------------   -------------------
1     CDB$ROOT          READ WRITE
2     CDB$ROOT          READ WRITE
1     PDB$SEED          READ ONLY
2     PDB$SEED          READ ONLY
1     PRODPDB           READ WRITE
2     PRODPDB           READ WRITE

6 rows selected.

Conclusão

Neste artigo, mostramos em detalhes, o que é necessário para realocar PDBs entre diferentes databases clusters usando RAC 12cR2. Esta mesma operação também irá funcionar em single instances CDBs da mesma maneira.Com essa funcionalidade, DBAs Oracle irão outra maneira incrível a sua disposição quando fizerem migrações de bancos de dados.


Y V RaviKumar é um Oracle ACE e Oracle Certified Master (OCM) com 18 anos de experiência em instituições financeiras, serviços financeiros e seguros (BFSI) e atuou em diversos papeis como Senior Database Architect e Production DBA. Ele também é OCP em Oracle 8i, 9i, 10g, 11g & 12c e Certificado em Golden Gate, RAC, Performance Tuning& Oracle Exadata. Ele continua motivando muitos DBAs e ajudando a Oracle Community publicando suas dicas /ideias/sugestões/soluções em seu blog. Ele escreveu 40+ artigos OTN sobre Oracle Exadata, Oracle RAC e Oracle GoldenGate para a OTN em Espanhol, OTN em Português e OTN em inglês e 19 artigos para a TOAD World, 2 Artigos para o UKOUG, 3 Artigos para OTech Magazine e 2 Artigos para a Redgate. Ele é membro do AllIndia Oracle UserGroup (AIOUG) e frequente Oracle speaker in @NYOUG, @OTN, AIOUG, Sangam e IOUG. Ele desenha, projeta e implementa Core Banking System (CBS) Databases para o Central Banks em dois países – India e Mahe, Seychelles. Ele é Co-Founder do OraWorld (www.oraworld.com). Leia mais sobre o seu perfil na LaserSoft

Rodrigo Mufalani é um DBA Sr. com mais de 10 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Performance & Tuning e RAC. É palestrante em eventos de Oracle como: OTN LAD TOUR e outros. Atualmente trabalha como consultor diversas empresas no segmento de variados ramos como: Educação, Saúde, Tecnologia, Seguros e etc. Foi o terceiro Oracle ACE a ser nomeado no Brasil e é OCP DBA nas versões 10g e 11g. Atualmente trabalha na Mufalani. Twitter @mufalani / blog www.mufalani.com.br/blog

Konstantin Kerekovskié um profissional Oracle com mais de 6 anos de experiência no ramo de serviços financeiros. Ele é membro do Independent Oracle User Group (IOUG) e do Suncoast Oracle User Group (SOUG). Ele tem trabalhado como Senior e Líder Database Administrator com suporte ao ramo de serviços financeiros. Ele é palestrante do IOUG Collaborate 2017 e do SOUG meetings em Tampa Bay. Ele é um expert em Oracle Real Application Cluster, ASM, Goldengate e Oracle 11g e 12c.

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.