Oracle 12cR1 PDB Point-in-time-recovery
Definições:
CDB: Container Database
PDB: Pluggable Database
DBPITR ou Database point-in-time-recovery restaura o banco de dados de um backup efetuado anteriormente. DBPITR é também conhecido como incomplete recovery pois não aplica todos os registros de redo disponíveis, ou seja, intencionalmente não restaura ou aplica todas as alterações que foram feitas após o ultimo backup. Somente uma parte dos registros de redo são aplicadas até um determinado ponto no tempo.
Devido ao fato de podermos informar o ponto no tempo específico para o qual queremos voltar o banco de dados, este procedimento é também, por vezes, chamado de time-based recovery.
Existem situações nas quais DBPITR é a única maneira de recuperar um banco de dados devido a perda de archived logs após um determinado ponto no tempo. Este tutorial mostra os procedimentos para se efetuar um DBPITR em uma PDB do ponto de vista intencioal, ou seja, queremos voltar o nosso banco de dados no tempo e não tendo a intenção de aplicar todos os registros de redo disponíveis.
Ambiente
O banco de dados PROD.xps15z.com é o CDB ou banco de dados root container.
O banco de dados PROD_PDB1.xps15z.com é uma PDB ou pluggable database que pretence ao container PROD.
Este tutorial fará uso do Oracle Recovery Manager (RMAN) para efetuar o DBPITR.
Faremos uso de uma Fast Recovery Area (FRA).
Todos os arquivos dos bancos de dados e instância estão armazenados em Oracle Automatic Storage Management (ASM).
[oracle@12c tmp]$ uname -r
2.6.39-200.24.1.el6uek.x86_64
[oracle@12c tmp]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 2 12:00:37 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4815M
[oracle@12c 12.1.0]$ . oraenv
ORACLE_SID = [prod] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@12c 12.1.0]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 10233 5206 0 5206 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 10228 9896 0 9896 0 N FRA/Procedimento
A view dinâmica V$PDB contém informações sobre todas as Pluggable Databases.
Como as PDB’s não possuem uma instance, não podemos checar sua disponibilidade via SO.
Consultamos a V$PDB para verificar a disponibilidade da Pluggable Database PROD_PDB1
[oracle@12c 12.1.0]$ . oraenv
ORACLE_SID = [+ASM] ? prod
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@12c 12.1.0]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 2 12:10:40 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select con_id, dbid, name, open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------------- ----------
2 4063732729 PDB$SEED READ ONLY
3 2300078418 PROD_PDB1 READ WRITEConectando na CDB diretamente via SQL*Plus via easy connect ou string de conexão:
[oracle@12c 12.1.0]$ sqlplus sys/oracle@localhost:1521/prod_pdb1.xps15z.com as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 2 12:13:18 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOGProsseguimos com a criação de algumas tabelas/dados de teste
SQL> create table ddameda.test_dbpitr (data date);
Table created.
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS' ;
Session altered.
SQL> insert into ddameda.test_dbpitr values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ddameda.test_dbpitr;
DATA
---------
02-NOV-13Neste ponto, faremos um backup da CDB, ou seja, faremos backups de todos os arquivos de bancos de dados de todos os containers incluindo o container root (CDB), seed database (PDB) e prod_pdb1 (PDB). Note que conectamos o RMAN ao root container e não a PDB via TNS service.
Caso ja possua um backup prévio do root container (CDB), pode-se fazer um backup somente do PDB. Fazer um backup somente do PDB a ser restaurado sem que haja um backup prévio dos arquivos pertencentes ao root container, irá resultar em erro na hora do restore/recover.
[oracle@12c 12.1.0]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Nov 2 12:33:54 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=232950009)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbs/snapcf_prod.f'; # default
RMAN> backup as compressed backupset database plus archivelog;
Starting backup at 02-NOV-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=102 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=204 RECID=1 STAMP=830362577
input archived log thread=1 sequence=205 RECID=2 STAMP=830366545
input archived log thread=1 sequence=206 RECID=3 STAMP=830366851
input archived log thread=1 sequence=207 RECID=4 STAMP=830367928
input archived log thread=1 sequence=208 RECID=5 STAMP=830368088
input archived log thread=1 sequence=209 RECID=6 STAMP=830431065
input archived log thread=1 sequence=210 RECID=7 STAMP=830431235
input archived log thread=1 sequence=211 RECID=8 STAMP=830435654
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=+FRA/PROD/BACKUPSET/2013_11_02/annnf0_tag20131102t123415_0.266.830435657 tag=TAG20131102T123415 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 02-NOV-13
Starting backup at 02-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/PROD/DATAFILE/sysaux.259.821111689
input datafile file number=00005 name=+DATA/PROD/DATAFILE/undotbs1.257.821111701
input datafile file number=00001 name=+DATA/PROD/DATAFILE/system.263.821111667
input datafile file number=00006 name=+DATA/PROD/DATAFILE/users.268.821111753
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=+FRA/PROD/BACKUPSET/2013_11_02/nnndf0_tag20131102t123431_0.261.830435671 tag=TAG20131102T123431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/sysaux.271.830433955
input datafile file number=00011 name=+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/system.273.830433939
input datafile file number=00013 name=+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/users.272.830434023
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=+FRA/PROD/EA31804FCE032FD2E043FA00A8C04398/BACKUPSET/2013_11_02/nnndf0_tag20131102t123431_0.276.830435747 tag=TAG20131102T123431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/PROD/E1CA44DF2E82200DE043FA00A8C0FF58/DATAFILE/sysaux.260.821111695
input datafile file number=00002 name=+DATA/PROD/E1CA44DF2E82200DE043FA00A8C0FF58/DATAFILE/system.262.821111677
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=+FRA/PROD/E1CA44DF2E82200DE043FA00A8C0FF58/BACKUPSET/2013_11_02/nnndf0_tag20131102t123431_0.275.830435793 tag=TAG20131102T123431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 02-NOV-13
Starting backup at 02-NOV-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=212 RECID=9 STAMP=830435839
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=+FRA/PROD/BACKUPSET/2013_11_02/annnf0_tag20131102t123719_0.272.830435841 tag=TAG20131102T123719 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-NOV-13
Starting Control File and SPFILE Autobackup at 02-NOV-13
piece handle=+FRA/PROD/AUTOBACKUP/2013_11_02/s_830435841.273.830435843 comment=NONE
Finished Control File and SPFILE Autobackup at 02-NOV-13
Com o backup concluído, iremos inserir mais um registro na tabela com a data e hora atual.
[oracle@12c tmp]$ sqlplus sys/oracle@localhost:1521/prod_pdb1.xps15z.com as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 1 15:51:12 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
[oracle@12c 12.1.0]$ sqlplus sys/oracle@localhost:1521/prod_pdb1.xps15z.com as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 2 12:39:26 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> insert into ddameda.test_dbpitr values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from ddameda.test_dbpitr;
DATA
-------------------
02/11/2013 12:24:15
02/11/2013 12:39:47Prosseguimos com o restore e recovery da PDB para um ponto no tempo antes da inserção da seguinda linha na tabela TEST_DBPITR.
Assim como bancos de dados convencionais não Multi-Tenant, é necessário que o banco esteja fechado para que se possa efetuar um DBPITR.
Para fazer o restore e recover da PDB, conectamos o RMAN na CDB como target
[oracle@12c 12.1.0]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Nov 2 12:41:28 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=232950009)
RMAN> alter pluggable database prod_pdb1 close;
using target database control file instead of recovery catalog
Statement processedAgora rodamos o run block que fará o restore e o point-in-time-recovery. Verifique que o UNTIL TIME é anterior a data inserida na segunda linha da tabela TEST_DBPITR. Ou seja, a PDB deveria ser restaurada para um ponto no tempo anterior a inserção da segunda linha na tabela TEST_DBPITR.
[oracle@12c 12.1.0]$ rman
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Nov 2 12:57:18 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target "sys as sysdba"
target database Password:
connected to target database: PROD (DBID=232950009)
RMAN> run {
2> set until time "to_date('02/11/2013 12:39:00','DD/MM/YYYY HH24:MI:SS')";
3> restore pluggable database PROD_PDB1;
4> recover pluggable database PROD_PDB1;
5> }Pode-se verificar no output do DBPITR os seguintes estágios:
- Faz o restore dos datafiles necessários. Estes, correspondem aos datafiles da PDB que é alvo de nosso DBPITR.
executing command: SET until clause
Starting restore at 02-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to +DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/system.273.830433939
channel ORA_DISK_1: restoring datafile 00012 to +DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/sysaux.271.830433955
channel ORA_DISK_1: restoring datafile 00013 to +DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/users.272.830434023
channel ORA_DISK_1: reading from backup piece +FRA/PROD/EA31804FCE032FD2E043FA00A8C04398/BACKUPSET/2013_11_02/nnndf0_tag20131102t123431_0.276.830435747
channel ORA_DISK_1: piece handle=+FRA/PROD/EA31804FCE032FD2E043FA00A8C04398/BACKUPSET/2013_11_02/nnndf0_tag20131102t123431_0.276.830435747 tag=TAG20131102T123431
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 02-NOV-13- Cria uma instância auxiliar automaticamente e da um nome randômico, em nosso caso foi nomeada de “sFqn”. Note que é necessário ter tanto RAM quanto espaço na FRA disponíveis em seu sistema para que o procedimento de criação da instância automatica e restore de datafiles de suporte a mesma funcione corretamente.
Creating automatic instance, with SID='sFqn'
initialization parameters used for automatic instance:
db_name=PROD
db_unique_name=sFqn_pitr_PROD_PDB1_PROD
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
#No auxiliary destination in use
enable_pluggable_database=true
_clone_one_pdb_recovery=true
control_files=+FRA/PROD/DATAFILE/current.278.830437207
#No auxiliary parameter file used
starting up automatic instance PROD
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- Restaura o control file diretamente para a FRA, não sobrepondo o control file da CDB.
contents of Memory Script:
{
# set requested point in time
set until time "to_date('02/11/2013 12:39:00','DD/MM/YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 02-NOV-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=27 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 +FRA/PROD/AUTOBACKUP/2013_11_02/s_830435841.273.830435843
channel ORA_AUX_DISK_1: piece handle=+FRA/PROD/AUTOBACKUP/2013_11_02/s_830435841.273.830435843 tag=TAG20131102T123721
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+FRA/PROD/DATAFILE/current.278.830437207
Finished restore at 02-NOV-13- Monta a instância automática e aponta seus datafiles para os arquivos que foram restaurados no item 1. Também restaura cópias dos datafiles relativos a CDB diretamente para a FRA. Desse modo, tem-se um banco de dados tradicional não multitenant que é acessado por meia da instância automática.
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('02/11/2013 12:39:00','DD/MM/YYYY HH24:MI:SS')";
# switch to valid datafilecopies
switch clone datafile 11 to datafilecopy
"+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/system.273.830433939";
switch clone datafile 12 to datafilecopy
"+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/sysaux.271.830433955";
switch clone datafile 13 to datafilecopy
"+DATA/PROD/EA31804FCE032FD2E043FA00A8C04398/DATAFILE/users.272.830434023";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 1 to
"+FRA/PROD/DATAFILE/system.279.830437259";
set newname for datafile 5 to
"+FRA/PROD/DATAFILE/undotbs1.280.830437261";
set newname for datafile 3 to
"+FRA/PROD/DATAFILE/sysaux.281.830437263";
set newname for datafile 6 to
"+FRA/PROD/DATAFILE/users.282.830437279";
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 6;
switch clone datafile all;
}- Restaura os archived logs necessários para a execução do DBPITR, faz o recover, exclui os datafiles de auxílio à instância automática. Após, remove a instância automática e abre a PDB com resetlogs.
Starting recover at 02-NOV-13
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 212 is already on disk as file +FRA/PROD/ARCHIVELOG/2013_11_02/thread_1_seq_212.274.830435837
archived log for thread 1 with sequence 213 is already on disk as file +FRA/PROD/ARCHIVELOG/2013_11_02/thread_1_seq_213.277.830437205
archived log file name=+FRA/PROD/ARCHIVELOG/2013_11_02/thread_1_seq_212.274.830435837 thread=1 sequence=212
archived log file name=+FRA/PROD/ARCHIVELOG/2013_11_02/thread_1_seq_213.277.830437205 thread=1 sequence=213
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-NOV-13
sql statement: alter database open read only
Oracle instance shut down
Removing automatic instance
Automatic instance removed
auxiliary instance file +FRA/PROD/DATAFILE/sysaux.281.830437263 deleted
auxiliary instance file +FRA/PROD/DATAFILE/current.278.830437207 deleted
Finished recover at 02-NOV-13
RMAN> alter pluggable database PROD_PDB1 open resetlogs;
Statement processedNeste ponto, fazemos a checagem de nossa PDB. O objetivo é observar somente um registro inserido em nossa tabela de teste.
oracle@12c 12.1.0]$ sqlplus sys/oracle@localhost:1521/prod_pdb1.xps15z.com as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 2 13:35:26 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select * from ddameda.test_dbpitr;
DATA
-------------------
02/11/2013 12:24:15Como a CDB foi aberta com resetlogs, pode-se verificar a nova incarnação de nossa PDB como mostrado na view v$pdb_incarnation.
SQL> select status, pdb_incarnation#, incarnation_time from v$pdb_incarnation;
STATUS PDB_INCARNATION# INCARNATION_TIME
------- ---------------- -------------------
CURRENT 1 02/11/2013 12:39:26
PARENT 0 18/07/2013 14:34:33Cabe lembrar que durante o processo de DBPITR emu ma PDB, todas as demais PDB’s bem como o container database root (CDB) não tem sua disponibilidade afetada e continuam “abertas” em modo read-write.
Até a próxima