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 WRITE

Conectando 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
------------
ARCHIVELOG

Prosseguimos 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-13

Neste 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:47

Prosseguimos 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 processed

Agora 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:

  1. 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
  1. 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
  1. 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
  1. 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;
}
  1. 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 processed

Neste 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:15

Como 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:33

Cabe 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

 

Deixe um comentário

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detectado !

Verificamos que está usando alguma extensão para bloquear os anúncios. O GPO (Grupo de Profissionais Oracle) obtém a sua renda através dos anúncios, para assim manter toda a estrutura dedicada a universalização do conhecimento.

Se você gosta de nosso trabalho, pedimos por gentileza que desabilite o ads blocker. Trabalhamos somente com o Google Adsense e tentamos ao máximo exibir apenas o necessário.

Agradecemos de antemão ! :)

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock