Backup e Restore per Container e PDB

-

 Utilizziamo il tool RMAN che interroga il catalog file,

 Assicurarsi che i container e i pdb sono up e running.

Ricordarsi di 
- abilitare l'archivelog mode
- abilitare la fast recovery area se si vuole effettuare il backup classico.


[oracle@test ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Dec 7 14:08:22 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1577757327)

oppure

[oracle@test ~]$ rman target =/
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Dec 7 14:08:30 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1577757327)

Individuiamo la presenza di backup.

RMAN> list backup of database;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

Individuiamo la presenza di archivelog.

RMAN> list archivelog all;

specification does not match any archived log in the repository

Questo vuol dire che non c'è stato mai uno switch dei redolog e quindi non c'è ancora un redo log file pieno.


Backup del db + archivelog

RMAN> backup database plus archivelog;

Questo comando effettua il backup dei datafile, redolog, control file, spfile e archivelog sotto la FRA.
Questo comando effettua lo switch dei redolog quindi anche se prima del backup non esistevano gli archivelog, ora sono stati generati.

[oracle@test ORCL]$ pwd
/u01/app/oracle/fast_recovery_area/ORCL
[oracle@test ORCL]$ ls -lrt
total 0
drwxr-x--- 3 oracle oinstall 24 Dec  7 12:22 autobackup
drwxr-x--- 3 oracle oinstall 24 Dec  7 14:34 archivelog
drwxr-x--- 3 oracle oinstall 24 Dec  7 14:34 backupset

Verifiche post backup:

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    2.56G      DISK        00:00:18     07-DEC-20
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T143425
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_07/o1_mf_nnndf_TAG20201207T143425_hwwcv1yl_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 12882009   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/system01.dbf
  3       Full 12882009   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/sysaux01.dbf
  4       Full 12882009   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/undotbs01.dbf
  83      Full 12882009   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/users02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    776.48M    DISK        00:00:09     07-DEC-20
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T143425
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/AE3ED9EC84541FB8E053C58E670AB441/backupset/2020_12_07/o1_mf_nnndf_TAG20201207T143425_hwwcvv28_.bkp
  List of Datafiles in backup set 5
  Container ID: 3, PDB Name: ORCLPDB
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9       Full 12882023   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
  10      Full 12882023   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
  11      Full 12882023   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
  12      Full 12882023   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
  84      Full 12882023   07-DEC-20              NO    /u01/app/oracle/oradata/ORCL/orclpdb/users02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    500.95M    DISK        00:00:02     07-DEC-20
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T143425
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/AE3EBEE00B1218ACE053C58E670AC325/backupset/2020_12_07/o1_mf_nnndf_TAG20201207T143425_hwwcwb4c_.bkp
  List of Datafiles in backup set 6
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  5       Full 7299980    14-OCT-20              NO    /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
  6       Full 7299980    14-OCT-20              NO    /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
  8       Full 7299980    14-OCT-20              NO    /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf


In questo caso il nostro backup con tag TAG20201207T143425 ha 3 backup set (BS). Il primo contiene il backup dei datafile del contianer mentre gli altri due dei pdb.

Il backup set del container è la directory
/u01/app/oracle/fast_recovery_area/ORCL/backupset

Il backup set dei pdb è sotto la direcotry
/u01/app/oracle/fast_recovery_area/ORCL/AE3EBEE00B1218ACE053C58E670AC325/backupset
e
/u01/app/oracle/fast_recovery_area/ORCL/AE3ED9EC84541FB8E053C58E670AB441/backupset


RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
4       1    343     A 07-DEC-20
        Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_07/o1_mf_1_343_hwwctztx_.arc

5       1    344     A 07-DEC-20
        Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_07/o1_mf_1_344_hwwcwk6t_.arc


Verifica backup del Control file

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 17.95M DISK 00:00:00 07-DEC-20
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20201207T122259
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058530979_hww44mdf_.bkp
Control File Included: Ckp SCN: 12875566 Ckp time: 07-DEC-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.95M DISK 00:00:00 07-DEC-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201207T133817
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058535497_hww8ks4w_.bkp
Control File Included: Ckp SCN: 12879575 Ckp time: 07-DEC-20


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 17.95M DISK 00:00:00 07-DEC-20
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20201207T143514
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058538914_hwwcwlqt_.bkp
Control File Included: Ckp SCN: 12882049 Ckp time: 07-DEC-20

Sono stati creasti 3 backup set con tag differenti per il control file.


Verifica backup di Spfile

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    17.95M     DISK        00:00:00     07-DEC-20
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T122259
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058530979_hww44mdf_.bkp
  SPFILE Included: Modification time: 07-DEC-20
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    17.95M     DISK        00:00:00     07-DEC-20
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T133817
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058535497_hww8ks4w_.bkp
  SPFILE Included: Modification time: 07-DEC-20
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    17.95M     DISK        00:00:00     07-DEC-20
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20201207T143514
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_07/o1_mf_s_1058538914_hwwcwlqt_.bkp
  SPFILE Included: Modification time: 07-DEC-20
  SPFILE db_unique_name: ORCL

Sono stati creati 3 backup set con tag differenti per spfile.
Il contol file ed spfile vengono copiati all'interno della stesa direcotry "autobackup" nello stesso file, uno per il container e uno per ogni pdb.

Creiamo una tabella di test sotto un pdb e poi effuttuiamo il restore.

SQL> alter session set container=ORCLPDB;
SQL> create table hr.appo (x number);
SQL> insert into hr.appo values (1);

Proviamo ad effettuare il restore del db, ricordando che il backup fatto precedentemente non contiene la tabella hr.appo

RMAN> RUN { 
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

Statement processed

Se si accede al pdb si può verificare che la tabella hr.appo esiste ancora a dimostrazione che il RESTORE prende l'ultimo backup e legge gli archivelog generati dall'ultimo backup.  

SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select * from hr.appo;
no rows selected

Backup del ROOT Container

RMAN > backup database root;

RMAN > backup database root tag 'ROOT_DB_BACKUP';

tarting backup at 18-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-FEB-21
channel ORA_DISK_1: finished piece 1 at 18-FEB-21
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2021_02_18/o1_mf_nnndf_ROOT_DB_BACKUP_j2wlcwsb_.bkp tag=ROOT_DB_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-FEB-21

Starting Control File and SPFILE Autobackup at 18-FEB-21
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_02_18/o1_mf_s_1064837012_j2wld4fk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-FEB-21

Il backup scrive nella FRA nelle directory:
/u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/
/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/


Backup del PDB

RMAN> backup pluggable database PDB1 tag 'PDB1_BACKUP';

Starting backup at 18-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/tbs_data01.dbs
input datafile file number=00017 name=/u01/app/oracle/oradata/tbs_indx01.dbs
input datafile file number=00018 name=/u01/app/oracle/oradata/tbs_data02.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/orcl/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-FEB-21
channel ORA_DISK_1: finished piece 1 at 18-FEB-21
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/B9BBEBE636451CD1E053C68E670A3A69/backupset/2021_02_18/o1_mf_nnndf_PDB1_BACKUP_j2wodqkj_.bkp tag=PDB1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-FEB-21

Starting Control File and SPFILE Autobackup at 18-FEB-21
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_02_18/o1_mf_s_1064840107_j2wodv7p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-FEB-21

Il backup scrive nella FRA nelle directory:
/u01/app/oracle/fast_recovery_area/orcl/ORCL/B9BBEBE636451CD1E053C68E670A3A69/backupset/
/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_02_18/

Backup Database in un altra directory
Se vogliamo effettuare il backup in una direcorty diversa dalla FRA ad esempio sotto /home/oracle/backup lanciare il comando

RMAN> backup database format "/home/oracle/backup/backup_%U";     

Starting backup at 18-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
..............
channel ORA_DISK_1: finished piece 1 at 18-FEB-21
piece handle=/home/oracle/backup/backup_14vngdu3_1_1 tag=TAG20210218T134633 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-FEB-21

Starting Control File and SPFILE Autobackup at 18-FEB-21
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_02_18/o1_mf_s_1064843207_j2wrfqcc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-FEB-21

Oppure modificare il parametro di configurazione di RMAN

RMAN> configure channel device type disk format '/home/oracle/backup/backup_%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/backup/backup_%U';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
starting full resync of recovery catalog
full resync complete

Block Change Tracking 

BCT - migliora le performance del backup level 1 (differential e cumulative) perché registra i "changed blocks" di ogni datafile in un file accessibile da RMAN e quindi RMAN evita di analizzare ogni blocco dei data file.
Query di verifica se abilitato

sqlplus / as sysdba
SQL> select * from v$block_change_tracking;

STATUS   FILENAME       BYTES     CON_ID
---------- ------------------------------------------------
DISABLED

SQL> show parameter db_create_file_dest

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest      string

Creare la directory che contiene i file.
$ mkdir /u01/app/oracle/BTC

SQL> alter system set db_create_file_dest='/u01/app/oracle/BTC' scope=both;

SQL> show parameter db_create_file_dest

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest      string /u01/app/oracle/BCT

SQL> alter database enable block change tracking;

SQL> select * from v$block_change_tracking;

STATUS                           FILENAME                                                                  BYTES     CON_ID
---------- ------------------------------------------------------------------------------------------------------------
ENABLED  /u01/app/oracle/BCT/ORCL/changetracking/o1_mf_j2x1o21f_.chg   11599872     1


Per disabilitarlo:

SQL> alter database disable block change tracking;

Può essere riabilitato come segue:

SQL> alter database enable block change tracking using file '/home/oracle/backup/bct.f' reuse;

SQL> show parameter db_create_file_dest

NAME       TYPE VALUE
----------------- ----------- -------------------------
db_create_file_dest   string /u01/app/oracle/BCT

SQL> select * from v$block_change_tracking;

STATUS                           FILENAME                                BYTES     CON_ID
---------- ------------------------------------------------------------------------------------------------------------
ENABLED  /home/oracle/backup/bct.f    11599872     1


Post popolari in questo blog

ORA-12154: TNS: il listener non è attualmente a conoscenza del servizio richiesto nel descrittore di connessione

-
Richiesta: Problemi connessione oraSiSV9i.  Di seguito il tnsnames.ora.  SISV9I.dominio.IT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = oraSISV9I)(Port = 1521)) )(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SISV9I))) errore che ricevo dall'applicazione: ORA-12514: TNS: il listener non è attualmente a conoscenza del servizio richiesto nel descrittore di connessione 1) Da una macchina server verifico che il ping funziona tnsping oraSISV9i TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 24-JAN-2019 12:05:31 Copyright (c) 1997, 2009, Oracle.  All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used HOSTNAME adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP) (HOST=xx.yyyy.zzz.n)(PORT=1521))) OK (0 msec) 2) Provare da un client locale , inserendo nel tnsnam
Continua a leggere

Create e Drop Pluggable Database

-
Immagine
Creare un pluggable database dal SEED. Il nome del pluggable database non è case sensitive. Condizioni: - Accedere al conatiner root come amministratore o avere il privilegio "create pluggable database". - Il CDB deve essere in stato READ e WRITE. - Usare il comando "create pluggable database" - Come best practice creare un utente che abbia il ruolo DBA all'interno del nuovo pdb La procedura prevede che vengano effettuate copie dei datafile del Seed in una nuova directory e creati i tablespace Sysaux, System, Undo e Temp e gli schema e gli utenti di deafult. Accediamo come root container e assicuriamoci che sia in stato Read Write SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> column name format a15 SQL> select con_id,name,open_mode  from v$containers;       CON_ID NAME    OPEN_MODE ---------- --------------- ---------------------------- 1  CDB$ROOT    READ WRITE 2  PDB$SEED    READ ONLY 3  ORCLPDB            R
Continua a leggere