Convert Single Instance to RAC – Part2: Manually Convert to RAC

Hi Everyone,

My articles published in Oracle Experts website media "Allthingsoracle.com" by RedGate

Please view the article using below link.
Convert Single Instance to RAC – Part2: Manually Convert to RAC

I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

RMAN Debug and Trace

If any RMAN operation fails, then you can use RMAN Debug to collect and diagnosis the failure. The RMAN Debug command generates more extensive output, use debugging for the following purposes.

•  To see the actual programs generated by RMAN
•  To determine the failure of RMAN Command

The RMAN debug option can be used trace RMAN session, and it will be very useful to troubleshoot an issue.

The RMAN Debug command will be available in from Oracle 8i onwards.

RMAN Debug Command

$ rman target / debug trace rman.trc log rman.log

Or
$ rman target / catalog xxx/xxxx@rmancat debug trace = /tmp/rman.trc log=/tmp/rman.log

Once you are connected to RMAN, you can run the commands that you want to debug.

The log file will shows how much percentage (%) completed and time left, It will help to estimate the time.

The rman.log file will look like below.

orclsr@oracle> cat rman.log
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 11 15:21:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN-06005: connected to target database: ORCL (DBID=2284874908408)
RMAN>

RMAN-03090: Starting backup at 11-OCT-12

RMAN-06009: using target database control file instead of recovery catalog

RMAN-08030: allocated channel: ORA_DISK_1

RMAN-08500: channel ORA_DISK_1: SID=618 device type=DISK

RMAN-08008: channel ORA_DISK_1: starting full datafile backup set

RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set

RMAN-08522: input datafile file number=00071 name=/oracle/ORCL/sapdata8/prd_57/prd.data57

RMAN-08522: input datafile file number=00073 name=/oracle/ORCL/sapdata9/prd_59/prd.data59

RMAN-08522: input datafile file number=00078 name=/oracle/ORCL/sapdata9/prd701_8/prd701.data8

RMAN-08522: input datafile file number=00069 name=/oracle/ORCL/sapdata4/prd_55/prd.data55

RMAN-08522: input datafile file number=00006 name=/oracle/ORCL/sapdata1/prd_3/prd.data3

RMAN-08522: input datafile file number=00001 name=/oracle/ORCL/sapdata1/system_1/system.data1

RMAN-08522: input datafile file number=00003 name=/oracle/ORCL/sapdata1/sysaux_1/sysaux.data1

RMAN-08522: input datafile file number=00030 name=/oracle/ORCL/sapdata1/prdusr_1/prdusr.data1

RMAN-08522: input datafile file number=00011 name=/oracle/ORCL/sapdata2/prd_8/prd.data8

RMAN-06731: command backup:1.0% complete, time left 03:27:06

RMAN-06731: command backup:1.9% complete, time left 03:32:09

The trace file will have all information running behind the screen.

The “rman.trc” will look like below

orclsr@oracle> cat rman.trc |more
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 11 15:21:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ORACLE_HOME = /oracle/ORCL/112_64

System name:    Linux

Node name:      sapORCL

Release:        2.6.18-194.17.1.el5

Version:        #1 SMP Mon Sep 20 07:12:06 EDT 2010

Machine:        x86_64

Starting with debugging set to level=9, types=ALL
DBGMISC:    ENTERED krmksimronly [15:21:06.175]
DBGSQL:      CHANNEL> alter session set events '19737 trace name context forever'

DBGSQL:         sqlcode = 0

DBGSQL:      CHANNEL> select decode(archiver, 'FAILED', 1, 0) into :archstuck from v$instance

DBGSQL:         sqlcode = 0

DBGSQL:          D :archstuck = 0

You can refer mentalink note 375386.1 for more information.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Time for Change: Migrate your Non-RAC Database to RAC

Hi Everyone,

One of my articles published in "IOUG Select Journal" and distributed at Oracle Open World 2012.

Time for Change: Migrate your Non-RAC Database to RAC - Page 25
By Satishbabu Gunukula

Please view the IOUG 2012 Q3 article using below link.
http://www.ioug.org/p/do/sd/sid=2371&fid=2133&req=direct


I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Send mails using UTL_SMTP in Oracle

The UTL_SMTP Oracle package provides capability to send emails from PL/SQL program using Simple Mail Transfer Protocol. UTL_MAIL is easy to use as it eliminates steps of formatting the message body and explicit coding of details required by SMTP protocol.

Use below simple example to send mails using UTL_MAIL

begin
utl_mail.send(
sender => 'fromuser@oracleracexpert.com',
recipients => 'touser@oracleracexpert.com',
message => 'Test mail for UTL_MAIL'
);
end;

If you can want to use UTL_MAIL refer “Send EMAILs using UTL_MAIL”

UTL_SMTP it is more fine-grained API that enables the attachments of larger size and it is very powerful utility.

Use below simple example to send mails using UTL_SMTP

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

You can write multiple line messages using the UTL_SMTP.WRITE_DTA command, use following example

CREATE OR REPLACE PROCEDURE send_mail_smtp(recipient IN VARCHAR2,
sender IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
mailhost IN VARCHAR2,
smpt_port IN NUMBER DEFAULT 25)
AS
smtp_mail_conn UTL_SMTP.connection;
BEGIN
Smtp_mail_conn := UTL_SMTP.open_connection(mailhost, smtp_port);
UTL_SMTP.helo(smtp_mail_conn, mailhost);
UTL_SMTP.mail(smtp_mail_conn, sender);
UTL_SMTP.rcpt(smtp_mail_conn, recipient);
UTL_SMTP.open_data(smtp_mail_conn);
UTL_SMTP.write_data(smtp_mail_conn, 'To: '||recipient||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'From: '||sender||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'Subject: '||subject||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, message||UTL_TCP.crlf||UTL_TCP.crlf);
UTL_SMTP.close_data(smtp_mail_conn);
UTL_SMTP.quit(smtp_mail_conn);
END;
/

Call the procedure by providing the below details

BEGIN
send_mail_smtp(recipient => 'touser@mycompany.com',
sender => 'fromuser@oracleracexpert.com',
subject => 'Test mail',
message => 'This is a test mail using UTL_SMTP.',
mailhost => localsmtp.oracleracexpert.com');
END;
/

To send emails to multiple recipients use UTL_SMTP.rcpt procedure for each separate email
For more information you can also refer Oracle Documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm#ARPLS074

Common errors when using UTL_SMTP:-
1. The UTL_SMTP package requires the ORACLE xmldb component, please install the same to fix below error

ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP"

2. You may receive below error if the “mailhost” was not set correctly

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20

3. You will receive below error if ACL is not assigned to target host and necessary privileges are not granted

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7

Convert Single Instance to RAC – Part1: Duplicate DB using RMAN

Hi Everyone,

My articles published in Oracle Experts website media "Allthingsoracle.com" by RedGate

Please view the article using below link.
Convert Single Instance to RAC - Part1: Duplicate DB using RMAN

I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Manage Access Control List in Oracle 11g and ORA-24247

Oracle 11g offers fine-grained access to network services (ACL) and the packages used to access external network resources are restricted.

The 11g allows access to external packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, UTL_INADDR, DBMS_LDAP, but the access must be granted explicitly. Please note that ACLs are stored in XML DB and user must install XML DB for the use of ACL, if not installed.

The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).

     • ACL - Name of access control list in xml file and relative path will be “/sys/acls”
     • Description - Description of the ACL.
     • Principal - To whom the privilege is granted or revoked
     • Is_grant - Indicates weather the privilege is granted (TRUE) or denied (FALSE).
     • Privilege – Network privilege, Use ‘connect’ for access and ‘resolve’ for UTL_INADDR name/IP resolution.
     • Position – Position of ACL
     • Start_date – Start date of the ACL , the default value is NULL.
     • End_date – End date of an ACL.

Uses might see below error when they upgrade their databases from Oracle 10g to 11g. This is expected behavior in Oracle 11g

ORA-24247: network access denied by access control list (ACL)

This is expected behavior in 11g, if any of the UTL_% packages referred or used in any user defined programs. To resolve this issue user must explicitly grant the access using DBMS_NETWORK_ACL_ADMIN package.

For ex: - A used defined new send_mail PL/SQL program or existing program (After upgrading to 11g) failed with ORA-24247

DECLARE

mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

DECLARE

*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7

To resolve the issue user need to create ACL and grant required access, follow the below steps.

Step1:- Create New Access Control List

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_smtp.xml',
description => 'Allow UTL_SMTP to send mails',
principal => 'TEST_USER',
is_grant => TRUE,
privilege => 'connect',
Start_Date => Null,
End_Date => Null);
end;

Step2:- Add privilege to Access control list

You can add the privilege like ‘resolve’ to the ACL.

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘TEST_USER’,
is_grant => TRUE,
privilege => 'resolve');
end;
/

Step3:- Assign Access control List

begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_smtp.xml',
host => 'localsmtp.oracleracexpert.com',
lower_port => 25,
upper_port => NULL);
end;
/

Step4:- Check the permission

Check the required permission is granted to the user “TEST_USER” using below query

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect     Resolve
----------- -------
DENIED DENIED

The access is not granted and user still receives error “ORA-24247: network access denied by access control list (ACL)”.

You must “COMMIT” the changes in order to work the Network ACL’s.

Now user should be able to see the changes using below queries

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect      Resolve
------------ -------
GRANTED GRANTED

SQL> select * from dba_network_acls where acl like '%utl_smtp%%';

HOST LOWER_PORT UPPER_PORT ACL ACLID
------------------------ ---------- ---------- ------------------------------ ---------------------------------------------
localsmtp.oracleracexpert.com /sys/acls/utl_smtp.xml C6B2CCC62AC30707E04025AC80DA7FA3

Now user defined send_mail program is working fine.

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/
PL/SQL procedure successfully completed.

Manage the Network ACLs as below

Add a user or role to newly created Access control list (ACL) – Using ADD_PRIVILEGE procedure adding privilege to the user “SCOTT”

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => 'connect');
end;
/

Remove a user or role to newly created Access control list (ACL) – Using DELETE_PRIVILEGE procedure deleting a privilege in an access control list from user “SCOTT”

begin
dbms_network_acl_admin.delete_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => FALSE,
privilege => 'connect');
end;
COMMIT;
/

Drop Access control list (ACL) – Using DROP_ACL procedure dropping an access control list (ACL).

begin
dbms_network_acl_admin.drop_acl (
acl => 'utl_smtp.xml');
COMMIT;
end;
/

Unassign Access control list (ACL) – Using UNASSIGN_ACL procedure un-assigning the access control list (ACL) from a host.

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => 'hostname’);
COMMIT;
end;
/

You can query below views for ACL and privilege information

DBA_NETWORK_ACL_PRIVILEGES describes the network privileges defined in all access control lists that are currently assigned to network hosts.

DBA_NETWORK_ACLS describes the access control list assignments to network hosts.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Packt Publishing reaches its 1000 titles

Hi Readers,

Packt Publishing is about to publish its 1000th title and they would like to celebrate this occasion by giving a surprise gift to their readers.

These gifts will only be revealed between 28th and 30th September, as when their 1000th title gets unveiled. The gifts are valid for only those readers who are currently registered on their website, and those who sign up for an account before 30th September.

You can find complete information about the event on http://www.packtpub.com/news/packt-publishing-reaches-1000-it-titles-and-celebrates-open-invitation

You can visit Packt Publishing Website http://www.packtpub.com to see their collection of books.

I hope you will get benefited.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Smart View timeout error message

If users are working with planning data forms on excel and receiving any of the below timeout error then the issue is with IE Settings.

Cannot connect to provider because: The request timed out. Contact your administrator to increase NetRetryCount and NetRetryInterval

The requeset timed out. Contact your administrator to increase netRetryCount and netRetryInterval.

When trying to save the data then users might see below error

“This form contains unsaved data. Select OK to continue without saving data, or Cancel this operation”

Follow below steps to fix the issue

Step1:- Create an empty text file and copy below contents

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"KeepAliveTimeout"=dword:00180000
"ReceiveTimeout"=dword:00dbba00
"ServerInfoTimeout"=dword:00180000

Step2: - Rename the file as LogOut.reg” and right click on the file  click on merge to update your registry

Step3:- Restart your computer (optional)

You can manually update/ add registry entries but it may corrupt the registry and this is the recommended approach.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Why you should attend Oracle OpenWorld?

Oracle OpenWorld presents the world's largest, most in-depth selection of sessions by Oracle experts, customers, and partners over a course of five days.

Oracle OpenWorld 2012- September 30 - October 4, 2012

In Oracle Open World, you are sure to discover new ways to optimize your systems, new solutions that will help you take your business to the next level and new practices that will make you more successful in your job and in your industry.

At Oracle Open World you are going to get -
     • Technology sessions
     • Labs & demos
     • Exhibitors and partners
     • Opportunities to meet with industry experts

And lot more…!

Click here for Oracle Open World Agenda

Learn at Oracle OpenWorld
Oracle Open World provides more educational and networking opportunities than any other Oracle conference. Last year 45,000+ Attendees registered and this year 50,000+ attendees are expected.

Whether you are an experienced developer or DBA, a novice technologist, or business manager or executive, you'll find how-tos, overviews, best-practice sessions, and customer panels targeted to your job role and level of expertise at Oracle OpenWorld 2012.

Oracle Open World Streams and Tracks
     • Applications
     • Database
     • Industries
     • Middleware
     • Oracle Develop
     • Server and Storage Systems
     • Cross-Stream

Let Your Manager Know Why You Should Attend
Oracle OpenWorld offers thousands of sessions geared toward helping Oracle users get the most out of their IT infrastructure and investments—and helping them make the most cost-effective and strategic IT decisions for the future.

Oracle OpenWorld is also a great showcase for the latest developments in cloud, big data, security, governance, data center optimization, and other issues that we’re dealing with. Through presentations and hands-on sessions the conference offers hundreds of opportunities to learn money- and time-saving techniques.

Nowhere else you could get the detailed best practices learned by other companies facing challenges similar to yours. You can get advice directly from the people who know the technology best, and can participate in question-and-answer sessions and other roundtable meetings—getting access to high-value information from experts, peers, partners, and industry leaders.

If you still need some help convincing your manager to let you attend Oracle OpenWorld, Download a Customizable Justification Letter.

Some of the sessions will be covering in Oracle Open world are:
     • Technology- Oracle’s complete technology portfolio
     • Applications - Enablement offerings, initiatives and service development options
     • Engineered Systems - Ways to transform IT into more cost effective operation
     • Industry - Discover ways to successfully position and deliver solutions to clients
     • Cloud - Learn how to go to market and implement innovative cloud solutions

Click here to View all the sessions.

Become Part of the Experience, Meet up with your peers, find out when and where our leaders and most exciting events will occur, and participate in the discussion.

Click here to Register for Oracle OpenWorld 2012

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Install and Configure MySQL using Generic Unix or Linux Binaries

Oracle provides the Generic binaries in the form of tar files. Using generic binaries you will be able to control the configuration and layout.

The MYSQL compressed tar files have names of the form mysql-version-OS.tar.gz.

Generic Unix/Linux Binaries Installation layout
Directory  Contents of Directory
bin            Client programs and the mysqld server
data          Log files, databases
docs         Manual in Info format
man          Unix manual pages
include     Include (header) files
lib            Libraries
scripts      mysql_install_db scripts
share        Miscellaneous support/sample configuration/Install files, including error messages
sql-bench Benchmarks

Before you install, make sure that there are no previous versions of mysql, unless you want to use older version of MySQL.

Command to find old rpms installed on the system
# rpm -qa | grep -i ^mysql
Command to find old mysql directories/files
# Find . –name *mysql* -print
Command to find mysql server process
# ps –ef | grep mysql

Follow the steps to Install and configure MySQL

1. Create mysql user and group
# groupadd -g 100 mysql
# useradd -u 100 -s ksh -d /u01/mysql -r -g mysql mysql
# chown -R mysql:mysql /u01/mysql

2. Login as Mysql and unpack the distribution
# su – mysql
$ tar zxvf /u01/mysql-5.0.45.tar.gz
Create a soft link to 5.0.45
# ln -s /u01/mysql/mysql-5.0.45 5.0.45

3. Create a profile and set the profile
-----.profile file----
MYSQL_BASE=/u01/mysql; export MYSQL_BASE
MYSQL_HOME=/u01/mysql/5.0.45; export MYSQL_HOME
PATH=$MYSQL_HOME/bin:$PATH ; export PATH
Set the profile
$ . ./.profile

4. Copy sample configuration file and edit the file according to your environment.
$ cp /u01/mysql/5.0.45/support-files/my-small.cnf $MYSQL_HOME/my.cnf
-----my.cnf file----
[mysqld]
datadir=/u01/mysql/data
socket=/u01/mysql/mysql.sock

innodb_data_home_dir=/u01/mysql/innodata
innodb_data_file_path=ibdata1:10M:autoextend:max:3000M
innodb_log_group_home_dir=/u01/mysql/innologs
user=mysql

[mysqld_safe]
log-error=/u01/mysql/mysqld.log
pid-file=/u01/mysql/mysqld.pid
[client]
socket=/u01/mysql/mysql.sock

5. Create mysql database files for mysql server.
$ scripts/mysql_install_db --datadir=/u01/mysql/5.0.45/data --basedir=$MYSQL_HOME

This command creates the default database schemas

6. Start the MySQL Server
$ mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &

Here we have placed the my.cnf under MYSQL_HOME. You can copy the configuration file to any location and use the path while starting the mysql server.

7. Verify the Mysql process and logs
$ ps –ef  | grep mysql

Verify the error log (log-error=/u01/mysql/mysqld.log) under the path that you mentioned in the my.cnf

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle Flashback Data Archive - Oracle Total Recall

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive
Create Flashback Data archive using below command
SQL> CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive
Remove Flashback Data Archive flashback_archive1 and all its historical data
SQL> DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive1
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Backup and Restore MySQL Database

The mysqldump is a backup program used to dump a database or collection of database for a backup
Syntax: Table level
$ mysqldump [options] db_name [tbble_name ...]
Database level
$ mysqldump [options] --databases database_name ...
All Databases
$ mysqldump [options] --all-databases

The mysqlimport is used to import the data into database.
Syntax:
$mysqlimport [options] db_name dumpfile1 [dumpfile2 ...]

You can load the dumpfile into server using mysql command.

Backup Mysql Database using mysqldump1. Take a backup of single database on the same server
$mysqldump -u user_name -d database_name –p > database.sql

2. Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name –p > database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port to backup database
$ mysqldump -u user_name -h host_name -P port -d database_name –p > database.sql

4. Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql

5. Backup all databases on the server as ROOT
$ mysqldump -u root --all-databases > all_databases.sql

Restore MySQL Database using “mysql” command
If you are moving the database from one server to another server or remotely then use below commands

1. Restore a database on the same server
$mysql -u user_name database_name –p < database.sql
Or
$ mysql -e "source /tmp/database.sql " database_name

2. Restore database from remote server
$ mysql -u user_name -h host_name database_name –p < database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port of Instance to restore
$ mysql -u user_name -h host_name -P port database_name –p < database.sql

4. Restore a table from a database
$ mysql -u username database_name < table.sql

5. Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql

Restore database using mysqlimportThe database should exist in order to import dump using mysqlimport.
$ mysqlimport -u user_name -p password database_name /tmp/dumpfile.sql

Write MySQL table data to a file

Use below syntax to write table data/contents to a file
mysql> SELECT * FROM . ORDER BY DESC LIMIT 0,99000 INTO OUTFILE '/tmp/table_name.txt'

If you don’t have access to write from MySQL to a file then you will receive below error.
ERROR 1045 (28000): Access denied for user 'xxxxxxx'@'%' (using password: YES)

If you dont have permission the use below alternate option

$ echo "SELECT * FROM database_name.table_name" mysql -u -h --port= --password= > /tmp/table_name.txt

Rename Mysql Database

Either you can backup and restore the database into new database or follow below steps
mysql>create database new_database;
$ mysqldump -u user_name -d old_database -p | mysql -u user_name -d new_database -p
mysql> drop database old_database

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

How to install and configure MySQL Database

Linux supports a number of different solutions for installing MySQL.
1. Install MySQL using RPM Package
2. Install MySQL using Native package manager
3. Install MySQL using Generic Binaries

If you are new to MySQL then i would recommend using RPM, otherwise install manually using Generic Binaries. The recommended way installing MySQL on PRM-based(RPM VsNative) Linux distribution is using RPM package.

Install MySQL using RPM PackageUse below command to see all files in an RPM package
$ rpm -qpl MySQL-server-VERSION.linux2.6.x86_64.rpm

Run the below command to install
$ rpm -i MySQL-server-VERSION.linux2.6.x86_64.rpm

The RPM installation creates a login account named mysql.

By default the RPM places the data under /var/lib/mysql directory and creates the appropriate entries in /etc/ini.d to start the server automatically at boot time.

MySQL Installation Layout for Linux RPM
/usr/bin - Client programs and scripts
/usr/sbin - The mysqld server
/var/lib/mysql - Log files, databases
/usr/share/info - Manual in Info format
/usr/share/man - Unix manual pages
/usr/include/mysql - Include (header) files
/usr/lib/mysql - Libraries
/usr/share/mysql - Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/usr/share/sql-bench – Benchmarks

Install My SQL using Native package managerThe native package installation will automatically take care of downloading and installing the software. Also it will take care of dependencies for MySQL Installation.

Use yum command to install MySQL.
# yum install mysql mysql-server mysql-libs mysql-server

An init script will be installed into /etc/init.d/mysqld, this script is used to stop and start the server. A configuration file will be created under /etc/mysql/my.cnf

Use below commands to Stop and Start MySQL service
# service mysql start
# service mysql stop

If you want to start MySQL server automatically during the boot run below command.
# chkconfig --levels 235 mysqld on

Here are some popular MySQL Download links

Download MySQL Community Serverhttp://dev.mysql.com/downloads/mysql/
Download MySQL Clusterhttp://dev.mysql.com/downloads/cluster/
Download MySQL Workbench GUI Toolhttp://dev.mysql.com/downloads/workbench/5.2.html
Download MySQL Proxyhttp://dev.mysql.com/downloads/mysql-proxy/
Download MySQL Connectorshttp://dev.mysql.com/downloads/connector/

Regards
Satishbabu Gunukulahttp://www.oracleracexpert.com/

Oracle version compatibility and support matrix

Before you install or upgrade your Database server, clients, JDBC, JDK…etc. first you need to check the version support matrix. Make sure that your target version is supported. Otherwise oracle will not provide support.

Please find the support matrix links/ Metalink Notes

• Oracle Database Server/ Client version compatibility matrix
Client / Server / Interoperability Support Matrix for Different Oracle Versions [ID 207303.1]

• Support Status of each Oracle Server (RDBMS) Release
Oracle Database (RDBMS) Releases Support Status Summary [ID 161818.1]

• JDBC, JDK, and Oracle Database Certification
Starting With Oracle JDBC Drivers [ID 401934.1]
You can also refer JDBC FAQ

• JDBC Driver Support for Oracle Application Server (Fusion Middleware) Note:365120.1

• JDBC Features - classes12.jar , oracle.jdbc.driver, and OracleConnectionCacheImpl [ID 335754.1]

• Export/Import Compatibility between different oracle versions Note:132904.1

• Export/Import Datapump Compatibility between different oracle versions Note:553337.1

• Oracle 11g Release 1 (11.1) Support Status and Alerts [ID 454507.1]

• Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]

• Oracle 10g Release 2 (10.2) Support Status and Alerts [ID 316900.1]

• Oracle 10g Release 1 (10.1) Support Status and Alerts [ID 263719.1]

• Oracle9i Release 2 (9.2) Support Status and Alerts [ID 189908.1]

• Oracle9i (9.0.1) Support Status and Alerts [ID 149018.1]

• Oracle8i Release 3 (8.1.7) Support Status and Alerts [ID 120607.1]

• Oracle8i Release 2 (8.1.6) Support Status and Alerts [ID 93849.1]

• Release Schedule of Current Database Releases [ID 742060.1]

• Oracle Lifetime Support Policies [ID 971415.1]

• Oracle Database 11g Editions feature comparison

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-01427: single-row subquery returns more than one row

Everyday developers, testers and newbie’s face ORA-01427 error and I have seen many users looking for a solution for this error.

First let’s understand why users receive this error.

SQL> select count(*) from employee where employee_id = (select employee_id from salary where gross_sal < 5000 );
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error is raised when a sub-query returns more than one row.

Make sure you use multiple row sub query operator instead of single row sub-query operator to resolve the issue.

SQL> select count(*) from employee where employee_id in (select employee_id from salary where gross_sal < 5000 );

Now this query works fine.

Single row sub query operators are = (equal) , < (less than) , <=( less than or equal) , >(greater than) , >= (greater than or equal), <>(not equal ) ,!=(not equal)

Multiple row sub query operators are IN (equal to any member in the list), ANY (compare value to each value returned by the subquery), ALL (compare value to every value returned by the subquery)

Few guide lines for using Sub queries:
1. Use single-row operators with sing row sub queries
2. Use multiple-row operators with multiple row sub queries
3. Make sure you enclose sub queries in parentheses
4. Always keep sub queries on the right side of the comparison operator
5. You can use inline view to avoid ORA-01427 error, please refer http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm#DWHSG08032

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/

User Account Control is enabled. Installation may lead to unpredictable results and thus will be stopped

You need to disable User Account Control (UAC) before you install Hyperion Essbase, otherwise you will receive below error.

 User Account Control is enabled. Installation may lead to unpredictable results and thus will be stopped. EPMINS-01001: One or more preinstallation checks failed. Correct the errors before continuing with the installation. Press Enter to exit

You can disable the User access Control (UAC) by following below steps

In case of Windows 2008 Server
1. Start -> Control Panel
2. Click User Accounts in Control Panel
3. In the User Accounts window, click User Accounts
4. Click on Turn User Account Control on or off
5. Uncheck the "Use User Account Control (UAC) to help protect your computer", and then click OK. 6. Restart the computer to apply the changes

In case of Windows7
1. Start -> Control Panel
2. Click User Accounts in Control Panel
3. In the User Accounts window, click User Accounts
4. Click on “Change User Account Control settings”
5. Move the bar to “Never Notify”
6. Restart the computer to apply the changes

Now launch the installTool.cmd to install Hyperion and you should receive below error message.

User Account Control is disabled. EPMINS-01002: All installation prerequisites have been met. Starting EPM System Installer.

If you are running the installation over network share then you will see below error message

EPMINS-01001: One or more preinstallation checks failed. Correct the errors before continuing with the installation. Installer Path check... Failed! Installation from shared folder is not supported, please mount a network drive.

Make sure you map the network drive to local computer and launch the installTool.cmd and you should be able to install successfully.

Regards
Satishbabu Gunukula
http://oracleracexpert.com

Oracle Application Express component is INVALID

I have created a new Database in Oracle 11gR2 and process is smooth without any errors. When I check the status of components, it showing that Oracle application Express is INVALID.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

Possible Cause:-
1. If one or more Application Express objects are invalid
2. When doing export/import privileges might have lost
3. The XDB or Oracle Text components are not installed or de-installed.

To find the cause run the APEX Installation Verification Script apex_verify.sql and this script will generate an output apex_verify_out.html. You can download the verification script from below metalink note.

APEX Installation Verification Script [ID 1254932.1]

From the apex_verify_out.html output I found that WWV_FLOW_HELP object is INVALID.

Owner Object Name Object type Status
APEX_030200 WWV_FLOW_HELP PACKAGE BODY INVALID

You can also check the invalid objects using below query

SQL> select owner,object_name from dba_objects where status='INVALID';
OWNER OBJECT_NAME
------------------------------ --------------------
APEX_030200 WWV_FLOW_HELP

Run utlrp.sql to compile all invalid objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

The utlrp.sql output has below error message
FAILED CHECK FOR PACKAGE BODY WWV_FLOW_HELP

To find the errors query DBA_ERRORS table or complete the package.

SQL> alter package APEX_030200.WWV_FLOW_HELP compile body;
Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
188/20 PL/SQL: SQL Statement ignored
189/27 PL/SQL: ORA-00942: table or view does not exist
191/13 PL/SQL: Statement ignored
191/13 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
196/16 PL/SQL: SQL Statement ignored
197/23 PL/SQL: ORA-00942: table or view does not exist
199/9 PL/SQL: Statement ignored
199/9 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
261/5 PL/SQL: Statement ignored
261/5 PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be
Declared

SQL> SELECT NAME,TYPE,TEXT from dba_errors where owner='APEX_030200'

NAME TYPE
------------------------------ ------------
TEXT
--------------------------------------------------------------------------------
WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DOC.FILTER' must be declared

WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared

It’s clear that APEX_030200 user does not have privilege on CTX_DDL and CTX_DOC, need to grant the execute privileges on CTX_DDL and CTX_DOC to APEX user.

SQL> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
SQL> grant execute on ctx_doc to APEX_030200;
Grant succeeded.

Now recompile the APEX_030200.WWV_FLOW_HELP package

SQL> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
SQL> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Now you should able to see that Application Express component is invalid.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express VALID 3.2.1.00.10

If there are no INVALID objects and if you still see the component as invalid then run below commands as SYS to reset the DBA_REGISTRY.

SQL> exec validate_apex; (If your APEX version is 2.0 or above)
OR
SQL> exec validate_htmldb; (If your APEX version is prior to 2.0)

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

The below are the most common errors, when connecting remotely to MySQL Server from MySQL Client.

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

Cause: The host that you are using to connect MySQL Server does not have privilege

Solution: Grant required privileges
GRANT ON *.* TO ‘user’@'hostname' IDENTIFIED BY ‘password’ ;
For ex:- if you are connecting as root then use below syntax
Mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Now you should able to see a new user in mysql.user table.
Mysql> select host,user from mysql.user;
+------+---------------------------
| User | Host
+------+---------------------------
| root | test-server
+------+---------------------------

If you want to grant privilege to a host on specific database try below syntax.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Error 1130: #HY000 Host ‘IP address’ is not allowed to connect to this MySQL server

Cause: The user host IP address is not allowed to connect to MySQL Server

Solution: Grant the privilege to host IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘user’@’IPADDRESS’ IDENTIFIED BY ‘password’ ;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’x.x.x.x’ IDENTIFIED BY ‘test’ ;

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: NO)

Cause: The root user is password protected and you need to use password parameter in order to connect

Solution: - Try to connect MySQL Server using below command
# mysql -u root -h test-server –p

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: YES)

Cause: The 'root'@hostname' doesn’t have privilege to connect MySQL Server

Solution: - Grant permission using below command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@hostname' IDENTIFIED BY ‘password’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

[client]
socket=/opt/mysql/mysql.sock

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Oracle ACE Award

Hi Everyone,

I am happy to share with you that I have received Oracle ACE Award. I am really honored to receive this Award for my contribution towards Oracle community.

I would like to take this opportunity to thank all my family, friends, colleagues and followers for their support.

Please take a moment to look at my Oracle ACE profile – Satishbabu Gunukula

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Could Not Get DeploymentManager When Deploying or Undploying Agile

When deploying and un-deploying Oracle Agile application, users may receive error "Could not get DeploymentManager”.

Problem: You will receive below error when Agile unable to deploy/un-deploy using admin_client.jar utility

Failed at "Could not get DeploymentManager".

This is typically the result of an invalid deployer URI format being supplied, the target server not being in a started state or incorrect authentication details being supplied.

More information is available by enabling logging -- please see the Oracle Containers for J2EE Configuration and Administration Guide for details.

Cause 1:- The deployment command uses opmn request port 6003 by default. You will receive the error If the install used different opmn port (6004)

Solution:- Replace the port from 6003 to 6004 in DeployAgilePLM.sh or UndeployAgilePLM.sh script

deployer:oc4j:[rmis]:opmn://host[:opmnPort]/[iASInstanceName] /oc4jInstanceName

Cause 2:- If the server name changed from fully qualified name to short name or vice versa then admin_client.jar utility unable to deploy and you will receive the error.

Solution:- Replace fully qualified name to short name or vice versa in DeployAgilePLM.sh or UndeployAgilePLM.sh script and run the script.

In some cases, the application may hang with following error message when deploying the Agile Application. It is due to the version mismatch or Admin Data Schema Version not present

07/12/11 06:26:30 Notification ==>Initialize link ends...
07/12/11 06:26:30 Notification ==>Initialize integration begins...
07/12/11 06:26:30 Notification ==>Initialize integration ends...
07/12/11 06:26:30 Notification ==>Initialize JavaClient begins...
07/12/11 06:26:30 Notification ==>Initialize JavaClient ends...
07/12/11 06:26:30 Notification ==>Started application : Agile
07/12/11 06:26:30 Notification ==>Binding web application(s) to site default-web-site begins...
07/12/11 06:26:30 Notification ==>Binding application web-module for application Agile to site default-web-site under context root Agile
07/12/11 06:26:31 Notification ==>Initializing Servlet: com.agile.ui.pcm.InitServlet for web application application

You will see below message in default_group_home_default_group_1.log

07/12/11 13:26:32 ++++++++++++++++++++++++++++++++++++++++++++++++
07/12/11 13:26:32 + +
07/12/11 13:26:32 + Admin Data Schema Version NOT PRESENT!!!
07/12/11 13:26:32 + Admin Server Schema Version : 9.3.185.0
07/12/11 13:26:32 + +
07/12/11 13:26:32 ++++++++++++++++++++++++++++++++++++++++++++++++

Solution: Install the correct version or enter correct database information in agile.properties files, if version mismatch. Please refer Note 569229.1 for more information.

Please refer the link How to Deploy and Un-deploy Agile application.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

How to Deploy and Un-deploy Oracle Agile Application

Whenever you are applying the patches to Oracle Agile environment, you need to Un-deploy and deploy the application.

Before you apply the patch you must Un-deploy the Agile application. After successful installation of the patch you must deploy the agile application in order to take effect of the applied patch.

Un-Deploy Agile application:-Go to Agile home directory and run below command
$ cd $/agileDomain/bin
$./ UndeployAgilePLM.sh

Where password is the oc4jadmin user password

12/02/09 14:04:10 Notification ==>Application UnDeployer for Agile STARTS.
12/02/09 14:04:11 Notification ==>Removing all web binding(s) for application Agile from all web site(s)
12/02/09 14:04:19 Notification ==>Application UnDeployer for Agile COMPLETES.

Follow the steps to apply a Patch in Oracle Agile environment
1. Check PMN processes are running on the Agile Server.

2. Stop the OPMN processes
$ opmnctl stopall

3. Unzip the patch to a temporary directory
For Unix
$/tmp//Install_Patch.sh
For Windows
C:\temp\\Install_Patch.bat

You will get a message “Is the Agile Application already undeployed? (y/n) “. You must enter Y inorder to proceed further.

Ignore the message "Enter Passphrase for keystore:" and let the installation continue

When the installation is completed, the “INSTALLATION SUCCESSFUL” message will appear and press the return key to complete the installation.

4. Start OPMN processes
$ opmnctl startall

Deploy Agile application:-After successful installation of the patch deploy the Agile application

$ cd $/agileDomain/bin
$./DeployAgilePLM.sh

Where password is the oc4jadmin user password
12/02/09 14:06:50 Notification ==>Application Deployer for Agile STARTS.
………………………
……………………….
12/02/09 14:07:35 Notification ==>Binding web application(s) to site default-web-site ends...

12/02/09 14:07:35 Notification ==>Application Deployer for Agile COMPLETES. Operation time: 45434 msecs

Check the status of the agile application
$ opmnctl status

Make sure to check applied patch from application. If Deploy and Un-deploys fails with some reason then you will not see the applied patch in Application.

. Launch the application and login
. Click on help --> About Agile
. You should see the patch number under “updated versions”

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

You want to participate in Oracle Beta program or Exam?

Oracle Technology Beta programs available for all Oracle technology products including Oracle Database, Oracle Enterprise manager, Oracle Fusion Middleware…etc.

Please note that Beta program is highly selective and acceptance into these programs makes you part of an elite group of test sites that are carefully selected based up on technical expertise and knowledge of Oracle productions

Why should you participate?

The main benefit is gaining access to technology before your competitors and developing a close working relationship with oracle development.

If you want to participate in Oracle beta program, please register yourself using below link.
http://otnbeta.oracle.com/bpo/prospects/index.htm

You can also contact BETAPROG_US@ORACLE.COM for details

You can also participate in Oracle Certification Beta exams. These Beta exams are pre-production exams used by Oracle to evaluate new exam questions and it will go through a rigorous development and evaluation process before being released to the public.

Beta exams are offered at a deeply discounted price and exam usually takes around 3 to 3.5 hours to complete.

To learn more information about Beta Exams, please visit
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=182

Regards,
Satishbabu Gunukula
http://www.oracleracepxert.com

How to STOP and START processes in Oracle RAC and Log Directory Structure

You need to follow the below steps to Stop and Start processes in Oracle RAC

Shutdown RAC Database
You need to Shutdown Database instances on each node. You can either use Oracle Enterprise Manager or SVRCTL to shutdown the instances. If you are using EM Grid control then set a blackout in Grid control for processes that you intend to shutdown. So that records for these processes indicate that the shutdown was planned.
Use below command to stop Enterprise Manager/Grid Control
$ORACLE_HOME/bin/emctl stop dbconsole

Use below command to shutdown all oracle RAC instances on all nodes.
$ ORACLE_HOME/bin/srvctl stop database -d db_name

If you want to stop specific database instances use below command
$ ORACLE_HOME/bin/srvctl stop database -d db_name –i instance_name

Shutdown Oracle ASM Instance
Once the database is stopped, proceed with ASM Instance shutdown.
Use below command to shutdown ASM instances on all nodes
$ORACLE_HOME/bin/bin/srvctl stop asm -n node

Shutdown Node applications
Use below command to shutdown node apps on all RAC nodes
$ORACLE_HOME/bin/bin/srvctl stop nodeapps -n node

Shutdown Oracle Clusterware
You need to Shutdown oracle clusterware or CRS as root and run below command on each node in the cluster.
#crsctl stop crs

Please note that using above command will stop Oracle High availability services (OHAS) and Clustware stack in a single command

From 11g R2, you can do this in two stops

1. Stop Clustwerware stack on local node
#crsctl stop cluster

You can stop the clusterware stack on all nodes in the cluster
# Crsctl stop cluster –all

Where
-all Start clusterware on all nodes
-n Start clusterware on particular nodes

2. Stop Oracle High availability service demon on each node in the cluster.
# crsctl stop has

Check the Status of Cluster
Once all process stopped run the below command to check the status of CRSD,CSSD,EVMD process.
# crsctl check crs

If you see any process failed to stop then you can also use Force option to terminate the processes unconditionally.
$ crsctl stop crs –all –f

Start processes in Oracle RAC
Follow the reverse sequence to start all processes in oracle RAC
# crsctl start crs
$ORACLE_HOME/bin/bin/srvctl start nodeapps -n node
$ORACLE_HOME/bin/bin/srvctl start asm -n node
$ORACLE_HOME/bin/srvctl start database -d db_name

If you come across any issues during startup orshutdown, check the Oracle Clusterware Component Log Files.

Oracle Clusterware Log Directory Structure

CRS_HOME/log/hostname/crsd/ - The log files for the CRS daemon CRS_HOME/log/hostname/cssd/ - The log files for the CSS daemon
CRS_HOME/log/hostname/evmd/ - The log files for the EVM daemon
CRS_HOME/log/hostname/client/ - The log files for the Oracle Cluster Registry (OCR)
CRS_HOME/log/hostname/racg/ - The log files for the Oracle RAC high availability component
CRS_HOME/log/hostname/racg/ - The log files for the Oracle RAC high availability component
CRS_HOME/log/hostanme/alert.log – The alert.log for Clusterware issues.

Please note that the CRS_HOME is the directory in which the Oracle Clusterware software was installed and hostname is the name of the node

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

One of my RMAN backup generated below Warnings, here is the detailed description and solution. I hope it helps other users.

Error:

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_BACKUP_RESTORE"

Cause & Solution:

The RMAN not able to update V$RMAN_STATUS or V$RMAN_OUTPUT due to associated error message. When you receive RMAN-06900 and RMAN-06901error, you need to look for associated error messages to find the root cause of the error message.

In this case, ORA-04031 is the associated error message

ORA-04031: unable to allocate 4120 bytes of shared memory
*Cause: More shared memory is needed than was allocated in the shared pool.
*Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and “shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

I have seen many users complaining about error ORA-19921. If you receive this error close some of existing connections or unused RMAN connections.

If your oracle version > 10.2.0.4 then you might be hitting below bugs. These bugs fixed in 10.2.0.4 and higher versions.

ORA-19921: maximum number of 64 rows exceeded - oracle Bug 465973.
ORA-19921: maximum number of 128 rows exceeded - oracle Bug 8264365

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com