Oracle Database 12c: “Upgrade” a “Oracle Database 12c” ( Parte III )

Por Joel Pérez y Mahir M. Quluzade (OCP)
Publicado en Enero 2014

Indice

1. Oracle Database 12c: “Upgrade” a “Oracle Database 12c” ( Parte I )
2. Oracle Database 12c: “Upgrade” a “Oracle Database 12c” ( Parte II )
3. Oracle Database 12c: “Upgrade” a “Oracle Database 12c” ( Parte III )
4. Oracle Database 12c: “Upgrade” a “Oracle Database 12c” ( Parte IV )


Iniciemos esta tercera parte…

Reconocimiento de BBDD origen “ldb11g” ( version 11.2.0.3 ) y creación de “Tablespace” con “Data” de aplicación

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb11g
[oracle@oel62-x64 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 29 11:05:56 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select banner from v$version;  

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> 
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP

SQL> select name from  v$datafile; 

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ldb11g/system01.dbf
/u01/app/oracle/oradata/ldb11g/sysaux01.dbf
/u01/app/oracle/oradata/ldb11g/undotbs01.dbf
/u01/app/oracle/oradata/ldb11g/users01.dbf

SQL>  
SQL> create tablespace APPDATA1 datafile '/u01/app/oracle/oradata/ldb11g/appdata101.dbf' 
size 10M autoextend  on next 10M;

Tablespace created.

SQL> alter tablespace APPDATA1 add datafile '/u01/app/oracle/oradata/ldb11g/appdata102.dbf' 
size 10M autoextend on next 10M;

Tablespace altered.

SQL> create user user1 identified by  user1
    2  default tablespace appdata1
    3  quota unlimited on appdata1; 

User created.

SQL> grant create session, resource, dba to user1;

Grant succeeded.

SQL> create or replace directory UPGDIR as '/tmp/upgrade';

Directory created.

SQL> connect user1/user1 
Connected.
SQL> 
SQL> create table t (n number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t; 

	 N
-------------------------------
	 1

    

Reconocimiento de la BBDD destino, la cual se encuentra en version “12c” con “Tablespaces” y estructura por defecto.

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb12c
[oracle@oel62-x64 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 29 11:12:26 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select cdb, name from v$database;

CDB NAME
--- ---------
NO  LDB12C

SQL> select banner from  v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP

    

Establecimiento de “Tablespaces” con “Data” de aplicación en modo “Read-Only”. Para el presente caso solo tenemos un solo “Tablespace” con datos de aplicación

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb11g
[oracle@oel62-x64 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 29 11:16:13 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace APPDATA1 read only;

Tablespace altered.

    

Ejecución de “Export full” de la base de datos origen excluyendo elementos que pudieran generar conflictos en el transporte de datos al destino. Para el presente caso excluiremos el “Tablespace Users” el cual comúnmente conforma parte de los “Tablespaces” por defecto de una base de datos y el schema “APEX_030200” el cual fui incluido en este ejemplo solo para dar una muestra extendida del concepto de excluir elementos en el proceso. La combinación de parámetros clave para la utilizar la nueva característica “Full Transportable Export/Import” son los siguientes:

“FULL=Y TRANSPORTABLE=ALWAYS VERSION=12”

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb11g
[oracle@oel62-x64 ~]$ expdp directory=UPGDIR dumpfile=appdata1_dump.dmp logfile=appdata1_exp.log full=Y 
transportable=always version=12 exclude=TABLESPACE:\"= \'USERS\'\" 
exclude=SCHEMA:\"= \'APEX_030200\'\" 

Export: Release 11.2.0.3.0 - Production on Mon Jul 29 11:57:53 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=UPGDIR dumpfile=appdata1_dump.dmp 
logfile=appdata1_exp.log full=Y transportable=always version=12 exclude=TABLESPACE:"= exclude= exclude
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
…

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /tmp/upgrade/appdata1_dump.dmp
******************************************************************************
Datafiles required for transportable tablespace APPDATA1:
  /u01/app/oracle/oradata/ldb11g/appdata102.dbf
  /u01/app/oracle/oradata/ldb11g/appdata101.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 12:02:59

    

Copia de “Datafiles” a la locación destino:

[oracle@oel62-x64 ~]$ cd /u01/app/oracle/oradata/ldb11g/
[oracle@oel62-x64 ldb11g]$ ls -l
total 1644340
-rw-r----- 1 oracle oinstall  10493952 Jul 29 11:16 appdata101.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 29 11:16 appdata102.dbf
-rw-r----- 1 oracle oinstall   9748480 Jul 29 12:31 control01.ctl
-rw-r----- 1 oracle oinstall  52429312 Jul 29 11:23 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 29 12:00 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 29 12:31 redo03.log
-rw-r----- 1 oracle oinstall 587210752 Jul 29 12:31 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jul 29 12:31 system01.dbf
-rw-r----- 1 oracle oinstall  39854080 Jul 29 12:19 temp01.dbf
-rw-r----- 1 oracle oinstall  94380032 Jul 29 12:31 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 29 12:06 users01.dbf
[oracle@oel62-x64 ldb11g]$ cp appdata* $ORACLE_BASE/oradata/ldb12c
[oracle@oel62-x64 ldb11g]$ cd ../ldb12c/
[oracle@oel62-x64 ldb12c]$ ls -l
total 1969732
-rw-r----- 1 oracle oinstall  10493952 Jul 29 12:32 appdata101.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 29 12:32 appdata102.dbf
-rw-r----- 1 oracle oinstall  10043392 Jul 29 12:32 control01.ctl
-rw-r----- 1 oracle oinstall  52429312 Jul 29 12:23 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 29 12:30 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 29 12:23 redo03.log
-rw-r----- 1 oracle oinstall 765468672 Jul 29 12:30 sysaux01.dbf
-rw-r----- 1 oracle oinstall 817897472 Jul 29 12:30 system01.dbf
-rw-r----- 1 oracle oinstall  91234304 Jul 29 12:30 temp01.dbf
-rw-r----- 1 oracle oinstall 162537472 Jul 29 12:28 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 29 12:23 users01.dbf
[oracle@oel62-x64 ldb12c]$ 

    

Creación de directorio en la base de datos destino e “Import” full. Los errores reportados al final de proceso se debe a la coincidencia de objetos del diccionario de datos pero no representan errores que conlleven a una incorrecta ejecución del proceso.

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb12c
[oracle@oel62-x64 ~]$ impdp full=Y directory=UPGDIR dumpfile=appdata1_dump.dmp 
logfile=appdata1_imp.log transport_datafiles='/u01/app/oracle/oradata/ldb12c/appdata101.dbf',
'/u01/app/oracle/oradata/ldb12c/appdata102.dbf'

Import: Release 12.1.0.1.0 - Production on Mon Jul 29 12:33:08 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Username: system 
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=Y directory=UPGDIR 
dumpfile=appdata1_dump.dmp logfile=appdata1_imp.log transport_datafiles=/u01/app/oracle/
oradata/ldb12c/appdata101.dbf,/u01/app/oracle/oradata/ldb12c/appdata102.dbf 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"ORDADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
ORA-31684: Object type ROLE:"APEX_ADMINISTRATOR_ROLE" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
…

ORA-39082: Object type PACKAGE BODY:"SYSMAN"."MGMT_TARGET_UPDATE" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"SYSMAN"."MGMT_TIME_SYNC" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"SYSMAN"."MGMT_USER" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"SYSMAN"."MGMT_VIEW_PRIV" created with compilation warnings
ORA-39082: Object type TRIGGER:"SYSMAN"."MGMT_CREDS_INS_UPD" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 515 error(s) at Mon Jul 29 12:50:50 2013 
elapsed 0 00:17:31

    

Chequeo final de los datos de aplicacion

[oracle@oel62-x64 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome
[oracle@oel62-x64 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oel62-x64 ~]$ export ORACLE_SID=ldb12c
[oracle@oel62-x64 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 29 12:55:34 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> connect user1/user1
Connected.

SQL> select *  from t;

	 N
-------------------------------
	 1

SQL>

    

Si se desease realizar el mismo procedimiento entre plataformas distintas lo único que habría que adicionar al procedimiento expuesto es convertir los “Datafiles” a la plataforma destino antes de realizar el “Full Import”. La conversión se puede realizar en el origen o destino. Este seria un ejemplo de cómo podríamos convertir los “Datafiles” en el destino:

[oracle@oel62-ora12c ~]$ export ORACLE_SID=ldb12c2
[oracle@oel62-ora12c ~]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 29 14:01:18 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target "sys as sysbackup";

target database Password: 
connected to target database: LDB12C2 (DBID=3477471046)

RMAN> CONVERT DATAFILE 
     2>	'/tmp/Upgrade2/APPDATA201.DBF', 
     3>	'/tmp/Upgrade2/APPDATA202.DBF' 
     4> DB_FILE_NAME_CONVERT 
     5> '/tmp/Upgrade2','/u01/app/oracle/oradata/ldb12c2'
     6> FROM PLATFORM 'Microsoft Windows x86 64-bit';

Starting conversion at target at 29-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/tmp/Upgrade2/APPDATA201.DBF
converted datafile=/u01/app/oracle/oradata/ldb12c2/APPDATA201.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/tmp/Upgrade2/APPDATA202.DBF
converted datafile=/u01/app/oracle/oradata/ldb12c2/APPDATA202.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 29-JUL-13

    

De esta manera hemos probado la funcionalidad de la nueva característica: “Full Transportable Export/Import”

 


Joel es un experto DBA con más de 12 años de experiencia, especializado en bases de datos con especial énfasis en la soluciones de alta disponibilidad (RAC, Data Guard, y otras). Es un conferencista habitual en eventos de Oracle como: OTN LAD TOUR y otros. Consultor Internacional con trabajos en más de 20 países alrededor del mundo. Fue el primer latinoamericano en ser nombrado "Experto OTN" en el año 2003, Oracle ACE año 2004 y actualmente Oracle ACE Director.

Mahir es un Senior DBA con mas de 10 anos de experiencia en bases de datos Oracle con especial foco en “High Availability” & “Disaster Recovery Solutions (RAC, Data Guard, RMAN,…)”. Mahir actualmente trabaja en el “Central Bank of the Republic of Azerbaijan”. El es OCP DBA. Mahir es frecuente orador en el “Azerbaijan Oracle User Group (AZEROUG)”.