转：12C CDB and pdb with sql developer

How to install the 12c DB and use the Pluggable DB with SQL DeveloperGoal

 To give a path to install the 12c Database with Pluggable Databases.

Solution

 

This is one method to install the 12c Pluggable Database onto a Windows 7 x64 PC.

 

1. Where to get the 12c DB?

 

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

 

For this note I am using the Microsoft Windows x64 (64-bit)Oracle Database 12c Release 1 Enterprise Edition.

 

Download both file 1 and file 2 into the same directory. Unzip the files into the same directory.

 

 

 

2. How to create the 12c DB installation?

Keep a Record of your DB names and passwords!

  A. Double click <path to DB files>\database\Setup

  B. Email = email address required - Next

  C. Make your choice.. skipped Software updates. - Next

  D. Create and configure a database. - Next

  E. Desktop class - Next

  F. Create New Window User - Give a new unused name and password. - Next

  G. a. Set the Oracle base path to a new oracle home location.

     b. Software location - where your software is located

     c. DB file location - as desired

     d. DB Edition - Enterprise Edition

     e. Character set - default

     f. global DB name: Different than other DB's you may have or just default if you have none.

     g. Admin pass word - set your PW

     h. Create as Container DB - Check

     i. Pluggable database name: give it a name or use Default - Next

     j. Install

     k. About 86 % the install will stop. There is a window that has to be

        clicked OK to continue. This may be behind the install window. If

        you have several windows open you may not see it.

     l. Finish the DB as normal. Passwords can be set at the end.

 

 

3. Set your path preferences for the plugable DB. This works in SQL Developer 4.0 EA2 but not in 3.2.2. You should be able to set it in Enterprise Manager also.

 

  A. In SQL Developer create a connection to SYS to the DB that you just

     created. Make sure in the make connection window you change Default to SYSDBA.

  B. From the Tool bar: View - DBA : This opens the DBA box.

  C. Create a connection in the DBA box. Use the SYS from the 12c DB.

     a. Expand the 12c SYS - expand the Database Configuration

     b. Click on the Initialization Parameters. This opens the parameters in the working window.

c.Item 61 - 66 need to have directory's assigned to them. 

Create 6 directories:


        i. db_create_file_dest = <your path>\db_create_file_dest

ii.db_create_online_log_dest_1 = <your path>\db_create_online_log_dest_1


        iii. db_create_online_log_dest_2 = <your path>\db_create_online_log_dest_2 Through 5.

  d. Select commit from the Worksheet tool bar. 

  e. select both spfile and Memory to save it permanently and on this session.

 

4. Open the Database

 SQL> select open_mode from v$database; 

 OPEN_MODE---------- MOUNTED 

 SQL> alter database open; 

 Database altered.

 

 

Now you have a Database of 12c. However you do not want to use it. That would defeat the purpose of the pluggable DB.

 

5. Create a Pluggable DB. This can be done from SQL Developer 4.0 EA2 or the DBCA. Pick A or B

A. Using SQL Developer

   a. In the DBA box expand Container Database under your 12c SYS connection.

   b. Right click on the Container Database - Create Pluggable Database.

   c. Provide the Database Name, Admin name and PW. 

d.Click Apply ONLY once. This will take a few seconds to create.(if you click apply more than once you will get an error)


 

B. DBCA from a command window.

   a. DBCA from command window

   b. Manage Pluggable Databases - Next

   c. Create a Pluggable Database - Next - Next

   d. Create a new Pluggable Database - Next

   e. Pluggable DB Name, Admin user name and PW

   f. Finish - Close

 

When trying to connect to the Pluggable DB, if you get the error:

Status : Failure -Test failed: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor then you will need to complete step C.

 

C. Before connecting, be sure the pluggable databases are listed in the listener.ora and the 

tnsnames.ora files

 Example:

 SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (SID_NAME = CLRExtProc)

 (ORACLE_HOME = E:\DB_12c\product\12.1.0\dbhome_1)

 (PROGRAM = extproc)

 (ENVS = "EXTPROC_DLLS=ONLY:E:\DB_12c\product\12.1.0\dbhome_1\bin\oraclr12.dll")

 )

 )

 

 LISTENER =

 (DESCRIPTION_LIST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1527))

 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))

 )

 )

 

 SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (SID_NAME = PDB1)

 (ORACLE_HOME = <your path>\DB_12c\product\12.1.0)

 )

 )

 

 

D. Open the Pluggable DB.

   i. In the DBA window expand your 12c SYS connection

   ii. expand Container Database

   iii. right- click your Plugable DB select - Modify State 

   iv. Make sure the New State is OPEN - Apply

 

5. Make a new connection to your Pluggable DB in the connection window.

6. To add Scott/Tiger to your new pluggable DB:

@ <your path>\DB_12c\product\12.1.0\dbhome_1\RDBMS\ADMIN\scott.sql