Duplicate Database Skip Tablespace
Salve Galera, a quanto tempo heim???!!!
Estive lendo o post do Alex Zaballa onde ele fala sobre Recuperação de Tabelas com o Oracle RMAN 12c e pensando “Já estava na hora de haver esta opção no RMAN”.
Mas uma coisa me chamou a atenção, como eu ainda não estudei a fundo as novas features do 12c, fiquei um pouco decepcionado com esta option, pois para restaurar uma tabela o RMAN faz um “Duplicate -> Export -> Import -> Drop Database”, fiquei pensando sobre estes passos, onde nada mais é do que um Duplicate Database com Skip Tablespace, a vantagem é que todo o passo-à-passo é realizado pelo RMAN, sem muita interação do DBA.
Eu já precisei utilizar esta opção algumas vezes para fazer restore de tabelas via RMAN de um banco 11g sem ter que restaurar o banco inteiro, isto é se seu banco possui os dados divididos em várias tablespaces, pois só é vantajoso realizar este duplicate neste caso. Se em seu banco os indices, tabelas, partições, tabelas antigas, tabelas read only, forem armazenadas na mesma tablespace o seu duplicate terá que ser FULL.
Vamos ao processo de restaurar uma tabela usando RMAN com Duplicate Database, somente lembrando que para isto o banco precisa estar em ARCHIVELOG.
Criei um banco de exemplo chamado ORCL e algumas tablespaces, conforme abaixo.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
6 rows selected.Como nesta demonstração a intenção é fazer um recover com skip tablespace, acabei criando mais 6 tablespaces, como se fosse armazenar dados atuais, dados antigos e dados apenas Read Only.
SQL> create tablespace DADOS datafile '+DATA' size 10M;
Tablespace created.
SQL> create tablespace INDICES datafile '+DATA' size 10M;
Tablespace created.
SQL> create tablespace DADOS_OLD datafile '+DATA' size 10M;
Tablespace created.
SQL> create tablespace INDICES_OLD datafile '+DATA' size 10M;
Tablespace created.
SQL> create tablespace DADOS_READONLY datafile '+DATA' size 10M;
Tablespace created.
SQL> create tablespace INDICES_READONLY datafile '+DATA' size 10M;
Tablespace created.Agora podemos ver que neste banco de demonstração, alem das tablespaces default, também existem mais 6 tablespaces, tornando nosso desafio mais empolgante.
SQL> set pagesize 20
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
DADOS
INDICES
DADOS_OLD
INDICES_OLD
DADOS_READONLY
INDICES_READONLY
12 rows selected.Agora vamos a carga de dados para esta demonstração, para isto criei um usuário e fazendo um merchandising com o meu nome, claro.
SQL> create user regis identified by regis default tablespace DADOS temporary tablespace TEMP;
User created.
SQL> grant create session, create table to regis;
Grant succeeded.O grant abaixo foi para não ficar tendo que dar grant de quota para o usuário em todas as tablespaces criadas.
SQL> grant unlimited tablespace to regis;
Grant succeededAgora vamos a criação das tabelas e indices em suas respectivas tablespaces, conforme abaixo.
SQL> conn regis/regis
Connected.
SQL> create table TESTE_DADOS (id_tdados number(10), cd_tdados varchar2(50)) tablespace DADOS;
Table created.
SQL> alter table TESTE_DADOS add constraint PK_TESTE_DADOS primary key (id_tdados) using index tablespace INDICES;
Table altered.
SQL> create table TESTE_DADOS_OLD (id_tdados number(10), cd_tdados varchar2(50)) tablespace DADOS_OLD;
Table created.
SQL> alter table TESTE_DADOS_OLD add constraint PK_TESTE_DADOS_OLD primary key (id_tdados) using index tablespace INDICES_OLD;
Table altered.
SQL> create table TESTE_DADOS_RONLY (id_tdados number(10), cd_tdados varchar2(50)) tablespace DADOS_READONLY;
Table created.
SQL> alter table TESTE_DADOS_RONLY add constraint PK_TESTE_DADOS_RONLY primary key (id_tdados) using index tablespace INDICES_READONLY;
Table altered.Agora vamos inserir alguns dados a estas tabelas, pois não adianta fazer demonstração sem os dados.
SQL> INSERT INTO TESTE_DADOS ( SELECT LEVEL,'DADOS' FROM DUAL CONNECT BY LEVEL < 10001);
10000 rows created.
SQL> INSERT INTO TESTE_DADOS_OLD ( SELECT LEVEL,'DADOS' FROM DUAL CONNECT BY LEVEL < 10001);
10000 rows created.
SQL> INSERT INTO TESTE_DADOS_RONLY (SELECT LEVEL,'DADOS' FROM DUAL CONNECT BY LEVEL < 10001);
10000 rows created.
SQL> commit;
Commit complete.Como vocês podem ver abaixo, as tabelas e indices foram criados em suas respectivas tablespaces alocando um pequeno espaço.
SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> select SEGMENT_NAME, TABLESPACE_NAME , sum(bytes)/1024/1024 from dba_segments where segment_name like '%TESTE%' group by SEGMENT_NAME, TABLESPACE_NAME ORDER BY 2;
SEGMENT_NAME TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------------- ------------------------------ --------------------
TESTE_DADOS DADOS .25
TESTE_DADOS_OLD DADOS_OLD .25
TESTE_DADOS_RONLY DADOS_READONLY .25
PK_TESTE_DADOS INDICES .1875
PK_TESTE_DADOS_OLD INDICES_OLD .1875
PK_TESTE_DADOS_RONLY INDICES_READONLY .1875
6 rows selected.Para ficar mais real, coloquei a tablespace DADOS_READONLY em READ ONLY, claro, não adianta ter uma tablespace com nome de READONLY e estar em READ WRITE.
SQL> conn / as sysdba
Connected.
SQL> ALTER TABLESPACE DADOS_READONLY READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE INDICES_READONLY READ ONLY;
Tablespace altered.Agora irei fazer um backup da base usando o RMAN, mas como eu havia dito no inicio do post, o banco precisa estar em modo ARCHIVELOG.
[oracle@DG01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 12:00:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/orcl/
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> exitPronto, já verificamos que o banco está em modo archive, irei realizar este backup sem uma base de catalogo, usando apenas o CONTROLFILE como repositório.
[oracle@DG01 orcl]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 15 12:00:39 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359248851)
RMAN> run {
2> allocate channel 'dev_0' type disk;
3> configure maxsetsize to 1G;
4> backup format '/u01/export/df_t%t_s%s_p%p' as compressed backupset database;
5> sql 'alter system archive log current';
6> backup format '/u01/export/al_t%t_s%s_p%p' archivelog all;
7> backup format '/u01/export/cf_t%t_s%s_p%p' current controlfile;
8> release channel 'dev_0';
9> }
using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=51 device type=DISK
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 1 G;
new RMAN configuration parameters are successfully stored
Starting backup at 15-NOV-13
channel dev_0: starting compressed full datafile backup set
channel dev_0: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.831486005
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.831486149
input datafile file number=00011 name=+DATA/orcl/datafile/indices_readonly.272.831486885
channel dev_0: starting piece 1 at 15-NOV-13
channel dev_0: finished piece 1 at 15-NOV-13
piece handle=/u01/export/df_t831488543_s1_p1 tag=TAG20131114T120224 comment=NONE
channel dev_0: backup set complete, elapsed time: 00:00:22
channel dev_0: starting compressed full datafile backup set
channel dev_0: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.831486003
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.831486007
input datafile file number=00006 name=+DATA/orcl/datafile/dados.267.831486755
input datafile file number=00007 name=+DATA/orcl/datafile/indices.268.831486825
input datafile file number=00008 name=+DATA/orcl/datafile/dados_old.269.831486843
input datafile file number=00009 name=+DATA/orcl/datafile/indices_old.270.831486855
input datafile file number=00010 name=+DATA/orcl/datafile/dados_readonly.271.831486869
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.831486009
channel dev_0: starting piece 1 at 15-NOV-13
channel dev_0: finished piece 1 at 15-NOV-13
piece handle=/u01/export/df_t831488566_s2_p1 tag=TAG20131114T120224 comment=NONE
channel dev_0: backup set complete, elapsed time: 00:00:35
channel dev_0: starting compressed full datafile backup set
channel dev_0: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel dev_0: starting piece 1 at 15-NOV-13
channel dev_0: finished piece 1 at 15-NOV-13
piece handle=/u01/export/df_t831488566_s3_p1 tag=TAG20131114T120224 comment=NONE
channel dev_0: backup set complete, elapsed time: 00:00:01
Finished backup at 15-NOV-13
sql statement: alter system archive log current
Starting backup at 15-NOV-13
current log archived
channel dev_0: starting archived log backup set
channel dev_0: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=831486604
input archived log thread=1 sequence=4 RECID=2 STAMP=831488605
input archived log thread=1 sequence=5 RECID=3 STAMP=831488605
channel dev_0: starting piece 1 at 15-NOV-13
channel dev_0: finished piece 1 at 15-NOV-13
piece handle=/u01/export/al_t831488605_s4_p1 tag=TAG20131114T120325 comment=NONE
channel dev_0: backup set complete, elapsed time: 00:00:01
Finished backup at 15-NOV-13
Starting backup at 15-NOV-13
channel dev_0: starting full datafile backup set
channel dev_0: specifying datafile(s) in backup set
including current control file in backup set
channel dev_0: starting piece 1 at 15-NOV-13
channel dev_0: finished piece 1 at 15-NOV-13
piece handle=/u01/export/cf_t831488605_s5_p1 tag=TAG20131114T120326 comment=NONE
channel dev_0: backup set complete, elapsed time: 00:00:01
Finished backup at 15-NOV-13
released channel: dev_0
RMAN> exit
Recovery Manager complete.Pronto, o backup foi realizado direto para o disco.
Vamos dar uma olhada em nossos dados, para saber qual a quantidade de linhas que existem.
SQL> exec dbms_stats.gather_schema_stats('REGIS');
PL/SQL procedure successfully completed.
SQL> conn regis/regis
Connected.
SQL> select table_name, num_rows from user_tables;
TABLE_NAME NUM_ROWS
------------------------------ ---------------------------
TESTE_DADOS_RONLY 10000
TESTE_DADOS_OLD 10000
TESTE_DADOS 10000Já verifiquei que na base existem 10000 linhas em cada tabela, mas digamos que por algum descuido,
alguem fez um delete em uma das tabelas, mas somente fomos informados algumas horas ou até alguns dias depois.
Mas neste caso não é discuido não, é na cara dura mesmo, vamos deletar metade dos registros da tabela TESTE_DADOS.
SQL> delete from TESTE_DADOS where id_tdados >= 5001;
5000 rows deleted.
SQL> commit;
Commit complete.Pronto, alguns registros deletados.
SQL> exec dbms_stats.gather_schema_stats('REGIS');
PL/SQL procedure successfully completed.
SQL> select owner, table_name, num_rows from dba_tables where owner = 'SYS' AND TABLE_NAME LIKE '%TESTE%';
TABLE_NAME NUM_ROWS
------------------------------ ----------
TESTE_DADOS 5000
TESTE_DADOS_OLD 10000
TESTE_DADOS_RONLY 10000Como vocês podem ver, a tabela TESTE_DADOS possui apanas 50% dos dados que existiam antes e agora será necessário restaurar esta tabela ao seu estado original, mas não há a possibilidade de usar FLASHBACK e também não existe backup via DATAPUMP, somente existe o backup via RMAN, mas não temos espaço suficiente para restaurar a base completa via RMAN e como a versão do banco é 11g, não existe a nova features do 12c.
Então será realizado um duplicate database com SKIP TABLESPACE, para isto será necessário encontrar um ponto até onde será restaurado este banco.
Estou usando como ponto de restauração o ultimo archive gerado no meu backup, que foi o archive de SEQUENCE 5. Você pode verificar o backup que será usado consultando o RMAN para saber qual o ultimo backup realizado antes do “problema” ter ocorrido na tabela.
EX: list backup of archivelog until time “TO_DATE(‘xx/xx/xxxx xx:xx:xx’,’DD/MM/YYYY HH24:MI:SS’)”;
Como já sei qual archive vou utilizar, não preciso rodar o list.
Agora irei verificar qual o NEXT_CHANGE# deste archive.
SQL> select first_change#, next_change# from v$archived_log where sequence# = 5;
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
1045120 1045128Já identificado o NEXT_CHANGE#, que nada mais é do que o SCN (System Change Number)
Porém no processo de duplicate que irei utilizar, não vou me conectar ao TARGET e nem ao CATALOGO, somente poderá ser feito utilizando o UNTIL TIME por este motivo iremos verificar qual a data do SCN capturado no select anterior (1045128).
Alguns não sabem mas o SCN nada mais é do que um TIMESTAMP, por isto pode-se usar a função SCN_TO_TIMESTAMP para capturar o TIMESTAMP do SCN, mas somente poderá retornar o TIMESTAMP dentro do próprio banco onde o SCN foi gerado.
SQL> select scn_to_timestamp(1045128) from dual;
SCN_TO_TIMESTAMP(1045128)
---------------------------------------------------------------------------
15-NOV-13 12.03.24.000000000 PMAgora que já temos o TIME que irei utilizar para o duplicate, vamos aos passos para o DUPLICATE.
Será necessário criar uma área CDUMP
[oracle@DG01 orcl]$ cd $ORACLE_BASE
[oracle@DG01 oracle]$ cd diag/rdbms/
[oracle@DG01 rdbms]$ ll
total 4
drwxr-x--- 3 oracle oinstall 4096 Nov 15 11:19 orcl
[oracle@DG01 rdbms]$ mkdir -p rest/cdump
[oracle@DG01 rdbms]$ cd rest/cdump
[oracle@DG01 resttable]$ pwd
/u01/app/oracle/diag/rdbms/rest/cdump
[oracle@DG01 cdump]$Para o duplicate é necessário um banco em modo NOMOUNT, por este motivo estou criando um INIT para subir o banco que será duplicado.
Os parametros que uso para este duplicate são apenas os demonstrados abaixo, já são o necessário para subir o banco em modo NOMOUNT.
[oracle@DG01 cdump]$ cd /u01/app/oracle/product/11.2.0/db/dbs/
[oracle@DG01 dbs]$ vi initrest.ora
*.db_name='rest'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/rest/cdump'
*.db_create_file_dest='+DATA'
*.undo_tablespace='UNDOTBS1'
[oracle@DG01 dbs]$
[oracle@DG01 dbs]$ export ORACLE_SID=rest
[oracle@DG01 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db/dbs
[oracle@DG01 dbs]$ ls | grep rest
initrest.ora
[oracle@DG01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 14:16:12 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db/dbs/initrest.ora';
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
SQL>Agora que o banco que será duplicado já está em NOMOUNT, irei demonstrar abaixo os passos que serão necessários para realizar o duplicate através do RMAN
Para este passo, precisará do DBID do banco original, que pode ser capturado no log do backup do RMAN ou logando com “rman target / ” no banco original ou conforme demonstrado abaixo.
[oracle@DG01 orcl]$ echo $ORACLE_SID
orcl
[oracle@DG01 orcl]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 15 14:44:02 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359248851)
RMAN> LIST DB_UNIQUE_NAME ALL;
using target database control file instead of recovery catalog
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
2 ORCL 1359248851 PRIMARY ORCLAgora que já temos o DBID do banco e também será necessário saber em qual tablespace está armazenada a tabela que precisamos restaurar, mas esta informação já foi demonstrada no inicio do post.
Um ponto importante, as tablespaces SYSTEM, SYSAUX, TEMP e UNDO não podem ser descartadas, pois estas tablespaces fazem parte da estrutura principal do banco.
OBS.: Na versão 11.2.0.2 ou superior, não existe a possibilidade de fazer um duplicate com skip se estiver conectado no TARGET, pois nesta versão foi implementado uma funcionalidade que valida no TARGET todas as informações necessárias para que o source duplicado fique 100% idêntico, então para fazer um duplicate com skip tablespace a partir da versão 11.2.0.2, o rman deverá ser conectado:
CATALOGO + AUXILIARY ou apenas AUXILIARY, no caso demonstrado, será apenas no AUXILIARY.
[oracle@DG01 dbs]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 15 14:45:59 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliary /
connected to auxiliary database: REST (not mounted)
RMAN> SET DBID 1359248851
executing command: SET DBID
RMAN> run {
2> ALLOCATE AUXILIARY CHANNEL 'dev_0' DEVICE TYPE DISK;
3> duplicate TARGET database to rest pfile=$ORACLE_HOME/dbs/initrest.ora
4> BACKUP LOCATION '/u01/export/'
5> SKIP TABLESPACE
6> 'DADOS_OLD',
7> 'DADOS_READONLY',
8> 'INDICES',
9> 'INDICES_OLD',
10> 'INDICES_READONLY'
11> noresume
12> logfile
13> GROUP 1 ('+DATA') SIZE 200M
14> ,GROUP 2 ('+DATA') SIZE 200M
15> ,GROUP 3 ('+DATA') SIZE 200M
16> UNTIL TIME "TO_DATE('15/11/2013 12:03:00','DD/MM/YYYY HH24:MI:SS')";
17> }
allocated channel: dev_0
channel dev_0: SID=18 device type=DISK
Starting Duplicate Db at 15-NOV-13
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
allocated channel: dev_0
channel dev_0: SID=18 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/rest/controlfile/current.280.831498385'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''REST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/export/cf_t831488605_s5_p1';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/rest/controlfile/current.280.831498385'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''REST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
allocated channel: dev_0
channel dev_0: SID=23 device type=DISK
Starting restore at 15-NOV-13
channel dev_0: restoring control file
channel dev_0: restore complete, elapsed time: 00:00:06
output file name=+DATA/rest/controlfile/current.280.831498385
Finished restore at 15-NOV-13
database mounted
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 7 skipped by request
Datafile 8 skipped by request
Datafile 9 skipped by request
Datafile 10 skipped by request
Datafile 11 skipped by request
contents of Memory Script:
{
set until scn 1045139;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
restore
clone database
skip forever tablespace "INDICES_READONLY",
"INDICES_OLD",
"INDICES",
"DADOS_READONLY",
"DADOS_OLD" ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 15-NOV-13
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to +DATA
channel dev_0: restoring datafile 00003 to +DATA
channel dev_0: restoring datafile 00004 to +DATA
channel dev_0: restoring datafile 00006 to +DATA
channel dev_0: reading from backup piece /u01/export/df_t831488566_s2_p1
channel dev_0: piece handle=/u01/export/df_t831488566_s2_p1 tag=TAG20131114T120224
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:55
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00002 to +DATA
channel dev_0: restoring datafile 00005 to +DATA
channel dev_0: reading from backup piece /u01/export/df_t831488543_s1_p1
channel dev_0: piece handle=/u01/export/df_t831488543_s1_p1 tag=TAG20131114T120224
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:25
Finished restore at 15-NOV-13
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=831480482 file name=+DATA/rest/datafile/system.281.831498403
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=831480482 file name=+DATA/rest/datafile/sysaux.285.831498457
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=831480482 file name=+DATA/rest/datafile/undotbs1.282.831498407
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=831480482 file name=+DATA/rest/datafile/users.284.831498409
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=831480483 file name=+DATA/rest/datafile/example.286.831498459
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=831480483 file name=+DATA/rest/datafile/dados.283.831498407
contents of Memory Script:
{
set until time "to_date('NOV 15 2013 12:03:00', 'MON DD YYYY HH24:MI:SS')";
recover
clone database
skip forever tablespace "INDICES_READONLY",
"INDICES_OLD",
"INDICES",
"DADOS_READONLY",
"DADOS_OLD" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 15-NOV-13
Executing: alter database datafile 11 offline drop
Executing: alter database datafile 9 offline drop
Executing: alter database datafile 7 offline drop
Executing: alter database datafile 10 offline drop
Executing: alter database datafile 8 offline drop
starting media recovery
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=4
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: reading from backup piece /u01/export/al_t831496258_s14_p1.dbf
channel dev_0: piece handle=/u01/export/al_t831496258_s14_p1.dbf tag=TAG20131114T141233
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_4_831468118.dbf thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_4_831468118.dbf RECID=1 STAMP=831480484
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=5
channel dev_0: reading from backup piece /u01/export/al_t831488605_s4_p1
channel dev_0: piece handle=/u01/export/al_t831488605_s4_p1 tag=TAG20131114T120325
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_5_831468118.dbf thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_5_831468118.dbf RECID=3 STAMP=831480487
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_6_831468118.dbf thread=1 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_6_831468118.dbf RECID=2 STAMP=831480486
media recovery complete, elapsed time: 00:00:03
Finished recover at 15-NOV-13
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''REST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
sql clone "create pfile= ''$ORACLE_HOME/dbs/initrest.ora'' from spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''REST'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
allocated channel: dev_0
channel dev_0: SID=23 device type=DISK
sql statement: create pfile= ''$ORACLE_HOME/dbs/initrest.ora'' from spfile
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA' ) SIZE 200 M ,
GROUP 2 ( '+DATA' ) SIZE 200 M ,
GROUP 3 ( '+DATA' ) SIZE 200 M
DATAFILE
'+DATA/rest/datafile/system.281.831498403'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/rest/datafile/sysaux.285.831498457",
"+DATA/rest/datafile/undotbs1.282.831498407",
"+DATA/rest/datafile/users.284.831498409",
"+DATA/rest/datafile/example.286.831498459",
"+DATA/rest/datafile/dados.283.831498407";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/rest/datafile/sysaux.285.831498457 RECID=1 STAMP=831480503
cataloged datafile copy
datafile copy file name=+DATA/rest/datafile/undotbs1.282.831498407 RECID=2 STAMP=831480503
cataloged datafile copy
datafile copy file name=+DATA/rest/datafile/users.284.831498409 RECID=3 STAMP=831480503
cataloged datafile copy
datafile copy file name=+DATA/rest/datafile/example.286.831498459 RECID=4 STAMP=831480503
cataloged datafile copy
datafile copy file name=+DATA/rest/datafile/dados.283.831498407 RECID=5 STAMP=831480503
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=831480503 file name=+DATA/rest/datafile/sysaux.285.831498457
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=831480503 file name=+DATA/rest/datafile/undotbs1.282.831498407
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=831480503 file name=+DATA/rest/datafile/users.284.831498409
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=831480503 file name=+DATA/rest/datafile/example.286.831498459
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=831480503 file name=+DATA/rest/datafile/dados.283.831498407
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Dropping offline and skipped tablespaces
Executing: drop tablespace "INDICES_READONLY" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES_READONLY due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "INDICES_OLD" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES_OLD due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "INDICES" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "DADOS_READONLY" including contents cascade constraints
Executing: drop tablespace "DADOS_OLD" including contents cascade constraints
Executing: drop tablespace "INDICES_READONLY" including contents cascade constraints
Executing: drop tablespace "INDICES_OLD" including contents cascade constraints
Executing: drop tablespace "INDICES" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "INDICES" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "INDICES" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "INDICES" including contents cascade constraints
ORACLE error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace INDICES due to constraints
Finished Duplicate Db at 15-NOV-13
released channel: dev_0
RMAN> exitPronto, banco duplicado e sem as demais tablespaces.
O erro apresentado “ORA-02429: cannot drop index used for enforcement of unique/primary key” realmente irá acontecer se a tablespace onde os indices da PK não forem a mesma em que está armazenado a TABELA, mas o duplicate apenas trouxe a tablespace e os dados contidos na mesma LOGICAMENTE e não FISICAMENTE.
Agora será verificado se o banco duplicado realmente está no ar e qual a situação dele.
[oracle@DG01 dbs]$ ps -ef | grep pmon
oracle 7360 1 0 11:16 ? 00:00:01 asm_pmon_+ASM
oracle 8549 1 0 11:25 ? 00:00:01 ora_pmon_orcl
oracle 14665 1 0 14:48 ? 00:00:00 ora_pmon_rest
oracle 14768 11905 0 14:49 pts/2 00:00:00 grep pmon
[oracle@DG01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 14:49:58 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select name from v$database;
NAME
---------
RESTPode-se verificar que o banco está no ar e em modo READ WRITE.
Agora será necessário verificar se a tabela alvo do teste foi realmente restaurada na situação original.
SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> select SEGMENT_NAME, TABLESPACE_NAME , sum(bytes)/1024/1024 from dba_segments where segment_name like '%TESTE%' and owner = 'REGIS' group by SEGMENT_NAME, TABLESPACE_NAME ORDER BY 2;
SEGMENT_NAME TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------------- ------------------------------ --------------------
TESTE_DADOS DADOS .25
PK_TESTE_DADOS INDICES .1875Como eu havia dito anteriormente, o duplicate restaurou LOGICAMENTE a tablespace e seu conteudo, porém ela não existe FISICAMENTE, conforme demonstrado abaixo.
SQL> set linesize 100
SQL> COLUMN FILE_NAME FORMAT A50
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME IN ('DADOS','INDICES');
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
+DATA/rest/datafile/dados.283.831498407 DADOS
/u01/app/oracle/product/11.2.0/db/dbs/MISSING00007 INDICESIsto demonstra que o DATAFILE pertencente a tablespace INDICES não existe, por este motivo ele consta com este nome e caminho.
Para equalizar o dicionário de dados do ORACLE, será necessário dropar a tablespace e o datafile inexistente.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/product/11.2.0/db/dbs/MISSING00007' OFFLINE;
Database altered.
SQL> drop tablespace INDICES including contents and datafiles;
Tablespace dropped.Procedimento realizado, agora a verificação dos dados da tabela que precisamos restaurar.
SQL> exec dbms_stats.gather_schema_stats('REGIS');
PL/SQL procedure successfully completed.
SQL> select owner, table_name, num_rows from dba_tables where owner = 'REGIS' AND TABLE_NAME LIKE '%TESTE%';
OWNER TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
REGIS TESTE_DADOS 10000Ai está, a tabela está com todos os registros antes de sua deleção, agora será necessário realizar os procedimentos de EXPORT/IMPORT e um IAS (Insert As Select) para equalizar a tabela original.
[oracle@DG01 u01]$ cd dump/
[oracle@DG01 dump]$ pwd
/u01/dump
[oracle@DG01 dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 15:01:57 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create directory BKP as '/u01/dump';
Directory created.
SQL> grant read, write on directory BKP to public;
Grant succeeded.
[oracle@DG01 dump]$ expdp regis directory=BKP dumpfile=TESTE_DADOS.dmp logfile=BKP:TESTE_DADOS.log tables=TESTE_DADOS
Export: Release 11.2.0.3.0 - Production on Fri Nov 15 15:07:19 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "REGIS"."SYS_EXPORT_TABLE_01": regis/******** directory=BKP dumpfile=TESTE_DADOS.dmp logfile=BKP:TESTE_DADOS.log tables=TESTE_DADOS
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "REGIS"."TESTE_DADOS" 141.9 KB 10000 rows
Master table "REGIS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for REGIS.SYS_EXPORT_TABLE_01 is:
/u01/dump/TESTE_DADOS.dmp
Job "REGIS"."SYS_EXPORT_TABLE_01" successfully completed at 15:07:31
[oracle@DG01 dump]$ export ORACLE_SID=orcl
[oracle@DG01 dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 15:08:34 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create directory BKP as '/u01/dump';
Directory created.
SQL> grant read, write on directory BKP to public;
Grant succeeded.
SQL> exit
[oracle@DG01 dump]$ impdp regis directory=BKP dumpfile=TESTE_DADOS.dmp logfile=BKP:IMPTESTE_DADOS.log tables=TESTE_DADOS REMAP_TABLE=TESTE_DADOS:TESTE_DADOSNEW
Import: Release 11.2.0.3.0 - Production on Fri Nov 15 15:09:32 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "REGIS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "REGIS"."SYS_IMPORT_TABLE_01": regis/******** directory=BKP dumpfile=TESTE_DADOS.dmp logfile=BKP:IMPTESTE_DADOS.log tables=TESTE_DADOS REMAP_TABLE=TESTE_DADOS:TES
TE_DADOSNEW
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REGIS"."TESTE_DADOSNEW" 141.9 KB 10000 rows
Job "REGIS"."SYS_IMPORT_TABLE_01" successfully completed at 15:09:37
[oracle@DG01 dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 15:10:16 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> conn regis/regis
Connected.
SQL> insert into TESTE_DADOS (SELECT * FROM TESTE_DADOSNEW N WHERE NOT EXISTS (SELECT 1 FROM TESTE_DADOS D WHERE D.id_tdados = N.id_tdados));
5000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@DG01 dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 15:12:35 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> exec dbms_stats.gather_schema_stats('REGIS');
PL/SQL procedure successfully completed.
SQL> conn regis/regis
Connected.
SQL> select table_name, num_rows from user_tables;
TABLE_NAME NUM_ROWS
------------------------------ ----------
TESTE_DADOSNEW 10000
TESTE_DADOS_RONLY 10000
TESTE_DADOS_OLD 10000
TESTE_DADOS 10000
SQL> drop table TESTE_DADOSNEW;
Table dropped.
SQL> select table_name, num_rows from user_tables;
TABLE_NAME NUM_ROWS
------------------------------ ----------
TESTE_DADOS 10000
TESTE_DADOS_OLD 10000
TESTE_DADOS_RONLY 10000Pronto, os procedimentos de EXPORT/IMPORT foram realizados, a equalização da tabela foi realizada, agora a tabela está com a mesma quantidade de dados de antes.
Agora só falta dropar o banco REST que foi criado através deste duplicate.
Este é o mesmo passo-a-passo que é realizado pelo RMAN do 12c, porém é muito mais trabalhoso, então para que ainda não possui o novíssimo 12c e precisa realizar este procedimento, segue o passo-a-passo de como faze-lo.
Espero que tenham gostado deste artigo e espero que seja util a todos como tem sido util a mim.
Um agradecimento especial a Lilian Barroso que me convenceu a montar este artigo, depois de uma de nossas conversas sobre trabalho!!!
Abraços galera!!!
