Multitenant - Performing RMAN Recovery from non-SYSTEM Root Datafile Loss on CDB

Multitenant -  Performing RMAN Recovery from non-SYSTEM Root Datafile Loss


Scenario Preview : In this case we will delete syssaux tablespace on PLM2 pluggable database and recover it using RDA recovery data advisor.


SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         8 PLM5                           READ WRITE NO
SQL> alter session set container=PLM2;

• Delete sysaux tablespace on PLM2 pluggable database.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/
o1_mf_sysaux_hlr3x0xq_.dbf


oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rm -f /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/
o1_mf_sysaux_hlr3x0xq_.dbf

• Using Recovery data Advisor RDA  validate datafile.

RMAN> validate datafile '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf';

Starting validate at 17-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
RMAN-06169: could not read file header for datafile 31 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 08/17/2020 16:18:38
RMAN-06056: could not access datafile 31

• List Failure 

RMAN> LIST FAILURE;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

RMAN>  LIST FAILURE detail;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 6202

  Failure ID Priority Status    Time Detected Summary

  ---------- -------- --------- ------------- -------

  6205       HIGH     OPEN      17-AUG-20     Datafile 31: '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf' is missing

    Impact: Some objects in tablespace SYSAUX might be unavailable

• Advise Failure 

RMAN> ADVISE FAILURE;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 6202

  Failure ID Priority Status    Time Detected Summary

  ---------- -------- --------- ------------- -------

  6205       HIGH     OPEN      17-AUG-20     Datafile 31: '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf' is missing

    Impact: Some objects in tablespace SYSAUX might be unavailable

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Restore and recover datafile 31

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

contents of repair script:

   # restore and recover datafile

   sql 'PLM2' 'alter database datafile 31 offline';

   restore ( datafile 31 );

   recover datafile 31;

   sql 'PLM2' 'alter database datafile 31 online';

• Recover datafile syssaux below :

RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

contents of repair script:

   # restore and recover datafile

   sql 'PLM2' 'alter database datafile 31 offline';

   restore ( datafile 31 );

   recover datafile 31;

   sql 'PLM2' 'alter database datafile 31 online';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

sql statement: alter database datafile 31 offline

Starting restore at 17-AUG-20

using channel ORA_DISK_1

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 00031 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp tag=TAG20200817T141833

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 17-AUG-20

Starting recover at 17-AUG-20

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-AUG-20

sql statement: alter database datafile 31 online

repair failure complete

RMAN>

Multitenant - Granting the Privileges as Common or Local on CDB and PDB

Multitenant - Granting the Privileges as Common or Local

• Connect and check common user privilages

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 12:11:22 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> col grantee format a18
col privilege format a14
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID FROM CDB_SYS_PRIVS WHERE GRANTEE IN ('C##USER1', 'LUSER2');

GRANTEE            PRIVILEGE      COM     CON_ID
------------------ -------------- --- ----------
C##USER1           CREATE SESSION YES          1
LUSER2             CREATE SESSION NO           5
C##USER1           CREATE SESSION YES          5
C##USER1           CREATE SESSION YES          6
C##USER1           CREATE SESSION YES          8

• Check user privileges on connecting PDB database. 

SQL> CONNECT system/system123@PLM2
Connected.
SQL> SELECT GRANTEE, PRIVILEGE, COMMON
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('C##USER1', 'LUSER2');  2    3

GRANTEE            PRIVILEGE      COM
------------------ -------------- ---
LUSER2             CREATE SESSION NO
C##USER1           CREATE SESSION YES

• Check  privileges and Grant create table to common C##USER1 to all container user.

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 12:11:22 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> col grantee format a18
col privilege format a14
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID FROM CDB_SYS_PRIVS WHERE GRANTEE IN ('C##USER1', 'LUSER2');SQL> SQL>

GRANTEE            PRIVILEGE      COM     CON_ID
------------------ -------------- --- ----------
C##USER1           CREATE SESSION YES          1
LUSER2             CREATE SESSION NO           5
C##USER1           CREATE SESSION YES          5
C##USER1           CREATE SESSION YES          6
C##USER1           CREATE SESSION YES          8

SQL> CONNECT system/system123@PLM2
Connected.
SQL> SELECT GRANTEE, PRIVILEGE, COMMON
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('C##USER1', 'LUSER2');  2    3

GRANTEE            PRIVILEGE      COM
------------------ -------------- ---
LUSER2             CREATE SESSION NO
C##USER1           CREATE SESSION YES

SQL> CONNECT system/system123
Connected.
SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL> col grantee format a12
col privilege format a30
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1'
ORDER BY 1,2;SQL> SQL>   2    3    4

GRANTEE      PRIVILEGE                      COM     CON_ID
------------ ------------------------------ --- ----------
C##USER1     CREATE SESSION                 YES          8
C##USER1     CREATE SESSION                 YES          5
C##USER1     CREATE SESSION                 YES          1
C##USER1     CREATE SESSION                 YES          6
C##USER1     CREATE TABLE                   YES          8
C##USER1     CREATE TABLE                   YES          1
C##USER1     CREATE TABLE                   YES          5
C##USER1     CREATE TABLE                   YES          6
C##USER1     UNLIMITED TABLESPACE           YES          6
C##USER1     UNLIMITED TABLESPACE           YES          8
C##USER1     UNLIMITED TABLESPACE           YES          1

GRANTEE      PRIVILEGE                      COM     CON_ID
------------ ------------------------------ --- ----------
C##USER1     UNLIMITED TABLESPACE           YES          5

12 rows selected.

• Check and grant  sequence privileges to current container

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 14 12:38:25 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> GRANT CREATE SEQUENCE TO C##USER1 CONTAINER=CURRENT;

Grant succeeded.

SQL> SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1' AND PRIVILEGE ='CREATE SEQUENCE';  2    3

GRANTEE
--------------------------------------------------------------------------------
PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
C##USER1
CREATE SEQUENCE                          NO           1

• Grant create synonym to PLM2 pluggable database

SQL> CONNECT system/system123@PLM2
Connected.
SQL> GRANT CREATE SYNONYM TO C##USER1 CONTAINER=CURRENT;

Grant succeeded.

SQL> col grantee format a18
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'C##USER1' AND PRIVILEGE ='CREATE SYNONYM';SQL>   2    3

GRANTEE            PRIVILEGE      COM     CON_ID
------------------ -------------- --- ----------
C##USER1           CREATE SYNONYM NO           5

• Grant unlimited tablespace to local user

SQL> CONNECT system/system123@PLM2
Connected.
SQL> GRANT UNLIMITED TABLESPACE TO LUSER2;

Grant succeeded.

SQL> col grantee format a18
SELECT GRANTEE, PRIVILEGE, COMMON, CON_ID
FROM CDB_SYS_PRIVS
WHERE GRANTEE = 'LUSER2';SQL>   2    3

GRANTEE            PRIVILEGE                                COM     CON_ID
------------------ ---------------------------------------- --- ----------
LUSER2             UNLIMITED TABLESPACE                     NO           5
LUSER2             CREATE SESSION                           NO           5


SQL> CONNECT / as sysdba
Connected.
SQL> column username format a10
SQL> column default_attr format a7
column owner format a6
column object_name format a11
column all_containers format a3
column container_name format a10
column con_id format 999
set pages 100
set line 200
SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME,
ALL_CONTAINERS, CONTAINER_NAME, CON_ID
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3  FROM CDB_CONTAINER_DATA
  4  WHERE username NOT IN ('GSMADMIN_INTERNAL', 'APPQOSSYS', 'DBSNMP')
ORDER BY USERNAME;  5

USERNAME   DEFAULT OWNER  OBJECT_NAME ALL CONTAINER_ CON_ID
---------- ------- ------ ----------- --- ---------- ------
DBSFWUSER  Y                          Y                   6
DBSFWUSER  Y                          Y                   1
DBSFWUSER  Y                          Y                   8
SYS        Y                          Y                   1
SYSBACKUP  Y                          Y                   1
SYSDG      Y                          Y                   1
SYSRAC     Y                          Y                   1
SYSTEM     Y                          Y                   6
SYSTEM     Y                          Y                   1
SYSTEM     Y                          Y                   8

10 rows selected.


SQL> CREATE USER C##USER2 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, SET CONTAINER TO C##USER2 CONTAINER=ALL;

Grant succeeded.

SQL> GRANT SELECT ON sys.V_$SESSION TO C##USER2 CONTAINER=ALL;

Grant succeeded.

SQL>
sqlplus sys/system123@plm2 as sysdba



SQL> SELECT USERNAME, CON_ID FROM V_$SESSION
WHERE USERNAME IS NOT NULL AND USERNAME <> 'DBSNMP'
AND CON_ID = ( SELECT CON_ID FROM V$PDBS WHERE NAME='PLM2');  2    3

USERNAME
--------------------------------------------------------------------------------
    CON_ID
----------
SYS
         5


SQL>CONNECT C##USER2/oracle


SQL> SELECT USERNAME, CON_ID FROM V$SESSION
WHERE USERNAME IS NOT NULL AND USERNAME <> 'DBSNMP';  2

USERNAME
--------------------------------------------------------------------------------
    CON_ID
----------
SYS
         0

C##USER2
         1

Multitenant -Creating the Common and Local Roles on CDB and PDB users

Create role and Manage the Common and Local Roles on CDB and PDB

• Query to check roles in CDB database

connect / as sysdba
col role format a30
SELECT ROLE, COMMON, CON_ID
FROM CDB_ROLES
ORDER BY ROLE, CON_ID;

• Connect to CDB database and create role on CDB container ALL

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 18:59:43 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE ROLE C##ROLE1 CONTAINER=ALL;


Role created.

• Create ROLE having name ROLE_PDB2  CONTAINER is CURRENT mode on PLM2 pluggable database

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@PLM2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 19:09:55 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE ROLE LROLE_PDB2 CONTAINER=CURRENT;

Role created.

SQL> SELECT ROLE FROM DBA_ROLES WHERE COMMON ='NO' ORDER BY ROLE;

ROLE
------------------------------
LROLE_PDB2

SQL>

• Redirect to CDB database grant common role to common user

SQL> connect / as sysdba
Connected.
SQL> GRANT C##ROLE1 TO C##USER1;

Grant succeeded.

• Check status below :

SQL> col grantee format A16
col granted_role format A16
SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1';SQL> SQL>

GRANTEE          GRANTED_ROLE     COM     CON_ID
---------------- ---------------- --- ----------
C##USER1         C##ROLE1         NO           1


SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
C##ROLE1

• Grant role privilege to all container pdb

SQL> GRANT C##ROLE1 TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1';  2

GRANTEE          GRANTED_ROLE     COM     CON_ID
---------------- ---------------- --- ----------
C##USER1         C##ROLE1         NO           1
C##USER1         C##ROLE1         YES          1
C##USER1         C##ROLE1         YES          5
C##USER1         C##ROLE1         YES          6
C##USER1         C##ROLE1         YES          8


SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
C##ROLE1

• Check status using PLM2 PDB database 

SQL> CONNECT C##USER1/oracle@PLM2;
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
C##ROLE1

• Revoke Role for all container pdb database 

SQL> CONNECT / as sysdba
Connected.
SQL> REVOKE C##ROLE1 FROM C##USER1 CONTAINER=ALL;

Revoke succeeded.

• Check the status below:

SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1';  2

GRANTEE          GRANTED_ROLE     COM     CON_ID
---------------- ---------------- --- ----------
C##USER1         C##ROLE1         NO           1

SQL> REVOKE C##ROLE1 FROM C##USER1;

Revoke succeeded.

SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='C##USER1';  2

no rows selected

SQL> CONNECT C##USER1/oracle
Connected.
SQL> SELECT * FROM SESSION_ROLES;

no rows selected

SQL> CONNECT C##USER1/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;

no rows selected

• Grant common role to local user on PLM2 PDB database only

SQL> connect system/system123@PLM2
Connected.
SQL> GRANT C##ROLE1 TO LUSER2;

Grant succeeded.

• Check the status 

SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID
FROM CDB_ROLE_PRIVS WHERE GRANTEE='LUSER2';  2

GRANTEE          GRANTED_ROLE     COM     CON_ID
---------------- ---------------- --- ----------
LUSER2           C##ROLE1         NO           5


SQL> CONNECT LUSER2/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
C##ROLE1

SQL> exit

• Check the session roles below:

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@PLM2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 20:10:58 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> connect system/system123@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
--------------------------------------------------------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
CAPTURE_ADMIN
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS

ROLE
--------------------------------------------------------------------------------
OPTIMIZER_PROCESSING_RATE
EM_EXPRESS_ALL
EM_EXPRESS_BASIC
SCHEDULER_ADMIN
XDBADMIN
XDB_SET_INVOKER
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_XS_ADMIN
OLAP_DBA

ROLE
----------------------------

SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON, CON_ID FROM CDB_ROLE_PRIVS WHERE GRANTEE='LUSER2';

GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
COM     CON_ID
--- ----------
LUSER2
LROLE_PDB2
NO           5

LUSER2
C##ROLE1
NO           5

GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
COM     CON_ID
--- ----------



SQL> CONNECT LUSER2/oracle@PLM2
Connected.
SQL> SELECT * FROM SESSION_ROLES;

ROLE
--------------------------------------------------------------------------------
C##ROLE1
LROLE_PDB2