首页 » ORACLE 9i-23c » 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.
# Migrate datafile to ASM
RMAN> sql "alter tablespace xxx offline"; RMAN> backup as copy datafile 11 format '+DATA'; RMAN> backup as copy datafile 12 format '+DATA'; RMAN> switch datafile 11 to copy; RMAN> switch datafile 12 to copy; RMAN> sql "alter tablespace test online";
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。