Friday, May 25, 2012

How to move SYSTEM tablespace with minimum downtime

Two weeks ago a colleague DBA asked me what would be the best way to move SYSTEM tablespace with minimum downtime. There are several ways to perform that task and I will note what would be the best way in my opinion.

Messing with SYSTEM tablespace is always tricky so my goal would be minimum risk and minimum downtime.

My testing environment is 11gR1 database on Linux 32bit OS.

After quick search on that subject I’ve noticed that DBA’s are comfortable with moving/renaming other datafiles but SYSTEM or UNDO are making problems. Some tablespaces are essential and cannot be taken offline while database is up and running.





⇒ My database.
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name AOCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     /u01/app/oradata/aocdb/system01.dbf
2    750      SYSAUX               ***     /u01/app/oradata/aocdb/sysaux01.dbf
3    1260     UNDOTBS1             ***     /u01/app/oradata/aocdb/undotbs01.dbf
4    2363     USERS                ***     /u01/app/oradata/aocdb/users01.dbf
5    1024     REST                 ***     /u01/app/oradata/aocdb/rest01.dbf
6    1024     INDX_REST            ***     /u01/app/oradata/aocdb/indx_rest01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    96       TEMP                 32767       /u01/app/oradata/aocdb/temp01.dbf

⇒ While database is up and running I will use RMAN to copy datafile of SYSTEM tablespace to another location.
RMAN> copy datafile 1 to '/u01/app/oradata/loc2/system01.dbf';

Starting backup at 15.05.2012 19:35:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oradata/aocdb/system01.dbf
output file name=/u01/app/oradata/loc2/system01.dbf tag=TAG20120515T193524 RECID=76 STAMP=783372953
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 15.05.2012 19:35:59


⇒ Now I will shutdown database and start in mount mode.
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1071333376 bytes

Fixed Size                     1318172 bytes
Variable Size                352322276 bytes
Database Buffers             713031680 bytes
Redo Buffers                   4661248 bytes


⇒ Switch datafile to copy.
RMAN> switch datafile 1 to copy;

datafile 1 switched to datafile copy "/u01/app/oradata/loc2/system01.dbf"

⇒ Now to perform quick recover.
RMAN> recover datafile 1;

Starting recover at 15.05.2012 19:38:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15.05.2012 19:38:57


⇒ That’s it! Open database.
RMAN> alter database open;

database opened


⇒ Little check that I’ve moved datafile.
RMAN> report schema;

Report of database schema for database with db_unique_name AOCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     /u01/app/oradata/loc2/system01.dbf
2    750      SYSAUX               ***     /u01/app/oradata/aocdb/sysaux01.dbf
3    1260     UNDOTBS1             ***     /u01/app/oradata/aocdb/undotbs01.dbf
…
…

⇒ After switching datafiles, old datafile become copy so it is safe to drop it.
RMAN> list copy of datafile 1;

List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
77      1    A 15.05.2012 19:38:15 5250088    15.05.2012 19:37:03
        Name: /u01/app/oradata/aocdb/system01.dbf

  

RMAN> delete copy of datafile 1;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
77      1    A 15.05.2012 19:38:15 5250088    15.05.2012 19:37:03
        Name: /u01/app/oradata/aocdb/system01.dbf

  
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u01/app/oradata/aocdb/system01.dbf RECID=77 STAMP=783373095
deleted datafile copy



So the only downtime was period during bouncing database and recovering datafile.

5 comments:

  1. I guess we can apply same procedure for any other DB file /tablespace , correct ? That would mean if we decide to relocate 10TB database the only downtime would be during switching files, which is great!

    ReplyDelete
  2. For SYSTEM or UNDO tablespaces you must bounce your instance but for other datafiles this step is not needed. You can flag other datafiles as read only during relocating and with this step recovery is not needed. This is the safest method which I use for moving datafiles.

    Regards,
    Marko

    ReplyDelete
  3. Undo doesn't necessarily have to be done this way. I typically pre-create a new UNDO tablespace, TEMP tablepsace, and new online redo log files so the only real shutdown dependency would be on the SYSTEM tablespace. I wasn't aware you could switch SYSAUX in this manner as well. For safety reasons, I tend to do SYSAUX at the same time as SYSTEM.

    ReplyDelete
  4. Bradd,

    my comment above is not completely correct. UNDO can be pre-created like you said and shutdown is not necessary in that case.
    I tend to shutdown database before moving UNDO,SYSTEM and SYSAUX but I should change this habit. Pre-creating UNDO is more convenient way.

    In post, why I meant that some DBA's are not comfortable with moving UNDO tablespace. In some situations "old UNDO" can be in PENDING OFFLINE mode for some time. Then you must wait pending transactions to finish before drop. This can be little confusing for inexperienced DBA.

    I didn't even tried to move SYSAUX on running instance.
    Thanks for the info!

    Kind regards,
    Marko

    ReplyDelete