Oracle10gR2 Logical Standby(五)逻辑备库的Switchover
相对物理备库的切换，逻辑备库的切换稍微复杂点，但只要按照步骤，整个操作过程也可以在很短的时间内完成。这里记录下主库正常时的switchover操作过程：
1.切换前的检查
包括主备库参数设置，主备库的TNS设置，以及应用连接数据库的TNS等，确保主备库角色转换后整个系统运行不受影响。
2.主库准备切换
命令执行成功后，主库的状态
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
3.备库准备切换
命令执行成功后，备库的状态
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
4.主库开始切换
执行完第3步后，现在的备库需要生成logminer所需要的数据字典信息，然后传送到现在的主库，所以要执行逻辑备库切换，必须在备库也设置到主库的归档路径，并且主备库监听都需要保持开启。否则，切换的状态就会一直停留在PREPARING SWITCHOVER。
备库数据字典信息传递到主库后，主库的状态变成TO LOGICAL STANDBY，这时才能执行实际的切换。
SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY
alter database commit to switchover to logical standby;
注：在PREPARING SWITCHOVER状态时，可以通过执行以下命令取消切换操作：
5.从库切换
上一步成功后，备库的状态应该变成了TO PRIMARY，则可以执行切换
SWITCHOVER_STATUS
--------------------
TO PRIMARY
alter database commit to switchover to primary;
6.在新的备库启动日志应用进程
如果是实时应用，则
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'));
