一、windows搭建部署oracle 11g数据库
1)安装oracle 11g数据库
s首先在电脑上安装PLSQL developer,这个是oracle图形化连接工具,然后安装win64_11gR2_client,这个是orace客户端,安装完成后可以在cmd命令行输入sqlplus命令进行验证,如图表示安装成功。
作为sys的连接应该是SySDBA或Sysopor
所以,我们在登录的时候,可以试一下:sys as sysdba
这个路径自动读出来
创建用户
#创建用户:
create user c##ruser identified by "1qaz@09876!@#$";
#授权:
grant create session to c##ruser;
grant create table to c##ruser;
grant create tablespace to c##ruser;
grant create view to c##ruser;
grant sysdba to c##ruser;
grant connect,resource,dba to c##ruser;
windows下安装oracle11g测试是否成功与监听器问题和网页控制台登录
#修改oralce密码
alter user C##ROOTUSER identified by "123456@abc.COM";
https://www.cnblogs.com/zixinyang/p/10611382.html
tnsping ORCLCDB
现在解决了
sqlplus c##ruser/1qaz@09876!@#$@ORCLCDB
https://www.yingsoo.com/news/database/42487.html
https://www.52dianzi.com/category/article/33f98702a85b8ca989a28f62ed787095.html
tnsping 192.168.121.130/ORCLCDB
1.首先检查监听程序是否能正常启动,开始,运行 cmd--->lsnrctl start,检查监听是否是在正常运行,如果正常运行,会提示:TNS-01106: 使用名称LISTENER的监听程序已经启动。如果没有运行,用上面的命令启动监听程序
2)创建表
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );
-- 建表语句
create table T_ADEV_SCLG_YXDXMB
(
obj_id VARCHAR2(42) not null,
xmmc VARCHAR2(400),
xmbh VARCHAR2(100),
jhnd INTEGER,
gsmc VARCHAR2(50),
xmssdwmc VARCHAR2(50),
xmlx VARCHAR2(4),
zcgs VARCHAR2(50),
gdzcfl VARCHAR2(50),
ejfl VARCHAR2(50),
sjfl VARCHAR2(50),
zgshzy VARCHAR2(40),
xmgllx VARCHAR2(42),
sbmc VARCHAR2(200),
xmnr VARCHAR2(3020),
tzgm VARCHAR2(20),
sl INTEGER,
jldw VARCHAR2(8),
kypfwh VARCHAR2(42),
zxxdzt VARCHAR2(40),
ztz NUMBER(10,2),
tzjh VARCHAR2(2000),
sfdbxm VARCHAR2(20),
bz VARCHAR2(2000),
xmsssdw VARCHAR2(42),
xmsssdwmc VARCHAR2(50),
xmssxdw VARCHAR2(42),
xmssxdwmc VARCHAR2(50),
bzr VARCHAR2(42),
bzrmc VARCHAR2(200),
bzrq DATE
);
-- Add comments to the table
comment on table T_ADEV_SCLG_YXDXMB
is '项目表';
-- Add comments to the columns
comment on column T_ADEV_SCLG_YXDXMB.obj_id
is '主键';
comment on column T_ADEV_SCLG_YXDXMB.xmmc
is '项目名称';
comment on column T_ADEV_SCLG_YXDXMB.xmbh
is '项目编号';
comment on column T_ADEV_SCLG_YXDXMB.jhnd
is '计划年度';
comment on column T_ADEV_SCLG_YXDXMB.bzr
is '编制人';
comment on column T_ADEV_SCLG_YXDXMB.bzrmc
is '编制人名称';
comment on column T_ADEV_SCLG_YXDXMB.bzrq
is '编制日期';
-- Create/Recreate indexes
create index IDX_T_ADEV_SCLG_YXDXMB_XMBH on T_ADEV_SCLG_YXDXMB (XMBH);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_ADEV_SCLG_YXDXMB
add constraint PK_T_ADEV_SCLG_YXDXMB primary key (OBJ_ID);
二、linux部署oralce19c数据库
2)端口的查看
su - oracle
lsnrctl status
echo $ORACLE_HOME
$ORACLE_HOME/network/admin/listener.ora
/opt/oracle/product/19c/dbhome_1/network/admin
第二种方法是查看listener.ora文件。listener.ora文件是监听者所使用的参数文件,用于设置和维护Oracle数据库端口号,其中可以找到服务监听端口号,通常在网络目录admin under Oracle ORACLE_HOME环境变量下面
根据listener.ora中的配置参数查看端口号:
3)进程的查看:
#服务器进程存放于服务器上面,进程的命名方式为oracleSID的格式,通过ps -ef|grep oracle的方式查看;
ps -ef | grep oracle
ps -ef|grep ora
安装过程遇到问题
1)Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
解决办法
修改之前先备份
cp listener.ora listener_bak.ora
cp tnsnames.ora tnsnames_bak.ora
原始未做修改
变成这个效果
查看状态
systemctl status oracle.service
[root@k8s-master01 ~]# systemctl restart oracle
Failed to restart oracle.service: Unit is not loaded properly: Invalid argument.
See system logs and 'systemctl status oracle.service' for details.
[root@k8s-master01 ~]# systemctl status oracle.service
● oracle.service - Oracle RDBMS
Loaded: error (Reason: Invalid argument)
Active: inactive (dead)
1月 01 09:24:26 k8s-master01 systemd[1]: [/usr/lib/systemd/system/oracle.service:7] Executable path is not absolute, ignoring: su - oracle -c "/opt/oracle/product/19c/dbhome_1/bin/dbstart.sh >> /tmp/oracle.log"
1月 01 09:24:26 k8s-master01 systemd[1]: [/usr/lib/systemd/system/oracle.service:8] Executable path is not absolute, ignoring: su - oracle -c "/opt/oracle/product/19c/dbhome_1/bin/dbrestart.sh >> /tmp/oracle.log"
1月 01 09:24:26 k8s-master01 systemd[1]: [/usr/lib/systemd/system/oracle.service:9] Executable path is not absolute, ignoring: su - oracle -c "/opt/oracle/product/19c/dbhome_1/bin/dbshut.sh \>\> /tmp/oracle.log"
1月 01 09:24:26 k8s-master01 systemd[1]: oracle.service lacks both ExecStart= and ExecStop= setting. Refusing.
1月 01 09:24:27 k8s-master01 systemd[1]: Cannot add dependency job for unit oracle.service, ignoring: Unit is not loaded properly: Invalid argument.
[root@k8s-master01 ~]#
解决办法
加上绝对路径就可以
2)ORA-28040: 没有匹配的验证协议
解决办法
打开 sqlnet.ora 文件,增加以下两行
find / -iname sqlnet.ora
vim /opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
不用重启Oracle Listener 和 数据库 服务,应用重新连接,会报告新问题:ORA-01017: 用户名/口令无效; 登录被拒绝
解决:用sys登录,修改密码,新密码与原密码相同即可
alter user 用户名 identified by 密码
应用再重新连接,成功。