12c database – Technology Geek

Pluggable Database Tutorial Part 1 (12c)

Posted on July 4, 2013December 23, 2018 by Osama Mustafa in Database

[oracle@test12c Desktop]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 19:05:10 2013
Copyright (c) 1982, 2013, 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, Real Application Testing
and Unified Auditing options

SQL> show con_name

CON_NAME
——————-
CDB$ROOT

Where
CON_NAME : Displays the name of the Container to which you are connected when connected to a Consolidated Database. For non-consolidated database, it will return “Non Consolidated”.

SQL> show con_id

CON_ID
————
1

Where
CON_ID : Displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0.

Now I want to check how Name for my my pluggable database , while installation i chosen five container with prefix db_

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
db_1 3
db_2 4
db_3 5
db_4 6
db_5 7
9 rows selected.

Listener will look like the below :

Example how to connect Container , In my case i didn’t create service in tnsnames.ora i am using easy connect :

SQL> conn sys/sys@localhost:1521/db_1 as sysdba
Connected.

SQL> show con_name

CON_NAME
—————-
DB_1

SQL> show con_id

CON_ID
————
3

Work On Pluggable Database :

After create Container and enable pluggable database , we need to add new one since container empty.

Under /u01/app/oracle/oradata ,create new folder

[oracle@test12c db12c]$ mkdir test
[oracle@test12c db12c]$ chmod -R 775 test

Sqlplus / as sysdba

SQL > create pluggable database TEST admin user admin identified by admin
file_name_convert= (‘/u01/app/oracle/oradata/db12c/pdbseed/’,’/u01/app/oracle/oradata/db12c/test/’);

Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME Status
———————– ————
PDB$SEED NORMAL
TEST NEW

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
TEST 3

Now Con_id=3 , Most of Oracle Data Dictionary contains new_column called con_id , to check datafile related to new pluaggable database :

SQL> select name from v$datafile where con_id=3 ;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf

Manage Oracle Container and Pluggable Database :

If you need to shutdown container, it will not be different as before :

SQL> show con_name
CON_NAME
——————————
CDB$ROOT

SQL> shutdown ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup ;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.

Check status for PDB :

SQL>select name, open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

If you the above Status for Test Database you will see it as mounted state which mean we cannot create anything yet on database, Let’s Open it

SQL> alter pluggable database TEST open ;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST READ WRITE

The Same for close Option

SQL> alter pluggable database TEST close immediate ;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

Now you can open/close all pluggable database :

SQL > Alter pluggable database all Open;
SQL > Alter pluggable database all close ;

SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=3 ;

TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3

to get data file

SQL> select file_name, con_id from cdb_data_files where con_id=3 ;NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf

SQL> select file_name, con_id from cdb_temp_files where con_id=3;

FILE_NAME CON_ID
——————————————
/u01/app/oracle/oradata/db12c/gls/pdbseed_temp01.dbf 3

If you do the below query to create tablespace, it will not be created under TEST database, therefore it will be created on root :

SQL > create tablespace cdata datafile ‘/u01/app/oracle/oradata/db12c/gls/test.dbf’ SIZE 30M;

SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSTEM 2
TEMP 2
SYSAUX 2
SYSTEM 3
SYSAUX 3
TEMP 3
12 rows selected.

Same for temp tablespace :

SQL> create temporary tablespace root_temp tempfile ‘/u01/app/oracle/oradata/db12c/temp_01.dbf’ SIZE 30M;

If you need to create Tablespace in pluggable database follow the below , you have to options

connect to pluggable database in our case test using tnsnames.ora or easy connect

connect sys/sys@localhost:1521/test

alter session command line

alter session set container=

SQL> alter session set container=TEST;Session altered.

SQL> create tablespace pdb_test datafile ‘/u01/app/oracle/oradata/db12c/TEST/test_pdb.dbf’ SIZE 20M;
Tablespace created.

SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3
PDB_TEST 3

Same for temporary tablespace, next post i will provide another manage for pluggable database.

Also I Upload this tutorial On SlideShare Here

Thank you

Osama mustafa

Reference :
1- oracle Documentation Here
2- Oracle Documentation Here

TEMP UNDO TABLESPACE : Another 12C Feature

Posted on July 1, 2013December 23, 2018 by Osama Mustafa in Database

Testing is not finished yet , Everyday working on database 12C , Lot Of New Features , TEMP_UNDO_TABLESPACE one of these new features, In older Oracle Versions Temp Tables are stored in Undo Tablespace, But now with #DB12c you can enable Parameter TEMP_UNDO_TABLESPACE by Default this parameter set to FALSE But in Dataguard 12c this parameter is set to TRUE by default.

The Benefit of Using Temp Undo Tablespace :

reduce the amount of using undo tablespace
in this case performance improved because less data will be written on redolog.
Temp Undo Tablespace support DML

SQL> show parameter TEMP_UNDO_ENABLED;

NAME TYPE VALUE
———————————— ———– ——-
temp_undo_enabled boolean FALSE

You Don’t have to reset Database to Enable this Parameter because it’s working on Session level

SQL> alter session set temp_undo_enabled=TRUE ;
Session altered.

also you set this parameter on database level :

SQL> alter system set temp_undo_enabled=true;
System altered.

SQL> show parameter temp_undo_enabled ;

NAME TYPE VALUE
———————————— ———– ——-
temp_undo_enabled boolean TRUE

View Related to temp undo tablespace :

SQL> desc V$TEMPUNDOSTAT;
Name Null? Type
—————————————– ——– —————————-
BEGIN_TIME DATE
END_TIME DATE
UNDOTSN NUMBER
TXNCOUNT NUMBER
MAXCONCURRENCY NUMBER
MAXQUERYLEN NUMBER
MAXQUERYID VARCHAR2(13)
UNDOBLKCNT NUMBER
EXTCNT NUMBER
USCOUNT NUMBER
SSOLDERRCNT NUMBER
NOSPACEERRCNT NUMBER
CON_ID NUMBER

12c Auditing Tutorial

Posted on June 28, 2013December 23, 2018 by Osama Mustafa in Database

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 17:07:52 2013
Copyright (c) 1982, 2013, 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, Real Application Testing
and Unified Auditing options

New Auditing came with oracle 12c called Unified Auditing which is ” enables selective and effective auditing inside the Oracle database using policies and conditions. The new policy based syntax simplifies management of auditing within the database and provides the ability to accelerate auditing based on conditions. For example, audit policies can be configured to audit based on specific IP addresses, programs, time periods, or connection types such as proxy authentication. In addition, specific schemas can be easily exempted from auditing when the audit policy is enabled.”

You can check here.

regarding to oracle Documentation with this kind of new audit you can capture :

Audit records (including SYS audit records) from unified audit policies and AUDIT settings

Fine-grained audit records from the DBMS_FGA PL/SQL package
Oracle Database Real Application Security audit records
Oracle Recovery Manager audit records
Oracle Database Vault audit records
Oracle Label Security audit records
Oracle Data Mining records
Oracle Data Pump
Oracle SQL*Loader Direct Load

For More Information Read Oracle Documentation Here

With Show parameter auditing you will not see value for unified auditing

SQL> show parameter audit ;
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/app/oracle/admin/db12c/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_siz integer 1048576

To check Value you need to see v$option table

select value from v$option where PARAMETER = ‘Unified Auditing’;
PARAMETER VALUE
—————————————————————-
Unified Auditing FALSE

if you set this parameter to OS level, check $ORACLE_BASE/audit which the new folder to save all the information , when you create database , oracle uses new feature for auditing called Mixed mode from the name you can see there’s more than one auditing in this mode , One : old mode that we already know in oracle 10g,11g and new mode that called unified auditing.

Auditing Traditional way can be control using AUDIT_TRAIL parameter , the new Way can be determine using Audit Policy called ORA_SECURECONFIG ( Policy create already in database ) and to enable Unified audit you have to enable at least one policy. Only sysdba can do that or someone have two role AUDIT_ADMIN,AUDIT_VIEWER. i mention before on my blog for 12c new feature how to use audit policy and enable,disable and drop them.

This is only small section for what ORA_SECURECONFIG contain and what can audit :

select POLICY_NAME, AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where policy_name = ‘ORA_SECURECONFIG’ order by 2 ;

POLICY_NAME AUDIT_OPTION
——————– —————————————-
ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG ALTER ANY PROCEDURE
ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG ALTER ANY TABLE
ORA_SECURECONFIG ALTER DATABASE
ORA_SECURECONFIG ALTER DATABASE LINK
ORA_SECURECONFIG ALTER PLUGGABLE DATABASE
ORA_SECURECONFIG ALTER PROFILE
ORA_SECURECONFIG ALTER ROLE
ORA_SECURECONFIG ALTER SYSTEM
ORA_SECURECONFIG ALTER USER
ORA_SECURECONFIG AUDIT SYSTEM
ORA_SECURECONFIG CREATE ANY JOB
ORA_SECURECONFIG CREATE ANY LIBRARY
ORA_SECURECONFIG CREATE ANY PROCEDURE
ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG CREATE ANY TABLE
ORA_SECURECONFIG CREATE DATABASE LINK
ORA_SECURECONFIG CREATE DIRECTORY
ORA_SECURECONFIG CREATE EXTERNAL JOB
ORA_SECURECONFIG CREATE PLUGGABLE DATABASE
ORA_SECURECONFIG CREATE PROFILE
ORA_SECURECONFIG CREATE PUBLIC SYNONYM
ORA_SECURECONFIG CREATE ROLE
ORA_SECURECONFIG CREATE SQL TRANSLATION PROFIL

Check if this Audit Policy set as default for Database :

SQL> select POLICY_NAME from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name = ‘ORA_SECURECONFIG’;

POLICY_NAME
——————–
ORA_SECURECONFIG

Check this to see by default oracle 12c enable audit which is very useful :

select action_name, dbusername from unified_audit_trail where dbusername=’OSAMA’

ACTION_NAME DBUSERNAME
——————– ——————–
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
UPDATE OSAMA
LOGON OSAMA
LOGON OSAMA
LOGON OSAMA
LOGON OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA

Unified Contain new features to monitor RMAN command , Expdp,impdp and other new features but to enable it ( set value to true ) you have to relink oracle lib after shutdown all services using the below command , cd $ORACLE_HOME/rdbms/lib and fire :

make -f ins_rdbms.mk uniaud_on ioracle

SQL> select value from v$option where PARAMETER = ‘Unified Auditing’;

VALUE
———-
TRUE

Let’s Test Some New Features and Audit RMAN :

SQL> create audit policy expdp_aduit actions component=datapump export;
Audit policy created.

SQL> audit policy expdp_aduit;
Audit succeeded.

Check if our audit policy is enabled :

select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like ‘%EXPDP%’;

USER_NAME POLICY_NAM ENABLED_ SUC FAI
———- ———- ——– — —
ALL USERS EXPDP_AUDIT BY YES YES

Make sure you create directory , Grant privileges on this directory and run expdp command ( system user )

[oracle@test12c dump]$ expdp directory=dump logfile=audit.log dumpfile=osama_schema schemas=osama

this transaction will exists on memory until background process flash it to disks , so before check the tables i will execute package that ensure to do this immediately ( no waiting )

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
PL/SQL procedure successfully completed.

check the user who make expdp

SQL> select DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1
from UNIFIED_AUDIT_TRAIL
where DP_TEXT_PARAMETERS1 is not null;

SYSTEM
MASTER TABLE: “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ , JOB_TYPE: EXPORT, METADATA_JOB_
MODE: SCHEMA_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPT
IONS: 0, DUMPER DIRECTORY: NULL REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITIO
N OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR
UE, JOB_RESTARTED: FALSE

For test case , i remove users tablespace from os after take backup using rman ( database should be in archive mode ) , by default Audit For RMAN is enable no need to do anything ,like the following :

RMAN > backup tablespace users ;

On OS Level :

rm /u01/app/oracle/oradata/db12c/users01.dbf

Back to RMAN :

RMAN> alter tablespace users offline immediate;
using target database control file instead of recovery catalog
Statement processed

RMAN> restore tablespace USERS;
RMAN> alter tablespace users online;

Now Check Audit :

SQL> select DBUSERNAME, RMAN_OPERATION from UNIFIED_AUDIT_TRAIL where RMAN_OPERATION is not null;

DBUSERNAME RMAN_OPERATION
—————————— ——————–
SYS Recover
SYS Restore
SYS Backup
SYS Backup

Note : if nothing appear at audit table level re run

exec SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

After finished test :

SQL> noaudit POLICY EXPDP_AUDIT;
Noaudit succeeded.

SQL> select count(*) from unified_audit_trail;
COUNT(*)
———-
3334

You can clean your Audit Now Using Package Called : DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL

Thank you

Osama mustafa

Database 12c New Audit Features

Posted on June 27, 2013December 23, 2018 by Osama Mustafa in Database

Amazing Features on Oracle Database 12c , I will blog everything i tested and check in New Oracle Database 12 , This Time Audit Enhancement :

Create Audit Policy :

SQL> create audit policy test_audit privileges select any table ;
Audit policy created.

Where :

Privileges indicate to What this audit policy will do.

SQL> create audit policy test_audit_object actions select,update, lock on osama.test ;

Audit policy created.

Where

Actions indicate to DML ,DDL Commands.

Until Now , You only Create Audit Policy Without Enable

In the below way you enable audit for all users

SQL> audit policy test_audit ;

Audit succeeded.

SQL> audit policy test_audit_object ;

Audit succeeded.

Enable Audit On Some Users

SQL> audit policy test_audit by osama;

Audit succeeded.

i want to enable audit but not for all users ;

SQL> audit policy test_audit expect osama;

Audit succeeded.

also whenever successful or not successful still exists

SQL> audit policy test_audit whenever successful

Audit succeeded.

SQL> audit policy test_audit Whenever not successful ;

Audit succeeded.

Check Audit :

select POLICY_NAME,AUDIT_CONDITION from audit_unified_policies where POLICY_NAME like ‘%TEST%’;

POLICY_NAME AUDIT_CONDITION
—————– ————————–
TEST_AUDIT_OBJECT select

Check Enable Audit Policy

select policy_name,user_name from audit_unified_ENABLED_POLICIES ;

POLICY_NAME USER_NAME
—————– ————————–
TEST_AUDIT_OBJECT SYS

Disable Audit Policy :

SQL> DROP AUDIT POLICY TEST_AUDIT ;
DROP AUDIT POLICY TEST_AUDIT
*
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.

SQL> NOAUDIT POLICY TEST_AUDIT ;
Noaudit succeeded.

SQL> DROP AUDIT POLICY TEST_AUDIT ;
Audit Policy dropped.

Thank you

Osama mustafa

Enable Enterprise Express for Database 12c

Posted on June 27, 2013December 23, 2018 by Osama Mustafa in Database

Oracle Database 12c comes with new feature called Enterprise manager 12c express, this new enterprise manager control Database 12c using GUI , manager storage , performance , user, roles, accounts and others new features added , No command to start or shutdown EM 12c express by default when you start/shutdown database it will be up/down.

export ORACLE_SID
Make sure listener is up and database is registered.

sqlplus / as sysdba
SQL> show parameter dispatcher ;
NAME TYPE VALUE
———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP) (SERVICE=db12cX
DB)

Configure HTTP PORT

SQL> exec DBMS_XDB_CONFIG.setHTTPPort (8080);

Check the below pictures that describe EM Express :

DBMS_XDB.setHTTPPort is deprecated

Thank you
Osama mustafa

Database 12c Installation

Posted on June 26, 2013December 23, 2018 by Osama Mustafa in Database

1. Download the Oracle Database 12.1 Software from OTN
2. Make sure Oracle Database Software and OS are certified using https://support.oracle.com
3. Make sure of the following

Hardware

you need to configure Swap Memory And make sure you RAM is enough to avoid Out Of memory during the installation.

Software

as i mention before check certified OS with database, in my case i will use Redhat 6 update 4.

install Packages :

binutils-2.20.51.0.2-5.11.el6 (x86_64)
glibc-2.12-1.7.el6 (x86_64)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libaio-0.3.107-10.el6 (x86_64)
libXext-1.1 (x86_64)
libXtst-1.0.99.2 (x86_64)
libX11-1.3 (x86_64)
libXau-1.0.5 (x86_64)
libxcb-1.5 (x86_64)
libXi-1.3 (x86_64)
make-3.81-19.el6sysstat-9.0.4-11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
ksh <== any version of ksh is acceptable
libstdc++-devel-4.4.4-13.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (x86_64)

inside /etc/sysctl.conf

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

Use this command to restart kernel /sbin/sysctl -p

/etc/hosts

Serverip Hostname

/etc/security/limits.conf

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

/etc/pam.d/login

session required pam_limits.so

after edit OS parameter you need to create Oracle user :

groupadd -g 101 oinstall
groupadd -g 102 dba
groupadd -g 103 oper

useradd -u 100 -g oinstall -G dba,oper oracle

passwd oracle

copy media to your Server , and do the following :

chown -R oracle:oinstall /u01/database
chmod -R 775 /u0/database
mkdir -p /u01/app/oracle/product/12.1.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

now start installation by ./runInstaller