Oracle 数据泵

数据库和实例

Oracle数据库服务器由一个数据库和至少一个数据库实例组成。 数据库是一组存储数据的文件，而数据库实例是一组管理数据库文件的内存结构。 另外，数据库由后台进程组成。

一个数据库和一个实例是紧密相连的，因此术语 - Oracle数据库 通常用来指代实例和数据库。

物理存储结构

物理存储结构是存储数据的纯文件。当执行一个CREATE DATABASE语句来创建一个新的数据库时，将创建下列文件：

• 数据文件：数据文件包含真实数据，例如销售订单和客户。逻辑数据库结构(如表和索引)的数据被物理存储在数据文件中。
• 控制文件：每个Oracle数据库都有一个包含元数据的控制文件。元数据描述数据库的物理结构，包括数据库名称和数据文件的位置。
• 联机重做日志文件：每个Oracle数据库都有一个联机重做日志，其中包含两个或多个联机重做日志文件。联机重做日志由重做条目组成，记录对数据所做的所有更改。

除这些文件外，Oracle数据库还包括其他重要文件，如参数文件，网络文件，备份文件以及用于备份和恢复的归档重做日志文件。

逻辑结构

在逻辑结构中，Oracle从大到下，分别是如下的结构：数据库实例 -> 表空间 -> 数据段（表） -> 区 -> 块。

也就是说当我们要使用Oracle作为项目的数据库时，我们需要先创建数据库实例，之后创建表空间，再创建相对应的表（也就是逻辑结构中的数据段）。

数据块(Data blocks)：数据块对应于磁盘上的字节数。Oracle将数据存储在数据块中。数据块也被称为逻辑块，Oracle块或页。

范围(Extents)：范围是用于存储特定类型信息的逻辑连续数据块的具体数量。

段(Segments)：段是分配用于存储用户对象(例如表或索引)的一组范围。

表空间(Tablespaces)：数据库被分成称为表空间的逻辑存储单元。 表空间是段的逻辑容器。 每个表空间至少包含一个数据文件。

实例

Oracle实例是客户端应用程序(用户)和数据库之间的接口。Oracle实例由三个主要部分组成：系统全局区(SGA)，程序全局区(PGA)和后台进程。

创建数据库

-- 创建数据库文件
CREATE TABLESPACE hspipmptest LOGGING DATAFILE '/oracle/app/oracle/test/hspipmptest.dbf' SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;
# 创建数据库临时文件
create temporary tablespace hspipmptest_temp tempfile '/oracle/app/oracle/test/hspipmptest_temp.dbf' size 100m autoextend on next 32m maxsize 500m extent management local;
# 创建用户与上述两个文件形成映射关系
CREATE USER hspipmptestopr IDENTIFIED BY paic1234 DEFAULT TABLESPACE hspipmptest TEMPORARY TABLESPACE hspipmptest_temp;
/*
username：用户名
password：密码
MyDataBase：映射的数据库名称
MyDataBase_temp：映射的数据库临时文件名称
*/
# 添加用户权限
grant connect,resource,dba to hspipmptestopr;
grant create session to hspipmptestopr;
grant create table to hspipmptestopr;
# 删除数据库
conn sys/dwh as sysdba;
drop tablespace MyDataBase including contents and datafiles;
drop tablespace MyDataBase_temp including contents and datafiles;
-- 删除用户
drop user username cascade;
SELECT username FROM dba_users;

-- 查询数据库实例
select INSTANCE_NAME from v$instance;

-- 查询当前用户拥有的所的有表空间
select tablespace_name from user_tablespaces;
-- 查询用户拥有哪里权限：
select * from user_sys_privs;–针对当前登陆用户被授予的系统权限
select * from dba_sys_privs; –针对所有用户被授予的系统权限
select * from dba_role_privs;
select * from role_sys_privs;

-- 创建新的实例
-- 首先设置要创建的ORACLE的SID
export ORACLE_SID=hspipmptest
-- 创建相应目录
mkdir -p $ORACLE_HOME/admin/hspipmptest/{a,b,c,u}dump
mkdir -p $ORACLE_HOME/admin/hspipmptest/pfile
mkdir -p $ORACLE_HOME/oradata/hspipmptest

3、在$ORACLE_HOME/dbs目录下创建初始化文件
命名方法：init实例名.ora 本例中initaddb.ora

[oracle@oracle12c ~]$ cd $ORACLE_HOME/hspipmptest

[oracle@oracle12c dbs]$ cp init.ora initaddb.ora

[oracle@oracle12c dbs]$ vi initaddb.ora

db_name='addb'
memory_target=1G
processes = 150
audit_file_dest='/usr/local/Oracle/admin/addb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/usr/local/Oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/usr/local/Oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

注意不要用原来的<ORACLE_BASE>作为路径，修改为对应的绝对路径，否则会报错

4、创建密码文件
[oracle@oracle12c dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwaddb password=p@ssw0rd entries=5 force=y

有复杂度要求

5、创建oracle的建库脚本 createdb.sql,内容如下，将其放在了$ORACLE_BASE/oradata/addb下面
[oracle@oracle12c dbs]$ cd $ORACLE_BASE/oradata/addb

[oracle@oracle12c addb]$ vi createdb.sql

CREATE DATABASE addb
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/usr/local/Oracle/oradata/addb/system01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited extent management local
sysaux datafile '/usr/local/Oracle/oradata/addb/sysaux01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited
default temporary tablespace TEMP tempfile '/usr/local/Oracle/oradata/addb/temp01.dbf' size 20m reuse autoextend on next 640k maxsize unlimited
undo tablespace UNDOTBS1 datafile '/usr/local/Oracle/oradata/addb/undo01.dbf' size 20m reuse autoextend on next 5M maxsize unlimited
logfile
GROUP 1 ('/usr/local/Oracle/oradata/addb/redo1.dbf') size 10m,
GROUP 2 ('/usr/local/Oracle/oradata/addb/redo2.dbf') size 10m,
GROUP 3 ('/usr/local/Oracle/oradata/addb/redo3.dbf') size 10m
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
;

-- 查看监听状态
lsnrctl status;

oracle存储过程调用

如果没有参数直接call就可以了

call procName(param1);

另外一种是需要申明入参和出参:

DECLARE
out_exit_code NUMBER;
mydate DATE;
BEGIN
mydate:=sysdate;
packageName.procName(mydate,out_exit_code)；
END;

Oracle数据泵（Dump）导出、导入

导出、导入前准备
注：使用sys、system等系统用户操作！
1）创建DIRECTORY目录对象

CREATE DIRECTORY exp_dir AS '/oracle/db/zone'; --存放dmp、log文件

2）为使用户user_local授权该目录

GRANT READ, WRITE ON DIRECTORY exp_dir TO user_local;

本地导出、本地导入
1：本地导出
1）导出指定表

expdp user_local/user_local DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log TABLES=USER_OFFICE  
--TABLES=表1,表2,表3等

2）导出整库（方案）

expdp user_local/user_local DIRECTORY=exp_dir  DUMPFILE=tab.dmp LOGFILE=tab.log SCHEMAS=user_local  
--SCHEMAS=用户（所有可操作表等）

3）导出全库

expdp system/orcl DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log FULL=Y

2：本地导入
1）导入指定表

Impdp user_local/user_local DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log TABLE_EXISTS_ACTION=APPEND TABLES=USER_OFFICE

说明：TABLES=USER_OFFICE ：可以不写
TABLE_EXISTS_ACTION=APPEND ：追加数据，数据不重复可多次导入
当TABLE_EXISTS_ACTION=REPLACE时，则全量导入，该方法会直接删除表，然后重新创建该表并将数据全部写入

2）导入整库（方案）

impdp user_local/user_local DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log SCHEMAS=user_local

3）导入全库

impdp system/orcl DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log FULL=Y

不落地导入
1：说明
远程数据库数据导入本地数据库！
1）远程数据库：

IP：192.168.1.200
用户名：user_remote
密码：user_remote_
实例名：orcl
表空间：remote_space

2）本地数据库：

用户名：user_local
密码：user_local
表空间：local_space

2：本地增加监听(REMOTE_ORCL)

REMOTE_ORCL =  
  (DESCRIPTION =  
    (ADDRESS_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))  
    )  
    (CONNECT_DATA =  
      (sid = orcl)  
    )  
  )

3：创建远程链接database link（source_db_link）

create database link source_db_link connect to user_remote identified by user_remote_ using 'REMOTE_ORCL'

REMOTE_ORCL ：上一步增加的监听名

4：远程库表（USER_OFFICE）导入本地库

impdp  user_local/user_local directory=exp_dir  logfile=tab.log network_link=source_db_link  TABLE_EXISTS_ACTION=APPEND 
tables=user_remote.USER_OFFICE remap_schema=user_remote:user_local REMAP_TABLESPACE=remote_space:local_space

注：若提示权限不足，可用sys系统用户将dba权限赋予本地用户user_local即可解决，或者将导入的用户连接改为‘system/orcl’
1）network_link=source_db_link ：上一步建立的连接名
2）TABLE_EXISTS_ACTION=APPEND ：追加数据，不重复数据可多次导入
注：可以将‘APPEND’改为‘REPLACE’，该方法将删除原表，然后重新创建该表并导入数据
3）tables=user_remote.USER_OFFICE ：表名，多张表用‘,’隔开
4）remap_schema=user_remote:user_local ：不同用户之间的数据搬移，写法：远程用户:本地用户，如果相同可以不写
5）REMAP_TABLESPACE=remote_space:local_space ：不同表空间之间数据的搬移，写法：远程表空间名:本地表空间名，如果相同可以不写