Showing posts with label Backup and Recovery. Show all posts
Showing posts with label Backup and Recovery. Show all posts

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

      , , ,      No comments   

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> 

Check datapump expdp/impdp job status

      ,      No comments   

 As we learned how to use data pump export/import utilities here, now we will discuss checking the datapump job and taking action on it like terminating the job or resuming, etc.

We will take backup of full database, there is a way to add job name to backup using JOB_NAME parameter, if do not use this parameter, the oracle will automatically generate one job in the database which can be checked under dba_datapump_jobs table or can be checked at server level logs of the expdp/impdp

Below is the command I am using to take export of full database 


expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y


Here,

I have created directory export_dir pointing to a physical location having read write access, from the command prompt, I executed the command to take export of full database


C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.0.0.0

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

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER


While this was running, I opened a new command prompt and logged in to the database and checked view dba_datapump_jobs


SQL> select owner_name, job_mode, operation,job_name from dba_datapump_jobs;

OWNER_NAME  JOB_MODE  OPERATION  JOB_NAME

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

TECHNO_USER  FULL      EXPORT     SYS_EXPORT_FULL_01


here I noted job name SYS_EXPORT_SCHEMA_01 and the same can be found from the export command log


Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

 

Once you get the job name, use the below command to check the status of the export taken using expdp

 

C:\Users\shaik>expdp userid =techno_user@technopdb attach=SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:45:38 2022

Version 19.3.0.0.0

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

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_FULL_01

  Owner: TECHNO_USER

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 19C1A2C6616645CDB516452D40951467

  Start Time: Tuesday, 14 June, 2022 20:44:54

  Mode: FULL

  Instance: technodb

  Max Parallelism: 1

  Timezone: +00:00

  Timezone version: 32

  Endianness: LITTLE

  NLS character set: AL32UTF8

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

     TRACE                 0

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Job heartbeat: 6

  Dump File: C:\DOWNLOADS\ARCH\EXPDP_FULL_DB.DMP

    bytes written: 135,168

Here you can see almost every detail of the export job running. Using the help command, you can get a list of activities you can perform

the job can be killed using the kill immediate command

Export> kill job=immediate;

Are you sure you wish to stop this job ([yes]/no): yes

In this way, you can do a lot of things with export attach command. 

 

ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.

      , ,      No comments   

I was trying to turn on flashback for the oracle database and it was failed with below error 

 

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

 

On further checking I found, database was in noarchivelog mode, this is how we can check archive log mode.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

In order to bring it in archive log mode, I followed steps mentioned here convert database to archive log mode  , once your database is in archive log mode , you can simple turn on flashback without any issues.

Follow below commands to turn on flashback for the database.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select database_name, flashback_on from v$database;

 

DATABASE_NAME        FLASHBACK_ON

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

TECHNODB             YES

 

 

 

Oracle RMAN : Incrementally Updated Backups

      , , ,      No comments   

We have learned about incremental backups and how to make these incremental backups faster. Now we will discuss incrementally updated backups
  • Assume we have very huge size database say around 50-60TB, there are few challenges we may face while performing backup like making backup faster and keeping recovery time as less as possible.
  • Generally recovery is done after restore and restore takes all the time, so we need to concentrate on reducing this restore time.
  • For backup minimization time we have enough option of incremental backups but in order to reduce restore time, we go new Incrementally updated backups
  • Using incrementally updated backup method, RMAN rolls forward the backup taken of an image copy with subsequent incremental backups. With incremental backups updated with all the changes since last incremental level backup
  • Using RMAN, database can be just switched over this updated image copy, rather than restoring them back to original files location
  • Since we are not restoring files, restore will be very very minimum and to recover the same, redo logs will be less and it would be only last incremental backup
  • Thus within few minutes, we will be able to restore and recover the database
  • One of the major condition while using this method is use of large enough FRA (Fast Recovery Area) in order to take full image copy database backup. RMAN will look for updated image at FRA only
Now we will discuss how exactly this works


We can use RUN block to execute set of RMAN commands
RUN
{
RECOVER copy of database
with tag 'tech_db_incr';
BACKUP
incremental level 1
for recover of copy with tag 'tech_db_incr' database;
}

Assuming you running this RUN block daily, here is what happens every day

Day 1 : On first day RECOVER command does nothing , to apply incremental backups a level backup is needed, since there isn't one existing yet BACKUP command will create level 0 image copy of database backup
Day 2 : On second day, as level 0 backup exists the BACKUP command will create level 1 incremental backup. There wont be any work for RECOVER command on second day as well
Day 3 Onward : from third day onward RECOVER command will update the image copy with previous days level 1 incremental backup daily 

To do point in time recovery of this image copy, the available time window is only until level 0 backup, so if we created level 0 backup of database on Aug 6, we can not recover it using incrementally updated backups till Aug 4 or so. 

Categories

Popular Posts