빠담이아빠(젊음은 길을 잃지 않는법)

용량이 꽉차서 , Oracle접속이 안될때..지울게 없을때

DB Full

1. 접속 안됨 ( sql developer도 마찬가지 )

[oracle@devdb ~]$ sqlplus / as sysdba

- 시스템 용량 확인.

[oracle@devdb ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VolGroup-lv_root 50G 47G 0G 100% /

- oracle이 설치된 디렉토리가 용량차지.

[root@devdb /]# du -sh /opt

38G /opt

- 데이타 파일 위치 확인

SQL> select name from v$datafile;

NAME

  --------------------------------------------------------------------------------

  /opt/oracle/oradata/devdb/system01.dbf

  /opt/oracle/oradata/devdb/sysaux01.dbf

  /opt/oracle/oradata/devdb/users01.dbf

  /opt/oracle/oradata/devdb/example01.dbf

  /opt/oracle/product/11gr2/dbs/jms.dbf

  /home/oracle/Data/santa15.dbf

  /opt/oracle/product/11gr2/dbs/santa3_1.dbf

  /opt/oracle/oradata/devdb/undotbs02.dbf

2. dbf 파일 이동.

[root@devdb dbs]# ls rlth /opt/oracle/product/11gr2/dbs

total 12G

-rw-r--r- 1 oracle oinstall 2.8K May 15 2009 init.ora

rw-r---- 1 oracle oinstall 24 Apr 19 2013 lkDEVDB

rw-r---- 1 oracle oinstall 1.5K Nov 10 18:53 orapwdevdb

rw-r---- 1 oracle oinstall 9.6G Dec 11 17:58 santa15.dbf

rw-rw--- 1 oracle oinstall 1.6K Dec 12 09:11 hc_devdb.dat

rw-r---- 1 oracle oinstall 769M Dec 12 09:11 jms.dbf

rw-r---- 1 oracle oinstall 1001M Dec 12 09:11 santa3_1.dbf

rw-r---- 1 oracle oinstall 2.5K Dec 12 09:12 spfiledevdb.ora

- 이중에 santa15.dbf 를 /home/oracle/Data 밑으로 카피.

(1) 디렉토리 생성

[oracle@devdb ~]$ mkdir -p /home/oracle/Data

(2) 파일카피

[oracle@devdb ~]$ cp -rp /opt/oracle/product/11gr2/dbs/santa15.dbf /home/oracle/Data/

(3) 리스너 stop / DB shutdown

[oracle@devdb ~]$ lsnrctl stop

[oracle@devdb ~]$ sqlplus / as sysdba

SQL> shutdown immdiate

(4) startup mount

SQL> startup mount

(5) dbf 위치 변경

SQL> alter database rename file '/opt/oracle/product/11gr2/dbs/santa15.dbf' to '/home/oracle/Data/santa15.dbf';

(6) alter database open

SQL> alter database open ;

(7) 변경 확인

SQL> select name from v$datafile;

NAME

  --------------------------------------------------------------------------------

  /opt/oracle/oradata/devdb/system01.dbf

  /opt/oracle/oradata/devdb/sysaux01.dbf

  /opt/oracle/oradata/devdb/users01.dbf

  /opt/oracle/oradata/devdb/example01.dbf

  /opt/oracle/product/11gr2/dbs/jms.dbf

  /home/oracle/Data/santa15.dbf

  /opt/oracle/product/11gr2/dbs/santa3_1.dbf

  /opt/oracle/oradata/devdb/undotbs02.dbf

- /opt/oracle/product/11gr2/dbs/santa15.dbf 파일 삭제.

[root@redmine2 views]# mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 64

Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.168.200.143' IDENTIFIED BY 'imsi00' WITH GRANT OPTION;

SQL Developer에서 DB 접속이 되지 않을때.

com # sqlplus / as sysdba

SQL > startup

com # lsnrctl start

이후에 sqldeveloper 에서 접속이 되지 않을 경우.

...

상황 )

 oracle 설치 후 sqldeveloper 설정한 뒤에 테스트하다가. 한참뒤에 재부팅.

 -> 그이후로 17002 에러가 뜨면서 접속이 되지 않음 .

조치 )

 tnsnames.ora ,  listener.ora 의 호스트 명을 변경.( 변경 전에는 localhost 로 적용이 되어있었다.)

>>listener.ora 파일 내용

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST =호스트명)(PORT = 1521))
    )
  )

>>tnsnames.ora 파일 내용

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 호스트명)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

내생각)

 기존에 만든 호스트 명이 재부팅을 하면서 바뀌어가지고. 접속이 되지 않았을 가능성이 있다.

 근데 localhost로 하면 되어야 하는게 정상인것 같다... localhost이니까

조치 이후 )

오라클 startup

리스너 start

sql developer 접속 원활히 잘됨. 끝 집 고고

1.설치파일

DBD-SQLite-1.42.tar.gz

DBI-1.631.tar.gz

2.설치

 gunzip DB*

 tar -xvf DB*.tar

 cd DB*

 make

 make test

 make install

rownum 기능이 없는 sqlite3

select * from idle

where jobid='4621'

order by time asc

limit 1;

limit 기능이 있다고 함. 굳

1. @ <- 배열에 넣는 방법;

   이게 편함

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" );  
$sth->execute();
      
my @row;
while (@row = $sth->fetchrow_array()) {
    print "@row\n";
}

$sth->finish();
$dbh->disconnect();

2.  $에 넣어서 @$로 받는 방법

  -> 테스트 하지 않음

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");
$sth->execute();

my $row;
while ($row = $sth->fetchrow_arrayref()) {
    print "@$row[0] @$row[1] @$row[2]\n";
}

$sth->finish();
$dbh->disconnect();

출처 : http://webkebi.zany.kr:9003/board/bView.asp?bCode=19&aCode=2538

1.필요 패키지 확인

Setarch와 compat-libstdc++은 없어도 됨.

# rpm -q(a) gcc make binutils openmotif setarch compat-db libaio compat-libstdc++

1.유저 추가(계정생성)

groupadd oinstall
groupadd dba
useradd -m -g oinstall -G dba oracle
passwd oracle

1.디렉토리 생성

mkdir -p /oracle/product/11.2.0/db_1

chown -R oracle:oinstall /oracle

chmod –R 775 /oracle

1.IP 설정.

127.0.0.1       localhost.localdomain  localhost

192.168.2.181   oracle

1.커널 파라미터 수정

Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

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

1.커널 수정 내역 적용

/sbin/sysctl –p

1.오라클 사용자 계정 Shell Limit 설정

Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  4096

oracle              hard    nofile  65536

oracle              soft    stack   10240

1./etc/pam.d/login 파일 수정

session required    pam_limits.so

추가.

1.Selinux 비활성화

/etc/selinux/config

SELINUX=disabled

1.환경변수

Login as the oracle user and add the following lines at the end of the ".bash_profile" file, remembering to adjust them for your specific installation.

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=ol5-112.localdomain; export ORACLE_HOSTNAME

ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME

ORACLE_BASE=/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=DB11G; 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

1.설치

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

su – oracle

./runInstaller

7.7 자동실행 설정

• 맨 마지막의 N을 Y로 변경