Chèr(e) ami(e)s Bonjour
Suite à l’accompagnement d’un de nos clients souhaitant migrer sa base de donnée Orale 11.2.1 vers une base affichage (PDB) dans oracle 12c,
Voici la procédure que j’ai mise en place afin d’importer les schémas de la base 11g vers une base plugable (PDB) dans un conteneur .
J’ai décidé d’utiliser l’export / import de données standard pour déplacer les schémas HR,OE,SH,SCOTT .
Mon environnement est un serveur linux centos 7 avec les binaires 11.2.4 et 12.1.2 installés et une base de données ORA11G pour oracle 11g et une base de donnée (CDB) cible ORA11T12 contenant une plugable database migrora12g qui est la base cible .
Vous avez donc compris que j’ai d’abord effectué une mise à jour de oracle 11.2.1 vers 11.2.4
Prérequis :
Migrer la base en oracle 11.2.4 avant le début des opérations migration oracle 11g vers un PDB oracle 12c
Note de preparation à la migration oracle 11g vers un PDB oracle 12c
ACTION sur ORACLE 11g
========================
QL> select banner from v$version;
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
— check schema des objects avant
===============================
SQL> select object_type, count(1) from dba_objects where owner = ‘OE’ group by object_type;
OBJECT_TYPE COUNT(1)
——————- ———-
SEQUENCE 1
LOB 15
TYPE BODY 3
TRIGGER 4
TABLE 14
INDEX 48
SYNONYM 6
VIEW 13
FUNCTION 1
TYPE 37
SQL> select object_type, count(1) from dba_objects where owner = ‘SCOTT’ group by object_type;
OBJECT_TYPE COUNT(1)
——————- ———-
INDEX 2
TABLE 4
SQL> select object_type, count(1) from dba_objects where owner = ‘HR’ group by object_type;
OBJECT_TYPE COUNT(1)
——————- ———-
SEQUENCE 3
PROCEDURE 2
TRIGGER 2
TABLE 7
INDEX 19
VIEW 1
6 ligne(s) selectionnee(s).
SQL> select object_type, count(1) from dba_objects where owner = ‘SH’ group by object_type;
OBJECT_TYPE COUNT(1)
——————- ———-
INDEX PARTITION 196
TABLE PARTITION 56
LOB 2
DIMENSION 5
MATERIALIZED VIEW 2
TABLE 17
INDEX 30
VIEW 1
8 ligne(s) selectionnee(s).
SQL>
SQL> select owner, object_name, object_type from dba_objects where status !=’VALID’;
OWNER OBJECT_NAME OBJECT_TYPE
—————————— ————————————————————— ——————-
ORDSYS ORDIMG_PKG PACKAGE BODY
ORDSYS ORDIMGEXTCODEC_PKG PACKAGE BODY
PUBLIC COE_CONFIGURATION SYNONYM
PUBLIC COE_NAMESPACES SYNONYM
PUBLIC COE_DOM_HELPER SYNONYM
PUBLIC COE_UTILITIES SYNONYM
PUBLIC COE_TOOLS SYNONYM
7 ligne(s) selectionnee(s).
Vérification des users et tablespaces
=====================================
SQL> select owner, tablespace_name from dba_segments where owner in (‘SH’,’OE’,’HR’,’SCOTT’)group by owner, tablespace_name order by 1,2;
OWNER TABLESPACE_NAME
—————————— ——————————
HR EXAMPLE
OE EXAMPLE
OE USERS
SCOTT USERS
SH EXAMPLE
Passage des TS en read only pour
============================================
4978 ligne(s) selectionnee(s).
SQL> alter tablespace users read only;
Tablespace modifie.
SQL> alter tablespace EXAMPLE read only;
Tablespace modifie.
Repertoire des dumps par defaut
===========================
select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME=’DATA_PUMP_DIR’;
SQL> ! mkdir /oracle/dumpmig
SQL> create directory dir_dump_mig as ‘/oracle/dumpmig’;
Repertoire cree.
SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME=’DIR_DUMP_MIG’;
DIRECTORY_PATH
——————————————————————————————————
/oracle/dumpmig
SQL>
SQL> grant read, write on directory dir_dump_mig to system;
Autorisation de privileges (GRANT) acceptee.
Export des schemas oe,sh,hr,scott
===========================================
expdp system/689Gi_ dumpfile=ora11g.dmp logfile=ora11g.log directory=data_pump_dir schemas=oe,sh,hr,scott
*********
ERREUR
**********
ORA-39181: Seule une partie des donnees de la table peut etre exportee en raison d’un controle d’acces detaille sur « OE ». »PURCHASEORDER »
********
SOLUTION*
*********
grant EXEMPT ACCESS POLICY to System;
Table maitre « SYSTEM ». »SYS_EXPORT_SCHEMA_01″ chargee/dechargee avec succes
******************************************************************************
L’ensemble de fichiers de vidage de SYSTEM.SYS_EXPORT_SCHEMA_01 est :
/u01/app/oracle/admin/ORA11G/dpdump/ora11g.dmp
L’execution du travail « SYSTEM ». »SYS_EXPORT_SCHEMA_01″ a abouti a Ven. Janv. 13 10:55:42 2017 elapsed 0 00:00:30
###################################################
# ORACLE12
###################################################
Connection au conteneur
==========================
[oracle@nsbd47 ~]$ sqlplus sys/689iG_@ORA11T12 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 11:00:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connecte a :
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
liste des conteneurs present
===============================================
SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
NAME OPEN_MODE
—————————— ———-
ORA12G READ WRITE
PDB$SEED READ ONLY
SQL>
fixer le parametre pdb_file_name_convert
=========================================
SQL> show parameter pdb_
NAME TYPE VALUE
———————————— ———– ——————————
pdb_file_name_convert string
alter session set pdb_file_name_convert=’/u02/oradata/ORA11T12/pdbseed’,’/u02/oradata/ORA11T12/MIGRORA12G’;
SQL> show parameter pdb_
NAME TYPE VALUE
———————————— ———– ——————————
pdb_file_name_convert string /u02/oradata/ORA11T12/pdbseed,
/u02/oradata/ORA11T12/MIGRORA
12G
pdb_lockdown string
pdb_os_credential string
SQL>
Création de la base pluggable
========================================
SQL> create pluggable database MIGRORA12G admin user pdb_mig identified by pdb_mig;
Base de donnees pluggable creee.
SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
NAME OPEN_MODE
—————————— ———-
MIGRORA12G MOUNTED
ORA12G READ WRITE
PDB$SEED READ ONLY
SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
NAME OPEN_MODE
—————————— ———-
MIGRORA12G MOUNTED
ORA12G READ WRITE
PDB$SEED READ ONLY
Ouverture de la base MIGRORA12G
====================================
SQL> alter pluggable database MIGRORA12G open read write;
Base de donnees pluggable modifiee.
SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
NAME OPEN_MODE
—————————— ———-
MIGRORA12G READ WRITE
ORA12G READ WRITE
PDB$SEED READ ONLY
Connexion à la base pluggable
===============================
SQL> alter session set container=MIGRORA12G;
Session modifiee.
SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME=’DATA_PUMP_DIR’;
DIRECTORY_PATH
——————————————————————————–
/ade/b/1281484529/oracle/admin/seeddata/dpdump/
Création du repertoire de dump
==================================
SQL> create directory dir_dump_mig as ‘/oracle/dumpmig’;
Repertoire cree.
SQL> grant read, write on directory dir_dump_mig to system;
Autorisation de privileges (GRANT) acceptee.
Création des schemas oe,sh,hr,scott
=======================================
create user oe identified by oe container=current;
create user hr identified by hr container=current;
create user sh identified by sh container=current;
create user scott identified by scott container=current;
Droit sur le TS
==========================================
grant connect, resource, create any view, unlimited tablespace to oe,hr,sh,scott;
Autorisation de privileges (GRANT) acceptee.
Création des TS
================================================
create tablespace appdata datafile ‘/u02/oradata/ORA11T12/MIGRORA12G/appdata01.dbf’ size 360M;
create tablespace USERS datafile ‘/u02/oradata/ORA11T12/MIGRORA12G/user01.dbf’ size 1000M;
create tablespace EXAMPLE datafile ‘/u02/oradata/ORA11T12/MIGRORA12G/example01.dbf’ size 50M;
EXAMPLE
alter user simplos default tablespace appdata;
alter database set default tablespace appdata;
il Faut configurer le tnsname pour pouvoir utiliser le service migrora12g
==========================================================================
lsnrctl status renvoit ceci
*****************************
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 12-JAN-2017 14:34:39
Uptime 1 days 2 hr. 54 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/nsbd47/listener/alert/log.xml
……
…….
Service « ORA11T12 » has 1 instance(s).
Instance « ORA11T12 », status READY, has 1 handler(s) for this service…
Service « ORA11T12XDB » has 1 instance(s).
Instance « ORA11T12 », status READY, has 1 handler(s) for this service…
Service « migrora12g » has 1 instance(s).
Instance « ORA11T12″, status READY, has 1 handler(s) for this service…
Contenue du tnsname.ora
===============================
migrora12g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nsbd47.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = migrora12g)
)
)
Test du service
=========================
tnsping migrora12g
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = nsbd47.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = migrora12g)))
OK (0 msec)
[oracle@nsbd47 admin]$
Connexion à la base pluggable migrora12g
===========================================
[oracle@nsbd47 admin]$ sqlplus system/689iG_@migrora12g
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 11:33:50 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Heure de la derniere connexion reussie : Ven. Janv. 13 2017 11:33:34 +01:00
Connecte a :
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name;
CON_NAME
——————————
MIGRORA12G
SQL>
Import des sches oe,sh,hr,scott avec exlusion des statistics
===============================================================
impdp system/689iG_@migrora12g dumpfile=ora11g.dmp logfile=ora11g.dmp_imp.log directory=dir_dump_mig
exclude=OBJECT_GRANT,statistics
Traitement du type d’objet SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Traitement du type d’objet SCHEMA_EXPORT/MATERIALIZED_VIEW
Traitement du type d’objet SCHEMA_EXPORT/DIMENSION
ORA-39082: Type d’objet VIEW: »OE ». »ACCOUNT_MANAGERS » cree avec des avertissements de compilation
ORA-39082: Type d’objet VIEW: »OE ». »CUSTOMERS_VIEW » cree avec des avertissements de compilation
Travail « SYSTEM ». »SYS_IMPORT_FULL_01″ termine avec 12 erreur(s) a Ven. Janv. 13 16:19:07 2017 elapsed 0 00:00:44
Deux erreurs de compilation
—————————
COLUMN object_name FORMAT A30
SELECT owner,object_type, object_name,status FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_type, object_name;
EXEC DBMS_UTILITY.compile_schema(schema => ‘OE’);
CHECK
===========
SQL> select object_type, count(1) from dba_objects where owner = ‘OE’ group by object_type;
OBJECT_TYPE COUNT(1)
———————– ———-
SEQUENCE 1
LOB 15
TYPE BODY 3
TRIGGER 3
SYNONYM 6
TABLE 14
INDEX 48
FUNCTION 1
VIEW 13
TYPE 34
10 lignes selectionnees.
SQL> select object_type, count(1) from dba_objects where owner = ‘SCOTT’ group by object_type;
OBJECT_TYPE COUNT(1)
———————– ———-
TABLE 4
INDEX 2
SQL> select object_type, count(1) from dba_objects where owner = ‘HR’ group by object_type;
OBJECT_TYPE COUNT(1)
———————– ———-
SEQUENCE 3
PROCEDURE 2
TRIGGER 2
TABLE 7
INDEX 19
VIEW 1
6 lignes selectionnees.
SQL> select object_type, count(1) from dba_objects where owner = ‘SH’ group by object_type;
OBJECT_TYPE COUNT(1)
———————– ———-
INDEX PARTITION 196
TABLE PARTITION 56
LOB 2
MATERIALIZED VIEW 2
DIMENSION 5
TABLE 16
INDEX 30
VIEW 1
8 lignes selectionnees.
ORA-39171: Le travail se heurte a une attente avec possibilite de reprise.
Resumable error: ORA-01658: impossible de creer l’extent INITIAL pour le segment dans le tablespace EXAMPLE
alter database tablespace EXAMPLE datafile ‘/u02/oradata/ORA11T12/MIGRORA12G/example01.dbf’ resize 150M;
alter tablespace add datafile /u02/oradata/ORA11T12/MIGRORA12G/example02.dbf’ size 100M autoextend on next 10M maxsize 250M
Oraclementvotre