hagamos un export con expdp/impdp

1) Para bajar la base

1.       Verificar las variables de ambiente

2.       sqlplus sys/oracle_4U as sysdba

3.       shutdown immediate;

4.       lsnrctl stop

2) Para subir la base modo restrictive

             1. Startup restrict;

3) para crear un directorio

            1.       create or replace directory RESPALDO as ‘/u01/direccion…’;
             2.       grant read, write on directory RESPALDO to <USUARIO> ;

4) Checar la vista V$NLS_PARAMETERS

1)     Tomar los valores de NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET
     Y formar el export de la siguiente manera

select trim(parameter)

                       ||'='

            ||trim(value)

                      from v$nls_parameters
                  where parameter like '%NLS_LANGUAGE%'
                        or parameter like '%NLS_TERRITORY%'
                       or parameter like '%NLS_CHARACTERSET%';

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 

5) para hacer un export full

expdp system/oracle_4U full=y directory=RESPALDO dumpfile=salida.dmp logfile=salida.log

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 

impdp system/oracle_4U directory=RESPALDO content=all dumpfile=salida2.dmp schemas=OE,HR logfile=salida_imp.log

6) iniciar la base y el listener

                 Startup  -- inicia la base

                Lsnrctl start

sql muy utiles en migracion de bases

--cuantos indices invalidos
select owner,count(1)
from dba_indexes
where status = 'INVALID'
group by owner;
--cuales son los indices invalidos
select owner,
       index_name,
       index_type,
       table_owner,
       table_name,
       table_type,
       tablespace_name
from dba_indexes
where status = 'INVALID';

-- cuantos son los objetos invalidos
select owner,object_type,count(1)
from dba_objects
where status = 'INVALID'
group by owner,object_type
order by 1,2;

-- cuales son los objetos invalidos
select owner,
       object_name,
       created,
       last_ddl_time
from dba_objects
where status = 'INVALID'
order by 1,2;

-- Paquetes con bodies sin que no tengan sus correspondientes headers
select unique owner,name
from dba_source a
where type = 'PACKAGE BODY'
  and not exists (select null
                  from dba_source b
                  where a.owner = b.owner
                    and a.name = b.name
                    and b.type = 'PACKAGE') ;

-- Constraints deshabilitadas
select owner,
       case constraint_type
          when 'P' then 'PRIMARY_KEY'
          when 'R' then 'FOREIGN_KEY'
          when 'U' then 'UNIQUE'
          when 'C' then 'CHECK'
       end constraint_type, 
       count(1)
from dba_constraints
where status = 'DISABLED'
group by owner,constraint_type
order by 1,2;

select owner,
       constraint_name,
       constraint_type,
       table_name
from dba_constraints
where status = 'DISABLED'
order by 1,2;

--Triggers deshabilitados
select owner,
       trigger_name,
       trigger_type,
       triggering_event,
       table_owner,
       table_name
from dba_triggers
where status = 'DISABLED'
order by 1,2;

-- para contar registros de todas las tablas
SELECT 'SELECT '
       || q'{'}'
       ||OWNER
       || '.'
       || OBJECT_NAME
       || q'{'}'
       || ' as Origen, COUNT(*) as registros'
       || ' FROM '
       ||OWNER ||'.'
       || OBJECT_NAME
       || ' UNION'  --,
      -- OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE='TABLE'
   and owner not in  ('SYS','SYSTEM','DBSNMP');