Below points are demonstrated in this document :
1. Performing RMAN Whole CDB Backup
2. Performing RMAN Whole and Partial PDB Backup
3. Performing RMAN Recovery from SYSTEM PDB Datafile Loss
4. Performing RMAN Recovery from non-SYSTEM CDB Datafile Loss
5. Performing PITR in a Pluggable Database
6. Performing PITR on a Tablespace in a PDB
7. Performing Flashback for the CDB from Dropping a Common User
8. Performing Flashback for PDBs
9. Using Data Recovery Advisory commands
Performing RMAN Whole CDB Backup:
Run RMAN and connect as target to CDB1 and make the configurations as shown below.
[oracle@multisrv1 ~]$ ps -ef|grep pmon
grid 4833 1 0 06:19 ? 00:00:00 asm_pmon_+ASM
oracle 4935 1 0 06:19 ? 00:00:00 ora_pmon_CDB1
oracle 5794 5742 0 06:31 pts/0 00:00:00 grep pmon
[oracle@multisrv1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 24 06:31:57 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=948697708)
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
2. Take backup of all the datafiles of the database (the root and all its PDBs),
control files, SPFILE file, and the archived redo log files.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
3. List the backupset files generated by RMAN.
RMAN> LIST BACKUP ;
Performing RMAN Whole and Partial PDB Backup
1. Login to RMAN and take a whole PDB backup of PDB2.
[oracle@multisrv1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 24 06:31:57 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=948697708)
RMAN> BACKUP PLUGGABLE DATABASE pdb2;
2. List the backupset files generated by RMAN as a backup to PDB2.
RMAN> list backup of pluggable database PDB2;
3. Login to RMAN again and take a backup of the tablespace users in PDB2 (partial backup).
rman target /
BACKUP TABLESPACE pdb2:users;
LIST BACKUP OF TABLESPACE pdb2:users;
Performing RMAN Recovery from SYSTEM PDB Datafile Loss
Recovery procedures in CDB database is pretty much similar to them in non-CDB database.
Note: If the PDB was opened when the file is lost, you need to shutdown CDB and mount it before you proceed
with the recovery procedure. The recovery procedure is similar to the traditional recovery procedure from
losing a SYSTEM datafile in the non-CDB database.
Run RMAN and connect to CDB1 and proceed with the traditional procedure to restore the missing SYSTEM data file.
-- mount the CDB
rman target /
RMAN> SHUTDOWN ABORT
RMAN> STARTUP MOUNT
-- execute the following commands:
RMAN> RESTORE TABLESPACE pdb2:SYSTEM;
RMAN> RECOVER TABLESPACE pdb2:SYSTEM;
-- OR the following commands:
RMAN> RESTORE pluggable database pdb2;
RMAN> RECOVER pluggable database pdb2;
RMAN> ALTER DATABASE OPEN;
RMAN> SELECT NAME, OPEN_MODE FROM V$PDBS WHERE NAME='PDB2';
Performing RMAN Recovery from non-SYSTEM Root Datafile Loss
1. Perform the recovery of a non-essential (non-SYSTEM) datafile in CDB1.
We will use the Data Recovery Advisory commands to discover, restore and recover the failure.
2. Run RMAN and connect to CDB1 as target. Discover the failure.
rman target /
RMAN> LIST FAILURE;
RMAN> LIST FAILURE DETAIL;
3. Obtain the recommendation from the Data Recovery Advisor to remediate the issue.
RMAN> ADVISE FAILURE;
3. Preview the suggested script to fix the issue.
RMAN> REPAIR FAILURE PREVIEW;
4. Execute the script.
When it prompts for confirmation, type YES then press ENTER.
RMAN> REPAIR FAILURE;
Note: in real life scenario, it is always advisable to take backup of the entire CDB after such a recovery procedure is implemented.
• There is not much difference between using RMAN to take backups in a CDB database and a non-CDB database. The recovery procedure is also nearly the same.
• The Data Recovery Advisor provides an easy approach to discover and restore from datafiles loss.
Point-in-time Recovery
Performing PITR in a Pluggable Database
1. Make sure the local undo mode is enabled.
sqlplus / as sysdba
col PROPERTY_NAME format a25
col PROPERTY_VALUE format a10
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
2. Perform the PDB PITR on PDB2.
Run rman and connect as target to the CDB and close PDB2
rman target /
ALTER PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE;
3. Perform the PDB PITR on PDB2.
Note that this recovery is at the PDB level. The other containers are not affected.
RUN {
SET UNTIL SCN= <scn>;
RESTORE PLUGGABLE DATABASE pdb2;
RECOVER PLUGGABLE DATABASE pdb2;
}
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;
4. Verify that the data has been restored in the table.
sqlplus SHANOJUSER/oracle@pdb2
SELECT COUNT(*) FROM SHANOJUSER.TB1;
for testing I inserted some value to table TB1
5. Take backup of the whole CDB. This is recommended after every PITR process.
rman target /
BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
Performing PITR on a Tablespace in a PDB
We will perform PITR in the tablespace level. This method provides higher availability than PITR at the pluggable database level because the other tablespaces will still be active while you are recovering the lost tablespace. However, it involves creating temporary (auxiliary) instance. Therefore, it takes longer time to execute and requires much more disk space than the PITR at the PDB level.
1. Verify that the table TB1 which is owned by SHANOJUSER is saved in the users tablespace.
sqlplus SHANOJUSER/oracle@pdb2
SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TB1';
2. As the users tablespace is the default tablespace, you need to switch the default tablespace of the PDB to some other tablespace before you proceed with the PITR procedure on it.
Create a staging tablespace and set it as the default tablespace.
conn / as sysdba
ALTER SESSION SET CONTAINER=pdb2;
-- verify the default tablespace is users tablespace
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
-- verify the users tablespace is online:
SELECT STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='USERS';
-- create a staging tablespace and set it as the default tablespace:
CREATE TABLESPACE stagingtbs;
ALTER DATABASE DEFAULT TABLESPACE stagingtbs;
3. Take backup of the whole CDB. This is among the best practices after adding or dropping a datafile in the database.
rman target /
DELETE BACKUPSET;
BACKUP DATABASE;
4. Delete the existing rows in the table SHANOJUSER.TB1 then insert some testing rows into it.
sqlplus SHANOJUSER/oracle@pdb2
DELETE SHANOJUSER.TB1;
COMMIT;
BEGIN
FOR I IN 1..10 LOOP
INSERT INTO SHANOJUSER.TB1 (ID,NOTES)
VALUES (I, TO_CHAR(sysdate,'DD-MM-YY HH24:MI'));
END LOOP;
COMMIT;
END;
/
SELECT COUNT(*) FROM SHANOJUSER.TB1;
5. Obtain the current database SCN and take a note of it.
conn / as sysdba
SELECT CURRENT_SCN FROM V$DATABASE;
6. Switch the redo log files so that archived redo log files will be generated.
ALTER SYSTEM SWITCH LOGFILE;
7. Delete the rows in SHANOJUSER.TB1 and commit the deletion.
conn SHANOJUSER/oracle@pdb2
DELETE SHANOJUSER.TB1;
COMMIT;
8. Make the users tablespace offline.
conn / as sysdba
ALTER SESSION SET CONTAINER=PDB2;
ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
9. Perform the PITR at the tablespace users.
Create the directory in which the auxiliary files will be created
ls /home/oracle/backup
mkdir /home/oracle/backup
b. Run rman and connect as target to the CDB
rman target /
10. Perform the PITR of the tablespace users in the database PDB2.
To execute this statement online, this statement will make RMAN create an auxiliary instance from which the users tablespace will be recovered.
Make sure the used directory exists.
RECOVER TABLESPACE pdb2:USERS UNTIL SCN <scc> AUXILIARY DESTINATION='/home/oracle/backup';
11. Make the users tablespace online.
sqlplus / as sysdba
ALTER SESSION SET CONTAINER=PDB2;
ALTER TABLESPACE USERS ONLINE;
12. Verify that the data has been restored in the table.
conn SHANOJUSER/oracle@pdb2
SELECT COUNT(*) FROM SHANOJUSER.TB1;
13. Take backup of the whole CDB. This is recommended after every PITR process.
rman target /
DELETE BACKUPSET;
BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
14. Make the users tablespace back as the default tablespace and drop the staging tablespace.
sqlplus / as sysdba
ALTER SESSION SET CONTAINER=pdb2;
ALTER DATABASE DEFAULT TABLESPACE users;
DROP TABLESPACE stagingtbs INCLUDING CONTENTS AND DATAFILES;
Performing Flashback for the CDB from Dropping a Common User
we will enable database flashback in CDB1. Then we will use the flashback to recover from dropping a common user. Common users are created in the CDB level. To use the flashback to recover from dropping a common user, you must flashback the entire CDB.
1. Create a common user.
sqlplus / as sysdba
CREATE USER C##AJAYUSER1 IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION TO C##AJAYUSER1 CONTAINER=ALL;
2. Make sure the Fast Recovery Area is enabled.
SHOW PARAMETER DB_RECOVERY_FILE_DEST
Enable the flashback in the CDB. It requires CDB restart.
Note: DB restart is not needed.
-- verify the flashback is off:
SELECT FLASHBACK_ON FROM V$DATABASE;
-- enable the flashback
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
3. Obtain the current SCN and take a note of it.
SELECT CURRENT_SCN FROM V$DATABASE;
4. Drop the common user C##AJAYUSER1
-- verify the user exists:
col username format A20
SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS
WHERE USERNAME='C##AJAYUSER1';
DROP USER C##AJAYUSER1 CASCADE;
5. Switch the logfile multiple times to generate flashback logs.
— execute the following statement multiple times:
ALTER SYSTEM SWITCH LOGFILE;
6. Flashback the CDB database up to the obtained SCN.
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO SCN <scn>;
7. Open the database in READ ONLY mode and verify that the common user is recovered.
Note:Opening the CDB in READ ONLY mode does not automatically open the PDBs. You have to manually open them.
ALTER DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;
-- you should see the common user recovered:
SELECT USERNAME, COMMON, CON_ID
FROM CDB_USERS
WHERE USERNAME='C##AJAYUSER1';
8. Open the CDB with RESETLOGS option.
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
9. Take backup of the whole CDB. This is recommended after every time you open the database with RESETLOGS option.
rman target /
DELETE BACKUPSET;
BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
Performing Flashback for PDBs
We will flashback a PDB up to a specific restore point.
1. Create a guaranteed restore point in PDB2.
sqlplus / as sysdba
CREATE RESTORE POINT pre_change FOR PLUGGABLE DATABASE pdb2 GUARANTEE FLASHBACK DATABASE;
col name format a20
SELECT SCN, NAME, CON_ID, PDB_RESTORE_POINT, GUARANTEE_FLASHBACK_DATABASE, CLEAN_PDB_RESTORE_POINT
FROM V$RESTORE_POINT;
2. Delete all the rows in SHANOJUSER.TB1 (in PDB2). This is the destructive operation that you need to recover from.
conn SHANOJUSER/oracle@pdb2
DELETE SHANOJUSER.tb1;
COMMIT;
3. Flashback PDB2 to the restore point.
conn / as sysdba
ALTER PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT pre_change;
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;
4. Verify that the data has been restored in the table.
conn SHANOJUSER/oracle@pdb2
SELECT COUNT(*) FROM SHANOJUSER.TB1;
5. Drop the restore point.
Note: It is important to drop the guaranteed restore points after you finish from the target of creating them. Otherwise, eventually you will run out of space in the FRA.
conn / as sysdba
ALTER SESSION SET CONTAINER = pdb2;
DROP RESTORE POINT pre_change;
6. Take backup of the whole CDB.
rman target /
DELETE BACKUPSET;
BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
Notes:
• You can perform PITR for a single PDB, keeping the CDB and the other PDBs up and running meanwhile.
• It easier to execute a PITR for a PDB when local undo is enabled than when the shared undo is being used.
• You can perform PITR on a tablespace within a PDB, keeping the PDB and the other tablespaces available for the users.
However, this process involves creating an auxiliary instance. Which means longer recovery time and more disk space is needed.
• Flashback PDB is a more efficient recovery way than PITR. However, it is not practically a full replacement to the PITR.
Flashback is best used for recover to a short time period in the past, whereas PITR is best used for recovery to a long time point in the past.
Shanoj 