Connecting to CDB(Container database) and Creating my_pdb(pluggable database)
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 2 04:33:48
2013
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
SQL> show parameter db_create
NAME TYPE
VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set
db_create_file_dest='/u01/app/oracle/oradata';
System altered.
SQL> desc cdb_pdbs
Name Null?
Type
-----------------------------------------
-------- ----------------------------
PDB_ID NOT NULL NUMBER
PDB_NAME NOT NULL VARCHAR2(128)
DBID NOT NULL NUMBER
CON_UID NOT NULL NUMBER
GUID RAW(16)
STATUS VARCHAR2(13)
CREATION_SCN NUMBER
CON_ID NUMBER
SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDB1
PDB$SEED
PDB2
PDB_RAN
Creating Pluggable database now:
SQL> create pluggable database my_pdb admin user app
identified by app roles = (DBA);
Pluggable database created.
Open pluggable database
SQL> alter pluggable database my_pdb open;
Pluggable database altered.
TO confirm the result after pluggable database created, check my_pdb is in NORMAL status
SQL> select
v.Name,
v.Open_Mode,
Nvl(v.Restricted, 'n/a') "Restricted",
d.Status
from v$PDBs v inner join DBA_PDBs d
using (GUID)
order by v.Create_SCN
2 3 4
5 6 7
8 ;
NAME OPEN_MODE Res
STATUS
------------------------------ ---------- --- -------------
PDB$SEED READ ONLY NO
NORMAL
PDB1 READ WRITE NO NEEDS SYNC
PDB2 MOUNTED n/a
NORMAL
PDB_RAN MOUNTED n/a
NORMAL
MY_PDB READ WRITE NO NORMAL
Also we can check from cdb_pdbs
SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDB1
PDB$SEED
PDB2
PDB_RAN
MY_PDB
List all datafiles and tempfiles related to all container databases including root and pluggable database with following query
SQL>with Containers as (
select PDB_ID Con_ID, PDB_Name Con_Name from DBA_PDBs
union
select 1 Con_ID, 'CDB$ROOT' Con_Name from Dual)
select
Con_ID,
Con_Name "Con_Name",
Tablespace_Name "T'space_Name",
File_Name "File_Name"
from CDB_Data_Files inner join Containers using (Con_ID)
union
select
Con_ID,
Con_Name "Con_Name",
Tablespace_Name "T'space_Name",
File_Name "File_Name"
from CDB_Temp_Files inner join Containers using (Con_ID)
order by 1, 3
CON_ID
Con_Name T'space_Name
File_Name
---------- ---------- ------------------------------
--------------------------------------------------
1 CDB$ROOT
SYSAUX /u01/app/oracle/oradata/cdb1/sysaux01.dbf
1 CDB$ROOT
SYSTEM /u01/app/oracle/oradata/cdb1/system01.dbf
1 CDB$ROOT
TEMP /u01/app/oracle/oradata/cdb1/temp01.dbf
1 CDB$ROOT
UNDOTBS1 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
1 CDB$ROOT
USERS /u01/app/oracle/oradata/cdb1/users01.dbf
2 PDB$SEED
SYSAUX /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
2 PDB$SEED
SYSTEM
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
2 PDB$SEED
TEMP
/u01/app/oracle/oradata/cdb1/pdbseed/pdbseed_temp0
1.dbf
3 PDB1
APEX_2594430521661121 /u01/app/oracle/oradata/cdb1/pdb1/APEX_25944305216
61121.dbf
3 PDB1
EXAMPLE
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf
3 PDB1
SYSAUX
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
3 PDB1
SYSTEM /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
3 PDB1
TEMP
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_temp01.dbf
3 PDB1
USERS
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_us
ers01.dbf
6 MY_PDB
SYSAUX /u01/app/oracle/oradata/CDB1/D95A5408497D0AD3E0450
00000000001/datafile/o1_mf_sysaux_8onr8rpw_.dbf
6 MY_PDB
SYSTEM
/u01/app/oracle/oradata/CDB1/D95A5408497D0AD3E0450
00000000001/datafile/o1_mf_system_8onr8rmp_.dbf
6 MY_PDB
TEMP
/u01/app/oracle/oradata/CDB1/D95A5408497D0AD3E0450
00000000001/datafile/o1_mf_temp_8onrfs2z_.dbf
Rows selected.
Setting the Open_Mode for a PDB:
To open all pluggable database at one(must be from root):
SQL> alter pluggable database all open;
Pluggable database altered.
This statement closes all the PDBs in the CDB:
SQL> alter pluggable database all close;
Pluggable database altered.
When a CDB instance starts up, its
PDBs are not automatically opened, so we could create a logon Trigger.
trigger Sys.After_Startup
after startup on database
begin
execute immediate 'alter pluggable database all open';
end After_Startup;
Create PDB as a clone of an existing PDB in the same CDB
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 2 05:33:50
2013
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select pdb_name,status from cdb_pdbs;
SQL> /
PDB_NAME STATUS
---------- -------------
PDB1 NEEDS SYNC
PDB$SEED NORMAL
PDB2 NORMAL
PDB_RAN NORMAL
MY_PDB NORMAL
Close my_pdb and open in read only mode so that we can make a clone of it
SQL> alter pluggable database my_pdb close;
Pluggable database altered.
SQL> alter pluggable database my_pdb open read only;
Pluggable database altered.
Create a clone
SQL> create pluggable database my_clone from my_pdb;
Pluggable database created.
SQL> alter pluggable database my_pdb close;
Pluggable database altered.
SQL> alter pluggable database my_pdb open;
Pluggable database altered.
SQL> alter pluggable database my_clone open;
Pluggable database altered.
Unplug My_PDB from cdb1
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [cdb2] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 2 07:29:41
2013
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
SQL> select pdb_name,status from cdb_pdbs;
PDB_NAME STATUS
---------- -------------
PDB1 NEEDS SYNC
PDB$SEED NORMAL
PDB2 NORMAL
PDB_RAN NORMAL
MY_PDB NORMAL
MY_CLONE NORMAL
6 rows selected.
SQL> alter pluggable database my_pdb unplug into
'/u01/app/oracle/oradata/my_pdb.xml';
alter pluggable database my_pdb unplug into
'/u01/app/oracle/oradata/my_pdb.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database MY_PDB is not closed on all instances.
SQL> alter pluggable database my_pdb close immediate;
Pluggable database altered.
SQL> alter pluggable database my_pdb unplug into
'/u01/app/oracle/oradata/my_pdb.xml';
Pluggable database altered.
NuoDB is the first and only emergent database that is 100% SQL compliant, guarantees ACID transactions, and scales out/in elastically on decentralized computing resources in the cloud, on-premises, or both. It is highly resilient, requires minimal cloud database administration, guarantees the integrity of transactions, and delivers high performance at web-scale with highly efficient and flexible resource utilization.
ReplyDelete