用Rman 異機遷移

pentium發表於2012-10-09

***********************************************************************

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@] Normal 0 false false false MicrosoftInternetExplorer4

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章