Oracle Database Commands and Practical Examples: August 2020

Thursday 27 August 2020

Multitenant - Install an application in the application root database

Multitenant - Install an application in the application root database

oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 25 17:37:16 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> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST1 READ WRITE NO
4 PLM1 READ WRITE NO
5 PDBREF READ ONLY NO
6 PROXYPDB READ WRITE NO
7 HR_AC READ WRITE NO

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO

Install a new application in an application root

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN INSTALL '1.0';

Pluggable database altered.

SQL> CREATE TABLESPACE hr_tbs;

Tablespace created.

SQL> CREATE USER HR IDENTIFIED BY oracle DEFAULT TABLESPACE HR_TBS QUOTA UNLIMITED ON
HR_TBS CONTAINER = ALL; 2

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE, CREATE TRIGGER TO HR; 2

Grant succeeded.

SQL> ALTER SESSION SET CURRENT_SCHEMA=hr;

Session altered.

SQL> host mkdir /home/oracle/scripts

hr_app_v1.0.sql

Upload tables and data on hr_app_v1.0.sql file as per data accordingly :

SQL> host vi /home/oracle/scripts/hr_app_v1.0.sql

SQL> host vi /home/oracle/scripts/hr_app_v1.0.sql

SQL> @/home/oracle/scripts/hr_app_v1.0.sql

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

After executing query end application installation

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app END INSTALL '1.0';

Pluggable database altered.

SQL> column app_name format a15
SQL> column app_version format a10
SQL> column app_status format a15
SQL>SELECT APP_NAME, APP_VERSION, APP_STATUS FROM DBA_APPLICATIONS WHERE APP_IMPLICIT='N';

APP_NAME APP_VERSIO APP_STATUS
--------------- ---------- ---------------
HR_APP 1.0 NORMAL

Multitenant- Create Application pluggable database on Application root Container database

In this Sceanario we will create 2 application pluggable database HR_PDB1 and HR_PDB2

SQL> conn sys/system123@hr_ac as sysdba
Connected.

SQL> CREATE PLUGGABLE DATABASE hr_pdb1 admin user hr_pdb1adm identified by oracle;

Pluggable database created.

SQL> SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO
8 HR_PDB1 MOUNTED

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 SAVE STATE;

Pluggable database altered.

SQL> SELECT STATUS FROM DBA_PDBS WHERE PDB_NAME='HR_PDB1';

STATUS
----------
NORMAL

SQL> ALTER SESSION SET CONTAINER=hr_pdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

We connect to application pdb database and check status of tables that were created at root application container database. We will share link:::::

SQL> conn hr/oracle@//srv4:1523/hr_pdb1
Connected.

SQL> SELECT TNAME FROM TAB ;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.

SQL> SELECT * FROM REGIONS ;

no rows selected

SQL> SELECT SEQUENCE_NAME FROM USER_SEQUENCES ;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_73166
ISEQ$$_73171
ISEQ$$_73174
ISEQ$$_73177
ISEQ$$_73180
ISEQ$$_73183
ISEQ$$_73186
ISEQ$$_73189
ISEQ$$_73192

9 rows selected.

Create another application PDB database hr_pdb2.

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE hr_pdb2 admin user hr_pdb2adm identified by oracle;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 SAVE STATE;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=hr_pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

SQL> conn hr/oracle@//srv4:1523/hr_pdb2
Connected.
SQL> SELECT TNAME FROM TAB;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.

Check Application container root database

oracle@srv4:~> sqlplus sys/system123@hr_ac as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:36 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> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO
8 HR_PDB1 READ WRITE NO
9 HR_PDB2 READ WRITE NO

Check overall status of container database including application root container database with there pdb.

oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:56 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> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST1 READ WRITE NO
4 PLM1 READ WRITE NO
5 PDBREF READ ONLY NO
6 PROXYPDB READ WRITE NO
7 HR_AC READ WRITE NO
8 HR_PDB1 READ WRITE NO
9 HR_PDB2 READ WRITE NO
SQL>

Multitenant - APPLICATION CONTAINER Database 12.2 version

Open container database STDRAC

oracle@srv4:~> . oraenv
ORACLE_SID = [SMRNONCDB] ? STDRAC
The Oracle base remains unchanged with value /u01/app/oracle
oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 25 13:48:19 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> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST1 READ WRITE NO
4 PLM1 READ WRITE NO
5 PDBREF READ ONLY NO
6 PROXYPDB READ WRITE NO

Create APPLICATION root CONTAINER Database 12.2 version

SQL> CREATE PLUGGABLE DATABASE hr_ac AS APPLICATION CONTAINER ADMIN USER hr_acadm IDENTIFIED BY oracle;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE hr_ac OPEN;

Pluggable database altered.

Check Application root container database status

SQL> col name format a10
SQL>SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS WHERE APPLICATION_ROOT='YES';

CON_ID NAME OPEN_MODE
---------- ---------- ----------
7 HR_AC READ WRITE

SQL> ALTER PLUGGABLE DATABASE hr_ac SAVE STATE;

Pluggable database altered.

Check status of root application container database

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM CDB_DATA_FILES WHERE CON_ID=7;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_system_hn9vs1qd_.dbf
SYSTEM

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_sysaux_hn9vs1tf_.dbf
SYSAUX

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
o1_mf_undotbs1_hn9vs1tf_.dbf
UNDOTBS1

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST1 READ WRITE NO
4 PLM1 READ WRITE NO
5 PDBREF READ ONLY NO
6 PROXYPDB READ WRITE NO
7 HR_AC READ WRITE NO

SQL> ALTER SESSION SET CONTAINER=HR_AC;

Session altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO

Check granted roles and privileges

SQL> col grantee format a10
SQL>col granted_role format a15
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON FROM DBA_ROLE_PRIVS where GRANTEE ='HR_ACADM';

GRANTEE GRANTED_ROLE COM
---------- --------------- ---
HR_ACADM PDB_DBA NO

SQL> col role format a10
SQL>col privilege format a30
SQL> SELECT ROLE, PRIVILEGE, ADMIN_OPTION, COMMON, INHERITED FROM ROLE_SYS_PRIVS WHERE ROLE='PDB_DBA';

ROLE PRIVILEGE ADM COM INH
---------- ------------------------------ --- --- ---
PDB_DBA CREATE SESSION NO NO NO
PDB_DBA SET CONTAINER NO NO NO
PDB_DBA CREATE PLUGGABLE DATABASE NO NO NO

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO
SQL> sho parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string STDRAC
SQL> select service_id,name,pdb from v$services;

SERVICE_ID NAME
---------- ----------
PDB
--------------------------------------------------------------------------------
19 hr_ac
HR_AC

Connect root container database as an sysdba

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 HR_AC READ WRITE NO

Tuesday 25 August 2020

Multitenant - Performing Flashback for PDB using restorepoint

Multitenant - Performing Flashback for PDB using restorepoint

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 21:04:31 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 RESTORE POINT pre_change FOR PLUGGABLE DATABASE plm2 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> col name format a20
SELECT SCN, NAME, CON_ID, PDB_RESTORE_POINT, GUARANTEE_FLASHBACK_DATABASE,CLEAN_PDB_RESTORE_POINT FROM V$RESTORE_POINT;SQL>

SCN NAME CON_ID PDB GUA CLE
---------- -------------------- ---------- --- --- ---
3478897 PRE_CHANGE 5 YES YES NO

SQL> conn infodba/infodba@plm2
Connected.
SQL> DELETE infodba.tb1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE plm2 close immediate;

Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE plm2 TO RESTORE POINT pre_change;

Flashback complete.

SQL> ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

Pluggable database altered.

SQL> conn infodba/infodba@plm2
Connected.
SQL> select count(*) from infodba.tb1;

COUNT(*)
----------
10

SQL> conn / as sysdba
Connected.

SQL> ALTER SESSION SET CONTAINER = plm2;

Session altered.

SQL> DROP RESTORE POINT pre_change;

Restore point dropped.

Monday 24 August 2020

Multitenant - Enable Flashback on CDB and perform flashback on CDB Common User

Create common user on CDB

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 20:40:04 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 USER C##USER1 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area/SMRCDB
db_recovery_file_dest_size big integer 10398M

Enable flashback on CDB database

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

SQL> SHUTDOWN IMMEDIATE
;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 8793832 bytes
Variable Size 704643352 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;

System altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Check current scn and perform flashback by drop common user

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
3477642

SQL> col username format A20
SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';SQL>

USERNAME COM CON_ID
-------------------- --- ----------
C##USER1 YES 1

SQL> DROP USER C##USER1 CASCADE;

User dropped.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 8793832 bytes
Variable Size 704643352 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL>FLASHBACK DATABASE TO SCN 3477642;

Flashback complete.

Check the status of common user and then open database in resetlogs

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

Pluggable database altered.

SQL> SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';

USERNAME COM CON_ID
-------------------- --- ----------
C##USER1 YES 1
C##USER1 YES 5
C##USER1 YES 6
C##USER1 YES 8

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
STARTUP MOUNORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 8793832 bytes
Variable Size 704643352 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 PLM2 MOUNTED
6 PLM4 MOUNTED
8 PLM5 MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

Multitenant - Performing Backup and Restore on Pluggable database

Enabling a PDB Administrator to Backup and Restore its PDB

Scenario Preview :

User creation and sysdba privilage and perform backup on pluggable database.
Restore and Recover system datafile from pluggable database.
Perform SCN based recovery on pluggable database.

PART 1:

We create user infodba and grant sysdba or sysbackup privlege to user and perform backup and recovery operations.

SQL> GRANT SYSDBA TO infodba;

Grant succeeded.

Backup Pluggable database PLM2

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target "infodba@PLM2"

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 16:39:43 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: SMRCDB:PLM2 (DBID=3048296195)

RMAN> BACKUP DATABASE TAG 'PLM2PDB';

Starting backup at 17-AUG-20
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=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf
input datafile file number=00031 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
input datafile file number=00030 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
input datafile file number=00032 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-20
channel ORA_DISK_1: finished piece 1 at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnx6hdd_.bkp tag=PLM2PDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 17-AUG-20

Validate backup and check size

RMAN> LIST BACKUPSET;

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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17 685.29M DISK 00:00:17 17-AUG-20
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20200817T135457
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/backupset/2020_08_17/o1_mf_annnn_TAG20200817T135457_hmnhps92_.bkp

List of Archived Logs in backup set 17
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 49 3079727 16-AUG-20 3115985 16-AUG-20
1 50 3115985 16-AUG-20 3154730 16-AUG-20
1 51 3154730 16-AUG-20 3192919 17-AUG-20
1 52 3192919 17-AUG-20 3240222 17-AUG-20
1 53 3240222 17-AUG-20 3249147 17-AUG-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 556.96M DISK 00:00:41 17-AUG-20
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20200817T135526
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
30 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
31 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
32 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
44 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23 260.00K DISK 00:00:00 17-AUG-20
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20200817T140343
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/backupset/2020_08_17/o1_mf_annnn_TAG20200817T140343_hmnj77s4_.bkp

List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 54 3249147 17-AUG-20 3249582 17-AUG-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 556.96M DISK 00:00:33 17-AUG-20
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20200817T141833
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
30 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
31 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
32 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
44 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 1.11M DISK 00:00:00 17-AUG-20
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20200817T142139
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
44 Full 3250286 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 557.00M DISK 00:00:05 17-AUG-20
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: PLM2PDB
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnsfpnq_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
30 Full 3458218 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
31 Full 3458218 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
32 Full 3458218 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
44 Full 3458218 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 556.98M DISK 00:00:03 17-AUG-20
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: PLM2PDB
Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnx6hdd_.bkp
List of Datafiles in backup set 30
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
30 Full 3462287 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
31 Full 3462287 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
32 Full 3462287 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
44 Full 3462287 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

PART2 :

Perform backup and recovery of Pluggable database.

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 14:18:03 2020

Connected to:

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

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> exit

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

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 14:18:19 2020

connected to target database: SMRCDB (DBID=3280283628)

RMAN> backup pluggable database plm2;

Starting backup at 17-AUG-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=106 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=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

input datafile file number=00031 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf

input datafile file number=00030 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf

input datafile file number=00032 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf

channel ORA_DISK_1: starting piece 1 at 17-AUG-20

channel ORA_DISK_1: finished piece 1 at 17-AUG-20

piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp tag=TAG20200817T141833 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 17-AUG-20

Starting Control File and SPFILE Autobackup at 17-AUG-20

piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/autobackup/2020_08_17/o1_mf_s_1048688358_hmnk4h8f_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 17-AUG-20

RMAN> list backup of pluggable database plm2;

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

21 Full 556.96M DISK 00:00:41 17-AUG-20

BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20200817T135526

Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp

List of Datafiles in backup set 21

Container ID: 5, PDB Name: PLM2

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

30 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf

31 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf

32 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf

44 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

25 Full 556.96M DISK 00:00:33 17-AUG-20

BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20200817T141833

Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp

List of Datafiles in backup set 25

Container ID: 5, PDB Name: PLM2

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

30 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf

31 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf

32 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf

44 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

RMAN> BACKUP TABLESPACE plm2:users;

Starting backup at 17-AUG-20

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=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-AUG-20

channel ORA_DISK_1: finished piece 1 at 17-AUG-20

piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp tag=TAG20200817T142139 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-AUG-20

Starting Control File and SPFILE Autobackup at 17-AUG-20

piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/autobackup/2020_08_17/o1_mf_s_1048688500_hmnk8wpm_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 17-AUG-20

RMAN> LIST BACKUP OF TABLESPACE plm2:users;

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

21 Full 556.96M DISK 00:00:41 17-AUG-20

BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20200817T135526

Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp

List of Datafiles in backup set 21

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

44 Full 3249524 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

25 Full 556.96M DISK 00:00:33 17-AUG-20

BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20200817T141833

Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp

List of Datafiles in backup set 25

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

44 Full 3250159 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

27 Full 1.11M DISK 00:00:00 17-AUG-20

BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20200817T142139

Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp

List of Datafiles in backup set 27

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

44 Full 3250286 17-AUG-20 NO /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 15:48:19 2020

Connected to:

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

SQL> ALTER SESSION SET CONTAINER=PLM2;

Session altered.

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

FILE_NAME

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

/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/

o1_mf_system_hlr3x0xl_.dbf

Remove system datafile on PLM2 database and perform recovery below:

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

5 PLM2 READ WRITE NO

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 15:58:07 2020

connected to target database: SMRCDB (DBID=3280283628)

RMAN> shutdown abort;

using target database control file instead of recovery catalog

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 1795162112 bytes

Fixed Size 8793832 bytes

Variable Size 704643352 bytes

Database Buffers 1073741824 bytes

Redo Buffers 7983104 bytes

RMAN> RESTORE TABLESPACE PLM2:SYSTEM;

Starting restore at 17-AUG-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 device type=DISK

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 00030 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.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:07

Finished restore at 17-AUG-20

RMAN> RECOVER TABLESPACE PLM2:SYSTEM;

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

RMAN> ALTER DATABASE OPEN;

Statement processed

PART 3:

Perform SCN based Pluggable database backup

RMAN> SELECT NAME, OPEN_MODE FROM V$PDBS WHERE NAME='PLM2';

NAME

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

OPEN_MODE

----------

PLM2

MOUNTED

SQL> alter session set container=PLM2;

Session altered.

SQL> select * from infodba.tb1;

no rows selected

SQL> BEGIN

FOR I IN 1..10 LOOP

INSERT INTO INFODBA.TB1 (ID,NOTES) VALUES (I, TO_CHAR(SYSDATE,'DD-MM-YY

HH24:MI'));

END LOOP;

COMMIT;

END;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM INFODBA.TB1;

COUNT(*)

----------

SQL> SELECT TIMESTAMP_TO_SCN(sysdate) from DUAL;

TIMESTAMP_TO_SCN(SYSDATE)

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

3469088

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3469108

SQL> DELETE INFODBA.TB1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL>

ALTER PLUGGABLE DATABASE plm2 CLOSE IMMEDIATE;

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 19:26:24 2020

Connected to:

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

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3468533

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

5 PLM2 MOUNTED

6 PLM4 READ WRITE NO

8 PLM5 READ WRITE NO

SQL> alter pluggable database PLM2 open;

Pluggable database altered.

oracle@srv3:~> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 19:30:25 2020

connected to target database: SMRCDB (DBID=3280283628)

RMAN> ALTER PLUGGABLE DATABASE plm2 CLOSE IMMEDIATE;

using target database control file instead of recovery catalog

Statement processed

RMAN> RUN

{

SET UNTIL SCN=3469108;

RESTORE PLUGGABLE DATABASE plm2;

RECOVER PLUGGABLE DATABASE plm2;

}

2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 17-AUG-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 device type=DISK

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

channel ORA_DISK_1: restoring datafile 00031 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf

channel ORA_DISK_1: restoring datafile 00032 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf

channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.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_TAG20200817T185138_hmo1hqpy_.bkp

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 17-AUG-20

Starting recover at 17-AUG-20

current log archived

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 17-AUG-20

RMAN>

ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

RMAN> ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

Statement processed

RMAN>

Recovery Manager complete.

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 19:34:02 2020

Connected to:

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

SQL> alter session set container=PLM2;

Session altered.

SQL> SELECT COUNT(*) FROM INFODBA.TB1;

COUNT(*)

----------

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: 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

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

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

HOME

Thursday 27 August 2020

Tuesday 25 August 2020

Monday 24 August 2020