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.

Setup a simple Data Redaction demo

The following will give you a simple demo for data redaction.

Setup

First we start with a cleanup, so that the script may be run several times.

conn /as sysdba
-- cleanup
drop user APP_OWN cascade;
drop user MIKE cascade;
drop user BOSS cascade;
drop role ROLE_SALES;
drop role ROLE_MANAGERS;

Now create users. One for the application itself, one for the manager Boss, and one for the employee Mike:
-- create users
create user APP_OWN identified by APP_OWN;
create user MIKE identified by MIKE;
create user BOSS identified by BOSS;

grant create session, create sequence, create table, unlimited tablespace to APP_OWN;
grant execute on dbms_redact to APP_OWN;
grant create session to MIKE;
grant create session to BOSS;


We will be working with roles to get the security setup:
-- create roles
create role ROLE_SALES;
create role ROLE_MANAGERS;
grant select any table to ROLE_SALES;
grant select any table to ROLE_MANAGERS;

grant ROLE_SALES to MIKE;
grant ROLE_MANAGERS to BOSS;

Now create a table in the application schema.
-- setup demo table
conn APP_OWN/APP_OWN
create table emp (id number generated always as identity, name varchar2(30), salary number(15,2));
insert into emp (name,salary) values ('TOM',9999.99);
insert into emp (name,salary) values ('NANCY',8888.88);
commit;

-- setup policy
BEGIN
 DBMS_REDACT.ADD_POLICY  (
    OBJECT_SCHEMA => 'APP_OWN',
    object_name => 'EMP',
    policy_name => 'DEMO_EMP_POLICY',
    expression  => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''ROLE_MANAGERS'')    = ''FALSE''',
    column_name => '"SALARY"',
    function_type => DBMS_REDACT.RANDOM );
END;
/

Results

-- checking results:
col NAME format a7

conn APP_OWN/APP_OWN
select USER  from dual;
select * from APP_OWN.EMP;

conn BOSS/BOSS
select USER  from dual;
select * from APP_OWN.EMP;

conn MIKE/MIKE
select USER  from dual;
select * from APP_OWN.EMP;

This will give you the results:

Connected.

USER
------------------------------
APP_OWN


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        9074.99
         2 NANCY      8315.64

Note that the application owner does not have the manager role and will not see the data itself!!!

Connected.

USER
------------------------------
BOSS


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        9999.99
         2 NANCY      8888.88


As you can see, the BOSS user sees the real data.

Connected.

USER
------------------------------
MIKE


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        4128.09
         2 NANCY      2698.32


And the MIKE user does not have access to the real data.

What if.... Mike tries to break into the data?

conn MIKE/MIKE
Connected.

create table MYCOPY as select * from APP_OWN.EMP;
create table MYCOPY as select * from APP_OWN.EMP
                              *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

Risks

!!!NOTE that sysdba has all rights, you will need to implement Database Vault to prevent the following:

conn / as sysdba
Connected.
select * from APP_OWN.EMP;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 TOM                               9999.99
         2 NANCY                             8888.88

Also, a second weakness is the implicit way to figure out values:

conn MIKE/MIKE
Connected.

select * from APP_OWN.EMP where SALARY between 9999 and 10000;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 TOM                                3660.5