django-\/\/ i K |

2018-03-02 Oracle 宅必备

这节讲日常用到的RMAN还原的相关脚本

写在前面:

参数文件中的相关目录需要事先建立

脚本中通道分配为第三方磁带备份，磁盘请使用常规方式

 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

···