Zephyros' Web Log: Oracle

레이블이 Oracle인 게시물을 표시합니다. 모든 게시물 표시

2015년 6월 26일 금요일

Max. Size of a Data file (Oracle): ORA-01688

오류 사항

ORA-01688: unable to extend table <schema>.<table> partition <parts> by <number> in tablespace <tablespace>

-------------------------------------------------------------------------------------------------------------
출처: https://community.oracle.com/thread/521373

Data files are not exactly unlimited in size, so the term "Unlimited" refers to the ceiling your datafile is able to reach, and it depends on the Oracle Block Size. To find the absolute maximum file size multiply block size by 4194303. This is the actual maximum size. You may want to read the Metalink Note:112011.1.

A datafile cannot be oversized, otherwise it could get corrupted. Let's say if your database is 8k blocks that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption.

Sizing datafiles is a matter of manageability, it depends on your storage, the amount of space allocated in a single managed storage unit.

128G is the maximum datafile size in 10g, but considering the maximum number of datafiles a Database can have, it can make a database to potentially size 8E (exabytes = 8,388,608 T).

The maximum data file size is calculated by:
Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:
Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

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

   2,048                      8              512

   4,096                     16            1,024

   8,192                     32            2,048

  16,384                     64            4,096

  32,768                    128            8,192

The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

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

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

해결 방안

SQL> ALTER TABLESPACE <tablespace_name> ADD DATAFILE

  2  <file_path> SIZE 10240M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Oracle's Data Pump Import: impdp

Syntax Diagrams for Data Pump Import

출처: Oracle Help Center

ImpInit

ImpStart

ImpModes

ImpOpts

ImpFilter

ImpRacOpt

ImptRemap

ImpFileOpts

ImpNetworkOpts

ImpDynOpts

ImpDiagnostics

examples)

$ impdp scott/tiger attach=import_job_name

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp logfile=logdir:import.log full=yes

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp full=yes transform=segment_attribute:n

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp full=yes transform=segment_attribute:n table_exists_action=truncate

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp transform=segment_attribute:n table_exists_action=append tables=scott.tablename

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp transform=segment_attribute:n table_exists_action=skip schemas=scott

$ impdp scott/tiger directory=datadumpdir dumpfile=datafile.dmp transform=segment_attribute:n table_exists_action=replace tablespaces=data_tbs

2015년 6월 25일 목요일

Oracle Linux 7 기반 Oracle Database 11g R2 설치

Linux 기초 명령어 (RedHat 계열)

    - hostname 변경
      # vi /etc/hostname
      # vi /etc/sysconfig/network
      # vi /etc/hosts
      # service network restart
      # reboot

    - zip / unzip
      $ zip -r test.zip ./*
      $ unzip happy.zip
      $ unzip happy.zip -d ./target

    - tar.gz
      $ tar -czvf images.tar.gz ./test
      $ tar -xzvf images.tar.gz

- software update
      # yum list updates
      # yum update –y

Oracle Database 11g R2 installation

$ su -

# df -h

# usermod -g oinstall -G dba oracle
# useradd -g oinstall -G dba oracle
# passwd oracle

# /sbin/sysctl -p
# /sbin/sysctl -a

# mkdir -p /opt/app/
# chown -R oracle:oinstall /opt/app/
# chmod -R 775 /opt/app/

# su - oracle
$ export TMP=/tmp
$ export TMPDIR=$TMP

$ export ORACLE_BASE=/opt/app/oracle
$ export ORACLE_SID=orcl

After installation

# vi /etc/oratab
SID:ORACLE_HOME:{Y|N|W} ----> Y

# vi /etc/init.d/oracle
#!/bin/bash

# oracle: Start/Stop Oracle Database 11g R2
#
# chkconfig: 345 90 10
# description: The Oracle Database is an Object-Relational Database Management System.
#
# processname: oracle

. /etc/rc.d/init.d/functions

LOCKFILE=/var/lock/subsys/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome
ORACLE_USER=oracle

case "$1" in
'start')
   if [ -f $LOCKFILE ]; then
      echo $0 already running.
      exit 1
   fi
   echo -n $"Starting Oracle Database:"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl start"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl start dbconsole"
   touch $LOCKFILE
   ;;
'stop')
   if [ ! -f $LOCKFILE ]; then
      echo $0 already stopping.
      exit 1
   fi
   echo -n $"Stopping Oracle Database:"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl stop"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbshut"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
   rm -f $LOCKFILE
   ;;
'restart')
   $0 stop
   $0 start
   ;;
'status')
   if [ -f $LOCKFILE ]; then
      echo $0 started.
      else
      echo $0 stopped.
   fi
   ;;
*)
   echo "Usage: $0 [start|stop|status]"
   exit 1
esac

exit 0

# chmod 755 /etc/init.d/oracle
# chkconfig --add oracle
# chkconfig oracle on

http://localhost:1158/em
$ emctl stop dbconsole
$ emctl config emkey -repos -sysman_pwd
$ emctl secure dbconsole -sysman_pwd
$ emctl start dbconsole
$ emctl config emkey -remove_from_repos -sysman_pwd

$ lsnrctl start
$ lsnrctl status
$ lsnrctl stop
$ netstat -anp | grep 1521 | grep LISTEN

$ sqlplus "/as sysdba"
SQL> startup
SQL> shutdown normal|immediate|abort

$ vi ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome
export ORACLE_HOME_LISTNER=$ORACLE_HOME/bin/lsnrctl
export ORACLE_SID=TMSRACDB
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH

Installed information

DB Global Name
SQL> SELECT * FROM props$ WHERE name='GLOBAL_DB_NAME';
SQL> SELECT * FROM global_name;

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO <>;

SID
SQL> select name from v$database;
SQL> SELECT instance FROM v$thread;

re-installation

# rm -Rf /usr/local/bin/oraenv
# rm -Rf /usr/local/bin/coraenv
# rm -Rf /etc/oratab

# chkconfig oracle off
# chkconfig --del oracle
# rm -Rf /etc/init.d/oracle
# rm -Rf $ORACLE_HOME

$ unset ORACLE_HOME
$ unset TNS_ADMIN

기타

** tnsname.ora
- $ORACLE_HOME/network/admin
- 로컬컴퓨터가 원격 서버로 접속할 방법을 서술

** listener.ora
- $ORACLE_HOME/network/admin
- 오라클서버에서 리스너를 기동시킬 때 (즉, lsnrctl start) 사용하게 되는 환경 파일

$ sqlplus -S scott/tiger < /data/script/test.sql
(-S : uses silent mode)