Skip to content

Posts from the ‘RAC & ASM’ Category

Duplicate database from ASM to non ASM

Download PDF Document from here


Duplicate Database from ASM to Non pdf

Right click on thir URL, Save Target as & then Download it.


Step 1:

Determine how much disk space will be required.

SQL> select name from v$database;

NAME
---------
AZARDB

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{
2> allocate channel d1 type disk;
3> backup format '/backups/df_t%t_s%s_p%p' database;
4> sql 'alter system archive log current';
5> backup format '/backups/al_t%t_s%s_p%p' archivelog all;
6> release channel d1;
7> }

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/azardb/datafile/system.256.736179685
input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685
input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971
input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685
input datafile file number=00004 name=+SPTEST/azardb/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.

SQL>

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@azardb 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: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)

RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }

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 =
''AZARDB'' 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 =  ''AZARDB'' 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/azardb/datafile/system.256.736179685";
set newname for datafile  2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile  3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile  4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile  5 to
"/u01/app/oracle/AUX/azardb/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/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/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/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/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/azardb/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/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/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/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M  REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile  1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy  "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/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/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb 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: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)

RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }

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 =
''AZARDB'' 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 =  ''AZARDB'' 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/azardb/datafile/system.256.736179685";
set newname for datafile  2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile  3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile  4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile  5 to
"/u01/app/oracle/AUX/azardb/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/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/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/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/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/azardb/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/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/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/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M  REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile  1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy  "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/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/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/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/azardb/datafile/system.256.736179685
/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

SQL>

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.

azardb/datafile

azardb/onlinelog

How to move ASM spfile from one diskgroup to another diskgroup

Here , I just showed you ,How to move ASM spfile from one diskgroup to another diskgroup.

before I have currently using one diskgroup, so i need to create another diskgroup using ASMCA, I just formated disk using fdisk command.

After created new diskgroup, follow the steps

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:46:32 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/reg
istry.253.736177541
SQL> create pfile='/install/initASM.ora' from spfile;

File created.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/reg
istry.253.736177541
SQL> create spfile='+SPTEST' from pfile='/install/initASM.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
[oracle@localhost ~]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'localhost'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'localhost'
CRS-2673: Attempting to stop 'ora.SPTEST.dg' on 'localhost'
CRS-2673: Attempting to stop 'ora.azardb.db' on 'localhost'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'localhost' succeeded
CRS-2677: Stop of 'ora.SPTEST.dg' on 'localhost' succeeded
CRS-2677: Stop of 'ora.azardb.db' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'localhost'
CRS-2677: Stop of 'ora.DATA.dg' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'localhost'
CRS-2677: Stop of 'ora.asm' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'localhost'
CRS-2677: Stop of 'ora.cssd' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'localhost'
CRS-2677: Stop of 'ora.diskmon' on 'localhost' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'localhost' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@localhost ~]$
[oracle@localhost ~]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:57:30 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +SPTEST/asm/asmparameterfile/r
egistry.253.737898615
SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
SPTEST                         MOUNTED
DATA                           MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

you can verify using asmcmd command

[oracle@localhost ~]$ asmcmd spget
+SPTEST/asm/asmparameterfile/registry.253.737898615

just remove old spfile from here

[[oracle@localhost ~]$ asmcmd rm +DATA/asm/asmparameterfile/registry.253.736177541
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 12:00:08 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +SPTEST/asm/asmparameterfile/r
egistry.253.737898615
SQL>

 

Grid Infrastructure Installation guide for standalone server

 I just make Grid Infrastructure Installation guide for standalone server , I hope This document is very useful & User friendly for who trying to install Grid, you can download from here or review from.

Download PDF Document from here

Grid Infrastructure Installation Steps

Presentation Document :

Active/passive vs active /active Cluster

Active/Passive

An active/passive cluster generally contain two identical nodes.At any time, one of the nodes is active and the other is passive.Oracle singel instance database software is installed on both nodes, but the database is located on shared storage.During normal operation, the Oracle database instance runs only on the active node.In the event of a failure of the currently active primary system,clustering software will transfer control of the disk subsystem to the secondary system, a process known as failover.As part of the failover process, the Oracle instance on the secondary nose is started, thereby recovering and resuming the service of the Oracle database. All connections to the instance on the primary system at the time of failure are terminated and must be reestablished withe the new instance on the secondary system.On resolution of the issue, the Oracle instance is cleanly shut down on the secondary system , control of the disk subsystem is transfered back to the primary system and oracle instance is started to resume normal operations.

a number of third party products are avilable to implement active /passive clustering on linux.

Active/Active

An active /active cluster has an identical hardware infrastruture to an active /passive one. However, Oracle instances run concurrenlty on both servers and access the same database. which is located on shared storage. The instances must communicated with each other to negotiate access to shared data in the database. In the event of a server failure, the remaining server can continue processing the workload and optionally failed sessions can be reconnected . the benefit of an active / active cluster over an active/passive cluster is that,during normal processing, the workload can be shared across both servers in the cluster

Oracle RAC is desighned to run in an active / active enviornment.

RAC first day class

Today I went to attend the my first day class about Oracle Real Application Cluster. The class taken by Mr.Syed Jaffar Hussian.I thought Why i want to explain about himself.Because almost all of them(oracle Community) know about him,He is one of the famous person and outstanding DBA in this world.However I briefly explain about my trainer.He is an Oracle Certified Master (First person in Saudi Arabia) and He also Oracle ACE member and he is one of the author of upcoming book of Oracle R1/R2 RAC handbook and He is reviewer of Oracle books and he given seminar for some Organization about RMAN and RAC .I hope I will become a RAC expert after finish this course.

Today He given overviwed about Course Agenda what he will take class in future..

Cluster Introduction with its advantages.

What is Oracle RAC?

Oracle RAC architecture

Advantage of Oracle RAC

Single instance and RAC

Installation Prequistes like Hardware, Network , Storage, OS groups and users and SSH/RCP preconfiguration

Cluster utility (cluvfy) overview and usage

RAC Installation – Clustware ,ASM and RDBMS

RAC Database Creation

Common Initilization Parameters

OCR (oracle cluster registry) & Voting Disk

Enabling and Disabling automatic cluster stack start and stop

Clustware administration

RAC workload management and services -Hig avilbilty, load balance

RAC internals

Troublshooting – Node eviction Issues

Performance Tuning

Node addition and Deletion

Single instance database conversion to RAC

New features of 11g R2.