12c Oracle Support Document until now

Second day for 12c database since it was  officially published, There is no much offical document from oracle https://support.oracle.com until now. But i tried to collect as much as i can and share it here 

RMAN Enhancements in Oracle 12c [ID 1534487.1]
Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]

Oracle Database 12.1 : FAQ on Queryable Patch Inventory [ID 1530108.1]
Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) [ID 1503653.1]
Changes For Oracle Database 12.1 Standalone Installation [ID 1483380.1]

Requirements for Installing Oracle Database 12.1 on Solaris 10 SPARC [ID 1517948.1]
Requirements for Installing Oracle Database 12.1 on RHEL6 or OL6 64-bit (x86-64) [ID 1529864.1]
Requirements for Installing Oracle Database 12.1 64-bit (AMD64/EM64T) on SLES 11 [ID 1519770.1]
How To Downgrade Oracle Database 12c Release 1 (12.1) To Previous Versions [ID 1516622.1]

How to Drop/Truncate Multiple Partitions in Oracle 12C [ID 1482264.1]
Oracle Database 12c Release 1 (12.1) DBUA : Understanding New Changes With All New 12.1 DBUA
[ID : NOTE:1493645.1]
RMAN RECOVER TABLE Feature New to Oracle Database 12c [ID 1521524.1]
How to Merge Multiple Partitions in Oracle 12C [ID 1482263.1]
How to Create Interval-Reference Partitioned Tables in Oracle 12c [ID 1519042.1]

Include to all above documents you can find all new features and document in one place, my friend Steve Karam on his blog (Click Here) collect all folks articles that has been posted about DB12c,  it’s really worth to take alook and share it.

Thank you
Osama mustafa


Enable Enterprise Express for Database 12c

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

Security Features 12c

As Any Newer Version of database oracle Enhance Security This time Oracle create new users for administration purpose

Separate user duties : 

SQL> select username from dba_users where username like ‘%SYS%’;

USERNAME
———————————-
SYS  : Super user
SYSKM : Key magament tasks
SYSDG : Data Guard Managment
SYSBACKUP : backup management

DBMS_PRIVILEGE_CAPTURE

The Privilege Analysis feature allows you to: 
  • Define a privilege capture
  • Start a privilege analysis during a period of time
  • Delete capture analysis
The Capture can be Done on Three level :
  • User
  • Role
  • Context
to understand this new package check the below examples :
sqlplus / as sysdba
SQL> create user test identified  by test ;
SQL> grant create session to test ;
Grant succeeded.
SQL> grant select any table to test ;
Grant succeeded.
Create new Capture using this package :

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( –
        name           =>  ‘test_capture’, –
        description    =>  ‘Capture_all_thing’, –
        type           =>   dbms_privilege_capture.g_database)

Where 

Name : Package name
Description : What this package fo
type : depend on level users ( g_database ) , role (g_role )  , Context : (g_role_and_context)

we need to enable this package to start capture :

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => ‘test_capture’);
 PL/SQL procedure successfully completed.

until everything goes fine now to start capture you have to logout from sqlplus and start do what you need to do.
SQL> conn test/test ;
Connected.
SQL> select * from osama.test ;
no rows selected
SQL> select * from osama.test2 ;
   TEST_ID TEST_NAME
———- ——————–
1 JENNIFER
2 STEVE
3 USER
again reconnect as / as sysdba to disable capture then generate results like the following :
SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => ‘test_capture’);
PL/SQL procedure successfully completed.
SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => ‘test_capture’);
PL/SQL procedure successfully completed.

SQL> select username, object_owner, object_name, obj_priv
from   dba_used_objprivs
where  username in (‘OSAMA’, ‘TEST’) 

USERNAME   OBJECT_OWNER OBJECT_NAME       OBJ_PRIV
———- ———— —————————— ————————-
TEST   SYS ORA$BASE                                      USE
TEST   SYS DBMS_APPLICATION_INFO       EXECUTE
TEST   SYS DUAL                                              SELECT
TEST   SYS DUAL                                               SELECT
TEST   SYSTEM PRODUCT_PRIVS                     SELECT
Drop Capture :
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => ‘test_capture’)
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

New Features 12c EXPDP

While i am testing Oracle 12c i notice new features in expdp , which you can export view like the following :

SQL> create table test ( id number);
Table created.

SQL> create view test_vw as select * from test ;
View created.

SQL> create directory dump as ‘/u01/dump’;
Directory created.

SQL> grant read,write on directory dump to osama ;
Grant succeeded.

[oracle@test12c u01]$ expdp directory=dump dumpfile=test.dmp logfile=test.log VIEWS_AS_TABLES=test_vw
Export: Release 12.1.0.1.0 – Production on Wed Jun 26 18:33:52 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Username: osama
Password: 
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
Starting “OSAMA”.”SYS_EXPORT_TABLE_01″:  osama/******** directory=dump dumpfile=test.dmp logfile=test.log VIEWS_AS_TABLES=test_vw 
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported “OSAMA”.”TEST_VW”                              0 KB       0 rows
Master table “OSAMA”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for OSAMA.SYS_EXPORT_TABLE_01 is:
  /u01/dump/test.dmp
Job “OSAMA”.”SYS_EXPORT_TABLE_01″ successfully completed at Wed Jun 26 18:34:15 2013 elapsed 0 00:00:17
I dropped the view 

SQL> drop view Test_vw ;
View dropped.
impdp directory=dump dumpfile=test.dmp logfile=test.log VIEWS_AS_TABLES=test_vw


Thank you 
Osama mustafa

12c new features

Tom Kyte , Talk about 12c new features LETS START :

1) “With” clause can define PL/SQL functions

2) Improved defaults, including Default col to a sequence or “default if (on) null”.  Or always use a generated as an identity (with optional sequence def info).  Or Metadata-only defaults (default on an added column). 

3) Bigger varchar2, nvarchar2, raw -up to 32K.  But follows rules like LOB, if over 4K will be out of line. (max_SQL_String_Size init param)

4) TopN and Pagenation queries using the ‘OFFSET’ clause + optional ‘FETCH next N rows’ in SELECT.  Eg: SELECT … FROM t ORDER BY y FETCH FIRST 5 ROWS

5) Row pattern matching using the “MATCH_RECOGNIZE” clause.  Gonna take a while to get this one.

6) Partitioning improvements including ASYNC Global Index maintenance (includes new jobs to do work ‘later’), cascade truncate & exchange, multi ops in a single DDL, online partition moves (no RDBMS_REDEFINITION), “interval + reference” partitioning.

7) Adaptive execution plans, which sets thresholds and allows execution plans to switch if threshold is exceeded.  (Also ‘gather_plan_statistics’ hint.)  Shown by ‘Statistics Collector’ steps in trace/tkprof.

8) Enhanced statistics. Dynamic sampling goes to ‘eleven’, turning it persistent.  New histograms: hybrid (for more than 254 distinct values, instead of height-balanced) and top.  Stats gathered on data loads automatically. (By the way, don’t regather stats if not needed.)  Session private statistics for GTTs. 

9) UNDO for temporary objects, managed in TEMP, which eliminates REDO on the permanent UNDO. (ALTER SESSION/SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE)

10) Data optimization, or Information Lifecycle Management, which detects block use – hot, medium, dormant – and allows policies in table defintion (new ILM clause) to compress or archive data after time.

11) “transaction Guard’ to preserve commit state, which includes TAF r/w transfer and restart for some types of transactions.

12) pluggable databases!  Implications too numerous to list right now.  Suffice it to say, huge resource improvements, huge consolidation possibilities.  Looking forward to reality.

Thank you
Osama mustafa