oracle中create for,How to create user for oracle10g/11g asm instance

在常规的asm维护管理中，我们可以用具有sysasm和sysdba权限的sys用户登录asm实例，进行相关的维护管理工作，但是在ogg相关的项目工程中，ogg读取asm存放的日志文件，为了管理的安全规范和方便，需要给ogg建立专用的登陆asm实例访问asm日志的具备sysasm或者sysdba权限的账号：

比如，ogg里的asm账号配置：

GGSCI (OSS-FWKT-DB1) 28> edit param EXTIOM

"/arch_1/ogg/dirprm/extiom.prm" 24 lines, 675 characters

extract extiom

setenv(NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

dynamicresolution

userid ogg,password ggspwd

TRANLOGOPTIONS ASMUSER ggadm@ASM,ASMPASSWORD ggadm--这里就是asm实例账号。

exttrail /arch_1/ogg/dirdat/pf

DBOPTIONS ALLOWUNUSEDCOLUMN

nocompressupdates

table IOM.WO_WORK_ORDER_DETAIL;

table IOM.OM_ORDER;

table IOM.OM_SERVICE_ORDER;

然而在10g版本里，ogg访问asm实例是有限制的：

SYS@ASM_instance

Specifies the ASM instance for the connection string. The user must be SYS.

此外10g版本里，没法在asm实例里创建用户。只能通过配置密码文件来实现密码的变更来用新密码登陆asm实例：

Oracle ASM is managed by a set of

processes called an Oracle ASM instance. In comparing an ASM instance to a

database instance; one can see similar processes for an ASM instance, as for a

database instance at the operating system level.

Similarly, ASM can be implemented

in a cluster configuration with multiple instances accessing a single ASM

storage system (compare with a RAC database in which multiple database instances

access a single database).

An Oracle ASM instance can be

accessed remotely through a database listener. Connections to an Oracle ASM

instance may only be made as a user with the SYSDBA privilege. The default ASM

user is SYS. A major difference with ASM, as compared to a database instance, is

that users cannot be added to an ASM instance. However a different username with

the SYSDBA privilege may be specified through the password

file.

Using a password

file

The use of a password file is

mandatory to connect remotely to an instance using a SYSDBA privilege. The first

step is to find out whether the ASM instance is configured to make use of a

password file. Connect to the ASM instance (using SYS (or /) as SYSDBA) and run

the following query:selectvalue fromv$parameterwhere name = ?remote_login_passwordfile?;There are three possible outcomes

for this query:

NONE

The

instance will not make use of a password file if there is one.

EXCLUSIVE:

The

password file is only used for this ASM instance.

SHARED:

The

password file may be used by other ASM or database instances.

If there is no password file, then

the ASM instance will behave as if the setting forremote_login_passwordfileis NONE. Ifremote_login_passwordfileis set to NONE, set it to

EXCLUSIVE or SHARED using an alter system command or by editing theinit+ASM.orafile (followed by a restart of the ASM

instance).

Next make sure a password file was

generated for the ASM instance, and if it wasn?t, generate one. More information

on how to configure and generate a password file is in the Oracle Database

documentation.

For GoldenGate to connect to the

ASM instance with a username other than the SYS username and password a SHARED

password file is required.

*************************************************************************************

Please check step by step

a) set environment variables ORACLE_HOME and ORACLE_SID

export ORACLE_SID = sid

export ORACLE_HOME = home

b)create database user : login to RDBMS in sqlplus and create new user

create user UserName identified by Password

connect / as sysdba

grant sysdba to UserName

c) rename original ASM password file

copy RDBMS password file and rename to ASM password file name

mv .orig -- this is a backup

asm password file

cp

d) verify the connection to ASM on a separated machine with UserNamein sqlplus

SQL> connect UserName/Passwordas sysdba

*************************************************************************************************************

而到11g版本，在asm实例里可以直接创建管理asm的用户，并能够grant sysdba和sysasm权限：

grid@OSS-FWKT-DB1:/home/db/grid$ sqlplus '/ as sysasm';

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 3 20:30:52 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> grant sysasm to ggadm;

Grant succeeded.

SQL>

SQL> create user gg identified by gg;

User created.

SQL> grant sysasm to    gg;

Grant succeeded.

SQL> grant sysdbato    gg;

Grant succeeded.

SQL> drop user gg;

User dropped.SQL>注意：

When capturing from an ASM

instance via the ASMUSER option, the maximum amount of data read by Capture is

28,672 bytes. With a default read and write buffer size of 1,024,000 bytes

DBLOGREADER provides the capability to capture large database transactions more

efficiently.