用多种方法实现创建3个PDB

来源：互联网发布：广联达软件培训编辑：程序博客网时间：2024/06/07 20:28

1. 创建第一个PDB：Creatinga PDB by Using the Seed

[oracle@12cr2 ~]$ export ORACLE_SID=zylong[oracle@12cr2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 20:04:22 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1207959552 bytesFixed Size                  8792152 bytesVariable Size             436209576 bytesDatabase Buffers          754974720 bytesRedo Buffers                7983104 bytesDatabase mounted.Database opened.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NOSQL> set timing onSQL> CREATE PLUGGABLE DATABASE seed_pdb  2    ADMIN USER pdbadm IDENTIFIED BY oracle  3    ROLES = (dba)              ##授予pdbadm用户DBA权限  4    DEFAULT TABLESPACE seed_pdb_tbs  5      DATAFILE '/u01/app/oracle/oradata/zylong/seed_pdb/seed_pdb_tbs01.dbf' SIZE 250M AUTOEXTEND ON  6    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/pdbseed/',  7                         '/u01/app/oracle/oradata/zylong/seed_pdb/')  8    STORAGE (MAXSIZE 2G)      ##当前PDB所有数据文件不超过2G  9    PATH_PREFIX = '/u01/app/oracle/oradata/zylong/seed_pdb/';Pluggable database created.Elapsed: 00:00:15.43

2. 创建第二个PDB：Cloninga PDB From an Existing PDB

此处创建PDB的SQL中没有指定STORAGE (MAXSIZE 2G)，说明这个PDB的数据文件大小没有限制。

SQL> CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb  2    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/seed_pdb/', '/u01/app/oracle/oradata/zylong/clon_pdb/')  3    PATH_PREFIX = '/u01/app/oracle/oradata/zylong/clon_pdb/';CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb*ERROR at line 1:ORA-65036: pluggable database SEED_PDB not open in required mode

##提示SEED_PDB数据库没有OPEN，下面将SEED_PDB数据库OPEN后重建执行

SQL> alter session set container=seed_pdb;Session altered.SQL> alter database open;  Database altered.SQL> conn / as sysdbaConnected.SQL> CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb  2    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/seed_pdb/', '/u01/app/oracle/oradata/zylong/clon_pdb/')  3    PATH_PREFIX = '/u01/app/oracle/oradata/zylong/clon_pdb/';Pluggable database created.Elapsed: 00:00:34.23SQL> alter pluggable database CLON_PDB open;Pluggable database altered.

3. 创建第三个PDB：Plugginga PDB into a CDB

将NON-CDB的数据库作为PDB插入到CDB中。

[oracle@12cr2 oradata]$ export ORACLE_SID=orcl[oracle@12cr2 oradata]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 21:07:07 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1610612736 bytesFixed Size                  8621232 bytesVariable Size            1040188240 bytesDatabase Buffers          553648128 bytesRedo Buffers                8155136 bytesDatabase mounted.Database opened.SQL> select name ,cdb from v$database;NAME      CDB--------- ---ORCL      NO

##插入测试数据

SQL> create table tb1 (id int);Table created.SQL> insert into tb1 values (1);1 row created.SQL> commit;Commit complete.SQL> select * from tb1;        ID----------         1

##将NON-CDB启动到read only

SQL> startup mount force;ORACLE instance started.Total System Global Area 1610612736 bytesFixed Size                  8621232 bytesVariable Size            1040188240 bytesDatabase Buffers          553648128 bytesRedo Buffers                8155136 bytesDatabase mounted.SQL> alter database open read only;alter database open read only*ERROR at line 1:ORA-16005: database requires recovery

##可以说明startup mount force是不正常关闭数据库，数据库启动后需要恢复，还是老老实实shutdown immediate吧。

SQL> alter database open;Database altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1610612736 bytesFixed Size                  8621232 bytesVariable Size            1040188240 bytesDatabase Buffers          553648128 bytesRedo Buffers                8155136 bytesDatabase mounted.SQL> alter database open read only;Database altered.

##创建XML文件并关闭NON-CDB

SQL> exec dbms_pdb.describe(PDB_DESCR_FILE=>'/u01/app/oracle/oradata/zylong/noncdb_pdp.xml');PL/SQL procedure successfully completed.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##切换到CDB数据库，将NON-CDB作为PDB插入到CDB中

[oracle@12cr2 oradata]$ export ORACLE_SID=zylong[oracle@12cr2 oradata]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 21:40:47 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> set timing on;SQL> CREATE PLUGGABLE DATABASE noncdb_pdp  2    USING '/u01/app/oracle/oradata/zylong/noncdb_pdp.xml'  3    FILE_NAME_CONVERT =  4      ('/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/zylong/noncdb_pdp/')  5    COPY;Pluggable database created.Elapsed: 00:00:44.08SQL> alter pluggable database noncdb_pdp open;Warning: PDB altered with errors.

##启动PDB有Warning，上网搜搜原来需要执行noncdb_to_pdb.sql脚本。

SQL> alter session set container=NONCDB_PDP;Session altered.sql> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sqlSQL> conn / as sysdbaConnected.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 SEED_PDB                       READ WRITE NO         4 NONCDB_PDP                     READ WRITE YES         5 CLON_PDB                       READ WRITE NO

## 查一下PDB状态，NONCDB_PDP受限，RESTRICTED是YES，重启一下PDB

SQL> alter session set container=NONCDB_PDP;Session altered.SQL> shutdown immediatePluggable Database closed.SQL> conn / as sysdbaConnected.SQL> alter pluggable database NONCDB_PDP open;Pluggable database altered.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 SEED_PDB                       READ WRITE NO         4 NONCDB_PDP                     READ WRITE NO         5 CLON_PDB                       READ WRITE NO

## NONCDB_PDP状态正常了，下面查查之前插入的数据还在吗

SQL> alter session set container=NONCDB_PDP;Session altered.SQL> select * from tb1;        ID----------         1

阅读全文

0 0