Powered By Blogger

Monday, January 30, 2012

Restore archivelogs to a different location

In some scenarios we may need to restore the archivelogs to a different location from its primary location using RMAN from its backups.

Below are the methods to do it.

1.  The below scripts restores the archive logs to a temporary location which is given in the run script.
RMAN> run
2> {
3> set archivelog destination to 'D:\backup';
4> restore archivelog from logseq=250 until logseq=264;
5> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 30-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=D:\backup
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=250
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=251
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=252
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=253
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=254
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=255
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=256
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=257
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=258
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=259
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=260
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=261
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=262
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=263
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=264
channel ORA_DISK_1: reading from backup piece D:\BACKUP\ARCH_BKP\BKP_ARCH_12N22PES_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\ARCH_BKP\BKP_ARCH_12N22PES_1_1 tag=TAG20120130T153139
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 30-JAN-12

2. Copy the archives to a different location and restore it to the same default location 

RMAN> copy archivelog 'D:\orcl10g_arch\ARC00250_0747232149.001' to 'D:\backup\arch_bkp';

Regards,
Navaneeth

Tuesday, January 10, 2012

Oracle ACL Configuration on 11g for using Network packages

In Oracle 11g network packages/system packages like UTL_MAIL,UTL_SMTP,UTL_TCP  are restricted from a normal user by using Access Control List (ACL). Oracle has introduced Fine-grained Access to use External Network Services.

Simply giving Execute privilege to these packages may not be essential to use these packages.

We will need to create the below Access Control List to allow use these packages to a normal user.

Below is the procedure  to create an access control list (ACL) with an initial privilege setting. 
An ACL must have at least one privilege setting. The ACL has no access control effect unless it is assigned to network target.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'SCOTT.xml',
        description  => 'ACL for users to send mail.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'connect',
        start_date   => null,
        end_date     => null
    );
END;
/

Use below to add a privilege to grant or deny the network access to the user. The access control entry (ACE) will be created if it does not exist.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'SCOTT',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/
Use Below to assign an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/

commit;
dont forget to give commit at end of the session to make these changes permanent.

The below procedure deletes a privilege in an access control list.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => 'SCOTT.xml',
    principal   => 'MN',
    is_grant    => TRUE,
    privilege   => 'connect');
  COMMIT;
END;
/

Execute privilege is must after running the above procedures.

select grantee , table_name , privilege from dba_tab_privs where table_name = 'UTL_MAIL' and   grantee = 'PUBLIC';

GRANTEE    TABLE_NAME   PRIVILEGE                  
---------- ----------- -----------------
PUBLIC     UTL_MAIL     EXECUTE              

select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                     HOST         LOWER_PORT  UPPER_PORT                           ---------------------------------------------------------
/sys/acls/SCOTT.xml mailservername      25         25
/sys/acls/SCOTT.xml Mailservername
/sys/acls/SCOTT.xml mailserver.in.com


select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES; 
ACL                  PRINCIPAL    PRIVILE   IS_GR
-----------------   ------------ --------- ---------------
/sys/acls/SCOTT.xml SCOTT          connect  true


SELECT HOST, LOWER_PORT, UPPER_PORT, ACL,
   DECODE(
     DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,  'SCOTT', 'connect'),
     1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM DBA_NETWORK_ACLS
WHERE host IN
  (SELECT * FROM      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('msxsmtp.server.bosch.com')))
 ORDER BY
  DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT, UPPER_PORT;

HOST                 LOWER_PORT  UPPER_PORT  ACL           PRIVILE
-------------------- ---------- ------------------------------
smtp.server.in.com     25         25        /sys/acls/SCOTT.xml GRANTED
smtp.server.in.com                          /sys/acls/SCOTT.xml
GRANTED

For adding additional users to the ACL use below

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'MN',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/
commit;


Reference :


http://oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm

Regards,
Navaneeth