Upgrade Oracle Database 11g to 12c

Upgrade Oracle Database 11g to 12c

Few things we should know before upgrading Oracle Database 11g to 12c.
This upgrade process will modify the data dictionary to compatible with newer version of Oracle Database software i.e. 12c.

This activity includes,

• Adding, dropping, or modifying columns in system tables and views
• Creating new system packages or procedures
• Modifying existing system packages or procedures
• Creating, modifying, or dropping database users, roles, and privileges
• Modifying seed data that is used by Oracle database components

All above activity will affect the data dictionary of your database. This will not affect the data stored in your user or application tablespaces.

Selecting a Database Upgrade. Below is the Metric which describe database version you can upgrade to Oracle 12c.

Database Release	Source Patch Set	Direct Upgrade to 12c
Oracle Database 11g Release 2	11.2.0.2 and later	YES
	11.2.0.1	NO, Use another Method.
Oracle Database 11g Release 1	11.1.0.7	YES
	11.1.0.6	NO, Use another Method.
Oracle Database 10g	10.2.0.5	YES
	10.2.0.4 and earlier	NO, Use another Method.
Oracle Database 9i and earlier	ALL	NO, Use another Method.

Here we can see some of Oracle versions are directly upgradeable to 12c and some are not.
How other version can be migrated to 12c ?
Option 1) Use Import/Export or Impdb/expdp
Option 2) Transportable tablespace

Here, In my below listing, I am having Oracle Database 11g, version 11.2.0.2 and also Oracle database 12c, version 12.1.0.2.0 installed on my Linux box.
Oracle database 12c is my CDB database and having one PDB also.

Our Goal

1) Upgrade Database 11g to 12c
[By default upgraded database will be non-CDB]
2) Migrate a Non-Container Database to a Pluggable Database (PDB)
[In other words we can say that add non-CDB to PDB in existing CDB.]
Click Here to directly go to Step 2 Migrate a Non-Container Database to a Pluggable Database (PDB)

NOTE: We will not change our SID i.e. ORCL11G while upgrading database to 12c.

Let's start, to upgrade 11g database to 12c.

Set your environment to,

export ORACLE_HOME=/mnt/devops_0/Oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

bash-4.1$ export ORACLE_HOME=/mnt/devops_0/Oracle/product/12.1.0/dbhome_1
bash-4.1$
bash-4.1$ export PATH=$ORACLE_HOME/bin:$PATH
bash-4.1$
bash-4.1$
bash-4.1$ dbua

 

Choose option "Upgrade Oracle Database."

Now, select the instance from the list. Here in our case it is "orcl11g".

It is checking for per-requisite. In background it was running script to check all parameters and db version etc.

Pre-check has given below two errors. In our case, we are not having EM repository & APEX hence we have ignored it and continue with next screen.

EM Repository check description

APEX check description

Here, we have selected the option "Recompile invalid object during post upgrade." It would reduce our post upgrade task by running script manually i.e. utlrp.sql

We have disabled the EM upgrade option.

We wanted to move our data files to new Oracle 12c location hence we selected move data file option and given path of new location.

Benefit of moving file location and recovery area is, in future you can de-install Oracle 11g and remove the all directory structure of 11g versions.
Hence it is recommended to move all your data files and recovery path to new Oracle 12c database path.

We have selected existing Oracle Listner.

It would always better to take backup before upgrading your database. If you are having backup of your database then you can choose option i.e. "I have my own backup and restore strategy". This will save your time for upgrade process.

Before continue with upgrade, check the summary of your upgrade and check source db, target db, all the path of data file, initialization parameters,  listener information, backup location etc...

Yes, now we have started actually Upgrade of Oracle 11g Database to 12c.

Now, we have completed this process. Click on "Upgrade Results" button to see the final result.
Below are some screenshot for the same.

All completed...
if you are not satisfied with the upgrade here you can restore your database by clicking "Restore Database" button.

Click Close button to close this window.

Let's crosscheck our Database by connecting to it.
Set your environment variables as shown in screenshot below i.e. ORACLE_HOME, ORACLE_SID, PATH  

Yes, we have successfully upgraded our database from 11g (11.2.0.2) to 12c (12.1.0.2.0).
Note: by default upgraded database is non-CDB database. It is not having your Multitenant feature.

Once you complete this upgradtion you can migrate your non-CDB 12c database to PDB in existing CDB.

Follow this link for our second step of Migrate a Non-Container Database to a Pluggable Database (PDB)

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

How to Plug non-CDB database to CDB database?

How to Plug non-CDB database oracle11g to CDB database?

This article is in continuation of my previous article i.e. Upgrade Oracle Database 11g to 12c .
One can use below listing without referring to previous notes. Just make sure here I have used my non-CDB database name as ORCL11G because it was migrated from 11g to 12c without changing SID of the database.

NOTE: ORCL11G instance is now our new 12c upgraded database. We haven't change SID while upgrading our database. 

There are several methods to migrate non-CDB database to PDB database.

1) Clone a Remote Non-CDB
2) Using DBMS_PDB
3) Using Data Pump (expdb, impdp)
4) Using Replication

Here I will use DBMS_PDB to migrate non-CDB to PDB.

Setp 1) Generate .xml file.
In this step, we will generate PDBOrcl11g.xml file, which we will used further to create PDB database.
- Connect to your non-CDB database. [In our case it is ORCL11G.]
- Shutdown the database
- Startup in read only mode
- Run DBMS_PDB.DESCRIBE to generate PDBOrcl11g.xml

Below is the listing for the same.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL>
SQL> BEGIN
  2  DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/PDBOrcl11g.xml');
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>

Step 2) Connect to an existing CDB and create a new PDB

Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Must use the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

As you see in below screenshot, we are connected to “orcl” CDB container. “orcl” CDB container is already having PDBORCL as one PDB. We will add our recently upgraded database “orcl11g” in this CDB container as “PDBORCL11g”.

Your can refer my previous article how to upgrade 11g to 12c. Click Here

Step 3) Create pluggable database PDBORCL11g

Create directory pdborcl11g under “/mnt/devops_0/Oracle/oradata/orcl/” path, where all PDB’s data files be located in.

Now, run below command to create new PDB named as PDBORCL11G

CREATE PLUGGABLE DATABASE PDBORCL11G USING '/tmp/PDBOrcl11g.xml' COPY FILE_NAME_CONVERT = ('/mnt/devops_0/Oracle/oradata/orcl11g/ORCL11G/','/mnt/devops_0/Oracle/oradata/orcl/pdborcl11g/');

As you can see in above screenshot newly created PDB i.e. PDBORCL11G is on mounted state.

Step 4) Run script noncdb_to_pdb.sql

Now, Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB.

1) ALTER SESSION SET CONTAINER=PDBORCL11G;
2) @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Above script output is attached here.


Step 5) Startup the PDB and check the open mode.

Now, Start the PDB and check the open mode.

1) ALTER SESSION SET CONTAINER=PDBORCL11G;
2) ALTER PLUGGABLE DATABASE OPEN;

Here, we are done with migration from non-CDB to PDB of Oracle Database 12c.

Step 6) Check all PDBs and PMON Process.
Now check all PDB status from the root container


SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> select con_id,dbid, name, open_mode from v$containers;

CON_ID       DBID    NAME                                OPEN_MODE
----------      ----------     ------------------------------  ----------
         1   1441782042   CDB$ROOT                      READ WRITE
         2   2980200325   PDB$SEED                       READ ONLY
         3       85573530   PDBORCL                         READ WRITE
         4   1021306567   PDBORCL11G                  READ WRITE

4 rows selected.
SQL>


Check the Database processes

-bash-4.1$ ps -ef | grep pmon
db1212   10033  8449  0 12:46 pts/3    00:00:00 grep pmon
db1212   12145     1  0 Jun08 ?        00:00:37 ora_pmon_orcl
-bash-4.1$

Our new PDBORCL11G is now in read write mode. Now you can connect to your new PDB for your regular operations.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Enable DDL Logging

Enable DDL Logging

DDL logging is really very good feature to monitor changes done in DB design.
One can monitor or audit all DB changes by viewing log file.

One can set enable_ddl_logging=true to enable DDL logging.
Oracle will start recording all DDL statements in log.xml file. This file has the same format and basic behavior as the alert log, but it only contains the DDL statements.

The DDL log file is created in $ADR_HOME/log/ddl directory; it contains DDL statements that are extracted from alertl log file.
Log file Path
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

NOTE:- Oracle license "Oracle Change Management Pack" is require to use this feature.
Parameter enable_ddl_logging is licensed as part of the Change Management Pack.

Following DDL statements are written to the log:
•    ALTER/CREATE/DROP/TRUNCATE CLUSTER
•    ALTER/CREATE/DROP FUNCTION
•    ALTER/CREATE/DROP INDEX
•    ALTER/CREATE/DROP OUTLINE
•    ALTER/CREATE/DROP PACKAGE
•    ALTER/CREATE/DROP PACKAGE BODY
•    ALTER/CREATE/DROP PROCEDURE
•    ALTER/CREATE/DROP PROFILE
•    ALTER/CREATE/DROP SEQUENCE
•    CREATE/DROP SYNONYM
•    ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
•    ALTER/CREATE/DROP TRIGGER
•    ALTER/CREATE/DROP TYPE
•    ALTER/CREATE/DROP TYPE BODY
•    DROP USER
•    ALTER/CREATE/DROP VIEW


Following is the example of DDL logging,

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 7 10:24:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL>
SQL> show parameter enable_ddl_logging;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     TRUE

SQL> conn hr/hr
Connected.
SQL>
SQL> create table ddltest (id number, name varchar2(50));
Table created.

SQL>
SQL> alter table ddltest add ( address varchar2(100));
Table altered.

SQL>
SQL> insert into ddltest values (1,'CBT','XYZ');
1 row created.

SQL>
SQL> commit;
Commit complete.

SQL> drop table ddltest;
Table dropped.
SQL>

Check the log file for all DDL commands that was run by user.


-bash-4.1$ pwd
/mnt/Oracle/diag/rdbms/orclnew/orclnew/log/ddl


-bash-4.1$ cat log.xml

 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'
 version='1'>
 create table ddltest (id number, name varchar2(50))
 
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 alter table ddltest add ( address varchar2(100))
 
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 drop table ddltest
 
-bash-4.1$

If you want to see clear and readable DDL log file, Just invoke ADRCI utility and issue show log command as shown below,

-bash-4.1$ adrci
ADRCI: Release 12.1.0.2.0 - Production on Tue Jun 7 12:33:02 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/mnt/Oracle"
adrci> show log -l ddl
ADR Home = /mnt/Oracle/diag/rdbms/orclnew/orclnew:
*************************************************************************
Output the results to file: /tmp/utsout_19321_13991_1.ado
adrci>


When you issue the command "show log", this will open the log.xml file in edtor (i.e. vi in linux/unix) and show the contents, in following format :

2016-06-07 12:23:22.395000 +05:30
create table ddltest (id number, name varchar2(50))
2016-06-07 12:24:05.301000 +05:30
alter table ddltest add ( address varchar2(100))
2016-06-07 12:25:14.003000 +05:30
drop table ddltest
2016-06-07 12:30:33.272000 +05:30
truncate table wri$_adv_addm_pdbs

Also you can see the debug log using adrci show log command i.e. show log -l debug.


For more information on adrci utility visit Oracle Doc
http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert