DBUA 12c Reports Database Down While It’s Up..Or..”The only way is up! – Yazz- “

After doing meticulously doing the pre-upgrade  steps on a RAC converted to single instance node, we finally could get started on the actual upgrade. The initial idea is to use the DBUA, since it automates a lot of steps, and is supposed to be less error-prone.. Oracle Corp had me with the “automates” step already, but Ok.. 😉

We set the correct environment settings, checked the listener:

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-FEB-2017 12:19:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-FEB-2017 11:23:14
Uptime                    1 days 0 hr. 55 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/12.1.0/grid/network/admin/listener.ora
Listener Log File         /oracle/12.1.0/base/diag/tnslsnr/node02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ora12tst" has 2 instance(s).
Instance "ora12tst", status UNKNOWN, has 1 handler(s) for this service...
Instance "ora12tst", status READY, has 2 handler(s) for this service...
The command completed successfully

Yep, perfect.

[oracle@bedr-odb0002 dbs]$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 10 12:20:59 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>

Awesome.

Let’s go!

From a x-terminal and the Oracle 12c Home:

$/oracle/12.1.0/base/dbhome_1/bin/dbua

selection_007

Yep. This is what we want..

selection_008

Oh, wait…what!? How!? Ok, it is a clone of a RAC, we get that, but down? Nope. Absolutely not.

We could make a long story even longer by sharing all the experiments, but the main things done, where:

Un-raccing (is this even a word?) the database:

SQL> alter system set cluster_database_instances=1 scope=spfile;

System altered.

SQL> alter database disable thread 2;

Database altered.

Delete the unwanted thread and redo logfiles.

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
---------- ----------
1 1
1 5
1 7
2 6
2 3
2 4

6 rows selected.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
---------- ----------
1 1
1 7
1 5
Database altered.

Drop the unwanted undo tablespace.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Create pfile from spfile.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;

SQL> exit

Remove all references for RAC components from the pfile and start the instance using the pfile.

Startup the database.

$ sql

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 9 20:09:28 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            3925872504 bytes
Database Buffers         8858370048 bytes
Redo Buffers               40861696 bytes
Database mounted.
Database opened.
SQL>

But although it cleaned some stuff up, which will benefit the upgrade anyway, it didn’t solve the issue. The DBUA still claimed the database was down.

But wait! There is a new way of doing things (Ok, not really new, but still) called the srvctl utility! Would the DBUA be looking at the service of the database?

Again, long story short: it does.

To get a clean(-ish) start, First remove the service completely, and rebuild it from scratch.

$ srvctl config database -d ora12tst
Database unique name: ora12tst
Database name:
Oracle home: /oracle/11.2/base/db/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ora12tst
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

$ srvctl remove database -d ora12tst
Remove the database ora12tst? (y/[n]) y

$ srvctl add database -d ora12tst -o /oracle/11.2/base/db/dbhome_1

$ srvctl status database -d ora12tst
Database is not running.
$ srvctl start database -d ora12tst
PRKO-3119 : Database ora12tst cannot be started since it has no configured instances.

$ srvctl config database -d ora12tst
Database unique name: ora12tst
Database name:
Oracle home: /oracle/11.2/base/db/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ora12tst
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

$ srvctl add instance -d ora12tst -i ora12tst -n node02
$ srvctl start database -d ora12tst
$ srvctl config database -d ora12tst
Database unique name: ora12tst
Database name:
Oracle home: /oracle/11.2/base/db/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ora12tst
Database instances: ora12tst
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

$ srvctl status database -d ora12tst
Instance ora12tst is running on node node02

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-FEB-2017 12:19:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-FEB-2017 11:23:14
Uptime                    1 days 0 hr. 55 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/12.1.0/grid/network/admin/listener.ora
Listener Log File         /oracle/12.1.0/base/diag/tnslsnr/node02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1521)))

Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ora12tst" has 2 instance(s).
Instance "ora12tst", status UNKNOWN, has 1 handler(s) for this service...
Instance "ora12tst", status READY, has 2 handler(s) for this service...
The command completed successfully

$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 10 12:20:59 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
$

Now we start the DBUA again…press Next on the first screen….

selection_009

YES! This is what we wanted to see…*pfieuw*.

And now… Onwards to the future…

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in 12c, Databases, Technical Stuff, upgrade. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s