Transport tablespaces across platforms in Oracle Database 12c

August 27, 2014 1 comment

Introduction:

Transportable Tablespaces helps to copy one or more tablespaces from one Oracle Database to another.
Starting with Oracle Database version 11g, transport tablespaces can be used to migrate database from one platform to another.
This includes transporting tablespaces acrosse platforms with differemt endian formats.

Oracle database 12c Realease 1 comes with the possibility to use backupsets and image copies to transport data between platforms.

The new clause “ALLOW INCONSISTENT” in a BACKUP or CONVERT command creates a cross-platform inconsistent backup of one or more tablespaces.
You can create an inconsistent backup of the tablespace when the tablespace is still in read/write mode.

The first inconsistent backup is a level 0 incremental backup. Then, you can create multiple cross-platform level 1 incremental backups.
The final cross-platform incremental backup must be a consistent backup which requires bring the tablespace in read-only mode.

In the following article, we are going to :

- Perform a Cross-Platform Transport of Tablespaces Using consistent Backupsets.
- Perform a Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets.

2- Performing Cross-Platform Transport of Tablespaces Using consistent Backupsets:

First, let’s create a new tablespace called “TBS_TRANSP”.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:00:47 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> CREATE TABLESPACE TBS_TRANSP DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  14.   
  15. Tablespace created.  
  16.   
  17. SQL> CREATE TABLE TAB_TRASNP (ID NUMBER) TABLESPACE TBS_TRANSP;  
  18.   
  19. Table created.  
  20.   
  21. SQL> insert into TAB_TRASNP values (1);  
  22.   
  23. 1 row created.  
  24.   
  25. SQL> commit;  
  26.   
  27. Commit complete.  

Pr-requisites:

Check if tablespace is self-contained; which means check for logical or physical dependencies between objects in the transportable tablespace
and those outside of the tablespace.

 
  1. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP’TRUE);  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  6.   
  7. no rows selected  
  8.   
  9. SQL>  

From the output above, we can go ahead with the procedure of transporting the tablespace as there is no violations

and the tablespace is self-contained.

Check if plateforms supported for cross-platform tablespace transport; note Microsoft Windows x86 64-bit is supported.

 
  1. SQL> set linesize 2000  
  2. SQL> set pagesize 2000  
  3. SQL> select * from v$transportable_platform;  
  4.   
  5. PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT      CON_ID  
  6. ———– —————————————————————————————————– ————– ———-  
  7.           1 Solaris[tm] OE (32-bit)                                                                               Big                     0  
  8.           2 Solaris[tm] OE (64-bit)                                                                               Big                     0  
  9.           7 Microsoft Windows IA (32-bit)                                                                         Little                  0  
  10.          10 Linux IA (32-bit)                                                                                     Little                  0  
  11.           6 AIX-Based Systems (64-bit)                                                                            Big                     0  
  12.           3 HP-UX (64-bit)                                                                                        Big                     0  
  13.           5 HP Tru64 UNIX                                                                                         Little                  0  
  14.           4 HP-UX IA (64-bit)                                                                                     Big                     0  
  15.          11 Linux IA (64-bit)                                                                                     Little                  0  
  16.          15 HP Open VMS                                                                                           Little                  0  
  17.           8 Microsoft Windows IA (64-bit)                                                                         Little                  0  
  18.           9 IBM zSeries Based Linux                                                                               Big                     0  
  19.          13 Linux x86 64-bit                                                                                      Little                  0  
  20.          16 Apple Mac OS                                                                                          Big                     0  
  21.          12 Microsoft Windows x86 64-bit                                                                          Little                  0  
  22.          17 Solaris Operating System (x86)                                                                        Little                  0  
  23.          18 IBM Power Based Linux                                                                                 Big                     0  
  24.          19 HP IA Open VMS                                                                                        Little                  0  
  25.          20 Solaris Operating System (x86-64)                                                                     Little                  0  
  26.          21 Apple Mac OS (x86-64)                                                                                 Little                  0  
  27.   
  28. 20 rows selected.  
  29.   
  30. SQL>  

Both databases must be COMPATIBLE set to 12.0 or greater.

Bring the tablespace TBS_TRANSP in read only mode prior the TTS (Trasportable TableSpace) process.

 
  1. SQL> ALTER TABLESPACE TBS_TRANSP READ ONLY;  
  2.   
  3. Tablespace altered.  
  4.   
  5. SQL>  

Perform a cross-platform Backup of the tablespace TBS_TRANSP and datapump export:

 
  1. BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  2. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  3. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  4. TABLESPACE TBS_TRANSP;  
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:02:50 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN>  CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN>  
  13.   
  14. RMAN> BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  15. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  16. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  17. TABLESPACE TBS_TRANSP;  
  18. 2> 3> 4>  
  19.   
  20. Starting backup at 07/15/2013 06:03:50  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=271 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=21 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=270 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=32 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=262 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_cfoz is:  
  48.    EXPDP>   /opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully completed at Mon Jul 15 06:05:17 2013 elapsed 0 00:01:07  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting full datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00060 name=+DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:21  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:23  
  60. piece handle=/home/oracle/PDB02/tbs_transp.bck tag=TAG20130715T060353 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02  
  62. channel ORA_DISK_1: starting full datafile backup set  
  63. input Data Pump dump file=/opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:23  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:24  
  66. piece handle=/home/oracle/PDB02/tbs_transp.dmp tag=TAG20130715T060353 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:05:24  
  69.   
  70. RMAN>  

Copy the Dump file and the backup file to destination host. The destination directory : C:\PDB02

Restore the foreign tablespace specifying the dump file and the backup taken in the previous step:

 
  1. Restore foreign tablespace TBS_TRANSP   
  2. FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  3. FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  4. DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:08:36 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> Restore foreign tablespace TBS_TRANSP  
  13. 2> FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  14. 3> FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  15. 4> DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
  16.   
  17. Starting restore at 15-JUL-13  
  18. using target database control file instead of recovery catalog  
  19. allocated channel: ORA_DISK_1  
  20. channel ORA_DISK_1: SID=366 device type=DISK  
  21.   
  22. channel ORA_DISK_1: starting datafile backup set restore  
  23. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  24. channel ORA_DISK_1: restoring all files in foreign tablespace TBS_TRANSP  
  25. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.bck  
  26. channel ORA_DISK_1: restoring foreign file 60 to C:\PDB02\TBS_TRANSP.DBF  
  27. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.bck  
  28. channel ORA_DISK_1: restored backup piece 1  
  29. channel ORA_DISK_1: restore complete, elapsed time: 00:00:05  
  30. channel ORA_DISK_1: starting datafile backup set restore  
  31. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  32. channel ORA_DISK_1: restoring Data Pump dump file to C:\app\wissem\product\12.1.0\dbh_1/dbs/backup_tts_CDBTST_83935.dmp  
  33. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.dmp  
  34. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.dmp  
  35. channel ORA_DISK_1: restored backup piece 1  
  36. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  37.   
  38. Performing import of metadata…  
  39.    IMPDP>  
  40. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  41.   
  42.    IMPDP> Master table “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully loaded/unloaded  
  43.    IMPDP> Source timezone version is +00:00 and target timezone version is -05:00.  
  44.    IMPDP> Starting “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF”:  
  45.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  46.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  47.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  48.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  49.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  50.    IMPDP> Job “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully completed at Mon Jul 15 12:12:43 2013 elapsed 0 00:00:23  
  51. Import completed  
  52.   
  53. Finished restore at 15-JUL-13  
  54.   
  55. RMAN> exit  
  56.   
  57.   
  58. Recovery Manager complete.  
  59.   

Check if the tablespace exists.

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 12:13:53 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP;  
  13.   
  14.         ID  
  15. ———-  
  16.          1  
  17.   
  18. SQL> exit;  
  19. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  20. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  21.   
  22. C:\Users>  

Performing Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets:

Note: the same per-requisites we have seen in the previous chapter must be used here as well.

First, let’s create a new tablespace called “TBS_TRANSP_2″.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. SQL> CREATE TABLESPACE TBS_TRANSP_2 DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  2.   
  3. Tablespace created.  
  4.   
  5. SQL>  CREATE TABLE TAB_TRASNP_2 (ID NUMBER) TABLESPACE TBS_TRANSP_2;  
  6.   
  7. Table created.  
  8.   
  9. SQL>  insert into TAB_TRASNP_2 values(2);  
  10.   
  11. 1 row created.  
  12.   
  13. SQL> commit;  
  14.   
  15. Commit complete.  
  16.   
  17. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP_2′,TRUE);  
  18.   
  19. PL/SQL procedure successfully completed.  
  20.   
  21. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  22.   
  23. no rows selected  
  24.   
  25. SQL>  

Let’s take a full 0 backup of the TBS_TRANSP_2.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:29:11 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 0  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2.bck’  
  17. ;  
  18.   
  19. Starting backup at 07/15/2013 06:33:26  
  20. using channel ORA_DISK_1  
  21. using channel ORA_DISK_2  
  22. using channel ORA_DISK_3  
  23. using channel ORA_DISK_4  
  24. using channel ORA_DISK_5  
  25. channel ORA_DISK_1: starting incremental level 0 datafile backup set  
  26. channel ORA_DISK_1: specifying datafile(s) in backup set  
  27. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  28. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:33:27  
  29. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:33:28  
  30. piece handle=/home/oracle/PDB02/tbs_incons_2.bck tag=TAG20130715T063327 comment=NONE  
  31. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  32. Finished backup at 07/15/2013 06:33:28  
  33.   
  34. RMAN>  

Because FOR TRANSPORT is used instead of TO PLATFORM, this cross-platform backup can be restored on any platform.
The conversion will be performed on the destination database.

Let’s insert more records into the table;

 
  1. SQL> insert into TAB_TRASNP_2 values(3);  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> commit;  
  6.   
  7. Commit complete.  

Create a cross-platform level 1 incremental backup
of the tablespace TBS_TRANSP_2 that contains the changes made since last backup.
The tablespace is still in read/write mode.

 
  1. BACKUP  
  2. FOR TRANSPORT  
  3. ALLOW INCONSISTENT  
  4. INCREMENTAL LEVEL 1  
  5. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:36:12 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 1  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’2> 3> 4> 5>  
  17. 6> ;  
  18.   
  19. Starting backup at 07/15/2013 06:36:26  
  20. using target database control file instead of recovery catalog  
  21. allocated channel: ORA_DISK_1  
  22. channel ORA_DISK_1: SID=18 device type=DISK  
  23. allocated channel: ORA_DISK_2  
  24. channel ORA_DISK_2: SID=271 device type=DISK  
  25. allocated channel: ORA_DISK_3  
  26. channel ORA_DISK_3: SID=29 device type=DISK  
  27. allocated channel: ORA_DISK_4  
  28. channel ORA_DISK_4: SID=263 device type=DISK  
  29. allocated channel: ORA_DISK_5  
  30. channel ORA_DISK_5: SID=28 device type=DISK  
  31. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  32. channel ORA_DISK_1: specifying datafile(s) in backup set  
  33. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  34. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:36:29  
  35. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:36:30  
  36. piece handle=/home/oracle/PDB02/tbs_incons_2_lvl1.bck tag=TAG20130715T063629 comment=NONE  
  37. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  38. Finished backup at 07/15/2013 06:36:30  
  39.   
  40. RMAN>  

To minimize application downtime, the level 0 and level 1 incremental backups created in previous steps
can be restored and applied on the destination database while the source tablespace is still in read/write mode.
When the destination database catches up with last level 1 incremental backup, you can create the final incremental
backup with the tablespace placed in read-only mode.

Let’s bring now the TABLESPACE TBS_TRANSP_2 in read only mode and perform a last incremental 1 backup
and an export datapump file
before trying to move the backups to the destination host.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:39:23 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> ALTER TABLESPACE TBS_TRANSP_2 READ ONLY;  
  14.   
  15. Tablespace altered.  
  16.   
  17. SQL> exit  
  18. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  19. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  20. and Real Application Testing options  
  21. sandbox1(orawiss):/home/oracle/PDB02>  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:41:10 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. INCREMENTAL LEVEL 1  
  15. TABLESPACE TBS_TRANSP_2  
  16. FORMAT ‘/home/oracle/PDB02/tbs_incons_final.bck’  
  17. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_incons_final.dmp’  
  18. DESTINATION ‘/home/oracle/PDB02′;  
  19.   
  20. Starting backup at 07/15/2013 06:41:23  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=31 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=262 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=271 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=28 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=263 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_urkc is:  
  48.    EXPDP>   /home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP_2:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully completed at Mon Jul 15 06:42:30 2013 elapsed 0 00:00:51  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:33  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:34  
  60. piece handle=/home/oracle/PDB02/tbs_incons_final.bck tag=TAG20130715T064125 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  62. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  63. input Data Pump dump file=/home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:34  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:35  
  66. piece handle=/home/oracle/PDB02/tbs_incons_final.dmp tag=TAG20130715T064125 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:42:35  
  69.   
  70. RMAN>  

Copy all backup / dum files to destination Windows host;

First, Restore from full backup:

 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:51:51 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> RESTORE  
  13. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  14. 3> FOREIGN DATAFILE 61  
  15. 4> FORMAT ‘C:\PDB02\tbs_transp_2_61.df’  
  16. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2.bck’;  
  17.   
  18. Starting restore at 15-JUL-13  
  19. using target database control file instead of recovery catalog  
  20. allocated channel: ORA_DISK_1  
  21. channel ORA_DISK_1: SID=364 device type=DISK  
  22.   
  23. channel ORA_DISK_1: starting datafile backup set restore  
  24. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  25. channel ORA_DISK_1: restoring foreign file 00061  
  26. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2.bck  
  27. channel ORA_DISK_1: restoring foreign file 61 to C:\PDB02\TBS_TRANSP_2_61.DF  
  28. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_2.bck  
  29. channel ORA_DISK_1: restored backup piece 1  
  30. channel ORA_DISK_1: restore complete, elapsed time: 00:00:04  
  31. Finished restore at 15-JUL-13  
  32.   
  33. RMAN>  

Recover from first incremental backup

 
  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2_lvl1.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2_lvl1.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_2_LVL1.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Recover from last incremental backup

  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_FINAL.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Restore the backup set containing the export dump file. The following command will create a dump file
called ‘tbs_incons_final_dest.dmp’ which be used later to import the metadata.

  1. RMAN> RESTORE  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> DUMP FILE ‘tbs_incons_final_dest.dmp’  
  4. 4> DATAPUMP DESTINATION ‘C:\PDB02′  
  5. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.dmp’;  
  6.   
  7. Starting restore at 15-JUL-13  
  8. using channel ORA_DISK_1  
  9.   
  10. channel ORA_DISK_1: starting datafile backup set restore  
  11. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  12. channel ORA_DISK_1: restoring Data Pump dump file to C:\PDB02/tbs_incons_final_dest.dmp  
  13. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.dmp  
  14. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_final.dmp  
  15. channel ORA_DISK_1: restored backup piece 1  
  16. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  17. Finished restore at 15-JUL-13  
  18.   
  19. RMAN>  

Create a directory object:

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:03:57 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> create directory TBS_TRANSP_DIR AS ‘C:\PDB02′;  
  13.   
  14. Directory created.  
  15.   
  16. SQL> exit  
  17. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  18. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  19.   

Plug the tablespace in to the destination database. Use the Data Pump import utility
to import the dump file containing the tablespace metadata in to the destination database.

 
  1. C:\Users>impdp \“/ as sysdba\” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  2.  
  3. Import: Release 12.1.0.1.0 – Production on Mon Jul 15 13:07:17 2013 
  4.  
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 
  6.  
  7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 
  8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
  9.  
  10. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. 
  11.  
  12. Master table “SYS“.”SYS_IMPORT_TRANSPORTABLE_01” successfully loaded/unloaded 
  13. Source timezone version is +00:00 and target timezone version is -05:00. 
  14. Starting “SYS“.”SYS_IMPORT_TRANSPORTABLE_01“:  “/******** AS SYSDBA” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  15. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 
  16. Processing object type TRANSPORTABLE_EXPORT/TABLE 
  17. Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 
  18. Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER 
  19. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 
  20. Job “SYS“.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Mon Jul 15 13:07:34 2013 elapsed 0 00:00:08  z
  21.   

CHeck the table exists with its records.

 
  1. C:\Users\wissem>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:08:40 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP_2;  
  13.   
  14.         ID  
  15. ———-  
  16.          2  
  17.          3  
  18.   
  19. SQL>  

Automatic Diagnostics Repository (ADR) Enhancements in Oracle Database 12c (ADRCI)

August 27, 2014 1 comment

DDL Log

Setting the ENABLE_DDL_LOGGING parameter to TRUE activates DDL logging. All DDL statements are logged into a plain text file and an XML file in the ADR location.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl_{SID}.log
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

The parameter is container-specific, so you will need to enable/disable it independently in the root container and any PDBs.

The following example shows how the parameter works and what output you can expect.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

CONN test/test@pdb1

CREATE TABLE test1 (id NUMBER);
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id);
DROP TABLE test1 PURGE;

Navigating to the ADR home for the database and into the “log” directory, we can see the plain text DDL log.

$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log
$ ls
ddl  ddl_cdb1.log  debug  test
$ cat ddl_cdb1.log
Wed Aug 26 10:11:11 2014
diag_adl:CREATE TABLE test1 (id NUMBER)
diag_adl:ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
diag_adl:DROP TABLE test1 PURGE
$

Moving down into the “ddl” directory, we can see the XML log.

$ cd ddl
$ ls
log.xml
$ cat log.xml
<msg time='2014-08-26T10:11:11.578+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'
 version='1'>
 <txt>CREATE TABLE test1 (id NUMBER)
 </txt>
</msg>
<msg time='2014-08-26T10:11:11.596+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'>
 <txt>ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
 </txt>
</msg>
<msg time='2014-08-26T10:11:11.628+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='ol6-121.localdomain' host_addr='192.168.56.2'>
 <txt>DROP TABLE test1 PURGE
 </txt>
</msg>
$

Interestingly, there does not appear to be any information in the logs to indicate which container the DDL came from.

Debug Log

Oracle Database 12c has thinned out some of the contents of the alert log and trace files. Some activities that may be considered unusual, but do not directly cause problems with the database are no longer written to the alert log and/or trace files. Instead, they are written to the debug log, typically located as follows.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/debug

As a DBA, you need to remember to keep an eye on the contents of the debug log in addition to the alert log. The debug log is included in any packages created to send to Oracle Support.

New ADRCI Command (SHOW LOG)

The SHOW LOG command has been added to ADRCI to give visibility of the new DDL and debug logs. The available options are visible using the HELP SHOW LOG command.

adrci> help show log

  Usage: SHOW LOG [-l <log name>] [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] ]
  Purpose: Show diagnostic log messages.

  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(4000)
        PROBLEM_KEY                   text(65)

    [-l <log_name>]: Output specified log, otherwise all messages from
    all diagnostic logs under the current ADR Home are displayed.

    [-tail [num] [-f]]: Output last part of the log messages and
    output latest messages as the log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new log messages are generated.
    This option requires that a log name be supplied via -l option.

    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.

  Examples:
    show log -l debug
    show log -p "message_text like '%tablespace%'"
    show log -l ddl -tail 20

adrci>

As the examples suggest, you can display the contents of the DDL log with the following command.

adrci> show log -l ddl

ADR Home = /u01/app/oracle/diag/rdbms/cdb1/cdb1:
*************************************************************************
Output the results to file: /tmp/utsout_8165_140616_1.ado
adrci>

2014-08-26 10:11:11.578000 +01:00
CREATE TABLE test1 (id NUMBER)
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
DROP TABLE test1 PURGE

If some were present, the contents of the debug log can be displayed with the following command.

adrci> show log -l debug

ADR Home = /u01/app/oracle/diag/rdbms/cdb1/cdb1:
*************************************************************************

No diagnostic log in selected home
adrci>

For more information see:

Online Move Datafile in Oracle Database 12c Release 1 (12.1)

August 27, 2014 1 comment

Basic Syntax

The text description of the syntax is shown below, but the syntax diagrams and a full description of the ALTER DATABASE command is available in the documentation here.

ALTER DATABASE MOVE DATAFILE 
( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ]

The source file can be specified using the file number or name, while the destination file must be specified by the file name. The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.

When the source file is an OMF file the KEEP option can not be used. If the destination file is an OMF file, the TO clause can be omitted and the file will be created with an OMF name in theDB_CREATE_FILE_DEST location.

The file number can be queried from the V$DATAFILE and DBA_DATA_FILES views.

SQL> CONN / AS SYSDBA

SQL> SET LINESIZE 100
SQL> COLUMN name FORMAT A70
SQL> SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;

     FILE# NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf

SQL>

SQL> COLUMN file_name FORMAT A70
SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf

SQL>

Examples

The following example shows a basic file move, specifying both source and destination by name. Notice the original file is no longer present.

SQL> ALTER DATABASE MOVE DATAFILE 
'/u01/app/oracle/oradata/cdb1/system01.dbf' TO
'/tmp/system01.dbf'; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /tmp/system01.dbf SQL> SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf ls: cannot access /u01/app/oracle/oradata/cdb1/system01.dbf:
No such file or directory SQL> HOST ls -al /tmp/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Oct 8 22:48 /tmp/system01.dbf SQL>

The next example uses the file number for the source file and keeps the original file.

SQL> ALTER DATABASE MOVE DATAFILE 1 TO 
'/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/system01.dbf SQL> SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Aug 26 22:48
/u01/app/oracle/oradata/cdb1/system01.dbf
SQL> HOST ls -al /tmp/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Aug 26 22:49
/tmp/system01.dbf SQL>

The next example shows the use of OMF.

SQL> ALTER SYSTEM SET db_create_file_dest=
'/u01/app/oracle/oradata/cdb1'; System altered. SQL> ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/cdb1/system01.dbf'; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf SQL>

The final example attempts to use the KEEP option, where the source file in an OMF file. Notice how the KEEP option is ignored.

SQL> ALTER DATABASE MOVE DATAFILE 1 To 
'/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/system01.dbf SQL> SQL> host ls -al /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf ls: cannot access /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf: No such file or directory SQL>

Pluggable Database (PDB)

The container database (CDB) can not move files that belong to a pluggable database. The following query displays all the datafiles for the CDB and the PDBs.

SQL> SELECT file#, name FROM v$datafile ORDER BY file#;

     FILE# NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 5 /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf
	 7 /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
	 8 /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
	 9 /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
	10 /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
	29 /u01/app/oracle/oradata/pdb2/system01.dbf
	30 /u01/app/oracle/oradata/pdb2/sysaux01.dbf
	31 /u01/app/oracle/oradata/pdb2/pdb2_users01.dbf

SQL> 

If we try to move a datafile belonging to a PDB an error is returned.

SQL> ALTER DATABASE MOVE DATAFILE 
'/u01/app/oracle/oradata/pdb2/system01.dbf' TO
'/tmp/system01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/pdb2/system01.dbf' TO
'/tmp/system01.dbf' REUSE
* ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "29" SQL>

If we switch to the PDB container, the datafile can be moved as normal.

SQL> ALTER SESSION SET container=pdb2;

Session altered.

SQL> ALTER DATABASE MOVE DATAFILE 
'/u01/app/oracle/oradata/pdb2/system01.dbf' TO
'/tmp/system01.dbf' REUSE; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 29 /tmp/system01.dbf SQL> SQL> ALTER DATABASE MOVE DATAFILE 29 TO
'/u01/app/oracle/oradata/pdb2/system01.dbf' REUSE; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 29 /u01/app/oracle/oradata/pdb2/system01.dbf SQL>ALTER SESSION SET container=cdb1; SQL> ALTER SESSION SET container=CDB$ROOT; Session altered. SQL>

Tempfiles

Not surprisingly, the ALTER DATABASE MOVE DATAFILE syntax does not work for temporary files.

SQL> SELECT file_id, file_name FROM dba_temp_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/temp01.dbf

SQL>


SQL> ALTER DATABASE MOVE DATAFILE 
'/u01/app/oracle/oradata/cdb1/temp01.dbf' TO
'/tmp/temp01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/cdb1/temp01.dbf' TO
'/tmp/temp01.dbf' REUSE
* ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/cdb1/temp01.dbf" SQL>

That is not major problem as temporary files can be created and removed quite simply.

For more information see:

Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE) in Oracle Database 12c Release 1 (12.1)

August 27, 2014 1 comment

Basic Usage

In order to use the DBMS_PRIVILEGE_CAPTURE package you must be granted the CAPTURE_ADMIN role. Regardless of what you are trying to monitor, the basic usage of the DBMS_PRIVILEGE_CAPTUREpackage is the same.

  1. Create a privilege analysis policy. (CREATE_CAPTURE)
  2. Enable it. (ENABLE_CAPTURE)
  3. Wait for the required analysis period.
  4. Disable the privilege analysis policy. (DISABLE_CAPTURE)
  5. Analyze the results. (GENERATE_RESULT and query dictionary views)
  6. Drop the policy if it, and the recorded data, is no longer needed. (DROP_CAPTURE)

The main differences between analysis runs will be based around the call to CREATE_CAPTURE procedure, which is discussed below.

The time waited between enabling and disabling the capture is a really import part of the process. You must wait for a representative period of time, or you might miss some important activity. For example, some privileges may be associated with tasks which happen infrequently, like year end jobs. If you don’t sample during a representative period, you may incorrectly conclude certain privileges are unnecessary.

Note. In a multitenant environment, the policies are container-specific.

CREATE_CAPTURE

The CREATE_CAPTURE procedure allows you to create privilege analysis policies with varying degrees of granularity.

  • G_DATABASE : Analyzes all privilege usage on the database, except the SYS user. The ROLES and CONDITION parameters are not needed.
  • G_ROLE : Analyzes all privilege usage by the roles specified in the ROLES parameter. Use the ROLE_NAME_LIST function to specify the roles.
  • G_CONTEXT : Analyzes all privilege usage when the boolean expression specified in the CONDITION parameter evaluates to TRUE. Conditions can include combinations of calls to theSYS_CONTEXT.
  • G_ROLE_AND_CONTEXT : Analyzes all privilege usage when both the ROLES and CONDITION criteria are true.

All policies are created in a disabled state. The following code gives a simple example of each.

-- Connect to a privileged using in a PDB.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

-- Whole database (type = G_DATABASE).
BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'db_pol',
    type        => DBMS_PRIVILEGE_CAPTURE.g_database
  );
END;
/

-- One or more roles (type = G_ROLE).
BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'role_pol',
    type        => DBMS_PRIVILEGE_CAPTURE.g_role,
    roles       => role_name_list('DBA', 'RESOURCE')
  );
END;
/

-- A user defined condition, when user is TEST (type = G_CONTEXT).
BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'cond_pol',
    type        => DBMS_PRIVILEGE_CAPTURE.g_context,
    condition   => 'SYS_CONTEXT(''USERENV'', 
''SESSION_USER'') = ''TEST''' ); END; / -- Combination of roles and conditions (type = G_ROLE_AND_CONTEXT). BEGIN DBMS_PRIVILEGE_CAPTURE.create_capture( name => 'role_cond_pol', type => DBMS_PRIVILEGE_CAPTURE.g_role_and_context, roles => role_name_list('DBA', 'RESOURCE'), condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')
IN (''TEST'',''EMP'')' ); END; /

The DBA_PRIV_CAPTURES view displays information on the existing privilege capture policies.

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;

NAME            TYPE             E ROLES                CONTEXT
--------------- ---------------- - -------------------- ------------------------------
cond_pol        CONTEXT          N                      SYS_CONTEXT('USERENV', 'SESSIO
                                                        N_USER') = 'TEST'

db_pol          DATABASE         N
role_cond_pol   ROLE_AND_CONTEXT N ROLE_ID_LIST(4, 3)   SYS_CONTEXT('USERENV', 'SESSIO
                                                        N_USER') IN ('TEST','EMP')

role_pol        ROLE             N ROLE_ID_LIST(4, 3)

4 rows selected.

SQL>

ENABLE_CAPTURE

The ENABLE_CAPTURE procedure is used to enable a capture policy. Typically, only one analysis policy can be enabled at a time. The exception to this is one G_DATABASE and one none G_DATABASEpolicy can be enabled at the same time.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.enable_capture('db_pol');
  DBMS_PRIVILEGE_CAPTURE.enable_capture('cond_pol');
END;
/

DISABLE_CAPTURE

As soon as you have waited a representative amount of time, the capture can be disabled using the DISABLE_CAPTURE procedure.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture('db_pol');
  DBMS_PRIVILEGE_CAPTURE.disable_capture('cond_pol');
END;
/

GENERATE_RESULTS

Once a capture is complete, the GENERATE_RESULT procedure should be used to push the captured information to the data dictionary views.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.generate_result('db_pol');
END;
/

Privilege Analysis Views

The following views have been added in Oracle 12c to allow you to query the results of privilege analysis runs.

  • DBA_PRIV_CAPTURES
  • DBA_USED_OBJPRIVS
  • DBA_USED_OBJPRIVS_PATH
  • DBA_USED_PRIVS
  • DBA_USED_PUBPRIVS
  • DBA_USED_SYSPRIVS
  • DBA_USED_SYSPRIVS_PATH
  • DBA_USED_USERPRIVS
  • DBA_USED_USERPRIVS_PATH
  • DBA_UNUSED_OBJPRIVS
  • DBA_UNUSED_OBJPRIVS_PATH
  • DBA_UNUSED_PRIVS
  • DBA_UNUSED_SYSPRIVS
  • DBA_UNUSED_SYSPRIVS_PATH
  • DBA_UNUSED_USERPRIVS
  • DBA_UNUSED_USERPRIVS_PATH

The information displayed by these views will help you decide which grants and roles should be amended.

DROP_CAPTURE

Once your analysis is complete, you can optionally choose to drop the captured information. Only disabled policies can be dropped.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture('cond_pol');
  DBMS_PRIVILEGE_CAPTURE.drop_capture('db_pol');
  DBMS_PRIVILEGE_CAPTURE.drop_capture('role_cond_pol');
  DBMS_PRIVILEGE_CAPTURE.drop_capture('role_pol');
END;
/

Example

In this section we will look at an example of privilege analysis. Create a user with a high degree of privilege by giving it the DBA and RESOURCE roles.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE USER priv_test_user IDENTIFIED BY priv_test_user;
GRANT DBA, RESOURCE TO priv_test_user;

Start capturing the privilege usage for these roles against this user.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'dba_res_user_pol',
    type        => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
    roles       => role_name_list('DBA', 'RESOURCE'),
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') 
= ''PRIV_TEST_USER''' ); DBMS_PRIVILEGE_CAPTURE.enable_capture( name => 'dba_res_user_pol' ); END; /

Perform some actions as the PRIV_TEST_USER user.

CONN priv_test_user/priv_test_user@pdb1

CREATE TABLE tab1 (
  id NUMBER,
  description VARCHAR2(50),
  CONSTRAINT tab1_px PRIMARY KEY (id)
);

CREATE SEQUENCE tab1_seq;

CREATE VIEW tab1_view AS
SELECT * FROM tab1;

INSERT INTO tab1
SELECT level, 'Description of ' || TO_CHAR(level)
FROM   dual
CONNECT BY level <= 5;

COMMIT;

SELECT name FROM v$database;

Disable the capture process and push the results to the data dictionary.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'dba_res_user_pol'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'dba_res_user_pol'
  );
END;
/

Check the privileges that were used during the capture period by querying the data dictionary.

What system privileges were used during the capture period? We can get that information from the DBA_USED_PRIVSDBA_USED_SYSPRIVS or DBA_USED_SYSPRIVS_PATH views.

COLUMN username FORMAT A20
COLUMN sys_priv FORMAT A20

SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'dba_res_user_pol'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV
-------------------- --------------------
PRIV_TEST_USER       CREATE ANY INDEX
PRIV_TEST_USER       CREATE SEQUENCE
PRIV_TEST_USER       CREATE SESSION
PRIV_TEST_USER       CREATE TABLE
PRIV_TEST_USER       CREATE VIEW

5 rows selected.

SQL>

These look straight forward, with the exception of the CREATE ANY INDEX privilege. This would need to be investigated further, but in many cases, this would just be one of those eccentricities of Oracle that can be ignored. If you have the ability to create tables, you also have the ability to index those tables. As a result, the use of the CREATE ANY INDEX privilege is not really necessary in most cases.

How were these privileges granted to the user? We can get that information from the DBA_USED_SYSPRIVS_PATH view.

COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN sys_priv FORMAT A20
COLUMN path FORMAT A50
SET LINESIZE 200

SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture = 'dba_res_user_pol'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV             USED_ROLE                      PATH
-------------------- -------------------- ------------------------------ --------------------------------------------------
PRIV_TEST_USER       CREATE ANY INDEX     IMP_FULL_DATABASE              GRANT_PATH('PRIV_TEST_USER', 'DBA', 'IMP_FULL_DATA
                                                                         BASE')

PRIV_TEST_USER       CREATE ANY INDEX     IMP_FULL_DATABASE              GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_IMP_
                                                                         FULL_DATABASE', 'IMP_FULL_DATABASE')

PRIV_TEST_USER       CREATE SEQUENCE      OLAP_DBA                       GRANT_PATH('PRIV_TEST_USER', 'DBA', 'OLAP_DBA')
PRIV_TEST_USER       CREATE SESSION       EM_EXPRESS_BASIC               GRANT_PATH('PRIV_TEST_USER', 'DBA', 'EM_EXPRESS_AL
                                                                         L', 'EM_EXPRESS_BASIC')

PRIV_TEST_USER       CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_
                                                                         FULL_DATABASE', 'EXP_FULL_DATABASE')

PRIV_TEST_USER       CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_
                                                                         FULL_DATABASE')

PRIV_TEST_USER       CREATE VIEW          DBA                            GRANT_PATH('PRIV_TEST_USER', 'DBA')

7 rows selected.

SQL>

So the privileges came from a variety of roles, but looking at the output from the PATH column, all of them stem from the grant of the DBA role.

What object privileges were necessary? We can get this information from the DBA_USED_PRIVSDBA_USED_OBJPRIVS or DBA_USED_OBJPRIVS_PATH views.

COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN object_type FORMAT A11

SELECT username, obj_priv, object_owner, object_name, object_type 
FROM   dba_used_objprivs
WHERE  capture = 'dba_res_user_pol';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- -------- --------------- -------------------- -----------
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          VIEW

1 row selected.

SQL>

How were these privileges granted to the user? We can get that information from the DBA_USED_OBJPRIVS_PATH view.

COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN used_role FORMAT A20
COLUMN path FORMAT A30
SET LINESIZE 200

SELECT username, obj_priv, object_owner, object_name, used_role, path 
FROM   dba_used_objprivs_path
WHERE  capture = 'dba_res_user_pol';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          USED_ROLE            PATH
-------------------- -------- --------------- -------------------- -------------------- ------------------------------
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'SELECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'IMP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_EXP_FULL_DATAB
                                                                                        ASE', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_IMP_FULL_DATAB
                                                                                        ASE', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_IMP_FULL_DATAB
                                                                                        ASE', 'IMP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'EM_EXPRESS_ALL', 'EM_EX
                                                                                        PRESS_BASIC', 'SELECT_CATALOG_
                                                                                        ROLE')


7 rows selected.

SQL>

Once again, the privileges came from a variety of roles, but looking at the output from the PATH column, all of them stem from the grant of the DBA role.

What can we conclude from this?

  • All privileges used were granted via the DBA role, so no direct privileges are necessary.
  • With the exception of the CREATE ANY INDEX privilege, which would need further investigation in a real situation, all the privileges used are quite basic, so this user really doesn’t need the DBA andRESOURCE roles.

So the solution here seems quite simple. Create a custom role to apply any necessary privileges, then revoke the DBA and RESOURCE roles.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE ROLE custom_role;
GRANT CREATE SEQUENCE TO custom_role;
GRANT CREATE SESSION TO custom_role;
GRANT CREATE TABLE TO custom_role;
GRANT CREATE VIEW TO custom_role;
GRANT SELECT ON SYS.V_$DATABASE TO custom_role;

GRANT custom_role TO priv_test_user;
REVOKE DBA, RESOURCE FROM priv_test_user;

With the analysis complete, we can optionally remove the captured information from the data dictionary.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'dba_res_user_pol'
  );
END;
/

For more information see:

Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

August 27, 2014 1 comment

Container Database (CDB)

Startup and shutdown of the container database is the same as it has always been for regular instances. The SQL*Plus STARTUP and SHUTDOWNcommands are available when connected to the CDB as a privileged user. Some typical values are shown below.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

Pluggable Database (PDB)

Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

SQL*Plus Commands

The following SQL*Plus commands are available to start and stop a pluggable database, when connected to that pluggable database as a privileged user.

STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];

Some examples are shown below.

STARTUP FORCE;
SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;
SHUTDOWN;

STARTUP;
SHUTDOWN IMMEDIATE;

ALTER PLUGGABLE DATABASE

The ALTER PLUGGABLE DATABASE command can be used from the CDB or the PDB.

The following commands are available to open and close the current PDB when connected to the PDB as a privileged user.

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];

Some examples are shown below.

ALTER PLUGGABLE DATABASE OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

The following commands are available to open and close one or more PDBs when connected to the CDB as a privileged user.

ALTER PLUGGABLE DATABASE 
<pdd-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE
<pdd-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE
<pdd-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE
<pdd-name-clause> CLOSE [IMMEDIATE];

The <pdd-name-clause> clause can be any of the following:

  • One or more PDB names, specified as a comma-separated list.
  • The ALL keyword to indicate all PDBs.
  • The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.

Some examples are shown below.

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

Pluggable Database (PDB) Automatic Startup

The 12.1.0.2 patchset has introduced the ability to preserve the startup state of PDBs, so you probably shouldn’t be implementing a trigger in the manner discussed in this section.

Prior to 12.1.0.2, when the CDB is started, all PDBs remain in mounted mode. There is no default mechanism to automatically start them when the CDB is started. The way to achieve this is to use a system trigger on the CDB to start some or all of the PDBs.

CREATE OR REPLACE TRIGGER open_pdbs 
  AFTER STARTUP ON DATABASE 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 
END open_pdbs;
/

You can customise the trigger if you don’t want all of your PDBs to start.

Preserve PDB Startup State (12.1.0.2 onward)

The 12.1.0.2 patchset introduced the ability to preserve the startup state of PDBs through a CDB restart. This is done using the ALTER PLUGGABLE DATABASE command.

We will start off by looking at the normal result of a CDB restart. Notice the PDBs are in READ WRITE mode before the restart, but in MOUNTED mode after it.

SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

SQL> 


SHUTDOWN IMMEDIATE;
STARTUP;


SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED

SQL>

Next, we open both pluggable databases, but only save the state of PDB1.

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb2 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

The DBA_PDB_SAVED_STATES view displays information about the saved state of containers.

COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE
-------------------- -------------------- --------------
PDB1                 cdb1                 OPEN

SQL>

Restarting the CDB now gives us a different result.

SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

SQL> 


SHUTDOWN IMMEDIATE;
STARTUP;


SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED

SQL>

The saved state can be discarded using the following statement.

ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL>

Here is a brief list of some of the usage notes explained in the documentation.

  • The state is only saved and visible in the DBA_PDB_SAVED_STATES view if the container is in READ ONLY or READ WRITE mode. The ALTER PLUGGABLE DATABASE ... SAVE STATEcommand does not error when run against a container in MOUNTED mode, but nothing is recorded, as this is the default state after a CDB restart.
  • Like other examples of the ALTER PLUGGABLE DATABASE command, PDBs can be identified individually, as a comma separated list, using the ALL or ALL EXCEPT keywords.
  • The INSTANCES clause can be added when used in RAC environments. The clause can identify instances individually, as a comma separated list, using the ALL or ALL EXCEPT keywords. Regardless of the INSTANCES clause, the SAVE/DISCARD STATE commands only affect the current instance.

For more information see:

Follow

Get every new post delivered to your Inbox.

Join 528 other followers