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.
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!
ReplyDeleteFor 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.
ReplyDeleteRegards,
Marko
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.
ReplyDeleteBradd,
ReplyDeletemy 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
Thanks!.
ReplyDelete