Thursday, 21 March 2019

Database Migration To A Different Filesystem On The Same Server With Minimal Downtime

The Objective is to move all Database files from old location [/csrprd/datafile/OEMDB] to the new location [/u02/oracle/oradata/OEMDB] within a minimal downtime window.

First Take an RMAN Backup As Copy to the new filesystem:
The below script will create a Copy Backup under the new filesystem /u02/oracle/oradata/OEMDB, if you re-run the same script for the second time it will refresh (sync) the already exist Backup Copy using a temporary incremental backup without the need to create a new Copy Backup from scratch which will save much time.

RMAN> run{
allocate channel F1 type disk format '/u02/oracle/oradata/OEMDB/%U';
allocate channel F2 type disk format '/u02/oracle/oradata/OEMDB/%U';
allocate channel F3 type disk format '/u02/oracle/oradata/OEMDB/%U';
DATABASE FORMAT '/u02/oracle/oradata/OEMDB/%d_%t_%s_%p'; # Incremental Level 1 Backup to recover the Image COPY.
RECOVER COPY OF DATABASE WITH TAG 'DB_COPY_UPDTD_BKP'; # Recover Image Copy with the Incr lvl1.
DELETE noprompt backup TAG 'DB_COPY_UPDTD_BKP'; # Delete [only] the incrmental bkp used for recovery.
#DELETE noprompt backup TAG 'arc_for_image_recovery' completed before 'sysdate-1'; # Delete Archive bkp for the previous recover.
DELETE noprompt copy   TAG 'ctrl_after_image_reco'; # Delete Controlfile bkp for the previous run.
BACKUP as copy current controlfile format '/u02/oracle/oradata/OEMDB/ctl_%U' tag 'ctrl_after_image_reco'; # Controlfile Copy Backup.
sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''/u02/oracle/oradata/OEMDB/controlfile.trc'' reuse"; # Controlfile Trace Backup.
sql "create pfile=''/u02/oracle/oradata/OEMDB/init${ORACLE_SID}.ora'' from spfile"; # Backup SPFILE.

In order to minimize the downtime during the migration, it's recommended to Sync the Copy Backup files using the same above command just before starting the actual migration.

Re-name the controlfile to point to the new location [/u02/oracle/oradata/OEMDB]:
Because I'm doing this practice on a test machine I'm keeping the control files on the same location which is not a best practice.

SQL> alter system set control_files='/u02/oracle/oradata/OEMDB/o1_mf_dmj045mz_.ctl','/u02/oracle/oradata/OEMDB/o1_mf_dmj045o9_.ctl' scope=spfile;

Generate the Shell command that will be used to move the controlfiles to the new filesystem during the actual migration:
SQL> sho parameter control
SQL> select 'cp '||name||'  /u02/oracle/oradata/OEMDB/'||SUBSTR(name, INSTR(name,'/', -1,1)+1) from v$controlfile;

Change all the parameters pointing to the old database files location to point to the new location:
Only if these parameters are already set:
SQL> ALTER SYSTEM SET db_create_file_dest='/u02/oracle/oradata/OEMDB'         SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='/u02/oracle/oradata/OEMDB' SCOPE=SPFILE;
-- In case the Archivelogs and Flashback logs will be stored in the new location:
SQL> ALTER SYSTEM SET db_recovery_file_dest='/u02/oracle/oradata/OEMDB'       SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/oracle/oradata/OEMDB' SCOPE=SPFILE;

Actual Database Files Migration:
SQL> shu immediate
 -- Copy the controlfiles to new location:
 # cp  /csrprd/datafile/OEMDB/o1_mf_dmj045mz_.ctl         /u02/oracle/oradata/OEMDB/o1_mf_dmj045mz_.ctl
 # cp  /csrprd/datafile/OEMDB/o1_mf_dmj045o9_.ctl  /u02/oracle/oradata/OEMDB/o1_mf_dmj045o9_.ctl 
SQL> startup mount

Using RMAN, Switch the database to the Image Copy Backup:
RMAN> switch database to copy;

Copy the REDOLOG files to the new location:
SQL>  select 'cp '||member||'  /u02/oracle/oradata/OEMDB/'||SUBSTR(member, INSTR(member,'/', -1,1)+1) from v$logfile;
 # cp /u02/oracle/oradata/OEMDB/onlinelog/o1_mf_3_dmj0483s_.log  /csrprd/datafile/OEMDB/

-- Prepare and Execute the commands for renaming the REDOLOG files:
--prepare the script:
SQL> select 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO ''/u02/oracle/oradata/OEMDB/'||SUBSTR(member, INSTR(member,'/', -1,1)+1)||''' ;' from v$logfile ORDER BY 1;
SQL> ALTER DATABASE RENAME FILE '/csrprd/datafile/OEMDB/o1_mf_1_dmj0480w_.log'     TO '/u02/oracle/oradata/OEMDB/o1_mf_1_dmj0480w_.log' ;

RMAN> recover database;
RMAN> alter database open;

Re-create the Temporary tablespaces:
SQL> create temporary tablespace temp1 tempfile '/u02/oracle/oradata/OEMDB/temp1.dbf' size 10m;

SQL> alter database default temporary tablespace temp1;

-- Check & kill the sessions that still using the old TEMP tablespace:
set lines 170 pages 1000
      col sid_serial for a15
      col OSUSER for a20
      col module for a30
      col SPID for a10
      col TABLESPACE for a15
      select s.sid || ',' || s.serial# sid_serial, p.spid, s.username, s.osuser,
      s.module,sum (o.blocks) * t.block_size / 1024 / 1024 mb_used ,count(*) sorts, s.sql_id,o.tablespace
      from v$sort_usage o, v$session s, dba_tablespaces t, v$process p
      where o.session_addr = s.saddr and s.paddr = p.addr and o.tablespace = t.tablespace_name
      group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,s.program, t.block_size,s.sql_id, o.tablespace
      order by o.tablespace,mb_used,s.username;

SQL> drop tablespace temp including contents and datafiles;
SQL> alter tablespace temp1 rename to temp;

Make sure that All DB files are pointing to the new location:
SQL> select name from v$datafile
select name from v$tempfile
select member from v$logfile
select name from v$controlfile;

SQL> sho parameter dest

Well done. The database is now migrated to the new location.

No comments:

Post a Comment