Create SPFILE Under ASM

Source: Internet
Author: User

 

-- Suppose that the database instance name is "osmdr"

-- In the database instance

When trying to create a new spfile from a pfile under sqlplus

SQL> Create spfile = '+ Data/osmdr/spfileosmdr. ora' from pfile = '/home/Oracle/osmdr. init ';
File Created.

SQL> Create spfile = '+ Data/osmdr/spfileosmdr. ora' from pfile = '/home/Oracle/osmdr. init ';

File Created

Under ASM, The spileosmdr. ora is link to the wrong location under ASM

Asmcmd> ls-ALR
Type redund striped time sys name
N spfileosmdr. ora =>
+ Data/db_unknown/parameterfile/spfile.272.613162051
Asmcmd>

 

Q. Why it will create + Data/db_unknow instead of + Data/osmdr?

 

 

-- Considering the testcase hereunder

The string "db_unknown/parameterfile/spfile" is a generic system tag for the proxied spfile creation. this will be used in case the database instance has no open client session to the ASM instance at the time of the 'create spfile' command. that is, the ASM instance is not aware of the DB name and therefore uses "db_unknown ".

Please see the testcase section above that demonstrates two scenarios;

[A] Where the db_unknown directory structure gets created, and [B] Where the spfile is created in the directory <diskgroup_name>/<db_name>/parameterfile.

 

 

Testcase

[A] No open database connection to the ASM instance

-- In the ASM instance

SQL> select * from V $ asm_client;

No rows selected

 

-- In the database instance

SQL> show parameter db_name

Name type value
-----------------------------------------------------------------------------
Db_name string v1020

SQL> select * from V $ asm_client;

No rows selected

SQL> Create spfile = '+ DG1' from pfile;
File Created.

 

-- In asmcmd
-- The db_unknown directory structure gets created

Asmcmd> pwd
+ DG1
Asmcmd> ls
Db_unknown/
V1020/
Asmcmd> ls db_unknown/
Parameterfile/
Asmcmd> ls db_unknown/parameterfile/
Spfile.259.613339345

-- Remove the parameter file from the ASM diskgroup

Asmcmd> RM db_unknown/parameterfile/spfile.259.613339345

-- Now the db_unknown directory structure gets automatically removed

 

[B]

-- In the database instance
-- Open a connection to the ASM instance by accessing a datafile in an ASM diskgro
-- In this example, a tablespace with a datafile in an ASM diskgroup is onlined

 

SQL> alter tablespace ts1 online;
Tablespace altered.

SQL> select * from V $ asm_client;

Group_number instance_nam db_name status Software
---------------------------------------------
1 + ASM v1020 connected 10.2.0.3.0 10.2.0.0.0

-- In the ASM instance

 

SQL> select * from V $ asm_client;
Group_number instance_nam db_name status softw
-------------------------------------------
1 v1020 v1020 connected 10.2.0.3.0 10.2.0.1.0

 

-- In the database instance

SQL> Create spfile = '+ DG1' from pfile;
File Created.

 

-- In asmcmd

Asmcmd> pwd
+ DG1
Asmcmd> ls
V1020/
Asmcmd> ls v1020/
Datafile/
Parameterfile/
Asmcmd> ls v1020/parameterfile/
Spfile.259.613339813

 

-- Result:
-The spfile is located in the directory <diskgroup_name>/<db_name>/parameterfile
-No db_unknown directory structure got created

 

 

 

 

 

From Oracle

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Chat group: 40132017 chat 2 group: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.