Ilmar Kerm

Oracle, databases, Linux and maybe more

Database upgrade using pluggable databases and how catctl.pl fails if you have custom login.sql

Written by:
Category: Blog entry
Published:

I started to write this blog post on how 12c gave us new multitenant architecture (pluggable databases) and with that also a new possibility to do database upgrades. But during writing I discovered how catctl.pl can fail the upgrade if you have modified the SQL plus prompt with custom login.sql.

I’m starting with these already created databases:

SID Home Description
cdb1 /u01/app/oracle/product/12.1.0.1/db The initial container with version 12.1.0.1. It is hosting the pluggable database DB1 that contains my application data.
cdb2 /u01/app/oracle/product/12.1.0.2/db The target container, already pre-created with version 12.1.0.2

All of them use Oracle Managed Files (OMF).
The goal of this blog post is to upgrade database DB1 from version 12.1.0.1 to 12.1.0.2 by unplugging it from CDB1 container and plugging it to CDB2 container that is created with version 12.1.0.2.

I have already created an empty container database CDB2 using new Oracle version 12.1.0.2. The upgrade itself is simply unplugging the DB1 database from the old container, plugging it into new container and running the database upgrade scripts on the container database.

sys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 close immediate;

Pluggable database altered.

Elapsed: 00:00:02.05

sys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 unplug into '/u01/data/db1_upgrade.xml';

Pluggable database altered.

Elapsed: 00:00:01.41

[oracle@cdbtest data]$ . oraenv
ORACLE_SID = [cdb1] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdbtest data]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/db

sys@cdb1(cdbtest.aws.ee.dom)> CREATE PLUGGABLE DATABASE db1 USING '/u01/data/db1_upgrade.xml' nocopy tempfile reuse;

Pluggable database created.

Elapsed: 00:00:10.16

sys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 open upgrade;

Warning: PDB altered with errors.


[oracle@cdbtest data]$ cd $ORACLE_HOME/rdbms/admin
[oracle@cdbtest admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'DB1' catupgrd.sql

... cut
[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
DB1
PDB Inclusion:[DB1] Exclusion:[]

     Time: 2s For PDB(s)

Grand Total Time: 2s

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time:    [0d:0h:0m:2s]

Something is wrong, 2 seconds it too fast. Logfile catupgrd0.log reveals one weird error:

sys@cdb2(cdbtest.aws.ee.dom)> sys@cdb2(cdbtest.aws.ee.dom)> ALTER SESSION SET CONTAINER = "0";
ERROR:
ORA-65011: Pluggable database 0 does not exist.


sys@cdb2(cdbtest.aws.ee.dom)> INSERT INTO sys.registry$error VALUES ('SYS', SYSDATE, 'catctl.pl', 'CATPROC', 'Invalid Upgrade on [0] Check catupgrd*.log', 'Invalid Upgrade');

1 row created.

Now that is a weird error, I specified DB1 as PDB name. Lets try again, first with serial execution. First clean up the failed upgrade from last time:

$ sqlplus / as sysdba

SQL> DELETE FROM sys.registry$error;

1 row deleted.

SQL> commit;

Commit complete.

And run catctl.pl again, with -S flag to run in serial.

[oracle@cdbtest ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@cdbtest admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'DB1' -S catupgrd.sql

... cut
[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
DB1
PDB Inclusion:[DB1] Exclusion:[]
PDB Serial Inclusion:[DB1] Exclusion:[0]
Running File In Serial Order FileName is catupgrd.sql
...

So, serial execution works. Lets try one more thing… “Bug 17810688 PDB upgrade error running catcon.pl or catctl.pl” this bug is that PDB upgrade fails when sqlplus is not in PATH, so maybe this is some kind of parsing error of sqlplus output that only shows itself, when PDB-s are upgraded? As you saw here, I’m using custom SQLPlus prompt 🙂 So lets remove my custom login.sql and try running upgrade again, in parallel.

[oracle@cdbtest admin]$ echo $SQLPATH
/home/oracle/dbscripts/oracle
[oracle@cdbtest admin]$ mv $SQLPATH/login.sql $SQLPATH/login.sql.bak
[oracle@cdbtest admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db/rdbms/admin
[oracle@cdbtest admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'DB1' catupgrd.sql

... cut
[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
DB1
PDB Inclusion:[DB1] Exclusion:[]

------------------------------------------------------
Phases [0-73]
Container Lists Inclusion:[DB1] Exclusion:[]
Serial   Phase #: 0 Files: 1     Time: 16s   DB1
Serial   Phase #: 1 Files: 5

So upgrade started to run this time. I’ll cancel it and try to debug further. Could this be because of the custom prompt? I’ll try again and only comment out this time the line that sets the custom prompt (set sqlprompt):

[oracle@cdbtest admin]$ mv $SQLPATH/login.sql.bak $SQLPATH/login.sql
[oracle@cdbtest admin]$ vi $SQLPATH/login.sql

define _editor=vi
set serveroutput on size 1000000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;
--set sqlprompt '&gname> '
set termout on

column db_unique_name format a20
column database_role format a16
column db_version format a10
column db_status format a8
column db_compatible format a10
column db_version heading VERSION
column db_status heading STATUS
column db_compatible heading COMPATIBLE
select d.db_unique_name, d.database_role, v.version db_version, p.value db_compatible, v.status db_status
  from v$database d, v$instance v, v$parameter p where p.name = 'compatible';

[oracle@cdbtest admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 5 15:24:41 2014

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


DB_UNIQUE_NAME       DATABASE_ROLE    VERSION    COMPATIBLE STATUS
-------------------- ---------------- ---------- ---------- --------
cdb2                 PRIMARY          12.1.0.2.0 12.1.0.2.0 OPEN

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@cdbtest admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db/rdbms/admin
[oracle@cdbtest admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'DB1' catupgrd.sql

... cut
[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
DB1
PDB Inclusion:[DB1] Exclusion:[]

     Time: 2s For PDB(s)

Grand Total Time: 2s

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time:    [0d:0h:0m:2s]

Still doesn’t work. Maybe it doesn’t like database information output, I’ll put back the custom prompt and remove the DB information.

[oracle@cdbtest admin]$ cat $SQLPATH/login.sql
define _editor=vi
set serveroutput on size 1000000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;
set sqlprompt '&gname> '
set termout on

column db_unique_name format a20
column database_role format a16
column db_version format a10
column db_status format a8
column db_compatible format a10
column db_version heading VERSION
column db_status heading STATUS
column db_compatible heading COMPATIBLE
--select d.db_unique_name, d.database_role, v.version db_version, p.value db_compatible, v.status db_status
--  from v$database d, v$instance v, v$parameter p where p.name = 'compatible';

[oracle@cdbtest admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 5 15:28:42 2014

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

sys@cdb2(cdbtest.aws.ee.dom)> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@cdbtest admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'DB1' catupgrd.sql

... cut
[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
DB1
PDB Inclusion:[DB1] Exclusion:[]

------------------------------------------------------
Phases [0-73]
Container Lists Inclusion:[DB1] Exclusion:[]
Serial   Phase #: 0 Files: 1     Time: 19s   DB1
Serial   Phase #: 1 Files: 5

... cut

Grand Total Time: 1404s

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time:    [0d:0h:23m:24s]

And it starts upgrading! So if you have custom login.sql for SQLPlus keep an eye for this during upgrades. This is rather strange bug, because I already used catctl.pl with the same login.sql setup to upgrade a non-CDB database to 12.1.0.2.

But back to the DB upgrade… Now the pluggable database upgrade is finished and we can open it:

sys@cdb2(cdbtest.aws.ee.dom)> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
DB1                            MOUNTED

sys@cdb2(cdbtest.aws.ee.dom)> alter pluggable database db1 open;

Pluggable database altered.

NB! Need to take special care with physical standby databases when going for this upgrade path!

Tags:

Ilmar Kerm