oracle 基础运用2

一、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的监听程序已经启动。如果没有运行，用上面的命令启动监听程序

Oracle数据库查看用户状态

 

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 密码

应用再重新连接，成功。