Oracle Streams in Azerbaijan Oracle User Group in azerbaijani

Hi friends, on 1st December of 2012 I will attend to Azerbaijan Oracle User Group meeting and will talk about Oracle Streams in Azerbaijani. In the seminar I will demonstrate how Streams works, and there will be some FAQ part, such as : What is streams, why do you use streams, streams advantages, streams architecture and so on.

Link: http://www.azeroug.org/?p=124

ORA-17628, ORA-19505 during RMAN DUPLICATE FROM ACTIVE Metalink : [ID 1331986.1]

When I tried to use Oracle 11g`s create active standby feature weeks ago I faced below error, but after research it on Metalink I found solution.

[oracle@db-server dbs]$ rman target / auxiliary sys/oracle@testdb_stby;

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 28 17:11:39 2012

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

connected to target database: TESTDB (DBID=2580410479)

connected to auxiliary database: TESTDB (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE

NOFILENAMECHECK

DORECOVER SPFILE

SET DB_UNIQUE_NAME="TESTDB_STBY"

SET LOG_ARCHIVE_DEST_2="service=TESTDB_LIVE LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)"

SET CONTROL_FILES='/oracle/product/11.2.0/oradata/testdb/control01.ctl';

Starting Duplicate Db at 28-OCT-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/oracle/product/11.2.0/db_1/dbs/orapwtestdb' auxiliary format

 '/oracle/product/11.2.0/db_1/dbs/orapwtestdb'   targetfile

 '/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' auxiliary format

 '/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora'   ;

   sql clone "alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''";

}

executing Memory Script

Starting backup at 28-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

Finished backup at 28-OCT-12

sql statement: alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''

contents of Memory Script:

{

   sql clone "alter system set  db_unique_name =

 ''TESTDB_STBY'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =

 ''service=TESTDB_LIVE LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  CONTROL_FILES =

 ''/oracle/product/11.2.0/oradata/testdb/control01.ctl'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_unique_name =  ''TESTDB_STBY'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''service=TESTDB_LIVE LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''/oracle/product/11.2.0/oradata/testdb/control01.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 10/28/2012 17:12:32

RMAN-03015: error occurred in stored script Memory Script

RMAN-04014: startup failed: ORA-16184: DB_UNIQUE_NAME testdb hashes to the same value as DB_UNIQUE_NAME testdb

RMAN-04017: startup error description: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Deatil of solution provided by Metalink guide :  Metalink : [ID 1331986.1]

References

BUG:12609412 - ORA-17628 ORA-19505 DURING DUPLICATE FROM ACTIVE

How to migrate NON ASM (datafiles, redologs, controlfiles, spfile, archive logs) to ASM and back

Today we will demonstrate migration NON ASM to ASM storage.

We have already ASM instance and 2 asm diskgroups DATA and FLSHBK

Our database name is mydb and it works Archivelog mode.

Be sure block change tracking is disabled

SQL> select status from v$block_change_tracking;

STATUS
----------
DISABLED

SQL>

If no, disable it

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>


Firstly check filenames on the database using this SQL:


SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1;  2    3    4    5    6    7

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

12 rows selected.

SQL>


Let`s go.

Shutdown database :


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

Create pfile from spfile and edit

SQL> create pfile from spfile;

File created.

SQL> 

[oracle@localhost dbs]$ gedit initmydb.ora
..
*.control_files=(+DATA, +FLSHBK)
...

Save and exit, then create spfile from pfile


SQL> create spfile from pfile;

File created.

SQL>


Now startup database with nomount status via RMAN and restore control file from fresh backup:


[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:19:43 2012

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                 83887696 bytes
Database Buffers             197132288 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile from '/home/oracle/oracle/product/10.2.0/db_1/dbs/control.ctl';

Starting restore at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/mydb/controlfile/current.256.799240855
output filename=+FLSHBK/mydb/controlfile/current.256.799240867
Finished restore at 13-NOV-12

RMAN>

Now our control files are located in two ASM disk gorups.

Open database with RESETLOGS option.


RMAN> alter database open resetlogs;

database opened

RMAN>

Because we restored control file!

SQL> select member from v$logfile

union

select file_name from dba_data_files

union

select name from v$controlfile

union

select value from v$parameter where name='spfile' order by 1;   2    3    4    5    6    7  

MEMBER

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

+DATA/mydb/controlfile/current.256.799240855

+FLSHBK/mydb/controlfile/current.256.799240867

/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora

/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf

/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

11 rows selected.

SQL> 

Now let`s migrate datafiles.

Repeat above steps:

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

SQL> 

Edit pfile:

[oracle@localhost dbs]$ gedit initmydb.ora

*.db_create_file_dest=+DATA

*.db_recovery_file_dest=+FLSHBK

Now, create spfile from pfile

SQL> create spfile from pfile;

File created.

SQL> 

Start database and backup as copy of database provide ASM disk group(s), then switch datafiles

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:35:09 2012

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started

database mounted

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes

Variable Size                 83887696 bytes

Database Buffers             197132288 bytes

Redo Buffers                   2973696 bytes

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 13-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf

output filename=+DATA/mydb/datafile/system.257.799241745 tag=TAG20121113T113544 recid=2 stamp=799241794

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf

output filename=+DATA/mydb/datafile/sysaux.258.799241803 tag=TAG20121113T113544 recid=3 stamp=799241832

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf

output filename=+DATA/mydb/datafile/example.259.799241837 tag=TAG20121113T113544 recid=4 stamp=799241844

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf

output filename=+DATA/mydb/datafile/undotbs1.260.799241853 tag=TAG20121113T113544 recid=5 stamp=799241856

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

output filename=+DATA/mydb/datafile/users.261.799241861 tag=TAG20121113T113544 recid=6 stamp=799241863

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

Finished backup at 13-NOV-12

Starting Control File and SPFILE Autobackup at 13-NOV-12

piece handle=+FLSHBK/mydb/autobackup/2012_11_13/s_799241353.257.799241869 comment=NONE

Finished Control File and SPFILE Autobackup at 13-NOV-12

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/mydb/datafile/system.257.799241745"

datafile 2 switched to datafile copy "+DATA/mydb/datafile/undotbs1.260.799241853"

datafile 3 switched to datafile copy "+DATA/mydb/datafile/sysaux.258.799241803"

datafile 4 switched to datafile copy "+DATA/mydb/datafile/users.261.799241861"

datafile 5 switched to datafile copy "+DATA/mydb/datafile/example.259.799241837"

RMAN> 

Now, open the database

RMAN> alter database open;

database opened

RMAN> 

[oracle@localhost dbs]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 11:41:30 2012

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select member from v$logfile

union

select file_name from dba_data_files

union

select name from v$controlfile

union

select value from v$parameter where name='spfile' order by 1;   2    3    4    5    6    7  

MEMBER

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

+DATA/mydb/controlfile/current.256.799240855

+DATA/mydb/datafile/example.259.799241837

+DATA/mydb/datafile/sysaux.258.799241803

+DATA/mydb/datafile/system.257.799241745

+DATA/mydb/datafile/undotbs1.260.799241853

+DATA/mydb/datafile/users.261.799241861

+FLSHBK/mydb/controlfile/current.256.799240867

/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

11 rows selected.

SQL> 

Not: tempfile not located on control file so we have to re-create new one and delete existing tempfile;

SQL> select name, bytes from v$tempfile;

 NAME                                                                                            BYTES

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

/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf          20971520

SQL> create temporary tablespace tempnew tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace tempnew;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace tempnew including contents;

Tablespace dropped.

SQL> select name, bytes from v$tempfile;

NAME                                                                                       BYTES

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

+DATA/mydb/tempfile/temp.263.799242427                            104857600

Now migrate redo logs and dropping old files, Set db_create_online_log_dest_1 value

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              88082000 bytes

Database Buffers          192937984 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL>

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

         2 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> 

As you see we have had 3 redo groups. To maintain redo files we have to use redo which status INACTIVE. To do that switch logfile.

SQL> alter system switch logfile;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 ACTIVE       /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

         2 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log

         2 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

         3 UNUSED     /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

Now drop INAVCTIVE redo files and create new one

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 50M;

Database altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 UNUSED      +DATA/mydb/onlinelog/group_1.262.799243295

         2 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log

         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 50M;

Database altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 UNUSED      +DATA/mydb/onlinelog/group_1.262.799243295

         2 UNUSED      +DATA/mydb/onlinelog/group_2.264.799243379

         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 INACTIVE    +DATA/mydb/onlinelog/group_1.262.799243295

         2 ACTIVE        +DATA/mydb/onlinelog/group_2.264.799243379

         3 CURRENT   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 CURRENT    +DATA/mydb/onlinelog/group_1.262.799243295

         2 INACTIVE    +DATA/mydb/onlinelog/group_2.264.799243379

         3 INACTIVE   /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 50M;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP# STATUS MEMBER

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

         1 INACTIVE    +DATA/mydb/onlinelog/group_1.262.799243295

         2 INACTIVE    +DATA/mydb/onlinelog/group_2.264.799243379

         3 CURRENT    +DATA/mydb/onlinelog/group_3.265.799243479

SQL> 

Now let`s re-create spfile on ASM disk group.

[oracle@localhost mydb]$ export ORACLE_SID=mydb

[oracle@localhost mydb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:23:23 2012

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

SQL> create spfile='+DATA/spfile' from pfile;

File created.

SQL> 

Edit pfile :

[oracle@localhost dbs]$ pwd

/home/oracle/oracle/product/10.2.0/db_1/dbs

[oracle@localhost dbs]$ vi initmydb.ora 

...

*.spfile='+DATA/spfile'

...

Check file in ASM

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

[oracle@localhost ~]$ asmcmd

ASMCMD> ls

DATA/

FLSHBK/

ASMCMD> cd DATA

ASMCMD> ls

MYDB/

spfile

ASMCMD>

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> 

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                                    string         +DATA/spfile

SQL>

Delete old datafiles

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 12:14:17 2012

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

connected to target database: MYDB (DBID=2680024004)

RMAN> delete copy of database;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=138 devtype=DISK

List of Datafile Copies

Key     File S Completion Time Ckp SCN    Ckp Time        Name

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

7       1    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf

8       2    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf

9       3    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf

10      4    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

11      5    A 13-NOV-12       526093     13-NOV-12       /home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf recid=7 stamp=799241987

deleted datafile copy

datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=8 stamp=799241987

deleted datafile copy

datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=9 stamp=799241987

deleted datafile copy

datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=10 stamp=799241987

deleted datafile copy

datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf recid=11 stamp=799241987

Deleted 5 objects

RMAN> 

Also delete other files : redo, control and tempfile

[oracle@localhost mydb]$ ls -lrt

total 192004

-rw-r----- 1 oracle oinstall 20979712 Oct 20  2011 temp01.dbf

-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control03.ctl

-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control02.ctl

-rw-r----- 1 oracle oinstall  7061504 Nov 13 11:13 control01.ctl

-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:58 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:59 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Nov 13 12:03 redo03.log

drwxr-xr-x 2 oracle oinstall     4096 Nov 13 12:10 arch

[oracle@localhost mydb]$ rm temp01.dbf 

[oracle@localhost mydb]$ rm redo0*.log

[oracle@localhost mydb]$ rm control0*

[oracle@localhost mydb]$ 

Now check ASM disk space:

[oracle@localhost mydb]$ export ORACLE_SID=+ASM

[oracle@localhost mydb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:17:07 2012

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name, state, total_mb, free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           3000        558

FLSHBK                       MOUNTED           2000       1726

SQL> 

Select above query:

SQL> select member from v$logfile

union

select file_name from dba_data_files

union

select name from v$controlfile

union

select value from v$parameter where name='spfile' order by 1;  2    3    4    5    6    7  

MEMBER

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

+DATA/mydb/controlfile/current.256.799240855

+DATA/mydb/datafile/example.259.799241837

+DATA/mydb/datafile/sysaux.258.799241803

+DATA/mydb/datafile/system.257.799241745

+DATA/mydb/datafile/undotbs1.260.799241853

+DATA/mydb/datafile/users.261.799241861

+DATA/mydb/onlinelog/group_1.262.799243295

+DATA/mydb/onlinelog/group_2.264.799243379

+DATA/mydb/onlinelog/group_3.265.799243479

+DATA/spfile

+FLSHBK/mydb/controlfile/current.256.799240867

11 rows selected.

SQL>

All files were successfully moved to ASM

Now migrate Archivelogs to ASM disk group

edit pfile file

...

*.log_archive_dest_1=''

...

recreate spfile and startup database

SQL> select name,sequence# from v$archived_log;

NAME                                                                                                                           SEQUENCE#

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

/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_9_799241036.arc         9

/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_10_799241036.arc       10

+FLSHBK/mydb/archivelog/2012_11_13/thread_1_seq_11.272.799253049                  11

3 rows selected.

And now migrate from ASM to FileSystem.

Let`s migrate spfile to FileSystem

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA/spfile

SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora' from spfile='+DATA/spfile';

File created.

SQL> create spfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora' from pfile;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              96470608 bytes

Database Buffers          184549376 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME                    TYPE    VALUE

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

spfile                       string      /home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora

SQL>