linux上操作Oracle

时间：2024-03-16 20:16:57

通过Oracle用户连接

linux下查看oracle中有多少个实例已经启动了:
ps -ef|grep smon

echo命令可以查看变量
echo $ORACLE_HOME看看目录在哪

启动数据库实例，分为两步：

第一步，启动监听；
su - oracle --“切换到oracle用户”
lsnrctl start --“打开监听”
lsnrctl stop --“关闭监听”
lsnrctl status --“查看监听状态”

第二步，启动数据库实例；
1、设置默认的oracle_sid 可以用echo $ORACLE_SID查看当前默认的SID
export ORACLE_SID=SID2

2、运行sqlplus命令，进入到sqlplus环境(以不连接数据库的方式启动sqlplus)”
sqlplus /nolog

3、连接数据库(以管理员身份连接)，使用sys登录oracle在执行startup命令
conn /as sysdba

4、启动数据库实例的命令
startup

5、关闭数据库实例的命令
shutdown immediate

6、查看状态
select status from vinstance;selectopenmodefromvinstance; select open_mode from vdatabase;

7、连接指定的用户（显示connected表示连接成功）
conn username/password

配置环境变量：
[[email protected] ~]$ vim ~/.bash_profile
linux上操作Oracle
[[email protected] ~]# source ~/.bash_profile（使其生效）

手动创建数据库：

  • 定义实例名、数据库名
    实例名：DZHBDB
    数据库名：DZHBDB

  • 创建目录
    mkdir -p /u01/app/oracle/admin/DZHBDB/adump
    mkdir -p /u01/app/oracle/oradata/DZHBDB/controlfile
    mkdir -p /u01/app/oracle/oradata/DZHBDB/datafile
    mkdir -p /u01/app/oracle/oradata/DZHBDB/onlinelog

  • 创建参数文件
    vi /u01/app/oracle/product/dbs/initDZHBDB.ora

    DZHBDB.__db_cache_size=10938744832
    DZHBDB.__java_pool_size=67108864
    DZHBDB.__large_pool_size=335544320
    DZHBDB.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
    DZHBDB.__pga_aggregate_target=6308233216
    DZHBDB.__sga_target=18857590784
    DZHBDB.__shared_io_pool_size=0
    DZHBDB.__shared_pool_size=7314866176
    DZHBDB.__streams_pool_size=67108864
    *.audit_file_dest=’/u01/app/oracle/admin/DZHBDB/adump’
    *.audit_trail=‘db’
    *.compatible=‘11.2.0.4.0’
    *.control_files=’/u01/app/oracle/oradata/DZHBDB/controlfile/control01.ctl’,’/u01/app/oracle/oradata/DZHBDB/controlfile/control02.ctl’,’/u01/app/oracle/oradata/DZHBDB/controlfile/control03.ctl’
    *.db_block_size=8192
    *.db_create_file_dest=’/u01/app/oracle/oradata/DZHBDB/datafile’
    *.db_domain=’’
    *.db_files=1024
    *.db_name=‘DZHBDB’
    *.db_writer_processes=6
    *.diagnostic_dest=’/u01/app/oracle’
    *.dispatchers=’(PROTOCOL=TCP) (SERVICE=DZHBDBXDB)’
    *.filesystemio_options=‘ASYNCH’
    *.job_queue_processes=0
    *.log_checkpoints_to_alert=TRUE
    *.open_cursors=300
    *.open_links=0
    *.pga_aggregate_target=11474836480
    *.processes=1000
    *.recovery_parallelism=0
    *.remote_login_passwordfile=‘EXCLUSIVE’
    *.sga_max_size=18854445056
    *.sga_target=18854445056
    *.undo_tablespace=‘UNDOTBS’
    *.use_large_pages=‘TRUE’

  1. 启动实例
    sqlplus / as sysdba
    startup nomount pfile=’/u01/app/oracle/product/dbs/initDZHBDB.ora’;
    create spfile from pfile=’/u01/app/oracle/product/dbs/initDZHBDB.ora’;
    shutdown immediate;
    startup nomount;

  2. 创建数据库
    CREATE DATABASE DZHBDB
    USER SYS IDENTIFIED BY oracle
    USER SYSTEM IDENTIFIED BY oracle
    LOGFILE GROUP 1 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo01.log’) SIZE 100M BLOCKSIZE 512,
    GROUP 2 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo02.log’) SIZE 100M BLOCKSIZE 512,
    GROUP 3 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo03.log’) SIZE 100M BLOCKSIZE 512
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 5
    MAXDATAFILES 1000
    CHARACTER SET ZHS16GBK
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/system01.dbf’ SIZE 325M REUSE
    SYSAUX DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/sysaux01.dbf’ SIZE 325M REUSE
    DEFAULT TABLESPACE users
    DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/users01.dbf’ SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE temp TEMPFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/temp01.dbf’ SIZE 20M
    UNDO TABLESPACE undotbs DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/undotbs01.dbf’ SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

  3. 创建视图和过程
    conn / as sysdba
    @?/rdbms/admin/catalog.sql
    @?/rdbms/admin/catproc.sql

  4. 创建监听并启动
    cd $ORACLE_HOME/network/admin
    vi listener.ora
    LISTENER=
    (DESCRIPTION=
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=ZL-ZHB-ORADB01)(PORT=1521))
    (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
    lsnrctl start

  5. 测试
    sqlplus system/[email protected]
    select open_mode from v$database;
    或
    sqlplus system/[email protected]/DZHBDB

如sys用户无法登录，解决方法如下：
cd /u01/app/oracle/product/11.2.4/db_1/dbs
orapwd file=orapwDZHBDB
输入登录密码设置完成

show parameter log_checkpoint
alter system set log_checkpoints_to_alert=FALSE;

相关文章