20.2 图形化
我的安装目录 cd /data/oracle/product/11.2.0
- 成功运行后，在/data/oracle/product/11.2.0/db_1/network/admin/或/data/oracle/product/11.2.0/network/admin/中生成listener.ora和sqlnet.ora
20.3 通过netstat命令可以查看1521端口正在监听。
Yum安装netstat软件，软件包是在net-tools中。
[root@localhost ~]$ yum install net-tools [root@localhost ~]$ netstat -tnulp | grep 1521 tcp6 0 0 :::1521 :::* LISTEN 19955/tnslsnr
21.以静默方式建立新库，同时也建立一个对应的实例。
用oracle用户登录,再次进去响应文件夹
[oracle@localhost ~]$ vim /home/oracle/response/dbca.rsp
注：在这个文件dbca.rsp找对应的将等于号后面填了，并且默认为是注释掉的，把用到的不要注释
设置参数：
GDBNAME= “infodb” SID =” infodb” SYSPASSWORD= “ system@2017” SYSTEMPASSWORD= “system@2017” SYSMANPASSWORD= “ system@2017” DBSNMPPASSWORD= “ system@2017” DATAFILEDESTINATION=/data/oracle/oradata RECOVERYAREADESTINATION=/data/oracle/fast_recovery_area CHARACTERSET= “ZHS16GBK” TOTALMEMORY= “819”
进行静默配置：
[oracle@localhost ~]$ dbca -silent -responseFile /home/oracle/response/dbca.rsp
查看监听状态(监听命令 启动监听：lsnrctl start、停止监听：lsnrctl stop、查看监听状态：lsnrctl status)
登录查看实例状态：
[oracle@localhost ~]$ sqlplus / as sysdba #sysdba超级用户 SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 24 12:55:23 2020 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost ~]$
22.创建表空间(模式)、用户及授权
1、创建表空间
INITIAL_G 是表空间的名称，DATAFILE是创建数据库文件的路径，然后初始大小是200m，然后自动增加是500M，参数可以根据自己的需求来调整
SQL> CREATE TABLESPACE INITIAL_G DATAFILE '\u01\app\oracle\oradata\INITIAL_G.dbf' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2、创建用户 oracle是根据用户区分库的也就是表空间 INITIAL_G 表空间 infodb是数据库用户 by后的是密码
SQL> CREATE USER infodb IDENTIFIED BY infodb DEFAULT TABLESPACE INITIAL_G; User created.
注：如果过出现以下的话就是你没有加分号，oracle语法是非常严格的,加个分号自动退出
SQL> CREATE USER infodb IDENTIFIED BY infodb DEFAULT TABLESPACE INITIAL_G 2
3、授权（直接给了 dba权限）
SQL> grant dba to infodb; Grant succeeded.
查询数据库用户名
select username, account_status, lock_date from dba_users;
ACCOUNT_STATUS ：账户状态 open 启用
oracle的默认的用户名和密码
1.用户名:sys密码:change_on_install 2.用户名:system密码:manager 3.用户名:scott密码:tiger 普通用户
[oracle@localhost ~]$ sqlplus /nolog 无用户名登录
conn 用户名/密码 切换用户
可能会报错SP2-0306: Invalid option.
换成connect 命令
SQL> connect Enter user-name: scott Enter password: tiger
会提示过期，直接修改密码
如果多次进行密码输入错误，我记得是3次以上就把你这个用户账号锁住了
4、 解锁用户
alter user 用户名 account unlock;
5、测试远程连接
23、设置开机启动
1、修改/data/oracle/product/11.2.0/bin/dbstart
[oracle@localhost~]$ vim /data/oracle/product/11.2.0/bin/dbstart
#将ORACLE_HOME_LISTNER=$1 修改为ORACLE_HOME_LISTNER=$ORACLE_HOME
2、修改/data/oracle/product/11.2.0/bin/dbshut
[oracle@localhost~]$ vim /data/oracle/product/11.2.0/bin/dbshut
将ORACLE_HOME_LISTNER=$1 修改为ORACLE_HOME_LISTNER=$ORACLE_HOME
3、修改/etc/oratab 文件
infodb:/data/oracle/product/11.2.0:N `将 N 改为 Y `
4、创建启动文件
切换root用户 vim /etc/init.d/oracle 添加以下内容
#!/bin/sh # chkconfig: 345 61 61 # description: Oracle 11g R2 AutoRun Servimces # /etc/init.d/oracle # # Run-level Startup script for the Oracle Instance, Listener, and # Web Interface export ORACLE_BASE=/data/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0 export ORACLE_SID=infodb export ORACLE_UNQNAME=$ORACLE_SID export PATH=$ORACLE_HOME/bin:/user/sbin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ORA_OWNR="oracle" # if the executables do not exist -- display error if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ] then echo "Oracle startup: cannot start" exit 1 fi # depending on parameter -- startup, shutdown, restart # of the instance and listener or usage display case "$1" in start) # Oracle listener and instance startup su $ORA_OWNR -lc $ORACLE_HOME/bin/dbstart echo "Oracle Start Succesful!OK." ;; stop) # Oracle listener and instance shutdown su $ORA_OWNR -lc $ORACLE_HOME/bin/dbshut echo "Oracle Stop Succesful!OK." ;; reload|restart) $0 stop $0 start ;; *) echo $"Usage: `basename $0` {start|stop|reload|reload}" exit 1 esac exit 0
5、设置启动文件权限
[root@localhost~]$ chmod +x oracle [root@localhost~]$ chkconfig --add oracle [root@localhost~]$ chkconfig oracle on
6、 重启测试
[root@localhost~]$ reboot
7、查看状态、没有启动
执行启动监听命令
修改hostname 和 hosts 为同样的
[root@localhost admin]# vim /etc/hostname localhost
修改hosts
[root@localhost admin]# vim /etc/hosts 127.0.0.1 localhost 192.168.198.130 localhost
24、解决报错
1、执行了一边命令配置监听有执行了一遍图形化的
（这个原因可能是我在执行步骤20时，执行了一遍图形化和命令监听导致的)看到这个·Listener "LISTENER" already exists.，但是我监听的名字是infodb,然后我查看/home/oracle/response/netca.rsp文件，将文件中LISTENER_NAMES改为我的监听 infodb,重新执行监听netca /silent /responseFile /home/oracle/response/netca.rsp,启动 lsnrctl statrt
[oracle@oracle ~]$ netca /silent /responseFile /home/oracle/response/netca.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/response/netca.rsp Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Listener "LISTENER" already exists. Oracle Net Services configuration successful. The exit code is 0
解决过程全命令
[root@oracle ~]# su - oracle Last login: Fri Sep 9 02:01:08 PDT 2022 on pts/1 [oracle@oracle ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:07:01 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused [oracle@oracle ~]$ lsnrctl start\ > LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:07:09 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /data/oracle/product/11.2.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /data/oracle/product/11.2.0/network/admin/listener.ora Log messages written to /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 09-SEP-2022 02:07:11 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data/oracle/product/11.2.0/network/admin/listener.ora Listener Log File /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) The listener supports no services The command completed successfully [oracle@oracle ~]$ tial -f /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml bash: tial: command not found... Similar command is: 'tail' [oracle@oracle ~]$ dbstart Processing Database instance "infodb": log file /data/oracle/product/11.2.0/startup.log [oracle@oracle ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:10:27 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 09-SEP-2022 02:07:11 Uptime 0 days 0 hr. 3 min. 16 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data/oracle/product/11.2.0/network/admin/listener.ora Listener Log File /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) Services Summary... Service "infodb" has 1 instance(s). Instance "infodb", status READY, has 1 handler(s) for this service... Service "infodbXDB" has 1 instance(s). Instance "infodb", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@oracle ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:22:24 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) The command completed successfully [oracle@oracle ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:22:34 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 2: No such file or directory Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused [oracle@oracle ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:22:40 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /data/oracle/product/11.2.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /data/oracle/product/11.2.0/network/admin/listener.ora Log messages written to /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 09-SEP-2022 02:22:40 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data/oracle/product/11.2.0/network/admin/listener.ora Listener Log File /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) The listener supports no services The command completed successfully [oracle@oracle ~]$ netca /silent /responseFile /home/oracle/response/netca.rsp\ > Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/response/netca.rsp Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Listener "LISTENER" already exists. Oracle Net Services configuration successful. The exit code is 0 [oracle@oracle ~]$ vim /home/oracle/response/netca.rsp [oracle@oracle ~]$ vim /home/oracle/response/netca.rsp [oracle@oracle ~]$ netca /silent /responseFile /home/oracle/response/netca.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/response/netca.rsp Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Listener "LISTENER" already exists. Oracle Net Services configuration successful. The exit code is 0 [oracle@oracle ~]$ vim /data/oracle/product/11.2.0/network/admin/ [oracle@oracle ~]$ vim /data/oracle/product/11.2.0/network/admin/listener.ora [oracle@oracle ~]$ netca /silent /responseFile /home/oracle/response/netca.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/response/netca.rsp Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Oracle Net Listener Startup: Running Listener Control: /data/oracle/product/11.2.0/bin/lsnrctl start LISTENER Listener Control complete. Listener started successfully. Listener configuration complete. Oracle Net Services configuration successful. The exit code is 0 [oracle@oracle ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:25:51 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 09-SEP-2022 02:25:31 Uptime 0 days 0 hr. 0 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data/oracle/product/11.2.0/network/admin/listener.ora Listener Log File /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) The listener supports no services The command completed successfully [oracle@oracle ~]$ lsnrctl statrt LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:27:15 Copyright (c) 1991, 2009, Oracle. All rights reserved. NL-00853: undefined command "statrt". Try "help" [oracle@oracle ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2022 02:27:20 Copyright (c) 1991, 2009, Oracle. All rights reserved. TNS-01106: Listener using listener name LISTENER has already been started [oracle@oracle ~]$ netstat -tnulp | grep 1521 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp6 0 0 :::1521 :::* LISTEN 5441/tnslsnr
2、异常关机（我是通过主动实现，因为我进去listener文件只有监听没有SID_LIST）
报错及解决思路：(该问题是由于缺少监听器的SID_LIST描述项引起的，采用netca进行配置的时候经常不会生成SID_LIST，导致连接出现问题)
Ora-12514：TNS：The listener is currently unable to recognize the service requested in the link descriptor
1、监听加载服务有主动和被动两种方式
主动是通过在listener.ora文件中添加代码来实现的。代码如下：
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = infodb) #根据自己的配置来 (ORACLE_HOME = /data/oracle/product/11.2.0) #根据自己的配置来 (GLOBAL_DBNAME = infodb) #根据自己的配置来 ) (SID_DESC = (GLOBAL_DBNAME = INITIALG)#根据自己的配置来 (ORACLE_HOME = /data/oracle/product/11.2.0)#根据自己的配置来 (SID_NAME = INITIALG)#根据自己的配置来 ) )
被动是由实例的pmon进程在listener中注册服务。
（解决办法：
1、把Oracle相关的服务关闭了；
lsnrctl stop
2、接着先启动监听服务（OracleOraDb11g_home1TNSListener）
lsnrctl start
3、后启动（OracleServiceORCL）可能会出现ORA-01034 - Oracle not available”和“ORA-27101 - shared memory realm does not exist”
SQL>sqlplus /nolog SQL>connect / as sysdba SQL>shutdown immediate; SQL> shutdown abort; SQL>sqlplus /nolog SQL>connect / as sysdba SQL>startup
下次就直接启动监听和oracleService就ok
如有错误或其他地方哪里不对，大家评论下方评论,及时改正
