ASM DiskGroup Migration

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.
 

No comments:

Post a Comment

My Popular Posts