ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to switch Oracle in Single User Mode ? [akadia]

How to switch Oracle in Single User Mode ? [akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-06-03 09:30:07 0 删除 编辑

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this

  • On Table Level using LOCK TABLE

  • On Database Level using RESTRICTED SESSION

Exclusive Table Locks (X)

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:

$ sqlplus scott/tiger

SQL> GRANT ALL ON emp TO PUBLIC;SQL> LOCK TABLE emo IN EXCLUSIVE MODE;

$ sqlplus test/test

SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)

Permitted Operations

Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations

An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

Lock Duration

Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.

Oracle in Single User Mode

You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.

RESTRICTED SESSION

Specifies whether logon to Oracle is restricted

ENABLE

Qllows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.

DISABLE

reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.

You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.

$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
|| username || ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;

As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:

sqlplus scott/tiger

ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege

After the migration to not to forget, to disable the RESTRICTED SESSION system privilege

svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;


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

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

注册时间:2001-10-12

  • 博文量
    240
  • 访问量
    172852