Saturday, June 29, 2013

Some Of Basic Questionnaire For Newly Release Oracle Database 12c


1) What is an Pluggable database really?

Ans-> This is new feature in oracle 12.1 release with respect to database. With this release there is a parent-child relationship between databases. I.e. One database act as Container(Parent) and rest act as its child(Pluggable) databases. We can have n number of pluggable databases for one container database. Container database are the root can contains the Data dictionary as a whole. However pluggable database refer to containers database data dictionary.

Pluggable databases shares some common users with container(ROOT) database like SYS,SYSTEm users.

2) Does my pluggable database contain user datafiles as any other pre-12c database?


Ans-> Pluggable database has its own set of user datafiles and tablespaces



3) Does pluggable database contain Temporary files?

Ans-> Container database holds the temporary file as a whole and all other pluggable database shares those tempfile. But we can create tempfiles for each individual pluggable database


4) Does pluggable database contain UNDO tablespace?

Ans=> No it doesn’t however UNDO tablespace of Container share it with other pluggable database


5) Can we have pluggable database without Container database?


Ans-> No we cannot have pluggable database without Container database.




6) Can container database have no pluggable database?


Ans-> No Container also doesn’t comes without pluggable database. When we install Container database, one implicit SeeD Pluggable database created by default.




7) What is Seed database?


Ans-> Seed database is a pluggable database which comes by default with Container database. Seed database act like a template and can be used to create other non-default pluggable databases




8) Can we remove SeeD database?


Ans-> No we cannot remove it




9) Are there any background processes for Pluggable database like DBWR?


Ans-> No there are no background processes available for pluggable database like we have for any other database. Background process runs for main container database and shares these with pluggable database. So background process like DBWR belongs to container database and do its task to write dirty blocks from SGA to disk. It also writes to user datafiles which belongs to pluggable database




10) Does pluggable database have its SGA and PGA?


Ans-> No Pluggable database doesn’t have its own set of SGA and PGA. However all pluggable database shares the SGA of Container Database. So SGA of container database is shared among other pluggable database




11) Can we create user defined datafiles inside container database?


Ans-> Yes we can create user defined datafiles and tablespaces for container database but its highly recommended not to create any user tablespaces inside CDB bacuse pluggable database purpose is to hold user data




12) Can we use SeeD database for users data?


Ans-> Yes we can use it for user data




13) Do we need separate REDO logs for each pluggable database?


Ans-> No, we cannot create separate REDO logfiles for each pluggable database because REDO belongs to CDB and there is only one set of REDO logs belongs to CDB





14) Does Pluggable database contain controlfiles?


Ans-> No. Controlfiles belongs to CDB only





15) What tablespaces are created by default with Pluggable database?


Ans-> SYSTEM, SYSAUX





16) Do we need separate Listener for each pluggable database


Ans-> There is only one listener.ora file for CDB as a whole. So all other pluggable database uses common listener. Therefore listener.ora and tnsnames.ora reside under CDB




17) Does PDB has alertlog and traces by itself?


Ans-> There is only one alert.log for all PDB which is maintained under CDB. Pluggable database sends message and alerts to CDB’s alert.log






Reference - MOS - FAQ Pluggable database

Create Pluggable database and few other admin stuff on Oracle database 12c

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.

 Hope this helps!