原文地址:http://blog.csdn.net/wshxgxiaoli/article/details/9627089
1. 环境: 原机: 192.168.102.156
目标机: 192.168.102.157
2. 需求: 将原机所在的控制文件,数据文件, REDOLOG恢复到/home/oracle/oradata/datafile下
3. 参数文件准备
3.1 在原库生成PFILE文件。
- SQL> create pfile='/home/oracle/initgg1.ora' from spfile;
- File created.
3.2 奖参数文件移至目标库。
- [oracle@GOLDENGATE1 ~]$ scp initgg1.ora oracle@192.168.102.157:/home/oracle
- oracle@192.168.102.157's password:
- initgg1.ora 100% 1051 1.0KB/s 00:00
4. 将目标库启动至NOMOUN状态。
4.1 修改参数文件:
- *.control_files='/home/oracle/oradata/datafile/control01.ctl','/home/oracle/oradata/datafile/control02.ctl','/home/oracle/oradata/datafile/control03.ctl'
- *.core_dump_dest='/home/oracle/admin/gg1/cdump'
4.2 创建PFILE所需要的目录。
- [oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/adump
- [oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/cdump
- [oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/bdump
- [oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/udump
- [oracle@GOLDENGATE2 ~]$ mkdir oradata/datafile
4.3 将目标库启动到NOMOUNT状态。
- [oracle@GOLDENGATE2 ~]$ export ORACLE_SID=gg1
- [oracle@GOLDENGATE2 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 21 03:48:06 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- Connected to an idle instance.
- SQL> startup nomount pfile='/home/oracle/initgg1.ora';
- ORACLE instance started.
- Total System Global Area 167772160 bytes
- Fixed Size 1218292 bytes
- Variable Size 83888396 bytes
- Database Buffers 75497472 bytes
- Redo Buffers 7168000 bytes
5. 备份文件准备。
5.1 备份原库。
- [oracle@GOLDENGATE1 ~]$ rman target /
- Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 21 02:08:38 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- connected to target database: GG1 (DBID=1620494887)
- RMAN> backup format='/home/oracle/rman/full_%d_%T_%s' database include current controlfile plus archivelog;
- Starting backup at 21-SEP-12
- current log archived
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=141 devtype=DISK
- channel ORA_DISK_1: starting archive log backupset
- channel ORA_DISK_1: specifying archive log(s) in backup set
- input archive log thread=1 sequence=4 recid=1 stamp=794527523
- input archive log thread=1 sequence=5 recid=2 stamp=794538401
- input archive log thread=1 sequence=6 recid=3 stamp=794539337
- input archive log thread=1 sequence=7 recid=4 stamp=794539385
- input archive log thread=1 sequence=8 recid=5 stamp=794539992
- input archive log thread=1 sequence=9 recid=6 stamp=794542152
- channel ORA_DISK_1: starting piece 1 at 21-SEP-12
- channel ORA_DISK_1: finished piece 1 at 21-SEP-12
- piece handle=/home/oracle/rman/full_GG1_20120921_17 tag=TAG20120921T020913 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
- Finished backup at 21-SEP-12
- Starting backup at 21-SEP-12
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting full datafile backupset
- channel ORA_DISK_1: specifying datafile(s) in backupset
- input datafile fno=00006 name=/home/oracle/oradata/gg1/gg01.dbf
- input datafile fno=00001 name=/home/oracle/oradata/gg1/system01.dbf
- input datafile fno=00003 name=/home/oracle/oradata/gg1/sysaux01.dbf
- input datafile fno=00005 name=/home/oracle/oradata/gg1/example01.dbf
- input datafile fno=00002 name=/home/oracle/oradata/gg1/undotbs01.dbf
- input datafile fno=00004 name=/home/oracle/oradata/gg1/users01.dbf
- channel ORA_DISK_1: starting piece 1 at 21-SEP-12
- channel ORA_DISK_1: finished piece 1 at 21-SEP-12
- piece handle=/home/oracle/rman/full_GG1_20120921_18 tag=TAG20120921T020917 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
- channel ORA_DISK_1: starting full datafile backupset
- channel ORA_DISK_1: specifying datafile(s) in backupset
- including current control file in backupset
- including current SPFILE in backupset
- channel ORA_DISK_1: starting piece 1 at 21-SEP-12
- channel ORA_DISK_1: finished piece 1 at 21-SEP-12
- piece handle=/home/oracle/rman/full_GG1_20120921_19 tag=TAG20120921T020917 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
- Finished backup at 21-SEP-12
- Starting backup at 21-SEP-12
- current log archived
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting archive log backupset
- channel ORA_DISK_1: specifying archive log(s) in backup set
- input archive log thread=1 sequence=10 recid=7 stamp=794542215
- channel ORA_DISK_1: starting piece 1 at 21-SEP-12
- channel ORA_DISK_1: finished piece 1 at 21-SEP-12
- piece handle=/home/oracle/rman/full_GG1_20120921_20 tag=TAG20120921T021016 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
- Finished backup at 21-SEP-12
- RMAN>
5.2 将备份移至目标机:
- [oracle@GOLDENGATE1 rman]$ scp * 192.168.102.157:/home/oracle/rman
- oracle@192.168.102.157's password:
- full_GG1_20120921_17 100% 40MB 19.9MB/s 00:02
- full_GG1_20120921_18 100% 594MB 10.4MB/s 00:57
- full_GG1_20120921_19 100% 6976KB 6.8MB/s 00:01
- full_GG1_20120921_20 100% 12KB 12.0KB/s 00:00
- [oracle@GOLDENGATE1 rman]$
6. 在目标机上面恢复控制文件, 并将数据库启动到MOUNT状态。
- [oracle@GOLDENGATE2 ~]$ rman target /
- Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 21 03:50:44 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- connected to target database: gg1 (not mounted)
- RMAN> restore controlfile from '/home/oracle/rman/full_GG1_20120921_19';
- Starting restore at 21-SEP-12
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=156 devtype=DISK
- channel ORA_DISK_1: restoring control file
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
- output filename=/home/oracle/oradata/datafile/control01.ctl
- output filename=/home/oracle/oradata/datafile/control02.ctl
- output filename=/home/oracle/oradata/datafile/control03.ctl
- Finished restore at 21-SEP-12
- RMAN> alter database mount;
- database mounted
- released channel: ORA_DISK_1
7. 恢复数据文件。
- RMAN> run{
- set newname for datafile 1 to '/home/oracle/oradata/datafile/system01.dbf';
- set newname for datafile 2 to '/home/oracle/oradata/datafile/undotbs01.dbf';
- set newname for datafile 3 to '/home/oracle/oradata/datafile/sysaux01.dbf';
- set newname for datafile 4 to '/home/oracle/oradata/datafile/users01.dbf';
- set newname for datafile 5 to '/home/oracle/oradata/datafile/example01.dbf';
- set newname for datafile 6 to '/home/oracle/oradata/datafile/gg01.dbf';
- restore database;
- switch datafile all;
- }2> 3> 4> 5> 6> 7> 8> 9> 10>
- executing command: SET NEWNAME
- executing command: SET NEWNAME
- executing command: SET NEWNAME
- executing command: SET NEWNAME
- executing command: SET NEWNAME
- executing command: SET NEWNAME
- Starting restore at 21-SEP-12
- Starting implicit crosscheck backup at 21-SEP-12
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=156 devtype=DISK
- Crosschecked 2 objects
- Finished implicit crosscheck backup at 21-SEP-12
- Starting implicit crosscheck copy at 21-SEP-12
- using channel ORA_DISK_1
- Finished implicit crosscheck copy at 21-SEP-12
- searching for all files in the recovery area
- cataloging files...
- no files cataloged
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting datafile backupset restore
- channel ORA_DISK_1: specifying datafile(s) to restore from backup set
- restoring datafile 00001 to /home/oracle/oradata/datafile/system01.dbf
- restoring datafile 00002 to /home/oracle/oradata/datafile/undotbs01.dbf
- restoring datafile 00003 to /home/oracle/oradata/datafile/sysaux01.dbf
- restoring datafile 00004 to /home/oracle/oradata/datafile/users01.dbf
- restoring datafile 00005 to /home/oracle/oradata/datafile/example01.dbf
- restoring datafile 00006 to /home/oracle/oradata/datafile/gg01.dbf
- channel ORA_DISK_1: reading from backup piece /home/oracle/rman/full_GG1_20120921_18
- channel ORA_DISK_1: restored backup piece 1
- piece handle=/home/oracle/rman/full_GG1_20120921_18 tag=TAG20120921T020917
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
- Finished restore at 21-SEP-12
- datafile 1 switched to datafile copy
- input datafile copy recid=8 stamp=794548607 filename=/home/oracle/oradata/datafile/system01.dbf
- datafile 2 switched to datafile copy
- input datafile copy recid=9 stamp=794548607 filename=/home/oracle/oradata/datafile/undotbs01.dbf
- datafile 3 switched to datafile copy
- input datafile copy recid=10 stamp=794548607 filename=/home/oracle/oradata/datafile/sysaux01.dbf
- datafile 4 switched to datafile copy
- input datafile copy recid=11 stamp=794548607 filename=/home/oracle/oradata/datafile/users01.dbf
- datafile 5 switched to datafile copy
- input datafile copy recid=12 stamp=794548607 filename=/home/oracle/oradata/datafile/example01.dbf
- datafile 6 switched to datafile copy
- input datafile copy recid=13 stamp=794548607 filename=/home/oracle/oradata/datafile/gg01.dbf
- RMAN>
8. 恢复数据库应用日志。
- SQL> recover adtabase using backup controlfile until cancel;
- ORA-00905: missing keyword
- SQL> recover database using backup controlfile until cancel;
- ORA-00279: change 531086 generated at 09/21/2012 02:09:18 needed for thread 1
- ORA-00289: suggestion : /home/oracle/archivelog/1_10_794474474.dbf
- ORA-00280: change 531086 for thread 1 is in sequence #10
- Specify log: {=suggested | filename | AUTO | CANCEL}
- cancel
- Media recovery cancelled.
- SQL> alter database open resetlogs;
- alter database open resetlogs
- *
- ERROR at line 1:
- ORA-00344: unable to re-create online log '/home/oracle/oradata/gg1/redo01.log'
- ORA-27040: file create error, unable to create file
- Linux Error: 2: No such file or directory
这里在打开数据库时出现错误, 原因是因为在打开数据库时, ORACLE 会根据控制文件来创建,这里控制文件记录的位置,目标机上并没有, 所以我们在打开数据库时创建这个目录。
- [oracle@GOLDENGATE2 oradata]$ mkdir gg1
- [oracle@GOLDENGATE2 oradata]$ sqlplus / as sysdba
- SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 21 04:03:20 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
- With the Partitioning, OLAP and Data Mining options
- SQL> alter database open resetlogs;
- Database altered.
在这里会自动创建REDO和临时表空间数据文件。
9. 将日志文件重创建到/home/oracle/oradata/datafile下。
- SQL> select group#,bytes/1024/1024||'M',status from v$log;
- GROUP# BYTES/1024/1024||'M' STATUS
- ---------- ----------------------------------------- ----------------
- 1 50M UNUSED
- 2 50M UNUSED
- 3 50M CURRENT
- SQL> alter database drop logfile group 1;
- Database altered.
- SQL> alter database add logfile group 1('/home/oracle/oradata/datafile/redo01.log') size 50m;
- Database altered.
- SQL> alter database drop logfile group 2;
- Database altered.
- SQL> alter database add logfile group 2('/home/oracle/oradata/datafile/redo02.log') size 50m;
- Database altered.
- SQL> alter system switch logfile;
- System altered.
- SQL> alter system switch logfile;
- System altered.
- SQL> select group#,bytes/1024/1024||'M',status from v$log;
- GROUP# BYTES/1024/1024||'M' STATUS
- ---------- ----------------------------------------- ----------------
- 1 50M ACTIVE
- 2 50M CURRENT
- 3 50M INACTIVE
- SQL> alter database drop logfile group 3;
- Database altered.
- SQL> alter database add logfile group 3('/home/oracle/oradata/datafile/redo03.log') size 50m;
- Database altered.
- SQL> select member from v$logfile;
- MEMBER
- --------------------------------------------------------------------------------
- /home/oracle/oradata/datafile/redo03.log
- /home/oracle/oradata/datafile/redo02.log
- /home/oracle/oradata/datafile/redo01.log
10. 处理临时表空间文件。
查询临时文件:
- SQL> select name from v$tempfile;
- NAME
- --------------------------------------------------------------------------------
- /home/oracle/oradata/gg1/temp01.dbf
将临时表空间置为OFFLINE状态:
- SQL> alter database tempfile '/home/oracle/oradata/gg1/temp01.dbf' offline;
- Database altered.
主机移动文件位置:
- SQL> !mv /home/oracle/oradata/gg1/temp01.dbf /home/oracle/oradata/datafile/temp01.dbf
在控制文件中修改临时文件位置:
- SQL> alter database rename file '/home/oracle/oradata/gg1/temp01.dbf' to '/home/oracle/oradata/datafile/temp01.dbf';
- Database altered.
置临时表空间为ONLINE:
- SQL> alter database tempfile '/home/oracle/oradata/datafile/temp01.dbf' online;
- Database altered.
查询临时表空间文件:
- SQL> select name from v$tempfile;
- NAME
- --------------------------------------------------------------------------------
- /home/oracle/oradata/datafile/temp01.dbf
11. 关闭数据库,创建SPFILE文件,启动数据库
- SQL> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> create spfile from pfile='/home/oracle/initgg1.ora';
- File created.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 167772160 bytes
- Fixed Size 1218292 bytes
- Variable Size 83888396 bytes
- Database Buffers 75497472 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- Database opened.
- SQL>