dgmgrl switchover or convert gives ORA-01017

I have this setup of dataguard with broker:

Host:     paris.localdomain

Instance: TESTDB  (primary)

Host:     london.localdomain

Instance: TESTDBS (physical standby)

When I do:

dgmgrl /

DGMGRL> show configuration;

It all seems fine.

Errors on switchover

If I do a switchover, I get the following errors:

DGMGRL> switchover to 'TESTDBS';
Performing switchover NOW, please wait...
Operation requires a connection to instance "TESTDBS" on database "TESTDBS"
Connecting to instance "TESTDBS"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

    connect to instance "TESTDBS" of database "TESTDBS"

DGMGRL>       

Errors on convert command

Also, when I do a convert to snapshot standby, it works fine, but back to physical standby fails:

DGMGRL> convert database 'TESTDBS' to snapshot standby;
Converting database "TESTDBS" to a Snapshot Standby database, please wait...
Database "TESTDBS" converted successfully
DGMGRL> show configuration;

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    TESTDBS - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> convert database 'TESTDBS' to physical standby;
Converting database "TESTDBS" to a Physical Standby database, please wait...
Operation requires shutdown of instance "TESTDBS" on database "TESTDBS"
Shutting down instance "TESTDBS"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
    shut down instance "TESTDBS" of database "TESTDBS"
    start up and mount instance "TESTDBS" of database "TESTDBS"

DGMGRL>

This leads to a lot of manual work, which is not nice - certainly not in times of emergency.

Solution

Note, that when you do such operations, you should login without the slash:

[oracle@paris ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password: ******
Connected.
DGMGRL>
 
The password is explicitly needed!! 
So do NOT use dgmgrl / for these operations.

This took me a few hours to find out, hope to never forget it anymore.

Documentation
The documentation that I should have read is:
http://docs.oracle.com/cd/B28359_01/server.111/b28295/dgmgrl.htm#BABHECFB and then under 8.1.1 the phrase:

       (remote database restarts will not work),

How could I have missed that....

Upgrade from 11.2 to 12.1 with just 24 seconds downtime

Rolling upgrade with Transient Logical Standby is known as a MAA (Maximum Availability Architecture) technique, to minimize downtime during upgrade of Oracle database. 

The white paper: Database Rolling Upgrade Using Transient Logical Standby: Oracle Data Guard 11g has been available for quite some time. But the steps involved in testing this technique require a lot of skills, patience hardware and experience, aka blood sweat and tears.

Limitations

• Important limitations are, that you need to be able to install the old and the new Oracle software on both nodes. For instance, you can install Oracle 10.2 on Oracle Linux 6.4, but that is not supported (follow OraToolkit if you need to do this). So search for a platform that supports both versions.
• You might have unsupported data types, read the white paper (above) to check this.
• You cannot use Dataguard Broker during this setup.

Last week I finally got this working on Virtualbox on my laptop. I give several hints how.

Create two nodes in Virtualbox

Create two Oracle Linux 6.4 hosts ‘london’ and ‘paris’. Make sure they can ssh and talk to each other. Provide ssh keys so that copying is easy.

Setup Oracle 11.2.0.4 and Oracle 12.1.0.2 on both nodes.

Setup a listener and demo database on ‘london’.

Setup standby

Now follow the note Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1) to create a physical standby on ‘paris’.

Test the physical standby, make sure that logs are applied (which may take a few minutes to start I experienced). You might want to use blog: http://sys-admin.wikidot.com/check-dataguard

Make sure you have a large db_recovery_file_dest_size on both instances. The restore point will require this space during database upgrade.

Test the switchover (and back), you might want to use blog: http://www.oracledistilled.com/oracle-database/data-guard-switchover-to-a-physical-standby for this.

Run the preupgrd.sql from $ORACLE12/rdbms/admin  and resolve problems if any.

Use the physru.sh script

Via the note Oracle11g Data Guard: Database Rolling Upgrade Shell Script (Doc ID 949322.1) you can download the physru.sh script. How this is used in a practical manner, is explained in a blog Minimal downtime rolling database upgrade to 12c Release 1 by Gavin Soorma. Follow this note and you will execute the physru.sh three times (from the ‘london’ Primary host). Gavin explains (in detail) how this works (copied the following text from his blog):

First execution

• Create control file backups for both the primary and the target physical standby database
• Creates Guaranteed Restore Points (GRP) on both the primary database and the physical standby database that can be used to flashback to beginning of the process or any other  intermediate steps along the way.
• Converts a physical standby into a transient logical standby database.

Second execution

• Use SQL apply to synchronize the transient logical standby database and make it current with the primary
• Performs a switchover to the upgraded 12c transient logical standby and  the standby database becomes the primary
• Performs a flashback on the original primary database to the initial Guaranteed Restore Point  and converts the original primary into a physical standby

Third execution

• Starts Redo Apply on the new physical standby database (the original primary database) to apply all redo that has been generated during the rolling upgrade process, including any SQL statements that have been executed on the transient logical standby as part of the upgrade.
• When synchronized, the script offers the option of performing a final switchover to return the databases to their original roles of primary and standby, but now on the new 12c database software version.
• Removes all Guaranteed Restore Points

Results

The results are displayed after the Third execution of physru.sh. As you can see, the whole process took a lot of time (about 6 hours) mainly because my laptop was running out of space. In the end, the steps were succesfully completed, with a service downtime of just 24 seconds.

Second attempt
A few days later, I retried the technique. Upgrade went much smoother, and also switched back at the end. This will give you additional downtime (switchover), in the screenshot below, seen as 19 seconds. Total procedure of upgrade took just over 1 hour, which might be useful for those situations that require maximum availability.

ORA-39083 ORA-02304 on impdp datapump import: TRANSFORM parameter

During impdp we get:

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE … 

Fix it by adding TRANSFORM parameter on impdp:

impdp system/welcome@orcl directory=DUMPDIR dumpfile=mydump.dmp logfile=import01.log schemas=ABC TRANSFORM=OID:N:TYPE

Details: check http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

SP2-1503 on AIX calling a sqlplus script

Which library path?

I ran a job, calling a shell script from Cloud Control on an AIX 5.3 host. This script in turn calls sqlplus and does some sql. Now I got the error in Cloud Control:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

It appeared that some environment settings were missing. In a note by IBM, I read that you might need LIBPATH defined. Check the note here: http://www-01.ibm.com/support/docview.wss?uid=isg3T1015835

Hence, my full script is :


export ORACLE_HOME=/app/oracle/11.2.0
export ORACLE_SID=mydb
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LIBPATH=$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus '/as sysdba' <<EOF
@myscript.sql
exit
EOF

This worked fine. Hope this helps.