Database 12c New Audit Features

[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

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

Tag: Database 12c New Audit Features

Pluggable Database Tutorial Part 1 (12c)

Database 12c New Audit Features