rman备份恢复相关

---------------------全备

RMAN_LOG_FILE=orcl_full_`date +%Y%m%d`.log
export ORACLE_SID=orcl
rman target / log=/data/oracle/rmanbackup/ORCLBAK/$RMAN_LOG_FILE append shutdown immediate;

SQL> startup;

------------------------------------------------------exp相关

USERID='/ as sysdba'
directory=dmp
dumpfile=xxxxxx_uat0726_%U.dmp
logfile=xxxxxxxxxx_user0726.log
parallel=8
schemas=(xxxxxxx,xxxx)
cluster=N
compression=all
exclude=STATISTICS

空间不够，导出到asm磁盘，创建目录obak create directory obak as '+data/obak';

USERID='/ as sysdba'
directory=obak
dumpfile=xxxxxx_uat0726_%U.dmp
logfile=dmp:xxxxxxx_user0726.log
parallel=8
schemas=(xxxxxxx,xxxx)
cluster=N
compression=all
exclude=STATISTICS

exclude=table:"in('xxxxxxxxxx','xxxxx'),STATISTICS

------导入

USERID='/ as sysdba'
directory=dmp
logfile=xxxxxxx_user0105.log
dumpfile=xxxxxxx_osit0104_%U.dmp
parallel=8

cluster=N

exclude=table:"in('xxxxxxxxxx','xxxxx'),STATISTICS

remap_schema=xxxxx:xxxxxx0101

-通过dblink导入

USERID='/ as sysdba'
directory=dmp
logfile=xxxxxxxx_user0519.log
NETWORK_LINK=xxxxxx
parallel=8
schemas=(xxxx,xxxxxxxxx)
cluster=N

-----导入指定表

USERID='/ as sysdba'
directory=dmp
logfile=xxxxxxxx_mtableesp0105.log
dumpfile=xxxxxxxx_sit_0104.dmp
tables=(
xxxxx.xxxxxxxxxxxxxxxxxxxxx,
xxxxx.xxxxxxxxxxxxxxxxxxxxxxxx)
remap_schema=xxxx:xxxxxxxxxxxxxxxxxxxxxxx
EXCLUDE=STATISTICS 

---------------------------------------------导入导出分区表exp

nohup exp xxxx/"xxxxxx"@xxxxxx file=xxxxxx122508.dmp tables=xxxx.xxxxxx:xxxxxxx_2023M9,xxxxx.xxxxxxx:xxxxxxx_2023M10 &

nohup imp xxxx/"xxxxxxx" file=/data/oracle/dumpdir/xxxxxxxxx07.dmp log=/data/oracle/dumpdir/log/xxxxxxx.log tables=xxxx:xxxxxxx_2023Q3,xxxxx:xxxxxx_2023Q4 buffer=40960000 commit=y fromuser=xxxx touser=xxxxxx ignore=y &

------------序列

impdp "'/ as sysdba'" directory= dumpfile=xxxxxx_0104.dmp logfile=xxxxxsequence_0104.log sqlfile=xxxxxxx_seq.sql include=sequence 

------------------收集统计信息

exec dbms_stats.gather_schema_stats(ownname=>'xxxxxxx',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>16,cascade=>true,granula
rity=>'ALL');

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'xxxxxxx',tabname=>'xxxxxx',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16,no_invalidate=>false);

-- no_invalidate=>false 立即失效

-----------------------sqlldr2

cd $ORACLE_HOME/bin
chmod 775 sqluldr2_linux64_10204.bin
ln -s sqluldr2_linux64_10204.bin sqluldr2

sqluldr2 xxxxx/xxxxxxxx@xxxxxx:1521/orcl sql=/data/dumpdir/xxxx.sql head=yes file=/data/dumpdir/xxxxxx0227.csv log=/data/dumpdir/xxxxx0202.log  

sqlldr userid=xxxx/xxxxxxx@xxxxxx:1521/orcl  control=xxxxxx.ctl direct=true 

options(skip=1,columnarrayrows=20971520,rows=10000,readsize=20971520,errors=999999999)
load data
CHARACTERSET AL32UTF8
infile '/data/dumpdir/xxxxx.csv'
insert into table "xxxxx"
fields terminated by ','
Optionally enclosed by '\''
(xxxx)

---------有换行符时

UPDATE xxxx SET xxxx = REPLACE(v_sn, CHR(13), '');