Oracle Database 12c Release 1

Oracle Database 12cR1 Released ..

URL :  http://www.oracle.com/technetwork/database/enterprise-edition/overview/index.html




The Learn More Page contains the OBE and videos (inclding :  

Oracle Multitenant Architecture )

ssh Password Less Login

Follow the below steps to perform the ssh passwordless login,

1.Login to the Server and run the below to generate the Key pair.

Sri> ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/sri/.ssh/id_rsa):

Created directory '/home/sri/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/sri/.ssh/id_rsa.

Your public key has been saved in /home/sri/.ssh/id_rsa.pub.

The key fingerprint is:

7y:74:10:98:25:06:17:d7:49:b7:u2:46:2i:jc:10:92 sri@beetle

The key's randomart image is:

+--[ RSA 2048]----+

|          ++B=oo=|

|         oE+..+.*|

|        . o  o = |

|         . o  . o|

|        S + .  . |

|         . o     |

|                 |

|                 |

|                 |

+-----------------+

2. The Private Keys gets genenrated .The Public Key gets created in the "/home/sri/.ssh/id_rsa.pub". 

Sri> cat /home/sri/.ssh/id_rsa.pub

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAouNqZ7+sK0P2zjf+y6pM1CY4jCZTD+CVzfjYyiAB7VhpIkn70MiPiNcbbrNCmIr4v+9U64Mm4ioAz9V4WJOhZ6+1/QLMhPpIhZcq7D1wfc26xU/b4moyzzygX8WaNd2D1XRX5JZMEcz/ir4C6wTFG9lL9Y9iKkKK2/sJHYGCDHJemxsw9wY4gH1fvrfdY/1M/rG4OOMrHsp9s6PB+1v+G7lEIHozDkPBDZx0ypSM4YNKZR6ddPt+GpcWU0RlS9X0eMeFljtWawmr1W90p/KeqwxQaVhl6YVZnQw1dJ0T5YUQIEhQcHJdJpbOfkN962D8dGJYGnjnTN2vJw83xoB/CQ== sri@beetle

3.To perform the Login without the Password copy the Public Key content (output is One line ) to the ".ssh/authorized_keys" on the remote System .
   The copy to the remote system can be done using scp or you can use the ssh as below

Sri> ssh remote -l sri "echo ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAouNqZ7+sK0P2zjf+y6pM1CY4jCZTD+CVzfjYyiAB7VhpIkn70MiPiNcbbrNCmIr4v+9U64Mm4ioAz9V4WJOhZ6+1/QLMhPpIhZcq7D1wfc26xU/b4moyzzygX8WaNd2D1XRX5JZMEcz/ir4C6wTFG9lL9Y9iKkKK2/sJHYGCDHJemxsw9wY4gH1fvrfdY/1M/rG4OOMrHsp9s6PB+1v+G7lEIHozDkPBDZx0ypSM4YNKZR6ddPt+GpcWU0RlS9X0eMeFljtWawmr1W90p/KeqwxQaVhl6YVZnQw1dJ0T5YUQIEhQcHJdJpbOfkN962D8dGJYGnjnTN2vJw83xoB/CQ== sri@beetle  >>~/.ssh/authorized_keys"

Enter the password when its prompted and the authorised_keys of Remote system is updated and now you can login without Password to the remote system.

snippets - SQL Developer

A nice feature of SQL developer, SNIPPETS.





You can bring up the Snippet toolbar by clicking on
View --> Snippets













It opens the snippet toolbar at the right pane with the default snippets
The Default snippets has the queries, the functions and a lot of good options. 
Also we can add a snippet of our wish and run it whenever we want.
Its really a good tool to be used in your Sql- developer ... Try ii

Queries to monitor Temporary Tablespace

Temp Usage


  set linesize 120
  col v_size_GB format 99.9999
  col TABLESPACE format a15
  col USERNAME format a15

  SELECT t.USERNAME,
    t.tablespace,
    s.sid,
    spid OS_ID,
    s.serial#,
    ROUND(blocks*8192/1024/1024/1024 , 5) v_size_GB,
    s.logon_time,
    (s.last_call_et/60) Session_Time_Minutes
  FROM v$tempseg_usage t,
    v$session s,
    v$process p
  WHERE t.SESSION_ADDR=s.saddr
  AND p.addr=s.paddr;


Temp USED/FREE 



  SELECT   A.tablespace_name tablespace, D.mb_total,
           SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
           D.mb_total - 
           SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
  FROM     v$sort_segment A,
           (
           SELECT   B.name, C.block_size,
                    SUM (C.bytes) / 1024 / 1024 mb_total
           FROM     v$tablespace B, v$tempfile C
           WHERE    B.ts#= C.ts#
           GROUP BY B.name, C.block_size
           ) D
  WHERE    A.tablespace_name = D.name
  GROUP by A.tablespace_name, D.mb_total;

NULL = NULL ::: sys_op_map_nonnull

Today came across this feature " sys_op_map_nonnull " which allows you to compare NULL = NULL .

Lets make our hands dirty .......

Create the Table :

create table p2 (
c number,
d number primary key);



>desc p2
 Name         Null?    Type
 ---------- -------- ----------------
 C                     NUMBER
 D            NOT NULL NUMBER

Insert the Data :

insert into p1 values (NULL,2);

insert into p2 values (NULL,1);

insert into p2 values (NULL,3);

insert into p2 values (2,4);

>select * from p2;

         C          D

---------- ----------

                    1

                    3

         2          4

Select the data by comparing column C with Column C :

>select * from p2 where c=c;

         C          D

---------- ----------

         2          4

one of the Traditional Way to resolve NULL compare:

>select * from p2 where (c=c or (c is null and c is null));

         C          D

---------- ----------

                    1

                    3

         2          4

Now try with sys_op_map_nonnull,

>select * from p2 where sys_op_map_nonnull(c)=sys_op_map_nonnull(c);

         C          D

---------- ----------

                    1

                    3

         2          4

SQLPATH - variable for DBA

Configuring the SQLPATH will make make the job of running the".sql" files more easier.

SQLPATH is a variable which when set to a directory, The SQL*PLUS will search for the files with this directory and sub directory and runs the file.

Also we can set a file -> login.sql , it will be run by default every time you login through SQL*PLUS.
Below is a simple login.sql configuration.
login.sql


SET LINES 200
SET PAGES 10000
SET LONG 100000
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER>"
DEFINE _EDITOR=vi
COL OBJECT_NAME FOR A40

As a DBA we always do certain formatting before working on the SQL Session and if  this is set in login.sql then Oracle will take care of running it all the time we login. :-)

For this just set the following in your Linux/Unix profile ,

SQLPATH=/home/sri/sql:/home/sri/bin  ; export SQLPATH

For Windows configuration steps :

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch2.htm#i1133354

The information is documented clearly in the following link,
http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch2.htm

after setting up the SQLPATH, now you can start making the sql scripts for the regular monitor queries like sga.sql , temp_usage.sql etc, in the SQLPATH directory and you can run it anytime in the SQL session by


SRI@sridb>!pwd
/home/sri

SRI@sridb>@sga.sql

SQLPATH will help a lot :-)

11G R2 RAC OVM Templates.

It became pretty old news that Oracle released OVM templates for RAC (Real Application Clusters) , this was announced in openworld also.
This post is mainly to discuss the issues I faced during the installation and tips to do it better ,
I downloaded 11.2.0.2 ovm templates to the playaround with RAC.  
Download links :

• http://www.oracle.com/technetwork/server-storage/vm/templates-101937.html ( Database & Real Application Clusters (RAC) ).
• http://edelivery.oracle.com

1)  During the download itself I got into issues ,as I went with a Download Manager and the Checksum failed. So its verify the check sum of the downloaded zip.

2) The Installation steps are pretty much clearly described in the Document that comes along with the download.
 

3) One main page I like to mention here is the screen where the OVM template is getting added to the VM server.The user name and password entering should anything apart from root , as if we enter root over here then it changes the password of the root each time we create a VM from this template.

 So Make sure that root user is not given here.

4) In this page please be careful with the With and down arrow at the end of the configuration. 

5) The emca is not configured with the default installation, so if you like to have it installed during this then its needs to enabled in the config file .
File : /u01/racovm/params.ini
Parameter :  " CONFIGURE_DBCONSOLE= YES"


Have a nice time with RAC OVM installation.


http://edelivery.oracle.com

Oracle SQL Developer 3.0 Early Adopter 1: New Feature List

Last week Oracle SQL Developer 3.0 got released and its making news in all Blogs.

Below is the feature list and the cool additions to SQL developer.

Just downloaded and started working, its cool & good J

Eye Catching :::

DBA Functionality

DB Configuration	Ability to review and tune db config and edit initation parameters
Resource Manager	Create and prioritize consumer groups and manage resource plans
Security	Define audit settings and manage profiles, roles and users
Storage	Define and manage archive logs, control files, datafiles, redo logs, rollback segments and tablespaces

Schema Browser

Easier navigation between object types and schemas for a specific connection

Support fast client-side name based filtering for narrowing the list of objects displayed

Tuning

SQL Tuning Advisor	Analyzes submitted high-volume SQL statement and offers tuning recommendations
Diff Tool for Explain Plans	Compare selected Explain Plan or Autotrace results for any SQL statements.
Management Pack Licensing Preferences	Preferences option to track which Oracle database features are claimed to be licensed on a connection-by-connection basis

Setting TNS_ADMIN for SQL developer :

We can set the tns_admin from sql developer , see the user Document :
"You can specify the tnsnames.ora location in the Database: Advanced preferences.By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory, but it can also be in the directory specified by the TNS_ADMIN environment variable or registry value or (on Linux systems) the global configuration directory. On Windows systems, if the tnsnames.ora file exists but its connections are not being used by SQL Developer, define TNS_ADMIN as a system environment variable. For information about the tnsnames.ora file, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference."

Link ::

New Feature : http://www.oracle.com/technetwork/developer-tools/sql-developer/rel3-featurelist-ea1-166831.html

New Feature : http://www.oracle.com/technetwork/developer-tools/sql-developer/featurelist-094691.html

To Download : http://www.oracle.com/technetwork/developer-tools/downloads/ea1-downloads-176664.html