Oracle 11.2.0.4学习笔记
一、 移动控制文件不成功
拷贝了控制文件到指定目录，执行：
alter system set control_files='/u01/app/oradata/controlfile/control01.ctl','/u01/app/oradata/controlfile/control02.ctl' scope=spfile;
后启动报告控制文件错误
后来使用oracle用户拷贝文件后成功，应该是文件访问权限问题。
二、 怎么配置Oracle开机自动启动?
有文章说编辑/orcl/app/oracle/product/12.1.0/db_1/bindbstart，将ORACLE_HOME_LISTNER=$1修改成 ORACLE_HOME_LISTNER=$ORACLE_HOME 前提是$ORACLE_HOME环境设置正确
ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/dbhome_1
我看了一下，其实dbstart中接收传入参数，所以启动时传入$ORACLE_HOME即可
Dbstart $ORACLE_HOME
这样编辑/etc/oratab文件
dbca建库时都会自动创建/etc/oratab文件
将orcl:/orcl/oracle/product/12.1.0/db_1:N
修改成 orcl:/orcl/oracle/product/12.1.0/db_1:Y
如果没有这个文件则
以root身份进入$ORACLE_HOME
执行./root.sh
编辑/etc/rc.d/rc.local启动文件，添加数据库启动脚本dbstart
su oracle -lc "/orcl/app/oracle/product/12.1.0/db_1/bin/lsnrctl start"
su oracle -lc "/orcl/app/oracle/product/12.1.0/db_1/bin/dbstart $ORACLE_HOME"
su oracle -lc "/orcl/app/oracle/product/12.1.0/db_1/bin/emctl start dbconsole"
注意，oracle 12c中没有emctl了。
发现不生效，再一查，发现：
在CentOS7中,官方将/etc/rc.d/rc.local 的开机自启的权限禁止掉了,他为了兼容性,设置了这个,但是并不默认启动.如果需要的话.执行以下代码
chmod +x /etc/rc.d/rc.local
将文件授权,这样他就可以开机自启了.
官方在新版上推荐使用systemcd进行自启动.
三、 监听器怎么配置及远程访问?
- 需要关闭防火墙，一定记住
查看防火墙状态
firewall-cmd --state
停止firewall
systemctl stop firewalld.service
禁止firewall开机启动
systemctl disable firewalld.service
- 修改hosts文件
[root@oracle12c ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.112 oracle12c
- 修改监听器配置
[oracle@oracle12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /orcl/app
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
- 修改tns配置
[oracle@oracle12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /orcl/app
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
- 启动监听器
停止：lsnrctl stop
启动：lsnrctl start
查看：lsnrctl status
重载：lsnrctl reload
[oracle@oracle12c admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2019 18:40:14
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 17-MAY-2019 18:33:04
Uptime 0 days 0 hr. 7 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /orcl/app/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
四、 SQLPlus 在连接时通常有四种方式
1. sqlplus / as sysdba
操作系统认证，不需要数据库服务器启动listener，也不需要数据库服务器处于可用状态。比如我们想要启动数据库就可以用这种方式进入
sqlplus，然后通过startup命令来启动。
2. sqlplus username/password
连接本机数据库，不需要数据库服务器的listener进程，但是由于需要用户名密码的认证，因此需要数据库服务器处于可用状态才行。
3. sqlplus usernaem/password@orcl
通过网络连接，这是需要数据库服务器的listener处于监听状态。此时建立一个连接的大致步骤如下
a. 查询sqlnet.ora，看看名称的解析方式，默认是TNSNAME
b. 查询tnsnames.ora文件，从里边找orcl的记录，并且找到数据库服务器的主机名或者IP，端口和service_name
c. 如果服务器listener进程没有问题的话，建立与listener进程的连接。
d. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端
就连接上了数据库的server process。
e. 这时连接已经建立，可以操作数据库了。
4.sqlplus username/password@//host:port/sid
用sqlplus远程连接oracle命令(例：sqlplus risenet/1@//192.168.130.99:1521/risenet)
五、 临时表空间
一个实例中的临时表空间可以有多个，只能有一个是活动的
六、 重做日志和还原表空间
还原表空间可以有多个，但只能有一个是活动的。可以切换活动的还原表空间。
七、 数据库实例和数据库的关系
见外部文件单独记录。
八、 表空间
表空间可以有多个物理文件，如果一个满了，会自动往第二个文件上写吗?如果第一个设置成了自动增长呢?
多数据库情况下的数据库自启动、监听
- vi /etc/oratab
orcl:/orcl/app/oracle/product/12.1.0/db_1:Y
myorcl:/orcl/app/oracle/product/12.1.0/db_1:Y
- listener.ora
[oracle@oracle12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /orcl/app
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = myorcl)
(SID_NAME = myorcl)
)
)
- tnsnames.ora
[oracle@oracle12c admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /orcl/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
MYORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myorcl)
)
)
九、 数据完整性
SQL> select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from dba_constraints where owner='SCOTT';
Deferrable和deferred，搞不清……
十、 安全管理
创建用户
Create user xxh identified by 123;
修改权限
Alter user xxh indentified by 123456;
授权
Grant create session,select any table,create table,create view to xxh;
GRANT RESOURCE TO xxh;
oracle中如何赋予该用户CONNECT、RESOURCE、DBA身份
grant connect,resource,dba to user;
CONNECT角色： --是授予最终用户的典型权利，最基本的
CREATE SESSION --建立会话
RESOURCE角色： --是授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
CREATE OPERATOR --创建操作者
CREATE INDEXTYPE --创建索引类型
CREATE TABLE --创建表
DBA角色： --是授予数据库维护人员的
角色的口令
对于 oracle里角色的密码，就是 当你 SET Role 启用角色的时候，如果这个 角色是有密码的， 你需要输入 角色的密码， 来启用这个角色。
十一、 测试非归档模式下的冷备份和恢复
因为测试环境建立了多个表空间，并且移动了控制文件，表空间等也放在了不同的地方，在shutdown immediate后拷贝数据库文件、参数文件等时忘记了拷贝控制文件，结果在拷贝文件后删除原库数据，然后再把备份过的文件拷贝回原位置后，试图启动数据库startup时报告错误。于是先startup mount，然后执行：
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oradata/orcl/system01.dbf'
如果此时执行：
SQL> recover datafile '/u01/app/oradata/orcl/system01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'
因为测试环境没有需要重做的，于是：
SQL> alter database clear unarchived logfile group 2;
Database altered.
此时再逐个执行有问题的文件：
SQL> recover datafile '/u01/app/oradata/orcl/system01.dbf';
Media recovery complete.
…
最后
SQL> alter database open;
Database altered.
不清楚这种情况是不是控制文件没有备份引起的，是不是有更好的恢复办法，这样有什么问题。
十二、 数据导入导出
建立了一个导出配置文件：
[oracle@DB ~]$ cat scott_par.txt
DIRECTORY=DATA_PUMP_DIR
tables=emp_dump,dept_dump
DUMPFILE=SCOTT.dump
然后在导出时出现错误：
[oracle@DB ~]$ expdp scott/tiger parfile=scott_par.txt
Export: Release 11.2.0.4.0 - Production on Fri May 24 17:40:07 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid
查看设置：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ --------------------------------------------------------------------------------
SYS LOG_FILE_DIR /u01/app/cfgtoollogs/dbca/orcl/
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/DB/state
SYS DATA_PUMP_DIR /u01/app/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
9 rows selected.
当前目录存在，判断是权限问题。
授权：
SQL> conn / as sysdba
Connected.
SQL> grant read,write on directory DATA_PUMP_DIR to scott;
Grant succeeded.
然后执行导出，成功。
[oracle@DB ~]$ expdp scott/tiger parfile=scott_par.txt
Export: Release 11.2.0.4.0 - Production on Fri May 24 17:41:52 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** parfile=scott_par.txt
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."DEPT_DUMP" 5.937 KB 4 rows
. . exported "SCOTT"."EMP_DUMP" 8.570 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/admin/orcl/dpdump/SCOTT.dump
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 24 17:41:58 2019 elapsed 0 00:00:04
导入到其他用户
建立导出配置：
[oracle@DB ~]$ cat exp_par.txt
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=scott
DUMPFILE=schema-scott.dat
EXCLUDE=PACKAGE
EXCLUDE=VIEWE
EXCLUDE=TABLE:"LIKE '%DUMP'"
建立导入配置：
[oracle@DB ~]$ cat imp_par.txt
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=schema-scott.dat
REMAP_SCHEMA=SCOTT:IMPTEST
REMAP_TABLESPACE=USERS:PIONEER_DATA
注意，REMAP_SCHEMA=SCOTT:IMPTEST中的IMPTEST将会在数据库中建立新的用户IMPTEST，如果设置为已存在用户，比如PJINLIAN，则会导入到现用户下。
导出：
[oracle@DB ~]$ expdp system/oracle parfile=exp_par.txt
Export: Release 11.2.0.4.0 - Production on Fri May 24 18:21:56 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=exp_par.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/admin/orcl/dpdump/schema-scott.dat
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 24 18:22:08 2019 elapsed 0 00:00:12
导入：
[oracle@DB ~]$ impdp system/oracle parfile=imp_par.txt
"imp_par.txt"
Import: Release 11.2.0.4.0 - Production on Fri May 24 18:27:50 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=imp_par.txt
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "IMPTEST"."DEPT" 5.929 KB 4 rows
. . imported "IMPTEST"."EMP" 8.562 KB 14 rows
. . imported "IMPTEST"."SALGRADE" 5.859 KB 5 rows
. . imported "IMPTEST"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri May 24 18:27:54 2019 elapsed 0 00:00:03
Oracle中的文件目录
十三、 Oracle创建directory
一般创建directory都是为了用数据泵导入/导出数据用，其实directory还有很多别的用处。
1、新建directory的语法
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
例如：
create or replace directory dump_dir as 'D:dumpdir'
这样把目录d:dumpdir设置成dump_dir代表的directory
2、查询有哪些directory
select * from dba_directories
3、赋权
grant read,write on directory dump_dir to user01
4、删除
drop directory dump_dir