后端开发实战

Appearance

oracle 11g 创建表空间及用户 授权 ​

sql
--  创建表空间   
CREATE TABLESPACE upgrade DATAFILE '/home/oracle/data/upgrade.dbf' size 50M AUTOEXTEND ON next 50M maxsize 20480M
--  创建用户并授权
create user upgrade identified by upgrade default tablespace upgrade ;
--  授予用户  管理员权限
grant dba to username; 
--  解锁用户
alter user scott account unlock;
--  给用户 授权 查询权限
grant select any table to upgrade;
--   授权  连接 等
grant connect,resource to upgrade;  

-- -- 查询数据库 当前进程的连接数
select count(*) from v$process;
-- -- 查询数据库 当前回话的连接数
select count(*) from v$session;
-- -- 查询数据库 并发的连接数
select count(*) from v$session  where  status='ACTIVE';
-- -- 查询数据库 允许的最大的连接数
SELECT VALUE from  v$parameter where name='processes'

oracle 11g 备份 ​

sh
## 数据泵方式
su - oracle 
mkdir -p /home/oracle/backup
sqlplus /nolog
conn upgrade/upgrade
## 登录sql plus   创建目录
create or replace directory backup_dir as '/home/oracle/backup';
##  查看  目录
select * from dba_directories;
## 备份 表
expdp upgrade/upgrade directory=backup_dir  dumpfile=upgrade.dmp  NOLOGFILE=Y tables=tb_user
## 恢复
impdp upgrade/upgrade DIRECTORY=backup_dir DUMPFILE=upgrade.dmp LOGFILE=biao.dmp.log  REMAP_SCHEMA=idms:idms TABLE_EXISTS_ACTION=truncate
## 恢复到别的数据库和表空间 idms -> upgrade
impdp upgrade/upgrade DIRECTORY=backup_dir DUMPFILE=idms.dmp LOGFILE=biao.dmp.log  REMAP_SCHEMA=idms:upgrade   REMAP_TABLESPACE=idms:upgrade TABLE_EXISTS_ACTION=truncate

oracle 11g 安装 centos 静默安装 ​

前置要求 swap 分区 至少2G 否则无法安装

1.基础设置 ​

sh
##  安装 依赖
yum install -y binutils* compat-libstdc++* compat-libcap1* gcc* gcc-c++* glibc-devel*
yum install -y ksh* libaio* libaio-devel* libgcc* libstdc++* libstdc++devel*
yum install -y libXi* libXtst* make* sysstat* elfutils* unixODBC* lrzsz
## 修改内核参数
vim /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p

## 检查资源限制
vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

##  创建用户和用户组
groupadd oinstall
groupadd dba
useradd -g dba -m oracle
usermod -a -G oinstall oracle
echo "Nimt@12580" | passwd --stdin oracle
## 创建目录
mkdir -p /opt/oracle
mkdir -p /opt/inventory
mkdir -p /opt/src
## 修改oracle用户的环境变量

vim /home/oracle/.bashrc
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LANG=C
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

source /home/oracle/.bashrc

##  上传文件并解压到指定目录
unzip -q oracleDB_linux_11gR2_1of2.zip -d /opt/src
unzip -q oracleDB_linux_11gR2_2of2.zip -d /opt/src

chown -R oracle:oinstall /opt/oracle/
chown -R oracle:oinstall /opt/inventory/
chown -R oracle:oinstall /opt/src/

#   关闭防火墙和selinux
systemctl stop firewalld && systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce 0

2.设置响应文件 ​

sh
vim /opt/src/database/response/db_install.rsp

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=Cent7OracleDB
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/opt/inventory/
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
ORACLE_BASE=/opt/oracle
oracle.install.db.InstallEdition=EE

oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=orcl
oracle.install.db.config.starterdb.SID=orcl
oracle.install.db.config.starterdb.memoryLimit=800
oracle.install.db.config.starterdb.password.ALL=Nimt@12580
DECLINE_SECURITY_UPDATES=true

安装

sh
##在 oracle 用户下执行
/opt/src/database/runInstaller -silent -responseFile /opt/src/database/response/db_install.rsp -ignorePrereq
##  root 用户执行
/opt/oracle/product/11.2.0/db_1/root.sh

3. 配置监听 ​

sh
## 在 oracle 用户下执行
vim /opt/src/database/response/netca.rsp
INSTALL_TYPE=""custom""

启动监听 在 oracle 用户下执行

sh
/opt/oracle/product/11.2.0/db_1/bin/netca /silent /responseFile /opt/src/database/response/netca.rsp

netstat -tnulp | grep 1521

4.数据库实例文件 ​

sh
vim /opt/src/database/response/dbca.rsp

## oracle  实例名称 sid
GDBNAME = "orcl"
SID = "orcl"
## 设置管理员密码
SYSPASSWORD = "Nimt@12580"
SYSTEMPASSWORD = "Nimt@12580"
SYSMANPASSWORD = "Nimt@12580"
DBSNMPPASSWORD = "Nimt@12580"
DATAFILEDESTINATION = /opt/oracle/oradata
RECOVERYAREADESTINATION=/opt/oracle/flash_recovery_area
## 字符集
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "AL16UTF16"
TOTALMEMORY = "1638"

##安装数据库实例
/opt/oracle/product/11.2.0/db_1/bin/dbca -silent -responseFile /opt/src/database/response/dbca.rsp

5.启动数据库 ​

sh
su - oracle
sqlplus / as sysdba

sqlplus 控制台乱码 ​

sh
yum install epel-release -y
yum install rlwrap -y

su - oracle
vim ~/.bashrc  
## 增加别名设置
alias sqlplus="rlwrap sqlplus"
alias rman='rlwrap rman'

source .bashrc
即可

开机 关机 ​

sh
shutdown immediate;
startup
## 关闭监听
lsnrctl stop
lsnrctl start

lsnrctl status