邏輯備份

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 
EOFcat > expdp.sh << "EOF"
export BAKDATE=`date +%Y%m%d`
expdp parfile=expdp_itpux.par
EOF#后臺執行
chmod +x expdp.sh
nohup ./expdp.sh &