HOL for Create and manage pluggable databases

Go back to OCM 12c Preparation Project 
Special Thanks to www.dbarj.com.br

Hands On Lab – Index

[0]. Prepare the DB Server Environment.
[0.1] Create a .bash_profile file
[0.2] Install Oracle Database Software in a silent mode
[1] Create the Database
[1.1] Create database using DBCA via GUI
[1.2] Create a database using dbca response file.
[1.3] Create a database using dbca with parameters in the command lines.
[1.4] Create database manually using OMF (Oracle Managed Files).
[1.5] Create database manually without OMF
[2] Drop the Database
[2.1] Using DBCA GUI.
[2.2] Using dbca silent method.
[2.3] Using SQL prompt
[3] Manage pluggable databases
[3.1] Create a PDB by using the seed
[3.2] Unplugging a PDB from a CDB
[3.3] Dropping a PDB
[3.4] Create a PDB by plugging an unplugged PDB into a CDB
3.4.1 Plugging an PDB from character set al32utf8 to WE8MSWIN1252 in cdb2
[3.5] Create a PDB by cloning an existing PDB or non-CDB (local/remote DBLink)
3.5.1 local clone
3.5.2 remote databases clone via DBLink
[3.6] Create a PDB by using a non-CDB
3.6.1 Create a PDB by using a non-CDB in same version and same characterset
3.6.2 Create a PDB by using a non-CDB in diff. version and diff. characterset
[4] Tools
[4.1] Oracle Universal Installer (OUI)
[4.2] Database Configuration Assistant (DBCA)
[4.3] SQL Developer
[4.4] Cloud Control

[0]. Prepare the DB Server Environment.

Documentation:
Database Installation Guide for Linux 
--> 5 Configuring Users, Groups and Environments for Oracle Database 
--> 5.2 Checking Resource Limits for Oracle Software Installation Users

$ cat /etc/security/limits.conf | tail -10
oracle soft nproc 2048
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768

Documentation:
Database Installation Guide for Linux 
--> D How to Complete Preinstallation Tasks Manually 
--> D.1 Configuring Kernel Parameters for Linux

# cat /etc/sysctl.conf | tail -14
# Controls the maximum number of shared memory segments, in pages
#kernel.shmall = 4294967296

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

#Creating groups and users.
[root@racnode1 ]#groupadd oinstall
[root@racnode1 ]#groupadd dba
[root@racnode1 ]#groupadd oper
[root@racnode1 ]#groupadd backupdba
[root@racnode1 ]#groupadd dgdba
[root@racnode1 ]#groupadd kmdba
[root@racnode1 ]#groupadd asmadmin
[root@racnode1 ]#groupadd asmdba
[root@racnode1 ]#groupadd asmoper
#useradd -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmadmin,
asmdba,asmoper oracle

# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.

#Creating directory
[root@racnode1 ~]# mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
[root@racnode1 ~]# mkdir -p /u01/app/grid/12.1.0.2

# chown -R oracle:oinstall /u01/app/oracle/product/12.1.0.2/db_1/
[root@racnode1 ~]# chmod -R 776 /u01/app/oracle/product/12.1.0.2/db_1/
[root@racnode1 ~]#
[root@racnode1 ~]# chown -R oracle:oinstall /u01/app/grid/12.1.0.2/
[root@racnode1 ~]# chmod -R 776 /u01/app/grid/12.1.0.2/

[0.1] Create a .bash_profile file (3-5 min.)

#Creating .bash_profile
1$ cat .bash_profile
#Oracle Profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=oltpdb
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

alias sql='sqlplus / as sysdba'
alias oltpdb="export ORACLE_SID=oltpdb; sqlplus / as sysdba"
alias dwhdb="export ORACLE_SID=dwhdb; sqlplus / as sysdba"
alias net="cd $ORACLE_HOME/network/admin"
alias dbs="cd $ORACLE_HOME/dbs"

-bash-4.1$ echo $ORACLE_SID
oltpdb
-bash-4.1$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/db_1
-bash-4.1$ cd $ORACLE_HOME
-bash-4.1$ pwd
/u01/app/oracle/product/12.1.0.2/db_1
-bash-4.1$ echo $ORACLE_BASE
/u01/app/oracle
-bash-4.1$ cd $ORACLE_BASE
-bash-4.1$ pwd
/u01/app/oracle

[0.2] Install Oracle in a silent mode 
(configure response file, tun the installation and track it) (10 min.)

#Silent installation of oracle software
$ cat db_install.rsp | grep -v '#' | awk 'NF>0' > dbinstall1.rsp

# cat dbinstall1.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=racnode1
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.BACKUPDBA_GROUP=backupdba
oracle.install.db.DGDBA_GROUP=dgdba
oracle.install.db.KMDBA_GROUP=kmdba
oracle.install.db.rac.configurationType=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.rac.serverpoolName=
oracle.install.db.rac.serverpoolCardinality=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryOption=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
COLLECTOR_SUPPORTHUB_URL=

$ ./runInstaller -silent -ignoreSysPrereqs -ignorePrereq 
-responsefile /u01/database/response/dbinstall1.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 20154 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4031 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-07-17_10-33-54AM. Please wait ...
-bash-4.1$ You can find the log of this install session at:
/u01/OraInventory/logs/installActions2019-07-17_10-33-54AM.log

Note:
-ignoreInternalDriverError parameter is used to avoid "unexpected internal driver error"

[1] Create the Database

[1.1] Create database using DBCA via GUI 
(the fastest and recommended if the exam does not put any restrictions) 
(10 min.)

./dbca

tail -100f $ORACLE_BASE/cfgtoollogs/dbca/$ORACLE_SID/trace.log

select status from V$instance;
sql

[1.2] Create a database using dbca with response file. (10 min.)

$ cd database/
-bash-4.1$ ls
install response rpm runInstaller sshsetup stage welcome.html
-bash-4.1$ cd response/
-bash-4.1$ ls
dbca2.rsp dbca.rsp dbinstall1.rsp db_install.rsp netca.rsp
-bash-4.1$
-bash-4.1$ cat dbca.rsp | grep -v '#' | awk 'NF>0' > dbca_cdb.rsp
-bash-4.1$ vi dbca_cdb.rsp
[GENERAL]
RESPONSEFILE_VERSION = "12.1.0"
OPERATION_TYPE = "createDatabase"
[CREATEDATABASE]
GDBNAME = "orcl12c.us.oracle.com"
SID = "orcl12c"
TEMPLATENAME = "General_Purpose.dbc"
createascontainerdatabase=true
numberofpdbs=2
pdbname=mypdb
pdbadminpassword=oracle_4U
syspassword=oracle_4U
systempassword=oracle_4U
emconfiguration=dbexpress
datafiledestination=/u01/app/oracle/oradata
memorypercentage=20
characterset=al32utf8

-bash-4.1$ cd $ORACLE_HOME/bin
-bash-4.1$ ./dbca -silent -responsefile /u01/database/response/dbca_cdb.rsp
Copying database files
1% complete
2% complete
8% complete
13% complete
19% complete
27% complete
Creating and starting Oracle instance
29% complete
32% complete
33% complete
34% complete
38% complete
42% complete
43% complete
45% complete
Completing Database Creation
48% complete
51% complete
53% complete
62% complete
64% complete
72% complete
Creating Pluggable Databases
78% complete
83% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl12c/orcl12c0.log" for further details.

[1.3] Create a database using dbca with parameters in the command lines.
(10 min.)
Get the list of all parameters from dbca -help output 
(dbca -createdatabase -help)
Run dbca -silent command to create a database
Check alert.log file

Example-1 Non CDB
dbca -silent -createdatabase -gdbname mydb -sid mydb -syspassword oracle 
-systempassword oracle -templatename General_Purpose.dbc 
-characterset al32utf8 -memorypercentage 40 
-datafiledestination $ORACLE_HOME/oradata
-emconfiguration none -redologfilesize 100 -databasetype oltp 
-storagetype fs -listeners LISTENER

Example-2 CDB
./dbca -silent -createdatabase -gdbname cdb1 -sid cdb1
-createascontainerdatabase true -numberofpdbs 2 -pdbadminpassword oracle_4U 
-pdbname pdb1 -templatename General_Purpose.dbc 
-datafiledestination /u01/app/oracle/oradata/
-characterset al32utf8 -syspassword oracle_4U 
-systempassword oracle_4U -memorypercentage 20 
-redologfilesize 100 -emConfiguration dbexpress

Note: Monitor the progress using below command.
tail -100f /u01/app/oracle/cfgtoollogs/dbca/cdb1/trace.log

REFERENCE
=========
-bash-4.1$ dbca -createdatabase -help
Create a database by specifying the following parameters:
-createDatabase
-templateName 
[-cloneTemplate]
-gdbName 
[-ignorePreReqs] 
[-sid ]
[-createAsContainerDatabase ]
[-numberOfPDBs ]
[-pdbName ]
[-pdbAdminPassword ]
[-sysPassword ]
[-systemPassword ]
[-emConfiguration ]
-dbsnmpPassword     
[-omsHost     
-omsPort     
-emUser     
-emPassword     
-emExpressPort     ]]
[-dvConfiguration 
-dvUserPassword     
-dvAccountManagerName     
-dvAccountManagerPassword     ]
[-olsConfiguration 
[-datafileDestination  |
-datafileNames ]
[-redoLogFileSize ]
[-recoveryAreaDestination ]
[-datafileJarLocation  ]
[-storageType 
[-asmsnmpPassword     ]
                         -diskGroupName   
                         -recoveryGroupName       
[-characterSet ]
[-nationalCharacterSet  ]
                [-registerWithDirService 
                        -dirServiceUserName    
                        -dirServicePassword    
                        -walletPassword    ]
[-listeners  ]
[-variablesFile   ]]
[-variables  ]
[-initParams ]
[-sampleSchema   ]
[-memoryPercentage ]
[-automaticMemoryManagement  ]
[-totalMemory ]
[-databaseType ]]

Documentation
Database Administrator’s Guide 
--> 2 Creating and Configuring an Oracle Database
--> 2.3 Creating a Database with DBCA 
--> 2.3.3 About Creating a Database with Noninteractive/Silent DBCA

[1.4] Create database manually using OMF (Oracle Managed Files) (10 min.)

1.4.1 Create the non container database using OMF with Manual Method 

#Create parameter files
dbs
cat init.ora | grep -v '#' > initmydb.ora
vi initmydb.ora
db_name='mydb'
db_unique_name='mydb
db_block_size=8192
memory_target=1024m
pga_aggregate_target=130m
control_files='/home/oracle/folder1/mydb/control01.ctl','/home/oracle/folder2/mydb/control02.ctl'
diagnostic_dest=/u01/app/oracle
db_recovery_file_dest=/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size=1G
db_create_file_dest=/u01/app/oracle/oradata
db_create_online_log_dest_1=/home/oracle/folder1
db_create_online_log_dest_2=/home/oracle/folder2
undo_tablespace=undotbs

cd $ORACLE_BASE/oradata
mkdir mydb

Oracle® Database Administrator's Guide
Creating a Database with the CREATE DATABASE Statement
Step 9: Issue the CREATE DATABASE Statement

vi createmydb.sql
CREATE DATABASE mydb
USER SYS IDENTIFIED BY oracle_4U
USER SYSTEM IDENTIFIED BY oracle_4U
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 100M AUTOEXTEND ON
UNDO TABLESPACE undotbs DATAFILE SIZE 200M AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TABLESPACE users DATAFILE SIZE 150M
character set AL32UTF8
LOGFILE GROUP 1 SIZE 100M,GROUP 2 SIZE 100M,GROUP 3 SIZE 100M;

sql
STARTUP NOMOUNT
@createmydb.sql

CREATE SPFILE FROM PFILE;
SHUTDOWN IMMEDIATE
STARTUP

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

connect system/oracle_4U
@?/sqlplus/admin/pupbld.sql

sql
@?/rdbms/admin/utlrp.sql

SELECT * FROM DBA_OBJECTS WHERE STATUS != 'VALID';

1.4.2 Create the container database using OMF with Manual Method 

#Create a parameter file
cat init.ora | grep -v '#' > initcdb2.ora

#Edit the parameter file.
vi initcdb2.ora
db_name='cdb2'
memory_target=1G
processes = 150
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (ora_cdb2_control1.ctl,/u01/app/oracle/fast_recovery_area/cdb2/ora_cdb2_control2.ctl)
compatible ='12.1.0.2.0'
db_create_file_dest='/u01/app/oracle/oradata'
db_create_online_log_dest_1='/u01/app/oracle/fast_recovery_area'
enable_pluggable_database=true

vi createdatabaseusingOMF.sql
CREATE DATABASE cdb2
USER SYS IDENTIFIED BY oracle_4U
USER SYSTEM IDENTIFIED BY oracle_4U
CHARACTER SET AL32UTF8
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE SIZE 100M AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE SIZE 100M AUTOEXTEND ON
DEFAULT TABLESPACE users DATAFILE SIZE 10M AUTOEXTEND ON
LOGFILE GROUP 1 SIZE 50M, GROUP 2 SIZE 50M, GROUP 3 SIZE 50M
ENABLE PLUGGABLE DATABASE
SEED
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON
SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON;

$export ORACLE_SID=cdb2
$sql
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcdb2.ora
SQL> @createdatabaseusingOMF.sql

@?/rdbms/admin/catcdb.sql

Documentation
37 Creating and Configuring a CDB
37.4.2 Creating a CDB with the CREATE DATABASE Statement

[1.5] Create database manually without OMF (10 min.)

1.5.1 Create the non CDB using CREATE DATABASE Command.

#Create parameter files
dbs
cat init.ora | grep -v '#' > initmydb.ora
vi initmydb.ora
db_name='mydb'
db_unique_name='mydb
db_block_size=8192
memory_target=1024m
pga_aggregate_target=130m
control_files='/home/oracle/folder1/mydb/control01.ctl','/home/oracle/folder2/mydb/control02.ctl'
diagnostic_dest=/u01/app/oracle
db_recovery_file_dest=/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size=1G
undo_tablespace=undotbs

cd $ORACLE_BASE/oradata
mkdir mydb

Oracle® Database Administrator's Guide
Creating a Database with the CREATE DATABASE Statement
Step 9: Issue the CREATE DATABASE Statement

vi createmydb.sql
CREATE DATABASE mydb
USER SYS IDENTIFIED BY eliteAAA123
USER SYSTEM IDENTIFIED BY eliteAAA123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mydb/redo01a.log','/home/oracle/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/mydb/redo02a.log','/home/oracle/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/mydb/redo03a.log','/home/oracle/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
sql
STARTUP NOMOUNT
@createmydb.sql

CREATE SPFILE FROM PFILE;
SHUTDOWN IMMEDIATE
STARTUP

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

connect system/system_password
@?/sqlplus/admin/pupbld.sql

1.5.2 Create the CDB using CREATE DATABASE Command.

[2] Drop the Database

2.1 Using DBCA GUI.

2.2 Using dbca silent method.

sql
select status from V$instance;
alert
dbca -silent -deletedatabase -sourceDB mydb

2.3 Using SQL prompt

alert
STARUP FORCE MOUNT EXCLUSIVE RESTRICT;
DROP DATABASE;

==========================
[3] Manage pluggable databases
==========================
[#1] Create a PDB by using the seed

Example-1
CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm 
IDENTIFIED BY password;

Example-2
CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm 
IDENTIFIED BY password ROLES=(DBA);

Example-3
CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm 
IDENTIFIED BY password STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE sales
DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', 
'/disk1/oracle/dbs/salespdb/');

Example-4
create pluggable database mypdb3 admin user myadmin 
identified by oracle_4U roles =(DBA) storage (maxsize 2G)
default tablespace sales
datafile '/u01/app/oracle/oradata/orcl12c/mypdb3/sales.dbf' size 10M 
autoextend on file_name_convert=('pdbseed','mypdb3');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB1 READ WRITE NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 MOUNTED
SQL> alter pluggable database mypdb3 open;

Pluggable database altered.

SQL> alter pluggable database mypdb3 save state;

Pluggable database altered.

SQL> alter session set container=mypdb3;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
MYPDB3
SQL> select * from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8 Character set

SQL> select * from database_properties where property_name LIKE 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE SALES Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type

Documentation
38 Creating and Removing PDBs with SQL*Plus
38.3.2 Creating a PDB from the Seed

[#2] Unplugging a PDB from a CDB

-bash-4.1$ export ORACLE_SID=orcl12c
-bash-4.1$ sql
SQL> startup
ORACLE instance started.

Total System Global Area 939524096 bytes
Fixed Size 2931088 bytes
Variable Size 348128880 bytes
Database Buffers 583008256 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB1 READ WRITE NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO
SQL>
SQL> alter pluggable database mypdb1 unplug into '/home/oracle/mypdb1.xml';
alter pluggable database mypdb1 unplug into '/home/oracle/mypdb1.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database MYPDB1 is not closed on all instances.

SQL> alter pluggable database mypdb1 close;

Pluggable database altered.

SQL> alter pluggable database MYPDB1 unplug into '/home/oracle/mypdb1.xml';

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB1 MOUNTED
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO

[#3] Dropping a PDB

SQL> drop pluggable database mypdb1 keep datafiles;

Pluggable database dropped.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO

SQL> exit

-bash-4.1$ ls -ltr /u01/app/oracle/oradata/orcl12c/mypdb1/
total 855072
-rw-r----- 1 oracle oinstall 5251072 Jul 25 09:35 mypdb1_users01.dbf
-rw-r----- 1 oracle oinstall 608182272 Jul 25 09:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 262152192 Jul 25 09:35 system01.dbf

ALERT
Completed: alter pluggable database MYPDB1 unplug into '/home/oracle/mypdb1.xml'
drop pluggable database mypdb1 keep datafiles
Thu Jul 25 09:36:56 2019
Deleted file /u01/app/oracle/oradata/orcl12c/mypdb1/temp012019-07-24_08-30-00-PM.dbf
Completed: drop pluggable database mypdb1 keep datafiles

[4] Create a PDB by plugging an unplugged PDB into a CDB

4.1 Plugging an PDB with character set al32utf8 into cdb2 having character set WE8MSWIN1252

-bash-4.1$ cd $ORACLE_HOME/bin
-bash-4.1$ ./dbca -silent -createdatabase -gdbname cdb2 -sid cdb2 
-createascontainerdatabase true -templatename General_Purpose.dbc 
-emconfiguration none -syspassword oracle_4U -systempassword oracle_4U 
-memorypercentage 15
Copying database files
1% complete
3% complete
11% complete
18% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
46% complete
47% complete
52% complete
57% complete
58% complete
59% complete
62% complete
Completing Database Creation
66% complete
70% complete
74% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/cdb20.log" for further details.
-bash-4.1$ export ORACLE_SID=cdb2
-bash-4.1$ sql
SQL> show parameter pdbs
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO

SQL>
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------
NLS_CHARACTERSET WE8MSWIN1252 Character set

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.
Total System Global Area 704643072 bytes
Fixed Size 2928392 bytes
Variable Size 293601528 bytes
Database Buffers 402653184 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL>
SQL> alter database character set internal_use al32utf8;
Database altered.

SQL> alter database character set al32utf8;
Database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY YES <<<<<<<<< alter session set container=pdb$seed; Session altered. SQL> select * from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------------------------------------------------
NLS_CHARACTERSET WE8MSWIN1252 Character set

SQL> shut immediate
ORA-65017: seed pluggable database may not be dropped or altered

SQL> !oerr ora65017
Usage: oerr facility error

Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error. So you should type "oerr ora 7300".

If you get LCD-111, type "oerr lcd 111", and so on.

SQL> alter session set container=cdb$root;

Session altered.

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

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY YES

SQL> alter database character set internal_use al32utf8 container=all;
alter database character set internal_use al32utf8 container=all
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter database character set al32utf8 container=pdb$seed;
alter database character set al32utf8 container=pdb$seed
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> alter pluggable database pdb$seed open read write;
alter pluggable database pdb$seed open read write
*
ERROR at line 1:
ORA-65019: pluggable database PDB$SEED already open

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read write;

Warning: PDB altered with errors.

SQL> select * from PDB_PLUG_IN_VIOLATIONS;

25-JUL-19 10.03.12.235768 AM PDB$SEED Database CHARACTER SET ERROR 65116 1
Character set mismatch: PDB character set WE8MSWIN1252. CDB character set AL32UTF8.
PENDING
Convert the character set of the PDB to match the CDB or plug the PDB in a CDB with compatible character set

SQL> show pdbs

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

SQL> alter session set container=pdb$seed;

Session altered.

SQL> alter database character set internal_use al32utf8;

Database altered.

SQL> alter database character set al32utf8;

Database altered.

SQL> show pdbs

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

SQL> select * from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8 Character set

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY YES

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 704643072 bytes
Fixed Size 2928392 bytes
Variable Size 293601528 bytes
Database Buffers 402653184 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/mypdb1.xml') then
3 dbms_output.put_line('compatible');
4 else
5 dbms_output.put_line('not compatible');
6 end if;
7 END;
8 /
compatible

PL/SQL procedure successfully completed.

SQL> create pluggable database pdb1 using '/home/oracle/mypdb1.xml' 
file_name_convert=('mypdb1','pdb1');

Pluggable database created.

SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=pdb1;

Session altered.

SQL> select name from v$datafile;
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/pdb1_users01.dbf

SQL> shutdown;
Pluggable Database closed.
SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml';

Pluggable database altered.

SQL> drop pluggable database pdb2 keep datafiles;

Pluggable database dropped.

SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

$export ORACLE_SID=orcl12c
sql
SQL> run
1 begin
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/pdb2.xml') then
3 dbms_output.put_line('compatible');
4 else
5 dbms_output.put_line('not compatible');
6 end if;
7* END;

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
compatible

PL/SQL procedure successfully completed.

SQL> CREATE PLUGGABLE DATABASE MYPDB5 USING '/home/oracle/pdb2.xml' MOVE
2 file_name_convert=('pdb2','MYPDB5');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB5 MOUNTED
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO
SQL> alter pluggable database mypdb5 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB5 READ WRITE NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO
SQL> alter session set container=mypdb5;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/MYPDB5/system01.dbf
/u01/app/oracle/oradata/cdb2/MYPDB5/sysaux01.dbf

[#5] Create a PDB by cloning an existing PDB or non-CDB 
(local or remote databases via DBLink)

5.1 local clone

SQL> create pluggable database mypdb4 from mypdb1 
file_name_convert=('mypdb1','mypdb4');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB1 READ WRITE NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 MOUNTED

SQL> alter pluggable database mypdb4 open;

Pluggable database altered.

SQL> alter pluggable database mypdb4 save state;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB1 READ WRITE NO
4 MYPDB2 READ WRITE NO
5 MYPDB3 READ WRITE NO
6 MYPDB4 READ WRITE NO
SQL> alter session set container=mypdb4;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
MYPDB4
SQL> select * from database_properties where property_name like 'DEFAULT%' OR property_name='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_CHARACTERSET AL32UTF8 Character set

4.2 remote databases clone via DBLink

$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cdb2)
)
)

orcl12c =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl12c.us.oracle.com)
)
)

export ORACLE_SID=cdb2
sql

SQL> create database link orcl12c_dblink connect to system 
identified by oracle_4U using 'orcl12c';

Database link created.

SQL> select count(*) from tab;

COUNT(*)
----------
7026

SQL> select count(*) from tab@orcl12c_dblink;

COUNT(*)
----------
200

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED

--Example-1

SQL> create pluggable database pdb2 from MYPDB2@orcl12c_dblink
file_name_convert=('mypdb2','pdb2')

Pluggable database created.

SQL> alter session set container=pdb2;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/pdb2_users01.dbf

--Example-2

SQL> create pluggable database pdb3 from mypdb4@orcl12c_dblink
file_name_convert=('/u01/app/oracle/oradata/orcl12c/mypdb4','/u01/app/oracle/oradata/cdb2/pdb3')

Pluggable database created.

SQL> show 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> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/pdb3/system01.dbf
/u01/app/oracle/oradata/cdb2/pdb3/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/pdb3/mypdb4_users01.dbf

SQL>

[6] Create a PDB by using a non-CDB

6.1 Create a PDB by using a non-CDB in same version and same characterset

-bash-4.1$ ./dbca -silent -createdatabase -gdbname ncdb -sid ncdb 
-syspassword oracle_4U -systempassword oracle_4U 
-templatename General_Purpose.dbc -emconfiguration none 
-memorypercentage 15 -characterset al32utf8
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete

-bash-4.1$ sql
SQL> select name,open_mode,cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
NCDB READ WRITE NO

SQL> select logins from v$instance;
LOGINS
----------
ALLOWED

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 704643072 bytes
Fixed Size 2928392 bytes
Variable Size 293601528 bytes
Database Buffers 402653184 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL>
SQL> set serveroutput on
SQL> EXEC DBMS_PDB.DESCRIBE('/home/oracle/ncdb.xml');

PL/SQL procedure successfully completed.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-bash-4.1$ export ORACLE_SID=orcl12c
-bash-4.1$ sql
SQL> startup
ORACLE instance started.
Total System Global Area 939524096 bytes
Fixed Size 2931088 bytes
Variable Size 348128880 bytes
Database Buffers 583008256 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
6 MYPDB4 READ WRITE NO
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/ncdb.xml') then
3 dbms_output.put_line('compatible');
4 else
5 dbms_output.put_line('not compatible');
6 end if;
7 END;
8 /
compatible

PL/SQL procedure successfully completed.

SQL> create pluggable database mypdb10 using '/home/oracle/ncdb.xml' move
2 file_name_convert=('/u01/app/oracle/oradata/ncdb',
'/u01/app/oracle/oradata/orcl12c/mypdb10');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB10 MOUNTED
6 MYPDB4 READ WRITE NO

SQL> alter session set container=mypdb10;

Session altered.

SQL>@?/rdbms/admin/noncdb_to_pdb.sql

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 MYPDB10 MOUNTED

SQL> startup
Pluggable Database opened.
SQL> show pdbs

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

6.2 Create a PDB by using a non-CDB in different version and different characterset

1. Create noncdb : 11.2.0.4 with character set AL32UTF8
2. Upgrade character set from AL32UTF8 to WE8MSWIN1252
3. Upgrade noncdb from 11.2.0.4 to 12.1.0.2

--Verify the target db characterset

-bash-4.1$ . oraenv
ORACLE_SID = [oracle] ? orcl12102cdb
The Oracle base has been set to /refresh/home/app/12.1.0.2/oracle
-bash-4.1$
-bash-4.1$ sql
SQL> startup
ORACLE instance started.
Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select * from database_properties where property_name='NLS_CHARACTERSET';
PROPERTY_NAME      PROPERTY_VALUEDESCRIPTION
--------------------------------------------------------------------------------
NLS_CHARACTERSET  WE8MSWIN1252  Character set

#1 Create noncdb in database version 11.2.0.4 with characterset al32utf8

-bash-4.1$ ./dbca -silent -createdatabase -gdbname ncdb11g -sid ncdb11g 
-templatename General_Purpose.dbc -syspassword oracle_4U 
-systempassword oracle_4U -emconfiguration none -totalmemory 1024 
-databasetype oltp -characterset al32utf8
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/refresh/home/app/11.2.0.4/oracle/cfgtoollogs/dbca/ncdb11g/ncdb11g.log" for further details.

-bash-4.1$ . oraenv
ORACLE_SID = [orcl11204] ? ncdb11g
The Oracle base remains unchanged with value /refresh/home/app/11.2.0.4/oracle
-bash-4.1$ sql
SQL> select * from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_NAME  PROPERTY_VALUE   DESCRIPTION
--------------------------------------------------------------------------------
NLS_CHARACTERSET        AL32UTF8        Character set

#2. Upgrade character set from AL32UTF8 to WE8MSWIN1252

SQL> exit
-bash-4.1$ csscan
Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Thu Jul 25 12:26:42 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.
-bash-4.1$
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 25 12:27:19 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/csminst.sql

-bash-4.1$ csscan
Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Thu Jul 25 12:27:39 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1

Current database character set is AL32UTF8.

Enter new database character set name: > WE8MSWIN1252

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..): 1 >

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 1 scanning SYS.PARAMETER$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.METHOD$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.TYPE$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.ATTRIBUTE$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.ARGUMENT$[AAAADfAABAAAAXYAAA]
....................
. process 1 scanning CTXSYS.DR$DBO
. process 1 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 1 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$ERRCODE

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

-bash-4.1$ echo $ORACLE_SID
ncdb11g
-bash-4.1$ sql
SQL> select * from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE  DESCRIPTION
--------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8 Character set

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area 801701888 bytes
Fixed Size 2257520 bytes
Variable Size 272633232 bytes
Database Buffers 520093696 bytes
Redo Buffers 6717440 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/csalter.plb

3 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf')  'Y') then
new 6: if (UPPER('Y')  'Y') then
Checking data validity...
begin converting system objects
1 row in table SYS.WRI$_ADV_OBJECTS is converted
1 row in table SYS.RADM_FPTM_LOB$ is converted
130 rows in table SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS are converted
9994 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
12 rows in table APEX_030200.WWV_FLOW_BUTTON_TEMPLATES are converted
1 row in table SYS.RULE$ is converted
7416 rows in table APEX_030200.WWV_FLOW_PAGE_PLUGS are converted
179 rows in table SYS.METASTYLESHEET are converted
21 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
5 rows in table MDSYS.SDO_XML_SCHEMAS are converted
64 rows in table APEX_030200.WWV_FLOW_TEMPLATES are converted
1490 rows in table SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
166 rows in table APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES are converted
1754 rows in table APEX_030200.WWV_FLOW_STEPS are converted
2238 rows in table APEX_030200.WWV_FLOW_STEP_PROCESSING are converted
39 rows in table APEX_030200.WWV_FLOW_SHORTCUTS are converted
11 rows in table APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS are converted
54 rows in table APEX_030200.WWV_FLOW_ROW_TEMPLATES are converted
5 rows in table APEX_030200.WWV_FLOW_FLASH_CHART_SERIES are converted
10 rows in table APEX_030200.WWV_FLOW_BANNER are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
30 rows in table APEX_030200.WWV_FLOW_WORKSHEETS are converted
44 rows in table APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
105 rows in table APEX_030200.WWV_FLOW_LIST_TEMPLATES are converted
31 rows in table SYSMAN.MGMT_IP_SQL_STATEMENTS are converted
45 rows in table APEX_030200.WWV_FLOW_PROCESSING are converted
176 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

SQL>

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 801701888 bytes
Fixed Size 2257520 bytes
Variable Size 272633232 bytes
Database Buffers 520093696 bytes
Redo Buffers 6717440 bytes
Database mounted.
Database opened.

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252

#3. Upgrade noncdb from 11.2.0.4 to 12.1.0.2

-bash-4.1$ echo $ORACLE_SID
ncdb11g
-bash-4.1$
-bash-4.1$ sql

SQL> @$NEW_ORACLE_HOME/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in NCDB11G...
***************************************************************************
************************************************************
====>> ERRORS FOUND for NCDB11G <> PRE-UPGRADE RESULTS for NCDB11G < sql SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.

SQL> @$ORACLE_BASE/cfgtoollogs/ncdb11g/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2019-07-25 12:58:36 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container NCDB11G
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
**********************************************************************
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
*************************************************
************* Fixup Summary ************

4 fixup routines generated INFORMATIONAL messages that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************

-bash-4.1$ . oraenv
ORACLE_SID = [ncdb11g] ? orcl12102cdb
The Oracle base has been changed from /refresh/home/app/11.2.0.4/oracle to /refresh/home/app/12.1.0.2/oracle
-bash-4.1$ echo $ORACLE_HOME
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2
-bash-4.1$
-bash-4.1$ export ORACLE_SID=ncdb11g
-bash-4.1$ sql
SQL> startup upgrade pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

-bash-4.1$ cd $NEW_ORACLE_HOME/rdbms/admin/
-bash-4.1$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base = /refresh/home/app/12.1.0.2/oracle

Analyzing file catupgrd.sql
Log files in /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_14530.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
SQL Process Count = 0
New SQL Process Count = 4

------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1
Time: 163s
Serial Phase #: 1 Files: 5 Time: 59s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 17s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 27s
Serial Phase #: 6 Files: 1 Time: 16s
Serial Phase #: 7 Files: 4 Time: 11s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 58s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 19s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 14s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 27s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 2s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 36s
Restart Phase #:20 Files: 1 Time: 0s
Serial Phase #:21 Files: 3 Time: 9s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 23 Time: 123s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 60s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 1s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 29s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 1s
Serial Phase #:34 Files: 1 Time: 5s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 1s
Serial Phase #:37 Files: 4 Time: 62s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13

Oracle Database 12.1 Post-Upgrade Status Tool 07-25-2019 14:51:06

Component Current Version Elapsed Time
Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:14:02
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:02:13
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:23
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:23
Oracle OLAP API VALID 12.1.0.2.0 00:00:26
Oracle XDK VALID 12.1.0.2.0 00:01:06
Oracle Text VALID 12.1.0.2.0 00:01:02
Oracle XML Database VALID 12.1.0.2.0 00:06:56
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:18
Oracle Multimedia VALID 12.1.0.2.0 00:03:08
Spatial UPGRADED 12.1.0.2.0 00:12:54
Oracle Application Express VALID 4.2.5.00.08 00:32:31
Final Actions 00:01:36
Post Upgrade 00:01:50

Total Upgrade Time: 01:20:44

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
Grand Total Upgrade Time: [0d:1h:22m:57s]

#After upgrade upgrade the timezone file from 14 to 18.

-bash-4.1$ cd $ORACLE_HOME/oracore/zoneinfo
-bash-4.1$ head -2 readme.txt
Current Structure version: 3
Current Content Version :18
-bash-4.1$
-bash-4.1$ sql
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(18);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 18
DST_UPGRADE_STATE PREPARE

SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;
COUNT(*)
----------
0

SQL> select count(*) from sys.dst$error_table;
COUNT(*)
----------
0

SQL> exec DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.

Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_UPGRADE(18);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
2 3 4

PROPERTY_NAME VALUE
------------------------------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE UPGRADE

SQL> col OWNER format a30
col TABLE_NAME format a30
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS XS$ROLE_GRANT NO
SYS AQ$_AQ_PROP_TABLE_L NO
SYS AQ$_ALERT_QT_L NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_L NO
SYS SCHEDULER$_FILEWATCHER_RESEND NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_L NO
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES
SYS PDB_ALERT$ NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_S NO
SYS AQ$_ALERT_QT_S NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
...........
DBSNMP MGMT_DB_FEATURE_LOG NO
WMSYS WM$WORKSPACES_TABLE$ NO

79 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
SQL>
SQL> startup pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.

Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

SQL>
SQL>
SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL>
SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> VAR numfail number
SQL>
SQL> BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/ 2 3 4 5 6 7 8 9 10 11
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> BEGIN
DBMS_DST.END_UPGRADE(:numfail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/ 2 3 4 5

An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

SQL> SQL> SQL>
SQL>
SQL>
SQL> select * from v$timezone_file;

FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 18 0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
14

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4

PROPERTY_NAME VALUE
------------------------------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
18

#Unplug non cdb(ncdb11g) (12.1.0.2)
SQL> SQL> startup open read only pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>

SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_PDB.DESCRIBE('/tmp/ncdb11g.xml');

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.

#Plugging non cdb(ncdb11g) into cdb(orcl12102cdb)

-bash-4.1$ . oraenv
ORACLE_SID = [ncdb11g] ? orcl12102cdb
The Oracle base remains unchanged with value /refresh/home/app/12.1.0.2/oracle
-bash-4.1$ sql
SQL> startup
ORACLE instance started.
Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL>
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> alter pluggable database pdb1 open ;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;

Pluggable database altered.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/tmp/ncdb11g.xml') then
3 dbms_output.put_line('compatibility');
4 else
5 dbms_output.put_line('no compatibility');
6 end if;
7 END;
8 /
compatibility

PL/SQL procedure successfully completed.

SQL> CREATE PLUGGABLE DATABASE PDB2 USING '/tmp/ncdb11g.xml' move
file_name_convert=('/refresh/home/app/11.2.0.4/oracle/oradata/ncdb11g',
'/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED

SQL> alter session set container=pdb2;

Session altered.

SQL>@?/rdbms/admin/noncdb_to_pdb.sql

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 MOUNTED

SQL> startup
Pluggable Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
SQL>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/undotbs01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/system01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/sysaux01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/users01.dbf

4 rows selected.

=====
[4] Tools
=====
REFERENCE LAB ==> 
Manage pluggable databases

1.) Oracle Universal Installer (OUI)

Reference
Creation of a pluggable database (PDB) during the creation of the container database (CDB) during the installation of the
Oracle software using the Oracle Universal Installer (OUI).
https://oracle-base.com/articles/12c/multitenant-create-and-configure-container-database-12cr1

2.) Database Configuration Assistant (DBCA)
Create a Pluggable Database (PDB) using the DBCA
Unplug a Pluggable Database (PDB) using the DBCA
Plugin a Pluggable Database (PDB) using the DBCA
Delete a Pluggable Database (PDB) using the DBCA
Configure a Pluggable Database (PDB) using the DBCA

3.) Manual (SQL*Plus)
Create a Pluggable Database (PDB) Manually
Unplug a Pluggable Database (PDB) Manually
Plugin a Pluggable Database (PDB) Manually
Clone a Pluggable Database (PDB) Manually
Clone a Pluggable Database (PDB) Manually (Metadata-Only : NO DATA)
Delete a Pluggable Database (PDB) Manually

4.) SQL Developer
5.) EM Express Edition
6.) EM Cloud Control

===================
Path to Documentation
===================
Database 2 Day DBA --> 2 Installing Oracle Database and Creating a Database 
--> 2.3 Creating and Managing a Database with DBCA

Database Installation Guide for Linux 
--> 10 Removing Oracle Database Software

Database Administrator’s Guide 
--> 2 Creating and Configuring an Oracle Database
--> 2.3 Creating a Database with DBCA 
--> 2.3.3 About Creating a Database with Noninteractive/Silent DBCA

Database Administrator’s Guide 
--> 2 Creating and Configuring an Oracle Database
--> 2.4 Creating a Database with the CREATE DATABASE Statement 
--> 2.4.10 Step 9: Issue the CREATE DATABASE Statement

Database Administrator’s Guide 
--> 2 Creating and Configuring an Oracle Database
--> 2.5 Specifying CREATE DATABASE Statement Clauses
--> 2.5.8 Specifying Oracle Managed Files at Database Creation

Database Administrator’s Guide 
--> 17 Using Oracle Managed Files
 --> 17.3.3 Creating Oracle Managed Files at Database Creation

Database Administrator’s Guide 
--> 37 Creating and Configuring a CDB 
--> 37.4.2 Creating a CDB with the CREATE DATABASE Statement
Database Administrator’s Guide 
--> 38 Creating and Removing PDBs with SQL*Plus

Database Administrator’s Guide 
--> 39 Creating and Removing PDBs with Cloud Control
--> 40 Administering a CDB with SQL*Plus
--> 42 Administering PDBs with SQL*Plus
--> 41 Administering CDBs and PDBs with Cloud Control

Database SQL Language Reference ---> 15 SQL Statements 
--> CREATE PLUGGABLE DATABASE

Database Upgrade Guide
3 Upgrading Oracle Database
3.1.1 Upgrading a Non-CDB Oracle Database
3.1.2 Upgrading an Earlier Release and Plugging a PDB into a CDB
3.1.3 Upgrading a Multitenant (CDB) Oracle Database
3.1.4 Upgrading a Pluggable Database (PDB)

Thank you for visiting this blog...
Advertisements