Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Oracle Grid and SHUTDOWN NORMAL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Funny situation. We have a development database that goes down every night for a cold backup (it is a BCV split, so downtime is minimal). Anyway, one of the DBA's here left GRID on a database Performance screen with 10 sec. refresh interval. Shutdown process is:

a) stop oem_agent
b) shutdown abort
c) startup restrict
d) shutdown normal
e) start oem_agent

Guess what:

sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Dec 8 17:24:33 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Connected.
SQL> select sid,serial#,status,username from v$session
  2  where program = 'OMS';

       SID    SERIAL# STATUS   USERNAME
---------- ---------- -------- ------------------------------
       232          3 INACTIVE SYS

SQL> alter system kill session '232,3';

System altered.

SQL> select sid,serial#,status from v$session
  2  where program = 'OMS';

no rows selected

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area 1761607680 bytes
Fixed Size                  2057480 bytes
Variable Size             788531960 bytes
Database Buffers          956301312 bytes
Redo Buffers               14716928 bytes
Database mounted.
Database opened.
SQL> select sid,serial#,status,username from v$session
  2  where program = 'OMS';

       SID    SERIAL# STATUS   USERNAME
---------- ---------- -------- ------------------------------
       234          7 INACTIVE SYS

SQL> 

Obviously, SHUTDOWN NORMAL waits forever and backup process hangs.

Now, AFAIK remote connections AS SYSDBA to a database in restricted mode are not allowed:

SQL> connect xxx@xxx as sysdba
Enter password: *********
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode


So question I have is how OMS server which is, obviously, on a separate box can make a remote connection AS SYSDBA to a database in restricted mode?

SY.

Сообщение было отредактировано: 9 дек 06, 01:58
9 дек 06, 01:54    [3513668]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
SY
Now, AFAIK remote connections AS SYSDBA to a database in restricted mode are not allowed:

SQL> connect xxx@xxx as sysdba
Enter password: *********
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Странно, может конечно в 10-ке поменялось, но в 9-ке без проблем
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area  277296276 bytes
Fixed Size                   455828 bytes
Variable Size             134217728 bytes
Database Buffers          134217728 bytes
Redo Buffers                8404992 bytes
Database mounted.
Database opened.
SQL> select logins from v$instance;

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

SQL> select machine from v$session where username='SYS' and type='USER';

MACHINE
----------------------------------------------------------------
apollo
lvl.feb.ru

bash-2.05b$ sqlplus 'sys/sys@tst as sysdba'

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Dec 11 09:50:53 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

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

MACHINE
----------------------------------------------------------------
lvl.feb.ru


SY
So question I have is how OMS server which is, obviously, on a separate box can make a remote connection AS SYSDBA to a database in restricted mode?

SY.
Может забить на это и опускать не через NORMAL, а через IMMEDIATE ?
11 дек 06, 03:01    [3516383]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Вячеслав Любомудров
Может забить на это и опускать не через NORMAL, а через IMMEDIATE?


That would work, or I could stop the listener before shutdown abort. I was just curious how it bypasses restricted session.

SY.
11 дек 06, 21:47    [3521255]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
В общем, побаловался на 10.1.0.3
Если регистрация в листенере статическая, то удаленно AS SYSDBA пускает независимо от RESTRICTED SESSION
Если регистрация только динамическая, то при ENABLED RESTRICTED SESSION удаленно не пускает AS SYSDBA (и что самое интересное, SYSTEM тоже (!)) с ошибкой ORA-12526
12 дек 06, 04:01    [3521620]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Вячеслав Любомудров
В общем, побаловался на 10.1.0.3
Если регистрация в листенере статическая, то удаленно AS SYSDBA пускает независимо от RESTRICTED SESSION
Если регистрация только динамическая, то при ENABLED RESTRICTED SESSION удаленно не пускает AS SYSDBA (и что самое интересное, SYSTEM тоже (!)) с ошибкой ORA-12526


Yeap, you are right. Actually, 10g docs mention remote connections to restricted service:

Oracle® Database Administrator's Guide
10g Release 2 (10.2)

Part I Basic Database Administration

Chapter 3 Starting Up and Shutting Down

Restricting Access to an Instance at Startup
You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

Perform an export or import of data

Perform a data load (with SQL*Loader)

Temporarily prevent typical users from using data

Perform certain migration or upgrade operations

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.


And MetaLink explains static versus dynamic registration piece LSNRCTL shows instance status as "RESTRICTED"

In my case it was dynamic registration and OMS somehow still managed to get through.

SY.

Сообщение было отредактировано: 12 дек 06, 20:09
12 дек 06, 20:09    [3526988]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
Alex Roudnev
Member

Откуда: Валнут Крик, Калифорния
Сообщений: 5547
Но между прочим, OMS вполне способен и локально соединяться - он же не сам это делает а агентом пользуется. Там даже во многих местах есть предложение не вводить логин и пароль, чтобы использовать INTERNAL коннекшен.
13 дек 06, 01:00    [3527702]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Alex Roudnev
Но между прочим, OMS вполне способен и локально соединяться - он же не сам это делает а агентом пользуется. Там даже во многих местах есть предложение не вводить логин и пароль, чтобы использовать INTERNAL коннекшен.


Agent was down all the time. See step a) in my original post. And database performance screen brings you to Database Login screen which requires username, password and connection type (either typed in or taken from saved credentials. And besides problem goes away when listener is down, which tells me connection is made via listener.

SY.

Сообщение было отредактировано: 13 дек 06, 18:10
13 дек 06, 18:09    [3532460]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
SY
SQL> select sid,serial#,status,username from v$session
  2  where program = 'OMS';

       SID    SERIAL# STATUS   USERNAME
---------- ---------- -------- ------------------------------
       234          7 INACTIVE SYS

SQL> 
Ради интереса, а что говорят столбцы MACHINE, TERMINAL ?
Действительно удаленная машинка?
14 дек 06, 02:17    [3533700]     Ответить | Цитировать Сообщить модератору
 Re: Oracle Grid and SHUTDOWN NORMAL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Вячеслав Любомудров
Действительно удаленная машинка?


Вячеслав, my first reaction was oбижаешь начальник . Then I decided to double-check:

SQL> host hostname
himhph02

SQL> select program,machine,terminal,type,server from v$session where sid = 222;

PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---------- --------------- ---------- ---------- ---------
OMS        h2ua62218f7                USER       DEDICATED

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area 1761607680 bytes
Fixed Size                  2057480 bytes
Variable Size             721423096 bytes
Database Buffers         1023410176 bytes
Redo Buffers               14716928 bytes
Database mounted.
Database opened.
SQL> select program,machine,terminal,type,server from v$session where program = 'OMS';

PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---------- --------------- ---------- ---------- ---------
OMS        h2ua62218f7                USER       DEDICATED

SQL> 

Looks like a remote connection (and it is: db is on unix host himhph02 grid is on windows box h2ua62218f7). At the same time null OSUSER and NETWORK_SERVICE_BANNER look suspicious:

SQL> select sid,program,machine,terminal,type,server from v$session where program = 'OMS';

 SID PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---- ---------- --------------- ---------- ---------- ---------
 232 OMS        h2ua62218f7                USER       DEDICATED

SQL> select * from v$session_connect_info where sid = 232;

 SID AUTHENTICATION_TYPE OSUSER NETWORK_SERVICE_BANNER
---- ------------------- ------ --------------------------------------------------
 232 INTERNAL

SQL>


And it definitely connects via listener(grid db performance screen refresh is 15 sec.):

SQL> alter system enable restricted session;

System altered.

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

 SID    SERIAL# PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---- ---------- ---------- --------------- ---------- ---------- ---------
 232         37 OMS        h2ua62218f7                USER       DEDICATED

SQL> alter system kill session '232,37';

System altered.

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

no rows selected

SQL> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

 SID    SERIAL# PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---- ---------- ---------- --------------- ---------- ---------- ---------
 232         41 OMS        h2ua62218f7                USER       DEDICATED

SQL> host lsnrctl stop > /dev/null

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

 SID    SERIAL# PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---- ---------- ---------- --------------- ---------- ---------- ---------
 232         41 OMS        h2ua62218f7                USER       DEDICATED

SQL> alter system kill session '232,41';

System altered.

SQL> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

no rows selected

SQL> host lsnrctl start > /dev/null

SQL> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

SQL> select sid,serial#,program,machine,terminal,type,server from v$session where program = 'OMS';

 SID    SERIAL# PROGRAM    MACHINE         TERMINAL   TYPE       SERVER
---- ---------- ---------- --------------- ---------- ---------- ---------
 232         49 OMS        h2ua62218f7                USER       DEDICATED

SQL> 

SY.

Сообщение было отредактировано: 14 дек 06, 19:42
14 дек 06, 19:37    [3538704]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить