Oracle之管理以及exp、imp的使用

4321

oracle管理、备份与恢复

管理数据库的用户是sys、system，可以将sys比作董事长，system比作总经理，两者有如下区别
1、最重要的区别，存储的数据的重要性不一样
sys:所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行
是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba、sysdba、sysoper
角色或权限，是oracle权限最高的用户
system：用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有
dba、sysdba角色或系统权限
2、其次的区别，权限的不同
sys用户必须以as sysdba或as sysoper形式登录，不能以正常的方式登录数据库
system如果正常登录，它其实就是一个普通dba用户，但是如果as sysdba登录，其结果实际上是作为sys用户登录的
从登录信息里面可以看出来

sysdba和sysoper的相同与不同，none为没有，也就是不同
startup数据库                                        startup
shutdown数据库                                       shutdown
alter database open/mount /backup     alter database open/mount /backup
改变字符集                                            none
create database（创建数据库）                            none
drop database（删除数据库）                              none
create spfile                                        create spfile
alter database archivelog(归档日志)   alter database archivelog(归档日志)
alter database recovery恢复数据库     只能完全恢复，不能执行不完全恢复
拥有restricted session会话限制        拥有restricted session会话限制
可以让用户作为sys用户连接              可以进行一些基本的操作，但不能查看用户数据
登录之后用户是sys                     登录之后用户是public

dba权限的用户
dba用户是指具有dba角色的数据库用户，特权用户可以执行启动实例，关闭实例等特殊操作
而dba用户只能在启动数据库后才能执行各种管理操作

管理初始化参数
显示初始化参数
1、show parameter 显示系统参数
SQL> show parameter;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
active_instance_count                integer     
aq_tm_processes                      integer     0
archive_lag_target                   integer     0
asm_diskgroups                       string      
asm_diskstring                       string      
asm_power_limit                      integer     1


Oracle数据库的备份与恢复
逻辑备份是指使用工具export将数据对象的结构和数据导出文件的过程（数据导出到磁盘），逻辑恢复
是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程
物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行

导出：
导出具体分为：导出用户某张表，导出各用户schema，导出整个数据库三种方式
导出使用exp命令来完成，下面是常见的选项
userid：用于指定执行导出操作的用户名，口令，连接字符串
tables：用于指定导出操作的表
owner：用于指定执行导出操作的方案
full=y：用于指定执行导出操作的数据库
inctype：用于指定执行导出操作的增量类型
rows：用于指定执行导出操作是否要导出表中的数据
file：用于指定导出文件名
导出表：
1、导出自己的表（表的数据）
[oracle@aliyun_test oracle]$ exp userid=scott/redhat@test tables=emp file=/oracle/test/scott_emp.dmp

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 14:33:45 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
EXP-00000: Export terminated unsuccessfully
导出报错，然后进行确定Oracle实例是否是test，查看
[oracle@aliyun_test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = aliyun_test)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11g.us.oracle.com)
    )
  )
Oracle实例是ORCL11G，于是重新导出
[oracle@aliyun_test oracle]$ exp userid=scott/redhat@orcl11g tables=emp file=/oracle/test/scott_emp.dmp      

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 14:38:11 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@aliyun_test oracle]$ ll /oracle/test/scott_emp.dmp
-rw-r--r-- 1 oracle oinstall 16384 Sep 26 14:38 /oracle/test/scott_emp.dmp

导出多张表

[oracle@aliyun_test oracle]$ exp userid=scott/redhat@orcl11g tables=emp,dept file=/oracle/test/scott_emp_dept.dmp

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 15:10:05 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.    这里出现警告信息，查出是字符集的问题
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
字符集不匹配导致的，首先查询数据库的字符集
SQL> select sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
然后再在设置与之相同的字符集
[oracle@aliyun_test oracle]$ export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
然后再导出多张表的数据
[oracle@aliyun_test oracle]$ exp userid=scott/redhat@orcl11g tables=emp,dept file=/oracle/test/scott_emp_dept.dmp

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 15:11:56 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
. . exporting table                           DEPT          4 rows exported
Export terminated successfully without warnings.
发现警告消失了，成功解决警告信息

上述执行的是Scott用户导出自己的表，下面介绍用system用户来导出scott用户的表

[oracle@aliyun_test oracle]$ exp userid=system/redhat@orcl11g tables=scott.emp file=/oracle/test/system_emp.dmp  

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 15:17:54 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.
只需要修改相应的表信息就行

导出表的结构，只导出结构

3、导出表的结构
exp userid=scott/redhat@orcl11g tables=emp file=/oracle/test/emp_table.dmp rows=n

4、使用直接导出方式
exp userid=scott/redhat@ocrl11g tables=emp file=/oracle/test/emp_direct.dmp direct=y
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用此种方法
这种方法需要数据库的字符集和客户端的字符集完全一致，否则会报错

导出方案
导出方案是指使用export工具导出一个方案或是多个方案中的所有对象（表、视图、索引等等）和数据，并将它存放到文件中
1、导出自己的方案
[oracle@aliyun_test oracle]$ exp userid=scott/redhat@orcl11g owner=scott file=/oracle/test/schema_scott.dmp

Export: Release 11.2.0.1.0 - Production on Mon Sep 26 15:22:27 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                        STUDENT          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

2、导出其他的方案
如果用户要导出其他方案，则需要dba的权限或是exp_full_database的权限，例如用system用户
就可以导出任何方案
exp userid=system/redhat@orcl11g owner=system,scott file=/oracle/test/system_scott.dmp
示例表示使用system用户不能导出sys的schema


导出整个数据库
导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限
或者是exp_full_database权限
exp userid=system/redhat@orcl11g full=y inctype=comlete file=/oracle/test/full_oracle.dmp

导入表
1、导入表
imp userid=scott/redhat@orcl11g tables=emp file=
模拟删除一张表，然后进行导出数据？
[oracle@oracle11g ~]$ exp userid=scott/redhat@orcl11g tables=student
file=/oracle/test/student.dmp
SQL> drop table student;
Table dropped
SQL> desc student;
Object student does not exist
然后imp进行导入数据
$ imp userid=scott/redhat@orcl11g tables=student file=/oracle/test/student.dmp
最后查看表是否成功导入？
SQL> desc student;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
XH       NUMBER(4)    Y                        
XM       VARCHAR2(20) Y                        
SEX      CHAR(2)      Y                        
SAL      NUMBER(7,2)  Y                        
BIRTHDAY DATE         Y
成功导入表数据
2、用system执行导入数据

模拟删除
SQL> drop table student;
Table dropped
然后导入表数据
$ imp userid=system/redhat@orcl11g tables=student file=/oracle/test/student.dmp  touser=scott
然后查看
SQL> desc student;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
XH       NUMBER(4)    Y                        
XM       VARCHAR2(20) Y                        
SEX      CHAR(2)      Y                        
SAL      NUMBER(7,2)  Y                        
BIRTHDAY DATE         Y

3、导入表的结构
只导入表的结构而不导入数据
imp userid=scott/redhat@orcl11g tables=emp file=  rows=n


4、导入数据
如果表的对象已经存在，那么就可以只导入数据，无需导入对象
imp userid=scott/redhat@orcl11g tables=emp file=  ignore=y

导入方案
导入方案是指使用import工具将文件中的对象和数据导入到一个或多个方案中
如果要导入其他用户的方案，要求用户具有dba的权限，或者是imp_full_database的权限
1、导入自身的方案
imp userid=scott/redhat@orcl11g file=/oracle/test/schema_scott.dmp
2、导入其他用户的方案（利用system用户进行操作）
imp userid=system/redhat@orcl11g file=/oracle/test/schema_system_scott.dmp fromuser=system touser=scott


导入数据库
在默认情况下，当导入数据库时，会导入所有结构和数据
imp userid=system/redhat full=y file=/oracle/test/database.dmp