How to create user for oracle10g/11g asm instance

最新推荐文章于 2023-11-16 15:49:27 发布

cppg67031

最新推荐文章于 2023-11-16 15:49:27 发布

阅读量101

点赞数

在常规的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:

select value from v$parameter where 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 for remote_login_passwordfile is NONE. If remote_login_passwordfile is set to NONE, set it to EXCLUSIVE or SHARED using an alter system command or by editing the init+ASM.ora file (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 UserName in sqlplus
SQL> connect UserName/Password as 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 sysdba to 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-1426564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-1426564/