2

what will happen, if we miss the '+' sign while adding datafile to ASM in oracle 11g database? Whether datafile added will be ASM based or Non-ASM based?

4
  • 1
    Non-ASM based. It will try to create the datafile in a default location (in db_create_file parameter, if I'm not mistaken) Commented May 18, 2017 at 12:48
  • @Ronato Afonso Thanks for the suggestion..but what happens if the db_create_file value in my database is empty? Commented May 20, 2017 at 10:46
  • Show me the DDL script. Commented May 22, 2017 at 11:13
  • alter tablespace CWSC add datafile 'DATA' size 100M AUTOEXTEND ON; Commented May 25, 2017 at 5:59

1 Answer 1

1

If you miss the '+' sign, there will be several scenarios:

  1. db_create_file_dest is set and points to ASM (i.e: +DATA): The database will take 'DATA' as an alias and will create ASM file on the destination associated with the 'DATA' alias that will be located at the ASM +DATA root directory.
  2. db_create_file_dest is set and points to filesystem: It should be quite obvious that you'll have a new 'DATA' file in the location pointed at the parameter.
  3. db_create_file_dest is not set (worst scenario): The database will default to $ORACLE_HOME/dbs and create a datafile there with the name 'DATA'.

A final piece of advice: Set the db_create_file_dest to point to the ASM DATA disk group and remove the datafile name parameter from your command:

alter tablespace CWSC add datafile size 100M AUTOEXTEND ON;

As you may know, even if you give a name to the datafile, when it's created on ASM, that name becomes an alias and the real filename is set by the ASM instance itself.

I hope this info helps you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.