MOVING DATAFILE IN ASM BY ASMCMD(cp)

After I wrote about Moving Datafile(system) from one ASM Diskgroup to Another, From example I used RMAN to move datafile. I read ocpdba's comment and curious Moving Datafile in ASM by asmcmd(cp). then start to test..

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DISK01
DISK02

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production
connected to target database: ORCL (DBID=1224745511)

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK02/orcl/datafile/users.336.701196307

This test, I want to move datafile from +DISK02 to +DISK01
.. Bring datafile offline

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

sql statement: ALTER DATABASE DATAFILE 4 OFFLINE

After bring DATAFILE offline... then copy datafile by asmcmd

$ asmcmd -p
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users.336.701196307
ASMCMD-08016: copy source->'+DISK02/orcl/datafile/users.336.701196307' and target->'+DISK01/orcl/datafile/users.336.701196307' failed
ORA-19505: failed to identify file "+DISK01/orcl/datafile/users.336.701196307"
ORA-17502: ksfdcre:4 Failed to create file +DISK01/orcl/datafile/users.336.701196307
ORA-15046: ASM file name '+DISK01/orcl/datafile/users.336.701196307' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Can't copy, so copy to new file name

ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf


ASMCMD [+] > ls -la +DISK02/orcl/datafile/users.336.701196307
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 25 17:00:00 Y none => users.336.701196307

ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411

ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411

After copied datafile... then rename file in database

SQL> ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf';

Database altered.

Check in alert log...
Sun Oct 25 17:24:55 2009
ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
Deleted Oracle managed file +DISK02/orcl/datafile/users.336.701196307
Completed: ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'

"ALTER DATABASE RENAME FILE" with ASM(OMF), that delete old datafile.

After renamed datafile in Database, then check + recover + bring datafile online.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf

RMAN> RECOVER DATAFILE 4;

Starting recover at 25-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 instance=orcl1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 25-OCT-09

RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

sql statement: ALTER DATABASE DATAFILE 4 ONLINE

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf

That's a good way... but I still interested about file (name, type) in ASM, after copied ;)

Shared Memory Tuning: startup database - ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

SQL> startup

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device

I have used RHEL X86_64 + memory (32GB) and setted sga_max_size=20G + sga_target=20G and ...

$ cat /proc/sys/kernel/shmmax

26843545600

What wrong with my kernel tuning... So, I checked metalink (301830.1) and some recommend from RHEL

They told; set SHMALL to the total amount of physical RAM divided by page size.

SHMALL =>Total amount of shared memory available (bytes or pages)

then:

Check Page Size:

$ getconf PAGE_SIZE

4096

Determine the system wide maximum number of shared memory pages:

$ cat /proc/sys/kernel/shmall

2097152

My system 64bits with memory 32GB, then 1024 * 1024 * 1024 * 32 / 4096 = 8388608

So, change kernel.shmall = 8388608

$ su - root

# echo 8388608 > /proc/sys/kernel/shmall

Or modify /etc/sysctl.conf file:

kernel.shmall=8388608

and

# sysctl -p

After changed... check and startup database again:

$ cat /proc/sys/kernel/shmall

8388608

SQL> startup

.

.

.

--- NO ERROR ---

By the way, I read Tuning Red Hat Enterprise Linux: Oracle and Oracle RAC

From this idea with memory 32GB

mem=$(free|grep Mem|awk '{print$2}')

totmem=$(echo "$mem*1024"|bc)

huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')

max=$(echo "$totmem*75/100"|bc)

all=$(echo "$max/$huge"|bc)

echo "kernel.shmmax = $max"

echo "kernel.shmall = $all"

Result:

kernel.shmmax = 25213120512, kernel.shmall = 12311094

However, This case "ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device" , solved by set SHMALL = MemTotal(byte)/PAGE_SIZE

and ...

I hope to hear your idea about kernel tuning with Oracle Database.

Moving datafile (system) from one ASM Diskgroup to Another

We have to move database file to NEW diskgroup... In case, we have to move system + sysaux database files as well.

With Oracle ASM, when we need to move database file from one diskgroup to another. we have to use "rman" to help.

About moving system + sysaux database files, we need database (mount)

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

--------------------------------------------------- --------

+OLD/db/datafile/sysaux.260.699468081 3

+OLD/db/datafile/system.259.699468079 1

SQL> SELECT name FROM v$asm_diskgroup;

NAME

--------------------

OLD

NEW

We need to move database files to NEW diskgroup

SQL> shutdown immediate;

SQL> startup mount;

Connect target database by "rman" command-line :

$ rman target /

connected to target database: DB (DBID=1043389676)

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 560 SYSTEM *** +OLD/db/datafile/system.259.699468079

3 500 SYSAUX *** +OLD/db/datafile/sysaux.260.699468081

Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroup

RMAN> run {

BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";

BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";

SWITCH DATAFILE "+OLD/db/datafile/sysaux.260.699468081" TO COPY;

SWITCH DATAFILE "+OLD/db/datafile/system.259.699468079" TO COPY;

}

Open database...

SQL> alter database open;

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

--------------------------------------------------- --------

+NEW/db/datafile/sysaux.291.700906921 3

+NEW/db/datafile/system.294.700906903 1

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 560 SYSTEM *** +NEW/db/datafile/system.294.700906903

3 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921

After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...

RMAN> run {

DELETE DATAFILECOPY "+OLD/db/datafile/sysaux.260.699468081";

DELETE DATAFILECOPY "+OLD/db/datafile/system.259.699468079";

}

reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm

If not "SYSTEM" database file. we can move database file while database open...

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

------------------------------------------------------------------ --------

+OLD/db/datafile/users.258.699468081 4

SQL> SELECT name FROM v$asm_diskgroup;

NAME

--------------------

OLD

NEW

We need to move database file (4) to NEW diskgroup

Connect target database by "rman" command-line

$ rman target /

connected to target database: DB (DBID=1043389676)

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

4 5 USERS *** +OLD/db/datafile/users.258.699468081

Bring database file to offline

RMAN> SQL "ALTER DATABASE DATAFILE ' ' +OLD/db/datafile/users.258.699468081 ' ' OFFLINE";

or

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

Backup as copy database file to NEW diskgroup

RMAN> BACKUP AS COPY DATAFILE "+OLD/db/datafile/users.258.699468081" FORMAT "+NEW";

or

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";

Switch database file to NEW diskgroup

RMAN> SWITCH DATAFILE "+OLD/db/datafile/users.258.699468081" TO COPY;

and we need "RECOVER"...

RMAN> RECOVER DATAFILE 4;

Bring database file to online:

RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

4 5 USERS *** +NEW/db/datafile/users.257.700906939

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

------------------------------------------------------------------ --------

+NEW/db/datafile/users.257.700906939 4

After Make sure... DELETE old datafilecopy(original):

RMAN> DELETE DATAFILECOPY "+OLD/db/datafile/users.258.699468081";

...

How to Setup 10gRAC with Solaris (IPMP)

On Solaris, IP network multipathing (IPMP) provides physical interface failure detection and transparent network access failover for a system with multiple interfaces on the same IP link.

On 10gRAC, It's a good question, How to Setup 10gRAC with Solaris (IPMP) ?

Setup Cluster Interconnect (metalink: 368464.1):

rac01:

- Physical IP : 10.10.10.1

- Test IP for ce2 : 10.10.10.11

- Test IP for ce3 : 10.10.10.12

rac02:

- Physical IP : 10.10.10.2

- Test IP for ce2 : 10.10.10.21

- Test IP for ce3 : 10.10.10.22

Check & configure...

oifcfg getif | grep -i cluster_interconnect

If already configuration... with "cluster_interconnect", then delete with 'oifcfg delif'

oifcfg delif [-node <nodename> | -global] [ <if_name> [/ <subnet> ]]

$ oifcfg getif | grep -i cluster_interconnect
ce2 10.10.10.0 global cluster_interconnect
ce3 10.10.10.0 global cluster_interconnect

$ oifcfg delif -global ce2

$ oifcfg delif -global ce3

set the CLUSTER_INTERCONNECTS parameter in the spfile/init.ora (RDBMS/ASM) to the Physical IP (IPMP)... and check on GV$CLUSTER_INTERCONNECTS as well.

rac database:

rac1.CLUSTER_INTERCONNECTS=10.10.10.1

rac2.CLUSTER_INTERCONNECTS=10.10.10.2

SQL> ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.1' scope=spfile sid='rac1';

SQL>ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.2' scope=spfile sid='rac2';

on ASM:

+ASM1.CLUSTER_INTERCONNECTS=10.10.10.1

+ASM2.CLUSTER_INTERCONNECTS=10.10.10.2

.

.

virtual IP (metalink:283107.1):

rac01:

- Physical IP : 192.168.10.10

- Test IP for ce0 : 192.168.10.11

- Test IP for ce1 : 192.168.10.12

- Oracle VIP : 192.168.10.1

rac02:

- Physical IP : 192.168.10.20

- Test IP for ce0 : 192.168.10.21

- Test IP for ce1 : 192.168.10.22

- Oracle VIP : 192.168.10.2

=> New 10gRAC installation:

In vipca (VIP Configuration Assistant, 1 of 2), select all NIC's within the same IPMP group where the VIP should run...

=> Existing 10gRAC installation, use "srvctl" to modify:

http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/srvctladmin.htm#CHDBJBIG

# srvctl config nodeapps -n rac01 -a

VIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0

# srvctl stop nodeapps -n rac01

# srvctl modify nodeapps -n rac01 -A 192.168.10.1/255.255.255.0/ce0\|ce1

# srvctl config nodeapps -n rac01 -a

VIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0:ce1

# srvctl start nodeapps -n rac01

# srvctl config nodeapps -n rac02 -a

VIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0

# srvctl stop nodeapps -n rac02

# srvctl modify nodeapps -n rac02 -A 192.168.10.2/255.255.255.0/ce0\|ce1

# srvctl config nodeapps -n rac02 -a

VIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0:ce1

# srvctl start nodeapps -n rac02

After modifed... use "srvctl config nodeapps -n nodename -a" to check.

Oracle SQL Developer 2.1 Book

Oracle SQL Developer is a graphical tool for database development and free. We can browse database objects, run SQL Statements, SQL scripts and edit/debug PL/SQL Statements.

Oracle SQL Developer 2.1 Early Adopter now available (min JDK 1.6 update11) - 24th September '09

We can check Oracle SQL Developer 2.1: New Features and Feature List.

I mention a book titled "Oracle SQL Developer 2.1" written by Sue Harper (Sue's Blog). Sue wrote an excellent Oracle SQL Developer's guide. "Oracle SQL Developer 2.1" covers SQL Developer fundamentals as well more intermediate and advanced topics, this book uses in-depth explanation and detailed examples to help you get the most out of Oracle SQL Developer.

What we will learn from this book:

- Build complex queries based on a number of tables using visual Query Builder

- Assess the health of your database, data structure of your application, and data in that application with built-in as well as user-defined reports

- Create, compile, and debug PL/SQL code and explore available features to facilitate writing PL/SQL code

- Integrate your SQL Developer with open source version control systems CVS and Subversion, which allow checking out of files from a repository, editing, and checking them back in

- Enter and execute your SQL, PL/SQL, and SQL*Plus statements with the SQL Worksheet interface

- Produce easily replicable scripts that copy and move data from one database instance to another, or from one schema to another

- Create advanced database connections using a variety of connection and authentication types available for Oracle as well as non-Oracle databases

- Create, review, and update database schema designs with SQL Developer Data Modeler

- Augment your environment with features that are specific to your needs by extending your SQL Developer with XML structured user-defined extensions

- Monitor and manage your Application Express applications by integrating with SQL Developer

- Set up an easy and quick migration environment for your database schema by using the migration repository

- Browse and review non-Oracle databases, before using the migration environment to migrate and consolidate databases on the Oracle platform

After I reviewed this ebook. This book is helpful to start Oracle SQL Developer 2.1 step by step.
We can use this book to be reference/manual when we use Oracle SQL Developer.

Reference:
- SQL Developer on OTN

- Download
- Installation Guide
- User's Guide

- Exchange

- SQL Developer Forum

- Sue's Blog

How to create Oracle Service on 11gR2

On 11gR2 "dbca" can not manage oracle services after created database ;)

we can use the DBMS_SERVICE procedures and SRVCTL to create/manage Oracle services. Anyway Oracle recommends using srvctl to manage services, if we use Oracle Clusterware and Oracle Restart (DBMS_SERVICE procedures do not update the CRS attributes).

Example (create service by using srvctl):

$ srvctl add service -d orcl -s service1 -r orcl1

$ lsnrctl services | grep service1
--- Not Found ---

$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc OFFLINE OFFLINE

$ srvctl start service -d orcl -s service1

$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc ONLINE ONLINE on rhel5-test

$ lsnrctl services | grep service1
Service "service1" has 1 instance(s).

Example (create service by using DBMS_SERVICE):

SQL> exec DBMS_SERVICE.CREATE_SERVICE('service2','service2');

PL/SQL procedure successfully completed.

$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----

$ lsnrctl services | grep service2
--- Not Found ---

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1

SQL> alter system set service_names='service1,service2';

System altered.

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1,service2

$ lsnrctl services | grep service2
Service "service2" has 1 instance(s).

$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----

"DBMS_SERVICE procedures do not update the CRS attributes"... So,we don't see ;)

Reference:

--- begin crsstat begin ---

#!/bin/bash
if [ $# -le 1 ]
then
GREP_KEY=$1
else
echo "Please Check arguments."
echo
echo "./crsstat [Word]"
echo
echo " [Word] use to find word in result "
echo " ./crsstat listen"
exit 0
fi

if [ -z $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check ORA_CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi
fi

AWK=/usr/bin/awk

if [ ! -x $AWK ]
then
AWK=/bin/awk
fi

$AWK \
'BEGIN {printf "%-45s %-10s %-12s\n", "HA Resource", "Target", "State (Host)"; printf "%-45s %-10s %-12s\n", "-----------", "------", "-----";}'
$ORA_CRS_HOME/bin/crs_stat | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$GREP_KEY'/ {appname = $2; state=1;}
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-12s\n", appname, apptarget, appstate; state=0;}'

--- end crsstat ---