2018-03-02 Oracle 宅必备
这节讲日常用到的RMAN还原的相关脚本
[TOC]
--------
** 写在前面:**
参数文件中的相关目录需要事先建立
脚本中通道分配为第三方磁带备份，磁盘请使用常规方式
```
allocate channel c1 type disk;
release channel c1;
```
## 1. 恢复spfile
### 1. 1 手动指定备份文件目录
```
RMAN>catalog start with '/rman/';
```
### 1.2 通过alert日志或pfile恢复
建议定期备份pfile
由于参数信息会放到alert’日志中，可以拷贝参数至文件中然后恢复
```
SQL> create spfile from pfile='the_location_of_parameter_file';
```
### 1.3 RMAN 恢复
使用catolog时:
```
RMAN> restore spfile from autobackup;
```
未使用catalog时:
```
RMAN> restore spfile to '/tmp/spfiletemp.ora' from autobackup recovery area ='/u01/app/oracle/flash_recovery_area' db_name=orcl;
```
## 2. 恢复控制文件
### 2.1 开启至nomount状态
这里注意参数文件中相关目录需事先存在
如dump目录，redo log目录，数据文件目录
```
SQL>start nomount;
```
### 2.2 恢复控制文件
```
rman>
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=dcprod,OB2BARLIST=DCPROD_ARCHIVE,OB2BARHOSTNAME=dc1)';
RESTORE CONTROLFILE FROM 'DCPROD_ARCHIVE<dcprod_57445:967816126:1>.dbf';
}
```
## 3. restore数据库
### 3.1 mount数据库
```
SQL>alter database mount;
```
### 3.2 restore数据库
```
RUN
{
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=dcprod,OB2BARLIST=DCPROD,OB2BARHOSTNAME=dc1)';
SET NEWNAME FOR DATAFILE 1 to '/dcprod/oradata/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/dcprod/oradata/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/dcprod/oradata/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 to '/dcprod/oradata/users01.dbf';
SET NEWNAME FOR DATAFILE 5 to '/dcprod/oradata/b2bods_01.dbf';
SET NEWNAME FOR DATAFILE 6 to '/dcprod/oradata/b2bods_02.dbf';
SET NEWNAME FOR DATAFILE 7 to '/dcprod/oradata/baan01.dbf';
SET NEWNAME FOR DATAFILE 8 to '/dcprod/oradata/dcb2b_02.dbf';
SET NEWNAME FOR DATAFILE 9 to '/dcprod/oradata/dcb2b_01.dbf';
SET NEWNAME FOR DATAFILE 10 to '/dcprod/oradata/dcb2b_idx01.dbf';
SET NEWNAME FOR DATAFILE 11 to '/dcprod/oradata/dcdm_02.dbf';
SET NEWNAME FOR DATAFILE 12 to '/dcprod/oradata/dcdm_01.dbf';
SET NEWNAME FOR DATAFILE 13 to '/dcprod/oradata/dcdm_idx01.dbf';
SET NEWNAME FOR DATAFILE 14 to '/dcprod/oradata/dclegacy01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
```
## 4. recover数据库
使用sqlplus 恢复需要先恢复归档文件
使用rman则不需要
### 4.1使用sqlplus恢复
```
SQL> recover database using backup controlfile until cancel; #恢复至归档结束
SQL>recover database until time '2018-2-11 10:00:00' using backup controlfile ;#恢复到时间点
```
### 4.2使用RMAN还原
set until time也可以用来restore
```
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=DCPROD,OB2BARLIST= DCPROD-archive,OB2BARHOSTNAME= dc1)';
set until time "to_date('11-Feb-2018 10:00:00','DD-MON-YYYY HH24:MI:SS')";
recover database;
}
```
## 5. 恢复归档
```
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=DCPROD,OB2BARLIST= DCPROD-archive,OB2BARHOSTNAME= dc1)';
restore archivelog from logseq 765941 until logseq 765960;
}
```
## 6. 重建临时表空间文件
由于rman不会恢复临时表空间文件，所以这里我们需要重建他
### 6.1 删除控制文件中原来临时表空间文件
```
SQL> alter database tempfile '/oracle/oradata/dcprod/datafile/temp01.dbf' drop;
SQL> alter database tempfile '/oracle/oradata/dcprod/datafile/temp02.dbf' drop;
SQL> alter database tempfile '/oracle/oradata/dcprod/datafile/temp03.dbf' drop;
```
### 6.2 新建临时表空间文件
```
SQL>alter tablespace temp add tempfile '/dcprod/oradata/temp01.dbf' size 20408M ;
SQL>alter tablespace temp add tempfile '/dcprod/oradata/temp02.dbf' size 20408M ;
```
## 7. 重新配置redo logs
这里可以根据需要是否需要更改redo log位置
```
alter database rename file '/oracle/NP1/origlogA/log_g5m1.dbf' to '/qastest2/oracle/NP1/origlogA/log_g5m1.dbf';
alter database rename file '/oracle/NP1/mirrlogB/log_g6m2.dbf' to '/qastest2/oracle/NP1/mirrlogB/log_g6m2.dbf';
alter database rename file '/oracle/NP1/origlogB/log_g6m1.dbf' to '/qastest2/oracle/NP1/origlogB/log_g6m1.dbf';
```
## 8.开启数据库
### 8.1 read only方式打开
打开后可以继续向后还原数据库，不可以向前
```
alter database open read only;
```
### 8.2 正常打开
这里redo log目录需事先存在
```
alter database open resetlogs;
```
## 9.修改数据库名称
如果有需要我们可以修改数据库db name
```
create spfile from pfile;
SQL> shutdown immediate;
SQL>startup mount;
cd $ORACLE_HOME/bin
./nid target=sys/passwd dbname=NQ1
···