Skip to content

Posts from the ‘RAC & ASM’ Category

ASM : Multiplex redolog files

If you have two diskgroup, you want to multiplex redo in different diskgroup, you just add redo log desitnation
diskgroup.

sql>alter system set db_create_online_log_dest_1='+RED01' scope=spfile;

System altered.

sql>alter system set db_create_online_log_dest_2='+REDO2' scope=spfile;

System altered

SQL> create pfile='/u01/initcrmprod.ora' from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@siebeldb ~]$ vi /u01/initcrmprod.ora
[oracle@siebeldb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 28 18:12:42 2012

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/initcrmprod.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size 2213976 bytes
Variable Size 3623880616 bytes
Database Buffers 3087007744 bytes
Redo Buffers 34623488 bytes
Database mounted.
Database opened.

SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#;

GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
3 52428800 INACTIVE
+DATA/crmprod/onlinelog/group_3.263.781072923

3 52428800 INACTIVE
+FRA/crmprod/onlinelog/group_3.259.781072923

2 52428800 INACTIVE
+DATA/crmprod/onlinelog/group_2.262.781072911
GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
2 52428800 INACTIVE
+FRA/crmprod/onlinelog/group_2.258.781072913

1 52428800 CURRENT
+DATA/crmprod/onlinelog/group_1.261.781072891

1 52428800 CURRENT
+FRA/crmprod/onlinelog/group_1.257.781072891
6 rows selected.

QL> show parameter db_create_online_log_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +REDO1
db_create_online_log_dest_2 string +REDO2
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 52428800;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 52428800;

Database altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance crmprod (thread 1) - cannot drop
ORA-00312: online log 1 thread 1:
'+DATA/crmprod/onlinelog/group_1.261.781072891'
ORA-00312: online log 1 thread 1:
'+FRA/crmprod/onlinelog/group_1.257.781072891'

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance crmprod (thread 1)
ORA-00312: online log 1 thread 1:
'+DATA/crmprod/onlinelog/group_1.261.781072891'
ORA-00312: online log 1 thread 1:
'+FRA/crmprod/onlinelog/group_1.257.781072891'

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance crmprod (thread 1)
ORA-00312: online log 1 thread 1:
'+DATA/crmprod/onlinelog/group_1.261.781072891'
ORA-00312: online log 1 thread 1:
'+FRA/crmprod/onlinelog/group_1.257.781072891'
SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#;

GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
3 52428800 CURRENT
+REDO1/crmprod/onlinelog/group_3.256.781812953

3 52428800 CURRENT
+REDO2/crmprod/onlinelog/group_3.256.781812953

2 52428800 ACTIVE
+REDO1/crmprod/onlinelog/group_2.257.781812977
GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
2 52428800 ACTIVE
+REDO2/crmprod/onlinelog/group_2.257.781812977

1 52428800 ACTIVE
+DATA/crmprod/onlinelog/group_1.261.781072891

1 52428800 ACTIVE
+FRA/crmprod/onlinelog/group_1.257.781072891
6 rows selected.

SQL> alter system checkpoint global;

System altered.

SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#;

GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
3 52428800 CURRENT
+REDO1/crmprod/onlinelog/group_3.256.781812953

3 52428800 CURRENT
+REDO2/crmprod/onlinelog/group_3.256.781812953

2 52428800 INACTIVE
+REDO1/crmprod/onlinelog/group_2.257.781812977
GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
2 52428800 INACTIVE
+REDO2/crmprod/onlinelog/group_2.257.781812977

1 52428800 INACTIVE
+DATA/crmprod/onlinelog/group_1.261.781072891

1 52428800 INACTIVE
+FRA/crmprod/onlinelog/group_1.257.781072891
6 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 52428800;

Database altered.

SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#;

GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
3 52428800 CURRENT
+REDO1/crmprod/onlinelog/group_3.256.781812953

3 52428800 CURRENT
+REDO2/crmprod/onlinelog/group_3.256.781812953

2 52428800 INACTIVE
+REDO1/crmprod/onlinelog/group_2.257.781812977
GROUP# BYTES STATUS
---------- ---------- ----------------
MEMBER
--------------------------------------------------------------------------------
2 52428800 INACTIVE
+REDO2/crmprod/onlinelog/group_2.257.781812977

1 52428800 UNUSED
+REDO1/crmprod/onlinelog/group_1.258.781813125

1 52428800 UNUSED
+REDO2/crmprod/onlinelog/group_1.258.781813125
6 rows selected.

SQL>

Upgrading to oracle 11gR2 RAC database webseminar video archive

Here I noticed that who are missed the webseminar about Upgrading to Oracle 11gr2 cluster enviornment presented by Mr.Syed Jaffar Hussain on last thrusday. Here this is the chance to watch again from web seminar video archive at red-gate website.I hope you can get more idea about upgrade cluster enviorment after this video watched and also get a good stuff about upgrade.

Upgrading to Oracle 11gR2 RAC presented by My.Syed Jaffar Hussain.

Oracle 11gr2 two node real application cluster installation guide on linux using Virtual Box 4.1.4

Here I just demonstrated how do we install two node rac on oracle Virtual Box.

Please refer before you try to install rac on virtual box.

Vmware Server rac installation guide , click below url.

Oracle 11gr2 two node rac installation guide on linux using vmware

Oracle Virtual Box 4.1.4

Download the below pdf document, I hope this document will helpful for you.

Oracle 11gr2 rac two node installation guide on linux using virtual box 4.1.4

Download right click on this URL , choose save target as and save it.

ORA-27300: OS system dependent operation:invalid_egid failed with status: 1

When i’m trying to create new database for RAC environment using DBCA,  It shows the error about invalid gid and diskgroup not mounted.

ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 501 (oinstall), current egid = 502 (dba)

yes I’m realized, the problem may happen for permission issues of oracle binary execute file in grid home.

I check the permission for Grid Home/bin/oracle binary file

[oracle@racnode1 ~]$ ls -l /u01/app/11.2.0/grid/bin/oracle
-rwxrwxr-x oracle dba 210824714 Sep 14 07:58 /u01/app/11.2.0/grid/bin/oracle
[oracle@racnode1 ~]$

Stop ASM instance and then execute following command

[oracle@racnode1 ~]$ chmod 6751 /u01/app/11.2.0/grid/bin/oracle
[oracle@racnode1 ~]$ ls -l /u01/app/11.2.0/grid/bin/oracle
-rwsr-s--x 1 oracle dba 210824714 Sep 14 07:58 /u01/app/11.2.0/grid/bin/oracle
[oracle@racnode1 ~]$

Change the permission on second node grid home oracle binary file also If it was modified before.

Start ASM instance again and then now you try to create database using DBCA. It will successful.

Oracle 11gR2 two node Real application cluster installation step by step guide on linux using VMware Server

Here…If you would like switch on to RAC DBA, first you must know about RAC installation. you think It may difficult for me while installation, Not at all but sometimes. you may refer some installation document so in this case If you’re searched in Google,  you can get a more installation document. then Why I was published, Answer is you can realized after reviewed my installation document. I hope This document style should be user friendly for you. In this document I demonstrated how do we install Oracle 11gr2 two node rac on linux using vmware server 2.0.

Download the below pdf document, I’m sure this installation guide should be easy for you.

Oracle 11gR2 two node real application cluster installation guide on linux using vmware server

Download right click on this url, choose save target as and save it.

could not validate asmsnmp password due to following error ora-01031:insufficent privileges

During Create new database, It shows following errors

 

“could not validate asmsnmp password due to following error ora-01031:insufficent privileges”

 

 

Solution :
[oracle@mazar ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@mazar ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@mazar ~]$ export ORACLE_SID=+ASM
[oracle@mazar ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 3 20:54:14 2011

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

Enter user-name: sys/Admin543 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SQL>  create user asmsnmp identified by Admin321;

User created.

SQL> grant sysdba to asmsnmp;

Grant succeeded.

ASMCMD-08102: no connection to ASM; command requires ASM to run

[oracle@mazar ~]$ asmcmd
Connected to an idle instance.
ASMCMD> ls -L +DATA
ASMCMD-08102: no connection to ASM; command requires ASM to run

Solution :
We need to Set ASM Instance Oracle home path 
[oracle@mazar ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@mazar ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@mazar ~]$ export ORACLE_SID=+ASM
[oracle@mazar ~]$ asmcmd

ASMCMD> ls -L +DATA/DBASM/DATAFILE
SYSAUX.257.753010705
SYSTEM.256.753010703
TESTTBS.275.753248961
UNDOTBS1.258.753010705
USERS.259.753010705
ASMCMD>

ORA-01565: error in identifying file /dbs/spfile@.ora

When we’re going to create pfile from spfile while spfile running on ASM instance, We can face the below errors,

SQL> create pfile=’/u01/inittest.ora’ from spfile;
create pfile=’/u01/inittest.ora’ from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file ‘?/dbs/spfile@.ora’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Solution :

We need to give ASM path of SPFILE.

SQL> create pfile=’/u01/inittest.ora’ from spfile=’+DATA/dbasm/spfiledbasm.ora’;

File created.

Can I Store Datapump dumpfiles in ASM diskgroup?

One of my friend asked me , Can I store Datapump dumpfile in asm diskgroup?. Yes you can. Now we can see How do we create directory and store dumpfile.

Step 1: Go To ASM Instance and Create New Directory.

C:\Documents and Settings\Administrator>set oracle_sid=+asm

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 22 15:27:13 2011

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

Enter user-name: / as sysdba

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

SQL> alter diskgroup data add directory '+DATA/dumpset';

Diskgroup altered.

Step 2:  Go to DB Instance

 Create Directory for dumpfile and logfile


Dumpfile Directory (ASM Disk)

SQL> create or replace directory dp_asm as '+DATA/dumpset';

Directory created.

Log file Directory (Local File System).

SQL> create or replace directory logfile as 'C:\azar';

Directory created.

SQL> grant read,write on directory dp_asm to system;

Grant succeeded.

SQL> grant read,write on directory logfile to system;

Grant succeeded.

Step 3: Doing Export using datapump


SQL> $expdp system/Admin123 directory=dp_asm dumpfile=testasm.dmp schemas=scott
logfile=logfile:testasm.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 22 February, 2011 15:35:00

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dp_asm dump
file=testasm.dmp schemas=scott logfile=logfile:testasm.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  +DATA/dumpset/testasm.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:36:01
SQL>

Step 4: Go to ASM Instance and Check the file created in ASM


SQL> select file_number,creation_date,bytes from v$asm_file where type='DUMPSET';

FILE_NUMBER CREATION_      BYTES
----------- --------- ----------
        283 22-FEB-11     212992

SQL>

Grid infrastructure installation standalone server for windows video guide

Here I just demonstrate for grid infrastructure installation standalone server for windows and also include How do we create asmdisk for windows 7.

Watch Now:

Just a Good quality watch from here also

http://www.youtube.com/watch?v=1aKPY0Zi_rc

For Grid infrastructure installation standalone server for Linux

https://mohamedazar.wordpress.com/2010/11/29/grid-infrastructure-installation-guide-for-standalone-server/