Oracle DB,在用户授权更改后重置实时连接

时间:2021-11-23 22:56:42

When there is a maintenance window, we change the grants of our application user to read-only. After we relaunch our platform (after the maintenance) we put back the grants, but all the existing connections still hold the read only mode. Is there any way to reset my connections without relaunch my platform or lost my active sessions?

当有维护窗口时,我们将应用程序用户的授权更改为只读。在我们重新启动我们的平台之后(维护之后)我们放回了授权,但所有现有连接仍然保持只读模式。有没有办法重置我的连接而无需重新启动我的平台或丢失我的活动会话?

2 个解决方案

#1


0  

If your application is using connection pool and is able to detect broken connections, then simply kill on Oracle side all of your pool sessions after the change of rights.

如果您的应用程序正在使用连接池并且能够检测到断开的连接,那么只需在更改权限后在Oracle端杀死所有池会话。

For this you will need to be able to execute "ALTER SYSTEM KILL SESSION" in separate connection not allocated from this connection pool. You also need to make an SQL query to find SID,SERIAL# pairs for exact sessions of your application.

为此,您需要能够在未从此连接池分配的单独连接中执行“ALTER SYSTEM KILL SESSION”。您还需要进行SQL查询以查找应用程序的精确会话的SID,SERIAL#对。

If connection pooling layer in your app is written good enough, it will detect each broken connection and open a new one, now with new rights. It's best not to use IMMEDIATE parameter to preserve data consistency for your application and avoid errors being reported. That will require some time until active transactions finish. If your application is making long transactions and you cannot wait too much for them, then you can use parameter IMMEDIATE.

如果您的应用程序中的连接池层写得足够好,它将检测每个断开的连接并打开一个新连接,现在具有新的权限。最好不要使用IMMEDIATE参数来保护应用程序的数据一致性,并避免报告错误。这将需要一些时间,直到活动交易完成。如果您的应用程序正在进行长时间的交易而您不能等待它们太多,那么您可以使用参数IMMEDIATE。

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

You can find in Oracle on line documentation how to find sessions which belong to your application.

您可以在Oracle在线文档中找到如何查找属于您的应用程序的会话。

#2


0  

Using a Java Proxy over my JdbcTemplate in combination with reflection , I'm breaking the Hikari Components to extract the stack of connections.

通过我的JdbcTemplate上的Java代理结合反射,我打破了Hikari组件来提取连接堆栈。

Each Connection has a creationTime field, which helps me to decide if I want to kill or not the connection, basically any connection older than 5 min is candidate to die.

每个Connection都有一个creationTime字段,这有助于我决定是否要杀死连接,基本上任何超过5分钟的连接都是死的候选者。

Java Proxy is just to catch the especic exception "ORA-01031" , is the only case when I cant to call the eviction component.

Java代理只是为了捕获特殊的异常“ORA-01031”,是我无法调用驱逐组件的唯一情况。

#1


0  

If your application is using connection pool and is able to detect broken connections, then simply kill on Oracle side all of your pool sessions after the change of rights.

如果您的应用程序正在使用连接池并且能够检测到断开的连接,那么只需在更改权限后在Oracle端杀死所有池会话。

For this you will need to be able to execute "ALTER SYSTEM KILL SESSION" in separate connection not allocated from this connection pool. You also need to make an SQL query to find SID,SERIAL# pairs for exact sessions of your application.

为此,您需要能够在未从此连接池分配的单独连接中执行“ALTER SYSTEM KILL SESSION”。您还需要进行SQL查询以查找应用程序的精确会话的SID,SERIAL#对。

If connection pooling layer in your app is written good enough, it will detect each broken connection and open a new one, now with new rights. It's best not to use IMMEDIATE parameter to preserve data consistency for your application and avoid errors being reported. That will require some time until active transactions finish. If your application is making long transactions and you cannot wait too much for them, then you can use parameter IMMEDIATE.

如果您的应用程序中的连接池层写得足够好,它将检测每个断开的连接并打开一个新连接,现在具有新的权限。最好不要使用IMMEDIATE参数来保护应用程序的数据一致性,并避免报告错误。这将需要一些时间,直到活动交易完成。如果您的应用程序正在进行长时间的交易而您不能等待它们太多,那么您可以使用参数IMMEDIATE。

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

You can find in Oracle on line documentation how to find sessions which belong to your application.

您可以在Oracle在线文档中找到如何查找属于您的应用程序的会话。

#2


0  

Using a Java Proxy over my JdbcTemplate in combination with reflection , I'm breaking the Hikari Components to extract the stack of connections.

通过我的JdbcTemplate上的Java代理结合反射,我打破了Hikari组件来提取连接堆栈。

Each Connection has a creationTime field, which helps me to decide if I want to kill or not the connection, basically any connection older than 5 min is candidate to die.

每个Connection都有一个creationTime字段,这有助于我决定是否要杀死连接,基本上任何超过5分钟的连接都是死的候选者。

Java Proxy is just to catch the especic exception "ORA-01031" , is the only case when I cant to call the eviction component.

Java代理只是为了捕获特殊的异常“ORA-01031”,是我无法调用驱逐组件的唯一情况。