\n| cdb2<\/td>\n | \/u01\/app\/oracle\/product\/12.1.0.2\/db<\/td>\n | The target container, already pre-created with version 12.1.0.2<\/td>\n<\/tr>\n<\/table>\n All of them use Oracle Managed Files (OMF).
\nThe 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.<\/strong><\/p>\nI 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.<\/p>\n sys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 close immediate;\n\nPluggable database altered.\n\nElapsed: 00:00:02.05\n\nsys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 unplug into '\/u01\/data\/db1_upgrade.xml';\n\nPluggable database altered.\n\nElapsed: 00:00:01.41\n\n[oracle@cdbtest data]$ . oraenv\nORACLE_SID = [cdb1] ? cdb2\nThe Oracle base remains unchanged with value \/u01\/app\/oracle\n[oracle@cdbtest data]$ echo $ORACLE_HOME\n\/u01\/app\/oracle\/product\/12.1.0.2\/db\n\nsys@cdb1(cdbtest.aws.ee.dom)> CREATE PLUGGABLE DATABASE db1 USING '\/u01\/data\/db1_upgrade.xml' nocopy tempfile reuse;\n\nPluggable database created.\n\nElapsed: 00:00:10.16\n\nsys@cdb1(cdbtest.aws.ee.dom)> alter pluggable database db1 open upgrade;\n\nWarning: PDB altered with errors.\n\n\n[oracle@cdbtest data]$ cd $ORACLE_HOME\/rdbms\/admin\n[oracle@cdbtest admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl -c 'DB1' catupgrd.sql\n\n... cut\n[CONTAINER NAMES]\n\nCDB$ROOT\nPDB$SEED\nDB1\nPDB Inclusion:[DB1] Exclusion:[]\n\n Time: 2s For PDB(s)\n\nGrand Total Time: 2s\n\nLOG FILES: (catupgrd*.log)\n\nGrand Total Upgrade Time: [0d:0h:0m:2s]\n<\/code><\/pre>\nSomething is wrong, 2 seconds it too fast. Logfile catupgrd0.log reveals one weird error:<\/p>\n sys@cdb2(cdbtest.aws.ee.dom)> sys@cdb2(cdbtest.aws.ee.dom)> ALTER SESSION SET CONTAINER = \"0\";\nERROR:\nORA-65011: Pluggable database 0 does not exist.\n\n\nsys@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');\n\n1 row created.\n<\/code><\/pre>\nNow 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:<\/p>\n $ sqlplus \/ as sysdba\n\nSQL> DELETE FROM sys.registry$error;\n\n1 row deleted.\n\nSQL> commit;\n\nCommit complete.\n<\/code><\/pre>\nAnd run catctl.pl again, with -S flag to run in serial.<\/p>\n [oracle@cdbtest ~]$ cd $ORACLE_HOME\/rdbms\/admin\n[oracle@cdbtest admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl -c 'DB1' -S catupgrd.sql\n\n... cut\n[CONTAINER NAMES]\n\nCDB$ROOT\nPDB$SEED\nDB1\nPDB Inclusion:[DB1] Exclusion:[]\nPDB Serial Inclusion:[DB1] Exclusion:[0]\nRunning File In Serial Order FileName is catupgrd.sql\n...\n<\/code><\/pre>\nSo, 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 \ud83d\ude42 So lets remove my custom login.sql and try running upgrade again, in parallel.<\/p>\n [oracle@cdbtest admin]$ echo $SQLPATH\n\/home\/oracle\/dbscripts\/oracle\n[oracle@cdbtest admin]$ mv $SQLPATH\/login.sql $SQLPATH\/login.sql.bak\n[oracle@cdbtest admin]$ pwd\n\/u01\/app\/oracle\/product\/12.1.0.2\/db\/rdbms\/admin\n[oracle@cdbtest admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl -c 'DB1' catupgrd.sql\n\n... cut\n[CONTAINER NAMES]\n\nCDB$ROOT\nPDB$SEED\nDB1\nPDB Inclusion:[DB1] Exclusion:[]\n\n------------------------------------------------------\nPhases [0-73]\nContainer Lists Inclusion:[DB1] Exclusion:[]\nSerial Phase #: 0 Files: 1 Time: 16s DB1\nSerial Phase #: 1 Files: 5\n<\/code><\/pre>\nSo 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):<\/p>\n [oracle@cdbtest admin]$ mv $SQLPATH\/login.sql.bak $SQLPATH\/login.sql\n[oracle@cdbtest admin]$ vi $SQLPATH\/login.sql\n\ndefine _editor=vi\nset serveroutput on size 1000000\nset linesize 100\nset pagesize 9999\ncolumn plan_plus_exp format a80\ncolumn global_name new_value gname\nset termout off\ndefine gname=idle\ncolumn global_name new_value gname\nselect lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;\n--set sqlprompt '&gname> '\nset termout on\n\ncolumn db_unique_name format a20\ncolumn database_role format a16\ncolumn db_version format a10\ncolumn db_status format a8\ncolumn db_compatible format a10\ncolumn db_version heading VERSION\ncolumn db_status heading STATUS\ncolumn db_compatible heading COMPATIBLE\nselect d.db_unique_name, d.database_role, v.version db_version, p.value db_compatible, v.status db_status\n from v$database d, v$instance v, v$parameter p where p.name = 'compatible';\n\n[oracle@cdbtest admin]$ sqlplus \/ as sysdba\n\nSQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 5 15:24:41 2014\n\nCopyright (c) 1982, 2014, Oracle. All rights reserved.\n\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\n\n\nDB_UNIQUE_NAME DATABASE_ROLE VERSION COMPATIBLE STATUS\n-------------------- ---------------- ---------- ---------- --------\ncdb2 PRIMARY 12.1.0.2.0 12.1.0.2.0 OPEN\n\nSQL> exit\nDisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\n\n[oracle@cdbtest admin]$ pwd\n\/u01\/app\/oracle\/product\/12.1.0.2\/db\/rdbms\/admin\n[oracle@cdbtest admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl -c 'DB1' catupgrd.sql\n\n... cut\n[CONTAINER NAMES]\n\nCDB$ROOT\nPDB$SEED\nDB1\nPDB Inclusion:[DB1] Exclusion:[]\n\n Time: 2s For PDB(s)\n\nGrand Total Time: 2s\n\nLOG FILES: (catupgrd*.log)\n\nGrand Total Upgrade Time: [0d:0h:0m:2s]\n<\/code><\/pre>\nStill doesn’t work. Maybe it doesn’t like database information output, I’ll put back the custom prompt and remove the DB information.<\/p>\n [oracle@cdbtest admin]$ cat $SQLPATH\/login.sql\ndefine _editor=vi\nset serveroutput on size 1000000\nset linesize 100\nset pagesize 9999\ncolumn plan_plus_exp format a80\ncolumn global_name new_value gname\nset termout off\ndefine gname=idle\ncolumn global_name new_value gname\nselect lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;\nset sqlprompt '&gname> '\nset termout on\n\ncolumn db_unique_name format a20\ncolumn database_role format a16\ncolumn db_version format a10\ncolumn db_status format a8\ncolumn db_compatible format a10\ncolumn db_version heading VERSION\ncolumn db_status heading STATUS\ncolumn db_compatible heading COMPATIBLE\n--select d.db_unique_name, d.database_role, v.version db_version, p.value db_compatible, v.status db_status\n-- from v$database d, v$instance v, v$parameter p where p.name = 'compatible';\n\n[oracle@cdbtest admin]$ sqlplus \/ as sysdba\n\nSQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 5 15:28:42 2014\n\nCopyright (c) 1982, 2014, Oracle. All rights reserved.\n\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\n\nsys@cdb2(cdbtest.aws.ee.dom)> exit\nDisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\n\n[oracle@cdbtest admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl -c 'DB1' catupgrd.sql\n\n... cut\n[CONTAINER NAMES]\n\nCDB$ROOT\nPDB$SEED\nDB1\nPDB Inclusion:[DB1] Exclusion:[]\n\n------------------------------------------------------\nPhases [0-73]\nContainer Lists Inclusion:[DB1] Exclusion:[]\nSerial Phase #: 0 Files: 1 Time: 19s DB1\nSerial Phase #: 1 Files: 5\n\n... cut\n\nGrand Total Time: 1404s\n\nLOG FILES: (catupgrd*.log)\n\nGrand Total Upgrade Time: [0d:0h:23m:24s]\n<\/code><\/pre>\nAnd 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.<\/p>\n But back to the DB upgrade… Now the pluggable database upgrade is finished and we can open it:<\/p>\n sys@cdb2(cdbtest.aws.ee.dom)> select name, open_mode from v$pdbs;\n\nNAME OPEN_MODE\n------------------------------ ----------\nPDB$SEED READ ONLY\nDB1 MOUNTED\n\nsys@cdb2(cdbtest.aws.ee.dom)> alter pluggable database db1 open;\n\nPluggable database altered.\n<\/code><\/pre>\nNB! Need to take special care with physical standby databases when going for this upgrade path!<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,11],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-upgrade"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":2,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":158,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/36\/revisions\/158"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
|