Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Create tablespace using ASM

Oracle Tips by Burleson Consulting

October 9, 2012

Question:  How cab I create a tablespace using ASM datafiles?  I know how to specify the file clause for non-ASM, but I need to understand how to create a tablespace for an ASM-enabled system.

Answer:  See creating an ASM diskgroup.   First, note that you can define a default ASM datafile.  For example, if you have a +DATAFILE ASM diskgroup, you can make it the default for all tablespaces by setting the

alter system set db_create_file_dest='+datafile' scope=both;

With a default ASM in-place, you need only specify the size of the tablespace:

create tablespace my_ts size 10m;

You can also specify a manual ASM disk:

create tablespace my_ts datafile '+datafile';

Finally, you can create a tablespace with a mix of ASM and non-ASM diskgroups:

create tablespace my_ts
      datafile
           '/u01/app/oracle/data/mydbf.dbf' size 10m,
           '+datafile'                      size 10m;

RMAN convert clause Keywords and Parameters

TABLESPACE tablespace_name

The TABLESPACE keyword specifies the name of a tablespace in the source database that you want to transport to the destination database on a different platform. CONVERT TABLESPACE can only be used when connected to the source database, not from the destination database. This is because until the tablespace transport has been completed, the destination database has no way of recognizing the tablespace name for use with the CONVERT TABLESPACE statement.

DATAFILE datafile_name

This specifies the name of a to-be-converted datafile that you want to transport into the destination database.

If you decide to convert at the destination database instead of at the source, you must use CONVERT DATAFILE instead of CONVERT TABLESPACE, and name each datafile that is being converted. This is because the files have not yet been imported into the destination database, so RMAN does not yet know which files belong to the tablespace being converted.

If you want to convert a single datafile at a time, you can use the DATAFILE argument in place of the TABLESPACE argument on the source platform, though it is more convenient to use the TABLESPACE argument and do all datafiles at one time for a specific tablespace.

FROM PLATFORM = platform_name

This parameter specifies the name of the source platform as it is displayed in a select from the v$transportable_platform.platform_name column.

TO PLATFORM = platform_name

This parameter specifies the name of the destination platform as displayed in a select from the v$transportable_platform.platform_name column.

 FORMAT formatSpec

This parameter specifies the name template for the output file(s).

DB_FILE_NAME_CONVERT='string_pattern'

This parameter accepts filename pairs (the first name in the pair is the converted filename), performs the required substitutions, and places the converted files in the new location after the substitution. You can specify as many pairs of replacement strings as required. You can use single or double quotation marks around your entries.

Convert Clause Restrictions Usage Notes

  • You can share read-only tablespaces across a heterogeneous cluster.
     

  • Both source and destination databases must be running Oracle Database 10g with the initialization parameter COMPATIBLE set to 10.0 or higher.
     

  • A tablespace must have been made read-write at least once in Oracle Database 10g before it can be transported to any other platform. Therefore, any read-only tablespaces (or currently existing transported tablespaces) that exist from a version prior to Oracle Database Oracle10g must be made read-write first on the platform where they were created, before they can be transported to a different platform.
     

  • RMAN cannot process user datatypes that require endian conversions. If you need to transport objects built on underlying types that store data in a platform-specific format, such as endian, then use Oracle's Data Pump feature.
     

  • Query the view v$transportable_platform  to determine the platforms supported by the RMAN CONVERT command. Cross-platform tablespace transport is only supported when both the source and destination platforms are contained in this view. In Oracle Database 10g, the CONVERT command is only required when transporting between platforms for which the value in v$transportable_platform.endian_format is different. If the endian_format column is the same, the files can simply be copied from the source to the destination machine, no conversion is required.
     

  • If conversion is needed, you must run the CONVERT command, either on the source host (using "CONVERT TO" and identifying the destination platform), or on the destination host (where the FROM parameter is not needed, because the source platform is noted in the datafile, and where the TO parameter is not needed because conversion will default to the platform RMAN is running on).
     

  • CONVERT does not convert the datafiles in-place, instead, it creates an output file that is readable on the specified platform.

The following are the supported datatypes for the CONVERT Command:

  • VARCHAR2

  • CHAR
     

  • NUMBER

  • DATE

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • INTERVAL YEAR MONTH

  • INTERVAL DAT TO SECOND

  • BFILE
     

  • LONG
     

  • ROWID
     

  • RAW
     

  • BLOB
     

  • LONG RAW
     

  • UROWID
     

  • NVARCHAR2
     

  • Native numbers
     

  • CLOB
     

  • Media Types (ORDSYS.ORDAudio, ORDSYS.ORDImage, RDSYS.ORDVideo)
     

  • In releases prior to Oracle Database 10g, Oracle created CLOBs in a variable-width character set and stored them in an endian-dependent format. The CONVERT command will not perform conversions on these CLOBs. Instead, RMAN will capture the endian format of each LOB column and propagate it to the target database. Subsequent reads of this data by the SQL layer will interpret the data correctly, based on either of the endian formats, then write it out in an endian-independent way if the tablespace is writeable. If you create new CLOBs in an Oracle Database 10g or later release, RMAN creates the CLOB in character set AL16UTF16, which is platform independent.

Examples of RMAN convert

RMAN's CONVERT command is only required in cases where you are transporting files between platforms with different byte ordering. You can copy the file directly if the platforms you are transporting the tablespace from and to have the same byte ordering.

Example: Source Platform Tablespace Conversion

Suppose you need to transport tablespaces research (datafiles ‘/oracle/oradata/rec/frec01.dbf' and ‘/oracle/oradata/rec/rec02.dbf') and pl (datafiles ‘/oracle/oradata/rec/project01.dbf' and '/oracle/oradata/rec/project02.dbf') from a source database running on a Linux host, to a destination database running on an Windows NT 4.0 server. You plan to store the converted datafiles in the temporary directory tmp/oracle/transport_windows/ on the source host.

The example assumes that you have carried out the following steps in preparation for the tablespace transport:

  • You have set the tablespaces to be transported to be read-only.
     

  • You know Oracle's name for the destination platform.

You will need Oracle's internal name for the target platform. You will use this name as a parameter to the CONVERT command. To get the platform name, use SQL*Plus to query the view v$transportable_platform:

SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME            ENDIAN FORMAT
----------- -----------------------  -------------
          1 Solaris[tm] OE (32-bit)  Big
          2 Solaris[tm] OE (64-bit)  Big
          3 HP-UX (64-bit)           Big
          4 HP-UX IA (64-bit)        Big
          5 HP Tru64 UNIX            Little
          6 AIX-Based Systems(64-bit)Big
          7 Microsoft Windows NT     Little
         10 Linux IA (32-bit)        Little
         11 Linux IA (64-bit)        Little
                              

If you want to restrict the results to a single platform type, simply add a WHERE clause.

The results show that the platform_name for Windows NT is ' Microsoft Windows NT'.

Now, you would use RMAN to convert the datafiles to be transported to the destination host's (Linux) format on the source host. The FORMAT argument controls the name and location of the converted datafiles.

% rman TARGET /
RMAN> CONVERT TABLESPACE research
2> TO PLATFORM 'Microsoft Windows NT'
3> FORMAT='/tmp/oracle/transport_windows/%U';

Starting backup at 09-SEP-03
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
RMAN-00571:
===========================================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: ===============================================
RMAN-03002: failure of backup command at 09/09/2003 13:22:57
RMAN-06598: conversion between platforms 'Linux IA (32-bit)' and
'Microsoft Windows NT' is not needed

So, why is it saying no conversion is required? Notice that in our listing of platforms, the ENDIAN setting for both Linux and NT is Little; this means their files are compatible. If we had specified a SUN platform instead, the results would have looked like so:

RMAN> CONVERT TABLESPACE research
2> TO PLATFORM 'Solaris[tm] OE (64-bit)'
3> FORMAT = '/tmp/oracle/transport_sun/%U';

Starting backup at 09-SEP-03
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK

 

channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/usr/oracle/product/10.1/oradata/aultdb1/research01.dbf
converted datafile=/tmp/oracle/transport_windows/data_D-AULTDB1_I-1051447236_TS-RESEARCH_FNO-4_02f0td2t
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/usr/oracle/product/10.1/oradata/aultdb1/research01.dbf
converted datafile=/tmp/oracle/transport_windows/data_D-AULTDB1_I-1051447236_TS-RESEARCH_FNO-5_03f0td31
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
Finished backup at 09-SEP-03

The result will be a set of converted datafiles with data in the right endian-order for the specified platform that will be located in the directory specified by the FORMAT clause. From this point, you follow the same procedure as for a normal tablespace transport.

Use the Export utility to create the file of metadata information. If you have not already, move the metadata information file from its location and the converted datafiles from /tmp/oracle/transport_linux/ to their respective target directories on the destination host, then plug the tablespace(s) into the new database with the Import utility.

Converting Tablespaces on the Target Platform

Suppose you need to transport tablespaces research (datafiles ‘/oracle/oradata/rec/rec01.dbf' and ‘/oracle/oradata/rec/rec02.dbf') and pl (datafiles ‘/oracle/oradata/pl/proj01.dbf' and ‘/oracle/oradata/pl/proj02.dbf') from a source database running on a Sun Solaris host to a destination database running on an Linux PC host. You plan to perform conversion on the target host. You will temporarily store the unconverted datafiles in the directory /tmp/oracle/transport_solaris/ on the target host. When the datafiles are plugged into Oracle, they will be stored in /oradata/oracle/rec/.

The example assumes that the following steps have been carried out in preparation for the tablespace transport:

  • The source tablespaces to be transported are set to be read-only, the Export utility has been used to create the metadata information file (named, in our example, research.dchmp), the research.dmp and the unconverted tablespace datafiles to be transported have been gathered and copied to the destination host, to the '/tmp/oracle/transport_solaris/' directory.
     

  • The subdirectory structure from the files original location has been preserved, that is, the datafiles are stored as:

a.        /tmp/oracle/transport_solaris/rec/research01.dbf

b.       /tmp/oracle/transport_solaris/rec/research02.dbf

c.        /tmp/oracle/transport_solaris/pl/proj01.dbf

d.       /tmp/oracle/transport_solarisr/pl/proj02.dbf

Now you can use RMAN's CONVERT command to convert the datafiles to be transported into the destination host's format and deposit the results in /oracle/oradata/rec.

You should note the following:

  • Datafiles must be identified by their names, not by their tablespace name. The local instance has no way of knowing the desired tablespace datafile names until the tablespace is plugged in.

  • The FORMAT argument controls the naming and location of the converted datafiles.

You can not specify the source or destination platform. RMAN determines the source platform by examining the datafile, and the target platform defaults to the platform of the host running the RMAN conversion.

% rman TARGET /
RMAN> CONVERT DATAFILE='/tmp/oracle/transport_solaris/*'
DB_FILE_NAME_CONVERT=
'/tmp/oracle/transport_solaris/research', '/oracle/oradata/rec/research',
'/tmp/oracle/transport_solaris/pl','/oracle/oradata/pl'

The result is a set of converted datafiles in the /oracle/oradata/rec/ and /oracle/oradata/pl directories, named thus:

  • /oracle/oradata/rec/research01.dbf
     

  • /oracle/oradata/rec/research02.dbf
     

  • /oracle/oradata/pl/proj01.dbf
     

  • /oracle/oradata/pl/proj02.dbf

Now follow the usual method for tablespace transport. Use Import to plug the converted tablespaces metadata into the new database, and as a final step, make the tablespaces read-write if needed.

 

 

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.


 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster