1、连接到目标数库
rman target/
rman target system/nocatalog
2、配置 RMAN 配置参数
①定义使用的通道数量
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
②恢复默认值
CONFIGURE DEVICE TYPE DISK CLEAR;
3、非一致性备份
rman target system/test2021 catalog rman_user/hadoop;
RMAN> backup database format '/home/oracle/oradata2/bak/non_uniform/oradb_%Y_%M_%D_%U.bak' maxsetsize 2G;
RMAN> sql 'alter system archive log current';
RMAN> list backup of database;
4、一致性备份
rman target system/test2021 catalog rman_user/hadoop;
RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database format '/home/oracle/oradata2/bak/uniform/oradb_%d_%s.bak';
RMAN> alter database open;
RMAN> list backup of database;
5、备份表空间
rman target system/test2021 nocatalog;
run{
allocate channel ch_1 type disk;
backup tablespace users
format '/home/oracle/oradata2/bak/tbsp/%d_%p_%t_%c.dbf';
}
list backup of tablespace users;
6、备份数据文件
rman target system/test2021 nocatalog;
RMAN> backup datafile 1,2,3 filesperset 3;
RMAN> list backup of datafile 1,2,3;
7、备份控制文件
rman target system/test2021 nocatalog;
RMAN> backup current controlfile;
RMAN> backup tablespace users include current controlfile;
RMAN> list backup of controlfile;
8、备份归档重做日志
RMAN> backup archivelog all delete all input;
RMAN> list backup of archivelog all;
9、增量备份
① 0 级差异增量备份
run{
allocate channel ch_1 type disk;
backup incremental level=0
format '/home/oracle/oradata2/bak/incre/user_%d_%t_%c.bak'
tablespace users;
}
② 1 级差异增量备份
backup incremental level=1 format '/home/oracle/oradata2/bak/incre/user_%d_%t_%c.bak' tablespace users;
③ 2 级累积增量备份
backup incremental level=2 cumulative format '/home/oracle/oradata2/bak/incre/user_%d_%t_%c.bak' tablespace users;
10、删除备份文件
rman target system/test2021 nocatalog;
RMAN> list backup;
手动删除备份文件
RMAN> crosscheck backup;
RMAN> delete expired backup;
RMAN> list backup;
11、 noarchivelog 模式数据库备份恢复
①备份
rman target system/test2021 nocatalog;
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run{
allocate channel ch_1 type disk;
backup database
format '/home/oracle/oradata2/bak/noarch/db_%d_%t_%u.bak';
}
②恢复
rman target system/test2021 nocatalog;
RMAN> startup mount;
RMAN> run{
allocate channel ch_1 type disk;
restore database;
}
RMAN> alter database open;
12、 archivelog 模式数据库备份恢复
①备份
rman target system/test2021 nocatalog;
RMAN> run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
backup database
format '/home/oracle/oradata2/bak/arch/db_%d_%t_%u.bak';
}
②恢复
RMAN> startup mount;
RMAN> run{
allocate channel ch_1 type disk;
restore database;
recover database;
}
RMAN> alter database open;
13、基于时间的不完全恢复
①备份
rman target system/test2021 nocatalog;
RMAN> run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
backup database
format '/home/oracle/oradata2/bak/arch/db_%d_%t_%u.bak';
}
②脱机备份,防止不完全恢复失败。
RMAN> shutdown immediate;
备份控制文件、数据文件和归档重做日志文件
③恢复
RMAN> startup mount;
RMAN> run{
sql'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
set until time '2021-04-18 03:35:00';
restore database;
recover database;
sql'alter database open resetlogs';
}
14、基于 SCN 的不完全恢复
①备份
rman target system/test2021 nocatalog;
RMAN> run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
backup database
format '/home/oracle/oradata2/bak/arch/db_%d_%t_%u.bak';
}
②误删操作后,通过日志挖掘找到对应的 SCN
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select sysdate from dual;
SQL> EXECUTE dbms_logmnr.start_logmnr(starttime=>to_date('2021-05-04 12:31:54','yyyy-mm-dd hh24:mi:ss'),endtime=>to_date('2021-05-04 12:33:14','yyyy/mm/dd hh24:mi:ss'),options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);
SQL> select scn,sql_redo,SQL_UNDO from v$Logmnr_contents where SEG_OWNER='SCOTT' and SEG_NAME='GUO1';
SQL> exec dbms_logmnr.end_logmnr;
或
####################################
方法一、
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select sysdate from dual;
SQL> EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.DICT_FROM_ONLINE_CATALOG);
SQL> SELECT NAME FROM v$archived_log where FIRST_TIME between to_date('2021-05-04 01:49:16','yyyy-mm-dd hh24:mi:ss') and to_date('2021-05-04 12:49:16','yyyy-mm-dd hh24:mi:ss') order by 1;
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_5_j92x94pq_.arc', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_6_j92xzgyb_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_7_j92xzkly_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_8_j92z1jff_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SQL> select filename from V$LOGMNR_LOGS;
SQL> select scn,sql_redo,SQL_UNDO from v$Logmnr_contents where SEG_OWNER='SCOTT' and SEG_NAME='GUO1';
SQL> exec dbms_logmnr.end_logmnr;
方法二、
#SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
#SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select sysdate from dual;
SQL> EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
SQL> set linesize 100
SQL> col member for a35
SQL> select log1.group#,log1.bytes/1024/1024 as logsize,log1.archived,log1.status,log2.member,log2.type from v$log log1,v$logfile log2 where log1.group#=log2.group# order by log1.group#;
SQL> SELECT NAME FROM v$archived_log where FIRST_TIME between to_date('2021-05-04 01:49:16','yyyy-mm-dd hh24:mi:ss') and to_date('2021-05-04 12:49:16','yyyy-mm-dd hh24:mi:ss') order by 1;
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_5_j92x94pq_.arc', OPTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_6_j92xzgyb_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_7_j92xzkly_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/orafra/ORCLPRI/archivelog/2021_05_04/o1_mf_1_8_j92z1jff_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
SQL> select filename from V$LOGMNR_LOGS;
SQL> select scn,sql_redo,SQL_UNDO from v$Logmnr_contents where SEG_OWNER='SCOTT' and SEG_NAME='GUO1';
SQL> exec dbms_logmnr.end_logmnr;
####################################
③脱机备份,防止不完全恢复失败。
RMAN> shutdown immediate;
备份控制文件、数据文件和归档重做日志文件
④恢复
RMAN> startup mount;
RMAN> run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
set until scn 1122384;
restore database;
recover database;
sql'alter database open resetlogs';
}