Given the current location of a PDB :
I can use RMAN to relocate it. First I take an Image Copy Backup to the new location
Then I switch the database file pointers to the new location.
I can now verify the new location for the database files.
The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database. Note that those datafiles are still registered by RMAN as COPY
If I manually delete the old location files, I'd need to also delete them from the RMAN Registry. Alternatively, I can directly delete them from RMAN.
I still need to relocate the TEMP Tablespace Tempfile.
So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.
To verify that I can access the PDB in the new location :
.
.
SQL> alter session set container=NEWPDB; Session altered. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7 f8go_.dbf /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7 f8hf_.dbf /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk j7f8hg_.dbf /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbc p0wz_.dbf FILE_NAME -------------------------------------------------------------------------------- SQL>
I can use RMAN to relocate it. First I take an Image Copy Backup to the new location
RMAN> backup as copy pluggable database newpdb format '/u03/oradata/NEWPDB/%U'; Starting backup at 20-JUN-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 tag=TAG20170620T231338 RECID=4 STAMP=947200428 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting datafile copy input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di tag=TAG20170620T231338 RECID=5 STAMP=947200441 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 tag=TAG20170620T231338 RECID=6 STAMP=947200451 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 tag=TAG20170620T231338 RECID=7 STAMP=947200454 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 20-JUN-17 Starting Control File and SPFILE Autobackup at 20-JUN-17 piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_20/o1_mf_s_947200455_dnms48pp_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-JUN-17 RMAN>
Then I switch the database file pointers to the new location.
RMAN> alter pluggable database newpdb close; Statement processed RMAN> switch pluggable database newpdb to copy; datafile 16 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di" datafile 17 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2" datafile 18 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1" datafile 19 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4" RMAN> recover pluggable database newpdb; Starting recover at 20-JUN-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=42 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 20-JUN-17 RMAN> alter pluggable database newpdb open; Statement processed RMAN>
I can now verify the new location for the database files.
SQL> alter session set container=NEWPDB; Session altered. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di /u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 /u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 /u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 SQL>
The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database. Note that those datafiles are still registered by RMAN as COPY
RMAN> list copy of pluggable database newpdb; using target database control file instead of recovery catalog List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 8 16 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf Container ID: 4, PDB Name: NEWPDB 9 17 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf Container ID: 4, PDB Name: NEWPDB 10 18 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf Container ID: 4, PDB Name: NEWPDB 11 19 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf Container ID: 4, PDB Name: NEWPDB RMAN>
If I manually delete the old location files, I'd need to also delete them from the RMAN Registry. Alternatively, I can directly delete them from RMAN.
RMAN> delete copy of pluggable database newpdb; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=278 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 8 16 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf Container ID: 4, PDB Name: NEWPDB 9 17 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf Container ID: 4, PDB Name: NEWPDB 10 18 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf Container ID: 4, PDB Name: NEWPDB 11 19 A 20-JUN-17 2188274 20-JUN-17 NO Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf Container ID: 4, PDB Name: NEWPDB Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf RECID=8 STAMP=947200522 deleted datafile copy datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf RECID=9 STAMP=947200522 deleted datafile copy datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf RECID=10 STAMP=947200522 deleted datafile copy datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf RECID=11 STAMP=947200522 Deleted 4 objects RMAN>
I still need to relocate the TEMP Tablespace Tempfile.
SQL> alter session set container=NEWPDB; Session altered. SQL> select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------- /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8 hg_.dbf SQL> alter tablespace temp add tempfile '/u03/oradata/NEWPDB/temp01.dbf' size 100M; Tablespace altered. SQL> SQL> alter tablespace temp drop tempfile '/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8hg_.dbf'; Tablespace altered. SQL> select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------- /u03/oradata/NEWPDB/temp01.dbf SQL>
So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.
To verify that I can access the PDB in the new location :
SQL> connect hemant/hemant@NEWPDB Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- OBJ_LIST HKC_STORE_FILE T SQL> insert into obj_list select * from obj_list; 72641 rows created. SQL> commit; Commit complete. SQL>
.
.