不需要重建就要重启，
alter database datafile move 就可以在线
file#.incarnation# 是 incarnation 不是dbid
Goal
How to convert non OMF files to OMF files with ASM storage
Datafiles are not deleted at dropping tablespace if files are non-OMF and if you want datafile to be deleted /removed from storage with dropping tablespace.
Solution
In ASM diskgroups, all files are created as OMF by default to ensure uniqueness.
A typical format of an OMF file in ASM is:
+group/dbname/file_type/file_type_tag.file#.incarnation#
If a non-OMF name is provided for a file creation, Oracle ASM code internally creates an OMF File and automatically creates an alias with user defined non-OMF name for the same, e.g. :
SQL> create tablespace test2 datafile '+DATA/test2.dbf' size 10M
It creates
+DATA/data.dbf <<== an alias as provided in command
+data/orcl/datafile/TEST2.327.953304985 <<== OMF datafile
To convert non-OMF ASM database to OMF ASM database
1. Get the name of alias and omf datafile
2. Drop alias of datafile
3. Restart database renaming datafile alias to omf datafile in controlfile after mount
1. Get the name of alias and omf datafile
ie.
* Check alias location. You can find alias name pointing to OMF datafile
ASMCMD> pwd
+data
ASMCMD> ls -altr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
Y ORCL/
Y ORCL11G/
N tclone/
DATAFILE UNPROT COARSE AUG 29 14:00:00 N test2.dbf => +DATA/ORCL/DATAFILE/TEST2.327.953304985
* Check OMF datafile location, you can find alias name pointing to OMF datafile
ASMCMD> cd ORCL/DATAFILE
ASMCMD> ls -altr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => SYSAUX.257.924952255
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => SYSTEM.258.924952315
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS1.260.924952429
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS2.268.924952857
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS3.269.924952863
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => USERS.259.924952407
DATAFILE UNPROT COARSE AUG 29 14:00:00 Y +DATA/test2.dbf => TEST2.327.953304985
2. Drop alias of datafile. You still have OMF datafile after dropping alias
ie.
alter diskgroup data drop alias '+DATA/test2.dbf';
* Check alias location. the alias is removed/deleted
ASMCMD> pwd
+data
ASMCMD> ls -altr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
Y ORCL/
Y ORCL11G/
N tclone/
* Check OMF datafile location, you can find OMF datafile without alias
ASMCMD> pwd
+data/orcl/datafile
ASMCMD> ls -altr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => SYSAUX.257.924952255
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => SYSTEM.258.924952315
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS1.260.924952429
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS2.268.924952857
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => UNDOTBS3.269.924952863
DATAFILE UNPROT COARSE AUG 29 11:00:00 Y none => USERS.259.924952407
DATAFILE UNPROT COARSE AUG 29 14:00:00 Y none => TEST2.327.953304985
3. Restart database renaming datafile (alias to omf datafile in controlfile)
sql> shutdown immediate
sql> startup mount
sql> alter database rename file '+DATA/test2.dbf' to '+data/orcl/datafile/TEST2.327.953304985';
sql> ater database open;
Note that you need to mount one db instance with all other instances(if any in RAC env) down to rename datafile name in cotnrolfile
otherwise, ora-1511 error
alter database rename file '+DATA/test2.dbf' to '+data/orcl/datafile/TEST2.327.953304985'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file x - file is in use or recovery
ORA-01110: data file x: '+DATA/test2.dbf'
Goal
NON-OMF to OMF ( Datafile and Redo Migration )
Solution
1. Who Can Use Oracle Managed Files?
Oracle Managed Files are most useful for the following types of databases:
- A logical volume manager that supports striping/RAID and dynamically extensible logical volumes .
- A file system that provides large, extensible files Low end or test databases
Initialization Parameters That Enable Oracle Managed Files for Datafiles , Redo and Controlfiles :
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
The name that is used for creation of an Oracle managed file is constructed from three sources:
- The default creation location
- A file name template that is chosen based on the type of the file. The template also depends on the operating system platform and whether or not Oracle Automatic Storage Management is used.
- A unique string created by Oracle Database or the operating system. This ensures that file creation does not damage an existing file and that the file cannot be mistaken for some other file.
As a specific example, filenames for Oracle Managed Files have the following format on a Solaris file system:
destination_prefix/o1_mf_%t_%u_.dbf
where: destination_prefix is destination_location/db_unique_name/datafile
where: destination_location is the location specified in DB_CREATE_FILE_DEST
db_unique_name is the globally unique name (DB_UNIQUE_NAME initialization parameter) of the target database. If there is no DB_UNIQUE_NAME parameter, then the DB_NAME initialization parameter value is used.
%t is the tablespace name.
%u is an eight-character string that guarantees uniqueness
For example, assume the following parameter settings:
DB_CREATE_FILE_DEST = /u01/app/oracle/oradata
DB_UNIQUE_NAME = XXX
Then an example data file name would be:
/u01/app/oracle/oradata/XXX/datafile/o1_mf_tbs1_2ixh90q_.dbf
Reference: Documentation
2) How to convert NON-OMF Datafiles to OMF FILES
a) Steps to Convert
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------------------
<PATH>/system01.dbf
<PATH>/sysaux01.dbf
<PATH>/undotbs01.dbf
<PATH>/users01.dbf
SQL> create tablespace test datafile '<PATH>/test01.dbf' size 100m;
Tablespace created.
SQL> create table dd as select * from dba_objects;
Table created.
SQL> commit;
Commit complete.
SQL> alter table dd move tablespace test;
Table altered.
SQL> select count(*) from dba_objects;
COUNT(*)
----------
90898
SQL> select count(*) from dd;
COUNT(*)
----------
90898
SQL> select table_name,tablespace_name from dba_tables where table_name='DD';
TABLE_NAME TABLESPACE_NAME
--------------- ----------------------------
DD TEST
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------
db_create_file_dest string
SQL> Alter system set db_create_file_dest='/tmp/OMF' scope=both;
System altered.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------ ----------- ------------------------------
db_create_file_dest string /tmp/OMF
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
<PATH>/system01.dbf
<PATH>/sysaux01.dbf
<PATH>/undotbs01.dbf
<PATH>/test01.dbf
<PATH>/users01.dbf
SQL> alter database move datafile 1;
Database altered.
SQL> alter database move datafile 3;
Database altered.
SQL> alter database move datafile 4;
Database altered.
SQL> alter database move datafile 6;
Database altered.
SQL> alter database move datafile 5;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/tmp/OMF/XXX/datafile/o1_mf_system_cxh44rx5_.dbf
/tmp/OMF/XXX/datafile/o1_mf_sysaux_cxh475v6_.dbf
/tmp/OMF/XXX/datafile/o1_mf_undotbs1_cxh490bz_.dbf
/tmp/OMF/XXX/datafile/o1_mf_test_cxh49m11_.dbf
/tmp/OMF/XXX/datafile/o1_mf_users_cxh49fqz_.dbf
b) Now Check With RMAN for recently converted Files
没有意思，肯定是啊
RMAN> backup database plus archivelog;
Starting backup at 13-SEP-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=248 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=922427023
channel ORA_DISK_1: starting piece 1 at 13-SEP-16
channel ORA_DISK_1: finished piece 1 at 13-SEP-16
piece handle=<PATH>/01rfm7kf_1_1 tag=TAG20160913T054343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-SEP-16
Starting backup at 13-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/tmp/OMF/XXX/datafile/o1_mf_system_cxh44rx5_.dbf
input datafile file number=00003 name=/tmp/OMF/XXX/datafile/o1_mf_sysaux_cxh475v6_.dbf
input datafile file number=00005 name=/tmp/OMF/XXX/datafile/o1_mf_test_cxh49m11_.dbf
input datafile file number=00004 name=/tmp/OMF/XXX/datafile/o1_mf_undotbs1_cxh490bz_.dbf
input datafile file number=00006 name=/tmp/OMF/XXX/datafile/o1_mf_users_cxh49fqz_.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-16
channel ORA_DISK_1: finished piece 1 at 13-SEP-16
piece handle=<PATH>/02rfm7kh_1_1 tag=TAG20160913T054345 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-SEP-16
channel ORA_DISK_1: finished piece 1 at 13-SEP-16
piece handle=<PATH>/03rfm7l0_1_1 tag=TAG20160913T054345 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-SEP-16
Starting backup at 13-SEP-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=2 STAMP=922427042
channel ORA_DISK_1: starting piece 1 at 13-SEP-16
channel ORA_DISK_1: finished piece 1 at 13-SEP-16
piece handle=<PATH>/04rfm7l2_1_1 tag=TAG20160913T054402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-SEP-16
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 20.96M DISK 00:00:01 13-SEP-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054343
Piece Name: <PATH>/01rfm7kf_1_1
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 1698537 13-SEP-16 1705596 13-SEP-16
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.10G DISK 00:00:02 13-SEP-16
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054345
Piece Name: <PATH>/02rfm7kh_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1705609 13-SEP-16 /tmp/OMF/XXX/datafile/o1_mf_system_cxh44rx5_.dbf
3 Full 1705609 13-SEP-16 /tmp/OMF/XXX/datafile/o1_mf_sysaux_cxh475v6_.dbf
4 Full 1705609 13-SEP-16 /tmp/OMF/XXX/datafile/o1_mf_undotbs1_cxh490bz_.dbf
5 Full 1705609 13-SEP-16 /tmp/OMF/XXX/datafile/o1_mf_test_cxh49m11_.dbf
6 Full 1705609 13-SEP-16 /tmp/OMF/XXX/datafile/o1_mf_users_cxh49fqz_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 10.11M DISK 00:00:01 13-SEP-16
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054345
Piece Name: <PATH>/03rfm7l0_1_1
SPFILE Included: Modification time: 13-SEP-16
SPFILE db_unique_name: XXX
Control File Included: Ckp SCN: 1705614 Ckp time: 13-SEP-16
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 7.00K DISK 00:00:00 13-SEP-16
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054402
Piece Name: <PATH>/04rfm7l2_1_1
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1705596 13-SEP-16 1705619 13-SEP-16
RMAN> exit
c) Add New Files using OMF method and check :
SQL> alter tablespace test add datafile;
Tablespace altered.
All Files converted into OMF Files and recorded in controlfile:
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /tmp/OMF/XXX/datafile/o1_mf_system_cxh44rx5_.dbf SYSTEM
2 /tmp/OMF/XXX/datafile/o1_mf_test_cxh4rofn_.dbf ONLINE
3 /tmp/OMF/XXX/datafile/o1_mf_sysaux_cxh475v6_.dbf ONLINE
4 /tmp/OMF/XXX/datafile/o1_mf_undotbs1_cxh490bz_.dbf ONLINE
5 /tmp/OMF/XXX/datafile/o1_mf_test_cxh49m11_.dbf ONLINE
6 /tmp/OMF/XXX/datafile/o1_mf_users_cxh49fqz_.dbf ONLINE
6 rows selected.
SQL> select count(*) from dd;
COUNT(*)
----------
90898
SQL> select table_name,tablespace_name from dba_tables where table_name='DD';
TABLE_NAME TABLESPACE_NAME
--------------- ------------------------------
DD TEST
3. How to convert Non-OMF REDO into OMF Files:
SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
3 <PATH>/redo03.log
2 <PATH>/redo02.log
1 <PATH>/redo01.log
SQL> alter system set DB_CREATE_ONLINE_LOG_DEST_1='/tmp' scope=both;
System altered.
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES ACTIVE
2 NO CURRENT
3 YES ACTIVE
a) Adding New Redo ( OMF FILES) Before Dropping OLD (NON-OMF files)
SQL> ALTER DATABASE ADD LOGFILE GROUP 11 SIZE 100M;
Database altered.
SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
3 <PATH>/redo03.log
2 <PATH>/redo02.log
1 <PATH>/redo01.log
11 /tmp/XXX/onlinelog/o1_mf_11_cxh687tt_.log =========> New group added, with OMF format
SQL> alter system switch logfile;
System altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 12 SIZE 100M;
Database altered.
SQL> ALTER DATA`BASE ADD LOGFILE GROUP 13 size 10m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 14 size 50m;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
b) If you get any Errors due to Drop redo check if its Current Redo log if so make some few Switch and drop them
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance XXX (thread 1)
ORA-00312: online log 2 thread 1: '<PATH>/redo02.log'
c) Switch Logfile to change the status of the file from "CURRENT"
SQL> alter system switch logfile;
System altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.
SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
12 /tmp/XXX/onlinelog/o1_mf_12_cxh6kb1r_.log
11 /tmp/XXX/onlinelog/o1_mf_11_cxh687tt_.log
13 /tmp/XXX/onlinelog/o1_mf_13_cxh6ns1q_.log
14 /tmp/XXX/onlinelog/o1_mf_14_cxh6o20c_.log