Shulou(Shulou.com)06/01 Report--

If you are just moving a tablespace or data file, you can first move the tablespace or data file to a location after offline, and pass the

Alert database rename and alter tablespace rename can write the position change to the control file.

Example migration data file

ALTER DATABASE DATAFILE 'data files to be migrated' OFFLINE

Copy to a new location

ALTER DATABASE RENAME FILE 'request the migrated data file' TO 'new location data file'

Media recovery

RECOVE DATAFILE 'data file for new location'

ONLINE the corresponding data file

ALTER DATABASE DATAFILE 'data file for new location'

Example migration tablespace

Change the tablespace OFFLINE

ALTER TABLESPACE tablespace name OFFLINE

Copy to a new location

ALTER TABLESPACE tablespace name RENAME DATAFILE 'old location file' TO 'new location file'

Tablespace ONLINE

ALTER TABLESPACE tablespace name ONLINE

Migrate the entire database in MOUNT

Need to consider the migration of data files, undo, Temp,Redo and control files

Data file

1 View the location of the data file

SELECT FILE_NAME FROM DBA_DATA_FILES

UNION ALL

SELECT FILE_NAME FROM DBA_TEMP_FILES

2 create a new folder to store data files

Mkdir

(3) write a script for backup (in fact, it can also be done manually, but on the one hand, if you need to enter a large number of commands manually, on the other hand, it may not go well due to the disconnection.)

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 $RMAN_LOG_FILE

Exit

Remember to give the script permission to chmod here

4 Boot DB to MOUNT state

Shoutdown immediate

Startup mount

5 start script

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

$jobs

$jobs

Confirm whether 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 sysdbarename.out 2 > & 1 &

7 check the database to verify whether the migration is successful

SELECT FILE_NAME FROM DBA_DATA_FILES

UNION ALL

SELECT FILE_NAME FROM DBA_TEMP_FILES

Here we will find that the temporary tablespace may not have been migrated, and then we will deal with the temporary tablespace data file.

8 temporary tablespace data file processing

Although we implemented the COPY temporary tablespace at 5, it was not as successful as COPY, and RMAN did not back up the tempfiles of the backup locally managed. The reason is that

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 when copying a data file, you don't need to copy the temporary tablespace, you just need to add a data file to the temporary tablespace and DROP the temporary file in the original directory.

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'

Verify the data file 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 file and so on.

Dealing with 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 add a redo logfile to each group, and then drop the logfile drop in the old directory

If we need an assistant here, we can only drop other states of logfile in drop inactive and unused.

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 the addition is successful

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

2 delete the old logfile

Suppose here group 1 is ACTIVE, and we can't drop, so we first drop 2 and 3, then switch logfile, in drop group 1.

ALTER DATABASE DROP LOGFILE MEMBER'/ u01qqappUniqoracleUnixoradataUniversDaveUniverse 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'

The last error message. 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'

Verification

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 a new location, and 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

The great task has been completed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.