RMAN还原数据库

利用DBCA创建一个数据库
进入实例
set ORACLE_SID=supportdb

[oracle@Server ~]$ sqlplus sys/123456 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 26 11:05:19 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

确认数据库实例
SQL> show parameter instance;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
active_instance_count                integer
cluster_database_instances           integer     1
instance_groups                      string
instance_name                        string      supportdb
instance_number                      integer     0
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
parallel_server_instances            integer     1

创建spfile
SQL> create pfile=’D:\dbbackup\pfile_temp0613.txt’  from spfile; 

File created.

停止数据库
SQL> shutdown abort
ORACLE instance shut down.

从手动生成pfile启动数据库
SQL> startup nomount pfile=’D:\dbbackup\pfile_temp0613.txt’

进入RMAN开始恢复控制文件
RMAN> restore controlfile from ‘D:\dbbackup\CONTROL_FILE_SUPPORTD_0_949580757.BAK’;

把数据库启动到mount;
SQL> alter database mount;

再到源数据库中查询如下:
SQL> select ‘set newname for datafile ‘||file#||’ to ”’||name||”’;’ from v$datafile;

‘SETNEWNAMEFORDATAFILE’||FILE#||’TO”’||NAME||”’;’
——————————————————————————–
set newname for datafile 20 to ‘E:\oradata\mesdb_data\SUPPORT08.DBF’;
set newname for datafile 19 to ‘E:\oradata\mesdb_data\SUPPORT07.DBF’;
set newname for datafile 18 to ‘E:\oradata\mesdb_data\SUPPORT06.DBF’;
set newname for datafile 17 to ‘E:\oradata\mesdb_data\SUPPORT05.DBF’;
set newname for datafile 16 to ‘E:\oradata\mesdb_data\SUPPORT04.DBF’;
set newname for datafile 15 to ‘E:\oradata\mesdb_data\SUPPORT03.DBF’;
set newname for datafile 14 to ‘E:\oradata\mesdb_data\SUPPORT02.DBF’;
set newname for datafile 13 to ‘E:\oradata\mesdb_data\SUPPORT01.DBF’;
省略部分文件

注册备份集位置
catalog start with’D:\dbbackup’;

数据库还原文件
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 20 to ‘E:\oradata\mesdb_data\SUPPORT08.DBF’;
set newname for datafile 19 to ‘E:\oradata\mesdb_data\SUPPORT07.DBF’;
set newname for datafile 18 to ‘E:\oradata\mesdb_data\SUPPORT06.DBF’;
set newname for datafile 17 to ‘E:\oradata\mesdb_data\SUPPORT05.DBF’;
set newname for datafile 16 to ‘E:\oradata\mesdb_data\SUPPORT04.DBF’;
set newname for datafile 15 to ‘E:\oradata\mesdb_data\SUPPORT03.DBF’;
set newname for datafile 14 to ‘E:\oradata\mesdb_data\SUPPORT02.DBF’;
set newname for datafile 13 to ‘E:\oradata\mesdb_data\SUPPORT01.DBF’;
省略部分文件
restore database ; 
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
}

还原全部归档日志文件
RMAN>restore archivelog all;

可能会报如下错误信息
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/08/2017 21:27:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 133516 and starting SCN of 432248487682

对目标库使用语句进行recover，指定scn为433473853954
RMAN> recover database until scn  433473853954;

Starting recover at 09-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 09-SEP-17

打开数据库
SQL> alter database open resetlogs;

Database altered.

查看作业参数
SQL> show parameter job

NAME                                 TYPE        VALUE
———————————— ———– ——————————
job_queue_processes                  integer     1000

修改作业参数
SQL> alter system set job_queue_processes=0; 

生成drop语句

删除JOB
SQL> select ‘exec dbms_scheduler.drop_job (‘||””||job_name||””||’);’ from dba_scheduler_jobs@t_130_9

删除批处理
SQL>select ‘ drop database link ‘ ||db_link|| ‘;’ from dba_db_links

重建联机日志
SQL>select ‘alter database rename file ‘ ||””||member||””||’ to ‘||””||member||””||’;’ from v$logfile;

刷新序列
SQL> select 
‘drop SEQUENCE MFGSUPPORT.’||sequence_name||’;’||CHR(10)||’CREATE SEQUENCE MFGSUPPORT.’||sequence_name||’ minvalue 1 maxvalue 9999999999999999999999999999 start with ‘||last_number||’ increment by 1
nocache;’ from dba_sequences where sequence_owner=’MFGSUPPORT’ and sequence_owner=’MFGSUPPORT’