To open database at startup manually:
How did you configure your database? Did you check the option for Pluggable database? If yes, please make sure you login to PDB and not CDB.
Please read Oracle 12c Post Installation Mandatory Steps.
By default, pre-installed users like SCOTT, HR etc. resides in container database and not in pluggable database.
tnsnames.ora
Edit your tnsnames.ora file to add the PDB details. For example,
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
Open all PDBs
To open all/specific PDBs immediately after logon, create a AFTER STARTUP system level trigger in CDB.
Since, the PDBs are not open through a CDB start. Letís see :
SHUTDOWN IMMEDIATE;
STARTUP;
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
PDB$SEED READ ONLY
PDBP6 MOUNTED
So, in order to have all the PDBs automatically open, do this :
Do, ìSQLPLUS / AS SYSDBAî, and then execute :
CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END open_pdbs;
/
1- Unplugging the PDB
To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.
a) connect to CDB and close PDB
sqlplus / as sysdba
SQL> alter pluggable database PDB1OR12C close immediate;
Pluggable database altered.
b) Unplug the closed PDB and then specify the path and name of the XML file.
SQL> alter pluggable database PDB1OR12C unplug into ‘D:\APP\INAM\ORADATA\OR12C\PDBOR12C\PDB1OR12C.xml’;
Pluggable database altered.
c) Drop the closed PDB and keep the data files.
SQL> drop pluggable database PDB1OR12C keep datafiles;
Pluggable database dropped.
d) Verify the status of the unplugged PDB.
SQL> select pdb_name, status from cdb_pdbs where pdb_name = ‘PDB1OR12C’;
no rows selected
The unplugging operation makes changes in the PDB data files to record that the PDB was properly and successfully unplugged. Because the PDB is still part of the CDB, you can back it up in Oracle Recovery Manager (Oracle RMAN). This backup provides a convenient way to archive the unplugged PDB. After backing it up, you then remove it from the CDB catalog. But, of course, you must preserve the data files for the subsequent plugging operation.
2- Plugging the PDB into the Same or another CDB
NOCOPY Method
a) Before starting the plugging operation, make sure that the to-be-plugged-in PDB is compatible with the new host CDB. Execution of the PL/SQL block raises an error if it is not compatible.
SQL> set serveroutput on
SQL> DECLARE
2 compatible BOOLEAN := FALSE;
3 BEGIN
4 compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
5 pdb_descr_file => ‘D:\app\Inam\oradata\or12c\pdbor12c\PDB1OR12C.xml’);
6 if compatible then
7 DBMS_OUTPUT.PUT_LINE(‘Is pluggable PDB1OR12C compatible? YES’);
8 else DBMS_OUTPUT.PUT_LINE(‘Is pluggable PDB1OR12C compatible? NO’);
9 end if;
10 END;
11 /
Is pluggable PDB1OR12C compatible? YES
PL/SQL procedure successfully completed.
b) Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy.
SQL> create pluggable database pdb_plug_nocopy using ‘D:\APP\INAM\ORADATA\OR12C\PDBOR12C\PDB1OR12C.xml’
2 NOCOPY
3 TEMPFILE REUSE;
Pluggable database created.
This operation lasts a few seconds. The original data files of the unplugged PDB now belong to the new plugged-in PDB in the new host CDB. A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE_REUSE clause is required
select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
PDB$SEED NORMAL
PDB_PLUG_NOCOPY NEW
select open_mode from v$pdbs where name=’PDB_PLUG_NOCOPY’;
OPEN_MODE
MOUNTED
SQL> alter pluggable database PDB_PLUG_NOCOPY open;
Pluggable database altered.
SQL> select name from v$datafile where con_id=3;
COPY Method
a) Close the PDB
SQL> alter pluggable database pdb_plug_nocopy close immediate;
b) Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
eg; D:\app\Inam\oradata\or12c\pdb_plug_copy
c) Unplug PDB
SQL> alter pluggable database pdb_plug_nocopy unplug into ‘D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_nocopy.xml’;
Pluggable database altered.
c) Drop PDB keeping datafiles
SQL> drop pluggable database pdb_plug_nocopy keep datafiles;
Pluggable database dropped.
e) Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.
SQL> create pluggable database pdb_plug_copy using ‘D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_nocopy.xml’
2 COPY
3 FILE_NAME_CONVERT=(‘D:\app\Inam\oradata\or12c\pdbor12c’,’D:\app\Inam\oradata\or12c\pdb_plug_copy’);
Pluggable database created.
f) Verify the status and open mode of the plugged PDB.
select pdb_name, status from cdb_pdbs where pdb_name=’PDB_PLUG_COPY’;
PDB_NAME STATUS
PDB_PLUG_COPY NEW
SQL> alter pluggable database PDB_PLUG_COPY open;
Pluggable database altered.
select name from v$datafile where con_id=3
NAME
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SYSTEM01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SYSAUX01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\EXAMPLE01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\LDATA01.DBF
MOVE Method
a) Close PDB
SQL> alter pluggable database pdb_plug_copy close immediate;
Pluggable database altered.
b) Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
eg;D:\app\Inam\oradata\or12c\pdb_plug_move
c) Unplug PDB
SQL> alter pluggable database pdb_plug_copy unplug into ‘D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_copy.xml’;
Pluggable database altered.
d) Drop PDB keeping datafiles
SQL> drop pluggable database pdb_plug_copy keep datafiles;
Pluggable database dropped.
e) Plug the PDB into the CDB and move the data files to a new location.
SQL> create pluggable database pdb_plug_move using ‘D:\app\Inam\oradata\or12c\pdbor12c\pdb_plug_copy.xml’
2 MOVE
3 FILE_NAME_CONVERT=(‘D:\app\Inam\oradata\or12c\pdb_plug_copy’,’D:\app\Inam\oradata\or12c\pdb_plug_move
‘);
Pluggable database created.
You can use AS CLONE clause also.
create pluggable database pdb_plug_move
AS CLONE using ‘D:\app\Inam\oradata\or12c\pdbor12c\pdb_plug_copy.xml’
MOVE
FILE_NAME_CONVERT=(‘D:\app\Inam\oradata\or12c\pdb_plug_copy’,’D:\app\Inam\oradata\or12c\pdb_plug_move’);
f) Verify the status and open mode of the plugged PDB.
select pdb_name, status from cdb_pdbs where pdb_name=’PDB_PLUG_MOVE’;
select open_mode from v$pdbs where name=’PDB_PLUG_MOVE’
