最近生产系统计划使用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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-714173/
2476
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
