How to find Cluster Interconnect IP address from Oracle Database

Hello,

The easiest way to find the cluster interconnect is to view the “hosts” file. The “hosts” file is located under:

UNIX .......... /etc

Windows ...... C:\WINDOWS\system32\drivers\etc

Following are the ways to find the cluster interconnect through Oracle database:

1) Query X$KSXPIA

The following query provides the interconnect IP address registered with Oracle database:

SQL> select IP_KSXPIA from x$ksxpia where PUB_KSXPIA = 'N';
IP_KSXPIA
----------------
192.168.10.11

This query should be run on all instances to find the private interconnect IP address used on their respective nodes.

2) Query GV$CLUSTER_INTERCONNECTS view

Querying GV$CLUSTER_INTERCONNECTS view lists the interconnect used by all the participating instances of the RAC database.

SQL> select INST_ID, IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;

INST_ID IP_ADDRESS
---------- ----------------
1 192.168.10.11
2 192.168.10.12

3) ASM and Database log

We can find out the cluster interconnect used by viewing the log file of both ASM instance and the database instance.

When the ASM instance is started it registers the private interconnect used. Following is the excerpt from the ASM alert log on node 1:

:
:
Cluster communication is configured to use the following interface(s) for this instance
192.168.10.11
:
:

Similarly, the database instance also registers the private interconnect with its instance and following message is recorded in the alert log:

:
:
Cluster communication is configured to use the following interface(s) for this instance
192.168.10.11
:
:

4) CLUSTER_INTERCONNECT initialization parameter

This parameter provides Oracle with information on the availability of additional cluster interconnects. It overrides the default interconnect settings at the OS level with a preferred one.

You can view the current value of this parameter by issuing the following in SQL*Plus, connected as SYS user:

SQL> show parameter cluster_interconect

Thanks for reading :-)

A Nice and Handy UNIX Guide

Most of the times we struggle to find the equivalent commands on other Operating Systems, say from AIX to Linux. Normally, we google and after a hard work we ended up with one equivalent command.

I have found the following link to be of great help, it compares commands between AIX, HP-UX, Linux, Solaris, and Tru64.

http://unixguide.net/cgi-bin/unixguide.cgi

Now it’s easy to answer questions like “What is the equivalent command for on HP-UX for df -k on AIX??”

Thanks for reading :-))

A closer look at "SYSDBA" and "DBA"

SYSDBA and DBA are often misunderstood. In short, SYSDBA is a system privilege whereas DBA is a role.

The DBA role does not include the SYSDBA or SYSOPER system privileges. SYSDBA and SYSOPER are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database, instance startup and shutdown, drop a database, open and mount a database, place database in archivelog mode or remove it from archivelog mode.

About SYSDBA system privilege

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

Query users having SYSDBA or SYSOPER privileges:

SQL> column sysdba format a10
SQL> column sysoper format a10
SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER
------------------------------ ---------- ----------
SYS TRUE TRUE
SCOTT TRUE FALSE

About DBA Role

A predefined role, named "DBA", is automatically created with every Oracle database. This role contains all database system privileges but SYSDBA and SYSOPER are excluded. Therefore, it is very powerful and should be granted only to fully functional database administrators.

Query all roles that exist in the database:

SQL> select role from dba_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA
:
:

Query all users to whom DBA role is granted:

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYS DBA YES YES
SYSMAN DBA NO YES
LETS DBA NO YES
SYSTEM DBA YES YES

Additionally, you may not grant SYSDBA privilege to a role. ORA-01931 error is raised when you try to grant it to a role.

SQL> grant sysdba to dba;
grant sysdba to dba
*
ERROR at line 1:
ORA-01931: cannot grant SYSDBA to a role

A database user can be granted both DBA and SYSDBA privilege.

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYS                            DBA                            YES YES
SCOTT                          DBA                            NO  YES
SYSMAN                         DBA                            NO  YES
LETS                           DBA                            NO  YES
SYSTEM                         DBA                            YES YES

Happy reading !!!

Eliminating Re-Creation of Control File when increasing the value of MAXDATAFILES parameter

With the enhancements in Oracle 10g Release 2, we can eliminate control file recreation for changing values of MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters. In earlier versions of Oracle database, you have to recreate the controlfile in order to modify any of these parameters.

These parameters are defined at the time of database creation. MAXDATAFILES parameter indicates the maximum number of datafiles that a database can accommodate. This parameter also affects the size of the of the control file.

DB_FILES initialization parameter specifies the maximum number of database files that can be opened for the database. If you modify this parameter then the instance should be bounced for the new value to take effect.

I have specified MAXDATAFILES to be 20 when creating my test database and started my instance with DB_FILES=19. We can check the current value of MAXDATAFILES for the database by:

1) Creating a trace of the control file, and,

2) Querying the V$CONTROLFILE_RECORD_SECTION view.

Check the current value of DB_FILES initialization parameter of the instance:

SQL> show parameter db_files

NAME TYPE VALUE
---------- ----------- -----------
db_files integer 20

Query the current value of MAXDATAFILES parameter defined for the database:

SQL> select type, record_size, records_total, records_used
from v$controlfile_record_section
where type = 'DATAFILE';

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 20 3

1 row selected.

Alternatively, we can check the value of MAXDATAFILES by creating a trace of the control file.

SQL> alter database backup controlfile to trace as 'c:\testdb\control\control.txt';

Database altered.

Open the file control.txt using any text editor and view the current setting for the MAXDATAFILES parameter.

Let’s, find out how many datafiles exist in the database:

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
3

I will create another 17 tablespaces with 17 datafiles using the below script:

SQL> select 'create tablespace drop_ts' || rownum ||
  2            ' datafile ''c:\testdb\data\drop_ts' || rownum ||
  3            '.dbf'' size 10M;' txt
  4    from all_objects where rownum <18;

TXT
-------------------------------------------------------------------------------
create tablespace drop_ts1 datafile 'c:\testdb\data\drop_ts1.dbf' size 10M;
create tablespace drop_ts2 datafile 'c:\testdb\data\drop_ts2.dbf' size 10M;
create tablespace drop_ts3 datafile 'c:\testdb\data\drop_ts3.dbf' size 10M;
create tablespace drop_ts4 datafile 'c:\testdb\data\drop_ts4.dbf' size 10M;
create tablespace drop_ts5 datafile 'c:\testdb\data\drop_ts5.dbf' size 10M;
create tablespace drop_ts6 datafile 'c:\testdb\data\drop_ts6.dbf' size 10M;
create tablespace drop_ts7 datafile 'c:\testdb\data\drop_ts7.dbf' size 10M;
create tablespace drop_ts8 datafile 'c:\testdb\data\drop_ts8.dbf' size 10M;
create tablespace drop_ts9 datafile 'c:\testdb\data\drop_ts9.dbf' size 10M;
create tablespace drop_ts10 datafile 'c:\testdb\data\drop_ts10.dbf' size 10M;
create tablespace drop_ts11 datafile 'c:\testdb\data\drop_ts11.dbf' size 10M;
create tablespace drop_ts12 datafile 'c:\testdb\data\drop_ts12.dbf' size 10M;
create tablespace drop_ts13 datafile 'c:\testdb\data\drop_ts13.dbf' size 10M;
create tablespace drop_ts14 datafile 'c:\testdb\data\drop_ts14.dbf' size 10M;
create tablespace drop_ts15 datafile 'c:\testdb\data\drop_ts15.dbf' size 10M;
create tablespace drop_ts16 datafile 'c:\testdb\data\drop_ts16.dbf' size 10M;
create tablespace drop_ts17 datafile 'c:\testdb\data\drop_ts17.dbf' size 10M;

17 rows selected.

SQL>

Querying the data dictionary shows that we have 20 datafiles in our database.

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
20

1 row selected.

Let us try to create one more tablespace and see what happens:

SQL> create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M;
create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

The command returns with an error indicating that we have already reached the maximum number of datafiles and it cannot create new datafiles anymore.

At this point, we have to shutdown the database, increase the value of DB_FILES initialization parameter, and start the database. Shut the database down:

SQL> shutdown immediate;

I modify the DB_FILES parameter in my PFILE to 25 (If you are using SPFILE then create a PFILE) and start the database:

SQL> startup pfile=c:\testdb\pfile\initTESTDB.ora

Query the DB_FILES initialization parameter after instance startup:

SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_files integer 25

SQL> select type, record_size, records_total, records_used
2 from v$controlfile_record_section
3 where type = 'DATAFILE';

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 20 20

Yeah, the new value is in effect. But querying the RECORDS_TOTAL column of V$CONTROLFILE_RECORD_SECTION view displays the maximum limit as 20.

Let us try to create a new tablespace with one datafile and see what happens.

SQL> create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M;

Tablespace created.

Wow, the tablespace is created. Lets query again V$CONTROLFILE_RECORD_SECTION view:

SQL> select type, records_total, records_used
2 from v$controlfile_record_section
3 where type = 'DATAFILE';

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 52 21

The control file has automatically expanded to accommodate the new datafile information. The RECORDS_TOTAL column value has increased to 52 (20 original + 32 the default value) and the RECORDS_USED column is at 21 (meaning we have 21 datafiles in our database).

The message recorded in the alert.log is :

create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M
Tue Jul 03 12:46:15 2007
Expanded controlfile section 4 from 20 to 52 records
Requested to grow by 32 records; added 1 blocks of records
Completed: create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M

Note: Temp files associated with temporary tablespace do not account for MAXDATAFILES limit.

Regards