How to use Rman to Restore Of Single PDB in Multitenant to Alternate Server (Doc ID 2142675.1)
GOAL
Goal of this article is to restore a single PDB or subset of PDB's from a multitenant to alternate test server using Rman restore.
In this scenario the customer does not want to touch the production PDB's but wants to review and access tables from one PDB's for reporting purpose on to test server.
This situation can also be used if a PDB has been dropped and the customer wants to restore it back. So this method can be used to first restore that dropped PDB to alternate server.
To restore the backup from tape to an alternate Server (since the backup_location option is not available with backups on tape)
SOLUTION
The recommended option is to use RMAN duplicate. Refer to the following link:
12c
Duplicating Databases
19c
Duplicating Databases
This document explains the manual restore method using RMAN backup to an alternate location.
This article outlines the plan to restore a single PDB to alternate server.
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
In this example we are assuming there are two PDB1 (PDB1 and PDB2) and we just want to restore PDB1 to test server .
On the source/production database
CDB$ROOT
PDB$SEED
PDB1
PDB2
db_name ORCL
For Reference :-You can Backup single PDB using below Command (PDB1 ) .
Rman>backup database root pluggable database PDB1,"PDB$SEED" plus archivelogs;
--no need s archivelog;
Details on fictitious environment being used in this example. Connect to cdb$root:
SQL>Connect /as sysdba
SQL> Select pdb_id,pdb_name,dbid,status from dba_pdbs ;
PDB_ID PDB_NAME DBID STATUS
---------- ---------- ---------- -------------
3 PDB1 3386774276 NORMAL
2 PDB$SEED 4121670527 NORMAL
4 PDB2 3386774575 NORMAL
Two PDB available are PDB1 and PDB2
- In this case we are assuming a full backup of the database including the PDB1/PDB2 has been taken on the target(source) database
- To restore a single PDB we need to restore the backup of CDB$ROOT and PDB$SEED along with the PDB which needs to be restored.
- A full backup of the database completed at sequence 100 and we need to restore single PDB PDB1 until sequence 120 on test server---use archive log？
- A full backup was taken using the 'backup database plus archivelog' command.
- Please note that you can use this same procedure to manually restore a subset of PDBs from multiple PDBs in the multitenant environment.
- You can restore a dropped PDB using this approach and later plug it into the source database.---recover pdb
Backuppiece generated during full backup
/<path>/ORCL/backupset/2016_05_27/o1_mf_nnnd0_TAG20160527T094853_cnhlohj4_.bkp ---> Datafile backup
/<path>/ORCL/backupset/2016_05_27/o1_mf_nnnd0_TAG20160527T094853_cnhlojk5_.bkp ----> Datafile backup
/<path>/ORCL/backupset/2016_05_27/o1_mf_nnnd0_TAG20160527T094853_cnhmojk5_.bkp ----> Datafile backup
/<path>/ORCL/backupset/2016_05_27/o1_mf_annnn_TAG20160527T095503_cnhmpoyk_.bkp ---> Archivelog backup
/<path>/ORCL/autobackup/2016_05_27/o1_mf_s_912939010_cnhmxd5y_.bkp ----> Controlfile backup ，dbid-912939010 also + spfile
Step 1: Copy the source database backuppieces to the alternate server
Please note you just need to copy the backuppiece containing CDB$ROOT,PDB$SEED, and the PDB you want to restore.
If directory structure is different before the target/source server and that of the alternative server, you will need to catalog the backuppieces once the controlfile is restored.
Step 2: Create the required Pfile/Spfile:
You can copy the spfile or the pfile from target/source and make the necessary changes. The DB_NAME and COMPATIBLE must remain the same.
Specify the following parameters
Control_files
Sga_target
Db_name
Compatible
Diagnostic_dest
Step 3: Restore controlfile/datafiles:
export ORACLE_SID=<SID>
Startup nomount pfile=/tmp/test.ora ;
$Rman target / set dbid ？
RMAN> Restore controlfile from '<name of the backuppiece containing the controlfile>' ;
For example:
RMAN> Restore controlfile from '/<path>/orcl/autobackup/2016_05_27/o1_mf_s_912939010_cnhmxd5y_.bkp' ;
Starting Restore at 30-May-16
allocate channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete , elapsed time : 00:00:0003
output file name='/<path>/oradata/orcl/control_1.ctl
Finished restore at 30-May-16
Once controlfile is restored, mount the database:
RMAN> alter database mount;
If the backup location is different, catalog the backups:
RMAN> catalog start with '<the location where the backuppieces were copied on test server>' noprompt;
RMAN> Crosscheck backup ; ---------------->Required to mark any unavailable files to expired.
If directory structure for the datafiles is same as the target/source database, restore:
NOTE: If backups are available on tape, add allocate tape/SBT channels.
RMAN> run { set until sequence 120;----在线的archive log也要拷贝
restore database root ; ------------------------->CDB$ROOT
restore database "PDB$SEED"; -------------------------------->PDB$SEED is required
restore database PDB1; } ------------------------------->PDB needing to restore
OR
RMAN>run { Set until sequence 120 ;
restore database root database "PDB$SEED" database PDB1; }
If directory structure on test server is different than the target/source database, 'SET NEWNAME' will be needed:
RMAN> report schema ;
This would show the file number associated with CDB$ROOT/PDB$SEED/PDB1.
Use 'set newname for datafile <fileno> to <new path>' to specify a new path for the datafiles.
set newname for database to '+DATA1'
RMAN> run { set newname for datafile <fileno> to 'system.dbf' ;
set newname for datafile <fileno> to 'system.dbf' ;
......
restore database root; ------------------------->CDB$ROOT
restore database "PDB$SEED"; -------------------------------->PDB$SEED is required
restore database PDB1; ------------------------------->PDB we want to restore
switch datafile all; }
Please note since the controlfile type is backup and you are restoring one /multiple PDB(s), if you issue a 'recover database' it would report below error:
RMAN-06067: RECOVER DATABASE required with a backup or created control file
Step 4: Skip the PDB's database not required during the recovery stage by using 'Skip forever tablespace <PDBname:<tablespace name>>'
Once the required PDB ,CDB$ROOT and PDB$SEED have been restored, execute a recovery of the required PDB excluding or skipping the PDBS not required).
In our example, only PDB1 is required and PDB2 recovery needs to be skipped:
RMAN>Run {Set until sequence 120;
recover database skip forever tablespace PDB2:SYSTEM,PDB2:USER,PDB2:SYSAUX,..... ; }
Please note:
The Controlfile restored has the names of all PDB's and their associated tablespace(s) so if you are not aware the the PDB tablespace name that needs to be skipped use 'report schema' in the restored server to find the PDB and tablespace names.
The 'skip forever tablespace' command will 'offline drop' the PDBs' associated tablespace which are not skipped. This option should include
all PDBs and their associated tablespaces separated by comma.
Step 5: If directory structure on test server is different than the target/source database, you need to rename the online redo log files:
------V$log 一定要仔细，别把Production的redo reset了
Capture the current location and name of the online redo log files:
SQL> select member from v$logfile;
For each name listed, issue an 'alter database rename' setting the location to a valid destination and name in the alternative server:
SQL> alter database rename file '<old_path>/redo01.rdo' to '<alternative server path>/redo01.rdo';
Confirm all the locations are valid before the database open:
SQL> select member from v$logfile;
Step 6: Once the redo logfiles are renamed and database restored and recovery, open the database:
RMAN> alter database open resetlogs ;
