Example of Oracle moving the entire database location using RMAN copy

Source: Internet
Author: User

 

I. Data Migration instructions

During DBA's work, I may encounter data migration, such as migrating a local disk to ASM or changing the storage device. Then I need to migrate the storage location of the entire database.

 

If you only want to move a tablespace or data file, you can move the tablespace or data file offline and write the location change to the control file using alter database Rename and alter tablespace rename.

 

(1) By data file:

1. First offline the corresponding data file

Alter database datafile 'd:/Oracle/oradata/DBA/test01.dbf' offline;

2. copy the data file to a new location.

3. Alter database rename file 'd:/Oracle/oradata/DBA/test01.dbf 'to 'd:/test01.dbf ';

 

4. Media recovery (offline data files must be restored by media)

Recover datafile 'd:/test01.dbf'

5. Upload the corresponding data file online

SQL> alter database datafile 'd:/test01.dbf' online;

 

(2) By tablespace:

1. Replace the corresponding tablespace offline first

SQL> alter tablespace test offline;

 

2. copy the data file to a new location.

3. Alter tablespace test rename datafile 'd:/test01.dbf 'to 'd:/Oracle/oradata/DBA/test01.dbf'

 

4. Online

SQL> alter tablespace test online;

 

 

Differences between alterdatabase and altertablespace offline

Http://blog.csdn.net/tianlesoftware/article/details/4898800

 

 

Here we will demonstrate how to use RMAN copy to migrate the entire database. This operation must be performed on the database in the Mount state.

 

2. Transfer the entire database in the Mount status

If you just move the data file, the operation is relatively simple. The procedure is similar to that described in section 1. It is only because the database changes to the Mount state and we do not need to move the table space or data file offline.

For full-database transfer, we also need to consider the following file location transfer: data files, undo, temp, redo and control files.

 

1. Move data file location 1. View datafile location:

 

SQL & gt; Set Lin 120

SQL> Col file_name for a70

SQL> select file_name from dba_data_files

2 Union all

3 select file_name from dba_temp_files;

 

File_name

----------------------------------------------------------------------

/U01/APP/Oracle/oradata/Dave/users01.dbf

/U01/APP/Oracle/oradata/Dave/undotbs01.dbf

/U01/APP/Oracle/oradata/Dave/sysaux01.dbf

/U01/APP/Oracle/oradata/Dave/system01.dbf

/U01/APP/Oracle/oradata/Dave/example01.dbf

/U01/APP/Oracle/oradata/Dave/temp01.dbf

 

6 rows selected.

 

2. Create a new directory to store data files:

[Oracle @ Dave oradata] $ pwd

/U01/APP/Oracle/oradata

[Oracle @ Dave oradata] $ mkdir-P/u01/APP/Oracle/oradata/Anqing

[Oracle @ Dave oradata] $ ls

Anqing Dave

 

3. Compile the RMAN Script: rcopy. sh:

Here, you can directly run the Copy command. If there are many data files, you are advised to run the script in the background. This will not cause problems due to connection interruption, and the operation will be safer.

 

#! /Bin/KSh

Export lang = en_us

Rman_log_file =$ {0}. Out

ORACLE_HOME =/u01/APP/Oracle/product/11.2.0/db_1

Export ORACLE_HOME

RMAN = $ ORACLE_HOME/bin/RMAN

Export RMAN

Oracle_sid = Dave

Export oracle_sid

Oracle_user = Oracle

Export oracle_user

 

Echo "oracle_sid: $ oracle_sid" >>$ rman_log_file

Echo "ORACLE_HOME: $ ORACLE_HOME" >>$ rman_log_file

Echo "oracle_user: $ oracle_user" >>$ rman_log_file

Echo "====================================" >>$ rman_log_file

 

Chmod 666 $ rman_log_file

 

$ RMAN nocatalog target/msglog $ rman_log_file append <EOF

Run

{

Allocate channel C1 type disk;

Allocate channel C2 type disk;

Copy datafile '/u01/APP/Oracle/oradata/Dave/users01.dbf' to '/u01/APP/Oracle/oradata/Anqing/users01.dbf ';

Copy datafile '/u01/APP/Oracle/oradata/Dave/undotbs01.dbf' to '/u01/APP/Oracle/oradata/Anqing/undotbs01.dbf ';

Copy datafile '/u01/APP/Oracle/oradata/Dave/sysaux01.dbf' to '/u01/APP/Oracle/oradata/Anqing/sysaux01.dbf ';

Copy datafile '/u01/APP/Oracle/oradata/Dave/system01.dbf' to '/u01/APP/Oracle/oradata/Anqing/system01.dbf ';

Copy datafile '/u01/APP/Oracle/oradata/Dave/example01.dbf' to '/u01/APP/Oracle/oradata/Anqing/example01.dbf ';

Copy datafile '/u01/APP/Oracle/oradata/Dave/temp01.dbf' to '/u01/APP/Oracle/oradata/Anqing/temp01.dbf ';

 

/U01/APP/Oracle/oradata/Dave/users01.dbf

 

Release Channel C2;

Release Channel C1;

}

EOF

 

Echo> $ rman_log_file

Exit

 

 

-- Grant the execution permission:

[Oracle @ Dave u01] $ chmod 755 rcopy. Sh

 

4. Start the database to the Mount status:

SQL> shutdown immediate

Database closed.

Database dismounted.

Oracle instance shut down.

SQL> startup Mount

Oracle instance started.

 

Total system global area 818401280 bytes

Fixed size 2232800 bytes

Variable Size 490737184 bytes

Database buffers 322961408 bytes

Redo buffers 2469888 bytes

Database mounted.

 

5. Execute the RMAN copy script:

[Oracle @ Dave u01] $ nohup sh/u01/rcopy. Sh> rcopy. out 2> & 1 &

[1] 5249

[Oracle @ Dave u01] $ jobs

[1] + running nohup sh/u01/rcopy. Sh> rcopy. out 2> & 1 &

[Oracle @ Dave u01] $ jobs

[1] + done nohup sh/u01/rcopy. Sh> rcopy. out 2> & 1

 

-- Confirm copy:

[Root @ Dave Anqing] # ls-lH

Total 1.7 GB

-RW-r ----- 1 Oracle oinstall 347 M Sep 1::27 example01.dbf

-RW-r ----- 1 Oracle oinstall 571 M Sep 12 01: 25sysaux01. DBF

-RW-r ----- 1 Oracle oinstall 721 m Sep 1::26 system01.dbf

-RW-r ----- 1 Oracle oinstall 96 m Sep 12 0:24 undotbs01.dbf

-- Note that the temp data file is not copied successfully.

 

 

6. Rename Data File

 

Or use the background operation. The script rename. Sh is as follows:

#! /Bin/KSh

Sqlplus/As sysdba <EOF

Alter database rename File '/u01/APP/Oracle/oradata/Dave/users01.dbf' to '/u01/APP/Oracle/oradata/Anqing/users01.dbf ';

Alter database rename File '/u01/APP/Oracle/oradata/Dave/undotbs01.dbf' to '/u01/APP/Oracle/oradata/Anqing/undotbs01.dbf ';

Alter database rename File '/u01/APP/Oracle/oradata/Dave/sysaux01.dbf' to '/u01/APP/Oracle/oradata/Anqing/sysaux01.dbf ';

Alter database rename File '/u01/APP/Oracle/oradata/Dave/system01.dbf' to '/u01/APP/Oracle/oradata/Anqing/system01.dbf ';

Alter database rename File '/u01/APP/Oracle/oradata/Dave/example01.dbf' to '/u01/APP/Oracle/oradata/Anqing/example01.dbf ';

Exit

EOF

 

-- Grant execution permission:

[Oracle @ Dave u01] $ chmod 755 rename. Sh

 

[Oracle @ Dave u01] $ nohup sh rename. Sh> rename. out 2> & 1 &

 

7. Open Database Verification

SQL & gt; Set Lin 120

SQL> Col file_name for a70

SQL> select file_name fromdba_data_files

2 Union all

3 select file_name fromdba_temp_files;

 

File_name

----------------------------------------------------------------------

/U01/APP/Oracle/oradata/Anqing/users01.dbf

/U01/APP/Oracle/oradata/Anqing/undotbs01.dbf

/U01/APP/Oracle/oradata/Anqing/sysaux01.dbf

/U01/APP/Oracle/oradata/Anqing/system01.dbf

/U01/APP/Oracle/oradata/Anqing/example01.dbf

/U01/APP/Oracle/oradata/Dave/temp01.dbf

 

6 rows selected.

 

Note that the temp tablespace is still in the original position. Next we will process temp datafile.

 

8. Processing temp datafile

We copied the temp file in step 5, but the copy was not successful. The error of RMAN copy is as follows:

Starting backup at 12-sep-12

Released channel: C1

Released channel: C2

RMAN-00571: ========================================================== ==============================

RMAN-00569: ==================== error messagestack follows ======================

RMAN-00571: ========================================================== ==============================

RMAN-03002: failure of Backup command at09/12/2012 01:27:20

RMAN-20201: datafile not found in therecovery catalog

RMAN-06010: Error while looking updatafile:/u01/APP/Oracle/oradata/Dave/temp01.dbf

 

RMAN does not back up the tempfiles of locally managed during Backup. The reason is as follows:

1. Locally managed tempfiles are always setto nologging mode. So thus will have no undo.

2. extents are managed by bitmap in each datafile to keep track of free or usedstatus of blocks in that datafile.

3. The data dictionary does not manage the tablespace.

4. rollback information is not generated because there is no update on the datadictionary.

5. Media recovery does not recognize tempfiles.

 

So we do not need to copy temporary tablespace when copying data files. You only need to add a data file to the temporary tablespace after the copy operation, and then drop the temporary file in the original directory. This operation is required as follows:

 

-- This operation must be executed in the dbopen state:

SQL> alter tablespace temp add tempfile '/u01/APP/Oracle/oradata/Anqing/temp01.dbf' size 500 m autoextend off;

 

Tablespace altered.

 

SQL> alter tablespace temp drop tempfile '/u01/APP/Oracle/oradata/Dave/temp01.dbf ';

 

Tablespace altered.

 

SQL>

 

In Verification:

SQL & gt; Set Lin 120

SQL> Col file_name for a70

SQL> select file_name fromdba_data_files

2 Union all

3 select file_name fromdba_temp_files;

 

File_name

----------------------------------------------------------------------

/U01/APP/Oracle/oradata/Anqing/users01.dbf

/U01/APP/Oracle/oradata/Anqing/undotbs01.dbf

/U01/APP/Oracle/oradata/Anqing/sysaux01.dbf

/U01/APP/Oracle/oradata/Anqing/system01.dbf

/U01/APP/Oracle/oradata/Anqing/example01.dbf

/U01/APP/Oracle/oradata/Anqing/temp01.dbf

 

6 rows selected.

 

All the data files have been transferred successfully this time. As long as we do not enter the wrong directory, We can boldly RM the previous data files.

 

Oracle Control File

Http://blog.csdn.net/tianlesoftware/article/details/4974440

Oracle temp temporary tablespace

Http://blog.csdn.net/tianlesoftware/article/details/4697417

 

 

9. Process redo log file: 1. View redo information:

SQL & gt; Set Lin 120

SQL> Col member for A60

SQL> select group #, type, member fromv $ logfile;

 

Group # type member

-----------------------------------------------------------------------------

3 online/u01/APP/Oracle/oradata/Dave/redo03.log

2 online/u01/APP/Oracle/oradata/Dave/redo02.log

1 online/u01/APP/Oracle/oradata/Dave/redo01.log

 

SQL> selectgroup #, thread #, archived, status, Bytes/1024/1024 from V $ log;

 

Group # thread # arc Status bytes/1024/1024

------------------------------------------------------

1 1 no current 50

2 1 No inactive 50

3 1 No inactive 50

 

SQL> select B. Group #, B. Status, A. Member from V $ logfile A, V $ log B where a. Group # = B. Group # order by 1;

 

Group # status Member

--------------------------------------------------------------------------------------

1 Current/u01/APP/Oracle/oradata/Dave/redo01.log

2 inactive/u01/APP/Oracle/oradata/Dave/redo02.log

3 inactive/u01/APP/Oracle/oradata/Dave/redo03.log

 

The solution here is simple. Add a redo logfile to each group and drop the logfile in the old directory. You can also add several new groups and drop the old group.

Note that we can only drop log files in inactive and unused states, but not in other States.

 

The procedure is as follows:

 

-- Add members to each group first:

SQL> alter database add logfile member '/u01/APP/Oracle/oradata/Anqing/redo01.log' to group 1;

 

Database altered.

 

SQL> alter database add logfile member '/u01/APP/Oracle/oradata/Anqing/redo02.log' to group 2;

 

Database altered.

 

SQL> alter database add logfile member '/u01/APP/Oracle/oradata/Anqing/redo03.log' to group 3;

 

Database altered.

 

-- Verification:

SQL> select B. Group #, B. Status, A. Member from V $ logfile A, V $ log B where a. Group # = B. Group # order by 1;

 

Group # status Member

--------------------------------------------------------------------------------------

1 Current/u01/APP/Oracle/oradata/Dave/redo01.log

1 Current/u01/APP/Oracle/oradata/Anqing/redo01.log

2 inactive/u01/APP/Oracle/oradata/Anqing/redo02.log

2 inactive/u01/APP/Oracle/oradata/Dave/redo02.log

3 inactive/u01/APP/Oracle/oradata/Anqing/redo03.log

3 inactive/u01/APP/Oracle/oradata/Dave/redo03.log

 

6 rows selected.

 

2. Drop the log file of the old directory:

Here group 1 is active, and we cannot drop it. So we first drop 2 and 3, then switch logfile, in drop Group 1.

 

 

 

SQL> alter database drop logfile member '/u01/APP/Oracle/oradata/Dave/redo03.log ';

 

Database altered.

 

SQL> alter database drop logfile member '/u01/APP/Oracle/oradata/Dave/redo02.log ';

 

Database altered.

 

SQL> alter database drop logfile member '/u01/APP/Oracle/oradata/Dave/redo01.log ';

Alter database drop logfile member '/u01/APP/Oracle/oradata/Dave/redo01.log'

*

Error at line 1:

ORA-01609: Log 1 is the current log forthread 1-cannot drop members

ORA-00312: Online log 1 thread 1: '/u01/APP/Oracle/oradata/Dave/redo01.log'

ORA-00312: Online log 1 thread 1: '/u01/APP/Oracle/oradata/Anqing/redo01.log'

 

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> alter database drop logfile member '/u01/APP/Oracle/oradata/Dave/redo01.log ';

 

Database altered.

 

-- Verification:

SQL> select B. Group #, B. Status, A. memberfrom v $ logfile A, V $ log B where a. Group # = B. Group # order by 1;

 

Group # status Member

--------------------------------------------------------------------------------------

1 active/u01/APP/Oracle/oradata/Anqing/redo01.log

2 current/u01/APP/Oracle/oradata/Anqing/redo02.log

3 inactive/u01/APP/Oracle/oradata/Anqing/redo03.log

 

Here, the redo log is also OK. The last one is the control file.

 

10. Process Control Files

The processing of the control file is very simple. Shut down the database, copy the control file to a new location, and modify the pfile parameter.

 

 

Note the following:

*. Control_files = '/u01/APP/Oracle/oradata/Dave/control01.ctl', '/u01/APP/Oracle/fast_recovery_area/Dave/control02.ctl'

 

Here is the configuration of Oracle 11G. Here there are only two control files, one of which is under the FRA directory. In Oracle 10 Gb, there will be three control files, all of which are under a directory.

 

SQL> Create pfile from spfile;

 

File Created.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

Oracle instance shut down.

 

[Oracle @ Dave] $ ls

Control01.ctl redo01.log redo03.log system01.dbf users01.dbf

Example01.dbf redo02.log sysaux01.dbf undotbs01.dbf

[Oracle @ Dave] $ CP control01.ctl ../Anqing

[Oracle @ Dave] $ CD ..

[Oracle @ Dave oradata] $ CD Anqing

[Oracle @ Dave Anqing] $ ls

Control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

Example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

 

SQL> Create spfile frompfile = '/u01/APP/Oracle/product/11.2.0/db_1/dbs/initdave. ora ';

 

File Created.

 

SQL> startup

Oracle instance started.

 

Total system global area 818401280 bytes

Fixed size 2232800 bytes

Variable Size 490737184 bytes

Database buffers 322961408 bytes

Redo buffers 2469888 bytes

Database mounted.

Database opened.

SQL> show parameter control_files

 

Name type value

-----------------------------------------------------------------------------

Control_files string/u01/APP/Oracle/oradata/Anqing

/Control01.ctl,/u01/APP/oracl

E/fast_recovery_area/Dave/cont

Rol02.ctl

 

 

So far, the entire database transfer operation using RMAN copy has ended. Now we can boldly drop the previous directory.

 

[Oracle @ Dave oradata] $ pwd

/U01/APP/Oracle/oradata

[Oracle @ Dave oradata] $ ls

Anqing Dave

[Oracle @ Dave oradata] $ Rm-RF Dave

[Oracle @ Dave oradata] $ ls

Anqing

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.