续：Filesystem to asm 迁移:
第二部分：
Author: Sheng Huiping
Date Jun 07 2012
本章主要是提到rman 的恢复：
下面开始做迁移：
2.1 检查源库的datafile, tempfile, redo file ,control file , DBID:
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
EZHOU 4046377924
2.2:检查数据库物理对象：
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u02/ezhou/system01.dbf
2 40 UNDOTBS1 *** /u02/ezhou/undotbs01.dbf
3 240 SYSAUX *** /u02/ezhou/sysaux01.dbf
4 5 USERS *** /u02/ezhou/users01.dbf
5 100 EXAMPLE *** /u02/ezhou/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/ezhou/temp01.dbf
好，在备份之前先create 一个table作为测试;
SQL> conn scott/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SQL> create table huiping as select * from emp;
Table created.
SQL> select count(*) from huiping;
COUNT(*)
----------
14
2.3：备份database ,archive log, controlfile:
RMAN> run {
allocate channel ezhou1 type disk;
allocate channel ezhou2 type disk;
allocate channel ezhou3 type disk;
backup incremental level 0 database format '/u01/rman/ezhou_full_%u.bak';
backup archivelog all format '/u01/rman/ezhou_archive_%u.bak';
backup current controlfile format '/u01/rman/ezhouctl';
release channel ezhou1;
release channel ezhou2;
release channel ezhou3;
}
刚才发现ezhou 没有变成归档modle,下面设一下;
startup mount;
alter database archvielog;
QL> alter system set log_archive_dest = '/u01/rman/archive' scope=both;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/rman/archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2.4:把备份的传到 asm server 上；
-------------
Step2: 下面修改启动参数文件：
[oracle@aoracle dbs]$ env | grep ORA
ORACLE_SID=ezhou
ORACLE_BASE=/u01/pp/oracle
ORACLE_HOME=/u01/pp/oracle/product/10.2/db_1
[oracle@aoracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 7 14:09:12 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> create pfile from spfile;
File created.
-----------------
启动ASM 实例，检查diskgroup 状态：
export ORACLE_SID=+SHENG
sqlplus / as sysdba
startup;
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
SHENGASM MOUNTED
DATA MOUNTED
真正的修改参数文件如下;
我就修改了一项：
*.control_files='+data/ezhou/controlfile/control01.ctl'
rman target /
set dbid=4046377924
startup nomount;
恢复控制文件，数据库：
RMAN> restore controlfile from '/u01/rman/ezhouctl';
Starting restore at 07-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=+DATA/ezhou/controlfile/control01.ctl
Finished restore at 07-JAN-12
打开另外一个terminate 看alert 日志：
可以看到已近触发到了 +data里面的内容：
Sat Jan 7 15:10:15 2012
Starting background process ASMB
ASMB started with pid=18, OS id=13096
Starting background process RBAL
RBAL started with pid=19, OS id=13101
Sat Jan 7 15:10:24 2012
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
Sat Jan 7 15:13:30 2012
NOTE: ASMB process exiting due to lack of ASM file activity
Sat Jan 7 15:13:30 2012
Stopping background process RBAL
-------------------------
以上是我们想看到的，
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
可以看一下日志：
Sat Jan 7 15:23:09 2012
alter database mount
Sat Jan 7 15:23:09 2012
Starting background process ASMB
ASMB started with pid=18, OS id=13501
Starting background process RBAL
RBAL started with pid=19, OS id=13505
Sat Jan 7 15:23:13 2012
SUCCESS: diskgroup DATA was mounted
Sat Jan 7 15:23:17 2012
Setting recovery target incarnation to 2
Sat Jan 7 15:23:17 2012
Successful mount of redo thread 1, with mount id 4058063773
Sat Jan 7 15:23:17 2012
Database mounted in Exclusive Mode
Completed: alter database mount
------------------------------
下面恢复数据：
RMAN> run {
2> set newname for datafile 1 to '+data';
3> set newname for datafile 2 to '+data';
4> set newname for datafile 3 to '+data';
5> set newname for datafile 4 to '+data';
6> set newname for datafile 5 to '+data';
7> set newname for tempfile 1 to '+data';
8> restore database;
9> switch datafile all;
10> recover database;
11> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA
restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/rman/ezhou_full_0bn060ge.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/ezhou_full_0bn060ge.bak tag=TAG20120107T141750
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +DATA
restoring datafile 00004 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/rman/ezhou_full_0an060ge.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/ezhou_full_0an060ge.bak tag=TAG20120107T141750
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/rman/ezhou_full_09n060ge.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/ezhou_full_09n060ge.bak tag=TAG20120107T141750
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 07-JAN-12
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=771953484 filename=+DATA/ezhou/datafile/system.261.771953449
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=771953485 filename=+DATA/ezhou/datafile/undotbs1.258.771953397
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=771953488 filename=+DATA/ezhou/datafile/sysaux.259.771953423
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=771953489 filename=+DATA/ezhou/datafile/users.260.771953423
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=771953489 filename=+DATA/ezhou/datafile/example.257.771953397
Starting recover at 07-JAN-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u02/ezhou/redo02.log
archive log thread 1 sequence 4 is already on disk as file /u02/ezhou/redo03.log
archive log filename=/u02/ezhou/redo02.log thread=1 sequence=3
archive log filename=/u02/ezhou/redo03.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:05
Finished recover at 07-JAN-12
--------------
以上过程，看一下alert 日志也是正确的。
下面把数据库打开：
RMAN> alter database open resetlogs;
database opened
下面检查并添加临时文件，日志文件：
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/ezhou/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/ezhou/redo03.log
/u02/ezhou/redo02.log
/u02/ezhou/redo01.log
好，下面添加日志文件：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 493371 07-JAN-12
SQL> alter database add logfile group 4 '+data' size 50m;
Database altered.
SQL> alter database add logfile group 5 '+data' size 50m;
Database altered.
SQL> alter database add logfile group 6 '+data' size 50m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 493371 07-JAN-12
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 NO CURRENT 494082 07-JAN-12
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 YES ACTIVE 493371 07-JAN-12
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 YES ACTIVE 494082 07-JAN-12
2 1 3 52428800 1 NO CURRENT 494090 07-JAN-12
3 1 1 52428800 1 YES ACTIVE 493371 07-JAN-12
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 YES ACTIVE 494082 07-JAN-12
2 1 3 52428800 1 YES ACTIVE 494090 07-JAN-12
3 1 1 52428800 1 YES ACTIVE 493371 07-JAN-12
4 1 4 52428800 1 NO CURRENT 494124 07-JAN-12
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
4 1 4 52428800 1 NO CURRENT 494124 07-JAN-12
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
下面从新配temp file:
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
6 rows selected.
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u02/ezhou/temp01.dbf
SQL> alter tablespace temp add tempfile '+data' size 100m;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u02/ezhou/temp01.dbf';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------------------------------
+DATA/ezhou/tempfile/temp.265.771955481
----------------------
下面检查datafile ,control file, redo log:
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
+DATA/ezhou/datafile/system.261.771953449
+DATA/ezhou/datafile/undotbs1.258.771953397
+DATA/ezhou/datafile/sysaux.259.771953423
+DATA/ezhou/datafile/users.260.771953423
+DATA/ezhou/datafile/example.257.771953397
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
+DATA/ezhou/controlfile/control01.ctl
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
+DATA/ezhou/onlinelog/group_4.262.771954183
+DATA/ezhou/onlinelog/group_5.263.771954207
+DATA/ezhou/onlinelog/group_6.264.771954225
下面检查看一下备份前的数据：
SQL> conn scott/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
HUIPING TABLE
SQL> select count(*) from huiping;
COUNT(*)
----------
14
到现在为止，迁移成功完成。
特别说明：
1: if instance is open ,then can not shutdown the ASM instance.
SQL> select name, state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
SHENGASM MOUNTED
DATA MOUNTED
SQL> shutdown immediate;
ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance
SQL>
2: if the database instance is down, then can shutdown the ASM instance:
Step1:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
Step2:
SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL>
今天很圆满 ^ _ ^
扫一扫
1246
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
