Step Wise Disk-Group Migration from DG01 to DG02
Scenario:
DBTST is an Oracle11g single instance database on ASM which holds complete database in Disk Group named DG01 spread across 4*50 GB of Disks. Objective is to migrate complete databases from DG01 to DG02 Disk Group. Here DG02 is a new Disk Group on 500 GB disk (/dev/mapper/mpathe)
col NAME for a20
col PATH for a40
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB TOTAL_MB FREE_MB NAME PATH
------- -------- ------- -------- ---------- ---------- -------- --------------- ----------------------------------------
1 2 CACHED NORMAL 51200 51200 43 DG01_0002 /dev/mapper/mpathc
1 1 CACHED NORMAL 51200 51200 41 DG01_0001 /dev/mapper/mpathb
1 3 CACHED NORMAL 51200 51200 45 DG01_0003 /dev/mapper/mpathd
1 0 CACHED NORMAL 51200 51200 46 DG01_0000 /dev/mapper/mpatha
0 0 CLOSED NORMAL 512000 0 0 /dev/mapper/mpathe
Migration Steps:
1. Create a New Disk Group DG02
SQL> create diskgroup DG02 external redundancy disk '/dev/mapper/mpathe' attribute 'compatible.asm'='11.2';
Diskgroup created.
SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB TOTAL_MB FREE_MB NAME PATH
-------- ----------- ------- -------- ---------- ---------- ---------- -------------------- -------------------------
1 2 CACHED NORMAL 51200 51200 43 DG01_0002 /dev/mapper/mpathc
1 1 CACHED NORMAL 51200 51200 41 DG01_0001 /dev/mapper/mpathb
1 3 CACHED NORMAL 51200 51200 45 DG01_0003 /dev/mapper/mpathd
1 0 CACHED NORMAL 51200 51200 46 DG01_0000 /dev/mapper/mpatha
2 0 CACHED NORMAL 512000 512000 511944 DG02_0000 /dev/mapper/mpathe
2. Identify existing schema & DB file locations on DG01
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2048 SYSTEM *** +DG01/DBTST/datafile/system.256.797379227
2 820 SYSAUX *** +DG01/DBTST/datafile/sysaux.257.797379227
3 3715 UNDOTBS1 *** +DG01/DBTST/datafile/undotbs1.258.797379227
4 2048 USERS *** +DG01/DBTST/datafile/users.259.797379227
5 30720 ARSYS *** +DG01/DBTST/datafile/arsys.276.812153865
6 30720 ARSYS *** +DG01/DBTST/datafile/arsys.275.812153995
7 30720 ARSYS *** +DG01/DBTST/datafile/arsys.274.812154063
8 30720 ARSYS *** +DG01/DBTST/datafile/arsys.273.812154191
9 10000 ARSYS *** +DG01/DBTST/datafile/arsys.266.821749103
10 500 ARSYS *** +DG01/DBTST/datafile/arsys.272.828469977
16 7168 SYSAUX *** +DG01/DBTST/datafile/sysaux.278.801406735
17 5120 UNDOTBS1 *** +DG01/DBTST/datafile/undotbs1.279.801406757
18 2048 USERS *** +DG01/DBTST/datafile/users.280.801406775
19 2048 SYSTEM *** +DG01/DBTST/datafile/system.281.801406799
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32767 TEMP 32767 +DG01/DBTST/tempfile/temp.264.797379307
2 10240 TEMP 10240 +DG01/DBTST/tempfile/temp.277.801406709
3 10 TEMP 10 +DG01/DBTST/tempfile/temp.282.802955967
4 10 TEMP 10 +DG01/DBTST/tempfile/temp.285.802956051
3. Perform DB file movement to new disk group DG02
Method 1: Copy each db file
e.g. Shown for single datafile
RMAN> backup as copy datafile 19 format '+DG02';
Starting backup at 17-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799
output file name=+DG02/DBTST/datafile/system.256.834409133 tag=TAG20131217T121852 RECID=13 STAMP=834409138
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 17-DEC-13
Method 2: Copy complete database (preferred)
RMAN> backup as copy database format '+DG02';
Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DG01/DBTST/datafile/arsys.276.812153865
output file name=+DG02/DBTST/datafile/arsys.257.834409167 tag=TAG20131217T121927 RECID=14 STAMP=834409255
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DG01/DBTST/datafile/arsys.275.812153995
output file name=+DG02/DBTST/datafile/arsys.258.834409263 tag=TAG20131217T121927 RECID=15 STAMP=834409350
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DG01/DBTST/datafile/arsys.274.812154063
output file name=+DG02/DBTST/datafile/arsys.259.834409359 tag=TAG20131217T121927 RECID=16 STAMP=834409447
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG01/DBTST/datafile/arsys.273.812154191
output file name=+DG02/DBTST/datafile/arsys.260.834409453 tag=TAG20131217T121927 RECID=17 STAMP=834409553
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DG01/DBTST/datafile/arsys.266.821749103
output file name=+DG02/DBTST/datafile/arsys.261.834409559 tag=TAG20131217T121927 RECID=18 STAMP=834409589
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DG01/DBTST/datafile/sysaux.278.801406735
output file name=+DG02/DBTST/datafile/sysaux.262.834409593 tag=TAG20131217T121927 RECID=19 STAMP=834409614
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DG01/DBTST/datafile/undotbs1.279.801406757
output file name=+DG02/DBTST/datafile/undotbs1.263.834409619 tag=TAG20131217T121927 RECID=20 STAMP=834409633
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG01/DBTST/datafile/undotbs1.258.797379227
output file name=+DG02/DBTST/datafile/undotbs1.264.834409633 tag=TAG20131217T121927 RECID=21 STAMP=834409647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG01/DBTST/datafile/system.256.797379227
output file name=+DG02/DBTST/datafile/system.265.834409649 tag=TAG20131217T121927 RECID=22 STAMP=834409656
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DG01/DBTST/datafile/users.259.797379227
output file name=+DG02/DBTST/datafile/users.266.834409663 tag=TAG20131217T121927 RECID=23 STAMP=834409670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DG01/DBTST/datafile/users.280.801406775
output file name=+DG02/DBTST/datafile/users.267.834409671 tag=TAG20131217T121927 RECID=24 STAMP=834409677
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799
output file name=+DG02/DBTST/datafile/system.268.834409677 tag=TAG20131217T121927 RECID=25 STAMP=834409683
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG01/DBTST/datafile/sysaux.257.797379227
output file name=+DG02/DBTST/datafile/sysaux.269.834409685 tag=TAG20131217T121927 RECID=26 STAMP=834409687
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DG01/DBTST/datafile/arsys.272.828469977
output file name=+DG02/DBTST/datafile/arsys.270.834409689 tag=TAG20131217T121927 RECID=27 STAMP=834409690
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG02/DBTST/controlfile/backup.271.834409691 tag=TAG20131217T121927 RECID=28 STAMP=834409692
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-DEC-13
channel ORA_DISK_1: finished piece 1 at 17-DEC-13
piece handle=+DG02/DBTST/backupset/2013_12_17/nnsnf0_tag20131217t121927_0.272.834409695 tag=TAG20131217T121927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13
RMAN>
4. Switch database to New disk groups
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG02/DBTST/datafile/system.265.834409649"
datafile 2 switched to datafile copy "+DG02/DBTST/datafile/sysaux.269.834409685"
datafile 3 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.264.834409633"
datafile 4 switched to datafile copy "+DG02/DBTST/datafile/users.266.834409663"
datafile 5 switched to datafile copy "+DG02/DBTST/datafile/arsys.257.834409167"
datafile 6 switched to datafile copy "+DG02/DBTST/datafile/arsys.258.834409263"
datafile 7 switched to datafile copy "+DG02/DBTST/datafile/arsys.259.834409359"
datafile 8 switched to datafile copy "+DG02/DBTST/datafile/arsys.260.834409453"
datafile 9 switched to datafile copy "+DG02/DBTST/datafile/arsys.261.834409559"
datafile 10 switched to datafile copy "+DG02/DBTST/datafile/arsys.270.834409689"
datafile 16 switched to datafile copy "+DG02/DBTST/datafile/sysaux.262.834409593"
datafile 17 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.263.834409619"
datafile 18 switched to datafile copy "+DG02/DBTST/datafile/users.267.834409671"
datafile 19 switched to datafile copy "+DG02/DBTST/datafile/system.268.834409677"
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2048 SYSTEM *** +DG02/DBTST/datafile/system.265.834409649
2 820 SYSAUX *** +DG02/DBTST/datafile/sysaux.269.834409685
3 3715 UNDOTBS1 *** +DG02/DBTST/datafile/undotbs1.264.834409633
4 2048 USERS *** +DG02/DBTST/datafile/users.266.834409663
5 30720 ARSYS *** +DG02/DBTST/datafile/arsys.257.834409167
6 30720 ARSYS *** +DG02/DBTST/datafile/arsys.258.834409263
7 30720 ARSYS *** +DG02/DBTST/datafile/arsys.259.834409359
8 30720 ARSYS *** +DG02/DBTST/datafile/arsys.260.834409453
9 10000 ARSYS *** +DG02/DBTST/datafile/arsys.261.834409559
10 500 ARSYS *** +DG02/DBTST/datafile/arsys.270.834409689
16 7168 SYSAUX *** +DG02/DBTST/datafile/sysaux.262.834409593
17 5120 UNDOTBS1 *** +DG02/DBTST/datafile/undotbs1.263.834409619
18 2048 USERS *** +DG02/DBTST/datafile/users.267.834409671
19 2048 SYSTEM *** +DG02/DBTST/datafile/system.268.834409677
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32767 TEMP 32767 +DG01/DBTST/tempfile/temp.264.797379307
2 10240 TEMP 10240 +DG01/DBTST/tempfile/temp.277.801406709
3 10 TEMP 10 +DG01/DBTST/tempfile/temp.282.802955967
4 10 TEMP 10 +DG01/DBTST/tempfile/temp.285.802956051
5. Control file movement to new disk group DG02
i. Backup controlfile to new DG02
RMAN> backup as copy current controlfile format '+DG02';
Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG02/DBTST/controlfile/backup.273.834409791 tag=TAG20131217T122951 RECID=43 STAMP=834409791
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13
ii. Change DB parameters to point to new DG02
SQL> show parameter file (o/p truncated)
NAME TYPE VALUE
------------- ------------------ ------------------------------
control_files string +DG01/DBTST/controlfile/current.260.797379301
db_create_file_dest string +DG01
SQL> alter system set db_create_file_dest='+DG02' scope=spfile;
System altered.
SQL> alter system set control_files='+DG02/DBTST/controlfile/Backup.273.834409791' scope=spfile;
System altered.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2229064 bytes
Variable Size 1224740024 bytes
Database Buffers 335544320 bytes
Redo Buffers 157814784 bytes
SQL> show parameter file (o/p truncated)
NAME TYPE VALUE
------------- ------------------ ------------------------------
control_files string +DG02/DBTST/controlfile/Backup.273.834409791
db_create_file_dest string +DG02
SQL> SQL> alter database mount;
Database altered.
6. Perform DB Recovery & Open DB
SQL> recover database using backup controlfile;
ORA-00279: change 7857026695002 generated at 12/17/2013 12:10:29 needed for thread 1
ORA-00289: suggestion : /u01/arc_dest/1_11552_797379303.dbf
ORA-00280: change 7857026695002 for thread 1 is in sequence #11552
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DG01/DBTST/onlinelog/redo03.log
Log applied.
Media recovery complete.
SQL> select status, checkpoint_change#, fuzzy,
2 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
3 count(*)
4 from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
5 6 order by status, checkpoint_change#, fuzzy, checkpoint_time;
STATUS CHECKPOINT_CHANGE# FUZ CHECKPOINT_TIME COUNT(*)
------- ---------------------- --- ---------------------------------------------------------- -------------
ONLINE 7857026695003 NO 17-DEC-2013 12:10:29 14
1 row selected.
SQL> alter database open resetlogs;
Database altered.7. Re-create Redo Log files
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------- ---
3 ONLINE +DG01/DBTST/onlinelog/redo03.log NO
2 ONLINE +DG01/DBTST/onlinelog/redo02.log NO
1 ONLINE +DG01/DBTST/onlinelog/redo01.log NO
3 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ------- ---------- ---------- -------- --------- --- ------- ------------- ------------------- ------------- -------------------
1 1 1 1073741824 512 1 YES ACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 0
3 rows selected.
SQL> alter database add logfile group 4 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------------- ------------- ------------- ------------- ------------- ------------- --- ---------- ------------- ------------ --------- -----------
1 1 1 1073741824 512 1 YES ACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
4 1 0 1073741824 512 1 YES UNUSED 0 0
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 0
4 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE +DG01/DBTST/onlinelog/redo03.log NO
2 ONLINE +DG01/DBTST/onlinelog/redo02.log NO
1 ONLINE +DG01/DBTST/onlinelog/redo01.log NO
4 ONLINE +DG02/DBTST/onlinelog/group_4.274.834411679 NO
4 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 1 1073741824 512 1 YES INACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
4 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 2.8147498E+14
3 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
4 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 2.8147498E+14
2 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database add logfile group 1 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 0 1073741824 512 1 YES UNUSED 0 0
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
2 1 2 1073741824 512 1 YES INACTIVE 7857026695398 17-DEC-2013 12:59:52 7857026695467 17-DEC-2013 13:02:28
3 rows selected.
SQL> alter database drop logfile group 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
1 1 0 1073741824 512 1 YES UNUSED 0 0
2 rows selected.
SQL> alter database add logfile group2 ('+DG02') size 1G;
Database altered.
SQL> alter database add logfile group 3 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 0 1073741824 512 1 YES UNUSED 0 0
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 0 1073741824 512 1 YES UNUSED 0 0
4 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE +DG02/DBTST/onlinelog/group_1.275.834411781 NO
2 ONLINE +DG02/DBTST/onlinelog/group_2.276.834411805 NO
3 ONLINE +DG02/DBTST/onlinelog/group_3.277.834411811 NO
4 ONLINE +DG02/DBTST/onlinelog/group_4.274.834411679 NO
4 rows selected.
8. Re-create Temporary Tablespace
SQL> select * from dba_temp_files;
FILE_NAME ILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------------------------- ------ ---------------- ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307 1 TEMP 34358689792 4194176 ONLINE 1 YES 34359721984 4194302 80 34357641216 4194048
+DG01/DBTST/tempfile/temp.277.801406709 2 TEMP 10737418240 1310720 ONLINE 2 NO 0 0 0 10736369664 1310592
+DG01/DBTST/tempfile/temp.282.802955967 3 TEMP 10485760 1280 ONLINE 3 NO 0 0 0 9437184 1152
+DG01/DBTST/tempfile/temp.285.802956051 4 TEMP 10485760 1280 ONLINE 4 NO 0 0 0 9437184 1152
4 rows selected.
SQL> create temporary tablespace TEMP1 tempfile '+DG02' size 20G;
Tablespace created.
SQL> alter database default temporary tablespace TEMP1;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2229064 bytes
Variable Size 1224740024 bytes
Database Buffers 335544320 bytes
Redo Buffers 157814784 bytes
Database mounted.
Database opened.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------- ---------- ------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307 1 TEMP 34358689792 4194176 ONLINE 1 YES 34359721984 4194302 80 34357641216 4194048
+DG02/DBTST/tempfile/temp1.278.834411957 5 TEMP1 21474836480 2621440 ONLINE 1 NO 0 0 0 21473787904 2621312
+DG01/DBTST/tempfile/temp.277.801406709 2 TEMP 10737418240 1310720 ONLINE 2 NO 0 0 0 10736369664 1310592
+DG01/DBTST/tempfile/temp.282.802955967 3 TEMP 10485760 1280 ONLINE 3 NO 0 0 0 9437184 1152
+DG01/DBTST/tempfile/temp.285.802956051 4 TEMP 10485760 1280 ONLINE 4 NO 0 0 0 9437184 1152
5 rows selected.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
----------------------------------------- ------------- ------------------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG02/DBTST/tempfile/temp1.278.834411957 5 TEMP1 21474836480 2621440 ONLINE 1 NO 0 0 0 21473787904 2621312
1 row selected.
Scenario:
DBTST is an Oracle11g single instance database on ASM which holds complete database in Disk Group named DG01 spread across 4*50 GB of Disks. Objective is to migrate complete databases from DG01 to DG02 Disk Group. Here DG02 is a new Disk Group on 500 GB disk (/dev/mapper/mpathe)
col NAME for a20
col PATH for a40
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB TOTAL_MB FREE_MB NAME PATH
------- -------- ------- -------- ---------- ---------- -------- --------------- ----------------------------------------
1 2 CACHED NORMAL 51200 51200 43 DG01_0002 /dev/mapper/mpathc
1 1 CACHED NORMAL 51200 51200 41 DG01_0001 /dev/mapper/mpathb
1 3 CACHED NORMAL 51200 51200 45 DG01_0003 /dev/mapper/mpathd
1 0 CACHED NORMAL 51200 51200 46 DG01_0000 /dev/mapper/mpatha
0 0 CLOSED NORMAL 512000 0 0 /dev/mapper/mpathe
Migration Steps:
1. Create a New Disk Group DG02
SQL> create diskgroup DG02 external redundancy disk '/dev/mapper/mpathe' attribute 'compatible.asm'='11.2';
Diskgroup created.
SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB TOTAL_MB FREE_MB NAME PATH
-------- ----------- ------- -------- ---------- ---------- ---------- -------------------- -------------------------
1 2 CACHED NORMAL 51200 51200 43 DG01_0002 /dev/mapper/mpathc
1 1 CACHED NORMAL 51200 51200 41 DG01_0001 /dev/mapper/mpathb
1 3 CACHED NORMAL 51200 51200 45 DG01_0003 /dev/mapper/mpathd
1 0 CACHED NORMAL 51200 51200 46 DG01_0000 /dev/mapper/mpatha
2 0 CACHED NORMAL 512000 512000 511944 DG02_0000 /dev/mapper/mpathe
2. Identify existing schema & DB file locations on DG01
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2048 SYSTEM *** +DG01/DBTST/datafile/system.256.797379227
2 820 SYSAUX *** +DG01/DBTST/datafile/sysaux.257.797379227
3 3715 UNDOTBS1 *** +DG01/DBTST/datafile/undotbs1.258.797379227
4 2048 USERS *** +DG01/DBTST/datafile/users.259.797379227
5 30720 ARSYS *** +DG01/DBTST/datafile/arsys.276.812153865
6 30720 ARSYS *** +DG01/DBTST/datafile/arsys.275.812153995
7 30720 ARSYS *** +DG01/DBTST/datafile/arsys.274.812154063
8 30720 ARSYS *** +DG01/DBTST/datafile/arsys.273.812154191
9 10000 ARSYS *** +DG01/DBTST/datafile/arsys.266.821749103
10 500 ARSYS *** +DG01/DBTST/datafile/arsys.272.828469977
16 7168 SYSAUX *** +DG01/DBTST/datafile/sysaux.278.801406735
17 5120 UNDOTBS1 *** +DG01/DBTST/datafile/undotbs1.279.801406757
18 2048 USERS *** +DG01/DBTST/datafile/users.280.801406775
19 2048 SYSTEM *** +DG01/DBTST/datafile/system.281.801406799
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32767 TEMP 32767 +DG01/DBTST/tempfile/temp.264.797379307
2 10240 TEMP 10240 +DG01/DBTST/tempfile/temp.277.801406709
3 10 TEMP 10 +DG01/DBTST/tempfile/temp.282.802955967
4 10 TEMP 10 +DG01/DBTST/tempfile/temp.285.802956051
3. Perform DB file movement to new disk group DG02
Method 1: Copy each db file
e.g. Shown for single datafile
RMAN> backup as copy datafile 19 format '+DG02';
Starting backup at 17-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799
output file name=+DG02/DBTST/datafile/system.256.834409133 tag=TAG20131217T121852 RECID=13 STAMP=834409138
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 17-DEC-13
Method 2: Copy complete database (preferred)
RMAN> backup as copy database format '+DG02';
Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DG01/DBTST/datafile/arsys.276.812153865
output file name=+DG02/DBTST/datafile/arsys.257.834409167 tag=TAG20131217T121927 RECID=14 STAMP=834409255
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DG01/DBTST/datafile/arsys.275.812153995
output file name=+DG02/DBTST/datafile/arsys.258.834409263 tag=TAG20131217T121927 RECID=15 STAMP=834409350
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DG01/DBTST/datafile/arsys.274.812154063
output file name=+DG02/DBTST/datafile/arsys.259.834409359 tag=TAG20131217T121927 RECID=16 STAMP=834409447
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG01/DBTST/datafile/arsys.273.812154191
output file name=+DG02/DBTST/datafile/arsys.260.834409453 tag=TAG20131217T121927 RECID=17 STAMP=834409553
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DG01/DBTST/datafile/arsys.266.821749103
output file name=+DG02/DBTST/datafile/arsys.261.834409559 tag=TAG20131217T121927 RECID=18 STAMP=834409589
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DG01/DBTST/datafile/sysaux.278.801406735
output file name=+DG02/DBTST/datafile/sysaux.262.834409593 tag=TAG20131217T121927 RECID=19 STAMP=834409614
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DG01/DBTST/datafile/undotbs1.279.801406757
output file name=+DG02/DBTST/datafile/undotbs1.263.834409619 tag=TAG20131217T121927 RECID=20 STAMP=834409633
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG01/DBTST/datafile/undotbs1.258.797379227
output file name=+DG02/DBTST/datafile/undotbs1.264.834409633 tag=TAG20131217T121927 RECID=21 STAMP=834409647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG01/DBTST/datafile/system.256.797379227
output file name=+DG02/DBTST/datafile/system.265.834409649 tag=TAG20131217T121927 RECID=22 STAMP=834409656
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DG01/DBTST/datafile/users.259.797379227
output file name=+DG02/DBTST/datafile/users.266.834409663 tag=TAG20131217T121927 RECID=23 STAMP=834409670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DG01/DBTST/datafile/users.280.801406775
output file name=+DG02/DBTST/datafile/users.267.834409671 tag=TAG20131217T121927 RECID=24 STAMP=834409677
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DG01/DBTST/datafile/system.281.801406799
output file name=+DG02/DBTST/datafile/system.268.834409677 tag=TAG20131217T121927 RECID=25 STAMP=834409683
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG01/DBTST/datafile/sysaux.257.797379227
output file name=+DG02/DBTST/datafile/sysaux.269.834409685 tag=TAG20131217T121927 RECID=26 STAMP=834409687
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DG01/DBTST/datafile/arsys.272.828469977
output file name=+DG02/DBTST/datafile/arsys.270.834409689 tag=TAG20131217T121927 RECID=27 STAMP=834409690
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG02/DBTST/controlfile/backup.271.834409691 tag=TAG20131217T121927 RECID=28 STAMP=834409692
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-DEC-13
channel ORA_DISK_1: finished piece 1 at 17-DEC-13
piece handle=+DG02/DBTST/backupset/2013_12_17/nnsnf0_tag20131217t121927_0.272.834409695 tag=TAG20131217T121927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13
RMAN>
4. Switch database to New disk groups
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG02/DBTST/datafile/system.265.834409649"
datafile 2 switched to datafile copy "+DG02/DBTST/datafile/sysaux.269.834409685"
datafile 3 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.264.834409633"
datafile 4 switched to datafile copy "+DG02/DBTST/datafile/users.266.834409663"
datafile 5 switched to datafile copy "+DG02/DBTST/datafile/arsys.257.834409167"
datafile 6 switched to datafile copy "+DG02/DBTST/datafile/arsys.258.834409263"
datafile 7 switched to datafile copy "+DG02/DBTST/datafile/arsys.259.834409359"
datafile 8 switched to datafile copy "+DG02/DBTST/datafile/arsys.260.834409453"
datafile 9 switched to datafile copy "+DG02/DBTST/datafile/arsys.261.834409559"
datafile 10 switched to datafile copy "+DG02/DBTST/datafile/arsys.270.834409689"
datafile 16 switched to datafile copy "+DG02/DBTST/datafile/sysaux.262.834409593"
datafile 17 switched to datafile copy "+DG02/DBTST/datafile/undotbs1.263.834409619"
datafile 18 switched to datafile copy "+DG02/DBTST/datafile/users.267.834409671"
datafile 19 switched to datafile copy "+DG02/DBTST/datafile/system.268.834409677"
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2048 SYSTEM *** +DG02/DBTST/datafile/system.265.834409649
2 820 SYSAUX *** +DG02/DBTST/datafile/sysaux.269.834409685
3 3715 UNDOTBS1 *** +DG02/DBTST/datafile/undotbs1.264.834409633
4 2048 USERS *** +DG02/DBTST/datafile/users.266.834409663
5 30720 ARSYS *** +DG02/DBTST/datafile/arsys.257.834409167
6 30720 ARSYS *** +DG02/DBTST/datafile/arsys.258.834409263
7 30720 ARSYS *** +DG02/DBTST/datafile/arsys.259.834409359
8 30720 ARSYS *** +DG02/DBTST/datafile/arsys.260.834409453
9 10000 ARSYS *** +DG02/DBTST/datafile/arsys.261.834409559
10 500 ARSYS *** +DG02/DBTST/datafile/arsys.270.834409689
16 7168 SYSAUX *** +DG02/DBTST/datafile/sysaux.262.834409593
17 5120 UNDOTBS1 *** +DG02/DBTST/datafile/undotbs1.263.834409619
18 2048 USERS *** +DG02/DBTST/datafile/users.267.834409671
19 2048 SYSTEM *** +DG02/DBTST/datafile/system.268.834409677
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32767 TEMP 32767 +DG01/DBTST/tempfile/temp.264.797379307
2 10240 TEMP 10240 +DG01/DBTST/tempfile/temp.277.801406709
3 10 TEMP 10 +DG01/DBTST/tempfile/temp.282.802955967
4 10 TEMP 10 +DG01/DBTST/tempfile/temp.285.802956051
5. Control file movement to new disk group DG02
i. Backup controlfile to new DG02
RMAN> backup as copy current controlfile format '+DG02';
Starting backup at 17-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG02/DBTST/controlfile/backup.273.834409791 tag=TAG20131217T122951 RECID=43 STAMP=834409791
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-DEC-13
ii. Change DB parameters to point to new DG02
SQL> show parameter file (o/p truncated)
NAME TYPE VALUE
------------- ------------------ ------------------------------
control_files string +DG01/DBTST/controlfile/current.260.797379301
db_create_file_dest string +DG01
SQL> alter system set db_create_file_dest='+DG02' scope=spfile;
System altered.
SQL> alter system set control_files='+DG02/DBTST/controlfile/Backup.273.834409791' scope=spfile;
System altered.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2229064 bytes
Variable Size 1224740024 bytes
Database Buffers 335544320 bytes
Redo Buffers 157814784 bytes
SQL> show parameter file (o/p truncated)
NAME TYPE VALUE
------------- ------------------ ------------------------------
control_files string +DG02/DBTST/controlfile/Backup.273.834409791
db_create_file_dest string +DG02
SQL> SQL> alter database mount;
Database altered.
6. Perform DB Recovery & Open DB
SQL> recover database using backup controlfile;
ORA-00279: change 7857026695002 generated at 12/17/2013 12:10:29 needed for thread 1
ORA-00289: suggestion : /u01/arc_dest/1_11552_797379303.dbf
ORA-00280: change 7857026695002 for thread 1 is in sequence #11552
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DG01/DBTST/onlinelog/redo03.log
Log applied.
Media recovery complete.
SQL> select status, checkpoint_change#, fuzzy,
2 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
3 count(*)
4 from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
5 6 order by status, checkpoint_change#, fuzzy, checkpoint_time;
STATUS CHECKPOINT_CHANGE# FUZ CHECKPOINT_TIME COUNT(*)
------- ---------------------- --- ---------------------------------------------------------- -------------
ONLINE 7857026695003 NO 17-DEC-2013 12:10:29 14
1 row selected.
SQL> alter database open resetlogs;
Database altered.7. Re-create Redo Log files
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------- ---
3 ONLINE +DG01/DBTST/onlinelog/redo03.log NO
2 ONLINE +DG01/DBTST/onlinelog/redo02.log NO
1 ONLINE +DG01/DBTST/onlinelog/redo01.log NO
3 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ------- ---------- ---------- -------- --------- --- ------- ------------- ------------------- ------------- -------------------
1 1 1 1073741824 512 1 YES ACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 0
3 rows selected.
SQL> alter database add logfile group 4 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------------- ------------- ------------- ------------- ------------- ------------- --- ---------- ------------- ------------ --------- -----------
1 1 1 1073741824 512 1 YES ACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
4 1 0 1073741824 512 1 YES UNUSED 0 0
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 0
4 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE +DG01/DBTST/onlinelog/redo03.log NO
2 ONLINE +DG01/DBTST/onlinelog/redo02.log NO
1 ONLINE +DG01/DBTST/onlinelog/redo01.log NO
4 ONLINE +DG02/DBTST/onlinelog/group_4.274.834411679 NO
4 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 1 1073741824 512 1 YES INACTIVE 7857026695004 17-DEC-2013 12:59:03 7857026695398 17-DEC-2013 12:59:52
4 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 2.8147498E+14
3 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
4 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 2 1073741824 512 1 NO CURRENT 7857026695398 17-DEC-2013 12:59:52 2.8147498E+14
2 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database add logfile group 1 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 0 1073741824 512 1 YES UNUSED 0 0
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
2 1 2 1073741824 512 1 YES INACTIVE 7857026695398 17-DEC-2013 12:59:52 7857026695467 17-DEC-2013 13:02:28
3 rows selected.
SQL> alter database drop logfile group 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
1 1 0 1073741824 512 1 YES UNUSED 0 0
2 rows selected.
SQL> alter database add logfile group2 ('+DG02') size 1G;
Database altered.
SQL> alter database add logfile group 3 ('+DG02') size 1G;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------- ------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ------------------- -------------
1 1 0 1073741824 512 1 YES UNUSED 0 0
4 1 3 1073741824 512 1 NO CURRENT 7857026695467 17-DEC-2013 13:02:28 0
3 1 0 1073741824 512 1 YES UNUSED 0 0
2 1 0 1073741824 512 1 YES UNUSED 0 0
4 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE +DG02/DBTST/onlinelog/group_1.275.834411781 NO
2 ONLINE +DG02/DBTST/onlinelog/group_2.276.834411805 NO
3 ONLINE +DG02/DBTST/onlinelog/group_3.277.834411811 NO
4 ONLINE +DG02/DBTST/onlinelog/group_4.274.834411679 NO
4 rows selected.
8. Re-create Temporary Tablespace
SQL> select * from dba_temp_files;
FILE_NAME ILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------------------------- ------ ---------------- ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307 1 TEMP 34358689792 4194176 ONLINE 1 YES 34359721984 4194302 80 34357641216 4194048
+DG01/DBTST/tempfile/temp.277.801406709 2 TEMP 10737418240 1310720 ONLINE 2 NO 0 0 0 10736369664 1310592
+DG01/DBTST/tempfile/temp.282.802955967 3 TEMP 10485760 1280 ONLINE 3 NO 0 0 0 9437184 1152
+DG01/DBTST/tempfile/temp.285.802956051 4 TEMP 10485760 1280 ONLINE 4 NO 0 0 0 9437184 1152
4 rows selected.
SQL> create temporary tablespace TEMP1 tempfile '+DG02' size 20G;
Tablespace created.
SQL> alter database default temporary tablespace TEMP1;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2229064 bytes
Variable Size 1224740024 bytes
Database Buffers 335544320 bytes
Redo Buffers 157814784 bytes
Database mounted.
Database opened.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------- ---------- ------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG01/DBTST/tempfile/temp.264.797379307 1 TEMP 34358689792 4194176 ONLINE 1 YES 34359721984 4194302 80 34357641216 4194048
+DG02/DBTST/tempfile/temp1.278.834411957 5 TEMP1 21474836480 2621440 ONLINE 1 NO 0 0 0 21473787904 2621312
+DG01/DBTST/tempfile/temp.277.801406709 2 TEMP 10737418240 1310720 ONLINE 2 NO 0 0 0 10736369664 1310592
+DG01/DBTST/tempfile/temp.282.802955967 3 TEMP 10485760 1280 ONLINE 3 NO 0 0 0 9437184 1152
+DG01/DBTST/tempfile/temp.285.802956051 4 TEMP 10485760 1280 ONLINE 4 NO 0 0 0 9437184 1152
5 rows selected.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
----------------------------------------- ------------- ------------------------------ ------------- ------------- ------- ------------- --- ------------- ------------- ------------- ------------- -------------
+DG02/DBTST/tempfile/temp1.278.834411957 5 TEMP1 21474836480 2621440 ONLINE 1 NO 0 0 0 21473787904 2621312
1 row selected.
No comments:
Post a Comment