In this demo I will show how to handle different types of RMAN restore and recover in a Multitenant environment.
In a Multitenant environment for cloud supporting data centers we will have container databases on many servers with one to many pluggable databases. These container and pluggable databases will have different database administrators and different SLAs.
I will show how we can recover and restore the container database as a whole or one to many pluggable databases.
CDB = Container Database
PDB = Pluggable Database
Environment
CDB- TESTDBS
PDB- DEV1DB, DEV2DB and TSTDB
Demo
The DBAs name is Jane Doe and she will have access to the database server. She will log in to the server with her own account.
Create OS Group and User
$groupadd backupdba
$useradd -g oinstall -G backupdba jane_doe
$password jane_doe
Create Common User
Common users are database accounts that only exist in a Multitenant environment. Common users are accounts that exist in the root CDB and all PDBs plugged into the CDB. Common users names need to start with C## or c##.
I will create a common user called C##ORADBMAINT which our DBA Jane Doe will have the password to log into the CDB and PDB.
In database 12c there is a new database system privilege called SYSBACKUP. In reviewing the SYSBACKUP database system privilege it has the same system privileges as the database system privilege SYSOPER.
Below you will see CONTAINER=ALL meaning that the statement executes in the root CDB and all PDBs plugged into the CDB.
$sqlplus / as sysdba
SQL>CREATE USER C##ORADBMAINT PROFILE DEFAULT IDENTIFIED BY temp1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK CONTAINER=ALL;
SQL>GRANT CONNECT TO C##ORADBMAINT CONTAINER=ALL;
SQL>GRANT SYSBACKUP TO C##ORADBMAINT CONTAINER=ALL;
CDB and all PDBs have now been restore and recovered. CDB and all PDBs are now open for read write.
In the CDB or PDB we can review who has the sysdba, sysoper or sysbackup privileges granted.
- $sqlplus / as sysdba
- SQL>select * from v$pwfile_users;
Restore and Recover CDB
In this restore we need to recover the CDB due to a full loss of spfile, datafiles, controlfiles, achivelogs and redologs for the CDB and all PDB.
Note: The account that is going to execute the restore must have privileges to write files to the location that the files will be restored.
Startup Nomount
$rman
RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"
RMAN>startup nomount
Restore SPFILE
RMAN>restore spfile from '/orabkup/testdbs/rman/c-2422992650-20130807-00';
Shutdown and Startup Nomount
RMAN shutdown immediate
RMAN>startup nomount
Restore Controlfile
RMAN>restore controlfile from '/orabkup/testdbs/rman/c-2422992650-20130807-00';
Mount Database
RMAN>alter database mount;
Restore Database
RMAN>restore database;
You can see below that datafiles for the CDB and all PDBs are restored.
You can see below that datafiles for the CDB and all PDBs are restored.
You can see below that datafiles for the CDB and all PDBs are restored.
You can see below that datafiles for the CDB and all PDBs are restored.
You can see below that datafiles for the CDB and all PDBs are restored.
Restore is complete
Recover Database
RMAN>recover database;
Open Database
RMAN>alter database open resetlogs;
Check PDBs
After the CDB is open the PDBs are in a mounted state and need to be open.
New feature in Database 12c RMAN is the ability to execute SQL command without the need to have SQL '<statement>'.
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Open PDBs
RMAN>alter pluggable database all open;
Check PDBs
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
CDB and all PDBs have now been restore and recovered. CDB and all PDBs are now open for read write.
Recover and Restore PDB
In this restore we need to recover the PDB TSTDB due to a human error which all datafiles for PDB TSTDB were deleted.
Error from trying to open the PDB
Below you can see the files that need to be recovered
Error from trying to open the PDB
Below you can see the files that need to be recovered
Restore PDB
You can connect to the container or directly to the PDB. In the example below I will be connecting to the CDB TESTDBS.
$rman
RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"
RMAN>restore plugable database tstdb;
The restore will begin as you can see only the datafiles for pluggable database TSTDB are restored.
Recover PDB
RMAN>recover pluggable database tstdb;
Recovery will begin current archivelogs are already on disk so they do not need to be restored.
Check PDB
After the PDB is restored and recovered we must open the PDB for read write.
New feature in Database 12c RMAN is the ability to execute SQL command without the need to have SQL '<statement>'.
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Open PDB
RMAN>alter pluggable database tstdb open;
Check PDB
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
We now see the TSTDB PDB is open for read write.
Recover and Restore PDB Connected Directly to PDB
You may have a need to connect directly to the PDB to perform the recovery maybe for the reason that certain DBAs will support certain PDBs in a CDB.
Restore PDB
$rman
RMAN>connect target "c##oradbmaint/temp1234@tstdb as sysbackup"
RMAN>restore database;
Note: If you get error RMAN-20021 or RMAN-06031 then exit RMAN and reconnect.
Recover PDB
RMAN>recover database;
Open PDB
RMAN>alter database open;
Check PDB
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Report Schema
User report schema to review all tablespace and datafiles.
RMAN>report schema;
Recover PDB Tablespaces
We had another human error and the system and users tablespace datafiles for PDB TSTDB were deleted. We will perform a recover of the tablespaces for the TSTDB PDB.
Error
Report Schema to review the tablespaces needing recovery
You can see that the datafiles for tablespace SYSTEM and USERS in the PDB TSTDB is reporting zero.
Restore PDB Tablespace USERS
Note: PDB must be closed to restore tablespace
$rman
RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
RMAN>restore tablespace tstdb:users;
We see that only the datafile for tablespace USERS in the TSTDB PDB is restored.
Recover PDB Tablespace USERS
RMAN>recover tablespace tstdb:users;
We see some of the archivelog are already on disk.
Restore PDB Tablespace SYSTEM
RMAN>restore tablespace tstdb:system;
We see that only the datafile for tablespace SYSTEM in the TSTDB PDB is restored.
Recover PDB Tablespace SYSTEM
RMAN>recover tablespace tstdb:system;
We see some of the archivelog are already on disk.
The archivelogs that are not on disk are restored and then used to complete the recover.
Open PDB
RMAN>alter pluggable database tstdb open;
Check PDB
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Report Schema
User report schema to review all tablespace and datafiles.
RMAN>report schema;
We can see that the datafiles for tablespace SYSTEM and USERS for PDB TSTDB are now report the total size.
Recover and Restore PDB Tablespaces Connected Directly to PDB
You may have a need to connect directly to the PDB to perform the recovery maybe for the reason that certain DBAs will support certain PDBs in a CDB.
Restore PDB Tablespace USERS
$rman
RMAN>connect target "c##oradbmaint/temp1234@tstdb as sysbackup"
RMAN>restore tablespace users;
We can see that the datafile for tablespace USERS for PDB TSTDB is restored.
Recover PDB Tablespace USERS
RMAN>recover tablespace users;
We see some of the archivelog are already on disk.
Restore PDB Tablespace System
Note: If you get error RMAN-20021 or RMAN-06019 exit RMAN and reconnect directly to PDB.
RMAN>recover tablespace system;
Recover PDB Tablespace SYSTEM
RMAN>recover tablespace users;
We see some of the archivelog are already on disk.
Open PDB
RMAN>alter database open;
Check PDB
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Report Schema
User report schema to review all tablespace and datafiles.
RMAN>report schema;
Restore and Recover Root Container
$rman target /
RMAN>startup mount
RMAN>restore database root;
RMAN>recover database root;
RMAN>alter database open;
RMAN>alter pluggable database all open;
RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;
Restore and Recover Seed Tablespace
$rman target /
Hello, In multitenant arch, when you perform RMAN backup command at CDB level you backup all PDB that are inside the CDB. My question is when you come to restore an individual PDB from the backup, will the process read all the backupset before restoring the single PDB database. Ie is the backupset for CDB and 3 PDB is 12G in size will it read all 12G before restoring the single PDB database or is the architecture aware of exactly where the backup is within the 12G backupset and will go directly and restore the single PDB database
ReplyDeleteThanks
When you are connected directly to the PDB via RMAN you only read the backupsets that are for the PDB only. If you connect to the CDB via RMAN you see the backupsets for all PDB. When you do a list backupset connected to the CDB you will see additional information container ID and PDB name. If you are connected directly to the PDB via RMAN and do a list backupset you do not see container ID or PDB name as you are connected directly to the PDB.
ReplyDeleteOK thanks, So if you have 3 PDB within a CDB and perform backup for all PDB together and it take 3 hours to complete (1 hour each for each PDB), will the restore be just 1 hour (assuming backup time and restore time are the same), or will it be longer. ie when you do a list backup with PDB you see only the backupset for that PDB, but does it still read the complete backupset for all PDB's or just restore from the backupset it see when connected directly to PDB. Also when the backup completes does it create individual backupset for each PDB or is it contained in 1 BIG backupset, thanks
ReplyDeleteIt will be the one hour. From my testing connected directly to the pdb the backupsets are only for the pdb I am connected to. The backup pieces may contain backupsets for more then one pdb but rman knows where the backup pieces for the pdb is localed so this means it does not need to read all the backup piece to to recover one pdb.
ReplyDeleteexcellent, thank you
DeleteIn multitenant architecture, the redo and undo are logical partitioned for each PDB that is within the CDB. You cannot issue “Alter system switch logfile” clause at PDB level only at CDB level, so my questions are
ReplyDeletewhen performing a PIT RMAN recovery for an individual PDB, does this mean it has to read the complete archivelog which contains data for all PDB within the CDB and therefore increasing the time for my to restore the individual PDB? I know that each logical partition within the redo has the CON_ID written into the header, but is that also taken when writing out to the archivelogs and therefore when doing an RMAN recover of archivelogs it will be able to just find the logical partition which belongs to the individual PDB you are trying to recover?
2nd question, what happens when a PDB is more busy then then the other PDB, how does switching occur differently in multitenant architecture and what happens when you are not able to create more logical partition quick enough within the redo?
Thanks
Another question around RMAN 12c recover table command
ReplyDeleteIn a scenario, when you perform a recover table, let say the tablespace that the table sits in is 100G in size and has 4 datafiles, will the recover command restore all 4 datafiles, and therefore read all 4 datafiles to recover the 1 table, or does it know that the table to recover is in datafile 1 of the tablespace and will only restore datafile 1 to perform the recovery?
Thanks
Jat
great work !
ReplyDeletePortable Apps