Taking Online Oracle Database, tablespace and datafile backups

Backup & Recovery / 14/07/2022

Checando se o banco de dados está em ARCHIVELOG MODE:

[oracle@oel8 oracle]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 12 06:27:27 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archives
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
SQL>

Realizando o backup do banco de dados e archives em um mesmo comando:

[oracle@oel8 BACKUP]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Mar 12 06:34:37 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
 
 
Starting backup at 2021-03-12:06:34:52
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=1 STAMP=1036965361
input archived log thread=1 sequence=13 RECID=2 STAMP=1039373324
input archived log thread=1 sequence=14 RECID=3 STAMP=1039385578
input archived log thread=1 sequence=15 RECID=4 STAMP=1039730500
input archived log thread=1 sequence=16 RECID=5 STAMP=1039730621
input archived log thread=1 sequence=17 RECID=6 STAMP=1039730703
input archived log thread=1 sequence=18 RECID=7 STAMP=1041720392
input archived log thread=1 sequence=19 RECID=8 STAMP=1065592715
input archived log thread=1 sequence=20 RECID=9 STAMP=1065710266
input archived log thread=1 sequence=21 RECID=10 STAMP=1065714954
input archived log thread=1 sequence=22 RECID=11 STAMP=1065717962
input archived log thread=1 sequence=23 RECID=12 STAMP=1066831250
input archived log thread=1 sequence=24 RECID=13 STAMP=1066972921
input archived log thread=1 sequence=25 RECID=14 STAMP=1066977292
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:34:54
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:35:01
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_annnn_TAG20210312T063454_j4pffyqo_.bkp tag=TAG20210312T063454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2021-03-12:06:35:01
 
Starting backup at 2021-03-12:06:35:01
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=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:35:02
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:35:17
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T063502_j4pfg728_.bkp tag=TAG20210312T063502 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2021-03-12:06:35:17
 
Starting backup at 2021-03-12:06:35:17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=15 STAMP=1066977317
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:35:18
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:35:19
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_annnn_TAG20210312T063518_j4pfgp99_.bkp tag=TAG20210312T063518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:06:35:19
 
Starting Control File and SPFILE Autobackup at 2021-03-12:06:35:19
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066977319_j4pfgrcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:06:35:22
 
RMAN>

Para o próximo teste, onde vamos criar um backup em um diretório fora da FRA, vou criar uma pasta na máquina que abrigará os backup pieces:

[oracle@oel8 oracle]$ cd /oracle/
[oracle@oel8 oracle]$ mkdir BACKUP
[oracle@oel8 oracle]$ chmod 775 BACKUP/

Utilizando o comando FORMAT:

RMAN> BACKUP DATABASE FORMAT '/oracle/BACKUP/RMANDB%U.bkp';
 
Starting backup at 2021-03-12:06:36:46
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=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:36:47
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:37:22
piece handle=/oracle/BACKUP/RMANDB0cvphi3v_1_1.bkp tag=TAG20210312T063647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2021-03-12:06:37:22
 
Starting Control File and SPFILE Autobackup at 2021-03-12:06:37:22
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066977442_j4pflml8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:06:37:25
 
RMAN> host 'ls -lthr';
 
total 1.2G
-rw-r-----. 1 oracle oinstall 1.2G Mar 12 06:36 RMANDB0cvphi3v_1_1.bkp
host command complete
 
RMAN>

Realizando backup de uma tablespace específica:

[oracle@oel8 BACKUP]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 12 06:38:54 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
 
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TS_CORTEX_CATALOG
 
6 rows selected.
 
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@oel8 BACKUP]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Mar 12 06:39:14 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> BACKUP TABLESPACE TS_CORTEX_CATALOG FORMAT '/oracle/BACKUP/tablespace_U%.bkp';
 
Starting backup at 2021-03-12:06:40:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:40:34
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:40:35
piece handle=/oracle/BACKUP/tablespace_U%.bkp tag=TAG20210312T064034 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:06:40:35
 
Starting Control File and SPFILE Autobackup at 2021-03-12:06:40:36
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066977636_j4pfro5d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:06:40:39
 
RMAN>

Consultando backupset da tablespace:

RMAN> LIST BACKUPSET OF TABLESPACE TS_CORTEX_CATALOG;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8       Full    1.15G      DISK        00:00:10     2021-03-12:06:35:12
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20210312T063502
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T063502_j4pfg728_.bkp
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1805856    2021-03-12:06:35:02              NO    /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
11      Full    1.15G      DISK        00:00:04     2021-03-12:06:36:51
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20210312T063647
        Piece Name: /oracle/BACKUP/RMANDB0cvphi3v_1_1.bkp
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1805937    2021-03-12:06:36:47              NO    /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
13      Full    5.73M      DISK        00:00:00     2021-03-12:06:40:34
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20210312T064034
        Piece Name: /oracle/BACKUP/tablespace_U%.bkp
  List of Datafiles in backup set 13
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1806064    2021-03-12:06:40:34              NO    /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
 
RMAN>

Realizando backup de mais de uma tablespace ao mesmo tempo:

RMAN> BACKUP TABLESPACE TS_CORTEX_CATALOG,USERS;
 
Starting backup at 2021-03-12:06:42:56
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=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:06:42:56
channel ORA_DISK_1: finished piece 1 at 2021-03-12:06:42:57
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T064256_j4pfx0x1_.bkp tag=TAG20210312T064256 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:06:42:57
 
Starting Control File and SPFILE Autobackup at 2021-03-12:06:42:57
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066977778_j4pfx2od_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:06:42:59
 
RMAN>

Realizando backup de um datafile usando o seu número:

RMAN> BACKUP DATAFILE 5;
 
Starting backup at 2021-03-12:08:41:09
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=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:08:41:09
channel ORA_DISK_1: finished piece 1 at 2021-03-12:08:41:10
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084109_j4pntow6_.bkp tag=TAG20210312T084109 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:08:41:10
 
Starting Control File and SPFILE Autobackup at 2021-03-12:08:41:11
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066984871_j4pntqx8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:08:41:12
 
RMAN>

Pegando informações do backupset:

RMAN> LIST BACKUP OF DATAFILE 5;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
17      Full    5.73M      DISK        00:00:00     2021-03-12:08:41:09
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20210312T084109
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084109_j4pntow6_.bkp
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1810142    2021-03-12:08:41:09              NO    /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
 
RMAN>

Realizando backup do datafile pelo seu nome (com diretório absoluto) e fazendo a consulta do backupset:

RMAN> BACKUP DATAFILE '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf';
 
Starting backup at 2021-03-12:08:44:35
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=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:08:44:36
channel ORA_DISK_1: finished piece 1 at 2021-03-12:08:44:37
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084435_j4po149t_.bkp tag=TAG20210312T084435 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:08:44:37
 
Starting Control File and SPFILE Autobackup at 2021-03-12:08:44:37
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985077_j4po15z4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:08:44:38
RMAN> LIST BACKUP OF DATAFILE '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf';
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
21      Full    1.05M      DISK        00:00:00     2021-03-12:08:44:36
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20210312T084435
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084435_j4po149t_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  7       Full 1810319    2021-03-12:08:44:36              NO    /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf

Realizando backup de mais de um datafile ao mesmo tempo:

RMAN> BACKUP DATAFILE 5,7;
 
Starting backup at 2021-03-12:08:46:26
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=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-12:08:46:26
channel ORA_DISK_1: finished piece 1 at 2021-03-12:08:46:27
piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084626_j4po4lsp_.bkp tag=TAG20210312T084626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-12:08:46:27
 
Starting Control File and SPFILE Autobackup at 2021-03-12:08:46:27
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985188_j4po4nns_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-12:08:46:29

Deletando todos os backupsets:

RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
21      21      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084435_j4po149t_.bkp
22      22      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985077_j4po15z4_.bkp
23      23      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084626_j4po4lsp_.bkp
24      24      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985188_j4po4nns_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084435_j4po149t_.bkp RECID=21 STAMP=1066985076
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985077_j4po15z4_.bkp RECID=22 STAMP=1066985077
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_12/o1_mf_nnndf_TAG20210312T084626_j4po4lsp_.bkp RECID=23 STAMP=1066985186
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_12/o1_mf_s_1066985188_j4po4nns_.bkp RECID=24 STAMP=1066985188
Deleted 4 objects

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish