ORACLE 12C/19C 手工建立多租户数据库

我作为ORACLE OCP/OCM的讲师在教学的过程中总会遇到这么一类问题很多学生对ORACLE多租户数据库都是比较茫然的。那么今天我就利用多租户手工建库的方式带大家去从数据库这个层面去理解一下多租户的相关概念。第一部分:手工创建CDB美好的一天从这里开始首先修改oracle环境变量为cdb1可以使用以下命令进行修改切换到oracle用户中: 后续步骤都在oracle用户中操作。su - oracle使用这个sed命令修改oracle用户环境变量中的ORACLE_SID名称如果不擅长LINUX命令的话还是建议手动修改。这里注意加了 -i 这个参数后sed就会去修改源文件如果你要是确定不了就可以先使用下面命令测试sed s#ORACLE_SID.*#ORACLE_SIDcdb1# /home/oracle/.bash_profile当你确定无误后 再使用下面的命令去修改环境变量文件-i的主要作用就是去修改你的原文件中的内容sed -i s#ORACLE_SID.*#ORACLE_SIDcdb1# /home/oracle/.bash_profile生成环境变量source ~/.bash_profile检查一下看看是不是ORACLE_SID修改成功了cat /home/oracle/.bash_profile或者使用下面的命令看看是否成功echo $ORACLE_SID应该输出cdb1如果修改完成,并环境变量生成后我们使用sqlplus / as sysdba将得到以下结果[oracleoracle12c-cdb ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 9 01:48:44 2020Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance.SQL看到了吗空实例。说明我们要从spfile开始一步一步的手工建库了不过我们这里建立的不是单实例数据库而是CDB(容器数据库)退出sqlplus[oracleoracle12c-cdb ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 9 01:51:20 2020Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance.SQL exit;Disconnected创建密码文件还记得他吧sys/system用户都要用到没有它后面就很多东西玩不转如果忘了回头看看071orapwd file$ORACLE_HOME/dbs/orapwcdb1 passwordoracle forcey format12下面开始手工创建CDB:第零步请确定你已经建立好了一个监听。这里我已经创建好监听了。如果不会创建监听建议回顾062的第五章。或者使用以下的内容请到下面查找创建动态监听的内容。推荐大家直接使用文件关键字的方式查找关键字为“创建一个监听程序”。第一步创建pfile。使用下面的命令和参数去创建pfile。cd /home/oracle --注意这是LINUX操作系统命令不能在sqlplus下执行。mkdir -p /u01/app/oracle/admin/cdb1/adump --建立cdb1的审计目录mkdir -p /u01/app/oracle/oradata/cdb1 --建立cdb1的数据文件存放目录mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed --创建pdbseed目录vi /home/oracle/init_cdb1.ora*.audit_file_dest/u01/app/oracle/admin/cdb1/adump*.audit_traildb*.control_files/u01/app/oracle/oradata/cdb1/control01.ctl,/u01/app/oracle/oradata/cdb1/control02.ctl*.db_block_size8192*.db_namecdb1*.diagnostic_dest/u01/app/oracle*.dispatchers(PROTOCOLTCP) (SERVICEcdb1XDB)*.enable_pluggable_databasetrue*.open_cursors300*.pga_aggregate_target200m*.processes300*.remote_login_passwordfileEXCLUSIVE*.sga_target800m*.undo_tablespaceUNDOTBS1修改后保存退出。这里我们必须要注意下面的参数必须要添加进去*.enable_pluggable_databasetrue第二步使用sqlplus / as sysdba进入到oracle数据库。[oracleoracle12c-cdb ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 9 02:16:05 2020Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance.SQL第三步使用我们创建出来的pfile启动数据库到nomount状态并创建spfile然后重新把数据库启动到startup nomountSQL startup nomount pfile/home/oracle/init_cdb1.ora 或者使用 startup pfile/home/oracle/init_cdb1.ora nomountORACLE instance started.Total System Global Area 838860800 bytesFixed Size 8798312 bytesVariable Size 322965400 bytesDatabase Buffers 503316480 bytesRedo Buffers 3780608 bytesSQL create spfile from pfile/home/oracle/init_cdb1.ora;File created.SQL shutdown abortORACLE instance shut down.SQL startup nomountORACLE instance started.Total System Global Area 838860800 bytesFixed Size 8798312 bytesVariable Size 322965400 bytesDatabase Buffers 503316480 bytesRedo Buffers 3780608 bytesSQL一连串的操作对吧。。。哈哈哈哈放轻松。第四步我们需要创建一个CDB的建库脚本。脚本如下cd /home/oracle --不是重复了是我要一直提醒大家所有的相关文件都必须放到这里。vi /home/oracle/create_cdb1.sqlCREATE DATABASE cdb1USER SYS IDENTIFIED BY oracleUSER SYSTEM IDENTIFIED BY oracleLOGFILE GROUP 1 (/u01/app/oracle/oradata/cdb1/redo01a.log,/u01/app/oracle/oradata/cdb1/redo01b.log)SIZE 100M BLOCKSIZE 512,GROUP 2 (/u01/app/oracle/oradata/cdb1/redo02a.log,/u01/app/oracle/oradata/cdb1/redo02b.log)SIZE 100M BLOCKSIZE 512,GROUP 3 (/u01/app/oracle/oradata/cdb1/redo03a.log,/u01/app/oracle/oradata/cdb1/redo03b.log)SIZE 100M BLOCKSIZE 512MAXLOGHISTORY 1MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 1024CHARACTER SET AL32UTF8NATIONAL CHARACTER SET AL16UTF16EXTENT MANAGEMENT LOCALDATAFILE /u01/app/oracle/oradata/cdb1/system01.dbfSIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDSYSAUX DATAFILE /u01/app/oracle/oradata/cdb1/sysaux01.dbfSIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDDEFAULT TABLESPACE deftbsDATAFILE /u01/app/oracle/oradata/cdb1/deftbs01.dbfSIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDDEFAULT TEMPORARY TABLESPACE tempts1TEMPFILE /u01/app/oracle/oradata/cdb1/temp01.dbfSIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITEDUNDO TABLESPACE undotbs1DATAFILE /u01/app/oracle/oradata/cdb1/undotbs01.dbfSIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITEDUSER_DATA TABLESPACE usertbsDATAFILE /u01/app/oracle/oradata/cdb1/pdbseed/usertbs01.dbfSIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDENABLE PLUGGABLE DATABASESEEDFILE_NAME_CONVERT (/u01/app/oracle/oradata/cdb1/, /u01/app/oracle/oradata/cdb1/pdbseed/)SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITEDSYSAUX DATAFILES SIZE 100MLOCAL UNDO ON;在已经启动到nomount状态下的数据库sqlplus工具中执行该脚本。SQL /home/oracle/create_cdb1.sqlDatabase created.成功了下一步我们执行一个脚本。不过我们在执行下一个脚本之前我们需要运行点其他的东西不然会出现错误。export PERL5LIB$ORACLE_HOME/rdbms/admin:$PERL5LIBexport PATH$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATHcd $ORACLE_HOME/perl/lib/5.22.0/x86_64-linux-thread-multi/Hashln -s Util.pm util.pm运行完成后我们现在去运行下面的脚本?/rdbms/admin/catcdb.sql如果遇到让你输入参数的时候 输入下面的参数:Enter value for 1: /home/oracleEnter value for 2: /home/oracle/cdb.logEnter new password for SYS: oracleEnter new password for SYSTEM: oracleEnter temporary tablespace name: temp当最后输出这些时候代表脚本运行成功catcon: See [/home/oracle/catbundleapply*.log] files for output generated by scriptscatcon: See [/home/oracle/catbundleapply_*.lst] files for spool files, if anycatcon.pl: completed successfullycatcon: ALL catcon-related output will be written to [/home/oracle/utlrp_ catcon_9786.lst]catcon: See [/home/oracle/utlrp*.log] files for output generated by scrip tscatcon: See [/home/oracle/utlrp_*.lst] files for spool files, if anycatcon.pl: completed successfully使用语句去验证一下SQL select name,cdb,con_id,open_mode from v$database;NAME CDB CON_ID OPEN_MODE--------- --- ---------- --------------------CDB1 YES 0 READ WRITESQL select status from v$instance;STATUS------------OPEN好现在我们做一下实例的关闭和开启实验。[oracleoracle12c-cdb Hash]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 9 18:48:01 2020Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 838860800 bytesFixed Size 8798312 bytesVariable Size 322965400 bytesDatabase Buffers 503316480 bytesRedo Buffers 3780608 bytesDatabase mounted.Database opened.SQLset lines 300SQLcol NAME for a40SQLselect con_id,dbid,NAME,OPEN_MODE from v$pdbs;CON_ID DBID NAME OPEN_MODE---------- ---------- ---------------------------------------- ----------2 1124557470 PDB$SEED READ ONLYSQL可以看到没问题。创建一个监听程序如果你要是已经有监听了就不用创建了这里我们可以创建一个动态监听程序因为静态静态监听程序在062中已经讲过了。而且在创建监听之前最好把数据库关闭。请使用下面的创建语句创建动态监听。ORACLE12C以后的版本在使用监听的方面官方推荐使用动态监听。vi $ORACLE_HOME/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST oracle12c-cdb)(PORT 1521))))如果写IP地址的话# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST 192.168.100.166)(PORT 1521))))保存退出。这里我们需要注意的是HOST必须填你自己机器的主机名或IP.启动监听。lsnrctl start[oracleoracle12c-cdb admin]$ lsnrctl startLSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-NOV-2020 19:10:10Copyright (c) 1991, 2016, Oracle. All rights reserved.Starting /u01/app/oracle/product/12.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 12.2.0.1.0 - ProductionSystem parameter file is /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/oracle12c-cdb/listener/alert/log.xmlListening on: (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSToracle12c-cdb)(PORT1521)))Connecting to (DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOSToracle12c-cdb)(PORT1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 12.2.0.1.0 - ProductionStart Date 09-NOV-2020 19:10:10Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/oracle12c-cdb/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSToracle12c-cdb)(PORT1521)))The listener supports no servicesThe command completed successfully使用sqlplus / as sysdba 打开你的数据库startup命令。先别着急等一会。。。。在查看监听的状态因为我们这个是用动态监听自动注册实例所以可能要等一会。查看监听状态lsnrctl status[oracleoracle12c-cdb admin]$ lsnrctl statusLSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-NOV-2020 19:12:27Copyright (c) 1991, 2016, Oracle. All rights reserved.Connecting to (DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOSToracle12c-cdb)(PORT1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 12.2.0.1.0 - ProductionStart Date 09-NOV-2020 19:10:10Uptime 0 days 0 hr. 2 min. 17 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/oracle12c-cdb/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSToracle12c-cdb)(PORT1521)))Services Summary...Service cdb1 has 1 instance(s).Instance cdb1, status READY, has 1 handler(s) for this service...Service cdb1XDB has 1 instance(s).Instance cdb1, status READY, has 1 handler(s) for this service...The command completed successfully看我们的cdb1 已经成功被监听了。这时你可以使用sqldeveloper去测试一下。###############################################################################这里给大家附送一个静态监听创建代码请酌情使用。动态 静态挑一个就行。我建议使用动态监听vi $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME cdb1)(ORACLE_HOME /u01/app/oracle/product/12.2.0/db_1)(SID_NAME cdb1))(SID_LIST (SID_DESC (GLOBAL_DBNAME pdb1)(ORACLE_HOME /u01/app/oracle/product/12.2.0/db_1)(SID_NAME cdb1)))LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST oracle12c-cdb)(PORT 1521)))(DESCRIPTION (ADDRESS (PROTOCOL IPC)(KEY EXTPROC1521))))ADR_BASE_LISTENER /u01/app/oracle###################################################################################至此手工创建CDB的所有步骤完成了。第二部分:手工创建PDB1.利用PDB$SEED创建PDB在SQLPLUS中执行以下的SQL语句CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1admin IDENTIFIED BY oracleFILE_NAME_CONVERT(/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/undotbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/deftbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/deftbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/usertbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/usertbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/temp01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/temp01.dbf)STORAGE UNLIMITED TEMPFILE REUSE;如果你想对新建成的pdb1进行使用空间限制的话可以写成以下格式。CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdbadmin IDENTIFIED BY oracleFILE_NAME_CONVERT(/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/undotbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/deftbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/deftbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/usertbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/usertbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/temp01.dbf, /u01/app/oracle/oradata/cdb1/pdb1/temp01.dbf)STORAGE (MAXSIZE 2GMAX_SHARED_TEMP_SIZE 200M)TEMPFILE REUSE;查询一下set lines 300col NAME for a40select con_id,dbid,NAME,OPEN_MODE from v$pdbs;第二种 使用已有的PDB去创建新的PDB。我们需要注意几点使用的用户必须拥有CREATE PLUGGABLE DATABASE的权限(测试里使用sys用户)源PDB不可以是关闭状态或者是mount状态的如果CDB为shared undoPDB必须为READ-ONLY状态。才能进行相关复制不然丢失事务。如果CDB不是归档模式那么PDB必须为READ-ONLY状态。才能进行相关复制创建测试PDBpdbtCREATE PLUGGABLE DATABASE pdbt ADMIN USER pdbtadmin IDENTIFIED BY oracleFILE_NAME_CONVERT(/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/system01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/sysaux01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/undotbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/deftbs01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/deftbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/usertbs01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/usertbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbseed/temp01.dbf, /u01/app/oracle/oradata/cdb1/pdbt/temp01.dbf)STORAGE UNLIMITED TEMPFILE REUSE;尝试把pdbt以READ ONLY方式打开:ALTER PLUGGABLE DATABASE PDBt OPEN READ ONLY;新的PDB一定要先启动到READ WRITE模式因为新的PDB状态是NEW而在NEW的这个模式下时是不能启动到READ ONLY的第一次启动到READ WRITE后将PDB关掉下次就能启动到READ ONLY状态了。PDB状态检查语句set lines 500col pdb_name for a10col db_name for a10col db_unique_name for a10col db_version_string for a20查询PDB的详细信息select pdb_name,pdb_id,pdb_dbid,pdb_guid,OP_TIMESTAMP,operation,db_version,db_name,db_unique_name,db_dbid,db_version_string from dba_pdb_history order by OP_TIMESTAMP;查询PDB的状态select pdb_name, status from cdb_pdbs;pdb的status有NEW、NORMAL、UNPLUGGED、NEEDS UPGRADE、CONVERTING、UNUSABLE几种状态对这几种状态的解释如下:文档的最后有英文原版内容NEW-自创建以来从未打开过PDB。 必须以READ WRITE模式打开该文件Oracle才能执行将PDB集成到CDB中并将其标记为NORMAL所需的处理。 如果尝试以只读方式打开PDB将引发错误。NORMAL-准备使用准许使用的PDB。UNPLUGGED-PDB已拔出。可以对其执行的唯一操作是DROP PLUGGABLE DATABASE。NEEDS UPGRADE-PDB需要升级到已插入的CDB的版本。CONVERTING-非CDB已插入CDB并且正在进行转换以使其表现得像真实的PDB。UNUSABLE-正在创建PDB或在创建PDB时遇到不可恢复的错误。 当状态设置为“不可用”时无法打开PDB。 如果PDB在创建过程中由于遇到错误而保持此状态则只能将其删除。 可以检查告警日志以确定是否存在错误启动PDB到READ WRITE状态ALTER PLUGGABLE DATABASE PDBt OPEN READ WRITE;关闭ALTER PLUGGABLE DATABASE PDBt close;然后启动到READ ONLY状态ALTER PLUGGABLE DATABASE PDBt OPEN READ ONLY;执行PDB的克隆语句CREATE PLUGGABLE DATABASE pdb2 FROM pdbtSTORAGE UNLIMITED TEMPFILE REUSEFILE_NAME_CONVERT(/u01/app/oracle/oradata/cdb1/pdbt/system01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf,/u01/app/oracle/oradata/cdb1/pdbt/sysaux01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf,/u01/app/oracle/oradata/cdb1/pdbt/undotbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/undotbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbt/deftbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/deftbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbt/usertbs01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/usertbs01.dbf,/u01/app/oracle/oradata/cdb1/pdbt/temp01.dbf, /u01/app/oracle/oradata/cdb1/pdb2/temp01.dbf)PARALLEL 2;第三种 把已拔出的PDB再插回到CDB中首先关掉PDBalter pluggable database pdb2 close;alter pluggable database pdb2 unplug into /home/oracle/pdb2.xml;删除PDB有两种方式 第一个是保留文件第二个是不保留文件drop pluggable database pdb2 including datafiles; #删除数据库和文件drop pluggable database pdb2 keep datafiles; #删除数据库并保存文件默认状态下是保留文件的。drop pluggable database pdb2;创建PDBcreate pluggable database pdb2 using /home/oracle/pdb2.xml nocopy;alter pluggable database pdb2 open read wirte ;或alter pluggable database pdb2 open;附注pdb的status有NEW、NORMAL、UNPLUGGED、NEEDS UPGRADE、CONVERTING、UNUSABLE几种状态对这几种状态的解释如下:NEW - The PDB has never been opened since it was created. It must be opened in READ WRITE mode for Oracle to perform processing needed to complete the integration of the PDB into the CDB and mark it NORMAL. An error will be thrown if an attempt is made to open the PDB read only.NORMAL - The PDB is ready to be used.UNPLUGGED - The PDB has been unplugged. The only operation that can be performed on it is DROP PLUGGABLE DATABASE.NEEDS UPGRADE - A PDB needs to be upgraded to the version of the CDB into which it was plugged.CONVERTING - A non-CDB was plugged into the CDB and is undergoing conversion required to make it behave like a real PDB.UNUSABLE - The PDB is being created or an unrecoverable error was encountered during its creation. The PDB cannot be opened while its state is set to UNUSABLE. If the PDB remains in this state because of an error encountered during its creation, it can only be dropped. The alert log can be checked to determine if there was an errorBEGINEXECUTE IMMEDIATE ALTER PLUGGABLE DATABASE PDB3 CLOSE IMMEDIATE;EXECUTE IMMEDIATE ALTER PLUGGABLE DATABASE PDB3 UNPLUG INTO PDB3.XML;EXECUTE IMMEDIATE DROP PLUGGABLE DATABASE PDB3 KEEP DATAFILES;END;