Oracle 8 - Nuevo enfoque (5).Profundizando en los backups de Oracle con expdp.

 0. Introducción

Créditos: 

https://www.oracle.com/a/ocom/docs/oracle-data-pump-best-practices.pdf

Vamos a ver como crear backups de:

1. La base de datos /FULL)
2.  Un SCHEMA
3. Varias tablas
4. Un Tablespace

También vamos a ver como:

1. Usar un fichero de parámetros de copia de seguridad
2. Fraccionar los ficheros de coopia de seguridad en varios ficheros de tamaño limitado
3. Comprimir las copias 

Pero no nos olvidemos que nuestra base de datos corre en un contenedor docker, y en el punto anterior hemos visto como crear volúmenes en docker para tener acceso directo  a dichos volumenes tanto por parte de los contenedores docker como del propio servidor. También hemos visto como se asignaban los volúmenes a los contenedores en el proceso de creación de los mismos a partir de una imágen

1. Pasos previos

Para cada uno de estos procesos hay una parte común que és:

1. Ejecutar el contenedor docker en modo shell
2. Crear una carpeta en el contenedor docker y a ser posible que esté en el volumen docker
3. Entrar a sqlplus como usario sys y permisos sysdba
4. Usar la BD en cuestión
5. Si no tenemos creado un usuario lo creamos
6. Mapear dicha carpeta con sqlplus (CREATE DIRECTORY)
7. Otorgar permisos de read, write del directorio a un usuario
8. Otorgar permisos DATAPUMP_EXP_FULL_DATABASE al ususario

Veamos estos 5 pasos previos:

#----------------------
# PRELIMINARES
#----------------------

#1. Ejecutar el contenedor docker en modo shell

#               <id contenedor> <programa a ejecutar>

docker exec -it 34a5f979d3de    /bin/bash

#2. Dentro del contenedor docker creamos la carpeta

#   Es conveniente que esta carpeta se encuentre en el volumen
#   asignado al crear el contenedor a partir de la imagen
mkdir /home/ximo/oracle-backup-server

#3. Nos conectamos a sqlplus con el usuario sys y permisos sysdba
sqlplus / as sysdba

#4. Una vez dentro de SQL plus, usar la BD (Contenedor) en cuestión
ALTER SESSION SET CONTAINER = XEPDB1;

#5. Si no tenemos creado un usuario lo creamos
CREATE USER backupuser IDENTIFIED BY myPassword;

#6. Mapear la carpeta física a un directorio de SQL
CREATE DIRECTORY oracle_backup_sql AS '/home/ximo/oracle-backup-server';

#7. Otorgamos permisos al usuario en ese directorio
GRANT read, write ON DIRECTORY oracle_backup_sql TO backupuser;

#8. Otorgamos permisos al usuario de DATAPUMP_EXP_FULL_DATABASE
GRANT DATAPUMP_EXP_FULL_DATABASE TO backupuser;



#9. Ahora queda realizar la copia de la BS, SQCHEMA, TABLESPACE ...

2. Copia de seguridad de la BD entera

Hay que tener en cuenta que NO SE COPIAN:

1. Los esquemas del sistema como SYS, ORDSYS, MDSYS
2. Los Grants sobre los objetos propietarios de SYS.
3. Hay que tener autorización para el REALM para exportar datos protegidos por REALM

La sentencia a ejecutar dentro de una ventana de comandos (shell) :

#     <usuario>  <password> <bd>             <directorio>               <dump-file>            <log-file>     <FULL copy>
expdp backupuser/myPassword@EXPDB1 DIRECTORY=oracle-backup-sql DUMPFILE=orclfull.dmp   LOGFILE=full_exp.log   FULL=YES;

Cosa que nos creará una copia de la BD EXPDB1 entera (parametro FULL=YES) en el directorio oracle-backup-sql que está mapeado al volumen de docker, el cual tenenos acdeso desde el servidor físico. La copia de seguridad se descarga en los ficheros ".dump" y ".log" que hemos indicado (orclfull.dmp y full_exp.log) 

3. Copia de seguridad de un SCHEMA

La sentencia a ejecutar dentro de una ventana de comandos (shell) :

#     <usuario>  <password> <bd>             <directorio>               <dump-file>            <log-file>     <SCHEMAS to backup>
expdp backupuser/myPassword@EXPDB1 DIRECTORY=oracle-backup-sql DUMPFILE=orclschm.dmp   LOGFILE=schm_exp.log   SCHEMAS=SCHM01_XIMO;

Cosa que nos creará una copia del SCHEMA SCHM01_XIMO de la BD EXPDB1  (parametro SCHEMAS=SCHM01_XIMO) en el directorio en cuestion, generando el fichero "dump" y "log" indicados en los parametros .

4. Copia de seguridad de tablas

Lógicamente no se exportan relaciones entre tablas.

La sentencia a ejecutar dentro de una ventana de comandos (shell) :

#     <usuario>  <password> <bd>             <directorio>               <dump-file>           <log-file>          <TABLES to backup>
expdp backupuser/myPassword@EXPDB1 DIRECTORY=oracle-backup-sql DUMPFILE=orcltabls.dmp LOGFILE=tabls_exp.log TABLES=regions, products;

Cosa que nos creará una copia de llas tablas regions y products de la BD EXPDB1  (parametro TABLES=regions,products) en el directorio en cuestion, generando el fichero "dump" y "log" indicados en los parametros .

5. Copia de seguridad de TABLESPACE

Lógicamente no se exportan relaciones entre tablas.

La sentencia a ejecutar dentro de una ventana de comandos (shell) :

#     <usuario>  <password> <bd>             <directorio>               <dump-file>            <log-file>       <TABLESPACE to backup>
expdp backupuser/myPassword@EXPDB1 DIRECTORY=oracle-backup-sql DUMPFILE=orcltblspc.dmp LOGFILE=tblspc_exp.log   TABLESPACES=TBLSPC1, TBLSPC2;

Cosa que nos creará una copia de los  TABLESPACES TBLSPC1, TBLSPC2 de la BD EXPDB1  (parametro TABLESPCES=TBLSPC1, TBLSPC2) en el directorio en cuestion, generando el fichero "dump" y "log" indicados en los parametros .

6. Múltiples ficheros del mismo tamaño, compresión, archivo de configuración...

6.1 Partir la copia en varios ficheros

Para crear múltiples ficheros de  copia de seguridad del mismo tamaño , hay que modificar el parámetro DUMPFILE y añadir el parámetr FILESIZE quedando:

DUMPFILE=orcltblspc%U.dmp FILESIZE=1M  

Observar el parámetro %U de DUMPFILE que se sustituye por el número correlativo del fichero generado y el parámetro FILESIZE que admite un valor entero seguido de una de estas letras B:byte, K:Kbyte, M: Megabyte y G:Gigabyte. En el ejemplo hemos puesto un mega de tamaño

6.2 Comprimir la copia de seguridad

OJO: Para utilizar compresión hay que tener la licencia Enterprise!

Para crear las copias comprimidas utilizamos el parámetro COMPRESSION que puede tomar los valores:

ALL: lo comprime todo.or the entire export operation.

DATA_ONLY: Solo datos.

METADATA_ONLY: Solo metadatos.

NONE: No comprime.

6.3 Archivo de configuración

Se puede crear un archivo de configuración. En mi caso he creado el archivo /home/oracle/COPY.par con la siguiente información

DIRECTORY=oracle-backup-sql 
DUMPFILE=orclschm%U.dmp   
FILESIZE=1M
LOGFILE=schm_exp.log
SCHEMAS=SCHM01_XIMO
COMPRESSION=ALL NO USAR EN ORACLE EXPRESS, SOLO EN ENTERPRISE

Y la sentencia a ejecutar es:

expdp backupuser/myPassword@EXPDB1 PARFILE=/home/oracle/COPY.par

Oracle 5 - Nuevo enfoque (2).Utilizar DBeaver para manejar Oracle Express

 0. Introducción

En Oracle tenemos estos conceptos:

SCHEMA: Un conjunto de tablas y otros elementos (funciones, procedimientos almacenados, tipos indices, vistas...)que tienen ciertas caracteristicas comunes. Por ejemplo podemos tener un esquema pora territorio,  para personas, para contabilidad etc. 

TABLESPACE: Sistema físico donde almacenar las tablas, el concepto es similar al de un directorio que puede guardar ficheros. Fijare que las tablas de un mismos SCHEMA pueden estar en distintos TABLESPACES.

Para crear una tabla, DBeaver nos ofrece la opción de seleccionar un SCHEMA y un TABLESPACE. Por omisión si no se los indicamos los crea en un esquema

IMPORTANTE: Tener cuidado con el contenedor (Base de datos) que estamos usando, como ya se vió:

Para listar los contenedores (siempre que no hayamos entrado en algún contenedor específico) hacemos

SQL> select con_id, name from v$containers;

Y para utilizar un contenedor debemos hacer 

SQL> ALTER SESSION SET CONTAINER = XEPDB1;

1. Creación de un SCHEMA.

Hacemos click derecho sobre Schemas y seleccionamos Create new Schema. Le damos nombre y contraseña

También se hubiera podido hacer ejecutando un script dentro de DBeaver (este escript dentro de DBeaver, se ejecuta en el contenedor XEPDB1).

CREATE USER SCHM01_XIMO IDENTIFIED BY myPassword;

OJO: Oracle considera un esquema como un usuario. 

Según el tutorial de Oracle hay que darle permisos y todo como un usuario.

GRANT CONNECT, RESOURCE, DBA TO SCHM01_XIMO;

2. Creación de un TABLESPACE.

Parece ser que en DBeaver not enomos la opción de crear TABLESPACES. Para ello hacemos click derecho sobre la conexion (en mi caso XEPDB1), Seleccionamos SQL Editor y New SQL Script  

Y ejecutamos el script

CREATE TABLESPACE TABSPC01_XIMO DATAFILE '/opt/oracle/oradata/XE/XEPDB1/TABSPC01_XIMO.dbf' size 50M;

y refrescando DBeaver vemos dicho TABLESPACE

3. Creación de una TABLA. 

Vamos Schemas - SCHM01_XIMO - Tables y con click derecho seleccionamos Create New Table   

y nos pide el nombre de la tabla ,TABLESACE y mas información.

Podemos utilizar un script SQL y crear una tabla. Para ello, este escipt sirva de ejemplo que se ha tomado de la base de datos ejemplo del tutorial de Oracle que se ha modificado para añadir el SCHEMA y el TABLESPACE

CREATE TABLE SCHM01_XIMO.REGIONS 
     (
        REGION_ID       NUMBER          GENERATED BY DEFAULT AS IDENTITY START WITH 5 PRIMARY KEY, 
        REGION_NAME     VARCHAR2(50)    NOT NULL --, 
     )
     TABLESPACE TABSPC01_XIMO;

4. Modificar script de creación de tablas

Vamos a modificar el script de creación de tablas del tutorial de Oracle, para ello tenemos que añadir el SCHEMA y el TABLESPACE, basta con buscar y sustituir 

1. "TABLE " por "TABLE SCHM01_XIMO." para añadir el SCHEMA y
2. ");" por  ") TABLESPACE TABSPC01_XIMO;"

y quedaría:

--------------------------------------------------------------------------------------
-- Name	       : OT (Oracle Tutorial) Sample Database
-- Link	       : http://www.oracletutorial.com/oracle-sample-database/
-- Version     : 1.0
-- Last Updated: July-28-2017
-- Copyright   : Copyright © 2017 by www.oracletutorial.com. All Rights Reserved.
-- Notice      : Use this sample database for the educational purpose only.
--               Credit the site oracletutorial.com explitly in your materials that
--               use this sample database.
--------------------------------------------------------------------------------------


---------------------------------------------------------------------------
-- execute the following statements to create tables
---------------------------------------------------------------------------
-- regions
CREATE TABLE SCHM01_XIMO.regions
  (
    region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2( 50 ) NOT NULL
  ) 
  TABLESPACE TABSPC01_XIMO;
-- countries table
CREATE TABLE SCHM01_XIMO.countries
  (
    country_id   CHAR( 2 ) PRIMARY KEY  ,
    country_name VARCHAR2( 40 ) NOT NULL,
    region_id    NUMBER                 , -- fk
    CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
      REFERENCES  SCHM01_XIMO.regions( region_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;

-- location
CREATE TABLE SCHM01_XIMO.locations
  (
    location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24 
                PRIMARY KEY       ,
    address     VARCHAR2( 255 ) NOT NULL,
    postal_code VARCHAR2( 20 )          ,
    city        VARCHAR2( 50 )          ,
    state       VARCHAR2( 50 )          ,
    country_id  CHAR( 2 )               , -- fk
    CONSTRAINT fk_locations_countries 
      FOREIGN KEY( country_id )
      REFERENCES  SCHM01_XIMO.countries( country_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- warehouses
CREATE TABLE SCHM01_XIMO.warehouses
  (
    warehouse_id NUMBER 
                 GENERATED BY DEFAULT AS IDENTITY START WITH 10 
                 PRIMARY KEY,
    warehouse_name VARCHAR( 255 ) ,
    location_id    NUMBER( 12, 0 ), -- fk
    CONSTRAINT fk_warehouses_locations 
      FOREIGN KEY( location_id )
      REFERENCES  SCHM01_XIMO.locations( location_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- employees
CREATE TABLE SCHM01_XIMO.employees
  (
    employee_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 108 
                PRIMARY KEY,
    first_name VARCHAR( 255 ) NOT NULL,
    last_name  VARCHAR( 255 ) NOT NULL,
    email      VARCHAR( 255 ) NOT NULL,
    phone      VARCHAR( 50 ) NOT NULL ,
    hire_date  DATE NOT NULL          ,
    manager_id NUMBER( 12, 0 )        , -- fk
    job_title  VARCHAR( 255 ) NOT NULL,
    CONSTRAINT fk_employees_manager 
        FOREIGN KEY( manager_id )
        REFERENCES  SCHM01_XIMO.employees( employee_id )
        ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- product category
CREATE TABLE SCHM01_XIMO.product_categories
  (
    category_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 6 
                PRIMARY KEY,
    category_name VARCHAR2( 255 ) NOT NULL
  ) 
  TABLESPACE TABSPC01_XIMO;

-- products table
CREATE TABLE SCHM01_XIMO.products
  (
    product_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 289 
               PRIMARY KEY,
    product_name  VARCHAR2( 255 ) NOT NULL,
    description   VARCHAR2( 2000 )        ,
    standard_cost NUMBER( 9, 2 )          ,
    list_price    NUMBER( 9, 2 )          ,
    category_id   NUMBER NOT NULL         ,
    CONSTRAINT fk_products_categories 
      FOREIGN KEY( category_id )
      REFERENCES  SCHM01_XIMO.product_categories( category_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- customers
CREATE TABLE SCHM01_XIMO.customers
  (
    customer_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                PRIMARY KEY,
    name         VARCHAR2( 255 ) NOT NULL,
    address      VARCHAR2( 255 )         ,
    website      VARCHAR2( 255 )         ,
    credit_limit NUMBER( 8, 2 )
  ) 
  TABLESPACE TABSPC01_XIMO;
-- contacts
CREATE TABLE SCHM01_XIMO.contacts
  (
    contact_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 320 
               PRIMARY KEY,
    first_name  VARCHAR2( 255 ) NOT NULL,
    last_name   VARCHAR2( 255 ) NOT NULL,
    email       VARCHAR2( 255 ) NOT NULL,
    phone       VARCHAR2( 20 )          ,
    customer_id NUMBER                  ,
    CONSTRAINT fk_contacts_customers 
      FOREIGN KEY( customer_id )
      REFERENCES  SCHM01_XIMO.customers( customer_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- orders table
CREATE TABLE SCHM01_XIMO.orders
  (
    order_id NUMBER 
             GENERATED BY DEFAULT AS IDENTITY START WITH 106 
             PRIMARY KEY,
    customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
    status      VARCHAR( 20 ) NOT NULL ,
    salesman_id NUMBER( 6, 0 )         , -- fk
    order_date  DATE NOT NULL          ,
    CONSTRAINT fk_orders_customers 
      FOREIGN KEY( customer_id )
      REFERENCES  SCHM01_XIMO.customers( customer_id )
      ON DELETE CASCADE,
    CONSTRAINT fk_orders_employees 
      FOREIGN KEY( salesman_id )
      REFERENCES  SCHM01_XIMO.employees( employee_id ) 
      ON DELETE SET NULL
  ) 
  TABLESPACE TABSPC01_XIMO;
-- order items
CREATE TABLE SCHM01_XIMO.order_items
  (
    order_id   NUMBER( 12, 0 )                                , -- fk
    item_id    NUMBER( 12, 0 )                                ,
    product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk
    quantity   NUMBER( 8, 2 ) NOT NULL                        ,
    unit_price NUMBER( 8, 2 ) NOT NULL                        ,
    CONSTRAINT pk_order_items 
      PRIMARY KEY( order_id, item_id ),
    CONSTRAINT fk_order_items_products 
      FOREIGN KEY( product_id )
      REFERENCES  SCHM01_XIMO.products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_order_items_orders 
      FOREIGN KEY( order_id )
      REFERENCES  SCHM01_XIMO.orders( order_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;
-- inventories
CREATE TABLE SCHM01_XIMO.inventories
  (
    product_id   NUMBER( 12, 0 )        , -- fk
    warehouse_id NUMBER( 12, 0 )        , -- fk
    quantity     NUMBER( 8, 0 ) NOT NULL,
    CONSTRAINT pk_inventories 
      PRIMARY KEY( product_id, warehouse_id ),
    CONSTRAINT fk_inventories_products 
      FOREIGN KEY( product_id )
      REFERENCES  SCHM01_XIMO.products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_inventories_warehouses 
      FOREIGN KEY( warehouse_id )
      REFERENCES  SCHM01_XIMO.warehouses( warehouse_id ) 
      ON DELETE CASCADE
  ) 
  TABLESPACE TABSPC01_XIMO;

Y se a da a ejecutar dicho escript y si acaso fallara, se puede seleccionar el código de creación de una tabla y crearla una por una.

Para ejecutar el script se seleccionan las sentencias a ejecturar y se le da al símbolo

Y al final tedríamos:

5. Modificar el script de carga de datos

Procediendo igual que antes, sobre elscript ot_data.sql cambiando  :

1. "TABLE " por "TABLE SCHM01_XIMO." para añadir el SCHEMA,
2. "Insert into " por "Insert into SCHM01_XIMO."
3. "REM INSERTING" por "--REM INSERTING." (Comentando)
4. "SET DEFINE" por "--SET DEFINE" (Comentando)

Ahora se tienen problemas para cargar la fecha y para ello da un error ORA1843 en la sentencia TO_DATE que dice que el més esta mal. Según stackoverflow podemos ejecutar una consulta como esta para ver como se lamna los meses del año en nuestro idioma

SELECT TO_CHAR(TO_DATE(1, 'MM'), 'MON'), 
TO_CHAR(TO_DATE(2, 'MM'), 'MON'),
TO_CHAR(TO_DATE(3, 'MM'), 'MON'),
TO_CHAR(TO_DATE(4, 'MM'), 'MON'),
TO_CHAR(TO_DATE(5, 'MM'), 'MON'),
TO_CHAR(TO_DATE(6, 'MM'), 'MON'),
TO_CHAR(TO_DATE(7, 'MM'), 'MON'),
TO_CHAR(TO_DATE(8, 'MM'), 'MON'),
TO_CHAR(TO_DATE(9, 'MM'), 'MON'),
TO_CHAR(TO_DATE(10, 'MM'), 'MON'),
TO_CHAR(TO_DATE(11, 'MM'), 'MON'),
TO_CHAR(TO_DATE(12, 'MM'), 'MON')
FROM DUAL;

Y se obtiene en mi caso:

GEN. FEBR. MARÇ ABR. ABR. MAIG JUNY JUL. AG. SET. OCT. NOV. DES.

Ahora debemos cambiar los meses en ingles (JAN FEB MAR APR JUN JUL AUG OCT NOV DEC) por los anteriores y ejecutar, y en teopría se deberían cargar

Analogamente se ejecutarían las sentenciasdel script  de carga de datos.