Backup & Recovery of Pluggable and Container database in 12c & 19C

-

Backup & Recovery of Pluggable and Container database in 12c & 19C


With Oracle 12C onwards ,Oracle have come up with concept of multitenant database and with that there are multiple question with respect to taking backup & recovery of Container and Pluggable database. 

Below the major three backup & recpvery scenario :

1) Backup & Recovery of container and pluggable database.
2) Backup & Recovery of container(root) database.
3) Backup & Recovery of the Pluggable database
4) Backup & Recovery individual tablespace and datafiles in Pluggable database

1) Backup & Recovery of container and pluggable database.

Backup: 

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
sample output
=============
input datafile file number=00001 name=/u01/app/oracle/oradata/cdb1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/cdb1/users01.dbf
...
name=/u01/app/oracle/oradata/cdb1/p saux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf

Recovery:

Restoring a CDB is similar to restoring a non-CDB database,but remember restoring a whole CDB will restore not only the root container, but all the PDBs also. Likewise a Point In Time Recovery (PITR) of the whole CDB will bring all PDBs back to the same point in time.

$ rman target=/
RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN;
}

Sample Output
=============
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cdb1/users01.dbf
...
channel ORA_DISK_1: reading from backup piece channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf


2) Backup of container(root) database.

Backup:

A backup of the root container is a backup of the CDB, excluding any of the PDBs.

$ rman target=/
RMAN> BACKUP DATABASE ROOT;
sample output
=============
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/cdb1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/cdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-13
...
Finished backup at 23-DEC-13
---------------------------------

Recovery:

$ rman target=/
RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE ROOT;
  RECOVER DATABASE ROOT;
  # Consider recovering PDBs before opening.
  ALTER DATABASE OPEN;
}

3) Backup of the Pluggable database

Backup:

There are two ways to back up pluggable databases.When connected to RMAN as the root container, you can backup one or more PDBs using the following command.

$ rman target=/
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;
sample output
=============
input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
...
name=/u01/app/oracle/oradata/pdb2/sysaux01.dbf
input datafile file number=00029 name=/u01/app/oracle/oradata/pdb2/system01.dbf

Alternatively, only that pdb

$ rman target=sys@pdb1
RMAN> BACKUP DATABASE;

Recovery:

There are two ways to restore and recover PDBs. From to root containers, you can restore and recover one or more PDBs using the following script.

$ rman target=/
RUN {
  ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
  RESTORE PLUGGABLE DATABASE pdb1, pdb2;
  RECOVER PLUGGABLE DATABASE pdb1, pdb2;
  ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

When connected directly to a PDB, you can restore and recover the current PDB using a local user with the SYSDBA privilege, as shown in the following script.

$ sqlplus sys@pdb1 as sysdba
CREATE USER admin_user IDENTIFIED BY admin_user;
GRANT CREATE SESSION, PDB_DBA, SYSDBA TO admin_user;
EXIT;

$ rman target=admin_user@pdb1
SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;

Warning : 

The simplicity of PITR of PDBs hides a certain amount of complexity. For a start, a PDB shares the root container with other PDBs, so a PITR of the root container must be performed. This is done in the fast recovery area (FRA) provided it is configured. If the FRA is not configured, an AUXILIARY DESTINATION must be specified.

Table Point in time recovery:

Login root with sysdba or sysbackup privilege

Issue the RECOVER TABLE command and include the OF PLUGGABLE DATABASE clause,
also give a suitable AUXILIARY DESTINATION location for the auxiliary database.
REMAP TABLE clause to give the recovered table a new name.

$ rman target=/

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

Alternatively,  just stop at the point where the recovered table is in a data pump dump file, which you can import manually at a later time. The following example uses the DATAPUMP DESTINATION, DUMP FILE and NOTABLEIMPORT clauses to achieve this.

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux'
  DATAPUMP DESTINATION '/u01/export'
  DUMP FILE 'test_t1_prev.dmp'
  NOTABLEIMPORT;


4) Backup individual tablespace and datafiles in Pluggable database


Backup tablespaces in pluggable database


$ rman target=sys@pdb1
RMAN> BACKUP TABLESPACE system, sysaux, users;

Recovery of tablespace in pluggable database

Due to potential name clashes, restoring a tablespace must be done while connected to the PDB.

$ rman target=sys@pdb1
RUN {
  ALTER TABLESPACE users OFFLINE;
  RESTORE TABLESPACE users;
  RECOVER TABLESPACE users;
  ALTER TABLESPACE users ONLINE;
}

Backup datafile in pluggable database.


Datafiles have unique file numbers and fully qualified names, so they can be backed up from the root container or the individual PDB.

$ rman target=/
# Or
$ rman target=sys@pdb1
RMAN> BACKUP DATAFILE 8, 9, 10;

Recover datafile in pluggable database:


Datafile recoveries can be done while connected to the container or directly to the PDB.
$ rman target=/
# Or
$ rman target=sys@pdb1
RUN {
  ALTER DATABASE DATAFILE 10 OFFLINE;
  RESTORE DATAFILE 10;
  RECOVER DATAFILE 10;
  ALTER DATABASE DATAFILE 10 ONLINE;
}

Please read below books in order to know more about oracle CDB and PDB databases:
Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more