---------------------全备
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 <<EOF
crosscheck backup;
crosscheck archivelog all;
crosscheck copy;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup as compressed backupset INCREMENTAL LEVEL 0 FILESPERSET 10 FORMAT '/data/oracle/rmanbackup/ORCLBAK/orcl_data_%T_%s' database;
backup format '/data/oracle/rmanbackup/ORCLBAK/orcl_cont_%s_%T' current controlfile;
backup archivelog all format '/data/oracle/rmanbackup/ORCLBAK/orcl_archivelog_%U.bak';
BACKUP FORMAT '/data/oracle/rmanbackup/ORCLBAK/orcl_spfile_%s_%p_%t' SPFILE;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
exit;
EOF
-------------------------恢复
restore controlfile from '/data/oracle/rmanbackup/ORCLBAK/orcl_cont_9574_20230718';
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore database;
recover database;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
-------------
catalog start with '/data/oracle/rmanbackup/ORCLBAK';
recover database until scn 12367183938339;
---恢复归档
restore ARCHIVELOG until sequence 97;
set archivelog destination to '/data/oracle/dmp';
restore archivelog from sequence 114814 until sequence 114893;
restore archivelog from sequence 155600 thread 2 until sequence 155700 thread 2;
restore ARCHIVELOG FROM TIME "to_date('01/17/22 16:30:01','MM/DD/YY HH24:MI:SS')" UNTIL TIME "to_date('01/17/22 23:40:01','MM/DD/YY HH24:MI:SS')";
--------注册归档
alter database register logfile '/data/oracle/dmp/2_73997_1018702018.dbf';
begin
for i in 139830..139858 loop
execute immediate 'alter database register logfile ''/data/oracle/dmp/2_'||i||'_1074284574.dbf''';
end loop;
end;
/
---------------------------------------nbu恢复相关
/usr/openv/netbackup/bin/bplist -S xxxxxxxxxxxxxx -C xxxxxxxxxx -t 4 -b -l -R / #####-k 策略名
run
{
allocate channel t1 type 'sbt_tape' ;
send 'NB_ORA_CLIENT=xxxxxxx,NB_ORA_SERV=xxxxxxxxxxxxxx';
restore primary controlfile from 'cntrl_1741_1_1119836214';
release channel t1;
}
date
rman target / log=/home/oracle/restore_orcl0424.log <<EOF
RUN{
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxx)';
ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxx)';
ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxxxxxx)';
ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxx)';
SEND DEVICE TYPE 'SBT_TAPE' 'NB_ORA_SERV=xxxxxxxxxxxxxxxxxxxxxx';
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
SET UNTIL TIME ='2023-04-17 00:00:00';
set newname for database to '/data/oracle/oradata/ORCL/%b';
restore database;
release channel CH1;
release channel CH2;
release channel CH3;
release channel CH4;}
exit;
EOF
date
rman>switch database to copy;
date
rman target / log=/home/oracle/recover_orcl0424.log <<EOF
RUN{
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxxxxx)';
ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxxxxxxxxxxxxxxxx)';
ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxxxxxxxxxxxxxx)';
ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=xxxxxxxxxxxxxxxxxxxxx)';
SEND DEVICE TYPE 'SBT_TAPE' 'NB_ORA_SERV=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
SET UNTIL TIME ='2023-06-14 00:00:00';
recover database;
release channel CH1;
release channel CH2;
release channel CH3;
release channel CH4;}
exit;
EOF
date
---------------------------
报错：
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
解决办法：
$ ln -s /usr/openv/netbackup/bin/libobk.so64 $ORACLE_HOME/lib/libobk.so
$ /usr/openv/netbackup/bin/oracle_link
-------------------------------------------------------------------------
--19c 10以下的小版本可能碰到在open resetlogs 时hang住，
需要设置隐含参数
alter system set "_min_undosegs_for_parallel_fptr"=0 scope=both sid='*' ;
然后重新open
--先清理掉部分不需要的redolog
select group#,member from v$Logfile;
alter database drop logfile group 6;
select distinct 'alter database drop logfile group '||group#||';' from v$Logfile;
alter database clear unarchived logfile group 14;
alter database drop logfile group 14;
...
alter database add logfile group 3 ('/data1/oradata/testfms/redo03.log') size 500m;
alter database add logfile thread 1 group 3('+arch') size 1G;
--打开数据库
alter database open resetlogs;
--禁用threa2
alter database disable thread 2;
--清理temp
select tablespace_name from dba_tablespaces where contents='TEMPORARY';
create temporary tablespace temp1 tempfile '/data/oracle/oradata/orcl/temp02.dbf' size 100m;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
create temporary tablespace temp tempfile '/data/oracle/oradata/orcl/temp01.dbf' size 30G;
alter database default temporary tablespace temp;
drop tablespace temp1 including contents and datafiles;
--清理undo
select tablespace_name from dba_tablespaces where contents='UNDO';
drop tablespace undotbs2 including contents and datafiles;
----------19cpdb service
exec dbms_service.create_service('xxxxx','xxxxx');
exec dbms_service.start_service('xxxx');
exec dbms_service.stop_service('xxxx');
exec dbms_service.delete_service('xxxxx');
set lines 200
col name for a20
col pdb for a20
select con_id,name,enabled,pdb from cdb_Services order by 1;
--------------------------版本不一致，需重新编译
SQL> spool catout.log
SQL> startup upgrade;
SQL> set echo on
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql <--------------$ORACLE_HOME/rdbms/admin
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql <--------------$ORACLE_HOME/rdbms/admin
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql <--------------$ORACLE_HOME/rdbms/admin
SQL> spool off
SQL> 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 charset=AL32UTF8
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), '');
