0 Flares Twitter 0 Facebook 0 Filament.io 0 Flares ×

Fala pessoal, beleza?

Continuando os posts sobre backup, vamos hoje fazer o restore de apenas um PDB. Vai ser bem curtinho o post hoje.

Se você não leu o post anterior, está aqui se quiser. Vamos brincar de backup e restore – Parte 1

Só lembrando que:

1) Vou demonstrar como se faz o backup de um PDB isolado.
2) Para o restore você pode usar esse backup ou então o backup FULL que você tem disponível (CDB + PDB).
3) Novamente frisando que os scripts podem e devem ser melhorados, eles apenas são usados para fins didáticos.
4) Use por sua conta e risco. 🙂

Dito isso, vamos fazer o restore.

1) Script de backup

run {
SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
SQL ‘ALTER SYSTEM CHECKPOINT’;
ALLOCATE CHANNEL d1 TYPE DISK MAXPIECESIZE 32G;
ALLOCATE CHANNEL d2 TYPE DISK MAXPIECESIZE 32G;
ALLOCATE CHANNEL d3 TYPE DISK MAXPIECESIZE 32G;
ALLOCATE CHANNEL d4 TYPE DISK MAXPIECESIZE 32G;
BACKUP AS COMPRESSED BACKUPSET pluggable DATABASE thorpdb3 plus archivelog;
}

Importante: Novamente frisando que se você faz um backup full de seu ambiente, não é necessário fazer o backup de apenas um PDB.

 

2) Baixe o PDB que será restaurado.
SQL> show pdbs

CON_ID  CON_NAME            OPEN MODE     RESTRICTED
——      ——————     ———-           ———-
2      PDB$SEED              READ ONLY       NO
3      THORPDB1             READ WRITE      NO
4      THORPDB2             READ WRITE      NO
5      THORPDB3             READ WRITE      NO
6      THORPDB5             READ WRITE      NO
7      THORPDB4             READ WRITE      NO

 

3) Criar uma tabela para testes e depois dropar a mesma no THORPDB2.

SQL> alter session set container=thorpdb4;
Session altered.

SQL> create table teste3 as select * from dba_objects;
Table created.

SQL> select sysdate from dual;
SYSDATE
——————-
29/01/2018 10:40:29

 

4) Vou fazer o backup de archives agora. Nem seria necessário já que os archives ainda estão em disco, mas é só para marcar um ponto no tempo aqui.

run {
SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
SQL ‘ALTER SYSTEM CHECKPOINT’;
BACKUP
AS COMPRESSED BACKUPSET
ARCHIVELOG ALL NOT BACKED UP 3 TIMES
CURRENT CONTROLFILE;
}

 

5) Vou dropar a tabela agora.

SQL> select sysdate from dual;
SYSDATE
——————-
29/01/2018 10:52:05

SQL> drop table teste3;
Table dropped.

SQL> select count(1) from teste3;
ORA-00942: table or view does not exist

 

6) Vamos restaurar para um ponto antes do drop da tabela, no momento de sua criação por exemplo.

RUN {
set until time “to_date(‘2018-01-29 10:41:00′,’YYYY-MM-DD HH24:MI:SS’)”;
ALTER PLUGGABLE DATABASE thorpdb4 CLOSE;
RESTORE PLUGGABLE DATABASE thorpdb4;
RECOVER PLUGGABLE DATABASE thorpdb4;
ALTER PLUGGABLE DATABASE thorpdb4 OPEN RESETLOGS;
}

Logs:
executing command: SET until clause
using target database control file instead of recovery catalog
Statement processed

Starting restore at 29-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK
^[[Ballocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=78 device type=DISK
^[[B^[[B^[[B^[[Ballocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=92 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 00022 to +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/sysaux.299.965485011
channel ORA_DISK_1: reading from backup piece +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.535.966438809
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00021 to +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/system.298.965485011
channel ORA_DISK_2: reading from backup piece +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.539.966438973
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00023 to +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/undotbs1.297.965485011
channel ORA_DISK_3: reading from backup piece +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.548.966439033
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00024 to +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/DATAFILE/users.296.965485011
channel ORA_DISK_4: reading from backup piece +DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.556.966439041
channel ORA_DISK_3: piece handle=+DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.548.966439033 tag=TAG20180126T151035
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:02
channel ORA_DISK_4: piece handle=+DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.556.966439041 tag=TAG20180126T151035
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=+DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.539.966438973 tag=TAG20180126T151035
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: piece handle=+DATA/THOR/62D3EADA70FC2F9FE0530238A8C0F70D/BACKUPSET/2018_01_26/nnndf0_tag20180126t151035_0.535.966438809 tag=TAG20180126T151035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 29-JAN-18

Starting recover at 29-JAN-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

starting media recovery

archived log for thread 1 with sequence 54 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_54.560.966439051
archived log for thread 1 with sequence 55 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_55.562.966439053
archived log for thread 1 with sequence 56 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_56.459.966441189
archived log for thread 1 with sequence 57 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_57.454.966441189
archived log for thread 1 with sequence 58 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_58.467.966441189
archived log for thread 1 with sequence 1 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_1.309.966441199
archived log for thread 1 with sequence 2 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_2.310.966441203
archived log for thread 1 with sequence 3 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_26/thread_1_seq_3.448.966441209
archived log for thread 1 with sequence 4 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_4.445.966680051
archived log for thread 1 with sequence 5 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_5.442.966680055
archived log for thread 1 with sequence 6 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_6.437.966680059
archived log for thread 1 with sequence 7 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_7.431.966680223
archived log for thread 1 with sequence 8 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_8.410.966680241
archived log for thread 1 with sequence 9 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_9.392.966680553
archived log for thread 1 with sequence 10 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_10.388.966680553
archived log for thread 1 with sequence 11 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_11.570.966680603
archived log for thread 1 with sequence 12 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_12.574.966680629
archived log for thread 1 with sequence 13 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_13.576.966680633
archived log for thread 1 with sequence 14 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_14.586.966680693
archived log for thread 1 with sequence 15 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_15.590.966681465
archived log for thread 1 with sequence 16 is already on disk as file +DATA/THOR/ARCHIVELOG/2018_01_29/thread_1_seq_16.592.966681741
media recovery complete, elapsed time: 00:00:02
Finished recover at 29-JAN-18

Statement processed

 

7) Vamos confirmar esse restore, se deu certo.

SQL> alter session set container=thorpdb4;
Session altered.

select sysdate from dual;
SYSDATE
——————-
29/01/2018 10:55:14

select count(1) from teste3;
COUNT(1)
———-
72712

SQL> show pdbs

CON_ID  CON_NAME            OPEN MODE     RESTRICTED
——      ——————     ———-           ———-
2      PDB$SEED              READ ONLY       NO
3      THORPDB1             READ WRITE      NO
4      THORPDB2             READ WRITE      NO
5      THORPDB3             READ WRITE      NO
6      THORPDB5             READ WRITE      NO
7      THORPDB4             READ WRITE      NO

 

Pode conferir também fazendo um SELECT na V$PDB_INCARNATION. Algo assim, por exemplo:

select
enc.CON_ID,
substr(pdb.name,1,9) as NAME,
enc.DB_INCARNATION#,
enc.PDB_INCARNATION#,
enc.STATUS,
enc.INCARNATION_SCN,
enc.INCARNATION_TIME,
enc.BEGIN_RESETLOGS_SCN,
enc.BEGIN_RESETLOGS_TIME
from v$pdb_incarnation enc
JOIN v$pdbs pdb ON pdb.con_id = enc.con_id
order by pdb.con_id, enc.PDB_INCARNATION#;

Então é isso, na próxima veremos como fazer um restore do PDB via “RESTORE POINT”.

 

Abraço

Mario

2 thoughts on “Vamos brincar de Backup e Restore? – Parte 2

  1. Olá Mario.
    Muito bom!! Apenas fiquei como uma duvida, esse exemplos (script) aplica-se no 11G ou apenas no 12C

    Obrigado
    []

    1. Bom dia Gustavo.

      Esses casos dos PDB’s somente no 12c (algumas situações somente no 12.2.0.1). Já o script de backup e restore full do primeiro artigo provavelmente rodam no 11g sim.

      Abraço

Leave a Reply

Your email address will not be published. Required fields are marked *