安裝完Oracle後，接著切換回command介面，切換oracle的身分 su - oracle
修改~/.bash_profile參數
PATH=$PATH:$HOME/bin
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=[dbname]
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib
export ORA_CRS_HOME=$ORACLE_HOME/crs
export LIBPATH=$LIBPATH:$ORA_CRS_HOME/lib:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:${PATH}:$HOME/bin
export EDITOR=vim
export NLS_LANG="american_america.UTF8";
#big5 american_america.ZHT16BIG5
請記得[dbname]不可以超過八個字元，超過的話後面create database會失敗
建立oracle的參數設定檔，cd $ORACLE_HOME/dbs，複製init.ora成init[dbname].ora
記得檔名一定要依照這個格式，要不然一樣啟動的時候會出錯
裡面的設定如下
compatible = 11.2.0
control_files = '$ORACLE_BASE/oradata/control01.ora','/$ORACLE_BASE/oradata/control02.ora'
db_block_size=8192
db_name="[dbname]"
db_domain=[domain]
instance_name="[dbname]"
service_names="[dbname]"
diagnostic_dest=$ORACLE_BASE/diag
DB_RECOVERY_FILE_DEST=$ORACLE_BASE/flash_recovery_area
DB_RECOVERY_FILE_DEST_SIZE=2G
log_buffer = 262144
PGA_AGGREGATE_TARGET=300M
workarea_size_policy=auto
SGA_TARGET=500M
processes = 300
remote_login_passwordfile=exclusive
undo_management = auto
undo_tablespace = undotbs
#------------------------建密碼擋
orapwd file=$ORACLE_HOME/dbs/orapwd11g password=dbpasswd entries=10
#------------------------以下是開啟archivelog的設定
log_archive_dest_1 = "LOCATION=/app/oracle/oradata/ts/ARCHIVE1/MANDATORY"
log_archive_dest_2 = "LOCATION=/app/oracle/oradata/ts/ARCHIVE2/MANDATORY"
log_archive_format = "arch%t_%s_%r.arc"
log_archive_max_processes=2
#-------------------------資料庫優化
max_dispatchers=20
max_shared_servers=20
dispatchers= "(PROTOCOL=TCP)(dispatchers=12)"
shared_servers=8
local_listener=[dbname] # /etc/hosts #??
open_cursors = 200
audit_sys_operations = false #關閉audit log，因為預設是把LOG存在system space中 ?????
檢查一下是不是所有的路徑是否都存在，接著我們建立一個ramdisk作為sort暫存用
mkdir /mnt/ramdisk
mount -t tmpfs -o size=4G tmpfs /mnt/ramdisk
預設開機掛載 vim /etc/fstab
在最後面增加一行
tmpfs /mnt/ramdisk tmpfs rw,relatime,size=4G 0 0
同時安裝一個模組讓sqlplus可以支援上下鍵
rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
yum -y install rlwrap readline readline-devel
在~/.bash_profile最底下加上
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
這樣等下操作會方便很多，接著用oracle的身分建立一個目錄等下可以放log、table space以及archivelog
mkdir /app/oracle/oradata/log
mkdir /app/oracle/oradata/ts
mkdir /app/oracle/oradata/ts/ARCHIVE1
mkdir /app/oracle/oradata/ts/ARCHIVE2
mkdir $ORACLE_BASE/flash_recovery_area
就可以使用sqlplus與DB進行連線以及建立資料庫了
sqlplus /nolog
SQL> connect /as sysdba
SQL> startup nomount
SQL> CREATE DATABASE [dbname]
USER SYS IDENTIFIED BY [passwd] (sys 系統使用者密碼)
USER SYSTEM IDENTIFIED BY [passwd] (system 系統使用者密碼)
LOGFILE GROUP 1 ('/app/oracle/oradata/log/redo01a.log','/app/oracle/oradata/log/redo01b.log') SIZE 100M,
GROUP 2 ('/app/oracle/oradata/log/redo02a.log','/app/oracle/oradata/log/redo02b.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
EXTENT MANAGEMENT LOCAL
datafile '/app/oracle/oradata/ts/sys.dbf' size 512M (系統檔)
sysaux datafile '/app/oracle/oradata/ts/sysaux.dbf' size 512M
DEFAULT TABLESPACE users (使用者的table space)
DATAFILE '/app/oracle/oradata/ts/users01.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 (sorting時的table space)
TEMPFILE '/mnt/ramdisk/temp01.dbf' #放ramdisk搜尋會比較快
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs (commit前的table space)
DATAFILE '/app/oracle/oradata/ts/undotbs.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
SET TIME_ZONE ='+08:00';
接著建立data用的table space，記得開存資料用的資料表時
一定要指定放到data的table space，要不然預設會放在system的table space
當system的table space爆掉的時候，就會發生很嚴重的後果
CREATE TABLESPACE [dataspace] DATAFILE '/app/oracle/oradata/ts/[dataname]01.dbf' SIZE 5120M
AUTOEXTEND ON
NEXT 8192K MINIMUM EXTENT 512K
DEFAULT STORAGE ( INITIAL 8192K NEXT 8192K MINEXTENTS 2 MAXEXTENTS 15000
PCTINCREASE 0);
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]02.dbf' SIZE 2048M;
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]03.dbf' SIZE 2048M;
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]04.dbf' SIZE 2048M;
如果資料量會越來越大的話記得開啟autoextend on
到這邊如果出現問題，要重新建立資料庫的話，就到/app/oracle/oradata資料夾內
將所建立的檔案全數刪除，如果刪不乾淨建立的時候就會出錯
接著建立index的table space
CREATE TABLESPACE DNSINDEX DATAFILE '/app/oracle/oradata/ts/index01.dbf' SIZE 1024M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 8192 PCTINCREASE 0);
接下來作不知道幹嘛用的(data dictionaries)，記得不要跳出sqlplus哦
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
開啟archive log功能
shutdown immediate;
startup mount;
alter database archivelog;
shutdown immediate;關DB
startup; 開DB後面有三個參數 nomount(開DB) mount(開系統檔案) open(正常開啟)
檢查交易LOG功能是否正常運作
SQL> archive log list;
Database log mode Archive Mode <---正常啟用中
Automatic archival Enabled
Archive destination /app/oracle/oradata/ts/ARCHIVE2/
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
修改資料庫listen的設定$ORACLE_HOME/network/admin/listener.ora
現在DB只有本機可以連線，要修改$ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=[dbname])(PORT=1521)(QUEUESIZE=150))
(ADDRESS = (PROTOCOL=TCP)(HOST=[ip])(PORT=1521)(QUEUESIZE=30))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [dbname])
(SID_NAME = [dbname])
)
)
TRACE_LEVEL_LISTENER =OFF
LOG_FILE_LISTENER = lsnr
接著開啟oracle listener
lsnrctl start
最後建立一般使用者
create user [username] identified by [password] default tablespace [dataspace] temporary tablespace TEMPTS1 quota unlimited on [dataspace];
#建議放在users的table space，不過選錯了其實也沒啥關係
grant create session,create synonym,select any table, execute any type, select any sequence to [username];
grant dba to [username];
最後讓oracle能夠開機啟動
vim /etc/rc.local
su - oracle -c 'lsnrctl start' →以Oracle_SID的這個身分下指令把listener啟動(-c就是command)
su - oracle -c 'dbstart' →以Oracle_SID的這個身分啟動DB
最後在到/etc/rc.local去執行這個檔案即可
這樣就完成整個oracle的架設以及create一個DB與user
但是重開機後還是要手動進入啟動資料庫
sqlplus /nolog
connect /as sysdba
startup
=============================================
使用system帳號匯入資料
imp userid=system/passwd log=/tmp/imp.log full=y grants=y indexes=n file=xxx.dmp #full匯入
imp userid=system/passwd log=/tmp/imp.log fromuser=dbuser touser=dbuser grants=y indexes=n file=xxx.dmp #只匯入特定使用者
理論上如果是full備份，所有使用者、帳號、密碼都會還原回資料庫
還原回去後要修改帳號密碼
ALTER USER 使用者名稱 IDENTIFIED BY 密碼;
修改~/.bash_profile參數
PATH=$PATH:$HOME/bin
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=[dbname]
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib
export ORA_CRS_HOME=$ORACLE_HOME/crs
export LIBPATH=$LIBPATH:$ORA_CRS_HOME/lib:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:${PATH}:$HOME/bin
export EDITOR=vim
export NLS_LANG="american_america.UTF8";
#big5 american_america.ZHT16BIG5
請記得[dbname]不可以超過八個字元，超過的話後面create database會失敗
建立oracle的參數設定檔，cd $ORACLE_HOME/dbs，複製init.ora成init[dbname].ora
記得檔名一定要依照這個格式，要不然一樣啟動的時候會出錯
裡面的設定如下
compatible = 11.2.0
control_files = '$ORACLE_BASE/oradata/control01.ora','/$ORACLE_BASE/oradata/control02.ora'
db_block_size=8192
db_name="[dbname]"
db_domain=[domain]
instance_name="[dbname]"
service_names="[dbname]"
diagnostic_dest=$ORACLE_BASE/diag
DB_RECOVERY_FILE_DEST=$ORACLE_BASE/flash_recovery_area
DB_RECOVERY_FILE_DEST_SIZE=2G
log_buffer = 262144
PGA_AGGREGATE_TARGET=300M
workarea_size_policy=auto
SGA_TARGET=500M
processes = 300
remote_login_passwordfile=exclusive
undo_management = auto
undo_tablespace = undotbs
#------------------------建密碼擋
orapwd file=$ORACLE_HOME/dbs/orapwd11g password=dbpasswd entries=10
#------------------------以下是開啟archivelog的設定
log_archive_dest_1 = "LOCATION=/app/oracle/oradata/ts/ARCHIVE1/MANDATORY"
log_archive_dest_2 = "LOCATION=/app/oracle/oradata/ts/ARCHIVE2/MANDATORY"
log_archive_format = "arch%t_%s_%r.arc"
log_archive_max_processes=2
#-------------------------資料庫優化
max_dispatchers=20
max_shared_servers=20
dispatchers= "(PROTOCOL=TCP)(dispatchers=12)"
shared_servers=8
local_listener=[dbname] # /etc/hosts #??
open_cursors = 200
audit_sys_operations = false #關閉audit log，因為預設是把LOG存在system space中 ?????
檢查一下是不是所有的路徑是否都存在，接著我們建立一個ramdisk作為sort暫存用
mkdir /mnt/ramdisk
mount -t tmpfs -o size=4G tmpfs /mnt/ramdisk
預設開機掛載 vim /etc/fstab
在最後面增加一行
tmpfs /mnt/ramdisk tmpfs rw,relatime,size=4G 0 0
同時安裝一個模組讓sqlplus可以支援上下鍵
rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
yum -y install rlwrap readline readline-devel
在~/.bash_profile最底下加上
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
這樣等下操作會方便很多，接著用oracle的身分建立一個目錄等下可以放log、table space以及archivelog
mkdir /app/oracle/oradata/log
mkdir /app/oracle/oradata/ts
mkdir /app/oracle/oradata/ts/ARCHIVE1
mkdir /app/oracle/oradata/ts/ARCHIVE2
mkdir $ORACLE_BASE/flash_recovery_area
就可以使用sqlplus與DB進行連線以及建立資料庫了
sqlplus /nolog
SQL> connect /as sysdba
SQL> startup nomount
SQL> CREATE DATABASE [dbname]
USER SYS IDENTIFIED BY [passwd] (sys 系統使用者密碼)
USER SYSTEM IDENTIFIED BY [passwd] (system 系統使用者密碼)
LOGFILE GROUP 1 ('/app/oracle/oradata/log/redo01a.log','/app/oracle/oradata/log/redo01b.log') SIZE 100M,
GROUP 2 ('/app/oracle/oradata/log/redo02a.log','/app/oracle/oradata/log/redo02b.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
EXTENT MANAGEMENT LOCAL
datafile '/app/oracle/oradata/ts/sys.dbf' size 512M (系統檔)
sysaux datafile '/app/oracle/oradata/ts/sysaux.dbf' size 512M
DEFAULT TABLESPACE users (使用者的table space)
DATAFILE '/app/oracle/oradata/ts/users01.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 (sorting時的table space)
TEMPFILE '/mnt/ramdisk/temp01.dbf' #放ramdisk搜尋會比較快
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs (commit前的table space)
DATAFILE '/app/oracle/oradata/ts/undotbs.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
SET TIME_ZONE ='+08:00';
接著建立data用的table space，記得開存資料用的資料表時
一定要指定放到data的table space，要不然預設會放在system的table space
當system的table space爆掉的時候，就會發生很嚴重的後果
CREATE TABLESPACE [dataspace] DATAFILE '/app/oracle/oradata/ts/[dataname]01.dbf' SIZE 5120M
AUTOEXTEND ON
NEXT 8192K MINIMUM EXTENT 512K
DEFAULT STORAGE ( INITIAL 8192K NEXT 8192K MINEXTENTS 2 MAXEXTENTS 15000
PCTINCREASE 0);
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]02.dbf' SIZE 2048M;
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]03.dbf' SIZE 2048M;
Alter tablespace [dataspace]ADD DATAFILE '/app/oracle/oradata/ts/[dataspace]04.dbf' SIZE 2048M;
如果資料量會越來越大的話記得開啟autoextend on
到這邊如果出現問題，要重新建立資料庫的話，就到/app/oracle/oradata資料夾內
將所建立的檔案全數刪除，如果刪不乾淨建立的時候就會出錯
接著建立index的table space
CREATE TABLESPACE DNSINDEX DATAFILE '/app/oracle/oradata/ts/index01.dbf' SIZE 1024M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 8192 PCTINCREASE 0);
接下來作不知道幹嘛用的(data dictionaries)，記得不要跳出sqlplus哦
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
開啟archive log功能
shutdown immediate;
startup mount;
alter database archivelog;
shutdown immediate;關DB
startup; 開DB後面有三個參數 nomount(開DB) mount(開系統檔案) open(正常開啟)
檢查交易LOG功能是否正常運作
SQL> archive log list;
Database log mode Archive Mode <---正常啟用中
Automatic archival Enabled
Archive destination /app/oracle/oradata/ts/ARCHIVE2/
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
修改資料庫listen的設定$ORACLE_HOME/network/admin/listener.ora
現在DB只有本機可以連線，要修改$ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=[dbname])(PORT=1521)(QUEUESIZE=150))
(ADDRESS = (PROTOCOL=TCP)(HOST=[ip])(PORT=1521)(QUEUESIZE=30))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [dbname])
(SID_NAME = [dbname])
)
)
TRACE_LEVEL_LISTENER =OFF
LOG_FILE_LISTENER = lsnr
接著開啟oracle listener
lsnrctl start
最後建立一般使用者
create user [username] identified by [password] default tablespace [dataspace] temporary tablespace TEMPTS1 quota unlimited on [dataspace];
#建議放在users的table space，不過選錯了其實也沒啥關係
grant create session,create synonym,select any table, execute any type, select any sequence to [username];
grant dba to [username];
最後讓oracle能夠開機啟動
vim /etc/rc.local
su - oracle -c 'lsnrctl start' →以Oracle_SID的這個身分下指令把listener啟動(-c就是command)
su - oracle -c 'dbstart' →以Oracle_SID的這個身分啟動DB
最後在到/etc/rc.local去執行這個檔案即可
這樣就完成整個oracle的架設以及create一個DB與user
但是重開機後還是要手動進入啟動資料庫
sqlplus /nolog
connect /as sysdba
startup
=============================================
使用system帳號匯入資料
imp userid=system/passwd log=/tmp/imp.log full=y grants=y indexes=n file=xxx.dmp #full匯入
imp userid=system/passwd log=/tmp/imp.log fromuser=dbuser touser=dbuser grants=y indexes=n file=xxx.dmp #只匯入特定使用者
理論上如果是full備份，所有使用者、帳號、密碼都會還原回資料庫
還原回去後要修改帳號密碼
ALTER USER 使用者名稱 IDENTIFIED BY 密碼;