[oracle@OracleDG01 ~]$ dbca -silent -createDatabase -templateName New_Database.dbt -gdbName NERV01 -sysPassword Nerv2012 -systemPassword Nerv2012 -characterset WE8ISO8859P1 -nationalCharacterSet AL16UTF16 -emConfiguration NONE -databaseType OLTP
[oracle@OracleDG02 ~]$ cat database/response/db_install.rsp #################################################################### ## Copyright(c) Oracle Corporation 1998,2008. All rights reserved.## ## ## ## Specify values for the variables listed below to customize ## ## your installation. ## ## ## ## Each variable is associated with a comment. The comment ## ## can help to populate the variables with the appropriate ## ## values. ## ## ## ## IMPORTANT NOTE: This file contains plain text passwords and ## ## should be secured to have read permission only by oracle user ## ## or db administrator who owns this installation. ## ## ## #################################################################### #------------------------------------------------------------------------------ # Do not change the following system generated value. #------------------------------------------------------------------------------ oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0 #------------------------------------------------------------------------------ # Specify the installation option. # It can be one of the following: # 1. INSTALL_DB_SWONLY # 2. INSTALL_DB_AND_CONFIG # 3. UPGRADE_DB #------------------------------------------------------------------------------- oracle.install.option=INSTALL_DB_SWONLY #------------------------------------------------------------------------------- # Specify the hostname of the system as set during the install. It can be used # to force the installation to use an alternative hostname rather than using the # first hostname found on the system. (e.g., for systems with multiple hostnames # and network interfaces) #------------------------------------------------------------------------------- ORACLE_HOSTNAME= #------------------------------------------------------------------------------- # Specify the Unix group to be set for the inventory directory. #------------------------------------------------------------------------------- UNIX_GROUP_NAME=oinstall #------------------------------------------------------------------------------- # Specify the location which holds the inventory files. #------------------------------------------------------------------------------- INVENTORY_LOCATION=/u01/app/oraInventory #------------------------------------------------------------------------------- # Specify the languages in which the components will be installed. # # en : English ja : Japanese # fr : French ko : Korean # ar : Arabic es : Latin American Spanish # bn : Bengali lv : Latvian # pt_BR: Brazilian Portuguese lt : Lithuanian # bg : Bulgarian ms : Malay # fr_CA: Canadian French es_MX: Mexican Spanish # ca : Catalan no : Norwegian # hr : Croatian pl : Polish # cs : Czech pt : Portuguese # da : Danish ro : Romanian # nl : Dutch ru : Russian # ar_EG: Egyptian zh_CN: Simplified Chinese # en_GB: English (Great Britain) sk : Slovak # et : Estonian sl : Slovenian # fi : Finnish es_ES: Spanish # de : German sv : Swedish # el : Greek th : Thai # iw : Hebrew zh_TW: Traditional Chinese # hu : Hungarian tr : Turkish # is : Icelandic uk : Ukrainian # in : Indonesian vi : Vietnamese # it : Italian # # Example : SELECTED_LANGUAGES=en,fr,ja #------------------------------------------------------------------------------ SELECTED_LANGUAGES= #------------------------------------------------------------------------------ # Specify the complete path of the Oracle Home. #------------------------------------------------------------------------------ ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 #------------------------------------------------------------------------------ # Specify the complete path of the Oracle Base. #------------------------------------------------------------------------------ ORACLE_BASE=/u01/app/oracle #------------------------------------------------------------------------------ # Specify the installation edition of the component. # # The value should contain only one of these choices. # EE : Enterprise Edition # SE : Standard Edition # SEONE : Standard Edition One # PE : Personal Edition (WINDOWS ONLY) #------------------------------------------------------------------------------ oracle.install.db.InstallEdition=EE #------------------------------------------------------------------------------ # This variable is used to enable or disable custom install. # # true : Components mentioned as part of 'customComponents' property # are considered for install. # false : Value for 'customComponents' is not considered. #------------------------------------------------------------------------------ oracle.install.db.isCustomInstall=false #------------------------------------------------------------------------------ # This variable is considered only if 'IsCustomInstall' is set to true. # # Description: List of Enterprise Edition Options you would like to install. # # The following choices are available. You may specify any # combination of these choices. The components you choose should # be specified in the form "internal-component-name:version" # Below is a list of components you may specify to install. # # oracle.rdbms.partitioning:11.2.0.1.0 - Oracle Partitioning # oracle.rdbms.dm:11.2.0.1.0 - Oracle Data Mining # oracle.rdbms.dv:11.2.0.1.0 - Oracle Database Vault # oracle.rdbms.lbac:11.2.0.1.0 - Oracle Label Security # oracle.rdbms.rat:11.2.0.1.0 - Oracle Real Application Testing # oracle.oraolap:11.2.0.1.0 - Oracle OLAP #------------------------------------------------------------------------------ oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0 ############################################################################### # # # PRIVILEGED OPERATING SYSTEM GROUPS # # ------------------------------------------ # # Provide values for the OS groups to which OSDBA and OSOPER privileges # # needs to be granted. If the install is being performed as a member of the # # group "dba", then that will be used unless specified otherwise below. # # # ############################################################################### #------------------------------------------------------------------------------ # The DBA_GROUP is the OS group which is to be granted OSDBA privileges. #------------------------------------------------------------------------------ oracle.install.db.DBA_GROUP=dba #------------------------------------------------------------------------------ # The OPER_GROUP is the OS group which is to be granted OSOPER privileges. #------------------------------------------------------------------------------ oracle.install.db.OPER_GROUP=oper #------------------------------------------------------------------------------ # Specify the cluster node names selected during the installation. #------------------------------------------------------------------------------ oracle.install.db.CLUSTER_NODES= #------------------------------------------------------------------------------ # Specify the type of database to create. # It can be one of the following: # - GENERAL_PURPOSE/TRANSACTION_PROCESSING # - DATA_WAREHOUSE #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.type= #------------------------------------------------------------------------------ # Specify the Starter Database Global Database Name. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.globalDBName= #------------------------------------------------------------------------------ # Specify the Starter Database SID. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.SID= #------------------------------------------------------------------------------ # Specify the Starter Database character set. # # It can be one of the following: # AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2, # EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257, # BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6, # AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8, # IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE, # KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950, # ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258 #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.characterSet=AL32UTF8 #------------------------------------------------------------------------------ # This variable should be set to true if Automatic Memory Management # in Database is desired. # If Automatic Memory Management is not desired, and memory allocation # is to be done manually, then set it to false. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.memoryOption=true #------------------------------------------------------------------------------ # Specify the total memory allocation for the database. Value(in MB) should be # at least 256 MB, and should not exceed the total physical memory available # on the system. # Example: oracle.install.db.config.starterdb.memoryLimit=512 #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.memoryLimit= #------------------------------------------------------------------------------ # This variable controls whether to load Example Schemas onto the starter # database or not. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.installExampleSchemas=false #------------------------------------------------------------------------------ # This variable includes enabling audit settings, configuring password profiles # and revoking some grants to public. These settings are provided by default. # These settings may also be disabled. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.enableSecuritySettings=true ############################################################################### # # # Passwords can be supplied for the following four schemas in the # # starter database: # # SYS # # SYSTEM # # SYSMAN (used by Enterprise Manager) # # DBSNMP (used by Enterprise Manager) # # # # Same password can be used for all accounts (not recommended) # # or different passwords for each account can be provided (recommended) # # # ############################################################################### #------------------------------------------------------------------------------ # This variable holds the password that is to be used for all schemas in the # starter database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.password.ALL= #------------------------------------------------------------------------------- # Specify the SYS password for the starter database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.password.SYS= #------------------------------------------------------------------------------- # Specify the SYSTEM password for the starter database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.password.SYSTEM= #------------------------------------------------------------------------------- # Specify the SYSMAN password for the starter database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.password.SYSMAN= #------------------------------------------------------------------------------- # Specify the DBSNMP password for the starter database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.password.DBSNMP= #------------------------------------------------------------------------------- # Specify the management option to be selected for the starter database. # It can be one of the following: # 1. GRID_CONTROL # 2. DB_CONTROL #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.control=DB_CONTROL #------------------------------------------------------------------------------- # Specify the Management Service to use if Grid Control is selected to manage # the database. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL= #------------------------------------------------------------------------------- # This variable indicates whether to receive email notification for critical # alerts when using DB control. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false #------------------------------------------------------------------------------- # Specify the email address to which the notifications are to be sent. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.dbcontrol.emailAddress= #------------------------------------------------------------------------------- # Specify the SMTP server used for email notifications. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.dbcontrol.SMTPServer= ############################################################################### # # # SPECIFY BACKUP AND RECOVERY OPTIONS # # ------------------------------------ # # Out-of-box backup and recovery options for the database can be mentioned # # using the entries below. # # # ############################################################################### #------------------------------------------------------------------------------ # This variable is to be set to false if automated backup is not required. Else # this can be set to true. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.automatedBackup.enable=false #------------------------------------------------------------------------------ # Regardless of the type of storage that is chosen for backup and recovery, if # automated backups are enabled, a job will be scheduled to run daily at # 2:00 AM to backup the database. This job will run as the operating system # user that is specified in this variable. #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.automatedBackup.osuid= #------------------------------------------------------------------------------- # Regardless of the type of storage that is chosen for backup and recovery, if # automated backups are enabled, a job will be scheduled to run daily at # 2:00 AM to backup the database. This job will run as the operating system user # specified by the above entry. The following entry stores the password for the # above operating system user. #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.automatedBackup.ospwd= #------------------------------------------------------------------------------- # Specify the type of storage to use for the database. # It can be one of the following: # - FILE_SYSTEM_STORAGE # - ASM_STORAGE #------------------------------------------------------------------------------ oracle.install.db.config.starterdb.storageType= #------------------------------------------------------------------------------- # Specify the database file location which is a directory for datafiles, control # files, redo logs. # # Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.fileSystemStorage.dataLocation= #------------------------------------------------------------------------------- # Specify the backup and recovery location. # # Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM #------------------------------------------------------------------------------- oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation= #------------------------------------------------------------------------------- # Specify the existing ASM disk groups to be used for storage. # # Applicable only when oracle.install.db.config.starterdb.storage=ASM #------------------------------------------------------------------------------- oracle.install.db.config.asm.diskGroup= #------------------------------------------------------------------------------- # Specify the password for ASMSNMP user of the ASM instance. # # Applicable only when oracle.install.db.config.starterdb.storage=ASM_SYSTEM #------------------------------------------------------------------------------- oracle.install.db.config.asm.ASMSNMPPassword= #------------------------------------------------------------------------------ # Specify the My Oracle Support Account Username. # # Example : MYORACLESUPPORT_USERNAME=metalink #------------------------------------------------------------------------------ MYORACLESUPPORT_USERNAME= #------------------------------------------------------------------------------ # Specify the My Oracle Support Account Username password. # # Example : MYORACLESUPPORT_PASSWORD=password #------------------------------------------------------------------------------ MYORACLESUPPORT_PASSWORD= #------------------------------------------------------------------------------ # Specify whether to enable the user to set the password for # My Oracle Support credentials. The value can be either true or false. # If left blank it will be assumed to be false. # # Example : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true #------------------------------------------------------------------------------ SECURITY_UPDATES_VIA_MYORACLESUPPORT= #------------------------------------------------------------------------------ # Specify whether user wants to give any proxy details for connection. # The value can be either true or false. If left blank it will be assumed # to be false. # # Example : DECLINE_SECURITY_UPDATES=false #------------------------------------------------------------------------------ DECLINE_SECURITY_UPDATES=true #------------------------------------------------------------------------------ # Specify the Proxy server name. Length should be greater than zero. # # Example : PROXY_HOST=proxy.domain.com #------------------------------------------------------------------------------ PROXY_HOST= #------------------------------------------------------------------------------ # Specify the proxy port number. Should be Numeric and atleast 2 chars. # # Example : PROXY_PORT=25 #------------------------------------------------------------------------------ PROXY_PORT= #------------------------------------------------------------------------------ # Specify the proxy user name. Leave PROXY_USER and PROXY_PWD # blank if your proxy server requires no authentication. # # Example : PROXY_USER=username #------------------------------------------------------------------------------ PROXY_USER= #------------------------------------------------------------------------------ # Specify the proxy password. Leave PROXY_USER and PROXY_PWD # blank if your proxy server requires no authentication. # # Example : PROXY_PWD=password #------------------------------------------------------------------------------ PROXY_PWD= [oracle@OracleDG02 ~]$
Adoro a instalação mínima do OEL (Oracle Enterprise Linux) 6. Este tipo de instalação existia na fonte (RHEL – Red Hat Enterprise Linux) na versão 4, mas sumiu na 5, para voltar na 6. Esta instalação vem até sem unzip, por isso os pacotes adicionais aos pré-requisitos do Oracle Database 11gR2.
Atenção aos parâmetros de kernel, eles podem ser diferentes para cada máquina.
[root@OracleDG01 ~]# cat OraclePreReqs.sh mount /dev/sr0 /mnt cd /mnt/Packages yum -y localinstall yum-utils-*.el6.noarch* yum-complete-transaction yum -y localinstall unzip-*.el6.x86_64.rpm sysstat-*.el6.x86_64* elfutils-libelf-devel-*.el6.x86_64* gcc-4.4.*.el6.x86_64* libaio-devel-*-10.el6.* kernel-uek-headers-*.el6uek.x86_64* compat-libstdc++-33-*.el6.* libstdc++-devel-*.el6.x86_64* libaio-*.el6.* glibc-headers-*.el6.x86_64* gcc-c++-4.*.el6.x86_64* cloog-ppl-*.el6.x86_64* libgomp-*.el6.x86_64* glibc-devel-*.el6.* ppl-*.el6.x86_64* cpp-*.el6.x86_64* mpfr-*.el6.x86_64* make-*.el6.x86_64* libgcc-*.el6.* libstdc++-*.el6.* binutils-2.*.el6.x86_64.rpm elfutils-libelf-*.el6.x86_64.rpm glibc-2.*.el6.* glibc-common-2.*.el6.x86_64.rpm nss-softokn-freebl-*.el6.* openssh-clients-*.el6.x86_64.rpm libedit-*.el6.x86_64.rpm perl-5.*.el6_1.1.x86_64.rpm perl-Module-Pluggable-*.el6_1.1.x86_64.rpm perl-libs-*.x86_64.rpm perl-Pod-Simple-*.el6_1.1.x86_64.rpm perl-Pod-Escapes-*.el6_1.1.x86_64.rpm perl-version-*.x86_64.rpm kernel-uek-devel-*.el6uek.x86_64.rpm libXext-1.*.el6.* libX11-1.*.el6.* libX11-common-1.*.el6.noarch.rpm libxcb-1.*.el6.* libXau-1.*.el6.* yum-complete-transaction yum -y localinstall unzip-*.el6.x86_64.rpm sysstat-*.el6.x86_64* elfutils-libelf-devel-*.el6.x86_64* gcc-4.4.*.el6.x86_64* libaio-devel-*-10.el6.* kernel-uek-headers-*.el6uek.x86_64* compat-libstdc++-33-*.el6.* libstdc++-devel-*.el6.x86_64* libaio-*.el6.* glibc-headers-*.el6.x86_64* gcc-c++-4.*.el6.x86_64* cloog-ppl-*.el6.x86_64* libgomp-*.el6.x86_64* glibc-devel-*.el6.* ppl-*.el6.x86_64* cpp-*.el6.x86_64* mpfr-*.el6.x86_64* make-*.el6.x86_64* libgcc-*.el6.* libstdc++-*.el6.* binutils-2.*.el6.x86_64.rpm elfutils-libelf-*.el6.x86_64.rpm glibc-2.*.el6.* glibc-common-2.*.el6.x86_64.rpm nss-softokn-freebl-*.el6.* openssh-clients-*.el6.x86_64.rpm libedit-*.el6.x86_64.rpm perl-5.*.el6_1.1.x86_64.rpm perl-Module-Pluggable-*.el6_1.1.x86_64.rpm perl-libs-*.x86_64.rpm perl-Pod-Simple-*.el6_1.1.x86_64.rpm perl-Pod-Escapes-*.el6_1.1.x86_64.rpm perl-version-*.x86_64.rpm kernel-uek-devel-*.el6uek.x86_64.rpmcd libXext-1.*.el6.* libX11-1.*.el6.* libX11-common-1.*.el6.noarch.rpm libxcb-1.*.el6.* libXau-1.*.el6.* cd umount /mnt echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf echo "fs.file-max = 6815744" >> /etc/sysctl.conf echo "kernel.shmmni = 4096" >> /etc/sysctl.conf echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf echo "net.core.rmem_default=262144" >> /etc/sysctl.conf echo "net.core.rmem_max=4194304" >> /etc/sysctl.conf echo "net.core.wmem_default=262144" >> /etc/sysctl.conf echo "net.core.wmem_max=1048586" >> /etc/sysctl.conf echo "oracle soft nproc 2047" >> /etc/security/limits.conf echo "oracle hard nproc 16384" >> /etc/security/limits.conf echo "oracle soft nofile 1024" >> /etc/security/limits.conf echo "oracle hard nofile 65536" >> /etc/security/limits.conf chkconfig iptables off chkconfig lvm2-monitor off chkconfig auditd off chkconfig ip6tables off chkconfig restorecond off groupadd -g 1000 oinstall groupadd -g 1200 dba groupadd -g 1300 oper useradd -u 1100 -g oinstall -G dba,oper oracle mkdir -p /u01/app/oracle/product/11.2.0/db_1 mkdir -p /u01/oradata chown -R oracle:oinstall /u01/ chmod -R 775 /u01 export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=nerv01.localdomain export ORACLE_UNQNAME=ORCL export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_SID=ORCL export ORACLE_TERM=xterm export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib echo "export TMP=/tmp" >> /home/oracle/.bash_profile echo "export TMPDIR=$TMP" >> /home/oracle/.bash_profile echo "export ORACLE_HOSTNAME=nerv01.localdomain" >> /home/oracle/.bash_profile echo "export ORACLE_UNQNAME=ORCL" >> /home/oracle/.bash_profile echo "export ORACLE_BASE=/u01/app/oracle" >> /home/oracle/.bash_profile echo "export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1" >> /home/oracle/.bash_profile echo "export ORACLE_SID=ORCL" >> /home/oracle/.bash_profile echo "export ORACLE_TERM=xterm" >> /home/oracle/.bash_profile echo "export PATH=/usr/sbin:$PATH" >> /home/oracle/.bash_profile echo "export PATH=$ORACLE_HOME/bin:$PATH" >> /home/oracle/.bash_profile echo "export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib" >> /home/oracle/.bash_profile echo "export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib" >> /home/oracle/.bash_profile echo "if [ $USER = "oracle" ]; then" >> /home/oracle/.bash_profile echo "if [ $SHELL = "/bin/ksh" ]; then" >> /home/oracle/.bash_profile echo "ulimit -p 16384" >> /home/oracle/.bash_profile echo "ulimit -n 65536" >> /home/oracle/.bash_profile echo "else" >> /home/oracle/.bash_profile echo "ulimit -u 16384 -n 65536" >> /home/oracle/.bash_profile echo "fi" >> /home/oracle/.bash_profile echo "fi" >> /home/oracle/.bash_profile chown oracle:oinstall /home/oracle/.bash_profile passwd oracle
Este é o script que utilizo para monitorar a atividade de um banco de dados PostgreSQL.
O primeiro comando SELECT monitora dados globais de cada banco de dados, o segundo dos processos background do PostgreSQL, e o terceiro mostra que comandos estão sendo executados no banco que eu escolhi.
Em uma máquina Linux, executo este script após “watch -d”, para que ele fique se repetindo, como está na imagem logo abaixo.
postgres@ZabbixSolvo:~$ cat stat.sh psql zabbix -c "SELECT * FROM pg_stat_database;" echo "========================================" echo "========================================" psql zabbix -c "SELECT * FROM pg_stat_bgwriter;" echo "========================================" echo "========================================" psql zabbix -c "SELECT datname,procpid,current_query FROM pg_stat_activity;" postgres@ZabbixSolvo:~$
jan 292012
Uma pergunta que pode ser muito difícil de ser respondida em outros bancos de dados é corriqueira em DB2, como o tempo necessário para completar um ROLLBACK.
Este tipo de operação, como várias outras, é exibida no db2top com o percentual de conclusão.
A estrutura dos CONTAINERs de TABLESPACEs do DB2 faz com que seja simples remover todos de um diretório e passar para outro (mesmo do DB2 Express-C), sem nenhuma indisponibilidade no banco de dados. Este procedimento é muito útil em uma migração de storage, por exemplo, ou quando não há mais espaço livre em um diretório.
Este teste abaixo é o procedimento para TABLESPACEs DMT (DATABASE MANAGED TABLESPACE), e pode ser feito tanto com arquivos quando dispositivos brutos.
Para testar, criamos uma TABLESPACE de teste com 2 CONTAINERs no diretório /prod, e a populamos com uma tabela com dados.
db2 => CREATE TABLESPACE TABLESPACE_TESTE MANAGED BY DATABASE USING (FILE '/prod/container01' 1024 M, FILE '/prod/container02' 1024 M) DB20000I The SQL command completed successfully. db2 => CREATE TABLE TABELA_TESTE LIKE ORG IN TABLESPACE_TESTE DB20000I The SQL command completed successfully. db2 => DROP TABLE TABELA_TESTE DB20000I The SQL command completed successfully. db2 => CREATE TABLE TABLE_TESTE LIKE ORG IN TABLESPACE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM ORG DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully. db2 => INSERT INTO TABLE_TESTE SELECT * FROM TABLE_TESTE DB20000I The SQL command completed successfully.
Vamos conferir qual é o número da TABLESPACE criada e seus CONTAINERs.
db2 => LIST TABLESPACES Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Tablespace ID = 2 Name = USERSPACE1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 3 Name = IBMDB2SAMPLEREL Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 4 Name = IBMDB2SAMPLEXML Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 5 Name = SYSTOOLSPACE Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 6 Name = TABLESPACE_TESTE Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 7 Name = SYSTOOLSTMPSPACE Type = System managed space Contents = User Temporary data State = 0x0000 Detailed explanation: Normal
Agora que sabemos que o número da TABLESPACE é 6, podemos conferir seus CONTAINERs:
db2 => LIST TABLESPACE CONTAINERS FOR 6 SHOW DETAIL Tablespace Containers for Tablespace 6 Container ID = 0 Name = /prod/container01 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 1 Name = /prod/container02 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes
Agora vamos adicionar mais 2 CONTAINERs, em outro diretório (/home/db2inst1).
db2 => ALTER TABLESPACE TABLESPACE_TESTE ADD (FILE '/home/db2inst1/container03' 1024 M, FILE '/home/db2inst1/container04' 1024 M)
DB20000I The SQL command completed successfully.
Logo em seguida tentamos criar mais dois, mas recebemos o erro abaixo, que indica que um rebalanceamento de dados está em curso.
db2 => ALTER TABLESPACE TABLESPACE_TESTE ADD (FILE '/home/db2inst1/container05' 1024 M, FILE '/home/db2inst1/container06' 1024 M) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0258N Neither container operations nor a REBALANCE can be performed while a rebalance of the table space is pending or in progress. SQLSTATE=55041
Então conferimos novamente os CONTAINERs da TABLESPACE, e vemos que ela alocou o espaço de forma uniforme nos 4 CONTAINERs, tanto nos novos quanto nos velhos.
db2 => LIST TABLESPACE CONTAINERS FOR 6 SHOW DETAIL Tablespace Containers for Tablespace 6 Container ID = 0 Name = /prod/container01 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 1 Name = /prod/container02 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 2 Name = /home/db2inst1/container03 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 3 Name = /home/db2inst1/container04 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes
Conferimos pelo db2top a execução do REBALANCE, e esperamos ele terminar. Veja que o db2top indica o percentual de conclusão do REBALANCE.
Então removemos um dos CONTAINERs antigos quando ela termina.
db2 => ALTER TABLESPACE TABLESPACE_TESTE DROP (FILE '/prod/container01') DB20000I The SQL command completed successfully. db2 => ALTER TABLESPACE TABLESPACE_TESTE DROP (FILE '/prod/container02') DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0258N Neither container operations nor a REBALANCE can be performed while a rebalance of the table space is pending or in progress. SQLSTATE=55041
Como vimos, a execução do REBALANCE reiniciou quando removemos o primeiro CONTAINER, e temos que esperar ela terminar novamente, acompanhando pelo db2top. Se tratar-se de uma remoção de muitos CONTAINERS, todos podem ser removidos de uma só vez pelo comando DROP para evitar esta situação.
Executamos a remoção e conferimos os CONTAINERs.
db2 => ALTER TABLESPACE TABLESPACE_TESTE DROP (FILE '/prod/container02') DB20000I The SQL command completed successfully. db2 => LIST TABLESPACE CONTAINERS FOR 6 SHOW DETAIL Tablespace Containers for Tablespace 6 Container ID = 0 Name = /prod/container02 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 1 Name = /home/db2inst1/container03 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 2 Name = /home/db2inst1/container04 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes
Então esperamos o REBALANCE terminar, e verificamos novamente os CONTAINERs, para constatar que os que estavam no diretório /prod já não existem mais, e nossa migração foi concluída sem nenhuma indisponibilidade.
db2 => LIST TABLESPACE CONTAINERS FOR 6 SHOW DETAIL Tablespace Containers for Tablespace 6 Container ID = 0 Name = /home/db2inst1/container03 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes Container ID = 1 Name = /home/db2inst1/container04 Type = File Total pages = 131072 Useable pages = 131040 Accessible = Yes
jan 292012
Em uma TABLESPACE do DB2, após a remoção de um objeto, o espaço que antes era utilizado por este fica como livre para uso, mas os arquivos de dados não são reduzidos.
Para reduzir o espaço utilizado em disco, utilize a cláusula REDUCE MAX do comando ALTER TABLESPACE.
db2 => LIST TABLESPACES SHOW DETAIL
...
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1933312
Useable pages = 1933280
Used pages = 1929888
Free pages = 3392
High water mark (pages) = 1929888
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
...
db2 => DROP TABLE TESTE
DB20000I The SQL command completed successfully.
db2 => LIST TABLESPACES SHOW DETAIL
...
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1933312
Useable pages = 1933280
Used pages = 608
Free pages = 1932672
High water mark (pages) = 608
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
...
db2 => ALTER TABLESPACE IBMDB2SAMPLEREL REDUCE MAX
DB20000I The SQL command completed successfully.
db2 => LIST TABLESPACES SHOW DETAIL
...
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x80000
Detailed explanation:
Total pages = 640
Useable pages = 608
Used pages = 608
Free pages = 0
High water mark (pages) = 608
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
...
jan 262012
Com o primeiro comando abaixo, eu altero todos os containers da tablespace TESTE para 256 MB cada.
Com o segundo, defino que os containers devem se autoincrementar em 10 MB de cada vez, até um tamanho máximo de 1024 MB.
db2 => ALTER TABLESPACE TESTE RESIZE (ALL 256 M) DB20000I The SQL command completed successfully. db2 => ALTER TABLESPACE TESTE AUTORESIZE YES INCREASESIZE 10 M MAXSIZE 1024 M DB20000I The SQL command completed successfully. db2 =>
E agora confiro que a tablespace tem 130944 páginas livres (de 8k cada) de um total de 131072.
db2 => LIST TABLESPACES SHOW DETAIL
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 16384
Useable pages = 16380
Used pages = 12808
Free pages = 3572
High water mark (pages) = 12808
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4064
Used pages = 1824
Free pages = 2240
High water mark (pages) = 1824
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1933312
Useable pages = 1933280
Used pages = 1929888
Free pages = 3392
High water mark (pages) = 1929888
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 4
Name = IBMDB2SAMPLEXML
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4064
Used pages = 1440
Free pages = 2624
High water mark (pages) = 1440
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 5
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4092
Used pages = 112
Free pages = 3980
High water mark (pages) = 112
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 6
Name = TESTE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 131072
Useable pages = 130944
Used pages = 96
Free pages = 130848
High water mark (pages) = 96
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 4
db2 =>
O programa db2relocatedb também pode ser utilizado para trocar o nome do banco de dados.
Este procedimento geralmente é necessário para a criação de um banco de deenvolvimento ou homologação, baseado em uma restauração do banco de produção.
Para alterar o nome do banco, basta indicar no parâmetro DB_NAME do arquivo relocate.cfg o antigo e o novo nome do banco.
[db2inst1@nerv00 ~]$ vi relocate.cfg
[db2inst1@nerv00 ~]$ cat relocate.cfg
DB_NAME=SAMPLE,PRD
DB_PATH=/home/db2inst1
INSTANCE=db2inst1
NODENUM=0
[db2inst1@nerv00 ~]$ db2stop force
SQL1064N DB2STOP processing was successful.
[db2inst1@nerv00 ~]$ db2relocatedb -f relocate.cfg
Files and control structures were changed successfully.
Database was catalogued successfully.
DBT1000I The tool completed successfully.
[db2inst1@nerv00 ~]$ db2start
SQL1063N DB2START processing was successful.
[db2inst1@nerv00 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.5
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => CONNECT TO SAMPLE
SQL30061N The database alias or database name "SAMPLE " was not
found at the remote node. SQLSTATE=08004
db2 => CONNECT TO PRD
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = PRD
db2 => QUIT
DB20000I The QUIT command completed successfully.
[db2inst1@nerv00 ~]$
Uma atividade de administração que não é incomum em bancos de dados é a readequação de arquivos de dados (chamados de CONTAINERS em DB2) em novos diretórios ou sistemas de arquivos.
No DB2, isto pode ser feito com o comando db2relocatedb.
No DB2, isto pode ser feito com o comando db2relocatedb.
Com os comandos abaixo, vamos escolher uma tablespace, para realocar seus containers:
db2 => LIST TABLESPACES
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = IBMDB2SAMPLEXML
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 5
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 6
Name = TESTE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
db2 => LIST TABLESPACE CONTAINERS FOR 6
Tablespace Containers for Tablespace 6
Container ID = 0
Name = /prod/container01
Type = File
Container ID = 1
Name = /prod/container0S
Type = File
Container ID = 2
Name = /prod/container03
Type = File
Container ID = 3
Name = /prod/container04
Type = File
db2 => QUIT
DB20000I The QUIT command completed successfully.
Em seguida, paramos a instância completamente e copiamos manualmente os containers:
[db2inst1@nerv00 ~]$ db2stop force SQL1064N DB2STOP processing was successful. [db2inst1@nerv00 ~]$ cp /prod/container01 /home/db2inst1/prod/container01 [db2inst1@nerv00 ~]$ cp /prod/container0S /home/db2inst1/prod/container0S [db2inst1@nerv00 ~]$ cp /prod/container03 /home/db2inst1/prod/container03 [db2inst1@nerv00 ~]$ cp /prod/container04 /home/db2inst1/prod/container04
Depois criamos um arquivo para ser utilizado pelo comando db2relocatedb. Neste arquivo, através da variável CONT_PATH, colocamos a tradução dos containers, no formato CaminhoVelho/CaminhoNovo.
[db2inst1@nerv00 ~]$ cat relocate.cfg DB_NAME=SAMPLE DB_PATH=/home/db2inst1 INSTANCE=db2inst1 NODENUM=0 CONT_PATH=/prod/*,/home/db2inst1/prod/*
Agora executamos o comando, para informar ao DB2 que trocamos os contaienrs de lugar.
[db2inst1@nerv00 ~]$ db2relocatedb -f relocate.cfg Files and control structures were changed successfully. DBT1000I The tool completed successfully.
Em seguida podemos iniciar a instância e conferir o resultado.
[db2inst1@nerv00 ~]$ db2start SQL1063N DB2START processing was successful. [db2inst1@nerv00 ~]$ LIST TABLESPACE CONTAINERS FOR 6 -bash: LIST: command not found [db2inst1@nerv00 ~]$ db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 9.7.5You can issue database manager commands and SQL statements from the command prompt. For example: db2 => connect to sample db2 => bind sample.bndFor general help, type: ?. For command help, type: ? command, where command can be the first few keywords of a database manager command. For example: ? CATALOG DATABASE for help on the CATALOG DATABASE command ? CATALOG for help on all of the CATALOG commands.To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2'. To list the current command option settings, type LIST COMMAND OPTIONS.For more detailed help, refer to the Online Reference Manual.db2 => CONNECT TO SAMPLEDatabase Connection InformationDatabase server = DB2/LINUXX8664 9.7.5 SQL authorization ID = DB2INST1 Local database alias = SAMPLEdb2 => LIST TABLESPACE CONTAINERS FOR 6Tablespace Containers for Tablespace 6Container ID = 0 Name = /home/db2inst1/prod/container01 Type = FileContainer ID = 1 Name = /home/db2inst1/prod/container0S Type = FileContainer ID = 2 Name = /home/db2inst1/prod/container03 Type = FileContainer ID = 3 Name = /home/db2inst1/prod/container04 Type = Filedb2 =>