Oracle 12c CDB&PDB basic maintenance
1. Check whether the database is a multi-tenant database (CDB)
SQL > SELECT CDB FROM V$DATABASE
CDB
YES
two。 View the current container name
The following commands can only be entered on 12c clients
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO 3 ORCLPDB READ WRITE NO
SQL > show con_name
CON_NAME
CDB$ROOT
Or
Select sys_context ('userenv',' con_name') "Container DB" from dual
3.pdb management
3.1 create a pdb database directly
SQL > CREATE PLUGGABLE DATABASE testdb
2 ADMIN USER testdbdb IDENTIFIED BY testdb
3 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100m)
4 DEFAULT TABLESPACE testdbdb DATAFILE'C:\ app\ Administrator\ virtual\ oradata\ orcl\ testdbdb_001.dbf' SIZE 25m AUTOEXTEND ON
DEFAULT TABLESPACE testdbdb DATAFILE'C:\ app\ Administrator\ virtual\ oradata\ orcl\ testdbdb_001.dbf' SIZE 25m AUTOEXTEND ON
*
An error occurred on line 4:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL > set line 1000
SQL > show parameter db_create_file_dest
NAME TYPE VALUE
Db_create_file_dest string
SQL > alter system set db_create_file_dest='C:\ app\ Administrator\ virtual\ oradata'
The system has changed.
SQL > show parameter db_create_file_dest
NAME TYPE VALUE
Db_create_file_dest string C:\ app\ Administrator\ virtual\ o
Radata
Switch to root container creation
SQL > alter session set container=cdb$root
The session has changed.
SQL > show con_name
CON_NAME
-- CDB$ROOT
SQL > CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2
2 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100m)
3 DEFAULT TABLESPACE pdb2
4 DATAFILE'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ pdb201.dbf' SIZE 250m
5 AUTOEXTEND ON 6 PATH_PREFIX ='C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2'
7 FILE_NAME_CONVERT = ('C:\ app\ Administrator\ virtual\ oradata\ orcl\ DATAFILE\ O1_MF_S
8 YSTEMB9GXSYNO.DBF', 'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ system01.dbf'
9,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ DATAFILE\ O1MFsets SYSAUXB9GXSYLX.DBF'
10,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ sysaux01.dbf'
11,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ DATAFILE\ PDBSEED_TEMP012017-12-13015
12-02-15 oradata PM.dbfcommands, temp01.dbf' C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ temp01.dbf')
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2
*
An error occurred on line 1:
ORA-65005: file name schema is missing or invalid-C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDBSEED\ SYSTEM01.DBF
SQL > set line 200
SQL > col file_name for A80
SQL > select con_id,file_name from cdb_data_files order by 1
CON_ID FILE_NAME 1 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ SYSTEM01.DBF 1 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ USERS01.DBF 1 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ UNDOTBS01.DBF 1 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ SYSAUX01.DBF 3 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ ORCLPDB\ UNDOTBS01.DBF 3 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORADATA\ ORADATA \ USERS01.DBF 3 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ ORCLPDB\ SYSTEM01.DBF 3 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ ORCLPDB\ SYSAUX01.DBF
Eight rows have been selected.
SQL > create pluggable database pdb2 admin user pdb2 identified by oracle file_name_convert= ('C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdbseed','C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2')
SQL > alter pluggable database pdb2 open read write
SQL > create TABLESPACE pdb2
2 DATAFILE'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ pdb201.dbf' SIZE 250m
3 AUTOEXTEND ON
4
The tablespace has been created.
SQL > alter user pdb2 DEFAULT TABLESPACE pdb2
The user has changed.
SQL > select con_id,file_name from cdb_data_files order by 1
CON_ID FILE_NAME 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ SYSTEM01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ SYSAUX01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ UNDOTBS01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ PDB201.DBF SQL > DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES *
An error occurred on line 1:
ORA-65025: pluggable database PDB2 is not closed on all instances.
SQL > alter pluggable database pdb2 close 2
The plug-in database has changed.
SQL > DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES
The pluggable database has been deleted.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY pdb2
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100m)
PATH_PREFIX ='C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2'
FILE_NAME_CONVERT = ('C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdbseed\ system01.dbf'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ system01.dbf'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdbseed\ sysaux01.dbf'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ sysaux01.dbf'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdbseed\ TEMP012017-12-12, 14-30-01-009, PM.dbf'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ temp01.dbf'
'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdbseed\ UNDOTBS01.DBF'
,'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ UNDOTBS01.DBF')
SQL > alter pluggable database pdb2 open read write
The plug-in database has changed.
SQL > alter session set container=pdb2
The session has changed.
SQL > create TABLESPACE pdb2 DATAFILE'C:\ app\ Administrator\ virtual\ oradata\ orcl\ pdb2\ pdb201.dbf' SIZE 250m AUTOEXTEND ON
The tablespace has been created.
SQL > alter user pdb2 DEFAULT TABLESPACE pdb2
The user has changed.
SQL > set line 1000
SQL > col file_name format A100
SQL > select con_id,file_name from cdb_data_files order by 1
CON_ID FILE_NAME 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ SYSTEM01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ SYSAUX01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ PDB2\ UNDOTBS01.DBF 4 C:\ APP\ ADMINISTRATOR\ VIRTUAL\ ORADATA\ ORCL\ PDB2\ PDB201.DBF
3.2 unplug
SQL > alter pluggable database pdb2 unplug into'c:\ pdb2.xml'; alter pluggable database pdb2 unplug into'c:\ pdb2.xml'
*
An error occurred on line 1:
ORA-65025: pluggable database PDB2 is not closed on all instances.
SQL > alter pluggable database pdb2 close
The plug-in database has changed.
SQL > alter pluggable database pdb2 unplug into'C:\ app\ Administrator\ virtual\ oradata\ pdb2.xml'
The plug-in database has changed.
After unplug, pdb can only mount but not open.
SQL > alter pluggable database pdb2 open read only
Alter pluggable database pdb2 open read only
*
An error occurred on line 1:
ORA-65086: cannot open / close pluggable database
SQL > drop pluggable database pdb2
The pluggable database has been deleted.
SQL > create pluggable database pdb2 using'C:\ app\ Administrator\ virtual\ oradata\ pdb2.xml' nocopy
The plug-in database has been created.
Silently build pdb and cdb
Dbca-silent-createDatabase-templateName C:\ app\ Administrator\ virtual\ product\ 12.2.0\ dbhome_2\ assistants\ dbca\ templates\ General_Purpose.dbc-gdbname newtest-sid newtest-characterSet UTF8-createAsContainerDatabase true-sysPassword zncg3008_ZNCG-systemPassword zncg3008ZNCG
