用Rman 異機遷移
***********************************************************************
Use rman to migrate source db to target db on same platform and oracle version
27-Sep-2012
The tested db is small, if for big db, need to redesign the full_backup.sh script to make the backup faster. And pay more attention to the source shutdown as downtime won’t be long in production.
[@more@]Needs to consider nologging impact during backup. May need to force logging.
About the Size of Backup Pieces:
RMAN will, by default, put the entire contents of a backup
set into one backup piece, regardless of the size of the backup set. If you are
backing up to a file system or media manager that has a limit on the maximum
file size that can be created, then you may need to restrict the size of backup
pieces that RMAN will create.To restrict the size of each backup piece, specify
the MAXPIECESIZE option of
the CONFIGURE CHANNEL or ALLOCATE CHANNEL commands. This option
limits backup piece size to the specified umber of bytes. If the total size of
the backup set is greater than the specified backup piece size, then multiple
physical pieces will be created to hold the backup set contents.
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2G;
+ If large pool is not configured then configure the large pool size and
enable the backup_tape_io_slaves :
LARGE_POOL_SIZE = 100 m
BACKUP_TAPE_IO_SLAVES = true
***********************************************************************
1. Install target database software, but don’t install database. Use same path as source.
get the data, redo log file path from source db:
select file_name from dba_data_files; select member from v$logfile;
Target:
cd /u01/app/oracle/product/10.2.0/db_1/
[oracle@rac2 db_1]$ mkdir oradata
[oracle@rac2 db_1]$ cd oradata/
[oracle@rac2 oradata]$ mkdir true
And set env on target.
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=true
2. On source, create pfile from spfile; check the audit, and dump dest, and create these directories on target.
*.audit_file_dest='/u01/app/oracle/product/10.2.0/db_1/admin/true/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/true/bdump'
*.core_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/true/cdump'
*.user_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/true/udump'
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area'
3. Create archive log destination in line with source.
SQL> archive log list
Or
SQL> Select name, value from v$parameter where name like ‘%arch%’;
Target:
[oracle@rac2 dbs]$ pwd
/u01/app/oracle/product/10.2.0
[oracle@rac2 10.2.0]$ mkdir arch
4. Create same backup directory as source on target.
Target:
[oracle@rac2 dbs]$ pwd
/u01/app/oracle/product/10.2.0
[oracle@rac2 10.2.0]$ mkdir backup
5. Create password file on target.
orapwd file=?/dbs/orapwtrue password=oracle
(If we want to use sysdba remotely, we require this passwd file. If admin db locally, use operating system authentication, the passwd file not required)
6. Backup source database, get the script to scripts directory. chmod u+x, and run the script: nohup ./full_backup.sh &
Backup script: full_backup.sh
[root@rac1 10.2.0]# pwd
/u01/app/oracle/product/10.2.0
[root@rac1 10.2.0]$ mkdir scripts
7. copy backup to target
scp * 143.168.1.102:/u01/app/oracle/product/10.2.0/backup
[oracle@rac1 backup]$ scp * 143.168.1.102:/u01/app/oracle/product/10.2.0/backup
The authenticity of host '143.168.1.102 (143.168.1.102)' can't be established.
RSA key fingerprint is ac:68:37:99:f1:be:6f:a7:2f:08:30:dd:41:56:0e:85.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '143.168.1.102' (RSA) to the list of known hosts.
arch_0snm8rem_1_1_20120927 100% 91MB 3.7MB/s 00:25
arch_0tnm8rem_1_1_20120927 100% 91MB 2.5MB/s 00:36
arch_0unm8rfq_1_1_20120927 100% 91MB 1.7MB/s 00:53
arch_0vnm8rfq_1_1_20120927 100% 45MB 1.7MB/s 00:27
arch_10nm8rgu_1_1_20120927 100% 4178KB 1.0MB/s 00:04
arch_11nm8rgu_1_1_20120927 100% 2122KB 2.1MB/s 00:01
arch_12nm8rgv_1_1_20120927 100% 15KB 15.0KB/s 00:00
ctl_file_13nm8rh3_1_1_20120927 100% 6944KB 6.8MB/s 00:01
true_0onm8r6v_1_1_20120927 100% 375MB 3.2MB/s 01:59
true_0pnm8r6v_1_1_20120927 100% 296MB 2.0MB/s 02:29
true_0qnm8reb_1_1_20120927 100% 6944KB 3.4MB/s 00:02
true_0rnm8red_1_1_20120927 100% 96KB 96.0KB/s 00:00
true_spfile_14nm8rh4_1_1_20120927 100% 96KB 96.0KB/s 00:01
*********************************************************************
Below is only for test on virtual machine:
2. Create a job to insert data to the table:
SQL> create user mig_test identified by oracle default tablespace users;
User created.
SQL> grant resource, CREATE SESSION to mig_test;
Grant succeeded.
SQL> grant execute on dbms_lock to mig_test;
SQL> conn mig_test/oracle
Connected.
SQL> create table load_data(id number(8), msg varchar2(100), whattime TIMESTAMP);
3. Run load_data.sh to keep loading data to load_data table so we can better test the rman recovery.
After finished backup, the last few rows in load_data table as below:
18121 Keep loading data to test rman recovery 27-SEP-12 04.32.40.861112 PM
18122 Keep loading data to test rman recovery 27-SEP-12 04.32.41.863074 PM
18123 Keep loading data to test rman recovery 27-SEP-12 04.32.42.864678 PM
18123 rows selected.
*********************************************************************
8. create pfile from spfile; (we can ignore this step, can restore from backup)
9. Get source db id:
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
TRUE 1384180587
10. On target, set env
[oracle@rac2 oradata]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
[oracle@rac2 oradata]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac2 oradata]$ export ORACLE_SID=true
11. Restore spfile file:
(If there’s change to control file path or others, we can restore to pfile, then change it:
restore spfile to pfile '/u01/app/oracle/product/10.2.0/db_2/dbs/inittrue.ora' from '/u01/app/oracle/product/10.2.0/backup/true_spfile_14nm8rh4_1_1_20120927';)
[oracle@rac2 backup]$ rman target / nocatalog
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 27 16:51:58 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> set dbid=1384180587
executing command: SET DBID
RMAN> restore spfile from '/u01/app/oracle/product/10.2.0/backup/true_spfile_14nm8rh4_1_1_20120927';
Starting restore at 27-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u01/app/oracle/product/10.2.0/backup/true_spfile_14nm8rh4_1_1_20120927
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 27-SEP-12
Then shutdown, and use the new restored spfile to start the instance, otherwise the control file will be restored to the path specified in init.ora.
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup nomount;
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 79693416 bytes
Database Buffers 197132288 bytes
Redo Buffers 7168000 bytes
12. Restore control file:
RMAN> restore controlfile from '/u01/app/oracle/product/10.2.0/backup/ctl_file_13nm8rh3_1_1_20120927';
Starting restore at 27-SEP-12
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:07
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/true/control01.ctl
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/true/control02.ctl
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/true/control03.ctl
Finished restore at 27-SEP-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
13. Restore database
RMAN> restore database;
Starting restore at 27-SEP-12
Starting implicit crosscheck backup at 27-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 29 objects
Finished implicit crosscheck backup at 27-SEP-12
Starting implicit crosscheck copy at 27-SEP-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-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 00002 to /u01/app/oracle/product/10.2.0/db_1/oradata/true/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/product/10.2.0/db_1/oradata/true/sysaux01.dbf
restoring datafile 00005 to /u01/app/oracle/product/10.2.0/db_1/oradata/true/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/backup/true_0pnm8r6v_1_1_20120927
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/backup/true_0pnm8r6v_1_1_20120927 tag=TRUE_HOT_DB_BK
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/product/10.2.0/db_1/oradata/true/system01.dbf
restoring datafile 00004 to /u01/app/oracle/product/10.2.0/db_1/oradata/true/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/backup/true_0onm8r6v_1_1_20120927
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/backup/true_0onm8r6v_1_1_20120927 tag=TRUE_HOT_DB_BK
channel ORA_DISK_1: restore complete, elapsed time: 00:02:12
Finished restore at 27-SEP-12
14. Recover database:
RMAN> recover database;
Starting recover at 27-SEP-12
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=23
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=24
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/backup/arch_11nm8rgu_1_1_20120927
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/backup/arch_11nm8rgu_1_1_20120927 tag=TAG20120927T160748
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/arch/1_23_795012654.dbf thread=1 sequence=23
archive log filename=/u01/app/oracle/product/10.2.0/arch/1_24_795012654.dbf thread=1 sequence=24
unable to find archive log
archive log thread=1 sequence=25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/27/2012 18:03:47
RMAN-06054: media recovery requesting unknown log: thread 1 seq 25 lowscn 578023
15. Test: create a table on source to test if it exists on target after recovery
SQL> conn / as sysdba
Connected.
SQL> create table recover_test as select * from all_tables;
************************************************************************************
16. Shutdown source database (Important steps…….)
(We need to do this step fast as this needs the source db is down!)
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> shutdown immediate
And transfer the archive log and redo log to target.
archive log:
scp * 143.168.1.102:/u01/app/oracle/product/10.2.0/arch
online redo log:
scp redo* 143.168.1.102:/u01/app/oracle/product/10.2.0/db_1/oradata/true
[oracle@rac1 true]$ scp redo* 143.168.1.102:/u01/app/oracle/product/10.2.0/db_1/oradata/true
redo01.log 100% 50MB 1.0MB/s 00:50
redo02.log 100% 50MB 1.3MB/s 00:39
redo03.log 100% 50MB 4.6MB/s 00:11
17. Recover database again:
RMAN> recover database;
Starting recover at 27-SEP-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/product/10.2.0/db_1/oradata/true/red o03.log
archive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/product/10.2.0/db_1/oradata/true/red o01.log
archive log filename=/u01/app/oracle/product/10.2.0/db_1/oradata/true/redo03.log thread=1 sequence=25
archive log filename=/u01/app/oracle/product/10.2.0/db_1/oradata/true/redo01.log thread=1 sequence=26
media recovery complete, elapsed time: 00:00:10
Finished recover at 27-SEP-12
18: Open database.
RMAN> alter database open resetlogs;
database opened
19: Validate: on source and target, the result is the same.
Select count(*) from recover_test;
COUNT(*)
----------
1575
Conn mig_test/oracle
Select * from load_data;
23945 rows selected
************************************************************************************
20: Recreate temp tablespace ? Below suggest to re-generate temp tablespace.
http://blog.csdn.net/tianlesoftware/article/details/6245209
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dave/temp01.dbf
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/dave/temp02.dbf ' size 8192M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dave/temp01.dbf
/u01/app/oracle/oradata/dave/temp02.dbf
SQL> alter database tempfile '/u01/app/oracle/oradata/dave/temp01.dbf' offline;
Database altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/dave/temp01.dbf' drop including datafiles;
Database altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dave/temp02.dbf
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14377/viewspace-1059554/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 用 RMAN 備份異機恢復 遷移資料
- 使用RMAN遷移資料庫到異機資料庫
- RMAN 異機遷移實戰操作-附加常用命令
- 用rman遷移資料庫資料庫
- 【遷移】使用rman遷移資料庫資料庫
- 用RMAN遷移檔案到ASM或從ASM遷出ASM
- RMAN遷移表空間
- Oracle rman duplicate遷移測試Oracle
- 【資料遷移1】Oracle 10gR2 rman異機恢復實驗（FS->RAW）（1）Oracle 10g
- 【資料遷移1】Oracle 10gR2 rman異機恢復實驗（FS->RAW）（2）Oracle 10g
- 使用RMAN遷移單庫到RAC
- RMAN之CONVERT整庫遷移
- 使用RMAN進行資料遷移
- Oracle 利用RMAN 完成資料遷移Oracle
- 【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM資料庫ASM
- 通過RMAN異機遷移資料庫並修改儲存路徑【相同位數與平臺版】資料庫
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 使用RMAN簡單遷移表空間
- Oracle資料庫遷移之一：RMANOracle資料庫
- RMAN遷移資料庫(rac or single)資料庫
- Oracle RMAN異機恢復Oracle
- ORACLE RMAN異機異目錄恢復Oracle
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 應用RMAN Transportable Database進行資料庫跨平臺遷移Database資料庫
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- 一次用rman做資料遷移的實戰經歷
- 【RMAN】windows2008 Oracle通過rman增量遷移WindowsOracle
- 使用RMAN完成跨平臺資料遷移
- 使用RMAN進行Oracle資料庫遷移Oracle資料庫
- 使用RMAN執行oracle ASM資料遷移OracleASM
- 利用RMAN跨平臺遷移資料庫資料庫
- rman進行跨平臺資料遷移
- oracle的RMAN異機恢復Oracle
- RMAN異機恢復總結
- Oracle使用RMAN從Windows遷移資料到LinuxOracleWindowsLinux
- 今晚遷移資料庫異常順利資料庫
- 虛擬機器遷移技術原理與應用虛擬機
- RMAN 異機複製資料庫資料庫