Sysbackup Administrative Privilege for Oracle Multitenant

Backup & Recovery, Multitenant / 13/07/2022

Na arquitetura Multitenant, também podemos usufruir do recurso de usuários (locais ou comuns) com privilégio de sysbackup, ou seja, que dentre vários privilégios, podem realizar as operações de backup/restore de um CDB ou PDBs. Neste artigo vamos realizar testes simples com este conceito.

Criando um usuário local com privilégio de sysbackup no PDB HIPOFISE1:

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 10 05:25:53 2021
Version 18.13.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.13.0.0.0
 
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
 
NAME      OPEN_MODE            CDB
--------- -------------------- ---
TALAMO    READ WRITE           YES
 
SQL> SELECT NAME FROM V$PDBS;
 
NAME
--------------------------------------------------------------------------------
PDB$SEED
HIPOFISE1
HIPOFISE2
SPFC
 
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> CREATE USER BSS1 IDENTIFIED BY oracle CONTAINER=CURRENT;
 
User created.
 
SQL> GRANT SYSBACKUP TO BSS1 CONTAINER=CURRENT;
 
Grant succeeded.

Como este usuário criado existe apenas no PDB HIPOFISE1, quando tentamos realizar a conexão ao CDB root (TALAMO), o RMAN reporta que não é possível:

[oracle@quiasma ~]$ rman target '"BSS1/oracle@TALAMO as sysbackup"'
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 10 05:29:35 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

Já no PDB HIPOFISE1 conseguimos conectar e realizar o backup da tablespace USERS do PDB:

[oracle@quiasma ~]$ rman target '"BSS1/oracle@HIPOFISE1 as sysbackup"'
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 10 05:30:09 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: TALAMO:HIPOFISE1 (DBID=294818613)
 
RMAN> BACKUP TABLESPACE USERS;
 
Starting backup at 10-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 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=00088 name=/oracle/dados/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/datafile/o1_mf_users_j7824z91_.dbf
channel ORA_DISK_1: starting piece 1 at 10-MAY-21
channel ORA_DISK_1: finished piece 1 at 10-MAY-21
piece handle=/oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053106_j9kvtbf6_.bkp tag=TAG20210510T053106 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-MAY-21
 
RMAN> LIST BACKUP OF TABLESPACE USERS;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    1.05M      DISK        00:00:00     10-MAY-21
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20210510T053106
        Piece Name: /oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053106_j9kvtbf6_.bkp
  List of Datafiles in backup set 44
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  88      Full 3698149    10-MAY-21              NO    /oracle/dados/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/datafile/o1_mf_users_j7824z91_.dbf
 
RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
skipping archived logs when connected to a PDB
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
44      44      1   1   AVAILABLE   DISK        /oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053106_j9kvtbf6_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053106_j9kvtbf6_.bkp RECID=44 STAMP=1072157466
Deleted 1 objects

Criando um common user chamado ASWAN e concedendo privilégio de sysbackup:

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 10 05:33:05 2021
Version 18.13.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.13.0.0.0
 
SQL> CREATE USER C##ASWAN IDENTIFIED BY oracle CONTAINER=ALL;
 
User created.
 
SQL> GRANT SYSBACKUP TO C##ASWAN CONTAINER=ALL;
 
Grant succeeded.

Realizando backup da tablespace USERS do CDB$ROOT:

[oracle@quiasma ~]$ rman target '"C##ASWAN/oracle@TALAMO as sysbackup"'
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 10 05:35:54 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: TALAMO (DBID=2194831545)
 
RMAN> BACKUP TABLESPACE USERS;
 
Starting backup at 10-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=111 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=/oracle/dados/TALAMO/datafile/o1_mf_users_j3w1czy4_.dbf
channel ORA_DISK_1: starting piece 1 at 10-MAY-21
channel ORA_DISK_1: finished piece 1 at 10-MAY-21
piece handle=/oracle/fra/TALAMO/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053607_j9kw3qbf_.bkp tag=TAG20210510T053607 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-MAY-21
 
Starting Control File and SPFILE Autobackup at 10-MAY-21
piece handle=/oracle/fra/TALAMO/autobackup/2021_05_10/o1_mf_s_1072157768_j9kw3rw0_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-MAY-21
 
RMAN> LIST BACKUP OF TABLESPACE USERS;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45      Full    1.05M      DISK        00:00:00     10-MAY-21
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20210510T053607
        Piece Name: /oracle/fra/TALAMO/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053607_j9kw3qbf_.bkp
  List of Datafiles in backup set 45
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 3699119    10-MAY-21              NO    /oracle/dados/TALAMO/datafile/o1_mf_users_j3w1czy4_.dbf
 
RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
45      45      1   1   AVAILABLE   DISK        /oracle/fra/TALAMO/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053607_j9kw3qbf_.bkp
46      46      1   1   AVAILABLE   DISK        /oracle/fra/TALAMO/autobackup/2021_05_10/o1_mf_s_1072157768_j9kw3rw0_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/TALAMO/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053607_j9kw3qbf_.bkp RECID=45 STAMP=1072157767
deleted backup piece
backup piece handle=/oracle/fra/TALAMO/autobackup/2021_05_10/o1_mf_s_1072157768_j9kw3rw0_.bkp RECID=46 STAMP=1072157768
Deleted 2 objects

Conectando no PDB HIPOFISE1 e realizando o backup da tablespace USERS do PDB:

[oracle@quiasma ~]$ rman target '"C##ASWAN/oracle@HIPOFISE1 as sysbackup"'
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 10 05:37:30 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: TALAMO:HIPOFISE1 (DBID=294818613)
 
RMAN> BACKUP TABLESPACE USERS;
 
Starting backup at 10-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 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=00088 name=/oracle/dados/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/datafile/o1_mf_users_j7824z91_.dbf
channel ORA_DISK_1: starting piece 1 at 10-MAY-21
channel ORA_DISK_1: finished piece 1 at 10-MAY-21
piece handle=/oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053741_j9kw6ovq_.bkp tag=TAG20210510T053741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-MAY-21
 
RMAN> LIST BACKUP OF TABLESPACE USERS;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47      Full    1.05M      DISK        00:00:00     10-MAY-21
        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20210510T053741
        Piece Name: /oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053741_j9kw6ovq_.bkp
  List of Datafiles in backup set 47
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  88      Full 3699239    10-MAY-21              NO    /oracle/dados/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/datafile/o1_mf_users_j7824z91_.dbf
 
RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
skipping archived logs when connected to a PDB
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
47      47      1   1   AVAILABLE   DISK        /oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053741_j9kw6ovq_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/TALAMO/BFC37CDFCB3A0CF9E0536A00A8C0C9C5/backupset/2021_05_10/o1_mf_nnndf_TAG20210510T053741_j9kw6ovq_.bkp RECID=47 STAMP=1072157861
Deleted 1 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