Multitenant - Creating PDB from seed
Creating PDB from PDB$SEED template default by mapping names of existing files to new file names destination option below:
KEYWARDS:
- DB_CREATE_FILE_DEST
- FILE_NAME_CONVERT
- PDB_FILE_NAME_CONVERT
EXAMPLE:
- DB_CREATE_FILE_DEST:
SQL> select cdb from v$database;
CDB
---
YES
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> sho parameter create_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/system01.dbf
/u01/app/oracle/oradata/SMRCDB/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/undotbs01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/users01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/undotbs01.dbf
7 rows selected.
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb1admin IDENTIFIED BY pdb1admin STORAGE (MAXSIZE 2G);
CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb1admin IDENTIFIED BY pdb1admin STORAGE (MAXSIZE 2G)
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL> alter session set DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/SMRCDB/';
Session altered.
SQL> sho parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/SMRCDB
/
SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb1admin IDENTIFIED BY pdb1admin STORAGE (MAXSIZE 2G);
Pluggable database created.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/system01.dbf
/u01/app/oracle/oradata/SMRCDB/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/undotbs01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/users01.dbf
/u01/app/oracle/oradata/SMRCDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC1FFF3873F80C57E0534938A8C08B37/datafile/
o1_mf_system_hlo34nwc_.dbf
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC1FFF3873F80C57E0534938A8C08B37/datafile/
NAME
--------------------------------------------------------------------------------
o1_mf_sysaux_hlo34nwj_.dbf
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC1FFF3873F80C57E0534938A8C08B37/datafile/
o1_mf_undotbs1_hlo34nwj_.dbf
10 rows selected.
- FILE_NAME_CONVERT:
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 17:57:44 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE PLUGGABLE DATABASE PDB2 ADMIN USER pdb1admin IDENTIFIED BY pdb1admin FILE_NAME_CONVERT=('/u01/app/oracle/oradata/SMRCDB/pdbseed/','/u01/app/oracle/oradata/SMRCDB/PDB2/');
Pluggable database created.
SQL>
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter pluggable database PDB2 open;
Pluggable database altered.
SQL> alter session set container=PDB2;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/PDB2/system01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB2/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB2/undotbs01.dbf
- PDB_FILE_NAME_CONVERT:
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 18:05:45 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> sho parameter PDB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SMRCDB/pdbseed/','/u01/app/oracle/oradata/SMRCDB/PDB3/';
Session altered.
SQL> sho parameter PDB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string /u01/app/oracle/oradata/SMRCDB
/pdbseed/, /u01/app/oracle/ora
data/SMRCDB/PDB3/
SQL> CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb1admin IDENTIFIED BY pdb1admin;
Pluggable database created.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL> alter pluggable database PDB3 open;
Pluggable database altered.
SQL> alter session set container=PDB3;
Session altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 READ WRITE NO
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/PDB3/system01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB3/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB3/undotbs01.dbf
SQL>
No comments:
Post a Comment