Thursday, August 7, 2014

Managing multiple archive log destinations with RMAN








Relevant TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.

objective

Explain how multiple archive destinations are handled during an RMAN backup and restore process

SOLUTION

DELETE INPUT

DELETE INPUT will remove archivelogs from the first destination. It will also remove archivelogs from the second destination that was PREVIOUSLY backed up.

For example:
Backup #1
  • Backup archivelogs older than seven days from destination #1, and remove these files
  • Do not backup or remove any archivelogs in destination #2 has all files
Backup #2
  • For destination #1, backup archivelogs older than seven days and remove these files
  • For destination #2, backup archivelogs that satisfied backup criteria #1 (more than 14 days old) and remove them
Whereas a DELETE ALL INPUT will backup from one destination and delete both copies of the archivelog.

Backup and Delete from a Specified Archive Destination

If you'd like to only backup and remove from a single archive destination, use the LIKE clause:
eg:
RMAN> backup archivelog
until time 'sysdate -7'
like '/u04/oracle/admin/TEST/arch/%'
delete input;

The above will only backup and delete from the LIKE destination
If archive logs are written up to multiple destinations, and if the required retention policy is required to be different for different destinations, then the following can be done:
 
RMAN> run {
backup archivelog all;
delete archivelog until time 'sysdate -1' like '/am3/oradata/arch/%';
delete archivelog until time 'sysdate -5' like '/am3/oradata/arch1/AM3P2/%';
}
 

The FRA and DELETE INPUT

RMAN will backup and remove the archivelogs in numerical order from _dest_1 to dest_10. However,
if one of the archive destinations is an FRA, RMAN will always backup and remove from the FRA
first, and then work on the numerical order of the log_archive_dest_x
eg.
If the following parameters were set:
  • log_archive_dest_1='location=/u002/oraarch/ORA1020'
  • log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST'
  • log_archive_dest_3='location=/u002/oraarch3/ORA1020'
Oracle will backup and remove from the FRA, folllowed by archivelogs in log_archive_dest_1.
Finally, logs in log_archive_dest_3 will be removed.

Where do the restored archivelogs go?

During the restore process RMAN will check all archive destinations to make sure that the archivelog requested does not already exist. If the archivelog already exist in one of the destinations RMAN will not restore the file. If the archivelog does not exist in any of the destinations RMAN will restore it to the FRA if it exists. Otherwise it will restore it to the highest archive destination defined.

Restoring archivelog to FRA in ASM

When using the FRA and ASM, the archivelog will be restored to the current directory, rather than the time at which it was generated. Even if using the SET ARCHIVELOG DESTINATION, an alias will be created to the current directory.
For example, on the 16 Aug 2010, when restoring the 13 Aug archivelogs:
RMAN> run {
    allocate channel c1 type 'sbt_tape';
    set archivelog destination to '+SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_13';
    restore archivelog
         from time "to_date('13/08/2010-00:00:00','dd/mm/yyyy-hh24:mi:ss')"
         until time "to_date('14/08/2010-00:00:00','dd/mm/yyyy-hh24:mi:ss')";
  }


+SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_13
ASMCMD> ls -ltr
Type  Redund  Striped  Time    Sys  Name
                                 N    1_94528_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94528.5341.727224533
                                 N    1_94529_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94529.3425.727224661
                                 N    1_94530_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94530.5644.727224779

Wednesday, August 6, 2014

ASMLIB quick reference

ASMLIB

Linux OS Service 'oracleasm'

  • Service Name
oracleasm
  • Description
The oracleasm service is used to provision, configure and manage Oracle Automatic Storage Management (ASM) disks via the Oracle Automatic Storage Management library driver (ASMLib). The oracleasm services creates the necessary library interface through which ASM disk devices are made available to Oracle ASM (instance).
  • Nature
System service
  • Configuration File
/etc/sysconfig/oracleasm
  • Oracle Enterprise Linux Version(s)
  • OEL 4
  • OEL 5
  • Requirement
Optional - needed only if operating system-level management and configuration of Oracle ASM disk devices is required. Not needed if Oracle ASM (instance-only) management of ASM group/disk devices is required/preferred.

oracleasm

/etc/init.d/oracleasm 

$ oracleasm -h
Usage: oracleasm [--exec-path=] [ ]
       oracleasm --exec-path
       oracleasm -h
       oracleasm -V

The basic oracleasm commands are:
    configure        Configure the Oracle Linux ASMLib driver
    init             Load and initialize the ASMLib driver
    exit             Stop the ASMLib driver
    scandisks        Scan the system for Oracle ASMLib disks
    status           Display the status of the Oracle ASMLib driver
    listdisks        List known Oracle ASMLib disks
    querydisk        Determine if a disk belongs to Oracle ASMlib
    createdisk       Allocate a device for Oracle ASMLib use
    deletedisk       Return a device to the operating system
    renamedisk       Change the label of an Oracle ASMlib disk
    update-driver    Download the latest ASMLib driver



OptionDescription
configure
Use the configure option to reconfigure the Automatic Storage Management library driver, if necessary:
# /etc/init.d/oracleasm configure
enable
disable
Use the disable and enable options to change the actions of the Automatic Storage Management library driver when the system starts. The enable option causes the Automatic Storage Management library driver to load when the system starts:
# /etc/init.d/oracleasm enable
start
stop
restart
Use the startstop, and restart options to load or unload the Automatic Storage Management library driver without restarting the system:
# /etc/init.d/oracleasm restart
createdisk
Use the createdisk option to mark a disk device for use with the Automatic Storage Management library driver and give it a name:
# /etc/init.d/oracleasm createdisk DISKNAME devicename
deletedisk
Use the deletedisk option to unmark a named disk device:
# /etc/init.d/oracleasm deletedisk DISKNAME
Caution: Do not use this command to unmark disks that are being used by an Automatic Storage Management disk group. You must delete the disk from the Automatic Storage Management disk group before you unmark it.
querydisk
Use the querydisk option to determine if a disk device or disk name is being used by the Automatic Storage Management library driver:
# /etc/init.d/oracleasm querydisk {DISKNAME | devicename}
listdisks
Use the listdisks option to list the disk names of marked Automatic Storage Management library driver disks:
# /etc/init.d/oracleasm listdisks
scandisks
Use the scandisks option to enable cluster nodes to identify which shared disks have been marked as Automatic Storage Management library driver disks on another node:
# /etc/init.d/oracleasm scandisks

Q&A

  • Is /dev/oracleasm created?
When ASMLIB is configured, a special filesystem is created and mounted: /dev/oracleasm.
   
$ df -ha
Filesystem            Size  Used Avail Use% Mounted on
/dev/hdc2              13G   11G  1.9G  85% /
none                     0     0     0   -  /proc
none                     0     0     0   -  /dev/pts
usbdevfs                 0     0     0   -  /proc/bus/usb
/dev/hdc1             101M   14M   81M  15% /boot
none                  250M     0  250M   0% /dev/shm
/dev/sda1             8.4G  4.8G  3.2G  60% /oradata2
/dev/sde1             8.3G  6.6G  1.4G  84% /oradata3
oracleasmfs              0     0     0   -  /dev/oracleasm

When command oracleasm createdisk is executed, a block device is created under /dev/oracleasm/disks.   This is the device discovered by ASMLIB using the string ORCL:*. 
$ ll /dev/oracleasm/disks
total 0
brw-rw----  1 oracle dba 8,  97 Apr 28 15:20 VOL001
brw-rw----  1 oracle dba 8,  81 Apr 28 15:20 VOL002
brw-rw----  1 oracle dba 8,  65 Apr 28 15:20 VOL003
brw-rw----  1 oracle dba 8,  49 Apr 28 15:20 VOL004
brw-rw----  1 oracle dba 8,  33 Apr 28 15:20 VOL005
brw-rw----  1 oracle dba 8,  17 Apr 28 15:20 VOL006
brw-rw----  1 oracle dba 8, 129 Apr 28 15:20 VOL007
brw-rw----  1 oracle dba 8, 113 Apr 28 15:20 VOL008

  • Checking if ASMLIB was installed properly:
[root@arlnx2 asm_tar]# /etc/init.d/oracleasm status
Checking if ASM is loaded:                                  [  OK  ]
Checking if /dev/oracleasm is mounted:                [  OK  ]

If the command fails, use strace and generate a log file:

strace -f -o asm_status.out /etc/init.d/oracleasm status

Additional information to verify the installation can be found in note 269194.1 

  • Listing the ASMLIB disks:
$ /etc/init.d/oracleasm listdisks
VOL001
VOL002
VOL003
VOL004
VOL005
VOL006
VOL007
VOL008

$ ll /dev/oracleasm/disks

total 0
brw-rw----  1 oracle dba 8,  97 Apr 28 15:20 VOL001
brw-rw----  1 oracle dba 8,  81 Apr 28 15:20 VOL002
brw-rw----  1 oracle dba 8,  65 Apr 28 15:20 VOL003
brw-rw----  1 oracle dba 8,  49 Apr 28 15:20 VOL004
brw-rw----  1 oracle dba 8,  33 Apr 28 15:20 VOL005
brw-rw----  1 oracle dba 8,  17 Apr 28 15:20 VOL006
brw-rw----  1 oracle dba 8, 129 Apr 28 15:20 VOL007
brw-rw----  1 oracle dba 8, 113 Apr 28 15:20 VOL008

You will find an entry under /dev/oracleasm/disks.   This is the block device associated to the physical device. If the file exist the command will return information, but if not, plese execute:

strace -f -o asm_listd.out /etc/init.d/oracleasm listdisks
  • How to identify the physical disk bound to the ASMLIB disk.
  Use  /etc/init.d/oracleasm querydisk   where NAME is any name under /dev/oracleasm/disks.
[root@arlnx2 asm_tar]# /etc/init.d/oracleasm querydisk -d VOL1

Disk "VOL1" is a valid ASM disk on device [8, 33]
The command reports the device identified with major,minor numbers which are unique numbers associated to each disk.  File /proc/partitions can be used to find the name of the device associated with those numbers:

$ more /proc/partitions

major minor  #blocks  name     rio rmerge rsect ruse wio wmerge wsect wuse running use aveq

   8     0    8891620 sda 39715 78016 941080 417000 156198 242472 3189752 214180 0 420630 631180
   8     1    8891376 sda1 39691 77970 940922 416780 156198 242472 3189752 214180 0 420410 630960
   8    16    8891620 sdb 87 250 803 740 0 0 0 0 0 740 740
   8    17    8891376 sdb1 57 193 632 480 0 0 0 0 0 480 480
   8    32   17783250 sdc 745 2993 8321 8300 0 0 0 0 0 5250 8300
   8    33     977904 sdc1 87 139 644 1040 0 0 0 0 0 1040 1040 
   8    34     977920 sdc2 35 193 456 230 0 0 0 0 0 230 230
   8    35          1 sdc3 4 0 8 40 0 0 0 0 0 40 40
   8    37     977904 sdc5 57 193 632 1240 0 0 0 0 0 1240 1240
   8    38     977904 sdc6 57 193 632 1170 0 0 0 0 0 1170 1170

Also connected as root you can run the same command but referencing the physical device:

[root@arlnx2 dbs]# /etc/init.d/oracleasm querydisk /dev/sdc1
Disk "/dev/sdc1" is marked an ASM disk with the label "VOL1"


Any error on this command will require using strace:
strace -f -o asm_query.out /etc/init.d/oracleasm querydisk
  • How to identify if ASMLIB is used or not
SQL> select path ,library from v$asm_disk;
PATH                 LIBRARY
-------------------- ------------------------------------------------------------
ORCL:VOL001          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL002          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL003          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL004          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)

PATH                                               LIBRARY
-------------------------------------------------- ------------------------------------------------------------
/dev/oracleasm/disks/ASM7                          System
/dev/oracleasm/disks/ASM2                          System
/dev/oracleasm/disks/ASM1                          System
/dev/oracleasm/disks/ASM5                          System
/dev/oracleasm/disks/ASM6                          System
/dev/oracleasm/disks/ASM4                          System
/dev/oracleasm/disks/ASM3                          System

Troubleshooting ASM/ASMLIB issues

1) In order to check if the ASMLIB API is correctly configured, please execute the next commands and provide us the output (from each node if this is RAC): 
$> cat /etc/*release 
$> uname -a 
$> rpm -qa |grep oracleasm 
$> df -ha
2) Check the discovery path (from each node if this is RAC): 
$> /etc/init.d/oracleasm status 
$> /usr/sbin/oracleasm-discover 
$> /usr/sbin/oracleasm-discover 'ORCL:*'

3) Please check if the ASMLIB devices can be accessed (from each node if this is RAC): 
$> /etc/init.d/oracleasm scandisks 
$> /etc/init.d/oracleasm listdisks 
$> /etc/init.d/oracleasm querydisk  
$> ls -l /dev/oracleasm/disks
4) Upload the next files from each node if this is RAC: 
=)> /var/log/messages* 
=)> /var/log/oracleasm
=)> /etc/sysconfig/oracleasm
5) Please show us the partition table (from each node if this is RAC): 
$> cat /proc/partitions
6) If you are using multipath devices (mapper devices or emcpower) then show me the output of: 
$> ls -l /dev/mpath/* 

$> ls -l /dev/mapper/* 

$> ls -l /dev/dm-*  

$> ls -l /dev/emcpower* 
Or if you have another multipath configuration then list the devices: 
$> ls -l /dev/*
7) Finally connect to your ASM instance, execute the next script and upload me the output file (from each node if this is RAC): 
spool asm<#>.html
SET MARKUP HTML ON 
set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

select * from v$asm_diskgroup;

SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER; 

SELECT * FROM V$ASM_CLIENT; 

select * from V$ASM_ATTRIBUTE;

select * from v$asm_operation;
select * from gv$asm_operation


select * from v$version;

show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile

show sga

spool off

exit

ASM using ASMLib and Raw Devices

ASM using ASMLib and Raw Devices

Introduction
Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which ASM manages internally. On Linux, ASM is capable of referencing disks as raw devices or by using the ASMLib software. This article presents the setup details for using either raw devices or ASMLib, as well as the procedures for converting between both methods.
The article assumes the operating system installation is complete, along with an Oracle software installation. The ASM instance shares the Oracle home with the database instance. If you plan on running multiple database instances on the server the ASM instance should be installed in a separate Oracle home.
Note: When running Oracle 10g Release 2 on RHEL 4 you should consider reading this article: Using Block Devices for Oracle 10g Release 2 in RHEL 4

Partition the Disks

Both ASMLib and raw devices require the candidate disks to be partitioned before they can be accessed. In this example, three 10Gig VMware virtual disks are to be used for the ASM storage. The following text shows the "/dev/sdb" disk being partitioned.
# ls sd*
sda  sda1  sda2  sdb  sdc  sdd
# 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.


The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

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

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

Calling ioctl() to re-read partition table.
Syncing disks.
#
The remaining disks ("/dev/sdc" and "/dev/sdd") must be partitioned in the same way.

ASMLib Installation

This step is only necessary if you want to use ASMLib to access the ASM disks.
Determine your kernel version using the following command as the root user.
# uname -r
2.6.9-34.ELsmp
#
Download the ASMLib software from the OTN website, making sure you pick the version that matches your distribution, kernel and architecture. For this example I used CentOS 4.3, so the following packages were required.
  • oracleasm-support-2.0.1-1.i386.rpm
  • oracleasmlib-2.0.1-1.i386.rpm
  • oracleasm-2.6.9-34.ELsmp-2.0.1-1.i686.rpm
Install the packages as the root user.
# rpm -Uvh oracleasm-support-2.0.1-1.i386.rpm \
           oracleasmlib-2.0.1-1.i386.rpm \
           oracleasm-2.6.9-34.ELsmp-2.0.1-1.i686.rpm
Preparing...                ########################################### [100%]
   1:oracleasm-support      ########################################### [ 33%]
   2:oracleasm-2.6.9-34.ELsm########################################### [ 67%]
   3:oracleasmlib           ########################################### [100%]
#
With the software installed, configure the ASM kernel module.
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration:           [  OK  ]
Creating /dev/oracleasm mount point:                       [  OK  ]
Loading module "oracleasm":                                [  OK  ]
Mounting ASMlib driver filesystem:                         [  OK  ]
Scanning system for ASM disks:                             [  OK  ]
#
Once the kernel module is loaded, stamp (or label) the partitions created earlier as ASM disks.
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk:                   [  OK  ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
Marking disk "/dev/sdc1" as an ASM disk:                   [  OK  ]
# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd1
Marking disk "/dev/sdd1" as an ASM disk:                   [  OK  ]
#
If this were a RAC installation, the disks would only be stamped by one node. The other nodes would just scan for the disks.
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks:                             [  OK  ]
#
The stamped disks are listed as follows.
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
#
The disks are now ready to be used by ASM.

Raw Device Setup

This step is only necessary if you want ASM to access the disks as raw devices.
Edit the "/etc/sysconfig/rawdevices" file, adding the following lines.
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc1
/dev/raw/raw3 /dev/sdd1
Restart the rawdevices service using the following command.
service rawdevices restart
Run the following commands and add them the "/etc/rc.local" file.
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chmod 600 /dev/raw/raw1
chmod 600 /dev/raw/raw2
chmod 600 /dev/raw/raw3
The ASM raw device disks are now configured.

ASM Creation

Creation of the ASM instance is the same, regardless of the use of ASMLib or raw devices. When using ASMLib, the candidate disks are listed using the stamp associated with them, while the raw devices are listed using their device name.
To configure an ASM instance, start the Database Configuration Assistant by issuing the "dbca" command as the oracle user. On the "Welcome" screen, click the "Next" button.
DBCA Welcome
Select the "Configure Automatic Storage Management" option, then click the "Next" Button.
DBCA Configure ASM
If the Oracle Cluster Syncronization Service (CSS) is not currently running, a warning screen will be displayed. Follow the instructions and click the "OK" button. Once you've returned to the previous screen, click the "Next" button again.
DBCA CSS Warning
The script gives the following output.
# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        centos2
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
#
Enter a password for the ASM instance, then click the "Next" button.
DBCA ASM Password
On the confirmation screen, click the "OK" button.
DBCA ASM Creation Confirm
Wait while the ASM instance is created.
DBCA ASM Creation
Once the ASM instance is created, you are presented with the "ASM Disk Groups" screen. Click the "Create New" button.
DBCA ASM Disk Groups
On the "Create Disk Group" screen, enter Disk Group Name of "DATA" and select the required level of redundancy:
  • External - ASM does not mirror the files. This option should only be used if your disks are already protected by some form of redundancy, like RAID.
  • Normal - ASM performs two-way mirroring of all files.
  • High - ASM performs three-way mirroring of all files.
In this example, the "High" redundancy is used. Select all three candidate disks and click the "OK" button. The following image shows how the candidate disks are displayed when using ASMLib.
DBCA Create Disk Group ASMLib
When using raw devices, the candidate discs are listed using the devide names.
DBCA Create Disk Group Raw Devices
On the "ASM Disk Groups" screen. Click the "Finish" button.
DBCA ASM Disk Groups
Click the "Yes" button to perform another operation.
DBCA Another Operation
You are now ready to create a database instance using ASM.

Database Creation

Before continuing with the database creation, check the listener is up and the ASM instance has registered with it. Start the listener using the following command.
$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-APR-2006 14:35:46

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener .log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos2.localdomain)(POR T=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                29-APR-2006 14:35:47
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listen er.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos2.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
$
The ASM instance is not registered, so we can force the registration by doing the following.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 29 14:37:06 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr oduction
With the Partitioning, OLAP and Data Mining options
$
Checking the status of the listener shows that the ASM instance is now registered.
$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-APR-2006 14:37:32

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                29-APR-2006 14:35:47
Uptime                    0 days 0 hr. 1 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listen er.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos2.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
$
Go back to the DBCA and create a custom database in the normal way, selecting the "Automatic Storage Management (ASM)" storage option.
DBCA ASM Storage Option
Enter the ASM password if prompted, then click the "OK" button.
DBCA ASM Password
Select the "DATA" disk group, then clicking the "Next" button.
DBCA ASM Disk Groups
Accept the default "Oracle-Managed Files" database location by clicking the "Next" button.
DBCA Database Location
Enable the "Flash Recovery Area" and Archiving, using the "+DATA" disk group for both.
DBCA Recovery Config
Continue with the rest of the DBCA, selecting the required options along the way.

Switching from Raw Devices to ASMLib

Shutdown any databases using the ASM instance, but leave the ASM instance itself running. Connect to the running ASM instance.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba
Perform the ASMLib Installation, but stop prior to stamping the ASM disk. If you attempt to stamp the disks using the createdisk command it will fail.
Alter the ASM disk discovery string to exclude the raw devices used previously, then shutdown the ASM instance.
SQL> ALTER SYSTEM SET asm_diskstring = 'ORCL:VOL*' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
ASM diskgroups dismounted
ASM instance shutdown
SQL>
If you are planning to remove the raw device mappings (Raw Device Setup), you could simply reset the ASM_DISKGROUP parameter.
SQL> ALTER SYSTEM RESET asm_diskstring SCOPE=SPFILE SID='*';

System altered.

SQL>
At this point the disks will not be used by ASM because they are not stamped. As mentioned previously, the createdisk command used to stamp new disks would fail, so we must issue the renamedisk command as the root user for each disk.
# /etc/init.d/oracleasm renamedisk /dev/sdb1 VOL1
Renaming disk "/dev/sdb1" to "VOL1":                       [  OK  ]
# /etc/init.d/oracleasm renamedisk /dev/sdc1 VOL2
Renaming disk "/dev/sdc1" to "VOL2":                       [  OK  ]
# /etc/init.d/oracleasm renamedisk /dev/sdd1 VOL3
Renaming disk "/dev/sdd1" to "VOL3":                       [  OK  ]
#
Notice, the stamp matches the discovery string set earlier. The ASM instance can now be started.
SQL> STARTUP
ASM instance started

Total System Global Area   83886080 bytes
Fixed Size                  1217836 bytes
Variable Size              57502420 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>
The ASM instance is now using ASMLib, rather than raw devices. All dependent databases can now be started.

Switching from ASMLib to Raw Devices

Shutdown any databases using the ASM instance, but leave the ASM instance itself running. Connect to the running ASM instance.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba
Alter the ASM disk discovery string to match the raw devices you plan to set up, then shutdown the ASM instance.
SQL> ALTER SYSTEM SET asm_diskstring = '/dev/raw/raw*' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
ASM diskgroups dismounted
ASM instance shutdown
SQL>
Perform all the steps listed in the Raw Device Setup, then start the ASM instance.
SQL> STARTUP
ASM instance started

Total System Global Area   83886080 bytes
Fixed Size                  1217836 bytes
Variable Size              57502420 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>
The ASM instance is now using the disks as raw devices, rather than as ASMLib disks. All dependent databases can now be started.

Performance Comparison

Some documents suggests using ASMLib with Oracle 10g Release 2 gives superior disk performance, while others say it only reduces the time searching for candidate disks, and hence ASM startup time. I decided to compare the performance of the two methods myself to see if I could tell the difference.
My first thought was to perform a simple insert/update/delete test, so I created the following user and schema for the test in a database using and ASM instance using ASMLib.
export ORACLE_SID=DB10G
sqlplus / as sysdba

CREATE TABLESPACE test_ts;

CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE test_ts QUOTA UNLIMITED ON test_ts;
GRANT CONNECT, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
  id    NUMBER,
  data  VARCHAR2(4000),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
Then, as the test user, I ran the following code several times and calculated an average time for each operation.
SET SERVEROUTPUT ON
DECLARE
  l_loops NUMBER := 1000;
  l_data  VARCHAR2(32767) := RPAD('X', 4000, 'X');
  l_start NUMBER;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    INSERT INTO test_tab (id, data) VALUES (i, l_data);
    COMMIT;
  END LOOP;

  DBMS_OUTPUT.put_line('Inserts (' || l_loops || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    UPDATE test_tab
    SET    data = l_data
    WHERE  id   = i;
    COMMIT;
  END LOOP;

  DBMS_OUTPUT.put_line('Updates (' || l_loops || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    DELETE FROM test_tab
    WHERE  id = i;
    COMMIT;
  END LOOP;

  DBMS_OUTPUT.put_line('Deletes (' || l_loops || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  EXECUTE IMMEDIATE 'TRUNCATE TABLE test_tab';
END;
/
The code is purposely inefficient, using a single statement and a commit within a loop for each operation. Remember, the ASM instance is using high redundancy, so each physical write operation is effectively done 3 times.
Once the tests on ASMLib were complete, I switched to using raw devices and repeated the tests. The average results for 1000 of each operation are listed below.
Operation       ASMLib (hsecs)  Raw Devices (hsecs)
==============  ==============  ===================
Inserts (1000)             468                  852
Updates (1000)             956                 1287
Deletes (1000)            1281                 1995
You can instantly see that the ASMLib results are better than those of the raw devices, but the testing is suspect for the following reasons:

  • For each single run of the script, only 1000 operations of each type were performed. That equates to about 4M of data in the table when it is full. When you consider the use of the buffer cache, this is a pitiful amount of data. I originally intended to perform many more operations, but my disk was grinding so badly I thought better of it.
  • The tests were performed using VMware virtual disks, so really all this work was being done on a single SATA disk. I can't be sure if these results aren't just an artifact of the setup.
  • Although the average results look convincing, the raw data was so eratic I'm not convinced these results mean anything.

How to identify if ASMLIB is used or not

  • How to identify if ASMLIB is used or not
SQL> select path ,library from v$asm_disk;
PATH                 LIBRARY
-------------------- ------------------------------------------------------------
ORCL:VOL001          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL002          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL003          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)
ORCL:VOL004          ASM Library - Generic Linux, version 2.0.2 (KABI_V2)

PATH                                               LIBRARY
-------------------------------------------------- ------------------------------------------------------------
/dev/oracleasm/disks/ASM7                          System
/dev/oracleasm/disks/ASM2                          System
/dev/oracleasm/disks/ASM1                          System
/dev/oracleasm/disks/ASM5                          System
/dev/oracleasm/disks/ASM6                          System
/dev/oracleasm/disks/ASM4                          System
/dev/oracleasm/disks/ASM3                          System

To find the OS utility location

To find the OS utility location

find / -name fuser

Friday, August 1, 2014

Find the Archivelog names by using the SCN

Find the Archivelog names by using the SCN

During database recovery, you may have a SCN number and need to know the archivelog names. Here is the SQL for the answer:

column first_change# format 9,999,999,999
column next_change# format 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;

If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:

restore archivelog from logseq=45164 until logseq=45179;

Or, use commands to check the backup status:

list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;