小知识:RMAN基于某个具体时间点的恢复示例(Small knowledge: RMAN recovery example based on a specific point in time)

最近帮忙基于某个时间点恢复一个库,说是备份和归档是全的。
好多年没做过这类事情了,不过这算是最基本的DBA技能,下面给出RMAN基于某个具体时间点的恢复示例脚本:

run{
allocate channel c1 type disk;
allocate channel c2 type disk;

sql "alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''";
set newname for database to '/public/Others/backup/db_%U.dbf';
restore database until time '2022-01-15 00:31:18';
switch datafile all;
recover database until time '2022-01-15 00:31:18';

release channel c1;
release channel c2;
}

注意/说明:

  • nls_date_format的格式设置;
  • 如果不确认时间是否需要,恢复完可以先alter database open read only 验证是否数据OK,如果有问题还可以再调整时间recover;
  • switch datafile all 是因为从rac的备份恢复到单实例,需要切换到正确的数据文件路径和名字;
  • set newname for database to ‘xxx/db_%U.dbf’,必须要有%U这类区分;
  • 这里只是示例,实际使用分配通道会更多,通常也要将脚本放到后台执行。
————————

Recently, I helped restore a library based on a certain point in time, saying that backup and archiving are complete.
This kind of thing has not been done for many years, but it is the most basic DBA skill. Here is an example script of RMAN recovery based on a specific point in time:

run{
allocate channel c1 type disk;
allocate channel c2 type disk;

sql "alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''";
set newname for database to '/public/Others/backup/db_%U.dbf';
restore database until time '2022-01-15 00:31:18';
switch datafile all;
recover database until time '2022-01-15 00:31:18';

release channel c1;
release channel c2;
}

Note / Description:

  • nls_date_format的格式设置;
  • If you don’t confirm whether the time is needed, you can first alter database open read only to verify whether the data is OK. If there is a problem, you can adjust the time to recover;
  • Switch datafile all is because you need to switch to the correct data file path and name to restore from RAC backup to a single instance;
  • set newname for database to ‘xxx/db_%U.dbf’,必须要有%U这类区分;
  • This is just an example. There will be more allocation channels actually used. Usually, the script should be executed in the background.