【备份恢复】上:还原和恢复数据库(即将一个库还原到另外一个库)

还原和恢复数据库

本实验是
将一个库还原到另外一个库。
说明:
源库:
192.168.10.2   ORACLE_SID=ORA11GR2   DBID=237843809
目标库:
192.168.10.3   ORACLE_SID= ORA11GR2   (保持与源库一直的SID)

1.
准备工作

源库 数据库全备及 开启 控制文件 自动备份,当然也包括参数文件

RMAN> backup as backupset database;

 

Starting backup at 02-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: starting piece 1 at 02-OCT-16

channel ORA_DISK_1: finished piece 1 at 02-OCT-16

piece handle= /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at 02-OCT-16

 

Starting Control File and SPFILE Autobackup at 02-OCT-16

piece handle= /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 02-OCT-16

2.拷贝
RMAN 备份文件

在目标库 创建相同目录并将源库 RMAN 备份文件 拷贝到目标库

[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/

[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2 /backupset /2016_10_02/

[oracle@bing ~]$

[oracle@bing 2016_10_02]$ pwd

/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp .

oracle@192.168.10.2's password:

o1_mf_s_924205924_cz1x757l_.bkp                                                                     100% 9600KB    9.4MB/s    00:01    

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ cd /u01/app/FRA/ORA11GR2/ backupset /2016_10_02/

[oracle@bing 2016_10_02]$ pwd

/u01/app/FRA/ORA11GR2/ backupset /2016_10_02

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp .

oracle@192.168.10.2's password:

o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp                                                        100% 1160MB   36.3MB/s    00:32    

[oracle@bing 2016_10_02]$

3.
配置目标库

目标库设置环境变量,启动 rman nomount 状态, 设置 dbid (即源库 dbid

[oracle@bing 2016_10_02]$ echo $ORACLE_SID

PROD

[oracle@bing 2016_10_02]$ export ORACLE_SID=ORA11GR2

此步骤可以取与源库不同的 ORACLE_SID ,也可以相同,本例子修改相同)

[oracle@bing 2016_10_02]$ echo $ORACLE_SID

ORA11GR2

[oracle@bing 2016_10_02]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:09:40 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.   All rights reserved.

 

connected to target database (not started)

 

RMAN> set dbid 237843809

 

executing command: SET DBID

4.
目标库启动到 nomount 模式

此时会报错,原因是,目标库没有参数文件,但也是能启动到 nomount 模式,分配内存、启动后台进程

RMAN> startup nomount;

 

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora'

 

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

 

Total System Global Area     1068937216 bytes

 

Fixed Size                       2260088 bytes

Variable Size                 281019272 bytes

Database Buffers              780140544 bytes

Redo Buffers                    5517312 bytes


5.
恢复参数文件

1). 恢复的 pfile 文件

RMAN> restore spfile to pfile '?/dbs/initORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';

 

Starting restore at 02-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

 

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 02-OCT-16

 

RMAN>

2). 查看恢复的 pfile 文件

[oracle@bing ~]$ ls $ORACLE_HOME/dbs/initORA11GR2*

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora

[oracle@bing ~]$

6.
编辑 pfile 参数文件

1). 查看 pfile 参数文件:

[oracle@bing dbs]$ cat initORA11GR2.ora

----------------------------------------------------------------------------

ORA11GR2.__db_cache_size=381681664

ORA11GR2.__java_pool_size=4194304

ORA11GR2.__large_pool_size=8388608

ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ORA11GR2.__pga_aggregate_target=289406976

ORA11GR2.__sga_target=545259520

ORA11GR2.__shared_io_pool_size=0

ORA11GR2.__shared_pool_size=138412032

ORA11GR2.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORA11GR2'

*.db_recovery_file_dest_size=3221225472

*.db_recovery_file_dest='/u01/app/FRA'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'

*.memory_target=833617920

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[oracle@bing dbs]$

——————————————————————————————————————————

因为我之前取了与源库相同的实例名 ORACLE_SID, 所以不需要修改 pfile 参数文件;需要注意一点,即使改了 ORACLE_SID ,在修改 pfile 参数文件时唯一不能改的是 db_name 的值!!!!!!

 

2). 根据 pfile 文件创建相应的目录

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/ORA11GR2/adump

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/

[oracle@bing dbs]$ mkdir -p /u01/app/FRA

3). 通过 pfile 启动数据库到 nomount 模式, 测试 pfile 是否有修改参数

[oracle@bing dbs]$ echo $ORACLE_SID

ORA11GR2

[oracle@bing dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 2 20:43:06 2016

 

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

 

Connected to an idle instance.

 

SYS@ORA11GR2>startup nomount;

ORACLE instance started.

 

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              503319672 bytes

Database Buffers           322961408 bytes

Redo Buffers                  2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter pfile

 

NAME                                  TYPE         VALUE

------------------------------------ -----------

spfile                                string


7.生成
spfile

SYS@ORA11GR2> create spfile from pfile;

 

File created.

 

SYS@ORA11GR2>shutdown immediate;

ORA-01507: database not mounted

 

ORACLE instance shut down.

 

——以 spfile 参数文件打开实例 ORA11GR1(ORACLE_SID)

SYS@ORA11GR2> startup nomount;

ORACLE instance started.

 

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              503319672 bytes

Database Buffers           322961408 bytes

Redo Buffers                 2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter spfile

 

NAME                                   TYPE         VALUE

------------------------------------ ----------- -

spfile                                 string    /u01/app/oracle/product/11.2.0

                                                /dbhome_1/dbs/spfileORA11GR2.ora                                          



8.还原控制文件

还原 控制文件 并启动到 mount 模式

[oracle@bing adump]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:49:58 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.   All rights reserved.

 

connected to target database: ORA11GR2 (not mounted)

 

RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2 /autobackup/ 2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';

 

Starting restore at 02-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORA11GR2/control01.ctl

output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl

Finished restore at 02-OCT-16

 

——连接数据库( mount 状态)

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

9. 还原数据文件set newname操作

(即将数据文件转换路径到 /u01/app/oracle/oradata/ORA11GR2/ 下)

RMAN> run{ set newname for datafile 1 to '/u01/app/oracle/oradata/ORA11GR2/system01.dbf';         

2> set newname for datafile 2 to '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf';              

3> set newname for datafile 3 to '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf';             

4> set newname for datafile 4 to '/u01/app/oracle/oradata/ORA11GR2/users01.dbf';               

5> set newname for datafile 5 to '/u01/app/oracle/oradata/ORA11GR2/example01.dbf';             

6> restore database;                                                                            

7> switch datafile all;                                                                         

8> recover database; }                                                                         

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 02-OCT-16

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA11GR2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp

channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 02-OCT-16

 

Starting recover at 02-OCT-16

using channel ORA_DISK_1

 

starting media recovery

 

unable to find archived log

archived log thread=1 sequence=14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/02/2016 21:16:09

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and s tarting SCN of 1199408

 

-- 此处我们会发现,在 recover 的时候,由于没有归档日志,所以,提示只能恢复到 SCN 1199408

解决:

RMAN>      run{                                                                           

2>     set until scn 1199408;                                                          

3>     restore database;                                                               

4>     switch datafile all;                                                           

5>     recover database;                                                              

6>     }                                                                               

 

executing command: SET until clause

 

Starting restore at 02-OCT-16

using channel ORA_DISK_1

 

skipping datafile 1; already restored to file /u01/app/oracle/oradata/ORA11GR2/system01.dbf

skipping datafile 2; already restored to file /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

skipping datafile 3; already restored to file /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ORA11GR2/users01.dbf

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA11GR2/example01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 02-OCT-16

 

 

Starting recover at 02-OCT-16

using channel ORA_DISK_1

 

starting media recovery

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

10. resetlogs 方式打开数据库

RMAN> alter database open resetlogs;

 

database opened

 

——验证:

SYS@ORA11GR2>select instance_name,status from v$instance;

 

INSTANCE_NAME     STATUS

---------------- ------------

ORA11GR2          OPEN

11. 收尾工作

此时已经 open 了数据库, 再验证 临时表空间数据文件 及日志文件

SYS@ORA11GR2> select file_name from dba_temp_files;

FILE_NAME

----------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11GR2/temp01.dbf

 

SYS@ORA11GR2> select member from v$logfile;

MEMBER

----------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11GR2/redo03.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

SYS@ORA11GR2> show parameter name

NAME                                  TYPE         VALUE

------------------------------------ ----------- ----------

cell_offloadgroup_name                string

db_file_name_convert                  string

db_name                               string       ORA11GR2

db_unique_name                        string       ORA11GR2

global_names                          boolean      FALSE

instance_name                         string       ORA11GR2

lock_name_space                       string

log_file_name_convert                 string

processor_group_name                  string

service_names                         string       ORA11GR2

SYS@ORA11GR2>

注意:当改了实例名后,则恢复过程有一些不同,最后收尾也不同,需注意!!!!!!!!!!!!!!!!!!


注意:在open resetlogs前都需要检查文件位置是否正确,尤其是redo文件的位置

 alter database rename file '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/testdb/temp01.dbf';

 alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/testdb/redo03.log';

 alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/testdb/redo02.log';

 alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/testdb/redo01.log';


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126552/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126552/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值