首页 » ORACLE » move rdbms from local file(no-ASM) to ASM instance

move rdbms from local file(no-ASM) to ASM instance

one asm instance can support multiple rdbms instances,and it is possible to run more than one asm instance to which the rdbms instance could connect,this potential many-to-many relationship btween the instance types is resolved by the cluster services.

it is possible to have more than on asm instance running on one computer,but there is no value in doing this,you should create one asm instance per computer and use it to manager all the asm disks available to that computer on behalf of all the RDBMS instance running on the computer.

[oracle@ora11 bin]$ export ORACLE_SID=+ASM
[oracle@ora11 bin]$ export ORACLE_HOME=/ora10/app/oracle11/product/11.2.0/grid

SQL> select NAME,STATE,FREE_MB,DATABASE_COMPATIBILITY from v$asm_diskgroup;
NAME STATE FREE_MB DATABASE_COMPATIBILITY
——————– ———- ——————————
DATA MOUNTED 20352 10.1.0.0.0

my test env..

asm:11201
db:10201 single instance

Tip:
if in rdbms instance not found the asm instance,login asm instance and execute
alter system register;

–connect rdbms instance

[oracle@ora11 ~]$ . env10 
[oracle@ora11 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 14 23:17:21 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

sys@ORA10> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/ora10/app/oracle/oradata/ora10/system01.dbf
/ora10/app/oracle/oradata/ora10/undotbs01.dbf
/ora10/app/oracle/oradata/ora10/sysaux01.dbf
/ora10/app/oracle/oradata/ora10/users01.dbf

sys@ORA10> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/ora10/app/oracle/oradata/ora10/temp01.dbf

sys@ORA10> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/ora10/app/oracle/oradata/ora10/redo03.log
/ora10/app/oracle/oradata/ora10/redo02.log
/ora10/app/oracle/oradata/ora10/redo01.log

sys@ORA10> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora10/app/oracle/product/10.2
                                                 .0/db2/dbs/spfileora10.ora
sys@ORA10> show parameter file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /ora10/app/oracle/admin/ora10/
                                                 adump
db_create_file_dest                  string
db_recovery_file_dest                string      /ora10/app/oracle/flash_recove
                                                 ry_area
db_recovery_file_dest_size           big integer 2G
sys@ORA10> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /ora10/app/oracle/oradata/ora1
                                                 0/control01.ctl, /ora10/app/or
                                                 acle/oradata/ora10/control02.c
                                                 tl, /ora10/app/oracle/oradata/
                                                 ora10/control03.ctl
sys@ORA10> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
sys@ORA10> select name,free_mb,state from v$asm_diskgroup;
NAME                              FREE_MB STATE
------------------------------ ---------- -----------
DATA                                20350 MOUNTED

sys@ORA10> alter system set control_files='+DATA' scope=spfile;
System altered.


sys@ORA10> alter system set db_create_file_dest='+DATA' scope=spfile;
System altered.

step 1,migrate the controle file
RMAN> backup current controlfile format '/ora10/contrf.ctf'
2> ;

Starting backup at 14-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 14-JUL-12
channel ORA_DISK_1: finished piece 1 at 14-JUL-12
piece handle=/ora10/contrf.ctf tag=TAG20120714T232437 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUL-12

RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount
connected to target database (not started)
Oracle instance started

Total System Global Area     922746880 bytes

Fixed Size                     1222648 bytes
Variable Size                234883080 bytes
Database Buffers             683671552 bytes
Redo Buffers                   2969600 bytes

RMAN> restore controlfile from '/ora10/contrf.ctf';
Starting restore at 14-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DATA/ora10/controlfile/current.256.788657129
Finished restore at 14-JUL-12

step 2,migrate the datafiles

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

Starting backup at 14-JUL-12
Starting implicit crosscheck backup at 14-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 14-JUL-12

Starting implicit crosscheck copy at 14-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JUL-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/ora10/app/oracle/oradata/ora10/system01.dbf
output filename=+DATA/ora10/datafile/system.257.788657191 tag=TAG20120714T232630 recid=1 stamp=788657229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/ora10/app/oracle/oradata/ora10/sysaux01.dbf
output filename=+DATA/ora10/datafile/sysaux.258.788657237 tag=TAG20120714T232630 recid=2 stamp=788657256
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/ora10/app/oracle/oradata/ora10/undotbs01.dbf
output filename=+DATA/ora10/datafile/undotbs1.259.788657261 tag=TAG20120714T232630 recid=3 stamp=788657263
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/ora10/app/oracle/oradata/ora10/users01.dbf
output filename=+DATA/ora10/datafile/users.260.788657265 tag=TAG20120714T232630 recid=4 stamp=788657265
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-JUL-12

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

TIP:current control file had in ASM,so the WARNING could be ignore!

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /ora10/app/oracle/oradata/ora10/system01.dbf
2    25       UNDOTBS1             ***     /ora10/app/oracle/oradata/ora10/undotbs01.dbf
3    240      SYSAUX               ***     /ora10/app/oracle/oradata/ora10/sysaux01.dbf
4    5        USERS                ***     /ora10/app/oracle/oradata/ora10/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /ora10/app/oracle/oradata/ora10/temp01.dbf

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ora10/datafile/system.257.788657191"
datafile 2 switched to datafile copy "+DATA/ora10/datafile/undotbs1.259.788657261"
datafile 3 switched to datafile copy "+DATA/ora10/datafile/sysaux.258.788657237"
datafile 4 switched to datafile copy "+DATA/ora10/datafile/users.260.788657265"

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     +DATA/ora10/datafile/system.257.788657191
2    25       UNDOTBS1             ***     +DATA/ora10/datafile/undotbs1.259.788657261
3    240      SYSAUX               ***     +DATA/ora10/datafile/sysaux.258.788657237
4    5        USERS                ***     +DATA/ora10/datafile/users.260.788657265

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /ora10/app/oracle/oradata/ora10/temp01.dbf

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/14/2012 23:30:34
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '+DATA/ora10/datafile/system.257.788657191'

RMAN> recover database;

Starting recover at 14-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /ora10/app/oracle/oradata/ora10/redo01.log
archive log filename=/ora10/app/oracle/oradata/ora10/redo01.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 14-JUL-12

RMAN> alter database open resetlogs;

database opened

step 3,migrate the temp file(have tow way)


sys@ORA10> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/ora10/app/oracle/oradata/ora10/temp01.dbf
sys@ORA10> alter database tempfile '/ora10/app/oracle/oradata/ora10/temp01.dbf' drop;

Database altered.
sys@ORA10> alter tablespace temp  add tempfile '+DATA' size 50m;
Tablespace altered.

sys@ORA10> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/ora10/tempfile/temp.261.788657707

*****************************second way******************
sys@ORA10> alter tablespace temp add  tempfile '+DATA';
Tablespace altered.

sys@ORA10> alter tablespace temp drop tempfile '+DATA/ora10/tempfile/temp.261.788657707';
Tablespace altered.

sys@ORA10> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/ora10/tempfile/temp.262.788657805
*****************************end************************

step 4,migrate the redo logfile
sys@ORA10> col member for a60
sys@ORA10> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /ora10/app/oracle/oradata/ora10/redo03.log                   NO
         2         ONLINE  /ora10/app/oracle/oradata/ora10/redo02.log                   NO
         1         ONLINE  /ora10/app/oracle/oradata/ora10/redo01.log                   NO

sys@ORA10> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          1   52428800          1 NO  CURRENT                 456885 14-JUL-12
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          0   52428800          1 YES UNUSED                       0

sys@ORA10> alter database drop logfile group 3;

Database altered.
sys@ORA10> alter database add logfile group 3 '+DATA';

Database altered.
sys@ORA10> alter database add logfile group 2 '+DATA';
Database altered.

sys@ORA10> ALTER system switch logfile;
System altered.

sys@ORA10> ALTER system switch logfile;
System altered.

sys@ORA10> alter system checkpoint;
System altered.

sys@ORA10> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          1   52428800          1 NO  INACTIVE                456885 14-JUL-12
         2          1          2  104857600          1 NO  INACTIVE                457320 14-JUL-12
         3          1          3  104857600          1 NO  CURRENT                 457323 14-JUL-12

sys@ORA10> alter database drop logfile group 1;
Database altered.

sys@ORA10> alter database add logfile group 1 '+DATA';
Database altered.

TIP:add more than one member per group
 ALTER database add LOGFILE  member '+DATA' TO GROUP 1;

sys@ORA10> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  +DATA/ora10/onlinelog/group_3.261.788657965                  NO
         2         ONLINE  +DATA/ora10/onlinelog/group_2.263.788658019                  NO
         1         ONLINE  +DATA/ora10/onlinelog/group_1.264.788658075                  NO

step 5,migrate the spfile
sys@ORA10> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora10/app/oracle/product/10.2
                                                 .0/db2/dbs/spfileora10.ora
sys@ORA10> create pfile from spfile;
File created.

sys@ORA10> create spfile='+DATA/ora10/spora10.ora' from pfile;
File created.

sys@ORA10> host
[oracle@ora11 ~]$ cd $ORACLE_HOME/dbs
[oracle@ora11 dbs]$ ls
hc_+ASM.dat  hc_ora10.dat   initdw.ora  init.ora  initora10.ora  lkORA10  orapwora10  snapcf_ora10.f  spfileora10.ora

[oracle@ora11 dbs]$ mv spfileora10.ora spfileora10.ora_bak
[oracle@ora11 dbs]$ vi initora10.ora 

spfile='+DATA/ora10/spora10.ora'

:wq
sys@ORA10> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10> startup
ORACLE instance started.

Total System Global Area  922746880 bytes
Fixed Size                  1222648 bytes
Variable Size             239077384 bytes
Database Buffers          679477248 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
sys@ORA10> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ora10/spora10.ora

sys@ORA10> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ora10/spora10.ora
sys@ORA10> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /ora10/app/oracle/flash_recove
                                                 ry_area
db_recovery_file_dest_size           big integer 2G
recovery_parallelism                 integer     0
sys@ORA10> alter system set db_recovery_file_dest='+DATA';
System altered.

sys@ORA10> show parameter recover
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DATA

ok.

Note:11203 database instance can not use 11201 asm instance.

SQL> create tablespace tbs datafile ‘+DATA’ size 3m;
create tablespace tbs datafile ‘+DATA’ size 3m
*
ERROR at line 1:
ORA-01119: error in creating database file ‘+DATA’
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup “DATA” does not exist or is not mounted
ORA-15064: communication failure with ASM instance
ORA-15295: ASM instance software version 11.2.0.1.0 less than client version
11.2.0.3.0

[oracle@ora11 ~]$ oerr ora 15295
15295, 00000, “ASM instance software version %s less than client version %s”
// Cause: The ASM instance software version was less than the client version.
// Action: Upgrade the ASM instance software. Check the alert log for
// more information on the reason for the failure.

打赏

对不起,这篇文章暂时关闭评论。