RMAN – Tablespace Point in Time Recovery
Olá Pessoal, hoje irei demonstrar uma solução muito interessante do RMAN que agiliza muito a recuperação de apenas um Usuario (schema) do Banco de Dados. Esta solução foi indicada por um grande amigo Rodrigo Almeida (alphamek). Gostaria ja de agradecer a contribuição do Rodrigo Almeida (alphamek) e do David (Drbs) por terem me ajudado com meus estudos para este Post. Bom, mas vamos lá, este artigo irá ajudar os DBAs que necessitam voltar apenas um Usuario(schema) do Backup RMAN sem necessitar voltar o Backup inteiro. Muitos confundem com recuperação incompleta, mas é um pouco diferente.
Vamos supor que você tenha uma Tablespace EXAMPLE que com tenha varios Usuarios (Schemas) nesta mesma Tablespace e você precise voltar o backup de apenas um único usuario (chamado HR) com apenas um Backup RMAN (Sem Backup Export). Este artigo irá lhe ajudar a desenvolver esta solução.
Ambiente
- Objetivo: Recuperar um schema que foi deletado do servidor de produção através do RMAN com TSPITR.
- Versão do Oracle: Oracle 10g 10.2.0.4
- Sistema Operacional: Red Hat Enterprise Linux 5
- Backup: Backup Full Database com RMAN.
- Instância Auxiliar: Preparar uma Instancia Auxiliar para residir no mesmo Servidor que a Instancia Produção(target)
=================================================
Problema: Usuario(Schema) HR removido
=================================================
[oracle@db10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 18 02:52:19 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> drop user HR cascade;
User dropped.
SQL>
=================================================
Solução: Utilizar RMAN TSPITR - Tablespace Point in Time Recovery
=================================================
Recuperar o schema(HR) que foi deletado do servidor de produção através do RMAN com TSPITR, pois existem outros Schemas como o SCOTT que não podem ser alterados pois residem na mesma Tablespace EXAMPLE.
=================================================
1 - Passo: Criar um arquivo de Senha (Password File) para a instancia auxiliar:
=================================================
orapwd file=$ORACLE_HOME/dbs/orapwaux1 PASSWORD=teste ENTRIES=5
=================================================
2- Passo: Criar um arquivo de inicialização(Parameter File):
=================================================
Criar o arquivo /oracle/app/oradata/aux1/initAux.ora
touch initAux.ora
DB_NAME=db10g
DB_UNIQUE_NAME=tspitr_db10g
CONTROL_FILES=/oracle/app/oradata/aux1/control01.ctl
DB_FILE_NAME_CONVERT=('/oracle/app/oradata/db10g/','/oracle/app/oradata
/aux1/')
LOG_FILE_NAME_CONVERT=('/oracle/app/oradata/db10g/','/oracle/app/orada
ta/aux1/')
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
COMPATIBLE =10.2.0.3.0
DB_BLOCK_SIZE=8192
processes=150
pga_aggregate_target=16777216
undo_management=AUTO
undo_tablespace=UNDOTBS1
sga_target=167772160processes=150
pga_aggregate_target=16777216
undo_management=AUTO
undo_tablespace=UNDOTBS1
sga_target=167772160
=================================================
3- Passo: Criar a conectividade do Oracle Net para a Instancia Auxiliar. Para isso utilize a ferramenta NETMGR.
=================================================
- Criar um LISTENER Auxiliar
-------------------------------
L1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1522))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1521))
)
SID_LIST_L1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = aux1)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = aux1)
)
)
------------------------------------------------
- Criar um HOSTSTRING AUXILIAR - TNSNAMES.ORA
------------------------------------------------
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db10g)
)
)
AUX1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = aux1)
)
)
=================================================
4- Passo: Iniciar a Instancia Auxiliar em Modo NOMOUNT
=================================================
[oracle@db10g /]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 14 22:19:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> CONNECT SYS/teste@aux1 AS SYSDBA
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='/oracle/app/oradata/aux1/initAux.ora'
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 62917928 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
SQL>=================================================
5- Passo: Conectar com RMAN na Instância Auxiliar:
=================================================
[oracle@db10g /]$ rman target / auxiliary sys/teste@aux1
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 14 22:24:59 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DB10G (DBID=86932143)
connected to auxiliary database: DB10G (not mounted)
RMAN>
=================================================
6- Passo: Executar o restore e recover controlfile e montar o banco de dados clone
=================================================
RMAN>
RUN{
set until time "18-AUG-2009 00:55:00','DD-MON-YYYY HH24:MI:SS";
restore clone controlfile;
sql clone 'alter database mount clone database';
sql 'alter system archive log current';
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 18-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/backupRMAN/backup_FULL_RMAN_180809/controlfile.ctlc-86932143-
20090818-02
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/backupRMAN/backup_FULL_RMAN_180809/controlfile.ctlc-
86932143-20090818-02 tag=TAG20090818T004430
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/app/oradata/aux1/control01.ctl
Finished restore at 18-AUG-09
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
RMAN>
=================================================
7- Passo: Executar o restore e recover dos datafiles e abrir o banco de dados clone
=================================================
RMAN>
RUN{
set until time "18-AUG-2009 00:55:00','DD-MON-YYYY HH24:MI:SS";
set newname for datafile 1 to "/oracle/app/oradata/aux1/system01.dbf";
set newname for datafile 2 to "/oracle/app/oradata/aux1/undotbs01.dbf";
set newname for tempfile 1 to "/oracle/app/oradata/aux1/temp01.dbf";
set newname for datafile 3 to "/oracle/app/oradata/aux1/sysaux01.dbf";
set newname for datafile 5 to "/oracle/app/oradata/aux1/example01.dbf";
switch clone tempfile all;
restore clone datafile 1, 2, 3, 5;
switch clone datafile all;
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 5 online";
recover clone database tablespace "EXAMPLE", "SYSAUX", "SYSTEM",
"UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
sql clone "create tablespace aux_tspitr_tmp datafile
''/oracle/app/oradata/aux1/temp_aux.dbf'' size 500K";
}
7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
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
released channel: ORA_AUX_DISK_1
Starting restore at 18-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/app/oradata/aux1/system01.dbf
restoring datafile 00002 to /oracle/app/oradata/aux1/undotbs01.dbf
restoring datafile 00003 to /oracle/app/oradata/aux1/sysaux01.dbf
restoring datafile 00005 to /oracle/app/oradata/aux1/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/backupRMAN/backup_FULL_RMAN_180809/backupfullDB10G_20090818_0
gkmv3no_1_1.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/oracle/backupRMAN/backup_FULL_RMAN_180809/backupfullDB10G_200
90818_0gkmv3no_1_1.dbf tag=BKP_FULL
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 18-AUG-09
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 5 online
Starting recover at 18-AUG-09
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 25 is already on disk as file
/archivelog/db10g/1_25_694590577.arc
archive log thread 1 sequence 26 is already on disk as file
/archivelog/db10g/1_26_694590577.arc
archive log thread 1 sequence 27 is already on disk as file
/archivelog/db10g/1_27_694590577.arc
archive log filename=/archivelog/db10g/1_25_694590577.arc thread=1 sequence=25
archive log filename=/archivelog/db10g/1_26_694590577.arc thread=1 sequence=26
archive log filename=/archivelog/db10g/1_27_694590577.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-AUG-09
database opened
sql statement: create tablespace aux_tspitr_tmp datafile
''/oracle/app/oradata/aux1/temp_aux.dbf'' size 500K
RMAN>
-------------------------------------------------------------
PRONTO BANCO DE DADOS RECUPERADO COM SUCESSO.
-------------------------------------------------------------
=================================================
8- Passo: Executar o Export apenas do usuario(schema)HR
=================================================
[oracle@db10g installoracle]$ exp userid =\"sys/teste@aux1 as sysdba\"
file=hr.dmp log=hr.log owner=hr
Export: Release 10.2.0.4.0 - Production on Tue Aug 18 03:08:20 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table COUNTRIES 25 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table DEPARTMENTS 27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMPLOYEES 107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table JOBS 19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table JOB_HISTORY 10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table LOCATIONS 23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table REGIONS 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@db10g installoracle]$
=================================================
9- Passo: Criar o Usuario HR no Banco de Dados Produção novamente
=================================================
[oracle@db10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 18 03:11:20 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> create user HR identified by hr
default tablespace Example
temporary tablespace TEMP;
User created.
SQL> grant resource to HR;
Grant succeeded.
SQL> grant connect to HR;
Grant succeeded.
=================================================
10- Passo: Executar o Import do usuario(schema)HR
=================================================
[oracle@db10g installoracle]$ imp userid =\"sys/oracle@db10g as sysdba\"
file=hr.dmp log=hr.log FROMUSER=hr TOUSER=hr
Import: Release 10.2.0.4.0 - Production on Tue Aug 18 03:18:41 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing HR's objects into HR
. . importing table "COUNTRIES" 25 rows imported
. . importing table "DEPARTMENTS" 27 rows imported
. . importing table "EMPLOYEES" 107 rows imported
. . importing table "JOBS" 19 rows imported
. . importing table "JOB_HISTORY" 10 rows imported
. . importing table "LOCATIONS" 23 rows imported
. . importing table "REGIONS" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
=================================================
PRONTO USUARIO(SCHEMA)HR RECUPERADO COM SUCESSO !!
=================================================Espero ter ajudado.
Abraço