ORA-02289 While ore.sync() in Oracle R Enterprise

6/08/2012 07:24:00 PM

Gönderen Mete Karar

1 yorum

After connecting to your Oracle database through R console and run the command ore.sync, you may have faced with:

ORA-02289: sequence does not exist

error. If you trace queries running while the sync command is in progress, you will see the one below:

SELECT rq$object_seq.NEXTVAL FROM DUAL

Sequence rq$object_seq belongs to RQSYS schema and actually select is granted for this sequence to RQROLE database role. Even you granted RQROLE to the user you are trying to use R with, this sequence is not accessible to the user. So the workaround is quite simple, create synonym for the sequence, a public synonym if you intend to grant RQROLE to more than one user:

CREATE PUBLIC SYNONYM RQ$OBJECT_SEQ FOR RQSYS.RQ$OBJECT_SEQ;

Adding New Disks to an Existing ASM Disk Group

5/18/2012 06:23:00 PM

Gönderen Mete Karar

1 yorum

When the free disk space level starts getting lower and lower, it is time to add disk(s) to feed your database. Most likely and advisable, your database is running on ASM (Automatic Storage Management) which provides abstraction over physical disks perfectly. Here are the steps of adding new disks:

1. Add new disks to your server physically. Assuming your database is running on a server class system or as a virtual machine,  it is possible to add disks without shutting down the server through SCSI interface either way.

2. Force Linux to discover new disks without need to reboot:
$ echo "- - -" > /sys/class/scsi_host/host0/scan

Run the commands below to check. You should see new disks and log entries regarding them:
$ fdisk -l
$ ls /dev/sd*
$ tail -f /var/log/message

3. Create a partition on each newly discovered disks:
$ fdisk /dev/sdx
Press 'p' to list partitions and see there is none. Then 'n' to create a new one. Answer questions as p, 1, default, default respectively. Press 'w' to save configuration.

4. Mark each disk as ASM disks:
$ /etc/init.d/oracleasm createdisk <disk_name> /dev/sdx1
Marking disk "<disk_name>" as an ASM disk:                   [  OK  ]

5. To add disks to a ASM disk group, as oracle run asmca tool.


Right click the disk group to be expanded and select "Add Disks"


Select disk(s) to add from the eligible disks list and click OK. When you return the first screen notice that free space is increased.

Configuring SQL Server Plug-in for OMS 12c

5/14/2012 06:17:00 PM

Gönderen Mete Karar

0 yorum

As you might have already noticed, Oracle released agents for Windows, both 32 and 64bit, and also the SQL Server plug-in. Today I'll try to briefly explain installing and configuring Microsoft SQL Server plug-in for Enterprise Manager Cloud Control 12c. I used to make use of the plug-in on 11g Grid Control and I found it as a pretty much good way of monitoring SQL Server databases especially by means of statistics collected by OEM. Besides, being able to view all of your databases -assuming you have both Oracle and SQL Server- together on a single page is very handy.

The first step is deploying agent to your Windows server. I won't get into it in detail. In a nutshell, you need install Cygwin and configure ssh service as a prerequisite and then deploy the management agent. 

Next step is deploying the plug-in:
1. Logon to OEM console. From Setup Menu navigate to Extensibility --> Self Update, click on Plug-in. Select Microsoft SQLServer Database and download it. If you can't find it in the list, most likely it is because MOS account is not configured. You can fix it by entering your MOS credentials from Setup --> My Oracle Support --> Set Credentials or Settings and re-running the UPDATE_MY_ORACLE_SUPPORT_COMPONENT_OOB_JOB job. Setting MOS credentials is also necessary to download agent for Windows.
2. When download is finished (you can select notify me option while submitting the download job), navigate to Setup --> Extensibility -->Plug-ins. Under databases, find Microsoft SQLServer Database and deploy it to your management server first then to the agent you have deployed to Windows server.

After finishing all deployments create a sql login on the SQL Server database instance to be monitored. I found that following rights is sufficient for monitoring:
  1. "View any database", "View any definition" and "View any database" on server
  2. Public role in each database, except master and tempdb, since public server role covers necessary access to them. Since login is added to model database, newly created databases from now on will also include this grant.
  3. Execute on msdb.dbo.sp_help_job stored procedure.
To add database as a target to OEM, navigate to Setup --> Add Target --> Add Targets Manually and select "Add Non-Host Targets by Specifying Target Monitoring Properties". Target type will be "Microsoft SQL Server" and choose the agent on Windows server from the list.  On the next screen fill in the Target Name, JDBC URL, Database Username (sql login you've just created) and Password of Database User. Test the connection and click OK if it succeeds.

 Below, you may find the home screen of SQL Server target.




Oracle Database Network Bandwidth Usage

4/29/2012 03:25:00 PM

Gönderen Mete Karar

0 yorum

Even it is not in the list of DBA's responsibilities essentially, time to time you may be asked for the required bandwidth value for your database. It could be for capacity planning of the WAN line between primary and disaster recovery sites or calculating traffic towards database by means of data streams etc.

Besides various networking tools, it is possible to calculate amount of data generated by monitoring usage of redo logs. Actually this is the suggested way to find out required bandwidth for Data Guard installations by Oracle.

The  metric to calculate bandwidth is "Redo Generated Per Sec". You can schedule a job that collects statistics regularly, say hourly. Script below inserts maximum and average values of the metric into a table with time stamp:

insert into bandwidth
(
     select sysdate, max(value) as max_val, avg(value) as avg_val
     from gv$sysmetric_history 
     where metric_id = 2016
);

The formulation to calculate bandwidth is:

Required bandwidth = ((Redo rate bytes per sec. /  0.7) * 8) / 1,000,000 = bandwidth in Mbps

Since you have collected enough data, you can figure out the peak and average bandwidth:

select 
   ((max_val/0.7)*8)/1000000 as peak_mbps, 
   ((avg_val/0.7)*8)/1000000 as average_mbps
from bandwidth;

Upgrading Oracle Enterprise Manager 11g Grid Control to 12c Cloud Control with Exadata Plug-ins

4/26/2012 06:21:00 PM

Gönderen Mete Karar

1 yorum

As you might know OEM 12c Cloud Control is out there for some while, even its bundle patch 1 has been released. 12c comes with a fully re-designed user interface and new Exadata features which takes my attention the most. To take advantage of these new features I've decided to upgrade my 11g box to 12c. What I have is a 64-bit Oracle Linux 5.7 with 11gR2 database and 11g Grid Control with Exadata plugins installed.

There are three upgrade approaches suggested by Oracle. I'll follow the 1-System Upgrade Approach which is in-place upgrade with minimal downtime.  After upgrading to 12c, I'll discover my database machine through 12c console and implement Exadata 12c integration.

Upgrading 11g Grid Control to 12c Cloud Control with 1-System Upgrade Approach:

1. As in every upgrade and patching, first thing to do is upgrading OPatch. Since we'll patch OMS 11g during upgrade process, we need to upgrade OPatch under OMS_HOME. Download latest OPatch from MOS 6880880 for release 11.1 and:

$ unzip p6880880_111000_Linux-x86-64.zip -d <OMS_HOME>/

2. As a prerequisite if you haven't installed OMS 11g PSU 1 (10065631), you should install it. Actually this is the prerequisite for installing preupgrade console. Download the PSU then:

$ unzip p10065631_111010_Generic.zip
$ <OMS_HOME>/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./10065631

If no conflict is found
$ <OMS_HOME>/bin/emctl stop oms
$ cd 10065631
$ <OMS_HOME>/OPatch/opatch apply
$ export ORACLE_HOME=<OMS_HOME>
$ <OMS_HOME>/bin/rcuJDBCEngine sys/<Password>@<host_name>:<db_sid> JDBC_SCRIPT post_install_script.sql $PWD $ORACLE_HOME
$ <OMS_HOME>/bin/rcuJDBCEngine sys/<Password>@s<host_name>:<db_sid> JDBC_SCRIPT post_install_pactht.sql $PWD $ORACLE_HOME
$ <OMS_HOME>/bin/emctl start oms   

3. Now we can install preupgrade patch. You can download it from OTN.

$ unzip p13597150_111010_Generic.zip
$ <OMS_HOME>/bin/emctl stop oms
$ <OMS_HOME>/bin/emctl start oms
$ cd 13597150
$ <OMS_HOME>/OPatch/opatch apply   
$ <OMS_HOME>/bin/rcuJDBCEngine sys/<Password>@<host_name>:<db_sid> JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/puc_dblink_pkgdef.sql
$ <OMS_HOME>/bin/rcuJDBCEngine sys/<Password>@<host_name>:<db_sid> JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/puc_dblink_pkgbody.sql
$ <OMS_HOME>/bin/rcuJDBCEngine sysman/<Password>@<host_name>:<db_sid> JDBC_SCRIPT $ORACLE_HOME/sysman/preupgc/pre_upg_console.sql   
$ <OMS_HOME>/bin/emctl start oms   

4. Now we are ready to start upgrading. Upgrade starts with agents. Log on to 11g console, at the Deployment tab click on the new link "Enterprise Manager 12c Upgrade Console" then "Manage Software". At the Software Location area write the directory of new plugins and click validate. 

To have plugins you can unzip all three disks of 12c those you have downloaded from OTN or you can just extract the plugins directory from the third disk. You also need to put the new version of agent(s) you have. You can download the one(s) from OTN too. 

There are two things to notice here. First, you must not extract the agent software zip file while putting under plugins directory. And the second is that there is no agent for Windows, not yet for 64-bit version and never ever going to be for 32-bit. So if you have a 32-bit Windows agent running you will not be able to use it with 12c.

When you click on validate a job will run and analyze your agent(s). Your agent(s) might fall into one of these groups:
  • Completely Upgradable - A non-windows agent with no or upgradable plugins.
  • Missing Plug-Ins Software -  A non-windows agent with not upgradable plugins. My agents falls in this category since it is a Linux agent with Exadata plugins. Starting from 12c, Exadata features are included in OEM, so no plugin is required.
  • Missing Agent Software - Agent software is not available
  • Not Supported - A Windows agent 
If what you have is a fully green pie chart, move on to next step. You can also move on with a yellow one which is due to not upgradable plugins. Otherwise, if you have not supported windows agent give up upgrading or uninstall the agent. And if you have not upgradable plugins, remove those plugins.

5. After verifying agent(s), upgrade them by following steps below described in Oracle documentation. First three steps will create a job, you can find job output screen in each step:

a) Deploy and configure the software binaries of Oracle Management Agent 12c


b) Generate a health report and check the readiness of the predeployed Management Agents


c) Verify and sign off the health check report
 
d) Switch over the old Management Agents to the newly deployed ones so that they can communicate with Enterprise Manager Cloud Control


Check the status of upgraded agent:
$ <AGENT12c_HOME>/agent_inst/bin/emctl status agent  

Out put should be like:

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : (unknown)
Protocol Version  : 12.1.0.1.0
Agent Home        : <AGENT12c_HOME>/agent_inst
Agent Binaries    : <AGENT12c_HOME>/core/12.1.0.1.0
Agent Process ID  : 19089
Parent Process ID : 18963  
Agent URL         : https://<host>:3872/emd/main/
Repository URL    : https://<host>:4900/empbs/upload
Started at        : 
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 69
Size of XML files pending upload(MB)         : 0.07  
Available disk space on upload filesystem    : 21.46%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 
Last successful heartbeat to OMS             : (none)

---------------------------------------------------------------
Agent is Running and Ready

6. It's time to upgrade OMS. Copy emkey from the OMS to the Management Repository and stop OMS service as prerequisite and then run the graphical installer from the directory you have unzipped the 12c CDs.

$ <OMS_HOME>/bin/emctl config emkey -copy_to_repos_from_file -repos_host <host_name> -repos_port 1521 -repos_sid <db_sid> -repos_user sysman -emkey_file <OMS_HOME>/sysman/config/emkey.ora
$ <OMS_HOME>/bin/emctl stop oms
$ ./runInstaller






During prerequisite check if anything found an error screen will appear as below, click yes to make the installer fix the issue

 Also some best practices and performance recommendations may appear in a warning window. You can implement them after installation





 Run the root script when prompted for 

You may now log on to new OMS Console.

Discovering Exadata Through 12c

1. First thing to do before start monitoring Exadata through 12c is installing 12c agents to database nodes. As you know, it is strictly disallowed to install anything on storage cells, therefore all Exadata components are monitored by agents on database nodes. Download agent software from MOS, bug number 12960610. In the zip file there is file named CloudControl_Standalone_12.1.0.1.0_LINUX.X64.tar.Z, copy it to first node under /tmp/emkit directory.

An important point here to pay attention is em.param file. The file is generated by Oracle Exadata Database Machine Configurator Spreadsheet and it should be located as /opt/oracle.SupportTools/onecommand/em.param. If not, you need to generate one. Once you have one, you have to add or modify the following lines properly:

OMS_LOCATION = MY_GC
EM_BASE = / u01/app/embase
OMS_HOST = oemserver.company.com
OMS_PORT = 4900

EM_USER=oracle
EM_PASSWORD=<password>

Do check the port number by running following command on OEM server:
$ emctl status oms -details
Look for the  "Upload HTTPS Port" in the output.

To install agent, as root:
$ tar xBvpf CloudControl_Standalone_12.1.0.1.0_LINUX.X64.tar.Z
$ cd /tmp/emkit/common
$ (sh setupem.sh -emOnly -emUser <em_user> -emBase <em_base> | tee /tmp/setupem.log) 3>&1 1>&2 2>&3 | tee /tmp/setupem.err

2. After installation of OEM 12c Agent for Exadata, it is time to discover database machine. Though, before starting do check the Exadata Database Machine Schematic file located as /opt/oracle.SupportTools/onecommand/databasemachine.xml. Its version must be at least 502 and well-formed as XML (In my case, I had problems, while discovery process, due to some invalid characters in the file). Instead of trying to fix errors later the hard way,  fix simply in the beginning.

Log on to 12c console. From the Setup menu, Add Target --> Add Targets Manually. Choose "Add Non-Host Targets Using Guided Process (Also Adds Related Targets)", select  Oracle Exadata Database Machine from the Target Types and click "Add Using Guided Discover".  To start the discovery wizard select "Discover a new Database Machine and its hardware components as targets" and click Discover Targets.

Go till the end of the wizard, process takes a little while to complete. At the end, you got your Exadata manageable via 12c Cloud Control.

Installing Oracle R Enterprise

3/20/2012 07:02:00 PM

Gönderen Mete Karar

0 yorum

Oracle has integrated popular statistical package R as a component of database's Advanced Analytics Option. Oracle R Enterprise comes with Big Data integrated too. It also can be installed separately to 11 R2 databases on Oracle Linux 5 64-bit and naturally to Exadata. I'll try to briefly explain how you can install it.

Prerequites:

1. If not already enabled, enable yum repos for Oracle. You can find out how from Oracle public yum site.
2. Install R

$ yum list  R
$ yum install R.x86_64
3. Patch your database if its version is not 11.2.0.3. Instal patch number 12598677 for 11.2.0.1 and  129765544 for 11.2.0.2.

Installing Client on Windows:

1. Next thing to be done is installing client tool. Download R for Windows from R Project and install it.
2. Download Oracle R Enterprise Client Packages and Client Supporting Packages for Windows from OTN and unzip them.
3. To install packages; run R from All Programs as administrator, from the menu: Packages -> Install package(s) from local zip files:
   a. Navigate to <unzip_dir>\ore-supporting-windows-1.0\bin\windows\contrib\2.13
   b. Select; DBI_0.2-5.zip, png_0.1-4.zip and ROracle_1.1-1.zip
   c. Click open:
        package 'DBI' successfully unpacked and MD5 sums checked
        package 'png' successfully unpacked and MD5 sums checked
        package 'ROracle' successfully unpacked and MD5 sums checked
   d. Repeat the step for client packages. Navigate to <unzip_dir>\ore-windows-1.0\bin\windows\contrib\2.13
   e. Select; ORE_1.0.zip, OREbase_1.0.zip, OREeda_1.0.zip, OREgraphics_1.0.zip, OREstats_1.0.zip, ORExml_1.0.zip
   f. Click open:
        package 'ORE' successfully unpacked and MD5 sums checked
        package 'OREbase' successfully unpacked and MD5 sums checked
        package 'OREeda' successfully unpacked and MD5 sums checked
        package 'OREgraphics' successfully unpacked and MD5 sums checked
        package 'OREstats' successfully unpacked and MD5 sums checked
        package 'ORExml' successfully unpacked and MD5 sums checked

Installing Server:

1. To install server download ore-server-linux-x86-64-1.0.zip from OTN.
2. Extract files
$ unzip ore-server-linux-x86-64-1.0.zip
$ tar xzf ore-server-linux-x86-64-1.0.tar.gz
3. Set environment variabeles
$ export R_HOME=/usr/lib64/R
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
4. Install
$ cd ore-server-linux-x86-64-1.0/
$ ./install.sh
5. Enable R for the user:
SQL> grant rqrole to <user_name>

To connect your database through R run R client:
R> library(ORE)
R> ore.connect(user="<user_name>", sid="<DB_SID>", host="<host_name>", password="<password>", port = 1521)
R> ore.sync
R> ore.attach()
R> ore.ls()
R> help(Startup)


Core Dumps After Upgrading Grid Infrastructure to 11.2.0.2

3/13/2012 11:52:00 AM

Gönderen Mete Karar

0 yorum

After upgrading Grid Infrastructure from 11.2.0.1 to 11.2.0.2 on a single instance, you may observe huge amount of increase in disk usage due to core dump creation. You will see that a directory named like core_123 created every 10 minutes in <Grid_Home>/log/<host_name>/diskmon.

There is a bug numbered 10283819 in MOS considers the problem , however no workaround is provided. There are two ways you can follow to handle situation. First one is upgrading to 11.2.0.3. And the other one is uninstalling 11.2.0.1 completely, even remove the empty home, and restart all Oracle services.

Subscribe to: Comments (Atom)