Oracle逻辑备份

逻辑备份

expdp

备份恢复表空间

创建测试数据

# 创建表空间 
create tablespace itpux01 datafile '/oradata/fghsdb/itpux01.dbf' size 100m autoextend off extent management local 
autoallocate segment space management auto; 
create tablespace itpux02 datafile '/oradata/fghsdb/itpux02.dbf' size 100m autoextend off extent management local 
autoallocate segment space management auto; 
# 创建用户并授权 
create user itpux01 identified by itpux01 default tablespace itpux01;
create user itpux02 identified by itpux02 default tablespace itpux02;
grant dba to itpux01;
grant dba to itpux02;
alter user itpux01 quota unlimited on itpux01;
alter user itpux02 quota unlimited on itpux02;
#用户登录并创建测试表 
conn itpux01/itpux01;
create table itpux01 (id number(30) primary key not null,name varchar2(10)); 
insert into itpux01 values (1,'itpux01');
insert into itpux01 values (2,'itpux02');
insert into itpux01 values (3,'itpux03');
insert into itpux01 values (4,'itpux04');
insert into itpux01 values (5,'itpux05');
commit;
select * from itpux01.itpux01;

conn itpux02/itpux02;
create table itpux02 (id number(30) primary key not null,name varchar2(10)); 
insert into itpux02 values (1,'itpux01');
insert into itpux02 values (2,'itpux02');
insert into itpux02 values (3,'itpux03');
insert into itpux02 values (4,'itpux04');
insert into itpux02 values (5,'itpux05');
commit;
select * from itpux02.itpux02;

#删除表空间
drop tablespace itpux01 including contents and datafiles;
drop tablespace itpux02 including contents and datafiles;

数据泵导出导入

#获取DDL语句
select dbms_metadata.get_ddl ('TABLESPACE','ITPUX01') from dual; 
select dbms_metadata.get_ddl ('TABLESPACE','ITPUX02') from dual; 

#创建备份用户
create user backup identified by backup;
grant dba to backup;
create directory bak_dir as '/tmp';
grant read,write on directory bak_dir to backup;

#导出
nohup expdp backup/backup directory=bak_dir dumpfile=expdp_itpux_ts.dmp logfile=expdp_itpux_ts.log tablespaces=itpux01,itpux02 parallel=4 &
#导入
nohup impdp backup/backup directory=bak_dir dumpfile=expdp_itpux_ts.dmp logfile=impdp_itpux_ts.log tablespaces=itpux01,itpux02 parallel=4 &

rman备份表空间

#备份表空间
rman target /
Configure default device type to disk;
configure channel 1 device type disk format '/backup/orcl_%U.bak';
backup tablespace itpux01,itpux02 include current controlfile;
#恢复表空间
restore tablespace itpux01,itpux02;
recover tablespace itpux01 auxiliary destination '/backup';

参考资料

https://blog.csdn.net/qq_40768088/article/details/124266687

待研究

#grant DATAPUMP_EXP_FULL_DATABASE to backup;
#grant DATAPUMP_IMP_FULL_DATABASE to backup;

cat > expdp_itpux.par << "EOF"
userid=backup/backup 
directory=bak_dir 
dumpfile=expdp_itpux_ts.$BAKDATE.%U.dmp 
logfile=expdp_itpux_ts.$BAKDATE.log 
tablespaces=itpux01,itpux02
parallel=4 
EOF

cat > expdp.sh << "EOF"
export BAKDATE=`date +%Y%m%d`
expdp parfile=expdp_itpux.par
EOF

#后台执行
chmod +x expdp.sh
nohup ./expdp.sh &