Assumptions

Data Guard

If the database is part of a DataGuard Physical Standby configuration, you should perform these actions on both databases. In particular you should be aware of the STANDBY_FILE_MANAGEMENT parameter. If you add a datafile on the Primary database whilst one database is on ASM and the other is using a filesystem then you are likely to run into errors.

Preparation

Copy the server parameter file or initialization parameter file to a temporary location...

cp spfileMYDB.ora orig_spfileMYDB.ora

Generate a list of Controlfiles and Tempfiles for later use...

sqlplus / as sysdba

SPOOL /tmp/ctlandtmpfiles.txt

SELECT name FROM v$controlfile;

SELECT name FROM v$tempfile;

SPOOL OFF

Migration

Use RMAN to copy all datafiles to new location and archive current redo log...

rman target /

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

BACKUP AS COPY

 INCREMENTAL LEVEL 0

 DATABASE

 FORMAT '/u02/oradata/MYDB/'

 TAG 'ORA_FS_MIGRATION';

}

SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

Use RMAN to backup the SPFILE...

BACKUP as backupset spfile;

Turn Flashback off and Shutdown database

SQL "ALTER DATABASE FLASHBACK OFF";

SHUTDOWN IMMEDIATE;

Use RMAN to Mount database and restore SPFILE to new location (and shutdown again)...

STARTUP MOUNT;

RESTORE SPFILE TO '/u01/app/oracle/product/11.2.0.2/dbs/spfileMYDB.ora';

SHUTDOWN IMMEDIATE;

exit

Set new OMF parameters...

sqlplus / as sysdba

STARTUP FORCE NOMOUNT;

ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u02/oradata/MYDB' SID='*';

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/flashrecovery/MYDB' SID='*';

Set new Controlfile location...

STARTUP FORCE NOMOUNT;

ALTER SYSTEM SET CONTROL_FILES='/u01/oradata/MYDB/control01.ctl','/u02/oradata/MYDB/control02.ctl' SCOPE=SPFILE SID='*';

Use RMAN to restore Controlfile to new location...

rman target /

STARTUP FORCE NOMOUNT;

restore controlfile from "+ORADATA/mydb/controlfile/current.256.766249189";

Use RMAN to switch database to new location and recover...

ALTER DATABASE MOUNT;

SWITCH DATABASE TO COPY;

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

RECOVER DATABASE;

}

Re-enable Flashback and Open database...

sqlplus / as sysdba

ALTER DATABASE FLASHBACK ON;

ALTER DATABASE OPEN;

Drop tempfile(s) and add new one(s)...

ALTER DATABASE TEMPFILE '+ORADATA/mydb/tempfile/TEMP.263.766249203' drop;

ALTER TABLESPACE temp ADD TEMPFILE;

SELECT name FROM v$tempfile;

Recreate regular and standby Redo logs...

SET SERVEROUTPUT ON;

DECLARE

CURSOR rlc IS

SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL

FROM V$LOG

UNION

SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL

FROM V$STANDBY_LOG

ORDER BY 1;

stmt VARCHAR2(2048);

BEGIN

FOR rlcRec IN rlc LOOP

IF (rlcRec.srl = 'YES') THEN

stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||

rlcRec.thr || ' SIZE ' || rlcRec.bytes;

EXECUTE IMMEDIATE stmt;

stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;

EXECUTE IMMEDIATE stmt;

ELSE

stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||

rlcRec.thr || ' SIZE ' || rlcRec.bytes;

EXECUTE IMMEDIATE stmt;

BEGIN

stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;

DBMS_OUTPUT.PUT_LINE(stmt);

EXECUTE IMMEDIATE stmt;

EXCEPTION

WHEN OTHERS THEN

EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';

EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';

EXECUTE IMMEDIATE stmt;

END;

END IF;

END LOOP;

END;

/

You might need to run the above SQL twice if you get errors the first time. e.g.

Use RMAN to delete all foreign archivelogs..

rman target /

DELETE FOREIGN ARCHIVELOG ALL;

Use RMAN to delete files that are no longer required...

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

BACKUP BACKUPSET ALL DELETE INPUT;

BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;

}

Use RMAN to check database status...

list backup summary;

crosscheck datafilecopy all;

report obsolete;

delete obsolete;

report schema;

As 'grid' or 'asm'' (dependant on which OS user owns ASM)...

crsctl stop has

Bibliography

Last checked for relevance: March 2014