New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    701

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

12c Database : Create PDB’s , Plug & Unplug

Create & Plug and Unplug PDB's

To Create PDB's the following methods can be followed

Copying

  • From PDB Seed

Cloning from a existing PDB using

  • Locally
  • Remotely through DB Link

Plugging in

  • Plugging in an unplugged DB (unplug and plug as database into same container database)
  • Plugging in a NON-CDB  as a PDB (NON-CDB database converting to PDB database into a container database)

Before getting Started the options or clauses using for Create/Alter Pluggable database are

Storage Limits :- You can specify the limit of the size of the PDB you create  

MAX_SIZE = The amount of storage that can be used by all tablespaces that belong to the PDB
MAX_SHARED_TEMP_SIZE = The amount of storage in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB

File Locations

    PATH_PREFIX = Restricting the PDB to specified directory so that all files placed in this directory
    Example:- PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

    FILE_NAME_CONVERT = Convert the location of the files to this location while creating PDB
    Example:- FILE_NAME_CONVERT = ('/oracle/dbs/', '/oracle/pdb5/')
    Note: If this parameter is not set, Oracle looks for pdb_file_name_convert parameter and converts accordingly
   

    SOURCE_FILE_NAME_CONVERT= When unplugging and plugging databases, Using this parameter will convert the names of the files that different of XML
    SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/pdb7/', '/disk2/oracle/pdb7/')

Temp File Reuse

    TEMPFILE Reuse:- Clause reuse the temp file in the location that PDB going to create.

Snapshot copy

Examples:-

 

Task Method Description Command
Create PDB Copying From Seed PDB creates a PDB (Assuming OMF is enabled or PDB_FILE_NAME_CONVERT is specified) SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;
  Copying From Seed PDB creates a PDB with a pdb dba role assigned to user SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password ROLES=(DBA);
  Copying From Seed PDB
  • A PDB with name SALESPDB to be create
    Admin user (local) salesadm to be create
    Size of tablespaces should not exceed more than 2gb
  • Shared Temporary tables should not exceed by this PDB database session by 100m
  • Default tablespace set as sales and datafile as below with 250M
  • all the files that creates in this PDB automatically relative to
  • this /disk1/oracle/dbs/salespdb/ path
    file name Convert the seed location to target location

SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password 
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE sales        DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON 
PATH_PREFIX = '/disk1/oracle/dbs/salespdb/' 
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');
  Cloning DB Local Example clones a PDB1 to PDB2 SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
    example clones a PDB1 to PDB2 with the path_prefix changes and changing the file name paths SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
PATH_PREFIX = '/disk2/oracle/pdb2'
FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/');
    Cloning from local PDB with storage limit clauses

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

  Clone DB from Remote Creates a PDB from a remote PDB SQL CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;
Plugging IN Unplug and plug as new To unplug first check the compatibility and also to flat xml file

set serveroutput on

DECLARE
   compatible BOOLEAN := FALSE;
BEGIN  
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
   else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
   end if;
END;
/
    Plug the database using NOCOPY
Means all the datafiles should be in same location no copy required

create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml'
NOCOPY
TEMPFILE REUSE;

Verify the status

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';

select open_mode from v$pdbs where name='PDB_PLUG_COPY';

alter pluggable database pdb_plug_nocopy open;
    Plug the database using COPY method
Means all the datafiles should be copied from location to target location

mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy

sqlplus / as sysdba

create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml'
COPY
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');

Verify the status

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';

select open_mode from v$pdbs where name='PDB_PLUG_COPY';

alter pluggable database pdb_plug_copy open;
    Plug the database using MOVE method when cloning

mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move

sqlplus / as sysdba

create pluggable database pdb_plug_move
AS CLONE using '/u01/app/oracle/oradata/pdb2.xml'
MOVE
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');

Verify the status

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE';

select open_mode from v$pdbs where name='PDB_PLUG_MOVE';

alter pluggable database pdb_plug_move open;

Unplugging Unplug database from CDB Unplug and export to Flat xml file

SQL> alter pluggable database pdb3 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb3 unplug into '/u01/app/oracle/oradata/pdb3_unplug.xml';

Pluggable database altered.
Convert NON-CDB database to PDB Convert NON-CDB database to PDB 1) Upgrade the Non-CDB if the database is pre 12c
2) Open the database in readonly
3) Execute DBMS_PDB
4) Use Create Pluggable database
5) Execute the script noncdb_to_pdb.sql		 1) Upgrade and set comptabile = 12.0.0
2) OPen the database in readonly mode
3) Execute the dbms_pdb package describe procedure
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/disk1/oracle/ncdb.xml'); END; /
4) Shutdown the non-cdb database
5) If in same server your CDB database contains
CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml' COPY FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/');
6) Execute the script
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
7) Open the read write mode new PDB
July 27th, 2013 | Tags: , , , | Category: 12c Database, Administration

Comments are closed.