Amsterdam DBA notes: OCP

First via dbca we need to create our non CDB database:

[oracle@ora12c66 ~]$ cat /vagrant/dbca_noncdb.rsp

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

RESPONSEFILE_VERSION = "11.2.0"

OPERATION_TYPE = "createDatabase"

GDBNAME = "orcl12c"

SID = "orcl12c"

TEMPLATENAME = "General_Purpose.dbc"

SYSPASSWORD = "qwe123"

SYSTEMPASSWORD = "qwe123"

SYSMANPASSWORD = "qwe123"

DBSNMPPASSWORD = "qwe123"

CHARACTERSET = "UTF8"

NATIONALCHARACTERSET= "UTF8"

[oracle@ora12c66 ~]$ dbca -silent -createDatabase -responseFile /vagrant/dbca_noncdb.rsp

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

85% complete

96% complete

100% complete

Look at the log file "/u01/cfgtoollogs/dbca/orcl12c/orcl12c0.log" for further details.

there is a new service registered with the listener:

[oracle@ora12c66 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-MAR-2015 09:18:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production

Start Date 12-MAR-2015 12:38:48

Uptime 1 days 20 hr. 39 min. 15 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/product/12.1.0/db_1/network/admin/listener.ora

Listener Log File /u01/diag/tnslsnr/ora12c66/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c66)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c66)(PORT=5500))(Security=(my_wallet_directory=/u01/admin/cdborcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "cdborcl" has 1 instance(s).

Instance "cdborcl", status READY, has 1 handler(s) for this service...

Service "cdborclXDB" has 1 instance(s).

Instance "cdborcl", status READY, has 1 handler(s) for this service...

Service "orcl12c" has 1 instance(s).

Instance "orcl12c", status READY, has 1 handler(s) for this service...

Service "orcl12cXDB" has 1 instance(s).

Instance "orcl12c", status READY, has 1 handler(s) for this service...

Service "pdb1" has 1 instance(s).

Instance "cdborcl", status READY, has 1 handler(s) for this service...

Service "pdb2" has 1 instance(s).

Instance "cdborcl", status READY, has 1 handler(s) for this service...

The command completed successfully

and we can see the new datafiles

[oracle@ora12c66 ~]$ tree /u01/oradata/

/u01/oradata/

├── cdborcl

│ ├── control01.ctl

│ ├── pdb1

│ │ ├── example01.dbf

│ │ ├── pdb1_temp01.dbf

│ │ ├── SAMPLE_SCHEMA_users01.dbf

│ │ ├── sysaux01.dbf

│ │ └── system01.dbf

│ ├── pdb2

│ │ ├── pdbseed_temp01.dbf

│ │ ├── sysaux01.dbf

│ │ └── system01.dbf

│ ├── pdbseed

│ │ ├── pdbseed_temp01.dbf

│ │ ├── sysaux01.dbf

│ │ └── system01.dbf

│ ├── redo01.log

│ ├── redo02.log

│ ├── redo03.log

│ ├── sysaux01.dbf

│ ├── system01.dbf

│ ├── temp01.dbf

│ ├── undotbs01.dbf

│ └── users01.dbf

└── orcl12c

├── control01.ctl

├── redo01.log

├── redo02.log

├── redo03.log

├── sysaux01.dbf

├── system01.dbf

├── temp01.dbf

├── undotbs01.dbf

└── users01.dbf

and it is registered in /etc/oratab

[oracle@ora12c66 ~]$ tail -5 /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.

cdborcl:/u01/product/12.1.0/db_1:N

orcl12c:/u01/product/12.1.0/db_1:N

before I start the migration, lets put a custom user/table:

SQL> select name from v$database;

NAME

---------

ORCL12C

SQL> show con_name

CON_NAME

------------------------------

Non Consolidated

SQL> create user dave identified by qwe123;

User created.

SQL> create table dave.test_table(col1 varchar2(30));

Table created.

SQL> desc dave.test_table

Name Null? Type

----------------------------------------- -------- ----------------------------

COL1 VARCHAR2(30)

so now I can start the migration :)

I am following oracle-base instructions: http://oracle-base.com/articles/12c/multitenant-migrate-non-cdb-to-pdb-12cr1.php

and I start by opening the database to be migrated in read only and generating the PDB xml file

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP OPEN READ ONLY;

ORACLE instance started.

Total System Global Area 588746752 bytes

Fixed Size 2291040 bytes

Variable Size 268438176 bytes

Database Buffers 314572800 bytes

Redo Buffers 3444736 bytes

Database mounted.

Database opened.

SQL>

BEGIN

DBMS_PDB.DESCRIBE(

pdb_descr_file => '/tmp/ORCL12C.xml');

END;

PL/SQL procedure successfully completed.

now lets shutdown the database to be migrated

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ora12c66 ~]$

You might want to inspect the xml file, it is mostly tablespace details, database options, init.ora parameters, timezone version data:

<?xml version="1.0" encoding="UTF-8"?>

<PDB>

<name>SYSTEM</name>

<file>

<path>/u01/oradata/orcl12c/system01.dbf</path>

</file>

</tablespace>

<name>SYSAUX</name>

<file>

<path>/u01/oradata/orcl12c/sysaux01.dbf</path>

</file>

</tablespace>

<file>

<path>/u01/oradata/orcl12c/temp01.dbf</path>

</file>

</tablespace>

<name>USERS</name>

<file>

<path>/u01/oradata/orcl12c/users01.dbf</path>

</file>

</tablespace>

<option>CATALOG=12.1.0.1.0</option>

<option>CATJAVA=12.1.0.1.0</option>

<option>CATPROC=12.1.0.1.0</option>

<option>CONTEXT=12.1.0.1.0</option>

<option>JAVAVM=12.1.0.1.0</option>

<option>ORDIM=12.1.0.1.0</option>

</options>

<parameter>processes=300</parameter>

<parameter>sga_target=591396864</parameter>

<parameter>db_block_size=8192</parameter>

<parameter>compatible=12.1.0.0.0</parameter>

<parameter>open_cursors=300</parameter>

<parameter>pga_aggregate_target=196083712</parameter>

</parameters>

<tzver>primary version:18</tzver>

<tzver>secondary version:0</tzver>

</tzvers>

</optional>

</PDB>

any way we can now create pdb3 based on the ora12c (non-container managed) database

SQL> CREATE PLUGGABLE DATABASE pdb3 using '/tmp/ORCL12C.xml' copy FILE_NAME_CONVERT = ('/u01/oradata/orcl12c/','/u01/oradata/cdborcl/pdb3/');

Pluggable database created.

although we can't connect yet:

SQL> conn system/qwe123@ora12c66/pdb3

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.

first we need to run the noncdb_to_pdb.sql :

SQL> conn / as sysdba

Connected.

SQL> ALTER SESSION SET CONTAINER=pdb3;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

...

on my smallish virtual machine, this script takes a long time (especially the utl_recomp.recomp_parallel(threads) step); but once it is done we can open the new pdb3 database and see the dave.test_table marker object we created on the original non-container manager database

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> conn system/qwe123@ora12c66/pdb3

Connected.

SQL> desc dave.test_table

Name Null? Type

----------------------------------------- -------- ----------------------------

COL1 VARCHAR2(30)

Lastly let's cleanup the noncdb orcl12c database:

[oracle@ora12c66 ~]$ dbca -silent -deleteDatabase -templateName New_Database.dbt -sourceDB orcl12c -sysDBAUserName qwe123 -sysDBAPassword qwe123

Connecting to database

4% complete

9% complete

14% complete

19% complete

23% complete

28% complete

47% complete

Updating network configuration files

48% complete

52% complete

Deleting instance and datafiles

76% complete

100% complete

Look at the log file "/u01/cfgtoollogs/dbca/orcl12c0.log" for further details.

[oracle@ora12c66 ~]$ tree /u01/oradata/

/u01/oradata/

└── cdborcl

├── control01.ctl

├── pdb1

│ ├── example01.dbf

│ ├── pdb1_temp01.dbf

│ ├── SAMPLE_SCHEMA_users01.dbf

│ ├── sysaux01.dbf

│ └── system01.dbf

├── pdb2

│ ├── pdbseed_temp01.dbf

│ ├── sysaux01.dbf

│ └── system01.dbf

├── pdb3

│ ├── sysaux01.dbf

│ ├── system01.dbf

│ ├── temp01.dbf

│ └── users01.dbf

├── pdbseed

│ ├── pdbseed_temp01.dbf

│ ├── sysaux01.dbf

│ └── system01.dbf

├── redo01.log

├── redo02.log

├── redo03.log

├── sysaux01.dbf

├── system01.dbf

├── temp01.dbf

├── undotbs01.dbf

└── users01.dbf

5 directories, 24 files

[oracle@ora12c66 ~]$ tail -5 /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.

cdborcl:/u01/product/12.1.0/db_1:N

Amsterdam DBA notes

Saturday, March 14, 2015

Migrate noncdb to pdb oracle12c