Installing the 12c management agent using the RPM method

2013/06/20 by

In one of my earlier posts I had described the 12c agent silent method installation

This note explains another silent procedure used to install a 12c management agent but this time using a .rpm file.

Currently the Agent RPM is only available on Linux 32-bit and 64-bit platforms.

At a high level these are the steps involved:

  • Download the .rpm file on the OMS host server using EMCLI
  • Copy the .rpm file from the OMS host to the destination host where we want to install the agent.
  • Install the .rpm file.

It should be noted that using the RPM method, the agent gets installed under a directory /usr/lib/oracle and this cannot be amended manually at this time.

It is documented in the MOS note:

EM 12c: Enterprise Manager 12.1.0.2 Cloud Control Agent Deployment with the RPM Method Does not Allow Passing a Custom Agent Base Directory Location [ID 1531693.1]

 

Installation Procedure

On the OMS host login to the EMCLI client.

After logging in, synchronize the EMCLI and check the platforms for which we currently have the agent software available in our Software Library.

[oracle@kens-oem-prod bin]$ ./emcli login -username=sysman
Enter password :

Login successful

[oracle@kens-oem-prod bin]$ ./emcli sync
Synchronized successfully

[oracle@kens-oem-prod bin]$ ./emcli get_supported_platforms
Getting list of platforms ...
Check the logs at /u01/app/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/agent.log
About to access self-update code path to retrieve the platforms list..
Getting Platforms list  ...
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Platforms list displayed successfully.

Download the .rpm file from the Software Library to a temporary location on the OMS host.

Note – we had to create the directory /usr/lib/oracle on the OMS host as well otherwise the get_agentimage_rpm command failed and this was the error which was logged:

“Directory /usr/lib/oracle doesnt exist. Please create the directory with write permissions and then retry the emcli command.”

[oracle@kens-oem-prod bin]$ ./emcli get_agentimage_rpm -destination=/u01/stage -platform="Linux x86-64" -version="12.1.0.2.0"
Platform:Linux x86-64
Destination:/u01/stage
Exalogic:false
 Checking for disk space requirements...
 === Partition Detail ===
Space free : 9 GB
Space required : 1 GB
RPM creation in progress ...
Check the logs at /u01/app/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_rpm_2013-06-19_09-53-32-AM.log
Copying agent image from software library to /u01/stage
Setting property ORACLE_HOME to:/u01/app/Middleware/oms
calling pulloneoffs with arguments:/u01/app/Middleware/oms/u01/app/Middleware/oms/sysman/agent/12.1.0.2.0_AgentCore_226.zip12.1.0.2.0Linux x86-64/u01/stagetrue
Agent Image copied successfully...
Creation of RPM started...
Agent image to rpm conversion failed

 

After creating the directory on the OMS host, we ran the command again and it still failed. This time with a different error message.

“RPM creation failed…
/u01/app/Middleware/oms/install/rpm/rpm.sh: line 61: rpmbuild: command not found”

So we need to install the rpmbuild package as it was not present on our OMS host.

We installed it using the yum command ‘yum install rpm-build‘

Confirmed the package was now present.

[root@kens-oem-prod bin]# which rpmbuild
/usr/bin/rpmbuild

Now the command ran fine and the .rpm file was successfully created.

[oracle@kens-oem-prod bin]$ ./emcli get_agentimage_rpm -destination=/u01/stage -platform="Linux x86-64" -version="12.1.0.2.0"
Platform:Linux x86-64
Destination:/u01/stage
Exalogic:false
 Checking for disk space requirements...
 === Partition Detail ===
Space free : 9 GB
Space required : 1 GB
RPM creation in progress ...
Check the logs at /u01/app/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_rpm_2013-06-19_10-08-55-AM.log
Copying agent image from software library to /u01/stage
Setting property ORACLE_HOME to:/u01/app/Middleware/oms
calling pulloneoffs with arguments:/u01/app/Middleware/oms/u01/app/Middleware/oms/sysman/agent/12.1.0.2.0_AgentCore_226.zip12.1.0.2.0Linux x86-64/u01/stagetrue
Agent Image copied successfully...
Creation of RPM started...
RPM creation successful.
Agent image to rpm conversion completed successfully
[oracle@kens-oem-prod bin]$ cd /u01/stage

[oracle@kens-oem-prod stage]$ ls -lrt
total 480384
-rw-r----- 1 oracle oinstall 255521471 May  8 11:34 12.1.0.2.0_AgentCore_233.zip
-rw-r----- 1 oracle oinstall 235885551 Jun 19 10:13 oracle-agt-12.1.0.2.0-1.0.x86_64.rpm

Next copy the .rpm file to the target server where we wish to install the 12c agent.

 

On the target host, as root, install the .rpm file. Create the directory /usr/lib/oracle as well and give permissions.

[root@kens-orawebl-001-dev ~]# mkdir /usr/lib/oracle
[root@kens-orawebl-001-dev ~]# chown oracle:dba /usr/lib/oracle

[root@kens-orawebl-001-dev stage]# rpm -ivh oracle-agt-12.1.0.2.0-1.0.x86_64.rpm
Preparing...                ########################################### [100%]
Running the prereq
   1:oracle-agt             ########################################### [100%]
Agent RPM installation is completed successfully. Now to configure the agent follow the below steps:
1. Edit the properties file: /usr/lib/oracle/agent/agent.properties with the correct values
2. Execute the script /etc/init.d/oracle-agt RESPONSE_FILE=/usr/lib/oracle/agent/agent.properties

 

Edit the agent.properties file and add the following values as shown in this example.

[root@kens-orawebl-001-dev stage]# vi /usr/lib/oracle/agent/agent.properties
#-------------------------------------------------------------------------------
#OMS_HOST: OMS host info required to connect to OMS
#OMS_PORT: OMS port info required to connect to OMS
#AGENT_REGISTRATION_PASSWORD: Agent Registration Password needed to
#     establish a secure connection to the OMS.
#-------------------------------------------------------------------------------
OMS_HOST="kens-oem-prod.domain"
OMS_PORT="4909"
AGENT_REGISTRATION_PASSWORD='password"
#-------------------------------------------------------------------------------
#AGENT_USERNAME: User name with which the agent should be installed.
#AGENT_GROUP: Group to which the agent user belogs.
#AGENT_PORT: Port in which the agent process will come up.
#-------------------------------------------------------------------------------
AGENT_USERNAME="oracle"
AGENT_GROUP="dba"
AGENT_PORT='3876"
#-------------------------------------------------------------------------------
#ORACLE_HOSTNAME: Virtual hostname where the agent is deployed.
#Example: ORACLE_HOSTNAME=hostname.domain
#-------------------------------------------------------------------------------
ORACLE_HOSTNAME="kens-orawebl-001-dev.corporateict.domain"

 

Run the following command (as root) to complete the agent installation

[root@kens-orawebl-001-dev oracle]# /etc/init.d/oracle-agt RESPONSE_FILE=/usr/lib/oracle/agent/agent.properties
Response File:/usr/lib/oracle/agent/agent.properties
Filed Under: 12c agent, 12c Cloud Control, agent installation, emcli, rpm, Silent Installation

Featured Certification: Oracle Fusion Human Capital Management 11g Human Resources Certified Implementation Specialist

2013/06/19 by

The "Oracle Fusion Human Capital Management 11g Human Resources Certified Implementation Specialist" certification is designed for implementation consultants who possess a strong foundation and expertise implementing and configuring Oracle Fusion Human Capital Management solutions. This certification measures key knowledge and skills in areas such as Global Human Resources, Security, People Management, Profile Management, and Workforce Structures. This certification differentiates candidates in the marketplace by providing a competitive edge through proven expertise. Up-to-date training and field experience are recommended.

AVAILABILITY

This certification is available to all candidates but is geared toward members of the Oracle PartnerNetwork. OPN members earning this certification will be recognized as OPN Certified Specialists, helping OPN partner companies qualify for their "Oracle Fusion Human Capital Management 11g" Specialization. 

EARNING THE CERTIFICATION

Earning this certification requires passing a single exam - "Oracle Fusion Human Capital Management 11g Human Resources Essentials" (exam 1Z0-584). This exam has 75 questions, requires a passing score of 69%, has a time limit of 2 hours, and is available now for registration at Pearson VUE with its more than 3,100 testing centers available worldwide.

RECOMMENDED TRAINING

Although training is not required for this certification, Oracle University offers the "Fusion Applications: Global Human Resources Implementation" (partners only) and "Fusion Applications: HCM Global Human Resources" training courses to help ensure success on the certification exam. Additionally, OPN members have access to the "Oracle Fusion Human Capital Management 11g Human Resources Implementation Specialist" training course as well as further training resources in the Guided Learning Path for this certification (Oracle web account login required)..

"Please keep in mind this is a practitioner-level exam and practitioner items present on-the-job scenarios and require the ability to integrate and apply knowledge in new contexts, analyze, troubleshoot, and solve problems.  Practical, hands-on experience is strongly recommended for you to succeed with this exam.

Also, recall that Oracle Fusion Human Resources is a broad and deep product area.  This exam intentionally focuses on core feature areas, leaving all the talent management modules for the talent management exam.  For this exam, you should be well versed in topics such as Security, the Person Model, and Profile Management.  Other fundamental areas including workforce structures and required knowledge as well.  Be sure to review the Study Guide for more tips!"

-Jonathan Vinoskey, Oracle Director, Applications Partner Enablement (HCM), Oracle Corp

To get started, register now for the certification exam.

QUICK LINKS

Filed Under: /News

ODBC 32bits for Windows 64bits

2013/06/19 by

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.

You will see there is another powershell, another registry, another ODBC tool, another Oracle client.


%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe

First, we run powershell(x86)


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                                                 ORACLE_HOME_NAME
-----------                                                 ----------------
C:\oracle\product\11.2.0\client_32                          client32bit_11203

Only the Oracle 32bit client is displayed

cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"

We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).


$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os


%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe

Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                             ORACLE_HOME_NAME
-----------                             ----------------
C:\oracle\product\11.2.0\client_64      client64bit_11203

cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

Do not get confused by Windows32 and WOW64.

Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.

Filed Under: odbc, windows

Wasted Space

2013/06/19 by

Here’s a little quiz: If I take the average row length of the rows in a table, multiply by the number of rows, and convert the result to the equivalent number of blocks, how can the total volume of data in the table be greater than the total number of blocks below the table high water mark ? I’ve got three tables in a schema, and they’re all in the same (8KB block, 1M uniform extent, locally managed) tablespace, but here’s a query, with results, showing their space utilisation – notice that I gather schema stats immediately before running my query:


execute dbms_stats.gather_schema_stats(user, method_opt=>'for all columns size 1');

select
        table_name,
        avg_row_len,
        num_rows,
        round(avg_row_len * num_rows / 8100)    used_space,
        blocks
from
        user_tables
order by
        table_name
;

TABLE_NAME           AVG_ROW_LEN   NUM_ROWS USED_SPACE     BLOCKS
-------------------- ----------- ---------- ---------- ----------
T1                            85       5000         52         63
T2                            85       5000         52         18
T3                           107       5000         66         32

The division by 8,100 is a fairly crude approximation to convert to from bytes to blocks – it allows for the basic block overhead, but doesn’t make any allowance for PCTFREE, ITL entries, etc. It’s generally good enough for a ball-park figure though. So one of my tables uses about 52 blocks’ worth of space, below a highwater mark of 63 blocks – but two of the tables are, apparently, using more space than has been made available !

There are probably several mechanisms that could produce these results – without using any elaborately contrived sequences of activity – all I did was “create as select” to come up with two very simple options which fell outside the boundaries of what the simple SQL diagnostic was able to handle. Any ideas ? I’ll be posting my examples later on today.

Answers

It didn’t take long for the two answers I had in mind to appear – compression and LOBs - here are the examples I had prepared:

create table t1
as
select	* 
from
	 all_objects
where
	rownum <= 5000
;

create table t2
compress
as
select
	*
from
	all_objects
where
	rownum <= 5000
;

To calculate the row lengths, dbms_stats sums the column lengths using a simple function call (sys_op_opnsize) to get the internal column size. When the table is compressed the basic query execution code reconstructs each row before applying the function – so Oracle ends up reporting the space that would be used if the table weren’t compressed. This is why I used the same data for two different tables – so that you could compare the results from the compressed and uncompressed tables.

create table t3
as
select
	rownum		id, 
	empty_blob()	b1
from
	all_objects
where
	rownum <= 5000
;

A column that holds a LOB always holds a “lob locator” even when the LOB is stored out of row, and even when the lob is the “empty lob” (which is different from NULL). To reduce the impact on the lob index for small LOBs, a lob locator can hold a variable number of pointers to lob chunks, so the size of the basic locator may vary quite dramatically – from 20 bytes to 84 bytes. When calculating the size of the row dbms_stats always bases its calculations on the maximum possible size of a LOB locator.

In my example the in-row empty_blob() actually used 20 bytes for the locator plus a further 16 for the lob content, but Oracle used 84 + 16 in its arithmetic rather than 20 + 16, resulting in a dramatic over-estimate for the avg_row_len.

If you’re interested, this is what the empty_blob() column looked line in a block dump when in row:

In row empty_blob()
===================
col  1: [36]
 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 42 7b b0 00 10 09 00 00
 00 00 00 00 00 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    1
  LobID: 00.00.00.01.00.00.00.42.7b.b0
  Flags[ 0x02 0x0c 0x00 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
  Inode:
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]

Oracle reported the column size as 103 (84 + 16 + 1 for the lock byte, one for the flags, and one for the column length) when it should have been 39.
The Inode section (16 bytes) goes to the lob segment if you disable storage in row.


Filed Under: compression, fragmentation, infrastructure, LOBs, Oracle

EM 12c Provisioning – Performing a database software clone

2013/06/19 by

Let us have a look at the Provisioning and Patching feature in OEM 12c.

We have a ‘gold image’ of a 11.2.0.3 Oracle Database Software where the Jan 2013 PSU has been installed.

We now want to deploy this particular 11.2.0.3 version of the database software on a number of target hosts.

Note – the deployment procedure outlined below assumes that  the 12c management agent has been installed on the target hosts and the Software Library has been setup and configured in OEM 12c.

An Oracle white paper on this subject has recently been released.

Provisioning & Patching Oracle Database using Enterprise Manager 12c
Here is an overview of the steps involved.

From the menu Enterprise- Provisioning and Patching – Software Library

 

 

Create folder to hold our 11.2.0.3 database software gold image

 

 

We will now add a component to this folder

 

 

Select Oracle Database Software Clone for the component type

 

 

Enter some details to describe the Oracle Database software being cloned

 

 

Select the Oracle Home Location you will use to create the gold image from. These are existing Oracle Homes hosted on servers where 12c agents are currently running

 

 

In the Create Component from list box, select Reference Oracle Home because we are going to use this particular Oracle Home as the basis for the software clone or gold image which we are then going to store in the Software Library.

 

Review the information related to the Create Oracle Database Software Clone.

Click the Save and Upload button.

 

 

We can see that the component has been added to the Software Library but the status is showing Incomplete because the clone job is still not completed.

 

 

From the Job Activity menu we can monitor the progress of the software clone job.

 

 

Once the job has completed successfully, we can see that the status of the component 11.2.0.3 PSU Jan 2013 has changed to Ready.

 

 

Let us now deploy the software clone on a target server.

From the Enterprise menu, select Provisioning and Patching, then Database Provisioning.

Highlight the Procedure Name Provision Oracle Database and click Launch.

 

 

Select the options as shown below. We are only deploying the Oracle Software and not creating a database and this is not a Grid Infrastructure environment.

Click on the Add button to select the host where we are going to deploy the software clone.

Let us briefly discuss the significance of the icon in the shape of a lock which can be seen in several of the deployment screens.

OEM 12c has introduced a new concept of Designer and Operator roles.

An OEM user with Designer role privileges can ensure standardization in deployment procedures by locking down deployment procedure inputs. Once these input values are locked in, when another user who has now Operator role privileges runs the same deployment procedure, they cannot edit the procedure and have to run the deployment with the values which were input and locked in by the OEM user who had Designer role privileges.

 

 

Select the credentials for the target host. The deployment procedure also executes the root.sh so we have also chosen a privileged user account with sudo privileges.

 

 

We see that the tasks Setup hosts is now having the status Completed.

We now need to complete the task Deploy Software. Click Next.

 

 

Select the deployment type as Component and we see the component which we earlier saved to the Software Library displaying.

 

 

Select the location for the Oracle Base and Oracle Home as well as a temporary working directory or software staging location. This location is cleaned up after the deployment job completes.

 

 

We now see that the Deploy Software task has the status Completed.

 

 

We can schedule the job to either run immediately or at a later time. Provide a name for the deployment job.

 

 

Review the job and click Submit button.

 

We can monitor the job while it is running and can see the different stages the clone software deployment job goes through.

 

 

 

Filed Under: 12c, 12c Cloud Control, clone, deployment, provisioning, software library

Man of Steel

2013/06/18 by

I’ve just got back from watching Man of Steel at the cinema.

I went into this film with extremely low expectations. For people of my age, this is the third time round for this story, so I expected to be pretty bored from a plot perspective.

I’m going to split the film into three parts.

  1. The first part of the story concerned the birth of Kal-El and him getting sent to earth. I expected this to be really dull and a bit annoying because of Russell Crowe’s presence. Actually is turned out to be completely brilliant. If the whole film had been similar to this first section it would probably have been the greatest Sci-Fi movie I had ever seen. If all you do is go in, watch this first sequence then leave, you will have had your money’s worth, especially since it was only £3 to get in on Tuesday night. :)
  2. The second part involved Kal-El growing up and becoming Superman. I also expected this to be a little dull, but actually is was really neat. They approached this part of the story in a different way to the previous films. It worked really well and I actually felt myself starting to care about the lead character.
  3. The third part of the film was just disaster porn. I found it really dull and generic. In parts it felt like a rip-off of the last fight scene in The Matrix Revolutions, mixed in with anything Michael Bay has ever done. I found myself hoping everyone would just hurry up and die so I could go home. Sometimes I find this stuff mildly amusing, but most of the time I just zone out and wonder what I am going to get to eat on the way home…

On my way out I was listening to a few conversations. One woman said, “The acting was terrible and I am so bored with seeing buildings get blown up!” I heard a group of guys talking in the car park and their conversation distilled down to, “He just didn’t do anything for the last half of the film!”

As it stands, I enjoyed it a lot more than I expected, but after a fantastic start it degenerated into mediocrity.

Cheers

Tim…

Man of Steel was first posted on June 19, 2013 at 12:04 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
Filed Under: Films, Man of Steel

Analyzing IO at the Exadata Cell level… a simple tool for IOPS.

2013/06/18 by

Lately I have been drawn into to a fare number of discussions about IO characteristics while helping customers run benchmarks.  I have been working with a mix of developers, DBAs, sysadmin, and storage admins.  As I have learned, every group has there own perspective – certainly when it comes to IO and performance.

  • Most DBA’s want to see data from the DB point of view so AWR’s or EM works just fine.
  • Most System Admin’s look at storage from the Filesystem or ASM disk level.
  • Storage Admins want to see what is going on within the array.
  • Performance geeks like myself, like to see all up and down the stack :)

As part of pulling back the covers, I came up with a simple little tool for show IOPS at the cell level.

Mining IO statistics from cellcli

The cellsrv process collects data about various events and performance metrics in an Exadata storage cell.  I certainly am a huge fan of the table and index usage data gathered using the  ”pythian_cell_cache_extract.pl” written by Christo Kutrovsky.  It is really provides a great look inside the Exadata Smart Flash Cache.  So, this got me to thinking.  What about IOPS data?

With the introduction of the Write Back Flash cache in X3, there is much more analysis about what is going to flash vs disk – and how what is written to flash is flushed to disk.

To look at all the current metrics gathered from the storage cells in your Exadata or SuperCluster you can run “cellcli -e list metriccurrent” on all the storage cells.  The “metriccurrent” parameters are updated every minute by cellsrv to store performance data.  There are a few convient parameters that can be used to sum up all the IOPS.

  • CD_IO_RQ_R_LG_SEC + CD_IO_RQ_R_SM_SEC
  • CD_IO_RQ_W_LG_SEC + CD_IO_RQ_W_SM_SEC

These parameters shore the number of IO/sec for reads and writes.  By mining this data and breaking it down by “FD” vs “CD” you can see hit ratios for reads from an overall cell point of view, but now you can also see how many writes are going to FLASH vs DISK.

The “ciops-all.sh” script will look at all the cells and sum up all the IOPS and report the findings.  This is very useful to get a quick look at the IO profile in the cells.

[oracle@exa6db01 WB]$ ./ciops-all.sh
FLASH_READ_IOPS: 6305
DISK_READ_IOPS: 213
FLASH_WRITE_IOPS: 488203
DISK_WRITE_IOPS: 6814
TOTAL_NUMBER_OF_DRIVES: 84
WRITE_PCT_to_FLASH: 98
READ_PCT_from_FLASH: 96
IOPS_PER_DISK: 83

This can be very helpful when trying to figure out if you need to go with high performance or high capacity disks.  This case shows most IO going to the flash and only 83 IOPS are spilled to each disk.  So, with this case HC disks would be a fine choice.  With a simple modification, I made the “ciops-mon.sh” script to print out the throughput every few minutes to graph the results over time.

ciops_data_x3-2

This has been helpful as I have been investigating and explaining the inter-workings of the Exadata smart flash cache.  Hopefully, you will find this useful when trying to analyze and understand Exadata Cell level IO with your workload.


Filed under: Exadata, Linux, Oracle, Solaris, Storage
Filed Under: Exadata, linux, Oracle, solaris, Storage

SQL Developer: I’m Looking at a Record, How Do I Edit It?

2013/06/18 by

You’re doing some data clean-up, or maybe you’re just trying to answer a business question for a user, and you notice the data doesn’t look quite right.

Name that tune...

Name that tune…

You want to click in that ‘(null)’ cell and tell the world that Tyler and Perry composed that song! But, we’re in a query result set, and not a table editor. I’m the ultimate lazy-dev, so what’s the easiest way to get what I want?

Throw In a Little Copy & Paste with some Mouse Magic

Copy the WHERE and ctrl+hover into the table

Copy the WHERE and ctrl+hover into the table

  1. Copy your WHERE clause, minus the actual word ‘WHERE’
  2. Ctrl+Mouse hover over the table or view name in the FROM

    3. This should get you here:

    Every Table and View Editor Has a Filter Control

    Every Table and View Editor Has a Filter Control

    Now paste in your text from where you copied it in the worksheet and hit

    Edit away!

    Edit away!

    But Why Can’t I Just Edit the Data in The Worksheet?

    Suffice to say there are philosophical and technical challenges at play here. We’ve decided to leave the worksheet as a place where you go query and run scripts. Interactive (read+write) grids are not on the road map currently. Obviously many of you feel differently as it’s the #1 request on the Exchange.

    But instead of just ‘being stuck’ I hope you find this alternative workflow better than just writing your UPDATEs and DELETEs on your own.

    No, you can’t always get what you want
    No, you can’t always get what you want
    No, you can’t always get what you want
    But if you try sometime, you just might find
    You get what you need     — Mick Jagger/Keith Richards

Filed Under: edit, records, SQL Developer, sqldev, table, worksheet

Oracle ODBC hello world with powershell

2013/06/18 by

Demo :


cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"

Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.

It is easy to get the Oracle Home name with


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                                       ORACLE_HOME_KEY
-----------                                       ---------------
C:\oracle\product\11.1.0\client_1                 OraClient11g_home1
C:\oracle\product\11.2.0\client_1                 OraClient11g_home2

Then we create the connection (as we did in ADO or ODP) :


$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

Filed Under: odbc, windows

ORA-00600 [kwqitnmphe:ltbagi]

2013/06/18 by
ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file. This issue arises on 11.2.0.2 is fixed on 11.2.0.3 but if the db has been upgraded from 11.2.0.2 -> 11.2.0.3 its also valid.

In order to be able to use the proposed workaround verify the issue :

1.

Inside the trace file reported in alert log you find :

Error 600 in Queue Table SYS.SCHEDULER$_EVENT_QTAB

2.
connect / as sysdba
Run:
select object_type from dba_objects where owner='SYS' and object_name='SCHEDULER$_EVENT_QTAB_HIST';

You should see :

OBJECT_TYPE
-------------------
INDEX


Solution :

connect / as sysdba

Drop the problematic index

drop index sys.scheduler$_event_qtab_hist;


Purge all messgaes in the queue along ith the problematic data

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'SYS.scheduler$_event_qtab',
purge_condition => NULL,
purge_options => po);
END;
/

Recompile all invalid objects

@?/rdbms/admin/utlrp.sql




Filed Under: Uncategorized
«Older Posts