Feed aggregator

parameter ENABLE_DDL_LOGGING

ContractOracle - Sun, 2013-06-30 23:43
If Oracle parameter ENABLE_DDL_LOGGING is enabled DDL records are written to the ADR.

SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> connect c##test/test@pdb1
Connected.
SQL> create view x as select * from user_views;

View created.

SQL> drop view x;

View dropped.

SQL> exit
Disconnected from 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

[oracle@rac1 log]$ pwd
/u01/app/oracle/diag/rdbms/t12/T12/log
[oracle@rac1 log]$ ls
ddl  ddl_T12.log  debug  test
[oracle@rac1 log]$ more *.log
Mon Jul 01 12:35:54 2013
diag_adl:create view x as select * from user_views
diag_adl:drop view x

[oracle@rac1 log]$ cd ddl
[oracle@rac1 ddl]$ more *.xml
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='rac1.test.com' host_addr='192.168.1.205'
 version='1'>
 create view x as select * from user_views
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='rac1.test.com' host_addr='192.168.1.205'>
 drop view x

Categories: DBA Blogs

Oracle 12c New Features - Clone a Plugged In Database

ContractOracle - Sun, 2013-06-30 23:21
One of the benefits of the CDB/PDB model in Oracle 12c is that it allows rapid cloning of Pluggable Databases (PDB).  To create a clone database in previous versions of Oracle the DBA would have needed to create a new database instance with a new set of parameters and then clone the source database files using rman.  

In the following example I will clone database PDB1 to a new database PDB3 using only the "create pluggable database" command.  First the source database needs to be open read-only.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

Then clone the PDB1 to PDB3.

SQL> create pluggable database PDB3 from PDB1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdb1','/u01/app/oracle/oradata/T12/pdb3');  

Pluggable database created.

And open both databases read-write.

SQL> alter pluggable database PDB3 open;

Pluggable database altered.

SQL> alter pluggable database PDB1 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE


Categories: DBA Blogs

OBIEE 11.1.1.6.11 Bundle Patch Now Available

Abhinav Agarwal - Sun, 2013-06-30 22:37
Last week saw the release of the OBIEE Bundle Patch 11.1.1.7.1 and on Friday bundle patch 11.1.1.6.11 for Oracle Business Intelligence Enterprise Edition became available on the Oracle My Support Portal.

The tracking ids for the respective bundle patch components are:
  • Oracle Business Intelligence Installer (BIINST): ID: 16747681
  • Oracle Real Time Decisions (RTD): ID: 16747684
  • Oracle Business Intelligence Publisher (BIP): ID: 16747692
  • Oracle Business Intelligence ADF Components (BIADFCOMPS): ID:  16747699
  • Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x (BIFNDNEPM): ID:  16747703
  • Oracle Business Intelligence: (OBIEE): ID: 16717325
  • Oracle Business Intelligence Platform Client Installers and MapViewer: ID: 16747708
Happy Monday to all!
Abhinav
Bangalore

    Oracle 12c New Features - Plugging and Unplugging Databases

    ContractOracle - Sun, 2013-06-30 22:35
    In a previous blog post I demonstrated creating Pluggable Databases (PDB) in an Oracle 12c Container Database (CDB).  In this test I will demonstrate how easy it is to unplug a PDB from a CDB, and then plugin again.

    We currently have one PDB with name PDB1.  We will shutdown, unplug it, and drop it.

    SQL> select name from v$pdbs;

    NAME
    ------------------------------
    PDB$SEED
    PDB1

    SQL> alter pluggable database pdb1 close immediate;

    Pluggable database altered.

    SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml';

    Pluggable database altered.

    SQL> drop pluggable database pdb1 keep datafiles;

    Pluggable database dropped.

    SQL> select name from v$pdbs;

    NAME
    ------------------------------
    PDB$SEED

    We can now backup the database to tape for later restore, or copy the datafiles and xml file for the pluggable database to another CDB on another server and plugin.  In this example I will just plug the database back into the original CDB.  

    Before we plugin we first need to run DBMS_PDB.CHECK_PLUG_COMPATIBILITY to check that the PDB is compatible with the new CDB.

    SQL> set serveroutput on
    SQL> DECLARE
       compatible BOOLEAN := FALSE;
      2    3  BEGIN
      4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
      5          pdb_descr_file => '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml');
      6     if compatible then
      7        DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? YES');
      8     else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? NO');
      9     end if;
     10  END;
     11  /
    Is pluggable PDB2 compatible? YES

    PL/SQL procedure successfully completed.

    As the PDB is compatible with the CDB we can proceed to plug it in. 

    SQL> create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE;

    Pluggable database created.

    SQL> select name, open_mode from v$pdbs;

    NAME                           OPEN_MODE
    ------------------------------ ----------
    PDB$SEED                       READ ONLY
    PDB1                           MOUNTED

    SQL> alter session set container=PDB1;

    Session altered.

    SQL> alter database open;

    Database altered.

    SQL> connect test/test@pdb1;
    Connected.


    SQL> show con_name



    CON_NAME

    ------------------------------
    PDB1

    We are now able to login to the plugged in database.

    The Alert log entries for these operations are as follows :-

    Mon Jul 01 11:14:31 2013
    alter pluggable database pdb1 close immediate
    Mon Jul 01 11:14:31 2013
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    Pluggable database PDB1 closed
    Completed: alter pluggable database pdb1 close immediate
    alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    Completed: alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
    drop pluggable database pdb1 keep datafiles
    Mon Jul 01 11:15:02 2013
    Deleted file /u01/app/oracle/oradata/T12/pdb1/pdbseed_temp01.dbf
    Completed: drop pluggable database pdb1 keep datafiles
    create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
    Mon Jul 01 11:20:45 2013
    ****************************************************************
    Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
    If any errors are encountered before the pdb is marked as NEW,
    then the pdb must be dropped
    ****************************************************************
    Deleting old file#10 from file$
    Deleting old file#11 from file$
    Adding new file#12 to file$(old file#10)
    Adding new file#13 to file$(old file#11)
    Successfully created internal service pdb1 at open
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    ****************************************************************

    Post plug operations are now complete.
    Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
    ****************************************************************
    Completed: create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
    Mon Jul 01 11:29:00 2013
    alter database open
    Mon Jul 01 11:29:00 2013
    Pluggable database PDB1 dictionary check beginning
    Pluggable Database PDB1 Dictionary check complete
    Opening pdb PDB1 (3) with no Resource Manager plan active

    XDB installed.

    XDB initialized.
    Pluggable database PDB1 opened read write
    Completed: alter database open






    Categories: DBA Blogs

    Oracle 12c New Features - Container and Pluggable Databases

    ContractOracle - Sun, 2013-06-30 20:30
    Oracle 12c introduces "Multitenant Architecture" which allows consolidation of databases via Container Databases (CDB) and Pluggable Databases (PDB)

    The CDB database owns the SGA and running processes, and the PDB databases are serviced by those resources.  This new architecture will be a big change for DBAs experienced in managing earlier versions of the Oracle database, so it is worth taking the time to read the documentation and testing extensively before using these new features.  The theory is that many databases sharing one SGA and set of processes should be more efficient that multiple individually managed memory segments, so this feature is specifically aimed at clouds and large companies.


    I used the DBCA utility to create a CDB called T12, which also created a small PDB$SEED database.  DBCA is easy to run, and similar to previous versions, so I won't show screen shots here.  It is also possible to create a CDB database using the CREATE DATABASE statement along with the new ENABLE PLUGGABLE DATABASE clause.  


    When managing CDBs and PDBs it is important to ensure you know what container you are currently working on.  By default when you login you will end up in CDB$ROOT.  


    [oracle@rac1 admin]$ ps -ef | grep pmon
    oracle    7830     1  0 09:08 ?        00:00:00 ora_pmon_T12

    [oracle@rac1 admin]$ echo $ORACLE_SID

    T12

    [oracle@rac1 admin]$ sqlplus / as sysdba

    SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 09:11:11 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

    SQL> show con_id

    CON_ID
    ------------------------------
    1

    By selecting from v$database we can see that we are currently in a CDB.

    SQL> select DBID, name, CDB, CON_ID, CON_DBID from v$database;

          DBID NAME      CDB     CON_ID   CON_DBID
    ---------- --------- --- ---------- ----------
    1216820329 T12       YES          0 1216820329

    We can select PDB from dba_services to check what PDBs exist.  In this case we have not created any PDBs, so only the CDB T12 is listed against CDB$ROOT.

    SQL> select name, pdb from dba_services;

    NAME                PDB
    ----------------------------------------------------------------
    SYS$BACKGROUND      CDB$ROOT
    SYS$USERS           CDB$ROOT
    T12XDB              CDB$ROOT
    T12                 CDB$ROOT

    SQL> select name, con_id from v$active_services;

    NAME                                                  CON_ID
    ---------------------------------------------------------------- 
    T12XDB                                                1
    T12                                                   1
    SYS$BACKGROUND                                        1
    SYS$USERS                                             1

    Create a tnsnames.ora entry for connecting to CDB service T12.

    T12 =
     (DESCRIPTION =
     (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.test.com)(PORT = 1521)))
     (CONNECT_DATA =
     (SERVICE_NAME = T12)
     )
      )

    When we try to create a user we find that in a CDB we are are not allowed to create "local" users, but can only create "common" users with usernames starting with "C##"

    SQL> create user test identified by test;
    create user test identified by test
                *
    ERROR at line 1:
    ORA-65096: invalid common user or role name

    SQL> !oerr ora 65096
    65096, 00000, "invalid common user or role name"
    // *Cause:  An attempt was made to create a common user or role with a name
    //          that wass not valid for common users or roles.  In addition to
    //          the usual rules for user and role names, common user and role
    //          names must start with C## or c## and consist only of ASCII
    //          characters.
    // *Action: Specify a valid common user or role name.
    //

    SQL> create user test identified by test container=current;
    create user test identified by test container=current
                                   *
    ERROR at line 1:
    ORA-65049: creation of local user or role is not allowed in CDB$ROOT

    SQL> create user c##test identified by test;

    User created.

    There are now additional data dictionary views to help manage the PDB and CDB databases (names include PDB, CDB).  You will also notice that many data dictionary views now contain a column CON_ID which allows DBAs to check details for a specific CDB or PDB.  

    If we select from v$datafile in the new container database we can see that in addition to the datafiles for the CDB T12, there are datafiles listed for database "pdbseed".  The PDB$SEED database is created at the same time as the CDB and can be used as a source to create PDB databases.


    SQL> select name, con_id, plugged_in from v$datafile order by 2;

    NAME                                           CON_ID PLUGGED_IN
    -------------------------------------------------- ---------- ---
    /u01/app/oracle/oradata/T12/system01.dbf            1          0
    /u01/app/oracle/oradata/T12/sysaux01.dbf            1          0
    /u01/app/oracle/oradata/T12/undotbs01.dbf           1          0
    /u01/app/oracle/oradata/T12/users01.dbf             1          0
    /u01/app/oracle/oradata/T12/pdbseed/system01.dbf    2          0
    /u01/app/oracle/oradata/T12/pdbseed/sysaux01.dbf    2          0

    6 rows selected.

    The PDB$SEED database is mounted read only, so it is possible to explore it, but there are limits to what you can do with this DB.

    SQL> select con_id, name, open_mode from v$pdbs;

        CON_ID NAME                                        OPEN_MODE

    ---------- ------------------------------------------- ---------
             2 PDB$SEED                                    READ ONLY

    SQL> alter session set container=PDB$SEED;

    Session altered.

    SQL> select name from v$database;

    NAME
    ---------
    T12

    SQL> show con_name

    CON_NAME
    ------------------------------
    PDB$SEED

    SQL> show con_id

    CON_ID
    ------------------------------
    2

    SQL> select open_mode from v$database;

    OPEN_MODE
    --------------------
    READ ONLY

    SQL> shutdown;
    ORA-65017: seed pluggable database may not be dropped or altered

    To create our own read-write PDB as a copy of the PDB$SEED database we just need to execute the "create pluggable database" command.

    SQL> create pluggable database PDB1 admin user pdb1_admin identified by password roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1');

    Pluggable database created.


    SQL> select pdb_name, status from cdb_pdbs;

    PDB_NAME      STATUS
    ----------------------------------------------------------------
    PDB$SEED      NORMAL
    PDB1          NEW

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

    NAME                                           CON_ID
    ---------------------------------------------------------------- 
    SYS$BACKGROUND                                 1
    SYS$USERS                                      1
    T12                                            1
    T12XDB                                         1
    pdb1                                           3

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

    NAME
    -----------------------------------------------------------------
    /u01/app/oracle/oradata/T12/pdb1/system01.dbf
    /u01/app/oracle/oradata/T12/pdb1/sysaux01.dbf

    SQL> select name, open_mode from v$pdbs;

    NAME                           OPEN_MODE
    ------------------------------ ----------
    PDB$SEED                       READ ONLY
    PDB1                           MOUNTED

    We can see from the above that the new PDB was created in MOUNTED state.  We will need to open it if we want to use it.  When we look in the CDB alert log we can see the following :-

    Mon Jul 01 10:02:30 2013
    create pluggable database PDB1 admin user pdb1_admin identified by * roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1')
    Mon Jul 01 10:02:53 2013
    ****************************************************************
    Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
    If any errors are encountered before the pdb is marked as NEW,
    then the pdb must be dropped
    ****************************************************************
    Deleting old file#5 from file$
    Deleting old file#7 from file$
    Adding new file#10 to file$(old file#5)
    Adding new file#11 to file$(old file#7)
    Successfully created internal service pdb1 at open
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    ****************************************************************
    Post plug operations are now complete.
    Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
    ****************************************************************

    Completed: create pluggable database PDB1 admin user pdb1_admin identified by * roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1')

    We can open databases individually as follows, or open all PDBs using "alter pluggable database all open;"

    SQL> alter session set container=PDB1;

    Session altered.

    SQL> show con_name

    CON_NAME
    ------------------------------
    PDB1

    SQL> alter database open;

    Database altered.

    SQL> select name, open_mode from v$pdbs;

    NAME                           OPEN_MODE
    ------------------------------ ----------
    PDB1                           READ WRITE

    We can see the following in the CDB alert log.

    alter database open
    Mon Jul 01 10:12:50 2013
    Pluggable database PDB1 dictionary check beginning
    Pluggable Database PDB1 Dictionary check complete
    Opening pdb PDB1 (3) with no Resource Manager plan active

    XDB installed.


    XDB initialized.

    Pluggable database PDB1 opened read write

    Completed: alter database open

    Create a tnsnames.ora entry for the new PDB using the default service PDB1.

    PDB1 =
     (DESCRIPTION =
     (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.test.com)(PORT = 1521)))
     (CONNECT_DATA =
     (SERVICE_NAME = PDB1)
     )
      )

    Now that we have created a PDB we can create "local" users.

    SQL> show con_name

    CON_NAME
    ------------------------------
    PDB1

    SQL> create user test identified by test;

    User created.

    SQL> grant create session to test;

    Grant succeeded.

    It is also possible to grant privileges in the PDB for the "common" users that exist in the CDB.

    SQL> grant create session to C##TEST container=ALL;


    Grant succeeded.

    We can now connect directly to the PDB1 pluggable database using both the "local" and "common" users.

    SQL> connect test/test@PDB1
    Connected.
    SQL> show con_name

    CON_NAME

    ------------------------------
    PDB1

    SQL> connect C##TEST/test@PDB1
    Connected.

    SQL> show con_name

    CON_NAME
    ------------------------------
    PDB1

    When logged into PDBs many data dictionary views will restrict our view of the world via CON_ID so that we can't see records relating to other PDBs.  We can see from the following selects that PDB1 uses the UNDO and REDO files from the container database, but has its own SYSTEM, SYSAUX, TEMP files.

    SQL> select name from v$datafile;

    NAME
    -----------------------------------------------------------------
    /u01/app/oracle/oradata/T12/undotbs01.dbf
    /u01/app/oracle/oradata/T12/pdb1/system01.dbf
    /u01/app/oracle/oradata/T12/pdb1/sysaux01.dbf

    SQL> select name from v$tempfile;

    NAME
    -----------------------------------------------------------------
    /u01/app/oracle/oradata/T12/pdb1/pdbseed_temp01.dbf

    SQL> select member from v$logfile;

    MEMBER
    -----------------------------------------------------------------
    /u01/app/oracle/oradata/T12/redo03.log
    /u01/app/oracle/oradata/T12/redo02.log
    /u01/app/oracle/oradata/T12/redo01.log

    PDB databases can be created from a seed database as demonstrated above, cloned from other PDB databases, or plugged in from previously unplugged PDBs or converted non-CDB databases.  In addition to creating new PDB databases we can also drop, rename, clone, unplug, plug backup, restore, and duplicate.  Check the Oracle documentation for details.

    More details here :- Introduction to the Multitenant Architecture
    Categories: DBA Blogs

    Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 2

    Oracle NZ - Sun, 2013-06-30 19:24

    We will complete our lab by installing the Oracle Database software, create a Pluggable database and take a look in the Oracle Enterprise Manager Database Express.

    The steps to install and create an Oracle Database 12c are:

    1 – Go to the database directory created when unzipped the 2 Oracle Database Files and run /runInstaller .

    SnapCrab_NoName_2013-7-1_20-33-26_No-00

    2 – Unselect the option [I wish to receive security updates via My Oracle Support] and click [Next]. Of course, if you want to receive updates all you need to do is to leave this option marked and enter your My Oracle Support email and password.

    .SnapCrab_NoName_2013-7-1_20-34-0_No-00

    3 – You will receive a warning message that you have not provided an email address. Just click on [Yes].

    SnapCrab_NoName_2013-7-1_20-34-27_No-00

    4 – Select [Skip software updates] and click [Next].

    SnapCrab_NoName_2013-7-1_20-34-46_No-00

    5 – Select [Create and Configure a Database] and click [Next].

    SnapCrab_NoName_2013-7-1_20-35-8_No-00

    6 – Select [Server Class] and click [Next]. *** If you do not have enough resources on your laptop, please choose Desktop Class instead of Server Class.

     SnapCrab_NoName_2013-7-1_20-35-30_No-00

    7 – Select the type of Database to be installed, choose [Single instance database installation] and click [Next].

     SnapCrab_NoName_2013-7-1_20-35-49_No-00

    8 – Select the Install type. For this lab I will choose [Advanced Install] and click [Next].

     SnapCrab_NoName_2013-7-1_20-36-7_No-00

    9 – Select the product Language, here all you need to to is leave English as the default and click [Next].

     SnapCrab_NoName_2013-7-1_20-36-24_No-00

    10 – Select the Database Edition you want to install. Select [Enterprise Edition] and click [Next[.

     SnapCrab_NoName_2013-7-1_20-36-38_No-00

    11 – Specify the Installation location and click [Next].

     SnapCrab_NoName_2013-7-1_20-36-53_No-00

    12 – Create the Inventory and click [Next].

     SnapCrab_NoName_2013-7-1_20-37-15_No-00

    13 – Select the type of database you want to create. Select [General Purpose] and click [Next].

     SnapCrab_NoName_2013-7-1_20-37-28_No-00

    14 – Specify the database identifiers, enter “orcl” as the SID and here you can decide if you want to create a Container Database or as early releases a non-Container Database (by not selecting [Create as Container database]. For this example we will select Container database and create a Pluggable Database called “pdborcl” and click [Next].

     SnapCrab_NoName_2013-7-1_20-37-48_No-00

    15 – Specify the configurations options, by default the Automatic Memory Management is enabled, click [Next].

     SnapCrab_NoName_2013-7-1_20-38-3_No-00

    16 – Specify the storage to be used. Select [File System] and click [Next].

     SnapCrab_NoName_2013-7-1_20-38-26_No-00

    17 – Management options. If you have an EM Cloud Control running on your environment, here is where you specify the EM details to manage this database. Leave as the default and click [Next].

     SnapCrab_NoName_2013-7-1_20-38-40_No-00

    18 – Enable Recovery and click [Next].

     SnapCrab_NoName_2013-7-1_20-39-14_No-00

    19 – Specify Passwords. For this scenario we use the password “oracle” to all accounts. Click [Next].

     SnapCrab_NoName_2013-7-1_20-39-38_No-00

    20 – A warning will appear due that we are using a easy password. Click [Yes].

     SnapCrab_NoName_2013-7-1_20-39-53_No-00

    21 – Click [Next] on Privileged Operating Systems groups.

     SnapCrab_NoName_2013-7-1_20-40-7_No-00

    22 – Review the Summary page and click [install].

     SnapCrab_NoName_2013-7-1_20-40-35_No-00

     SnapCrab_NoName_2013-7-1_20-41-28_No-00

    23 – Execute the configuration scripts as root and click [Ok] to continue the installation.

     SnapCrab_NoName_2013-7-1_20-43-51_No-00

     SnapCrab_NoName_2013-7-1_20-44-33_No-00

     SnapCrab_NoName_2013-7-1_20-45-15_No-00

     SnapCrab_NoName_2013-7-1_20-45-52_No-00

     SnapCrab_NoName_2013-7-1_20-51-35_No-00

    24 – In the Finish page click [Close].

     SnapCrab_NoName_2013-7-1_20-52-3_No-00

    25 – Edit /etc/oratab as follows.

     SnapCrab_NoName_2013-7-1_20-52-59_No-00

    26 – Check the listener status.

     SnapCrab_NoName_2013-7-1_20-53-29_No-00

    27 – Check if the Container Database is running.

     SnapCrab_NoName_2013-7-1_20-53-46_No-00

    28 – Connect to the Enterprise Manager Database Express. It will first ask you to add a Security Exception in Firefox.

    SnapCrab_NoName_2013-7-1_20-57-17_No-00

     SnapCrab_NoName_2013-7-1_20-57-30_No-00

    29 – The Next step will be to download the Adobe Flash Player rpm and install it.

    SnapCrab_NoName_2013-7-2_12-58-39_No-00

    30 – Enter your database username and password. SYS and oracle and click [Login]

     SnapCrab_NoName_2013-7-2_13-1-6_No-00

    31 – This is the First Screen of the new Enterprise Manager Database Express 12c. Here you are not able to use monitoring, set alertings or even execute backup and recovery operations. But you can do a lot of other stuff such  such as per example: Manage Storage such as: Undo, Redo Log Files, and Control Files, Configure Initialization Parameters, Memory  and Database Features and finally Manage  Performance, SQL Tuning and Users. and Roles (Security).

     SnapCrab_NoName_2013-7-2_13-47-36_No-00

    SnapCrab_NoName_2013-7-2_13-44-55_No-00

    SnapCrab_NoName_2013-7-2_13-45-27_No-00

    SnapCrab_NoName_2013-7-2_13-45-56_No-00

    SnapCrab_NoName_2013-7-2_13-44-21_No-00

     SnapCrab_NoName_2013-7-2_13-46-24_No-00

     SnapCrab_NoName_2013-7-2_13-47-3_No-00

     

    Hope you enjoyed this tutorial and soon many more will come.

     

    Regards,

     

    Francisco Munoz Alvarez

    //



    Tags:  , , , , , ,

    Del.icio.us
    Facebook
    TweetThis
    Digg
    StumbleUpon

    Copyright © OracleNZ by Francisco Munoz Alvarez [Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 2], All Right Reserved. 2016.
    Categories: DBA Blogs

    Easy HTML output in IPython Notebook

    Catherine Devlin - Fri, 2013-06-28 12:54
    If any object has a _repr_html_ method, the IPython Notebook will use it to render HTML output. It's really easy to make a simple class that permits general dynamic HTML-rich output with Markdown. Markdown is a superset of HTML, so HTML in your output string will work, too.

    import markdown
    class MD(str):
    def _repr_html_(self):
    return markdown.markdown(self)
    Four little lines, and you can do this!

    SOA Suite 11g Developers Cookbook Published

    Antony Reynolds - Fri, 2013-06-28 10:33
    SOA Suite 11g Developers Cookbook Available

    Just realized that I failed to mention that Matt & mine’s most recent book, the SOA Suite 11g Developers Cookbook was published over Christmas last year!

    In some ways this was an easier book to write than the Developers Guide, the hard bit was deciding what recipes to include.  Once we had decided that the writing of the book was pretty straight forward.

    The book focuses on areas that we felt we had neglected in the Developers Guide, and so there is more about Java integration and OSB, both of which we see a lot of questions about when working with customers.

    Amazon has a couple of reviews.

    Table of Contents

    Chapter 1: Building an SOA Suite Cluster
    Chapter 2: Using the Metadata Service to Share XML Artifacts
    Chapter 3: Working with Transactions
    Chapter 4: Mapping Data
    Chapter 5: Composite Messaging Patterns
    Chapter 6: OSB Messaging Patterns
    Chapter 7: Integrating OSB with JSON
    Chapter 8: Compressed File Adapter Patterns
    Chapter 9: Integrating Java with SOA Suite
    Chapter 10: Securing Composites and Calling Secure Web Services
    Chapter 11: Configuring the Identity Service
    Chapter 12: Configuring OSB to Use Foreign JMS Queues
    Chapter 13: Monitoring and Management

    More Reviews

    In addition to the Amazon Reviews I also found some reviews on GoodReads.

    Free WebLogic Administration Cookbook

    Antony Reynolds - Fri, 2013-06-28 10:16
    Free WebLogic Admin Cookbook

    Packt Publishing are offering free copies of Oracle WebLogic Server 12c Advanced Administration Cookbook : http://www.packtpub.com/oracle-weblogic-server-12c-advanced-administration-cookbook/book  in exchange for a review either on your blog or on the title’s Amazon page.

    Here’s the blurb:

    • Install, create and configure WebLogic Server
    • Configure an Administration Server with high availability
    • Create and configure JDBC data sources, multi data sources and gridlink data sources
    • Tune the multi data source to survive database failures
    • Setup JMS distributed queues
    • Use WLDF to send threshold notifications
    • Configure WebLogic Server for stability and resilience

    If you’re a datacenter operator, system administrator or even a Java developer this book could be exactly what you are looking for to take you one step further with Oracle WebLogic Server, this is a good way to bag yourself a free cookbook (current retail price $25.49).

    Free review copies are available until Tuesday 2nd July 2013, so if you are interested, email Harleen Kaur Bagga at: harleenb@packtpub.com.

    I will be posting my own review shortly!

    Useful MOS notes on 12c

    Syed Jaffar - Fri, 2013-06-28 08:31
    Here is the list of useful MOS notes on 12c:


    1. Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
    2. NOTE:1520299.1 - Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC)
    3. NOTE:1493645.1 - Oracle Database 12c Release 1 (12.1) DBUA : Understanding New Changes With All New 12.1 DBUA 
    4. NOTE:1503653.1 - Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)
    5. Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
    6. RMAN RECOVER TABLE Feature New to Oracle Database 12c [ID 1521524.1]
    7. How to Merge Multiple Partitions in Oracle 12C [ID 1482263.1]
    8. How to Drop/Truncate Multiple Partitions in Oracle 12C [ID 1482264.1]
    9. How to Create Interval-Reference Partitioned Tables in Oracle 12c [ID 1519042.1]


    DataDirect Access your favorite SaaS app with SQL

    Kubilay Çilkara - Fri, 2013-06-28 08:24
    Database driver company +Progress DataDirect  are writing the next generation of data access. They are working on a platform called Datadirect which will enable you to access loads of data sources in the cloud, from the cloud, and some of them with SQL

    Yes you have read correct, SQL!

    All you have to do is visit their website and sign up for a trial account and access your favorite SaaS app - i.e Salesforce  or any other provided Cloud app with SQL.

    Here is how their availability of connectors looks like






     










    To sign up just follow this link.

    I very much like this noble idea. SQL is the universal language for data manipulation and access. And things like Big Data, Hadoop in the horizon they are pro-active and seems like they are already working on it.

    See some list of data sources they can/will SQL:

    • Twitter
    • Facebook
    • Hive
    • Eloqua
    • force.com
    • Salesforce
    • SQLAzure
    • Microsoft Dynamics CRM
    • and many more...

    This is on my hot list of technology to watch. Well done DataDirect. 
    Categories: DBA Blogs

    Oracle Database 12c Technology Day worldwide

    Syed Jaffar - Fri, 2013-06-28 03:30
    Oracle quotes that 12c comes with more than 500 new features. Join the Oracle technology day event to learn firsthand how the new multitenant architecture make it easy to deploy and manage database clouds.

    You will learn the following:


      • Simplify database consolidation
      • Automatically compress and tier data
      • Improve application continuity
      • Redact sensitive data
    Why waiting, select your city and enroll for the event now on http://www.oracle.com/us/corporate/events/database-12c-technology-day/index.html




    Oracle 12c New Features - multiple indexes on the same set of columns

    ContractOracle - Fri, 2013-06-28 00:29
    With Oracle 12c it is now possible to have multiple indexes on the same set of columns as long as there is a difference between the indexes (index type, partitioning etc), and one is invisible.

    This makes it possible to quickly change index strategies with minimum impact to applications.

    For this example I will first create a test partitioned table.

    SQL> CREATE TABLE test_range
    (id  NUMBER(5),
    att1 char(1),
    att2 char(1),
    att3 char(1))
    PARTITION BY RANGE(id)
    (
    PARTITION id_10 VALUES LESS THAN(10),
    PARTITION id_20 VALUES LESS THAN(20)
    );  

    Table created.

    Now create a global index on ATT1.

    SQL> create index att1_global on test_range(att1) global;

    Index created.

    Try to create another index on ATT1 with local partitioning, and it fails because the existing index is still visible.

    SQL> create index att1_local on test_range(att1) local;
    create index att1_local on test_range(att1) local
                                          *
    ERROR at line 1:
    ORA-01408: such column list already indexed

    Try to create an invisible global index on ATT1 and it fails because the structure is the same as the existing index.

    SQL> create index att1_global2 on test_range(att1) global invisible;
    create index att1_global2 on test_range(att1) global invisible
                                            *
    ERROR at line 1:
    ORA-01408: such column list already indexed

    Try to create an invisible index with local partitioning and it is successful.

    SQL> create index att1_local on test_range(att1) local invisible;

    Index created.

    Try to make the locally partitioned index visible, and it fails because there is another visible index with the same columns.

    SQL> alter index att1_local visible;
    alter index att1_local visible
    *
    ERROR at line 1:
    ORA-14147: There is an existing VISIBLE index defined on the same set of
    columns.

    We need to make the existing index invisible first, then make the new index visible.  With this method we can easily test multiple indexing strategies without needing long outages to drop and recreate indexes.

    SQL> alter index att1_global invisible;

    Index altered.

    SQL> alter index att1_local visible;

    Index altered.

    Categories: DBA Blogs

    Oracle 12c New Features - SQL*Loader Express

    ContractOracle - Thu, 2013-06-27 23:06
    Oracle 12c introduces Sql*Loader Express features, which allow users to run sqlldr with minimum configuration.  

    The following example shows loading records into table EMPLOYEE from CSV file EMPLOYEE.dat without having to create a control file.


    SQL> create table EMPLOYEE (id integer primary key, name varchar2(10));

    Table created.

    SQL> exit
    Disconnected from 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

    [oracle@rac1 admin]$ more EMPLOYEE.dat
    1,Adam
    2,Ben
    3,Colin
    4,Dean
    5,Evan
    6,Frank
    7,Greg
    8,Hank
    9,Ian
    10,Jack
    [oracle@rac1 admin]$ sqlldr test/test TABLE=EMPLOYEE

    SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

    Express Mode Load, Table: EMPLOYEE
    Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
    SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
    ORA-01031: insufficient privileges
    SQL*Loader-579: switching to direct path for the load
    SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
    SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
    Express Mode Load, Table: EMPLOYEE
    Path used:      Direct

    Load completed - logical record count 10.

    Table EMPLOYEE:
      10 Rows successfully loaded.

    Check the log file:
      EMPLOYEE.log
    for more information about the load.

    [oracle@rac1 admin]$ ls EMPLOYEE*
    EMPLOYEE.dat  EMPLOYEE.log

    [oracle@rac1 admin]$ more EMPLOYEE.log

    SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

    Express Mode Load, Table: EMPLOYEE
    Data File:      EMPLOYEE.dat
      Bad File:     EMPLOYEE_%p.bad
      Discard File:  none specified

     (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation:    none specified
    Path used:      External Table

    Table EMPLOYEE, loaded from every logical record.
    Insert option in effect for this table: APPEND

    Column Name                Position   Len   Term Encl Datatype
    -------------------------- ---------- ----- ---- ---- ---------
    ID                         FIRST      *     ,         CHARACTER
    NAME                       NEXT       *     ,         CHARACTER

    Generated control file for possible reuse:
    OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
    LOAD DATA
    INFILE 'EMPLOYEE'
    APPEND
    INTO TABLE EMPLOYEE
    FIELDS TERMINATED BY ","
    (
      ID,
      NAME
    )
    End of generated control file for possible reuse.

    SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
    ORA-01031: insufficient privileges

    ----------------------------------------------------------------
    SQL*Loader-579: switching to direct path for the load
    SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
    SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
    ----------------------------------------------------------------

    Express Mode Load, Table: EMPLOYEE
    Data File:      EMPLOYEE.dat
      Bad File:     EMPLOYEE.bad
      Discard File:  none specified

     (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation:    none specified
    Path used:      Direct

    Table EMPLOYEE, loaded from every logical record.
    Insert option in effect for this table: APPEND

       Column Name        Position   Len   Term Encl Datatype
    --------------------- ---------- ----- ---- ---- ----------------
    ID                    FIRST      *     ,         CHARACTER
    NAME                  NEXT       *     ,         CHARACTER

    Generated control file for possible reuse:
    OPTIONS(DIRECT=TRUE)
    LOAD DATA
    INFILE 'EMPLOYEE'
    APPEND
    INTO TABLE EMPLOYEE
    FIELDS TERMINATED BY ","
    (
      ID,
      NAME
    )
    End of generated control file for possible reuse.

    The following index(es) on table EMPLOYEE were processed:
    index TEST.SYS_C009860 loaded successfully with 10 keys

    Table EMPLOYEE:
      10 Rows successfully loaded.
      0 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.

    Bind array size not used in direct path.
    Column array  rows :    5000
    Stream buffer bytes:  256000
    Read   buffer bytes: 1048576

    Total logical records skipped:          0
    Total logical records read:            10
    Total logical records rejected:         0
    Total logical records discarded:        0
    Total stream buffers loaded by SQL*Loader main thread:        1
    Total stream buffers loaded by SQL*Loader load thread:        0

    Run began on Fri Jun 28 11:58:11 2013
    Run ended on Fri Jun 28 11:58:12 2013

    Elapsed time was:     00:00:01.27
    CPU time was:         00:00:00.02


    [oracle@rac1 admin]$ sqlplus test/test

    SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 12:05:49 2013

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

    Last Successful login time: Fri Jun 28 2013 11:58:11 +08:00

    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> select * from employee;

            ID NAME
    ---------- ----------
             1 Adam
             2 Ben
             3 Colin
             4 Dean
             5 Evan
             6 Frank
             7 Greg
             8 Hank
             9 Ian
            10 Jack


    10 rows selected.


    Categories: DBA Blogs

    Oracle 12c New Features - FETCH FIRST ROWS, OFFSET

    ContractOracle - Thu, 2013-06-27 22:20
    Oracle 12c introduces sql syntax for row limiting.  This makes it easier to retrieve records in sets for display or processing.

    Example :-

    create table employee (id integer primary key, name varchar2(10));
    insert into employee values (1,'Adam');
    insert into employee values (2,'Ben');
    insert into employee values (3,'Colin');
    insert into employee values (4,'Dean');
    insert into employee values (5,'Evan');
    insert into employee values (6,'Frank');
    insert into employee values (7,'Greg');
    insert into employee values (8,'Hank');
    insert into employee values (9,'Ian');
    insert into employee values (10,'Jack');
    commit;

    SQL> select * from employee order by id fetch first 3 rows only;

            ID NAME
    ---------- ----------
             1 Adam
             2 Ben
             3 Colin

    SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;

            ID NAME
    ---------- ----------
             4 Dean
             5 Evan
             6 Frank

    SQL> select * from employee order by id fetch first 50 percent rows only;

            ID NAME
    ---------- ----------
             1 Adam
             2 Ben
             3 Colin
             4 Dean
             5 Evan



    Categories: DBA Blogs

    Oracle 12c New Features - Extended Datatypes VARCHAR2 32767 bytes

    ContractOracle - Thu, 2013-06-27 21:55
    In Oracle 12c it is now possible to create VARCHAR2, NVARCHAR2, and RAW attributes of size 32767 bytes.

    This is controlled by new parameter MAX_STRING_SIZE.  The value of the parameter defaults to STANDARD, but if you set it to EXTENDED you can use the expanded variable size.  Consider this carefully, as increased column sizes will have effects on any applications that use the data in variables and parameters and can limit the use of partitions and indexes on the extended datatypes.

    In addition to setting MAX_STRING_SIZE=EXTENDED you also need to start the database in "upgrade" mode and run utl32k.sql  Also be aware that if you are running CDB / PDB you may need to upgrade them individually, including the SEED DB.

    SQL> alter system set max_string_size=EXTENDED scope=spfile;

    System altered.

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

    Total System Global Area 1636814848 bytes
    Fixed Size                  2288968 bytes
    Variable Size            1056965304 bytes
    Database Buffers          570425344 bytes
    Redo Buffers                7135232 bytes
    Database mounted.
    Database opened.
    SQL> @utl32k.sql

    Session altered.

    DOC>#######################################################################
    DOC>#######################################################################
    DOC>   The following statement will cause an "ORA-01722: invalid number"
    DOC>   error if the database has not been opened for UPGRADE.
    DOC>
    DOC>   Perform a "SHUTDOWN ABORT"  and
    DOC>   restart using UPGRADE.
    DOC>#######################################################################
    DOC>#######################################################################
    DOC>#

    no rows selected

    DOC>#######################################################################
    DOC>#######################################################################
    DOC>   The following statement will cause an "ORA-01722: invalid number"
    DOC>   error if the database does not have compatible >= 12.0.0
    DOC>
    DOC>   Set compatible >= 12.0.0 and retry.
    DOC>#######################################################################
    DOC>#######################################################################
    DOC>#

    PL/SQL procedure successfully completed.


    Session altered.


    0 rows updated.


    Commit complete.


    System altered.


    PL/SQL procedure successfully completed.


    Commit complete.


    System altered.


    Session altered.


    PL/SQL procedure successfully completed.

    No errors.

    Session altered.


    PL/SQL procedure successfully completed.


    Commit complete.


    Package altered.


    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN  2013-06-28 10:47:30

    DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
    DOC>   objects in the database. Recompilation time is proportional to the
    DOC>   number of invalid objects in the database, so this command may take
    DOC>   a long time to execute on a database with a large number of invalid
    DOC>   objects.
    DOC>
    DOC>   Use the following queries to track recompilation progress:
    DOC>
    DOC>   1. Query returning the number of invalid objects remaining. This
    DOC>      number should decrease with time.
    DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
    DOC>
    DOC>   2. Query returning the number of objects compiled so far. This number
    DOC>      should increase with time.
    DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
    DOC>
    DOC>   This script automatically chooses serial or parallel recompilation
    DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
    DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
    DOC>   On RAC, this number is added across all RAC nodes.
    DOC>
    DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
    DOC>   recompilation. Jobs are created without instance affinity so that they
    DOC>   can migrate across RAC nodes. Use the following queries to verify
    DOC>   whether UTL_RECOMP jobs are being created and run correctly:
    DOC>
    DOC>   1. Query showing jobs created by UTL_RECOMP
    DOC>         SELECT job_name FROM dba_scheduler_jobs
    DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>
    DOC>   2. Query showing UTL_RECOMP jobs that are running
    DOC>         SELECT job_name FROM dba_scheduler_running_jobs
    DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>#

    PL/SQL procedure successfully completed.


    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_END  2013-06-28 10:47:32

    DOC> The following query reports the number of objects that have compiled
    DOC> with errors.
    DOC>
    DOC> If the number is higher than expected, please examine the error
    DOC> messages reported with each object (using SHOW ERRORS) to see if they
    DOC> point to system misconfiguration or resource constraints that must be
    DOC> fixed before attempting to recompile these objects.
    DOC>#

    OBJECTS WITH ERRORS
    -------------------
                      0

    DOC> The following query reports the number of errors caught during
    DOC> recompilation. If this number is non-zero, please query the error
    DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
    DOC> are due to misconfiguration or resource constraints that must be
    DOC> fixed before objects can compile successfully.
    DOC>#

    ERRORS DURING RECOMPILATION
    ---------------------------
                              0


    Function created.


    PL/SQL procedure successfully completed.


    Function dropped.

    ...Database user "SYS", database schema "APEX_040200", user# "98" 10:47:42
    ...Compiled 0 out of 2998 objects considered, 0 failed compilation 10:47:43
    ...263 packages
    ...255 package bodies
    ...453 tables
    ...11 functions
    ...16 procedures
    ...3 sequences
    ...458 triggers
    ...1322 indexes
    ...207 views
    ...0 libraries
    ...6 types
    ...0 type bodies
    ...0 operators
    ...0 index types
    ...Begin key object existence check 10:47:43
    ...Completed key object existence check 10:47:43
    ...Setting DBMS Registry 10:47:43
    ...Setting DBMS Registry Complete 10:47:43
    ...Exiting validate 10:47:43

    PL/SQL procedure successfully completed.

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

    Total System Global Area 1636814848 bytes
    Fixed Size                  2288968 bytes
    Variable Size            1056965304 bytes
    Database Buffers          570425344 bytes
    Redo Buffers                7135232 bytes
    Database mounted.
    Database opened.
    SQL> show parameter max_string_size

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    max_string_size                      string      EXTENDED


    SQL> connect test/test
    Connected.
    SQL> create table employee (emp_id integer, emp_name varchar2(20), life_story varchar2(32000));

    Table created.

    SQL> desc employee
     Name                                      Null?  Type
     ----------------------------------------- ------ ---------------
     EMP_ID                                           NUMBER(38)
     EMP_NAME                                         VARCHAR2(20)
     LIFE_STORY                                       VARCHAR2(32000)

    SQL> select segment_name, segment_type from user_segments;

    no rows selected

    SQL> insert into employee values (1,'John','Wage Slave');

    1 row created.

    SQL> commit;

    Commit complete.

    Looking at the segments that exist we can see that by creating a TABLE with an extended VARCHAR2 column this was actually implemented using a TABLE, LOBSEGMENT, and associated LOBINDEX.

    SQL> select segment_name, segment_type from user_segments;

    SEGMENT_NAME                          SEGMENT_TYPE
    ------------------------------------- ---------------------------
    EMPLOYEE                              TABLE
    SYS_IL0000092103C00003$$              LOBINDEX
    SYS_LOB0000092103C00003$$             LOBSEGMENT




    Categories: DBA Blogs

    Oracle 12c New Features - DBMS_UTILITY.EXPAND_SQL_TEXT

    ContractOracle - Thu, 2013-06-27 20:34
    As a DBA you occasionally get handed SQL statements many hundreds of lines long, and asked to help tune it.  Often the SQL selects from views based on views based on views, which can force the database to access the same table multiple times and join to itself, leading to poor performance.  To work out what the sql is actually doing the DBA needs to extract the sql from every view, merge it, then try to work out if it can be improved.  This can be time consuming, but 12c has introduced DBMS_UTILITY.EXPAND_SQL_TEXT to help.

    The following gives and example of expanding a simple sql statement based on a view.


    SQL> create table employee (emp_id integer, emp_name varchar2(20));

    Table created.

    SQL> insert into employee values (1,'John');

    1 row created.

    SQL> insert into employee values (2,'David');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> create view v1 as select * from employee;

    View created.

    SQL> create view v2 as select * from employee;

    View created.

    If we were just given the following piece of SQL we could run it through dbms_utility.expand_sql_text to get a better idea of what the logic in v1 and v2 is.  

    SQL> select * from v1 union select * from v2;

        EMP_ID EMP_NAME
    ---------- --------------------
             1 John
             2 David

    SQL> set linesize 32000 pagesize 0 serveroutput on
    SQL> declare
       original_sql clob :='select * from v1 union select * from v2';
       expanded_sql clob := empty_clob();
    begin
        dbms_utility.expand_sql_text(original_sql,expanded_sql);
        dbms_output.put_line(expanded_sql);
    end;
    /  
    (SELECT "A3"."EMP_ID" "EMP_ID","A3"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A4"."EMP_ID" "EMP_ID","A4"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A4") "A3")UNION (SELECT "A2"."EMP_ID" "EMP_ID","A2"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A5"."EMP_ID" "EMP_ID","A5"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A5") "A2")

    PL/SQL procedure successfully completed.

    So we can see from the expanded query that the original sql was just doing a union of two identical selects from table TEST.EMPLOYEE, which we can easily simplify to a single query with better performance.

    # I found the following reference from Jonathan Lewis which indicates this procedure previously existed in package DBMS_SQL2
    Categories: DBA Blogs

    KScope 13 - Wednesday

    Brent Martin - Thu, 2013-06-27 05:14

    I just made it back from the Mardi Gras celebration and my decision to blog at this point may not be my smartest decision ever but I feel like it’s now or never so here it goes.


    Tom's Top Twelve Things About the Latest Generation of Database Technology


    Tom Kyte


    Started the morning with Tom Kyte and his session on Oracle 12C.  Say what you want about Oracle, but they keep innovating and improving their database platform.  There are a lot of features that we’ve been waiting for a long time (like defaulting to a sequence, default if null, etc) and being able to embed PL/SQL inline in an SQL is way cool.  But I was blown away with the new Row Pattern Matching functionality, and the Pluggable Database options will be very valuable to cloud providers and other companies who like to consolidate databases onto shared hardware and offer database as a service to the company.  Download the deck and get familiar with the new features.


    OBIEE and Essbase Integration with BI Foundation Suite 11.1.1.7


    Mark Rittman


    Who knew Mark Rittman was from the UK?!  Anyway he had a great presentation about how to integrate Essbase with OBI, what kinds of problems you might run into, and what works well as well as what doesn’t.  Most of this stuff has been blogged about on the Rittman Mead blog so if you missed the session don’t worry.


    What I heard was that BI Foundation 11.1.1.7 was just released back in April, but it has a lot of features that may make it work upgrading to.  Smartview can now report from the OBI logical model.  A lot of the Essbase integration wasn’t all that new (we’ve been able to plug Essbase into OBI’s semantic layer for some time), but with 11.1.1.7 Oracle has aligned the security managers. This allowed Oracle to get past some technical challenges around the integration and now the Essbase workspace can run inside of OBI.  It’s cool that Financial Reports can also run within this workspace so we’re getting closer to an integrated reporting view.


    There’s also a developer preview where you can spin an Essbase cube off of OBI content.  It doesn’t have a lot of options, but it works and automates most of the steps that previously would have been manual.  Good stuff.


    Beyond the Import Metadata Wizard


    John McGale


    John continued with the Essbase/OBI integration theme, but he was coming from the OBI side and had some great slides about how the Essbase and OBI disciplines were different.  I’m not an expert in either one, but what I heard that the data warehouse (OBI) discipline is all about star schemas and the Kimball methodology.  Essbase is all about how accountants think about their financial data and data is stored in hierarchies at the intersection of dimensions.  I might have heard him say that OBI can outperform Essbase.  Unfortunately I had to duck out for a conference call and to avoid a fistfight that had broken out but I do plan to download the slides just to see what I may have missed.


    Streamline your Financial Close with an Integrated Tax Provision


    Janette Kosior


    New Orleans can be a crazy place, and after a night on Bourbon Street I thought I had seen it all.  Then I went to the HFM Integrated Tax Provision session.  Wow.


    Ok, I shouldn’t give the tax accountants a hard time.  Heck, I doubt if any are actually at this conference since most of the audience (the ones wearing clothes anyway) were Hyperion admins.  But something about the topic had everybody in a party mood.


    What happens in the Integrated Tax Provision session stays in the Integrated Tax Provision session, but I’m going to bend the code a bit to tell you something about this new application from Oracle.  First, it’s all built on top of HFM.  And why not? HFM is the perfect platform to leverage the dimensionality, complex business rules and robust reporting needed to deliver nice tax provision functionality.


    So it’s deployed as a separate HFM-on-steroids application.  It has to be kept in sync with HFM which can be done through EPMA, which is more than you can say for CorpTax or your spreadsheets.   This is an initial release, but the speaker seemed confident that all of the bells and whistles needed were there and the system is rock solid.  There’s even a Financial Management Analytics Module that will let you do some awesome tax reports and dashboards.


    On the down side, you have to buy a new license and pay maintenance on it (buzz kill), and the analytics module would be yet another license and maintenance (major buzz kill).


    This is part of Oracle’s close manager, which automates your end-to-end close process and helps you build a world-class close process.  And that's enough to make accountants want to  P A R T Y!!!


     

    Read More...

    Oracle 12c New Features - Unified Auditing

    ContractOracle - Thu, 2013-06-27 02:07
    Oracle 12c introduces Unified Auditing, which consolidates database audit records including :-
    • DDL, DML, DCL
    • Fine Grained Auditing (DBMS_FGA)
    • Oracle Database Real Application Security
    • Oracle Recovery Manager 
    • Oracle Database Vault 
    • Oracle Label Security 
    • Oracle Data Mining 
    • Oracle Data Pump
    • Oracle SQL*Loader Direct Load
      The data is stored in the AUDSYS schema / SYSAUX tablespace.

        By default Unified Auditing is not enabled.  To enable it, shutdown the database and listener and relink :-

        make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

        Then start the listener and database and confirm it is enabled.

        SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; 

        VALUE
        ---------------------------------------------------------------TRUE

        Unified Auditing can be configured to queue writes of audit data in SGA to improve performance, or immediately write to disk to reduce data loss in case of crash.


        To configure immediate write :-

        BEGIN 
        DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(  
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,  
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,  
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
        END;
        /


        To configure queued writes :-

        BEGIN   
        DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(   
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,      
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,   
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE); 
        END; 
        /

        12C has a new parameter unified_audit_sga_queue_size. I did not change this.

        SQL> show parameter unified

        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ----------------
        unified_audit_sga_queue_size         integer     1048576

        New roles AUDIT_ADMIN and AUDIT_VIEWER are required to administer unified auditing.

        SQL> create user auditor identified by auditor;

        User created.

        SQL> grant create session to auditor;

        Grant succeeded.

        SQL> grant AUDIT_ADMIN to auditor;

        Grant succeeded.

        Create an audit policy with an action to capture SELECT on table TEST.SALARY.  It would also be possible to capture events DDL, RMAN, FGA, Data Pump etc.

        SQL> connect auditor/auditor@T12P1
        Connected.
        SQL> CREATE AUDIT POLICY audit_salary
        ACTIONS SELECT ON TEST.SALARY;


        Audit policy created.


        Enable the policy for user "nobody"

        SQL> AUDIT POLICY audit_salary by nobody;

        Audit succeeded.


        Login as "nobody" and select from table TEST.SALARY.

        SQL> connect nobody/nobody@T12P1
        Connected.
        SQL> select * from test.salary;

        no rows selected

        To flush the audit data to table execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL :-

        SQL> connect / as sysdba
        Connected.
        SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;PL/SQL procedure successfully completed.

        Audit data can be extracted from table UNIFIED_AUDIT_TRAIL. You can see below there are audit records for the LOGON and the SELECT.


        SQL> connect auditor/auditor@T12P1
        Connected.


        SQL> SELECT ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL
        WHERE DBUSERNAME = 'NOBODY';

        ACTION_NAME
        ----------------------------------------------------------------
        OBJECT_SCHEMA
        ------------------------------
        OBJECT_NAME
        ----------------------------------------------------------------
        EVENT_TIMESTAMP
        ----------------------------------------------------------------
        SELECT
        TEST
        SALARY
        27-JUN-13 03.24.07.677753 PM

        LOGON


        27-JUN-13 03.24.02.215469 PM


        To upload OS audit files to the DB :-
        EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
        SQL> desc unified_audit_trail
         Name                                      Null?    Type
        ----------------------------------------- -------- ----------------------------
        AUDIT_TYPE VARCHAR2(64)
        SESSIONID NUMBER
        PROXY_SESSIONID NUMBER
        OS_USERNAME VARCHAR2(30)
        USERHOST VARCHAR2(128)
        TERMINAL VARCHAR2(30)
        INSTANCE_ID NUMBER
        DBID NUMBER
        AUTHENTICATION_TYPE VARCHAR2(1024)
        DBUSERNAME VARCHAR2(30)
        DBPROXY_USERNAME VARCHAR2(30)
        EXTERNAL_USERID VARCHAR2(1024)
        GLOBAL_USERID VARCHAR2(32)
        CLIENT_PROGRAM_NAME VARCHAR2(48)
        DBLINK_INFO VARCHAR2(4000)
        XS_USER_NAME VARCHAR2(128)
        XS_SESSIONID RAW(33)
        ENTRY_ID NUMBER
        STATEMENT_ID NUMBER
        EVENT_TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE
        ACTION_NAME VARCHAR2(64)
        RETURN_CODE NUMBER
        OS_PROCESS VARCHAR2(16)
        TRANSACTION_ID RAW(8)
        SCN NUMBER
        EXECUTION_ID VARCHAR2(64)
        OBJECT_SCHEMA VARCHAR2(30)
        OBJECT_NAME VARCHAR2(128)
        SQL_TEXT CLOB
        SQL_BINDS CLOB
        APPLICATION_CONTEXTS VARCHAR2(4000)
        CLIENT_IDENTIFIER VARCHAR2(64)
        NEW_SCHEMA VARCHAR2(30)
        NEW_NAME VARCHAR2(128)
        OBJECT_EDITION VARCHAR2(30)
        SYSTEM_PRIVILEGE_USED VARCHAR2(1024)
        SYSTEM_PRIVILEGE VARCHAR2(40)
        AUDIT_OPTION VARCHAR2(40)
        OBJECT_PRIVILEGES VARCHAR2(19)
        ROLE VARCHAR2(30)
        TARGET_USER VARCHAR2(30)
        EXCLUDED_USER VARCHAR2(30)
        EXCLUDED_SCHEMA VARCHAR2(30)
        EXCLUDED_OBJECT VARCHAR2(128)
        ADDITIONAL_INFO VARCHAR2(4000)
        UNIFIED_AUDIT_POLICIES VARCHAR2(4000)
        FGA_POLICY_NAME VARCHAR2(30)
        XS_INACTIVITY_TIMEOUT NUMBER
        XS_ENTITY_TYPE VARCHAR2(32)
        XS_TARGET_PRINCIPAL_NAME VARCHAR2(30)
        XS_PROXY_USER_NAME VARCHAR2(30)
        XS_DATASEC_POLICY_NAME VARCHAR2(30)
        XS_SCHEMA_NAME VARCHAR2(30)
        XS_CALLBACK_EVENT_TYPE VARCHAR2(32)
        XS_PACKAGE_NAME VARCHAR2(30)
        XS_PROCEDURE_NAME VARCHAR2(30)
        XS_ENABLED_ROLE VARCHAR2(30)
        XS_COOKIE VARCHAR2(1024)
        XS_NS_NAME VARCHAR2(30)
        XS_NS_ATTRIBUTE VARCHAR2(4000)
        XS_NS_ATTRIBUTE_OLD_VAL VARCHAR2(4000)
        XS_NS_ATTRIBUTE_NEW_VAL VARCHAR2(4000)
        DV_ACTION_CODE NUMBER
        DV_ACTION_NAME VARCHAR2(30)
        DV_EXTENDED_ACTION_CODE NUMBER
        DV_GRANTEE VARCHAR2(30)
        DV_RETURN_CODE NUMBER
        DV_ACTION_OBJECT_NAME VARCHAR2(128)
        DV_RULE_SET_NAME VARCHAR2(90)
        DV_COMMENT VARCHAR2(4000)
        DV_FACTOR_CONTEXT VARCHAR2(4000)
        DV_OBJECT_STATUS VARCHAR2(1)
        OLS_POLICY_NAME VARCHAR2(30)
        OLS_GRANTEE VARCHAR2(30)
        OLS_MAX_READ_LABEL VARCHAR2(4000)
        OLS_MAX_WRITE_LABEL VARCHAR2(4000)
        OLS_MIN_WRITE_LABEL VARCHAR2(4000)
        OLS_PRIVILEGES_GRANTED VARCHAR2(30)
        OLS_PROGRAM_UNIT_NAME VARCHAR2(30)
        OLS_PRIVILEGES_USED VARCHAR2(128)
        OLS_STRING_LABEL VARCHAR2(4000)
        OLS_LABEL_COMPONENT_TYPE VARCHAR2(12)
        OLS_LABEL_COMPONENT_NAME VARCHAR2(30)
        OLS_PARENT_GROUP_NAME VARCHAR2(30)
        OLS_OLD_VALUE VARCHAR2(4000)
        OLS_NEW_VALUE VARCHAR2(4000)
        RMAN_SESSION_RECID NUMBER
        RMAN_SESSION_STAMP NUMBER
        RMAN_OPERATION VARCHAR2(20)
        RMAN_OBJECT_TYPE VARCHAR2(20)
        RMAN_DEVICE_TYPE VARCHAR2(5)
        DP_TEXT_PARAMETERS1 VARCHAR2(512)
        DP_BOOLEAN_PARAMETERS1 VARCHAR2(512)
        DIRECT_PATH_NUM_COLUMNS_LOADED NUMBER


        More details can be found in the documentation :- http://docs.oracle.com/cd/E16655_01/network.121/e17607/audit_config.htm
        Categories: DBA Blogs

        Oracle 12c - need to start container AND pluggable databases.

        ContractOracle - Thu, 2013-06-27 01:56
        In Oracle 12c we start the Container Database (CDB) the same as we started previous database versions.

        [oracle@rac1 lib]$ sqlplus / as sysdba

        SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013

        Copyright (c) 1982, 2013, Oracle.  All rights reserved.

        Connected to an idle instance.

        SQL> startup
        ORACLE instance started.

        Total System Global Area 1653518336 bytes
        Fixed Size                  2289016 bytes
        Variable Size             989856392 bytes
        Database Buffers          654311424 bytes
        Redo Buffers                7061504 bytes
        Database mounted.
        Database opened.

        But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

        SQL> select name, open_mode from v$pdbs;

        NAME                           OPEN_MODE
        ------------------------------ ----------
        PDB$SEED                       READ ONLY
        PDB1                           MOUNTED
        PDB2                           MOUNTED
        PDB3                           MOUNTED

        From CDB$ROOT we can manage any PDB.  

        SQL> show con_name

        CON_NAME
        ------------------------------
        CDB$ROOT

        To open one PDB :-

        SQL> alter pluggable database PDB1 open;

        Pluggable database altered.

        To open ALL PDBS :-

        SQL> alter pluggable database all open;

        Pluggable database altered.

        SQL> select name, open_mode from v$pdbs;

        NAME                           OPEN_MODE
        ------------------------------ ----------
        PDB$SEED                       READ ONLY
        PDB1                           READ WRITE
        PDB2                           READ WRITE
        PDB3                           READ WRITE

        Or we can move down to a PDB container to stop and start them individually.

        SQL> alter session set container=PDB1;

        Session altered.

        SQL> show con_name

        CON_NAME
        ------------------------------
        PDB1

        SQL> shutdown;
        Pluggable Database closed.
        SQL> startup;
        Pluggable Database opened.


        Startup of PDBs can be automated using a startup trigger.

        SQL> create or replace trigger Sys.After_Startup
                                  after startup on database
        begin
           execute immediate 'alter pluggable database all open';
        end;
        /  

        Trigger created.

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

        Total System Global Area 1636814848 bytes
        Fixed Size                  2288968 bytes
        Variable Size             973079224 bytes
        Database Buffers          654311424 bytes
        Redo Buffers                7135232 bytes
        Database mounted.
        Database opened.

        SQL> select name, open_mode from v$pdbs;

        NAME                           OPEN_MODE
        ------------------------------ ----------
        PDB$SEED                       READ ONLY
        PDB1                           READ WRITE
        PDB2                           READ WRITE
        PDB3                           READ WRITE

        Categories: DBA Blogs

        Pages

        Subscribe to Oracle FAQ aggregator