今天手工添加oracle的数据库实例(实例名:orcl_new),相对于我这种小白还是有一定的难度的,值得记录下来和大家分享
1.使用oracle用户登录主机,进入到oracle安装目录下,执行以下命令
cd /usr/oracle/app
mkdir -p admin/orcl_new/adump
mkdir -p admin/orcl_new/bdump
mkdir -p admin/orcl_new/cdump
mkdir -p admin/orcl_new/udump
mkdir -p admin/orcl_new/pfile
mkdir -p admin/orcl_new/create
mkdir -p oradata/orcl_new(用于存放控制文件之类的)
mkdir -p flash_recovery_area/orcl_new
2.进入目录:/usr/oracle/app/oracle/product/11.2.0/dbhome_1/dbs,创建初始化脚本initorcl_new.ora(建议复制原有的init.ora文件,文件名:initorcl_new.ora 中的orcl_new为我们新建的实例名,修改其中的如下内容)
db_name='orcl_new'
audit_file_dest='/usr/oracle/app/admin/orcl_new/adump'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL_NEWXDB)'
control_files = (/data/oracle/app/oradata/orcl_new/ora_control1,/data/oracle/app/oradata/orcl_new/ora_control2)
注:要注意,上面的内容要修改五个地方,特别要注意的是SERVICE,是实例名+XDB,博主之前就忘记了,还有control_files一定要放到各自实例的对应目录下,千万别覆盖了原来已有的实例的文件,不然你的灵魂会得到升华,亲身经历。
3.将实例切换到我们要新建的orcl_new(很重要,很重要,很重要,接下来的每一步操作都意味着是在操作这个oracle实例)
export ORACLE_SID=orcl_new
4.创建口令文件,口令文件存放路径我是跟我们第2步初始化脚本存放在一起的(注意:orapworcl_new 其中orcl_new是我们要新建的实例名)
orapwd file="/usr/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl_new" password=oracle entries=10;
5.进入到我们的oracle控制台,并且连接到sysdba用户
sqlplus /nolog
conn /as sysdba
startup nomount;
这里强烈建议执行下面命令,再次确认我们是否已经切换到了我们即将新建的orcl_new,查看是否打印了orcl_new
show parameter instance_name;
6.执行命令,创建数据库(注意其中的路径跟您是否一致,如果一致可直接复制粘贴执行,不一致的根据自身情况修改)
CREATE DATABASE orcl_new
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/usr/oracle/app/oradata/orcl_new/redo01.log') SIZE 20M,
GROUP 2 ('/usr/oracle/app/oradata/orcl_new/redo02.log') SIZE 20M,
GROUP 3 ('/usr/oracle/app/oradata/orcl_new/redo03.log') SIZE 20M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/usr/oracle/app/oradata/orcl_new/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/usr/oracle/app/oradata/orcl_new/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users datafile '/usr/oracle/app/oradata/orcl_new/users_01.dbf' size 500m
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/usr/oracle/app/oradata/orcl_new/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/usr/oracle/app/oradata/orcl_new/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
7.继续执行脚本,创建字典及视图(下面命令请一条一条的在oracle控制台执行,其中第三条和第四条的执行时间稍微会久点,估计要20-30分钟)
conn sys as sysdba
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/owminst.plb;
conn system/ manager
@?/sqlplus/admin/pupbld.sql;
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
8.到这一步,恭喜你,实例以及创建成功了,但是我们要需要配置监听文件,才能够使用该实例,进修改文件:/usr/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora,往里添加以下内容,千万要注意到格式,别大风大浪都过来了,小阴沟里翻了船。
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_produce)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /usr/oracle/app
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_new)
(ORACLE_HOME = /usr/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl_new)
)
)
9.然后配置本地Pl/SQL的tnsnames.ora文件
orcl_new =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_produce)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_new )
)
)
10.给我们新建的实例添加用户并解锁
create user orcl identified by Abc_123;
alter user orcl account unlock;
11.给上一步新建的用户进行赋权,具体需要什么权限根据自身情况定制
grant create session to orcl; //创建session权限,及登录的权限
grant unlimited tablespace to orcl; //使用表空间权限
grant create table to orcl; //建表权限
grant drop any table to orcl; //删除表权限
grant insert any table to orcl; //插入表权限
grant update any table to orcl; //修改表权限
grant create view to orcl; //创建视图权限
12.到这里我们就全部结束了,可以使用pl/sql工具进行登录了,希望对你有用。