Single-instance Oracle migrating from file system to Asm____oracle

Today, you need to test a tool in an ASM environment that lacks a test base for the ASM environment, so migrate a single instance of a filesystem to the ASM for testing and record

To install the Configure ASM instance this is not repeated here.

1, view ASM instance Status
[Oracle@goolen ~]$ Export Oracle_sid=+asm

[Oracle@goolen ~]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.1.0-production on Sat Dec 14 18:51:48 2013

Copyright (c) 1982, +, 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 Instance_name,status from V$instance;

instance_name STATUS
-------------------------------- ------------------------
+asm started

Sql> col name for A35
Sql> Set Lines 120
Sql> select Name,state from V$asm_diskgroup;

NAME State
----------------------------------- ----------------------
DATA01 Mounted
DATA02 Mounted

2, view database information:
[Oracle@goolen ~]$ Export oracle_sid=ora10g
[Oracle@goolen ~]$ Sqlplus/as SYSDBA

Sql> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/system01.dbf
/opt/app/oracle/oradata/ora10g/undotbs01.dbf
/opt/app/oracle/oradata/ora10g/sysaux01.dbf
/opt/app/oracle/oradata/ora10g/user01.dbf
/opt/app/oracle/oradata/ora10g/goolen01.dbf

Sql> Select member from V$logfile;

Member
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/redo01.log
/opt/app/oracle/oradata/ora10g/redo02.log
/opt/app/oracle/oradata/ora10g/redo03.log

Sql> select name from V$controlfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/control01.ctl

Sql> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/temp01.dbf

3, change the SPFile file, modify the parameters
Sql> Show Parameter Control_file

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
Control_file_record_keep_time Integer 7
Control_files string/opt/app/oracle/oradata/ora10g
/control01.ctl
Sql> Show Parameter File_dest

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
Audit_file_dest String/opt/app/oracle/admin/ora10g/adump
Db_create_file_dest string
Db_recovery_file_dest string
Db_recovery_file_dest_size Big Integer 0

Sql> alter system set control_files= ' +data01 ' scope=spfile;

System altered.

Sql> alter system set db_create_file_dest= ' +data01 ' scope=spfile;

System altered.

Sql> shut Immediate

4, using Rman to migrate data files and control files
[Oracle@goolen ~]$ Rman Target/

Recovery manager:release 10.2.0.1.0-production on Sat Dec 14 19:01:15 2013

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

Connected to target database (not started)

rman> startup Nomount;

Oracle instance started

Total System Global area 289406976 bytes

Fixed Size 1219016 bytes
Variable Size 234882616 bytes
Database buffers 50331648 bytes
Redo buffers 2973696 bytes

rman> restore Controlfile from '/opt/app/oracle/oradata/ora10g/control01.ctl ';

Starting restore at 2013-12-14 19:01:56
Using target database control file instead of recovery catalog
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=36 Devtype=disk

Channel ora_disk_1:copied control File copy
Output filename=+data01/ora10g/controlfile/backup.256.834174129
Finished restore at 2013-12-14 19:02:12

Rman> ALTER DATABASE Mount;

Database mounted
Released Channel:ora_disk_1

rman> run{backup as Copy database format ' +DATA01 ';}

Starting backup at 2013-12-14 19:03:19
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=36 Devtype=disk
Channel ora_disk_1:starting datafile Copy
Input datafile fno=00001 name=/opt/app/oracle/oradata/ora10g/system01.dbf
Output filename=+data01/ora10g/datafile/system.257.834174201 tag=tag20131214t190320 recid=1 stamp=834174219
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:25
Channel ora_disk_1:starting datafile Copy
Input datafile fno=00003 name=/opt/app/oracle/oradata/ora10g/sysaux01.dbf
Output filename=+data01/ora10g/datafile/sysaux.258.834174225 tag=tag20131214t190320 recid=2 stamp=834174234
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:15
Channel ora_disk_1:starting datafile Copy
Input datafile fno=00002 name=/opt/app/oracle/oradata/ora10g/undotbs01.dbf
Output filename=+data01/ora10g/datafile/undotbs1.259.834174241 tag=tag20131214t190320 recid=3 stamp=834174243
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:03
Channel ora_disk_1:starting datafile Copy
Input datafile fno=00004 name=/opt/app/oracle/oradata/ora10g/user01.dbf
Output filename=+data01/ora10g/datafile/users.260.834174245 tag=tag20131214t190320 recid=4 stamp=834174245
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:01
Channel ora_disk_1:starting datafile Copy
Input datafile fno=00005 name=/opt/app/oracle/oradata/ora10g/goolen01.dbf
Output filename=+data01/ora10g/datafile/goolen.261.834174245 tag=tag20131214t190320 recid=5 stamp=834174246
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:01
Channel ora_disk_1:starting datafile Copy
Copying control file
Output filename=+data01/ora10g/controlfile/backup.262.834174247 tag=tag20131214t190320 recid=6 stamp=834174247
Channel ora_disk_1:datafile copy complete, elapsed time:00:00:01
Channel ora_disk_1:starting full datafile backupset
Channel ora_disk_1:specifying DataFile (s) in Backupset
Including current SPFILE in Backupset
Channel ora_disk_1:starting piece 1 at 2013-12-14 19:04:07
Channel ora_disk_1:finished piece 1 at 2013-12-14 19:04:08
Piece handle=+data01/ora10g/backupset/2013_12_14/nnsnf0_tag20131214t190320_0.263.834174247 tag= tag20131214t190320 Comment=none
Channel Ora_disk_1:backup set complete, elapsed time:00:00:01
Finished backup at 2013-12-14 19:04:09

rman> switch database to copy;

DataFile 1 switched to datafile copy "+data01/ora10g/datafile/system.257.834174201"
DataFile 2 switched to datafile copy "+data01/ora10g/datafile/undotbs1.259.834174241"
DataFile 3 switched to datafile copy "+data01/ora10g/datafile/sysaux.258.834174225"
DataFile 4 switched to datafile copy "+data01/ora10g/datafile/users.260.834174245"
DataFile 5 switched to datafile copy "+data01/ora10g/datafile/goolen.261.834174245"

rman> Recover database;

Starting recover at 2013-12-14 19:04:34
Using channel Ora_disk_1

Starting Media recovery
Media recovery complete, elapsed time:00:00:06

Finished recover at 2013-12-14 19:04:41

rman> ALTER DATABASE open;

Database opened

Rman> exit

Recovery Manager complete.


5, migrating redo log and Tempfile
[Oracle@goolen ~]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.1.0-production on Sat Dec 14 19:05:02 2013

Copyright (c) 1982, +, 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 Instance_name,status from V$instance;

instance_name STATUS
-------------------------------- ------------------------
ora10g OPEN

Sql> Select member from V$logfile;

Member
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/redo01.log
/opt/app/oracle/oradata/ora10g/redo02.log
/opt/app/oracle/oradata/ora10g/redo03.log

Sql> Select bytes from V$log;

BYTES
----------
52428800
52428800
52428800


sql> ALTER DATABASE Add logfile ' +data02 ' size 50m;

Database altered.

sql> ALTER DATABASE Add logfile ' +data02 ' size 50m;

Database altered.

sql> ALTER DATABASE Add logfile ' +data02 ' size 50m;

Database altered.

Sql> Col member for A55
Sql> Set Lines 120
Sql> select Group#,member from V$logfile;

group# Member
---------- -------------------------------------------------------
1/opt/app/oracle/oradata/ora10g/redo01.log
2/opt/app/oracle/oradata/ora10g/redo02.log
3/opt/app/oracle/oradata/ora10g/redo03.log
4 +data02/ora10g/onlinelog/group_4.256.834174527
5 +data02/ora10g/onlinelog/group_5.257.834174533
6 +data02/ora10g/onlinelog/group_6.258.834174539

6 rows selected.

sql> ALTER DATABASE drop logfile Group 1;
ALTER DATABASE drop logfile Group 1
*
ERROR at line 1:
Ora-01623:log 1 is current log for instance ora10g (thread 1)-Cannot drop
Ora-00312:online Log 1 Thread 1: '/opt/app/oracle/oradata/ora10g/redo01.log '

sql> ALTER DATABASE drop logfile Group 2;

Database altered.

sql> ALTER DATABASE drop logfile Group 3;

Database altered.

sql> alter system switch logfile;

System altered.

sql> ALTER DATABASE drop logfile Group 1;
ALTER DATABASE drop logfile Group 1
*
ERROR at line 1:
Ora-01624:log 1 needed for crash recovery of Instance ora10g (thread 1)
Ora-00312:online Log 1 Thread 1: '/opt/app/oracle/oradata/ora10g/redo01.log '

Sql> alter system checkpoint;

System altered.

sql> ALTER DATABASE drop logfile Group 1;

Database altered.

Sql> select Group#,member from V$logfile;

group# Member
---------- -------------------------------------------------------
4 +data02/ora10g/onlinelog/group_4.256.834174527
5 +data02/ora10g/onlinelog/group_5.257.834174533
6 +data02/ora10g/onlinelog/group_6.258.834174539

sql> Alter tablespace temp add tempfile ' +data02 ' size 10m autoextend on maxsize 2G;

Tablespace altered.

sql> alter tablespace temp drop tempfile '/opt/app/oracle/oradata/ora10g/temp01.dbf ';

Tablespace altered.

Sql> col name for A55
Sql> select Name,status from V$tempfile;

NAME STATUS
------------------------------------------------------- --------------
+data02/ora10g/tempfile/temp.259.834174669 ONLINE

sql> alter system switch logfile;

System altered.

sql> alter system switch logfile;

System altered.

sql> alter system switch logfile;

System altered.

Sql> select name from V$datafile;

NAME
-------------------------------------------------------
+data01/ora10g/datafile/system.257.834174201
+data01/ora10g/datafile/undotbs1.259.834174241
+data01/ora10g/datafile/sysaux.258.834174225
+data01/ora10g/datafile/users.260.834174245
+data01/ora10g/datafile/goolen.261.834174245