Small change, big difference – Part 2

A couple of days ago I wrote about snapshot cloning a non-CDB database in Oracle 12.1.0.2.

It is even easier with CDB/PDB. And I think this is what it is really made for. You need only one line of code to create a snapshot copy. For this example I use the same machine as I did in the previous example, that means all the database files are on an EXT4 filesystem.

SQL> show parameter clone

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
clonedb                              boolean                           TRUE

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ ONLY  NO

SQL> create pluggable database my12c from pdb12c snapshot copy;

Pluggable database created.

SQL> alter pluggable database my12c open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ ONLY  NO
         4 MY12C                          READ WRITE NO

[oracle@oel6u4 CDB12C]$ du -k 1242*/datafile/*
501772  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_sysaux_bkb5v3vq_.dbf
256012  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
56      12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_temp_bkb5v3vs_.dbf
5128    12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_users_bkb5zyhh_.dbf
16      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_sysaux_bkb6r742_.dbf
140     12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
56      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_temp_bkb6r750_.dbf
16      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_users_bkb6r750_.dbf

Here we go, a new PDB which is a snapshot copy of the initial PDB. It’s as easy as that.

You can even make the original PDB read-write again and continue.

SQL> alter pluggable database pdb12c close;

Pluggable database altered.

SQL> alter pluggable database pdb12c open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ WRITE NO
         4 MY12C                          READ WRITE NO

Create some stuff in PDB12c:

SQL> alter session set container=pdb12c;

Session altered.

SQL> create user marco identified by marco quota unlimited on users;

User created.

SQL> grant create table to marco;

Grant succeeded.

SQL> grant create session to marco;

Grant succeeded.

SQL> conn marco/marco@localhost/pdb12c
Connected.
SQL> create table mytab as select owner, object_name, object_type from all_objects;

Table created.

Go to MY12C check if things are there and create other stuff inside this MY12C PDB.

SQL> alter session set container=my12c;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 MY12C                          READ WRITE NO

SQL> select username from dba_users where username='MARCO';

no rows selected

SQL> create user marco2 identified by marco2 quota unlimited on users;

User created.

SQL> grant create table to marco2;

Grant succeeded.

SQL> grant create session to marco2;

Grant succeeded.

SQL> conn marco2/marco2@localhost/my12c
Connected.
SQL> create table mytab2 as select owner, object_name, object_type from all_objects;

Table created.

The two PDBs seem to go different directions which is fine. The datafiles are growing too, obvious.

[oracle@oel6u4 CDB12C]$ du -k 1242*/datafile/*
522252  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_sysaux_bkb5v3vq_.dbf
256012  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
56      12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_temp_bkb5v3vs_.dbf
6408    12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_users_bkb5zyhh_.dbf
21132   12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_sysaux_bkb6r742_.dbf
2236    12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
56      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_temp_bkb6r750_.dbf
3756    12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_users_bkb6r750_.dbf

But I have no idea how Oracle manages to keep track of the changes. Maybe I just don’t see it because it’s Friday… Any ideas?

Update 04-01-2015:

Jean-Christophe commented on this and I tested the other option of creating a PDB as a copy. Let’s see:

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
[...]
/u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
[...]

12 rows selected.

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 08:48 /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb                            5v3h9_.dbf

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 08:49 /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb                            6r71x_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
256012  /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
2580    /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf

SQL> alter pluggable database pdb12c close;

Pluggable database altered.

SQL> alter pluggable database pdb12c open read only;

Pluggable database altered.

SQL> create pluggable database copy12c from pdb12c;

Pluggable database created.

SQL> alter pluggable database copy12c open;

Pluggable database altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
[...]
/u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
[...]

15 rows selected.

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 09:31 /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
256008  /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf

The first SYSTEM datafile in the list is the one of my initial PDB12C pluggable database. The file size is about 250M. The second SYSTEM datafile belongs to the snapshot copy MY12C. “ls -l” shows a size of 250M but the file only allocates 2.5M as “du -k” shows. Now the third SYSTEM datafile is part of the copied COPY12C and as it is a copy it really about 250M in size.

Advertisements

NULLs not indexed – not allways true

B-tree indexes does not include NULL values. That is the common understanding of how B-tree indexes organize their values. NULL is the absence of a value so it will be not part of the index. If one need to index NULL values, we need to use Oracle’s bitmap indexes. But this is not completely true. Just follow the example:

SQL> create table mytab as
  2  select owner, object_name, object_type from dba_objects;

Table created.

SQL> update mytab set owner=null where owner=user;

22 rows updated.

SQL> commit;

Commit complete.

SQL> create index mytab_owner on mytab(owner);

Index created.

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

So far, so good. Let’s see what happens if we query all our NULL values:

SQL> select * from mytab where owner is null;

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  catcc32q5jnyw, child number 0
-------------------------------------
select * from mytab where owner is null

Plan hash value: 96696846

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   128 (100)|          |
|*  1 |  TABLE ACCESS FULL| MYTAB |    22 |   858 |   128   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER" IS NULL)

Ok, as expected the optimizer goes for a full table scan and filters all the NULL values. But as a matter of fact my application needs to do a lot of searching for those NULL values and my table happen to be very very large. So no good idea to do a full table scan over and over again. Let’s see what we can do:

SQL> drop index mytab_owner;

Index dropped.

SQL> create index mytab_owner on mytab(owner, 0);

Index created.

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

SQL> select * from mytab where owner is null;

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  catcc32q5jnyw, child number 0
-------------------------------------
select * from mytab where owner is null

Plan hash value: 1460271605

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTAB       |    22 |   902 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYTAB_OWNER |    22 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER" IS NULL)

The thing is, in a concatenated index NULL values in a column are indexed in case the following column is NOT NULL. And since a constant like “0” is per definition NOT NULL, the preceeding column in the index contains all my NULL values. The optimizer is happy and now geos for an index range scan instead.

That’s all, quite easy isn’t it?

Moving from OEL5 to OEL7 with Oracle Grid Infrastructure 11.2.0.4

Some time ago a customer asked how to easily switch his two-node failover cluster to new servers. So I suggested to setup the new nodes and add them to the cluster. Then switch all the ressources from the old servers to the new ones and voila, we’re finished. The old nodes were running OEL5.6 so we decided to go to OEL7 with the new nodes. My Oracle Support was telling that this is certified, so nothing to complain about. That was the plan. Reality was a little different.

The preparation of the two new nodes went fine. But CVU had some complaints here and there. We went ahead and tried to add the first new node to the cluster. But the “addNode.sh” was doing the same checks that CVU did and since there were some issues, it aborted. So we needed a way to skip or ignore these checks. The solution was to set

export IGNORE_PREADDNODE_CHECKS=Y

which is checked inside the “addNode.sh” script and skips the checks if this variable is set to “Y”. Now the installation went on and copied the Grid Infrastructure to the new node. Next step was running the “root.sh” script. It started, but soon got stuck at “Adding Clusterware entries to inittab”. A couple of minutes later the script told us there was a timeout waiting for the OHASD to come up. Makes sense, there is no “inittab” anymore in OEL7. At least it does not matter what’s in there, OEL is now using systemd to control the services in different runlevels. Obviously the “root.sh” script which was being used, was created on the old OEL5 nodes. So what did we do? We created the services for systemd on our own, we copied the configuration from an existing OEL7 cluster:

# cd /etc/systemd/system
# ls -l orac*
-rw-r--r-- 1 root root 362 11. Mar 15:05 oracle-ohasd.service
-rw-r--r-- 1 root root 319 11. Mar 15:06 oracle-tfa.service
#

After adding these files to “/etc/systemd/system” we re-run the “root.sh” script. At the stage “Installing Trace File Analyzer” we enabled and started the TFA service:

systemctl enable oracle-tfa.service
systemctl start  oracle-tfa.service

When we came to the point where OHASD is being setup (“Adding Clusterware entries to inittab”) we checked the corresponding logfile “GRID_HOME/cfgtoollogs/crsconfig/rootcrs*.log”. Once the line with “crsctl start has” comes up, it is time to enable OHASD service too:

systemctl enable oracle-ohasd.service
systemctl start  oracle-ohasd.service

Now that the OHASD service is running, the “root.sh” script is very happy and continues and completes successfully without any further complaints.

I think this issue can occur when moving from any Linux release which is using old “inittab” to a new Linux release that makes use of “systemd” functionality. Maybe this can help some of you to make life easier.

Update 15-APR-2015:

Oracle now commited this behaviour as a bug: MOS Note 1959008.1

Small change, big difference

Did you notice this small change in the “New Features Guide” of 12.1.0.2 database?

With the initialization parameter CLONEDB set to true, snapshot clones of a pluggable database are supported on any local, Network File Storage (NFS) or clustered file systems with Oracle Direct NFS (dNFS) enabled. The source of the clone must remain read-only while the target needs to be on a file system that supports sparseness.

Any local filesystems? Great. But why is it restricted to PDBs only? In fact it isn’t. It also works for non-CDB architecture. The only difference is, you need to do a lot more steps. Actually it is pretty much the same as Direct-NFS-cloning.

This my VM for testing and it only has a local filesystem which is ext4.

[oracle@oel6u4]$ mount
/dev/mapper/vg_oel6u4-lv_root on / type ext4 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0")
/dev/sda1 on /boot type ext4 (rw)
/dev/mapper/vg_data-lv_data on /u01 type ext4 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

[oracle@oel6u4]$ df .
Filesystem           1K-blocks     Used Available Use% Mounted on
/dev/mapper/vg_data-lv_data
                      51606140 16905488  32079212  35% /u01

All I need to do is take an image copy of the database.

RMAN> backup as copy database;

Then create a controlfile backup as text.

SQL> alter database backup controlfile to trace as '/tmp/db12c.ctl';

Database altered.

I need to change the RESETLOGS section a bit, create new redolog groups and use the image copies as datafiles:

CREATE CONTROLFILE REUSE SET DATABASE "CLONE12C" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/CLONE12C/redo01.rdo'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/CLONE12C/redo02.rdo'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_system_bhv010hk_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_sysaux_bhv03ztl_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_undotbs1_bhv09yrm_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_example_bhtzxq18_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_users_bhv06zdm_.dbf'
CHARACTER SET WE8MSWIN1252
;

Last thing I need is a new init.ora for my clone database:

audit_file_dest='/u01/app/oracle/admin/clone12c/adump'
audit_trail ='db'
clonedb = TRUE
compatible ='12.1.0.2.0'
control_files = (/u01/app/oracle/oradata/CLONE12C/control.dbf)
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_name='CLONE12C'
db_recovery_file_dest_size=2G
db_recovery_file_dest='/u01/app/oracle/fra'
diagnostic_dest='/u01/app/oracle'
pga_aggregate_target=128M
processes = 150
remote_login_passwordfile='EXCLUSIVE'
sga_target=800M
undo_tablespace='UNDOTBS1'

Now that I have that, I can start the instance and create our controlfile. Don’t forget to create the directories beforehand…

SQL> startup nomount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes

SQL> sta /tmp/create_control.sql

Control file created.

Now it is time to rename-clone the datafiles As I said, this is done using the DBMS_DNFS functionality:

SQL> begin
  2    dbms_dnfs.clonedb_renamefile(
  3      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_system_bhv010hk_.dbf',
  4      '/u01/app/oracle/oradata/CLONE12C/system_clone.dbf');
  5    dbms_dnfs.clonedb_renamefile(
  6      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_sysaux_bhv03ztl_.dbf',
  7      '/u01/app/oracle/oradata/CLONE12C/sysaux_clone.dbf');
  8    dbms_dnfs.clonedb_renamefile(
  9      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_undotbs1_bhv09yrm_.dbf',
 10      '/u01/app/oracle/oradata/CLONE12C/undotbs1_clone.dbf');
 11    dbms_dnfs.clonedb_renamefile(
 12      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_example_bhtzxq18_.dbf',
 13      '/u01/app/oracle/oradata/CLONE12C/example_clone.dbf');
 14    dbms_dnfs.clonedb_renamefile(
 15      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_users_bhv06zdm_.dbf',
 16      '/u01/app/oracle/oradata/CLONE12C/users_clone.dbf');
 17  end;
 18  /

PL/SQL procedure successfully completed.

Now, last step is to recover and open-resetlogs the clone database. I only need to specify the propper archivelogs.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2224557 generated at 03/09/2015 12:10:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/CLONE12C/archivelog/2015_03_09/o1_mf_1_33_%u_.arc
ORA-00280: change 2224557 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fra/DB12C/archivelog/2015_03_09/o1_mf_1_33_bhv29dgb_.arc
ORA-00279: change 2225737 generated at 03/09/2015 12:51:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/CLONE12C/archivelog/2015_03_09/o1_mf_1_34_%u_.arc
ORA-00280: change 2225737 for thread 1 is in sequence #34
ORA-00278: log file
'/u01/app/oracle/fra/DB12C/archivelog/2015_03_09/o1_mf_1_33_bhv29dgb_.arc' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

That’s it. This is my clone database now using sparse files.

[oracle@oel6u4]$ cd /u01/app/oracle/oradata/CLONE12C/
[oracle@oel6u4]$ ll
-rw-r-----. 1 oracle oinstall 10371072 Mar  9 12:55 control.dbf
-rw-r-----. 1 oracle oinstall 1304174592 Mar  9 12:51 example_clone.dbf
-rw-r-----. 1 oracle oinstall 52429312 Mar  9 12:55 redo01.rdo
-rw-r-----. 1 oracle oinstall 52429312 Mar  9 12:51 redo02.rdo
-rw-r-----. 1 oracle oinstall  692068352 Mar  9 12:52 sysaux_clone.dbf
-rw-r-----. 1 oracle oinstall  838868992 Mar  9 12:51 system_clone.dbf
-rw-r-----. 1 oracle oinstall   73408512 Mar  9 12:51 undotbs1_clone.dbf
-rw-r-----. 1 oracle oinstall  690757632 Mar  9 12:51 users_clone.dbf
[oracle@oel6u4]$ du -h *
9.9M    control.dbf
16K     example_clone.dbf
51M     redo01.rdo
51M     redo02.rdo
580K    sysaux_clone.dbf
380K    system_clone.dbf
596K    undotbs1_clone.dbf
16K     users_clone.dbf

Ok, a little more work than “create pluggable database … snapshot copy …” but it works. And it is on a local filesystem.

And never ever delete the image copies 😉

Oracle Grid Infrastructure 12.1.0.2 on Windows caveats

Recently I needed to set up a two-node RAC cluster on Windows 2012 at a customers site. Therefore we picked the latest and greatest available version for that, which is 12.1.0.2 as of now. Since this is not my first cluster on Windows I took my checklist and went through all the prerequisites, renamed network interfaces, disabled automatic DNS registration, changed the bind order, created partitions, things like that.

So I continued to run through all those steps in the installer and finally the prerequisites check told me that the so called “Automatic Metric” value for the public and interconnect network is the same while it should not. This Automatic Metric feature was introduced with Windows 2012 and does a kind of automatic ordering of network interfaces depending on their speed. And since our public network was only 1GBit in contrast to the interconnect network having 4GBit it was put behind the interconnect network. That’s why the documentation (http://docs.oracle.com/database/121/CWWIN/networks.htm#CIHJFCGC) tells us to disable this feature and do the ordering manually which we then did.

Now that all prerequisites checks were successful the installer started to do all the work. Until the creation of the Container Database for the Oracle Grid Infrastructure Management Repository…. You know, that’s this new management database which was optional in 12.1.0.1 and became mandatory with 12.1.0.2. That means there is no way around this database. A quick research in MOS and the web gave no useful hints. That’s why I started to walk through the logfiles. Finally I came to the dbca log for the Management Database at “D:\app\oracle\cfgtoollogs\dbca\_mgmtdb\trace.log”:

[CRSNative.genericStartResource:313]  Failed to start resource: Name: ora.mgmtdb, node: myracnode01, filter: null, msg CRS-5020: Not all endpoints are registered for listener MGMTLSNR
CRS-2674: Start of 'ora.MGMTLSNR' on 'myracnode01' failed
[Thread-24] [ 2015-02-27 10:36:10.649 CET ] [HADatabaseUtils.start:1244]  Error starting mgmt database in local node, PRCR-1013 : Failed to start resource ora.mgmtdb
PRCR-1064 : Failed to start resource ora.mgmtdb on node myracnode01
CRS-5020: Not all endpoints are registered for listener MGMTLSNR
CRS-2674: Start of 'ora.MGMTLSNR' on 'myracnode01' failed

Ok, the management listener has some issues, it can’t register all it’s endpoints? Why is that?

C:\Windows\system32> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       myracnode01              STABLE
               ONLINE  ONLINE       myracnode02              STABLE
ora.GIMGMT.dg
               ONLINE  ONLINE       myracnode01              STABLE
               ONLINE  ONLINE       myracnode02              STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       myracnode01              STABLE
               ONLINE  ONLINE       myracnode02              STABLE
ora.net1.network
               ONLINE  ONLINE       myracnode01              STABLE
               ONLINE  ONLINE       myracnode02              STABLE
ora.ons
               ONLINE  ONLINE       myracnode01              STABLE
               ONLINE  ONLINE       myracnode02              STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       myracnode02              STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.MGMTLSNR
      1        ONLINE  INTERMEDIATE myracnode01              Not All Endpoints Re
                                                             gistered 192.168.50.
                                                             33 192.168.50.34,STA
                                                             BLE
ora.asm
      1        ONLINE  ONLINE       myracnode01              Started,STABLE
      2        ONLINE  ONLINE       myracnode02              Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.oc4j
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       myracnode02              STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.myracnode01.vip
      1        ONLINE  ONLINE       myracnode01              STABLE
ora.myracnode02.vip
      1        ONLINE  ONLINE       myracnode02              STABLE
--------------------------------------------------------------------------------

Let’s look into the listener configuration:

C:\Windows\system32> srvctl config mgmtlsnr
Name: MGMTLSNR
Type: Management Listener
Owner: nt authority\system
Home:
End points: TCP:1521
Management listener is enabled.
Management listener is individually enabled on nodes:
Management listener is individually disabled on nodes:

C:\Windows\system32> srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: nt authority\system
Home:
End points: TCP:1521
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

Look at that, both listeners use the same TCP port. That is not the case on Linux. On Linux the management listener uses port 1531. Should it be that easy? I tried it:

C:\Windows\system32> srvctl modify mgmtlsnr -endpoints TCP:1531
C:\Windows\system32> srvctl stop mgmtlsnr
C:\Windows\system32> srvctl start mgmtlsnr
C:\Windows\system32> srvctl config mgmtlsnr
Name: MGMTLSNR
Type: Management Listener
Owner: nt authority\system
Home:
End points: TCP:1531
Management listener is enabled.
Management listener is individually enabled on nodes:
Management listener is individually disabled on nodes:

Now the management listener is running on port 1531. A quick look at “crsctl stat res -t” showed no more issues:

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MGMTLSNR
      1        ONLINE  ONLINE       myracnode01              192.168.50.35 192.16
                                                             8.50.36,STABLE

Now I re-run the assistant and it went past that step without any errors. Easy fix, but not quite easy to find the cause of the issue.

Finally the installer came up with lots of green check marks:
installer-successful

I hope this helps all of you who need to cope with Oracle clusters on Windows.