Oracle10gR2 Logical Standby(四)转换逻辑备库的过程
关于Oracle10gR2逻辑备库，之前写过几篇：
Oracle10gR2 Logical Standby(一)概念与原理
Oracle10gR2 Logical Standby(二)配置前需要考虑的问题
Oracle10gR2 Logical Standby(三)配置逻辑备库
这里记录一下Oracle10gR2物理备库转换为逻辑备库的过程中，Oracle都做了哪些操作。从alert中可以获得很多有用的信息：当执行alter database recover to logical standby xxxx的命令后，首先启动基于SCN的不完全恢复(Oracle10g会自动开启并行恢复，这个例子中启动了15个并行恢复进程)。然后clear online redo logfile，但是Oracle10g在将备库置于manged standby状态的时候就提前将这个clear的动作做了，所以这里记录的是previously cleared，然后将备库激活，调用nid修改数据库的DBID。
alter database recover to logical standby shtest
Thu Jun 5 14:02:55 2008
Media Recovery Start: Managed Standby Recovery (shtest)
Thu Jun 5 14:02:55 2008
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 15 processes
Media Recovery Log /oradata/archive/shtest/1_2274_655399684.arc
Media Recovery Log /oradata/archive/shtest/1_2275_655399684.arc
Media Recovery Log /oradata/archive/shtest/1_2276_655399684.arc
Media Recovery Log /oradata/archive/shtest/1_2277_655399684.arc
Thu Jun 5 14:02:56 2008
Incomplete Recovery applied until change 20447678
Thu Jun 5 14:02:56 2008
Media Recovery Complete (shtest)
RESETLOGS after incomplete recovery UNTIL CHANGE 20447678
Resetting resetlogs activation ID 54725959 (0×3430d47)
Online log /oradata/shtest/redo01_01.dbf: Thread 1 Group 1 was previously cleared
Online log /oradata/shtest/redo01_02.dbf: Thread 1 Group 1 was previously cleared
Online log /oradata/shtest/redo02_01.dbf: Thread 1 Group 2 was previously cleared
Online log /oradata/shtest/redo02_02.dbf: Thread 1 Group 2 was previously cleared
Online log /oradata/shtest/redo03_01.dbf: Thread 1 Group 3 was previously cleared
Online log /oradata/shtest/redo03_02.dbf: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 20447676
Thu Jun 5 14:03:01 2008
Setting recovery target incarnation to 2
Thu Jun 5 14:03:01 2008
Converting standby mount to primary mount.
Thu Jun 5 14:03:01 2008
ACTIVATE STANDBY: Complete - Database mounted as primary (shtest)
*** DBNEWID utility started ***
DBID will be changed from 54167428 to new DBID of 55371925 for database SHTEST
DBNAME will be changed from SHTEST to new DBNAME of SHTEST
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Database name changed to SHTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database SHTEST changed to 55371925.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Thu Jun 5 14:03:03 2008
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Thu Jun 5 14:03:03 2008
Completed: alter database recover to logical standby shtest
Oracle10.2.0.4 Linux64版本监听存在多个子进程
Update:还是被这个监听搞了一回，即使加了SUBSCRIBE_FOR_NODE_DOWN_EVENT_
连续两台全新安装的Linux64+Oracle10.2.0.4的环境，启动listener后，都出现了4个进程：
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
Kernel \r on an \m
oracle 3244 1 0 09:37 ? 00:00:00 /u01/oracle/product/10.2/bin/tnslsnr listener_stb -inherit
oracle 3245 3244 0 09:37 ? 00:00:00 /u01/oracle/product/10.2/bin/tnslsnr listener_stb -inherit
oracle 3246 3245 0 09:37 ? 00:00:00 /u01/oracle/product/10.2/bin/tnslsnr listener_stb -inherit
oracle 3247 3245 0 09:37 ? 00:00:00 /u01/oracle/product/10.2/bin/tnslsnr listener_stb -inherit
oracle 5841 5504 0 10:12 pts/2 00:00:00 grep tns
之前一台Linux32位平台从9i升级上来的没有这个问题，一套Linux64平台从10.2.0.3升级上来的RAC也没有这个问题。在10.2.0.3之前，曾经有一个bug 4518443，listener可能产生一个或者多个子进程，最终导致监听hang住无法提供服务。这回到好，一启动直接就开4个进程。Oracle在10.2.0.3版中加入了4518443的patch，应该是屏蔽了某段代码，而看来到10.2.0.4他们认为这段问题代码找到了解决方法，又加了进来，结果在Linux64平台上问题更严重了，faint。
通过Bug 4518443提供的workaround可以暂时屏蔽该问题， 在listener.ora中加入：
按照metalink的说法，这个语句关闭了监听自动向ONS(Oracle Notification Services)注册，正是这个注册可能导致监听启动子进程。ONS是RAC中的一个组件，禁用该特性将导致RAC的FAN(Fast Application Notification)特性不可用。还好我这里两台都是单机，这么解决应该没什么问题。
Bug啊，总是来了又走，走了又来，就像移动联通电信，分了又合，合了又分，呵呵。
如何获得Oracle用户创建和授权语句
有时候，我们需要在不同的库中复制用户定义，比如需要在一个测试库中创建和产品库中同名的用户，并且拥有同样的权限。或者在同一个库中创建一个不同名的用户，但是和另外一个用户拥有同样的权限等。换句话说，就是需要获得某个用户的创建和授权语句。
可以通过SQL从一些数据字典中查询到授权信息，生成授权语句：
set pagesize 1000
select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' to '||tt.grantee||';'
from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';'
from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));
Redhat Linux上Oracle如何启用AIO
从Oracle9iR2开始支持Linux上的异步IO，但是Oracle9iR2和Oracle10gR1中的AIO模块默认是disable的，如果要启用必须relink一下
make -f ins_rdbms.mk async_on
make -f ins_rdbms.mk ioracle
当然，如果要关闭AIO支持，只需要使用async_off选项进行relink即可。在Oracle10gR2中AIO默认已经是开启的了。可以通过ldd或者nm来检查oracle是否已经启用了AIO支持，有输出代表已经启用
libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000003ca9800000)
/usr/bin/nm $ORACLE_HOME/bin/oracle | grep io_getevent
w io_getevents@@LIBAIO_0.4
当然，Linux也必须已经安装了AIO相关的package
libaio-0.3.105-2
libaio-devel-0.3.105-2
可以通过查看slabinfo统计信息查看操作系统中AIO是否运行，slab是Linux的内存分配器，AIO相关的内存结构已经分配的话(第二列和第三列非0)说明AIO已经启用
kioctx 102 170 384 10 1 :tunables 54 27 8 : slabdata 17 17 0
kiocb 488 495 256 15 1 :tunables 120 60 8 : slabdata 33 33 120
最后，还需要在Oracle中设置相关的初始化参数来使用AIO
filesystemio_options = asynch #文件系统才需要
