Oracle Learning-Data Migration

If you are migrating a tablespace or data file only, you can move the table space or data file offline after the location, and

Alert database rename and alter tablespace rename write position changes to the control file

Example migrating a data file

ALTER database datafile ' data file to be migrated ' OFFLINE;

Copy to New location

ALTER DATABASE RENAME file ' requests data files for migrated data files ' to ' new location ';

Media recovery

Recove datafile ' new location data file '

The corresponding data file online

ALTER database datafile ' new location data file ';

Example migration Table Space

Offline a tablespace

ALTER tablespace table space name OFFLINE;

Copy to New location

ALTER tablespace tablespace name RENAME datafile ' old location file ' to ' new location file ';

Table Space Online

ALTER tablespace table space name ONLINE;

Migrating an entire database in the Mount state

Need to consider the migration of data files, Undo, Temp,redo and control files

Data files

1 Viewing the location of your data files

SELECT file_name from Dba_data_files

UNION All

SELECT file_name from Dba_temp_files;

2 Create a new folder to hold the data file

Mkdir

3 Scripting for backup (you can actually manually but on the one hand you need to enter a large number of commands two aspects may be due to the connection interruption and lead to progress is not smooth)

Vim rcopy.sh

#!/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 ';

Release channel C2;

Release channel C1;

}

Eof

echo >> $RMAN _log_file

Exit

Remember to give the script permission here chmod

4 booting the DB into the Mount State

Shoutdown Immediate

Startup Mount

5 Startup scripts

$ nohup sh/u01/rcopy.sh>rcopy.out 2>&1 &

$ jobs

$ jobs

Verify that the data file has been copied to the specified folder when the task is completed

Ls-lh

6 Rname Data File

#!/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

Remember to give permission chmod

Run nohup sh rename.sh >rename.out 2>&1 &

7 viewing database validation for Migration success

SELECT file_name from Dba_data_files

UNION All

SELECT file_name from Dba_temp_files;

Here we will find that the temporary table space may not be migrated next we are working with temporary tablespace data files

8 Temporal tablespace data file processing

While we were in 5, we made a copy of the temporary table space but in fact did not copy successfully, RMAN will not back up the backup locally managed tempfiles. The reason is

1. Locally managed tempfiles is always setto nologging mode. So thus would have no undo.
2. Extents is managed by bitmap in the datafile to keep track of free or usedstatus of blocks on that datafile.
3. The data dictionary does not manage the tablespace.
4. Rollback information are not generated because there are no update on the datadictionary.
5. Media recovery does not recognize tempfiles.

So when copying a data file, you don't need to copy the temporary table space, just add a data file to the temporary table space and drop the temporary file in the original directory when you copy it.

Alter tablespace temp add tempfile '/u01/app/oracle/oradata/anqing/temp01.dbf ' size 500M autoextend off;

Alter tablespace temp drop tempfile '/u01/app/oracle/oradata/dave/temp01.dbf ';

Data File Validation again

SELECT file_name from Dba_data_files

UNION All

SELECT file_name from Dba_temp_files;

Check if there is no problem, you can delete the original data files, etc.

Handling Redo LOGFILE

1 View redo Information

SELECT group#, TYPE, MEMBER from $LOGFILE;

SELECT group#, thread#. Archived, STATUS, bytes/1024/1024 from V$log;

SELECT b.group#, B.status, A.member,

From V$logfile A, V$log b

WHERE a.group# = b.group#;

Here we give each group a redo logfile, and then drop the logfile in the old directory

We can only drop inactive and unused in the state of logfile other states cannot drop

ALTER DATABASE add logfile member '/u01/app/oracle/oradata/anqing/redo01.log ' to group 1;

ALTER DATABASE add logfile member '/u01/app/oracle/oradata/anqing/redo02.log ' to group 2;

ALTER DATABASE add logfile member '/u01/app/oracle/oradata/anqing/redo03.log ' to group 3;

Verify that you added the success

Select b.group#, B.status, A.member from V$logfile A, v$log b where a.group# = b.group# order by 1;

2 Deleting the old logfile

Assuming group 1 is active here, we cannot drop, so we drop 2 and 3 first, then switch logfile, in Drop Group 1.

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/dave/redo03.log ';

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/dave/redo02.log ';

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/dave/redo01.log ';

Last error we need to switch the log status

alter system switch logfile;

Drop again

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/dave/redo01.log ';

Verify

Select b.group#, B.status, A.memberfrom v$logfile A, v$log b where a.group# = b.group# order by 1;

Control File Migration

Shutdown the library, then copy the control file to the new location, modify the Pfile parameter.

Create Pfile from SPFile;

Shutdown immediate

CP old control File new control file

Create SPFile frompfile= '/u01/app/oracle/product/11.2.0/db_1/dbs/initdave.ora ';

Startup

Check

Show Parameter Control_files

Done

This article is from "someone who says I am a tech house" blog, please be sure to keep this source http://1992mrwang.blog.51cto.com/3265935/1413919