12.2RAC instance_mode 支持读写，只读实例共存

###在12.2 当中新推出了一个感觉比较鸡肋的特性，但是我觉得oracle 出这个是肯定有原因的,在官方描述中，在12.2中开始支持read-only，以及read-mostly参数。以下是官方文档原文。

In previous releases, all database instances—unless they accessed a standby database—were read/write.

Starting in Oracle Database 12c Release 2 (12.2), read-only and read/write instances can co-exist within a single database.

This configuration is useful for parallel SQL statements that both query and modify data,

because both read/write and read-only instances can query, while the read/write instances modify.

Unlike read/write instances, read-only instances have the following characteristics:

Can only open a database that has already been opened by a read/write instance

Disable many background processes, including the checkpoint and archiver processes, which are not necessary

Can mount a disabled redo thread or a thread without any online redo log

To designate an instance as read-only, set the INSTANCE_MODE initialization parameter to READ_ONLY. The default value of the parameter is READ_WRITE.

从这段描述上来看，感觉还是挺好的。。至少有一点，可以禁用很多进程。那么意味着比较少的进程占用。其次我们可以配置比较多的内存，同时建立多个rac 单节点在这里，用这台机器来做资源池。

##那么如何使用这些参数呢？

下面做一下测试：

##数据库以读写方式打开：

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> show parameter instance_mode

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

instance_mode string READ-WRITE

SQL> !ps -efgrep ora_wc -l

99

有99个oracle 的进程。

##下面准备开启read-only，这里有点坑。

##查看数据的一些信息，比如线程号，sid等

SQL> select INSTANCE_NAME,STATUS,THREAD#,INSTANCE_MODE from V$instance;

INSTANCE_NAME STATUS THREAD# INSTANCE_MO

---------------- ------------ ---------- -----------

flyme1 OPEN 1 REGULAR

##修改参数

alter system set INSTANCE_MOde='read-only' sid='flyme1' scope=spfile;

##关闭数据库

shutdown immediate;

##这个时候如果直接起，那么会报错。

ORACLE 例程已经启动。

Total System Global Area 1509949440 bytes

Fixed Size 8793016 bytes

Redo Buffers 7983104 bytes

ORA-12965: ????????????????

报错的含义是：

12965, 00000, "cannot mount an enabled thread on a read-only instance"

// *Cause: An attempt was made to mount an enabled redo thread obtained as a user

// specified parameter.

// *Action: The parameter value should be changed to the thread number of a

// disabled thread.

无法mount 一个允许的线程在一个read-only 的实例上。当时我是懵逼了一下，不知道怎么处理，官方文档各种找不到。

实际上，，我们去另外一个节点关了就好了。

##首先禁用日志线程，，inst 2

alter database disable thread 1;

SQL> alter database disable thread 1;

数据库已更改。

##inst 1

SQL> alter database mount;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select INSTANCE_NAME,STATUS,THREAD#,INSTANCE_MODE from V$instance;

INSTANCE_NAME STATUS THREAD# INSTANCE_MO

---------------- ------------ ---------- -----------

flyme1 OPEN 1 READ ONLY

##比较有意思的是，在inst 1 查看gv$instance 是只能看到一个节点。

SQL> select INSTANCE_NAME,STATUS,THREAD#,INSTANCE_MODE from gv$instance;

INSTANCE_NAME STATUS THREAD# INSTANCE_MO

---------------- ------------ ---------- -----------

flyme1 OPEN 1 READ ONLY

##在inst 2看就没问题了，，这个暂时不去想太多吧。嘻嘻

SQL> select INSTANCE_NAME,STATUS,THREAD#,INSTANCE_MODE from gv$instance;

INSTANCE_NAME STATUS THREAD# INSTANCE_MO

---------------- ------------ ---------- -----------

flyme1 OPEN 1 READ ONLY

flyme2 OPEN 2 REGULAR

##再看看启用的线程数有多少个

SQL> !ps -efgrep ora_wc -l

77

##看来也没多少进程被停了。。但是能少点是点吧

##成功