我们在工作中，会遇到各式各样的问题、需求，而为了解决这些问题，我们一定要尽量找到最快速、最完美的解决方法。作为一名DBA，对工作的责任和态度尤其重要，遇到问题，先要仔细思考，可能有的时候一条命令下去，引发的连锁反应就会很多很多。举个例子，前段时间我的领导要求我做一套和我们生产环境一模一样的数据库，有的人第一反应可能会想到逻辑备份，使用EXP/IMP,EXPDP/IMPDP这样的备份恢复命令去解决问题，命令很简单，但是做到最后会产生很多问题，比如有些视图要重新编译，遇到有同义词synonym的这种数据库对象根本不知道怎么解决…..,如果你懂得使用RMAN备份在另外一条机器上做还原，那么这样的需求就再简单不过了，而且恢复时间大大缩短，换个角度想这个问题，这难道不是一种比较实用的数据迁移方案吗！
详细步骤如下：
1. 新建立一台虚拟机
2. 在虚拟机上建立操作系统，和原来的操作系统一致，这里采用的是Linux 5.4 32位的操作系统，分区的时候创建/data新的分区(原来的数据库放在/dat路径下，新建立的虚拟机把数据库放在/data路径下)
3. 操作系统创建之后，安装oracle 10g 32位数据库软件部分，安装的过程这里不再追述，之前怎么创建的就怎么创建，需要注意的是数据库的名称和dbid在这个过程中不能改变
4. 对原数据库进行rman 0级增量备份
run{
allocate channel c1 type disk;
backup incremental level 0 tag 'db0' format '/dat/DBData/oradata/db0_%d_%T_%s' database include current controlfile;
sql'alter system archive log current';
backup filesperset 3 format '/dat/DBData/oradata/arch0_%d_%T_%s' archivelog all delete input;
release channel c1;
}
5. 备份之后生成的备份片如下
arch0_NUODA_20160612_16
arch0_NUODA_20160612_17
arch0_NUODA_20160612_18
arch0_NUODA_20160612_19
arch0_NUODA_20160612_20
db0_NUODA_20160612_14
db0_NUODA_20160612_15
6. 以上的备份片包含了控制文件、SPFILE文件、数据文件及归档日志的文件的备份，至于这些备份能不能都用到，我们演示的时候再做决定
7. 把生成的备份COPY到新的数据库服务器上，这里放在/home/oracle路径下
8. 创建密码文件
[oracle@localhost ~]$ orapwd file=$ORACLE_HOME/dbs/orapwNUODA password=password entries=5
9. 从备份恢复spfile，必须先使用startup nomount命令，如下会报错
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/DBSoftware/oracle/product/10.2.0/db_1/dbs/initECOALCHINA.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
注意：在rman下即使没有参数文件，默认也会启动一个DUMMY实例，以便能够恢复参数文件。
开始恢复spfile
RMAN> restore spfile to '/data/DBSoftware/oracle/product/10.2.0/db_1/dbs/spfileNUODA.ora' from '/home/oracle/db0_NUODA_20160612_15';
Starting restore at 12-JUN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/db0_NUODA_20160612_15
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 12-JUN-16
恢复之后，可以看到在dbs路径下，新的spfile产生了
10.spfile恢复之后，创建pfile
RMAN> sql'create pfile from spfile';
关闭数据库，创建转储目录
[oracle@localhost oracle]$ mkdir admin
[oracle@localhost oracle]$ mkdir flash_recovery_area
[oracle@localhost admin]$ mkdir adump
[oracle@localhost admin]$ mkdir bdump
[oracle@localhost admin]$ mkdir cdump
[oracle@localhost admin]$ mkdir dpdump
[oracle@localhost admin]$ mkdir udump
11.看到pfile出现后，编辑pfile，把相关的转储路径及闪回恢复区及控制文件的位置改成新的位置，改过之后如下
[oracle@localhost dbs]$ more initECOALCHINA.ora
NUODA.__db_cache_size=318767104
NUODA.__java_pool_size=4194304
NUODA.__large_pool_size=4194304
NUODA.__shared_pool_size=88080384
NUODA.__streams_pool_size=0
*.audit_file_dest='/data/DBSoftware/oracle/admin/adump'
*.background_dump_dest='/data/DBSoftware/oracle/admin/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/data/DBData/oradata/NUODA/control01.ctl','/data/DBData/oradata
/NUODA/control02.ctl','/data/DBData/oradata/NUODA/control03.ctl','/data/DBData/o
radata/NUODA/control04.ctl','/data/DBData/oradata/NUODA/control05.ctl'
*.core_dump_dest='/dat/DBSoftware/oracle/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='NUODA'
*.db_recovery_file_dest='/data/DBSoftware/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NUODAXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=419430400
*.sga_target=419430400
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/DBSoftware/oracle/admin/udump'
12.以初始化参数文件启动数据库到nomount状态下
RMAN> startup pfile='/data/DBSoftware/oracle/product/10.2.0/db_1/dbs/initNUODA.ora' nomount;
Oracle instance started
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 96469816 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
12.在新路径下还原控制文件
RMAN> restore controlfile to '/data/DBData/oradata/NUODA/control01.ctl' from '/home/oracle/db0_NUODA_20160612_15';
回来1个控制文件，但初始化参数文件里有5个，复制出另外四个即可。
13.确定控制文件回来了，再检查一遍吧
14.执行create spfile from pfile；
15.Shutdown immediate数据库
16.使用spfile启动数据库到mount状态下
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
-----------------------------------------------------------------------------1
/dat/DBData/oradata/NUODA/system01.dbf
2
/dat/DBData/oradata/NUODA/undotbs01.dbf
3
/dat/DBData/oradata/NUODA/sysaux01.dbf
FILE#
----------
NAME
--------------------------------------------------------------------------------
4
/dat/DBData/oradata/NUODA/users01.dbf
5
/dat/DBData/oradata/NUODA/catalog.dbf
7
/dat/DBData/oradata/NUODA/qqqq.dbf
FILE#
----------
NAME
--------------------------------------------------------------------------------
8
/dat/DBData/oradata/NUODA/eeee.dbf
9
/dat/DBData/oradata/NUODA/rrrr.dbf
10
/dat/DBData/oradata/NUODA/tttt.dbf
17.在新控制文件中注册数据文件备份和归档备份---也就是备份所在的路径
catalog start with ‘/home/oracle’
18.把数据文件还原的新的位置上
run{
set newname for datafile '/dat/DBData/oradata/NUODA/system01.dbf
' to '/data/DBData/oradata/NUODA/system01.dbf';
set newname for datafile '/dat/DBData/oradata/NUODA/undotbs01.dbf
' to '/data/DBData/oradata/NUODA/undotbs01.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/sysaux01.dbf
' to '/data/DBData/oradata/NUODA/sysaux01.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/users01.dbf
' to '/data/DBData/oradata/NUODA/users01.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/catalog.dbf
' to '/data/DBData/oradata/NUODA/catalog.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/qqqq.dbf
' to '/data/DBData/oradata/NUODA/qqqq.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/eeee.dbf
' to '/data/DBData/oradata/NUODA/eeee.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/rrrr.dbf
' to '/data/DBData/oradata/NUODA/rrrr.dbf ';
set newname for datafile '/dat/DBData/oradata/NUODA/tttt.dbf
' to '/data/DBData/oradata/NUODA/ tttt.dbf ';
restore database;
switch datafile all;
}
19.恢复数据库，使用日志文件
RMAN> recover database;
Starting recover at 12-JUN-16
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=42
channel ORA_DISK_1: reading from backup piece /home/oracle/arch0_NUODA_20160612_19
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/arch0_NUODA_20160612_19 tag=TAG20160612T114512
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/data/DBSoftware/oracle/flash_recovery_area/NUODA/archivelog/2016_06_12/o1_mf_1_42_cosxk1l0_.arc thread=1 sequence=42
channel default: deleting archive log(s)
archive log filename=/data/DBSoftware/oracle/flash_recovery_area/NUODA/archivelog/2016_06_12/o1_mf_1_42_cosxk1l0_.arc recid=55 stamp=914334113
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=43
channel ORA_DISK_1: reading from backup piece /home/oracle/arch0_NUODA_20160612_20
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/arch0_NUODA_20160612_20 tag=TAG20160612T114512
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/data/DBSoftware/oracle/flash_recovery_area/NUODA/archivelog/2016_06_12/o1_mf_1_43_cosxk3tf_.arc thread=1 sequence=43
channel default: deleting archive log(s)
archive log filename=/data/DBSoftware/oracle/flash_recovery_area/NUODA/archivelog/2016_06_12/o1_mf_1_43_cosxk3tf_.arc recid=56 stamp=914334115
unable to find archive log
archive log thread=1 sequence=44
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/12/2016 13:41:58
RMAN-06054: media recovery requesting unknown log: thread 1 seq 44 lowscn 986489
出现了以上错误—稍等一会，别急
RMAN> recover database until sequence 44;
Starting recover at 12-JUN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-JUN-16
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/12/2016 13:42:30
ORA-00344: unable to re-create online log '/dat/DBData/oradata/NUODA/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
20.查看当前日志文件的名称
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/dat/DBData/oradata/NUODA/redo03.log
NO
2 ONLINE
/dat/DBData/oradata/NUODA/redo02.log
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 ONLINE
/dat/DBData/oradata/NUODA/redo01.log
NO
更改日志文件的路径
SQL> alter database rename file '/dat/DBData/oradata/NUODA/redo01.log' to '/data/DBData/oradata/NUODA/redo01.log';
Database altered.
SQL> alter database rename file '/dat/DBData/oradata/NUODA/redo02.log' to '/data/DBData/oradata/NUODA/redo02.log';
Database altered.
SQL> alter database rename file '/dat/DBData/oradata/NUODA/redo03.log' to '/data/DBData/oradata/NUODA/redo03.log';
Database altered.
21.打开数据库
SQL> alter database open resetlogs;
Database altered.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2136592/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31448824/viewspace-2136592/
186
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
