实现数据库由文件系统迁移到 ASM 磁盘组中
一、 存储规划
二、 创建 VG、LV
[root@aix213 /]$lspv
hdisk1 00c6d0ef0395eef2 rootvg active
hdisk0 00032fb8843fafe9 racvg active
[root@aix213 dev]$mklv -y oradata -t raw racvg 160
oradata
[root@aix213 dev]$mklv -y recovery -t raw racvg 160
recovery
[root@aix213 dev]$lsvg -l racvg
racvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
oradata raw 160 160 1 closed/syncd N/A
recovery raw 160 160 1 closed/syncd N/A
更改宿主目录
[root@aix213 /]$chown oracle:dba /dev/roradata
[root@aix213 /]$chown oracle:dba /dev/oradata
[root@aix213 /]$chown oracle:dba /dev/recovery
[root@aix213 /]$chown oracle:dba /dev/rrecovery
三、 创建 ASM 磁盘组
1、 创建+ASM 实例的参数文件
[oracle@aix213 dbs]$vi init+ASM.ora
"init+ASM.ora" 6 lines, 246 characters
*.background_dump_dest='/u01/app/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/app/oracle/admin/+ASM/cdump'
*.remote_login_passwordfile='EXCLUSIVE'
*.user_dump_dest='/u01/app/oracle/admin/+ASM/udump'
instance_type='asm'
large_pool_size=12M
asm_diskstring='/dev/roradata','/dev/rrecovery'
~
2、 用 root 用户启动 CSS 服务
[root@aix213 bin]$./localconfig delete
/etc/oracle does not exist. Creating it now.
./localconfig[715]: /etc/init.cssd: not found
[root@aix213 bin]$./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'system'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
aix213
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@aix213 bin]$
3、 创建 ASM 磁盘组
[oracle@aix213 bin]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 25 16:42:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$Instance;
STATUS
------------
STARTED
SQL> desc v$asm_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
SQL> select NAME, STATE from v$asm_diskgroup;
no rows selected
SQL> create diskgroup DATA external redundancy disk '/dev/roradata';
Diskgroup created.
SQL> create diskgroup REC external redundancy disk '/dev/rrecovery';
Diskgroup created.
SQL> select NAME, STATE from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DATA MOUNTED
REC MOUNTED
[实施步骤]
一、 将原库控制文件所在路径修改为 ASM 磁盘组上的路径
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DATA';
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
二、 用 rman 迁移目标数据库的控制文件和数据文件
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
1、恢复一个最近的 0 级备份的控制文件
RMAN> restore controlfile from '/back/mirror/zhang_c-2690774443-20140625-13.ctl';
Starting restore at 25-JUN-14
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:17
output filename=+DATA/zhang1/controlfile/current.256.851192723
Finished restore at 25-JUN-14
2、 以 COPY 的形式备份一个新的数据文件在 ASM 磁盘上
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 25-JUN-14
Starting implicit crosscheck backup at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 41 objects
Finished implicit crosscheck backup at 25-JUN-14
Starting implicit crosscheck copy at 25-JUN-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-14
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=/u01/app/oracle/oradata/zhang1/system01.dbf
output filename=+DATA/zhang1/datafile/system.257.851192845 tag=TAG20140625T182724 recid=34
stamp=851192898
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/zhang1/report01.dbf
output filename=+DATA/zhang1/datafile/report.258.851192901 tag=TAG20140625T182724 recid=35
stamp=851192940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/zhang1/sysaux01.dbf
output filename=+DATA/zhang1/datafile/sysaux.259.851192945 tag=TAG20140625T182724 recid=36
stamp=851192979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/zhang1/example01.dbf
output filename=+DATA/zhang1/datafile/example.260.851192981 tag=TAG20140625T182724 recid=37
stamp=851193004
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/zhang1/undotbs01.dbf
output filename=+DATA/zhang1/datafile/undotbs1.261.851193007 tag=TAG20140625T182724 recid=38
stamp=851193011
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/zhang1/users01.dbf
output filename=+DATA/zhang1/datafile/users.262.851193015 tag=TAG20140625T182724 recid=39
stamp=851193014
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUN-14
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
3、 令数据库中的控制文件指向新的备份所在的位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/zhang1/datafile/system.257.851192845"
datafile 2 switched to datafile copy "+DATA/zhang1/datafile/undotbs1.261.851193007"
datafile 3 switched to datafile copy "+DATA/zhang1/datafile/sysaux.259.851192945"
datafile 4 switched to datafile copy "+DATA/zhang1/datafile/users.262.851193015"
datafile 5 switched to datafile copy "+DATA/zhang1/datafile/example.260.851192981"
datafile 6 switched to datafile copy "+DATA/zhang1/datafile/report.258.851192901"
4、对数据库进行恢复
RMAN> recover database;
Starting recover at 25-JUN-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 133 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo01.log
archive log thread 1 sequence 134 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo02.log
archive log thread 1 sequence 135 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo03.log
archive log filename=/u01/app/oracle/oradata/zhang1/redo01.log thread=1 sequence=133
archive log filename=/u01/app/oracle/oradata/zhang1/redo02.log thread=1 sequence=134
archive log filename=/u01/app/oracle/oradata/zhang1/redo03.log thread=1 sequence=135
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-JUN-14
RMAN>
5、开库查看
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
四、将临时表空间和 redo 日志文件也转移到 ASM 磁盘组中
1、Temp 文件：
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> select file# ,name,status from v$tempfile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/zhang1/temp01.dbf ONLINE
2 +DATA/zhang1/tempfile/temp.263.851193897 ONLINE
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/zhang1/temp01.dbf';
Tablespace altered.
2、Redo 日志：
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
6 rows selected.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 898271
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.
删除文件系统上的 redo.log:
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo01.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo02.log';
Database altered.
删除当前日志：
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES ACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system checkpoint;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES INACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo03.log';
Database altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
五、将 spfile 转移到 ASM 磁盘中
SQL> startup nomount pfile='/tmp/11.ora';
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
SQL> create spfile='+DATA/spfilezhang1.ora' from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL>
六、修改归档路径
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /back/archive
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
SQL> show parameter log;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/back/archive
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 14306304
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set log_archive_dest_1='LOCATION=+REC/';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +REC
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
测试一下是否可以归档：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO INACTIVE 899420
5 1 3 52428800 1 NO CURRENT 901827
6 1 0 52428800 1 YES UNUSED 0
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 YES INACTIVE 899420
5 1 3 52428800 1 YES INACTIVE 901827
6 1 4 52428800 1 NO CURRENT 901853
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
thread_1_seq_3.257.851198147
七、删除原来文件系统上的数据文件
[oracle@aix213 dbs]$cd $ORACLE_BASE
[oracle@aix213 oracle]$ls
admin flash_recovery_area oraInventory oradata product
[oracle@aix213 oracle]$cd oradata/
[oracle@aix213 oradata]$ls
zhang1
[oracle@aix213 oradata]$rm -rf zhang1
查看磁盘平衡情况：
select * from V$ASM_OPERATION
二、 创建 VG、LV
[root@aix213 /]$lspv
hdisk1 00c6d0ef0395eef2 rootvg active
hdisk0 00032fb8843fafe9 racvg active
[root@aix213 dev]$mklv -y oradata -t raw racvg 160
oradata
[root@aix213 dev]$mklv -y recovery -t raw racvg 160
recovery
[root@aix213 dev]$lsvg -l racvg
racvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
oradata raw 160 160 1 closed/syncd N/A
recovery raw 160 160 1 closed/syncd N/A
更改宿主目录
[root@aix213 /]$chown oracle:dba /dev/roradata
[root@aix213 /]$chown oracle:dba /dev/oradata
[root@aix213 /]$chown oracle:dba /dev/recovery
[root@aix213 /]$chown oracle:dba /dev/rrecovery
三、 创建 ASM 磁盘组
1、 创建+ASM 实例的参数文件
[oracle@aix213 dbs]$vi init+ASM.ora
"init+ASM.ora" 6 lines, 246 characters
*.background_dump_dest='/u01/app/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/app/oracle/admin/+ASM/cdump'
*.remote_login_passwordfile='EXCLUSIVE'
*.user_dump_dest='/u01/app/oracle/admin/+ASM/udump'
instance_type='asm'
large_pool_size=12M
asm_diskstring='/dev/roradata','/dev/rrecovery'
~
2、 用 root 用户启动 CSS 服务
[root@aix213 bin]$./localconfig delete
/etc/oracle does not exist. Creating it now.
./localconfig[715]: /etc/init.cssd: not found
[root@aix213 bin]$./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'system'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
aix213
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@aix213 bin]$
3、 创建 ASM 磁盘组
[oracle@aix213 bin]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 25 16:42:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$Instance;
STATUS
------------
STARTED
SQL> desc v$asm_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
SQL> select NAME, STATE from v$asm_diskgroup;
no rows selected
SQL> create diskgroup DATA external redundancy disk '/dev/roradata';
Diskgroup created.
SQL> create diskgroup REC external redundancy disk '/dev/rrecovery';
Diskgroup created.
SQL> select NAME, STATE from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DATA MOUNTED
REC MOUNTED
[实施步骤]
一、 将原库控制文件所在路径修改为 ASM 磁盘组上的路径
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DATA';
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
二、 用 rman 迁移目标数据库的控制文件和数据文件
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
1、恢复一个最近的 0 级备份的控制文件
RMAN> restore controlfile from '/back/mirror/zhang_c-2690774443-20140625-13.ctl';
Starting restore at 25-JUN-14
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:17
output filename=+DATA/zhang1/controlfile/current.256.851192723
Finished restore at 25-JUN-14
2、 以 COPY 的形式备份一个新的数据文件在 ASM 磁盘上
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 25-JUN-14
Starting implicit crosscheck backup at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 41 objects
Finished implicit crosscheck backup at 25-JUN-14
Starting implicit crosscheck copy at 25-JUN-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-14
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=/u01/app/oracle/oradata/zhang1/system01.dbf
output filename=+DATA/zhang1/datafile/system.257.851192845 tag=TAG20140625T182724 recid=34
stamp=851192898
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/zhang1/report01.dbf
output filename=+DATA/zhang1/datafile/report.258.851192901 tag=TAG20140625T182724 recid=35
stamp=851192940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/zhang1/sysaux01.dbf
output filename=+DATA/zhang1/datafile/sysaux.259.851192945 tag=TAG20140625T182724 recid=36
stamp=851192979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/zhang1/example01.dbf
output filename=+DATA/zhang1/datafile/example.260.851192981 tag=TAG20140625T182724 recid=37
stamp=851193004
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/zhang1/undotbs01.dbf
output filename=+DATA/zhang1/datafile/undotbs1.261.851193007 tag=TAG20140625T182724 recid=38
stamp=851193011
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/zhang1/users01.dbf
output filename=+DATA/zhang1/datafile/users.262.851193015 tag=TAG20140625T182724 recid=39
stamp=851193014
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUN-14
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
3、 令数据库中的控制文件指向新的备份所在的位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/zhang1/datafile/system.257.851192845"
datafile 2 switched to datafile copy "+DATA/zhang1/datafile/undotbs1.261.851193007"
datafile 3 switched to datafile copy "+DATA/zhang1/datafile/sysaux.259.851192945"
datafile 4 switched to datafile copy "+DATA/zhang1/datafile/users.262.851193015"
datafile 5 switched to datafile copy "+DATA/zhang1/datafile/example.260.851192981"
datafile 6 switched to datafile copy "+DATA/zhang1/datafile/report.258.851192901"
4、对数据库进行恢复
RMAN> recover database;
Starting recover at 25-JUN-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 133 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo01.log
archive log thread 1 sequence 134 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo02.log
archive log thread 1 sequence 135 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo03.log
archive log filename=/u01/app/oracle/oradata/zhang1/redo01.log thread=1 sequence=133
archive log filename=/u01/app/oracle/oradata/zhang1/redo02.log thread=1 sequence=134
archive log filename=/u01/app/oracle/oradata/zhang1/redo03.log thread=1 sequence=135
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-JUN-14
RMAN>
5、开库查看
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
四、将临时表空间和 redo 日志文件也转移到 ASM 磁盘组中
1、Temp 文件：
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> select file# ,name,status from v$tempfile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/zhang1/temp01.dbf ONLINE
2 +DATA/zhang1/tempfile/temp.263.851193897 ONLINE
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/zhang1/temp01.dbf';
Tablespace altered.
2、Redo 日志：
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
6 rows selected.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 898271
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.
删除文件系统上的 redo.log:
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo01.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo02.log';
Database altered.
删除当前日志：
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES ACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system checkpoint;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES INACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo03.log';
Database altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
五、将 spfile 转移到 ASM 磁盘中
SQL> startup nomount pfile='/tmp/11.ora';
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
SQL> create spfile='+DATA/spfilezhang1.ora' from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL>
六、修改归档路径
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /back/archive
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
SQL> show parameter log;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/back/archive
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 14306304
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set log_archive_dest_1='LOCATION=+REC/';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +REC
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
测试一下是否可以归档：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO INACTIVE 899420
5 1 3 52428800 1 NO CURRENT 901827
6 1 0 52428800 1 YES UNUSED 0
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 YES INACTIVE 899420
5 1 3 52428800 1 YES INACTIVE 901827
6 1 4 52428800 1 NO CURRENT 901853
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
thread_1_seq_3.257.851198147
七、删除原来文件系统上的数据文件
[oracle@aix213 dbs]$cd $ORACLE_BASE
[oracle@aix213 oracle]$ls
admin flash_recovery_area oraInventory oradata product
[oracle@aix213 oracle]$cd oradata/
[oracle@aix213 oradata]$ls
zhang1
[oracle@aix213 oradata]$rm -rf zhang1
查看磁盘平衡情况：
select * from V$ASM_OPERATION
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29802484/viewspace-1852002/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
动态创建视图脚本
下一篇：
dblink ora600
请登录后发表评论
登录
全部评论
