Realocando Pluggable Database (PDB) em modo READ WRITE em ambiente Multi-Tenant com Real Application Cluster (RAC)
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.