Início > Oracle, RedHat > Oracle 11.2.0.3: EXPDP / IMPDP FULL DATABASE

Oracle 11.2.0.3: EXPDP / IMPDP FULL DATABASE

1.       SERVIDOR DE ORIGEM ORA01

 

DOWNLOAD FULL DOCUMENT: EXPDP IMPDP

 

1.1.     Crie o diretório de export

[oracle@ORA01 ~]$ cd /u04/app/oracle/export/PROD
[oracle@ORA01 PROD]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>  CREATE OR REPLACE DIRECTORY DIR_EXPORT_U04 AS '/u04/app/oracle/export/PROD';
Directory created.
SQL> exit;

1.2.     Verifique os parametros instância de origem e guarde essas informações

[oracle@ORA01 PROD]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select instance_name from v$instance; 

 INSTANCE_NAME 
 ---------------- 
 prod 

 SQL> SELECT value FROM v$nls_parameters; 

 VALUE 
 --------------------------------------------- 
 AMERICAN 
 AMERICA 
 $ 
 AMERICA 
 ., 
 GREGORIAN 
 DD/MM/YYYY 
 AMERICAN 
 WE8MSWIN1252 
 BINARY 
 HH.MI.SSXFF AM 
 DD-MON-RR HH.MI.SSXFF AM 
 HH.MI.SSXFF AM TZR 
 DD-MON-RR HH.MI.SSXFF AM TZR 
 $ 
 AL16UTF16 
 BINARY 
 BYTE 
 FALSE 

 19 rows selected.

1.3.     Liste os Tablespace, Datafiles e seus respectivos tamanhos

 

[oracle@ORA01 PROD]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> col file_name format a70
2    col tablespace_name format a30
3    SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
4    FROM dba_data_files ORDER BY 1;

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
+DGDATA/prod/datafile/sysaux.274.744668437                   SYSAUX                     1106
+DGDATA/prod/datafile/system.273.744668419                   SYSTEM                     5775
+DGDATA/prod/datafile/undotbs1.275.744668451                 UNDOTBS1                  13778
+DGDATA/prod/datafile/users.277.744668463                    USERS                      3712
+DGDATA/prod/datafile/TSAPAAIX.279.744722453                 TSAPAAIX                   1049
+DGDATA/prod/datafile/TSMSAFDT.291.744722541                 TSMSAFDT                   4981
+DGDATA/prod/datafile/TSMSAFDT.292.744722559                 TSMSAFDT                   4194
+DGDATA/prod/datafile/TSMSAFDT.293.744722579                 TSMSAFDT                   4194
+DGDATA/prod/datafile/TSMSAFDT.294.744722595                 TSMSAFDT                   4456

139 rows selected.
SQL>

1.4.     Crie o arquivo de export com a opção FULL=Y

[oracle@ORA01 PROD]$ expdp system/password@db10g full=Y 
directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

1.5.     Copie o arquivo para o servidor de destino

[oracle@ORA01 PROD]$ scp -r /u04/app/oracle/export/PROD oracle@192.168.1.2:/u04/app/oracle/export/PROD


 2.       SERVIDOR DE DESTINO ORA02

2.1.     Utilizando o DBCA crie um database com mesmo

– Selecione o mesmo Character Set da base de origem

– Desabilite o ARCHIVE LOG MODE

2.2.     Parametros do Servidor

 

2.2.1. Verificando os parametros

[oracle@ORA02 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select instance_name from v$instance; 

 INSTANCE_NAME
 ---------------- 
 prod 

 SQL> SELECT value FROM v$nls_parameters; 

 VALUE 
 --------------------------------------------- 
 AMERICAN 
 AMERICA 
 $ 
 AMERICA 
 ., 
 GREGORIAN 
 YYYY-MM-DD
AMERICAN 
 WE8MSWIN1252 
 BINARY 
 HH.MI.SSXFF AM 
 DD-MON-RR HH.MI.SSXFF AM 
 HH.MI.SSXFF AM TZR 
 DD-MON-RR HH.MI.SSXFF AM TZR 
 $ 
 AL16UTF16 
 BINARY 
 BYTE 
 FALSE 

 19 rows selected.

SQL> SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

VALUE
—————————————————————
YYYY-MM-DD

####### O formato da data está diferente da origem #######

2.2.2. Alternando o parametro de formatação de data

[oracle@ORA02 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> create pfile='/u01/app/oracle/initPROD.ora' from spfile;

SQL> exit;

[oracle@ORA02 oracle]$ vi initPROD.ora

PROD.__db_cache_size=3154116608
PROD.__java_pool_size=16777216
PROD.__large_pool_size=16777216
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=2701131776
PROD.__sga_target=4043309056
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=788529152
PROD.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/PROD/controlfile/o1_mf_8hfld38y_.ctl','/u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_8hfld3bl_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/app/oracle/oradata'
*.db_domain='DOMINIO.LOCAL'
*.db_name='PROD'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=63042486272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=6744440832
*.open_cursors=300
*.processes=4000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=4405
*.undo_tablespace='UNDOTBS1'
## Add this line bellow
*.nls_date_format='DD/MM/YYYY'

[oracle@ ORA02 oracle]$

[oracle@ ORA02 oracle]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup pfile='/u01/app/oracle/initPROD.ora';
ORACLE instance started.

Total System Global Area 6714322944 bytes
Fixed Size                  2241064 bytes
Variable Size            3539996120 bytes
Database Buffers         3154116608 bytes
Redo Buffers               17969152 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
----------
16/01/2013

SQL> create spfile from
pfile='/u01/app/oracle/initPROD.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6714322944 bytes
Fixed Size                  2241064 bytes
Variable Size            3539996120 bytes
Database Buffers         3154116608 bytes
Redo Buffers               17969152 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
----------
16/01/2013

SQL>

2.3.     Tablespace

Se o caminho dos datafiles no servidor de destino for diferente do caminho no servidor de origem, preciso criar previamente os Tablespace.

Nesse nosso exemplo o servidor de Origem utiliza ASM e o servidor de destino utiliza file system ext4, portanto precisamos criar nosso tablespaces com seus datafiles.

Obs.: Se o caminho dos seus datafiles foram os mesmos no servidor de origem e destino você pode pular esse item 2.3.

 

 

2.3.1. Vamos utilizar as informações do item 1.3 para criar nossos tablespaces

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
+DGDATA/prod/datafile/sysaux.274.744668437                   SYSAUX                     1106
+DGDATA/prod/datafile/system.273.744668419                   SYSTEM                     5775
+DGDATA/prod/datafile/undotbs1.275.744668451                 UNDOTBS1                  13778
+DGDATA/prod/datafile/users.277.744668463                    USERS                      3712
+DGDATA/prod/datafile/tsapaaix.279.744722453                 TSAPAAIX                   1049
+DGDATA/prod/datafile/tsmsafdt.291.744722541                 TSMSAFDT                   4981
+DGDATA/prod/datafile/tsmsafdt.292.744722559                 TSMSAFDT                   4194
+DGDATA/prod/datafile/tsmsafdt.293.744722579                 TSMSAFDT                   4194
+DGDATA/prod/datafile/tsmsafdt.294.744722595                 TSMSAFDT                   4456

Os quatro primeiros Tablespaces (SYSAUX, SYSTEM, UNDOTBS1 e USERS) são de sistemas e já existem.

Os dois tablespaces TSAPAAIX e TSMSAFDT são de usuário e precisam ser criados;

2.3.2. Execute o script abaixo no servidor de destino para criar os tablespaces

--TABLESPACE DE USUÁRIO SERÃO CRIADAS
CREATE TABLESPACE TSAPAAIX DATAFILE
'/u02/app/oracle/oradata/PROD/datafile/tsapaaix.dbf'
SIZE 1049m REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT

SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TSMSAFDT DATAFILE
'/u02/app/oracle/oradata/PROD/datafile/tsmsafdt01.dbf'
SIZE 10486m REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/u02/app/oracle/oradata/PROD/datafile/tsmsafdt02.dbf'
SIZE 10486m REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/u02/app/oracle/oradata/PROD/datafile/tsmsafdt03.dbf'
SIZE 10486m REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/u02/app/oracle/oradata/PROD/datafile/tsmsafdt04.dbf'
SIZE 10486m REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

2.4.     Importação

[oracle@ORA02 PROD]$ ll
total 104340752
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 14:13 expdp_full_prod_2_01.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 14:55 expdp_full_prod_2_02.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 14:47 expdp_full_prod_2_03.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:01 expdp_full_prod_2_04.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:04 expdp_full_prod_2_05.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:22 expdp_full_prod_2_06.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:25 expdp_full_prod_2_07.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:32 expdp_full_prod_2_08.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:44 expdp_full_prod_2_09.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 15:57 expdp_full_prod_2_10.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 16:08 expdp_full_prod_2_11.dmp
-rw-r----- 1 oracle oinstall 8589934592 Jan 15 16:17 expdp_full_prod_2_12.dmp
-rw-r----- 1 oracle oinstall 3763863552 Jan 15 16:27 expdp_full_prod_2_13.dmp
[oracle@ORA02 PROD]$

[oracle@ORA02 PROD]$ impdp system/password@PROD FULL=Y DIRECTORY=DIR_EXPORT_U04
DUMPFILE=expdp_full_prod_2_%U.dmp LOGFILE=impdpPROD.log TABLE_EXISTS_ACTION=REPLACE

Import: Release 11.2.0.3.0 - Production on Thu Jan 17 10:01:54 2013

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

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@PROD full=Y directory=DIR_EXPORT_U04 dumpfile=expdp_full_prod_2_%U.dmp logfile=impdpPROD.log TABLE_EXISTS_ACTION=REPLACE
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/LIBRARY/LIBRARY
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 0 error(s) at 21:51:59

[oracle@ORA02 PROD]$

2.5.     Como verificar se a importação esta executando corretamente

[oracle@ORA02 PROD]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 16 13:54:12 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> select sql_text from v$sql where sql_text like 'CREATE%' and users_executing=1;
SQL_TEXT
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SERVCOMNET"."SYS_PKTEMPLATE" ON "SERVCOMNET"."TEMPLATE" ("I
DTEMPLATE") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 10485
76 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "
TSSERVCOMNETDT" PARALLEL 1
SQL> SELECT JOB_NAME, OPERATION FROM DBA_DATAPUMP_JOBS;
JOB_NAME                       OPERATION
------------------------------ ------------------------------
SYS_IMPORT_FULL_01             IMPORT
SQL> exit

[oracle@ORA02 ~]$ impdp system/oracle11g@PROD  attach=SYS_IMPORT_FULL_01
Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 25,072,580,204
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_01.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_02.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_03.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_04.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_05.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_06.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_07.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_08.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_09.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_10.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_11.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_12.dmp
  Dump File: /u04/app/oracle/export/PROD/expdp_full_prod_2_13.dmp

Worker 1 Status:
  State: EXECUTING
  Object Schema: SERVCOMNET
  Object Name: SYS_PKTEMPLATE
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
  Completed Objects: 295
  Worker Parallelism: 1

Espero ter ajudado!

Categorias:Oracle, RedHat Tags:,
  1. Diego Andrade
    21 de junho de 2016 às 4:56 PM

    Fala Lucas. Tudo bom?
    Cara… muito bom o seu artigo. muito bem explicado. Parabéns!
    Eu tentei aplicar o que você mostrou nesse artigo mas estou tentando migrar um database do 11g para um non-cdb do 12c. O problema é que ao fazer o impdp estou recebendo os seguintes erros:
    ORA-39002
    ORA-39070
    ORA-29283
    ORA-06512
    ORA-29283
    Você tem alguma dica de como resolver esse problema?
    Valeu!!

    • 6 de setembro de 2016 às 9:40 AM

      Oi Diego, eu me esqueci de responder sua dúvida. Teve algum progresso? ainda precisa de ajuda?

  1. No trackbacks yet.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: