My Blog

September 25, 2008

Add new diskgroup in oracle rac

Filed under: Oracle — by enotebook @ 8:56 am

Host information – Linux & Oracle
$ uname -a
Linux rac1.andy.com 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL>

Use sqlplus to connect ASM instance on node1(rac1). Check the value of instance_type to make sure it’s connecting to ASM instance
$ export ORACLE_SID=+ASM1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Sep 24 16:03:19 2008
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, Real Application Clusters, OLAP and Data Mining options
SQL> show parameter instance_type;
NAME TYPE VALUE
———————————— ———– ——————————
instance_type string asm
SQL>

Check the raw devices to determine if they have been detected.
SQL> select path from v$asm_disk;
PATH
——————————————————————————–
/dev/raw/raw5 # detected – this is the newly bounded raw device
/dev/raw/raw4
/dev/raw/raw3
/dev/raw/raw2
4 rows selected.
If there is no newly bounded raw devices detected, try to modify the value of parameter – asm_diskstring, and re-check
SQL> show parameter asm_diskstring
NAME TYPE VALUE
———————————— ———– ——————————
asm_diskstring string
SQL> alter system set asm_diskstring=’/dev/raw/raw*’;
System altered.

Issue the command to create diskgroup
SQL> create diskgroup DG2 external redundancy disk ‘/dev/raw/raw5’;
Diskgroup created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
3 DG2 MOUNTED 3067
SQL>

Diskgroup mounted automatically on this node, but not mounted on node2, need to mount it manually.
$ ssh rac2
Last login: Wed Sep 24 16:00:05 2008 from rac1.andy.com
$ export ORACLE_SID=+ASM2
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Sep 24 17:08:31 2008
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, Real Application Clusters, OLAP and Data Mining options

SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
0 DG2 DISMOUNTED 0
SQL> alter diskgroup DG2 mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
3 DG2 MOUNTED 3067
SQL>
Create tablespace.
SQL> create tablespace test datafile ‘+DG2/test.dbf’ size 10M;
Tablespace created.

Done.

September 23, 2008

Processes to solve problem of ora-12514(sometimes comes out as ora-12523)

Filed under: Oracle — by enotebook @ 5:28 pm

Just finished the Oracle 10g RAC installation on linux with using VMware server, everything runs smoothly without testing connection to the instance on first node(rac1). Details like blowing.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:14:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
It raised an error when trying to connect devdb1(instance on first node named rac1), enven i was allowed to connect devdb2 and devdb. crs_stat -t was issued:
$ crs_stat -t
Name Type Target State Host
————————————————————
ora…..crm.cs application ONLINE ONLINE rac1
ora….db1.srv application ONLINE ONLINE rac2
ora.devdb.db application ONLINE ONLINE rac1
ora….b1.inst application ONLINE ONLINE rac1
ora….b2.inst application ONLINE ONLINE rac2
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
$
Every resource is running fine. Then try to check the status of listener.

$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:15:30
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$
Outputs like above showing us the listener does not even ‘trurely started'(started incorrectly). Tried to restart it and re-check the status.

$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

The outputs are same with above while excuting command of lsnrctl status. Tried to compare the content of listener.ora in rac1 with rac2 and found that HOST string is not same within rac2, HOST = 172.16.113.32 in rac2, but HOST = 127.0.0.1 in rac1, i guess it causes the problem. Modify it and make it sync with rac2.

$ more listener.ora
# listener.ora.rac1 Network Configuration File: /u01/app/oracle/product/10g/db/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.andy.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10g/db)
(PROGRAM = extproc)
)
)
$ vi listener.ora

Restart listener on rac1.
$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

Check the status.
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:18:07
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 23-SEP-2008 15:18:01
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10g/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10g/db/network/log/listener_rac1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.31)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
$

Then connect to devdb1.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:29:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

It works!
Done.

September 17, 2008

Funny math

Filed under: Miscellaneous — by enotebook @ 12:56 pm

Funny math
1.


2.

3.

4.

5.

6.

Blog at WordPress.com.