Sunday, June 30, 2013

Enterprise manager Performance Hub (Real Time) 12c

Enterprise manager express is 


SQL> drop user osama cascade ;
User dropped.

SQL> create user osama identified by osama;
User created.

SQL> grant select any table to osama;
Grant succeeded.

SQL> grant select any dictionary to osama ;
Grant succeeded.

SQL> grant create session to osama;
Grant succeeded.

SQL> conn osama/osama ;
Connected.
SQL> EXEC  DBMS_SQL_MONITOR.BEGIN_OPERATION ('My_First_JOB', forced_tracking => 'Y');
BEGIN DBMS_SQL_MONITOR.BEGIN_OPERATION ('My_First_JOB', forced_tracking => 'Y'); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'BEGIN_OPERATION' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

to avoid this error :

SQL> VAR myjob_id NUMBER;
SQL> EXEC :myjob_id := DBMS_SQL_MONITOR.BEGIN_OPERATION ('My_First_JOB', forced_tracking => 'Y')

PL/SQL procedure successfully completed.

Where 
myjob_id : variable
My_first_Job : Job name.

Now Check Enterprise manager express Page : 




if you check the second picture you will see if you don't stop the sql_monitor the Job will still running

SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION('My_First_JOB', :myjob_id) ;
PL/SQL procedure successfully completed.


Thank you 
Osama Mustafa

Friday, June 28, 2013

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

Thursday, June 27, 2013

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




Database 12c New Audit Features

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

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

tiny features : Truncate table in 12c

New Features with Database 12c , CASCADE Usually used with Drop or update command (10g,11g) But now with Database 12c , you can use this features with Truncate command

SQL> select * from test ;
no rows selected
SQL> desc test ;

 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST_ID   NOT NULL NUMBER
 TEST_NAME    VARCHAR2(20)
SQL> truncate table test cascade ;
Table truncated.
More Features coming up :)
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

Queryable Patch Inventory New Features 12c

Again !!! Oracle 12c Under test , every time i tested it i discover new features, while i am testing today i found new folder under $ORACLE_HOME called QOPatch , for the first time when i opened this folder i only found two files,

Check the https://support.oracle.com note :

Oracle Database 12.1 : FAQ on Queryable Patch Inventory [ID 1530108.1] 
from the name you can see it's retrieve  information  using query, and to use query you have to get access to SQL Plus, this features  allow you access to the OPatch information from within the database.  This Package called  DBMS_QOPATCH

Some of Attribute to use with this package :


  • GET_OPATCH_INSTALL_INFO
  • SET_CURRENT_OPINST
  • GET_OPATCH_LIST
  • IS_PATCH_INSTALLED
  • GET_OPATCH_DATA
  • GET_OPATCH_BUGS
  • GET_OPATCH_FILES
  • GET_OPATCH_COUNT
  • GET_OPATCH_PREQS
  • GET_OPATCH_OLAYS
  • PATCH_CONFLICT_DETECTION
  • GET_PENDING_ACTIVITY
  • GET_OPATCH_XSLT
  • GET_OPATCH_LSINVENTORY
  • GET_SQLPATCH_STATUS


Since this function used to get Opatch information within database then you have to access to SQL Plus 
Check the below examples :

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY()
--------------------------------------------------------------------------------

Another Examples :


SQL> select dbms_qopatch.GET_PENDING_ACTIVITY() from dual;
DBMS_QOPATCH.GET_PENDING_ACTIVITY()
--------------------------------------------------------------------------------



My Database still fresh and not patch yet So you will not find that much of information 

Thank you 
Osama mustafa

Wednesday, June 26, 2013

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 

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

Database 12c Installation

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













to Create Database you need to configure listener using netca and after finished successfully use dbca like below : 





I Upload the article Here
Thank you
Osama mustafa
  

Oracle Enterprise Manager unable to Start

When I checked the log Located $ORACLE_HOME/SID_HOSTNAME/sysman/log

I found the below errors :

app.ContextInitializer contextInitialized.420 - Integration Class not found

and

ERROR main: nmectla_agentctl: Error connecting to

First you need to make sure that the link in emd.properties and ports and configured right and hostname is correct, restrat dbconsole if this not works then check the below

On Host :

oracle@TEST:/u01/app/$ echo $TZ
localtime
oracle@TEST:/u01/app/$ export TZ=Etc/GMT+2
emctl config agent updateTZ
emctl resetTZ agent 

Restart Dbconsole

Emctl start dbconsole 
 Thank you
Osama mustafa

Tuesday, June 25, 2013

12c Officaly Released


Finally After Waiting Oracle 12c Available to Download
On OTN HERE
Or ON oracle edelivery Here

Thank you
Osama mustafa

Enable Debug Mode Oracle Enterprise Manager

you need to change directory to below :
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/config
under this directory you will work on

  • emd.properties
  • emagentlogging.properties
Backup two files. using cp command.

Open 

  • emagentlogging.properties
File :


change 


log4j.rootCategory=WARN, emagentlogAppender, emagenttrcAppender

TO 

log4j.rootCategory=DEBUG, emagentlogAppender, emagenttrcAppender


  • emd.properties
Change 
EMAGENT_PERL_TRACE_LEVEL=WARN

TO 

EMAGENT_PERL_TRACE_LEVEL=DEBUG

Restart dbconsole


Thank you 
Osama mustafa

Monday, June 24, 2013

Share Folder On Solaris For Window Purpose

In this article i will show how to share folder in Solaris and use this folder for Copy/Paste in windows , I will use NFS so you need to make sure it's already enable in Windows


  • Select Control Panel.
  • Select Programs.
  • Select Programs and Features.
  • Select Turn Windows Features on or off.
  • Select Services for NFS.
  • Select the check box Client for NFS and click OK.

Now On Solaris Side , Using Share command 

share [-F fstype] [ -o options] [-d ""] [resource]

Create Folder and use the below command to share 

share -F nfs -o rw -d "codereview dirs" /u01/Shared --> my Shared Folder


root@Host:/u01# cat /etc/dfs/sharetab 

/u01/hmmdb u01_hmmdb nfs sec=sys,rw codereview
/u01/Shared u01_Shared nfs sec=sys,rw
Now back to windows open Command Prompt "cmd"

and run the below command :

showmount -e Server-IP 

Exports list on :
/u01/Shared                        All Machines
/u01/hmmdb                         All Machines
Command to share on windows :
mount -o mtype=hard Server-IP:Path_for_share_folder name drive letter or *

mount -o mtype=hard server-ip:/u01/Shared Z:

Done

Thank you 
Osama mustafa

Wednesday, June 19, 2013

Oracle Java SE Critical Patch Released


"The June 2013 Oracle Java SE Critical Patch Update was released on June 18, 2013. Oracle strongly recommends applying the patches as soon as possible.

The Critical Patch Update Advisory is the starting point for relevant information. It includes the list of products affected, a summary of the security vulnerabilities, and a pointer to obtain the latest releases and patches."

June 2013 Oracle Java SE Critical Patch Update: Here


The next four scheduled dates for Oracle Java SE Critical Patch Updates are:
16 April 2013
18 June 2013
15 October 2013
14 January 2014


Thank you
Osama mustafa

Tuesday, June 18, 2013

Extend File system On Solaris Using ZFS Command

The below is demostration for extend File system On Solaris 11 :  
root@TEST:~# zfs list
NAME                      USED  AVAIL  REFER  MOUNTPOINT
oracle                    107G  10.6G  1.11M  /oracle
oracle/u01                105G  13.0G   105G  /u01
rpool                    32.2G  46.0G    39K  /rpool
rpool/ROOT               5.40G  46.0G    31K  legacy
rpool/ROOT/solaris       5.40G  46.0G  5.03G  /
rpool/ROOT/solaris/var    318M  46.0G   314M  /var
rpool/dump               1.03G  46.1G  1.00G  -
rpool/export              854K  46.0G    32K  /export
rpool/export/home         822K  46.0G    32K  /export/home
rpool/export/home/omnix   790K  46.0G   790K  /export/home/omnix
rpool/swap               25.8G  46.8G  25.0G  -
 root@TEST:~# zpool list

NAME     SIZE  ALLOC   FREE  CAP  DEDUP  HEALTH  ALTROOT
oracle   140G   105G  34.9G  75%  1.00x  ONLINE  -
rpool   79.5G  31.4G  48.1G  39%  1.00x  ONLINE  -
root@TEST:~# zpool set autoexpand=on oracle

root@TEST:~# zpool list
NAME     SIZE  ALLOC   FREE  CAP  DEDUP  HEALTH  ALTROOT
oracle   140G   105G  34.9G  75%  1.00x  ONLINE  -
rpool   79.5G  31.4G  48.1G  39%  1.00x  ONLINE  -
now you have to do the below :

root@TEST:~# zfs get volsize,reservation oracle
         NAME    PROPERTY     VALUE    SOURCE
oracle  volsize      -        -
oracle  reservation  107G     local
root@TEST:~# zfs set reservation=140G oracle/u01

          root@TEST:~# zfs get reservation oracle/u01
NAME        PROPERTY     VALUE   SOURCE
oracle/u01  reservation  140     local
--> zfs set quota=140G oracle

Finally Check df -h and size are increased :)

Thank you
Osama mustafa 

cannot unmount '/export/home/oracle': Device busy

Operating System : Solaris 11

When run the below command :
zfs destroy rpool/export/home/grid
 cannot unmount '/export/home/oracle': Device busy

Solution :

zfs unmount rpool/export/home/grid
zfs destroy rpool/export/home/grid


Thank you
Osama mustafa

Thursday, June 13, 2013

cannot mount 'rpool/u01' on '/u01': mountpoint or dataset is busy

When you try to use the below command :

zfs create -o mountpoint=/u01 rpool/u01

Error :

cannot mount 'rpool/u01' on '/u01': mountpoint or dataset is busy

The Solution :

  • First destroy rpool/u01
  • zfs create rpool/u01
  • zfs set mountpoint=/u01 rpool/u01
  • mkdir /u01
  • zfs mount /u01


Thank you 
Osama Mustafa



Wednesday, June 12, 2013

Example on how to add Virtual Host On em12c

Active/Passive Clusterware , how to use it with em12c ? How to monitor OS and Database ? As One Single host Agent will deploy on Both Hosts without Any problem but in this case you will only Deal with one Host as Single not as Clusterware.

i will provide example how to add active/passive Clusterware to em12c :


First Agent should be deploy for single Host not acitve/passive clusterware, after do that Run the below command from Single Host 
 
emctl deploy agent -o -n "Service_name" -A  

The Below Commands should be run from new agent Location 

emctl secure agent
emctl start agent
emctl config agent addinternaltargets


**I prefer to set Service_name ---> OracleAgent12cShared.
** Re run the above command on the passive Node after Failover shared storage on it.

Thank you 
Osama Mustafa

Tuesday, June 11, 2013

Oracle OTN Forum Upgraded

After waiting Long More than one month to upgrade Oracle Forum, yesterday New Release Published with Big Surprise for all of us.

Lot Of New Features, New Look , and New Red Banner if you get used old forum it's easy for you to deal with new one , Specially it's Have Lot of Social media Features

  • Change Your Avatar
  • Like Posts
  • Add Friends
  • Private message
  • using @ to Tag Someone
  • Follow Post
  • You can include your mobile number.
This is What I know until now, And We need time to know how to use it and get use to it, Since lot of new Features and totally Surprise me also you read this article here.

I guess Good things need time, and some of these feature not working 100% but hope everything Will Goes smoothly.

Thank you 
Osama Mustafa 

Sunday, June 9, 2013

Some notes you maybe need for Em12c

I would like to share some MOS  Notes that could be useful please note that You can share yours in comments


EM12c R2: How to Upgrade to EM 12c R2 - Two System Method [ID 1363162.1]
EM12c: How to install JVM Diagnostics (JVMD) in Cloud Control [ID 1493862.1]
EM12c: How To Upgrade Plugin On 12.1 Management Agent [ID 1490762.1]
How to Install 12c Agent on Virtual Host [ID 1469995.1]

EM12c Upgrade : Agents Are Not Listed in the Upgrade Console With Error Inventory Data Is Missing From the Repository [ID 1489109.1]

EM 12c : How to Discover All Targets On a Single Host After Agent Installation ? [ID 1386817.1]
EM 12c : Understanding Promote Target Functionality [ID 1386161.1]

EM12c How to Perform Automatic Host Discovery Using IP Scan Method ? [ID 1386811.1]
EM12c Agent status Fails With "Peer Not Authenticated" Error. [ID 1510706.1]

EM12C: Agent Installation Failed With Error: "OMS_HOST and EM_UPLOAD_PORT is not active" [ID 1371873.1]

How To Upgrade Java Diagnostics In Enterprise Manager 12c With the Latest Patches [ID 1534023.1]
EM12c Cloud Control: Required Patches for Discovery and Monitoring of Coherence Targets [ID 1526316.1]

EM12c : Patch Conflict While Applying Patch 14040891 [ID 1479092.1]
Unable To Add host targets with EM12c - Valid Host Name check fails [ID 1433843.1]
Release Schedule of Current Enterprise Manager Releases and Patch Sets (10g, 11g, 12c) [ID 793512.1]
EM 12c: How to Deploy a Plug-In from Enterprise Manager Cloud Control 12.1.0.1 [ID 1364030.1]

Thank you
Osama Mustafa

Monday, June 3, 2013

Allow users Login Solaris 11

Solaris 11 is the most amazing Platform that you can deal with , Specially if you want to install any Oracle products. you don't need to check anything before install.

By default root login on solaris disabled and you cannot login as root. ( which is amazing for security ) but sometimes you need to enable this login and to do that follow the below steps :

  • after login to privileged  user can access on Solaris switch user to root.
  • vi etc/user_attr

#
# The system provided entries are stored in different files
# under "/etc/user_attr.d".  They should not be copied to this file.
#
# Only local changes should be stored in this file.
# This line should be kept in this file or it will be overwritten.
#
oracle::::type=normal;project=user.oracle;defaultpriv=basic,net_privaddr
type should be normal.

and to enable SSH for root user you have to do the following :

vi /etc/ssh/sshd_config
PermitRootLogin = yes
vi /etc/default/login
#CONSOLE =/dev/login|console
rolemod -K type=normal root


Thank you
Osama mustafa