Oracle Technologies Blog

By ASKM

Archive for the ‘ORACLE Tech’ Category

COPY Schema into Same Database with impdp

Posted by Srikrishna Murthy Annam on June 7, 2012

In this article I am going to explain the procedure to copy a schema to a different schema in the same database without exporting the source schema. This procedure works fine if you want to copy the schema to a different database.

1. Create a directory pointing to any physical directory on the server
2. Create a loopback database link. It means, you have to create a database link pointing to the same database.
   NOTE : If your requirement is to copy schema to a different schema, create a database link pointing to target database.
3. Use the impdp to copy schema to another schema.

Assume that the database name is DB-A. I am trying to copy a schema SCOTT to a different schema SCOTT_NEW in the same database.

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;

SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

If you are trying to copy schema to a different database , say DB-B , create database link as follows.

SQL> create a database link SCOTT_DB_LINK connect to system identified by xxxxx using ‘DB-B’;

(NOTE : We should be able to resolve the alias DB-A to database A and DB-B to database B.)

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

Verify the new schema with the source schema with following commands.

SQL> select object_type,count(1) from dba_objects where owner=’SCOTT’ and status=’INVALID’ group by object_type;
SQL> select object_type,count(1) from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’ group by object_type;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT’ and status=’INVALID’;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’;

Observations:

We are eliminating the creation of dump file, by using database link with the parameter network_link

Hope it helps

SRI

Advertisements

Posted in Database Articles, ORACLE Tech | Tagged: , , , , , , | 2 Comments »

relocate spfile from ASM to file system

Posted by Srikrishna Murthy Annam on February 3, 2011

In the present article i am going to show you how to move the spfile located on ASM diskgroup to the file system. This may not be the practical requirement in the RAC as we need common place for the spfile to store. If we move spfile to local file system in RAC, then it is very hard to keep the spfiles on all the nodes in sync. But it will be useful if you are trying to change any non-dynamic parameter in the spfile.

Thats fine, how do we manage spfile, but lets see  the process how do we move the spfile located in ASM to the file system.

SQL> select name,user from v$database; 

NAME                                               USER
————————————————– ——————————
TESTDB                                             SYS

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/testdb/spfiletestdb.ora
SQL>

[oracle@dhcppc1 ~]$ asmcmd
ASMCMD> cd +DATA/testdb/
ASMCMD> pwd
+DATA/testdb
ASMCMD> ls -lt spfile*
Type           Redund  Striped  Time             Sys  Name
N    spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD>

[oracle@dhcppc1 datafiles]$ cd $ORACLE_HOME/dbs
[oracle@dhcppc1 dbs]$ pwd
/u01/app/11.2.0/db/dbs
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r—– 1 oracle oinstall   39 Dec 24 16:45 inittestdb.ora
[oracle@dhcppc1 dbs]$ mv inittestdb.ora inittestdb.ora_bak
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r—– 1 oracle oinstall   39 Dec 24 16:45 inittestdb.ora_bak
[oracle@dhcppc1 dbs]$

SQL> create pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’ from spfile;

File created.

SQL> !ls -lrt /u01/app/11.2.0/db/dbs/inittestdb.ora
-rw-r–r– 1 oracle dba 862 Dec 31 16:42 /u01/app/11.2.0/db/dbs/inittestdb.ora

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

ASMCMD> ls -lt spfile*
Type           Redund  Striped  Time             Sys  Name
N    spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD> rm spfiletestdb.ora
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> ls -lt spfile*
ASMCMD-08002: entry ‘spfile*’ does not exist in directory ‘+DATA/testdb/’
ASMCMD>

SQL> create spfile=’/u01/app/11.2.0/db/dbs/spfiletestdb.ora’ from pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.
Database opened.
SQL> select name,user from v$database;

NAME                                               USER
————————————————– ——————————
TESTDB                                             SYS

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/11.2.0/db/dbs/spfiletestdb.ora
SQL>

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , | 2 Comments »

Troubleshooting ASM disk drop

Posted by Srikrishna Murthy Annam on February 2, 2011

The present article describes how to troubleshoot  if you are not able delete an ASM disk from  the ASM storage. The troubleshooting technique may vary from person to person and the following article describes the technique  i used to drop an ASM disk. There may be so many other possible solutions to the issue, but the one presented here worked for me.

The present issue is that , i have to delete an ASM disk from the ASM storage. The disk is already marked as ASM disk and when i try to unmark it for ASM disk, it throws an error. So i cant delete the OS disk without deleting it from the ASM disks.

Present issue occured in 11gR2 RAC on linux and we are using multi path ASM disks for the storage.

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED] 

 

So i verified the log file  /var/log/oracleasm and the content of the log file shows me the following error

Unable to clear disk “DATAVOL1”
Clearing disk header: oracleasm-write-label: Unable to open device “/dev/oracleasm/disks/DATAVOL1”: Device or resource busy
failed
Unable to clear disk “DATAVOL1”

I tried creating a diskgroup with this ASM disk and droped it and then tried to delete the ASM disk, but no use.

SQL> create diskgroup test external redundancy disk ‘/dev/oracleasm/disks/DATAVOL1’; 

Diskgroup created.

SQL> drop diskgroup test;
Diskgroup dropped.
SQL>

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED]

I verified with the following command if the disk is still marked as ASM disk or not. It is still marked as ASM disk.

[root@rac01 disks]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b” scsi_id=”;(echo $a|tr -d [:digit:]|tr -d [:]|cut -d”/” -f3|xargs -i scsi_id -g -s /block/{})done | grep -i DATAVOL1;
/dev/sdc:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdf:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdi:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdl:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdo:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdu:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdx:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
[root@rac01 disks]# 

[root@rac01 disks]# multipath -ll

..

..

vpath0 (360050768019600fba80000000000036b) dm-11 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 2:0:7:0 sdo  8:224  [active][ready]
\_ 1:0:7:0 sdp  8:240  [active][ready]
\_ 2:0:6:0 sdu  65:64  [active][ready]
\_ 1:0:6:0 sdx  65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc  8:32   [active][ready]
\_ 1:0:5:0 sdf  8:80   [active][ready]
\_ 2:0:4:0 sdi  8:128  [active][ready]
\_ 2:0:5:0 sdl  8:176  [active][ready]
[root@rac01 disks]#

Then i tried clearing the disk with the OS command dd.

[root@rac01 disks]# dd if=/dev/zero of=/dev/dm-11 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.05526 seconds, 1.9 MB/s
[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [  OK  ]
[root@rac01 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac01 disks]#

Now i am able to successfully delete the disk from ASM disk list.

On all other nodes in the RAC , do the following to sync

[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
DATAVOL1
FRADISK1
FRAVOL1
[root@rac02 disks]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac02 disks]#

Content from the log file /var/log/oracleasm now shows …

Disk “DATAVOL1” defines an unmarked device
Dropping disk: done
Reloading disk partitions: done
Cleaning any stale ASM disks…
Validating disk “CRSVOL1”
Validating disk “DATA1”
Validating disk “FRADISK1”
Validating disk “FRAVOL1” 

 

Hope it helps

SRI

Posted in 11gR2 RAC, ASM, Database Articles, Troubleshooting | Tagged: , , , , , , , , | 3 Comments »

Moving/Renaming database files from ASM to file system

Posted by Srikrishna Murthy Annam on January 26, 2011

In the present article we will move/rename the datafiles which reside on ASM storage to normal file system. There are different ways to achieve this. We can use ASMCMD utility or RMAN  to move the datafiles from ASM to file system and then use the database rename command to update the moved location in the database.

With ASMCMD Utility :

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′; 

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739201677 AVAILABLE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739201677             AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

SQL> alter database rename file ‘+DG1/testdb/datafile/TS1.256.739201677’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Using RMAN with DB downtime.

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m; 

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739203881 AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.
SQL>

[oracle@dhcppc1 ~]$ asmcmd ls -lt +DG1/testdb/datafile/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739203881
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:29:11 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115, not open)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’
2> ;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739203881
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T142940 RECID=6 STAMP=739204183
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file ‘+DG1/testdb/datafile/ts1.256.739203881’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Using RMAN without DB downtime :

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m; 

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739204397 AVAILABLE

SQL> alter tablespace ts1 offline;

Tablespace altered.

SQL>

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file ‘+DG1/testdb/datafile/ts1.256.739204397’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter tablespace ts1 online;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , , , | 2 Comments »

Copying files from ASM to file system

Posted by Srikrishna Murthy Annam on January 9, 2011

In the present article i am going to describe the various methods that we have to copy(Not moving,just copying) a datafile from ASM storage to file system storage.

1) Using ASMCMD Utility ( Latest method )
2) Using RMAN ( alternative method )
3) Using DBMS package ( old method )

In each method, we will copy the file from ASM to file system.

Method 1 : Using ASMCMD Utility

In this method, we are copying the datafile “TS1.256.739191187” in diskgroup DG1 from ASM to “ts2.dbf” on to the file system. 

[oracle@dhcppc1 datafiles]$ pwd
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 datafiles]$

[oracle@dhcppc1 ~]$ asmcmd
ASMCMD> ls -lt
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      DG1/
MOUNTED  EXTERN  N      FRA/
MOUNTED  EXTERN  N      OCR/
ASMCMD> cd dg1
ASMCMD> cd testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 13:00:00  Y    TS1.256.739191187
ASMCMD> cp TS1.256.739191187 /u01/datafiles/ts2.dbf
copying +dg1/testdb/datafile/TS1.256.739191187 -> /u01/datafiles/ts2.dbf
ASMCMD>

[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle oinstall 314580992 Dec 31 13:17 ts2.dbf
[oracle@dhcppc1 datafiles]$

Method 2 : Using RMAN

[oracle@dhcppc1 datafiles]$ pwd
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 datafiles]$ 

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m;

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739204397             AVAILABLE

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle dba      314580992 Dec 31 14:40 ts1.dbf
[oracle@dhcppc1 datafiles]$

Method 3 : Using DBMS package

SQL> select file_name from dba_data_files; 

FILE_NAME
——————————————————————————–
+DATA/testdb/datafile/users.259.738606665
+DATA/testdb/datafile/undotbs1.258.738606665
+DATA/testdb/datafile/sysaux.257.738606663
+DATA/testdb/datafile/system.256.738606661
+DATA/testdb/datafile/example.269.738607053
+DATA/testdb/datafile/test.271.738755277
+TESTDG/testdb/datafile/test1.257.738755445
+DG1/testdb/datafile/ts1.256.739191187

8 rows selected.

SQL> desc dbms_file_transfer
PROCEDURE COPY_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
PROCEDURE GET_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
SOURCE_DATABASE                VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
PROCEDURE PUT_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
DESTINATION_DATABASE           VARCHAR2                IN

SQL> create directory TEST_DIR as ‘+DG1/testdb/datafile/’;

Directory created.

SQL> create directory TARGET_DIR as ‘/u01/datafiles/’;

Directory created.

SQL> !ls -lrt /u01/datafiles/
total 0

SQL> BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
‘TEST_DIR’, source_file_name => ‘ts1.256.739191187’,
destination_directory_object => ‘TARGET_DIR’,
destination_file_name => ‘ts1.dbf’);
END;
/   2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> !ls -lrt /u01/datafiles/
total 307512
-rw-r—– 1 oracle dba 314580992 Dec 31 13:12 ts1.dbf

SQL> select file_name from dba_data_files where tablespace_name=’TS1′;

FILE_NAME
——————————————————————————–
+DG1/testdb/datafile/ts1.256.739191187

SQL>

Hope it helps

SRI

Posted in ASM, Database Articles, RMAN | Tagged: , , , , | 7 Comments »

Drop a disk in an ASM Disk Group

Posted by Srikrishna Murthy Annam on January 8, 2011

The big challenge in ASM is to reconfigure the storage online. This is due to the feature that ASM distributes data across all disks in a disk group evenly.
So when you are planning to drop any disk from the diskgroup, we need to see if the data in the disk we are removing can be readjusted in the other available disks in the same diskgroup.
When you drop a disk from diskgroup ASM will seamlessly migrate the data to the existing disks in the disk group.

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat; 

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        898
4 DISK_F                1019        901
4 DISK_G                1019        901

6 rows selected.

From the above output, diskgroup 4 has three disks each with the same size. If you see the FREE_MB, it is almost same in all the 3 disks.
Now we will drop DISK_F and DISK_G from diskgroup 4 and will see how the data is rebalanced in the last remaining disk DISK_E.

SQL> alter diskgroup DG1 drop disk DISK_G; 

Diskgroup altered.

SQL> alter diskgroup DG1 drop disk DISK_F;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
———— —– ———- ———- ———- ———- ———- ———- ———– ——————————————–
4 REBAL RUN                 1          1        145        231        660           0

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        746
4 DISK_F                1019        977
4 DISK_G                1019        977

6 rows selected.

Now we can see from the above result that the FREE_MB for disks DISK_F and DISK_G is increasing as data is moving to DISK_E and FREE_MB is decreasing in DISK_E as data is coming from other two disks to this disk.

When the total rebalncing is completed,the disks will be removed from the diskgroup 4.

SQL> select * from v$asm_operation; 

no rows selected

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        666

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , , | Leave a Comment »

ORA-01157: cannot identify/lock data file

Posted by Srikrishna Murthy Annam on January 7, 2011

In the present article, we will see how to start the database working with ASM if someone accidentally deleted moved the datafile which resides on ASM storage to a different location.

The present case is similar to the case we do on normal storage.
If we delete moved database file to a different location and if it is not updated in database, we will not be able to start the database.
So we need to update the database with this information to start it successfully.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD> 

Remove  the datafile from ASM using rm command.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

CRS-2674: Start of ‘ora.testdb.db’ on ‘dhcppc1’ failed
[oracle@dhcppc1 ~]$

SQL> startup
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             478151068 bytes
Database Buffers          239075328 bytes
Redo Buffers                5419008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

SQL>

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database rename file ‘+DG1/testdb/datafile/TS1.256.739201677’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf                             AVAILABLE

SQL>

 

Hope it helps

SRI

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

Posted in ASM, Database Articles, Troubleshooting | Tagged: , , , | 5 Comments »

Installing 11gR2 Single Instance Database With Non-ASM Storage

Posted by Srikrishna Murthy Annam on December 17, 2010

The present demo shows you how to install 11gR2 single instance database with Non-ASM storage.
Following sequence is followed ..

  1. Install Linux 5.x on VMware server linux machine
  2. Prepare Linux Machine for 11gR2 software installation
  3. Install 11gR2 software
  4. Create 11gR2 database with Non-ASM storage

1. Install Linux 5.x on VMware server linux machine

Follow the demo  VMware Server – Linux installation and complete the linux installation.

2. Prepare Linux Machine for 11gR2 software installation

Follow the article  Prepare Linux virtual machine for 11gR2 installation on Linux 5.x and prepare Linux OS for 11gR2 Database Installation.

3. Install 11gR2 software

Follow the following demo and install 11gR2 software.

4. Create 11gR2 database with Non-ASM storage

Follow the following demo to create 11gR2 database with Non-ASM storage.

Now you installed 11gR2 database successfully.

Hope it helps

SRI


Add to FaceBookAdd to TwitterAdd to Google BookmarkAdd to MySpaceAdd to DiggAdd to DeliciousAdd to NewsvineAdd to RedditAdd to StumbleUponAdd to Technorati

<iframe src=”http://www.facebook.com/plugins/like.php?href=http%3A%2F%2Flearnwithme11g.wordpress.com&amp;layout=standard&amp;show_faces=true&amp;width=450&amp;action=like&amp;font=arial&amp;colorscheme=light&amp;height=80&#8243; scrolling=”no” frameborder=”0″ style=”border:none; overflow:hidden; width:450px; height:80px;” allowTransparency=”true”></iframe>

Posted in Database Articles, VMWARE | Tagged: , , , , | 2 Comments »

Prepare Linux virtual machine for 11gR2 installation on Linux 5.x

Posted by Srikrishna Murthy Annam on September 21, 2010

There are some OS preparation steps to be performed before installing database on linux.

Pls refer the Master Note ID : 851598.1 and complete all the steps specified in the note as per your OS.

In the present article, i am using the OEL 5.4 (32-bit) as OS and following the note id : 880936.1 to complete all the preparatory steps for 11gR2 database creation.

Create the required user and groups for the installation

  1. groupadd -g 1000 oinstall
  2. groupadd -g 1200 dba
  3. useradd -u 1100 -g oinstall -G dba oracle
  4. mkdir -p /u01/app/oracle
  5. chown -R oracle:oinstall /u01/app
  6. chmod -R 775 /u01/app

Required RPMs installation :

Make sure that the following RPMs are installed,

  1. binutils-2.17.50.0.6-2.el5
  2. compat-libstdc++-33-3.2.3-61
  3. elfutils-libelf-0.125-3.el5
  4. glibc-2.5-24
  5. glibc-common-2.5-12
  6. ksh-20060214-1.7
  7. libaio-0.3.106
  8. libgcc-4.1.2-42.el5
  9. libstdc++-4.1.2-42
  10. make-3.81-1.1
  1. elfutils-libelf-devel-0.125-3.el5.i386.rpm
  2. a.requires elfutils-libelf-devel-static-0.125-3.el5.i386.rpm as a prerequisite.
  3. b.elfutils-libelf-devel and elfutils-libelf-devel-static each depend upon the other. Therefore, they must be installed together, in one (1″rpm -ivh” command as follows:
  4. rpm -ivh elfutils-libelf-devel-0.125-3.el5.i386.rpm elfutils-libelf-devel-static-0.125-3.el5.i386.rpm
  5. kernel-headers-2.6.18-92.el5.i386.rpm
  6. glibc-headers-2.5-24.i386.rpm
  7. glibc-devel-2.5-24.i386.rpm
  8. libstdc++-devel-4.1.2-42.el5.i386.rpm
  9. libgomp-4.1.2-42.el5.i386.rpm
  10. gcc-4.1.2-42.el5.i386.rpm
  11. gcc-c++-4.1.2-42.el5.i386.rpm
  12. libaio-devel-0.3.106-3.2.i386.rpm
  13. sysstat-7.0.2-1.el5.i386.rpm
  14. unixODBC-2.2.11-7.1.i386.rpm
  15. unixODBC-devel-2.2.11-7.1.i386.rpm

NOTE :
rpm -qa | grep -i <rpm name>  ==> to check if the rpm is installed or not
rpm -ivh <rpm name>    ==> to install rpm
rpm -e <rpm name>  ==> to remove package from the OS

In my OS following four RPMs are not installed.

libaio-devel-0.3.106-3.2.i386.rpm
sysstat-7.0.2-1.el5.i386.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-devel-2.2.11-7.1.i386.rpm

Session Log :

[root@localhost ~]# rpm -qa | grep -i  binutils-
binutils-2.17.50.0.6-12.el5
[root@localhost ~]# rpm -qa | grep -i  compat-libstdc
compat-libstdc++-296-2.96-138
compat-libstdc++-33-3.2.3-61
[root@localhost ~]# rpm -qa | grep -i  elfutils-libelf
elfutils-libelf-0.137-3.el5
elfutils-libelf-devel-static-0.137-3.el5
elfutils-libelf-devel-0.137-3.el5
[root@localhost ~]# rpm -qa | grep -i  glibc-
glibc-headers-2.5-42
glibc-common-2.5-42
glibc-devel-2.5-42
glibc-2.5-42
[root@localhost ~]# rpm -qa | grep -i  glibc-common-
glibc-common-2.5-42
[root@localhost ~]# rpm -qa | grep -i  ksh-
ksh-20080202-14.el5
[root@localhost ~]# rpm -qa | grep -i  libaio-
libaio-0.3.106-3.2
[root@localhost ~]# rpm -qa | grep -i  libgcc-
libgcc-4.1.2-46.el5
compat-libgcc-296-2.96-138
[root@localhost ~]# rpm -qa | grep -i  libstdc++
compat-libstdc++-296-2.96-138
libstdc++-4.1.2-46.el5
libstdc++-devel-4.1.2-46.el5
compat-libstdc++-33-3.2.3-61
[root@localhost ~]# rpm -qa | grep -i  make-
imake-1.0.2-3
make-3.81-3.el5
automake-1.9.6-2.1
[root@localhost ~]# rpm -qa | grep -i  elfutils-libelf-devel
elfutils-libelf-devel-static-0.137-3.el5
elfutils-libelf-devel-0.137-3.el5
[root@localhost ~]# rpm -qa | grep -i  kernel-headers-
kernel-headers-2.6.18-164.el5
[root@localhost ~]# rpm -qa | grep -i  glibc-headers-
glibc-headers-2.5-42
[root@localhost ~]# rpm -qa | grep -i  glibc-devel-
glibc-devel-2.5-42
[root@localhost ~]# rpm -qa | grep -i  libstdc++-devel-
libstdc++-devel-4.1.2-46.el5
[root@localhost ~]# rpm -qa | grep -i  libgomp-
libgomp-4.4.0-6.el5
[root@localhost ~]# rpm -qa | grep -i  gcc-
libgcc-4.1.2-46.el5
gcc-c++-4.1.2-46.el5
compat-libgcc-296-2.96-138
gcc-4.1.2-46.el5
gcc-gfortran-4.1.2-46.el5
[root@localhost ~]# rpm -qa | grep -i  libaio-devel
[root@localhost ~]# rpm -qa | grep -i  sysstat-
[root@localhost ~]# rpm -qa | grep -i  unixODBC
[root@localhost ~]#

[root@localhost Server]# rpm -ivh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@localhost Server]#

[root@localhost Server]# rpm -ivh libaio-devel-0.3.106-3.2.i386.rpm
warning: libaio-devel-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@localhost Server]# rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]
[root@localhost Server]# rpm -ivh sysstat-7.0.2-3.el5.i386.rpm
warning: sysstat-7.0.2-3.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:sysstat                ########################################### [100%]
[root@localhost Server]#

Following files should be modified as per the following session log:
/etc/security/limits.conf
/etc/pam.d/login
/etc/profile
/etc/sysctl.conf  ==> Can be modified while installing using OUI.

Session Log

[root@localhost ~]# diff /etc/security/limits.conf  /etc/security/limits.conf_bak
49,52c49
< oracle soft nproc 2047
< oracle hard nproc 16384
< oracle soft nofile 1024
< oracle hard nofile 65536

>
[root@localhost ~]#

[root@localhost ~]# diff /etc/pam.d/login /etc/pam.d/login_bak
15d14
< session required pam_limits.so
[root@localhost ~]#

[root@localhost ~]# diff /etc/profile /etc/profile_bak
57,65d56
< if [ $USER = “oracle” ]; then
<     if [ $SHELL = “/bin/ksh” ]; then
<        ulimit -u 16384
<        ulimit -n 65536
<     else
<        ulimit -u 16384 -n 65536
<     fi
< fi
<
[root@localhost ~]#

Make sure the following versions are verified.

[root@localhost ~]# gcc –version
gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[root@localhost ~]# g++ –version
g++ (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[root@localhost ~]#

Finally add the following entries in the oracle user profile ( .bash_profile )

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/11.2.0/db_1
ORACLE_SID=TESTDB
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export PATH

Now the OS is ready for installing 11gR2 database.

Hope it helps
SRI

Posted in Database Articles, Linux | 2 Comments »

SCAN in 11gR2 Grid InfraStructure

Posted by Srikrishna Murthy Annam on September 2, 2010

11gR2 grid infrastructure uses SCAN, which is a new concept we use for all client connections. SCAN is single client access name and it is a new feature in 11g Release 2 that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Prior to 11gR2 , the tnsnames.ora entries are

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

In 11gR2 , the tnsnames.ora entries look like ….

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

The only difference is that , we specify the scan name instead of all VIPs in the tnsnames.ora file.

So what is the benifit with it?

Suppose if you are adding any node to the cluster, you need to specify corresponding VIP entry for that node in the tnsnames.ora prior to 11gR2. But with SCAN name, we dont need to do any modifications to the tnsnames.ora file. SCAN name will resolve and load balance the client connections to all the nodes in the RAC.

SCAN configuration in the cluster :

The SCAN is configured during the installation of the grid infrastructure that is distributed with Oracle Database 11g Release2. So in order to configure SCAN, you need to create a single name that resolves to 3 IP addresses using a round robin algorithm. The IP addresses must be on the same subnet as your public network in the cluster.
rac-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

SCAN Details

[grid@rac01 ~]$ host rac-scan
rac-scan.example.com has address 133.22.67.192
rac-scan.example.com has address 133.22.67.193
rac-scan.example.com has address 133.22.67.194
[grid@rac01 ~]$ nslookup rac-scan
Server:         133.22.5.53
Address:        133.22.5.53#53

Name:   rac-scan.example.com
Address: 133.22.67.194
Name:   rac-scan.example.com
Address: 133.22.67.193
Name:   rac-scan.example.com
Address: 133.22.67.192

[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.193) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=1 ttl=64 time=0.187 ms
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=2 ttl=64 time=0.144 ms
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=3 ttl=64 time=0.152 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.144/0.161/0.187/0.018 ms
[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.192) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=1 ttl=64 time=0.042 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=2 ttl=64 time=0.046 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=3 ttl=64 time=0.051 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.042/0.046/0.051/0.006 ms
[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.194) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.194): icmp_seq=2 ttl=64 time=0.144 ms
64 bytes from rac-scan.example.com (133.22.67.194): icmp_seq=3 ttl=64 time=0.164 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 2 received, 33% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.144/0.154/0.164/0.010 ms
[grid@rac01 ~]$

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192
SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194
SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.193
[root@rac01 network-scripts]#

[grid@rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac02
[grid@rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac02
[grid@rac01 ~]$

[grid@rac01 ~]$ ps -ef | grep -i tns
grid      9052     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      9098     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
grid     21388 20424  0 05:06 pts/1    00:00:00 grep -i tns
[grid@rac01 ~]$

[grid@rac02 ~]$ ps -ef | grep -i tns
grid      1323  1292  0 05:07 pts/0    00:00:00 grep -i tns
grid      9110     1  0 Aug09 ?        00:00:02 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid     17278     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid     17285     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[grid@rac02 ~]$

[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
———–                                        ——     —–
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on rac02
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on rac02

ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
ora.rac01.ASM1.asm                                  ONLINE     ONLINE on rac01
ora.rac01.LISTENER_ORRCDBTSL01.lsnr                 ONLINE     ONLINE on rac01
ora.rac01.gsd                                      OFFLINE    OFFLINE
ora.rac01.ons                                       ONLINE     ONLINE on rac01
ora.rac01.vip                                      ONLINE     ONLINE on rac01
ora.rac02.ASM2.asm                                 ONLINE     ONLINE on rac02
ora.rac02.LISTENER_ORRCDBTSL02.lsnr                ONLINE     ONLINE on rac02
ora.rac02.gsd                                       OFFLINE    OFFLINE
ora.rac02.ons                                       ONLINE     ONLINE on rac02
ora.rac02.vip                                       ONLINE     ONLINE on rac02
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.scan2.vip                                      ONLINE     ONLINE on rac02
ora.scan3.vip                                      ONLINE     ONLINE on rac02

ora.testdb.db                                      ONLINE     ONLINE on rac01
[grid@rac02 grid]$

Hope it helps
SRI

Posted in 11g New Features, 11gR2 RAC | Tagged: , , , , , | Leave a Comment »

Tracing DBCA

Posted by Srikrishna Murthy Annam on August 25, 2010

The present article discusses the tracing of DBCA. Sometimes you may be landing into issues when using the DBCA and you may not have any clue for the  error. Enabling the tracing for DBCA will give more detailed output showing some clue for the issue.

Till release 10g , we have to manually enable tracing …

Modify the jre command at the end of the  file : $ORACLE_HOME/bin/dbca  as …

$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

But from Release 10g onwards , this tracing is automatically enabled.

We can find the tracing file at

$ORACLE_HOME/cfgtoollogs/dbca/trace.log   ==> in 10g

$ORACLE_BASE/cfgtoollogs/dbca//trace.log  ==> in 11g

Hope it helps

–SRI

$ORACLE_HOME/bin/db

Posted in Database Articles | Tagged: , , | 1 Comment »

oracle exp/imp and expdp/impdp

Posted by Srikrishna Murthy Annam on August 24, 2010

If you dont know how to use the exact options of the exp/imp and expdp/impdp utilities for your requirement, here is a front end tool to use

http://www.alderprogs.com/

It is a licenced software but you can install the trial version to test.

Hope it helps

–SRI

Posted in export/import utils | Tagged: , , , , | Leave a Comment »

exporting objects to different schema with different tablespace

Posted by Srikrishna Murthy Annam on August 22, 2010

Assume that we have some objects in a schema TEST1 with default tablespace TS1. Suppose if there is an application running on this database which requires these objects to be in two different schemas.
Say the second schema is TEST2 with default tablespace TS2.
Now we have to move some objects from TEST1 schema to TEST2 schema.

Consider the following syntax ..

SQL>alter table TEST1.TABLE1 rename to TEST2.TABLE1; ==> Syntax error.
sql>alter table TEST1.TABLE1 move tablespace TS2; ==> This moves the object to tablespace TS2, but still in schema TEST1.

Does export and import work ?   Lets try ….

$exp system/manager file=TABLE1.dmp tables=TEST1.TABLE1 log=exp_TABLE1.log
$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

This should actually move the table TABLE1 from TEST1 to TEST2.
But if you observe the table TABLE1 after the import it is in schema TEST2 which is ok but tablespace is still TS1. WHY ?
The user TEST2 has default tablespace TS2, so when we import the table it should be created in TS2 tablespace. Why is it created in TS1?

Then what is the solution to move an object to TEST2 with tablespace TS2. !!!!!!!

Follow the following steps to move the objects to different schema with different tablespace.

SQL>alter user TEST2 quota 0 on TS1 quota unlimited on TS2;
SQL>revoke unlimited tablespace from TEST2;

$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

Then verify the object schema and tablespace. It should be TEST2 with TS2.

Hope it helps ….

— SRI


Posted in export/import utils | Tagged: , | 3 Comments »

Exporting/Importing table partitions

Posted by Srikrishna Murthy Annam on August 20, 2010

This article shows the table partition management and also how to export and import the table partitions.

SQL> CREATE TABLE “SH”.”PART_TABLE”
2     (       “PARAMETER” VARCHAR2(32) NOT NULL ENABLE,
3     “TIMESTAMP” NUMBER NOT NULL ENABLE,
4     “VALUESUM” NUMBER NOT NULL ENABLE,
5     “VALUECOUNT” NUMBER DEFAULT 1 NOT NULL ENABLE,
6      CONSTRAINT “PK_INDEX01” PRIMARY KEY (“PARAMETER”, “TIMESTAMP”) ENABLE,
7      CONSTRAINT “VALUECOUNT_POS” CHECK ( valuecount > 0 ) ENABLE
8     ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING
9    TABLESPACE “TS1”
10    STORAGE(
11    BUFFER_POOL DEFAULT)
12   PCTTHRESHOLD 50
13    PARTITION BY RANGE (“TIMESTAMP”)
14   (PARTITION “PART_1”  VALUES LESS THAN (1277596800001)
15    PCTFREE 10 INITRANS 2 MAXTRANS 255
16    STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18    TABLESPACE “TS1” );

Table created.

SQL> select segment_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select partition_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select table_name,tablespace_name from dba_tables where table_name=’PART_TABLE’;

TABLE_NAME                     TABLESPACE_NAME
—————————— ——————————
PART_TABLE

SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name=’PART_1′;

PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————————
PART_1

SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name=’TS1′;

INDEX_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT STATUS
—————————— —————————— —————— ——–
PK_INDEX01                     PART_1                               0 USABLE

SQL> select owner,table_name,index_name,status from dba_indexes where index_name=’PK_INDEX01′;

OWNER      TABLE_NAME                     INDEX_NAME              STATUS
———- —————————— ——————————
SH         PART_TABLE                     PK_INDEX01              N/A

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1′ group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             1

SQL> ALTER TABLE SH.PART_TABLE ADD
2    PARTITION “PART_2”  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1” ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1′ group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             2

Insert some  data

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported

About to export sSHcified tables via Conventional Path …
. . exporting table PART_TABLE
. . exporting partition              PART_2
…………………………………………………………………
…..
809310 rows exported
Export terminated successfully without warnings.

SQL> alter table SH.PART_TABLE drop partition PART_2;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

SQL>    alter table SH.PART_TABLE add
2    PARTITION “PART_2”  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1” ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SH, not by you

import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH’s objects into SH
. . importing partition “PART_TABLE”:”PART_2″
…………………………………………………………………
…..
809310 rows imported
Import terminated successfully without warnings.

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

Hope it helps …
–SRI

Posted in Database Articles, export/import utils | Tagged: , , , , , | Leave a Comment »

Issues and solutions for 10g RAC Database creation on 11gR2 grid infra

Posted by Srikrishna Murthy Annam on August 19, 2010

Issues and solutions for

10g RAC Database creation on 11gR2 grid infra:


Issue 1 :

Oracle Single instance creation screen appears instead of RAC database creation screen.

Solution : Note ID 1073926.1

Issue 2 :

From the above note 1073926.1, it is also required to apply the patch 8288940 to fix 11g ASM Incompatibility for 10g

Issue 3 :

While creating the database using dbca , getting the following error.
“Encountered file error when copying listeners from home=/o001/home/11.2.0/grid/”


Solution : Note ID : 762304.1

Issue 4 :

Invalid specification for system parameter “REMOTE_LISTENER” …..

Solution :

Modified the remote_listener init parameter to point to “<scan-name>:1521”

Issue 5:

Found the following messages in alert log file

ORA-15025: could not open disk ‘/dev/oracleasm/disks/DATAVOL1’

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

Additional information: 2

Fri Aug 20 09:41:54 2010

SUCCESS: diskgroup RACDB_DATA was mounted

SUCCESS: diskgroup RACDB_DATA was dismounted

Fri Aug 20 09:41:54 2010

Errors in file /o001/home/oracle/admin/rac10g/udump/rac10g1_ora_14539.trc:

ORA-00200: control file could not be created

ORA-00202: control file: ‘+RACDB_DATA/rac10g/control01.ctl’

ORA-15012: ASM file ‘rac10g/control01.ctl’ does not exist

ORA-17502: ksfdcre:5 Failed to create file +RACDB_DATA/rac10g/control01.ctl

ORA-15081: failed to submit an I/O operation to a disk

Work Around :

SQL> select name,state from v$asm_diskgroup;

NAME             STATE

—————————— ———–

CRS             MOUNTED

FRA             MOUNTED

RACDB_DATA MOUNTED

SQL> select name,path from v$asm_disk;

NAME PATH

——————————————————————————–

CRS_0000 /dev/oracleasm/disks/CRSVOL1

FRA_0000 /dev/oracleasm/disks/FRAVOL1

RACDB_DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL> !

[grid@rac1 disks]$ ls -lrt /dev/oracleasm/disks/DATAVOL1

brw-rw—- 1 grid asmadmin 8, 32 Aug 20 11:53 /dev/oracleasm/disks/DATAVOL1

[grid@rac1 disks]$ id oracle

uid=1659(oracle) gid=501(dba) groups=501(dba),552(admin2),503(oper),1000(oinstall),1201(asmdba)

[grid@rac1 disks]$ ls -lrt /dev/oracleasm/disks/*

brw-rw—- 1 grid asmadmin 8, 48 Aug 20 11:54 /dev/oracleasm/disks/FRAVOL1

brw-rw—- 1 grid asmadmin 8, 32 Aug 20 11:54 /dev/oracleasm/disks/DATAVOL1

brw-rw—- 1 grid asmadmin 8, 64 Aug 20 11:54 /dev/oracleasm/disks/CRSVOL1

[grid@rac1 disks]$ chmod 777 /dev/oracleasm/disks/*

[grid@rac1 disks]$ ls -lrt /dev/oracleasm/disks/*

brwxrwxrwx 1 grid asmadmin 8, 48 Aug 20 11:55 /dev/oracleasm/disks/FRAVOL1

brwxrwxrwx 1 grid asmadmin 8, 32 Aug 20 11:55 /dev/oracleasm/disks/DATAVOL1

brwxrwxrwx 1 grid asmadmin 8, 64 Aug 20 11:55 /dev/oracleasm/disks/CRSVOL1

[grid@rac1 disks]$

Issue 6 :

PRKP-1001 : Error starting the instance rac10g2 on node rac2

CRS-0215 Could not start resource ‘ora.rac10g.rac10g2.inst’

This error can be ignored. The instance is started automatically.

Posted in 11gR2 RAC, Database Articles | Tagged: | 1 Comment »