-
Notifications
You must be signed in to change notification settings - Fork 9
Clonage d'un PDB
PhilippeLeroux edited this page Oct 8, 2018
·
10 revisions
Fonctionne à partir de la 12cR2
-
Serveur :
srvplutot01CDBPLUTOT(Pas de Grid Infra)oracle@srvplutot01:PLUTOT:~> lspdbs SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 09:40:38 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connecte a : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Instance PDB name Open mode Open time Size (Mb) RECOVERY RES State ---------- ------------ ---------- -------------- ------------ -------- --- -------------- PLUTOT CDB$ROOT READ WRITE 17/05/09 09:38 0 ENABLED NO NOT SAVED PLUTOT PDB$SEED READ ONLY 17/05/09 09:38 774 ENABLED NO NOT SAVED PLUTOT PDB01 READ WRITE 17/05/09 09:38 844 ENABLED NO OPENSur
PDB01les schémas de démos ont été installés pour faire un peu de volume. -
Serveur :
srvplutard01CDBPLUTARD(Pas de Grid Infra)oracle@srvplutard01:PLUTARD:~> lspdbs SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 09:40:50 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connecte a : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Instance PDB name Open mode Open time Size (Mb) RECOVERY RES State ---------- ------------ ---------- -------------- ------------ -------- --- -------------- PLUTARD CDB$ROOT READ WRITE 17/05/09 09:38 0 ENABLED NO NOT SAVED PLUTARD PDB$SEED READ ONLY 17/05/09 09:38 774 ENABLED NO NOT SAVED 2 lignes selectionnees.
Cloner le PDB01 de PLUTOT sur PLUTARD
-
A partir de la 18c il faut donner un privilège à l'utisateur
pdbadmin.Se connecter sur la base
PLUTOT:SQL> alter session set container=PDB01; SQL> grant create pluggable database to pdbadmin container=current;La commande
grant create pluggable database to pdbadmin container=PDB01;ne fonctionne pas.Pour information : PDB switchover
-
Ajout de l'alias
pdb01_on_plutotsur le serveursrvplutard01:J'utilise le service par défaut du PDB.
oracle@srvplutard01:PLUTARD:admin> cat tnsnames.ora pdb01_on_plutot = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = srvplutot01) (PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb01) ) )Cet alias sera utilisé par le dblink.
-
Test de connexion
oracle@srvplutard01:PLUTARD:sql> sqlplus sys/Oracle12@pdb01_on_plutot as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 09:47:55 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connecte a : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production sys@pdb01> @lsdbrole DB name UQ name Open mode DB role Broker CDB Flashback ---------- ---------- ---------- ---------------- -------- --- ---------- PLUTOT PLUTOT READ WRITE PRIMARY DISABLED YES YES 1 ligne selectionnee.On est bien connecté sur le serveur
srvplutot01. -
Sur le CDB
PLUTARDcréation d'un dblink pointant sur le PDBpdb01dePLUTOTLe compte
pdbadminet le mot passeOracle12sont ceux utilisés lors de la création dePDB01.sys@cdb$root> create database link pdb01_on_plutot connect to pdbadmin identified by Oracle12 using 'pdb01_on_plutot'; Lien de base de donnees cree. -
Clonage du PDB
sys@cdb$root> create pluggable database pdb01_cloned from pdb01@pdb01_on_plutot; Base de donnees pluggable creee. Ecoule : 00 :00 :52.27 -
Validation
sys@cdb$root> @lspdbs Instance PDB name Open mode Open time Size (Mb) RECOVERY RES State ---------- ------------ ---------- -------------- ------------ -------- --- -------------- PLUTARD CDB$ROOT READ WRITE 17/05/09 09:38 0 ENABLED NO NOT SAVED PLUTARD PDB$SEED READ ONLY 17/05/09 09:38 774 ENABLED NO NOT SAVED PLUTARD PDB01_CLONED MOUNTED 17/05/09 09:59 0 ENABLED NOT SAVED 3 lignes selectionnees. -
Ouverture du PDB :
sys@cdb$root> alter pluggable database PDB01_CLONED open; Base de donnees pluggable modifiee. sys@cdb$root> alter pluggable database PDB01_CLONED save state; Base de donnees pluggable modifiee. sys@cdb$root> @lspdbs Instance PDB name Open mode Open time Size (Mb) RECOVERY RES State ---------- ------------ ---------- -------------- ------------ -------- --- -------------- PLUTARD CDB$ROOT READ WRITE 17/05/09 09:38 0 ENABLED NO NOT SAVED PLUTARD PDB$SEED READ ONLY 17/05/09 09:38 774 ENABLED NO NOT SAVED PLUTARD PDB01_CLONED READ WRITE 17/05/09 10:26 844 ENABLED NO OPEN 3 lignes selectionnees. -
Liste des services :
sys@cdb$root> @lssrv Name Network name Enabled pdb ---------------- ---------------- -------- ---------------- pdb01_oci pdb01_oci NO PDB01_CLONED pdb01_java pdb01_java NO PDB01_CLONED pdb01_cloned pdb01_cloned NO PDB01_CLONED SYS$BACKGROUND NO CDB$ROOT SYS$USERS NO CDB$ROOT PLUTARDXDB PLUTARDXDB NO CDB$ROOT PLUTARD PLUTARD NO CDB$ROOT 7 lignes selectionnees.Les services sont présents mais pas renommés, il faut donc les supprimer.
Avec le Grid Infra, il faut les supprimer puis les recréer avec
srvctl.
-
paris
Remarque : j'ai du passer
memory_targetà 900Mb, sinon crash régulier.oracle@srvparis01:PARIS:db> lsdbrole DB name UQ name Open mode DB role Broker Guard Logging CDB Flashback ---------- ---------- ---------- ---------------- -------- ------- ------- --- ---------- PARIS PARIS READ WRITE PRIMARY DISABLED NONE NO YES YES oracle@srvparis01:PARIS:db> lspdbs Instance PDB name Open mode Open time Size (Mb) Reco sta Save state ---------- ------------ ---------- -------------- ---------- -------- -------------- PARIS CDB$ROOT READ WRITE 18/02/22 08:22 0 ENABLED NOT SAVED PARIS PDB$SEED READ ONLY 18/02/22 08:22 764 ENABLED NOT SAVED PARIS PDB01 READ WRITE 18/02/22 08:22 764 ENABLED NOT SAVED oracle@srvparis01:PARIS:db> -
Antibes
oracle@srvantibes01:ANTIBES:db> lsdbrole DB name UQ name Open mode DB role Broker Guard Logging CDB Flashback ---------- ---------- ---------- ---------------- -------- ------- ------- --- ---------- ANTIBES ANTIBES READ WRITE PRIMARY DISABLED NONE NO YES YES oracle@srvantibes01:ANTIBES:db> lspdbs Instance PDB name Open mode Open time Size (Mb) Reco sta Save state ---------- ------------ ---------- -------------- ---------- -------- -------------- ANTIBES CDB$ROOT READ WRITE 18/02/22 08:25 0 ENABLED NOT SAVED ANTIBES PDB$SEED READ ONLY 18/02/22 08:25 754 ENABLED NOT SAVED oracle@srvantibes01:ANTIBES:db>
Cloner PDB01 de PARIS sur ANTIBES en le rafraîchissant toutes les 10mn.
Note : fonctionne sur une édition SE2.
-
Création de l'alias réseau
oracle@srvantibes01:ANTIBES:db> ./add_tns_alias.sh -service=pdb01_oci -host_name=srvparis01 -tnsalias=paris_pdb01 # srvantibes01 : Delete TNS alias paris_pdb01 if exists. 08:48:46> ~/plescripts/db/delete_tns_alias.sh -tnsalias=paris_pdb01 # Alias PARIS_PDB01 not found. # srvantibes01 : Append new alias : paris_pdb01 08:48:46> ~/plescripts/shell/gen_tns_alias.sh -service=pdb01_oci -alias_name=paris_pdb01 -server_list=srvparis01 >> $TNS_ADMIN/tnsnames.ora # srvantibes01 : $TNS_ADMIN/tnsnames.ora updated. oracle@srvantibes01:ANTIBES:db> cat $TNS_ADMIN/tnsnames.ora PARIS_PDB01 = (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (HOST = srvparis01) (PORT = 1521) ) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb01_oci) ) ) -
Création du dblink
oracle@srvantibes01:ANTIBES:db> sp SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 22 08:50:09 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connecte a : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create database link paris_pdb01 connect to pdbadmin identified by Oracle12 using 'paris_pdb01'; Lien de base de donnees cree. SQL> select 1 from dual@paris_pdb01; 1 ---------- 1 -
Clonage de
PDB01de la basePARISsur la baseANTIBESSQL> create pluggable database PARIS_PDB01 from pdb01@paris_pdb01 refresh mode every 10 minutes; Base de donnees pluggable creee. SQL> @lspdbs Instance PDB name Open mode Open time Size (Mb) Reco sta Save state ---------- ------------ ---------- -------------- ---------- -------- -------------- ANTIBES CDB$ROOT READ WRITE 18/02/22 08:25 0 ENABLED NOT SAVED ANTIBES PARIS_PDB01 MOUNTED 0 ENABLED NOT SAVED ANTIBES PDB$SEED READ ONLY 18/02/22 08:25 754 ENABLED NOT SAVED -
Création des schémas démo sur
PARISLe refresh ne fonctionne que quand la base est
MOUNTED.Pour visuliser les schémas sur
PARIS_PDB01l'ouvrir enRO, lerefreshse désactivera.Relancer le
refresh:SQL> alter session set container=paris_pdb01; Session modifiee. SQL> alter pluggable database close immediate; Base de donnees pluggable modifiee. SQL> alter pluggable database refresh ; Base de donnees pluggable modifiee. -
18c et plus switchover
oracle@srvpluton01:PLUTON:db> vi $TNS_ADMIN/tnsnames.ora
oracle@srvpluton01:PLUTON:db> tnsping CLONE_PDB1515_OCI
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 21-SEP-2018 19:29:18
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/18.0.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (HOST = srvorion01) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1515_oci)))
OK (0 msec)
oracle@srvpluton01:PLUTON:db> sp
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 21 19:29:47 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connecte a :
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
sys@cdb$root> alter pluggable database pdb1515 refresh mode every 10 minutes from pdb1515@CLONE_PDB1515_OCI switchover;`
2
sys@cdb$root> alter pluggable database pdb1515 refresh mode every 10 minutes from pdb1515@CLONE_PDB1515_OCI switchover;
alter pluggable database pdb1515 refresh mode every 10 minutes from pdb1515@CLONE_PDB1515_OCI switchover
*
ERREUR a la ligne 1 :
ORA-12754: Feature PDB REFRESH SWITCHOVER is disabled due to missing capability .