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”
No hay comentarios:
Publicar un comentario