参考文章: EXPDP 导出表结构
修改数据库运行模式
查看数据库当前运行模式
1
2
3
4
5
6SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1732
Current log sequence 1734关闭数据库，并启动到 mount 模式
1
2
3SQL> shutdown immediate;
SQL> startup mount;修改数据库运行模式为日志归档模式
1
SQL> alter database archivelog;
修改归档日志存放位置(可选)
1
2
3
4
5
6
7
8# 修改归档日志存放到本地目录中
SQL> alter system set log_archive_dest_1='location=/backup/oracle/archive';
# 修改归档日志存放到闪回去
SQL> alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
# log_archive_dest_1 与 log_archive_dest 和 LOG_ARCHIVE_DUPLEX_DEST 参数不兼容，如果设置了 log_archive_dest 参数，需要先将其清空，如下
SQL> alter system set log_archive_dest='';查看闪回区参数配置
1
SQL> show parameter db_recovery;
查询当前闪回区使用情况
1
2
3
4
5
6
7
8
9
10
11
12
13SQL> set linesize 2000;
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 29.01 28.35 605
FOREIGN ARCHIVED LOG 0 0 0查看闪回区是否开启
1
SQL> select flashback_on from v$database;
修改闪回区空间大小
1
SQL> alter system set db_recovery_file_dest_size=50g;
修改闪回区目录(可选)
1
SQL> alter system set db_recovery_file_dest='/home/oracle/flashback';
开启闪回区
1
2
3
4
5
6
7SQL> alter database flashback on;
````
11. 打开数据库
```sql
SQL> alter database open;再次检查数据库运行模式
1
2
3
4
5
6
7SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/oracle/archive
Oldest online log sequence 1735
Next log sequence to archive 1737
Current log sequence 1737
EXPDP/IMPDP 导入导出
expdp 查看帮助信息
1
expdp -help
impdp 查看帮助信息
1
impdp -help
准备工作
创建备份目录
1
SQL> create directory dump_dir as '/backup/oracle/dump_dir';
查看备份目录是否存在
1
SQL> select * from dba_directories;
创建表空间
1
SQL> create bigfile tablespace ahwms datafile '/u01/app/oracle/oradata/ahwms/ahwms.dbf' size 1G autoextend on next 1G maxsize unlimited;
创建用户并指定默认表空间
1
SQL> create user ahwms identified by "xxxxxxx" default tablespace ahwms;
授权用户角色
1
SQL> grant connect,resource to ahwms;
授权用户对备份目录的读写权限
1
SQL> grant read,write on directory dump_dir to ahwms;
查看用户在线会话
1
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='QDMWMS';
EXPDP 导出
确定需要导出的用户在哪些表空间，及其表初始化时占用的表空间大小
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30SELECT D.OWNER, D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent
FROM DBA_SEGMENTS D
WHERE D.owner IN (
SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')
) GROUP BY D.tablespace_name, D.OWNER
ORDER BY initial_extent desc;
-- 输出信息如下
OWNER TABLESPACE_NAME INITIAL_EXTENT
------------------------------ ------------------------------ --------------
HD40 HDAPP 5397.03125
HD40 HDAPPQRY 3.8125
HD40 USERS .390625
SELECT SUM(D.initial_extent)/1024/1024 initial_extent
FROM DBA_SEGMENTS D
WHERE D.owner IN (
SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')
);
-- 输出信息如下
INITIAL_EXTENT
--------------
5401.23438由此可以知道，创建这些元数据大约需要 5G 的空间，如果涉及到数据的话，还需要判断数据占用空间，这里一定需要判断这个，不然执行导入的时候会因为表空间不足而不能导入，我第一次导入的时候就是因为这里没有判断导致花费了很长的时间，我一直扩展表空间，但是就是就表空间不足的错误(ORA-01659),想想建表不会花这么大的空间的吧，最后查看了表的定义才知道，原来表初始化的时候就很大，这个问题后边还需要再处理一下的，不然测试库没法导入，当然存储够的话就另当别论了。
确定需要导出的用户中有哪些无效的对象、及总共需要导出的对象数量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46-- 查看对象总数
SELECT d.OWNER,count(1)
FROM dba_objects d
WHERE d.OWNER in (
SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')
) GROUP BY d.OWNER;
-- 输出信息如下
OWNER COUNT(1)
------------------------------ ----------
HD40 474
-- 查看有效以及无效对象各有多少
SELECT d.OWNER, d.status, count(1)
FROM dba_objects d
WHERE d.OWNER in (
SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')
) GROUP BY d.OWNER, d.status;
-- 输出信息如下
OWNER STATUS COUNT(1)
------------------------------ ------- ----------
HD40 VALID 473
HD40 INVALID 1
-- 查看无效的对象
SELECT d.OWNER, d.OBJECT_NAME, d.OBJECT_TYPE, d.status
FROM dba_objects d
WHERE d.status = 'INVALID'
AND d.owner IN (
SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')
);
-- 输出信息如下
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------- --------------------- -------------------------- ----------
HD40 LLP_BI PACKAGE BODY INVALID按 schema 导出
1
expdp tms/tms@helowin directory=dump_dir dumpfile=schema_tms.dmp schemas=tms logfile=export_tms.log
tms/tms@helowin: username/password@ORACLE_SID，当用户密码存在特殊字符时，格式为
'username/"password"'@ORACLE_SID导出指定表
1
2
3
4
5expdp 'qdmwms_pre/"qdmwms_pre"'@helowin \
directory=dump_dir \
dumpfile=schema_qdmwms_pre_report_temp.dmp \
tables=qdmwms_pre.report_temp \
logfile=export_qdmwms_pre.logexpdp 数据泵利用
content=metadata_only导出元数据，导出命令如下1
2
3
4
5
6expdp hd40/hd40 \
directory=dump_dir \
dumpfile=schema_user1_metadata.dmp \
logfile=expdp_hd40_metadata.log \
content=metadata_only \
schemas=hd40使用 impdp 将 DMP SQL 文件
1
2
3
4
5impdp hd40/hd40 \
directory=dump_dir \
dumpfile=schema_hd40_metadata.dmp \
logfile=impdp_hd40_metadata.log \
sqlfile=schema_hd40_metadata.sql
IMPDP 导入
注：使用 impdp 导入数据之前，建议先关闭连接数据库的应用，以及停止 Oracle 监听，防止在导入过程中有会话导致锁表或锁存储过程; 如果有外键约束，需要先禁用外键约束，导入完成后再启用外键约束；
重新导入序列
删除原有序列
1
2
3
4-- 1. 查询用户下所有的序列并生成删除语句
select 'drop sequence QDMWMS.' ||SEQUENCE_NAME ||' ;' from dba_sequences where sequence_owner='QDMWMS';
-- 2. 执行生成的删除序列语句导入序列
1
2
3
4
5
6
7impdp 'qdmwms/"123456"' \
directory=dump_dir \
dumpfile=schema_qdmwms_pre.dmp \
remap_schema=qdmwms_pre:qdmwms \
remap_tablespace=qdmwms_pre:qdmwms \
logfile=import_qdmwms_pre.log \
include=SEQUENCE;
触发器管理
查看触发器状态
1
select TRIGGER_NAME,OWNER,TABLE_NAME,STATUS FROM ALL_TRIGGERS WHERE OWNER = 'QDMWMS';
禁用所有触发器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19declare
v_owner varchar2(60) := 'qdmwms';
begin
for cur in (select t.TRIGGER_NAME from all_triggers t where t.OWNER = v_owner)
loop
execute immediate 'alter trigger '|| cur.trigger_name ||' disable';
end loop;
end;
/
declare
v_owner varchar2(60) := 'QDMWMS';
begin
for cur in (select t.TABLE_NAME from all_triggers t where t.OWNER = v_owner)
loop
execute immediate 'alter trigger '|| cur.table_name ||' disable all triggers';
end loop;
end;
/启用所有触发器
1
2
3
4
5
6
7
8
9declare
v_owner varchar2(60) := 'qdmwms';
begin
for cur in (select t.TRIGGER_NAME from all_triggers t where t.OWNER = v_owner)
loop
execute immediate 'alter trigger '|| cur.trigger_name ||' enable';
end loop;
end;
/
外键约束管理
禁用所有外键约束
1
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
启动所有外键约束
1
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
删除所有外键约束
1
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';
导入数据
完全导入
1
impdp 'tms/"123456"' directory=dump_dir dumpfile=schema_tms.dmp full=true logfile=import_tms.log
修改被导入数据库的用户与表空间
1
2
3
4
5
6
7impdp 'ahwms/"123456"' \
directory=dump_dir \
dumpfile=schema_ahwms_collect.dmp \
remap_schema=wms40:ahwms \
remap_tablespace=wmstest2:ahwms \
full=true \
logfile=import_ahwms_pre.logremap_schema=wms40:ahwms 源数据库与目标数据库的 schema 不同时，源数据库 schema 为 wms40，目标数据库 schema 为 ahwms；
remap_tablespace=wmstest2:ahwms 源数据库与目标数据库的表空间不同时，源数据库表空间为 wmstest2，目标数据库表空间为 ahwms；只导入数据，并排除某张表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23impdp 'qdmwms/"123456"' \
directory=dump_dir \
dumpfile=schema_qdmwms_pre.dmp \
remap_schema=qdmwms_pre:qdmwms \
remap_tablespace=qdmwms_pre:qdmwms \
exclude=TABLE:\"=\'WM_MIS_TRECVLOG\'\" \
content=data_only \
table_exists_action=truncate \
data_options=skip_constraint_errors \
logfile=import_qdmwms_pre.log
# 或者
impdp 'qdmwms/"123456"' \
directory=dump_dir \
dumpfile=schema_qdmwms_pre.dmp \
remap_schema=qdmwms_pre:qdmwms \
remap_tablespace=qdmwms_pre:qdmwms \
exclude=TABLE:\"IN \(\'WM_MIS_TRECVLOG\', \'SY_SENDMSG_INFO\'\)\" \
content=data_only \
table_exists_action=truncate \
data_options=skip_constraint_errors \
logfile=import_qdmwms_pre.log导入某张表的数据
1
2
3
4
5
6
7
8
9impdp 'qdmwms/"Qdm_123qaz"' \
directory=dump_dir \
dumpfile=schema_qdmwms_pre_report_temp.dmp \
tables=qdmwms_pre.report_temp \
remap_schema=qdmwms_pre:qdmwms \
remap_tablespace=qdmwms_pre:qdmwms \
content=data_only \
table_exists_action=truncate \
logfile=import_qdmwms_pre_report_temp.log
RMAN 备份管理
通常如果数据库不多，执行但数据库备份管理，可以在 Nocatalog 方式执行。一个有效的完整备份应该包括所有数据文件的全备份，备份期间产生的所有归档日志备份，控制文件的备份；
全备份的脚本可能如下:
1
2
3
4
5
6
7run{
allocate channel c1 type disk;
backup full tag 'dbfull' fromat '/path/full_%u_%s_%D' database include current controlfile;
sql 'alter system archivelog current';
backup fileaperset 3 foramt '/path/arch_%u_%s_%D' archivelog all delete input;
release channel c1;
}
注意：在数据库全备份完成之后，需要执行
alter system archive log current命令 将当前日志归档，备份归档日志时应该包括最后生成的归档。
从 Oracle 9i 开始，为了简化备份操作，Oracle 引入了一个新的命令 plus archivelog，当使用这个选项进行备份时，Oracle 将执行如下步骤简化以上的备份操作：
- 运行一个
alter system archivelog current命令; - 运行
backup archivelog all命令。注意，如果备份优化被启用，RMAN 只会备份未备份过的日志; - 备份
backup命令中定义的文件; - 运行
alter system archivelog current命令; - 备份所有的剩下的归档日志。
示例
创建完全备份
创建备份所需要的目录
1
2
3mkdir -p /backup/oracle/rman/{logs,scripts,backupfiles}
mkdir -p /backup/oracle/rman/backupfiles/{lev0,lev1}
chown -R oracle:oinstall /backup/oracle/rman/创建 RMAN level 0 备份命令文件
backup_level0.rcv1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29run {
# Hot database level 0 whole backup
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/oracle/rman/backupfiles/lev0/%F.bak';
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup as compressed backupset
incremental level 0 tag 'backup_level0'
skip inaccessible
format '/backup/oracle/rman/backupfiles/lev0/bak_%d_%T_%s_%p_db_level0'
database
plus archivelog
format '/backup/oracle/rman/backupfiles/lev0/arclogback_%s_%p_%t_%d'
delete input;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
release CHANNEL t1;
release CHANNEL t2;
release CHANNEL t3;
}创建 RMAN level 0 级备份的 shell 脚本
oracle_level0_backup.sh1
2
3
4
5
6
7
8
export ORACLE_SID=ahwms
export ORACLE_HOME=/u01/app/oracle/product/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export TIMESTAMP=`date +'%Y_%m_%d_%H_%M'`
rman target / nocatalog cmdfile=/backup/oracle/rman/scripts/backup_level0.rcv log=/backup/oracle/rman/logs/log_lev0_${TIMESTAMP}.log创建定时任务，每周日凌晨3点以 oracle 用户执行
oracle_level0_backup.sh脚本1
0 3 * * 0 oracle /backup/oracle/rman/oracle_level0_backup.sh
创建增量差异备份
创建 RMAN level 1 备份命令文件
backup_level1.rcv1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28run {
# Hot database level 1 whole backup
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/oracle/rman/backupfiles/lev1/%F.bak';
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup as compressed backupset
incremental level 1 tag 'subsdb_level1'
skip inaccessible
format '/backup/oracle/rman/backupfiles/lev1/bak_%d_%T_%s_%p_db_level1'
database plus archivelog
format '/backup/oracle/rman/backupfiles/lev1/arclogback_%s_%p_%t_%d'
delete input;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
release channel t1;
release channel t2;
release channel t3;
}创建 RMAN level 1 级增量备份的 shell 脚本
oracle_level1_backup.sh1
2
3
4
5
6
7
8
export ORACLE_SID=ahwms
export ORACLE_HOME=/u01/app/oracle/product/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export TIMESTAMP=`date +'%Y_%m_%d_%H_%M'`
rman target / nocatalog cmdfile=/backup/oracle/rman/scripts/backup_level1.rcv log=/backup/oracle/rman/logs/log_lev1_${TIMESTAMP}.log创建定时任务，周一到周六凌晨3点以 oracle 用户执行
oracle_level1_backup.sh脚本1
0 3 * * 1-6 oracle /backup/oracle/rman/oracle_level1_backup.sh