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

標籤：oracle

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、匯出自己的表（表的資料）

[[email protected]_test oracle]$ exp userid=scott/[email protected] 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，查看

[[email protected]_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，於是重新匯出

[[email protected]_test oracle]$ exp userid=scott/[email protected] 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.

[[email protected]_test oracle]$ ll /oracle/test/scott_emp.dmp 

-rw-r--r-- 1 oracle oinstall 16384 Sep 26 14:38 /oracle/test/scott_emp.dmp

匯出多張表

[[email protected]_test oracle]$ exp userid=scott/[email protected] 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

然後再在設定與之相同的字元集

[[email protected]_test oracle]$ export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

然後再匯出多張表的資料

[[email protected]_test oracle]$ exp userid=scott/[email protected] 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使用者的表

[[email protected]_test oracle]$ exp userid=system/[email protected] 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/[email protected] tables=emp file=/oracle/test/emp_table.dmp rows=n

4、使用直接匯出方式

exp userid=scott/[email protected] tables=emp file=/oracle/test/emp_direct.dmp direct=y

這種方式比預設的常規方式速度要快，當資料量大時，可以考慮使用此種方法

這種方法需要資料庫的字元集和用戶端的字元集完全一致，否則會報錯

匯出方案

匯出方案是指使用export工具匯出一個方案或是多個方案中的所有對象（表、視圖、索引等等）和資料，並將它存放到檔案中

1、匯出自己的方案

[[email protected]_test oracle]$ exp userid=scott/[email protected] 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/[email protected] owner=system,scott file=/oracle/test/system_scott.dmp

樣本表示使用system使用者不能匯出sys的schema

匯出整個資料庫

匯出資料庫是指利用export匯出所有資料庫中的對象及資料，要求該使用者具有dba的許可權

或者是exp_full_database許可權

exp userid=system/[email protected] full=y inctype=comlete file=/oracle/test/full_oracle.dmp

匯入表

1、匯入表

imp userid=scott/[email protected] tables=emp file=

類比刪除一張表，然後進行匯出資料？

[[email protected] ~]$ exp userid=scott/[email protected] 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/[email protected] 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/[email protected] 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/[email protected] tables=emp file=  rows=n

4、匯入資料

如果表的對象已經存在，那麼就可以只匯入資料，無需匯入對象

imp userid=scott/[email protected] tables=emp file=  ignore=y

匯入方案

匯入方案是指使用import工具將檔案中的對象和資料匯入到一個或多個方案中

如果要匯入其他使用者的方案，要求使用者具有dba的許可權，或者是imp_full_database的許可權

1、匯入自身的方案

imp userid=scott/[email protected] file=/oracle/test/schema_scott.dmp

2、匯入其他使用者的方案（利用system使用者進行操作）

imp userid=system/[email protected] file=/oracle/test/schema_system_scott.dmp fromuser=system touser=scott

匯入資料庫

在預設情況下，當匯入資料庫時，會匯入所有結構和資料

imp userid=system/redhat full=y file=/oracle/test/database.dmp

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