描述:在B库通过A库的RMAN联机全库备份做完全恢复
此实验是在B库上恢复A库,不过更改了实例名。(前提:RMAN联机全库备份,及控制文件自动备份,当然也包括参数文件)
源库(A库):192.168.10.2 ORACLE_SID=ORA11GR2 DBID=237843809
目标库(B库):192.168.10.3 ORACLE_SID=PROD
源库备份(A库)联机全备:
RMAN> backup database;
Starting backup at 10-OCT-16
using channel ORA_DISK_1
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 10-OCT-16
channel ORA_DISK_1: finished piece 1 at 10-OCT-16
piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_10/o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp tag=TAG20161010T141436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-OCT-16
Starting Control File and SPFILE Autobackup at 10-OCT-16
piece handle=/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924876901_czpdh5ht_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-OCT-16
目标库(B库操作)
1.拷贝RMAN备份文件:
创建相同目录并将源库RMAN备份文件拷贝到目标库
[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/
[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2/backupset/2016_10_10/
[oracle@bing ~]$
[oracle@bing ~]$ cd /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/
[oracle@bing 2016_10_10]$
[oracle@bing 2016_10_10]$ pwd
/u01/app/FRA/ORA11GR2/autobackup/2016_10_10
[oracle@bing 2016_10_10]$ scp oracle@192.168.10.2:/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924876901_czpdh5ht_.bkp .
The authenticity of host '192.168.10.2 (192.168.10.2)' can't be established.
RSA key fingerprint is f8:c9:06:00:43:30:36:ce:a2:3a:d6:9c:6c:21:c2:5b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.10.2' (RSA) to the list of known hosts.
oracle@192.168.10.2's password:
o1_mf_s_924876901_czpdh5ht_.bkp 100% 9600KB 9.4MB/s 00:00
[oracle@bing 2016_10_10]$
[oracle@bing 2016_10_10]$ cd /u01/app/FRA/ORA11GR2/backupset/2016_10_10/
[oracle@bing 2016_10_10]$ pwd
/u01/app/FRA/ORA11GR2/backupset/2016_10_10
[oracle@bing 2016_10_10]$ scp oracle@192.168.10.2:/u01/app/FRA/ORA11GR2/backupset/2016_10_10/o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp .
oracle@192.168.10.2's password:
o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp 100% 1162MB 18.8MB/s 01:02
[oracle@bing 2016_10_10]$ du -ah
1.2G ./o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp
1.2G .
[oracle@bing 2016_10_10]$
2.配置目标数据库:
目标库设置环境变量,启动rman到nomount状态,设置dbid(即源库dbid)
[oracle@bing 2016_10_10]$ echo $ORACLE_SID
PROD
[oracle@bing 2016_10_10]$ export ORACLE_SID=OCMU
[oracle@bing 2016_10_10]$ echo $ORACLE_SID
OCMU
[oracle@bing 2016_10_10]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 10 11:06:32 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
3.目标库启动到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/initOCMU.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
4.恢复参数文件:
RMAN> restore spfile to pfile '?/dbs/initORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924876901_czpdh5ht_.bkp';
Starting restore at 10-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_10/o1_mf_s_924876901_czpdh5ht_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-OCT-16
——查看恢复的pfile文件;
[oracle@bing ~]$ ls $ORACLE_HOME/dbs/initORA11GR2.ora
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora
[oracle@bing ~]$
5.编辑pfile参数文件:
编辑pfile参数文件并且在目标库创建相关目录,因为目标库只安装了数据库软件,所以,好多目录都未创建,或源库目录与目标库目录不同
[oracle@bing dbs]$ vi initORA11GR2.ora
ORA11GR2.__db_cache_size=331350016
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=188743680
ORA11GR2.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/OCMU/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/OCMU/control01.ctl','/u01/app/oracle/oradata/OCMU/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'(db_name不能改)
*.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'
~
"initORA11GR2.ora" 25L, 939C written
——按照pfile参数文件创建相应目录
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/OCMU/adump
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/OCMU/
6.修改pfile文件名称,因为我们目标库的实例名为OCMU
[oracle@bing dbs]$ ls initORA11GR2.ora
initORA11GR2.ora
[oracle@bing dbs]$ mv initORA11GR2.ora initOCMU.ora
[oracle@bing dbs]$ ls initOCMU.ora
initOCMU.ora
[oracle@bing dbs]$
7.通过pfile启动数据库到nomount模式,测试pfile是否有修改参数
[oracle@bing dbs]$ echo $ORACLE_SID
PROD
[oracle@bing dbs]$ export ORACLE_SID=OCMU
[oracle@bing dbs]$ echo $ORACLE_SID
OCMU
[oracle@bing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 10 11:27:14 2016
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
8.生成spfile:
以pfile参数启动到nomount模式成功,通过pfile生成spfile
SQL> create spfile from pfile;
File created.
SQL> !ls spfileOCMU.ora
spfileOCMU.ora
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- -------
Spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCMU.ora
SQL>
9.还原控制文件:
还原控制文件并启动到mount模式
RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924876901_czpdh5ht_.bkp';
Starting restore at 10-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/OCMU/control01.ctl
output file name=/u01/app/oracle/oradata/OCMU/control02.ctl
Finished restore at 10-OCT-16
——验证:
[oracle@bing dbs]$ cd /u01/app/oracle/oradata/OCMU/
[oracle@bing OCMU]$ ls
control01.ctl control02.ctl
——启库到mount下:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
10.还原数据文件(即set newname):
RMAN> run{
2> set newname for datafile 1 to '/u01/app/oracle/oradata/OCMU/system01.dbf';
3> set newname for datafile 2 to '/u01/app/oracle/oradata/OCMU/sysaux.dbf';
4> set newname for datafile 3 to '/u01/app/oracle/oradata/OCMU/undotbs01.dbf';
5> set newname for datafile 4 to '/u01/app/oracle/oradata/OCMU/users01.dbf';
6> set newname for datafile 5 to '/u01/app/oracle/oradata/OCMU/example01.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 10-OCT-16
Starting implicit crosscheck backup at 10-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 10-OCT-16
Starting implicit crosscheck copy at 10-OCT-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-OCT-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924876901_czpdh5ht_.bkp
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/OCMU/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/OCMU/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/OCMU/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OCMU/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/OCMU/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_10/o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_10/o1_mf_nnndf_TAG20161010T141436_czpdgdcw_.bkp tag=TAG20161010T141436
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 10-OCT-16
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=924878535 file name=/u01/app/oracle/oradata/OCMU/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=924878535 file name=/u01/app/oracle/oradata/OCMU/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=924878535 file name=/u01/app/oracle/oradata/OCMU/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=924878535 file name=/u01/app/oracle/oradata/OCMU/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=924878535 file name=/u01/app/oracle/oradata/OCMU/example01.dbf
Starting recover at 10-OCT-16
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/10/2016 14:42:15
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 1235044
——此处我们会发现,在recover的时候,由于没有归档日志,所以,提示只能恢复到SCN 1235044
RMAN> run{
2> set until scn 1235044;
3> restore database;
4> switch datafile all;
5>recover database;
6> }
executing command: SET until clause
Starting restore at 10-OCT-16
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/OCMU/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/OCMU/sysaux.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/OCMU/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/OCMU/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/OCMU/example01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 10-OCT-16
Starting recover at 10-OCT-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-OCT-16
11.Resetlogs方式打开数据库:
由于我们恢复的目录与源目录不同,所以要先做一下rename
RMAN>alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/10/2016 14:46:08
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/ORA11GR2/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
(因为此时数据库实例名为OCMU)
RMAN> run{
2> sql "alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo01.log'' to ''/u01/app/oracle/oradata/OCMU/redo01.log'' ";
3> sql "alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo02.log'' to ''/u01/app/oracle/oradata/OCMU/redo02.log'' ";
4> sql "alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo03.log'' to ''/u01/app/oracle/oradata/OCMU/redo03.log'' ";
5> }
sql statement: alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo01.log'' to ''/u01/app/oracle/oradata/OCMU/redo01.log''
sql statement: alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo02.log'' to ''/u01/app/oracle/oradata/OCMU/redo02.log''
sql statement: alter database rename file ''/u01/app/oracle/oradata/ORA11GR2/redo03.log'' to ''/u01/app/oracle/oradata/OCMU/redo03.log''
——再次resetlogs方式启动数据库:
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/10/2016 14:51:50
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/OCMU/redo02.log'
——解决:手工clear然后再Open resetlogs
SQL> alter database clear logfile group 2;
Database altered.
——再次启库:
SQL> alter database open resetlogs;
Database altered.
12.收尾工作:
此时已经open了数据库,看似已经完成恢复,但其实还差一点点,我们查询临时表空间数据文件时,会报如下错误,可以看出来,还是由于我们改换了实例名造成的
SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf'
--解决这个错误,如下操作:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf' to '/u01/app/oracle/oradata/OCMU/temp01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
----------------------------------------------------------------
/u01/app/oracle/oradata/OCMU/temp01.dbf
SQL> 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 OCMU
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ORA11GR2
SQL>
注:1:这个测试改变了实例名,我们看一下恢复后的数据库会发现,除了实例名之外,其他的名字还是源库的名字,如果所有的名字都想改为OCMU,那么,还需要逐一修改,当然,db_name改动起来,相对麻烦一点点,可以使用nid工具来完成修改或者通过修改控制文件的方式来修改db_name.
2:在整个恢复过程中,如果做到源库和目标库所有的配置都一致的话,那么,整个恢复的步骤会相对更简单些!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126599/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126599/