Tag: upgrade
- Written by: ilmarkerm
- Category: Blog entry
- Published: July 25, 2014
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!