SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
MASTER READ WRITE
SQL> alter system set db_create_file_dest=’+DATA’ scope=spfile;
System altered.
/* This parameter define the default location for data files,
control_files etc, if no location for these files is specified
at the time of their creation. +DATA is the diskgroup in ASM
*/
SQL> sho parameter db_create_file
NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string
SQL> alter system set db_create_online_log_dest_1=’+FLASH’ scope=spfile;
System altered.
/*
This parameter defines the default location number 1 for online logs.
*/
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled
/* If you are using block change tracking, then disable it. In my case I am not using it. */
SQL> sho parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u02/oradata/master/control01.
ctl, /u02/oradata/master/control02.ctl
SQL> alter system reset control_files scope=spfile;
System altered.
/*
Remove the control_files parameter from spfile.
So next time we restore the control file it will automatically go
to +DATA diskgroup since it is defined in db_create_file_dest,
and the new path will be automatically updated in spfile.
*/
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-4.1$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Tue Apr 28 07:42:43 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 1224738928 bytes
Database Buffers 369098752 bytes
Redo Buffers 7360512 bytes
RMAN> restore controlfile from ‘/u02/oradata/master/control01.ctl’;
Starting restore at 28-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+FLASH/master/controlfile/current.256.878197401
Finished restore at 28-APR-15
/*
Control file restored to +DATA from its old location.
It will be restored to +DATA because we set db_create_file_dest to
+DATA.
*/
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format ‘+DATA’;
Starting backup at 28-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/master/system01.dbf
output file name=+DATA/master/datafile/system.256.878197455 tag=TAG20150428T074413 RECID=1 STAMP=878197504
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/master/sysaux01.dbf
output file name=+DATA/master/datafile/sysaux.257.878197509 tag=TAG20150428T074413 RECID=2 STAMP=878197554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/master/undotbs01.dbf
output file name=+DATA/master/datafile/undotbs1.258.878197565 tag=TAG20150428T074413 RECID=3 STAMP=878197598
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/master/controlfile/backup.259.878197599 tag=TAG20150428T074413 RECID=4 STAMP=878197601
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/master/users01.dbf
output file name=+DATA/master/datafile/users.260.878197603 tag=TAG20150428T074413 RECID=5 STAMP=878197603
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
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 28-APR-15
channel ORA_DISK_1: finished piece 1 at 28-APR-15
piece handle=+DATA/master/backupset/2015_04_28/nnsnf0_tag20150428t074413_0.261.878197603 tag=TAG20150428T074413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-APR-15
/* make a copy of all datafiles on +DATA diskgroup. */
RMAN> switch database to copy;
datafile 1 switched to datafile copy “+DATA/master/datafile/system.256.878197455”
datafile 2 switched to datafile copy “+DATA/master/datafile/sysaux.257.878197509”
datafile 3 switched to datafile copy “+DATA/master/datafile/undotbs1.258.878197565”
datafile 4 switched to datafile copy “+DATA/master/datafile/users.260.878197603”
/*
Switch database to the datafile copies. After this switch the copies on +DATA
have become the database datafiles and the original files have become copies.
*/
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 28 07:47:26 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> set lines 200 pages 30
SQL> col file_name format a50
SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_data_files;
FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 +DATA/master/datafile/system.256.878197455 .68359375 YES 31.9999847
2 +DATA/master/datafile/sysaux.257.878197509 .6640625 YES 31.9999847
3 +DATA/master/datafile/undotbs1.258.878197565 .571289063 YES 31.9999847
4 +DATA/master/datafile/users.260.878197603 .004882813 YES 31.9999847
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
1 ONLINE /u02/oradata/master/redo01.log NO
2 ONLINE /u02/oradata/master/redo02.log NO
3 ONLINE /u02/oradata/master/redo03.log NO
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string +FLASH/master/controlfile/current.256.878197401
/*
Migrate tempfile to ASM storage
*/
SQL> select file_name from dba_temp_files;
FILE_NAME
————————————————–
/u02/oradata/master/temp01.dbf
SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;
FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 /u02/oradata/master/temp01.dbf .0625 YES 31.9999847
SQL> alter tablespace temp add tempfile size 20m;
Tablespace altered.
SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;
FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 /u02/oradata/master/temp01.dbf .0625 YES 31.9999847
2 +DATA/master/tempfile/temp.262.878197751 .01953125 NO 0
SQL> alter tablespace temp drop tempfile ‘/u02/oradata/master/temp01.dbf’;
Tablespace altered.
SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;
FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
2 +DATA/master/tempfile/temp.262.878197751 .01953125 NO 0
/*
Migrate redolog files to ASM
*/
SQL> alter system set db_create_online_log_dest_1=’+FLASH’ scope=spfile;
System altered.
/*
This parameter defines the default location number 1 for online
logs i.e. if you don’t specify a location for the log file while
creating it it will be created on the location defined in this
parameter, which I have already run earlier.
*/
SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;
GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 CURRENT /u02/oradata/master/redo01.log
2 52428800 INACTIVE /u02/oradata/master/redo02.log
3 52428800 INACTIVE /u02/oradata/master/redo03.log
/*
Here in this case log group 2 and 3 are yet UNUSED, so its safe
to drop them and re-create them on ASM. This database is running
in NOARCHIVELOG mode so its okay to drop any group which is
check pointed or UNUSED.
IF you are on an ARCHIVELOG enabled database then make sure any
group you delete is archived i.e.
SELECT group# , archived from v$log; and archived should be YES.
*/
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 52428800;
Database altered.
/*
This magic works because of db_create_online_log_dest_1 parameter.
Log group 3 created on ASM. No need of mentioning the file name.
*/
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 52428800;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;
GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 ACTIVE /u02/oradata/master/redo01.log
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215
SQL> alter system checkpoint;
System altered.
SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;
GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 INACTIVE /u02/oradata/master/redo01.log
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 52428800;
Database altered.
SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;
GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 UNUSED +FLASH/master/onlinelog/group_1.259.878198279
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215