NLS_LENGTH_SEMANTICS,oracle

ORACLE RSS Icon ATOM Icon 2014/02/20 11:20 visualp

9i에서 10G이상 데이터를 이관하다 보면 ,
charset을 euc-kr에서 utf-8로 변경해야 되는 상황이 발생 한다.
그랬을 때 byte로 되어 있는 euc-kr을 utf-8로 이관 하면서
varchar2에 maxsizse를 넘어 버려서 에러가 발생하는 경우가 있다
그러면 다음과 같이 nls_length_semantics을 default BYTE를
CHAR로 변환 한다.


#in sqlplus
show parameter nls_length
alter system set nls_length_semantics=CHAR scope=both;
show parameter nls_length



NLS_LENGTH_SEMANTICS
enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.

NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.

NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string

Example: NLS_LENGTH_SEMANTICS = 'CHAR'

Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR
2014/02/20 11:20 2014/02/20 11:20
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/644

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/644

oracle 12c. bash_profile config

ORACLE RSS Icon ATOM Icon 2014/02/10 18:06 visualp

Now it is called Oracle Enterprise Manager Cloud Control (previously, Grid Control).

Version: 12

Platform: OEL 6.1, x86_64 (Installed on a VM)

Requirements: Oracle Database 11g Release 2 11.2.0.3

1 - Install the database

1a- firewall and SELinux should be disabled:

# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination  
# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
# SELINUX=enforcing
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted 

#

1b - Verify that /etc/hosts contains the server's fqdns

# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
10.20.30.40    my-em-host.my.domain.com my-em-host
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

#

1c - Modified kernel parameters: added in /etc/sysctl.conf the following entries:

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Then called

# /sbin/sysctl -p 

1d - Added the following lines to /etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

1e - Added groups and users:

# groupadd -g 502 oinstall
# groupadd -g 503 dba
# groupadd -g 504 oper
# groupadd -g 505 asmadmin
# groupadd -g 506 asmoper
# groupadd -g 507 asmdba
# useradd -u 503 -g oinstall -G dba,asmdba,oper oracle
# passwd oracle

1f - Create the directory for the database

# mkdir -p /u01/app/oracle/product/11.2.0/db_1
# chown -R oracle:oinstall /u01
# chmod -R 775 /u01

1g - install the following packages (use yum for this):

glibc-devel-2.12-1.25.el6.i686 ksh-20100621-6.el6.x86_64 
libaio-0.3.107-10.el6.i686 libaio-devel-0.3.107-10.el6.i686 libaio-devel-0.3.107-10.el6.x86_64 
libstdc++-4.4.5-6.el6.i686 numactl-devel-2.0.3-9.el6.x86_64

1h - login as 'oracle', add the following lines to .bash_profile:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=my-em-host.my.domain.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

1i - log in as 'oracle', unzip the oracle-db zips, and run 'runInstaller'

Configure Security Updates: NULL

Download Software Updates: Skip'

Installation Option: Create and configure database

System Class: Server class

Grid options: Single instance

Install type: Typical

Typical Installation: accept values; osdba group: oinstall

Create Inventory: accept default

Prerequisite checks: Installed via yum: compat-libcap1, compat-libstdc++-33, ksh, libaio-devel

Finish: run scripts as 'root':

# /u01/app/oraInventory/orainstRoot.sh  # /u01/app/oracle/product/11.2.0/db_1/root.sh 

1j - Edit /etc/oratab, set resatrt flag to 'Y':

orcl:/u01/app/oracle/product/11.2.0/db_1:Y

1k - create as root file /etc/init.d/dbora

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

Then:

# chmod 750 /etc/init.d/dbora
# chkconfig --add dbora

1k - start oracle db

1l - as user 'oracle' access sqlplus / as sysdba and reset password for sys, system and sysman

2 as user oracle run:

emca -deconfig dbcontrol db -repos drop -SYS_PWD <password-SYS> -SYSMAN_PWD <password-SYSMAN>

 3 - as user oracle run sqlplus / as sysdba and:

sqlplus / AS SYSDBA

ALTER SYSTEM SET processes=300 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
ALTER SYSTEM SET job_queue_processes=20 SCOPE=SPFILE;

-- May be required if using older versions of DB.
--ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;
--ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

-- Restart the instance.
SHUTDOWN IMMEDIATE
STARTUP 

Got problems to start the database:

SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M

So, I did:

SQL> create pfile from spfile;

And edited the created file

/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

and set *.memory_target=3221225472

but also remounted the devshm (as root):

# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
...
tmpfs                  1929528       528   1929000   1% /dev/shm
...
# mount -t tmpfs shmfs -o size=4g /dev/shm
# df -h
...
tmpfs                 4.0G     0  4.0G   0% /dev/shm
...
# vi /etc/fstab
...
tmpfs        /dev/shm        tmpfs    size=4g        0 0
...
#

then restarted the database:

SQL> startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size            2232640 bytes
Variable Size         1761611456 bytes
Database Buffers     1426063360 bytes
Redo Buffers           16928768 bytes
Database mounted.
Database opened.
SQL> 

And recreated the spfile

SQL> CREATE SPFILE FROM PFILE;

4 - As 'oracle' created the directory

/u01/app/oracle/Middleware 

4a - install:

 binutils-devel-2.20.51.0.2-5.20.el6.i686

5 - unzipped the OEM zips, and as 'oracle' user ran 'runInstaller'

5a - MOS: no email, uncheck box

5b - Software updates: Skip

5c - Pre-requisite checks: glib-devel warning, ignore and continue

5d - Install types: Create new EM -> Simple -> Middelware location:

/u01/app/oracle/Middleware 

5e - Configuration details: ... (admin password same as DB password)

5f -Warning dialog: click 'Yes' to disable stats gathering job

5g - Warnings : 'OK'

5h - Review: Click 'Install'

5i - After Installation, when pop-up indicates so, run (as root):

/u01/app/oracle/Middleware/oms/allroot.sh

(Note: it overrides all the existing files)

5j - Finishing installation:

This information is also available at: 
    /u01/app/oracle/Middleware/oms/install/setupinfo.txt
See below for information pertaining to your Enterprise Manager installation:
Use the following URL to access:
    1. Enterprise Manager Cloud Control URL: https://my-em.host.my.domain.com:7803/em
    2. Admin Server URL: https://my-em-host.my.domain.com:7103/console
The following details need to be provided during the additional OMS install:
    1. Admin Server Hostname: my-em-host.ie.oracle.com
    2. Admin Server Port: 7103
NOTE: An encryption key has been generated to encrypt sensitive data in the Management Repository. If this key is lost, all encrypted data in the Repository becomes unusable. Please run following command to backup the Management Service configuration including the emkey and keep the backup on another system:
 emctl exportconfig oms -dir <backup location>

5k - Log-In ...

5l - Accept License Agreement

Homepage Selector Screen -> Summary -> 'Preview'; 'Select As My Home'

6 - Create (as root) start/stop script /etc/init.d/emora:

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export OMS_HOME=/u01/app/oracle/Middleware/oms
export AGENT_HOME=/u01/app/oracle/Middleware/agent/core/12.1.0.1.0

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start everything

        #$ORACLE_HOME/bin/dbstart $ORACLE_HOME
        /etc/init.d/dbora start

        su - $ORA_OWNER -c "$OMS_HOME/bin/emctl start oms"

        su - $ORA_OWNER -c "$AGENT_HOME/bin/emctl start agent"
        ;;
    'stop')
        # Stop everything

        su - $ORA_OWNER -c "$OMS_HOME/bin/emctl stop oms -all"

        su - $ORA_OWNER -c "$AGENT_HOME/bin/emctl stop agent"

        #$ORACLE_HOME/bin/dbshut $ORACLE_HOME
        /etc/init.d/dbora stop
        ;;
esac

Then:

# chmod 750 /etc/init.d/emora
2014/02/10 18:06 2014/02/10 18:06
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/642

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/642

MP-00003: ORACLE error 20001 encountered
ORA-20001: Invalid or inconsistent input values

imp username file=filename.dmp log=logname.log  full=y statistics=none

options add "statistics=none"


더 보기

http://agreeable.tistory.com/entry/Oracle-expimp
2014/02/07 16:55 2014/02/07 16:55
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/641

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/641

서비스와 관련된 프로세스를 사용하던 프로세스가 없습니다.

라는 에러가 발생 하면,
윈도우 2003을 안전모드로 부팅하여 진행한다.

설치 완료후 재부팅 해서
sqlplus로 접속 해서
다음과 같이
select * from v$version
쿼리를 날려 오라클 버전업이 되었는지 확인한다.
2014/02/07 16:42 2014/02/07 16:42
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/640

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/640

[원문]
Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)

Related articles.

Introduction

A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set. This concept is not a new one. In fact, Oracle already provides multiple ways to perform Top-N queries, as discussed here. These methods work fine, but they look rather complicated compared to the methods provided by other database engines. For example, MySQL uses a LIMIT clause to page through an ordered result set.

SELECT * 
FROM   my_table 
ORDER BY column_1
LIMIT 0 , 40

Oracle 12c has introduced the row limiting clause to simplify Top-N queries and paging through ordered result sets.

Setup

To be consistent, we will use the same example table used in the Top-N Queries article.

Create and populate a test table.

DROP TABLE rownum_order_test;

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

The following query shows we have 20 rows with 10 distinct values.

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6

       VAL
----------
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

SQL>

Top-N Queries

The syntax for the row limiting clause looks a little complicated at first glance.

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Actually, for the classic Top-N query it is very simple. The example below returns the 5 largest values from an ordered set. Using the ONLY clause limits the number of rows returned to the exact number requested.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

Using the WITH TIES clause may result in more rows being returned if multiple rows match the value of the Nth row. In this case the 5th row has the value "8", but there are two rows that tie for 5th place, so both are returned.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

SQL>

In addition to limiting by row count, the row limiting clause also allows us to limit by percentage of rows. The following query returns the bottom 20% of rows.

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

SQL>

Paging Through Data

Paging through an ordered resultset was a little annoying using the classic Top-N query approach, as it required two Top-N queries, one nested inside the other. For example, if we wanted the second block of 4 rows we might do the following.

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   rownum_order_test
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

With the row limiting clause we can achieve the same result using the following query.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

The starting point for the FETCH is OFFSET+1.

The OFFSET is always based on a number of rows, but this can be combined with a FETCH using a PERCENT.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

Not surprisingly, the offset, rowcount and percent can, and probably should, be bind variables.

VARIABLE v_offset NUMBER;
VARIABLE v_next NUMBER;

BEGIN
  :v_offset := 4;
  :v_next   := 4;
END;
/

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET :v_offset ROWS FETCH NEXT :v_next ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

SQL>

Extra Information

  • The keywords ROW and ROWS can be used interchangeably, as can the FIRST and NEXT keywords. Pick the ones that scan best when reading the SQL like a sentence.
  • If the offset is not specified it is assumed to be 0.
  • Negative values for the offset, rowcount or percent are treated as 0.
  • Null values for offset, rowcount or percent result in no rows being returned.
  • Fractional portions of offset, rowcount or percent are truncated.
  • If the offset is greater than or equal to the total number of rows in the set, no rows are returned.
  • If the rowcount or percent are greater than the total number of rows after the offset, all rows are returned.
  • The row limiting clause can not be used with the FOR UPDATE clause, CURRVAL and NEXTVAL sequence pseudocolumns or in an fast refresh materialized view.

For more information see:

2014/02/03 11:05 2014/02/03 11:05
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/636

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/636

oracle 12c 설치관련

ORACLE RSS Icon ATOM Icon 2014/01/24 10:25 visualp

[원문] : http://netmaid.tistory.com/archive/20130910

Oracle Linux 는 기본적으로 Red Hat Enterprise Linux 와 동일합니다.

따라서 CentOS 하고도 동일합니다. 단지 내부적으로 최적화를 위해서 몇가지 configuration 이 변경된 것으로 알고 있습니다. 자세한 사항은 http://en.wikipedia.org/wiki/Oracle_Linux 를 참고합니다.

이하 최신 Oracle Linux 6.4 를 기준으로 설치하고, Oracle DB 12c Release 1 을 설치해봅니다.

1. Oracle Linux 설치

다운로드 이후에, iso 이미지를 이용하여 설치를 진행합니다.

중간에 서버의 용도를 선택하는데 여기서 Database Server 로 선택합니다.

2. 네트워크 설정

CentOS 와 동일합니다. 참고: http://netmaid.tistory.com/91

DNS 도 설정합니다. 설정하지 않으면, 오라클 도메인을 찾지 못하여 업데이트가 안될 수도 있습니다.

3. 최신으로 업데이트

yum update 로 진행합니다.

4. Oracle DB 12c Release 1 다운로드

오라클 홈페이지에서 다운로드합니다. Linux x86-64 버전의 linuxamd64_12c_database_1of2.zip, linuxamd64_12c_database_2of2.zip 파일을 다운로드합니다.

scp 를 이용하여 설치하려는 서버로 업로드합니다.

참고: http://netmaid.tistory.com/23

5. 압축해제

$ unzip linuxamd64_12c_database_1of2.zip
$ unzip linuxamd64_12c_database_2of2.zip

6. 오라클 계정 생성

Oracle DB 는 보안상 root 계정으로 설치할 수 없게 되어 있습니다. 별도의 계정을 만듭니다.

$ adduser oracle

5번에서 압축해제한 파일이 root 권한으로 되어 있으므로, oracle 계정 공간으로 옮기고 권한도 바꿉니다.

$ mv database /home/oracle
$ chown -R oracle:oracle /home/oracle/database

7. Xfce 설치

Oracle DB 는 GUI 환경에서 설치를 진행합니다. 원격의 GUI 환경에서 접속하여 Oracle DB 를 설치합니다.

원격의 GUI 환경으로는 Xubuntu 를 선택했습니다. Xubuntu 는 Xfce 를 기본 xwindow 로 탑재하고 있습니다.

따라서 Oracle Linux 서버에도 Xfce 를 설치합니다.

Xfce 를 설치하려면 EPEL 저장소를 추가해야 합니다.

$ yum install wget
$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ rpm -ivh epel-release-6.8.noarch.rpm

Xfce 를 설치합니다.

$ yum groupinstall Xfce

추가로 폰트, 인증 모듈, 유틸 모듈도 설치합니다.

$ yum install xorg-x11-fonts-Type1 xorg-x11-fonts-misc
$ yum install xorg-x11-xauth xorg-x11-utils

참고:

8. ssh 를 통해서 xwindow 접속이 가능하도록 설정

sshd_config 를 수정하여 다음의 3개를 활성화시킵니다.

$ vi /etc/ssh/sshd_config
.....
X11Forwarding yes
X11DisplayOffset 10 
X11UseLocalhost yes

9. Xubuntu 를 설치한 서버에서 Oracle Linux 로 접속

터미널 창을 띄우고 ssh 로 -X 옵션을 지정하여 접속합니다. (원격으로 xwindow 를 활성화하여 접속하는 것입니다)

$ ssh -X oracle@oracle_linux_ip

10. hostname 점검

원격으로 접속을 성공했으면, hostname 과 ip 를 확인합니다.

$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME = test.domain.com

$ vi /etc/hosts
127.0.0.1 test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4

제대로 설정되지 않으면 DB 설치 진행 중에 다음과 같은 오류 메시지가 뜰 것입니다.

PRVF-0002 : Could not retrieve local nodename

참고:

11. DB data 디렉토리 생성

root 권한으로 디렉토리를 생성합니다. 생성한 디렉토리는 oracle 계정 권한으로 만듭니다.

$ su root
$ mkdir /oradata
$ chown oracle:oracle /oradata

12. limits.conf 변경

root 권한으로 보안 설정을 바꿉니다. 이는 성능을 높이기 위해 시스템 자원을 많이 사용할 수 있도록 설정하는 것입니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/security/limits.conf
.....
oracle	soft	nproc	2047
oracle	hard	nproc	16384
oracle	soft	nofile	1024
oracle	hard	nofile	65536

참고:

13. sysctl.conf 변경

root 권한으로 시스템 설정을 바꿉니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/sysctl.conf
.....
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744

net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

참고:

14. Oracle DB 를 위한 종속적인 패키지 설치

root 권한으로 Oracle DB 설치에서 필요한 패키지를 추가로 설치해둡니다.

$ yum install compat-libcap1 gcc gcc-c++ ksh compat-libstdc++-33 libaio-devel

15. 시스템을 리부팅

12번과 13번에서 시스템 설정을 변경했기 때문에 root 권한으로 리부팅을 합니다.

$ sudo shutdown -r now

16. Oracle DB 설치

리부팅이 완료되면, 9번처럼 원격의 Xubuntu 에서 oracle 계정 권한으로 서버에 접속합니다. Oracle DB 를 설치합니다.

$ cd database
$ ./runInstaller

GUI 에 따라서 진행합니다. 오라클 계정이 있다면, 최신 업데이트를 받을 수 있습니다. 그리고, 중간에 새로운 database 를 생성하는 옵션으로 설정합니다. (대부분은 이것을 선택할 것입니다.)

설치를 진행하다가 다음 화면에서 멈춥니다.

17. root 로 스크립트 설치

다른 터미널을 통해서 Oracle Linux 서버에 root 권한으로 접속합니다.

GUI 에서 명시한 대로 스크립트를 실행합니다.

$ /home/oracle/app/oraInventory/orainstRoot.sh
$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh

18. Oracle DB 설치 계속

스크립트를 실행한 뒤에 GUI 를 계속 진행합니다. 그러면 설치가 마무리 됩니다.

19. 방화벽 설정

외부 서버에서 접속이 가능하도록 방화벽의 포트를 개방합니다.

$ vi /etc/sysconfig/iptables
.....
.....

참고:

20. 접속 테스트

Oracle DB 설치 참고 문서:

2014/01/24 10:25 2014/01/24 10:25
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/634

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/634

oracle limit 구현

ORACLE RSS Icon ATOM Icon 2013/02/28 17:29 visualp
   select * from(
                select rownum rnum, A.*  from table_name A
              )
              where rnum <100 and rnum>10;
          
2013/02/28 17:29 2013/02/28 17:29
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/564

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/564

오라클 웹 관리자 세팅

ORACLE RSS Icon ATOM Icon 2013/02/05 14:24 visualp


1.포터번호 변경
SQL>EXEC DBMS_XDB.SETHTTPPORT(원하는포트번호);

2.접근권한 변경
SQL>EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
2013/02/05 14:24 2013/02/05 14:24
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/547

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/547

oracle 10G charset 변경

ORACLE RSS Icon ATOM Icon 2013/02/05 14:00 visualp
Oracle 10g Character Set 변경

SQLPLUS 접속후 (system 계정으로 로그인 혹시 모른다면 sqlplus /nolog; 후 conn /as sysdba; 로 접속한다)

C:\>sqlplus /nolog;

sql>conn /as sysdba;

<-- 여기서 sysdba로 로그인이 안된다면
sql>conn /as sys
로 로그인 하여 진행 한다.


변경하고자하는 캐릭터셋을 수정

sql>update sys.props$ set value$='UTF8' where name='NLS_CHARACTERSET';

sql>update sys.props$ set value$='UTF8' where name='NLS_NCHAR_CHARACTERSET';

sql>update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE';

sql>commit;



재시작

sql>shutdown immediate;

sql>startup mount;

sql>alter system enable restricted session;

sql>alter system set job_queue_processes=0;

sql>alter system set aq_tm_processes=0;

sql>alter database open;

sql>alter database character set UTF8;

sql>shutdown immediate;

sql>startup;

2013/02/05 14:00 2013/02/05 14:00
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/546

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/546

pl/sql , replace , 문자열 변환

ORACLE RSS Icon ATOM Icon 2012/11/12 10:59 visualp

Oracle/PLSQL: Replace Function

In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.

Syntax

The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

Applies To

  • Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

replace('123123tech', '123'); would return 'tech'
replace('123tech123', '123'); would return 'tech'
replace('222tech', '2', '3'); would return '333tech'
replace('0000123', '0'); would return '123'
replace('0000123', '0', ' '); would return ' 123'

[원문] : http://www.techonthenet.com/oracle/functions/replace.php
2012/11/12 10:59 2012/11/12 10:59
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/530

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/530