一、怎么关闭启动数据库
1.经典模式关闭启动数据库
a.启动oracle步骤:
1.启动监听
[oracle@o12c ~]$ lsnrctl start
[oracle@o12c ~]$ echo $ORACLE_SID #查看当前的数据库是什么,要想改变数据库用export ORACLE_SID=haha
[oracle@o12c ~]$ sqlplus / as sysdba;
SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 26 01:11:54 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
此时出现了connected to an idle instance这个问题,会有两种原因:
原因一:数据库没有启动
原因二:此时你要连接的数据库名不存在或者错误
2.那么,解决的话就是再启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 8625032 bytes
Variable Size 553649272 bytes
Database Buffers 163577856 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
b.关闭oracle步骤:
1.关闭数据库
SQL> shutdown immediate
2.关闭监听
[oracle@o12c ~]$ lsnrctl stop
2.传统模式关闭启动数据库
a.启动oracle步骤:
1.启动监听
[oracle@o12c ~]$ lsnrctl start
2.启动数据库时,pdb不会随之启动,状态还是monuted
[oracle@o12c ~]$ sqlplus / as sysdba;
SQL>startup
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HXPDB MOUNTED
3.启动pdb,状态由Mounted变为read write
SQL> alter pluggable database all open; #pdb关闭命令alter pluggable database all close;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HXPDB READ WRITE NO
b.关闭oracle步骤:
1.关闭数据库(关闭数据库后pdb也关闭,此时pdb状态变为mounted)
SQL> shutdown immediate
2.关闭监听:
[oracle@o12c ~]$ lsnrctl stop
二、oracle可视化管理软件的安装
1.B/S架构:在浏览器进入em可以进行可视化管理
在Oracle 12c的版本中,引入了简化的EM版本,这一版本被称为:EM Database Express 12c。 Express版本的EM对之前的EM做出了大量的简化,使之更加轻量级(整个部署的空间需求在50M ~ 100M左右),更加适合用于轻量级的数据库监控和性能管理。
EM Express配置非常简单,底层需要XML DB的支持,在Oracle 12c中,XDB是强制安装的,所以在数据库安装完成之后,只需要确认基本的XDB配置,设置端口即可启用EM Express,以下配置8080端口作为EM Express的监听端口:
[oracle@o12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 16 22:46:15 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter dispatchers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=testXD
B)
max_dispatchers integer
SQL> exec DBMS_XDB_CONFIG.setHTTPPort(8080);
PL/SQL procedure successfully completed.
#在浏览器输入http://192.0.2.20:8080/em/login进入登陆管理界面,界面如下:
2.C/S架构:安装客户端软件进行可视化管理数据库(此时监听要开启)
安装之后一定要以管理员身份运行
会遇到的问题客户端与服务端版本不一致,解决方案:
[oracle@o12c ~]$ cd $ORACLE_HOME/network/admin
[oracle@o12c ~]$ vim sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVE=8
[orale@o12c admin]$ sqlplus / as sysdba
SQL> grant dba,connect,resource to test identified by oracle;
#然后用test用户可在客户端上连接服务器的oracle
三.图形化管理工具安装
1.pl/sql Developer:以管理员身份运行
2.Navicat
四、创建cdb容器数据库
oracle12c的新特性:可插拔式数据库
Oracle 12C引入了CDB与PDB的新特性,在ORACLE 12C数据库引入的多租用户环境(Multitenant Environment)中,允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。CDB全称为Container Database,中文翻译为数据库容器,PDB全称为Pluggable Database,即可插拔数据库。
1.创建cdb容器数据库,和pdb(hxpdb)
打开xmanager里的passive产生一个客户端和xshell
[root@o12c ~]# xhost +
access control disabled, clients can connect from any host
[root@o12c ~]# export DISPLSY=192.168.56.1:0.0
[root@o12c ~]# exit
logout
[oracle@o12c ~]$ dbca #创建cdb容器数据库,进入创建数据库界面
2.选择连接:最后都是连接pdb。此传统连接的两种方法都可适用于经典连接
连接方法一:配置环境变量的ORACLE_SID数据库名进行连接
[oracle@o12c ~]$ echo $ORACLE_SID
test
[oracle@o12c ~]$ export ORACLE_SID=orcl
[oracle@o12c ~]$ echo $ORACLE_SID
orcl
[oracle@o12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 25 22:05:32 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show con_name; #查看连接名
CON_NAME
------------------------------
CDB$ROOT
SQL> select name,open_mode from v$database; #查看数据库名
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> show pdbs; #查看都有哪些pdb
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HXPDB READ WRITE NO
SQL> alter session set container=HXPDB; #将会话切换到pdb(先去全局再去局部)
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
HXPDB
连接方法二:使用连接串去连接数据库(在这个tnsnames.ora文件里添加连接串),不吃环境变量ORACLE_SID
[oracle@o12c ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@o12c ~]$ sqlplus sys/pineyang000@orcl as sysdba;
#其中@后面的orcl匹配的是$ORACLE_HOME/network/admin的tnsnames.ora文件里面的端口,主机,服务名
#若改变连接串的服务名为HXPDB,则sqlplus sys/pineyang000@orcl as sysdba之后show con_name的是HXPDB
#与切换会话进入pdb方法一样
[oracle@o12c ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HXPDB)
)
)
[oracle@o12c ~]$ sqlplus sys/pineyang000@orcl as sysdba;
SQL> show con_name;
HXPDB
五、oracle12c快捷方式使用
1.三个重要的目录
$ORACLE_HOME
$ORACLE_HOME/dbs
$ORACLE_HOME/network/admin
六、Oracle用户使用
1.经典模式下创建账号:
SQL> create user oca identified by oracle;
SQL> grant connect,resource to oca;
SQL> conn oca/oracle
SQL> select * from tab;
2.在传统模式下创建账号:在Oracle 12C中,账号分为两种,一种是公用账号,一种是本地账号(亦可理解为私有账号)。共有账号是指在CDB下创建,并在全部PDB中生效的账号,本地账号另一种是在PDB中创建的账号。
针对这两种账号的测试如下:
1.创建本地账号:在PDB中创建测试账号
SQL> alter session set container=HXPDB;
SQL> create user cp1 identified by oracle;
SQL> grant connect,resource to cp1;
SQL> conn cp1/oracle@hxpdb
2.创建共有账号:在CDB中创建测试账号
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##cp2 identified by oracle;
SQL> grant dba to c##cp2;
结论:
如果在PDB中已经存在一个用户或者角色,则在CDB中不能创建相同的账号或者角色名。
同样在CDB中创建账号后不能在PDB中出现同名的账号,因CDB中的账号对所有的PDB都是有效的。
七、生成测试数据库:Oracle 12C实验测试数据导入,登陆PDB数据库执行脚本。
1.在之前创建容器数据库cdb时,就添加了一个实例模板,当时打了个对勾,里面有一个用户hr
SQL> alter session set container=HXPDB
SQL> alter user hr identified by pineyang000 account unlock;
SQL> exit
[oracle@o12c ~]$ sqlplus hr/pineyang000@orcl
SQL> show user;
USER is "HR"
SQL> select * from tab; #有8张表
2.经典模式生成测试数据库: $ORACLE_HOME/rdbms/admin目录下的脚本utlsampl.sql
1.在ORACLE_HOME/rdbms/admin进入sqlplus / as sysdba执行@utlsampl.sql
2.进入sqlplus / as sysdba执行@/uo1/app/oracle/12c/db_1/rdbms/admin/utlsqmpl.sql
3.进入sqlplus / as sysdba执行@?/rdbms/admin/utlsampl.sql(其中?代表ORACLE_HOME)
4.进入sqlplus / as sysdba复制粘贴ORACLE_HOME/rdbms/admin/utlsampl.sql 里的内容执行。
[oracle@o12c ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@o12c admin]$ cat utlsampl.sql
DROP USER SCOTT CASCADE;
DROP USER ADAMS CASCADE;
DROP USER JONES CASCADE;
DROP USER CLARK CASCADE;
DROP USER BLAKE CASCADE;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
DROP PUBLIC SYNONYM PARTS;
CONNECT SCOTT/tiger
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
EXIT
[oracle@o12c ~]$ sqlplus / as sysdba
将utlsampl.sql脚本中的内容粘贴运行下
[oracle@o12c ~]$ sqlplus / as sysdba
SQL> conn scott/tiger
Connected.
SQL> show user;
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-------------------- ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> col tname for a20; #格式化列