还原和恢复数据库
本实验是 将一个库还原到另外一个库。
说明:
源库: 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/