Olá,
No artigo de Março/2010 eu abordei um pouco da técnica de recuperação pontual de dados já bem conhecida entre os DBAs Oracle chamada de Tablespace Point-in-Time Recovery (TSPITR) disponível desde a versão do Oracle 8 e também uma outra técnica chamada Database Point-in-Time Recovery (DBPITR). Como já explicado anteriormente naquele artigo, basicamente o TSPITR (Recuperação Pontual de Tablespace) é uma funcionalidade que fornece ao DBA a opção de recuperar um conjunto de dados em um ponto específico no tempo sem afetar o restante do banco de dados. Já o DBPITR possibilita um banco de dados inteiro "voltar no tempo" da mesma forma que o recurso Flashback Database (10g) possibilita. Neste artigo irei abordar um novo recurso que veio no RMAN do Oracle 12c chamado de Table-Point-In-Time Recovery. Daqui pra frente irei fazer referência a ele apenas como TPITR. Da mesma forma que no TSPITR, iremos notar que o TPITR também se utilizará não só de uma instância auxiliar, como também do utilitário Datapump (expdp/impdp) no processo de restore/recover. Vamos então a prática.
[oracle@linux1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 15:36:29 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Conectado a:
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 * from scott.t1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 linhas selecionadas.
SQL> select sysdate from dual;
SYSDATE
-------------------
01/06/2014 15:36:55
Podemos ver pelo resultado acima, que a tabela T1 de propriedade do usuário SCOTT contém 10 linhas e que a mesma existia às 15:36:55 do dia 01/06/2014. Após realização de um backup do banco de dados com o RMAN, irei dropar a tabela T1 conforme demonstrado abaixo.
SQL> drop table scott.t1 purge;
Tabela eliminada.
SQL> select * from scott.t1;
select * from scott.t1
*
ERRO na linha 1:
ORA-00942: a tabela ou view não existe
Pelo fato de eu ter utilizado a opção purge do comando drop table, a mesma não foi para a lixeira (recycle bin). Portanto, não será possível recuperá-la através do flashback drop. Bom, o objetivo então será recuperar a tabela diretamente do backup do banco de dados que fiz previamente através do RMAN. Para me certificar, segue abaixo a prova de que o backup foi realizado.
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 404.21M DISK 00:01:59 01/06/2014
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20140508T153228
Piece Name: /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------- ----
1 Full 365502 01/06/2014 /u01/app/oracle/oradata/BD01/system01.dbf
2 Full 365502 01/06/2014 /u01/app/oracle/oradata/BD01/sysaux01.dbf
3 Full 365502 01/06/2014 /u01/app/oracle/oradata/BD01/undotbs01.dbf
4 Full 365502 01/06/2014 /u01/app/oracle/oradata/BD01/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.61M DISK 00:00:03 01/06/2014
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20140508T153228
Piece Name: /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_ncnnf_TAG20140508T153228_9pqmkyfx_.bkp
Control File Included: Ckp SCN: 365548 Ckp time: 01/06/2014
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 9.64M DISK 00:00:02 01/06/2014
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20140508T153440
Piece Name: /u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
SPFILE Included: Modification time: 01/06/2014
SPFILE db_unique_name: BD01
Control File Included: Ckp SCN: 365556 Ckp time: 01/06/2014
Assim como nos métodos DBPITR e TSPITR, para que o processo de recuperação utilizando a técnica TPITR seja possível, será necessário criar um diretório no sistema de arquivos para uso da instância auxiliar que será criada no processo de recover.
[oracle@linux1 ~]$ mkdir /u01/aux_dest
Como eu sei que a tabela existia exatamente às 15:36:35, irei utilizar esse mesmo horário para recuperar a tabela T1. Segue abaixo o comandos necessário para realizar essa operação.
[oracle@linux1 ~]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jun 1 15:42:17 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: BD01 (DBID=3099510927)
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest';
Starting recover at 01/06/2014
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='hgkF'
initialization parameters used for automatic instance:
db_name=BD01
db_unique_name=hgkF_pitr_BD01
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/aux_dest
log_archive_dest_1='location=/u01/aux_dest'
#No auxiliary parameter file used
starting up automatic instance BD01
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=83 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp tag=TAG20140508T153440
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl
Finished restore at 01/06/2014
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/aux_dest/BD01/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 01/06/2014
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux_dest/BD01/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/aux_dest/BD01/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:39
Finished restore at 01/06/2014
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=847035974 file name=/u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=847035975 file name=/u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=847035976 file name=/u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:02
Finished recover at 01/06/2014
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter system set control_files = ''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01/06/2014
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=847036181 file name=/u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 4 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 4 online
Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:01
Finished recover at 01/06/2014
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_hgkF_glDz":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "SCOTT"."T1" 5.093 KB 10 rows
EXPDP> Master table "SYS"."TSPITR_EXP_hgkF_glDz" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_hgkF_glDz is:
EXPDP> /u01/aux_dest/tspitr_hgkF_20324.dmp
EXPDP> Job "SYS"."TSPITR_EXP_hgkF_glDz" successfully completed at Sun Jun 1 15:52:23 2014 elapsed 0 00:00:45
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_hgkF_znug" carregada/descarregada com sucesso
IMPDP> Iniciando "SYS"."TSPITR_IMP_hgkF_znug":
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . importou "SCOTT"."T1" 5.093 KB 10 linhas
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> O job "SYS"."TSPITR_IMP_hgkF_znug" foi concluido com sucesso em Dom Jun 1 15:55:09 2014 elapsed 0 00:01:49
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_temp_9pqn8x8x_.tmp deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_3_9pqngcjb_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_2_9pqngbsx_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_1_9pqng9r7_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl deleted
auxiliary instance file tspitr_hgkF_20324.dmp deleted
Finished recover at 01/06/2014
Pronto. Após a execução do processo de restore/recover, poderemos ver abaixo que a tabela foi recuperada com sucesso.
[oracle@linux1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 16:24:10 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Conectado a:
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 * from scott.t1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 linhas selecionadas.
Bom, da mesma forma que podemos restaurar uma tabela com o nome original, podemos também restaurá-la com um nome diferente. Para isso basta apenas utilizarmos a opção REMAP TABLE. Segue abaixo alguns trechos do processo de recuperação na qual eu irei restaurar a tabela com T1 com o nome de T1_DROP.
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> remap table SCOTT.T1:T1_DROP;
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_isFB_xdwa":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "SCOTT"."T1" 5.093 KB 10 rows
EXPDP> Master table "SYS"."TSPITR_EXP_isFB_xdwa" successfully loaded/unloaded
EXPDP> *****************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_isFB_xdwa is:
EXPDP> /u01/aux_dest/tspitr_isFB_93535.dmp
EXPDP> Job "SYS"."TSPITR_EXP_isFB_xdwa" successfully completed
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_isFB_EpCe" carregada/descarregada com sucesso
IMPDP> Iniciando "SYS"."TSPITR_IMP_isFB_EpCe":
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . importou "SCOTT"."T1_DROP" 5.093 KB 10 linhas
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> O job "SYS"."TSPITR_IMP_isFB_EpCe" foi concluido com sucesso
Import completed
Pronto. Após a finalização do processo de recover podemos ver abaixo a tabela T1_DROP.
RMAN> select owner,table_name from dba_tables where owner = 'SCOTT';
using target database control file instead of recovery catalog
OWNER TABLE_NAME
---------- ---------------
SCOTT T1
SCOTT T1_DROP
Para finalizar, existe uma outra opção que podemos utilizar no processo TPITR. Essa opção consiste em recuperar a tabela, mas não importá-la de volta para o banco de dados. Neste caso, apenas um dump contendo a tabela será gerado. Caso queiramos importá-la posteriormente poderemos fazer isso manualmente. Segue abaixo as opções que devemos utilizar para realizar essa operação.
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> datapump destination '/tmp'
5> dump file 't1.dmp'
6> notableimport;
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_pBbn_otbe":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "SCOTT"."T1" 5.093 KB 10 rows
EXPDP> Master table "SYS"."TSPITR_EXP_pBbn_otbe" successfully loaded/unloaded
EXPDP> *****************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_pBbn_otbe is:
EXPDP> /tmp/t1.dmp
EXPDP> Job "SYS"."TSPITR_EXP_pBbn_otbe" successfully completed
Export completed
Not performing table import after point-in-time recovery
Ao final, poderemos ver que o dump foi gerado no diretório especificado.
[oracle@linux1 tmp]$ ls -l /tmp/*.dmp
-rw-r----- 1 oracle oinstall 131072 Mai 8 17:03 /tmp/t1.dmp
4 comentários:
Bom dia Eduardo,
Outro dia já entrei em seu blog e verifiquei alguns procedimento,
Hoje estou com um problema, tenho na minha maquina o Oracle 10G XE com o conjunto de caracteres WE8MSWIN1252
Estou tentando importar no Oracle 11G XE o conjunto de caracteres é AL32UTF8
Mas não é compatível.
Gostaria verificar a possibilidade de alterar o Conjunto de Caracteres no 11G XE para WE8MSWIN1252
Já verifiquei em alguns sites mas não tive sucesso.
Olá Antonio,
Você já tentou executar o comando ALTER DATABASE no Oracle 11g XE para alterar o CHARACTER SET? Se não me engano o banco de dados ter que estar aberto no modo restrito.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESS=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESS=0;
SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Abraços
Legatti
Bom dia quando fui dar um ALTER SYSTEM.
deu um erro de opção ivalida para SYSTEM
Olá Antônio,
Me parece que os procedimentos são esses mesmos. Se você não conseguir, já pensou em reinstalar o Oracle XE com o novo CHARACTER SET?
Veja se esse artigo pode te ajudar.
Abraços
Legatti
Postar um comentário