ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

今天虚拟机上安装了DG，到了最后测试阶段
primary:
操作系统：Oracle Linux Server release 6.4
数据库：oracle10g 10.2.0.1 64
standby:
操作系统：Oracle Linux Server release 6.4
数据库：oracle10g 10.2.0.1 64
主库操作：
1、主库切日志
SQL> alter system switch logfile;
System altered.
2、查看
SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
      1314 YES
      1315 YES
      1316 YES
      1317 YES
      1318 YES
      1319 NO

备库操作：
1、备库查看
SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
      1314 YES
      1315 YES
      1316 YES
      1317 YES
      1318 YES
      1319 NO
2、重新连接一个session，开启备库管理恢复进程，即MRP，报错：
SQL> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

3、查看错误
[oracle@dbstandby1 ~]$ oerr ora 1093
01093, 00000, "ALTER DATABASE CLOSE only permitted with no sessions connected"
// *Cause:  There is at least one more session other than the current one
//         logged into the instance.  ALTER DATABASE CLOSE is not permitted.
// *Action: Find the other sessions and log them out and resubmit the command
原因应该是除了当前session，还有其它不少于1个session连接着实例，
4、 把其它session关闭，只保留当前，然后执行
SQL>  SELECT SID, PROCESS, PROGRAM FROM V$SESSION  WHERE TYPE = 'USER'  AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);
       SID PROCESS      PROGRAM
---------- ------------ ------------------------------------------------
       151 63478        sqlplus@dbstandby1 (TNS V1-V3)
       157 2875         oracle@dbprimary1 (TNS V1-V3)
       162 63554        sqlplus@dbstandby1 (TNS V1-V3)
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL>  alter database recover managed standby database disconnect from session;
Database alterd.
5、查看MRP进程
SQL> select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
RFS
MR(fg)
6、查看备库日志应用状态
SQL>  select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
      1314 YES
      1315 YES
      1316 YES
      1317 YES
      1318 YES
      1319 YES
多切换几次主库的logfile，然后查看备库状态，发现都能自动应用
SQL>  select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
      1314 YES
      1315 YES
      1316 YES
      1317 YES
      1318 YES
      1319 YES
      1320 YES
      1321 YES
8 rows selected.
以下是上面报错时的警告日志
alert

Active process 63555 user 'oracle' program 'oracle@dbstandby1 (TNS V1-V3)'
CLOSE: Active sessions prevent database close operation
CLOSE: Error 1093 during database close
ORA-1093 signalled during: alter database recover managed standby database...
Sun Feb  5 09:53:30 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:53:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:54:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:55:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:56:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:57:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:58:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 09:59:53 2017
ksvcreate: Process(m000) creation failed
Sun Feb  5 10:00:53 2017
ksvcreate: Process(m000) creation failed

PS：
1、看有的文章说：
(注意：Oracle 10g，要在mount状态下，才能开启MRP。然而11g在open就可以)
但是上面的例子就是oracle10g，在open状态也可以启动MRP进程。只是10g备库在启动MRP进程后，数据库status和 open_mode均变为mount状态（11g status仍为open，open_mode变为READ ONLY WITH APPLY）
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> SELECT status from  v$instance;
STATUS
------------
MOUNTED
2、10G备库在MRP进程启动后，  alter database open报错
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
只有关闭MRP进程后，才可以启动到open状态
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN

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

转载于:http://blog.itpub.net/31414383/viewspace-2133085/