Tuesday, September 29, 2009

Createdisk, Deletedisk and Querydisk in ASM

The /etc/init.d/oracleasm script is used to create, delete and query ASM disks and make disks available.

Create and Delete ASM disk:
Run below command to Create ASM disks.
# /etc/init.d/oracleasm createdisk DATA1 /dev/sdc
Creating Oracle ASM disk "DATA1" [ OK ]

Run below command to Delete ASM disks.
# /etc/init.d/oracleasm deletedisk DATA1
Deleting Oracle ASM disk "DATA1" [ OK ]

Query/List/Scan ASM disk:
Run the below querydisk command to see if the Disk/Device is used by ASM:
# /etc/init.d/oracleasm querydisk /dev/sdc
Checking if device "/dev/sdc" is an Oracle ASM disk [ OK ]

# /etc/init.d/oracleasm querydisk DATA1
Checking for ASM disk "DATA1" [ OK ]

Run below command to list Existing disks
# /etc/init.d/oracleasm listdisks

Run the below command to scan the disks created on another node.
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks [ OK ]

Satishbabu Gunukula

Friday, September 18, 2009

How to move or replace and repair OCR (Oracle Cluster Registry)

Moving or Replacing Oracle Cluster Registry:
If you are replacing or moving an OCR/OCR MIRROR make sure that other OCR file is ONLINE and also clusterware is running on the node that you are performing the replace operation.

Please note that the OCR that you are replacing or moving can be either online or offline.

Run the below command to move or replace OCR
#ocrconfig -replace ocr
For ex:- ocrconfig -replace ocr /dev/raw/raw5

Run the below command to move or replace OCR MIRROR.

#ocrconfig -replace ocrmirror
For ex: - ocrconfig -replace ocr /dev/raw/raw6

Repairing Oracle Cluster Registry (OCR) Configuration:
You may need to repair an OCR configuration, if your OCR configuration changes ran (adding/removing/replacing OCR/OCR mirror) while the node is shutdown.

Make sure you run the command on the node which you have shutdown and you cannot perform this operation while oracle clusterware is running.

Run the below command to repair
# ocrconfig –repair ocrmirror device_name
for ex:- ocrconfig –repair ocrmirror /dev/raw/raw1

Click here to learn How to Add and Remove Oracle Cluster Registry

Satishbabu Gunukula

Wednesday, September 16, 2009

How to Change Static parameters in SPFILE and PFILE

Change Static parameters in SPFILE:
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.

You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME                     VALUE          ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 FALSE

All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.

The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.

For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.

SQL> alter system set processes=200 scope=spfile;
System altered.

You can view the new value in V$SPPARAMETER view as the SPFILE is updated.

SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME                     VALUE         ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 TRUE

Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.

Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.

You can see the Oracle SPFILE location.

SQL > show parameter spfile;
NAME                    TYPE        VALUE
------------------ ----------- -----------------------------------------
spfile                     string          /oracle/v10201/dbs/spfileOradb.ora

SQL > select name, value from v$parameter where name = 'spfile';
NAME          VALUE
------------ ------------------------------------------------------------
spfile             /oracle/v10201/dbs/spfileOradb.ora

SQL> select count(1) from v$spparameter where isspecified = 'TRUE';

Returns a NON-ZERO value if SPFILE is in use.

Satishbabu Gunukula

ORA-19755: could not open change tracking file

You will receive ORA-19755 under following circumstances

1. When moving the Change tracking file
2. During Database Recovery
3. During Database startup

The reason is due to missing or corrupted Block change tracking file.

Errors during database startup
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DATA/testdb/changetracking/ctf.411. 5567291'
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb/changetracking/ctf.411.5567291'
ORA-15012: ASM file '+DATA/testdb/changetracking/ctf.411. 5567291' does not exist

Errors during Recovery:-
RMAN-03002: failure of recover command at 05/05/2009 08:13:29
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘/oradata/testdb/flash_recovery_area/archivelog/arch_1_532_432353.arc‘
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: ‘/u02/oradata/test/ofsap_blk_change.dbf’
ORA-27037: unable to obtain file status

Disable Block change tracking and perform recover and enable the tracking.

Click here learn Enable/Disable and moving the Block change tracking file

Satishbabu Gunukula

Friday, September 11, 2009

How to Add and Remove OCR (Oracle Cluster Registry)

Adding an OCR (Oracle Cluster Registry):-

You can also add an OCR after completing the Oracle RAC installation, if you didn’t mirror the OCR during installation. Please note that you must run all the commands as “root”.

Run the below command to add OCR MIRROR.
#ocrconfig -replace ocrmirror

Removing an Oracle Cluster Registry (OCR)
To remove OCR/OCR MIRROR the other OCR must be online.

Run the below command on any node to remove OCR.
# ocrconfig -replace ocr

Run the below command on any node to remove OCR Mirror.
# ocrconfig -replace ocrmirror

Please note that these commands update the OCR configuration on all of the nodes where Oracle Clusterware is running.

Click here to learn How to backup and recover Oracle Cluster Registry.
Satishbabu Gunukula

Thursday, September 3, 2009

ORA-29740: evicted by member 0, group incarnation

I have seen RAC node eviction error in common on RAC Databases running on Oracle 10g R2 ( Here is the details explanation and solution.

Node Eviction in RAC causes the Instance hang or restart.
Check for Possible Error messages in “Database/ASM” alert.log and trace files.

IPC Send timeout detected.Sender: ospid 25102
Receiver: inst 2 binc 860622349 ospid 12543
IPC Send timeout to 1.2 inc 10 for msg type 36 from opid 44
Communications reconfiguration: instance_number 2
Trace dumping is performing id=[cdmp_20090819220537]
Waiting for clusterware split-brain resolution
Errors in file /oracle/10201/admin/testdb/bdump/testdb_lmon_3433.trc:
ORA-29740: evicted by member 0, group incarnation 12
LMON: terminating instance due to error 29740

Trace file generated for ospid 25102 (1st line in alert.log):
(13190 <- 30904)SKGXPDOAINVALCON: connection 0x2a9754b730 scoono 0x15694aec acconn 0x399413e2 getting closed. inactive: threshold: 0x4bff6 (13190 <- 30904)SKGXPDOAINVALCON: WARN: potential problem in keep alive connection protocol LMON Trace file:
GES IPC: Receivers 3 Senders 3
GES IPC: Buffers Receive 1000 Send (i:1050 b:1050) Reserve 301
kjxgmrcfg: Reconfiguration started, reason 1
kjxgmcs: Setting state to 0 0.
kjxgrrcfgchk: Initiating reconfig, reason 3
kjxgmrcfg: Reconfiguration started, reason 3
kjxgrrecp2: Waiting for split-brain resolution, upd 0, seq 12

If you find these symptoms then you are hitting a bug 4631662.

Cause: Due to Bug 4631662, you will see instance evictions caused by network timeouts. This bug is caused by a failure in "ach reaping", "ach reaping" reduces the packet size being sent to a receiver.
1. Upgrade database to, this feature is disabled in
2. Modify the below parameter to disable the feature.

Run the below command if you are using spfile.
SQL> alter system set "_skgxp_udp_ach_reaping_time"=0 sid='*';

If you are using pfile add the below line to ini.ora parameter
*._skgxp_udp_ach_reaping_time = 0

ASM: Add the following lines to init.ora parameter.
*._disable_instance_params_check = TRUE
*._skgxp_udp_ach_reaping_time = 0

Click here to see the instructions to create pfile or spfile and apply the changes.

Satishbabu Gunukula

Transportable tablespace export and Import

Transportable tablespaces export and import is manageable across platforms and only Meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that
1. source and target database must use the same character set/national character set
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.

Transportable tablespace export and import on same endian platforms
Step 1: Find the Operating system byte order on Source and Target Database
SQL > select * from v$transportable_platform order by platform_id;
----------------- ---------------------- ------- ----------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little

For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit(Little endian) machine TESTLINUX to Microsoft Windows 64 bit (Little endian) machine TESTWIN. Both the source and target platforms are of LITTLE endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbff.

Step 2:- Make the tablespace “READ ONLY”
SQL> alter tablespace test_user_tbs read only;

Step 3: Export metadata
(i) Using export utility$ exp testuser/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp log=exp_test_user_tbs.log

(ii) Using Export data pump utility$ expdp system/password TRANSPORT_TABLESPACES=test_user_tbs TRANSPORT_FULL_CHECK=Y DIRECTORY=export_dir DUMPFILE=expdp_test_user_tbs.dmp logfile= expdp_test_user_tbs.log

TRANSPORT_FULL_CHECK= Y Specifies that check for dependencies between those objects inside the transportable Tablespace and those outside the transportable Tablespace.

The file “exp_test_user_tbs.dmp” or ““expdp_test_user_tbs.dmp” contains only metadata.

Step 4: Copy the files to Target system
If you are using FTP use binary option.

Step 5: Initiate Import to plug the tablespace into the database.
(i) Using import utility $ imp test_user/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp datafiles='test_user_tbs01.dbf' log=imp_test_user_tbs.log

(ii) Using impdp utility
Click here to see the Instructions to create Directory and grant privileges.

$ impdp test_user/test123 TRANSPORT_DATAFILES='test_user_tbs01.dbf' DIRECTORY=import_dir DUMPFILE=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.log

You can use REMAP_SCHEMA= (source: target), if you want to import into another schema.

Step6: - Put the tablespace in read/write mode.

Now the database has a tablespace named “test_user_tbs” and the objects of the tablespace will be available.

Transport Tablespace Import Common Errors:-
1. Oracle Error : EXP-00044: must be connected "AS SYSDBA" to do Point-in-time Recovery or Transportable Tablespace import
Cause: The user must log in "as SYSDBA" to perform transportable tablespace imports or Point-In-Time Recovery imports.
Action: Ask your database administrator to perform the Transportable Tablespace import or
the Tablespace Point-in-time Recovery import.

2. IMP-00017: following statement failed with ORACLE error
19721:IMP-00003: ORACLE error 19721 encountered
ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
ORA-06512: at line 1
Cause: A duplicated data file name in the import parameters file was causing the issue
Action: Modify the import parameters file with the right datafile name

Please see the Data pump Export/Import related documents:

Click here for Data Pump Export modes and Interfaces.
Click here for Data Pump Export/Import with Examples.

Satishbabu Gunukula

Wednesday, September 2, 2009

Create spfile from pfile

Create spfile from pfile in Oracle:
Use the CREATE SPFILE statement to create a Server Parameter file from PFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the SPFILE example.

SQL> create spfile from pfile;
SQL> Create spfile='/tmp/spfileOradb.ora' from pfile;

Common Errors:
1. If you edit SPFILE or corrupted, you will receive below error (Don not edit spfile).
SQL> alter system set db_files=200 scope=spfile;
alter system set db_files=200 scope=spfile
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

2. If spfile deleted accidentally, you will get below error during Database startup.
SQL> startup
ORA-01078: failure in processing system parameters

Solution: Recreate a binary SPFILE from PFILE.

Remove parameter from Spfile:
You can remove spfile parameter by using RESET command.

SQL> ALTER SYSTEM RESET session_cached_cursors SCOPE=spfile sid='SID*'

Create pfile from spfile:
Use the CREATE PFILE statement to create a Parameter file from SPFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the pfile example.

SQL> create pfile from spfile;
SQL> create pfile = '/tmp/initDB1.ora' from spfile;

Common Errors:
you will encounter this error while creating pfile/spfile.

ERROR at line 1:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory Additional information: 3

1. If database is down and you are trying to create a pfile from spfile.
2. Providing wrong file names.

Change Oracle spfile Location :
If you want to change the spfile location, create a PFILE in the default location and in pfile specify the spfile parameter location to non-default location.

For ex: - PFILE contents look like below
spfile = “New_path”/spfileOradb.ora

Create spfile from pfile in RAC :
In order to use same spfile at startup each RAC instance uses its own pfile and that points to one shared spfile on shared storage. If you are using ASM the spfile then the spfile will be located in Shared ASM file system.

SQL > show parameter spfile;
-------- ------- -----------------------------------------
spfile string +DATA/testdb/spfiletestdb.ora

Use above commands to create spfile from pfile and pfile from spfile.

Common Errors in RAC:
You will receive below error when modifying the parameter SCOPE=memory using SID=’*’

SQL> alter system set sga_target=500m scope=memory SID=’*’;
alter system set sga_target=500m scope=memory
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Solution: - You need to modify the parameter individually on each instance using the SID=’Instance_Name’

Satishbabu Gunukula

Tuesday, September 1, 2009

Block Change Tracking in Oracle 10g

Oracle Block change Tracking improves the incremental Backup performance and is used to record changed blocks in each datafile in a change tracking file. If change Tracking is enabled, RMAN uses block change tracking file to identify changed blocks for incremental backups and avoids full datafile scans during the backup.

Block change tracking feature introduce in Oracle 10g R1. By default, the Block change tracking file is created as Oracle managed file in DB_CREATE_FILE_DEST.

By default, Oracle will not record block change information. You can Enable or disable the change tracking when the database is open or mounted.

Enable and Disable Block Change Tracking:
Run the below command to enable block change tracking

Run the below command to create change tracking file in a specified location.
USING FILE '/oradata/block_change_track.ora' REUSE;

Run the below command to disable block change tracking

Run the below Query to monitor the status of block change tracking

Moving Block Change tracking file without Database shutdown:
If your database is 24x7 critical production and you cannot shut down, then follow the below steps. Please note that you must disable change tracking and you will lose the old contents of the change tracking file, if you choose this method.

Step1: Disable the change tracking

Step2: Re-enable it at the new location

Moving Block Change tracking file with Database shutdown:
Step1: Determine the change tracking file:

Step2: shutdown and move or copy the tracking file.
$ cp ‘/old_lockation/block_change_tracking.ora’ ‘/new_location/ block_change_tracking.ora’

Step3: Mount the database and rename change tracking file to new location.
SQL> ALTER DATABASE RENAME FILE ‘old_location’ TO ‘new_location’;

Step4: Open the database

Block change tracking in RAC (Real Applications Clusters) environment, the file must be located on shared storage so that the file is accessible for all the nodes.

Satishbabu Gunukula