Martins Blog

Trying to explain complex things in simple terms

Archive for May, 2016

Experimenting with the ZFSSA’s snapshot capability using the simulator part 2

Posted by Martin Bach on May 31, 2016

In my last post I wrote down some notes about my experience while experimenting with the ZFSSA simulator. A simulator is a great way to get familiar with the user interface and general usability of a product. What I wanted to find out using the ZFSSA simulator was the answer to the question: “what happens to a clone of a database when I roll the master copy forward?”

In the first part of the series I explained how I created a clone of a database, named CLONE1. It is based on a backup of my NCDB database. On top of the backup I have created a snapshot as the basis for my clone. A clone in ZFS(SA) terminology is a writeable snapshot, and CLONE1 uses it. But what would happen to CLONE1 if I modified the source database, NCDB? And can I create a new clone-CLONE2-based on a new backup of the source without modifying the first clone? Let’s try this.

Changing the Source Database

Let’s change the source database a little by creating a new tablespace with a couple of data files. To add a little bit of spice to the scenario I decided to create a new user. Its only schema object will be created on the new tablespace.

[oracle@oraclelinux7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 17:25:57 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create tablespace dropme datafile size 5m;

Tablespace created.

SQL> alter tablespace dropme add datafile size 5m;

Tablespace altered.

SQL> grant dba to new_user identified by ...;

Grant succeeded.

SQL> create table new_user.t1 tablespace dropme as 
  2  select * from dba_source where rownum <= 100; 

Table created. 

SQL> 

This should be enough for this quick test.

Backing Up

The next step is to roll my existing image copies forward to reflect the changes. That’s not too hard, essentially you create an incremental backup “for recover of copy” … followed by a recover “copy of database with tag” … When the backup/recover command combination completed you also need to back up the archived logs in a second step.

At the risk of repeating myself, please be careful: adding this procedure to an existing backup strategy might have adverse side effects-as always make sure you understand the implications of this technique and its impact. Test thoroughly!

As with the previous backup I stored the incremental backup “for recover” of my image copies in ‘/zfssa/ncdb_bkp/data/’. That’s a location mounted via NFS from the ZFSSA. This is the same location I previously used for the image copies. There is nothing too exciting to report about the backup.

Just as with the previous post my archived logs went to ‘/zfssa/ncdb_bkp/archive/’ to complete the preparations. Here are the files that were created:

[oracle@oraclelinux7 ~]$ ls -lrt /zfssa/ncdb_bkp/data/
total 3035612
-rw-r-----. 1 oracle asmdba    7192576 Mar  3 17:26 6qqvijpt_1_1
-rw-r-----. 1 oracle asmdba    3702784 Mar  3 17:26 6pqvijps_1_1
-rw-r-----. 1 oracle asmdba    1851392 Mar  3 17:40 6tqvikjv_1_1
-rw-r-----. 1 oracle asmdba     442368 Mar  3 17:40 6sqvikjv_1_1
-rw-r-----. 1 oracle asmdba    5251072 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-DROPME_FNO-7_6uqvikk2
-rw-r-----. 1 oracle asmdba  650125312 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-SYSAUX_FNO-3_6kqvie3k
-rw-r-----. 1 oracle asmdba    5251072 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-DROPME_FNO-2_6rqvijq0
-rw-r-----. 1 oracle asmdba  828383232 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-SYSTEM_FNO-1_6jqvie1q
-rw-r-----. 1 oracle asmdba    5251072 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-USERS_FNO-6_6mqvie57
-rw-r-----. 1 oracle asmdba  293609472 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-UNDOTBS1_FNO-4_6lqvie4d
-rw-r-----. 1 oracle asmdba 1304174592 Mar  3 17:40 data_D-NCDB_I-3358649481_TS-EXAMPLE_FNO-5_6iqvie1p
[oracle@oraclelinux7 ~]$ ls -lrt /zfssa/ncdb_bkp/archive/
total 8204
-r--r-----. 1 oracle asmdba    1024 Mar  3 16:06 1_118_905507850.arc
-r--r-----. 1 oracle asmdba 2869760 Mar  3 16:06 1_117_905507850.arc
-r--r-----. 1 oracle asmdba    1024 Mar  3 17:49 1_120_905507850.arc
-r--r-----. 1 oracle asmdba 5426688 Mar  3 17:49 1_119_905507850.arc
[oracle@oraclelinux7 ~]$ 

The image copies are now current, and you can see the 2 data files for the new tablespace “DROPME” that didn’t exist before.

Creating the Second clone Database

With the preparations in place it is time to see if I can create a new clone that reflects the new tablespace and users. I also would like to see if the following steps have any implications on my database CLONE1.

On the ZFSSA Simulator

I headed over to the ZFSSA (simulator) and navigated to shares -> projects. After selecting “NCDB_BKP” I chose Snapshots and hit the (+) button to create snap1. You should see snap0 on that view as well if you are following the examples.

The next task is to create a new project. You should already see the projects pane on the left hand side. Click on the (+) sign next to ALL to create a new one. I named the project NCDB_CLONE2 to stay in line with the naming convention I used previously. With the project created, you should set the properties as needed. I moved the mount point to /export/ncdb_clone2/. On a real ZFSSA you’d set others as well, but that is out of scope of this post. Consult the relevant white papers for more information.

Just as described in the first post now you need to create clones based on snap1. To do so, switch back to the NCDB_BKP project and select (the list of) shares. You should see alert, archive, data and redo. Create clones for each, by following these steps per share:

  • Hover the mouse over the share name
  • Click on the share’s pencil icon to edit share properties
  • Select “snapshots”
  • Hover the mouse over the snapshot name, snap1
  • In the “Clones” column, click on the [+] sign
  • In the resulting pop-up, make sure to create the clone in NCDB_CLONE2 and give it the same name as the share you are creating the snapshot for

The end result should be 4 shares shown in the new project, all of them based on snapshots of their cousins from NCDB_BKP. That concludes the work on the ZFSSA for now.

NB: you can script this :)

On the database Server

If you haven’t done so yet, create the directories to mount the new shares. Everything I mount from the ZFSSA goes to /zfssa on the database server, and as a result I have my files in /zfssa/ncdb_clone2/{alert,archive,data,redo}. Update /etc/fstab accordingly and mount the shares.

The remaining steps are the same as for the creation of CLONE1, and I am not repeating them here. Because it’s very important, here is the warning again: Be careful with the path information in the create controlfile statement and make sure they point to /zfssa/ncdb_clone2/!

Reviewing the Outcome

After CLONE2 is created, my initial questions may be answered.

  1. Is the new tablespace part of CLONE2?
  2. Can I access the newly created table new_user.t1 in CLONE2?
  3. Did anything change for CLONE1?

Starting with CLONE2, I can see the new tablespace, and the data in new_user.t1 is available as well:

SQL> select name from v$database;

NAME
---------
CLONE2

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
DROPME

7 rows selected.

SQL> select count(*) from new_user.t1;

  COUNT(*)
----------
       100

SQL>

And what about CLONE1?

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> select count(*) from new_user.t1;
select count(*) from new_user.t1
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

Well that looks ok!

Posted in Linux, Oracle | Tagged: | Leave a Comment »

Experimenting with the ZFSSA’s snapshot capability using the simulator

Posted by Martin Bach on May 17, 2016

Recently I have been asked how the Copy-on-Write cloning works on the ZFS Storage Appliance. More specifically, the question was about the “master” copy: did it have to be static or could it be rolled forward? What better than a test to work out how it works. Unfortunately I don’t have an actual system available to me at home so I had to revert to the simulator, hoping that it represents the real appliance accurately.

Setup

First I downloaded the ZFS Storage Appliance Simulator from the Oracle website and created a nice, new, shiny storage system (albeit virtual). Furthermore I have an Oracle Linux 7 system with UEK3 that will attach to the ZFSSA using dNFS. The appliance has an IP address of 192.168.56.101 while the Linux system is accessible via 192.168.56.20. This is of course a virtual toy environment, a real life setup would be quite different using IPMP and multiple paths preferably over Infiniband.

Configuration

Configuring the system is a two step process. The first is to create a storage pool on the ZFSSA that will host database backups, snapshots and clones. The second part is the configuration of the database server to use dNFS. I have written about that in detail in a previous blog post: https://martincarstenbach.wordpress.com/2014/07/09/setting-up-direct-nfs-on-oracle-12c/.

Step 1: Configure the ZFSSA

Again this is the simulator and I can only wish I had the real thing :) I have created a mirrored pool across all disks (that’s possible at this point because I skipped the pool creation during the initial appliance configuration). Navigating to Configuration -> Storage I clicked on the + button to create the pool and assigned all disks to it. I used a mirrored configuration, which again is owned to my lab setup. Depending on your type of (Exadata) backup you would probably choose something else. There are white papers that explain the best data profile based on the workload.

Next I created a new project, named NCDB_BKP to have a common location to set attributes. I tend to set a different mount point, in this case /export/ncdb_bkp to group all shares about to be created. Set the other attributes (compression, record size, access permissions etc) according to your workload. Following the recommendation in the white paper listed in the reference section I created 4 shares under the NCDB_BKP project:
– data
– redo
– alert
– archive

You probably get where this is heading … With those steps I took from the white paper listed in the reference section, the setup of the ZFSSA Simulator is done, at least for now. Head over to the database server.

Step 2: configure the database server

On the database server I customarily create a /zfssa/<projectName>/ mount point where I’m intending to mount the project’s shares. In other words I have this:

[oracle@oraclelinux7 ~]$ ls -l /zfssa/ncdb_bkp
total 0
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 alert
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 archive
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 data
drwxr-xr-x. 2 oracle oinstall 6 Jan 12 16:28 redo

These will be mounted from the ZFSSA – edit your fstab to mount the shares from the appliance (simulator). When mounted, you’d see something like that:

[root@oraclelinux7 ~]# mount | awk '/^[0-9].*/ {print $1}'
192.168.56.101:/export/ncdb_bkp/alert
192.168.56.101:/export/ncdb_bkp/archive
192.168.56.101:/export/ncdb_bkp/data
192.168.56.101:/export/ncdb_bkp/redo

The next step is to add those to the oranfstab which I covered in the previous post I referred to. That should be it for now! Time to take a backup of the source database in preparation for the cloning. Be careful: adding image copies to an existing backup strategy might have adverse side effects-as always make sure you understand the implications of this technique and its impact. As always, test thoroughly!

Creating a backup

Let’s have a look at the database before taking a backup:

[oracle@oraclelinux7 ~]$ NLS_DATE_FORMAT="dd.mm.yyyy hh24:mi:ss" rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 3 10:12:28 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: NCDB (DBID=3358649481)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name NCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     +DATA/NCDB/DATAFILE/system.279.905507757
3    610      SYSAUX               NO      +DATA/NCDB/DATAFILE/sysaux.273.905507723
4    280      UNDOTBS1             YES     +DATA/NCDB/DATAFILE/undotbs1.259.905507805
5    1243     EXAMPLE              NO      +DATA/NCDB/DATAFILE/example.283.905507865
6    5        USERS                NO      +DATA/NCDB/DATAFILE/users.266.905507803

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/NCDB/TEMPFILE/temp.282.905507861

RMAN>

Nothing special, just a standard DBCA-created General Purpose database … Time to take the image copy.

RMAN> @/u01/app/oracle/admin/NCDB/scripts/imagecopy.rman

RMAN> run {
2>   allocate channel c1 device type disk format '/zfssa/ncdb_bkp/data/%U';
3>   allocate channel c2 device type disk format '/zfssa/ncdb_bkp/data/%U';
4>   backup incremental level 1 for recover of copy with tag 'zfssa' database ;
5>   recover copy of database with tag 'zfssa';
6> }

allocated channel: c1
channel c1: SID=36 device type=DISK

allocated channel: c2
channel c2: SID=258 device type=DISK

Starting backup at 03.03.2016 14:48:25
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 6 found
channel c1: starting datafile copy
input datafile file number=00005 name=+DATA/NCDB/DATAFILE/example.283.905507865
channel c2: starting datafile copy
input datafile file number=00001 name=+DATA/NCDB/DATAFILE/system.279.905507757
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-SYSTEM_FNO-1_6jqvie1q 
  tag=ZFSSA RECID=36 STAMP=905525356
channel c2: datafile copy complete, elapsed time: 00:00:55
channel c2: starting datafile copy
input datafile file number=00003 name=+DATA/NCDB/DATAFILE/sysaux.273.905507723
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-EXAMPLE_FNO-5_6iqvie1p 
  tag=ZFSSA RECID=37 STAMP=905525386
channel c1: datafile copy complete, elapsed time: 00:01:23
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA/NCDB/DATAFILE/undotbs1.259.905507805
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-UNDOTBS1_FNO-4_6lqvie4d
  tag=ZFSSA RECID=39 STAMP=905525414
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: starting datafile copy
input datafile file number=00006 name=+DATA/NCDB/DATAFILE/users.266.905507803
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-SYSAUX_FNO-3_6kqvie3k
  tag=ZFSSA RECID=38 STAMP=905525409
channel c2: datafile copy complete, elapsed time: 00:00:51
output file name=/zfssa/ncdb_bkp/data/data_D-NCDB_I-3358649481_TS-USERS_FNO-6_6mqvie57
  tag=ZFSSA RECID=40 STAMP=905525416
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03.03.2016 14:50:18

Starting recover at 03.03.2016 14:50:18
no copy of datafile 1 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
Finished recover at 03.03.2016 14:50:19
released channel: c1
released channel: c2

RMAN> **end-of-file**

The Oracle white paper I just mentioned proposes using the %b flag as part of the RMAN formatSpec in the backup command – which interestingly does not work. The second time I ran it it failed like this:

RMAN> @/u01/app/oracle/admin/NCDB/scripts/imagecopy.rman

RMAN> run {
2>   allocate channel c1 device type disk format '/zfssa/ncdb_bkp/data/%b';
3>   allocate channel c2 device type disk format '/zfssa/ncdb_bkp/data/%b';
4>   backup incremental level 1 for recover of copy with tag 'zfssa' database ;
5>   recover copy of database with tag 'zfssa';
6> }

allocated channel: c1
channel c1: SID=36 device type=DISK

allocated channel: c2
channel c2: SID=258 device type=DISK

Starting backup at 03.03.2016 14:42:04
channel c1: starting incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/NCDB/DATAFILE/example.283.905507865
input datafile file number=00006 name=+DATA/NCDB/DATAFILE/users.266.905507803
input datafile file number=00004 name=+DATA/NCDB/DATAFILE/undotbs1.259.905507805
channel c1: starting piece 1 at 03.03.2016 14:42:04
RMAN-03009: failure of backup command on c1 channel at 03/03/2016 14:42:04
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn
continuing other job steps, job failed will not be re-run
channel c2: starting incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/NCDB/DATAFILE/system.279.905507757
input datafile file number=00003 name=+DATA/NCDB/DATAFILE/sysaux.273.905507723
channel c2: starting piece 1 at 03.03.2016 14:42:04
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 03/03/2016 14:42:04
ORA-19715: invalid format b for generated name
ORA-27302: failure occurred at: slgpn

RMAN> **end-of-file**

This makes sense-Oracle tries to create an incremental backup with the same name as the data file copy, which would erase the copy and replace it with the incremental backup. Thankfully RMAN does not allow that to happen. This is why I chose the %U flag in the formatSpec, as it allows the incremental backup to be created successfully in addition to the datafile image copies. I am conscious of the fact that the image copies have somewhat ugly names.

After this little digression it’s time to back up the archived logs. When using copies of archived logs you need to make sure that you don’t have overlapping backups. The Oracle white paper has the complete syntax, I spare you the detail as it’s rather boring.

After some time the result is a set of image copies of the database plus the archived logs:

[oracle@oraclelinux7 ~]$ ls -lR /zfssa/ncdb_bkp/
/zfssa/ncdb_bkp/:
total 14
drwxr-xr-x. 2 oracle dba 2 Mar  3 10:32 alert
drwxr-xr-x. 2 oracle dba 4 Mar  3  2016 archive
drwxr-xr-x. 2 oracle dba 7 Mar  3  2016 data
drwxr-xr-x. 2 oracle dba 2 Mar  3 10:32 redo

/zfssa/ncdb_bkp/alert:
total 0

/zfssa/ncdb_bkp/archive:
total 2821
-r--r-----. 1 oracle asmdba 2869760 Mar  3  2016 1_117_905507850.arc
-r--r-----. 1 oracle asmdba    1024 Mar  3  2016 1_118_905507850.arc

/zfssa/ncdb_bkp/data:
total 3001657
-rw-r-----. 1 oracle asmdba 1304174592 Mar  3  2016 data_D-NCDB_I-3358649481_TS-EXAMPLE_FNO-5_6iqvie1p
-rw-r-----. 1 oracle asmdba  639639552 Mar  3  2016 data_D-NCDB_I-3358649481_TS-SYSAUX_FNO-3_6kqvie3k
-rw-r-----. 1 oracle asmdba  828383232 Mar  3  2016 data_D-NCDB_I-3358649481_TS-SYSTEM_FNO-1_6jqvie1q
-rw-r-----. 1 oracle asmdba  293609472 Mar  3  2016 data_D-NCDB_I-3358649481_TS-UNDOTBS1_FNO-4_6lqvie4d
-rw-r-----. 1 oracle asmdba    5251072 Mar  3  2016 data_D-NCDB_I-3358649481_TS-USERS_FNO-6_6mqvie57

/zfssa/ncdb_bkp/redo:
total 0

[oracle@oraclelinux7 ~]$

Create a Clone

Now that the database is backed up in form of an image copy and I have archived redo logs I can create a clone of it. This requires you to jump back to the ZFSSA interface (either CLI or BUI) and create snapshots followed by clones on the directories used.

One way is to log in to the BUI, select the NCDB_BKP project, navigate to “snapshots” and creating one by clicking on the + button. I named it snap0. If you plan on doing this more regularly it can also be scripted.

A clone is a writeable snapshot and is as easy to create. Add a new project – for example NCDB_CLONE1 – and set it up as required for your workload. In the next step you need to switch back to the backup project and for each of the 4 shares create a clone. To do so, navigate to the list of shares underneath NCDB_BKP and click on the pencil icon. This takes you to the share settings. Click on snapshots and you should see snap0. Hovering the mouse over the snapshot’s name reveals additional icons on the right, one of which allows you to create a clone (“clone snapshot as a new share”). Hit that plus sign and change the project to your clone (NCDB_CLONE1) and assign a name to the clone. I tend to use the same name as the source. The mount point should automatically be updated to /export/ncdb_clone1/sharename.

Now you need to get back to the database server and add the mount points for the recently created clones: /zfssa/ncdb_clone1/{data,redo,arch,alert}. Edit the fstab and oranfstab files, then mount the new shares.

Finishing the clone creation

The following procedure is most likely familiar to DBAs who created databases as file system copies. The steps are somewhere along the line of this:
– register the database in oratab
– create an initialisation file
– create a password file
– back up the source controlfile to trace in order to create a “create controlfile” statement for the clone
– start the clone
– create the controlfile
– recover the clone using the newly created backup controlfile
– open the clone with the resetlogs option
– add temp file(s)

Based on my source database’s parameter file, I created the following for the clone database which I’ll call CLONE1:

*.audit_file_dest='/u01/app/oracle/admin/CLONE1/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/zfssa/ncdb_clone1/redo/control1.ctl'
*.db_block_size=8192
*.db_create_file_dest='/zfssa/ncdb_clone1/data'
*.db_domain=''
*.db_name='CLONE1'
*.db_recovery_file_dest='/zfssa/ncdb_clone1/archive'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONE1XDB)'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'

Notice how some of the filesystem related parameters changed to point to the mount points exported via NFS from the ZFSSA. I have refrained from changing diagnostic_dest to the ZFSSA simulator, this prevented the database from starting (perf told me that the sqlplus session spent all the time trying to use the network).

I’ll spare you the details of the create controlfile command, just make sure you change paths and point to the data files on the cloned shares (/zfssa/ncdb_clone1/data/*) and NOT on the ones where the master copy resides. After the controlfile is created, recover the database using the backup controlfile, then open it. Voila! You have just created CLONE1. Just add a temp file and you are almost good to go.

In the next part

The post has already become a bit too long, so I’ll stop here and split it into this one and a second part. In the next part you will read about changes to the source database (NCDB) and how I’ll roll the image copies forward.

References

http://www.oracle.com/technetwork/articles/systems-hardware-architecture/cloning-solution-353626.pdf

Posted in Linux, Oracle | Tagged: | 1 Comment »

Trouble with multiple SCAN listeners in 12c Release 1

Posted by Martin Bach on May 14, 2016

UPDATE 28-JUL-2016: the problem – as stated by Anil in the comments section – seems to be fixed. I applied the Proactive Bundle Patch 23273686 and OJVM patch 23177536 to the Grid and RDBMS home. After a successful execution of datapatch the errors in the CRSD agent log I reported in this post went away, and I can also see the settings in the database alert.log.

Scroll to the bottom of the post for more detail.

For those not yet on that patch level-the original post

Prompted by comments made by readers about my posts describing how to add a second SCAN in 12c Release 1 and problems regarding the listener_networks parameter I thought it was time to revisit the situation. I’m still running Oracle Linux 7.1/UEK3 (although that should not matter) but upgraded to 12.1.0.2.5. This is the release that is reportedly showing the odd behaviour. I don’t recall my exact version when I wrote the original posts back in April 2014, but by looking at them I think it all worked ok at the time. Here is my current patchlevel after the upgrade to the troublesome PSU.

 SQL> select patch_id,description,status from DBA_REGISTRY_SQLPATCH;

  PATCH_ID DESCRIPTION                                                                      STATUS
---------- -------------------------------------------------------------------------------- ---------------
  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)                       SUCCESS
  21359755 Database Patch Set Update : 12.1.0.2.5 (21359755)                                SUCCESS

This is not the latest patch level! After having seen a presentation by Ludovico Caldara (@ludodba) just this week I would think that the April 2016 patch which was current at the time of writing is the one you want to be on :) I have an upgrade to the April 2016 PSU planned but first wanted to test against 12.1.0.2.5 to see if I could reproduce the issue.

Problem Statement

The parameter listener_networks should be populated automatically by CRS when the database comes up-and indeed you can see evidence of that in the respective node’s crsd_oraagent_oracle.trc file. However, with 2 SCANs present, there is something not quite right. You can see this in the usual locations:

– SCAN listeners
– listener_networks parameter
– CRSD trace

The most obvious clue is that you cannot connect to the database any more using one of the SCANs. To avoid you having to flick back and forth between my posts, here is the network configuration again:

[oracle@ron12cprinode2 ~]$ srvctl config scan -netnum 1
SCAN name: ron12cpri-scan, Network: 1
Subnet IPv4: 192.168.100.0/255.255.255.0/eth0, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.100.65
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.100.66
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.100.64
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 

[oracle@ron12cprinode2 ~]$ srvctl config scan -netnum 2
SCAN name: ron12cpri-dg-scan, Network: 2
Subnet IPv4: 192.168.102.0/255.255.255.0/eth2, static
Subnet IPv6: 
SCAN 0 IPv4 VIP: 192.168.102.66
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 1 IPv4 VIP: 192.168.102.64
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 
SCAN 2 IPv4 VIP: 192.168.102.65
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes: 
SCAN VIP is individually disabled on nodes: 

It’s essentially using the 192.168.100/24 network for the “public” traffic and 192.168.102/24 for Data Guard. I still use my RAC One Node database RON, which is currently active on node 2. All of my SCAN listeners should know about its services, RON_SRV and RON_DG_SRV. However, that’s not the case, as a quick check reveals:

[oracle@ron12cprinode1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 19:56:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:39
Uptime                    0 days 0 hr. 34 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.65)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "ron12cpri" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ron12cprinode1 ~]$ lsnrctl status DGSCANLSNR_SCAN2_NET2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 19:56:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DGSCANLSNR_SCAN2_NET2)))
STATUS of the LISTENER
------------------------
Alias                     DGSCANLSNR_SCAN2_NET2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:42
Uptime                    0 days 0 hr. 34 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/dgscanlsnr_scan2_net2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGSCANLSNR_SCAN2_NET2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.64)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@ron12cprinode1 ~]$ 

Before generating the above output I specifically “reset” the listener_networks settings on both instances, and ensured that they were created dynamically. After the database restart I couldn’t make out ANY entry for listener_networks:

SQL> select inst_id,name,value from gv$parameter where name = 'listener_networks';

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 listener_networks

SQL> select inst_id,name,value from gv$spparameter where name = 'listener_networks';

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 listener_networks

Nothing at all. I manage to reach the system using the RON_SRV service that’s known to the first (public) network’s SCAN:

[oracle@ron12cprinode1 ~]$ sqlplus a/b@ron12cpri-scan/RON_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:18:02 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
[oracle@ron12cprinode1 ~]$ 

The same test failed for connections against ron12cpri-dg-scan, stating that the listener didn’t know about the service. Checking the CRSD trace (on the node the instance runs!) I could see the reason:

2016-05-14 19:47:49.637611 : USRTHRD:2023044864: {1:58687:2893} Endp=ron12cpri-dg-scan:1521
2016-05-14 19:47:49.637638 : USRTHRD:2023044864: {1:58687:2893} Final Endp=(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521)), remoteEndp= ron12cpri-scan:1521','ron12cpri-scan:1521','ron12cpri-scan:1521
2016-05-14 19:47:49.637662 : USRTHRD:2023044864: {1:58687:2893} sqlStmt = ALTER SYSTEM SET REMOTE_LISTENER=' ron12cpri-scan:1521','ron12cpri-scan:1521','ron12cpri-scan:1521' SCOPE=MEMORY SID='RON_1' /* db agent *//* {1:58687:2893} */
2016-05-14 19:47:49.645739 : USRTHRD:2023044864: {1:58687:2893} sqlStmt = ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1' /* db agent *//* {1:58687:2893} */
2016-05-14 19:47:49.655035 : USRTHRD:2023044864: {1:58687:2893} ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521'

2016-05-14 19:47:49.655191 : USRTHRD:2023044864: {1:58687:2893} DbAgent::DedicatedThread::run setRemoteListener Exception OCIException
2016-05-14 19:47:49.655207 : USRTHRD:2023044864: {1:58687:2893} ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521ron12cpri-dg-scan:1521'

Looking at this output it appears that setting the remote_listener worked, although I thought we’d only set the host once and not thrice? This looks fishy. It appears to work though, as confirmed in v$parameter and the fact that I can connect against the system.

Interestingly setting listener_networks fails with an ORA-02097: parameter cannot be modified because specified value is invalid. This makes sense: there are white spaces missing in the alter system command, and even if the spaces were correct, the command would fail. Trying manually confirms that thought:

SQL> ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1';
ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=net2
ORA-00138: all addresses specified for attribute REMOTE_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'ron12cpri-dg-scan:1521
ron12cpri-dg-scan:1521 ron12cpri-dg-scan:1521'

OK – that’s all I need to know. When changing the command to look like what I thought it should look like in the first place (the remote listener specifies only 1 host:port) it works:

SQL> ALTER SYSTEM SET LISTENER_NETWORKS='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.63)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521))' SCOPE=MEMORY SID='RON_1';

System altered.

SQL> alter system register;

System altered.

What does that mean? Back to my SCAN listeners again:

[oracle@ron12cprinode1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 20:07:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------   
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:39
Uptime                    0 days 0 hr. 45 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.65)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "ron12cpri" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully

No negative change there, but would the DG_SCAN listener also pick it up?

[oracle@ron12cprinode1 ~]$ lsnrctl status DGSCANLSNR_SCAN2_NET2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2016 20:07:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DGSCANLSNR_SCAN2_NET2)))
STATUS of the LISTENER
------------------------   
Alias                     DGSCANLSNR_SCAN2_NET2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-MAY-2016 19:21:42
Uptime                    0 days 0 hr. 45 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode1/dgscanlsnr_scan2_net2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGSCANLSNR_SCAN2_NET2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.64)(PORT=1521)))
Services Summary...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
The command completed successfully

Well it seems it did. Now the question is: can I connect?

[oracle@ron12cprinode1 ~]$ sqlplus system/secretPWD@ron12cpri-scan/RON_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:08:45 2016

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sat May 14 2016 19:58:28 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@ron12cprinode1 ~]$ sqlplus system/secretPWD@ron12cpri-dg-scan/RON_DG_SRV

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 14 20:08:54 2016

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sat May 14 2016 20:08:45 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> 

Summary

Well I can! So that should solve the problem for the active instance, however there are problems bound to happen when the instance restarts. Since I don’t really have control over the instance name in RAC One Node (RON_1 can be started on node 1 and node 2) I can’t hard-code the value for listener_networks into the spfile. As an end result I’d lock myself out just like CRS did. This is likely a similar issue for multi-node RAC using policy managed databases.

I have repeated the test with the latest version of the stack (upgraded in place), and got the same result. Here are the version numbers:

[oracle@ron12cprinode2 ~]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22502555;OCW Patch Set Update : 12.1.0.2.160419 (22502555)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

[oracle@ron12cprinode2 ~]$ /u01/app/12.1.0.2/grid/OPatch/opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22502555;OCW Patch Set Update : 12.1.0.2.160419 (22502555)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

SQL> select action_time, patch_id,description,status from DBA_REGISTRY_SQLPATCH order by action_time;

ACTION_TIME                                PATCH_ID DESCRIPTION                                                            STATUS
---------------------------------------- ---------- ---------------------------------------------------------------------- ---------------
29-OCT-15 12.46.55.763581                  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)             SUCCESS
29-OCT-15 12.46.55.939750                  21359755 Database Patch Set Update : 12.1.0.2.5 (21359755)                      SUCCESS
14-MAY-16 21.32.15.211167                  21555660 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)             SUCCESS
14-MAY-16 21.32.15.233105                  22674709 Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)        SUCCESS
14-MAY-16 21.32.15.591460                  22291127 Database Patch Set Update : 12.1.0.2.160419 (22291127)                 SUCCESS

The same values for remote_listener and listener_networks as with 12.1.0.2.5 have been observed, and the error about setting listener_networks in the CRSD log was identical to the previous release. I guess that needs to be raised with Oracle …

Apparently this is now fixed with the July Proactive Patch

As stated in the introduction I applied Proactive Bundle Patch 23273686 and OJVM patch 23177536 to the Grid and RDBMS home. After a successful execution of datapatch the errors in the CRSD agent log I reported in this post went away, and I can also see the settings in the database alert.log:

Reconfiguration complete (total time 0.0 secs)
Instance started by oraagent
NOTE: ASMB registering with ASM instance as Flex client 0xffffffffffffffff (reg:3848556776) (new connection)
ORACLE_BASE from environment = /u01/app/oracle
NOTE: ASMB connected to ASM instance +ASM1 osid: 23292 (Flex mode; client id 0x10007)
NOTE: initiating MARK startup
Starting background process MARK
MARK started with pid=34, OS id=23296
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.61)(PORT=1521))'
   SCOPE=MEMORY SID='RON_1';
NOTE: MARK has subscribed
2016-07-28 15:05:58.435000 +01:00
ALTER SYSTEM SET remote_listener=' ron12cpri-scan:1521' SCOPE=MEMORY SID='RON_1';
ALTER SYSTEM SET listener_networks='(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
  (HOST=192.168.102.61)(PORT=1523)))))','(( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521))' 
  SCOPE=MEMORY SID='RON_1';
ALTER DATABASE MOUNT /* db agent *//* {1:45086:1009} */
2016-07-28 15:05:59.470000 +01:00

I can now see that the settings are reflected in the database as well:

SQL> select inst_id, value from gv$spparameter where name = 'listener_networks';

   INST_ID VALUE
---------- ------------------------------
         1

SQL> select inst_id, value from gv$parameter where name = 'listener_networks';

   INST_ID
----------
VALUE
--------------------------------------------------------------------------------
         1
(( NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1
02.61)(PORT=1523))))), (( NAME=net2)(REMOTE_LISTENER=ron12cpri-dg-scan:1521))

There is only 1 instance because this is a RAC One database. There is no value in the spfile for the listener_networks parameter, but it’s set in gv$parameter, indicating it has been set dynamically.

As a result, even the listeners know about my database:

[oracle@ron12cprinode2 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2016 15:20:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                28-JUL-2016 14:35:04
Uptime                    0 days 0 hr. 45 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.65)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "ron12cpri" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully

What about the SCAN listener on the second network?

[oracle@ron12cprinode2 ~]$ lsnrctl status DGSCANLSNR_SCAN2_NET2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2016 15:21:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DGSCANLSNR_SCAN2_NET2)))
STATUS of the LISTENER
------------------------
Alias                     DGSCANLSNR_SCAN2_NET2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                28-JUL-2016 14:35:03
Uptime                    0 days 0 hr. 46 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ron12cprinode2/dgscanlsnr_scan2_net2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGSCANLSNR_SCAN2_NET2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.64)(PORT=1521)))
Services Summary...
Service "RON" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RONXDB" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_DG_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
Service "RON_SRV" has 1 instance(s).
  Instance "RON_1", status READY, has 1 handler(s) for this service...
The command completed successfully

So finally I can connect to the system referencing both SCANs, without having to do anything on my end:

[oracle@ron12cprinode2 ~]$ sqlplus a/b@ron12cpri-scan/ron_srv

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 28 15:23:08 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
[oracle@ron12cprinode2 ~]$ sqlplus a/b@ron12cpri-dg-scan/ron_srv

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 28 15:23:17 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

This seems to prove that the problem is solved.

Posted in 12c Release 1, Linux, RAC | 2 Comments »