Разблокировка и доступ к пользователю HR в Oracle Database 18c Express Edition
В посте рассматривается способ разблокировки и доступа к учебному и тестовому пользователю (схемы) HR в базе данных Oracle Database 18c Express Edition. Рассмотрены следующие вопросы:
Краткий обзор Multitenant архитектуры На сегодняшний день последней актуальной версией бесплатной редакции Oracle Database является Oracle Database 18c Express Edition. Данная версия выпущена в 2018 году. Предыдущая версия бесплатной редакции была Oracle Database 11g Express Edition. В Oracle Database 18c Express Edition включены многие важные опции наиболее функциональной редакции Oracle Database – Oracle Database Enterprise Edition. Ниже приведены некоторые основные опции, которые доступны также в Oracle Database 18c Express Edition:
Для подключения к схеме HR в Oracle Database 18c Express Edition необходимо понимать принцип работы новой опции Multitenant. Начиная с Oracle Database 12с поддерживается новая архитектура – Multitenant, которая предоставляет возможность использовать множество баз данных для консолидации их в составе единой и главной базы данных. Такая консолидация упрощает задачи администрирования баз данных. Единая и главная база данных используется в качестве платформы и называется контейнерная база данных (Container Database – CDB), а база данных из множества работающих в составе контейнерной базы данных называется подключаемой базой данных (Pluggable Database – PDB). Архитектура Multitenat позволяет создать в Oracle Database 18с Express Edition одну CDB базу и до трех PDB баз. Архитектура Oracle Database 11g Express Edition предоставляет возможность создать одну и единственную базу. В Oracle Database 18с Express Edition учебная и тестовая схема (пользователь) HR, которая содержит взаимосвязанные таблицы и данные, располагается в составе PDB. В связи с этим, чтобы подключиться к базе данных под этой учетной записью, необходимо войти в PDB, разблокировать пользователя HR и назначить ему пароль. Ниже пошагово описываются шаги подключения к CDB, PDB и манипуляция настроек пользователя с помощью SQLPlus и SQLDeveloper.
Разблокировка пользователя (схемы) HR
Предполагается, что есть успешно установленная Oracle Database 18c Express Edition. При необходимости, можно установить Oracle Database 18c Express Edition используя следующие материалы: установка Oracle Database 18c Express Edition на Linux и установка Oracle Database 18c Express Edition на Windows. Нижеописанные шаги будут работать с Oracle Database 18c Express Edition, установленной, как на операционную систему Linux, так и на Windows.
Вариант разблокировки с помощьюSQL*Plus.
Шаг 1. Подключение к CDB
Выполняется подключение к CDB с помощью пользователя sys с ролью as sysdba:
Подключение успешно прошло к CDB. Далее проверяется имя и идентификатор CDB.
Результат запроса показывает, что CDB имеет имя XE и ее уникальный идентификатор = 0. По умолчанию, после установки Oracle Database 18c Express Edition есть одна PDB с именем XEPDB1. Следующий запрос покажет существующие PDB.
Активная PDB имеет имя XEPDB1 с идентификатором 3 и ее режим работы определен как READ WRITE. OPEN MODE – READ WRITE означает, что база данных (БД) открыта и готова работать в режиме чтения и записи. PDB$SEED используется CDB как шаблон для создания новых PDB баз.
Проверяется наличие пользователя HR в CDB.
Запрос не вернул данные. Это означает, что пользователя HR нет в CDB. Далее необходимо подключиться к PDB и найти там HR.
Шаг 2. Подключение кPDB
Есть два способа подключиться к PDB с использованием SQL*Plus.
Способ 1. Находясь в CDB, подключиться к PDB используя команду alter session. В примере ниже происходит переключение из сеанса CDB к PDB с именем XEPDB1:
Переключение прошло успешно. Для того, чтобы удостовериться в корректности подключения, проверяется имя и идентификатор PDB базы:
Запросы показывают характеристики существующей PDB (Шаг 1.).
Способ 2. Можно подключиться к PDB с консоли операционной системы, указав параметры подключения.
Ниже выполняется подключение к PDB под пользователем sys с указанием IP адреса сервера БД, порта и имени PDB (по умолчанию для созданной PDB (XEPDB1) используется порт 1539):
Подключение прошло успешно.
Для информации: Администраторы баз данных временами выполняют подключение к БД используя аутентификацию на уровне операционной системы с помощью команды sqlplus / as sysdba и без указания пароля. При запуске этой команды в среде с Multitenant архитектурой будет осуществлено подключение к CDB. Для того, чтобы напрямую подключиться к PDB минуя CDB, используется sqlplus / as sysdba и без указания пароля, также необходимо в переменную среду операционной системы добавить новый системный параметр ORACLE_PDB_SID и в его значении указать название PDB. Этот параметр для подключения к PDB без указания пароля могут осуществлять только пользователи sys и system. Остальные пользователи будут автоматически подключены к CDB, если не укажут параметры подключения к PDB. Ниже описываются шаги подключения к PDB для пользователя sys с применением параметра ORACLE_PDB_SID в переменной среде операционной системы. Это очень удобный способ для администраторов баз данных:
Подключение к PDB прошло успешно напрямую из операционной системы без указания пароля и параметров подключения PDB. Далее проверяется имя и идентификатор PDB.
После успешного подключения к PDB c использованием одного из двух способов определяется наличие пользователя HR, а также его статус.
Запускается запрос поиска пользователя HR среди всех существующих пользователей в XEPDB1:
Получен результат, подтверждающий наличие пользователя HR в PDB.
При помощи запроса определяется имя, статус и дата блокировки пользователя HR:
Результат запроса показывает, что статус пользователя «заблокирован» и пароль просрочен (необходимо задать новый пароль) – EXPIRED & LOCKED. Первоначальная дата блокировки равна дате установки Oracle Database 18c Express Edition.
Шаг 3. Разблокировка пользователя HR
После установки Oracle Database 18c Express Edition учетная запись HR заблокирована и пароль у нее просрочен (необходимо задать новый пароль) (см. предыдущий шаг – Шаг 2.). В этом случае, система позволяет сделать запросы к объектам HR (таблицам, представлениям, функциям и т.п.) от имени других пользователей при наличии соответствующих привилегий. Например, при выполнении запроса на определение количества строк в таблице EMPLOYEES пользователя HR под пользователем SYS система успешно выдаст следующий результат:
Для пользователя HR назначается новый пароль:
При попытке подключения к PDB, не разблокировав пользователя, можно получить следующую ошибку:
Необходимо заново подключиться к PDB под пользователем sys:
и разблокировать пользователя HR следующей командой:
Операции назначения пароля и разблокировки пользователя HR прошли успешно. Проверяется статус пользователя:
Пользователь HR разблокирован и новый пароль активен. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.
Шаг 4. Подключение к PDB с учетной записью HR.
Используя данные для подключения к PDB, выполняется вход систему под учетной записью HR и запускается запрос для определения количества строк в его таблице EMPLOYEES.
На этом завершается определение наличия пользователя, назначение ему пароля и разблокировка HR в PDB Oracle Database 18c Express Edition, а также выполнение запроса к его объекту с помощью SQL*Plus.
Вариант разблокировки с помощью SQL Developer.
Шаг 1. Подключение кCDB
Для этого создается новое подключение в SQL Developer и указываются необходимые параметры подключения к CDB, такие как:
Name: XE_18c Указывается имя соединения, которое позволяет однозначно идентифицировать CDB при подключении.
IP: 192.168.0.1 IP адрес сервера БД.
Port: 1539 Порт подключения к БД.
SID: XE SID или имя CDB.
Username: sys Указывается имя пользователя для подключения к БД.
Role: SYSDBA Подключение к БД осуществляется пользователем sys. Данный пользователь может подключиться только с ролью SYSDBA.
Password: Пароль пользователя sys, который был назначен во время установки базы данных.
После нажатия Connect произойдет успешное подключение к CDB с именем XE. Далее проверяется имя, идентификатор и версия CDB, а также выводятся существующие PDB.
Как и ожидалось, выведенные выше данные идентичны полученным с помощью SQL*Plus.
Далее проверяется наличие пользователя HR в CDB.
Запрос не вернул данные, это означает, что пользователя HR нет в CDB. Теперь необходимо подключиться к PDB и проверить наличие HR в PDB.
Шаг 2. Подключение кPDB
Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1, такие как:
Name: XEPDB1_18c Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении.
IP: 192.168.0.1 IP адрес сервера БД.
Port: 1539 Порт подключения к БД.
SID: XEPDB1 SID или имя PDB.
Username: sys Указывается имя пользователя для подключения к БД.
Role: SYSDBA Подключение к БД осуществляется пользователем sys. Данный пользователь может подключиться только с ролью SYSDBA.
Password: Пароль пользователя sys, который был назначен во время установки базы данных. Пользователи sys и system могут подключиться с одним и тем же паролем и к CDB и к PDB.
После нажатия Connect произойдет успешное подключение к подключаемой БД XEPDB1. Далее проверяется имя и идентификатор.
Результаты показывают, что было подключение к PDB с именем XEPDB1 и идентификатором 3. Определяется наличие пользователя HR в этой PDB. В иерархии дерева надо выбрать «Other Users» в соединении с именем XEPDB1_18c как показано на скриншоте:
В списке пользователей необходимо найти пользователя HR и нажать на правую кнопку. Из контекстного меню выбрать «Edit User». Откроется новое модальное окно «Edit User» как показано на скриншоте. Как видно на скриншоте учетная запись HR заблокирована (Account is Locked) и пароль у нее просрочен (Password Expired):
Шаг 3. Разблокировка пользователяHR:
В продолжение предыдущего шага необходимо:
Пользователь HR разблокирован и ему назначен пароль. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.
Шаг 4. Подключение кPDBс учетной записьюHR.
Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1 с пользователем HR, такие как:
Name: XEPDB1_18c_hr Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении с пользователем HR.
IP: 192.168.0.1 IP адрес сервера БД.
Port: 1539 Порт подключения к БД.
SID: XEPDB1 SID или имя PDB.
Username: HR Указывается имя пользователя для подключения к БД.
Role: default Подключение к БД осуществляется пользователем HR. Данный пользователь не может использовать роль SYSDBA.
Password: Пароль, который был назначен пользователю HR на третьем шаге, то есть hr.
После нажатия Connect произойдет успешное подключение к PDB с именем XEPDB1 под пользователем HR. Выполняется запрос для определения количества строк в таблице EMPLOYEES:
На этом завершается определение наличия пользователя, назначение ему пароля и разблокировка HR в PDB Oracle Database 18c Express Edition, а также выполнение запроса к его объекту с помощью SQL Developer.
Administering PDBs includes tasks such as connecting to a PDB, modifying a PDB, and managing services associated with PDBs.
Related Topics
About PDB Administration
Administering a pluggable database (PDB) involves a subset of the tasks required to administer a non-CDB.
In this subset of tasks, most are the same for a PDB and a non-CDB, but differences exist. For example, there are differences when you modify the open mode of a PDB. Also, a PDB administrator is limited to managing a single PDB and cannot manage other PDBs in the multitenant container database (CDB).
«Modifying a PDB at the Database Level» for more information about changing the open mode of the current PDB
Tasks Common to PDBs and Non-CDBs
Most administrative tasks are the same for a PDB and a non-CDB.
Table 15-1 Administrative Tasks Common to PDBs and Non-CDBs
Task
Description
Additional Information
You can create, modify, and drop tablespaces for a PDB. You can specify a default tablespace and default tablespace type for each PDB. Also, there is a default temporary tablespace for each PDB. You optionally can create additional temporary tablespaces for use by individual PDBs.
Oracle Database Administrator’s Guide for information about managing tablespaces
Managing data files and temp files
Each PDB has its own data files. You can manage data files and temp files in the same way that you would manage them for a non-CDB. You can also limit the amount of storage used by the data files for a PDB by using the STORAGE clause in a CREATE PLUGGABLE DATABASE or ALTER PLUGGABLE DATABASE statement.
Oracle Database Administrator’s Guide for information about managing data files and temp files
Managing schema objects
You can create, modify, and drop schema objects in a PDB in the same way that you would in a non-CDB. You can also create triggers that fire for a specific PDB.
When you manage database links in a CDB, the root has a unique global database name, and so does each PDB. The global name of the root is defined by the DB_NAME and DB_DOMAIN initialization parameters. The global database name of a PDB is defined by the PDB name and the DB_DOMAIN initialization parameter. The global database name of each PDB must be unique within the domain.
Oracle Database Administrator’s Guide for more information about schema objects
Oracle Database PL/SQL Language Reference for information about creating triggers in a CDB
Tasks Specific to CDBs
Some administrative tasks cannot be performed when the current container is a PDB.
The following tasks are performed by a common user for the entire CDB or for the CDB root when the current container is the root:
Starting up and shutting down a CDB instance
Modifying the CDB or the root with an ALTER DATABASE statement
Modifying the CDB or the root with an ALTER SYSTEM statement
Executing data definition language (DDL) statements on a CDB or the root
Managing the following components:
The online redo log and the archived redo log files
Creating, plugging in, unplugging, and dropping PDBs
A common user whose current container is the root can also change the open mode of one or more PDBs. Similarly, a common user or local user whose current container is a PDB can change the open mode of the current PDB.
«Administering a CDB» for more information about this task and other tasks related to administering a CDB or the root
Managing Connections to a PDB
You manage connections for a PDB in the same way as for a non-CDB, with some special considerations.
Connecting to a PDB
You can use several techniques to connect to a PDB with the SQL*Plus CONNECT command.
This section assumes that you understand how to connect to a non-CDB in SQL*Plus.
You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT command:
Local connection with operating system authentication
Database connection using easy connect
Database connection using a net service name
The following prerequisites must be met:
The user connecting to the PDB must be granted the CREATE SESSION privilege in the PDB.
This section assumes that the user connecting to the PDB using a local user account. You can also connect to the PDB as a common user, and you can connect to the root as a common user and switch to the PDB.
To connect to a PDB using the SQL*Plus CONNECT command:
Configure your environment so that you can open SQL*Plus.
Start SQL*Plus with the /NOLOG argument:
Issue a CONNECT command using easy connect or a net service name to connect to the PDB.
To connect to a PDB, connect to a service with a PDB property.
Example 15-1 Connecting to a PDB in SQL*Plus Using the PDB’s Net Service Name
The following command connects to the hr user using the hrapp service. The hrapp service has a PDB property for the hrpdb PDB. This example assumes that the client is configured to have a net service name for the hrapp service.
«Modifying the Open Mode of PDBs» and «Modifying a PDB at the Database Level» for information about changing the open mode of a PDB.
«About Container Access in a CDB» for information about connecting to a PDB as a common user
Oracle Database Administrator’s Guide for information about connecting to the database with SQL*Plus
Managing Services for PDBs
You can create, modify, or remove services for a PDB.
About Services for PDBs
The PDB Property
The PDB property associates a service with a PDB. When a client connects to a service with a PDB property, the current container for the connection is the PDB.
The PDB property is required only when you do either of the following:
Modify the PDB property of a service
You do not specify a PDB property when you start, stop, or remove a service. Also, you do not need to specify a PDB property when you modify a service without modifying its PDB property.
You can view the PDB property for a service by querying the ALL_SERVICES data dictionary view. Alternatively, when using the SRVCTL utility, you can use the srvctl config service command.
Default and User-Defined Services
Creating a PDB creates a new default service for the PDB automatically.
Each database service name must be unique in a CDB, and each database service name must be unique within the scope of all the CDBs whose instances are reached through a specific listener. The default service has the same name as the PDB. You cannot manage this service, which you should only use for administrative tasks.
Always use user-defined services for applications. The reason is that you can customize user-defined services to fit the requirements of your applications. Oracle recommends that you not use the default PDB service for applications.
Do not associate a service with a proxy PDB.
In an Oracle Clusterware environment, you must create an Oracle Clusterware resource for each service that is created for the PDB. When your database is being managed by Oracle Restart or Oracle Clusterware, and when you use the SRVCTL utility to start a service with a PDB property for a PDB that is closed, the PDB is opened in read/write mode on the nodes where the service is started. However, stopping a PDB service does not change the open mode of the PDB.
When you unplug or drop a PDB, the services of the unplugged or dropped PDB are not removed automatically. You can remove these services manually.
«Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement» for information about changing the open mode of a PDB
Tools for Managing Services
Oracle recommends using the SRVCTL utility to create and modify services. Alternatively, you can use the DBMS_SERVICE package.
If your single-instance database is being managed by Oracle Restart or your Oracle RAC database is being managed by Oracle Clusterware, then use the Server Control (SRVCTL) utility to create, modify, or remove the service.
The PDB name is not validated when you create or modify a service with the SRVCTL utility. However, an attempt to start a service with invalid PDB name results in an error.
If your database is not being managed by Oracle Restart or Oracle Clusterware, then use the DBMS_SERVICE package to create or remove a database service.
DBMS_SERVICE exists at the root level and in each PDB. It is owned and executed by SYS at each level. A PDB administrator cannot stop, relocate, or test the connection for a service that is owned by another PDB.
When you create a service with the DBMS_SERVICE package, the PDB property of the service is set to the current container. Therefore, to create a service with a PDB property set to a specific PDB using the DBMS_SERVICE package, run the CREATE_SERVICE procedure when the PDB is the current container. If you create a service using the CREATE_SERVICE procedure when the current container is the root, then the service is associated with the root.
You cannot modify the PDB property of a service with the DBMS_SERVICE package. However, you can remove a service in one PDB and create a similar service in a different PDB. In this case, the new service has the PDB property of the PDB in which it was created.
You can also use other DBMS_SERVICE subprograms to manage the service, such as the START_SERVICE and STOP_SERVICE procedures. You can use DBMS_SERVICE. * _CONNECTION_TEST procedures to check the health of a database connection during planned maintenance. Use the DELETE_SERVICE procedure to remove a service.
Oracle Database Administrator’s Guide for information about configuring automatic restart of an Oracle database
Oracle Real Application Clusters Administration and Deployment Guide for information about creating services in an Oracle Real Application Clusters (Oracle RAC) environment
Managing Services for a PDB Using SRVCTL and DBMS_SERVICE
You can create, modify, or remove a service with a PDB property.
To manage a service with a PDB property using the SRVCTL utility:
Log in to the host computer with the correct user account.
Ensure that you run SRVCTL from the correct Oracle home.
Perform one of the following operations:
To remove a service, run the remove service command.
To create or remove a service for a PDB using the DBMS_SERVICE package:
In SQL*Plus, ensure that the current container is a PDB.
Run the appropriate subprogram in the DBMS_SERVICE package.
If your database is being managed by Oracle Restart or Oracle Clusterware, then use the SRVCTL utility to manage services. Do not use the DBMS_SERVICE package.
Example 15-2 Creating a Service for a PDB Using the SRVCTL Utility
This example adds the salesrep service for the PDB salespdb in the CDB with DB_UNIQUE_NAME mycdb :
Example 15-3 Modifying the PDB Property of a Service Using the SRVCTL Utility
This example modifies the salesrep service in the CDB with DB_UNIQUE_NAME mycdb to associate the service with the hrpdb PDB:
Example 15-4 Relocating a Service in Oracle RAC Using the SRVCTL Utility
You can use the relocate service command to relocate a service from one Oracle RAC instance, where the service is currently running, to another instance, where it can run. This technique applies both to services for administrator-managed databases as well as singleton services for policy-managed databases.
The following command performs the same operation for a policy-managed database:
Example 15-5 Removing a Service Using the SRVCTL Utility
This example removes the salesrep service in the CDB with DB_UNIQUE_NAME mycdb :
Example 15-6 Creating a Service for a PDB Using the DBMS_SERVICE Package
This example creates the salesrep service for the current PDB:
Example 15-7 Removing a Service Using the DBMS_SERVICE Package
This example removes the salesrep service in the current PDB.
Oracle Real Application Clusters Administration and Deployment Guide for information about managing services in an Oracle Real Application Clusters (Oracle RAC) environment
Modifying the Listener Settings of a Referenced PDB
A PDB that is referenced by a proxy PDB is called a referenced PDB.
When the port or host name changes for the listener of the referenced PDB, you must modify the listener settings of the referenced PDB so that its proxy PDBs continue to function properly.
Related Topics
Altering the Listener Host Name of a Referenced PDB
When the host name of the listener for a referenced PDB changes, you must run an ALTER PLUGGABLE DATABASE CONTAINERS HOST statement to reset the host name of the referenced PDB so that its proxy PDBs continue to function properly.
A proxy PDB uses a database link to establish communication with its referenced PDB during PDB creation. After communication is established, the proxy PDB communicates directly with the referenced PDB without using the database link used during PDB creation, and the database link can be dropped. When the listener host name changes for the referenced PDB, each proxy PDB must reestablish communication with its referenced PDB.
Beginning with Oracle Database 19c, version 19.10, you can execute the ALTER PLUGGABLE DATABASE CONTAINERS HOST command in the CDB root, an application root, or a PDB by including the PDB name.
Example 15-8 Altering the Listener Host Name of a Referenced PDB
Example 15-9 Resetting the Listener Host Name to the Default Value
This example resets the host name for the referenced PDB to its default value. The default value is the host name of the referenced PDB.
Example 15-10 Using the PDB Name When Altering the Listener Host Name
Altering the Listener Port Number of a Referenced PDB
When the port number of the listener for a referenced PDB changes, you must run an ALTER PLUGGABLE DATABASE CONTAINERS PORT statement to reset the port number of the referenced PDB so that its proxy PDBs continue to function properly.
A proxy PDB uses a database link to establish communication with its referenced PDB during PDB creation. After communication is established, the proxy PDB communicates directly with the referenced PDB without using the database link used during PDB creation, and the database link can be dropped. When the listener port number changes for the referenced PDB, each proxy PDB must re-establish communication with its referenced PDB.
Beginning with Oracle Database 19c, version 19.10, you can execute the ALTER PLUGGABLE DATABASE CONTAINERS PORT command in the CDB root, an application root, or a PDB by including the PDB name.
Example 15-11 Altering the Listener Port Number of a Referenced PDB
Example 15-12 Resetting the Listener Port Number to the Default Value
This example resets the port number for the referenced PDB to its default value. The default value for the port number is 1521.
Example 15-13 Using the PDB Name When Altering the Listener Port Number
Related Topics
Modifying a PDB at the System Level
You can use the ALTER SYSTEM statement to modify a PDB.
About System-Level Modifications of a PDB
The ALTER SYSTEM statement can dynamically alter a PDB. You can issue an ALTER SYSTEM statement when you want to change the way a PDB operates.
When the current container is a PDB, you can run the following ALTER SYSTEM statements:
ALTER SYSTEM FLUSH
ALTER SYSTEM < ENABLE | DISABLE >RESTRICTED SESSION
ALTER SYSTEM SET USE_STORED_OUTLINES
ALTER SYSTEM CHECKPOINT
ALTER SYSTEM CHECK DATAFILES
ALTER SYSTEM REGISTER
ALTER SYSTEM SET initialization_parameter (for a subset of initialization parameters)
All other ALTER SYSTEM statements affect the entire CDB and must be run by a common user in the root.
The ALTER SYSTEM SET initialization_parameter statement can modify only some initialization parameters for PDBs. All initialization parameters can be set for the root. For any initialization parameter that is not set explicitly for a PDB, the PDB inherits the parameter value from the root.
You can modify an initialization parameter for a PDB when the ISPDB_MODIFIABLE column is TRUE for the parameter in the V$SYSTEM_PARAMETER view. The following query lists all initialization parameters that are modifiable for a PDB:
When the current container is a PDB, run the ALTER SYSTEM SET initialization_parameter statement to modify the PDB. The statement does not affect the root or other PDBs. The following table describes the behavior of the SCOPE clause when you use a server parameter file (SPFILE) and run the ALTER SYSTEM SET statement on a PDB.
The initialization parameter setting is changed in memory and takes effect immediately in the PDB. The new setting affects only the PDB.
The setting reverts to the value set in the root in the any of the following cases:
The PDB is closed and re-opened.
The CDB is shut down and re-opened.
The initialization parameter setting is changed for the PDB and stored persistently. The new setting takes effect in any of the following cases:
The PDB is closed and re-opened.
The CDB is shut down and re-opened.
In these cases, the new setting affects only the PDB.
The initialization parameter setting is changed in memory, and it is changed for the PDB and stored persistently. The new setting takes effect immediately in the PDB and persists after the PDB is closed and re-opened or the CDB is shut down and re-opened. The new setting affects only the PDB.
When a PDB is unplugged from a CDB, the values of the initialization parameters that were specified for the PDB with SCOPE=BOTH or SCOPE=SPFILE are added to the PDB’s XML metadata file. These values are restored for the PDB when it is plugged in to a CDB.
A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.
Modifying a PDB with ALTER SYSTEM
To modify a PDB at the system level, use the ALTER SYSTEM statement (just as for a non-CDB).
The current user must be granted the following privileges, which must be either commonly granted or locally granted in the PDB:
To use ALTER SYSTEM to modify a PDB:
In SQL*Plus, ensure that the current container is a PDB.
Run the ALTER SYSTEM statement.
Example 15-14 Enable Restricted Sessions in a PDB
To restrict sessions in a PDB, issue the following statement:
Example 15-15 Changing the Statistics Gathering Level for the PDB
This ALTER SYSTEM statement sets the STATISTICS_LEVEL initialization parameter to ALL for the current PDB:
Modifying a PDB at the Database Level
You can modify a PDB using the ALTER PLUGGABLE DATABASE statement.
About Database-Level Modifications of a PDB
The ALTER PLUGGABLE DATABASE for a PDB is analogous to the ALTER DATABASE for a non-CDB.
Storage Clauses
Use ALTER PLUGGABLE DATABASE to configure storage at the PDB level.
The following clauses of ALTER PLUGGABLE DATABASE modify PDB storage:
These clauses work the same as they would in an ALTER DATABASE statement, but the statement applies to the current PDB.
DEFAULT TABLESPACE clause
For users created while the current container is a PDB, this clause specifies the default tablespace for the user if the default tablespace is not specified in the CREATE USER statement.
DEFAULT TEMPORARY TABLESPACE clause
For users created while the current container is a PDB, this clause specifies the default temporary tablespace for the user if the default temporary tablespace is not specified in the CREATE USER statement.
SET DEFAULT < BIGFILE | SMALLFILE >TABLESPACE clause
This clause changes the default type of subsequently created tablespaces in the PDB to either bigfile or smallfile. This clause works the same as it would in an ALTER DATABASE statement, but it applies to the current PDB.
This clause sets a limit on the amount of storage used by all tablespaces that belong to a PDB. This limit applies to the total size of all data files and temp files comprising tablespaces that belong to the PDB.
This clause can also set a limit on the amount of storage that can be used by unified audit OS spillover (.bin format) files in the PDB. If the limit is reached, then no additional storage is available for these files.
This clause can also set a limit on the amount of storage in a shared temporary tablespace that can be used by sessions connected to the PDB. If the limit is reached, then no additional storage in the shared temporary tablespace is available to sessions connected to the PDB.
Logging and Recovery Clauses
Use ALTER PLUGGABLE DATABASE to set logging and recovery and recovery modes at the PDB level.
This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).
This clause specifies the logging attribute of the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file ( LOGGING ) or not ( NOLOGGING ).
You can use this clause to specify one of the following attributes:
LOGGING indicates that any future tablespaces created within the PDB will be created with the LOGGING attribute by default. You can override this default logging attribute by specifying NOLOGGING at the schema object level, in a CREATE TABLE statement for example.
NOLOGGING indicates that any future tablespaces created within the PDB will be created with the NOLOGGING attribute by default. You can override this default logging attribute by specifying LOGGING at the schema object level, in a CREATE TABLE statement for example.
The specified attribute is used to establish the logging attribute of tablespaces created within the PDB if the logging_clause is not specified in the CREATE TABLESPACE statement.
The DBA_PDBS view shows the current logging attribute for a PDB.
The PDB must be open in restricted mode to use this clause.
This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).
This clause places a PDB into force logging or force nologging mode or takes a PDB out of force logging or force nologging mode.
You can use this clause to specify one of the following attributes:
ENABLE FORCE LOGGING places the PDB in force logging mode, which causes all changes in the PDB, except changes in temporary tablespaces and temporary segments, to be logged. Force logging mode cannot be overridden at the schema object level.
PDB-level force logging mode takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces in the PDB and any NOLOGGING settings you specify for individual database objects in the PDB.
ENABLE FORCE LOGGING cannot be specified if a PDB is in force nologging mode. DISABLE FORCE NOLOGGING must be specified first.
DISABLE FORCE LOGGING takes a PDB which is currently in force logging mode out of that mode. If the PDB is not in force logging mode currently, then specifying DISABLE FORCE LOGGING results in an error.
ENABLE FORCE NOLOGGING places the PDB in force nologging mode, which causes no changes in the PDB to be logged. Force nologging mode cannot be overridden at the schema object level.
CDB-wide force logging mode supersedes PDB-level force nologging mode. PDB-level force nologging mode takes precedence over and is independent of any LOGGING or FORCE LOGGING settings you specify for individual tablespaces in the PDB and any LOGGING settings you specify for individual database objects in the PDB.
ENABLE FORCE NOLOGGING cannot be specified if a PDB is in force logging mode. DISABLE FORCE LOGGING must be specified first.
DISABLE FORCE NOLOGGING takes a PDB that is currently in force nologging mode out of that mode. If the PDB is not in force nologging mode currently, then specifying DISABLE FORCE NOLOGGING results in an error.
The DBA_PDBS view shows whether a PDB is in force logging or force nologging mode.
The PDB must be open in restricted mode to use this clause.
This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).
ALTER PLUGGABLE DATABASE DISABLE RECOVERY takes the data files that belong to the PDB offline and disables recovery of the PDB. The PDB data files are not part of any recovery session until it is enabled again. Any new data files created while recovery is disabled are created as unnamed files for the PDB.
ALTER PLUGGABLE DATABASE ENABLE RECOVERY brings the data files that belong to the PDB online and marks the PDB for active recovery. Recovery sessions include these files.
Check the recovery status of a PDB by querying the RECOVERY_STATUS column in the V$PDBS view.
Oracle Database Administrator’s Guide for information about controlling the writing of redo records
Oracle Database SQL Language Reference for more information about the logging attribute
Miscellaneous Clauses
You can use ALTER PLUGGABLE DATABASE to modify the open mode, global name, time zone, and default edition.
When the current container is a PDB, an ALTER PLUGGABLE DATABASE statement with any of the following clauses modifies the PDB:
This clause changes the open mode of the current PDB.
If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB.
Specifying FORCE in this clause changes semantics of the ALTER PLUGGABLE DATABASE statement so that, in addition to opening a PDB that is currently closed, it can be used to change the open mode of a PDB that is already open.
RENAME GLOBAL_NAME clause
This clause changes the unique global database name for the PDB. The new global database name must be different from that of any container in the CDB. When you change the global database name of a PDB, the PDB name is changed to the name before the first period in the global database name.
You must change the PDB property of database services used to connect to the PDB when you change the global database name.
This clause works the same as it would in an ALTER DATABASE statement, but it applies to the current PDB.
DEFAULT EDITION clause
This clause works the same as it would in an ALTER DATABASE statement, but it applies to the current PDB. Each PDB can use edition-based redefinition, and editions in one PDB do not affect editions in other PDBs. In a multitenant environment in which each PDB has its own application, you can use edition-based redefinition independently for each distinct application.
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
To modify the attributes of a single PDB, use the ALTER PLUGGABLE DATABASE statement.
When the current container is a PDB, an ALTER PLUGGABLE DATABASE statement modifies the PDB. The modifications overwrite the defaults set for the root in the PDB. The modifications do not affect the CDB root or other PDBs.
The following prerequisites must be met:
For all other operations performed using the ALTER PLUGGABLE DATABASE statement, the current user must have the ALTER DATABASE system privilege, and the privilege must be either commonly granted or locally granted in the PDB.
To close a PDB, the PDB must be open.
This section does not cover changing the global database name of a PDB using the ALTER PLUGGABLE DATABASE statement.
In SQL*Plus, ensure that the current container is a PDB.
Run an ALTER PLUGGABLE DATABASE statement.
Example 15-16 Changing the Open Mode of a PDB
This ALTER PLUGGABLE DATABASE statement changes the open mode of the current PDB to mounted.
The following statement changes the open mode of the current PDB to open read-only.
A PDB must be in mounted mode to change its open mode to read-only or read/write unless you specify the FORCE keyword.
The following statement changes the open mode of the current PDB from mounted or open read-only to open read/write.
The following statement changes the open mode of the current PDB from mounted to migrate.
Example 15-17 Bringing a Data File Online for a PDB
This ALTER PLUGGABLE DATABASE statement uses a database_file_clause to bring the /u03/oracle/pdb1_01.dbf data file online.
Example 15-18 Changing the Default Tablespaces for a PDB
This ALTER PLUGGABLE DATABASE statement uses a DEFAULT TABLESPACE clause to set the default permanent tablespace to pdb1_tbs for the PDB.
This ALTER PLUGGABLE DATABASE statement uses a DEFAULT TEMPORARY TABLESPACE clause to set the default temporary tablespace to pdb1_temp for the PDB.
The tablespace or tablespace group specified in the ALTER PLUGGABLE DATABASE statement must exist in the PDB. Users whose current container is a PDB that are not explicitly assigned a default tablespace or default temporary tablespace use the default tablespace or default temporary tablespace for the PDB.
Example 15-19 Changing the Default Tablespace Type for a PDB
This ALTER DATABASE statement uses a SET DEFAULT TABLESPACE clause to change the default tablespace type to bigfile for the PDB.
Example 15-20 Setting Storage Limits for a PDB
This statement sets the storage limit for all tablespaces that belong to a PDB to two gigabytes.
This statement specifies that there is no storage limit for the tablespaces that belong to the PDB.
This statement specifies that there is no storage limit for the tablespaces that belong to the PDB and that there is no storage limit for the shared temporary tablespace that can be used by sessions connected to the PDB.
Example 15-21 Setting the Logging Attribute of a PDB
With the PDB open in restricted mode, this statement specifies the NOLOGGING attribute for the PDB:
Example 15-22 Setting the Force Logging Mode of a PDB
This statement enables force logging mode for the PDB:
Example 15-23 Setting the Default Edition for a PDB
«About Database-Level Modifications of a PDB» for information about the clauses that modify the attributes of a single PDB
Oracle Database SQL Language Reference for more information about the ALTER PLUGGABLE DATABASE statement
Oracle Database Development Guide for a complete discussion of edition-based redefinition
Changing the Global Database Name of a PDB
You can change the global database name of a PDB with the ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO statement.
When you change the global database name of a PDB, the new global database name must be different from that of any container in the CDB.
The following prerequisites must be met:
The current user must have the ALTER DATABASE system privilege, and the privilege must be either commonly granted or locally granted in the PDB.
For an Oracle Real Application Clusters (Oracle RAC) database, the PDB must be open on the current instance only. The PDB must be closed on all other instances.
The PDB being modified must be opened on the current instance in read/write mode with RESTRICTED specified so that it is accessible only to users with RESTRICTED SESSION privilege in the PDB.
To change the global database name of a PDB:
In SQL*Plus, ensure that the current container is a PDB.
Run an ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO statement.
The following example changes the global database name of the PDB to salespdb.example.com :
Open the PDB in read/write mode.
When you change the global database name of a PDB, the PDB name is changed to the first part of the new global name, which is the part before the first period. Also, Oracle Database changes the name of the default database service for the PDB automatically. Oracle Database also changes the PDB property of all database services in the PDB to the new global name of the PDB. You must close the PDB and open it in read/write mode for Oracle Database to complete the integration of the new PDB service name into the CDB.
Oracle Net Services must be configured properly for clients to access database services. You might need to alter your Oracle Net Services configuration because of the PDB name change.
«Managing Services for PDBs» for information about PDBs and database services
Managing Refreshable Clone PDBs
A refreshable clone PDB is a read-only clone that can periodically synchronize with its source PDB.
Refreshing a PDB
You can refresh a PDB that was created as a refreshable clone.
When you refresh a PDB manually, changes made to the source PDB since the last refresh are propagated to the PDB being refreshed. You can manually refresh a PDB that is configured for automatic refresh.
To refresh a PDB, the PDB must have been created as a clone with the REFRESH MODE MANUAL or REFRESH MODE EVERY minutes clause included.
Related Topics
Switching Over a Refreshable Clone PDB
You can switch the roles of a source PDB and its refreshable clone PDB.
The following statement performs a switchover:
After the switchover completes, the source PDB becomes the refreshable clone PDB, which can only be opened in READ ONLY mode.
You must meet the following prerequisites:
If the source PDB and clone PDB are in separate CDBs, then the user specified in the database link must have the same name and password in the source PDB and clone PDB.
To switch the roles of the source and clone PDBs:
In SQL*Plus or SQL Developer, log in to the source PDB.
Execute the ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER statement.
After the statement completes, the currently connected PDB is now the refreshable clone PDB.
Optionally, refresh the clone PDB:
Example 15-24 Switching Over a Refreshable Clone PDB
In SQL*Plus, connect to cdb1 as a user with administrator privileges, and then ensure sure that cdb1_pdb1 is open in read/write mode (sample output included):
Create a common user named c##u1 (replace pwd with a user-specified password):
Connect to cdb2 as a user with administrator privileges, and then create the common user named c##u1 (replace pwd with a user-specified password):
Now cdb1 and cdb2 both have a common user with the same name ( c##u1 ) and password.
The following statement specifies the database link cdb1_datalink and the file destination /dsk1/df :
Query t1 to check that the refreshable clone PDB contains the correct contents (sample output included):
Connect to cdb1 as a user with administrator privileges, and then create a database link to cdb2 :
Query t1 to check that the current PDB, which is now the refreshable clone PDB, contains the correct contents (sample output included):
Connect to cdb1 as a user with administrator privileges, set the container to cdb1_pdb1 (which is the new clone), refresh it, and then query t1 :
Modifying the Open Mode of PDBs
You can modify the open mode of a PDB by using the ALTER PLUGGABLE DATABASE SQL statement or the SQL*Plus STARTUP command.
About the Open Mode of a PDB
When a PDB is mounted, you can open it in read/write, read-only, or MIGRATE mode. You can also mount a PDB without opening it.
Open Modes of a PDB
You can view the open mode of a PDB by querying the OPEN_MODE column of the V$PDBS view.
The following table describes the possible open modes.
Table 15-2 PDB Mount and Open Modes
Mode
Description
Notes
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.
This is the default open mode except when a PDB belongs to a physical standby database.
A PDB in open read-only mode allows queries but does not allow user changes.
This is the default open mode when a PDB belongs to a physical standby database.
Database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB.
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.
A PDB is in this mode after you run an ALTER DATABASE OPEN UPGRADE statement.
If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB.
When a PDB is mounted, it does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Consistent backups of the PDB are supported.
Database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB.
Automatic Compatibility Check
When a PDB is opened, Oracle Database checks the compatibility of the PDB with the CDB. Each compatibility violation is either of the following:
The database records the warning in the alert log, and then opens the PDB normally without displaying a warning message.
The database displays a message when the PDB is opened stating that the PDB was altered with errors, and records the errors in the alert log. You must correct the condition that caused each error. When there are errors, the PDB is opened, but access to the PDB is limited to users with RESTRICTED SESSION privilege so that the compatibility violations can be addressed. You can view descriptions of violations by querying PDB_PLUG_IN_VIOLATIONS view.
«Modifying the Open Mode of PDBs» for information about modifying the open mode of one or more PDBs when the current container is the root
«Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement» for information about modifying the open mode of a PDB when the current container is the PDB
«Modifying a PDB at the Database Level» for information about modifying other attributes of a PDB
Clauses for Changing the Open State of PDBs
OPEN and CLOSE Clauses
READ WRITE is the default for ALTER PLUGGABLE DATABASE OPEN unless a PDB being opened belongs to a CDB used as a physical standby database, in which case READ ONLY is the default.
When you specify PDBs to open or close, you can do the following:
List one or more PDBs.
Specify ALL to modify all PDBs.
Specify ALL EXCEPT to modify all PDBs, except for the PDBs listed.
The following table describes the clauses of the ALTER PLUGGABLE DATABASE statement that modify the mode of a PDB.
Table 15-3 ALTER PLUGGABLE DATABASE Clauses That Modify the Mode of a PDB
Clause
Description
OPEN READ WRITE [RESTRICTED] [FORCE]
Opens the PDB in read/write mode.
When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB. All sessions connected to the PDB that do not have RESTRICTED SESSION privilege on it are terminated, and their transactions are rolled back.
When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode of a PDB that is in open read-only mode.
OPEN READ ONLY[RESTRICTED] [FORCE]
Opens the PDB in read-only mode.
When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB. All sessions connected to the PDB that do not have RESTRICTED SESSION privilege on it are terminated.
When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode of a PDB that is in open read/write mode.
OPEN UPGRADE [RESTRICTED]
Opens the PDB in migrate mode.
When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB.
Places the PDB in mounted mode.
When IMMEDIATE is specified, this statement is the PDB equivalent of the SQL*Plus SHUTDOWN IMMEDIATE command.
If the CDB is in ARCHIVELOG mode, and if ABORT is specified, then the PDB is forcefully closed. The PDB data files are not checkpointed or accessed during this process. If other instances have the PDB open, then an available instance performs instance recovery automatically. During this time, access to the PDB on other instances may observe a brown-out time. If no instance has the PDB open, then the next PDB open may cause automatic media recovery. If automatic media recovery fails (for example, because of inaccessible files), then you must manually recover the PDB before opening it.
If the PDB keystore was in an open state, then ALTER PLUGGABLE DATABASE CLOSE does not close it. To close the keystore, run the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY » pdb_ks_pwd » command.
SERVICES Clause
You can use the services clause to specify the services that are started when a single PDB is opened.
The clause has the following variations:
List one or more services in the services clause in the following form:
Specify ALL in the services clause to start all PDB’s services, as in the following example:
Specify ALL EXCEPT in the services clause to start all PDB’s services, except for the services listed, in the following form:
Specify NONE in the services clause to start only the PDB’s default service and none of the other PDB’s services, as in the following example:
NONE is the default setting for the services clause. A PDB’s default service is always started, regardless of the setting for the services clause.
INSTANCES Clause
In an Oracle RAC CDB, you can use the instances clause to specify the instances on which the PDB is modified.
You can close a PDB in some instances and leave it open in others. The instances clause has the following variations:
List one or more instances in the instances clause in the following form:
Specify ALL in the instances clause to modify the PDB in all running instances, as in the following example:
Specify ALL EXCEPT in the instances clause to modify the PDB in all instances, except for the instances listed, in the following form:
The RELOCATE Clause
In an Oracle Real Application Clusters environment, use RELOCATE to instruct the database to reopen the PDB on a different Oracle RAC instance.
You can use the following options:
Specify RELOCATE TO and specify an instance name to reopen the PDB in the specified instance.
Specify RELOCATE to reopen the PDB on a different instance that is selected by Oracle Database.
If both the services clause and the instances clause are specified in the same ALTER PLUGGABLE DATABASE statement, then the specified services are started on the specified instances.
Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
You can modify the open mode of PDBs with the ALTER PLUGGABLE DATABASE statement with a pdb_change_state clause.
To change the open mode of PDBs with the ALTER PLUGGABLE DATABASE statement, you must meet the following prerequisites:
The current user must have one of the following administrative privileges, which must be either commonly granted or locally granted in the PDB:
You can modify the open mode of a PDB when the current container is the PDB.
When RESTRICTED SESSION is enabled, you must specify RESTRICTED when a PDB is opened.
In an Oracle RAC CDB, if a PDB is open in one or more Oracle RAC instances, then it can be opened in additional instances. However, the PDB must be opened in the same mode as in the instances in which it is already open. A PDB can be closed in some instances and opened on others.
To place PDBs in a target mode with the ALTER PLUGGABLE DATABASE statement, you must meet the requirements described in the following table.
Table 15-4 Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
Target Mode of PDBs
ALL Keyword Included
FORCE Keyword Included
Required Mode for the Root
Required Mode for Each PDB Being Modified
Mounted, read-only, or read/write
Mounted or read/write
Mounted, read-only, or read/write
Read-only or read/write
Mounted, read-only, or read/write
Read-only or read/write
Mounted or read-only
Read-only or read/write
Mounted, read-only, or read/write
Read-only or read/write
Read-only or read/write
Read-only or read/write
Read-only or read/write
Mounted, read-only, migrate, or read/write
Read-only or read/write
Read-only, migrate, or read/write
To modify the open mode:
In SQL*Plus, ensure that the current container is the root.
Run an ALTER PLUGGABLE DATABASE statement with a pdb_change_state clause.
Example 15-25 Changing the Open Mode of Listed PDBs
This statement changes the open mode of PDBs salespdb and hrpdb to open in read/write mode.
This statement changes the open mode of PDB salespdb to open in read-only mode. RESTRICTED specifies that the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB.
This statement changes the open mode of PDB salespdb to open in migrate mode:
Example 15-26 Changing the Open Mode of All PDBs
Run the following query to display the open mode of each PDB associated with a CDB:
Notice that hrpdb is already in read/write mode. To change the open mode of salespdb and dwpdb to open in read/write mode, use the following statement:
The hrpdb PDB is not modified because it is already in open read/write mode. The statement does not return an error because two PDBs are in mounted mode and one PDB ( hrpdb ) is in the specified mode (read/write). Similarly, the statement does not return an error if all PDBs are in mounted mode.
However, if any PDB is in read-only mode, then the statement returns an error. To avoid an error and open all PDBs in the CDB in read/write mode, specify the FORCE keyword:
With the FORCE keyword included, all PDBs are opened in read/write mode, including PDBs in read-only mode.
Example 15-27 Changing the Open Mode of All PDBs Except for Listed Ones
This statement changes the mode of all PDBs except for salespdb and hrpdb to mounted mode.
An ALTER PLUGGABLE DATABASE statement modifying the open mode of a PDB is instance-specific. Therefore, if this statement is issued when connected to an Oracle RAC instance, then it affects the open mode of the PDB only in that instance.
«Modifying a PDB at the Database Level» for information about modifying the other attributes of a PDB
Oracle Database Administrator’s Guide for information about database modes and their uses
Oracle Database Concepts for more information about shutdown modes
Preserving or Discarding the Open Mode of PDBs When the CDB Restarts
You can preserve the open mode of one or more PDBs when the CDB restarts by using the ALTER PLUGGABLE DATABASE SQL statement with a pdb_save_or_discard_state clause.
You can do this in the following way:
Specify SAVE STATE to preserve the PDBs’ mode when the CDB is restarted.
For example, if a PDB is in open read/write mode before the CDB is restarted, then the PDB is in open read/write mode after the CDB is restarted; if a PDB is in mounted mode before the CDB is restarted, then the PDB is in mounted mode after the CDB is restarted.
Specify DISCARD STATE to ignore the PDBs’ open mode when the CDB is restarted.
When DISCARD STATE is specified for a PDB, the PDB is always mounted after the CDB is restarted.
You can specify which PDBs to modify in the following ways:
List one or more PDBs.
Specify ALL to modify all PDBs.
Specify ALL EXCEPT to modify all PDBs, except for the PDBs listed.
For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB’s open mode is preserved in the following ways:
List one or more instances in the instances clause in the following form:
Specify ALL in the instances clause to modify the PDB in all running instances, as in the following example:
Specify ALL EXCEPT in the instances clause to modify the PDB in all instances, except for the instances listed, in the following form:
For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.
To issue an ALTER PLUGGABLE DATABASE SQL statement with a pdb_save_or_discard_state clause, the current user must have the ALTER DATABASE privilege in the root.
You can check the saved states for the PDBs in a CDB by querying the DBA_PDB_SAVED_STATES view.
To preserve or discard a PDB’s open mode when the CDB restarts:
In SQL*Plus, ensure that the current container is the root.
Run an ALTER PLUGGABLE DATABASE statement with a pdb_save_or_discard_state clause.
The following examples either preserve or discard the open mode of one or more PDBs when the CDB restarts.
Example 15-28 Preserving the Open Mode of a PDB When the CDB Restarts
This statement preserves the open mode of the salespdb when the CDB restarts.
Example 15-29 Discarding the Open Mode of a PDB When the CDB Restarts
This statement discards the open mode of the salespdb when the CDB restarts.
Example 15-30 Preserving the Open Mode of All PDBs When the CDB Restarts
This statement preserves the open mode of all PDBs when the CDB restarts.
Example 15-31 Preserving the Open Mode of Listed PDBs When the CDB Restarts
This statement preserves the open mode of the salespdb and hrpdb when the CDB restarts.
Example 15-32 Preserving the Open Mode of All PDBs Except for Listed Ones When the CDB Restarts
Altering the Open Mode of a PDB Using STARTUP and SHUTDOWN
When the current container is a PDB, you can use the SQL*Plus STARTUP command to open the PDB and the SQL*Plus SHUTDOWN command to close the PDB.
About Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
When the current container is the root, the STARTUP PLUGGABLE DATABASE command can open a single PDB.
Use the following options of the STARTUP PLUGGABLE DATABASE command to open a PDB:
Closes an open PDB before re-opening it in read/write mode. When this option is specified, no other options are allowed.
Enables only users with the RESTRICTED SESSION system privilege in the PDB to access the PDB.
If neither OPEN READ WRITE nor OPEN READ ONLY is specified, then the PDB is opened in read-only mode when the CDB to which it belongs is a physical standby database. Otherwise, the PDB is opened in read/write mode.
The following prerequisites must be met:
When RESTRICTED SESSION is enabled, RESTRICT must be specified when a PDB is opened.
In addition, to place PDBs in a target mode with the STARTUP PLUGGABLE DATABASE command, you must meet the requirements described in the following table.
Table 15-5 Modifying the Open Mode of a PDB with STARTUP PLUGGABLE DATABASE
Target Mode of the PDB
FORCE Option Included
Required Mode for the Root
Required Mode of the PDB Being Modified
Mounted, read-only, or read/write
Read-only or read/write
You can also use the STARTUP command to modify the open mode of a PDB when the current container is the PDB.
Starting Up a PDB Using the STARTUP Command
When the current container is a PDB, the SQL*Plus STARTUP command opens the PDB.
Use the following options of the STARTUP command to open a PDB:
Closes an open PDB before re-opening it in read/write mode. When this option is specified, no other options are allowed.
Enables only users with the RESTRICTED SESSION system privilege in the PDB to access the PDB.
If neither OPEN READ WRITE nor OPEN READ ONLY is specified and RESTRICT is specified, then the PDB is opened in read-only mode when the CDB to which it belongs is a physical standby database. Otherwise, the PDB is opened in read/write mode.
To issue the STARTUP command when the current container is a PDB, the following prerequisites must be met:
Excluding the use of the FORCE option, the PDB must be in mounted mode to open it.
To place a PDB in mounted mode, the PDB must be in open read-only or open read/write mode.
To modify a PDB with the STARTUP command:
In SQL*Plus, ensure that the current container is a PDB.
Run the STARTUP command.
Example 15-33 Opening a PDB in Read/Write Mode with the STARTUP Command
Example 15-34 Opening a PDB in Read-Only Mode with the STARTUP Command
Example 15-35 Opening a PDB in Read-Only Restricted Mode with the STARTUP Command
Example 15-36 Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option
This example assumes that the PDB is currently open. The FORCE option closes the PDB and then opens it in the read/write mode.
Oracle Database Administrator’s Guide for information about starting up a database
Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
You can use the STARTUP PLUGGABLE DATABASE command to open a single PDB.
To modify a PDB with the STARTUP PLUGGABLE DATABASE command:
In SQL*Plus, ensure that the current container is the root.
Run the STARTUP PLUGGABLE DATABASE command.
When the current container is the root, the SQL*Plus SHUTDOWN command always shuts down the CDB instance. It cannot be used to close individual PDBs.
Example 15-37 Opening a PDB in Read/Write Mode with the STARTUP Command
Example 15-38 Opening a PDB in Read/Write Restricted Mode with the STARTUP Command
Example 15-39 Opening a PDB in Read-Only Restricted Mode with the STARTUP Command
Example 15-40 Opening a PDB in Read-Only Mode with the STARTUP Command
Example 15-41 Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option
This example assumes that the hrpdb PDB is currently open. The FORCE option closes the PDB and then opens it in the read/write mode.
«Altering the Open Mode of a PDB Using STARTUP and SHUTDOWN» for information about using the STARTUP or SHUTDOWN command when the current container is a PDB
Shutting Down a PDB Using the SHUTDOWN Command
When the current container is a PDB, the SQL*Plus SHUTDOWN command closes the PDB.
After the SHUTDOWN command is issued on a PDB successfully, it is in mounted mode.
The following SHUTDOWN modes are possible:
When you specify SHUTDOWN only, then the PDB is shut down with the normal mode.
Note that if the PDB keystore was in an open state, then issuing SHUTDOWN at the PDB level does not close it. To close the keystore, run the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY » pdb_ks_pwd » command.
To issue the SHUTDOWN command when the current container is a PDB, the following prerequisites must be met:
To close a PDB, the PDB must be open.
To modify a PDB with the SHUTDOWN command:
In SQL*Plus, ensure that the current container is a PDB.
Run the SHUTDOWN command.
When the current container is a PDB, the SHUTDOWN command only closes the PDB, not the CDB instance.
There is no SHUTDOWN command for a PDB that is equivalent to SHUTDOWN TRANSACTIONAL for a CDB.
Example 15-42 Closing a PDB with the SHUTDOWN IMMEDIATE Command
Oracle Database Administrator’s Guide for more information about shutdown modes
Starting and Stopping PDBs in Oracle RAC
Administering a pluggable database (PDB) involves a small subset of the tasks required to administer a non-CDB.
Administering an Oracle RAC-based multitenant container database (CDB) is similar to administering a non-CDB. The differences are that some administrative tasks apply to the entire CDB, some to the CDB root, and some to specific PDBs. In this subset of tasks, most are the same for a PDB and a non-CDB. There are some differences, however, such as when you modify the open mode of a PDB. Also, a PDB administrator is limited to managing a single PDB and is not affected by other PDBs in the CDB.
You manage PDBs in an Oracle RAC CDB by managing services. This is true regardless of whether the PDBs are policy managed or administrator managed. Assign one dynamic database service to each PDB to coordinate start, stop, and placement of PDBs across instances in a clustered container database.
To stop the service plug :
Because PDBs are managed using dynamic database services, typical Oracle RAC-based management practices apply. For this reason, if the service plug is in the online state when Oracle Clusterware is shut down on a server hosting this service, then the service is restored to its original state after the restart of Oracle Clusterware on this server. Thus, starting PDBs is automated as with any other Oracle RAC database.
Unlike SQL*Plus, SRVCTL operates on an entire cluster database. Starting a PDB using services therefore applies to multiple instances of the clustered CDB at the same time when the service is defined to run on multiple servers simultaneously and the current status of the cluster allows for this placement.
