Install SQL Developer on Centos 5

Installing SQL Developer on Centos 5 is a little bit different from Installing SQL Developer on Open Suse.

1. Download SQL Developer installation from Oracle SQL Developer RPM for Linux.

2. Install that rpm package:

> su
Password:

# rpm -Uhv sqldeveloper-3.0.04.34-1.noarch.rpm

3. Download JDK from here. You need Self Extracting Installer…not RPM Installer

Go to the directory, where you have downloaded JDK(Note I’ve downloaded file for 64bit…Indicate the correct filename):

> su
Password:

Make it executable(indicate correct filename,which you’ve downloaded):

#chmod a+x jdk-6u26-linux-x64.bin

Run that file:

# ./jdk-6u26-linux-x64.bin

There will appear folder jdk1.6.0_26. Copy that folder to the desired location, you will need it.

4. Open the .bash_profile(located in $HOME directory) and add/edit the following entries:

JAVA_HOME=/usr/java/jdk1.6.0_26/
PATH=$PATH:/opt/sqldeveloper/
export PATH
export JAVA_HOME

5. Run SQL Developer:

# sqldeveloper

Writing First Node.js Server

Since Node.js project has been announced in 2009 it has gained very big interest and popularity in developers’ world. I wanted to play with node.js and wrote very simple server. It takes two parameters from HTTP request from browser URL and response is the sum of those two numbers.
Firstly we need to import http module for creating instance of server:

var http = require('http');

We will need url module too for simplifying parsing of arguments from URL:

var url = require('url');

Then we need to create server instance with listener function:

http.createServer(function (req, res) {
  ...
}).listen(7777);

This anonymous function is listener for client requests. It takes request and response objects as parameters. listen(7777) means that server is started on 7777 port at localhost.
For getting parameter values very easily in JSON object we will need just this:

var query = url.parse(req.url, true).query || {};

It is very simple and intuitive, as expected... In variable query we store object like this {a:1, b:2} if the request URL was ?a=1&b=2.
Let's check if both a and b are defined in query object and if yes then print sum if no then print warning in browser.

var rez = "";
if (query.a && query.b) {
  rez = (+query.a + +query.b) + "";
} else {
  rez = 'type url like "?a=1&b=2" to get sum from server!';
}

And finally there is whole code:

var http = require('http');
var url = require('url');

http.createServer(function (req, res) {
    res.writeHead(200, {'Content-Type': 'text/plain'});

    var query = url.parse(req.url, true).query || {};

    var rez = "";
    if (query.a && query.b) {
        rez = (+query.a + +query.b) + "";
    } else {
        rez = 'type url like "?a=1&b=2" to get sum from server!';
    }

    res.end(rez);
}).listen(7777);

How To Configure Network Interfaces in Solaris

I will discuss how to change existing interface by another one.

I have interface ce0 and want it to change with ce5.

1. Stop the interface

# ifconfig ce0 down

2. Unplumb the interface

# ifconfig ce0 unplumb

3. Plumb another interface

# ifconfig ce5 plumb

4. Configure the interface

# ifconfig ce5 10.0.0.7 netmask 255.255.255.0

5. Start the interface

# ifconfig ce5 up

This changes are not permanent, it will be lost after reboot. To make it permanent:

1. Edit the file /etc/hosts and enter IPs for each interface, but include different names.

For example,

hostname    10.0.0.7
hostname_ce5 10.0.0.6

In my case I have just one(because, I am not adding interface but changing)

hostname    10.0.0.7

2. edit the file(s) /etc/hostname.<interface>

# cat /etc/hostname.ce0
hostname

# cat /etc/hostname.ce5
hostname_ce5

In my case I have just the last one.

Connection to host as user oracle failed: NMO not setuid-root

Error in Oracle Enterprise Manager:
Error – Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)

Solution:

–Connect as a root user:

$ su -
Password:

# /u0/app/oracle/product/10.2.0/db_1/root.sh

ORACLE_OWNER= oracle
ORACLE_HOME=/u0/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: PRESS ENTER
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n)[n]:y
.. type  for each question

That’s it!

Gtk-WARNING **: cannot open display: :0.0

Connect as a normal user(I mean user who is logged into the x server).

!!!Not as a root user!!!

And allow any local user to load X displays:

$ xhost +

To disable it run the following:

$ xhost –

DWRProxy for ExtJs 4

If you are using ExtJs version 4.x for your RIA and DWR for easy to communicate between server-side and client-side (that means “easy ajax”, transform JavaScript object into Java object and vice-versa and so on) in that case you might have noticed that data package of ExtJs4 doesn’t support loading data from DWR method. I’we created Ext.ux.data.DwrProxy class that supports loading data into store from DWR method. Let’s look at sample code:


/**
 * Let's assume that we have already defined App.data.Store 
 * class with its Model class.
 */
var store = Ext.create('App.data.Store', {
    proxy: {
        type : 'dwr',
        /*DWR method that will return data.*/
        dwrFn : MyDWRClass.getRecords,

        /** Function that returns parameters for remote
         * DWR Method for each request.
         */
         getDwrArgs: function(operation, store) {
           var argObj = Ext.apply({}, operation.params);
           argObj.start = operation.start;
           argObj.limit = operation.limit;
           /**
            * If server side method takes several parameters
            * then array must be returned.
            */
           return argObj;
       },

       reader : {
           type: 'json',
           root: 'records',
           totalProperty: 'count'
       }
   }
}); 

The usage is very likely as DWRProxy classes for ExtJs 3.x and ExtJs 2.x but supports only loading data. I’m ready to implement create, update and destroy methods too if there will be any request for it (I only use DWRProxy just for loading data and paging).

I’ve implemented one additional config option – preprocessResponse that takes response data and fired before server response will be loaded into store. In very specific cases may be you need to do custom modifications to data before it will be load
ed by proxy.reader.

Please look at source of Ext.ux.data.DwrProxy and see more about what and how I’ve done and give me any feedback.

Wish you good luck with ExtJs4 and don’t forget to include DwrProxy.js. 🙂

UPDATE:
reader propery of proxy depends on response format and structure. In given case reader can read the following structured JSON object:

{
  /* because root = 'records' */
  records: [{},{},{},{}...],
  /* because totalProperty = 'count' */
  count: 100
}

ORA-32589:unable to drop minimal supplemental logging

Error: ORA-32589: unable to drop minimal supplemental logging

Cause: Minimal supplemental logging cannot be dropped until one of the PRIMARY KEY, FOREIGN KEY, UNIQUE or ALL COLUMN supplemental logging is enabled.

For example: You may have enabled the following supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

And now trying to turn off supplemental logging like this:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Action: Use the following query to determine which supplemental logging is turned on:

select supplemental_log_data_min
       ,supplemental_log_data_all
       ,supplemental_log_data_pk
       ,supplemental_log_data_ui
from v$database

For example: If the output is the following:

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI
------------------------- ------------------------ ------------------------ ------------------------
IMPLICIT                  NO                       YES                      NO

Do the following:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

And then:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Enabling ARCHIVELOG Mode

For High Availability features in Oracle you need to enable archivelog mode. When database is in ARCHIVELOG mode, redo logs are archived. These archivelog files are saved in a separate place and can be backed up. Archive logs are used by RMAN, Data Guard, Flashback and so on…

Log Miner, that is discussed in the next post, uses archive logs to show you the changes made to the database during some period of time.

If you are going to enable archivelog mode on a real database, it is better to shutdown database and make a cold backup. To save final noarchivelog mode backup.

Parameter that defines the location of archivelog files is LOG_ARCHIVE_DEST. You are able to set 10 different locations

for archivelogs: using log_archive_dest_1 through log_archive_dest_10 parameters.

The following system views provide us with the information about archiving:

V$DATABASE- identifies whether database is in ARCHIVELOG or NOARCHIVELOG mode.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

V$ARCHIVED_LOG – displays historical archived log information from the control file. If you are using recovery catalog, then RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST- displays archivelog destinations, its status…

SQL> select DEST_NAME, STATUS, DESTINATION from V$ARCHIVE_DEST;

DEST_NAME                  STATUS    DESTINATION
------------------------- ---------- ---------------------------------------
LOG_ARCHIVE_DEST_1    VALID        C:\oracle\product\10.2.0\db_1\RDBMS
LOG_ARCHIVE_DEST_2    INACTIVE
…
LOG_ARCHIVE_DEST_10    INACTIVE

As you can see my archivelogs will go to “C:\oracle\product\10.2.0\db_1\RDBMS”.

V$ARCHIVE_PROCESSES-Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG- Contains information about any backups of archived logs. If you use a recovery catalog, then RC_BACKUP_REDOLOG contains similar information.V$LOG-Displays all redo log groups,their sizes,number of members,are they archived or not and their status.

Now, after a brief introduction, let take our database into archivelog mode…

–Connect as sysdba

>set ORACLE_SID=ORCL

>sqlplus sys/pass as sysdba

–Shutdown database

SQL> shutdown immediate;

–Mount database

SQL> startup mount;

–Take DB into archivelog mode

SQL> alter database archivelog;

–Open database

SQL> alter database open;

–Check database mode

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Good Archiving!

Oracle Advanced Replication(Master-to-Master Definition)

In my previous post I discussed “Master-to-Master” replication configuration.

Master-to-Master Definition Replication implies that the transactions in the first database will be reflected to the second database, BUT NOT vise-versa.

Let’s start Master-to-Master Definition Replication:

I have created two databases:

ORCL-Master Site,
TEST-Master Definition Site

In this case, when there are DML operations on the ORCL site changes ARE reflected on the TEST database, but changes on the TEST site are not reflected on the ORCL database.

 

————————————ORCL——————————-

–Connect as sysdba

SQL> connect sys/sys_passwdg@orcl as sysdba

–Create user “REPADMIN”

SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
–Grant necessary privileges to “REPADMIN”
SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');
SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');
SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);
–Create public database link of another database
SQL> CREATE PUBLIC DATABASE LINK test using 'test';
–Connect as REPADMIN
SQL> connect repadmin/repadmin@orcl
–Create private database link in REPADMIN schema
CREATE DATABASE LINK test CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'test';
————————————TEST——————————-
–Connect as sysdba
SQL> connect sys/sys_passwdg@test as sysdba

–Create user “REPADMIN”

SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
–Grant necessary privileges to “REPADMIN”
SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');
SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');
SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);
–Create public database link of another database
SQL> CREATE PUBLIC DATABASE LINK orcl using 'orcl';
–Connect as REPADMIN
SQL> connect repadmin/repadmin@test
–Create private database link in REPADMIN schema
CREATE DATABASE LINK orcl CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'orcl';
–Create replication group, in which replication objects can be assigned
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('REP2');
–Assign replication object(HR.JOBS table) to the created group
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOBS', 'TABLE', GNAME=>'REP2');
–Assign replication object(Index on HR.JOBS table) to the created group
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOB_ID_PK', 'INDEX', GNAME=>'REP2');
Before declaring master database, I remind you that replicated tables must have primary key or at least unique constraints should be defined on the column(s).
If your table has primary key then continue from “Declare master database” section. Or do the following:

Define alternative key for table that have no primary key:

BEGIN
   DBMS_REPCAT.SET_COLUMNS(
     sname => 'HR',
     oname => 'AAA',
     column_list => 'A');
END;
/
Note: JOBS table already has primary key…
If your table has neither primary key nor unique constraint, during generating replication support it will give the following error:
ORA-23308: object HR.AAA does not exist or is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2874
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2849
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2928
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2856
ORA-06512: at "SYS.DBMS_REPCAT", line 766
ORA-06512: at line 2
–Declare master database
SQL>  EXEC DBMS_REPCAT.ADD_MASTER_DATABASE (GNAME=>'REP2', MASTER=>'ORCL', USE_EXISTING_OBJECTS =>TRUE, COPY_ROWS=>FALSE,PROPAGATION_MODE=> 'ASYNCHRONOUS');
  • ASYNCHRONOUS –With asynchronous replication, changes made at one master site occur at a later time at all other participating master sites.
  • SYNCHRONOUS – With synchronous replication, changes made at one master site occur immediately at all other participating master sites.
————————————ORCL——————————-
–Create job which pushes the deferred transactions to TEST master definition site after 10 seconds
SQL> show user;
User is "repadmin"
BEGIN
dbms_defer_sys.schedule_push(
destination => 'TEST',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/

–Create job which purges the deferred transactions after 3 days(means that deferred transactions were not pushed)

BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/

————————————TEST——————————-

SQL> show user
User is "repadmin"

—Generate replication support for the replicated object

SQL> exec dbms_repcat.generate_replication_support('HR','JOBS','TABLE');

In this case our repgroup is in QUIESCED mode, in which you cannot perform DML operations on the replicated objects, just SELECT is permitted. To solve this, you should run the following:

SQL> select status from dba_repgroup where gname='REP2';

STATUS
--------
QUIESCED

–Resume master activity

SQL> EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');

If it fails with ORA-23419 error, do this:

SQL> begin
  2  dbms_repcat.generate_replication_support('HR','JOBS','TABLE');
  3  DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');
  4  end;
  5  /

—Check the status again

SQL> select status from dba_repgroup where gname='REP2';

STATUS
--------
NORMAL

If you have read my previous post, you should notice that the only difference between previous and recent posts is that: in this post jobs are not created in TEST database(Jobs are created just in ORCL database).

Now let’s test that everything is OK.

–In ORCL database

SQL> show user;
User is "repadmin"

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20000      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20000
SA_REP     Sales Representative                      6000      12000
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2000       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected

–In TEST database

SQL> show user;
User is "repadmin"

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20000      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20000
SA_REP     Sales Representative                      6000      12000
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2000       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected

–In ORCL database

SQL> insert into hr.jobs values('AAA','Aaa',1500,2000);

1 row inserted

SQL> commit;

Commit complete

–In TEST database

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20000      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20000
SA_REP     Sales Representative                      6000      12000
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2000       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected

–Output is the same data, because 10 seconds was not passed yet!

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AAA        Aaa                                       1500       2000
AD_PRES    President                                20000      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20000
SA_REP     Sales Representative                      6000      12000
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2000       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

20 rows selected

As you can see the push process was happened after 10seconds, as it is indicated in the pushing job.

Warning!

Replication is not resistant to schema changes. It will fail with ORA-23474 error.

For example, if you change the type of existing columns(Adding columns do not arise any error), during inserting/deleting/updating(DML) there will arise the following error:

ORA-23474: definition of "HR"."AAA" has changed since generation of replication support

To solve this, do the following:

–From TEST database

SQL> exec dbms_repcat.suspend_master_activity(gname => 'REP2');
SQL> exec dbms_repcat.generate_replication_support(sname => 'HR',oname => 'AAA',type => 'TABLE');
SQL> exec dbms_repcat.resume_master_activity(gname => 'REP2');

I mentioned previously that adding columns do not arise an error, but it makes data inconsistency.

Look at this…

I have table HR.AAA on TEST  and ORCL databases, HR.AAA is already a repobject and support is also generated for it.

–TEST

SQL> delete from HR.AAA;

SQL> alter table HR.AAA add(B number);

–ORCL

SQL> alter table HR.AAA add(B number);

Do not forget that DDL changes are not replicated, just DML!!!

–TEST

SQL> insert into HR.AAA values(1,1);

SQL> commit;

SQL> select * from HR.AAA;

         A          B
---------- ----------
         1          1

–ORCL

SQL> select * from HR.AAA;

         A          B
---------- ----------
         1

As you can see “B” column was not reflected, to solve this do the following:

–TEST

SQL> exec dbms_repcat.suspend_master_activity(gname => 'REP2');
SQL> exec dbms_repcat.generate_replication_support(sname => 'HR',oname => 'AAA',type => 'TABLE');
SQL> exec dbms_repcat.resume_master_activity(gname => 'REP2');

Now check again:

–TEST

SQL> insert into HR.AAA values(2,1);

SQL> commit;

SQL> select * from hr.aaa;

         A          B
---------- ----------
         1          1

2 1

–ORCL

SQL> select * from HR.AAA;

         A          B
---------- ----------
         1

2 1

 

Good Luck!