Oracle的安装及使用流程

07-16 982阅读

Oracle的安装及使用流程

1.Win10安装Oracle10g

1.1 安装与测试

安装版本: OracleXEUniv10.2.1015.exe

步骤参考:oracleXe下载与安装

安装完成后测试是否正常

# 输入命令连接oracle
conn sys as sysdba;
# 无密码,直接按回车
# 测试连接的sql语句
select 1 from dual;

Oracle的安装及使用流程

Oracle的安装及使用流程

1.2 查询数据库信息

#在Oracle中
#查询数据库名
select name,dbid from v$database;或者show parameter db_name;
#查询实例名
select instance_name from v$instance;或者show parameter instance_name;
#查询数据库域名
select value from v$parameter where name='db_domain';或者show parameter domain;
#查询数据库服务器
select value from v$parameter where name='service_name';或者show parameter service;或者show parameter names;
#在PL/SQL下
Oracle用户查询其他用户的表一定要使用user.table格式才能查询,每次用户名.表名嫌麻烦的话。可以使用同义词,意思将user.table访问格式改成table格式即可
#创建同义词语句格式:
create public synonym table_name for user.table_name;
public很关键词,访问权限修饰,若想某表的授权用户也能用同义词查询,必须要用public访问修饰符。
#举例说明
原本"表前缀.表名"格式才能访问select * from PAASDATA.paas_city_def;
创建同义词:create public synonym table_name for PAASDATA.paas_city_def;
可直接用表名:select * from paas_city_def;

1.3 自定义用户和表空间

执行创建命令

conn sys/root as sysdba;
# 查询实例
select instance_name from v$instance;
# 查询数据库
select name from v$datafile;
# 创建表空间
create tablespace CK_BUS_TABLE_SPACE datafile 'D:/AsiaInfo/oraclexe/oraclexe/oradata/XE/CK_BUS_TABLE_SPACE.dbf' size 200m 
autoextend on extent  management local segment space management auto;
# 查询数据库
select name from v$datafile;
# 创建用户并指定表空间
# CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB;
CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE CK_BUS_TABLE_SPACE;
# 给用户授予dba的权限
grant dba to CK_BUS; 
# 查看当前数据库连接数
select count(*) from v$process;
# 查看数据库允许的最大连接数
select value from v$parameter where name = 'processes';
# 如果数据库连接数超过最大连接数,请修改最大连接数,我这里修改成了1000
alter system set processes = 1000 scope = spfile;
# 重启
shutdown immediate;
startup;

Oracle的安装及使用流程

测试创建结果

# 登录账号
conn CK_BUS/ck_bus@xe;
select * from dual;

Oracle的安装及使用流程

2.CentOS7安装Oracle11gR2

2.1 安装与测试

#CentOS Linux release 7.6.1810 (Core)
redhat-7
yum -y install binutils* compat-libcap1* compat-libstdc++* gcc* gcc-c++* glibc* glibc-devel* ksh* libaio* libaio-devel* libgcc* libstdc++* libstdc++-devel* libXi* libXtst* make* sysstat* elfutils* unixODBC* unzip lrzsz
groupadd oinstall
groupadd dba
useradd -g dba -m oracle
passwd oracle
id oracle
mkdir -p /data/oracle
mkdir -p /data/oracle/inventory
mkdir -p /data/oracle/src
chown -R oracle:oinstall /data/oracle
chown -R oracle:oinstall /data/oracle/inventory
chown -R oracle:oinstall /data/oracle/src
#解压linux.x64_11gR2安装包
unzip linux.x64_11gR2_database_1of2.zip -d /data/oracle/src
unzip linux.x64_11gR2_database_2of2.zip -d /data/oracle/src
chown -R oracle:oinstall /data/oracle/src
ll /data/oracle/src
su oracle
vim /data/oracle/src/database/response/db_install.rsp
/data/oracle/src/database/runInstaller -silent -responseFile /data/oracle/src/database/response/db_install.rsp -ignorePrereq
/data/oracle/product/11.2.0/db_1/root.sh
vim /data/oracle/src/database/response/netca.rsp
/data/oracle/product/11.2.0/db_1/bin/netca /silent /responseFile /data/oracle/src/database/response/netca.rsp
vim /data/oracle/src/database/response/dbca.rsp
/data/oracle/product/11.2.0/db_1/bin/dbca -silent -responseFile /data/oracle/src/database/response/dbca.rsp
ps -ef | grep ora_ | grep -v grep
vim /data/oracle/product/11.2.0/db_1/bin/dbstart
vim /data/oracle/product/11.2.0/db_1/bin/dbshut
# 遇到问题
# 解决linux中oracle的/etc/oratab文件的缺失导致数据库启动失败https://blog.csdn.net/kruie/article/details/88676777
vim /etc/oratab
orcl:/data/oracle/product/11.2.0:Y
/data/oracle/product/11.2.0/db_1/bin/dbstart
chmod +x /etc/rc.d/rc.local
vim /etc/rc.d/rc.local
su oracle -lc "/data/oracle/product/11.2.0/db_1/bin/lsnrctl start"
su oracle -lc "/data/oracle/product/11.2.0/db_1/bin/dbstart"
su oracle
sqlplus / as sysdba

2.2 创建用户和表空间

  • 以超级管理员身份登录
  • 创建表空间
  • 创建用户
  • 给用户授权
  • 查询测试
    # linux创建数据库Oracle用户,linux下ORACLE数据库中创建新用户
    # https://blog.csdn.net/weixin_29325515/article/details/116349855
    su - oracle
    sqlplus /nolog
    # 通过超级管理员以dba的身份登录
    conn sys/root as sysdba;
    # conn /as sysdba
    # startup
    select name from v$datafile;
    # 查询实例
    select instance_name from v$instance;
    # 创建表空间
    CREATE TABLESPACE MY_ORACLE_TABLE_SPACE DATAFILE '/data/oracle/oradata/orcl/my_oracle_table_space.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
    select name from v$datafile;
    # 创建用户并指定表空间
    # CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB;
    CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE MY_ORACLE_TABLE_SPACE;
    # 给用户授予dba的权限
    grant dba to CK_BUS; 
    exit;
    db_name=ora_ck
    db_block_size=8192
    control_files=(/data/oracle/oradata/check/ora_ck.ctl)
    undo_tablespace=undotbs1
    undo_management=auto
    sga_max_size=1024M
    sga_target=1024M
    pga_aggregate_target=1024M
    log_buffer=10485760
    processes=300
    open_cursors=2000
    sessions=330
    audit_trail ='db'
    db_domain=''
    remote_login_passwordfile='EXCLUSIVE'
    compatible ='11.2.0.1.0'
    export ORACLE_SID=ora_ck
    sqlplus / as sysdba
    mkdir redo
    mkdir data
    startup nomount pfile='/data/oracle/oradata/check/ora_ck.ora'
    CREATE DATABASE ora_ck
    USER SYS IDENTIFIED BY oracle
    USER SYSTEM IDENTIFIED BY oracle
    DATAFILE '/data/oracle/oradata/check/data/system_01.dbf' SIZE 1024m autoextend on
    SYSAUX DATAFILE '/data/oracle/oradata/check/data/sysaux_01.dbf' SIZE 512m autoextend on
    UNDO TABLESPACE undotbs1 DATAFILE '/data/oracle/oradata/check/data/undotbs1_01.dbf' SIZE 1024m autoextend on
    DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 
    '/data/oracle/oradata/check/data/temp_01.dbf' SIZE 1024m REUSE autoextend on
    LOGFILE
    GROUP 1 ('/data/oracle/oradata/check/redo/redo11.log','/data/oracle/oradata/check/redo/redo12.log') SIZE 50m,
    GROUP 2 ('/data/oracle/oradata/check/redo/redo21.log','/data/oracle/oradata/check/redo/redo22.log') SIZE 50m,
    GROUP 3 ('/data/oracle/oradata/check/redo/redo31.log','/data/oracle/oradata/check/redo/redo32.log') SIZE 50m
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET UTF8;
    create spfile from pfile='/data/oracle/oradata/check/ora_ck.ora';
    @?/rdbms/admin/catalog
    @?/rdbms/admin/catproc
    lsnrctl status
    lsnrctl stop
    lsnrctl start
    lsnrctl reload
    hostname
    more /etc/hosts
    ps -ef|grep LISTENER
    kill 4562
    # 开机自启动
    cat /etc/oratab
    su - oracle
    export ORACLE_SID=ora_ck
    sqlplus / as sysdba
    # 动态绑定,不需要考虑listener.ora和tnsnames.ora
    alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.31.214)(PORT=1521))';
    alter system register;
    show parameter local_listener
    show parameter service
    shutdown immediate
    startup
     
    # 不使用该语句,则默认连接orcl数据库
    export ORACLE_SID=ora_ck
    sqlplus / as sysdba
    # 查询实例
    select instance_name from v$instance;
    # 查询数据库
    select name from v$datafile;
    # 创建表空间
    CREATE TABLESPACE CK_BUS_TABLE_SPACE DATAFILE '/data/oracle/oradata/check/data/ck_bus_table_space.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
    # 查询数据库
    select name from v$datafile;
    # 创建用户并指定表空间
    # CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB;
    CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE CK_BUS_TABLE_SPACE;
    # 给用户授予dba的权限
    grant dba to CK_BUS; 
    

    参考网址

    CentOS7安装Oracle完整教程(超级详细,亲测完美)

    Linux服务器创建Oracle实例,命令行安装详解

    Linux下安装ORACLE(一周成果、全是干货!)

    CentOS7安装Oracle数据库的全流程

    解决数据库连接报错Listener refused the connection with the following error: ORA-12505

    linux 启动监听报 TNS-12545、TNS-12560、TNS-00515问题处理办法

    Oracle 11g安装使用、备份恢复并与SpringBoot集成

    oracle jdbctype null,Oracle数据库之springboot 项目mybatis plus 设置 jdbcTypeForNull

    怎么删除oracle用户及表空间

    解决ORA-01034: ORACLE not available

    如何重启oracle数据库及监听

    Oracle创建实例dbca错误

    Oracle删除数据的三种方式

    使用Druid连接池远程访问阿里云服务器中安装的Oracle库,隔一段时间总是连接超时的解决方案

    linux centos7环境下修改oracle19c监听IP并重启

    TNS-12542: TNS:address already in use 问题

    NS-01201: Listener cannot find executable /u01/oracle/bin/extproc for SID orcl Listener failed to

    Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…

    监控多台oracle,ORACLE 一个监听开启多个实例

    Oracle processes和sessions参数(进程连接数&会话连接数)

    解决‘ORA-12505, TNS:listener does not currently know of SID given in connect descriptor’

VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]