ITPub博客

首页 > Linux操作系统 > Linux操作系统 > restricted_session_quiesce_suspend

restricted_session_quiesce_suspend

原创 Linux操作系统 作者:redhouser 时间:2011-12-29 16:35:34 0 删除 编辑

最近生产系统计划使用HDS的VSP(Virtual Storage Platform)的shadowimage特性实现数据同步,其中涉及suspend操作,在这里对相关特性进行测试比较.

数据库或实例有一些特殊的状态,可用于特殊的维护场景:
*可以限制非特权用户登录(restricted session)
*暂停非SYS/SYSTEM用户的操作(quiesce restricted/unquiesce)
*挂起系统(suspend/resume)

1,限制会话(restricted session)
仅允许有RESTRICTED SESSION权限的用户本地登录,不影响已登陆用户操作.

1.1启动实例
设置:
STARTUP [MOUNT|NOMOUNT|OPEN] RESTRICT
查询状态:
select logins from v$instance;
解除:
ALTER SYSTEM DISABLE RESTRICTED SESSION;

1.2已启动实例
使用restricted session限制当前实例登录,不影响当前会话
设置:
alter system ENABLE RESTRICTED SESSION;
查询状态:
select logins from v$instance;
==>RESTRICTED
解除:
alter system DISABLE RESTRICTED SESSION;

测试:
1.2.1创建测试用户
create user user_no_res identified by a;
grant create session to user_no_res;

create user user_with_res identified by a;
grant create session,restricted session to user_with_res;

create user user_sysdba identified by a;
grant create session,sysdba to user_sysdba;

1.2.2连接数据库
--session 1:
sqlplus user_with_res/a

--session 2:
sqlplus user_no_res/a

--session 3:
sqlplus user_sysdba/a as sysdba


1.2.3 限制登录ENABLE RESTRICTED SESSION
--session 4:
sqlplus user_sysdba/a as sysdba

SQL> alter system ENABLE RESTRICTED SESSION;
System altered.

SQL> select logins from v$instance;

LOGINS
------------------------------
RESTRICTED


1.2.4连接数据库
--session 1:
SQL> select sysdate from dual;

SYSDATE
------------------
23-DEC-13

SQL> exit
[oracle@rhel5 mah]$ sqlplus user_with_res/a
SQL>

--session 2:
SQL> select sysdate from dual;

SYSDATE
------------------
23-DEC-13

SQL> exit
[oracle@rhel5 mah]$ sqlplus user_no_res/a
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Enter user-name:


--session 3:
SQL> select sysdate from dual;

SYSDATE
------------------
23-DEC-13

SQL> exit
[oracle@rhel5 mah]$ sqlplus user_sysdba/a as sysdba
SQL>

--session 5(remote):
D:\>sqlplus user_sysdba/a@ctg_bocnet_97_96 as sysdba
ERROR:
ORA-12526: TNS: 监听程序: 所有适用例程都处于受限模式

请输入用户名:

1.2.5取消限制登录
--session 3:-->注意不是发起ENABLE RESTRICTED SESSION的session 4.
alter system DISABLE RESTRICTED SESSION;

SQL> select logins from v$instance;

LOGINS
------------------------------
ALLOWED

1.2.6连接数据库
所有登录恢复正常(本地无RESTRICTED SESSION权限的登录,远程的登录).

测试结论:
*系统限制会话后,只允许有RESTRICTED SESSION权限的用户登录
*系统限制会话后,已存在所有的会话操作不受影响
*系统限制会话后,不允许远程登陆,即使有RESTRICTED SESSION权限

2,静默状态(quiesce)
在静默状态下,只有具有DBA权限(这里特指SYS和SYSTEM用户,与DBA角色无关)的用户能够在数据库中执行查询/更新操作,运行PL/SQL程序,任何非DBA用户都不能在数据库中执行任何操作.

执行alter system quiesce restricted后,数据库将等待所有正在运行的非DBA用户会话主动终止/事务结束,同时不再允许开始任何新的非DBA用户会话。当所有的非DBA用户的活动会话都被成功暂停后,alter system quiesce restricted语句执行完毕,这是数据库被认为处于静默状态。在静默状态中,即使某个非DBA用户试图执行一条SQL语句强行激活某个会话,该SQL语句也会挂起。当数据库从静默状态中恢复时,停止的会话将继续执行,前面被挂起的SQL语句也会继续执行。

设置:
alter system quiesce restricted;
查询状态:
select ACTIVE_STATE from v$instance;
==>NORMAL: Normal unquiesced state.
==>QUIESCING: Being quiesced, but some non-DBA sessions are still active.
==>QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

解除:
alter system unquiesce;

2.1建立连接
--session 1:
[oracle@rhel5 mah]$ sqlplus mh/mh
SQL> show user
USER is "MH"

SQL> update emp set comm='';
14 rows updated.


--session 2:
[oracle@rhel5 mah]$ sqlplus mh/mh  as sysdba
SQL> show user
USER is "SYS"
SQL> update emp set comm='' where empno=7900;

1 row updated.

--session 3:
[oracle@rhel5 mah]$ sqlplus / as sysdba
SQL> show user
USER is "SYS"
SQL> update emp set comm='' where empno=7902;

1 row updated.

2.2进入静默
--session 4:
[oracle@rhel5 mah]$ sqlplus / as sysdba
SQL> show user
USER is "SYS"
SQL> alter system quiesce;   
alter system quiesce
                   *
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter system quiesce restricted;
-->挂起

--session 3:
SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
QUIESCING

2.3连接状态
--session 1:
SQL> delete from test;

2 rows deleted.

SQL> rollback;

Rollback complete.

--session 3:
SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
QUIESCED

--session 1:
SQL> delete from test;
--挂起

--session 6:
[oracle@rhel5 mah]$ sqlplus mh/mh
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 23 15:43:11 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
--挂起

--session 2:
SQL> rollback;

Rollback complete.

SQL> update emp set comm='' where empno=7900;

1 row updated.SQL> rollback;

Rollback complete.

SQL> exit
[oracle@rhel5 mah]$ sqlplus mh/mh  as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL> update emp set comm='' where empno=7902;

1 row updated.


--session 3:
SQL> rollback;

Rollback complete.

SQL> update emp set comm='' where empno=7902;

1 row updated.

SQL> exit
[oracle@rhel5 mah]$ sqlplus / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL>  update emp set comm='' where empno=7900;

1 row updated.


--session 5(远程):
D:\>sqlplus user_sysdba/a@ctg_bocnet_97_96 as sysdba
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>exit
D:\>sqlplus user_sysdba/a@ctg_bocnet_97_96
SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 12月 29 15:48:23 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
--挂起

 

2.4退出静默
--session 3:-->不是发起quiesce的会话
SQL> alter system unquiesce;

System altered.

SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
NORMAL

2.5连接状态
被挂起的会话(非DBA用户发起的连接,非DBA用户新的事务)恢复.

2.6进入静默,会话退出,系统状态
--session 4:
SQL> alter system quiesce restricted;
-->挂起

SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
QUIESCED

SQL> exit

--session 3:
SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
QUIESCED


SQL> alter system unquiesce;

System altered.

SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
NORMAL

SQL> select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);

       SID    SERIAL#
---------- ----------
       158         72
SQL> alter system quiesce restricted;

System altered.

SQL> select ACTIVE_STATE from v$instance;

ACTIVE_STATE
---------------------------
QUIESCED

--session 4:
[oracle@rhel5 mah]$ sqlplus / as sysdba
SQL> alter system kill session '158,72';
System altered.

SQL> select ACTIVE_STATE from v$instance;
ACTIVE_STATE
---------------------------
QUIESCED

测试结论:
*进入quiesce状态过程中,非DBA用户事务会阻塞系统进入静默状态
*进入quiesce状态后,非DBA用户的操作(查询或新事务)会被挂起
*进入quiesce状态后,SYSDBA权限用户可以发起新的事务
*进入quiesce状态后,限制非DBA用户登录
*执行alter system语句的会话被意外中止(exit或kill by other session),系统保持静默状态


3,挂起(suspend)
数据库在挂起状态,数据库所有的物理文件(控制文件/数据文件以及重做日志文件)的I/O操作都被暂停。
这样能够保证数据库在没有任何I/O操作的情况下进行物理备份。挂起的状态与静默状态的区别是:它并不禁止非DBA用户的数据库操作,只是暂时停止所有用户的I/O操作。

在RAC环境,一个实例的挂起会传播到所有实例;挂起后新启动的实例不受此影响.

主要用途:
当数据库处于挂起状态时,可以首先为数据库创建磁盘镜像,然后再从镜像中分离出备份文件,这样就提供了一种进行数据库备份和恢复的替代方法。在数据库进入挂起状态时,当前所有的I/O操作能够继续进行,但是所有新提交的I/O不会执行,而是被放入一个等待队列中。一旦数据库恢复到正常状态,这些I/O操作将从队列中取出并继续执行。

设置:
alter system suspend;

查询状态:
select database_status from v$instance;
==>SUSPENDED

解除:
alter system resume;

测试:
3.1创建连接
--session 1:
sqlplus user_sysdba/a as sysdba

SQL> update emp set comm='';

14 rows updated.


3.2挂起系统
--session 2:
sqlplus user_sysdba/a as sysdba

SQL> alter system suspend;

System altered.

SQL> select database_status from v$instance;

DATABASE_STATUS
---------------------------------------------------
SUSPENDED

3.3对其他会话的影响
--session 1:
SQL> commit;
-->挂起

--session 3:
[oracle@rhel5 mah]$ sqlplus mh/mh as sysdba-->允许sysdba登录
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 23 14:16:31 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit

oracle@rhel5 mah]$ sqlplus mh/mh-->不允许非sysdba登录
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 23 14:17:16 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
-->挂起


--session 4:
SQL> select sysdate from dual;

SYSDATE
------------------
23-DEC-13

SQL> create table t as select sysdate d from dual;

3.4停止挂起
--session 2:
SQL>  alter system resume;

System altered.

SQL> select database_status from v$instance;

DATABASE_STATUS
-------------------
ACTIVE

3.5挂起的会话恢复

测试结论:
*系统挂起后,所有涉及物理IO(数据文件,控制文件,日志)的操作会被挂起,比如commit操作
*系统挂起后,允许sysdba登录,不允许非sysdba登录

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    790624