RMAN Backup of Container and Pluggable Database in 19c/21c/23c

In multitenant architecture, there are two databases involved, container database and pluggable database.

To manage RMAN backups of these databases, we need to consider different scenarios,

1. Backup of a container database (CDB+PDB)
2. Backup of pluggable database
3. Backup of the root container 

Case 1: RMAN Backup of container (CDB) plus pluggable database (PDB)

 SQL> show parameter db_name

NAME                       TYPE   VALUE

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

db_name                    string      FREE

SQL> 

SQL> show pdbs

 

    CON_ID CON_NAME                OPEN MODE  RESTRICTED

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

       2 PDB$SEED                   READ ONLY  NO

       3 FREEPDB1                   READ WRITE NO

SQL> 

  

The container database name is FREE while Pluggable database name is FREEPDB1,

now we will take a backup of the pluggable and container databases together

  

Create one directory to take backup of the databases 

 [oracle@localhost oracle]$ mkdir -p /opt/oracle/db_backup

[oracle@localhost oracle]$ cd /opt/oracle/db_backup

[oracle@localhost db_backup]$ 

 Prepare a script to take backup of the database (CDB+PDB) 

 run

{

allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_free_%t_%s_%p_%U.bck';

BACKUP DATABASE plus ARCHIVELOG;

release channel disk1;

}

 

Output of backup script 

[oracle@localhost db_backup]$ rman target /

Recovery Manager: Release 23.0.0.0.0 - Developer-Release on Sun Mar 17 10:07:55 2024

Version 23.2.0.0.0

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

connected to target database: FREE (DBID=1408182090)

RMAN> run

{

allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_free_%t_%s_%p_%U.bck';

BACKUP DATABASE plus ARCHIVELOG;

release channel disk1;

}run

2> {

3> allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_free_%t_%s_%p_%U.bck';

4> BACKUP DATABASE plus ARCHIVELOG;

5> release channel disk1;

6> 

}

using target database control file instead of recovery catalog

allocated channel: disk1

channel disk1: SID=56 device type=DISK

Starting backup at 17-MAR-24

current log archived

channel disk1: starting archived log backup set

channel disk1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=1 STAMP=1163844377

input archived log thread=1 sequence=10 RECID=2 STAMP=1163844465

input archived log thread=1 sequence=11 RECID=3 STAMP=1163844483

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_free_1163844483_152_1_4o2ltms3_152_1_1.bck tag=TAG20240317T100803 comment=NONE

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

Finished backup at 17-MAR-24

 

Starting backup at 17-MAR-24

channel disk1: starting full datafile backup set

channel disk1: specifying datafile(s) in backup set

input datafile file number=00016 name=/opt/oracle/oradata/FREE/undotbs2.dbf

input datafile file number=00001 name=/opt/oracle/oradata/FREE/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/FREE/sysaux01.dbf

input datafile file number=00007 name=/opt/oracle/oradata/FREE/users01.dbf

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_free_1163844486_153_1_4p2ltms6_153_1_1.bck tag=TAG20240317T100805 comment=NONE

channel disk1: backup set complete, elapsed time: 00:00:36

channel disk1: starting full datafile backup set

channel disk1: specifying datafile(s) in backup set

input datafile file number=00013 name=/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf

input datafile file number=00012 name=/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf

input datafile file number=00015 name=/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf

input datafile file number=00014 name=/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_free_1163844522_154_1_4q2ltmta_154_1_1.bck tag=TAG20240317T100805 comment=NONE

channel disk1: backup set complete, elapsed time: 00:00:15

channel disk1: starting full datafile backup set

channel disk1: specifying datafile(s) in backup set

input datafile file number=00004 name=/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf

input datafile file number=00002 name=/opt/oracle/oradata/FREE/pdbseed/system01.dbf

input datafile file number=00009 name=/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_free_1163844537_155_1_4r2ltmtp_155_1_1.bck tag=TAG20240317T100805 comment=NONE

channel disk1: backup set complete, elapsed time: 00:00:07

Finished backup at 17-MAR-24

 

Starting backup at 17-MAR-24

current log archived

channel disk1: starting archived log backup set

channel disk1: specifying archived log(s) in backup set

input archived log thread=1 sequence=12 RECID=4 STAMP=1163844545

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_free_1163844545_156_1_4s2ltmu1_156_1_1.bck tag=TAG20240317T100905 comment=NONE

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

Finished backup at 17-MAR-24

 

Starting Control File and SPFILE Autobackup at 17-MAR-24

piece handle=/opt/oracle/product/23c/dbhomeFree/dbs/c-1408182090-20240317-01 comment=NONE

Finished Control File and SPFILE Autobackup at 17-MAR-24

released channel: disk1

RMAN>

 

Case 2 : RMAN Backup of Root Container

[oracle@localhost db_backup]$ rman target /

Recovery Manager: Release 23.0.0.0.0 - Developer-Release on Sun Mar 17 10:10:08 2024

Version 23.2.0.0.0

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

connected to target database: FREE (DBID=1408182090)

RMAN> run

{

allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_ROOT_%t_%s_%p_%U.bck';

BACKUP DATABASE ROOT;

release channel disk1;

}run

2> {

3> allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_ROOT_%t_%s_%p_%U.bck';

4> BACKUP DATABASE ROOT;

5> release channel disk1;

6> 

}

using target database control file instead of recovery catalog

allocated channel: disk1

channel disk1: SID=60 device type=DISK

Starting backup at 17-MAR-24

channel disk1: starting full datafile backup set

channel disk1: specifying datafile(s) in backup set

input datafile file number=00016 name=/opt/oracle/oradata/FREE/undotbs2.dbf

input datafile file number=00001 name=/opt/oracle/oradata/FREE/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/FREE/sysaux01.dbf

input datafile file number=00007 name=/opt/oracle/oradata/FREE/users01.dbf

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_ROOT_1163844660_158_1_4u2ltn1k_158_1_1.bck tag=TAG20240317T101059 comment=NONE

channel disk1: backup set complete, elapsed time: 00:00:35

Finished backup at 17-MAR-24

 

Starting Control File and SPFILE Autobackup at 17-MAR-24

piece handle=/opt/oracle/product/23c/dbhomeFree/dbs/c-1408182090-20240317-02 comment=NONE

Finished Control File and SPFILE Autobackup at 17-MAR-24

released channel: disk1

RMAN> 

 

Case 3: RMAN Backup of Pluggable Database

[oracle@localhost db_backup]$ rman target /

Recovery Manager: Release 23.0.0.0.0 - Developer-Release on Sun Mar 17 10:17:26 2024

Version 23.2.0.0.0

 

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

 

connected to target database: FREE (DBID=1408182090)

 

RMAN> run

{

allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_freepdb1_%t_%s_%p_%U.bck';

BACKUP PLUGGABLE DATABASE freepdb1;

release channel disk1;

}run

2> {

3> allocate channel disk1 type disk format '/opt/oracle/db_backup/full_backup_freepdb1_%t_%s_%p_%U.bck';

4> BACKUP PLUGGABLE DATABASE freepdb1;

5> release channel disk1;

6> 

}

using target database control file instead of recovery catalog

allocated channel: disk1

channel disk1: SID=294 device type=DISK

 

Starting backup at 17-MAR-24

channel disk1: starting full datafile backup set

channel disk1: specifying datafile(s) in backup set

input datafile file number=00013 name=/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf

input datafile file number=00012 name=/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf

input datafile file number=00015 name=/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf

input datafile file number=00014 name=/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf

channel disk1: starting piece 1 at 17-MAR-24

channel disk1: finished piece 1 at 17-MAR-24

piece handle=/opt/oracle/db_backup/full_backup_freepdb1_1163845061_160_1_502ltne5_160_1_1.bck tag=TAG20240317T101740 comment=NONE

channel disk1: backup set complete, elapsed time: 00:00:15

Finished backup at 17-MAR-24

Starting Control File and SPFILE Autobackup at 17-MAR-24

piece handle=/opt/oracle/product/23c/dbhomeFree/dbs/c-1408182090-20240317-03 comment=NONE

Finished Control File and SPFILE Autobackup at 17-MAR-24

released channel: disk1

RMAN> 

Read More

Read Only Oracle user in Oracle 23c database

In earlier releases of Oracle database editions, we can see common users, local users, or normal users, to make them read-only, we had to create special read-only roles and grant those to users, but with Oracle 23c, you can create special read-only users and can convert them to read write as well whenever required.

This read-only user can only perform select operations and completely restrict write operations

In the below example we will create read-only users in oracle 23c database and try to perform different operations 

SQL> conn sys/oracle@FREEPDB1 as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> show con_name

 

CON_NAME

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

FREEPDB1

SQL> 

use the below statement to create a read-only user in the Oracle 23c database and grant DBA privileges to the user

 

SQL> create user technodb identified by tech123 quota unlimited on users read only;

 

User created.

 

SQL> set lines 200 pages 2000 

SQL> col username for a20

SQL>select username, read_only from dba_users where username='TECHNODB';

USERNAME       REA

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

TECHNODB       YES

 

SQL> grant dba to technodb;

 

Grant succeeded.

 

Now we will try to create table with account

SQL>conn technodb/tech123@freepdb1

 

SQL> create table tab1 (id integer);

create table tab1 (id integer)

*

ERROR at line 1:

ORA-28194: Can perform read operations only

 

 

To convert read only user to read write, we can use below option 

SQL> alter user technodb read write;

 

User altered.

 

SQL> 

SQL> select username, read_only from dba_users where username='TECHNODB';

 

USERNAME       READ_ONLY

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

TECHNODB       NO

 

 

Now you can see you will be able to perform write operations as well

 

SQL> conn technodb/tech123@freepdb1

Connected.

SQL> 

SQL> create table tab1(id integer);

 

Table created.

 

SQL> 

 

 One thing to note that, read-only users can execute procedures that does not perform any DML/DDL statements in it.

 

 

Read More

ORA-12514: Cannot connect to database. Service FREE is not registered with the listener at host 127.0.0.1 port 1521

I was getting the below error while starting up the Oracle 23c database, which is a new release from Oracle.

The same error was getting repeated while starting and shutting down. 

 

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

ERROR:

ORA-12514: Cannot connect to database. Service FREE is not registered with the

listener at host 127.0.0.1 port 1521. (CONNECTION_ID=+wf/LAyCEVfgUwEAAH9uOg==)

Warning: You are no longer connected to ORACLE.

 

[oracle@localhost Desktop]$ sqlplus sys@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat May 6 14:19:05 2023

Version 23.2.0.0.0

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

Enter password:

ERROR:

ORA-12514: Cannot connect to database. Service FREE is not registered with the

listener at host 127.0.0.1 port 1521. (CONNECTION_ID=+wgWspJwEdfgUwEAAH9dmQ==)

Enter user-name: sys

Enter password:

ERROR:

ORA-12514: Cannot connect to database. Service FREEPDB1 is not registered with

the listener at host 127.0.0.1 port 1521.

(CONNECTION_ID=+wgWspJxEdfgUwEAAH9dmQ==)

Enter user-name: ^C

 

Below is the solution to resolve this error, there is one parameter called TWO_TASK, which we need to unset

 

[oracle@localhost Desktop]$ echo $ORACLE_SID

FREE

[oracle@localhost Desktop]$ echo $TWO_TASK

FREEPDB1

[oracle@localhost Desktop]$ unset TWO_TASK

 

Once you unset this parameter, connect using sqlplus as sysdba and try to start the database 

 

[oracle@localhost Desktop]$ sqlplus

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat May 6 14:23:16 2023

Version 23.2.0.0.0

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL>

SQL> startup mount  

ORACLE instance started.

Total System Global Area 1608409424 bytes

Fixed Size                             10043728 bytes

Variable Size                       671088640 bytes

Database Buffers               922746880 bytes

Redo Buffers                        4530176 bytes

Database mounted.

SQL>

In this way, there is the solution for everything in oracle database

Read More