ORACLE DATABASE PROBLEM AND SOLUTIONS

Wednesday, May 21, 2025

How to backup users tablespace from cdb and pdb oracle database ?

Backup users tablespace from cdb and pdb oracle database

[oracle@orcl dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 12:53:49 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN> report schema ;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name DB1

List of Permanent Datafiles

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

File Size(MB) Tablespace RB segs Datafile Name

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

1 900 SYSTEM YES /u01/app/oracle/oradata/DB1/system01.dbf

3 630 SYSAUX NO /u01/app/oracle/oradata/DB1/sysaux01.dbf

4 275 UNDOTBS1 YES /u01/app/oracle/oradata/DB1/undotbs01.dbf

5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/DB1/pdbseed/system01.dbf

6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/DB1/pdbseed/sysaux01.dbf

7 5 USERS NO /u01/app/oracle/oradata/DB1/users01.dbf

8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/DB1/pdbseed/undotbs01.dbf

9 270 PDB:SYSTEM NO /u01/app/oracle/oradata/DB1/pdb/system01.dbf

10 340 PDB:SYSAUX NO /u01/app/oracle/oradata/DB1/pdb/sysaux01.dbf

11 100 PDB:UNDOTBS1 NO /u01/app/oracle/oradata/DB1/pdb/undotbs01.dbf

12 5 PDB:USERS NO /u01/app/oracle/oradata/DB1/pdb/users01.dbf

16 270 DEMO1:SYSTEM NO /u01/app/oracle/oradata/demo1/system01.dbf

17 340 DEMO1:SYSAUX NO /u01/app/oracle/oradata/demo1/sysaux01.dbf

18 100 DEMO1:UNDOTBS1 NO /u01/app/oracle/oradata/demo1/undotbs01.dbf

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 131 TEMP 32767 /u01/app/oracle/oradata/DB1/temp01.dbf

2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/DB1/pdbseed/temp012025-03-06_12-16-29-897-PM.dbf

3 128 PDB:TEMP 32767 /u01/app/oracle/oradata/DB1/pdb/temp01.dbf

4 128 DEMO1:TEMP 32767 /u01/app/oracle/oradata/demo1/temp012025-03-06_12-16-29-897-PM.dbf

RMAN>

RMAN> backup tablespace pdb:users, users;

Starting backup at 21-MAY-25

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 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=00012 name=/u01/app/oracle/oradata/DB1/pdb/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/2FACECCE69615207E0630400040AB366/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T125551_n2vmbqvv_.bkp tag=TAG20250521T125551 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/DB1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T125551_n2vmbsgr_.bkp tag=TAG20250521T125551 comment=NONE

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

Finished backup at 21-MAY-25

Starting Control File and SPFILE Autobackup at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201697756_n2vmbyk4_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 21-MAY-25

RMAN>

How to set the date and time format for RMAN

setting the date and time format for RMAN

You can set the date and time format for RMAN output using the NLS_DATE_FORMAT and NLS_LANG environment variables before starting RMAN.

For Unix/Linux:

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

rman target /

For Windows (Command Prompt):

set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

rman target /

[oracle@orcl dbs]$

[oracle@orcl dbs]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

[oracle@orcl dbs]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@orcl dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 12:41:50 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

1 Full 17.95M DISK 00:00:01 2025-03-06 12:58:33

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20250306T125831

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_03_06/o1_mf_s_1195045112_mwm6zsr4_.bkp

SPFILE Included: Modification time: 2025-03-06 12:48:41

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2142808 Ckp time: 2025-03-06 12:58:32

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

2 Full 17.95M DISK 00:00:01 2025-04-29 07:01:08

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20250429T070107

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689267_n10y9nxy_.bkp

SPFILE Included: Modification time: 2025-04-29 05:55:51

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2493621 Ckp time: 2025-04-29 07:01:07

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

3 Full 17.95M DISK 00:00:02 2025-04-29 07:11:13

BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20250429T071111

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689871_n10ywkbc_.bkp

SPFILE Included: Modification time: 2025-04-29 05:55:51

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2494671 Ckp time: 2025-04-29 07:11:11

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4 Full 17.95M DISK 00:00:02 2025-04-29 07:21:18

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20250429T072116

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199690476_n10zhg00_.bkp

SPFILE Included: Modification time: 2025-04-29 07:14:43

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2495971 Ckp time: 2025-04-29 07:21:16

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

5 Full 17.95M DISK 00:00:02 2025-05-21 10:39:40

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20250521T103938

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201689578_n2vccdph_.bkp

SPFILE Included: Modification time: 2025-05-21 10:19:41

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2919395 Ckp time: 2025-05-21 10:39:38

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

6 Full 1.21G DISK 00:00:38 2025-05-21 12:15:41

BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20250521T121502

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vjy7y0_.bkp

List of Datafiles in backup set 6

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

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

1 Full 2923534 2025-05-21 12:15:03 NO /u01/app/oracle/oradata/DB1/system01.dbf

3 Full 2923534 2025-05-21 12:15:03 NO /u01/app/oracle/oradata/DB1/sysaux01.dbf

4 Full 2923534 2025-05-21 12:15:03 NO /u01/app/oracle/oradata/DB1/undotbs01.dbf

7 Full 2923534 2025-05-21 12:15:03 NO /u01/app/oracle/oradata/DB1/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

7 Full 474.72M DISK 00:00:11 2025-05-21 12:15:59

BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20250521T121502

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/2FACECCE69615207E0630400040AB366/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vjzns9_.bkp

List of Datafiles in backup set 7

Container ID: 3, PDB Name: PDB

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

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

9 Full 2627500 2025-04-30 06:56:07 NO /u01/app/oracle/oradata/DB1/pdb/system01.dbf

10 Full 2627500 2025-04-30 06:56:07 NO /u01/app/oracle/oradata/DB1/pdb/sysaux01.dbf

11 Full 2627500 2025-04-30 06:56:07 NO /u01/app/oracle/oradata/DB1/pdb/undotbs01.dbf

12 Full 2627500 2025-04-30 06:56:07 NO /u01/app/oracle/oradata/DB1/pdb/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

8 Full 481.67M DISK 00:00:10 2025-05-21 12:16:24

BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20250521T121502

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/33E6395D57264163E0630400040A2C41/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vk0g8j_.bkp

List of Datafiles in backup set 8

Container ID: 4, PDB Name: DEMO1

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

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

16 Full 2915005 2025-05-21 10:08:05 NO /u01/app/oracle/oradata/demo1/system01.dbf

17 Full 2915005 2025-05-21 10:08:05 NO /u01/app/oracle/oradata/demo1/sysaux01.dbf

18 Full 2915005 2025-05-21 10:08:05 NO /u01/app/oracle/oradata/demo1/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

9 Full 553.92M DISK 00:00:21 2025-05-21 12:17:00

BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20250521T121502

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/2FAC79D13BBE3BB3E0630400040A98FA/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vk17nv_.bkp

List of Datafiles in backup set 9

Container ID: 2, PDB Name: PDB$SEED

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

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

5 Full 2137894 2025-03-06 12:44:14 NO /u01/app/oracle/oradata/DB1/pdbseed/system01.dbf

6 Full 2137894 2025-03-06 12:44:14 NO /u01/app/oracle/oradata/DB1/pdbseed/sysaux01.dbf

8 Full 2137894 2025-03-06 12:44:14 NO /u01/app/oracle/oradata/DB1/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

10 Full 17.95M DISK 00:00:02 2025-05-21 12:17:06

BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20250521T121704

Piece Name: /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201695424_n2vk22pc_.bkp

SPFILE Included: Modification time: 2025-05-21 10:55:24

SPFILE db_unique_name: DB1

Control File Included: Ckp SCN: 2923603 Ckp time: 2025-05-21 12:17:04

RMAN>

How to configure rman configuration settings ?

Configure and clear rman configuration settings

You can configure RMAN settings to control how backups are performed and managed. These settings are persistent and stored in the database. Here are some common RMAN configuration commands:

1. View Current Configuration

RMAN> SHOW ALL;

2. Set Default Backup Destination

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

3. Set Backup Retention Policy

By Recovery Window (e.g., 7 days):

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

By Redundancy (e.g., 2 copies):

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

4. Configure Control File Autobackup

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

5. Set Backup Location

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/%U.bkp';

6. Configure Compression

RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC';

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

7. Clear a Configuration Setting

RMAN> CONFIGURE RETENTION POLICY CLEAR;

How to run sql commands from rman prompt ?

run sql commands from rman prompt

[oracle@orcl dbs]$

[oracle@orcl dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 12:22:35 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN> select dbid , name ,open_mode from v$database;

using target database control file instead of recovery catalog

DBID NAME OPEN_MODE

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

1788905834 DB1 READ WRITE

RMAN>

*Note - not all sql commands will run from rman prompt

Backup Oracle database using rman

Backup an Oracle database using RMAN

1. Connect to RMAN

Open a terminal or command prompt and connect to RMAN as a user with the necessary privileges:

rman target /

2. Perform a Full Database Backup

At the RMAN prompt, run:

BACKUP DATABASE;

3. (Optional) Backup Archive Logs

To include archived redo logs in your backup:

BACKUP DATABASE PLUS ARCHIVELOG;

4. (Optional) Specify Backup Location

To specify a different backup location:

BACKUP DATABASE FORMAT '/backup/yourdb_%U.bkp';

5. (Optional) Backup Control File Separately

BACKUP CURRENT CONTROLFILE;

Note:

Make sure your database is in ARCHIVELOG mode for point-in-time recovery.

Schedule regular backups as per your organization’s policy.

Example

RMAN> backup database ;

Starting backup at 21-MAY-25

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 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=00001 name=/u01/app/oracle/oradata/DB1/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/DB1/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DB1/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/DB1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vjy7y0_.bkp tag=TAG20250521T121502 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/DB1/pdb/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/DB1/pdb/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/DB1/pdb/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/DB1/pdb/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/2FACECCE69615207E0630400040AB366/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vjzns9_.bkp tag=TAG20250521T121502 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00017 name=/u01/app/oracle/oradata/demo1/sysaux01.dbf

input datafile file number=00016 name=/u01/app/oracle/oradata/demo1/system01.dbf

input datafile file number=00018 name=/u01/app/oracle/oradata/demo1/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/33E6395D57264163E0630400040A2C41/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vk0g8j_.bkp tag=TAG20250521T121502 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/DB1/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/DB1/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/DB1/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 21-MAY-25

channel ORA_DISK_1: finished piece 1 at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/2FAC79D13BBE3BB3E0630400040A98FA/backupset/2025_05_21/o1_mf_nnndf_TAG20250521T121502_n2vk17nv_.bkp tag=TAG20250521T121502 comment=NONE

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

Finished backup at 21-MAY-25

Starting Control File and SPFILE Autobackup at 21-MAY-25

piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201695424_n2vk22pc_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 21-MAY-25

RMAN>

Recovery Window and Redundancy in RMAN

In Oracle RMAN (Recovery Manager), the recovery window is a key concept used to define your backup retention policy. It specifies the period of time (in days) for which you want to be able to recover your database to any point in time. RMAN uses this window to determine which backups and archived logs to retain and which can be deleted.

What is the Recovery Window?

The recovery window is the number of days into the past that you want your database to be recoverable.

For example, if you set a recovery window of 7 days, RMAN will keep all backups and archived logs needed to restore and recover the database to any point within the last 7 days.

How Recovery Window Works?

RMAN considers a backup as obsolete if it is no longer needed to satisfy the recovery window.

When you run DELETE OBSOLETE, RMAN removes backups and archived logs that are outside the recovery window.

Setting the Recovery Window

You set the recovery window using the CONFIGURE RETENTION POLICY command:

This tells RMAN to retain all backups and archived logs required for point-in-time recovery within the last 7 days.

Example Scenario

Suppose today is May 21, and you set a recovery window of 7 days.

RMAN will keep all backups and archived logs needed to recover the database to any point between May 14 and May 21.

Any backups or logs older than May 14 that are not needed for recovery within this window are considered obsolete.

Checking the Retention Policy

You can check your current retention policy with:

RMAN> SHOW RETENTION POLICY;

Deleting Obsolete Backups

To remove backups and archived logs that are outside the recovery window, use:

RMAN> DELETE OBSOLETE;

Recovery Window vs. Redundancy

Recovery Window: Retains backups based on time (e.g., last 7 days).

Redundancy: Retains a specific number of backup copies (e.g., last 2 backups).

You can only have one retention policy active at a time (either recovery window or redundancy).

Best Practices

Set the recovery window based on your business requirements for point-in-time recovery.

Regularly run DELETE OBSOLETE to free up storage.

Monitor backup and recovery operations to ensure compliance with your retention policy.

Multiple ways to connect with rman - Oracle database

Multiple ways to connect with rman to oracle database

1->

[oracle@orcl dbs]$

[oracle@orcl dbs]$ rman target "' / as sysdba'"

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 11:38:04 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN>

2->

[oracle@orcl dbs]$

[oracle@orcl dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 11:38:26 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN>

3->

[oracle@orcl dbs]$

[oracle@orcl dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 11:38:26 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN>

4->

[oracle@orcl dbs]$

[oracle@orcl dbs]$ rman target sys/sys

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 21 11:42:47 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN>