grid的listener和oracle的listener

用了asm后，gh(grid_home)和oh（oracle_home）两边都有listener，到底该用谁的呢。我测试了一下，是不能删掉gh那边的listener的（我用的srvctl remove listener -l LISTENER），停止了后，就乱了，也就是说，gh这边的listener一定要让oracle restart去启动，不能删。当然oraclehome那边的是可以不用的。但为了方便，我两边都用，于是我修改了gh的lisener.ora。

名字修改为listenerasm，端口变成了1524，后
srvctl  stop listener
srvctl remove listener -l LISTENER
srvctl add listener -l LISTENERASM -p "TCP:1524" -o $ORACLE_HOME
另外还需要修改asm里面的local_listener参数
 以sysasm进去后，
alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.81)(PORT=1524))"
然后重启后，就可以了。
crsctl stat res -t可以看到有了ora.LISTENERASM.lsn的资源，并且是online的。

到这里，好像是结束了，但是用
srvctl config asm看时，发现listenr是空的
ASM home: /u01/app/grid/product/12.1.0/grid
Password file: +DG_GRID/orapwasm
ASM listener:
Spfile: +DG_GRID/ASM/ASMPARAMETERFILE/registry.253.864659509
ASM diskgroup discovery string: /dev/raw/*

找了google，用下面的法子给填上了
 crsctl modify resource ora.asm -attr START_DEPENDENCIES="hard(ora.cssd) weak(ora.LISTENERASM.lsnr)"

然后再次执行
[grid@orasrv localhost]$ srvctl config asm
ASM home: /u01/app/grid/product/12.1.0/grid
Password file: +DG_GRID/orapwasm
ASM listener: LISTENERASM
Spfile: +DG_GRID/ASM/ASMPARAMETERFILE/registry.253.864659509
ASM diskgroup discovery string: /dev/raw/*

也可以用下面的方法

srvctl remove asm
srvctl add asm -l LISTENERASM -p +DG_GRID/ASM/ASMPARAMETERFILE/registry.253.864659509
srvctl config asm

在12.1.0.2里面这样做了，passwordfile就空了，需要通过asmcmd的pwset去设置

asm的静态注册listener居然是区分大小写的

SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.beikan)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2.beikan)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pdborcl.beikan)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pdborclasm.beikan)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = +ASM)
      (ORACLE_HOME = /u01/app/grid/product/12.1.0/grid)
      (SID_NAME = +ASM)
    )
  )

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orasrv.beikan)(PORT = 1523))
    )
  )

我用小写启动listner2后，是连不上的。当然tnsname.ora里不区分大小写。

grid的安装

oinstall   grid, oracle     Oracle Inventory and Software Owner
dba        oralcle           Database Administrator
asmadmin   grid            Oracle Automatic Storage Management Group
asmdba     grid, oracle    ASM Database Administrator Group
asmoper    grid            ASM Operator Group
oper       oracle        Database Operator

 我碰到的问题是如果oracle不是asmdba的用户的话，dbca里面是看不见asm实例里面建立的diskgroup

而/dev/raw下面的权限，给grid:asmdba，让grid和oracle都可以读取。
 [root@orasrv rules.d]# cat /etc/udev/rules.d/60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
#   ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
#   ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N" 
ACTION=="add", KERNEL=="sdb2", RUN+="/bin/raw /dev/raw/raw2 %N" 
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sdc2", RUN+="/bin/raw /dev/raw/raw4 %N"
KERNEL=="raw*", OWNER="grid" GROUP="asmdba", MODE="0660"
  

# start_udev
# raw -qa
# ls -l /dev/raw

如果需要grid用户的crs能启动database，还需要将grid用户加入到dba组才可以启动，否则

[grid@orasrv ~]$ srvctl start database -d orclem
PRCR-1079 : Failed to start resource ora.orclem.db
ORA-01017: invalid username/password; logon denied
CRS-5017: The resource action "ora.orclem.db start" encountered the following error: 
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/orasrv/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.orclem.db' on 'orasrv' failed
ORA-01017: invalid username/password; logon denied

oracle的平台间传送

TTS之间的传递需要convert的，应该只是endian不同的情况才需要，不同平台但endian相同，应该是不需要covnert就可以的。

如果是这样，这种情况下，应该也不需要再导出meta后 alter tablespace  read write的了

https://docs.oracle.com/cd/E15817_01/backup.111/e05700/rcmxplat.htm

oracle的权限

对象权限

书上说， owner grant 对象权限1 给 A with grant option，然后A grant 给B。 A不能 revoke from B。只能ower(或者有grant any objectprivilege的user) 去 rovke from B。但是我在11g,12c里面测试，结论如下

对象权限，只能grant出去的的人去回收，就算owner或者sys都回收不回来，会报【ORA-01927: 付与していない権限にはREVOKEを実行できません。】错误，上面的B的权限只能A去revoke。当然，owner可以通过级联来回收B的权限，比如上面的owner收回A的，B的也就没了。

特殊的情况是，sys可以revoke owner分发出去的权限，比如上面sys可以回收A的权限，但是不能回收由A分发出去的B的权限。

真的是比较奇怪。

audit by session & by access

11g和12c里面，对ddl只能进行by access了，执行by session会32595错。
但是，仍然可以对权限进行by session，
比如 audit select any table by zyz by session，不会报错。可是它的效果仍然是by access（一个session会产生多条记录）。 这和以前的版本是完全一些样，语句通过了，效果仍然是by access。

目前，在orcale9的文档里才有这个说明，说ddl的只能用by access。

http://docs.oracle.com/cd/B10500_01/server.920/a96524/c25audit.htm

The AUDIT statement lets you specify either BY SESSION or BY ACCESS. However, several audit options can be set only BY ACCESS, including:

• All statement audit options that audit DDL statements
• All privilege audit options that audit DDL statements

For all other audit options, BY SESSION is used by default.

11g
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF53733
12c
http://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF55571
BY SESSION
In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. Oracle recommends that you include the BY ACCESS clause for all AUDIT statements, which results in a more detailed audit record. If you specify neither clause, then BY ACCESS is the default.

Note:

This change applies only to schema object audit options, statement options and system privileges that audit SQL statements other than data definition language (DDL) statements. The database has always audited BY ACCESS all SQL statements and system privileges that audit a DDL statement.

BY ACCESS

Specify BY ACCESS if you want Oracle Database to write one record for each audited statement and operation.

Note:

If you specify either a SQL statement shortcut or a system privilege that audits a data definition language (DDL) statement, then the database always audits by access. In all other cases, the database honors the BY SESSION or BY ACCESS specification.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY ACCESS is the default.

就算给了用户connect的role，仍然连接不上ORA-01045

10g以后的connect role里面就只有一个CREATE SESSION，照理如果给了用户这个role，就能连接不上，后来知道光
GRANT "CONNECT" TO user
不行
还得将这个role设置为user对的default role，也就是

alter user user default role connect

至于为啥要弄成deafult role，得看oracle的文档了

用户虽然有这个role，但是没enable，需要切换到这个role才能行使这个role的权限。

http://dba-tips.blogspot.jp/2012/03/about-default-roles.html