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

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