Multitenant Dataguard: Create multitenant database on priamry and standby database using FILE_NAME_CONVERT parameter

Recently we faced some issue on multitenant standby database . As we create pdb on primary same not reflected on standby database as it was conflicting  with destination directory and datafile as well. 

This scenario  about using FILE_NAME_CONVERT parameter we create multitenant  primary pluggable  database and same will reflect on standby database. 

PRIMARY Pluggable database :

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

• NOTE : Create directory in pluggable standby database before creting pluggable database on primary database.

Example: 

[oracle@srv7 ~]$ mkdir -pv /u02/app/oracle/oradata/PRIMARY19C/pdb3

mkdir: created directory ‘/u02/app/oracle/oradata/PRIMARY19C/pdb3’

• Create Pluggable database on primary database and mention file_name_convert parameter  below and provide directory of new pdb3 we have mentioned  /u02/app/oracle/oradata/PRIMARY19C/pdb3 and it will be created automatically. 

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1 FILE_NAME_CONVERT=('/u02/app/oracle/oradata/PRIMARY19C/pdbseed','/u02/app/oracle/oradata/PRIMARY19C/pdb3');

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

         4 PDB3                           MOUNTED

• Open primary pluggable database 

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

         4 PDB3                           READ WRITE NO

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

         4 PDB3                           READ WRITE NO

         5 PDB4                           READ WRITE NO

• Check the PDB service entry  

SQL> COLUMN name FORMAT A30

SQL> COLUMN PDB FORMAT a40

SQL>

SQL> set lines 200

SQL> SELECT name, pdb FROM   v$services ORDER BY name;

NAME                           PDB

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

SYS$BACKGROUND                 CDB$ROOT

SYS$USERS                      CDB$ROOT

pdb                            PDB

pdb3                           PDB3

pdb4                           PDB4

primary19c                     CDB$ROOT

primary19cXDB                  CDB$ROOT

7 rows selected.

SQL> connect sys/sys@PDB3 as sysdba

Connected.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         4 PDB3                           READ WRITE NO

SQL>

• Check datafile details below on primary multitenant database 

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/users01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf

/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

• Check Details on Multitenant standby database 

[oracle@srv7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:49:56 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MOUNTED

         3 PDB                            MOUNTED

         4 PDB3                           MOUNTED

SQL>  sho pdbs

• Check datafile details below on Standby multitenant database 

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/users01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf

/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

14 rows selected.

Alter log file file standby database :

Recovery created pluggable database PDB3

2022-02-03T20:48:59.217732+05:30

Recovery copied files for tablespace SYSTEM

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

PDB3(4):Successfully added datafile 25 to media recovery

PDB3(4):Datafile #25: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf'

2022-02-03T20:49:23.126371+05:30

Recovery copied files for tablespace SYSAUX

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

PDB3(4):Successfully added datafile 26 to media recovery

PDB3(4):Datafile #26: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf'

2022-02-03T20:49:30.846103+05:30

Recovery copied files for tablespace UNDOTBS1

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

PDB3(4):Successfully added datafile 27 to media recovery

PDB3(4):Datafile #27: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf'

2022-02-03T20:52:50.952812+05:30

• Same example now we create   PDB4 multitenant database on primary DB

• NOTE : Create directory in pluggable standby database before creting pluggable database on primary database.

[oracle@srv7 ~]$ mkdir -pv /u02/app/oracle/oradata/PRIMARY19C/pdb4

mkdir: created directory ‘/u02/app/oracle/oradata/PRIMARY19C/pdb4’

[oracle@srv7 ~]$ cd /u02/app/oracle/oradata/PRIMARY19C/pdb4

[oracle@srv7 pdb4]$ ll

Create Pluggable database on primary database and mention file_name_convert parameter  below and provide directory of new pdb4 we have mentioned  /u02/app/oracle/oradata/PRIMARY19C/pdb4 and it will be created automatically.

SQL> CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY Password1 FILE_NAME_CONVERT=('/u02/app/oracle/oradata/PRIMARY19C/pdbseed','/u02/app/oracle/oradata/PRIMARY19C/pdb4');

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

         4 PDB3                           READ WRITE NO

         5 PDB4                           MOUNTED

• Open primary pluggable database 

SQL> alter pluggable database PDB4 open;

Pluggable database altered.

SQL> sho parameter file_name_convert

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u02/app/oracle/oradata/PRIMAR

                                                 Y19C, /u02/app/oracle/oradata/

                                                 STANDBY19C

log_file_name_convert                string      /u02/app/oracle/oradata/PRIMAR

                                                 Y19C, /u02/app/oracle/oradata/

                                                 STANDBY19C

pdb_file_name_convert                string

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/users01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf

/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf

17 rows selected.

SQL>

STANDBY Pluggable database :

[oracle@srv7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:49:56 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MOUNTED

         3 PDB                            MOUNTED

         4 PDB3                           MOUNTED

SQL>  sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MOUNTED

         3 PDB                            MOUNTED

         4 PDB3                           MOUNTED

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/users01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf

/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

14 rows selected.

SQL> host

total 0

[oracle@srv7 pdb4]$ pwd

/u02/app/oracle/oradata/PRIMARY19C/pdb4

[oracle@srv7 pdb4]$ ll

total 716828

-rw-r----- 1 oracle oinstall 346038272 Feb  3 20:53 sysaux01.dbf

-rw-r----- 1 oracle oinstall 283123712 Feb  3 20:53 system01.dbf

-rw-r----- 1 oracle oinstall 104865792 Feb  3 20:53 undotbs01.dbf

[oracle@srv7 pdb4]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:54:21 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> sho parameter file_name_convert

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u02/app/oracle/oradata/STANDB

                                                 Y19C, /u02/app/oracle/oradata/

                                                 PRIMARY19C

log_file_name_convert                string      /u02/app/oracle/oradata/STANDB

                                                 Y19C, /u02/app/oracle/oradata/

                                                 PRIMARY19C

pdb_file_name_convert                string

• Check datafile details below on primary multitenant database 

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/users01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf

/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME

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

/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf

/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf

17 rows selected.

• Check the multitenant database status on standby database 

SQL> @/home/oracle/d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

PRIMARY1  standby19c       standby19c                     MOUNTED              PHYSICAL STANDBY NO                 0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MOUNTED

         3 PDB                            MOUNTED

         4 PDB3                           MOUNTED

         5 PDB4                           MOUNTED

SQL>

Alter log file file standby database :

Recovery created pluggable database PDB3

2022-02-03T20:48:59.217732+05:30

Recovery copied files for tablespace SYSTEM

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf

PDB3(4):Successfully added datafile 25 to media recovery

PDB3(4):Datafile #25: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf'

2022-02-03T20:49:23.126371+05:30

Recovery copied files for tablespace SYSAUX

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf

PDB3(4):Successfully added datafile 26 to media recovery

PDB3(4):Datafile #26: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf'

2022-02-03T20:49:30.846103+05:30

Recovery copied files for tablespace UNDOTBS1

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

PDB3(4):WARNING: File being created with same name as in Primary

PDB3(4):Existing file may be overwritten

PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

PDB3(4):Successfully added datafile 27 to media recovery

PDB3(4):Datafile #27: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf'

2022-02-03T20:52:50.952812+05:30

Recovery created pluggable database PDB4

2022-02-03T20:53:08.208662+05:30

Recovery copied files for tablespace SYSTEM

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf

PDB4(5):WARNING: File being created with same name as in Primary

PDB4(5):Existing file may be overwritten

PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf

PDB4(5):Successfully added datafile 28 to media recovery

PDB4(5):Datafile #28: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf'

2022-02-03T20:53:39.119703+05:30

Recovery copied files for tablespace SYSAUX

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf

PDB4(5):WARNING: File being created with same name as in Primary

PDB4(5):Existing file may be overwritten

PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf

PDB4(5):Successfully added datafile 29 to media recovery

PDB4(5):Datafile #29: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf'

2022-02-03T20:53:45.814030+05:30

Recovery copied files for tablespace UNDOTBS1

Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf

PDB4(5):WARNING: File being created with same name as in Primary

PDB4(5):Existing file may be overwritten

PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf

PDB4(5):Successfully added datafile 30 to media recovery

PDB4(5):Datafile #30: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf'

Multitenant - Triggers 12.1.0.2.0

Hi all in 12.1.0.2.0 we use trigger and for 12.2 we use savepoint . 

See link: 12.2.0.1.0 Savepoint

We tested in both standalone or RAC grid database when start again CDB get started but PDB remains mounted. So in order to avoid this we user trigger in 12.1 and in 12.2 we use save point.

Below example we execute trigger so on next time when we start CDB automatically all pdb will be in read write.  We can modify triggers as per requirement just like save point.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL> CREATE OR REPLACE TRIGGER open_pdbs

  AFTER STARTUP ON DATABASE

BEGIN

   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END open_pdbs;

/  2    3    4    5    6

Trigger created.

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRCDB    SMRCDB           SMRCDB                         READ WRITE           PRIMARY          NO                 0

SQL> exit

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

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@TEST:~> tnsping SMRCDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2021 08:56:06

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SMRCDB)))

OK (0 msec)

oracle@TEST:~> sqlplus sys/----@SMRCDB as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:16 2021

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

Connected to:

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

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRCDB    SMRCDB           SMRCDB                         READ WRITE           PRIMARY          NO                 0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE.

SQL> exit

oracle@TEST:~> . oraenv

ORACLE_SID = [SMRCDB] ?

The Oracle base remains unchanged with value /u01/app/oracle

oracle@TEST:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:56 2021

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             654313592 bytes

Database Buffers         1476395008 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL>

Multitenant : Application PDB status after application container database upgrade on 12.2 version

Multitenant : Application PDB status after application container database upgrade on 12.2 version

Application Container database :  HR_APP

Application Pluggable database :  HR_PDB1

                                                       :   HR_PDB2

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

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 15:52:02 2020

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

Connected to:

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

• Connect to root container database and check the application container and pluggable database status

SQL> conn / as sysdba

Connected.

SQL> col name format a20

SQL> SELECT CON_ID, NAME, APPLICATION_ROOT, OPEN_MODE, APPLICATION_ROOT_CON_ID FROM V$PDBS ORDER BY 3,1;

    CON_ID NAME                 APP OPEN_MODE  APPLICATION_ROOT_CON_ID

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

         2 PDB$SEED             NO  READ ONLY

         3 TEST1                NO  READ WRITE

         4 PLM1                 NO  READ WRITE

         5 PDBREF               NO  READ ONLY

         6 PROXYPDB             NO  READ WRITE

         8 HR_PDB1              NO  READ WRITE                       7

         9 HR_PDB2              NO  READ WRITE                       7

         7 HR_AC                YES READ WRITE

        10 F2441155579_21_1     YES READ ONLY                        7

9 rows selected.

SQL> col name format a80

SQL> SELECT NAME FROM V$DATAFILE WHERE CON_ID=7;

NAME

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

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

o1_mf_system_hn9vs1qd_.dbf

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

o1_mf_sysaux_hn9vs1tf_.dbf

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

o1_mf_undotbs1_hn9vs1tf_.dbf

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

o1_mf_hr_tbs_hn9zsfl1_.dbf

NAME

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

• Connect to application container database and SYNC application pluggable database.

SQL> ALTER SESSION SET CONTAINER=HR_AC;

Session altered.

SQL> column app_name format a15

column app_version format a5

column app_status format a15

SELECT APP_NAME, APP_VERSION, APP_STATUS FROM DBA_APPLICATIONS WHERE APP_IMPLICIT='N';SQL> SQL> SQL>

APP_NAME        APP_V APP_STATUS

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

HR_APP          2.0   NORMAL

SQL> conn sys/system123@hr_ac as sysdba

Connected.

SQL> ALTER SESSION SET CONTAINER= hr_pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=hr_pdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

• Check the status below:

SQL> conn sys/system123@hr_ac as sysdba

Connected.

SQL> col pdb_name format a10

SQL> SELECT PDB.PDB_NAME, APP.APP_NAME, APP.APP_VERSION , APP.APP_STATUS FROM DBA_APP_PDB_STATUS APP, DBA_PDBS PDB WHERE APP.CON_UID=PDB.CON_UID;

PDB_NAME   APP_NAME        APP_V APP_STATUS

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

HR_PDB1    HR_APP          2.0   NORMAL

HR_PDB2    HR_APP          2.0   NORMAL

Multitenant : Application container database Maintenance on 12.2 version

Multitenant : Application container database Maintenance on 12.2 version

Scenario Preview : We have created application container database and 2 pluggable database already created. We have to perform modification or add application tables on application container database. So all modification were reflected in plugable database.

Application Container database :  HR_APP

Application Pluggable database :  HR_PDB1

                                                       :  HR_PDB2

SQL> conn sys/system123@hr_ac as sysdba
Connected.
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

• Begin upgrade at application container database level.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN UPGRADE '1.0' TO '2.0';

Pluggable database altered.

• Make changes at script either modify or add tables on it.

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

SQL> @/home/oracle/scripts/hr_app_v1.1.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.


Table created.

• End Upgrade after completion of script at application container database.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app END UPGRADE TO '2.0';

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER= hr_pdb1;

Session altered.

• SYNC application container database with multiple pluggable database.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

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

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

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 15:52:02 2020

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


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

• Connecting with root container and check status below of PDB and Container application PDB database.

SQL> conn / as sysdba
Connected.
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
        10 F2441155579_21_1               READ ONLY  NO
SQL> col name format a20
SQL> SELECT CON_ID, NAME, APPLICATION_ROOT, OPEN_MODE, APPLICATION_ROOT_CON_ID FROM V$PDBS ORDER BY 3,1;

    CON_ID NAME                 APP OPEN_MODE  APPLICATION_ROOT_CON_ID
---------- -------------------- --- ---------- -----------------------
         2 PDB$SEED             NO  READ ONLY
         3 TEST1                NO  READ WRITE
         4 PLM1                 NO  READ WRITE
         5 PDBREF               NO  READ ONLY
         6 PROXYPDB             NO  READ WRITE
         8 HR_PDB1              NO  READ WRITE                       7
         9 HR_PDB2              NO  READ WRITE                       7
         7 HR_AC                YES READ WRITE
        10 F2441155579_21_1     YES READ ONLY                        7

9 rows selected.

SQL> col name format a80
SQL> SELECT NAME FROM V$DATAFILE WHERE CON_ID=7;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_system_hn9vs1qd_.dbf

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

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_undotbs1_hn9vs1tf_.dbf

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_hr_tbs_hn9zsfl1_.dbf

NAME
--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER=HR_AC;

Session altered.

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

APP_NAME        APP_V APP_STATUS
--------------- ----- ---------------
HR_APP          2.0   NORMAL

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

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

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

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.

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

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 :

1. User creation and sysdba privilage and perform backup on pluggable database.
2. Restore and Recover system datafile from pluggable database.
3. 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

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

         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

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

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

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

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

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

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(*)

----------

        10

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

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

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

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

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: piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T185138_hmo1hqpy_.bkp tag=TAG20200817T185138

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

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

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(*)

----------

        10