rac环境中连接pdb报ORA-01033
连接pdb数据库报:
ORA-01033: ORACLE initialization or shutdown in progress
检查数据库状态,其中3节点的portal库处于mount状态：
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
INST_ID CON_ID NAME OPEN_MODE
---------- ---------- ------------------------------------------------------- --------------------
1 2 PDB$SEED READ ONLY
1 3 PORTALMESSAGE READ WRITE
1 4 PORTAL READ WRITE
1 5 PORTALDEV READ WRITE
1 6 PORTALMOBILE READ WRITE
1 7 PORTALTEST READ WRITE
1 9 ECOLOGY READ WRITE
2 2 PDB$SEED READ ONLY
2 3 PORTALMESSAGE READ WRITE
2 4 PORTAL READ WRITE
2 5 PORTALDEV READ WRITE
2 6 PORTALMOBILE READ WRITE
2 7 PORTALTEST READ WRITE
2 9 ECOLOGY READ WRITE
3 2 PDB$SEED READ ONLY
3 3 PORTALMESSAGE READ WRITE
3 4 PORTAL MOUNTED <<<<<
3 5 PORTALDEV READ WRITE
3 6 PORTALMOBILE READ WRITE
3 7 PORTALTEST READ WRITE
3 9 ECOLOGY READ WRITE
文档Connecting To A 12c RAC Pluggable Database Intermittently Fails With ORA-1033 (文档 ID 1998112.1)中指出，原因有两个：
1.pdb所使用的服务名与pdb数据库名相同
2.PDB没有在所有RAC实例上打开，在RAC环境中，使用pdb数据库名作为服务名不是一个最佳方案，因为当实例使用SCAN注册pdb名时且节点监听到pdb被mounted。这可能造成非sysdba权限连接被发送到被mounted的pdb实例上，导致ora-0133错误。
创建服务名：
[oracle@rac1 ~]$ $ORACLE_HOME/bin/srvctl add service -database orcl -pdb ECOLOGY -s ecology_srv -preferred "orcl11,orcl12,orcl13" -available "orcl11,orcl12,orcl13" -e select -m basic -P BASIC -failoverdelay 2 -failoverretry 2
此处配置如为-preferred "orcl11" -available "orcl12,orcl13",则在手动停止orcl11上的pdb后无法进行故障转移。
[oracle@rac1 ~]$ $ORACLE_HOME/bin/srvctl start service -database orcl -s ecology_srv
[oracle@rac1 ~]$ srvctl status service -db orcl -s ecology_srv
服务 ecology_srv 正在实例 orcl11 上运行
[oracle@rac1 ~]$ srvctl config service -d orcl -s ecology_srv服务名: ecology_srv
服务器池:
基数: 3
服务角色: PRIMARY
管理策略: AUTOMATIC
DTP 事务处理: 假
AQ HA 通知: 假
全局: 假
提交结果: 假
故障转移类型: SELECT
故障转移方法: BASIC
TAF 故障转移重试次数: 2
TAF 故障转移延迟: 2
故障转移还原: NONE
连接负载平衡目标: LONG
运行时负载平衡目标: NONE
TAF 策略规范: BASIC
版本:
插接式数据库名: ECOLOGY
最长滞后时间: ANY
SQL 转换概要文件:
保留期: 86400 秒
重放启动时间: 300 秒
消耗超时:
停止选项:
会话状态一致性: DYNAMIC
GSM 标记: 0
服务已启用
首选实例: orcl11,orcl12,orcl13
可用实例:
CSS 关键型: no
$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 13-MAY-2021 17:31:10Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 11-MAY-2021 09:17:00
Uptime 2 days 8 hr. 14 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.91)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.102.96)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR_VOTE" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "99f774786184674be0535b66a8c016e8" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "99f92203449e800fe0535b66a8c029cd" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "99f9220344a0800fe0535b66a8c029cd" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "99f9220344a1800fe0535b66a8c029cd" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "9aee50bc89125960e0535b66a8c0e34e" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "a365d71938bb20f7e0535b66a8c08094" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "a37448aa67e01097e0535b66a8c0686d" has 2 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Instance "orcl11", status READY, has 2 handler(s) for this service...
Service "ecology" has 2 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Instance "orcl11", status READY, has 2 handler(s) for this service...
Service "ecology_srv" has 2 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Instance "orcl11", status READY, has 2 handler(s) for this service...
Service "ecologytest20200321" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "portal" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "portaldev" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "portalmessage" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "portalmobile" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
Service "portaltest" has 1 instance(s).
Instance "orcl11", status READY, has 1 handler(s) for this service...
The command completed successfully
配置tns:
ecol =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racscan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ecology_srv)
)
)
检查服务
$crsctl status res -t
ora.orcl.ecology_srv.svc
1 ONLINE ONLINE rac1 STABLE
2 ONLINE ONLINE rac2 STABLE
3 ONLINE ONLINE rac3 STABLE
登录测试
停掉rac2对应的pdb,连接中断后能自动恢复：
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
INSTANCE_NAME HOST_NAME
--------------------------------------------- -------------------------
orcl13 rac3.ccic.com
INSTANCE_NAME HOST_NAME
--------------------------------------------- -------------------------
orcl13 rac3.ccic.com
----
$ srvctl add service -help -verbose
将服务配置添加到 Oracle Clusterware。
用法: srvctl add service -db <db_unique_name> -service <service_name>
{-preferred "<preferred_list>" [-available "<available_list>"] [-tafpolicy {BASIC | NONE | PRECONNECT}] | -serverpool <pool_name> [-cardinality {UNIFORM | SINGLETON}] }
[-netnum <network_number>] [-role "[PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]"] [-policy {AUTOMATIC | MANUAL}]
[-notification {TRUE | FALSE}] [-dtp {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}]
[-failovertype {NONE | SESSION | SELECT | TRANSACTION}] [-failovermethod {NONE | BASIC}] [-failoverretry <failover_retries>] [-failoverdelay <failover_delay>] [-failover_restore {NONE | LEVEL1}]
[-edition <edition>] [-pdb <pluggable_database>] [-global {TRUE | FALSE}] [-maxlag <max_lag_time>] [-sql_translation_profile <sql_translation_profile>]
[-commit_outcome {TRUE | FALSE}] [-retention <retention>] [-replay_init_time <replay_initiation_time>] [-session_state {STATIC | DYNAMIC}]
[-pqservice <pq_service>] [-pqpool "<pq_pool_list>"] [-gsmflags <gsm_flags>] [-drain_timeout <timeout>] [-stopoption <stop_option>] [-css_critical {YES | NO}] [-rfpool <pool_name>]
[-force] [-eval] [-verbose]
-db <db_unique_name> 数据库的唯一名称
-service <service> 服务名
-preferred "<preferred_list>" 逗号分隔的首选实例列表
-available "<available_list>" 逗号分隔的可用实例列表
-serverpool <pool_name> 服务器池名称
-cardinality (UNIFORM | SINGLETON) 服务在托管此服务的服务器池中的每一个活动服务器上运行 (UNIFORM) 或仅在一个服务器上运行 (SINGLETON)
-netnum <network_number> 网络编号 (默认编号为 1)
-tafpolicy (NONE | BASIC | PRECONNECT) TAF 策略规范
-role <role> 服务的角色 (primary, physical_standby, logical_standby, snapshot_standby)
-policy <policy> 服务的管理策略 (AUTOMATIC 或 MANUAL)
-failovertype (NONE | SESSION | SELECT | TRANSACTION) 故障转移类型
-failovermethod (NONE | BASIC) 故障转移方法
-failoverdelay <failover_delay> 故障转移延迟 (秒)
-failoverretry <failover_retries> 重试连接的尝试次数
-failover_restore <failover_restore> Option to restore initial environment for Application Continuity and TAF (NONE or LEVEL1)
-edition <edition> 版本 ("" 表示空版本值)
-pdb <pluggable_database> 插接式数据库名
-maxlag <maximum replication lag> 最长复制滞后时间, 以秒为单位 (非负整数, 默认值为 'ANY')
-clbgoal (SHORT | LONG) 连接负载平衡目标。默认值为 LONG。
-rlbgoal (SERVICE_TIME | THROUGHPUT | NONE) 运行时负载平衡目标
-dtp (TRUE | FALSE) 分布式事务处理
-notification (TRUE | FALSE) 启用用于 OCI 连接的快速应用程序通知 (FAN)
-global <global> 全局属性 (TRUE 或 FALSE)
-preferred <new_pref_inst> 新首选实例的名称
-available <new_avail_inst> 新可用实例的名称
-sql_translation_profile <sql_translation_profile> 为 SQL 转换概要文件指定数据库对象
-commit_outcome (TRUE | FALSE) 提交结果
-retention <retention> 指定保留提交结果的秒数
-replay_init_time <replay_init_time> 之后不启动重放的秒数
-session_state <session_state> 会话状态一致性 (STATIC 或 DYNAMIC)
-pqservice <pq_service> 并行查询服务名
-pqpool <pq_pool> 并行查询服务器池名
-gsmflags <gsm_flags> 设置行政区和区域故障转移值
-drain_timeout <drain_timeout> 服务消耗超时, 以秒为单位指定
-stopoption <stop_options> 用于停止服务的选项 (例如 TRANSACTIONAL 或 IMMEDIATE)
-css_critical {YES | NO} 定义数据库或服务是否为 CSS 关键型
-rfpool <pool_name> 读进程场服务器池名称
-eval 评估事件的效果, 不对系统进行任何更改
用法: srvctl add service -db <db_unique_name> -service <service_name> -update {-preferred "<new_pref_inst>" | -available "<new_avail_inst>"} [-force] [-verbose]
-db <db_unique_name> 数据库的唯一名称
-service <service> 服务名
-update 为服务配置添加一个新实例
-preferred <new_pref_inst> 新首选实例的名称
-available <new_avail_inst> 新可用实例的名称
-force 强制执行添加操作, 即使没有为网络配置监听程序
-verbose 详细输出
-help 输出用法