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
Tuesday, October 16, 2012
Friday, September 21, 2012
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
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
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
Thursday, September 20, 2012
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
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
Thursday, August 2, 2012
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
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
Subscribe to:
Posts (Atom)
