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