Taking Backup in Primary and Standby Databases using RMAN

Backup & Recovery, High Availability / 13/07/2022

Vamos simular a execução de backup de tablespace em um ambiente Data Guard, tanto do Primary quanto do seu Standby Database.

Validando configuração do Data Guard:

[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 04:48:52 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxPerformance
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 46 seconds ago)
 
DGMGRL> SHOW DATABASE CORTEX;
 
Database - cortex
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    cortex
 
Database Status:
SUCCESS
 
DGMGRL>

Standby:

[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 04:49:26 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> SHOW DATABASE CORTEXDR;
 
Database - cortexdr
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 19.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    CORTEXDR
 
Database Status:
SUCCESS
 
DGMGRL>

No ambiente que abriga o banco Primary, vou criar um diretório novo para o backup da tablespace:

[oracle@fornix1 oracle]$ cd /oracle/
[oracle@fornix1 oracle]$ mkdir BACKUP
[oracle@fornix1 oracle]$ cd BACKUP/
[oracle@fornix1 BACKUP]$ pwd
/oracle/BACKUP
[oracle@fornix1 BACKUP]$

Realizando backup da tablespace USERS do banco Primary (CORTEX):

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 04:52:02 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> BACKUP TABLESPACE USERS FORMAT '/oracle/BACKUP/users.bak';
 
Starting backup at 29-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 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=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: starting piece 1 at 29-JUN-21
channel ORA_DISK_1: finished piece 1 at 29-JUN-21
piece handle=/oracle/BACKUP/users.bak tag=TAG20210629T045255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUN-21
 
Starting Control File and SPFILE Autobackup at 29-JUN-21
piece handle=+DG_FRA/CORTEX/AUTOBACKUP/2021_06_29/s_1076475179.272.1076475181 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUN-21
 
RMAN>

Realizando o mesmo processo no banco STANDBY:

[oracle@fornix2 ~]$ cd /oracle/
[oracle@fornix2 oracle]$ mkdir BACKUP
[oracle@fornix2 oracle]$ cd BACKUP/
[oracle@fornix2 BACKUP]$ pwd
/oracle/BACKUP
[oracle@fornix2 BACKUP]$

Backup:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 04:54:24 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> BACKUP TABLESPACE USERS FORMAT '/oracle/BACKUP/users2.bak';
 
Starting backup at 29-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=86 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=00007 name=+DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
channel ORA_DISK_1: starting piece 1 at 29-JUN-21
channel ORA_DISK_1: finished piece 1 at 29-JUN-21
piece handle=/oracle/BACKUP/users2.bak tag=TAG20210629T045446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUN-21
 
Starting Control File and SPFILE Autobackup at 29-JUN-21
piece handle=+DG_FRA/CORTEXDR/AUTOBACKUP/2021_06_29/s_1076475239.275.1076475293 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUN-21
 
RMAN>

Utilizando as strings/alias criados NESTE artigo, podemos ver a diferença (do datafile name) exibido tanto no primary quanto standby pelo comando LIST:

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:01:15 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> LIST BACKUP OF TABLESPACE USERS for DB_UNIQUE_NAME CORTEX;
 
List of Backup Set for database with db_unique_name CORTEX
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
260     Full    8.36M      DISK        00:00:01     29-JUN-21
        BP Key: 263   Status: AVAILABLE  Compressed: NO  Tag: TAG20210629T045255
        Piece Name: /oracle/BACKUP/users.bak
  List of Datafiles in backup set 260
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 6336017    29-JUN-21              NO    +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
 
RMAN> LIST BACKUP OF TABLESPACE USERS for DB_UNIQUE_NAME CORTEXDR;
 
List of Backup Set for database with db_unique_name CORTEXDR
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
280     Full    8.62M      DISK        00:00:01     29-JUN-21
        BP Key: 283   Status: AVAILABLE  Compressed: NO  Tag: TAG20210629T045446
        Piece Name: /oracle/BACKUP/users2.bak
  List of Datafiles in backup set 280
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 6218684    14-JUN-21 6336125      NO    +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
 
RMAN>

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:02:01 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> LIST BACKUP OF TABLESPACE USERS for DB_UNIQUE_NAME CORTEX;
 
List of Backup Set for database with db_unique_name CORTEX
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
260     Full    8.36M      DISK        00:00:01     29-JUN-21
        BP Key: 263   Status: AVAILABLE  Compressed: NO  Tag: TAG20210629T045255
        Piece Name: /oracle/BACKUP/users.bak
  List of Datafiles in backup set 260
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 6336017    29-JUN-21              NO    +DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
 
RMAN> LIST BACKUP OF TABLESPACE USERS for DB_UNIQUE_NAME CORTEXDR;
 
List of Backup Set for database with db_unique_name CORTEXDR
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
280     Full    8.62M      DISK        00:00:01     29-JUN-21
        BP Key: 283   Status: AVAILABLE  Compressed: NO  Tag: TAG20210629T045446
        Piece Name: /oracle/BACKUP/users2.bak
  List of Datafiles in backup set 280
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 6218684    14-JUN-21 6336125      NO    +DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
 
RMAN>

Usando o REPORT SCHEMA, podemos ver a já esperada diferença do nome dos datafiles:

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:03:01 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> report schema for DB_UNIQUE_NAME CORTEX;
 
Report of database schema for database with db_unique_name CORTEX
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    920      SYSTEM               YES     +DG_DATA/CORTEX/DATAFILE/system.256.1039033445
3    610      SYSAUX               NO      +DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
4    340      UNDOTBS1             YES     +DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
5    888      SOE                  NO      +DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
7    12       USERS                NO      +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       +DG_DATA/CORTEX/TEMPFILE/temp.264.1039033669
 
RMAN> report schema for DB_UNIQUE_NAME CORTEXDR;
 
Report of database schema for database with db_unique_name CORTEXDR
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    920      SYSTEM               YES     +DG_DATA/CORTEXDR/DATAFILE/system.259.1066479769
3    610      SYSAUX               NO      +DG_DATA/CORTEXDR/DATAFILE/sysaux.257.1066479825
4    340      UNDOTBS1             YES     +DG_DATA/CORTEXDR/DATAFILE/undotbs1.256.1066479825
5    888      SOE                  NO      +DG_DATA/CORTEXDR/DATAFILE/soe.258.1066479771
7    12       USERS                NO      +DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       +DG_DATA/CORTEXDR/TEMPFILE/temp.268.1074148127
 
RMAN>

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:03:31 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> report schema for DB_UNIQUE_NAME CORTEX;
 
Report of database schema for database with db_unique_name CORTEX
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    920      SYSTEM               YES     +DG_DATA/CORTEX/DATAFILE/system.256.1039033445
3    610      SYSAUX               NO      +DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
4    340      UNDOTBS1             YES     +DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
5    888      SOE                  NO      +DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
7    12       USERS                NO      +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       +DG_DATA/CORTEX/TEMPFILE/temp.264.1039033669
 
RMAN> report schema for DB_UNIQUE_NAME CORTEXDR;
 
Report of database schema for database with db_unique_name CORTEXDR
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    920      SYSTEM               YES     +DG_DATA/CORTEXDR/DATAFILE/system.259.1066479769
3    610      SYSAUX               NO      +DG_DATA/CORTEXDR/DATAFILE/sysaux.257.1066479825
4    340      UNDOTBS1             YES     +DG_DATA/CORTEXDR/DATAFILE/undotbs1.256.1066479825
5    888      SOE                  NO      +DG_DATA/CORTEXDR/DATAFILE/soe.258.1066479771
7    12       USERS                NO      +DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       +DG_DATA/CORTEXDR/TEMPFILE/temp.268.1074148127
 
RMAN>

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