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';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'DB_COPY_UPDTD_BKP'
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;
--Execute:
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
union
select name from v$tempfile
union
select member from v$logfile
union
select name from v$controlfile;

SQL> sho parameter dest

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

All About Statspack

Are DBAs still using Statspack instead of AWR? Yes
Why?

- It's the only option for Standby databases as you cannot genereate an AWR report on the Standby DB because the data will represent the Primary DB not the DR [Technical details are mentioned in the second half of this article]

- AWR is costly, if you use it one time you need to pay for the Diagnostic Pack license which is not cheap for small and mid-size companies.
https://www.oracle.com/assets/technology-price-list-070617.pdf

- Statspack is 80% of the AWR except it doesn't include the information about clusterware and other minor components, but still it can do 80% of the job for you.

- Although Statspack doesn't have the fancy HTML look of the AWR, but this shouldn't make you unsatisfied if you are an expert DBA 😅

- AWR is not available in Standard Edition, yes you can generate an AWR report on Standard Edition but it will be showing a useless report with zero values, then Oracle Licensing Audit Department will ask you to upgrade to Enterprise Edition because you tried to generate an AWR report on a SE, it sounds silly but this actually happened to me!

Conclusion, if you will buy the Diagnostic Pack license mainly for AWRs, I believe you will be paying too much just for the 20% of the information which is only available in AWR reports.

Now let's jump to the technical part which suppose to cover most of technical challenges when you deal with Statspack.


Statspack Installation:

Prerequisites:
- Although you can use SYSAUX tablespace to store statspack data, it's recommended to create a separate tablespace dedicated only for STATSPACK, e.g. TOOLS tablespace.
- For better performance analysis set the dynamic initialization parameter "timed_statistics" to true [Default].
ALTER SYSTEM SET timed_statistics=TRUE;

STATSPACK Installation Steps:

SQL>  connect / as sysdba
SQL>  @?/rdbms/admin/spcreate
PERFSTAT password:
default tablespace: assign "TOOLS" as a default tablespace for PERFSTAT
temporary tablespace:

SQL>  connect / as sysdba
SQL>  grant create job to PERFSTAT;

To take a snapshot manually: [For reference]
SQL>  connect perfstat/perf#123SQL>  execute statspack.snap;

Disable AWR Auto Snapshots: [Optional]
SQL> execute dbms_workload_repository.modify_snapshot_settings (   interval => 0,   retention => 43200);
Create a job to take a snapshot every 30min:
SQL>
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAP',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=HOURLY; BYHOUR=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23; BYMINUTE=4,34',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Statspack automated snap');
END;
/

Create a job to Purge snapshotss older than one month:
-- Create the procedure:

SQL> Create or replace PROCEDURE purge_stats IS   loop_counter number;
   v_num number;
   tot_num number;
   CURSOR get_num IS
   SELECT round((count(*)/50),0) from stats$snapshot where snap_time < sysdate-31;
begin
   open get_num;
   FETCH get_num INTO tot_num;
   close get_num;
   tot_num := tot_num + 1;
   for loop_counter in 1 .. tot_num loop
       delete from stats$snapshot where snap_time < sysdate-31 and rownum <51;
       commit;
   end loop;
end;
/

-- Create the job:
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PURGE_STATSPACK_SNAP',
job_type => 'STORED_PROCEDURE',
job_action => 'purge_stats',
repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=13',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Purge Statspack snaps older than 31 days');
END;
/

Note: The time required to create a snapshot is dependent on the shared_pool_size,the larger the shared pool, the longer it will take to create a snapshot.

Generate a STATSPACK report:
SQL>  connect perfstat/perf#123
SQL>  @?/rdbms/admin/spreport

- You have to provide at least 2 snapshots IDs where the database didn't restart in between.
- It will ask for the report name.
- The report ill be created under the current working directory.

Advanced Tasks:


Mark a snapshot as a baseline

You can mark group of snapshots as a baseline represent your DB performance.
The snapshots inside the baseline will be kept and will not be deleted even by STATSPACK.PURGE.

Examples:

--This will make the snapshots from snap_id 45 to snap_id 50 in a baseline.
SQL> exec statspack.make_baseline (i_begin_snap => 45,i_end_snap => 50);

--This will make only snapshots 45,50 as a baseline.
SQL> exec statspack.make_baseline (45,50,false);

--This will take the snapshots between 9am to 12pm @31Aug.
SQL> exec statspack.make_baseline (
todate('31-AUG-2018 09:00','DD-MON-YYY HH24:MI'),
todate('31-AUG-2018 12:00','DD-MON-YYY HH24:MI');

--This will remove the baseline mark on the snapshots between 9am to 12pm and make them ordinary snapshots can be deleted:
SQL> exec statspack.clear_baseline (
todate('31-AUG-2018 09:00','DD-MON-YYY HH24:MI'),
todate('31-AUG-2018 12:00','DD-MON-YYY HH24:MI');


Manually Purge specific snapshots:
SQL>  connect perfstat/perf#123
SQL>  set transaction use rollback segment rbig;
SQL>  @?/rdbms/admin/sppurge

It will ask for the snapshot ID to begin with and the number of snapshots it will be purged after:
SQL>  connect perfstat/perf#123
SQL>  @?/rdbms/admin/sptrunc

Remove STATSPACK:
[AS SYSDBA]
SQL> @?/rdbms/admin/spdrop.sql
SQL> @?/rdbms/admin/sbdrop


STATSPACK for Standby DB:

As you know, you cannot generate neither an AWR nor a STATSPACK report directly on the standby database as the available statistics on the standby DB are a replica from the primary DB. Oracle recommends to install STATSPACK for standby on the primary DB itself, where performance stats will be gathered from the standby DB via DB link and will be stored on the Primary DB.

To install STATSPACK for a StandbyDB [orcldr]:
[FROM the Primary DB]
[AS SYSDBA]
SQL> @?/rdbms/admin/sbcreate
-- Provide a password for the stdbyuser user that will be created.
Default Tablespace: TOOLS
Temp Tablespace: TEMP

Do you want to continue (y/n) ?
Enter value for key: y
Enter value for tns_alias: orcldr1    -- STANDBY Instance TNS alias
Enter value for perfstat_password: -- Same passowrd

In case your Standby DB is a RAC setup, add the second STANDBY Instance2:
[FROM Primary DB]
SQL> connect stdbyperf/perf#123
SQL> @?/rdbms/admin/sbaddins
Enter value for tns_alias: orcldr2 -- STANDBY Instance2 TNS alias
Enter value for perfstat_password: -- Same passowrd

Create a job to automatically gather STATSPACK Snapshots from the Standby:
[FROM Primary DB]
SQL> conn / as sysdbaSQL> grant create job to stdbyperf;
SQL> connect stdbyperf/perf#123

SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAP_STANDBY_NODE1',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_ORCLDR_ORCLDR1.snap',
repeat_interval => 'FREQ=HOURLY; BYHOUR=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23; BYMINUTE=4,34',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Statspack automated snap for ORCLDR1');
END;
/

In case your Standby DB is a RAC setup, you have to create another job to create Snapshots for the second STANDBY Instance2:
[FROM Primary DB]
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAP_STANDBY_NODE2',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_ORCLDR_ORCLDR2.snap',
repeat_interval => 'FREQ=HOURLY; BYHOUR=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23; BYMINUTE=4,34',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Statspack automated snap for ORCLDR2');
END;
/

Create a job to automatically purge old Snapshots:[Older then a month]

-- Create Purge Procedure to be called from purge job:
[FROM Primary DB]
SQL> Create or replace PROCEDURE purge_stats IS
   loop_counter number;
   v_num number;
   tot_num number;
   CURSOR get_num IS
   SELECT round((count(*)/50),0) from stats$snapshot where snap_time < sysdate-31;
begin
   open get_num;
   FETCH get_num INTO tot_num;
   close get_num;
   tot_num := tot_num + 1;
   for loop_counter in 1 .. tot_num loop
       delete from stats$snapshot where snap_time < sysdate-31 and rownum <51;
       commit;
   end loop;
end;
/

-- Create the job:
[FROM Primary DB]
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PURGE_STATSPACK_SNAP_STNDBY',
job_type => 'STORED_PROCEDURE',
job_action => 'purge_stats',
repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=13',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Purge Statspack snaps older than 31 days');
END;
/

Create a snapshot manually: [For reference]
[FROM Primary DB]
SQL> connect stdbyperf/perf#123
SQL> exec statspack_ORCLDR_ORCLDR1.snap --Create Snapshot for STANDBY Instance1
SQL> exec statspack_ORCLDR_ORCLDR2.snap --Create Snapshot for STANDBY Instance2

Generate a STATSPACK Report on the Standby DB:
[FROM Primary DB]
SQL> connect stdbyperf/perf#123
     @?/rdbms/admin/sbreport

To Purge Snapshots manually: [For reference][FROM Primary DB]
SQL> connect stdbyperf/perf#123
SQL>@?/rdbms/admin/sbpurge

Delete an instance from the STATSPACK configuration:
[FROM Primary DB]
SQL> connect stdbyperf/perf#123
SQL> @?/rdbms/admin/sbdelins