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