sábado, 24 de marzo de 2018

Instalando Oracle Database 18c for EXADATA en una VM con VirtualBox y OEL 7.3

ADVERTENCIA: Este post debe ser utilizado como un laboratorio para aprendizaje y no para poner en producción. La versión ON-PREMISE de Oracle Database 18c, estará disponible para producción en el segundo semestre de este año.

Bueno lo primero que necesitamos para crear este laboratorio de aprendizaje, es tener una máquina virtual configurada y preparada para la instalación del software de Oracle Database, siguiendo los pasos que podrán encontrar en este mismo blog, al lado derecho con el título de e-book "Instalando Oracle Linux UEK y Oracle 12c en una VM en 1 hora o menos."

Si bien es cierto, este documento sirve para la preparación de VM con Oracle 6.x y para Oracle Database 12c o Oracle Forms & Reportes o Oracle Weblogic o cualquier producto de las distintas familias de software On-Premise, también puede ser utilizado como guía para instalar Oracle Linux Versión 7.3.

Esta es la versión en la cuál he preparado el laboratorio y ha funcionado adecuadamente.

Segundo, tal y como lo comentamos en el video en el FB LIVE que hice hace unos días atrás, vamos a proceder a bajar la versión del motor de base de datos 18c para Exadata x86.

El software lo pueden conseguir en el sitio de Delivery Cloud de Oracle. Para ello, necesitan estar previamente registrados en el portal de oracle.com.


Esta descarga es de aproximadamente unos 3.76GB

Vamos a descomprimir el archivo en la ruta que queremos tener como ORACLE_HOME. Para efectos míos, esta ruta corresponde a /opt/app/oracle/product/18.0.0/dbhome_1


Cuando termine el unzip del archivo del software del motor de la base de datos, este habrá creado, toda una jerarquía de directorios.

Modifique manualmente el archivo /etc/oratab e incluya el directorio ORACLE_HOME escogido, con el nombre que deseas darle al servicio de base de datos que vas a crear, ya sea a nivel de contenedor o de una single-instance.

En mi caso, voy a crear en esta primera instalación, un contenedor de base de datos con el nombre de CDB1.

Tengan en cuenta, que el motor que hemos bajado, te va a permitir instalar versiones Oracle Database SE y E.E.

En el caso de la versión SE, es tecnología SingleTenant y en el caso de E.E. Multitenant.

En la versión SE, máximo pueden tener 1 instancia PDB ( Acoplada ) por contenedor de base de datos y en la versión E.E., pueden llegar a tener hasta 4096 instancias PDBs por contenedor.

Como indicamos, vamos a ingresar la línea al archivo oratab.

[oracle@lab1 ~]$ more /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
cdb1:/opt/app/oracle/product/18.0.0/dbhome_1:N

Ahora podemos utilizar el archivo de configuración de variables de ambiente, para el usuario "oracle".

[oracle@lab1 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1
The Oracle base has been set to /opt/app/oracle

Una vez realizado el paso anterior, van a ir al directorio de descompresión y van a ubicar el archivo clásico "runInstaller". El mismo debe ser ejecutado con el usuario "oracle".

Aquí esta el primer detalle, deben escoger la instalación sólo del software. El DBCA no arranca de manera adecuada en una VM, ya que esta hecho para EXADATA y no para nuestra infraestructura.


Una vez concluida esta parte, es necesario pasar a configurar el archivo de parámetros del contenedor inicial para base de datos.

Para ello, vamos a trasladarnos hasta el directorio ORACLE_HOME del software y vamos a ingresar en el subdirectorio dbs

[oracle@lab1 ~]$ cd $ORACLE_HOME
[oracle@lab1 dbhome_1]$ cd dbs

Ahí vamos a crear el archivo initcdb1.ora, con el siguiente contenido:

db_name='cdb1'
memory_target=1G
processes = 150
db_block_size=8192
open_cursors=300
undo_tablespace='UNDOTBS1'
_exadata_feature_on=true
enable_pluggable_database=true
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)

Observen con cuidado, el parámetro _exadata_feature_on=true es el que hace la diferencia en este archivo de configuración para la instancia. Este parámetro oculto, engaña al software de la base de datos y le permite correr en un sistema que no es un EXADATA en realidad.


Ahora vamos a proceder a levantar los servicios de la instancia que vamos a configurar. Debe hacerse en modo nomount.

[oracle@lab1 dbs]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Mon Mar 5 20:56:44 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup nomount force
ORACLE instance started.
Total System Global Area 1073741008 bytes
Fixed Size 8903888 bytes
Variable Size 616562688 bytes
Database Buffers 440401920 bytes
Redo Buffers 7872512 bytes

SQL> exit
Disconnected from Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

[oracle@lab1 dbs]$ pwd

/opt/app/oracle/product/18.0.0/dbhome_1/dbs

Ahora nos vamos a pasar al directorio en donde vamos a crear nuestro nuevo contenedor de bases de datos Oracle 18c

El script para crear el contenedor manual, puede ser escrito de la siguiente manera:

CREATE DATABASE "cdb1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/opt/app/oracle/oradata/cdb1/system01.dbf' SIZE 700M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/opt/app/oracle/oradata/cdb1/sysaux01.dbf' SIZE 550M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/app/oracle/oradata/cdb1/temp01.dbf' SIZE 20M REUSE
  AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/opt/app/oracle/oradata/cdb1/undotbs01.dbf' SIZE 200M REUSE
  AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/opt/app/oracle/oradata/cdb1/redo01.log') SIZE 50M,
GROUP 2 ('/opt/app/oracle/oradata/cdb1/redo02.log') SIZE 50M,
GROUP 3 ('/opt/app/oracle/oradata/cdb1/redo03.log') SIZE 50M
USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"
enable pluggable database
seed file_name_convert=('/opt/app/oracle/oradata/cdb1/system01.dbf','/opt/app/oracle/oradata/cdb1/pdbseed/system01.dbf',                        '/opt/app/oracle/oradata/cdb1/sysaux01.dbf','/opt/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf',                        '/opt/app/oracle/oradata/cdb1/temp01.dbf','/opt/app/oracle/oradata/cdb1/pdbseed/temp01.dbf',                     '/opt/app/oracle/oradata/cdb1/undotbs01.dbf','/opt/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf');
[oracle@lab1 dbs]$ cd /opt/app/oracle/oradata

Vamos a guardar dicho archivo con el nombre 1.sql

[oracle@lab1 oradata]$ ls -la
total 8
drwxr-x---. 4 oracle oinstall 40 Mar 5 20:54 .
drwxr-xr-x. 9 oracle oinstall 4096 Mar 5 13:57 ..
-rw-r--r--. 1 oracle oinstall 1503 Mar 5 20:54 1.sql
drwxr-xr-x. 3 oracle oinstall 20 Mar 5 15:21 cdb1
drwxr-x---. 4 oracle oinstall 32 Mar 5 15:22 CDB1

Ahora nos vamos a conectar a la instancia inicializada en modo nomount y vamos a ejecutar nuestro archivo 1.sql para crear el contenedor de base de datos.

[oracle@lab1 oradata]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Mon Mar 5 20:57:35 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> connect / as sysdba

Connected.

SQL> @1

Database created.

Bajamos la base de datos y la montamos en modo normal.

SQL> shutdown immediate

Database closed.
Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741008 bytes
Fixed Size 8903888 bytes
Variable Size 616562688 bytes
Database Buffers 440401920 bytes
Redo Buffers 7872512 bytes

Database mounted.

Database opened.

Creamos el archivo de parámetros a partir de la configuración en memoria.

SQL> create spfile from pfile;

File created.

Y reiniciamos la base de datos nuevamente.

SQL> startup force

ORACLE instance started.

Total System Global Area 1073741008 bytes
Fixed Size 8903888 bytes
Variable Size 616562688 bytes
Database Buffers 440401920 bytes
Redo Buffers 7872512 bytes

Database mounted.

Database opened.


Ahora que hemos reiniciado la base de datos, vamos a ejecutar el script de creación del contenedor.

No debe ejecutarse por separado los clásicos archivos de catalog y catalogproc.

Para crear el contenedor, vamos a utilizar el script catcdb. Esto proceso tomará algunos minutos, antes de concluir. Tenga un poco de paciencia, que ya pronto tendremos nuestro ambiente listo para trabajar. Durante la ejecución se le pedirá algunos datos, este al pendiente del ingreso de los mismos.

[oracle@lab1 oradata]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Mon Mar 5 21:02:49 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> @?/rdbms/admin/catcdb

SQL>

SQL> Rem The script relies on the caller to have connected to the DB

SQL>

SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to

SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL>

SQL> Rem $ORACLE_HOME

SQL> column oracle_home new_value oracle_home noprint

SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;

SQL>

SQL> Rem OS-dependent slash

SQL> column slash new_value slash noprint

SQL> select sys_context('userenv', 'platform_slash') as slash from dual;

SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin

SQL> column rdbms_admin new_value rdbms_admin noprint

SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;

old 1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual

SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint

SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;

old 1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual


SQL>

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

Enter value for 1: log_cdb1.log

Enter value for 2: log2_cdb1.log

Requested Logging Directory log_cdb1.log does not exist

SQL> @?/rdbms/admin/catcdb

SQL> Rem

SQL> Rem $Header: rdbms/admin/catcdb.sql /main/8 2017/05/28 22:46:01 stanaya Exp $

SQL> Rem

SQL> Rem catcdb.sql

SQL> Rem

SQL> Rem Copyright (c) 2013, 2017, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem NAME

SQL> Rem catcdb.sql -

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem invoke catcdb.pl

SQL> Rem

SQL> Rem NOTES

SQL> Rem

SQL> Rem

SQL> Rem PARAMETERS:

SQL> Rem - log directory

SQL> Rem - base for log file name

SQL> Rem

SQL> Rem BEGIN SQL_FILE_METADATA

SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catcdb.sql

SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catcdb.sql

SQL> Rem SQL_PHASE: UTILITY

SQL> Rem SQL_STARTUP_MODE: NORMAL

SQL> Rem SQL_IGNORABLE_ERRORS: NONE

SQL> Rem END SQL_FILE_METADATA

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem akruglik 06/21/16 - Bug 22752041: pass --logDirectory and

SQL> Rem --logFilename to catcdb.pl

SQL> Rem akruglik 11/10/15 - use catcdb.pl to collect passowrds and pass them

SQL> Rem on to catcdb_int.sql using env vars

SQL> Rem aketkar 04/30/14 - remove SQL file metadata

SQL> Rem cxie 07/10/13 - 17033183: add shipped_file metadata

SQL> Rem cxie 03/19/13 - create CDB with all options installed

SQL> Rem cxie 03/19/13 - Created

SQL> Rem

SQL>

SQL> set echo on

SQL>

SQL> Rem The script relies on the caller to have connected to the DB

SQL>

SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to

SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL>

SQL> Rem $ORACLE_HOME

SQL> column oracle_home new_value oracle_home noprint

SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;
SQL>

SQL> Rem OS-dependent slash

SQL> column slash new_value slash noprint

SQL> select sys_context('userenv', 'platform_slash') as slash from dual;

SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin

SQL> column rdbms_admin new_value rdbms_admin noprint

SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;

old 1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual


SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint

SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;

old 1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual


SQL>

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

Requested Logging Directory log_cdb1.log does not exist

SQL> exit

Disconnected from Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

[oracle@lab1 oradata]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Mon Mar 5 21:03:27 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> @?/rdbms/admin/catcdb

SQL> Rem The script relies on the caller to have connected to the DB

SQL>

SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to

SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL>

SQL> Rem $ORACLE_HOME

SQL> column oracle_home new_value oracle_home noprint

SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;

SQL>

SQL> Rem OS-dependent slash

SQL> column slash new_value slash noprint

SQL> select sys_context('userenv', 'platform_slash') as slash from dual;

SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin

SQL> column rdbms_admin new_value rdbms_admin noprint

SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;

old 1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual

SQL>

SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl

SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint

SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;

old 1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual

new 1: select '/opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual

SQL>

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

Enter value for 1: /opt/app/oracle/oradata

Enter value for 2: log_cdb1.log

Enter new password for SYS: oracle

Enter new password for SYSTEM: oracle

Enter temporary tablespace name: temp

No options to container mapping specified, no options will be installed in any containers

catcon::exec_DB_script: /opt/app/oracle/oradata/catcdb__catcon_16982_exec_DB_script.done did not need to be deleted before running a script
catcon::exec_DB_script: opened Reader and Writer
catcon::exec_DB_script: executed set newpage 1
catcon::exec_DB_script: executed set pagesize 14
catcon::exec_DB_script: executed @@?/rdbms/admin/sqlsessstart.sql
catcon::exec_DB_script: connected
catcon::exec_DB_script: executed set echo on
catcon::exec_DB_script: executed set serveroutput on
catcon::exec_DB_script: executed spool /opt/app/oracle/oradata/log_cdb1.log

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b catalog catalog.sql

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b catproc catproc.sql

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b catoctk catoctk.sql


catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b owminst owminst.plb

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYSTEM -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/sqlplus/admin -n 1 -l /opt/app/oracle/oradata -b pupbld pupbld.sql

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYSTEM -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/sqlplus/admin/help -n 1 -l /opt/app/oracle/oradata -b pupbld hlpbld.sql --p"helpus.sql"

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b catclust catclust.sql

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b catfinal catfinal.sql

catcon::exec_DB_script: executed host perl -I /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -u SYS -w CATCDB_SYSTEM_PASSWD -U SYS -W CATCDB_SYS_PASSWD -d /opt/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -n 1 -l /opt/app/oracle/oradata -b utlrp utlrp.sql

catcon::exec_DB_script: sent

host sqlplus -v > /opt/app/oracle/oradata/catcdb__catcon_16982_exec_DB_script.done to Writer

catcon::exec_DB_script: sent -exit- to Writer
catcon::exec_DB_script: closed Writer

catcon::exec_DB_script: marker was undefined; read and ignore output, if any

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/catalog_catcon_16993.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catalog*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catalog_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/catproc_catcon_17618.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catproc*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catproc_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/catoctk_catcon_22968.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catoctk*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catoctk_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/owminst_catcon_23068.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/owminst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/owminst_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/pupbld_catcon_23678.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/pupbld*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/pupbld_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/pupbld_catcon_23817.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/pupbld*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/pupbld_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/catclust_catcon_23915.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catclust*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catclust_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/catfinal_catcon_24372.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catfinal*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/catfinal_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/app/oracle/oradata/utlrp_catcon_24468.lst]

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/opt/app/oracle/oradata/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon::exec_DB_script: finished reading and ignoring output
catcon::exec_DB_script: waiting for child process to exit
catcon::exec_DB_script: child process exited
catcon::sureunlink: unlink(/opt/app/oracle/oradata/catcdb__catcon_16982_exec_DB_script.done) succeeded after 1 attempt(s)
catcon::sureunlink: verify that the file really no longer exists
catcon::sureunlink: confirmed that /opt/app/oracle/oradata/catcdb__catcon_16982_exec_DB_script.done no longer exists after 1 attempts
catcon::exec_DB_script: deleted /opt/app/oracle/oradata/catcdb__catcon_16982_exec_DB_script.done after running a script
catcon::exec_DB_script: closed Reader
catcon::exec_DB_script: waitpid returned

Siguiente paso, vamos a ejecutar el paquete de compilación.

SQL> @?/rdbms/admin/utlrp

SQL> Rem

SQL> Rem $Header: rdbms/admin/utlrp.sql /main/23 2017/03/20 12:21:12 raeburns Exp $

SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME

SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this

SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES

SQL> Rem * This script must be run using SQL*PLUS.

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

SQL> Rem

SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlrp.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlrp.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem raeburns 03/09/17 - Bug 25616909: Use UTILITY for SQL_PHASE

SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate

SQL> Rem gviswana 06/12/03 - Switch default back to serial

SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning

SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics

SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc

SQL> Rem gviswana 06/25/02 - Add documentation

SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial

SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368

SQL> Rem rburns 11/12/01 - validate all components after compiles

SQL> Rem rburns 11/06/01 - fix invalid CATPROC call

SQL> Rem rburns 09/29/01 - use 9.2.0

SQL> Rem rburns 09/20/01 - add check for CATPROC valid

SQL> Rem rburns 07/06/01 - get version from instance view

SQL> Rem rburns 05/09/01 - fix for use with 8.1.x

SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33

SQL> Rem skabraha 09/25/00 - validate is now a keyword

SQL> Rem kosinski 06/14/00 - Persistent parameters

SQL> Rem skabraha 06/05/00 - validate tables also

SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.

SQL> Rem rshaikh 09/22/99 - quote name for recompile

SQL> Rem ncramesh 08/04/98 - change for sqlplus

SQL> Rem usundara 06/03/98 - merge from 8.0.5

SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).

SQL> Rem Mark Ramacher (mramache) was the original

SQL> Rem author of this script.

SQL> Rem

SQL>

SQL> Rem ================================================
SQL> Rem BEGIN utlrp.sql

SQL> Rem ================================================
SQL>

SQL> @@utlprp.sql 0

SQL> Rem Copyright (c) 2003, 2017, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem NAME

SQL> Rem utlprp.sql - Recompile invalid objects in the database

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

SQL> Rem

SQL> Rem This script is typically used to recompile invalid objects

SQL> Rem remaining at the end of a database upgrade or downgrade.

SQL> Rem

SQL> Rem Although invalid objects are automatically recompiled on demand,

SQL> Rem running this script ahead of time will reduce or eliminate

SQL> Rem latencies due to automatic recompilation.

SQL> Rem

SQL> Rem This script is a wrapper based on the UTL_RECOMP package.

SQL> Rem UTL_RECOMP provides a more general recompilation interface,

SQL> Rem including options to recompile objects in a single schema. Please

SQL> Rem see the documentation for package UTL_RECOMP for more details.

SQL> Rem

SQL> Rem INPUTS

SQL> Rem The degree of parallelism for recompilation can be controlled by

SQL> Rem providing a parameter to this script. If this parameter is 0 or

SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate

SQL> Rem level of parallelism based on Oracle parameters cpu_count and

SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential

SQL> Rem recompilation is used. Please see the documentation for package

SQL> Rem UTL_RECOMP for more details.

SQL> Rem

SQL> Rem NOTES

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

.....
.....
SQL> Rem automatic redirection is turned off. This is needed so that utlrp/utlprp
SQL> Rem can be used to recompile objects in Proxy PDB.
SQL> Rem
SQL> alter session set "_enable_view_pdb"=false;

Session altered.

SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-03-06 05:39:42

SQL>
SQL> DOC
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

DOC>
DOC> Use the following queries to track recompilation progress:
DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

SQL>

SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /

PL/SQL procedure successfully completed.
SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-03-06 05:39:43

SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status in (3,4,5,6);

OBJECTS WITH ERRORS
-------------------
0

SQL> DOC
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0

SQL>

SQL> Rem ================================================
SQL> Rem Reenable indexes that may have been disabled, based on the

SQL> Rem table SYS.ENABLED$INDEXES

SQL> Rem ================================================
SQL>

SQL> @@?/rdbms/admin/reenable_indexes.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /main/3 2015/02/04 13:57:27 sylin Exp $

SQL> Rem

SQL> Rem reenable_indexes.sql

SQL> Rem

SQL> Rem Copyright (c) 2014, 2015, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem NAME

SQL> Rem reenable_indexes.sql -

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem

SQL> Rem

SQL> Rem NOTES

SQL> Rem

SQL> Rem

SQL> Rem BEGIN SQL_FILE_METADATA

SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_PHASE: REENABLE_INDEXES

SQL> Rem SQL_STARTUP_MODE: NORMAL

SQL> Rem SQL_IGNORABLE_ERRORS: NONE

SQL> Rem SQL_CALLING_FILE: rdbms/admin/noncdb_to_pdb.sql

....
....
SQL> SET serveroutput on

SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off

SQL> Rem ================================================
SQL> Rem END utlrp.sql

SQL> Rem ================================================

Listo terminamos con la compilación de paquetes y ahora vamos a confirmar el estado de los componentes de la base de datos.

SQL> select comp_id, comp_name, version_full, status from dba_registry;

SQL> col COMP_NAME format a50
SQL> set linesize 399

SQL> /
COMP_ID COMP_NAME VERSION_FULL STATUS
--------------- ------------------------------------------- ---------------- -----
CATALOG Oracle Database Catalog Views 18.1.0.0.0 VALID
CATPROC Oracle Database Packages and Types 18.1.0.0.0 VALID
RAC Oracle Real Application Clusters 18.1.0.0.0 OPTION OFF
XDB Oracle XML Database 18.1.0.0.0 VALID
OWM Oracle Workspace Manager 18.1.0.0.0 VALID

SQL>

Seguidamente, vamos a crear nuestra primera instancia acoplada para el contenedor CDB1

SQL> create pluggable database pdb1 admin user pdbadmin identified by oracle \
FILE_NAME_CONVERT=('/opt/app/oracle/oradata/cdb1/pdbseed/','/opt/app/oracle/oradata/cdb1/pdb1/');

Pluggable database created.

Validamos su creación y estado.

SQL> select name, open_mode, block_size, pdb_count, max_size from v$pdbs;

NAME OPEN_MODE BLOCK_SIZE PDB_COUNT MAX_SIZE
--------------------------------- ---------- ---------- ---------- ----------
PDB$SEED READ ONLY 8192 0 0
PDB1 MOUNTED 8192 0 0




Vamos a procede a abrir la instancia PDB creada.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> exit

Ahora probamos que podemos conectarnos a la instancia acoplada del contenedor CDB1 y hacemos una simple consulta al diccionario de la base de datos.

Modifique el archivo tnsnames.ora, para permitir el acceso al contenedor y a la instancia PDB usando un cadena de conexión.

[oracle@lab1 admin]$ sqlplus system/oracle@pdb1

SQL*Plus: Release 18.0.0.0.0 Production on Tue Mar 6 05:56:51 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Tue Mar 06 2018 05:51:44 -06:00
Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select count(*) from dba_objects;
COUNT(*)
----------
22662

SQL> exit

Disconnected from Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Nos desconectamos y hacemos lo mismo para el contenedor.

[oracle@lab1 admin]$ sqlplus system/oracle@cdb1

SQL*Plus: Release 18.0.0.0.0 Production on Tue Mar 6 05:57:17 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Tue Mar 06 2018 05:56:51 -06:00

Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select count(*) from dba_objects;

COUNT(*)
----------
22678

Listo ahora ya podemos trabajar con nuestra máquina virtual en Oracle Linux 7.3 y Oracle Database E.E. 18c

Si desean tener más contenedores, deben repetir el paso descrito en esta guía.

Para mayor información sobre características de la nueva versión, pueden consultar la documentación oficial en oracle.com en el siguiente link:


Un abrazo a todos y todas y que la disfruten.


No hay comentarios:

Publicar un comentario

Te agradezco tus comentarios. Te esperamos de vuelta.

Todos los Sábados a las 8:00PM

Optimismo para una vida Mejor

Optimismo para una vida Mejor
Noticias buenas que comentar