So there is a new toy in the market for database geeks : Oracle has released database 12c. Every social platform is abuzz with the 12c activity. So thought that I should also complete the ritual 
In this post Aman has already summed up many important links.
Maria Colgan has posted some useful links here.
And here is a link to a slidedeck about Upgrading and Migrating to 12c.
Happy 12c’ing !
Yesterday I was configuring EM 12c for a Sun Super Cluster system. There were a total of 4 LDOMs where I needed to deploy the agent (Setup –> Add targets –> Add targets manually). Out of these 4 everything went fine for 2 LDOMs but for the other two it failed with an error message. It didn’t give much details on the EM screen but rather gave a message to try to secure/start the agent manually. When I tried to do that manually the secure agent part worked fine but the start agent command failed with the following error message:
oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Starting agent ………………………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(3872): Retrying the operation…
Failed to start the agent after 1 attempts. Please check that the port(3872) is available.
I thought that there was something wrong with the port thing so I cleaned the agent installation, made sure that the port wasn’t being used and did the agent deployment again. This time it again failed with the same message but it reported a different port number ie 1830 agent port no:
oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Starting agent ……………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(1830): Retrying the operation…
Failed to start the agent after 1 attempts. Please check that the port(1830) is available.
Again checked few things but found nothing wrong. All the LDOMs had similar configuration so what worked for the other two should have worked for these two also.
Before starting with the installation I had noted the LDOM hostnames and IPs in a notepad file and had swapped the IPs of two LDOMs (actually these two only 
). But later on I found that and corrected. While looking at the notepad file it occurred to me that the same stuff could be wrong in /etc/hosts of the server where EM is deployed. Oh boy that is what it was. While making the entries in /etc/hosts of EM server, I copied it from the notepad and the wrong entries got copied. The IPs for these two LDOMs got swapped with each other and that was causing the whole problem.
deinstalled the agent, correct the /etc/hosts and tried to deploy again…all worked well !
Just a quick note about change in the way the compute nodes are patched starting from version 11.2.3.1.1. For earlier versions Oracle provided the minimal pack for patching the compute nodes. Starting with version 11.2.3.1.1 Oracle has discontinued the minimal pack and the updates to compute nodes are done via Unbreakable Linux Network (ULN).
Now there are three ways to update the compute nodes:
1) You have internet access on the Compute nodes. In this case you can download patch 13741363, complete the one time setup and start the update.
2) In case you don’t have internet access on the Compute nodes you can choose some intermediate system (that has internet access) to create a local repository and then point the Compute nodes to this system to install the updates.
3) Oracle will also provide all the future updates via an downloadable ISO image file (patch 14245540 for 11.2.3.1.1). You can download that ISO image file, mount it on some local system and point the compute nodes to this system for updating the rpms (the readme has all the details on how to do this).
Some useful links:
https://blogs.oracle.com/XPSONHA/entry/updating_exadata_compute_nodes_using
https://blogs.oracle.com/XPSONHA/entry/new_channels_for_exadata_11
Metalink note 1466459.1
There was an interesting issue at one of the customer sites. Few tables in the database were altered and the dependent objects became invalid. But the attempts to compile the objects using utlrp.sql or manually were failing. In all the cases it was giving the same error:
SQL> alter function SCOTT.SOME_FUNCTION compile; alter function SCOTT.SOME_FUNCTION compile * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 27 SQL>
At first look it sounded like some issue with the dictionary as the error in case of every object (be it a view, function or package) was the same.
Everybody was trying to compile the invalid objects and surprisingly few VIEWs (that were not getting compiled from SQL*Plus) got compiled from Toad ! But that didn’t explain anything. In fact it was more confusing.
Finally I enabled errorstack for event 1422 and tried to compile a view. Here is the relevant content from the trace file
----- Error Stack Dump -----
ORA-01422: exact fetch returns more than requested number of rows
----- Current SQL Statement for this session (sql_id=7kb01v7t6s054) -----
SELECT SQL_TEXT FROM V$OPEN_CURSOR VOC, V$SESSION VS WHERE VOC.SADDR = VS.SADDR AND AUDSID=USERENV('sessionid') AND UPPER(SQL_TEXT) LIKE 'ALTER%'
I took it to be some system SQL and started searching in that direction and obviously that was of no use.
In the mean time another guy almost shouted…”oh there is a trigger to capture DDL operations in the database; it must be that”. And indeed it was. Here is the code that was creating the problem:
select sql_text into vsql_text
from v$open_cursor voc, v$session vs
where voc.saddr = vs.saddr
and audsid=userenv('sessionid')
and upper(sql_text) like 'ALTER%';
As v$open_cursor was returning multiple rows, hence the problem !
Moral is that the errorstack traces do tell a lot (of course if you listen carefully) 😉
Sometimes you may need to run GoldenGate on some different machine than the one that hosts the database. It is very much possible but some kind of restrictions apply. First is that the Endian order of both the systems should be same and the second is the bit width has to be same. For example it is not possible to run GoldenGate on a 32 bit system to read from a database that runs on some 64 bit platform. Assuming that the environemnt satisfies the above two conditions; we can use the LOGSOURCE option of TRANSLOGOPTIONS to achieve this.
Here we run GG on host goldengate1 (192.168.0.109) and the database from which we want to capture the changes runs on the host goldengate3 (192.168.0.111). Both the systems run 11.2.0.2 on RHEL 5.5. On goldengate3 redo logs are in the mount point /home which has been NFS mounted on goldengate1 as /home_gg3
Filesystem 1K-blocks Used Available Use% Mounted on 192.168.0.111:/home 12184800 7962496 3593376 69% /home_gg3
The Extract parameters are as follows:
EXTRACT ERMT01 USERID ggadmin@orcl3, PASSWORD ggadmin EXTTRAIL ./dirdat/er TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /home/oracle/app/oracle/oradata/orcl /home_gg3/oracle/app/oracle/oradata/or cl, PATHMAP /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog /home_gg3/oracle/app/oracle/flash_recovery_ area/ORCL/archivelog TABLE HR.*; (The text in the line starting with TRANLOGOPTIONS is a single line)
So using PATHMAP we can make GG aware about the actual location of the red logs & archive logs on the remote server and the mapped location on the system where GG is running (It is somewhat like db_file_name_convert option for Data Guards).
We fire some DMLs on the source database and then run stats command for the Extract
GGSCI (goldengate1) 93> stats ermt01 totalsonly *
Sending STATS request to EXTRACT ERMT01 ...
Start of Statistics at 2012-05-26 05:17:05.
Output to ./dirdat/er:
Cumulative totals for specified table(s):
*** Total statistics since 2012-05-26 04:51:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
.
.
.
End of Statistics.
GGSCI (goldengate1) 94>
For more details have a look at the GG reference guide (Page 402).