准备环境:
1、系统需求
硬件环境:
内存 ≥1G
SWAP<2G:物理内存的1~2倍
2G~16G
与物理内存相同
DISK
8G
TMP
≥1G
软件环境
软件包
yum install elfutils-libelf-devel gcc gcc-c++ libaio-devel sysstat unixODBC unixODBC-devel compat-libstdc++-33-3.2.3 -y
用户环境
groupadd oinstall&&groupadd dba&&useradd -g oinstall -G dba oracle
passwd oracle
mkdir /opt/oracle&&chown -R oracle:oinstall /opt/&&chmod -R 775 /opt
[root@dbserver ~]# vim ~oracle/.bash_profile
……
ORACLE_BASE=/opt
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
ORACLE_TERM=xterm
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_TERM ORACLE_SID PATH
内核要求
vim /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
会话要求
vi /etc/pam.d/login
session required pam_limits.so
vi /etc/security/limits.conf
oracle soft nproc 8192
oracle hard nproc 16384
oracle soft nofile 32768
oracle hard nofile 65536
重启谨记以新建的oracle用户登录
安装即可
oracle配置
启动、停止Oracle数据库
vim .bash_profile
ORACLE_BASE=/opt
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
ORACLE_TERM=xterm
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_TERM ORACLE_SID PATH
vim /etc/oratab
orcl:/opt/oracle:Y
运行dbstart时启动数据库实例
lsnrctl status
lsnrctl start
lsnrctl stop
emctl start/stop/status dbconsole
dbstart/dbshut
vim /opt/oracle/bin/dbstart
ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTNER=$ORACLE_HOME
vim /opt/oracle/bin/dbshut
ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTNER=$ORACLE_HOME
控制脚本
#!/bin/bash
# chkconfig: 35 88 99
# description: Oracle Database Service Daemon.
ORACLE_BASE="/opt"
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
case "$1" in
start)
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" #//启用监听器
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" #//启用数据库
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole" #//启用OEM
;;
stop)
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole" #//停止OEM
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" #//停止数据库
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" #//停止监听器
;;
status)
if(pgrep "tnslsnr" && netstat -anpt | grep ":1521") &> /dev/null
then
echo "Oracle 11g Net Listener is running."
else
echo "Oracle 11g Net Listener is not running."
fi
if(netstat -anpt | grep ":1158" && netstat -anpt | grep ":5520") &> /dev/null
then
echo "Oracle 11g Enterprise Manager is running."
else
echo "Oracle 11g Enterprise Manager is not running."
fi
;;
restart)
$0 stop
$0 start
;;
*)
echo "Usage: $0 {start|stop|restart|status}"
exit 1
;;
esac
exit 0
历史命令
yum install readline readline-devel -y
tar zxvf rlwrap-0.37.tar.gz
./configure&&make&&make install
vim /home/oracle/.bashrc
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
登录及退出
sqlplus /nolog
conn /as sysdba
exit
启动、关闭数据库
startup
shutdown immediate;
用户操作
create user wcg identified by "123456";
grant connect,resource to wcg;
select * from session_privs;
select * from all_users where username='WCG'
alter user wcg identified by 654321;
drop user wcg cascade;
sysdba
select username,password from dba_users;
sys
alter user sys identified by manager;
system
alter user system identified by manager;
创建/删除
netca
dbca
create table stu01(name char(16) not null primary key,password char(16) not null );
select table_name from all_tables where table_name='STU01';
insert into stu01 values('wcg','123456');
desc stu01
drop table stu01;
显示结构
select * from v$instance;
select table_name from all_tables;
desc table-name
数据记录的插入与维护
insert into stu01 values('wcg','123456');
DELETE FROM STU01 WHERE NAME='WCG';
UPDATE STU01 SET PASSWORD='000000' WHERE NAME='LY';
SELECT * FROM STU01;
维护数据库
环境准备
mkdir /opt/bakup
chown -R oracle:oinstall /opt/bakup
CREATE DIRECTORY dmpdir AS '/opt/bakup';
GRANT all ON DIRECTORY dmpdir TO wcg;
备份
expdp wcg/123456 DIRECTORY=dmpdir DUMPFILE=wcg-20121010.dmp
还原
impdp wcg/123456 DIRECTORY=dmpdir DUMPFILE=wcg-20121010.dmp REUSE_DATAFILES=Y TABLE_EXISTS_ACTION=REPLACE
1、系统需求
硬件环境:
内存 ≥1G
SWAP<2G:物理内存的1~2倍
2G~16G
与物理内存相同
DISK
8G
TMP
≥1G
软件环境
软件包
yum install elfutils-libelf-devel gcc gcc-c++ libaio-devel sysstat unixODBC unixODBC-devel compat-libstdc++-33-3.2.3 -y
用户环境
groupadd oinstall&&groupadd dba&&useradd -g oinstall -G dba oracle
passwd oracle
mkdir /opt/oracle&&chown -R oracle:oinstall /opt/&&chmod -R 775 /opt
[root@dbserver ~]# vim ~oracle/.bash_profile
……
ORACLE_BASE=/opt
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
ORACLE_TERM=xterm
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_TERM ORACLE_SID PATH
内核要求
vim /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
会话要求
vi /etc/pam.d/login
session required pam_limits.so
vi /etc/security/limits.conf
oracle soft nproc 8192
oracle hard nproc 16384
oracle soft nofile 32768
oracle hard nofile 65536
重启谨记以新建的oracle用户登录
安装即可
oracle配置
启动、停止Oracle数据库
vim .bash_profile
ORACLE_BASE=/opt
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
ORACLE_TERM=xterm
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_TERM ORACLE_SID PATH
vim /etc/oratab
orcl:/opt/oracle:Y
运行dbstart时启动数据库实例
lsnrctl status
lsnrctl start
lsnrctl stop
emctl start/stop/status dbconsole
dbstart/dbshut
vim /opt/oracle/bin/dbstart
ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTNER=$ORACLE_HOME
vim /opt/oracle/bin/dbshut
ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTNER=$ORACLE_HOME
控制脚本
#!/bin/bash
# chkconfig: 35 88 99
# description: Oracle Database Service Daemon.
ORACLE_BASE="/opt"
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_OWNER=oracle
case "$1" in
start)
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" #//启用监听器
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" #//启用数据库
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole" #//启用OEM
;;
stop)
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole" #//停止OEM
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" #//停止数据库
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" #//停止监听器
;;
status)
if(pgrep "tnslsnr" && netstat -anpt | grep ":1521") &> /dev/null
then
echo "Oracle 11g Net Listener is running."
else
echo "Oracle 11g Net Listener is not running."
fi
if(netstat -anpt | grep ":1158" && netstat -anpt | grep ":5520") &> /dev/null
then
echo "Oracle 11g Enterprise Manager is running."
else
echo "Oracle 11g Enterprise Manager is not running."
fi
;;
restart)
$0 stop
$0 start
;;
*)
echo "Usage: $0 {start|stop|restart|status}"
exit 1
;;
esac
exit 0
历史命令
yum install readline readline-devel -y
tar zxvf rlwrap-0.37.tar.gz
./configure&&make&&make install
vim /home/oracle/.bashrc
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
登录及退出
sqlplus /nolog
conn /as sysdba
exit
启动、关闭数据库
startup
shutdown immediate;
用户操作
create user wcg identified by "123456";
grant connect,resource to wcg;
select * from session_privs;
select * from all_users where username='WCG'
alter user wcg identified by 654321;
drop user wcg cascade;
sysdba
select username,password from dba_users;
sys
alter user sys identified by manager;
system
alter user system identified by manager;
创建/删除
netca
dbca
create table stu01(name char(16) not null primary key,password char(16) not null );
select table_name from all_tables where table_name='STU01';
insert into stu01 values('wcg','123456');
desc stu01
drop table stu01;
显示结构
select * from v$instance;
select table_name from all_tables;
desc table-name
数据记录的插入与维护
insert into stu01 values('wcg','123456');
DELETE FROM STU01 WHERE NAME='WCG';
UPDATE STU01 SET PASSWORD='000000' WHERE NAME='LY';
SELECT * FROM STU01;
维护数据库
环境准备
mkdir /opt/bakup
chown -R oracle:oinstall /opt/bakup
CREATE DIRECTORY dmpdir AS '/opt/bakup';
GRANT all ON DIRECTORY dmpdir TO wcg;
备份
expdp wcg/123456 DIRECTORY=dmpdir DUMPFILE=wcg-20121010.dmp
还原
impdp wcg/123456 DIRECTORY=dmpdir DUMPFILE=wcg-20121010.dmp REUSE_DATAFILES=Y TABLE_EXISTS_ACTION=REPLACE
通过对Oracle数据库的监控,可以全面了解Oracle的运行状态、数据库响应情况、数据库表空用度情况。
SUM服务器监控软件对Oracle数据库的监控十分全面,主要有:缓冲区命中率监控、表空间使用率监控、表空间监控、会话数监控、连接数监控等核心性能,这些性能均为DBA必须了解和随时掌握的性能,SUM的监控为DBA提供了有力的帮助。在Oracle数据库的性能监控中其中表空间随时监控是DBA首要职责,因为一旦表空用尽,将无法再进行数据库的操作,这将给应用软件造成很大的损失。通过SUM监控与短信报警功能,DBA可以设定表空间使用率阀值进行报警,比如可以设定表空间使用率达到95%后就立即短信通知指定的DBA进行表空间扩容,从而可以有效地避免因为表空间不足引起的应用程序错误和数据库错误。