Adopting NON-CDB to CBD

This article describe steps to Adopt non-container database 12c to container 12c 

[oracle@test12c Desktop]$ ps -ef | grep pmon

oracle    3230     1  0 04:04 ?        00:00:09 ora_pmon_db12c
oracle   12112     1  0 08:20 ?        00:00:07 ora_pmon_NonCDB
oracle   29621  3203  0 18:47 pts/1    00:00:00 grep pmon

Where :
db12c : Container database
NonCDB : Non Container Database
Let Start

[oracle@test12c Desktop]$ export ORACLE_SID=NonCDB

SQL> select instance_name from v$instance ;
INSTANCE_NAME
—————-
NonCDB

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area  939495424 bytes
Fixed Size    2295080 bytes
Variable Size  348130008 bytes
Database Buffers  583008256 bytes
Redo Buffers    6062080 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Run the below procedure to generate the manifest file. 

SQL> exec dbms_pdb.describe(pdb_descr_file=>’/u01/noncdb.xml’);

PL/SQL procedure successfully completed.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

 I already working on the same host so no need to copy datafiles,if you are not working on same host copy datafiles to same location.

Let’s Work On Container Database .

after copy datafiles to folder, On Os Level , Create directory to use file_name_convert.

[oracle@test12c oradata]$ mkdir newnoncdb
[oracle@test12c newnoncdb]$ pwd
/u01/app/oracle/oradata/newnoncdb

[oracle@test12c Desktop]$ export ORACLE_SID=db12c
[oracle@test12c Desktop]$ sqlplus / as sysdba

 

SQL> create pluggable database non_cdb as clone
  2  using ‘/u01/noncdb.xml’
  3  file_name_convert=(‘/u01/app/oracle/oradata/NonCDB’,’/u01/app/oracle/oradata/newnoncdb’) copy;

Pluggable database created.

 Where

  1.  using ‘/u01/noncdb.xml’ : file that should be generated using dbms_pdb.describe
  2. ‘/u01/app/oracle/oradata/NonCDB’ : location for Copied Non_CDB datafile
  3. ,’/u01/app/oracle/oradata/newnoncdb’ : location for created directory.

SQL> alter pluggable database non_cdb open ;        
Pluggable database altered.

SQL> alter pluggable database non_cdb close ;
Pluggable database altered.

SQL> alter pluggable database non_cdb open ;
Pluggable database altered.

NAME       OPEN_MODE
—————————— ———-
PDB$SEED       READ ONLY
NEWDB_PLUG MOUNTED
NEW_PLUG_COPY MOUNTED
NEW_PLUG_MOVE MOUNTED
NON_CDB       READ WRITE

Now you plugged NON-CDB to Container Database successfully without any problems and it will works fine.
the below step is optional but recommanded for production to move all thing from non-cdb to cdb. ( specially for upgrade 12c later ) 

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

OR 

[oracle@test12c newnoncdb2]$ sqlplus sys/sys@test12c:1521/Non_cdb as sysdba  ;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql   

Thank you
Osama Mustafa

12c Auditing Tutorial

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

Another New Features 12c : generated as identity / Sequence Replacement

in old version of oracle database if you want to create automatic generated number you have to create sequence and use attribute nextval.

But with oracle database 12c this concept is changed new features add when you create table called generated as identity.

Check the Below Demonstration which explain this new features :

SQL> create table test (test_id number generated as identity , test_name varchar2(20));

SQL> desc test ;
 Name   Null?      Type
 —————————————–   ——– ————–
 TEST_ID   NOT      NULL NUMBER
 TEST_NAME          VARCHAR2(20)

SQL> insert into test values (1,’osama’);
insert into test values (1,’osama’)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

The TEST_ID Column will be inserting automatically  no need to use in insert command.

SQL> insert into test (TEST_NAME) values ( ‘Jennifer’ );
1 row created.

SQL> select * from test ;
   TEST_ID TEST_NAME
———- ——————–
1       Jennifer 

Check another example :

SQL> create table test2 (TEST_ID NUMBER generated as identity (start with 1 increment by 1 cache 30 order), TEST_NAME varchar2(20));
Table created.

SQL> insert into test2 (test_name) values (‘JENNIFER’);
SQL> insert into test2 (test_name) values (‘STEVE’);
SQL> insert into test2 (test_name) values (‘USER’);

SQL> select * from test2 ;

   TEST_ID TEST_NAME
———- ——————–
1 JENNIFER
2 STEVE
3 USER

Thank you 
Osama mustafa

Move Datafiles Online 12c

As you know Oracle database 12c released Yesterday and we all still test it and learn what are the new features !!

For the first time in Oracle Database you can move objects online without offline or shutdown database which is very useful and helpful for any DBA’s

Check the example below :

SQL> select file_name from dba_data_files ;

FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/system01.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf

Let’s move system01.dbf 
SQL> alter database move datafile ‘/u01/app/oracle/oradata/db12c/system01.dbf’ to ‘/u01/system.dbf’;

SQL> select File_name from dba_data_files ;
FILE_NAME
——————————————————————————–
/u01/system.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf

Thank you
Osama mustafa