\r\n Database properties for a PDB: PIPBOY\r\n\r\nProperty name Property value Property description\r\n------------------------ ------------------------ ------------------------------------------\r\nDBTIMEZONE +02:00 DB time zone\r\nDEFAULT_EDITION ORA$BASE Name of the database default edition\r\nDEFAULT_PERMANENT_TABLES VAULT13 Name of default permanent tablespace\r\nPACE\r\n\r\nDEFAULT_TBS_TYPE SMALLFILE Default tablespace type\r\nDEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace\r\nDICT.BASE 2 dictionary base tables version #\r\nDST_PRIMARY_TT_VERSION 18 Version of primary timezone data file\r\nDST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file\r\nDST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade\r\nEXPORT_VIEWS_VERSION 8 Export views revision #\r\nFlashback Timestamp Time GMT Flashback timestamp created in GMT\r\nZone\r\n\r\nGLOBAL_DB_NAME PIPBOY Global database name\r\nMAX_PDB_STORAGE 2048 MB Maximum Space Usage of Datafiles and Local\r\n Tempfiles in Container\r\n\r\nMAX_SHARED_TEMP_SIZE 1024 MB Maximum Space Usage in Shared Temporary Ta\r\n blespace\r\n\r\nMAX_STRING_SIZE STANDARD MAX_STRING_SIZE paramter used for dictiona\r\n ry metadata\r\n\r\nNLS_CALENDAR GREGORIAN Calendar system\r\nNLS_CHARACTERSET AL32UTF8 Character set\r\nNLS_COMP BINARY NLS comparison\r\nNLS_CURRENCY $ Local currency\r\nNLS_DATE_FORMAT DD-MON-RR Date format\r\nNLS_DATE_LANGUAGE AMERICAN Date language\r\nNLS_DUAL_CURRENCY $ Dual currency symbol\r\nNLS_ISO_CURRENCY AMERICA ISO currency\r\nNLS_LANGUAGE AMERICAN Language\r\nNLS_LENGTH_SEMANTICS BYTE NLS length semantics\r\nNLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set\r\nNLS_NCHAR_CONV_EXCP FALSE NLS conversion exception\r\nNLS_NUMERIC_CHARACTERS ., Numeric characters\r\nNLS_RDBMS_VERSION 12.1.0.2.0 RDBMS version for NLS parameters\r\nNLS_SORT BINARY Linguistic definition\r\nNLS_TERRITORY AMERICA Territory\r\nNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format\r\nNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM Timestamp with timezone format\r\n TZR\r\n\r\nNLS_TIME_FORMAT HH.MI.SSXFF AM Time format\r\nNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format\r\nNO_USERID_VERIFIER_SALT 5A9235A582ACE9E5EEA30CEA\r\n D2E1B6AC\r\n\r\nOLS_OID_STATUS 0 OLS OID Status used for Label Security\r\nPATH_PREFIX \/u02\/oradata\/tcdb\/pipboy All paths for objects such as directories\r\n are relative to this\r\n\r\nTDE_MASTER_KEY_ID\r\nWORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in pro\r\n gress\r\n\r\nWORKLOAD_REPLAY_MODE PREPARE implies external replay clients ca\r\n n connect; REPLAY implies workload replay\r\n is in progress\r\n<\/pre>\nInitialization parameters for PDB are inherited from cdb$root container. You can stop this behaviour by changing parameter value with alter system command from PDB session level. If you use cdb_properties view in PDB then you’ll get exactly the same data which can be reached through database_properties view. Difference is on container id column which is added to all CDB_ views.<\/p>\n
Leaving properties area and going for initialization parameter file and server parameter file. In PDB there is no file associated with PDB in $ORACLE_HOME\/dbs directory. As PDB uses data dictionary to store all information about itself there is no need for another spfile. Oracle let PDB inherit parameters from root container and allow to change some of the parameters. You can find which one can be changed checking column ISPDB_MODIFIABLE from v$system_parameter view.<\/p>\n
As this behaviour is quite important to master we’ll take a quick trip behind the wheel of open_cursors parameter.<\/p>\n
First check current value for root container and pipboy PDB. From cdb$root container connected as sysdba execute script .\/iad\/parameter121.sql.<\/p>\n
\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nGLOBAL (conid=0) open_cursors 300 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 300 T\r\n<\/pre>\nopen_cursors parameter has been set to value of 300 by dbca. Default value for this parameter is 50.<\/p>\n
Open new putty session and connect directly to pipboy as common user sys.<\/p>\n
\r\nsqlplus sys\/manager@pipboy as sysdba\r\n<\/pre>\n\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nGLOBAL (conid=0) open_cursors 300 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 0 F\r\n<\/pre>\nordinal column in v$spparameter differs between containers because root container use spfile to start and pipboy haven’t got the ability to read spfile during startup (PDB’s use data dictionary information only). This column has more useful value when listed to find out what is the correct order of strings within one parameter, for instance when checking control_files parameter with more than one copy of it. Other data is the same within presented scope.<\/p>\n
Change open_cursors value in pipboy PDB to 456 – use session in pipboy PDB.<\/p>\n
\r\nalter system set open_cursors = 456;\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nPIPBOY open_cursors 456 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 456 T\r\n<\/pre>\nValue for open_cursors parameter has been changed for pipboy PDB. From this point of view spparameter present “global” value. This is true only for pipboy PDB and her “data dictionary server parameter file”. Check what we can see from root container point of view. Switch to cdb$root session.<\/p>\n
\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nPIPBOY open_cursors 456 F T\r\nGLOBAL (conid=0) open_cursors 300 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 300 T\r\n<\/pre>\nFrom root container point of view we can see that someone has changed value for pipboy’s open_cursors parameter but in server parameter file we still have value 300. The same for active value in cdb$root. Note that con_id and con_name need to be considered as a point of view. Another thing is that we’ve broke inheritance behaviour – from pipboy point of view we can see that the parameter is no longer global (inherited) but it is specified within PDB.<\/p>\n
How to revert to standard state where value of the parameter is inherited? We can reset this value from PDB side or use container = all and set this new value for all containers. To get back to default value we need to reset parameter on PDB side and reset in spfile. Let’s check setting the parameter everywhere at once. From root container use command with container clause.<\/p>\n
\r\nalter system set open_cursors = 787 container = all;\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nGLOBAL (conid=0) open_cursors 787 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 787 T\r\n<\/pre>\nEverywhere we’ve got the same value. This explains why we cannot see a row for pipboy PDB.<\/p>\n
Execute the same script from PDB session.<\/p>\n
\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nGLOBAL (conid=0) open_cursors 787 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 456 T\r\n<\/pre>\nData dictionary information has not been deleted yet for pipboy PDB. After pluggable database restart all return to normal state and there is no information in data dictionary cache.<\/p>\n
Restart pluggable database from root container session.<\/p>\n
\r\nalter pluggable database pipboy close immediate;\r\nalter pluggable database pipboy open read write;\r\n<\/pre>\nExit from pipboy sys session and connect again.<\/p>\n
\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nGLOBAL (conid=0) open_cursors 787 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 0 F\r\n<\/pre>\nParameter has been reset to old value. The same could be achieved by resetting the parameter from pipboy PDB side. If you make change from root container of open_cursors parameter value to 300 then it will be inherited to pipboy PDB too as this PDB inheriting value again.<\/p>\n
From pipboy sys user execute command.<\/p>\n
\r\nalter system set open_cursors = 495;\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\nIsS - is specified in spfile?\r\nAdd %% if needed for parameter name. Search is case insensitive.\r\n\r\nInput system parameter name for like search: open_c%\r\n\r\n1 - System parameter for LIKE with argument open_c%\r\n(active instance parameters)\r\n\r\nContainer Name Parameter Name Parameter Value IsD IsPM\r\n---------------- ------------------------------ -------------------------------- --- ----\r\nPIPBOY open_cursors 495 F T\r\n\r\n\r\n2 - Server Parameter File information for LIKE with argument open_c%\r\n\r\nContainer Name Parameter Name Ord Parameter Value IsS\r\n---------------- ------------------------------ ---- -------------------------------- ---\r\nGLOBAL (conid=0) open_cursors 1 495 T\r\n<\/pre>\n\r\nalter system reset open_cursors;\r\nstart iad\/parameter121\r\n<\/pre>\n\r\nKey for column names\r\nIsD - is default value?\r\nIsPM - is modifiable in pluggable database?\r\n\r\n1 - System parameter for LIKE with argument 'open_cursors'\r\n(active instance parameters)\r\n\r\nParameter Name Parameter Value IsD IsPM Container Name\r\n------------------------------ -------------------------------- --- ---- ----------------\r\nopen_cursors 495 F T PIPBOY\r\n\r\n2 - Server Parameter File information for LIKE with argument 'open_cursors'\r\n\r\nParameter Name Parameter Value IsS Ord Container Name\r\n------------------------------ -------------------------------- --- ---- ----------------\r\nopen_cursors F 0 GLOBAL (conid=0)\r\n<\/pre>\nFrom this point we could set parameter value from root container for all PDB’s or only dynamically apply value for pipboy PDB. Restart of pipboy PDB will result in clearing current memory value and applying inherited value from cdb$root container. One thing to remember is to do not forget about inheritance of PDB parameters from global configuration. To clear situation I’m going to set value for all containers and return old behaviour with inheritance. In the root container:<\/p>\n
\r\nalter system set open_cursors = 300 container = all;\r\n<\/pre>\nAnother thing I wanted to check was path prefix setup on PDB creation time. We cannot change it later, we can set it only during PDB creation. According to documentation my path: \/u02\/oradata\/tcdb\/pipboy\/ is now prefix for all database directories. I’ll create simple directory as pipboy administrator. We need quota on default maxstone tablespace to create expdp table which will be removed after the export job will be done.<\/p>\n
Value for this property can be checked in database_properties view (presented above).<\/p>\n
From maxstone user session execute statements (you can re-login in second session or create new ssh session):<\/p>\n
\r\nalter user maxstone quota unlimited on vault13;\r\ncreate directory maindir as 'maindir';\r\nstart do\/directory112.sql\r\n<\/pre>\n\r\nOwner Directory name Directory path\r\n---------------- -------------------- ----------------------------------------------------\r\nSYS DATA_PUMP_DIR \/u01\/app\/oracle\/admin\/tcdb\/dpdump\/\r\nSYS MAINDIR \/u02\/oradata\/tcdb\/pipboy\/maindir\r\nSYS OPATCH_INST_DIR \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/OPatch\r\nSYS OPATCH_LOG_DIR \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/QOpatch\r\nSYS OPATCH_SCRIPT_DIR \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/QOpatch\r\nSYS ORACLE_BASE \/\r\nSYS ORACLE_HOME \/\r\nSYS XSDDIR \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/rdbms\/xml\/sc\r\n hema\r\n<\/pre>\nTo test this directory we will use expdp from command line. Switch to ssh session (or create one), create subdirectory and change your working directory to newly created one.<\/p>\n
\r\nmkdir \/u02\/oradata\/tcdb\/pipboy\/maindir\r\ncd \/u02\/oradata\/tcdb\/pipboy\/maindir\r\nexpdp userid=maxstone\/MxSt9281@pipboy dumpfile=d1.dmp logfile=d1.log directory=maindir content=metadata_only schemas=maxstone\r\n<\/pre>\nFor security reasons you should never input password in command line command – always use parameter file or input interactively requested string. Returning to main topic – we can see that new files have been created in selected directory:<\/p>\n
\r\npwd\r\n<\/pre>\n\r\n\/u02\/oradata\/tcdb\/pipboy\/maindir\r\n<\/pre>\n\r\nls -l\r\n<\/pre>\n\r\ntotal 252\r\n-rw-r----- 1 oracle oinstall 249856 Apr 14 15:29 d1.dmp\r\n-rw-r--r-- 1 oracle oinstall 1536 Apr 14 15:29 d1.log\r\n<\/pre>\nI’ll try to create absolute path for a directory object using sesssion with maxstone user logged in.<\/p>\n
\r\ncreate directory absdir as '\/u02\/oradata\/tcdb';\r\n<\/pre>\n\r\nexpdp userid=maxstone\/MxSt9281@pipboy dumpfile=d1.dmp logfile=d1.log directory=absdir content=metadata_only schemas=maxstone\r\n<\/pre>\nAnd all looks good. Files where created in \/u02\/oradata\/tcdb directory. From Oracle Database SQL Language Reference under create pluggable database syntax:<\/p>\n
\r\npath_prefix_clause\r\n\r\nUse this clause to ensure that file paths for directory objects associated with the PDB are restricted to the specified directory or its subdirectories.\r\n<\/pre>\nFor me this works only for directories with non-absolute paths. Omit \/ in front of your path and you’ll get your directory inside pointed directory. My theory was correct as I found another statement about this parameter in Administrator’s Guide in a table “38-2 Clauses for Creating a PDB From the Seed” with question\/answer list reviewing all available parameters:<\/p>\n
\r\nDo you want to use a PATH_PREFIX clause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories?\r\n<\/pre>\nThis is for relative paths only. Cleaning up my playground from maxstone user:<\/p>\n
\r\ndrop directory absdir;\r\ndrop directory maindir;\r\n<\/pre>\nAnd removing operating system dump and log files d1.dmp\/d1.log including maindir subdirectory.<\/p>\n
Creating PDB from another PDB<\/h2>\n
We’re going to use pipboy PDB to create a clone named special. Below you can find sql command which will be our starting point for other examples. To introduce PDB parameter pdb_file_name_convert I’m going to omit file_name_convert clause in create pluggable database statement. If your pipboy PDB is open for read write close it and open for read only – it is required to use PDB as a source for cloning. As this is sql statement I’m going to create target directories before issuing commands. From ssh session:<\/p>\n
\r\nmkdir -p \/u02\/oradata\/tcdb\/special\/default_dpdir\r\n<\/pre>\nAnd sql part:<\/p>\n
\r\nalter session set pdb_file_name_convert = '\/u02\/oradata\/tcdb\/pipboy','\/u02\/oradata\/tcdb\/special';\r\nalter pluggable database pipboy close;\r\nalter pluggable database pipboy open read only;\r\ncreate pluggable database special\r\n from pipboy\r\n storage (maxsize 2g max_shared_temp_size 2g)\r\n path_prefix = '\/u02\/oradata\/tcdb\/special\/default_dpdir'\r\n user_tablespaces = all\r\n standbys = all\r\n logging\r\n create_file_dest = '\/u02\/oradata\/tcdb\/special';\r\n<\/pre>\nTo bring online both databases issue commands:<\/p>\n
\r\nalter pluggable database all open read write;\r\n<\/pre>\nYou’ll receive an error:<\/p>\n
\r\nORA-65019: pluggable database PIPBOY already open\r\n<\/pre>\nPDB special have been opened from mount state and this is normal. Pipboy PDB is in read only mode and we can change it’s state using force keyword only.<\/p>\n
\r\nalter pluggable database pipboy open read write force;\r\n<\/pre>\nOther way to open for read write would be closing and opening the PDB. Both options are correct and does not harm your database – even more – currently connected sessions to read only database will be maintained and can continue after command with force clause. To demonstrate changing PDB state first change it’s state from root$PDB container:<\/p>\n
\r\nalter pluggable database pipboy open read only force;\r\n<\/pre>\nCreate new ssh session and connect using sql*plus as maxstone user to pipboy PDB:<\/p>\n
\r\nsqlplus maxstone\/MxSt9281@pipboy\r\n<\/pre>\nCheck your session state and sid:<\/p>\n
\r\nstart sap\/mysession121\r\n<\/pre>\n\r\nUser Name OS User Program CID Service Name Machine\r\n------------ -------------- -------------------------- ---- ------------ ----------------\r\nMAXSTONE oracle sqlplus@odone (TNS V1-V3) 3 pipboy odone\r\n<\/pre>\n\r\nselect userenv('sid') from dual;\r\n<\/pre>\n\r\nUSERENV('SID')\r\n--------------\r\n 414\r\n<\/pre>\n\r\nselect open_mode from v$database;\r\n<\/pre>\n\r\nOPEN_MODE\r\n--------------------\r\nREAD ONLY\r\n<\/pre>\nFrom root container session:<\/p>\n
\r\nalter pluggable database pipboy open read write force;\r\n<\/pre>\nAnd again check maxstone session.<\/p>\n
\r\nselect open_mode from v$database;\r\n<\/pre>\n\r\nOPEN_MODE\r\n--------------------\r\nREAD WRITE\r\n<\/pre>\n\r\nselect userenv('sid') from dual;\r\n<\/pre>\n\r\nUSERENV('SID')\r\n--------------\r\n 414\r\n<\/pre>\nYou can check logon time too just to be sure that’s the same session. As you can see PDB give some flexibility – you can change to read only for cloning time and then to read write after cloning – user sessions will be intact. The same is true when going back to read write. You need to look out for migrate state as it is not allowed to use force keyword to go to\/from this state with force keyword.<\/p>\n
Just to check the state of our new PDB cloned from pipboy PDB check it’s state with couple scripts from root container:<\/p>\n
\r\nstart mt\/container121\r\n<\/pre>\n\r\nKey for column names\r\nCID - Container ID\r\n\r\n CID NAME DbID Size in MB Open mode Open time Res\r\n---- -------------------- ------------ ---------- ---------- ---------------------- ---\r\n 1 CDB$ROOT 918792381 0 READ WRITE 2015-04-19 09:08:10 NO\r\n 2 PDB$SEED 4034246255 845 READ ONLY 2015-04-19 09:08:10 NO\r\n 3 PIPBOY 3788905833 901 READ WRITE 2015-04-19 09:14:27 NO\r\n 4 SPECIAL 3986562878 901 READ WRITE 2015-04-19 09:16:20 NO\r\n<\/pre>\n\r\nstart ts\/datatempfiles121\r\n<\/pre>\n\r\n~~ data files ~~\r\n\r\nTb Name Tb Status Fl ID Fl Name Fl Status Cont. Name\r\n------------ --------- ------ ---------------------------------------- --------- ----------\r\nSYSAUX ONLINE 3 \/u02\/oradata\/tcdb\/sysaux01.dbf AVAILABLE CDB$ROOT\r\nSYSTEM ONLINE 1 \/u02\/oradata\/tcdb\/system01.dbf AVAILABLE CDB$ROOT\r\nUNDOTBS1 ONLINE 5 \/u02\/oradata\/tcdb\/undotbs01.dbf AVAILABLE CDB$ROOT\r\nUSERS ONLINE 6 \/u02\/oradata\/tcdb\/users01.dbf AVAILABLE CDB$ROOT\r\nSYSAUX ONLINE 42 \/u02\/oradata\/tcdb\/pipboy\/sysaux01.dbf AVAILABLE PIPBOY\r\nSYSTEM ONLINE 41 \/u02\/oradata\/tcdb\/pipboy\/system01.dbf AVAILABLE PIPBOY\r\nVAULT13 ONLINE 43 \/u02\/oradata\/tcdb\/pipboy\/vault13_01.dbf AVAILABLE PIPBOY\r\nSYSAUX ONLINE 45 \/u02\/oradata\/tcdb\/special\/TCDB\/140F91809 AVAILABLE SPECIAL\r\n 458086BE0530C38A8C0632D\/datafile\/o1_mf_s\r\n ysaux_bm6ohqh4_.dbf\r\n\r\nSYSTEM ONLINE 44 \/u02\/oradata\/tcdb\/special\/TCDB\/140F91809 AVAILABLE SPECIAL\r\n 458086BE0530C38A8C0632D\/datafile\/o1_mf_s\r\n ystem_bm6ohqgv_.dbf\r\n\r\nVAULT13 ONLINE 46 \/u02\/oradata\/tcdb\/special\/TCDB\/140F91809 AVAILABLE SPECIAL\r\n 458086BE0530C38A8C0632D\/datafile\/o1_mf_v\r\n ault13_bm6ohqh6_.dbf\r\n\r\n\r\n10 rows selected.\r\n\r\n\r\n~~ temp files ~~\r\n\r\nTb Name Tb Status Fl ID Fl Name Fl Status Cont. Name\r\n------------ --------- ------ ---------------------------------------- --------- ----------\r\nTEMP ONLINE 1 \/u02\/oradata\/tcdb\/temp01.dbf ONLINE CDB$ROOT\r\nTEMP ONLINE 3 \/u02\/oradata\/tcdb\/pipboy\/temp01.dbf ONLINE PIPBOY\r\nTEMP ONLINE 4 \/u02\/oradata\/tcdb\/special\/TCDB\/140F91809 ONLINE SPECIAL\r\n 458086BE0530C38A8C0632D\/datafile\/o1_mf_t\r\n emp_bm6ohqh6_.dbf\r\n<\/pre>\nI’ve set up both: pdb_file_name_convert as a session parameter and OMF parameter create_file_dest in create pluggable database clause. This forced Oracle to use OMF as it almost always takes precedence over other parameters. To do not use OMF I’m going to drop this PDB and create it again but using pdb_file_name create session parameter only. From root container session – clean up first:<\/p>\n
\r\nalter pluggable database special close;\r\ndrop pluggable database special including datafiles;\r\n<\/pre>\nAnd create special PDB once more:<\/p>\n
\r\nalter session set pdb_file_name_convert = '\/u02\/oradata\/tcdb\/pipboy','\/u02\/oradata\/tcdb\/special';\r\nalter pluggable database pipboy open read only force;\r\ncreate pluggable database special\r\n from pipboy\r\n storage (maxsize 2g max_shared_temp_size 2g)\r\n path_prefix = '\/u02\/oradata\/tcdb\/special\/default_dpdir'\r\n user_tablespaces = all\r\n standbys = all\r\n logging\r\n create_file_dest = none;\r\n<\/pre>\nIn my case above command returned an error:<\/p>\n
\r\nORA-65005: missing or invalid file name pattern for file -\r\n\/u02\/oradata\/tcdb\/pipboy\/system01.dbf\r\n<\/pre>\nUnfortunately create_file_dest = none did not force Oracle Database to drop this relation with OMF and still something went wrong. Corrected command:<\/p>\n
\r\ncreate pluggable database special\r\n from pipboy\r\n storage (maxsize 2g max_shared_temp_size 2g)\r\n path_prefix = '\/u02\/oradata\/tcdb\/special\/default_dpdir'\r\n user_tablespaces = all\r\n standbys = all\r\n logging;\r\n<\/pre>\nJust remove create_file_dest clause from command and all will be good.<\/p>\n
\r\nalter pluggable database pipboy open read write force;\r\nalter pluggable database special open read write;\r\nstart ts\/datatempfiles121\r\n<\/pre>\n\r\n~~ data files ~~\r\n\r\nTb Name Tb Status Fl ID Fl Name Fl Status Cont. Name\r\n------------ --------- ------ ---------------------------------------- --------- ----------\r\nSYSAUX ONLINE 3 \/u02\/oradata\/tcdb\/sysaux01.dbf AVAILABLE CDB$ROOT\r\nSYSTEM ONLINE 1 \/u02\/oradata\/tcdb\/system01.dbf AVAILABLE CDB$ROOT\r\nUNDOTBS1 ONLINE 5 \/u02\/oradata\/tcdb\/undotbs01.dbf AVAILABLE CDB$ROOT\r\nUSERS ONLINE 6 \/u02\/oradata\/tcdb\/users01.dbf AVAILABLE CDB$ROOT\r\nSYSAUX ONLINE 42 \/u02\/oradata\/tcdb\/pipboy\/sysaux01.dbf AVAILABLE PIPBOY\r\nSYSTEM ONLINE 41 \/u02\/oradata\/tcdb\/pipboy\/system01.dbf AVAILABLE PIPBOY\r\nVAULT13 ONLINE 43 \/u02\/oradata\/tcdb\/pipboy\/vault13_01.dbf AVAILABLE PIPBOY\r\nSYSAUX ONLINE 48 \/u02\/oradata\/tcdb\/special\/sysaux01.dbf AVAILABLE SPECIAL\r\nSYSTEM ONLINE 47 \/u02\/oradata\/tcdb\/special\/system01.dbf AVAILABLE SPECIAL\r\nVAULT13 ONLINE 49 \/u02\/oradata\/tcdb\/special\/vault13_01.dbf AVAILABLE SPECIAL\r\n\r\n10 rows selected.\r\n\r\n\r\n~~ temp files ~~\r\n\r\nTb Name Tb Status Fl ID Fl Name Fl Status Cont. Name\r\n------------ --------- ------ ---------------------------------------- --------- ----------\r\nTEMP ONLINE 1 \/u02\/oradata\/tcdb\/temp01.dbf ONLINE CDB$ROOT\r\nTEMP ONLINE 3 \/u02\/oradata\/tcdb\/pipboy\/temp01.dbf ONLINE PIPBOY\r\nTEMP ONLINE 4 \/u02\/oradata\/tcdb\/special\/temp01.dbf ONLINE SPECIAL\r\n<\/pre>\nThis time file names are much more understandable for us.<\/p>\n
Creating PDB from non-cdb<\/h2>\n
I’ve created simple database with a name molerat as a non-CDB database. Before conversion I’ll put there some database objects which will be converted as PDB objects. All database files are clustered in one directory: \/u02\/oradata\/molerat\/. From sys user I’m starting with two users, couple tablespaces, tables and some very simple data.<\/p>\n
\r\ncreate tablespace megaton_data datafile '\/u02\/oradata\/molerat\/megaton_data_01.dbf' size 16m autoextend on next 8m maxsize 128m;\r\ncreate tablespace megaton_indx datafile '\/u02\/oradata\/molerat\/megaton_indx_01.dbf' size 16m autoextend on next 8m maxsize 128m;\r\ncreate temporary tablespace megaton_temp tempfile '\/u02\/oradata\/molerat\/megaton_temp_01.dbf' size 8m autoextend on next 8m maxsize 256m;\r\ncreate user agnes identified by agnes\r\n default tablespace megaton_data\r\n temporary tablespace megaton_temp\r\n quota unlimited on megaton_data\r\n quota unlimited on megaton_indx\r\n account unlock;\r\ncreate role megaton_citizen;\r\ngrant create session, create table, create sequence, create synonym, create view to megaton_citizen;\r\ngrant megaton_citizen to agnes;\r\ncreate user lewis identified by lewis\r\n default tablespace megaton_data\r\n temporary tablespace megaton_temp\r\n quota unlimited on megaton_data\r\n quota unlimited on megaton_indx\r\n account unlock;\r\ngrant megaton_citizen to lewis;\r\n\r\nconnect agnes\/agnes\r\n\r\ncreate table room\r\n(\r\n id number(10),\r\n name varchar2(128),\r\n floor number(2)\r\n) tablespace megaton_data;\r\n\r\ncreate unique index room_uidx1 on room(id)\r\n tablespace megaton_indx;\r\nalter table room\r\n add constraint room_pk\r\n primary key (id)\r\n using index room_uidx1;\r\n\r\ncreate table citizen\r\n(\r\n id number(16),\r\n firstname varchar2(32),\r\n surename varchar2(64),\r\n room_id number(10)\r\n) tablespace megaton_data;\r\n\r\ncreate unique index citizen_uidx1 on citizen(id)\r\n tablespace megaton_indx;\r\nalter table citizen\r\n add constraint citizen_pk\r\n primary key (id)\r\n using index citizen_uidx1;\r\nalter table citizen\r\n add constraint citizen_room_fk\r\n foreign key (room_id) referencing room(id);\r\n\r\ncreate sequence room_seq\r\n start with 1 increment by 1 cache 100;\r\ncreate sequence citizen_seq\r\n start with 1 increment by 1 cache 100;\r\n\r\ndeclare\r\n v_room_id room.id%type;\r\nbegin\r\n v_room_id := room_seq.nextval;\r\n insert into room values (v_room_id, 'summer heat', 1);\r\n insert into citizen values (citizen_seq.nextval, 'Jimmy', 'James', v_room_id);\r\n insert into citizen values (citizen_seq.nextval, 'Shannon', 'Shawn', v_room_id);\r\n \r\n v_room_id := room_seq.nextval;\r\n insert into room values (v_room_id, 'dirty water', 3);\r\n insert into citizen values (citizen_seq.nextval, 'Danny', 'Dean', v_room_id);\r\n insert into citizen values (citizen_seq.nextval, 'Mickey', 'Mick', v_room_id); \r\nend;\r\n\/\r\n\r\ncommit;\r\n\r\ngrant select, referencing on room to lewis;\r\n\r\nconnect lewis\/lewis\r\n\r\ncreate table gun\r\n(\r\n id number(8),\r\n name varchar2(32),\r\n room_id number(10)\r\n) tablespace megaton_data;\r\n\r\ncreate unique index gun_uidx1 on gun(id)\r\n tablespace megaton_indx;\r\nalter table gun\r\n add constraint gun_pk\r\n primary key (id)\r\n using index gun_uidx1;\r\nalter table gun\r\n add constraint gun_room_fk\r\n foreign key (room_id) referencing agnes.room(id);\r\n\r\ncreate sequence gun_seq\r\n start with 1 increment by 1 cache 100;\r\n\r\ninsert into gun values (gun_seq.nextval, 'big gun', (select min(id) from agnes.room));\r\ninsert into gun values (gun_seq.nextval, 'tiny gun', (select max(id) from agnes.room));\r\ncommit;\r\n\r\nconnect \/ as sysdba\r\n<\/pre>\nThis is our simple application which we’ll check, if it still exists in PDB after conversion. To see those objects we need couple simple queries with very predictable results. Run couple scripts for validation. As sysdba execute:<\/p>\n
\r\nstart do\/constraint112\r\n<\/pre>\n\r\nKey for column names\r\nCT - Constraint type:\r\n..C - Check constraint on a table\r\n..P - Primary key\r\n..U - Unique key\r\n..R - Referential integrity\r\n..V - With check option, on a view\r\n..O - With read only, on a view\r\n..H - Hash expression\r\n..F - Constraint that involves a REF column\r\n..S - Supplemental logging\r\nAdd %% if needed for owner name. Search is case insensitive.\r\n\r\nInput constraints owner: LEWIS\r\nConstraints with owner LIKE LEWIS\r\n\r\nOwner Table name Constraint name CT Refer. obj. Ref. constr. nam\r\n---------------- -------------------- ---------------- -- ------------ ----------------\r\nLEWIS GUN GUN_PK P\r\nLEWIS GUN GUN_ROOM_FK R AGNES ROOM_PK\r\n<\/pre>\n\r\nstart do\/constraint112\r\n<\/pre>\n\r\nInput constraints owner: agnes\r\nConstraints with owner LIKE agnes\r\n\r\nOwner Table name Constraint name CT Refer. obj. Ref. constr. nam\r\n---------------- -------------------- ---------------- -- ------------ ----------------\r\nAGNES CITIZEN CITIZEN_PK P\r\nAGNES CITIZEN CITIZEN_ROOM_FK R AGNES ROOM_PK\r\nAGNES ROOM ROOM_PK P\r\n<\/pre>\nAll objects in schemas can be listed through another script.<\/p>\n
\r\nstart do\/object112\r\n<\/pre>\n\r\nAdd %% if needed for owner name. Search is case insensitive.\r\n\r\nInput objects owner: lewis\r\nObjects with owner LIKE lewis\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX GUN_UIDX1 2015-04-19 10:54:58 VALID\r\nSEQUENCE GUN_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE GUN 2015-04-19 10:54:58 VALID\r\n<\/pre>\n\r\nstart dbobjects\r\n<\/pre>\n\r\nAdd %% if needed for owner name. Search is case insensitive.\r\n\r\nInput objects owner: agnes\r\nObjects with owner LIKE agnes\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX CITIZEN_UIDX1 2015-04-19 10:54:57 VALID\r\nINDEX ROOM_UIDX1 2015-04-19 10:54:57 VALID\r\nSEQUENCE CITIZEN_SEQ 2015-04-19 10:54:58 VALID\r\nSEQUENCE ROOM_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE CITIZEN 2015-04-19 10:54:57 VALID\r\nTABLE ROOM 2015-04-19 10:54:57 VALID\r\n<\/pre>\nAnd to see segment allocation in tablespaces simple query from dba_segments:<\/p>\n
\r\ncol segment_name for a32\r\ncol tablespace_name for a32\r\nselect segment_name, tablespace_name, bytes\r\n from dba_segments\r\n where owner in ('AGNES','LEWIS');\r\n<\/pre>\n\r\nSEGMENT_NAME TABLESPACE_NAME BYTES\r\n-------------------------------- -------------------------------- ----------\r\nGUN MEGATON_DATA 65536\r\nCITIZEN MEGATON_DATA 65536\r\nROOM MEGATON_DATA 65536\r\nGUN_UIDX1 MEGATON_INDX 65536\r\nCITIZEN_UIDX1 MEGATON_INDX 65536\r\nROOM_UIDX1 MEGATON_INDX 65536\r\n<\/pre>\nAll is ready for our test. We’ll try to clone non-CDB to our tcdb as a PDB. From root container create a database link to molerat database. It’s not written anywhere in documentation but I assume that I should connect as system user. I’ve created tnsnames entry for my molerat database with the same name to ease database link creation:<\/p>\n
\r\n# molerat - non-CDB for cloning to tcdb\r\nMOLERAT =\r\n (DESCRIPTION =\r\n (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))\r\n (CONNECT_DATA =\r\n (SERVER = DEDICATED)\r\n (SERVICE_NAME = molerat)\r\n )\r\n )\r\n<\/pre>\nThen create directory for molerat database files in tcdb database.<\/p>\n
\r\ncreate database link molerat\r\n connect to system identified by manager\r\n using 'molerat';\r\n!mkdir \/u02\/oradata\/tcdb\/molerat\/\r\n<\/pre>\nIssue command for non-CDB cloning using from clause in create pluggable database command.<\/p>\n
\r\ncreate pluggable database molerat_clone\r\n from non$cdb@molerat\r\n storage unlimited\r\n file_name_convert = ('\/u02\/oradata\/molerat\/','\/u02\/oradata\/tcdb\/molerat\/')\r\n user_tablespaces = all\r\n logging\r\n create_file_dest = none;\r\n<\/pre>\nCloned database is now in mounted state. Notice that in documentation it is required to put source database into read only mode. We didn’t do that and there were no errors. In production environment you should always put source non-CDB database in read only mode before cloning. create_file_dest clause works fine too and file names aren’t created with OMF enabled – only converted paths were applied here. Next step is to convert this non-CDB to PDB using dedicated script. First check your current status of your PDB’s.<\/p>\n
\r\nstart mt\/container121\r\n<\/pre>\n\r\nKey for column names\r\nCID - Container ID\r\n\r\n CID NAME DbID Size in MB Open mode Open time Res\r\n---- -------------------- ------------ ---------- ---------- ---------------------- ---\r\n 1 CDB$ROOT 918792381 0 READ WRITE 2015-04-19 09:08:10 NO\r\n 2 PDB$SEED 4034246255 845 READ ONLY 2015-04-19 09:08:10 NO\r\n 3 PIPBOY 3788905833 901 READ WRITE 2015-04-19 09:14:27 NO\r\n 4 SPECIAL 510174438 901 READ WRITE 2015-04-19 09:26:11 NO\r\n 5 MOLERAT_CLONE 2993991422 0 MOUNTED 2015-04-19 11:08:50\r\n<\/pre>\nSwitch you session to molerat_clone container and start dedicated script for conversion from non-CDB to PDB.<\/p>\n
\r\nalter session set container = molerat_clone;\r\n@$ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql\r\n<\/pre>\nDuring execution of this script my session hangs on recompilation with a command utl_recomp.recomp_parallel – as I have only couple database objects which do not require recompilation I thought that I shouldn’t even see this prompt – but still – this job took 12c huge amount of time (*in my virtual environment). I’ve hit enter after some time – maybe this was only a coincidence but script react and moved forward almost instantly.<\/p>\n
To finish cloning process we need to open the database for read write (do not use read only as this can cause error – according to documentation).<\/p>\n
\r\nalter database open read write;\r\nexit\r\n<\/pre>\nAs the script totally mess up your sql*plus session with strange settings I recommend to exit from this session and login again to root container and check status of all containers. Check if your environment is setup to connect to tcdb. Add tnsnames.ora entry to connect to PDB via listener.<\/p>\n
\r\n# molerat_clone - cloned non-cdb to pdb in tcdb\r\nMOLERAT_CLONE =\r\n (DESCRIPTION =\r\n (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))\r\n (CONNECT_DATA =\r\n (SERVER = DEDICATED)\r\n (SERVICE_NAME = MOLERAT_CLONE)\r\n )\r\n )\r\n<\/pre>\n\r\nsqlplus \/ as sysdba\r\n<\/pre>\n\r\nstart mt\/container121\r\n<\/pre>\n\r\nKey for column names\r\nCID - Container ID\r\n\r\n CID NAME DbID Size in MB Open mode Open time Res\r\n---- -------------------- ------------ ---------- ---------- ---------------------- ---\r\n 1 CDB$ROOT 918792381 0 READ WRITE 2015-04-19 09:08:10 NO\r\n 2 PDB$SEED 4034246255 845 READ ONLY 2015-04-19 09:08:10 NO\r\n 3 PIPBOY 3788905833 901 READ WRITE 2015-04-19 09:14:27 NO\r\n 4 SPECIAL 510174438 901 READ WRITE 2015-04-19 09:26:11 NO\r\n 5 MOLERAT_CLONE 2993991422 1315 READ WRITE 2015-04-19 11:26:20 NO\r\n<\/pre>\nFor some unknown reason my cloned PDB has the same size as source database counting only allocated space of the database files. Source database, counting by segments, has 686 MB and 1487 MB of allocated space in dba_data_files. What happened during the conversion – maybe some dictionary objects were not converted as common and they exists as common and local in the same time (see the difference between pipboy and molerat_clone sizes above – both PDBs are almost empty). Counting internally from molerat_clone PDB database has allocated 1287 MB and used by segments 606 MB. I think that this process still can be improved as I used the same Oracle Database binaries for creating tcdb container database and molerat non-cdb database (for cloning from non-CDB to PDB you have to use 12.1.0.2.0 or later version).<\/p>\n
Quickly check database objects state. Add tnsname entry in tnsname.ora file for newly created PDB and connect as agnes. In separate ssh session connect to tcdb PDB named molerat_clone. Then connect as lewis to check his accessibility and finaly switch to sys user and execute scripts as previously in molerat non-cdb.<\/p>\n
\r\nsqlplus agnes\/agnes@molerat_clone\r\n<\/pre>\n\r\nconnect lewis\/lewis@molerat_clone\r\nconnect sys\/manager@molerat_clone as sysdba\r\nstart do\/constraint112\r\n<\/pre>\n\r\nInput constraints owner: agnes\r\nConstraints with owner LIKE agnes\r\n\r\nOwner Table name Constraint name CT Refer. obj. Ref. constr. nam\r\n---------------- -------------------- ---------------- -- ------------ ----------------\r\nAGNES CITIZEN CITIZEN_PK P\r\nAGNES CITIZEN CITIZEN_ROOM_FK R AGNES ROOM_PK\r\nAGNES ROOM ROOM_PK P\r\n<\/pre>\n\r\nstart do\/constraint112\r\n<\/pre>\n\r\nInput constraints owner: lewis\r\nConstraints with owner LIKE lewis\r\n\r\nOwner Table name Constraint name CT Refer. obj. Ref. constr. nam\r\n---------------- -------------------- ---------------- -- ------------ ----------------\r\nLEWIS GUN GUN_PK P\r\nLEWIS GUN GUN_ROOM_FK R AGNES ROOM_PK\r\n<\/pre>\n\r\nstart do\/object112\r\n<\/pre>\n\r\nInput objects owner: agnes\r\nObjects with owner LIKE agnes\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX CITIZEN_UIDX1 2015-04-19 10:54:57 VALID\r\nINDEX ROOM_UIDX1 2015-04-19 10:54:57 VALID\r\nSEQUENCE CITIZEN_SEQ 2015-04-19 10:54:58 VALID\r\nSEQUENCE ROOM_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE CITIZEN 2015-04-19 10:54:57 VALID\r\nTABLE ROOM 2015-04-19 10:54:57 VALID\r\n<\/pre>\n\r\nstart do\/object112\r\n<\/pre>\n\r\nInput objects owner: lewis\r\nObjects with owner LIKE lewis\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX GUN_UIDX1 2015-04-19 10:54:58 VALID\r\nSEQUENCE GUN_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE GUN 2015-04-19 10:54:58 VALID\r\n<\/pre>\nAnd separate script for segments:<\/p>\n
\r\ncol segment_name for a32\r\ncol tablespace_name for a32\r\nselect segment_name, tablespace_name, bytes\r\n from dba_segments\r\n where owner in ('AGNES','LEWIS');\r\n<\/pre>\n\r\nSEGMENT_NAME TABLESPACE_NAME BYTES\r\n-------------------------------- -------------------------------- ----------\r\nGUN MEGATON_DATA 65536\r\nCITIZEN MEGATON_DATA 65536\r\nROOM MEGATON_DATA 65536\r\nGUN_UIDX1 MEGATON_INDX 65536\r\nCITIZEN_UIDX1 MEGATON_INDX 65536\r\nROOM_UIDX1 MEGATON_INDX 65536\r\n<\/pre>\nLooks good. Try to add another row in lewis table using simple insert.<\/p>\n
\r\nconnect lewis\/lewis@molerat_clone\r\ninsert into gun values (gun_seq.nextval, 'old gun', (select min(id) from agnes.room));\r\ninsert into gun values (gun_seq.nextval, 'shiny gun', (select max(id) from agnes.room));\r\ncommit;\r\nselect *\r\n from gun;\r\n<\/pre>\n\r\n ID NAME ROOM_ID\r\n---------- -------------------------------- ----------\r\n 1 big gun 1\r\n 2 tiny gun 2\r\n 101 old gun 1\r\n 102 shiny gun 2\r\n<\/pre>\n\r\nselect *\r\n from session_roles;\r\n<\/pre>\n\r\nROLE\r\n--------------------------------------------------------------------------------------------\r\nMEGATON_CITIZEN\r\n<\/pre>\nCopying PDB to another CDB<\/h2>\n
Some time ago I’ve created another CDB and named her beecdb. Right now I’m going to use it to clone molerat_clone to this container database.<\/p>\n
In target CDB from root container create new database link to molerat PDB in tcdb database.<\/p>\n
\r\ncreate database link molerat_clone\r\n connect to system identified by manager\r\n using 'molerat_clone';\r\n!mkdir \/u02\/oradata\/BEECDB\/molerat_clone2\r\n<\/pre>\nFrom tcdb CDB switch molerat_clone PDB to read only mode.<\/p>\n
\r\nalter pluggable database molerat_clone open read only force;\r\n<\/pre>\nIssue command from a beecdb database to clone PDB using from clause in create pluggable database command. Remember to connect as sys in root container. After creating PDB open it.<\/p>\n
\r\ncreate pluggable database molerat_clone2\r\n from molerat_clone@molerat_clone\r\n storage unlimited\r\n file_name_convert = ('\/u02\/oradata\/tcdb\/molerat','\/u02\/oradata\/BEECDB\/molerat_clone2')\r\n user_tablespaces = all\r\n logging\r\n create_file_dest = none;\r\nalter pluggable database molerat_clone2 open read write;\r\n<\/pre>\nAfter creating PDB switch current container to check if there are the database objects which were created earlier.<\/p>\n
\r\nalter session set container = molerat_clone2;\r\n<\/pre>\n\r\nstart do\/object112\r\n<\/pre>\n\r\nInput objects owner: agnes\r\nObjects with owner LIKE agnes\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX CITIZEN_UIDX1 2015-04-19 10:54:57 VALID\r\nINDEX ROOM_UIDX1 2015-04-19 10:54:57 VALID\r\nSEQUENCE CITIZEN_SEQ 2015-04-19 10:54:58 VALID\r\nSEQUENCE ROOM_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE CITIZEN 2015-04-19 10:54:57 VALID\r\nTABLE ROOM 2015-04-19 10:54:57 VALID\r\n<\/pre>\n\r\nstart do\/object112\r\n<\/pre>\n\r\nInput objects owner: lewis\r\nObjects with owner LIKE lewis\r\n\r\nObject type Object name Creation date Status\r\n-------------------- ------------------------------ ------------------- -------\r\nINDEX GUN_UIDX1 2015-04-19 10:54:58 VALID\r\nSEQUENCE GUN_SEQ 2015-04-19 10:54:58 VALID\r\nTABLE GUN 2015-04-19 10:54:58 VALID\r\n<\/pre>\nConstraints are the same too. Looks like this procedure works just fine.<\/p>\n
Moving PDB between CDBs<\/h2>\n
There is one more scenario which I would like to test – unplug PDB from beecdb container and then plug into tcdb. First, unplug molerat_clone2 from beecdb. Use a connection to root container – switch between containers if needed.<\/p>\n
\r\nalter session set container = cdb$root;\r\nalter pluggable database molerat_clone2 close;\r\nalter pluggable database molerat_clone2 unplug into '\/u02\/oradata\/BEECDB\/molerat_clone2\/molerat_clone2_meta.xml';\r\nstart mt\/pdb12102\r\n<\/pre>\n\r\nKey for column names\r\nFL - force logging\r\nFnL - force nologging\r\nCID - Container ID\r\n\r\n CID Pluggable database name Status Logging FL FnL\r\n---- -------------------------------- ------------- --------- --- ---\r\n 2 PDB$SEED NORMAL LOGGING NO NO\r\n 3 MOLERAT_CLONE2 UNPLUGGED LOGGING NO NO\r\n<\/pre>\nWe can see that our molerat_clone2 status has changed to unplugged. This PDB is still part of the database and will be in backupset if you’ll start rman backup of the database. I’ve checked this by issuing command from rman (open a new session to check this command if needed):<\/p>\n
\r\nrman target "'\/ as sysbackup'"\r\n<\/pre>\n\r\nbackup as compressed backupset incremental level 0 database plus archivelog delete all input;\r\n<\/pre>\nIn output I can find:<\/p>\n
\r\n[...]\r\nchannel ORA_DISK_1: starting compressed incremental level 0 datafile backup set\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set\r\ninput datafile file number=00019 name=\/u02\/oradata\/BEECDB\/molerat_clone2\/system01.dbf\r\ninput datafile file number=00020 name=\/u02\/oradata\/BEECDB\/molerat_clone2\/sysaux01.dbf\r\ninput datafile file number=00022 name=\/u02\/oradata\/BEECDB\/molerat_clone2\/megaton_data_01.dbf\r\ninput datafile file number=00023 name=\/u02\/oradata\/BEECDB\/molerat_clone2\/megaton_indx_01.dbf\r\ninput datafile file number=00021 name=\/u02\/oradata\/BEECDB\/molerat_clone2\/users01.dbf\r\nchannel ORA_DISK_1: starting piece 1 at 19-APR-15\r\n[...]\r\n<\/pre>\nNow let’s clone this PDB in the same database first. To do so we need to use as clone clause and rename database files during copy. First create target directory for new files.<\/p>\n
\r\n!mkdir \/u02\/oradata\/BEECDB\/molerat_clone44\/\r\n<\/pre>\nAnd execute cloning command:<\/p>\n
\r\ncreate pluggable database molerat_clone44\r\n as clone using '\/u02\/oradata\/BEECDB\/molerat_clone2\/molerat_clone2_meta.xml'\r\n copy file_name_convert = ('\/u02\/oradata\/BEECDB\/molerat_clone2\/', '\/u02\/oradata\/BEECDB\/molerat_clone44\/')\r\n storage unlimited\r\n path_prefix = '\/u02\/oradata\/BEECDB\/molerat_clone44'\r\n user_tablespaces = all\r\n logging;\r\n<\/pre>\nI’m not using source_file_name_convert because I didn’t move the files anywhere earlier (manually). They are in the same place, where they were during unplugging from beecdb and I need to use file_name_convert (or initialization parameter pdb_file_name_convert) to copy files to correct location.<\/p>\n
\r\nstart mt\/pdb12102\r\n<\/pre>\n\r\nKey for column names\r\nFL - force logging\r\nFnL - force nologging\r\nCID - Container ID\r\n\r\n CID Pluggable database name Status Logging FL FnL\r\n---- -------------------------------- ------------- --------- --- ---\r\n 2 PDB$SEED NORMAL LOGGING NO NO\r\n 3 MOLERAT_CLONE2 UNPLUGGED LOGGING NO NO\r\n 4 MOLERAT_CLONE44 NEW LOGGING NO NO\r\n<\/pre>\nOur newly created PDB is in place. Let’s check what happens during opening of molerat_clone44.<\/p>\n
\r\nalter pluggable database molerat_clone44 open read write;\r\n<\/pre>\nStatus is switched to normal and this PDB is available for use along with agnes and lewis users. Another attempt will be to use this unplugged database but I will move those files first. Just to clear this CDB of old entries I’m going to remove this PDB from beecdb but leave the files intact.<\/p>\n
\r\ndrop pluggable database molerat_clone2 keep datafiles;\r\nstart mt\/pdb12102\r\n<\/pre>\n\r\nKey for column names\r\nFL - force logging\r\nFnL - force nologging\r\nCID - Container ID\r\n\r\n CID Pluggable database name Status Logging FL FnL\r\n---- -------------------------------- ------------- --------- --- ---\r\n 2 PDB$SEED NORMAL LOGGING NO NO\r\n 4 MOLERAT_CLONE44 NORMAL LOGGING NO NO\r\n<\/pre>\nPDB molerat_clone2 vanished from the list. Now I’ll move those files to new location. I’m creating new directory:<\/p>\n
\r\n!mv \/u02\/oradata\/BEECDB\/molerat_clone2 \/u02\r\n<\/pre>\nNow paths from xml file points to location \/u02\/oradata\/BEECDB\/molerat_clone2\/ but it’s not true anymore. They are located in \/u02\/molerat_clone2\/. I’m switching to tcdb database and I’ll try to copy those files with directory conversion to copy files to \/u02\/oradata\/tcdb\/molerat_clone37. First, I’ll create target directory.<\/p>\n
\r\nmkdir \/u02\/oradata\/tcdb\/molerat_clone37\r\n<\/pre>\nAnd now I’m ready to create new PDB from xml using this unplugged PDB. From root container in CDB named tcdb:<\/p>\n
\r\ncreate pluggable database molerat_clone37\r\n using '\/u02\/molerat_clone2\/molerat_clone2_meta.xml'\r\n source_file_name_convert = ('\/u02\/oradata\/BEECDB\/molerat_clone2\/', '\/u02\/molerat_clone2\/')\r\n copy file_name_convert = ('\/u02\/molerat_clone2\/', '\/u02\/oradata\/tcdb\/molerat_clone37\/')\r\n storage unlimited\r\n path_prefix = '\/u02\/oradata\/tcdb\/molerat_clone37'\r\n user_tablespaces = all\r\n logging;\r\n<\/pre>\n\r\nstart mt\/pdb12102\r\n<\/pre>\n\r\nKey for column names\r\nFL - force logging\r\nFnL - force nologging\r\nCID - Container ID\r\n\r\n CID Pluggable database name Status Logging FL FnL\r\n---- -------------------------------- ------------- --------- --- ---\r\n 2 PDB$SEED NORMAL LOGGING NO NO\r\n 3 PIPBOY NORMAL LOGGING NO NO\r\n 4 SPECIAL NORMAL LOGGING NO NO\r\n 5 MOLERAT_CLONE NORMAL LOGGING NO NO\r\n 6 MOLERAT_CLONE37 NEW LOGGING NO NO\r\n<\/pre>\n\r\nalter pluggable database molerat_clone37 open read write;\r\n<\/pre>\nYou can check details about all database files using ts\/datatempfiles121.sql script and switch to molerat_clone37 container and see database objects through do\/object112.sql script as previously. The last attempt would be with move option to create new PDB in tcdb container database with relocation of the database files. First, I’m going to drop this pluggable database and then create new one again. Command to do this from tcdb root container:<\/p>\n
\r\nalter pluggable database molerat_clone37 close;\r\ndrop pluggable database molerat_clone37 including datafiles;\r\n\r\ncreate pluggable database molerat_clone37\r\n using '\/u02\/molerat_clone2\/molerat_clone2_meta.xml'\r\n source_file_name_convert = ('\/u02\/oradata\/BEECDB\/molerat_clone2\/', '\/u02\/molerat_clone2\/')\r\n move file_name_convert = ('\/u02\/molerat_clone2\/', '\/u02\/oradata\/tcdb\/molerat_clone37\/')\r\n storage unlimited\r\n path_prefix = '\/u02\/oradata\/tcdb\/molerat_clone37'\r\n user_tablespaces = all\r\n logging;\r\n\r\nalter pluggable database molerat_clone37 open read write;\r\n<\/pre>\nI can see that (again) all files are in place and PDB works properly.<\/p>\n
In \/u02\/molerat_clone2\/ directory no database files were kept, only molerat_clone2_meta.xml file was not deleted (looks like it’s auxiliary file for the database – it’s not registered anywhere) – they were moved to \/u02\/oradata\/tcdb\/molerat_clone37. During this test you could see that moving files inside the same file system was really fast just like with mv os command. Cleaning up after my copy operations:<\/p>\n
\r\nrm -rf \/u02\/molerat_clone2\r\n<\/pre>\nUsing xml file is a good way to copy unplugged PDB and restore it from the “sands of time”. I cannot see clearly the benefits of unplugged PDB but maybe with next releases we would see something funky right here too – right now unplugged PDB can be only dropped. This way she will vanish from rman backup list.<\/p>\n
Summary<\/h2>\n
It’s worth to mention that moving from 12.1.0.1.0 to 12.1.0.2.0 brings some facility in a matter of PDB creation from non-CDB – we don’t have to use anymore (manually) script or DBMS package to extract some xml files, import it to target CDB and follow complex procedure. Right now you’ll just create a database link and execute script which accommodate this new PDB in your CDB. Old way with xml file is still valid, especially in environment where you cannot use database link to connect directly with source container database.<\/p>\n
Beside standard procedure you can use optional step in plugging in unplugged PDB into CDB – DBMS_PDB package. You can describe PDB if the xml file is lost using recover procedure, use describe routine to generate xml file which can be used to check, if this PDB can be plugged into different container or even check the compatibility for such operation between CDB’s by using check_plug_compatibility subprogram.<\/p>\n
Looks like Multitenant idea will be explored by Oracle more deeply in future releases and we can expect new functionality with each cumulative update.<\/p>\n","protected":false},"excerpt":{"rendered":"
Multitenant CDB and PDBs Creating new Pluggable Database should be easy and straightforward as much as possible – Oracle claims that this technology can lead to many savings through firing couple useless database administrators who manage databases as a standalone beings. With 12c and multitenant option you can consolidate databases, leverage costs on database administrators,…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/108"}],"collection":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=108"}],"version-history":[{"count":7,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/108\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/108\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}