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 - Allows 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;