Shulou(Shulou.com)06/01 Report--

How to understand Restrict Session and Restricted Mood, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Check the official documentation of Oracle and see two special system permissions, System Privilege, SYSDBA and SYSOPER. Among the operations that can be performed by two permissions, there is a permission project called Restricted Session to do some in-depth research.

Starting the Oracle database server is a phased operation. It can be divided into three stages: nomount, mount and Open. The following is a phased launch demonstration on the linux platform.

/ / Connect to an empty instance. Note that OS authentication is used for verification here.

SQL > conn / as sysdba

Connected to an idle instance.

/ / nomount status startup, instance process startup, SGA space allocation, parameter file loading

SQL > startup nomount

ORACLE instance started.

Total System Global Area 414298112 bytes

Fixed Size 1336904 bytes

Variable Size 310380984 bytes

Database Buffers 96468992 bytes

Redo Buffers 6111232 bytes

/ / Control files load applications and locate data files

SQL > alter database mount

Database altered.

/ / Open the database file to realize the operation

SQL > alter database open

Database altered.

After startup, the database user can connect.

Start restricated mode

As a DBA, you can control the startup phase and process. At different stages, you can perform different types of operations, such as backing up various types of files.

Sometimes, we want only certain users to connect to the database, such as administrator-level users. At this point, we can start the database in restricted mode mode.

After enabling restricted mode to start and run the database, only those users with create session and restricted session privileges can log in to the system database. By default, only SYSDBA and SYSOPER users have restricted session permissions. It means that only users with database management rights can carry out data management under restricted mode.

It is also important to note that under restricted mode, users can only log in locally without a listener, even if they have restricted session privileges. Login in Remote mode is rejected.

In general, restricted mode is considered for maintenance work only in the following cases.

You need to import and export data. In this process, data integrity cannot be guaranteed, and it is best to prohibit application users from logging in.

Using SQL*Loader to load data

Temporary denial of access to data by ordinary users

Perform specific data migration and upgrade operations

Using the startup restrict command, you can start the database in restricted mode mode.

SQL > startup restrict

ORACLE instance started.

Total System Global Area 414298112 bytes

Fixed Size 1336904 bytes

Variable Size 310380984 bytes

Database Buffers 96468992 bytes

Redo Buffers 6111232 bytes

Database mounted.

Database opened.

At this point, try to log in from the client.

First, try logging in to the local local

/ / Log in locally by bypassing the listener program, yes.

SQL > conn / as sysdba

Connected.

SQL > select count (*) from dba_objects

COUNT (*)

-

72461

/ / listeners log in locally

SQL > conn sys/sys@wilson as sysdba

ERROR:

ORA-12526: TNS:listener: all appropriate instances are in restricted mode / / error reported, rejected

Warning: You are no longer connected to ORACLE.

/ / users with non-restricted session permissions

SQL > conn scott/tiger@wilson

ERROR:

ORA-12526: TNS:listener: all appropriate instances are in restricted mode / / connection denied

/ / Local non-permission login

SQL > conn scott/tiger

ERROR:

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

Switch to remote login this time

SQL > conn sys/sys@wilson as sysdba

ERROR:

ORA-12526: TNS: listener: all applicable routines are in restricted mode

SQL > conn / @ wilson as sysdba

ERROR:

ORA-12526: TNS: listener: all applicable routines are in restricted mode

SQL > conn scott/tiger@wilson as sysdba

ERROR:

ORA-12526: TNS: listener: all applicable routines are in restricted mode

The test results are rather strange.

1. Restricted mode is a connection mode that seems to be intercepted by the listener

2. After bypassing the listener program, you need to verify the permissions

Both conditions are met, verifying that the user is authorized and logged in locally.

Is there anything special about the status of the listener?

[oracle@oracle11g ~] $lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 04-JAN-2011 15:23:08

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=oracle11g) (PORT=1521)

STATUS of the LISTENER

-

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 04-JAN-2011 14:55:16

Uptime 0 days 0 hr. 27 min. 52 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / u01/oracle/network/admin/listener.ora

Listener Log File / u01/diag/tnslsnr/oracle11g/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=oracle11g) (PORT=1521))

Services Summary...

Service "wilson" has 1 instance (s).

Instance "wilson", status RESTRICTED, has 1 handler (s) for this service...

Service "wilsonXDB" has 1 instance (s).

Instance "wilson", status RESTRICTED, has 1 handler (s) for this service...

The command completed successfully

As you can see, the listener knows the state of the database. This makes it understandable that the listener reported an error of 12526.

Recovery state

With the alter system command, you can convert the database system from restricted mode to normal mode.

SQL > alter system disable restricted session

System altered.

/ / wait a moment, because dynamic registration may take a while to update

SQL > conn scott/tiger@wilson

Connected.

SQL > select count (*) from emp

COUNT (*)

-

fourteen

System restricted access for Open statu

When the database is in the open state, we can also forcibly convert the system mode to restricted mode through the alter system command. The command is:

SQL > alter system enable restricted session

System altered.

At this point, the system will switch to restricted mode. Note that it is possible that another session is connected to the database at this time, and different structures are handled differently.

In a single-instance environment, if the system does not have Oracle Restart (a tool for Oracle management) installed, all user sessions will not be affected. So, after switching to restricted mode, before doing administrative work, manually kill all user sessions.

In a single-instance environment, if there is an Oracle Restart component, all services managed by Oracle Restart are handled offline and all user session is automatically terminated. Connections to services not managed by Restart will not go offline

In ü RAC environment, the services of instances are managed by Oracle Clusterware and will be terminated automatically. Components that are not managed by Clusterware will not be processed offline

Restricted Mode is a management tool, which is quite practical when managing the production environment.

After reading the above, have you mastered how to understand Restrict Session and Restricted Mood? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.