Oracle 12/18c 如何重建PDB$SEED PDB?

by

前段时间为了尽快体验18C的功能，在安装时少修改了一个文件参数，结果安装的数据库无法创建新的PDB(使用pdb$seed)，提示ORA-65036，并且db$seed Plugable database状态也有问题，这篇简单的记录一下如何修复PDB$SEED,先看一下我的场景。

SQL> CREATE PLUGGABLE DATABASE 
  2     anbob_pdb1 
  3  ADMIN USER pdbadmin IDENTIFIED BY oracle 
  4  FILE_NAME_CONVERT=('/u01/orabase/oradata/ANBOB18C/pdbseed/','/u01/orabase/oradata/ANBOB18C/anbob_pdb1/');
CREATE PLUGGABLE DATABASE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []

Note: 查看trace文件是在查询temp file时有问题，新创建的CDB没有TEMP FILE, 解决方法很简单增加tempfile.

SQL> alter tablespace temp add tempfile '/u01/orabase/oradata/ANBOB18C/temp01.dbf' size 30m ;
Tablespace altered.

SQL> CREATE PLUGGABLE DATABASE 
  2     anbob_pdb1 
  3  ADMIN USER pdbadmin IDENTIFIED BY oracle 
  4  FILE_NAME_CONVERT=('/u01/orabase/oradata/ANBOB18C/pdbseed/','/u01/orabase/oradata/ANBOB18C/anbob_pdb1/');
CREATE PLUGGABLE DATABASE
*
ERROR at line 1:
ORA-65036: pluggable database PDB$SEED not open in required mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED

SQL> alter pluggable database pdb$seed open;
alter pluggable database pdb$seed open
*
ERROR at line 1:
ORA-65104: operation not allowed on an inactive pluggable database

SQL> ho oerr ora 65104
65104, 00000, "operation not allowed on an inactive pluggable database"
// *Cause:  The pluggable database status was INACTIVE. It was still being
//          created or there was an error during the create operation.
// *Action: Wait until the status is changed to CREATED or, in case of
//          errors, drop the pluggable database and re-create it.

Note:
当前的PDB$SEED状态有问题，正常情况下PDB$SEED应该是open read-only。 根据错误提示需要重建PDB$SEED。

Oracle 12c为Oracle数据库引入了多租户架构，其中一个容器数据库（CDB $ ROOT）可以具有多个可插拔数据库（PDB），在多租户体系结构中，理想情况下，我们使用SEED模板可插入数据库（PDB $ SEED）在容器数据库（CDB $ ROOT）内创建任何新的可插入数据库。 SEED可插入数据库（PDB $ SEED）充当创建新的可插拔数据库的模板，默认情况下不允许更改SEED可插入数据库的配置（默认情况下以READ ONLY模式打开），除非使用“_oracle_script”隐藏参数。

重建PDB$SEED的常见方法：

1. 使用建库时保存的脚本
2. 使用rman 恢复pdb$seed(前提有备份)
3. 使用同一个CDB的其它较干净的已存在的PDB创建PDB$SEED(不用备份)
4. 使用其它CDB的PDB$SEED复制PDB$SEED

使用建库时保存的脚本

SQL> drop pluggable database pdb$seed;
drop pluggable database pdb$seed
                        *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> drop pluggable database pdb$seed;
Pluggable database dropped.

vi /u01/orabase/admin/anbob18c/scripts/initanbob18cTemp.ora
-- append 
_exadata_feature_on=true

[oracle@anbob scripts]$ cd /u01/orabase/oradata/ANBOB18C/pdbseed
[oracle@anbob pdbseed]$ ls
sysaux01.dbf  system01.dbf  temp012018-03-03_18-27-17-324-PM.dbf  undotbs01.dbf

[oracle@anbob pdbseed]$ rm *
[oracle@anbob pdbseed]$ ls

SQL> @plug_PDBSeed.sql 

SQL> CREATE PLUGGABLE DATABASE 
  2     anbob_pdb1 
  3  ADMIN USER pdbadmin IDENTIFIED BY oracle 
  4  FILE_NAME_CONVERT=('/u01/orabase/oradata/ANBOB18C/pdbseed/','/u01/orabase/oradata/ANBOB18C/anbob_pdb1/');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ WRITE YES
         3 ANBOB_PDB1                     MOUNTED

使用rman 恢复pdb$seed

$ rman target /
RMAN> backup database "pdb$seed" ;
...

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> alter pluggable database "pdb$seed" close;
Pluggable database altered.

RMAN> restore datafile xxx;  -- pdb$seed datafile 

RMAN> alter session set "_oracle_script"=true;
Statement processed

RMAN> alter pluggable database "pdb$seed" open read only;
Statement processed

RMAN> alter session set "_oracle_script"=false;
Statement processed

已存在的PDB创建PDB$SEED

SQL>  alter session set "_oracle_script"=true;
Session altered.

SQL> alter pluggable database "pdb$seed" close;
Pluggable database altered.

SQL> drop pluggable database "pdb$seed" including datafiles;
Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ANBOB_PDB1                     READ WRITE NO

SQL> alter pluggable database ANBOB_PDB1 close;
Pluggable database altered.

SQL> alter pluggable database ANBOB_PDB1 open read only;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ANBOB_PDB1                     READ WRITE NO

SQL> create pluggable database "PDB$SEED" from CDB2_PDB_2
   file_name_convert=('/u01/orabase/oradata/ANBOB18C/anbob_pdb1/','/u01/orabase/oradata/ANBOB18C/pdbseed/') ;
Pluggable database created.

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL>  alter pluggable database PDB$SEED open read only;
 alter pluggable database PDB$SEED open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read-only mode

SQL> alter pluggable database PDB$SEED open read write;
Pluggable database altered.

SQL> alter pluggable database PDB$SEED close;
Pluggable database altered.

SQL> alter pluggable database PDB$SEED open read only;
Pluggable database altered.

SQL> alter session set "_oracle_script"=false;
Session altered.

使用其它CDB的PDB$SEED复制PDB$SEED

创建一个dblink指向远程CDB
SQL> create database link rmt_seed_link xxxxxx ;

SQL> select con_id,name,open_mode from v$pdbs@rmt_seed_link where name='PDB$SEED';

    CON_ID NAME            OPEN_MODE
---------- --------------- ----------
         2 PDB$SEED        READ ONLY

SQL> exec DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/seed_orpcdb1.xml', pdb_name => 'pdb$seed@REMOTE_SEED_LINK');
PL/SQL procedure successfully completed.

SQL> !ls -lrt /home/oracle/seed_orpcdb1.xml
-rw-r--r-- 1 oracle dba 5344 Apr 29 01:04 /home/oracle/seed_orpcdb1.xml

SQL> select name from v$datafile@RMT_SEED_LINK where con_id=2;
NAME
------------------------------------------------------------
/u01/orabase/oradata/ANBOB18C_R/pdbseed/system01.dbf
/u01/orabase/oradata/ANBOB18C_R/sysaux01.dbf
/u01/orabase/oradata/ANBOB18C_R/users01.dbf


SQL> select name from v$tempfile@RMT_SEED_LINK where con_id=2;
NAME
------------------------------------------------------------
/u01/orabase/oradata/ANBOB18C_R/temp01.dbf

$> scp 复制所有pdb$seed 数据文件从远程到本地

-- plug-in
SQL> create pluggable database "PDB$SEED" using '/home/oracle/seed_orpcdb1.xml'
  2  source_file_name_convert=('/u01/orabase/oradata/ANBOB18C_R/pdbseed/','/u01/orabase/oradata/ANBOB18C/pdbseed/')
  3  NOCOPY
  4  TEMPFILE REUSE
  5  ;

Pluggable database created.

— 另外12.2 以后提供的online pdb clone,也可以测试一下，参考之前的blog不再演示.