Monday, January 23, 2012

ACFS Snapshot setup & views


In this below note, I have configured the ADVM & ACFS mount point already. So I am creating snapshot for the existing ACFS mount points. If you don’t have ACFS mount points then we have to create it first.

What is ACFS Snapshot

Oracle ACFS snapshot is an online, read-only or read-write, point in time copy of an Oracle ACFS file system.
The snapshot copy is space-efficient and uses Copy-On-Write functionality. Oracle ACFS snapshots are immediately available for use after they are created. The snapshots are created in the .ACFS/snaps/ directory of the file system. They are always online while the file system is mounted. Oracle ACFS read-write snapshots enable fast creation of a snapshot image that can be both read and written without impacting the state of the Oracle ACFS file system 

1)    We can use the snapshot before installing & removing patches for the $ORACLE_HOME or any application which are in   production environment. We will have original copy of data in the snapshot which we can move it at any time.
2)    Testing of new versions of application software on production file data reflected in the read-write snapshot image without    modifying the original production file system
3)    Running test scenarios on a real data set without modifying the original production file system
4)    Oracle ACFS snapshot can support the online recovery of files inadvertently modified or deleted from a file system.

View detail information on each file system

To Collect more information about ACFS, you will get it through the below view

oracrs +ASM1 > /sbin/acfsutil info fs –h

It has penalty of option available on this view

To collect the statistics for the ACFS Mount point

oracrs +ASM1 > /sbin/acfsutil info fs /oracle/dbdump -s
    amount of change since mount:       0.11 MB
    average rate of change since mount: 0 KB/s

CREATE ACFS SNAPSHOTS
TO create a snapshot in read-write or read-only mode

oracrs +ASM1> /sbin/acfsutil snap create SNAP_PREUPGRADE /oracle/dbadmin
acfsutil snap create: Snapshot operation is complete.

VIEW ACFS SNAPSHOTS
                          
To know about the snapshot for the particular mount in the ACFS, below command will work from 11.2.0.3 onwards

$ /sbin/acfsutil snap info /oracle/dbdump

To view current snapshot files. All the snapshot will be located under the mount point of the ACFS file system .ACFS/snaps folder

oracrs +ASM1 > cd /oracle/dbadmin/

oracrs +ASM1 > pwd
/oracle/dbadmin/.ACFS/snaps
oracrs +ASM1 > ls -lrt
total 4
drwxrwxr-x 22 oracle oinstall 4096 Jan 20 11:42 SNAP_PREUPGRADE
oracrs +ASM1 >

To view the specific mount point information and it gives us total no of snapshot available for the current mount point

oracrs +ASM1 > /sbin/acfsutil info fs /oracle/dbadmin
/oracle/dbadmin
    ACFS Version: 11.2.0.2.0
    flags:        MountPoint,Available
    mount time:   Thu Jan 12 09:58:51 2012
    volumes:      1
    total size:   20669530112
    total free:   20450643968
    primary volume: /dev/asm/acfs_vol1-98
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       40.0
        allocation unit:       4096
        major, minor:          252, 50177
        size:                  20669530112
        free:                  20450643968
        ADVM diskgroup         ACFS
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  1
    snapshot space usage: 3198976

To view the snapshot information through sqlplus

SQL> SELECT SUBSTR(fs_name,1,34) FILESYSTEM,SUBSTR(snap_name,1,28) SNAPSHOT, CREATE_TIME TIME FROM V$ASM_ACFSSNAPSHOTS;

FILESYSTEM                         SNAPSHOT                     TIME
---------------------------------- ---------------------------- -----------
/oracle/dbadmin                    SNAP_PREUPGRADE                    20-JAN-2012


DELETE ACFS SNAPSHOTS

oracrs +ASM1 > /sbin/acfsutil snap delete SNAP_PREUPGRADE /oracle/dbadmin
acfsutil snap delete: Snapshot operation is complete.

To identify the file system which are allocated through the ACFS
SQL> col FS_NAME format a15
SQL> select * from V$ASM_FILESYSTEM;

FS_NAME         AVAILABLE_T BLOCK_SIZE STATE         CORRU    NUM_VOL TOTAL_SIZE
--------------- ----------- ---------- ------------- ----- ---------- ----------
TOTAL_FREE TOTAL_SNAP_SPACE_USAGE
---------- ----------------------
/oracle/dbadmin 12-JAN-2012          4 AVAILABLE     FALSE          1      19712
19492.9375                      0

To know about the mount point and volumes allocated in the ACFS

SQL> col FS_NAME format a15
SQL> col VOL_DEVICE format a15
SQL> col VOL_LABEL format a15     
SQL> select * from v$asm_acfsvolumes;

FS_NAME         VOL_DEVICE      VOL_LABEL       PRIMA   TOTAL_MB    FREE_MB
--------------- --------------- --------------- ----- ---------- ----/oracle/dbadmin /dev/asm/acfs_v                 TRUE       19712 19492.9375
                ol1-98

TO understand more about the current volume statistics for ACFS volumes

SQL> col VOLUME_NAME format a15
SQL> select * from V$ASM_VOLUME_STAT;

GROUP_NUMBER VOLUME_NAME     COMPOUND_INDEX VOLUME_NUMBER      READS     WRITES
------------ --------------- -------------- ------------- ---------- ----------
 READ_ERRS WRITE_ERRS  READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
---------- ---------- ---------- ---------- ---------- -------------
           1 ACFS_VOL1             16777217             1      23342       5887
         0          0     12.073     18.228   34204672       5329920



SQL> col VOLUME_NAME format a15
SQL> col USAGE format a15
SQL> col MOUNTPATH  format a15
SQL> select VOLUME_NAME,SIZE_MB,STRIPE_COLUMNS,USAGE,MOUNTPATH from v$asm_volume;

VOLUME_NAME        SIZE_MB STRIPE_COLUMNS USAGE           MOUNTPATH
--------------- ---------- -------------- --------------- ---------------
ACFS_VOL1            19712              4 ACFS            /oracle/dbadmin


In side the ASMCMD tool, we can find about the volumes through the below commands

volcreate
voldelete
voldisable
volenable
volinfo
volresize
volset
volstat

Wednesday, January 18, 2012

restored spfile in DB_UNKNOWN Directory


In the below scenario, I am placing the spfile correctly after the RMAN restored the spfile in the DB_UNKNOWN directory in the diskgroup.

This is oracle bug( Bug 5370663: RMAN RESTORES SPFILE IN ASM TO DB_UNKNOWN’)

Note: 1) we have to enable the autobackup mode in the RMAN to restore the spfile from the backup. If autobackup mode is not enabled then we can’t restore the spfile from backup
      2) I have started with one instance on the RAC database. Till I completed the restore, I will be performing all the activity from the same node. Keep the second instance down.
      3) Remove the init_<INSTANCE_NAME>.ora parameter file from the $ORACLE_HOME/dbs folder. This file will contain the old spfile details. When you start the database with the srvctl command, this file will be created automatically by the oracle agent in the $ORACLE_HOME/dbs folder.

We need to identify the DB_NAME, DBID & DB_UNIQUE_NAME details for recovering the database using RMAN

To start the database in the force mode, only DB_NAME is sufficient

oracle BHU_1 bhuora01> cat initBHU.ora
*.db_name='BHU'
*.db_unique_name='BHU_A'

Start the instance with the force option to the nomount stage


oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:28:28 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount force pfile='/home/oracle/initBHU.ora';
ORACLE instance started.

Total System Global Area  304861184 bytes
Fixed Size                  2225872 bytes
Variable Size             159385904 bytes
Database Buffers          134217728 bytes
Redo Buffers                9031680 bytes
SQL> exit

Note: 1) you can identify the DBID from the level 0 or 1 backup log file
     2) Some time oracle writes the DBID in the alert log file

We have to connect to the database using rman and provide the DBID for the database. If you have the recovery catalog then there is no need to specify the DBID.

oracle BHU_1 bhuora01> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:28:59 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHU (not mounted)

RMAN> set dbid=263762951

executing command: SET DBID

RMAN> run {
ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE';
restore spfile to '+BHU_DATA1' from autobackup;
}
2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: dev_1
channel dev_1: SID=175 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_

Starting restore at 17-JAN-2012

channel dev_1: looking for AUTOBACKUP on day: 20120117
channel dev_1: AUTOBACKUP found: c-263762951-20120117-00
channel dev_1: restoring spfile from AUTOBACKUP c-263762951-20120117-00
channel dev_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-JAN-2012
released channel: dev_1


Spfile has been restored to the DB_UNKNOWN directory under the diskgroup which we specified in the restore


ASMCMD [+BHU_DATA1] > ls -lt
Type  Redund  Striped  Time             Sys  Name
                                        Y    BHU_A/
                                        Y    DB_UNKNOWN/
ASMCMD [+BHU_DATA1] > cd D*
ASMCMD [+BHU_DATA1/DB_UNKNOWN] > cd p*
ASMCMD [+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE] > ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   JAN 17 15:00:00  Y    SPFILE.271.772818033

Once the spfile is restored. We have create a new pfile from the restore spfile from the ASM diskgroup


oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:42:08 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create pfile='/home/oracle/BHU_afterbackup.ora' from spfile='+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.271.772818033';

File created.


Now, I am removing the DB_UNKNOWN directory from the diskgroup
Don’t remove before create a pfile from it.

ASMCMD [+BHU_DATA1] > ls -lt
Type  Redund  Striped  Time             Sys  Name
                                        Y    BHU_A/
                                        Y    DB_UNKNOWN/
ASMCMD [+BHU_DATA1] > ls
DB_UNKNOWN/
BHU_A/
ASMCMD [+BHU_DATA1] > rm -rf db*

We are stopping the database and Re-start the database with the newly created pfile.


oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:43:30 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/zs1_afterbackup.ora';
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1.4431E+10 bytes
Fixed Size                  2240272 bytes
Variable Size            3892314352 bytes
Database Buffers         1.0503E+10 bytes
Redo Buffers               34148352 bytes
SQL> create spfile='+BHU_DATA1' from pfile='/home/oracle/bhu_afterbackup.ora';

File created.

SQL>

We could see that the spfile has been created in the right directory structure.

ASMCMD [+BHU_DATA1/ZS2_A/PARAMETERFILE] > ls
spfile.268.772818375

+BHU_DATA1/ZS2_A/PARAMETERFILE/spfile.268.772818375

Now we are changing the configuration details in the server control

oracle BHU_1 bhuora01> srvctl modify database -d BHU_A -p +BHU_DATA1/BHU_A/PARAMETERFILE/spfile.268.772818375


oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:48:07 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

If you start the database with sqlplus, Oracle can’t able to identify the spfile or pfile in the $ORACLE_HOME/dbs directory.

So we have to start the database with the srvctl command, which has the right spfile details.

If you start the database with the srvctl command, oracle creates pfile for the respective instance on the database.

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/BHU/112_64/dbs/initBHU_1.ora'
SQL> exit

oracle BHU_1 bhuora01> srvctl start database -d BHU_A -o nomount

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string  +BHU_DATA1/BHU_a/parameterfile
                                                 /spfile.268.772818375

Saturday, January 14, 2012

Difference between CANDIDATE & PROVISIONED in ASM DISK




Disks that were discovered but that have not yet been assigned to a disk group have a header status of either CANDIDATE or PROVISIONED.
CANDIDATE
Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
PROVISIONED
Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
For example, on Windows, the administrator used asmtool or asmtoolg to stamp the disk with a header, or on Linux, the administrator used ASMLib to prepare the disk for ASM.


Below are the HEADER_STATUS in the v$ASM_DISK. I have taken below status from 11gR2.
·         UNKNOWN - Automatic Storage Management disk header has not been read
·         CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
·         INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
·         PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
·         MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
·         FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
·         CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
·         FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

      When adding a disk, the FORCE option must be used if Oracle ASM   
      recognizes that the disk was managed by Oracle. Such a disk appears 
      in the V$ASM_DISK view with a status of FOREIGN.

Friday, January 13, 2012

.patch_storage in RDBMS_HOME & GI_HOME

Can we delete .patch_storage in RDBMS_HOME & GI_HOME directoryy

Yes, Please follow the below document. read it very carefully before clearing the files


Is it necessary to keep the $ORACLE_HOME/.patch_storage directory after successful installation of a patch?

Yes, It is necessary to keep the $ORACLE_HOME/.patch_storage directory even after successful installation of a patch. 

Reason for ORACLE storing the patch binaries & patch information on .patch_storage directory

* When you apply an interim patch to an Oracle home, OPatch stores the patch information in $ORACLE_HOME/.patch_storage directory. Inside this directory, there are separate directories created for each patch applied to the Oracle home. Interim patches are bug fixes that are made available to customers in response to specific bugs. CPU (Critical Patch Update) is also a kind of. The latest CPU will rollback the previously applied CPU as CPU is cumulative. So this rollback information is from the .patch_storage directory.

* You may come across a bug conflict and might want to remove the conflicting patch during the patch installation. This process is known as patch rollback. During patch installation, OPatch saves copies of all the files that were replaced by the new patch before the new versions of these files are loaded and stores it in $ORACLE_HOME/.patch_storage/patch ID/. These saved files are called rollback files and are the key to making patch rollback possible.


To Clean up the .patch_storage directory, oracle has come up with option called 
"opatch util cleanup"  option, please read the below document.

How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]

Thursday, January 12, 2012

How to Upgrade RMAN Catalog in 11gR2 after applying a PSU Patch


This post discuss about how to upgrade RMAN catalog in 11gR2 after applying a PSU Patch

Note: This is not the database upgrade, this post provides information on the RMAN Catalog upgrade for an database after a PSU patch has been applied in the database server.

“The recovery catalog schema version must be greater than or equal to the RMAN client version”.

For your 11.2 database, if you are running RMAN from the same ORACLE_HOME, your RMAN Client is 11.2. The catalog schema must be 11.2. Although it can be in an 11.1 database, it would have been preferable to have it in 11.2. The better course is to upgrade the RMAN Catalog database to the highest of all the target databases and the Catalog schema to the highest RMAN client.

-- Connect to the target database on the server

oracle > rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jan 12 15:03:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHUVAN (DBID=3982664177)

-- Connect to the Recover catalog database

RMAN> connect catalog rman/xxxxx@RMAN_CATALOG
connected to recovery catalog database

-- upgrade the catalog by connecting to the target database and catalog database.

RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade


RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 11.02.00.02
DBMS_RCVMAN package upgraded to version 11.02.00.02
DBMS_RCVCAT package upgraded to version 11.02.00.02

RMAN>

TO DETERMINE THE CURRENT RELEASE OF THE CATALOG SCHEMA, YOU MUST RUN A SQL QUERY.


oracle> sqlplus rman/xxxx@RMAN_CATALOG

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 12 15:19:51 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SELECT * FROM rcver;

VERSION
------------
11.02.00.02


Note:
1)  If multiple versions are listed, then the last row is the current version, and the rows before it are prior versions.
2) For releases 11.2 and later, the last two digits in the rcver output indicate patch level. For earlier releases, they are always zeros.