CDB & PDB Recovery - Oracle Multitenant Recovery - RMAN Restore & Recovery
CDB & PDB Recovery:
Cloning a Pluggable Database from an RMAN Container Database Backup
Recovery:
Container Database (CDB) Complete/Full Recovery:
$ rman target=/
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
Root Container Complete/Full 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;
}
Pluggable Database (PDB) Recovery Scenario:
rman target /
RMAN> RUN {
ALTER PLUGGABLE DATABASE PDB1, PDB2 CLOSE;
RESTORE PLUGGABLE DATABASE PDB1, PDB2;
RECOVER PLUGGABLE DATABASE PDB1, PDB2;
ALTER PLUGGABLE DATABASE PDB1, PDB2 OPEN;
}
$ sqlplus sys@PDB1 as sysdba
SQL> CREATE USER shaaban IDENTIFIED BY shaaban;
SQL> GRANT CREATE SESSION, PDB_DBA, SYSDBA TO shaaban;
rman target=shaaban@PDB1
rman target=sys@PDB1
RMAN> SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;
$ sqlplus sys@PDB2 as sysdba
SQL> CREATE USER shaaban IDENTIFIED BY shaaban;
SQL> GRANT CREATE SESSION, PDB_DBA, SYSDBA TO shaaban;
rman target=shaaban@PDB2
rman target=sys@PDB2
RMAN> SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;
Tablespace Recovery of PDB:
rman target=sys@PDB1
RMAN> RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}
Tablespace Recovery of CDB:
rman target=sys@CDBDB
RMAN> RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}
Datafile Recovery of a PDB:
rman target=sys@PDB1
RMAN> RUN {
ALTER DATABASE DATAFILE 12 OFFLINE;
RESTORE DATAFILE 12;
RECOVER DATAFILE 12;
ALTER DATABASE DATAFILE 12 ONLINE;
}
Datafile Recovery of a CDB:
rman target=sys@CDBDB
RMAN> RUN {
ALTER DATABASE DATAFILE 7 OFFLINE;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
ALTER DATABASE DATAFILE 7 ONLINE;
}
New Feature:
New SYSBACKUP privilege in RMAN 12c
create common user in CDB with sysbackup privilege.
SQL> create user c##dba_backup identified by test123;
SQL> grant sysbackup to c##dba_backup;
Backup whole database using sysbackup privilege
[oracle@rac1 ~]$ rman target '"c##dba_backup@CDBDB as sysbackup"'
RMAN> backup database;
export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
rman target=sys/SysPassword1@cdb1 auxiliary=sys/SysPassword1@cdb3 <<EOF
DUPLICATE PLUGGABLE DATABASE pdb1 AS pdb2 TO cdb3
FROM ACTIVE DATABASE
SECTION SIZE 400M;
exit;
EOF
Logs:
[root@oraclelab1 ~]# ps -ef|grep smon
oracle 2533 1 0 Jun09 ? 00:00:01 ora_smon_CDBDB
root 9047 8869 0 22:48 pts/0 00:00:00 grep --color=auto smon
[root@oraclelab1 ~]# su - oracle
Last login: Fri Jun 10 10:48:59 IST 2022 on pts/0
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [oracle] ? CDBDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ env |grep ORA
ORACLE_SID=CDBDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$ rman target /
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CDBDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_CDBDB.f'; # default
RMAN> list backup;
List of Backup Sets
===================
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
LOST_WRITE_PROTECT VARCHAR2(7)
SQL> set pages 1000 lines 1000
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL>
SQL> select FILE_ID, FILE_NAME from dba_data_files;
FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 /u01/app/oracle/oradata/CDBDB/datafile/o1_mf_users_kb1s8x6q_.dbf
4 /u01/app/oracle/oradata/CDBDB/datafile/o1_mf_undotbs1_kb1s8w3g_.dbf
1 /u01/app/oracle/oradata/CDBDB/datafile/o1_mf_system_kb1s6zv5_.dbf
3 /u01/app/oracle/oradata/CDBDB/datafile/o1_mf_sysaux_kb1s830r_.dbf
SQL> alter session set container=PDB1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select FILE_ID, FILE_NAME from dba_data_files;
FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 /u01/app/oracle/oradata/CDBDB/E0F4BE2C2A70647BE053B404260A606F/datafile/o1_mf_system_kb1t1415_.dbf
10 /u01/app/oracle/oradata/CDBDB/E0F4BE2C2A70647BE053B404260A606F/datafile/o1_mf_sysaux_kb1t141f_.dbf
11 /u01/app/oracle/oradata/CDBDB/E0F4BE2C2A70647BE053B404260A606F/datafile/o1_mf_undotbs1_kb1t141g_.dbf
12 /u01/app/oracle/oradata/CDBDB/E0F4BE2C2A70647BE053B404260A606F/datafile/o1_mf_users_kb1t1cnh_.dbf
SQL> alter session set container=PDB2;
Session altered.
SQL> select FILE_ID, FILE_NAME from dba_data_files;
FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 /u01/app/oracle/oradata/CDBDB/E0F4CDBF489E6715E053B404260A1371/datafile/o1_mf_system_kb1t99cn_.dbf
14 /u01/app/oracle/oradata/CDBDB/E0F4CDBF489E6715E053B404260A1371/datafile/o1_mf_sysaux_kb1t99co_.dbf
15 /u01/app/oracle/oradata/CDBDB/E0F4CDBF489E6715E053B404260A1371/datafile/o1_mf_undotbs1_kb1t99co_.dbf
16 /u01/app/oracle/oradata/CDBDB/E0F4CDBF489E6715E053B404260A1371/datafile/o1_mf_users_kb1t99cp_.dbf
SQL> exit
[oracle@oraclelab1 ~]$ rman target=sys@PDB1
RMAN> ALTER DATABASE DATAFILE 12 OFFLINE;
using target database control file instead of recovery catalog
Statement processed
RMAN> RESTORE DATAFILE 12;
RMAN> RECOVER DATAFILE 12;
RMAN> ALTER DATABASE DATAFILE 12 ONLINE;
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ rman target=sys@CDBDB
Recovery Manager: Release 19.0.0.0.0
RMAN> ALTER DATABASE DATAFILE 7 OFFLINE;
RMAN> RESTORE DATAFILE 7;
Starting restore at 10-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK
RMAN> RECOVER DATAFILE 7;
RMAN> ALTER DATABASE DATAFILE 7 ONLINE;
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ rman target=sys@PDB1
target database Password:
connected to target database: CDBDB:PDB1 (DBID=2604719255)
RMAN> RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}2> 3> 4> 5> 6>
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ rman target=sys@CDBDB
RMAN> RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
Statement processed
Starting restore at 10-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ rman target=sys@PDB1
RMAN> SHUTDOWN IMMEDIATE;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN>
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [CDBDB] ? CDBDB
The Oracle base has been changed from /home/oracle to /u01/app/oracle
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> exit
[oracle@oraclelab1 ~]$ rman target /
RMAN> ALTER PLUGGABLE DATABASE PDB1, PDB2 CLOSE;
using target database control file instead of recovery catalog
Statement processed
RMAN>
RMAN> RESTORE PLUGGABLE DATABASE PDB1, PDB2;
RMAN> RECOVER PLUGGABLE DATABASE PDB1, PDB2;
RMAN> ALTER PLUGGABLE DATABASE PDB1, PDB2 OPEN;
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ rman target=/
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
Total System Global Area 3707763808 bytes
Fixed Size 9141344 bytes
Variable Size 721420288 bytes
Database Buffers 2969567232 bytes
Redo Buffers 7634944 bytes
RMAN> RESTORE DATABASE ROOT;
RMAN> RECOVER DATABASE ROOT;
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ rman target=/
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
Total System Global Area 3707763808 bytes
Fixed Size 9141344 bytes
Variable Size 721420288 bytes
Database Buffers 2969567232 bytes
Redo Buffers 7634944 bytes
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN>
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [oracle] ? CDBDB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>