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

Transaction Slot on a Block

Transaction Slot on a Oracle Block :

Your process does some DML that's going to update a table, so, Oracle reserves a slot in the transaction header of a rollback segment. This is specified by the rollback number, slot, and wrap. This is what's stored in the transaction slot of the data block that's to be modified. So, now that transaction slot in the data block points to that placeholder in the rollback segment header. When rows in the data block are updated, then in the data block's row directory, the lock byte for that row is set to point to that transaction slot. So, the row directory entry (lock byte) for an updated row points to a specific transaction slot in the data block's transaction header, which in turn points to the rollback transaction slot, which in turn points to where the before images are actually recorded in the rollback segment.

Reference :

Very good one...

http://www.proligence.com/itl_waits_demystified.html

Regards,

Navaneeth

Using 10g Logminer on 9.2.0.7 archive files

in 9i production server

SQL> execute   DBMS_LOGMNR_D.build ('dictionary.ora','C:\BCH\VER2.0\EXPORT\MONTHLY',OPTIONs => DBMS_LOGMNR_D.store_in_flat_file);

PL/SQL procedure successfully completed.

in 10g test server using 10g logminer to analyse 9.2.0.7 archive logfiles

execute DBMS_LOGMNR.ADD_LOGFILE('D:\je_rda\JE_logmining\LOG_1_31443.ARC',options => dbms_logmnr.new);


execute DBMS_LOGMNR.START_LOGMNR(dictfilename =>'D:\je_rda\JE_logmining\dictionary.ora');

select * from v$logmnr_logs
select * from v$logmnr_logfile
desc v$logmnr_stats

select SEG_OWNER,SEG_NAME,TABLE_NAME,SEG_TYPE,ROW_ID,SESSION#,SERIAL#,USERNAME,SESSION_INFO,SQL_REDO,SQL_UNDO,AUDIT_SESSIONID from v$logmnr_contents

Reference :

Oracle 10g New Features of LogMiner [ID 249001.1]
LogMiner - Frequently Asked Questions (FAQ) [ID 174504.1]
Oracle9i LogMiner New Features [ID 148616.1]
LogMiner Utility Release 8.1.x - 10g [ID 291686.1];

Regards,

Navaneeth

Sharing folder from RHEL5 machine to Windows machine

Configuring Samba service on RHEL5 machine

Create a share folder in RHEL5 machine which u want to share with Windows machine.

Check Linux machine for the samba packages whether installed or not.

[root@nav1 ~]# rpm -qa *samba*
sblim-cmpi-samba-0.5.2-31.el5_2.1
sblim-cmpi-samba-test-1-31.el5_2.1
samba-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
sblim-cmpi-samba-devel-1-31.el5_2.1
system-config-samba-1.2.41-3.el5
samba-common-3.0.33-3.7.el5
samba-swat-3.0.33-3.7.el5
You have new mail in /var/spool/mail/root

[root@nav1 ~]# service smb status
smbd (pid 3635 3068 3029) is running...
nmbd (pid 3032) is running...
[root@nav1 ~]#

[root@nav1 ~]# useradd samba -d /home/samba
[root@nav1 ~]# smbpasswd -a samba
New SMB password:
Retype new SMB password:
Added user samba.
[root@nav1 ~]# cat /etc/samba/smb
smb.conf  smbusers 
[root@nav1 ~]# cat /etc/samba/smb.conf

[windows]
        path = /u01/windows
        writeable = yes
;       browseable = yes
        valid users = az, nv, samba
[root@nav1 ~]#

After doing any changes to smb.conf file, please restart smb service to take effect of the changes as below.

[root@nav1 ~]# service smb restart
Shutting down SMB services:                                [  OK  ]
Shutting down NMB services:                                [  OK  ]
Starting SMB services:                                     [  OK  ]
Starting NMB services:                                     [  OK  ]
[root@nav1 ~]#

Now go to Windows machine and try to Map the network drive using the RHEL5 machine IP and share name and Samba username/password.
It should work fine.

Reference:

http://mars.netanya.ac.il/~unesco/cdrom/LDP/Samba/HTML_FORMAT/SMB-HOWTO.html#toc6


Regards,
Navaneeth

Sharing folder from Windows to RHEL5 machine

First share a folder in windows and give permission to everyone to access to that folder.

Go to linux machine and do the below steps

Prerequsties should be , all the packages for running SAMBA service should be installed on the Linux machine. And the SAMABA service should be running on the machine.

Please use below commands for checking about the SAMBA service.

[root@nav1 ~]# rpm -qa *samba*
sblim-cmpi-samba-0.5.2-31.el5_2.1
sblim-cmpi-samba-test-1-31.el5_2.1
samba-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
sblim-cmpi-samba-devel-1-31.el5_2.1
system-config-samba-1.2.41-3.el5
samba-common-3.0.33-3.7.el5
samba-swat-3.0.33-3.7.el5
You have new mail in /var/spool/mail/root

[root@nav1 ~]# service smb status
smbd (pid 3635 3068 3029) is running...
nmbd (pid 3032) is running...
[root@nav1 ~]#

[root@nav1 ~]# smbclient -L WindowsIP -U APAC/WinUsrId
Password:
Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

        Sharename       Type      Comment
        ---------       ----      -------
        IPC$            IPC       Remote IPC
        D$              Disk      Default share
        ADMIN$          Disk      Remote Admin
        C$              Disk      Default share
        linux_share     Disk     
Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

        Server               Comment
        ---------            -------

        Workgroup            Master
        ---------            -------
[root@nav1 /]# smbclient \\\\WindowsIP\\linux_share -U APAC/WinUsrId
Password:
Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]
smb: \> ls
  .                                   D        0  Thu Oct 13 21:29:35 2011
  ..                                  D        0  Thu Oct 13 21:29:35 2011

                59616 blocks of size 2097152. 37993 blocks available
smb: \> ls
  .                                   D        0  Thu Oct 13 21:41:10 2011
  ..                                  D        0  Thu Oct 13 21:41:10 2011
  sfsdf                               A        0  Thu Oct 13 21:41:07 2011

                59616 blocks of size 2097152. 37993 blocks available
smb: \>

For Configuring through GUI use below option

go to places menu -> connect to server

it will open a connet to server configuration menu

1. select service type as "Windows Share"
2. Enter Windows Server name which u want to share in the server name option
3. Enter share folder name in the folder name option
4. Enter windows username in the username option
5. Enter windows Domain name in domain name option
6. Name to use for connection is optional and u can use whatever name u want

at last give connect it will ask for password for windows user id, give the passwd thats it !! 

it will be connected to the windows share folder and u will be able to do whatever u want.

Reference:

http://mars.netanya.ac.il/~unesco/cdrom/LDP/Samba/HTML_FORMAT/SMB-HOWTO-7.html

Regards,

Navaneeth

Adding Harddisk on RHEL5

[root@localhost ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1           6       48163+  83  Linux
/dev/sda2               7         515     4088542+  83  Linux
/dev/sda3             516         776     2096482+  82  Linux swap / Solaris
/dev/sda4             777        1305     4249192+   5  Extended
/dev/sda5             777        1305     4249161   83  Linux

Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@localhost ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-913, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-913, default 913):
Using default value 913

Command (m for help): p

Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         913     7333641   83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@localhost ~]# mount -t ext3 /dev/sdb1 /u01

[root@nav1 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             3.8G  1.9G  1.7G  53% /
/dev/sda5             4.0G  137M  3.6G   4% /home
/dev/sda1              46M   11M   33M  24% /boot
tmpfs                 506M     0  506M   0% /dev/shm
/dev/sdb1             6.9G  144M  6.4G   3% /u01

For mounting permanently add the entry in the fstab file in "/etc" location as below

[root@nav1 ~]# cat /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/home             /home                   ext3    defaults        1 2
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda3         swap                    swap    defaults        0 0
/dev/sdb1               /u01                    ext3    defaults        0 0

Regards,
Navaneeth