Tuesday, September 25, 2012

5 minuted guide on taking Oracle dumps and restoring them (using exp/imp commands)

To take the a dump, use the below command

$ exp [username]/[password[ file=[anyname].dmp log=[anyname].log owner=[owner_of_db]

E.g.:- exp wso2user/wso2user file=wso2db.dmp log=wso2dberror.log owner=wso2user

To restore the dump which you took, use the below command

$ imp [new user]/[password] file=[dump to be restored].dmp fromuser=[who exported the db] touser=[new user]

E.g.:- imp wso2newuser/wso2newuser file=wso2db.dmp fromuser=wso2user touser=wso2newuser

Note that you need to create a new user using the below commands before issuing the above imp command.

drop user wso2newuser cascade;
Create user wso2newuser identified by wso2newuser account unlock;
grant connect to wso2newuser;
grant create session, dba to wso2newuser;
commit;


Sunday, September 23, 2012

How to resolve "ORA-00018: maximum number of sessions exceeded" issue

While working with WSO2 G-Reg which was connected to an Oracle DB, we came across and issue where the maximum number of connections were exceeded. 

To get over this exception, we had to increase the number of sessions, processors and transactions as below. 

I found this great blog which explains everything in detail. 


 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup