ASM Instance Issues

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

[oracle@Jaya ~]$ 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@jaya ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

[oracle@jaya ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin

[oracle@jaya ~]$ export ORACLE_SID=+ASM

[oracle@jaya ~]$ 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?

 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:\raju';

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

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

Duplicate database from ASM to non ASM

Step 1:

SQL> select name from v$database;

NAME

---------

RAJUDB

Calculate total space for all datafiles within database 

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",

(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,

(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;

DataFile Size Mb   Redo Log Size Mb    Control File Size Mb   Total Size Mb

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

1310                150                     18.59375           1478.59375

Calculate space for list of datafiles within primary database

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",

LOG.TOTAL/1048576 "Redo Log Size Mb",

CONTROL.TOTAL/1048576 "Control File Size Mb",

(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,

(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF,

(select sum(b.bytes) TOTAL from v$log b) LOG,

(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;

DataFile Size Mb Redo Log Size Mb  Control File Size Mb  Total Size Mb

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

1165              150                 18.59375            1333.59375

Step 2: backup Source database 

RMAN> run{

   allocate channel d1 type disk;

   backup format '/backups/df_t%t_s%s_p%p' database;

   sql 'alter system archive log current';

   backup format '/backups/al_t%t_s%s_p%p' archivelog all;

   release channel d1;

  }

released channel: ORA_DISK_1

allocated channel: d1

channel d1: SID=44 device type=DISK

Starting backup at 17-DEC-10

channel d1: starting full datafile backup set

channel d1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/rajudb/datafile/system.256.736179685

input datafile file number=00002 name=+DATA/rajudb/datafile/sysaux.257.736179685

input datafile file number=00005 name=+DATA/rajudb/datafile/example.269.736179971

input datafile file number=00003 name=+DATA/rajudb/datafile/undotbs1.258.736179685

input datafile file number=00004 name=+SPTEST/rajudb/datafile/users.256.737964169

channel d1: starting piece 1 at 17-DEC-10

channel d1: finished piece 1 at 17-DEC-10

piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 comment=NONE

channel d1: backup set complete, elapsed time: 00:02:27

channel d1: starting full datafile backup set

channel d1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel d1: starting piece 1 at 17-DEC-10

channel d1: finished piece 1 at 17-DEC-10

piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-DEC-10

sql statement: alter system archive log current

Starting backup at 17-DEC-10

current log archived

channel d1: starting archived log backup set

channel d1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=736268182

input archived log thread=1 sequence=4 RECID=2 STAMP=736345263

input archived log thread=1 sequence=5 RECID=3 STAMP=736438711

input archived log thread=1 sequence=6 RECID=4 STAMP=737551583

input archived log thread=1 sequence=7 RECID=5 STAMP=737880523

input archived log thread=1 sequence=8 RECID=6 STAMP=737897929

input archived log thread=1 sequence=9 RECID=7 STAMP=737962069

input archived log thread=1 sequence=10 RECID=8 STAMP=737969217

input archived log thread=1 sequence=11 RECID=9 STAMP=737969220

input archived log thread=1 sequence=12 RECID=10 STAMP=737969948

input archived log thread=1 sequence=13 RECID=11 STAMP=737969948

channel d1: starting piece 1 at 17-DEC-10

channel d1: finished piece 1 at 17-DEC-10

piece handle=/backups/al_t737969948_s8_p1 tag=TAG20101217T073908 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:15

Finished backup at 17-DEC-10

released channel: d1

RMAN>

Step 3: Create pfile & edit some required parameters

SQL> create pfile='/backups/initAUX.ora' from spfile;

File created.

Edit& Save

*.audit_file_dest =/u01/app/oracle/AUX/adump

*.core_dump_dest =/u01/app/oracle/AUX/cdump

*.db_name ="AUX"

*.instance_name =AUX

*.control_files =/u01/app/oracle/AUX/control01.ctl

*.db_file_name_convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX")

*.log_file_name_convert =("+DATA","/u01/app/oracle/AUX")

*.undo_management =AUTO

*.undo_retention =10800

*.undo_tablespace='UNDOTBS1'

*.db_block_size=8192

*.compatible='11.2.0.0.0'

Step 4: startup nomount for AUX database

[oracle@localhost ~]$ export ORACLE_SID=AUX

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount pfile='/backups/initAUX.ora';

ORACLE instance started.

Total System Global Area  146472960 bytes

Fixed Size                  1335080 bytes

Variable Size              92274904 bytes

Database Buffers           50331648 bytes

Redo Buffers                2531328 bytes

SQL>

Step 5:Create duplicate database from here

[oracle@localhost ~]$ export ORACLE_SID=AUX

[oracle@localhost ~]$ rman target sys/Admin123@rajudb auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010

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

connected to target database: RAJUDB (DBID=1655311948)

connected to auxiliary database: AUX (not mounted)

RMAN> run{

   allocate auxiliary channel C1 device type disk;

   duplicate target database to AUX;

   }

using target database control file instead of recovery catalog

allocated channel: C1

channel C1: SID=20 device type=DISK

Starting Duplicate Db at 17-DEC-10

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes

Variable Size                 92274904 bytes

Database Buffers              50331648 bytes

Redo Buffers                   2531328 bytes

allocated channel: C1

channel C1: SID=18 device type=DISK

contents of Memory Script:

{

sql clone "alter system set  db_name =

''RAJUDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set  db_unique_name =

''AUX'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''RAJUDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes

Variable Size                 92274904 bytes

Database Buffers              50331648 bytes

Redo Buffers                   2531328 bytes

allocated channel: C1

channel C1: SID=18 device type=DISK

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore

channel C1: restoring control file

channel C1: reading from backup piece /backups/df_t737969943_s7_p1

channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635

channel C1: restored backup piece 1

channel C1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/AUX/control01.ctl

Finished restore at 17-DEC-10

database mounted

contents of Memory Script:

{

set until scn  971599;

set newname for datafile  1 to

"/u01/app/oracle/AUX/rajudb/datafile/system.256.736179685";

set newname for datafile  2 to

"/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685";

set newname for datafile  3 to

"/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685";

set newname for datafile  4 to

"/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169";

set newname for datafile  5 to

"/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971";

restore

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore

channel C1: specifying datafile(s) to restore from backup set

channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/rajudb/datafile/system.256.736179685

channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685

channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685

channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/rajudb/datafile/users.256.737964169

channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/rajudb/datafile/example.269.736179971

channel C1: reading from backup piece /backups/df_t737969795_s6_p1

channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635

channel C1: restored backup piece 1

channel C1: restore complete, elapsed time: 00:01:27

Finished restore at 17-DEC-10

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/rajudb/datafile/system.256.736179685

datafile 2 switched to datafile copy

input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685

datafile 3 switched to datafile copy

input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685

datafile 4 switched to datafile copy

input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169

datafile 5 switched to datafile copy

input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971

contents of Memory Script:

{

set until scn  971599;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 17-DEC-10

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file +DATA/rajudb/archivelog/2010_12_17/thread_1_seq_12.279.737969947

archived log for thread 1 with sequence 13 is already on disk as file +DATA/rajudb/archivelog/2010_12_17/thread_1_seq_13.280.737969949

archived log file name=+DATA/rajudb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12

archived log file name=+DATA/rajudb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13

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

Finished recover at 17-DEC-10

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set  db_name =

''AUX'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset  db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes

Variable Size                 92274904 bytes

Database Buffers              50331648 bytes

Redo Buffers                   2531328 bytes

allocated channel: C1

channel C1: SID=18 device type=DISK

sql statement: alter system set  db_name =  ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes

Variable Size                 92274904 bytes

Database Buffers              50331648 bytes

Redo Buffers                   2531328 bytes

allocated channel: C1

channel C1: SID=18 device type=DISK

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG

MAXLOGFILES     16

MAXLOGMEMBERS      3

MAXDATAFILES      100

MAXINSTANCES     8

MAXLOGHISTORY      292

LOGFILE

GROUP  1 ( '/u01/app/oracle/AUX/rajudb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/rajudb/onlinelog/group_1.263.736179919' ) SIZE 50 M  REUSE,

GROUP  2 ( '/u01/app/oracle/AUX/rajudb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/rajudb/onlinelog/group_2.265.736179925' ) SIZE 50 M  REUSE,

GROUP  3 ( '/u01/app/oracle/AUX/rajudb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/rajudb/onlinelog/group_3.267.736179929' ) SIZE 50 M  REUSE

DATAFILE

'/u01/app/oracle/AUX/rajudb/datafile/system.256.736179685'

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

set newname for tempfile  1 to

"/u01/app/oracle/AUX/rajudb/tempfile/temp.268.736179955";

switch clone tempfile all;

catalog clone datafilecopy  "/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685",

"/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685",

"/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169",

"/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/AUX/rajudb/tempfile/temp.268.736179955 in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112

cataloged datafile copy

datafile copy file name=/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112

cataloged datafile copy

datafile copy file name=/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169 RECID=3 STAMP=737976112

cataloged datafile copy

datafile copy file name=/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971 RECID=4 STAMP=737976113

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Duplicate database was successfully created

Step 6: Check Duplicate Database

[oracle@localhost ~]$ export ORACLE_SID=AUX

[oracle@localhost ~]$ sqlplus

 SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:25:45 2010

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

 Enter user-name: / as sysdba

 Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

AUX

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/AUX/rajudb/datafile/system.256.736179685

/u01/app/oracle/AUX/rajudb/datafile/sysaux.257.736179685

/u01/app/oracle/AUX/rajudb/datafile/undotbs1.258.736179685

/u01/app/oracle/AUX/rajudb/datafile/users.256.737964169

/u01/app/oracle/AUX/rajudb/datafile/example.269.736179971

Possible Errors:

You can get some errors , cann’t restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.

rajudb/datafile

rajudb/onlinelog