Sunday, November 17, 2013

sys_connect_by_path in mysql

In this we are going to introduced with mysql hierarchical data. In this post i am going to concat the hierarchical data from a table or we can say sys_connect_by_path in mysql or wm_concat in mysql.


1. create table
CREATE TABLE t_hierarchy (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        parent int(10) unsigned NOT NULL,
        PRIMARY KEY (id),
        KEY ix_hierarchy_parent (parent, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Fill Data in table
DELIMITER $$
CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT)
BEGIN
        DECLARE _level INT;
        DECLARE _fill INT;
        INSERT
        INTO    t_hierarchy (id, parent)
        VALUES  (1, 0);
        SET _fill = 0;
        WHILE _fill < fill DO
                INSERT
                INTO    t_hierarchy (parent)
                VALUES  (1);
                SET _fill = _fill + 1;
        END WHILE;
        SET _fill = 1;
        SET _level = 0;
        WHILE _level < level DO
                INSERT
                INTO    t_hierarchy (parent)
                SELECT  hn.id
                FROM    t_hierarchy ho, t_hierarchy hn
                WHERE   ho.parent = 1
                        AND hn.id > _fill;
                SET _level = _level + 1;
                SET _fill = _fill + POWER(fill, _level);
        END WHILE;
END
$$
DELIMITER ;

START TRANSACTION;
CALL prc_fill_hierarchy(6, 5);
COMMIT;

3. create function
delimiter $$
CREATE FUNCTION hierarchy_sys_connect_by_path(delimiter TEXT, node INT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
     DECLARE _path TEXT;
 DECLARE _cpath TEXT;
        DECLARE _id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
      SET _path = _id;
    LOOP
                SELECT  parent
              INTO    _id
         FROM    t_hierarchy
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
              SET _path = CONCAT(_id, delimiter, _path);
  END LOOP;
END
$$

delimiter ;

4. Hierarchical Query
SELECT  hi.id AS id,
        hierarchy_sys_connect_by_path('/', hi.id) AS tree_position,
        parent
FROM    (
        SELECT  id,
                CAST(@level AS SIGNED) AS level
        FROM    (
                SELECT  @start_with := 1,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id limit 10

The result will be below format :
43293    | 1/3/12/67/312/1957/9172/43293

moreover we can replace the input variable i.e. connector here we are using '/'.

Source : http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/



For any queries please comment :-)

Saturday, November 16, 2013

DML- DATATYPE

Today We are going to study about oracle datatypes and their type where to use , which to use.
I am going to post today difference between varchar2(1 char) and nvarchar2(1).



Today only one difference, but timely i will update this post, with more diff's of datatypes.
Check the below set of queries
create table t (a varchar2(1));
create table t1 (b varchar2(1 char));

SQL> insert into t values (unistr('\0161'));
insert into t values (unistr('\0161'))
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."A" (actual: 2, maximum: 1)
SQL> insert into t1 values (unistr('\0161'));
1 row created.
SQL> select * from t1;
B
-
Ü

If you look at above queries, here i create two tables having datatypes varchar2(1) and
varchar2(1 char). If i insert data in both tables (data is same and byte len is two), table with datatype varchar2(1) is fail to insert and table with datatype varchar2(1 char) has been not failed the reason being byte length of string.

 On other hand nvarchar2(1) and varchar2(1 char) will not fail in above case.
The NVARCHAR2 datatype was for that want to use Unicode values without changing the character set for database (which is used by VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.
Both columns in your example (Unicode VARCHAR2(1 CHAR) and NVARCHAR2(1)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.

Saturday, November 9, 2013

Recover database if it is in between crashed

Create New database using old datafiles.

For this we will use oradim approach which is generally used while manual database creation.

prerequisite:
- New window.
- No previous instance.
- Cold backup or copied datafile ( while database running , it doesn't matter).
- Oracle should be installed with option ( software only ).
- Chose new instance name ( here we are using PROD).
- Directory structure should be same as in pfile.


Start 1,2,3 go :-)

1. Open cmd

set oracle_sid=prod
oradim -new -sid prod -intpwd prod -startmode m -pfile D:\app\pfile.ora

You might got this error
DIM-00014: Cannot open the Windows NT Service Control Manager.
solution open cmd with "run as administrator"

2. Below is the content of my pfile.ora file
db_name='PROD'
memory_target=1G
processes = 150
audit_file_dest='D:\app\username\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=D:\app\username\admin\prod\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=D:\app\username\admin\prod\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("D:\app\username\oradata\prod\CONTROL1.ctl")
compatible ='11.2.0'

3. At this stage we need to create control file after startup database at nomount state
    connect with sysdba user
   (before creation of control file and reuse of datafile , copy all the datafiles and redo log file)

C:\Windows\system32> sqlplus sys/prod as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 9 15:35:48 2013

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

Connected to an idle instance.

SQL> startup nomount pfile='D:\app\pfile.ora'
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
  2  MAXLOGFILES 16
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 8
  6  MAXLOGHISTORY 18688
  7  LOGFILE
  8  GROUP 1 'D:\app\username\oradata\prod\REDO01.LOG' SIZE 50M,
  9  GROUP 2 'D:\app\username\oradata\prod\REDO02.LOG' SIZE 50M,
 10  GROUP 3 'D:\app\username\oradata\prod\REDO03.LOG' SIZE 50M
 11  DATAFILE
 12  'D:\app\username\oradata\prod\SYSTEM01.DBF',
 13  'D:\app\username\oradata\prod\SYSAUX01.DBF',
 14  'D:\app\username\oradata\prod\UNDOTBS01.DBF',
 15  'D:\app\username\oradata\prod\USERS01.DBF',
 16  'D:\app\username\oradata\prod\TRY01.DBF',
 17  'D:\app\username\oradata\prod\LOB_INDEX.DBF'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.
 
after creation of control file the system will automatically move to MOUNT State.
 
 
 (media recovery needed) 
SQL>  RECOVER DATABASE;
Media recovery complete.
 
 
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger
Connected.
 
Thats it.. 
 
Now we need to create spfile from pfile, so that database can startup without any problem after shutdown
 create spfile from pfile='D:\app\pfile.ora';

You may also like this post
Oracle de install / clear all previous directory



Oracle Deinstallation


 While DE-installation some times the folders at installation path not get removed, so we need to do some manual work to remove those directories. After follow below instruction you can able to remove all oracle installation folders and files.
  • Uninstall all Oracle components using the Oracle Universal Installer (OUI).
  • Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.
  • Delete Oracle services at registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. (Oracle services are starts with “Ora”).
  • Reboot the machine.
  • Delete the ORACLE installation folder
  • Empty the recycle bin.

Sunday, November 3, 2013

Oracle 10g installtion in red hat linux (11g)


10g/ 11g



1.Installtion of Oracle10g :-login as root user and make these changes:-

   open the terminal by right click on desktop

   and open the sysctl.conf file and define all these parameter in this.

   # gedit /etc/sysctl.conf (press enter) and paste these parameter in

     this file and save it.

kernel.sem=250 32000 250 250
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

2. execute below command from root user

# useradd oracle
# passwd oracle
# groupadd oinstall
# groupadd dba
# usermod -g oinstall -G dba oracle
# usermod -g oinstall -G apache apache
# chown -R oracle:oinstall /u01
# chown -R oracle:oinstall /u02
# chmod -R 777 /u01
# chmod -R 777 /u02

 

3.Apply all rpm as listed at below link


    http://docs.oracle.com/cd/E11882_01/install.112/e24321/pre_install.htm#LADBI1112 (11g)

    http://docs.oracle.com/cd/B19306_01/install.102/b14203/prelinux.htm (chapter 2.9.1 ) 10g

   using command on terminal:-

# rmp -ivh (path of rpm)
   or

   (you can use yum repository to install them)

 

4. For 11g user, change the entry of redhat release from this file:-

   # gedit /etc/redhat-release (press enter)

     replace 5 with 4 and save it.



5.logout from root user and login as oracle users and create bash_profile:-(if not exists)


$ [oracle@localhost ~]$vi .bash_profile

 
 
  
$ gedit .bash_profile(and paste the parameter and save it)

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_TERM=xterm

export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export editor=gedit
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/rdbms/jlib


6. open the terminal and make directory:-

$ mkdir -p /u01/app/oracle/product/10.2.0



7. copy the 10g dump on desktop before starting the setup

   logout from oracle user and login again to reflect the

    changes of bash_profile.



8. Open the terminal run the setup like this

$ cd /home/oracle/Desktop/10g-dump(press enter)
$ [oracle@localhost 10g-dump]$./runInstaller (press enter)



9. After completig the installtion open terminal and

    login as root($ su - root) user and run

    both script that showing in the end the name is:-

# /home/oracle/orainventory/orainstRoot.sh
# /u01/app/oracle/product/10.2.0/db_1/root.sh



10. Open the terminal and login in oracle

$ sqlplus



you may also like:---

ORA-01102 cannot mount database in EXCLUSIVE mode

Oracle startup error : ORA-01078: failure in processing system parameters LRM-00109

ORA-00845: MEMORY_TARGET not supported on this system

 

Friday, November 1, 2013

ORA-01102 cannot mount database in EXCLUSIVE mode

ORA-01102 cannot mount database in EXCLUSIVE mode

    Cause: Some other instance has the database mounted exclusive or shared.
    Action: Shut down the other instance or mount in a compatible mode.

shu immediate and try again to startup the database server

In my case there is problem in .bash_profile
where ORACLE_SID is not set.

"login with ORACLE user"
vim .bash_profile

"press i and add"
ORACLE_SID=orcl

restart/logout the machine to reflect the change to oracle user

 sqlplus sys/orcl as sysdba

SQL startup pfile='/u01/app/oracle/product/11.2.0/dbhome/dbs/*.ora'
ORACLE instance started.

Database mounted.
Database opened.

Oracle startup error : ORA-01078: failure in processing system parameters LRM-00109

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/11.2.0/dbhome_1/dbs/inittest01.ora'

This error is because of unavailability of file or current user does not have access to spfile.ora at path $ORACLE_HOME/dbs/*.ora


[root@localhost ~]# cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[root@localhost dbs]# ls -l
-rwxrwxrwx. 1 oracle oinstall 2851 May 15  2009 init.ora
-rwxrwxrwx. 1 oracle oinstall   24 Oct 31 23:06 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Oct 31 23:10 orapworcl
-rwxrwxrwx. 1 oracle oinstall  911 Nov  1 00:48 spfileorcl.ora
[root@localhost dbs]
 
make sure *.ora file must have permission to oracle user  
 
if file not have permissions , then use this commands to grant permissions 
 
 
[root@localhost dbs]# chown -R oracle:oinstall *
[root@localhost dbs]# chmod -R 777 *

Sometimes you got the error
ORA-01078: failure in processing system parameters
LRM-00123: invalid character found in the input file



When we try to startup the database using pfile , sometime above error can be shown, because of your pfile is containing invalid chars.

To overcome this error edit your ora file using "vim" or "gedit" editor and remove all invalid characters. Then then startup your database.
web stats